深入浅出MySQL笔记：SQL基础

SQL分类

SQL语句分为三类：

DDL（Data Definition Language）语句：数据定义语句，定义了不同的数据段、数据库、表、列、索引等数据库对象。常用的关键词包括：create drop alter 等
DML（Data Manipulation Language）语句：数据操纵语句，用于添加、删除、更新、查询数据库记录，并检查数据完整性，常用关键词包括：insert、delete 、update 、select 等。
DCL（Data Control Language）语句：数据控制语句，用于控制不同数据段直接的许可和访问级别的语句，这些语句定义了，数据库、表、字段、用户的访问权限和安全级别。常用的语句关键字主要包括：grant revoke 等。

DDL语句

DDL语句就是对数据库内部进行创建、删除、修改等操作的语言。它和DML最大的区别是DML只操作表内部的数据，而不涉及表的定义、结构的修改、更不会涉及其他对象。DLL语句更多的是由数据库管理员（DBA）使用。开发人员一般很少使用。

创建数据库： CREATE DATABASE [dbname 数据库名称]

查看数据库： show databases

选择数据库： use [dbname 数据库名称]

除了手动创建的数据库外还有四个MySQL自动创建的数据库：

information_schema 主要存储系统中的一些数据库对象信息，比如用户表信息、列信息、权限信息、字符集信息、分区信息等。每个用户都可以查看这个数据库，但根据权限的不同，看到的内容不同。
performance_schema MySQL5.5引入的系统库，用于存储系统性能相关的动态参数表。
sys MySQL5.7引入的系统库，本身不记录系统数据，基于 information_schema和performance_schema 之上，封装了一层更加易于调优和诊断的系统视图。
mysql 存储系统的用户权限信息。

查看数据表： show tables

删除数据库： drop database [dbname 数据库名称]

创建数据库： create table [tablename 表名]()

create table emp(
 ename varchar(10),
 hiredate date,
 sal decimal(10,2),
 deptno int(2));

查看表字段： DESC [tablename 表名称]

查看创表语句: show create table [tablename] G

mysql> show create table emp G;
*************************** 1. row ***************************
       Table: emp
Create Table: CREATE TABLE `emp` (
  `ename` varchar(10) DEFAULT NULL,
  `hiredate` date DEFAULT NULL,
  `sal` decimal(10,2) DEFAULT NULL,
  `deptno` int(2) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR:
No query specified

使用这种方式查看创表语句，可以看到表定义外，还可以看到表的engine（存储引擎）和charset（字符集）等信息。“G”的含义是让记录能够按照字段顺序是竖向排列，以便更好地展示内容较长的记录。

删除表： DROP TABLE [tablename 表名称]

修改表类型： ALTER TABLE 表名称 MODIFY [COLUMN] column_definition [FIRST / ALFER col_name]

例如：修改表emp的ename字段定义，将varchar(10)，改为varchar(20)；

mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(10)   | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int(2)        | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> alter table emp modify ename varchar(20);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(20)   | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int(2)        | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

增加表字段： ALTER TABLE 表名称 ADD [COLUMN] column_defnition [FIRST / AFTER col_name]

例如：在emp表中增加age字段，类型为int(3);

mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(20)   | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int(2)        | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> alter table emp add column age int(3);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(20)   | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int(2)        | YES  |     | NULL    |       |
| age      | int(3)        | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

删除表字段： ALTER TABLE 表名称 DROP column col_name

mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(20)   | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int(2)        | YES  |     | NULL    |       |
| age      | int(3)        | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> alter table emp drop column age;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(20)   | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int(2)        | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

表字段改名： ALTER TABLE 表名称 CHANGE [COLUMN] old_col_name column_definition [FIRST / AFTER col_name]

例如：将age改名age1，同时修改字段类型为int(4);

mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(20)   | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int(2)        | YES  |     | NULL    |       |
| age      | int(3)        | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> alter table emp change age age1 int(4);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(20)   | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int(2)        | YES  |     | NULL    |       |
| age1     | int(4)        | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

注意：change 和 modify 都可以修改表的定义，不同的是 change需要写两次列名不方便，但change可以修改列名，而modify不能。

修改字段排列顺序：

前面介绍到的字段修改和添加的语法（ADD/CHANGE/MIODIFY），都有一个可选项，first|after column_name，这个选项可以修改字段在表中的排序位置，ADD添加的字段默认都在表的最后位置，而CHANGE/MODIFY默认都不会修改字段位置。

例如：新增一个birth data 在ename字段之后

mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(20)   | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int(2)        | YES  |     | NULL    |       |
| age1     | int(4)        | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> alter table emp add birth date after ename;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(20)   | YES  |     | NULL    |       |
| birth    | date          | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int(2)        | YES  |     | NULL    |       |
| age1     | int(4)        | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

修改字段age，放在最前面：

mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(20)   | YES  |     | NULL    |       |
| birth    | date          | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int(2)        | YES  |     | NULL    |       |
| age1     | int(4)        | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql> alter table emp modify age1 int(3) first;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| age1     | int(3)        | YES  |     | NULL    |       |
| ename    | varchar(20)   | YES  |     | NULL    |       |
| birth    | date          | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int(2)        | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

注意：CHANGE/FIRST/AFTER COLUMN 这些关键字都属于MySQL在标准SQL上扩展的关键字，在其他数据库不一定适用。

修改表名称： ALTER TABLE 表名称 RENAME 新表名称

mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| age1     | int(3)        | YES  |     | NULL    |       |
| ename    | varchar(20)   | YES  |     | NULL    |       |
| birth    | date          | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int(2)        | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql> alter table emp rename empl;
Query OK, 0 rows affected (0.01 sec)

mysql> desc empl;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| age1     | int(3)        | YES  |     | NULL    |       |
| ename    | varchar(20)   | YES  |     | NULL    |       |
| birth    | date          | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int(2)        | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

DML语句

DML操作是指对数据库中表记录的操作，主要包括表记录的插入（insert）、更新（update）、删除（delete）、查询（select），是开发人员日常使用频繁的操作。

插入记录：

表创建好之后就可以向表中插入记录，最基本的语法如下：

INSERT INTO tablename (feild1,feild2...feildN,) VALUES(value1,value2...valueN);

例如：向emp表内插入记录，ename为zzxl，hiredate为2000-01-01，sal为2000，deptno为1：

/* 使用完整方式插入数据 */
mysql> insert into emp (ename,hiredate,sal,deptno) values('zzxl','2000-01-01','2000',1);
Query OK, 1 row affected (0.00 sec)

/* 不指定字段名称，也可以插入数据，但values后的顺序应该与字段顺序一致 */
mysql> insert into emp values('lisa','2003-02-01','3000',2);
Query OK, 1 row affected (0.01 sec)

mysql> select * from emp;
+-------+------------+---------+--------+
| ename | hiredate   | sal     | deptno |
+-------+------------+---------+--------+
| zzxl  | 2000-01-01 | 2000.00 |      1 |
| lisa  | 2003-02-01 | 3000.00 |      2 |
+-------+------------+---------+--------+
2 rows in set (0.00 sec)

含可空的字段、非空但有默认值及自增的字段，可以在使用insert不写，values中只包含对映字段名的值。这些没写的字段会自动设置为NULL、默认值、自增的下一个数字，在某些情况下大大缩短了SQL语句的复杂性。

例如：只对表中ename和sal字段显式插入值：

mysql> insert into emp (ename,sal) values('dony',1000);
Query OK, 1 row affected (0.01 sec)

mysql> select * from emp;
+-------+------------+---------+--------+
| ename | hiredate   | sal     | deptno |
+-------+------------+---------+--------+
| zzxl  | 2000-01-01 | 2000.00 |      1 |
| lisa  | 2003-02-01 | 3000.00 |      2 |
| dony  | NULL       | 1000.00 |   NULL |
+-------+------------+---------+--------+
3 rows in set (0.00 sec)

使用 insert 也可以一次性插入多条记录，每条记录使用逗号分隔：

mysql> insert into emp (ename,sal) values('zhangsan',4000),('wangwu',5000);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from emp;
+----------+------------+---------+--------+
| ename    | hiredate   | sal     | deptno |
+----------+------------+---------+--------+
| zzxl     | 2000-01-01 | 2000.00 |      1 |
| lisa     | 2003-02-01 | 3000.00 |      2 |
| dony     | NULL       | 1000.00 |   NULL |
| zhangsan | NULL       | 4000.00 |   NULL |
| wangwu   | NULL       | 5000.00 |   NULL |
+----------+------------+---------+--------+
5 rows in set (0.00 sec)

这个特性可以使得MySQL在插入大量记录时，节省很多网络开销，大大提高插入效率。

更新记录：

更新表里的记录值可以使用update 命令进行修改，语法如下：

UPDATE tablename SET field1=value1,field2=value2...fieldN=valueN [WHERE CONDITION]

例如：将emp表中，ename为 “lisa” 的薪水（sal）从3000，改为4000;

mysql> select * from emp;
+----------+------------+---------+--------+
| ename    | hiredate   | sal     | deptno |
+----------+------------+---------+--------+
| zzxl     | 2000-01-01 | 2000.00 |      1 |
| lisa     | 2003-02-01 | 3000.00 |      2 |
| dony     | NULL       | 1000.00 |   NULL |
| zhangsan | NULL       | 4000.00 |   NULL |
| wangwu   | NULL       | 5000.00 |   NULL |
+----------+------------+---------+--------+
5 rows in set (0.00 sec)

mysql> update emp set sal=4000 where ename='lisa';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from emp;
+----------+------------+---------+--------+
| ename    | hiredate   | sal     | deptno |
+----------+------------+---------+--------+
| zzxl     | 2000-01-01 | 2000.00 |      1 |
| lisa     | 2003-02-01 | 4000.00 |      2 |
| dony     | NULL       | 1000.00 |   NULL |
| zhangsan | NULL       | 4000.00 |   NULL |
| wangwu   | NULL       | 5000.00 |   NULL |
+----------+------------+---------+--------+
5 rows in set (0.00 sec)

也可以同时更新多张表：

mysql> select * from dept;
+--------+----------+
| deptno | deptname |
+--------+----------+
|      1 | tech     |
|      2 | sale     |
|      5 | fin      |
+--------+----------+
3 rows in set (0.00 sec)

mysql> select * from emp;
+----------+------------+---------+--------+
| ename    | hiredate   | sal     | deptno |
+----------+------------+---------+--------+
| zzxl     | 2000-01-01 | 2000.00 |      1 |
| lisa     | 2003-02-01 | 4000.00 |      2 |
| dony     | NULL       | 1000.00 |      4 |
| zhangsan | NULL       | 4000.00 |   NULL |
| wangwu   | NULL       | 5000.00 |   NULL |
+----------+------------+---------+--------+
5 rows in set (0.00 sec)

mysql> update emp a,dept b set a.sal=a.sal*b.deptno,b.deptname=a.ename where a.deptno=b.deptno;
Query OK, 3 rows affected (0.01 sec)
Rows matched: 4  Changed: 3  Warnings: 0

mysql> select * from emp;
+----------+------------+---------+--------+
| ename    | hiredate   | sal     | deptno |
+----------+------------+---------+--------+
| zzxl     | 2000-01-01 | 2000.00 |      1 |
| lisa     | 2003-02-01 | 8000.00 |      2 |
| dony     | NULL       | 1000.00 |      4 |
| zhangsan | NULL       | 4000.00 |   NULL |
| wangwu   | NULL       | 5000.00 |   NULL |
+----------+------------+---------+--------+
5 rows in set (0.00 sec)

mysql> select * from dept;
+--------+----------+
| deptno | deptname |
+--------+----------+
|      1 | zzxl     |
|      2 | lisa     |
|      5 | fin      |
+--------+----------+
3 rows in set (0.00 sec)

多表更新多用于根据一个表的字段动态的更新另一个表的字段。

删除记录：

如果数据不再需要，可以使用delete命令删除：

DELETE FROM tablename [where CONDITION]

例如删除 emp表中 “wangwu”这条记录

mysql> delete from emp where ename='wangwu';
Query OK, 1 row affected (0.01 sec)

同时删除多张表的记录

DELETE t1,t2...tN FROM t1,t2...tN [WHERE CONDITION]

注意：如果from后面的表使用别名，则delete后面也要使用相应的别名，否则会提示语法错误。

例如：同时删除emp和dept表中deptno为2的记录：

mysql> select * from emp;
+----------+------------+---------+--------+
| ename    | hiredate   | sal     | deptno |
+----------+------------+---------+--------+
| zzxl     | 2000-01-01 | 2000.00 |      1 |
| lisa     | 2003-02-01 | 8000.00 |      2 |
| dony     | NULL       | 1000.00 |      4 |
| zhangsan | NULL       | 4000.00 |   NULL |
+----------+------------+---------+--------+
4 rows in set (0.00 sec)

mysql> select * from dept;
+--------+----------+
| deptno | deptname |
+--------+----------+
|      1 | zzxl     |
|      2 | lisa     |
|      5 | fin      |
+--------+----------+
3 rows in set (0.00 sec)

mysql> delete a,b from emp a,dept b where a.deptno=b.deptno and a.deptno=2;
Query OK, 2 rows affected (0.01 sec)

mysql> select * from emp;
+----------+------------+---------+--------+
| ename    | hiredate   | sal     | deptno |
+----------+------------+---------+--------+
| zzxl     | 2000-01-01 | 2000.00 |      1 |
| dony     | NULL       | 1000.00 |      4 |
| zhangsan | NULL       | 4000.00 |   NULL |
+----------+------------+---------+--------+
3 rows in set (0.00 sec)

mysql> select * from dept;
+--------+----------+
| deptno | deptname |
+--------+----------+
|      1 | zzxl     |
|      5 | fin      |
+--------+----------+
2 rows in set (0.00 sec)

注意：不管是单表还是多表，不加where条件将会把表的所有记录删除，所以操作时一定要小心。

查询记录：

SELECT * FROM tablename [WHERE CONDITION]

最简单的查询方式是将记录全部查出：

mysql> select * from emp;
+----------+------------+---------+--------+
| ename    | hiredate   | sal     | deptno |
+----------+------------+---------+--------+
| zzxl     | 2000-01-01 | 2000.00 |      1 |
| dony     | NULL       | 1000.00 |      4 |
| zhangsan | NULL       | 4000.00 |   NULL |
+----------+------------+---------+--------+
3 rows in set (0.01 sec)

使用 * 可以查出所有字段，如果需要查询指定字段内容需要把字段一个一个列出来：

mysql> select ename,sal from emp;
+----------+---------+
| ename    | sal     |
+----------+---------+
| zzxl     | 2000.00 |
| dony     | 1000.00 |
| zhangsan | 4000.00 |
+----------+---------+
3 rows in set (0.00 sec)

查询不重复记录：

有时候需要将表中的记录去重后显示出来，可以用distinct关键字来实现：

mysql> select * from emp;
+----------+------------+---------+--------+
| ename    | hiredate   | sal     | deptno |
+----------+------------+---------+--------+
| zzxl     | 2000-01-01 | 2000.00 |      1 |
| dony     | NULL       | 1000.00 |      4 |
| zhangsan | NULL       | 4000.00 |      1 |
+----------+------------+---------+--------+
3 rows in set (0.00 sec)

mysql> select distinct deptno from emp;
+--------+
| deptno |
+--------+
|      1 |
|      4 |
+--------+
2 rows in set (0.00 sec)

条件查询：

在大多数情况下，并不需要查询出所有数据，而是需要根据限定条件查询出一部分数据，用where关键字就可以实现这样的操作。

例如查询deptno为1的记录：

mysql> select * from emp where deptno=1;
+----------+------------+---------+--------+
| ename    | hiredate   | sal     | deptno |
+----------+------------+---------+--------+
| zzxl     | 2000-01-01 | 2000.00 |      1 |
| zhangsan | NULL       | 4000.00 |      1 |
+----------+------------+---------+--------+
2 rows in set (0.00 sec)

where后面的条件是对一个字段的 = 比较、除此之外还可以使用：>、<、>=、<=、!= 等比较运算符；多个条件之间还可以使用 or and 等逻辑运算符进行多条件的联合查询。

比如即查询 deptno为1，同时sal小于3000的记录：

mysql> select * from emp where deptno=1 and sal<3000;
+-------+------------+---------+--------+
| ename | hiredate   | sal     | deptno |
+-------+------------+---------+--------+
| zzxl  | 2000-01-01 | 2000.00 |      1 |
+-------+------------+---------+--------+
1 row in set (0.01 sec)

排序和限制

经常有这样的需求，取出按照某个字段进行排序后的记录结果集，这样就需要用到数据库排序操作，关键字 ORDER BY 来实现：

SELECT * FROM tablename [WHERE CONDITION] [ORDER BY field1 [DESC|ASC],field2 [DESC|ASC]];

DESC和ASC是排列顺序关键字，DESC表示降序排列，ASC表示升序排列，如果不写此关键词默认是升序排列。Order By 后面可以跟多个不同的排列字段，并且每个字段都可以有自己的排列顺序。

例如：把emp表中的记录按照工资从低到高升序排列显示:

mysql> select * from emp order by sal;
+----------+------------+---------+--------+
| ename    | hiredate   | sal     | deptno |
+----------+------------+---------+--------+
| dony     | NULL       | 1000.00 |      4 |
| zzxl     | 2000-01-01 | 2000.00 |      1 |
| wangwu   | 2020-08-26 | 3000.00 |      1 |
| zhaoliu  | 2020-03-12 | 3500.00 |      1 |
| zhangsan | NULL       | 4000.00 |      1 |
+----------+------------+---------+--------+
5 rows in set (0.00 sec)

如果排序后的记录只想显示一部分而不是全部的话，可以使用limit 关键字，语法如下：

SELECT ...[LIMIT offset_start,row_count]

其中 offset_start表示记录的起始偏移量，row_count表示显示的行数。默认情况下，起始偏移量为0，只需要填写显示的行数就行了，这个时候实际现实的就是前N条记录。

例如，显示emp表按sal排列后的前三条记录：

mysql> select * from emp order by sal limit 3;

+--------+------------+---------+--------+
| ename  | hiredate   | sal     | deptno |
+--------+------------+---------+--------+
| dony   | NULL       | 1000.00 |      4 |
| zzxl   | 2000-01-01 | 2000.00 |      1 |
| wangwu | 2020-08-26 | 3000.00 |      1 |
+--------+------------+---------+--------+
3 rows in set (0.00 sec)

如果要按照sal排列后从第二条开始显示三条数据，可以使用下面的语法：

mysql> select * from emp order by sal limit 1,3;
+---------+------------+---------+--------+
| ename   | hiredate   | sal     | deptno |
+---------+------------+---------+--------+
| zzxl    | 2000-01-01 | 2000.00 |      1 |
| wangwu  | 2020-08-26 | 3000.00 |      1 |
| zhaoliu | 2020-03-12 | 3500.00 |      1 |
+---------+------------+---------+--------+
3 rows in set (0.00 sec)

limit 经常和 order by 一起配合使用来进行记录的分页显示。

注意：limit属于MySQL扩展SQL92后的语法，在其他数据库上不能通用。

聚合查询：

很多情况下都需要进行汇总操作，比如统计整个公司的人数或者统计每个部门的人数，这时就需要用到SQL的聚合操作。

聚合操作语法如下：

SELECT [field1,field2...fieldN] fun_name
FROM tablename
[WHERE where_contition]
[GROUP BY field1,field2...fieldN
[WITH ROLLUP]]
[HAVING where_contition]

参数说明如下：

fun_name表示要做的聚合操作，也就是聚合函数，常用的有 SUM(求和) 、count(*) 记录数、avg(平均数) 、max（最大值）、min(最小值);
GROUP BY 关键字表示要进行分类聚合的字段，比如要按照部门分类统计员工数量、部门就应该写在GROUP BY 的后面。
WITH ROLLUP 是可选参数，表明是否对分类聚合后的结果再进行汇总。
HAVING 关键字表示对分类后的结果再进行条件的过滤。

注意：HAVING和WHERE的区别在于，HAVING 是对聚合后的结果进行过滤，而 WHERE 是在聚合前就对记录进行过滤。如果条件允许，尽可能先使用 WHERE 进行过滤记录，因为这样的结果集减小，聚合的效率大大提高，最后再根据逻辑看是否需要用 HAVING 进行再过滤。

例如：使用emp表统计公司总人数：

mysql> select count(1) from emp;
+----------+
| count(1) |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)

在这个基础上统计各个部门的人数：

mysql> select deptno,count(1) from emp group by deptno;
+--------+----------+
| deptno | count(1) |
+--------+----------+
|      1 |        2 |
|      2 |        2 |
|      4 |        1 |
+--------+----------+
3 rows in set (0.00 sec)

既要统计公司总人数，也要统计各个部门的人数：

mysql> select deptno,count(1) from emp group by deptno with rollup;
+--------+----------+
| deptno | count(1) |
+--------+----------+
|      1 |        2 |
|      2 |        2 |
|      4 |        1 |
|   NULL |        5 |
+--------+----------+
4 rows in set (0.00 sec)

统计人数大于1的部门：

mysql> select deptno,count(1) from emp group by deptno having count(1)>1;
+--------+----------+
| deptno | count(1) |
+--------+----------+
|      1 |        2 |
|      2 |        2 |
+--------+----------+
2 rows in set (0.00 sec)

统计公司所有员工的薪水总额、最高和最低的薪水：

mysql> select * from emp;
+----------+------------+---------+--------+
| ename    | hiredate   | sal     | deptno |
+----------+------------+---------+--------+
| zzxl     | 2000-01-01 | 2000.00 |      1 |
| dony     | NULL       | 1000.00 |      4 |
| zhangsan | NULL       | 4000.00 |      1 |
| wangwu   | 2020-08-26 | 3000.00 |      2 |
| zhaoliu  | 2020-03-12 | 3500.00 |      2 |
+----------+------------+---------+--------+
5 rows in set (0.00 sec)

mysql> select sum(sal),max(sal),min(sal) from emp;
+----------+----------+----------+
| sum(sal) | max(sal) | min(sal) |
+----------+----------+----------+
| 13500.00 |  4000.00 |  1000.00 |
+----------+----------+----------+
1 row in set (0.00 sec)

表连接：

当需要同时显示多个表众中的字段时，可以使用表连接来实现。从大类上分，表连接分内连接和外连接，他们之间主要的区别是，内连接仅选出两张表中互相匹配的记录，而外连接会选择出其他不匹配的记录。我们最常用的是内连接。

例如：查询出所有雇员的名字和所在部门名称，因为雇员名称和部门分别存在emp和dept两个表中，因此需要使用使用表连接进行查询：

mysql> select * from emp;
+----------+------------+---------+--------+
| ename    | hiredate   | sal     | deptno |
+----------+------------+---------+--------+
| zzxl     | 2000-01-01 | 2000.00 |      1 |
| dony     | NULL       | 1000.00 |      3 |
| zhangsan | NULL       | 4000.00 |      1 |
| wangwu   | 2020-08-26 | 3000.00 |      2 |
| zhaoliu  | 2020-03-12 | 3500.00 |      2 |
+----------+------------+---------+--------+
5 rows in set (0.00 sec)

mysql> select * from dept;
+--------+----------+
| deptno | deptname |
+--------+----------+
|      1 | tech     |
|      2 | sale     |
|      3 | hr       |
+--------+----------+
3 rows in set (0.00 sec)

mysql> select ename,deptname from emp,dept where emp.deptno=dept.deptno;
+----------+----------+
| ename    | deptname |
+----------+----------+
| zzxl     | tech     |
| dony     | hr       |
| zhangsan | tech     |
| wangwu   | sale     |
| zhaoliu  | sale     |
+----------+----------+
5 rows in set (0.00 sec)

外连接又分为左连接和右链接，具体定义如下：

左连接：包含所有左边表的记录，甚至是右边表中没有和它匹配的记录。
右链接：包含所有右边表的记录，甚至是左边表中没有和它匹配的记录。

例如emp中所有用户名所在的部门名称：

mysql> select * from emp;
+----------+------------+---------+--------+
| ename    | hiredate   | sal     | deptno |
+----------+------------+---------+--------+
| zzxl     | 2000-01-01 | 2000.00 |      1 |
| dony     | NULL       | 1000.00 |      3 |
| zhangsan | NULL       | 4000.00 |      1 |
| wangwu   | 2020-08-26 | 3000.00 |      2 |
| zhaoliu  | 2020-03-12 | 3500.00 |      4 |
+----------+------------+---------+--------+
5 rows in set (0.00 sec)

mysql> select * from dept;
+--------+----------+
| deptno | deptname |
+--------+----------+
|      1 | tech     |
|      2 | sale     |
|      3 | hr       |
+--------+----------+
3 rows in set (0.00 sec)

mysql> select ename,deptname from emp left join dept on emp.deptno=dept.deptno;
+----------+----------+
| ename    | deptname |
+----------+----------+
| zzxl     | tech     |
| zhangsan | tech     |
| wangwu   | sale     |
| dony     | hr       |
| zhaoliu  | NULL     |
+----------+----------+
5 rows in set (0.00 sec)

可以看出即使，”zhaoliu”所对应的部门4在dept表中并不存在，因为使用的是左连接，所以依然会显示出 “zhaoliu” 这条记录，只不过因为deptname字段没有对应的记录，所以显示为NULL。

子查询：

在某些情况下，当进行查询的时候，需要的条件是另一个SELECT语句的执行结果，这个时候就要使用到子查询。用于子查询的关键字包括：in、not in、=、!=、exists、not exists等。

例如，从emp表中查询出所有部门在dept表中的所有记录：

mysql> select * from emp;
+----------+------------+---------+--------+
| ename    | hiredate   | sal     | deptno |
+----------+------------+---------+--------+
| zzxl     | 2000-01-01 | 2000.00 |      1 |
| dony     | NULL       | 1000.00 |      3 |
| zhangsan | NULL       | 4000.00 |      1 |
| wangwu   | 2020-08-26 | 3000.00 |      2 |
| zhaoliu  | 2020-03-12 | 3500.00 |      4 |
+----------+------------+---------+--------+
5 rows in set (0.00 sec)

mysql> select * from dept;
+--------+----------+
| deptno | deptname |
+--------+----------+
|      1 | tech     |
|      2 | sale     |
|      3 | hr       |
+--------+----------+
3 rows in set (0.00 sec)

mysql> select * from emp where deptno in(select deptno from dept);
+----------+------------+---------+--------+
| ename    | hiredate   | sal     | deptno |
+----------+------------+---------+--------+
| zzxl     | 2000-01-01 | 2000.00 |      1 |
| dony     | NULL       | 1000.00 |      3 |
| zhangsan | NULL       | 4000.00 |      1 |
| wangwu   | 2020-08-26 | 3000.00 |      2 |
+----------+------------+---------+--------+
4 rows in set (0.00 sec)

如果子查询数唯一，还可以使用= 代替 in”：

mysql> select * from emp where deptno = (select deptno from dept);
ERROR 1242 (21000): Subquery returns more than 1 row
mysql> select * from emp where deptno = (select deptno from dept limit 1);
+----------+------------+---------+--------+
| ename    | hiredate   | sal     | deptno |
+----------+------------+---------+--------+
| zzxl     | 2000-01-01 | 2000.00 |      1 |
| zhangsan | NULL       | 4000.00 |      1 |
+----------+------------+---------+--------+
2 rows in set (0.00 sec)

在某些情况下，子查询转换为表连接，例如：

mysql> select * from emp where deptno in (select deptno from dept);
+----------+------------+---------+--------+
| ename    | hiredate   | sal     | deptno |
+----------+------------+---------+--------+
| zzxl     | 2000-01-01 | 2000.00 |      1 |
| dony     | NULL       | 1000.00 |      3 |
| zhangsan | NULL       | 4000.00 |      1 |
| wangwu   | 2020-08-26 | 3000.00 |      2 |
+----------+------------+---------+--------+
4 rows in set (0.00 sec)

转换为表连接后：

mysql> select emp.* from emp ,dept where emp.deptno=dept.deptno;
+----------+------------+---------+--------+
| ename    | hiredate   | sal     | deptno |
+----------+------------+---------+--------+
| zzxl     | 2000-01-01 | 2000.00 |      1 |
| dony     | NULL       | 1000.00 |      3 |
| zhangsan | NULL       | 4000.00 |      1 |
| wangwu   | 2020-08-26 | 3000.00 |      2 |
+----------+------------+---------+--------+
4 rows in set (0.00 sec)

注意：表连接在很多情况下用于优化子查询。

记录联合：

我们经常会碰到这样的应用，将两个表的数据按照一定的查询条件查询出来后，将结果合并在一起显示出来，这个时候就需要用到 union 和 union all关键字来实现这样的功能，具体语法如下：

SELECT * FROM t1
UNION|UNION ALL
SELECT * FROM t2
...
UNION|UNION ALL
SELECT * FROM tN;

UNION 和UNION ALL的主要区别就是UNION ALL是把结果集直接合并在一起，而UNION则是将UNION ALL 之后的结果进行一次DISTINCT，去除重复记录后的结果。

比如这个例子，将emp表和dept表中部门编号的合集显示出来：

mysql> select * from emp;
+----------+------------+---------+--------+
| ename    | hiredate   | sal     | deptno |
+----------+------------+---------+--------+
| zzxl     | 2000-01-01 | 2000.00 |      1 |
| dony     | NULL       | 1000.00 |      3 |
| zhangsan | NULL       | 4000.00 |      1 |
| wangwu   | 2020-08-26 | 3000.00 |      2 |
| zhaoliu  | 2020-03-12 | 3500.00 |      4 |
+----------+------------+---------+--------+
5 rows in set (0.00 sec)

mysql> select * from dept;
+--------+----------+
| deptno | deptname |
+--------+----------+
|      1 | tech     |
|      2 | sale     |
|      3 | hr       |
+--------+----------+
3 rows in set (0.00 sec)

mysql> select deptno from emp
    -> union all
    -> select deptno from dept;
+--------+
| deptno |
+--------+
|      1 |
|      3 |
|      1 |
|      2 |
|      4 |
|      1 |
|      2 |
|      3 |
+--------+
8 rows in set (0.00 sec)

将结果去重后：

mysql> select deptno from emp
    -> union
    -> select deptno from dept;
+--------+
| deptno |
+--------+
|      1 |
|      3 |
|      2 |
|      4 |
+--------+
4 rows in set (0.00 sec)