目录
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\Gmysql> show table status in test\Gmysql> 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: 0mysql> 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: 0mysql> 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: 0mysql> 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: 0mysql> 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: 0mysql> 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: 0mysql> 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: 1mysql> 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: 0mysql> UPDATE tb2 SET age=11 LIMIT 2; #修改前两行的年龄为11Query OK, 2 rows affected (0.01 sec)Rows matched: 2 Changed: 2 Warnings: 0mysql> 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为密码登陆，但权限只有selectmysql> 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 changedmysql> 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: 0mysql> 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中对子查询优化很有限，建议不要使用子查询，可改为其他的简单查询或联合查询。