SQL 语句总结

一、DDL（Data Definition Language）数据定义语言

CREATE / ALTER / DROP / SHOW

1. 操纵数据库

  • a. 创建数据库:
1
2
3
CREATE DATABASE mydb1;
CREATE DATABASE mydb2 CHARACTER SET gbk;
CREATE DATABASE mydb3 CHARACTER SET gbk COLLATE gbk_chinese_ci;
  • b. 查询数据库：
1
2
SHOW DATABASES;                       -- 查看当前数据库服务器中的所有数据库
SHOW CREATE DATABASE mydb2;           -- 查看前面创建的 mydb2 数据库的定义信息
  • c. 修改数据库：
1
2
ALTER DATABASE mydb2 CHARACTER SET utf8;    -- 查看服务器中的数据库，并把 mydb2 的字符集修改为 utf8
DROP DATABASE mydb3;                       -- 删除数据库

2. 操作数据表

  • a. 创建表
1
2
3
4
5
6
CREATE TABLE 表名(
    字段1   字段类型,
    字段2   字段类型,
    ...
    字段n   字段类型        -- 这边没有句号和分号
);

常用数据类型：

1
2
3
4
5
6
7
8
9
10
int：整型
double：浮点型，例如 double(5,2) 表示最多5位，其中必须有2位小数，即最大值为999.99；
char：固定长度字符串类型；    char(10)     'aaa      '  占10位
varchar：可变长度字符串类型；varchar(10)  'aaa'  占3位
text：字符串类型；
blob：字节类型；
date：日期类型，格式为：yyyy-MM-dd；
time：时间类型，格式为：hh:mm:ss
timestamp：时间戳类型 yyyy-MM-dd hh:mm:ss  会自动赋值
datetime：日期时间类型 yyyy-MM-dd hh:mm:ss
  • b. 查询表
1
2
3
SHOW TABLES;                      -- 当前数据库中的所有表
DESC employee;                    -- 查看表的字段信息
SHOW CREATE TABLE user;           -- 查看表格的创建细节
  • c. 删除表
1
2
3
4
5
6
7
ALTER TABLE employee ADD image blob;              -- 在上面员工表的基本上增加一个 image 列
ALTER TABLE employee MODIFY job VARCHAR(60);     -- 修改 job 列，使其长度为60
ALTER TABLE employee DROP image;                  -- 删除 image 列，一次只能删一列
RENAME TABLE employee TO user;                    -- 表名改为 user
ALTER TABLE user CHARACTER SET gbk;              -- 修改表的字符集为 gbk
ALTER TABLE user CHANGE name username VARCHAR(100);  -- 列名 name 修改为 username
DROP TABLE user;                                  -- 删除表

二、DML（Data Manipulation Language）数据操作语言

DML 是对表中的数据进行增、删、改的操作。不要与 DDL 混淆了。

1. 插入操作：INSERT

1
2
3
4
5
6
7
-- 单条
INSERT INTO 表名（列名1，列名2 ...）VALUES(列值1，列值2...);
-- 批量
INSERT INTO 表名（列名1，列名2 ...）VALUES(列值1，列值2...),
(列值1，列值2...),
(列值1，列值2...),
(列值1，列值2...);

小知识：在 MySQL 中，字符串类型和日期类型都要用单引号括起来。

空值：NULL

查看数据库编码的具体信息：

1
SHOW VARIABLES LIKE 'character%';

临时更改客户端和服务器结果集的编码：

1
2
SET character_set_client=gbk;
SET character_set_results=gbk;

插入操作：insert

2. 修改操作：UPDATE

1
UPDATE 表名 SET 列名1=列值1，列名2=列值2 ... WHERE 列名 = 值

示例：

1
2
3
4
5
6
7
8
9
10
11
-- 将所有员工薪水修改为 5000 元
UPDATE emp SET salary=5000;

-- 将姓名为'zhangsan'的员工薪水修改为 3000 元
UPDATE emp SET salary=3000 WHERE name='zhangsan';

-- 将姓名为'lisi'的员工薪水修改为 4000 元, job 改为 ccc
UPDATE emp SET salary=4000, job='ccc' WHERE name='lisi';

-- 将 gender 为 male 的薪水在原有基础上增加 1000 元
UPDATE emp SET salary=salary+1000 WHERE gender='male';

3. 删除操作：DELETE

1
2
3
4
5
6
7
8
-- 删除表中名称为'zs'的记录
DELETE FROM emp WHERE name='zs';

-- 删除表中所有记录
DELETE FROM emp;

-- 使用 truncate 删除表中记录
TRUNCATE TABLE emp;

DELETE 删除表中的数据，表结构还在；删除后的数据可以找回。TRUNCATE 删除是把表直接 DROP 掉，然后再创建一个同样的新表，删除的数据不能找回，执行速度比 DELETE 快。

三、DQL（Data Query Language）数据查询语言

数据库执行 DQL 语句不会对数据进行改变，而是让数据库发送结果集给客户端，查询返回的结果集是一张虚拟表，建如下表：

1
2
3
4
5
6
CREATE TABLE stu(
    sid INT PRIMARY KEY AUTO_INCREMENT,
    sname VARCHAR(50) NOT NULL,
    age INT,
    gender VARCHAR(50)
) ENGINE=INNODB, DEFAULT CHARSET=UTF8;

dql（data query language）数据

dql（data query language）数据

dql（data query language）数据

dql（data query language）数据

dql（data query language）数据

1. 条件查询

条件查询就是在查询时给出 WHERE 子句，在 WHERE 子句中可以使用如下运算符及关键字：

1
2
-- AND
SELECT * FROM stu WHERE gender='female' AND age<50;

条件查询

1
2
-- OR
SELECT * FROM stu WHERE sid=8 OR sname='chenEr';

条件查询

1
2
3
4
-- IN
SELECT * FROM stu WHERE sid IN ('1','2','3');
-- NOT IN
SELECT * FROM stu WHERE sid NOT IN ('1','2','3');

条件查询

条件查询

1
2
-- IS NULL
SELECT * FROM stu WHERE age IS NULL;

条件查询

1
2
3
4
5
6
-- BETWEEN…AND
SELECT * FROM stu WHERE age BETWEEN 20 AND 40;
-- 相当于
SELECT * FROM stu WHERE age>=20 AND age<=40;

-- 比较运算符：=  !=  <>  <  <=  >  >=

条件查询

1
2
3
-- NOT
SELECT * FROM stu WHERE NOT sname IS NULL;
SELECT * FROM stu WHERE sname IS NOT NULL;

条件查询

2. 模糊查询

当想查询姓名中包含 a 字母的学生时就需要使用模糊查询了。模糊查询需要使用关键字 LIKE。

1
2
-- _：任意一个字母
SELECT * FROM stu WHERE sname LIKE '___Y_';

模糊查询

1
2
-- %：任意 0~n 个字母
SELECT * FROM stu WHERE sname LIKE '%a%';

模糊查询

3. 字段控制查询

1
2
-- 去除重复记录
SELECT DISTINCT gender FROM stu;

字段控制查询

两列值相加

字段控制查询

给列添加别名

字段控制查询

4. 排序

1
2
3
4
-- 升序
SELECT * FROM stu ORDER BY age ASC;
-- 降序
SELECT * FROM stu ORDER BY age DESC;

5. 聚合函数：SUM / AVG / MAX / MIN / COUNT

1
2
3
4
5
COUNT()：统计指定列不为 NULL 的记录行数；
MAX()：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
MIN()：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
SUM()：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为 0；
AVG()：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为 0；

聚合函数：sum / avg / max / mi

聚合函数：sum / avg / max / mi

聚合函数：sum / avg / max / mi

6. 分组查询

当需要分组查询时需要使用 GROUP BY 子句，例如查询每个部门的工资和，这说明要使用部门来分组。

注：凡和聚合函数同时出现的列名，则一定要写在 GROUP BY 之后

1
2
SELECT gender, SUM(age) FROM stu GROUP BY gender;
SELECT gender AS 性别, COUNT(*) AS 人数 FROM stu WHERE age>=30 GROUP BY gender;

HAVING 子句：

1
SELECT gender AS 性别, COUNT(*) AS 人数 FROM stu GROUP BY gender HAVING SUM(age)>30;

HAVING 与 WHERE 的区别：

1
2
3
4
1. having 是在分组后对数据进行过滤。
   where 是在分组前对数据进行过滤。
2. having 后面可以使用分组函数（统计函数）。
   where 后面不可以使用分组函数。

WHERE 是对分组前记录的条件，如果某行记录没有满足 WHERE 子句的条件，那么这行记录不会参加分组；而 HAVING 是对分组后数据的约束。

7. LIMIT

LIMIT 用来限定查询结果的起始行，以及总行数。

1
SELECT * FROM stu LIMIT 0,5;    -- 查询 5 行数据，从第 0 行开始

limit

分页查询：

limit

四、数据的完整性

作用：保证用户输入的数据保存到数据库中是正确的。

确保数据的完整性 = 在创建表时给表中添加约束。

1. 实体完整性

  • a. 主键约束（PRIMARY KEY）

实体完整性

  • b. 唯一约束（UNIQUE）

特点：数据不能重复。

实体完整性

  • c. 自动增长列（AUTO_INCREMENT）

给主键添加自动增长的数值，列只能是整数类型。

实体完整性

2. 域完整性

域完整性

  • a. 数据类型：（数值类型、日期类型、字符串类型）

域完整性

  • b. 非空约束：NOT NULL

域完整性

  • c. 默认值约束：DEFAULT

域完整性

3. 引用完整性

  • a. 外键约束：FOREIGN KEY

引用完整性

引用完整性

4. 表与表之间的关系

表与表之间的关系

表与表之间的关系

五、多表查询

多表查询有如下几种：

1. 合并结果集

作用：合并结果集就是把两个 SELECT 语句的查询结果合并到一起！

1
2
3
4
-- UNION：去除重复记录
SELECT * FROM t1 UNION SELECT * FROM t2;
-- UNION ALL：不去除重复记录
SELECT * FROM t1 UNION ALL SELECT * FROM t2;

合并结果集

合并结果集

2. 连接查询（非常重要）

连接查询就是求出多个表的乘积，例如 t1 连接 t2，那么查询出的结果就是 t1*t2。

连接查询（非常重要）

连接查询会产生笛卡尔积，假设集合 A={a,b}，集合 B={0,1,2}，则两个集合的笛卡尔积为 {(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。可以扩展到多个集合的情况。

1
SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno;

连接查询（非常重要）

1
2
3
SELECT emp.ename, emp.sal, emp.comm, dept.dname
FROM emp, dept
WHERE emp.deptno=dept.deptno;

连接查询（非常重要）

还可以为表指定别名，然后在引用列时使用别名即可。

1
2
3
SELECT e.ename, e.sal, e.comm, d.dname
FROM emp AS e, dept AS d
WHERE e.deptno=d.deptno;
  • a. 内连接

上面的连接语句就是内连接，但它不是 SQL 标准中的查询方式，可以理解为方言！SQL 标准的内连接为（内连接 INNER，MySQL 默认可以省略）：

1
SELECT * FROM emp e INNER JOIN dept d ON e.deptno=d.deptno;

连接查询（非常重要）

  • b. 外连接

外连接的特点：查询出的结果存在不满足条件的可能。

连接查询（非常重要）

连接查询（非常重要）

连接查询（非常重要）

连接查询（非常重要）

3. 自然连接

自然连接

4. 子查询

一个 SELECT 语句中包含另一个完整的 SELECT 语句。

子查询就是嵌套查询，即 SELECT 中包含 SELECT，如果一条语句中存在两个或两个以上 SELECT，那么就是子查询语句了。

子查询

子查询作为条件，形式为单行多列、单行单列、多行单列：

子查询

子查询

子查询

子查询

子查询

子查询作为表，形式多行多列：

子查询

子查询

5. 自连接

自己连接自己，起别名。

自连接

自连接

六、MySQL 数据备份与恢复

1
2
# 备份
mysqldump -u用户名 -p密码 数据库名 > 生成的脚本文件路径

在控制台使用 mysqldump 命令可以用来生成指定数据库的脚本文本。注意：脚本文本中只包含数据库的内容，而不会存在创建数据库的语句！所以在恢复数据时，还需要手动创建一个数据库之后再去恢复数据。

备份

1
2
# 恢复
source d:\备份文件

注意：在执行脚本时需要先行核查当前数据库中的表是否与脚本文件中的语句有冲突！例如在脚本文件中存在 CREATE TABLE a 的语句，而当前数据库中已经存在了 a 表，那么就会出错！

恢复

恢复