SQL分類

DDL （數(shù)據(jù)定義語句）
  數(shù)據(jù)定義語言 - Data Definition Language
  用來定義數(shù)據(jù)庫的對象，如數(shù)據(jù)表、視圖、索引等
DML  （數(shù)據(jù)操縱語句）
  數(shù)據(jù)處理語言 - Data Manipulation Language
  在數(shù)據(jù)庫表中更新，增加和刪除記錄
  如 update， insert， delete --- 增刪改
DCL （數(shù)據(jù)控制語句）
  數(shù)據(jù)控制語言 – Data Control Language
  指用于設置用戶權限和控制事務語句
  如grant，revoke，if…else，while，begin transaction
DQL （數(shù)據(jù)查詢語句）
  數(shù)據(jù)查詢語言 – Data Query Language
  select

數(shù)據(jù)庫操作

• 1.創(chuàng)建數(shù)據(jù)庫

方法一：
   create database 數(shù)據(jù)庫名字;
   例如： create database day06;

方法二：
   create database 數(shù)據(jù)庫名字 character set 字符集;
   例如： create database day06_1 character set utf8;

方法三：
   create database 數(shù)據(jù)庫名字 character set 字符集 collate 校對規(guī)則;
   例如： create database day06_2 character set utf8 collate utf8_bin;

創(chuàng)建成功打印如下：

Query OK, 1 row affected, 1 warning (0.05 sec)

• 2.查看數(shù)據(jù)庫

查看所有數(shù)據(jù)庫
  show databases;    

查看數(shù)據(jù)庫定義的語句
  show create database 數(shù)據(jù)庫名字;
  例如：show create database day06;
  例如：show create database day06_1;

查詢成功打印如下：

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| day06 |
| day06_1 |
| day06_2 |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
7 rows in set (0.00 sec)

+----------+----------------------------------------------------------------------------------------------+
| Database | Create Database |
+----------+----------------------------------------------------------------------------------------------+
| day06 | CREATE DATABASE day06 /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ |
+----------+----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

• 3.修改數(shù)據(jù)庫操作

修改數(shù)據(jù)的字符集
  alter database 數(shù)據(jù)庫的名字 character set 字符集;
  例如：  alter database day06_1 character set gbk;

修改前打印如下：

mysql> mysql> show create database day06_1;
+----------+------------------------------------------------------------------+
| Database | Create Database |
+----------+------------------------------------------------------------------+
| day06_1 | CREATE DATABASE day06_1 /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+------------------------------------------------------------------+
1 row in set (0.00 sec)

修改

mysql> alter database day06_1 character set gbk;
Query OK, 1 row affected (0.06 sec)

修改后打印如下：

mysql> show create database day06_1;
+----------+-----------------------------------------------------------------+
| Database | Create Database |
+----------+-----------------------------------------------------------------+
| day06_1 | CREATE DATABASE day06_1 /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

• 4.刪除數(shù)據(jù)庫

drop database 數(shù)據(jù)庫名字;
例如：  drop database day06_1;

打印如下：

刪除數(shù)據(jù)庫

mysql> drop database day06_1;
Query OK, 0 rows affected (0.04 sec)

查詢數(shù)據(jù)庫，day06_1表示已經(jīng)刪除

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| day06 |
| day06_2 |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)

• 5.其他數(shù)據(jù)庫操作

use 數(shù)據(jù)庫名字;
例如： use day06;

查看一下當前正在使用的數(shù)據(jù)庫
例如：select database();

打印如下：

mysql> use day06;
Database changed

mysql> select database();
+------------+
| database() |
+------------+
| day06 |
+------------+
1 row in set (0.01 sec)