跳转至

SQL 常用的数据库/表操作

SHOW

显示用户所有的数据库

SQL
1
SHOW DATABASES;

用户进入某个数据库

SQL
1
USE $DB_NAME;

显示当前数据库中所有的数据表

SQL
1
SHOW TABLES;

查看当前位于的数据库名称

SQL
1
SELECT DATABASE(); -- 查看当前位于的数据库名称，`NULL` 表示没有选择任何数据集

CREATE

创建数据库并使用它

SQL
1
2
3
4
5
-- for demo, we use summer for $DB_NAME here :)

CREATE DATABASE $DB_NAME;

USE $DB_NAME;

在当前数据库中创建数据表

形如这样，给出所有数据表的格式定义

SQL
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
CREATE TABLE Students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sid CHAR(10) NOT NULL UNIQUE,
    name VARCHAR(255) NOT NULL,
    major VARCHAR(255) NOT NULL
);

CREATE TABLE Courses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    cid VARCHAR(8) NOT NULL UNIQUE,
    title VARCHAR(255) NOT NULL,
    credit INT NOT NULL,
    time VARCHAR(255)
);

CREATE TABLE Association (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT,
    course_id INT,
    grade CHAR(2),
    CONSTRAINT fk_student_id FOREIGN KEY (student_id) REFERENCES Students(id),
    CONSTRAINT fk_course_id FOREIGN KEY (course_id) REFERENCES Courses(id)
);

很显然这就创建了lec0和lec1中反复提到的三张数据表，分别是 Students、Courses 和 Association，其中 Association 是一个关联表，用于表示学生和课程之间的关系。

查看当前数据库中所有的数据表

SQL
1
SHOW TABLES;

查看具体某数据表的具体字段信息

SQL
1
DESC $TB_NAME;

下面是展示的过程：

SQL
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
mysql> SHOW TABLES;
+------------------+
| Tables_in_summer |
+------------------+
| Association      |
| Courses          |
| Students         |
+------------------+
3 rows in set (0.00 sec)

mysql> DESC Students;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int          | NO   | PRI | NULL    | auto_increment |
| sid   | char(10)     | NO   | UNI | NULL    |                |
| name  | varchar(255) | NO   |     | NULL    |                |
| major | varchar(255) | NO   |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

mysql> DESC Courses;
+--------+--------------+------+-----+---------+----------------+
| Field  | Type         | Null | Key | Default | Extra          |
+--------+--------------+------+-----+---------+----------------+
| id     | int          | NO   | PRI | NULL    | auto_increment |
| cid    | varchar(8)   | NO   | UNI | NULL    |                |
| title  | varchar(255) | NO   |     | NULL    |                |
| credit | int          | NO   |     | NULL    |                |
| time   | varchar(255) | YES  |     | NULL    |                |
+--------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql>

查看某张表中所有的索引

SQL
1
SHOW INDEX FROM $TB_NAME;

ALTER

ALTER 语句用于给数据表 添加、删除字段，修改字段 的类型或属性，例如：

添加字段

SQL
1
2
ALTER TABLE $TB_NAME
ADD $COLUMN $RESTRICT; -- 添加字段

举个例子：

SQL
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
mysql> ALTER TABLE Students
    -> ADD gender VARCHAR(10);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC Students;
+--------+--------------+------+-----+---------+----------------+
| Field  | Type         | Null | Key | Default | Extra          |
+--------+--------------+------+-----+---------+----------------+
| id     | int          | NO   | PRI | NULL    | auto_increment |
| sid    | char(10)     | NO   | UNI | NULL    |                |
| name   | varchar(255) | NO   |     | NULL    |                |
| major  | varchar(255) | NO   |     | NULL    |                |
| gender | varchar(10)  | YES  |     | NULL    |                |
+--------+--------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

删除字段

SQL
1
2
ALTER TABLE $TB_NAME
DROP COLUMN $COLUMN; -- 删除字段

举个例子：

SQL
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
mysql> ALTER TABLE Students
    -> DROP COLUMN gender; -- 删除字段
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC Students;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int          | NO   | PRI | NULL    | auto_increment |
| sid   | char(10)     | NO   | UNI | NULL    |                |
| name  | varchar(255) | NO   |     | NULL    |                |
| major | varchar(255) | NO   |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

修改字段

SQL
1
2
ALTER TABLE $TB_NAME
MODIFY COLUMN $COLUMN $RESTRICT; -- 修改字段: CHAR(10) -> CHAR(20)

举个例子：

SQL
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
mysql> ALTER TABLE Students
    -> MODIFY COLUMN sid CHAR(20); -- 修改字段: CHAR(10) -> CHAR(20)
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC Students;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int          | NO   | PRI | NULL    | auto_increment |
| sid   | char(20)     | YES  | UNI | NULL    |                |
| name  | varchar(255) | NO   |     | NULL    |                |
| major | varchar(255) | NO   |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

建立索引

SQL
1
2
ALTER TABLE $TB_NAME
ADD INDEX $INDEX_NAME ($COLUMN); -- 建立索引: set up index for sids

举个例子：

SQL
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
mysql> ALTER TABLE Students
    -> ADD INDEX idx_sid (sid); -- 建立索引: set up index for sids
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC Students;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int          | NO   | PRI | NULL    | auto_increment |
| sid   | char(20)     | YES  | UNI | NULL    |                |
| name  | varchar(255) | NO   |     | NULL    |                |
| major | varchar(255) | NO   |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

建立了两个索引：

SQL
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
mysql> ALTER TABLE Students ADD INDEX idx_sid (name);
ERROR 1061 (42000): Duplicate key name 'idx_sid'
mysql> ALTER TABLE Students ADD INDEX idx_name (name);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC Students;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int          | NO   | PRI | NULL    | auto_increment |
| sid   | char(20)     | YES  | UNI | NULL    |                |
| name  | varchar(255) | NO   | MUL | NULL    |                |
| major | varchar(255) | NO   |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

删除索引

SQL
1
2
ALTER TABLE $TB_NAME 
DROP INDEX $INDEX_NAME; -- 删除现有的 idx_sid 索引
SQL
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
-- 删除现有的 idx_sid 索引
mysql> ALTER TABLE Students DROP INDEX idx_sid;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC Students;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int          | NO   | PRI | NULL    | auto_increment |
| sid   | char(20)     | YES  | UNI | NULL    |                |
| name  | varchar(255) | NO   | MUL | NULL    |                |
| major | varchar(255) | NO   |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

复合索引

如果打算为 sid 和 name 两个字段创建复合索引（即在一个索引中包含多个列），可以执行以下命令：

SQL
1
ALTER TABLE $TB_NAME ADD INDEX $INDEX_NAME ($COLUMN1, $COLUMN2, ...);

举个例子：

SQL
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
ALTER TABLE Students ADD INDEX flex_mapping (name, major);
Query OK, 0 rows affected, 1 warning (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> DESC Students;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int          | NO   | PRI | NULL    | auto_increment |
| sid   | char(20)     | YES  | UNI | NULL    |                |
| name  | varchar(255) | NO   | MUL | NULL    |                |
| major | varchar(255) | NO   |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

显示当前表中所有索引

SQL
1
SHOW INDEX FROM $TB_NAME;

举个例子：

SQL
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
mysql> SHOW INDEX FROM Students;
+----------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table    | Non_unique | Key_name      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Students |          0 | PRIMARY       |            1 | id          | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| Students |          0 | sid           |            1 | sid         | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| Students |          1 | $flex_mapping |            1 | name        | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| Students |          1 | $flex_mapping |            2 | major       | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+----------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
4 rows in set (0.04 sec)

删除外键

SQL
1
2
ALTER TABLE Association
DROP FOREIGN KEY fk_student_id; -- 删除外键: (id in StudentTable) and (student_id)

显示当前表中所有的外键

这个并没有简单粗暴的直接指令，而是需要把表中所有信息打印出来，然后找到 CONSTRAINT 关键字，就是外键。

SQL
1
SHOW CREATE TABLE $TB_NAME\G
Tips

这里 \G 有利于格式化输出

SQL
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
mysql> SHOW CREATE TABLE Association;
+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table       | Create Table                                                                                                                                                                                                                                                                                                                                                                                                           |
+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Association | CREATE TABLE `Association` (
  `id` int NOT NULL AUTO_INCREMENT,
  `student_id` int DEFAULT NULL,
  `course_id` int DEFAULT NULL,
  `grade` char(2) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_student_id` (`student_id`),
  KEY `fk_course_id` (`course_id`),
  CONSTRAINT `fk_course_id` FOREIGN KEY (`course_id`) REFERENCES `Courses` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

DROP

删除当前数据库内某张表

SQL
1
DROP TABLE $TB_NAME;

用户删除某数据库

SQL
1
DROP TABLE $DB_NAME;

举个例子：

SQL
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| Hello              |
| hello              |
| information_schema |
| mysql              |
| performance_schema |
| summer             |
| sys                |
| table              |
+--------------------+
8 rows in set (0.00 sec)

mysql> USE summer;
Database changed
mysql> SHOW TABLES;
+------------------+
| Tables_in_summer |
+------------------+
| Association      |
| Courses          |
| Students         |
+------------------+
3 rows in set (0.00 sec)

mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| summer     |
+------------+
1 row in set (0.00 sec)

再看个例子：

SQL
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
mysql> DROP TABLE Students;
Query OK, 0 rows affected (0.02 sec)

mysql> DROP DATABASE summer;
Query OK, 2 rows affected (0.06 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

mysql> SELECT DATABASE(); -- 查看当前位于的数据库名称，`NULL` 表示没有选择任何数据集
+------------+
| DATABASE() |
+------------+
| NULL       |
+------------+
1 row in set (0.00 sec)

常用指令汇总

我们创建ucb数据库，并放上Students和Courses两张表：

从0创建Database并在内部创建Table

SQL
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
SHOW DATABASES; -- 展示用户下所有的Databases

CREATE DATABASE ucb; -- 新建一个database

USE ucb; -- 使用某个database

CREATE TABLE Students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sid CHAR(10) NOT NULL UNIQUE,
    name VARCHAR(255) NOT NULL,
    major VARCHAR(255) NOT NULL
); -- table creation

CREATE TABLE Courses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    cid VARCHAR(8) NOT NULL UNIQUE,
    title VARCHAR(255) NOT NULL,
    credit INT NOT NULL,
    time VARCHAR(255)
); -- table creation

SHOW TABLES; -- 展示当前database下所有的tables

在Table内增删改字段

SQL
1
2
3
4
5
6
DESC Students; -- 展示Students表内字段
DESC Courses; -- 展示Courses表内字段

ALTER TABLE Students ADD COLUMN gender CHAR(4); -- 增加(ADD)字段
ALTER TABLE Students DROP COLUMN gender; -- 删除(DROP)字段
ALTER TABLE Students MODIFY COLUMN name VARCHAR(100); -- 修改(MODIFY)字段

Table内有关索引的操作

感觉用的少，见招拆招算了，不太需要肌肉记忆

删除Table和Database

SQL
1
2
3
4
5
6
DROP TABLE Students; -- 删除Students表
DROP DATABASE ucb; -- 删除ucb数据库

SELECT DATABASE(); -- 查看当前位于的数据库名称
SHOW TABLES; -- 展示当前database下所有的tables
SHOW DATABASES; -- 展示用户下所有的Databases