慢谈MYSQL常用SQL语句

目录

1、SQL语句分类

2、DDL语句

3、DML语句

4、DCL语句

5、事务相关语句

6、mysql查询

    6.1、单表简单查询

    6.2、多表组合查询

        6.2.1、联结查询（交叉联结，内联结，外联结（左外联结、右外联结））

        6.2.2、联合查询（UNION）

        6.2.3、内联结

    6.3、子查询

        6.3.1、WHERE子查询

        6.3.2、FROM子查询

1、SQL语句分类

DDL：数据定义语言，用来定义数据库对象，包括数据库、表、索引、存储过程、存储函数、约束、触发器、事件调度器等

DML：数据操作语言，包括CRUD（Insert，Select，Replace，Update，Delete）

DCL：数据控制语言，包括grant，revoke

与事务相关的语言：start transaction，commit，rollback，save point

2、DDL语句

2.1、数据库操作

a)、创建数据库：

语法：mysql> create {database | schema} [if not exists] 数据库名;      #“if not exists”是用来作条件判断

举例：

mysql> create database if not exists mydb;  #创建一个mydb数据库
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb               |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)
mysql> create schema if not exists mydb;  #尝试再次建立mydb数据库
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> show warnings;  #查看警告信息
+-------+------+-----------------------------------------------+
| Level | Code | Message                                       |
+-------+------+-----------------------------------------------+
| Note  | 1007 | Can't create database 'mydb'; database exists |
+-------+------+-----------------------------------------------+
1 row in set (0.00 sec)

b)、删除数据库：

语法：

mysql> drop {database | schema} [if exists] 数据库名;

举例：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb               |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)
mysql> drop schema if exists mydb;
Query OK, 0 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

c)、修改数据库

语法：

mysql> alter {database | schema} 数据库名 [DEFAULT] CHARACTER SET [=] charset_name ｜[DEFAULT] COLLATE [=] collation_name

mysql> alter {database | schema} 数据库名 upgrade data directory name;  #此语句用在数据进行升级后用来升级数据字典

2.2、数据表操作

a)、创建字段自定义表

语法：

mysql> create table [if not exists] 表名 (字段1 字段定义,字段2 字段定义, ...)  众多选项

mysql> help create table;  #获取表创建的帮助。

举例：

mysql> use test;
mysql> create table if not exists tb1 (id int unsigned auto_increment primary key,name char(30) not null,age tinyint unsigned,class char(20) not null ) character set = utf8 engine = innodb;

以下三个sql语句都可查数据库中的表状态：

mysql> show table status from test\G
mysql> show table status in test\G
mysql> show table status like 'tb1';

b)、以select语句查询到的数据为结果来创建一张表

mysql> use mydb1;
mysql> create table if not exists tb2 select user,host,password from mysql.user;
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> select * from tb2;
+------+------------+-------------------------------------------+
| user | host       | password                                  |
+------+------------+-------------------------------------------+
| root | localhost  | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root | db\_server | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root | 127.0.0.1  | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+------+------------+-------------------------------------------+
3 rows in set (0.00 sec)
注：这种方式创建出来的表，表的表结构与源表的表结构是不同的，也就是说字段中的定义和修饰符不会全部在新表中都有

c)、模仿一张表的表结构来创建一张只有表结构的表

mysql> create table if not exists tb3 like tb2;
Query OK, 0 rows affected (0.03 sec)
mysql> desc tb3;
+----------+----------+------+-----+---------+-------+
| Field    | Type     | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| user     | char(16) | NO   |     |         |       |
| host     | char(60) | NO   |     |         |       |
| password | char(41) | NO   |     |         |       |
+----------+----------+------+-----+---------+-------+
3 rows in set (0.03 sec)
mysql> desc tb2;
+----------+----------+------+-----+---------+-------+
| Field    | Type     | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| user     | char(16) | NO   |     |         |       |
| host     | char(60) | NO   |     |         |       |
| password | char(41) | NO   |     |         |       |
+----------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> select * from tb3;   #tb3中是没有数据的
Empty set (0.02 sec)

开动脑筋：

怎样从一个表中挑选一些数据生成一张新表，且新表的表结构(字段定义、修饰符等)与源表是完成相同的。

实现方式分两部来完成，先创建和源表一样表结构的表，再以select的方式插入数据到此表。

实现：

mysql> DESC tb1;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type                | Null | Key | Default | Extra          |
+-------+---------------------+------+-----+---------+----------------+
| id    | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| name  | char(20)            | NO   |     | NULL    |                |
| age   | tinyint(3) unsigned | NO   |     | NULL    |                |
+-------+---------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM tb1;
+----+-------+-----+
| id | name  | age |
+----+-------+-----+
|  1 | jack  |  29 |
|  2 | jason |  39 |
|  3 | tom   |  37 |
|  4 | luck  |  22 |
|  7 | root  |   0 |
|  8 | root  |   0 |
|  9 | root  |   0 |
+----+-------+-----+
7 rows in set (0.01 sec)
mysql> CREATE TABLE IF NOT EXISTS tb2 LIKE tb1;  #创建tb2以tb1的表结构为源
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> DESC tb2;  #tb2的表结构和tb1的表结构是完成相同的
+-------+---------------------+------+-----+---------+----------------+
| Field | Type                | Null | Key | Default | Extra          |
+-------+---------------------+------+-----+---------+----------------+
| id    | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| name  | char(20)            | NO   |     | NULL    |                |
| age   | tinyint(3) unsigned | NO   |     | NULL    |                |
+-------+---------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> INSERT INTO tb2 SELECT * FROM tb1 WHERE age>30; #只把tb1中年龄大于30的数据插入到新表中
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> SELECT * FROM tb2;  #验证数据
+----+-------+-----+
| id | name  | age |
+----+-------+-----+
|  2 | jason |  39 |
|  3 | tom   |  37 |
+----+-------+-----+
2 rows in set (0.00 sec)
这样就可实现保持源表的表结构提取所需数据。

d)、删除表

mysql> drop table [if exists] 表名;

e)、修改表名称、字段、定义、修饰符等

用help alter table来获取帮助

新增字段，删除字段

modify 修改指定字段定义：

change 修改字段名

rename  新表名 

举例：

mysql> SELECT * FROM tb2;
+------+------+
| id   | name |
+------+------+
|    1 | Zcj  |
|    2 | Tom  |
+------+------+
2 rows in set (0.00 sec)
mysql> DESC tb2;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | YES  |     | NULL    |       |
| name  | char(30) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
mysql> ALTER TABLE tb2 ADD Age TINYINT UNSIGNED AFTER id;
  #增加“Age”字段
Query OK, 2 rows affected (0.04 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> SELECT * FROM tb2;
+------+------+------+
| id   | Age  | name |
+------+------+------+
|    1 | NULL | Zcj  |
|    2 | NULL | Tom  |
+------+------+------+
mysql> ALTER TABLE tb2 DROP Age;
 #删除字段“Age”
Query OK, 2 rows affected (0.06 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> SELECT * FROM tb2;
+------+------+
| id   | name |
+------+------+
|    1 | Zcj  |
|    2 | Tom  |
+------+------+
mysql> ALTER TABLE tb2 MODIFY id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY;
 #修改id字段的定义
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> DESC tb2;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | char(30)         | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
mysql> ALTER TABLE tb2 CHANGE name fullname CHAR(50) NOT NULL;
 #修改字段name的名称及定义
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> DESC tb2;
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id       | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| fullname | char(50)         | NO   |     | NULL    |                |
+----------+------------------+------+-----+---------+----------------+
mysql> ALTER TABLE tb2 RENAME newtb2;
 #修改表名
Query OK, 0 rows affected (0.00 sec)
mysql> DESC newtb2;
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id       | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| fullname | char(50)         | NO   |     | NULL    |                |
+----------+------------------+------+-----+---------+----------------+

3、DML语句

3.1、insert：用来插入数据

语法：

mysql> insert into 表名 [(字段名，...)]  {values | value} (值,...)

mysql> insert into 表名 set 字段名=值，...

举例：

mysql> CREATE TABLE IF NOT EXISTS tb1 (id int unsigned auto_increment primary key,name char(20) not null,age tinyint unsigned not null);  #先创建一张表

a)、插入多行数据

mysql> INSERT INTO tb1 (name,age) VALUES ('jack',29),('jason',39),('tom',37);

b)、插入一行数据

mysql> INSERT INTO tb1 SET name='luck',age=22;
mysql> SELECT * FROM tb1; #查看表数据
+----+-------+-----+
| id | name  | age |
+----+-------+-----+
|  1 | jack  |  29 |
|  2 | jason |  39 |
|  3 | tom   |  37 |
|  4 | luck  |  22 |
+----+-------+-----+
4 rows in set (0.00 sec)

c)、以select结果插入数据

mysql> INSERT INTO tb1 (name) SELECT user FROM mysql.user; #把user表中的用户信息检索出来填充到tb1表中的name字段
Query OK, 3 rows affected, 1 warning (0.08 sec)
Records: 3  Duplicates: 0  Warnings: 1
mysql> SHOW WARNINGS;  #有个警告信息，因为创建表时age字段是not null的,只填充了name字段，没有age字段的相应数据
+---------+------+------------------------------------------+
| Level   | Code | Message                                  |
+---------+------+------------------------------------------+
| Warning | 1364 | Field 'age' doesn't have a default value |
+---------+------+------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM tb1;  #自动把age字段填充为0
+----+-------+-----+
| id | name  | age |
+----+-------+-----+
|  1 | jack  |  29 |
|  2 | jason |  39 |
|  3 | tom   |  37 |
|  4 | luck  |  22 |
|  7 | root  |   0 |
|  8 | root  |   0 |
|  9 | root  |   0 |
+----+-------+-----+
7 rows in set (0.08 sec)

3.2、replace：用来替换表的数据，如果源表中没有此数据则增加数据

mysql> DESC tb2; #name字段是唯一键索引
+-------+---------------------+------+-----+---------+----------------+
| Field | Type                | Null | Key | Default | Extra          |
+-------+---------------------+------+-----+---------+----------------+
| id    | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| name  | char(20)            | NO   | UNI | NULL    |                |
| age   | tinyint(3) unsigned | NO   |     | NULL    |                |
+-------+---------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM tb2;
+----+-------+-----+
| id | name  | age |
+----+-------+-----+
|  1 | jack  |  29 |
|  2 | jason |  39 |
|  3 | tom   |  37 |
|  4 | luck  |  22 |
+----+-------+-----+
4 rows in set (0.00 sec)
mysql> REPLACE INTO tb2 SET name='jack',age=33; #因原表中有jack这个用户，replace这个命令会先删除原来的那一行，再新增加一行，所以会有两行受到影响
Query OK, 2 rows affected (0.04 sec)
mysql> REPLACE INTO tb2 SET name='zhaochj',age=29; #因原表中没有zhaochj这个用户，所以直接在最后增加
Query OK, 1 row affected (0.03 sec)
mysql> SELECT * FROM tb2;
+----+---------+-----+
| id | name    | age |
+----+---------+-----+
|  2 | jason   |  39 |
|  3 | tom     |  37 |
|  4 | luck    |  22 |
|  5 | jack    |  33 |
|  6 | zhaochj |  29 |
+----+---------+-----+
5 rows in set (0.02 sec)

3.3、update：修改表中的数据

mysql> SELECT * FROM tb2;
+----+---------+-----+
| id | name    | age |
+----+---------+-----+
|  2 | jason   |  39 |
|  3 | tom     |  37 |
|  4 | luck    |  22 |
|  5 | jack    |  33 |
|  6 | zhaochj |  29 |
+----+---------+-----+
5 rows in set (0.02 sec)
mysql> UPDATE tb2 SET age=33 WHERE name='zhaochj';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> UPDATE tb2 SET age=11 LIMIT 2; #修改前两行的年龄为11
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0
mysql> SELECT * FROM tb2;
+----+---------+-----+
| id | name    | age |
+----+---------+-----+
|  2 | jason   |  11 |
|  3 | tom     |  11 |
|  4 | luck    |  22 |
|  5 | jack    |  33 |
|  6 | zhaochj |  33 |
+----+---------+-----+
5 rows in set (0.02 sec)

3.4、delete 删除表中数据

mysql> SELECT * FROM tb2;
+----+---------+-----+
| id | name    | age |
+----+---------+-----+
|  2 | jason   |  11 |
|  3 | tom     |  11 |
|  4 | luck    |  22 |
|  5 | jack    |  33 |
|  6 | zhaochj |  33 |
+----+---------+-----+
5 rows in set (0.02 sec)
mysql>
mysql> DELETE FROM tb2 WHERE name='zhaochj';  #加上where子句限定删除的范围
Query OK, 1 row affected (0.38 sec)
mysql> SELECT * FROM tb2;
+----+-------+-----+
| id | name  | age |
+----+-------+-----+
|  2 | jason |  11 |
|  3 | tom   |  11 |
|  4 | luck  |  22 |
|  5 | jack  |  33 |
+----+-------+-----+
4 rows in set (0.00 sec)
mysql> DELETE FROM tb2;  #这样直接把表中的数据全部删除
Query OK, 4 rows affected (0.01 sec)
mysql> SELECT * FROM tb2;
Empty set (0.00 sec)
mysql> INSERT INTO tb2 (name,age) VALUES ('zcj',29); #试着插入一行数据
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM tb2; #查看数据发现id号不是从"1"开始的，这是因为字段“id”被定义成了“auto_increment”了
+----+------+-----+
| id | name | age |
+----+------+-----+
|  7 | zcj  |  29 |
+----+------+-----+
1 row in set (0.03 sec)
mysql> TRUNCATE TABLE tb2;  #清空一张表，应该用这个命令，表示重置表，会清空“auto_increment”的计数器
Query OK, 0 rows affected (0.05 sec)
mysql> INSERT INTO tb2 (name,age) VALUES ('zcj',29);
Query OK, 1 row affected (0.03 sec)
mysql> SELECT * FROM tb2;  #id号又从“1”开始
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | zcj  |  29 |
+----+------+-----+
1 row in set (0.00 sec)

4、DCL语句

4.1、权限赋予

mysql> GRANT select ON mydb2.tb1 TO 'mydb2user1'@'%' IDENTIFIED BY '111111';
#对mydb2数据库中的tb1表授予mydb2user1用户可在任意主机以6个1为密码登陆，但权限只有select
mysql> SHOW GRANTS FOR mydb2user1; #显示mydb2user1用户的权限信息
+-----------------------------------------------------------------------------------------------------------+
| Grants for mydb2user1@%                                                                                   |
+-----------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'mydb2user1'@'%' IDENTIFIED BY PASSWORD '*FD571203974BA9AFE270FE62151AE967ECA5E0AA' |
| GRANT SELECT ON `mydb2`.`tb1` TO 'mydb2user1'@'%'                                                         |
+-----------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
验证权限是否生效：
[root@mariadb ~]# mysql -umydb2user1 -p111111  #以mydb2user1登陆
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb2              |
| test               |
+--------------------+
mysql> USE mydb2;
Database changed
mysql> SHOW TABLES;
+-----------------+
| Tables_in_mydb2 |
+-----------------+
| tb1             |
+-----------------+
mysql> SELECT * FROM tb1;
+------+------+
| id   | name |
+------+------+
|    1 | Zcj  |
|    2 | Tom  |
|    3 | Jack |
+------+------+
mysql> INSERT INTO tb1 (id,name) VALUES (4,'Cora');  #没有权限插入数据
ERROR 1142 (42000): INSERT command denied to user 'mydb2user1'@'localhost' for table 'tb1'
mysql> GRANT insert ON mydb2.tb1 TO 'mydb2user1'@'%'; #再赋予insert权限
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW GRANTS FOR mydb2user1;
+-----------------------------------------------------------------------------------------------------------+
| Grants for mydb2user1@%                                                                                   |
+-----------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'mydb2user1'@'%' IDENTIFIED BY PASSWORD '*FD571203974BA9AFE270FE62151AE967ECA5E0AA' |
| GRANT SELECT, INSERT ON `mydb2`.`tb1` TO 'mydb2user1'@'%'                                                 |
+-----------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
[root@mariadb ~]# mysql -umydb2user1 -p111111  #再以mydb2user1登陆
mysql> INSERT INTO tb1 (id,name) VALUES (4,'Cora');
Query OK, 1 row affected (0.07 sec)
mysql> SELECT * FROM tb1; #数据已插入
+------+------+
| id   | name |
+------+------+
|    1 | Zcj  |
|    2 | Tom  |
|    3 | Jack |
|    4 | Cora |
+------+------+
4 rows in set (0.00 sec)

4.2、回收权限

[root@mariadb ~]# mysql -uroot -p123456 #以root登陆
mysql> REVOKE insert ON mydb2.tb1 FROM mydb2user1;
Query OK, 0 rows affected (0.00 sec)
[root@mariadb ~]# mysql -umydb2user1 -p111111 #以mydb2user1登陆
mysql> INSERT INTO mydb2.tb1 (id,name) VALUES (5,'Lucky'); #无法插入数据，insert权限已被回收
ERROR 1142 (42000): INSERT command denied to user 'mydb2user1'@'localhost' for table 'tb1'

5、事务相关语句

mysql> START TRANSACTION; #开始事务

mysql> SAVEPOINT point_name; #保存一个事务的位置,以方便撤销事务

mysql> ROLLBACK TO point_name;  #回滚到一个点

mysql> ROLLBACK;  #回滚到开始事务时的状态

mysql> COMMIT;  #提交事务

举例：

mysql> START TRANSACTION;
 #开始事务
Query OK, 0 rows affected (0.02 sec)
mysql> SELECT * FROM tb1;
+------+------+
| id   | name |
+------+------+
|    1 | Zcj  |
|    2 | Tom  |
|    3 | Jack |
|    4 | Cora |
+------+------+
4 rows in set (0.00 sec)
mysql> INSERT INTO tb1 (id,name) VALUES (5,'Lucky');
 #插入一行数据
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM tb1;
+------+-------+
| id   | name  |
+------+-------+
|    1 | Zcj   |
|    2 | Tom   |
|    3 | Jack  |
|    4 | Cora  |
|    5 | Lucky |
+------+-------+
5 rows in set (0.00 sec)
mysql> SAVEPOINT p1;
 #在有id为5这行数据时创建一个保存点
Query OK, 0 rows affected (0.01 sec)
mysql> DELETE FROM tb1 WHERE id = 2;
 #删除一行数据
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM tb1;
+------+-------+
| id   | name  |
+------+-------+
|    1 | Zcj   |
|    3 | Jack  |
|    4 | Cora  |
|    5 | Lucky |
+------+-------+
4 rows in set (0.00 sec)
mysql> ROLLBACK TO p1;
 #回滚到保存点p1的状态
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM tb1;
+------+-------+
| id   | name  |
+------+-------+
|    1 | Zcj   |
|    2 | Tom   |
|    3 | Jack  |
|    4 | Cora  |
|    5 | Lucky |
+------+-------+
5 rows in set (0.00 sec)
mysql> ROLLBACK;
 #回滚到事务开始时的状态
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM tb1;
+------+------+
| id   | name |
+------+------+
|    1 | Zcj  |
|    2 | Tom  |
|    3 | Jack |
|    4 | Cora |
+------+------+
4 rows in set (0.00 sec)
mysql> COMMIT;
 #提交事务
Query OK, 0 rows affected (0.00 sec)

6、mysql查询

为演示各种SELECT语句，建立以下几个表：

一)、表名为“students_tb”，字段为：StudentID，Name，Age，Gender，ClassID，分别存放学生的名字、年龄、性别、有班级的ID号，具体的班级存放在另一张表中

二)、表名为“classes_tb”，字段为：ClassID，Class，TeacherID，分别存放班级的ID号，具体的班级名，这个班级是哪个老师负责的ID号，具体的负责的老师存放在另一张表中

三)、表名为“teacher_tb”，字段为：TeacheID，Name，Age，Gender，分别存放老师的ID号，老师的名字，年龄，性别

mysql> USE mydb1;
mysql> CREATE TABLE IF NOT EXISTS students_tb (StudentID SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,Name CHAR(30) NOT NULL,Age TINYINT UNSIGNED,Gender ENUM('M','F'),ClassID TINYINT UNSIGNED NOT NULL);
mysql> INSERT INTO students_tb (Name,Age,Gender,ClassID) VALUES ('Tom',17,'M',1),('Jack',18,'M',3),('Lucy',21,'F',6),('Jimima',15,'F',4),('Jimmy',30,'M',9),('Jim',26,'M',7);
mysql> SELECT * FROM students_tb;
+-----------+--------+------+--------+---------+
| StudentID | Name   | Age  | Gender | ClassID |
+-----------+--------+------+--------+---------+
|         1 | Tom    |   17 | M      |       1 |
|         2 | Jack   |   18 | M      |       3 |
|         3 | Lucy   |   21 | F      |       6 |
|         4 | Jimima |   15 | F      |       4 |
|         5 | Jimmy  |   30 | M      |       9 |
|         6 | Jim    |   26 | M      |       7 |
+-----------+--------+------+--------+---------+
mysql> CREATE TABLE IF NOT EXISTS classes_tb (ClassID TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,Class CHAR(20) NOT NULL,TeacherID TINYINT UNSIGNED NOT NULL);
mysql> ALTER TABLE classes_tb ADD UNIQUE KEY (Class);  #这个表中的班别应该是唯一的，所以用此sql语句来修改
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> DESC classes_tb;
+-----------+---------------------+------+-----+---------+----------------+
| Field     | Type                | Null | Key | Default | Extra          |
+-----------+---------------------+------+-----+---------+----------------+
| ClassID   | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |
| Class     | char(20)            | NO   | UNI | NULL    |                |
| TeacherID | tinyint(3) unsigned | NO   |     | NULL    |                |
+-----------+---------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> INSERT INTO classes_tb (Class,TeacherID) VALUES ('class1',5),('class2',2),('class3',5),('class4',2),('class5',4),('class6',1);
mysql> SELECT * FROM classes_tb;
+---------+--------+-----------+
| ClassID | Class  | TeacherID |
+---------+--------+-----------+
|       1 | class1 |         5 |
|       2 | class2 |         2 |
|       3 | class3 |         5 |
|       4 | class4 |         2 |
|       5 | class5 |         4 |
|       6 | class6 |         1 |
+---------+--------+-----------+
mysql> CREATE TABLE IF NOT EXISTS teacher_tb (TeacherID TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,Name CHAR(30) NOT NULL,Age TINYINT UNSIGNED,Gender ENUM('M','F'));
mysql> INSERT INTO teacher_tb (Name,Age,Gender) VALUES ('Wang baoqiang',30,'M'),('Zhang shanfeng',32,'F'),('Zhao qiang',40,'M'),('Ying hui',50,'M'),('Feng bing',46,'M'),('Qian qiang',37,'M'),('Shun bin',68,'M');
mysql> SELECT * FROM teacher_tb;
+-----------+----------------+------+--------+
| TeacherID | Name           | Age  | Gender |
+-----------+----------------+------+--------+
|         1 | Wang baoqiang  |   30 | M      |
|         2 | Zhang shanfeng |   32 | F      |
|         3 | Zhao qiang     |   40 | M      |
|         4 | Ying hui       |   50 | M      |
|         5 | Feng bing      |   46 | M      |
|         6 | Qian qiang     |   37 | M      |
|         7 | Shun bin       |   68 | M      |
+-----------+----------------+------+--------+
三个表创建及数据插入完成。

6.1、单表简单查询

语法：

SELECT 字段，字段，... FROM 表名 WHERE 子句;

举例：

查询students_tb表中前5个学生的姓名和年龄

mysql> SELECT Name,Age FROM students_tb LIMIT 5;
+--------+------+
| Name   | Age  |
+--------+------+
| Tom    |   17 |
| Jack   |   18 |
| Lucy   |   21 |
| Jimima |   15 |
| Jimmy  |   30 |
+--------+------+

a)、比较运算：>,<,>=,<=,!=,<>,<=>

在students_tb表中查询年龄大于20的学生有哪些：

mysql> SELECT Name,Age FROM students_tb WHERE Age>20;
+-------+------+
| Name  | Age  |
+-------+------+
| Lucy  |   21 |
| Jimmy |   30 |
| Jim   |   26 |
+-------+------+

在students_tb表中查询年龄不等于30的学生有哪些：

mysql> SELECT Name,Age FROM students_tb WHERE Age!=30;
+--------+------+
| Name   | Age  |
+--------+------+
| Tom    |   17 |
| Jack   |   18 |
| Lucy   |   21 |
| Jimima |   15 |
| Jim    |   26 |
+--------+------+

在students_tb表中查询年龄不等于30和不等于15的学生有哪些：

mysql> SELECT Name,Age FROM students_tb WHERE Age!=30 and Age!=15;
+------+------+
| Name | Age  |
+------+------+
| Tom  |   17 |
| Jack |   18 |
| Lucy |   21 |
| Jim  |   26 |
+------+------+

b)、BETWEEN...AND..表示在一个范围内的数据

在students_tb表中查询年龄在20到30之间的学生：

mysql> SELECT Name,Age FROM students_tb WHERE Age BETWEEN 20 AND 30;
+-------+------+
| Name  | Age  |
+-------+------+
| Lucy  |   21 |
| Jimmy |   30 |
| Jim   |   26 |
+-------+------+

c)、IN (元素，...)，表示在一个范围内匹配

在students_tb表中查询年龄是18或26或30的学生：

mysql> SELECT Name,Age FROM students_tb WHERE Age IN (18,26,30);  #表示查询一个集合内的数据
+-------+------+
| Name  | Age  |
+-------+------+
| Jack  |   18 |
| Jimmy |   30 |
| Jim   |   26 |
+-------+------+

d)、LIKE子句与RLIKE子句

在students_tb表中查询姓名中有字母“J”，且在后边跟了任意个字符的用户：

mysql> SELECT Name,Age FROM students_tb WHERE Name LIKE 'J%';  
+--------+------+
| Name   | Age  |
+--------+------+
| Jack   |   18 |
| Jimima |   15 |
| Jimmy  |   30 |
| Jim    |   26 |
+--------+------+
#通配符匹配有“%”，“_” ，“%”表示匹配任意个任意字符，“_”表示匹配任意单个字符

在students_tb表中查询姓名以“J”开头，后接任意字符,再接有一个m或多个m，再后接任意个字符，并且以y结尾的学生：

mysql> SELECT Name,Age FROM students_tb WHERE Name RLIKE '^J.*m+.*y$';  #正则匹配
+-------+------+
| Name  | Age  |
+-------+------+
| Jimmy |   30 |
+-------+------+

e)、逻辑运算符（NOT,AND,OR）,优先级依次降低

在students_tb表中查询学生姓名以“J”开头，并且年龄大于20的信息：

mysql> SELECT Name,Age FROM students_tb WHERE Name RLIKE '^J' && Age>20;  #“&&”=“AND”
+-------+------+
| Name  | Age  |
+-------+------+
| Jimmy |   30 |
| Jim   |   26 |
+-------+------+

在students_tb表中查询年龄是30或者是15的学生：

mysql> SELECT Name,Age FROM students_tb WHERE Age=30 OR Age=15;   #“OR”="||"
+--------+------+
| Name   | Age  |
+--------+------+
| Jimima |   15 |
| Jimmy  |   30 |
+--------+------+

在students_tb表中查询性别不是“F”的学生：

mysql> SELECT Name,Gender FROM students_tb WHERE NOT Gender='F';
+-------+--------+
| Name  | Gender |
+-------+--------+
| Tom   | M      |
| Jack  | M      |
| Jimmy | M      |
| Jim   | M      |
+-------+--------+

在students_tb表中查询年龄在10到26之间，姓名以“J”或“L”开头的女生：

mysql> SELECT Name,Age,Gender FROM students_tb WHERE Age>=10 AND Age<=26 AND (Name LIKE 'J%' OR Name LIKE '%L') AND Gender='F';
+--------+------+--------+
| Name   | Age  | Gender |
+--------+------+--------+
| Jimima |   15 | F      |
+--------+------+--------+
注：逻辑运算是有优先级的，如果不用括号那得到的结果与预想的不一样。

f)、聚合函数：MAX(),MIN(),AVG(),SUM(),COUNT()

在students_tb表中查询年龄最大的学生是谁：

mysql> SELECT Name,Age FROM students_tb WHERE Age = (SELECT MAX(Age) FROM students_tb);
+-------+------+
| Name  | Age  |
+-------+------+
| Jimmy |   30 |
+-------+------+

在students_tb表中查询年龄最小的学生是谁：

mysql> SELECT Name,Age FROM students_tb WHERE Age = (SELECT MIN(Age) FROM students_tb);
+--------+------+
| Name   | Age  |
+--------+------+
| Jimima |   15 |
+--------+------+

在students_tb表中求所有学生年龄的平均值

mysql> SELECT AVG(Age) FROM students_tb;
+----------+
| AVG(Age) |
+----------+
|  21.1667 |
+----------+

g)、GROUP BY  分组，及HAVING子句

在students_tb表中求男生、女生年龄分别的平均值：

mysql> SELECT Gender,AVG(Age) FROM students_tb GROUP BY Gender;
+--------+----------+
| Gender | AVG(Age) |
+--------+----------+
| M      |  22.7500 |
| F      |  18.0000 |
+--------+----------+

显示students_tb表中分别显示男生，女生的最大年龄：

mysql> SELECT Gender,MAX(Age) FROM students_tb GROUP BY Gender;
+--------+----------+
| Gender | MAX(Age) |
+--------+----------+
| M      |       30 |
| F      |       21 |
+--------+----------+

为了演示HAVING子句，现在加入以下数据：

mysql> INSERT INTO students_tb (Name,Age,Gender,ClassID) VALUES ('Cora',25,'F',6),('Echo',58,'F',7),('张三',47,'M',3);
mysql> SELECT * FROM students_tb;
+-----------+--------+------+--------+---------+
| StudentID | Name   | Age  | Gender | ClassID |
+-----------+--------+------+--------+---------+
|         1 | Tom    |   17 | M      |       1 |
|         2 | Jack   |   18 | M      |       3 |
|         3 | Lucy   |   21 | F      |       6 |
|         4 | Jimima |   15 | F      |       4 |
|         5 | Jimmy  |   30 | M      |       9 |
|         6 | Jim    |   26 | M      |       7 |
|         7 | Cora   |   25 | F      |       6 |
|         8 | Echo   |   58 | F      |       7 |
|         9 | 张三   |   47 | M      |       3 |
+-----------+--------+------+--------+---------+

以班级排序，显示students_tb表中各个班级中年龄最小的同学，只显示年龄小于20的的班级：

mysql> SELECT ClassID,Name,MIN(Age) FROM students_tb GROUP BY ClassID;
+---------+--------+----------+
| ClassID | Name   | MIN(Age) |
+---------+--------+----------+
|       1 | Tom    |       17 |
|       3 | Jack   |       18 |
|       4 | Jimima |       15 |
|       6 | Lucy   |       21 |
|       7 | Jim    |       26 |
|       9 | Jimmy  |       30 |
+---------+--------+----------+
mysql> SELECT ClassID,Name,MIN(Age) FROM students_tb GROUP BY ClassID HAVING MIN(Age)<20;
+---------+--------+----------+
| ClassID | Name   | MIN(Age) |
+---------+--------+----------+
|       1 | Tom    |       17 |
|       3 | Jack   |       18 |
|       4 | Jimima |       15 |
+---------+--------+----------+

h)、ORDER BY排序

以性别分组，分别计算男女生的年龄之和，并逆序排列：

mysql> SELECT Gender,SUM(Age) FROM students_tb GROUP BY Gender ORDER BY SUM(Age) DESC;
+--------+----------+
| Gender | SUM(Age) |
+--------+----------+
| M      |      138 |
| F      |      119 |
+--------+----------+

查看students_tb表中男女生各是多少：

mysql> SELECT Gender,COUNT(Name) FROM students_tb GROUP BY Gender;
+--------+-------------+
| Gender | COUNT(Name) |
+--------+-------------+
| M      |           5 |
| F      |           4 |
+--------+-------------+

6.2、多表组合查询

6.2.1、联结查询（交叉联结，内联结，外联结（左外联结、右外联结））

a)、交叉联结（返回笛卡尔集）

关键字：CROSS JOIN 

mysql> SELECT * FROM students_tb CROSS JOIN classes_tb; 
#结果略，类似数学里的多项式相乘，这种对服务器的开销特别大，极少使用。

b)、自然联结：让两张表的某个相同字段进行等值关系联结，是左右外联结的交集

关键字：NATURAL...JOIN

mysql> SELECT * FROM students_tb NATURAL JOIN classes_tb;
+---------+-----------+--------+------+--------+--------+-----------+
| ClassID | StudentID | Name   | Age  | Gender | Class  | TeacherID |
+---------+-----------+--------+------+--------+--------+-----------+
|       1 |         1 | Tom    |   17 | M      | class1 |         5 |
|       3 |         2 | Jack   |   18 | M      | class3 |         5 |
|       3 |        12 | 张三   |   47 | M      | class3 |         5 |
|       4 |         4 | Jimima |   15 | F      | class4 |         2 |
|       6 |         3 | Lucy   |   21 | F      | class6 |         1 |
|       6 |        10 | Cora   |   25 | F      | class6 |         1 |
+---------+-----------+--------+------+--------+--------+-----------+
注意：在表students_tb中有些有的ClassID，而在classed_tb中没有相对应的值，采用内联结里是不显示出来的。

显示学生的信息，所在的班级也要显示出来：

mysql> SELECT * FROM students_tb,classes_tb WHERE students_tb.ClassID=classes_tb.ClassID; #其实也是交叉联结过虑后的结果
+-----------+--------+------+--------+---------+---------+--------+-----------+
| StudentID | Name   | Age  | Gender | ClassID | ClassID | Class  | TeacherID |
+-----------+--------+------+--------+---------+---------+--------+-----------+
|         1 | Tom    |   17 | M      |       1 |       1 | class1 |         5 |
|         2 | Jack   |   18 | M      |       3 |       3 | class3 |         5 |
|         9 | 张三   |   47 | M      |       3 |       3 | class3 |         5 |
|         4 | Jimima |   15 | F      |       4 |       4 | class4 |         2 |
|         3 | Lucy   |   21 | F      |       6 |       6 | class6 |         1 |
|         7 | Cora   |   25 | F      |       6 |       6 | class6 |         1 |
+-----------+--------+------+--------+---------+---------+--------+-----------+

c)、外联结

左外联结（语法：左表 LEFT JOIN 右表 ON 条件）

显示各个同学是哪个班级的，如果此同学还没有给定班级，则留空：

mysql> SELECT * FROM students_tb LEFT JOIN classes_tb ON students_tb.ClassID=classes_tb.ClassID;
+-----------+--------+------+--------+---------+---------+--------+-----------+
| StudentID | Name   | Age  | Gender | ClassID | ClassID | Class  | TeacherID |
+-----------+--------+------+--------+---------+---------+--------+-----------+
|         1 | Tom    |   17 | M      |       1 |       1 | class1 |         5 |
|         2 | Jack   |   18 | M      |       3 |       3 | class3 |         5 |
|         3 | Lucy   |   21 | F      |       6 |       6 | class6 |         1 |
|         4 | Jimima |   15 | F      |       4 |       4 | class4 |         2 |
|         5 | Jimmy  |   30 | M      |       9 |    NULL | NULL   |      NULL |
|         6 | Jim    |   26 | M      |       7 |    NULL | NULL   |      NULL |
|         7 | Cora   |   25 | F      |       6 |       6 | class6 |         1 |
|         8 | Echo   |   58 | F      |       7 |    NULL | NULL   |      NULL |
|         9 | 张三   |   47 | M      |       3 |       3 | class3 |         5 |
+-----------+--------+------+--------+---------+---------+--------+-----------+

右外联结（语法：左表 RIGHT JION 右表 ON 条件）

显示各个班级有哪些同学，如果这个班级没有同学就留空：

mysql> SELECT * FROM students_tb RIGHT JOIN classes_tb ON students_tb.ClassID=classes_tb.ClassID;
+-----------+--------+------+--------+---------+---------+--------+-----------+
| StudentID | Name   | Age  | Gender | ClassID | ClassID | Class  | TeacherID |
+-----------+--------+------+--------+---------+---------+--------+-----------+
|         1 | Tom    |   17 | M      |       1 |       1 | class1 |         5 |
|      NULL | NULL   | NULL | NULL   |    NULL |       2 | class2 |         2 |
|         2 | Jack   |   18 | M      |       3 |       3 | class3 |         5 |
|         9 | 张三   |   47 | M      |       3 |       3 | class3 |         5 |
|         4 | Jimima |   15 | F      |       4 |       4 | class4 |         2 |
|      NULL | NULL   | NULL | NULL   |    NULL |       5 | class5 |         4 |
|         3 | Lucy   |   21 | F      |       6 |       6 | class6 |         1 |
|         7 | Cora   |   25 | F      |       6 |       6 | class6 |         1 |
+-----------+--------+------+--------+---------+---------+--------+-----------+

全外联结：

mysql无全外联结

6.2.2、联合查询：UNION

mysql> SELECT Name,Age FROM teacher_tb WHERE Age > 40 UNION SELECT Name,Age FROM students_tb WHERE Age > 30;
+-----------+------+
| Name      | Age  |
+-----------+------+
| Ying hui  |   50 |
| Feng bing |   46 |
| Shun bin  |   68 |
| Echo      |   58 |
| 张三      |   47 |
+-----------+------+

查询同学所对应的负责老师：

mysql> SELECT students_tb.Name AS student_name,teacher_tb.Name AS teacher_name FROM students_tb,classes_tb,teacher_tb WHERE students_tb.ClassID=classes_tb.ClassID AND classes_tb.TeacherID=teacher_tb.TeacherID;
+--------------+----------------+
| student_name | teacher_name   |
+--------------+----------------+
| Tom          | Feng bing      |
| Jack         | Feng bing      |
| 张三         | Feng bing      |
| Jimima       | Zhang shanfeng |
| Lucy         | Wang baoqiang  |
| Cora         | Wang baoqiang  |
+--------------+----------------+

6.2.3、内联结：INNER JON...ON

语法：

表1 AS 别名1 INNER JOIN 表1 AS 别名2 ON 别名1.字段=别名2.字段

mysql> SELECT * FROM students_tb AS s INNER JOIN classes_tb AS c ON s.ClassID = c.ClassID;
+-----------+--------+------+--------+---------+---------+--------+-----------+
| StudentID | Name   | Age  | Gender | ClassID | ClassID | Class  | TeacherID |
+-----------+--------+------+--------+---------+---------+--------+-----------+
|         1 | Tom    |   17 | M      |       1 |       1 | class1 |         5 |
|         2 | Jack   |   18 | M      |       3 |       3 | class3 |         5 |
|        12 | 张三   |   47 | M      |       3 |       3 | class3 |         5 |
|         4 | Jimima |   15 | F      |       4 |       4 | class4 |         2 |
|         3 | Lucy   |   21 | F      |       6 |       6 | class6 |         1 |
|        10 | Cora   |   25 | F      |       6 |       6 | class6 |         1 |
+-----------+--------+------+--------+---------+---------+--------+-----------+

6.3、子查询

6.3.1、WHERE子查询

显示年龄大于平均年龄的学生：

mysql> SELECT Name,Age FROM students_tb WHERE Age > (SELECT AVG(Age) FROM students_tb);
+--------+------+
| Name   | Age  |
+--------+------+
| Jimmy  |   30 |
| Echo   |   58 |
| 张三   |   47 |
+--------+------+

查询学生表中给定来存在的班级的学生信息：

mysql> SELECT * FROM classes_tb;
+---------+--------+-----------+
| ClassID | Class  | TeacherID |
+---------+--------+-----------+
|       1 | class1 |         5 |
|       2 | class2 |         2 |
|       3 | class3 |         5 |
|       4 | class4 |         2 |
|       5 | class5 |         4 |
|       6 | class6 |         1 |
+---------+--------+-----------+
#只有6个班级
mysql> SELECT Name,ClassID FROM students_tb WHERE ClassID NOT IN (SELECT ClassID FROM classes_tb);
+-------+---------+
| Name  | ClassID |
+-------+---------+
| Jimmy |       9 |
| Jim   |       7 |
| Echo  |       7 |
+-------+---------+
#7和9这两个班级是不存在的

查询各个班级中同学年龄大于全校平均年龄的同学的个数：

mysql> SELECT ClassID,COUNT(Name) FROM students_tb WHERE Age > (SELECT AVG(Age) FROM students_tb) GROUP BY ClassID;
+---------+-------------+
| ClassID | COUNT(Name) |
+---------+-------------+
|       3 |           1 |
|       7 |           1 |
|       9 |           1 |
+---------+-------------+
mysql> SELECT Name,Age,ClassID FROM students_tb WHERE Age > (SELECT AVG(Age) FROM students_tb);  #验证上边的结果
+--------+------+---------+
| Name   | Age  | ClassID |
+--------+------+---------+
| Jimmy  |   30 |       9 |
| Echo   |   58 |       7 |
| 张三   |   47 |       3 |
+--------+------+---------+

6.3.2、FROM子查询：

查询表中男性同学中年龄大于20的：

mysql> SELECT * FROM (SELECT Name,Age,Gender FROM students_tb WHERE Gender='M') AS S WHERE Age > 20;
+--------+------+--------+
| Name   | Age  | Gender |
+--------+------+--------+
| Jimmy  |   30 | M      |
| Jim    |   26 | M      |
| 张三   |   47 | M      |
+--------+------+--------+

mysql中对子查询优化很有限，建议不要使用子查询，可改为其他的简单查询或联合查询。

转载于:https://blog.51cto.com/zhaochj/1629759