基本语法

一、创建数据库 DATABASE

习惯上，SQL 指令使用大写，而数据库名称使用小写，且自定义名称最好用反引号括起来，以免和关键字重名。以下 TABLE 同理。

sql

# 查看当前数据库
SHOW DATABASES;
# 创建数据库 CREATE
CREATE DATABASE `database-name`;

# 删除数据库 DROP
DROP DATABASE `database-name`;

二、创建数据表 TABLE

sql

 # 选择数据库
 USE `database-name`

 # 显示当前数据库下所有数据表
 SHOW TABLES;

 # 创建一个 student 表格
 CREATE TABLE `student`(
	 `student_id` INT PRIMARY KEY,
	 `name` VARCHAR(20),
	 `major` VARCHAR(20)
 );
+------------------------+
| Tables_in_sql_tutorial |
+------------------------+
| student                |
+------------------------+
 ## 也可以单独设置 PRIMARY KEY
  CREATE TABLE `student`(
	 `student_id` INT,
	 `name` VARCHAR(20),
	 `major` VARCHAR(20),
	 PRIMARY KEY(`student_id`)
 );

 # 概览数据表 DESCRIBE
 DESCRIBE `student`;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| student_id | int         | NO   | PRI | NULL    |       |
| name       | varchar(20) | YES  |     | NULL    |       |
| major      | varchar(20) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+

 # 删除数据表
 DROP `student`;

 # 新增属性 ALTER / ADD
 ALTER TABLE `student` ADD `gpa` DECIMAL(3,2);
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| student_id | int          | NO   | PRI | NULL    |       |
| name       | varchar(20)  | YES  |     | NULL    |       |
| major      | varchar(20)  | YES  |     | NULL    |       |
| gpa        | decimal(3,2) | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+

# 删除属性 ALTER / DROP
ALTER TABLE `student` DROP COLUMN gpa;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| student_id | int         | NO   | PRI | NULL    |       |
| name       | varchar(20) | YES  |     | NULL    |       |
| major      | varchar(20) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+

存储数据 INSERT

VALUES(a, b, c…) 括号中逗号隔开的数据，顺序要和定义数据表时的顺序相同。

sql

# 查询数据 SELECT FROM
SELECT * FROM `student`;
# 插入数据
INSERT INTO `student` VALUES(1, '小白', '历史');
+------------+--------+--------+
| student_id | name   | major  |
+------------+--------+--------+
|          1 | 小白   | 历史   |
+------------+--------+--------+

# 插入空数据 INSERT INTO
INSERT INTO `student` VALUES(3,'小绿',NULL);
+------------+--------+--------+
| student_id | name   | major  |
+------------+--------+--------+
|          1 | 小白   | 历史   |
|          2 | 小黑   | 生物   |
|          3 | 小绿   | NULL   |
+------------+--------+--------+

# 自定义插入数据的顺序
INSERT INTO `student`(`name`, `major`, `student_id`) VALUES('小兰','英语', 4);
+------------+--------+--------+
| student_id | name   | major  |
+------------+--------+--------+
|          1 | 小白   | 历史   |
|          2 | 小黑   | 生物   |
|          3 | 小绿   | NULL   |
|          4 | 小兰   | 英语   |
+------------+--------+--------+
## 也可以不插入某个属性，不填就是 NULL
INSERT INTO `student`(`major`, `student_id`) VALUES('英语', 5);
+------------+--------+--------+
| student_id | name   | major  |
+------------+--------+--------+
|          1 | 小白   | 历史   |
|          2 | 小黑   | 生物   |
|          3 | 小绿   | NULL   |
|          4 | 小兰   | 英语   |
|          5 | NULL   | 英语   |
+------------+--------+--------+

限制约束 CONSTRAINT

sql

CREATE TABLE `student`(
	 `student_id` INT AUTO_INCREMENT,
	 `name` VARCHAR(20) NOT NULL, # 非空
	 `major` VARCHAR(20) UNIQUE, # 不可重复
	 PRIMARY KEY(`student_id`)
);

 NOT NULL # 非空
 UNIQUE # 不可重复
 DEFAULT 'value' # 默认值，没提供具体值会使用默认值
 AUTO_INCREMENT # 自增，一般用于 Primry Key ，不需要每次都给数据指定 Primary Key

修改、删除数据 UPDATE && DELETE

sql

# 关闭 sql 默认更新
SET SQL_SAFE_UPDATES = 0;

CREATE TABLE `student`(
	 `student_id` INT AUTO_INCREMENT,
	 `name` VARCHAR(20),
	 `major` VARCHAR(20),
	 `score` INT,
	 PRIMARY KEY(`student_id`)
);
+------------+------+---------+-------+
| student_id | name | major   | score |
+------------+------+---------+-------+
|          1 | ming | English |    20 |
|          2 | hei  | English |    40 |
|          3 | bai  | Math    |    50 |
+------------+------+---------+-------+

# 更新 major
UPDATE student
SET major = 'Chinese'
WHERE major = 'English';
+------------+------+---------+-------+
| student_id | name | major   | score |
+------------+------+---------+-------+
|          1 | ming | Chinese |    20 |
|          2 | hei  | Chinese |    40 |
|          3 | bai  | Math    |    50 |
+------------+------+---------+-------+

UPDATE student
SET major = 'English'
WHERE student_id = 3;
+------------+------+---------+-------+
| student_id | name | major   | score |
+------------+------+---------+-------+
|          1 | ming | Chinese |    20 |
|          2 | hei  | Chinese |    40 |
|          3 | bai  | English |    50 |
+------------+------+---------+-------+

# 多条件：OR
UPDATE student
SET major = 'Computer'
WHERE major = 'Chinese' OR major = 'English';
+------------+------+----------+-------+
| student_id | name | major    | score |
+------------+------+----------+-------+
|          1 | ming | Computer |    20 |
|          2 | hei  | Computer |    40 |
|          3 | bai  | Computer |    50 |
+------------+------+----------+-------+

# 修改多数据：SET 用 , 隔开
UPDATE student
SET name = 'heihei', major = 'Philosophy'
WHERE student_id = 1;
+------------+--------+------------+-------+
| student_id | name   | major      | score |
+------------+--------+------------+-------+
|          1 | heihei | Philosophy |    20 |
|          2 | hei    | Computer   |    40 |
|          3 | bai    | Computer   |    50 |
+------------+--------+------------+-------+

# 不加 WHERE 更新全部
UPDATE student
SET major = 'Philosophy';

sql

# 条件删除
DELETE FROM student
WHERE student_id = 2;
+------------+--------+------------+-------+
| student_id | name   | major      | score |
+------------+--------+------------+-------+
|          1 | heihei | Philosophy |    20 |
|          3 | bai    | Computer   |    50 |
+------------+--------+------------+-------+

# 多条件删除
DELETE FROM student
WHERE name = 'bai' AND major = 'Computer';
+------------+--------+------------+-------+
| student_id | name   | major      | score |
+------------+--------+------------+-------+
|          1 | heihei | Philosophy |    20 |
+------------+--------+------------+-------+

# 运算符删除
DELETE FROM student
WHERE score < 60;
Empty set (0.00 sec)

获取数据 SELECT

比较运算符：

大于： >

小于：<

不等于：<>

大于等于：>=

逻辑运算符：

且：AND

或：OR

sql

# 获取所有属性
SELECT * FROM student;

# 获取指定属性的数据
SELECT name FROM student;
SELECT name, major FROM student;

# 最后呈现的数据不重复
SELECT DISTINCT sex FROM `employee`

# 依据指定属性排序
SELECT *
FROM student
ORDER BY score; ## 按照 score 从小到大排序

ORDER BY score DESC; ## 按照 score 从大到小排序，默认是 ASC 即由小到大

ORDER BY score, student_id; # 首先根据 score 排序，当 score 相等时再根据 student_id 进行排序

# 限制获取数据的数量
SELECT *
FROM student
LIMIT 3; ## 只获取前三笔数据

SELECT *
FROM student
ORDER BY score
LIMIT 2; ## 与 ORDER BY 混用

# 使用 where 进行筛选
SELECT *
FROM student
WHERE major = 'English' AND student_id = 1;

# IN 关键字
WHERE major IN('English','Chinese','Math'); # 等价于下面的写法
WHERE major = 'English' OR major = 'Chinese' OR major = 'Math';

聚合函数 aggregate function

sql

# 获取员工人数
SELECT COUNT(*) FROM `employee`;

# 获取具有 sup_id 一共有多少笔资料
SELECT COUNT(`sup_id`) FROM `employee`;

select count(*) from employee where birth_date > '1970-01-01' and sex = 'F';

# 取平均数
select avg(salary) from employee;

# 总和
select sum(salary) from employee;

# 最大值
select max(salary) from employee;

select min(salary) from employee;

wildcards 万用字符 %(多个字符) _（一个字符）

sql

# 取得电话尾号为 335 的客户
select * from client where phone like '%335';

# 取得姓艾的客户
select * from client where name like '艾%';

# 取得生日在 12 月的员工
select * from employee where birth_date like '_____12%';

联合 union

属性数必须相同且属性的类型也必须相同

sql

# 员工名字 union 客户名字
select name from employee
union
select client_name from client;

连接 join

sql

# 获取所有公司经理的名字
select * from employee join branch on emp_id = manager_id;

select emp_id, name, branch_name from employee join branch on emp_id = manager_id;

# 如果有两张表属性重名的情况，可以在属性前面加上具体是哪个表的属性
select * from employee join branch on employee.emp_id = branch.manager_id;

# 左边表格无论是否成立都返回，右边的表格只有满足条件才会返回
select * from employee left join branch on emp_id = manager_id;

# 右边表格无论是否成立都返回，左边的表格只有满足条件才会返回，
select * from employee right join branch on emp_id = manager_id;

子查询 subquery

sql

# 找出研发部门的经理名字
select name
from employee
where emp_id = (
	select manager_id
	from branch
	where branch_name = '研发'
);

ON DELET SET NULL

当对应不到的时候直接把外键设置为 NULL

ON DELET SET CASECADE

当对应不到直接删除那一笔数据

基本语法 ​

一、创建数据库 DATABASE ​

二、创建数据表 TABLE ​

存储数据 INSERT ​

限制 约束 CONSTRAINT ​

修改、删除数据 UPDATE && DELETE ​

获取数据 SELECT ​

聚合函数 aggregate function ​

wildcards 万用字符 %(多个字符) _（一个字符） ​

联合 union ​

连接 join ​

子查询 subquery ​

ON DELET SET NULL ​

ON DELET SET CASECADE ​