44.MySQL数据库中数据表的基本操作_primary key2个字段组成-CSDN博客

本文链接：https://blog.csdn.net/Pink_Home/article/details/127830863

本文详细介绍了MySQL数据库中数据表的操作，包括复制表结构、数据表完整性约束的设置，如非空、唯一和主键约束，以及单表查询和多表查询的各种方法，如WHERE、JOIN、子查询等，旨在帮助读者深入理解数据库操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一：复制表的结构：

方法一：create table 新表名 like 原表

方法二：create table 新表名 select * from 源表

注意：此方法不仅可复制表的结构，还可将源表的表记录拷贝到新表中。

二：数据表完整性约束：

1.表完整性约束作用：用于保证数据的完整性和唯一性。

2.常见约束：

约束条件	说明
primary key (pk)	标识该字段为该表的主键，可以唯一的标识记录，不可以为空 unique + not null
foreign key (fk)	标识该字段为该表的外键，实现表与表（父表主键/子表1外键/子表2外键）之间的关联
not null	标识该字段不能为空
unique key (uk)	标识该字段的值是唯一的，可以为空，一个表中可有多个 unique key
auto_increment	标识该字段的值自动增长（整数类型。而且为主键！）
default	为该字段设置默认值
unsigned	无符号，正数
zerofill	使用0填充，例如0000001

说明：

（1）是否允许为空，默认NULL，可设置为not null，字段不允许为空，必须赋值。

（2）字段是否有默认值，缺省的默认值是null，如果插入记录时不给字段赋值，此字段使用默认值。

（3）是否为key。

主键 primary key
外键 foreign key
索引（index,unique...）

3.示例：

（1）非空约束：字段名数据类型 not null;

非空约束即 NOT NULL指的是字段的值不能为空。

示例：添加一个新的数据表，设置name字段不为空：
mysql> create table student1 (
    -> id int,
    -> name varchar(20) not null
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql>

查看数据表结构：
mysql> desc student1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql>

（2）唯一约束：字段名数据类型 unique;

唯一性约束即unique用于保证数据表中的字段的唯一性，即表中字段所对应的值不能出现重复。

示例：新建数据表，将表中的name字段所对应的值设置成唯一：
mysql> create table student2(
    -> id int,
    -> name varchar(20) unique
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql>

查看数据表结构：
mysql> desc student2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(20) | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql>

（3）单列做主键约束：字段名数据类型 primary key;

1）设置主键约束的第一种方式：

新建数据表，将数据表中id作为主键：
mysql> create table student3(
    -> id int primary key,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql>

查看数据表结构：
mysql> desc student3;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql>

2）设置约束主键的第二种方式：

示例：新建数据表，以id字段为主键：
mysql> create table student4(
    -> id int,
    -> name varchar(20),
    -> primary key(id)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql>

查看数据表结构：
mysql> desc student4;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql>

（4）复合主键：primary key (字段名1，字段名2)

（5）添加主键约束：alter table 表名 modify 字段名类型 primary key;

注意：primary key ：这一列主键（非空且唯一）约束

原表：
mysql> desc student;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | YES  |     | NULL    |       |
| newname  | varchar(10) | YES  |     | NULL    |       |
| gender   | varchar(10) | YES  |     | NULL    |       |
| birthday | date        | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql>

为原表中的newname字段添加主键约束：
mysql> alter table student modify newname varchar(20) primary key;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>

查看数据表结构：
mysql> desc student;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | YES  |     | NULL    |       |
| newname  | varchar(20) | NO   | PRI | NULL    |       |
| gender   | varchar(10) | YES  |     | NULL    |       |
| birthday | date        | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql>

（6）删除数据表主键约束：alter table 表名 drop primary key;

为刚刚新添加的主键删除：
mysql> alter table student drop primary key;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>

查看删除主键约束后的数据表结构：
mysql> desc student;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | YES  |     | NULL    |       |
| newname  | varchar(20) | NO   |     | NULL    |       |
| gender   | varchar(10) | YES  |     | NULL    |       |
| birthday | date        | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql>

（7）主键自动增长：定义int类型字段自动增长：auto_increment

示例：新建数据表，以id字段设置自增加：
mysql> create table student5 (
    -> id int primary key auto_increment,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql>

查看数据表结构：
mysql> desc student5;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int         | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql>

注意：设置自动增长是从1开始的，每增加一条记录，自动增长的列会自定义+1；当把某条记录删除之后再添加数据，自动增长的数据也不会重复生成（只保证唯一性，不保证连续性。）

（8）设置外键约束：constraint 外键名 foreign key (从表外键字段) references 主表 (关联字段名)

为了保证数据的完整性，我们会选择的使用外键约束，一般使用于多张表之间。

注意：在创建表时添加外键不指定模式的情况下，需要关联主表的字段名必须是索引，如果不是索引，则无法添加外键约束：

示例：新建teacher表 为主表,索引为name
mysql> create table teacher (
    -> course_id int not null auto_increment,
    -> name varchar(20) default null,
    -> sex varchar(10),
    -> primary key (course_id),        #主键为course_id
    -> index (name)        #设置索引为name
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> desc teacher;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| course_id | int         | NO   | PRI | NULL    | auto_increment |
| name      | varchar(20) | YES  | MUL | NULL    |                |
| sex       | varchar(10) | YES  |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

mysql>

示例：新建child表，设置name为外键
mysql> create table child (
    -> course_id int not null auto_increment,
    -> name varchar(20) default null,
    -> sex varchar(10),
    -> primary key (course_id),
    -> constraint waijian foreign key (name) references teacher (name)  #设置name字段为外键
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> desc child;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| course_id | int         | NO   | PRI | NULL    | auto_increment |
| name      | varchar(20) | YES  | MUL | NULL    |                |
| sex       | varchar(10) | YES  |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

mysql>

外键约束的方式有三种：

1）cascade：级联方式，删除/更新父表的某条记录，子表中引用该值的记录会自动被删除/更新

示例：父表employee01
mysql> create table employee01(
    -> name varchar(20) not null,
    -> mail varchar(20),
    -> primary key(name)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> desc employee01;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(20) | NO   | PRI | NULL    |       |
| mail  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql>

示例：设置子表payroll01,子表name外键，关联父表（employee01主键name），同步更新，同步删除
mysql> create table payroll01 (
    -> id int auto_increment not null,
    -> name varchar (20) not null,
    -> payroll int not null,
    -> primary key(id),
    -> constraint employee foreign key (name) references employee01 (name) on delete cascade on update cascade             
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> desc payroll01;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int         | NO   | PRI | NULL    | auto_increment |
| name    | varchar(20) | NO   | MUL | NULL    |                |
| payroll | int         | NO   |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql>

演示：
#####先向父表中插入数据：
mysql> insert into employee01 values ('张三','zhangsan@163.com'),('李四','lisi@163.com');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from employee01;
+--------+------------------+
| name   | mail             |
+--------+------------------+
| 张三   | zhangsan@163.com |
| 李四   | lisi@163.com     |
+--------+------------------+
2 rows in set (0.00 sec)

mysql> select * from payroll01;        ###子表并未发生变化
Empty set (0.00 sec)

mysql>

#####向子表中插入数据：
mysql> insert into payroll01 values (1,'张三',12000),(2,'李四',13300);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from payroll01;
+----+--------+---------+
| id | name   | payroll |
+----+--------+---------+
|  1 | 张三   |   12000 |
|  2 | 李四   |   13300 |
+----+--------+---------+
2 rows in set (0.00 sec)

mysql>

#####更新父表中的数据：（注意：一定是与子表对应的外键）
mysql> update employee01 set name='啊啊啊' where name='张三';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from employee01;
+-----------+------------------+
| name      | mail             |
+-----------+------------------+
| 啊啊啊    | zhangsan@163.com |
| 李四      | lisi@163.com     |
+-----------+------------------+
2 rows in set (0.00 sec)

mysql> select * from payroll01;        #发现子表设置name为外键，已经与父表发生同样的改变
+----+-----------+---------+
| id | name      | payroll |
+----+-----------+---------+
|  1 | 啊啊啊    |   12000 |
|  2 | 李四      |   13300 |
+----+-----------+---------+
2 rows in set (0.00 sec)

mysql>

结论：当父表中字段对应的数据发生改变时，子表设置的外键字段对应的数据也会随之发生改变。

2）set null：设置为null。主表主键值被更新或删除，从表的外键被设置为null。但注意，要求该外键序列没有not null属性约束。

示例：先删除外键，后重建：
#####删除子表payroll01的外键约束：
mysql> alter table payroll01 drop foreign key employee;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 
#####将子表payroll01的name字段设置可为null：
mysql> alter table payroll01 modify name varchar(20) null;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

 desc payroll01;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int         | NO   | PRI | NULL    | auto_increment |
| name    | varchar(20) | YES  | MUL | NULL    |                |
| payroll | int         | NO   |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

#####将子表payroll01设置外键约束，并设置为null：
mysql> alter table payroll01 add constraint employee foreign key (name) references employee01(name) on delete set null on update set null;
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql>

查看原表的数据：
mysql> select * from employee01;
+-----------+------------------+
| name      | mail             |
+-----------+------------------+
| 啊啊啊    | zhangsan@163.com |
| 李四      | lisi@163.com     |
+-----------+------------------+
2 rows in set (0.00 sec)

mysql> select * from payroll01;
+----+-----------+---------+
| id | name      | payroll |
+----+-----------+---------+
|  1 | 啊啊啊    |   12000 |
|  2 | 李四      |   13300 |
+----+-----------+---------+
2 rows in set (0.00 sec)

mysql>

演示：修改父表的数据后，查看子表的变化：
mysql> delete from employee01 where name="啊啊啊";
Query OK, 1 row affected (0.00 sec)

mysql> select * from employee01;
+--------+--------------+
| name   | mail         |
+--------+--------------+
| 李四   | lisi@163.com |
+--------+--------------+
1 row in set (0.00 sec)

mysql> select * from payroll01;    #发现子表中设置外键约束的字段所对应的数值已被设置为null
+----+--------+---------+
| id | name   | payroll |
+----+--------+---------+
|  1 | NULL   |   12000 |
|  2 | 李四   |   13300 |
+----+--------+---------+
2 rows in set (0.00 sec)

mysql>

3）no action/restrict 禁止模式，拒绝父表删除和更新。

示例：
#####先删除子表中的外键
mysql> alter table payroll01 drop foreign key employee;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>
#####重建子表中的外键约束
mysql> alter table payroll01 add constraint employee foreign key (name) references employee01(name) on delete no action on update restrict;
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql>

查看原表的数据：
mysql> select * from employee01;
+--------+--------------+
| name   | mail         |
+--------+--------------+
| 李四   | lisi@163.com |
+--------+--------------+
1 row in set (0.00 sec)

mysql> select * from payroll01;
+----+--------+---------+
| id | name   | payroll |
+----+--------+---------+
|  1 | NULL   |   12000 |
|  2 | 李四   |   13300 |
+----+--------+---------+
2 rows in set (0.00 sec)

mysql>

对父表字段对应的数据进行更改，查看子表的变化：（发现禁止父表进行删除或更新）
mysql> update employee01 set name="王麻子" where name="李四";
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test_mysql`.`payroll01`, CONSTRAINT `employee` FOREIGN KEY (`name`) REFERENCES `employee01` (`name`) ON UPDATE RESTRICT)
mysql>

默认：也是禁止模式：

alter table payroll01 add constraint employee foreign key (name) references employee01(name)

三：表的查询(单表查询)：

（1）创建素材：

mysql> select * from employees5;
+----+-----------+--------+------------+------------+-----------------+----------+--------+--------+
| id | name      | sex    | hire_date  | post       | job_description | salary   | office | dep_id |
+----+-----------+--------+------------+------------+-----------------+----------+--------+--------+
|  1 | jack      | male   | 2018-02-02 | instructor | teach           |  5000.00 |    501 |    100 |
|  2 | tom       | male   | 2018-02-03 | instructor | teach           |  5500.00 |    501 |    100 |
|  3 | robin     | male   | 2018-02-02 | instructor | teach           |  8000.00 |    501 |    100 |
|  4 | alice     | female | 2018-02-02 | instructor | teach           |  7200.00 |    501 |    100 |
|  5 | localhost | male   | 2018-02-02 | hr         | hrcc            |   600.00 |    502 |    101 |
|  6 | harry     | male   | 2018-02-02 | hr         | NULL            |  6000.00 |    502 |    101 |
|  7 | emma      | female | 2018-02-06 | sale       | salecc          | 20000.00 |    503 |    102 |
|  8 | christine | female | 2018-02-05 | sale       | salecc          |  2200.00 |    503 |    102 |
|  9 | zhuzhu    | male   | 2018-02-05 | sale       | NULL            |  2200.00 |    503 |    102 |
| 10 | gougou    | male   | 2018-02-05 | sale       |                 |  2200.00 |    503 |    102 |
+----+-----------+--------+------------+------------+-----------------+----------+--------+--------+
10 rows in set (0.00 sec)

mysql>

（2）去重：select distinct 字段名 from 表名;

mysql> select distinct post from employees5;
+------------+
| post       |
+------------+
| instructor |
| hr         |
| sale       |
+------------+
3 rows in set (0.01 sec)

mysql>

（3）通过四则运算查询：

示例：将每个人的工资翻倍：
mysql> select name,salary*2 from employees5;
+-----------+----------+
| name      | salary*2 |
+-----------+----------+
| jack      | 10000.00 |
| tom       | 11000.00 |
| robin     | 16000.00 |
| alice     | 14400.00 |
| localhost |  1200.00 |
| harry     | 12000.00 |
| emma      | 40000.00 |
| christine |  4400.00 |
| zhuzhu    |  4400.00 |
| gougou    |  4400.00 |
+-----------+----------+
10 rows in set (0.01 sec)

mysql> 
#若想将 工资*2 的字段名指定：
mysql> select name,salary*2 as annual_salary from employees5;
+-----------+---------------+
| name      | annual_salary |        #发现字段名已指定为annual_salary
+-----------+---------------+
| jack      |      10000.00 |
| tom       |      11000.00 |
| robin     |      16000.00 |
| alice     |      14400.00 |
| localhost |       1200.00 |
| harry     |      12000.00 |
| emma      |      40000.00 |
| christine |       4400.00 |
| zhuzhu    |       4400.00 |
| gougou    |       4400.00 |
+-----------+---------------+
10 rows in set (0.00 sec)

mysql>

（4）定义显示格式：concat()函数用于连接字符串：

示例：查找工人的姓名和工资联系在一起：
mysql> select concat(name,"annual salary:",salary) as annual_salary from employees5;
+--------------------------------+
| annual_salary                  |
+--------------------------------+
| jackannual salary:5000.00      |
| tomannual salary:5500.00       |
| robinannual salary:8000.00     |
| aliceannual salary:7200.00     |
| localhostannual salary:600.00  |
| harryannual salary:6000.00     |
| emmaannual salary:20000.00     |
| christineannual salary:2200.00 |
| zhuzhuannual salary:2200.00    |
| gougouannual salary:2200.00    |
+--------------------------------+
10 rows in set (0.00 sec)

mysql>

（5）单条件查询：select * from 表名 where 条件;

mysql> select name,post from employees5 where post='hr';
+-----------+------+
| name      | post |
+-----------+------+
| localhost | hr   |
| harry     | hr   |
+-----------+------+
2 rows in set (0.00 sec)

mysql>

（6）多条件查询：select * from 表名 where 条件1 and 条件2;

（7）关键字 between and：select * from 表名 where 字段名 between 值1 and 值2;

between and用于判断某个字段的值是否在指定的范围内，如果字段的值在指定的范围内，则将所在的记录查询出来。

示例：查找工人薪资在5000指15000之间的：
mysql> select name,salary from employees5 where salary between 5000 and 15000;
+-------+---------+
| name  | salary  |
+-------+---------+
| jack  | 5000.00 |
| tom   | 5500.00 |
| robin | 8000.00 |
| alice | 7200.00 |
| harry | 6000.00 |
+-------+---------+
5 rows in set (0.00 sec)

mysql>

（8）关键字 is null ：select 字段名 from 表名 where 字段名 is null;

示例：查找出数据表中字段名为空的：
mysql> select name,job_description from employees5 where job_description is null;
+--------+-----------------+
| name   | job_description |
+--------+-----------------+
| harry  | NULL            |
| zhuzhu | NULL            |
+--------+-----------------+
2 rows in set (0.00 sec)

mysql>

注意：字段名不为空：select 字段名 from 表名 where 字段名 is not null;

字段名为" "：select 字段名 from 表名 where 字段名=" ";

（9）关键字 in 集合查询：select 字段名 from 表名 where 字段名 in (值);

示例：
mysql> select name,salary from employees5 where salary in (4000,5000,6000,9000);
+-------+---------+
| name  | salary  |
+-------+---------+
| jack  | 5000.00 |
| harry | 6000.00 |
+-------+---------+
2 rows in set (0.00 sec)

mysql>

（10）关键字 like 模糊查询：通配符%：select * from 表名 where 字段名 like "%";

示例：查找表中姓名以al开头的所有信息：
mysql> select * from employees5 where name like "al%";
+----+-------+--------+------------+------------+-----------------+---------+--------+--------+
| id | name  | sex    | hire_date  | post       | job_description | salary  | office | dep_id |
+----+-------+--------+------------+------------+-----------------+---------+--------+--------+
|  4 | alice | female | 2018-02-02 | instructor | teach           | 7200.00 |    501 |    100 |
+----+-------+--------+------------+------------+-----------------+---------+--------+--------+
1 row in set (0.00 sec)

mysql>

通配符"_"：select * from 表名 where 字段名 like "al___"; (注意：单词数与 _ 匹配)

示例：查找表中姓名以al开头的所有信息：(注意：al后面只有三个单词)
mysql> select * from employees5 where name like "al___";
+----+-------+--------+------------+------------+-----------------+---------+--------+--------+
| id | name  | sex    | hire_date  | post       | job_description | salary  | office | dep_id |
+----+-------+--------+------------+------------+-----------------+---------+--------+--------+
|  4 | alice | female | 2018-02-02 | instructor | teach           | 7200.00 |    501 |    100 |
+----+-------+--------+------------+------------+-----------------+---------+--------+--------+
1 row in set (0.00 sec)

mysql>

（11）查询排序：按单列升序排序：select * from 表名 order by 将排序字段名 ASC;

示例：将表中的工资升序排序
mysql> select name,salary from employees5 order by salary asc;
+-----------+----------+
| name      | salary   |
+-----------+----------+
| localhost |   600.00 |
| christine |  2200.00 |
| zhuzhu    |  2200.00 |
| gougou    |  2200.00 |
| jack      |  5000.00 |
| tom       |  5500.00 |
| harry     |  6000.00 |
| alice     |  7200.00 |
| robin     |  8000.00 |
| emma      | 20000.00 |
+-----------+----------+
10 rows in set (0.00 sec)

mysql>

按单列降序排序：select * from 表名 order by 将排序字段名 DESC;

示例：将表中工资降序排序：
mysql> select name,salary from employees5 order by salary desc;
+-----------+----------+
| name      | salary   |
+-----------+----------+
| emma      | 20000.00 |
| robin     |  8000.00 |
| alice     |  7200.00 |
| harry     |  6000.00 |
| tom       |  5500.00 |
| jack      |  5000.00 |
| christine |  2200.00 |
| zhuzhu    |  2200.00 |
| gougou    |  2200.00 |
| localhost |   600.00 |
+-----------+----------+
10 rows in set (0.00 sec)

mysql>

（12）按多列排序：select * from 表名 order by 将排序字段名1 ASC，字段名2 ASC;

示例：将表中的入职日期降序排序，再将工资升序排序：(发现，先以日期排序，再以工资排序)
mysql> select * from employees5 order by hire_date DESC,salary ASC;
+----+-----------+--------+------------+------------+-----------------+----------+--------+--------+
| id | name      | sex    | hire_date  | post       | job_description | salary   | office | dep_id |
+----+-----------+--------+------------+------------+-----------------+----------+--------+--------+
|  7 | emma      | female | 2018-02-06 | sale       | salecc          | 20000.00 |    503 |    102 |
|  8 | christine | female | 2018-02-05 | sale       | salecc          |  2200.00 |    503 |    102 |
|  9 | zhuzhu    | male   | 2018-02-05 | sale       | NULL            |  2200.00 |    503 |    102 |
| 10 | gougou    | male   | 2018-02-05 | sale       |                 |  2200.00 |    503 |    102 |
|  2 | tom       | male   | 2018-02-03 | instructor | teach           |  5500.00 |    501 |    100 |
|  5 | localhost | male   | 2018-02-02 | hr         | hrcc            |   600.00 |    502 |    101 |
|  1 | jack      | male   | 2018-02-02 | instructor | teach           |  5000.00 |    501 |    100 |
|  6 | harry     | male   | 2018-02-02 | hr         | NULL            |  6000.00 |    502 |    101 |
|  4 | alice     | female | 2018-02-02 | instructor | teach           |  7200.00 |    501 |    100 |
|  3 | robin     | male   | 2018-02-02 | instructor | teach           |  8000.00 |    501 |    100 |
+----+-----------+--------+------------+------------+-----------------+----------+--------+--------+
10 rows in set (0.01 sec)

mysql>

（13）限制查询的记录数：select * from 表名 limit 数字;

示例：查询表中的前三行：
mysql> select * from employees5 limit 3;
+----+-------+------+------------+------------+-----------------+---------+--------+--------+
| id | name  | sex  | hire_date  | post       | job_description | salary  | office | dep_id |
+----+-------+------+------------+------------+-----------------+---------+--------+--------+
|  1 | jack  | male | 2018-02-02 | instructor | teach           | 5000.00 |    501 |    100 |
|  2 | tom   | male | 2018-02-03 | instructor | teach           | 5500.00 |    501 |    100 |
|  3 | robin | male | 2018-02-02 | instructor | teach           | 8000.00 |    501 |    100 |
+----+-------+------+------------+------------+-----------------+---------+--------+--------+
3 rows in set (0.00 sec)

mysql>

示例：查询表中前五行：
mysql> select * from employees5 limit 0,5;
+----+-----------+--------+------------+------------+-----------------+---------+--------+--------+
| id | name      | sex    | hire_date  | post       | job_description | salary  | office | dep_id |
+----+-----------+--------+------------+------------+-----------------+---------+--------+--------+
|  1 | jack      | male   | 2018-02-02 | instructor | teach           | 5000.00 |    501 |    100 |
|  2 | tom       | male   | 2018-02-03 | instructor | teach           | 5500.00 |    501 |    100 |
|  3 | robin     | male   | 2018-02-02 | instructor | teach           | 8000.00 |    501 |    100 |
|  4 | alice     | female | 2018-02-02 | instructor | teach           | 7200.00 |    501 |    100 |
|  5 | localhost | male   | 2018-02-02 | hr         | hrcc            |  600.00 |    502 |    101 |
+----+-----------+--------+------------+------------+-----------------+---------+--------+--------+
5 rows in set (0.00 sec)

mysql> 

示例：查询表中第3行以后的5行内容：
mysql> select * from employees5 limit 3,5;
+----+-----------+--------+------------+------------+-----------------+----------+--------+--------+
| id | name      | sex    | hire_date  | post       | job_description | salary   | office | dep_id |
+----+-----------+--------+------------+------------+-----------------+----------+--------+--------+
|  4 | alice     | female | 2018-02-02 | instructor | teach           |  7200.00 |    501 |    100 |
|  5 | localhost | male   | 2018-02-02 | hr         | hrcc            |   600.00 |    502 |    101 |
|  6 | harry     | male   | 2018-02-02 | hr         | NULL            |  6000.00 |    502 |    101 |
|  7 | emma      | female | 2018-02-06 | sale       | salecc          | 20000.00 |    503 |    102 |
|  8 | christine | female | 2018-02-05 | sale       | salecc          |  2200.00 |    503 |    102 |
+----+-----------+--------+------------+------------+-----------------+----------+--------+--------+
5 rows in set (0.00 sec)

mysql>

（14）使用集合函数查询：

示例：查看表的总共行数：
mysql> select count(*) from employees5;
+----------+
| count(*) |
+----------+
|       10 |
+----------+
1 row in set (0.07 sec)

示例：根据条件查询数据所在行数：
mysql> select count(*) from employees5 where dep_id=101;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.01 sec)

示例：根据函数查询数据
mysql> select max(salary) from employees5;
+-------------+
| max(salary) |
+-------------+
|    20000.00 |
+-------------+
1 row in set (0.01 sec)


select min(salary) from employees5;
select avg(salary) from employees5;
select sum(salary) from employees5;
select sum(salary) from employees5 where dep_id=101;

(15)分组查询：group by +字段名表示以字段名为分组查询

示例：group by 关键字和group_concat()函数一起使用
以dep_id为分组查询，结果为dep_id连接name
mysql> select dep_id,group_concat(name) from employees5 group by dep_id;
+--------+------------------------------+
| dep_id | group_concat(name)           |
+--------+------------------------------+
|    100 | jack,tom,robin,alice         |
|    101 | localhost,harry              |
|    102 | emma,christine,zhuzhu,gougou |
+--------+------------------------------+
3 rows in set (0.00 sec)

mysql> select dep_id,group_concat(name) as emp_members from employees5 group by dep_id;
+--------+------------------------------+
| dep_id | emp_members                  |
+--------+------------------------------+
|    100 | jack,tom,robin,alice         |
|    101 | localhost,harry              |
|    102 | emma,christine,zhuzhu,gougou |
+--------+------------------------------+
3 rows in set (0.00 sec)

（这里的 as 表示重新命名）

示例：group by 与集合函数一起使用：
以dep_id分组查询，dep_id对应连接的count(dep_id)数目
mysql> select dep_id,count(dep_id) from employees5 group by dep_id;
+--------+---------------+
| dep_id | count(dep_id) |
+--------+---------------+
|    100 |             4 |
|    101 |             2 |
|    102 |             4 |
+--------+---------------+
3 rows in set (0.00 sec)

(16)使用正则表达式查询：

示例：
查询数据表中名字以 'ali' 开头的所有信息：
mysql> select * from employees5 where name REGEXP '^ali';
+----+-------+--------+------------+------------+-----------------+---------+--------+--------+
| id | name  | sex    | hire_date  | post       | job_description | salary  | office | dep_id |
+----+-------+--------+------------+------------+-----------------+---------+--------+--------+
|  4 | alice | female | 2018-02-02 | instructor | teach           | 7200.00 |    501 |    100 |
+----+-------+--------+------------+------------+-----------------+---------+--------+--------+
1 row in set (0.01 sec)

同理：
mysql> select * from employees5 where name REGEXP 'yun$';
Empty set (0.00 sec)

mysql> select * from employees5 where name REGEXP 'm{2}';
+----+------+--------+------------+------+-----------------+----------+--------+--------+
| id | name | sex    | hire_date  | post | job_description | salary   | office | dep_id |
+----+------+--------+------------+------+-----------------+----------+--------+--------+
|  7 | emma | female | 2018-02-06 | sale | salecc          | 20000.00 |    503 |    102 |
+----+------+--------+------------+------+-----------------+----------+--------+--------+
1 row in set (0.00 sec)

小结：对字符串匹配的方式：

where name = 'tom';

where name like 'to%';

四：多表查询：

（1）素材准备：

mysql> select * from employee6;
+--------+-----------+------+---------+
| emp_id | emp_name  | age  | dept_id |
+--------+-----------+------+---------+
|      1 | localhost |   19 |     200 |
|      2 | tom       |   26 |     201 |
|      3 | jack      |   30 |     201 |
|      4 | alice     |   24 |     202 |
|      5 | robin     |   40 |     200 |
|      6 | natasha   |   28 |     204 |
+--------+-----------+------+---------+
6 rows in set (0.00 sec)


mysql> select * from department6;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|     200 | hr        |
|     201 | it        |
|     202 | sale      |
|     203 | fd        |
+---------+-----------+
4 rows in set (0.00 sec)

（2）多表的连接查询：

交叉连接：生成笛卡尔积，它不使用任何匹配条件

内连接：只连接匹配的行

外连接之左连接：只显示左边表内所有的值，不论在右边表内匹不匹配

外连接之右连接：只显示右边表内所有的值，不论在左边表内匹不匹配

全外连接：包含左，右两个表的全部行

（3）交叉连接：

示例：将employee6表中的emp_name，age，dept_id信息与department6表中的dept_name连接起来
mysql> select employee6.emp_name,employee6.age,employee6.dept_id,department6.dept_name from employee6,department6;
+-----------+------+---------+-----------+
| emp_name  | age  | dept_id | dept_name |
+-----------+------+---------+-----------+
| localhost |   19 |     200 | fd        |
| localhost |   19 |     200 | sale      |
| localhost |   19 |     200 | it        |
| localhost |   19 |     200 | hr        |
| tom       |   26 |     201 | fd        |
| tom       |   26 |     201 | sale      |
| tom       |   26 |     201 | it        |
| tom       |   26 |     201 | hr        |
| jack      |   30 |     201 | fd        |
| jack      |   30 |     201 | sale      |
| jack      |   30 |     201 | it        |
| jack      |   30 |     201 | hr        |
| alice     |   24 |     202 | fd        |
| alice     |   24 |     202 | sale      |
| alice     |   24 |     202 | it        |
| alice     |   24 |     202 | hr        |
| robin     |   40 |     200 | fd        |
| robin     |   40 |     200 | sale      |
| robin     |   40 |     200 | it        |
| robin     |   40 |     200 | hr        |
| natasha   |   28 |     204 | fd        |
| natasha   |   28 |     204 | sale      |
| natasha   |   28 |     204 | it        |
| natasha   |   28 |     204 | hr        |
+-----------+------+---------+-----------+
24 rows in set (0.00 sec)

（4）内连接：

示例：只找出有部门的员工
mysql> select employee6.emp_id,employee6.emp_name,employee6.age,department6.dept_name from employee6,department6 where employee6.dept_id = department6.dept_id;
+--------+-----------+------+-----------+
| emp_id | emp_name  | age  | dept_name |
+--------+-----------+------+-----------+
|      1 | localhost |   19 | hr        |
|      2 | tom       |   26 | it        |
|      3 | jack      |   30 | it        |
|      4 | alice     |   24 | sale      |
|      5 | robin     |   40 | hr        |
+--------+-----------+------+-----------+
5 rows in set (0.00 sec)

（5）外连接语法：SELECT 字段列表 FROM 表1 LIFT|REGHT JOIN 表2 ON 表1.字段 = 表2.字段

1）外连接（左连接 left join）

示例：找出所有员工及所属的部门，包括没有部门的员工
mysql> select emp_id,emp_name,dept_name from employee6 left join department6 on employee6.dept_id = department6.dept_id;
+--------+-----------+-----------+
| emp_id | emp_name  | dept_name |
+--------+-----------+-----------+
|      1 | localhost | hr        |
|      2 | tom       | it        |
|      3 | jack      | it        |
|      4 | alice     | sale      |
|      5 | robin     | hr        |
|      6 | natasha   | NULL      |
+--------+-----------+-----------+
6 rows in set (0.00 sec)

2）外连接（右连接right join）

示例：找出所有部门包含的员工，包括空部门
mysql> select emp_id,emp_name,dept_name from employee6 right join department6 on employee6.dept_id = department6.dept_id;
+--------+-----------+-----------+
| emp_id | emp_name  | dept_name |
+--------+-----------+-----------+
|      5 | robin     | hr        |
|      1 | localhost | hr        |
|      3 | jack      | it        |
|      2 | tom       | it        |
|      4 | alice     | sale      |
|   NULL | NULL      | fd        |
+--------+-----------+-----------+
6 rows in set (0.00 sec)

3）全外连接（full join）

mysql> select * from employee6 full join department6;
+--------+-----------+------+---------+---------+-----------+
| emp_id | emp_name  | age  | dept_id | dept_id | dept_name |
+--------+-----------+------+---------+---------+-----------+
|      1 | localhost |   19 |     200 |     203 | fd        |
|      1 | localhost |   19 |     200 |     202 | sale      |
|      1 | localhost |   19 |     200 |     201 | it        |
|      1 | localhost |   19 |     200 |     200 | hr        |
|      2 | tom       |   26 |     201 |     203 | fd        |
|      2 | tom       |   26 |     201 |     202 | sale      |
|      2 | tom       |   26 |     201 |     201 | it        |
|      2 | tom       |   26 |     201 |     200 | hr        |
|      3 | jack      |   30 |     201 |     203 | fd        |
|      3 | jack      |   30 |     201 |     202 | sale      |
|      3 | jack      |   30 |     201 |     201 | it        |
|      3 | jack      |   30 |     201 |     200 | hr        |
|      4 | alice     |   24 |     202 |     203 | fd        |
|      4 | alice     |   24 |     202 |     202 | sale      |
|      4 | alice     |   24 |     202 |     201 | it        |
|      4 | alice     |   24 |     202 |     200 | hr        |
|      5 | robin     |   40 |     200 |     203 | fd        |
|      5 | robin     |   40 |     200 |     202 | sale      |
|      5 | robin     |   40 |     200 |     201 | it        |
|      5 | robin     |   40 |     200 |     200 | hr        |
|      6 | natasha   |   28 |     204 |     203 | fd        |
|      6 | natasha   |   28 |     204 |     202 | sale      |
|      6 | natasha   |   28 |     204 |     201 | it        |
|      6 | natasha   |   28 |     204 |     200 | hr        |
+--------+-----------+------+---------+---------+-----------+
24 rows in set (0.00 sec)

（6）复合条件连接查询

示例1：以内连接的方式查询employee6表和department6表，并且employee6表中的age字段值必须大于25
（找出公司所有部门中年龄大于25岁的员工）
mysql> select emp_id,emp_name,age,dept_name from employee6,department6 where employee6.dept_id = department6.dept_id and age > 25;
+--------+----------+------+-----------+
| emp_id | emp_name | age  | dept_name |
+--------+----------+------+-----------+
|      5 | robin    |   40 | hr        |
|      3 | jack     |   30 | it        |
|      2 | tom      |   26 | it        |
+--------+----------+------+-----------+
3 rows in set (0.00 sec)

示例2：以内连接的方式查询employee6和department6表，并以age字段的升序方式显示
mysql> select emp_id,emp_name,age,dept_name from employee6,department6 where employee6.dept_id = department6.dept_id order by age asc;
+--------+-----------+------+-----------+
| emp_id | emp_name  | age  | dept_name |
+--------+-----------+------+-----------+
|      1 | localhost |   19 | hr        |
|      4 | alice     |   24 | sale      |
|      2 | tom       |   26 | it        |
|      3 | jack      |   30 | it        |
|      5 | robin     |   40 | hr        |
+--------+-----------+------+-----------+
5 rows in set (0.00 sec)

（7）子查询：

子查询是将一个查询语句嵌套在另一个查询语句中。

内层查询语句的查询结果，可以为外层查询语句提供查询条件。

子查询中可以包含：IN，NOT IN，ANY，EXISTS 和 NOT EXISTS等关键字。

还可以包含比较远算符：=，!=，>，< 等。

（8）带IN关键字的子查询：

示例：查询employee6表，但dept_id必须在department表中出现过
mysql> select * from employee6 where dept_id IN (select dept_id from department6);
+--------+-----------+------+---------+
| emp_id | emp_name  | age  | dept_id |
+--------+-----------+------+---------+
|      1 | localhost |   19 |     200 |
|      2 | tom       |   26 |     201 |
|      3 | jack      |   30 |     201 |
|      4 | alice     |   24 |     202 |
|      5 | robin     |   40 |     200 |
+--------+-----------+------+---------+
5 rows in set (0.00 sec)

（9）带比较运算符的子查询：=，!=，>，>=，<，<=，<>

示例：查询员工年龄大于等于25岁的部门
mysql> select dept_id,dept_name from department6 where dept_id IN (select DISTINCT dept_id from employee6 where age >=25);
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|     200 | hr        |
|     201 | it        |
+---------+-----------+
2 rows in set (0.00 sec)

（10）带EXISTS关键字的子查询：