MySQL 数据库的查询详解
一、单表查询：
1.准备工作：
创建查询环境：
mysql> CREATE TABLE course (id INT AUTO_INCREMENT PRIMARY KEY, name CHAR(20) NOT NULL, price DECIMAL(8,2) NOT NULL) charset utf8;
Query OK, 0 rows affected (0.12 sec)
mysql> desc course;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(20) | NO | | NULL | |
| price | decimal(8,2) | NO | | NULL | |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
-
解释字段含义：
1.id：课程ID，主键，使用的INT类型，自动增长2.name：课程名称，CHAR类型，不可以为空，是必填项
3.price：课程价格，是DECIMAL类型，不可以为空，是必填项
批量添加数据：
mysql> INSERT INTO course (name, price)
VALUES
('上海财经大学', 99.99), ('北京大学', 199.99), ('天津大学', 299.99),
('哈 尔滨工业大学', 399.99), ('交通大学', 499.99), ('同济大学', 599.99),
('清华大学', 699.99);
2.查询所有字段：
语法：select * from [表名]
# 取出course表中所有的字段信息
mysql> select * from course;
+----+----------------+--------+
| id | name | price |
+----+----------------+--------+
| 1 | 上海财经大学 | 99.99 |
| 2 | 北京大学 | 199.99 |
| 3 | 天津大学 | 299.99 |
| 4 | 哈尔滨工业大学 | 399.99 |
| 5 | 交通大学 | 499.99 |
| 6 | 同济大学 | 599.99 |
| 7 | 清华大学 | 699.99 |
+----+----------------+--------+
7 rows in set (0.06 sec)
3.查询指定字段：
语法：select [指定字段1], [指定字段2], [指定字段n] from [表名]
# 取出course表中的指定的id跟name字段
mysql> select id, name from course;
+----+----------------+
| id | name |
+----+----------------+
| 1 | 上海财经大学 |
| 2 | 北京大学 |
| 3 | 天津大学 |
| 4 | 哈尔滨工业大学 |
| 5 | 交通大学 |
| 6 | 同济大学 |
| 7 | 清华大学 |
+----+----------------+
7 rows in set (0.00 sec)
4.查询指定记录：
简单理解：列就是变量，WHERE 条件是表达式，哪行表达式为真，就取哪一行
语法：select [列名] from [表名] where [表达式]
4.1：WHERE 关键字的使用：
# 取出course表中 课程价格大于 500元的课程
mysql> select * from course where price > 500;
+----+----------+--------+
| id | name | price |
+----+----------+--------+
| 6 | 同济大学 | 599.99 |
| 7 | 清华大学 | 699.99 |
+----+----------+--------+
2 rows in set (0.01 sec)
# 取出course表中 课程ID 等于 1的课程信息
mysql> select * from course where id=1;
+----+--------------+-------+
| id | name | price |
+----+--------------+-------+
| 1 | 上海财经大学 | 99.99 |
+----+--------------+-------+
1 row in set (0.01 sec)
4.2：INT 关键字的使用：
# 取出course表中, 课程ID 是 1，3，5的课程信息
mysql> select * from course where id in (1,3,5);
+----+--------------+--------+
| id | name | price |
+----+--------------+--------+
| 1 | 上海财经大学 | 99.99 |
| 3 | 天津大学 | 299.99 |
| 5 | 交通大学 | 499.99 |
+----+--------------+--------+
3 rows in set (0.00 sec)
4.3：BETWEEN AND 范围查询的使用：
# 取出course表中，课程ID 2到5的课程信息
mysql> select * from course where id BETWEEN 2 AND 5;
+----+----------------+--------+
| id | name | price |
+----+----------------+--------+
| 2 | 北京大学 | 199.99 |
| 3 | 天津大学 | 299.99 |
| 4 | 哈尔滨工业大学 | 399.99 |
| 5 | 交通大学 | 499.99 |
+----+----------------+--------+
4 rows in set (0.00 sec)
4.4：OR 、AND 的条件查询使用：
# 查询课程ID为1或课程ID为10的课程，满足一个条件即可
mysql> select * from course where id = 1 OR id = 10;
+----+--------------+-------+
| id | name | price |
+----+--------------+-------+
| 1 | 上海财经大学 | 99.99 |
+----+--------------+-------+
1 row in set (0.00 sec)
# 查看课程ID为1且课程名称是'上海财经大学'的课程信息, 必须都满足才可以为真
mysql> select * from course where id = 1 AND name = '上海财经大学';
+----+--------------+-------+
| id | name | price |
+----+--------------+-------+
| 1 | 上海财经大学 | 99.99 |
+----+--------------+-------+
1 row in set (0.00 sec)
# 查看课程ID为1且课名是'上海财经大学' 或者 课程ID 为5的课程信息, AND 优先级比OR高
mysql> select * from course where id = 1 AND name = '上海财经大学' OR id = 5;
+----+--------------+--------+
| id | name | price |
+----+--------------+--------+
| 1 | 上海财经大学 | 99.99 |
| 5 | 交通大学 | 499.99 |
+----+--------------+--------+
2 rows in set (0.00 sec)
比较运算符：
| 操作符 | 描述 | 实例 |
|---|---|---|
| = | 等号，检测两个值是否相等，如果相等返回true | (A = B) 返回false。 |
| <>, != | 不等于，检测两个值是否相等，如果不相等返回true | (A != B) 返回 true。 |
| > | 大于号，检测左边的值是否大于右边的值, 如果左边的值大于右边的值返回true | (A > B) 返回false。 |
| < | 小于号，检测左边的值是否小于右边的值, 如果左边的值小于右边的值返回true | (A < B) 返回 true。 |
| >= | 大于等于号，检测左边的值是否大于或等于右边的值, 如果左边的值大于或等于右边的值返回true | (A >= B) 返回false。 |
| <= | 小于等于号，检测左边的值是否小于于或等于右边的值, 如果左边的值小于或等于右边的值返回true | (A <= B) 返回 true。 |
| in | 在某集合内 | (A IN B) 返回true。 |
| between | 在某范围 | 取(A,B)之间的值 |
逻辑运算符：
| 运算符 | 说明 |
|---|---|
| NOT 或 ! | 逻辑非 |
| OR 或 || | 逻辑或 |
| AND 或 && | 逻辑与 |
5.模糊查询：
语法：select [列名] from [表名] WHERE [指定字段] LIKE [条件]
5.1：使用"%"匹配任意长度的字符:
# 取出course表中, 以课程名以哈尔滨开头的课程信息
mysql> select * from course where name LIKE '哈尔滨%';
+----+----------------+--------+
| id | name | price |
+----+----------------+--------+
| 4 | 哈尔滨工业大学 | 399.99 |
+----+----------------+--------+
1 row in set (0.00 sec)
# 取出course表中，以课程名为大学结尾的课程信息
mysql> select * from course where name LIKE '%大学';
+----+----------------+--------+
| id | name | price |
+----+----------------+--------+
| 1 | 上海财经大学 | 99.99 |
| 2 | 北京大学 | 199.99 |
| 3 | 天津大学 | 299.99 |
| 4 | 哈尔滨工业大学 | 399.99 |
| 5 | 交通大学 | 499.99 |
| 6 | 同济大学 | 599.99 |
| 7 | 清华大学 | 699.99 |
+----+----------------+--------+
7 rows in set (0.00 sec)
5.2：使用"_"只能匹配任意一个字符：
# 取出course表中， 以课程名占位一个字符_
mysql> select * from course where name LIKE '_京大学';
+----+----------+--------+
| id | name | price |
+----+----------+--------+
| 2 | 北京大学 | 199.99 |
+----+----------+--------+
1 row in set (0.00 sec)
6.查询空值：
6.1：查询字段名是NULL：
语法：select [列名] from [表名] where [指定字段] is null
# 查询所有课程价格为空的信息(因为我们创建表的时候, 将此字段设置不能为NULL)
mysql> select * from course where price is null;
Empty set (0.00 sec)
6.2：查询字段名不是NULL：
语法：select [列名] from [表名] where [指定字段] is not null
# 查询所有课程价位不为空的信息
mysql> select * from course where price is not null;
+----+----------------+--------+
| id | name | price |
+----+----------------+--------+
| 1 | 上海财经大学 | 99.99 |
| 2 | 北京大学 | 199.99 |
| 3 | 天津大学 | 299.99 |
| 4 | 哈尔滨工业大学 | 399.99 |
| 5 | 交通大学 | 499.99 |
| 6 | 同济大学 | 599.99 |
| 7 | 清华大学 | 699.99 |
+----+----------------+--------+
7 rows in set (0.00 sec)
7.去重查询(DISTINCT)：
语法：select distinct [要去重列名] from [表名]
# 往course表里新插入一条数据, 让价格也是699.99 跟清湖大学相同
mysql> insert into course (name, price) values('华北大学', 699.99);
Query OK, 1 row affected (0.02 sec)
# 查看当前表中的数据
mysql> select * from course;
+----+----------------+--------+
| id | name | price |
+----+----------------+--------+
| 1 | 上海财经大学 | 99.99 |
| 2 | 北京大学 | 199.99 |
| 3 | 天津大学 | 299.99 |
| 4 | 哈尔滨工业大学 | 399.99 |
| 5 | 交通大学 | 499.99 |
| 6 | 同济大学 | 599.99 |
| 7 | 清华大学 | 699.99 |
| 8 | 华北大学 | 699.99 |
+----+----------------+--------+
8 rows in set (0.00 sec)
# 将course表中的价格, 进行去重
mysql> select distinct price from course;
+--------+
| price |
+--------+
| 99.99 |
| 199.99 |
| 299.99 |
| 399.99 |
| 499.99 |
| 599.99 |
| 699.99 |
+--------+
7 rows in set (0.00 sec)
8.聚合函数查询：
8.1：count:
概念：表示计算总行数，括号中写*或者列名。
语法：select count(列名) from [表名]
# 查询该课程表中，共有多少条数据：以价格行数为标准
mysql> select count(price) from course;
+--------------+
| count(price) |
+--------------+
| 8 |
+--------------+
1 row in set (0.01 sec)
mysql> select count(*) from course;
+----------+
| count(*) |
+----------+
| 8 |
+----------+
1 row in set (0.00 sec)
注意：
count(*)查询的就是绝对的行数，哪怕某一行所有字段全为NULL，也计算在内。count(列名)查询的是该列不为NULL的所有行的行数。
区别：
count(*) 与 count(列名)谁比较好呢？
- 对于Myisam引擎的表，是没有区别，这种引擎内部有一计数器在维护着行数。
- 对于Innodb引擎的表，count(*)直接读行数，效率很低，因为Innodb真的会全表检索一遍。
8.2：max：
概念：表示求此列的最大值。
语法：select max(列名) from [表名]
# 查询课程中，最贵的课程价格
mysql> select max(price) from course;
+------------+
| max(price) |
+------------+
| 699.99 |
+------------+
1 row in set (0.00 sec)
8.3：min：
概念：表示求此列最小值。
语法：select min(列名) from [表名]
# 查询课程中，最便宜的课程价格
mysql> select min(price) from course;
+------------+
| min(price) |
+------------+
| 99.99 |
+------------+
1 row in set (0.00 sec)
8.4：sum：
概念：表示求此列的和。
语法：select sum(列名) from [表名]
# 查询该课程表中，总共课程的价格总和
mysql> select min(price) from course;
+------------+
| min(price) |
+------------+
| 99.99 |
+------------+
1 row in set (0.00 sec)
8.5：avg：
概念：表示求此列的平均值。
语法：select avg(列名) from [表名]
# 查看该课程表中，课程价格的平均值(总价格/总个数)
mysql> select avg(price) from course;
+------------+
| avg(price) |
+------------+
| 437.490000 |
+------------+
1 row in set (0.00 sec)
9.分组查询(GROUP BY)：
语法：select [列1]，[列2] from [表名] group by [列1]，[列2]，[列3]
概念：
- 按照字段分组，表示此
字段相同的数据会被放到一个组中。 - 分组后，
只能查询出相同的数据列。 - 可以对
分组后的数据进行统计做聚合运算。
准备工作：
# 在course表的基础上新增一列, class_id
mysql> alter table course add class_id int not null;
Query OK, 0 rows affected (0.16 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 查看当前表的信息
mysql> select * from course;
+----+----------------+--------+----------+
| id | name | price | class_id |
+----+----------------+--------+----------+
| 1 | 上海财经大学 | 99.99 | 0 |
| 2 | 北京大学 | 199.99 | 0 |
| 3 | 天津大学 | 299.99 | 0 |
| 4 | 哈尔滨工业大学 | 399.99 | 0 |
| 5 | 交通大学 | 499.99 | 0 |
| 6 | 同济大学 | 599.99 | 0 |
| 7 | 清华大学 | 699.99 | 0 |
| 8 | 华北大学 | 699.99 | 0 |
+----+----------------+--------+----------+
8 rows in set (0.00 sec)
# 将id = 1,2,3 班级号class_id 设置为1
mysql> update course set class_id = 1 where id in (1,2,3);
Query OK, 3 rows affected (0.02 sec)
Rows matched: 3 Changed: 3 Warnings: 0
# 将 id = 4, 5 班级号class_id 设置为2
mysql> update course set class_id = 2 where id in (4, 5);
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2 Changed: 2 Warnings: 0
# 把 id = 6, 7 班级号 class_id 设置为3
mysql> update course set class_id = 3 where id in (6,7);
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
# 查询课程表的信息
mysql> select * from course;
+----+----------------+--------+----------+
| id | name | price | class_id |
+----+----------------+--------+----------+
| 1 | 上海财经大学 | 99.99 | 1 |
| 2 | 北京大学 | 199.99 | 1 |
| 3 | 天津大学 | 299.99 | 1 |
| 4 | 哈尔滨工业大学 | 399.99 | 2 |
| 5 | 交通大学 | 499.99 | 2 |
| 6 | 同济大学 | 599.99 | 3 |
| 7 | 清华大学 | 699.99 | 3 |
| 8 | 华北大学 | 699.99 | 0 |
+----+----------------+--------+----------+
8 rows in set (0.00 sec)
GROUP BY 一般不单独使用:
# 如果就按照 class_id 进行分组，不指定检索出的字段, 那么只会显示每个组中的第一条记录
mysql> select * from course group by class_id;
+----+----------------+--------+----------+
| id | name | price | class_id |
+----+----------------+--------+----------+
| 8 | 华北大学 | 699.99 | 0 |
| 1 | 上海财经大学 | 99.99 | 1 |
| 4 | 哈尔滨工业大学 | 399.99 | 2 |
| 6 | 同济大学 | 599.99 | 3 |
+----+----------------+--------+----------+
4 rows in set (0.00 sec)
按照什么分组，就检索出哪个字段:
# 查询course表，根据class_id列进行分组, 检索出id,name, class_id
mysql> select id, name, class_id from course group by class_id;
+----+----------------+----------+
| id | name | class_id |
+----+----------------+----------+
| 8 | 华北大学 | 0 |
| 1 | 上海财经大学 | 1 |
| 4 | 哈尔滨工业大学 | 2 |
| 6 | 同济大学 | 3 |
+----+----------------+----------+
4 rows in set (0.00 sec)
举个栗子：
# 按照class_id进行分组，并检索出课程平均价格
mysql> select class_id, avg(price) from course group by class_id;
+----------+------------+
| class_id | avg(price) |
+----------+------------+
| 0 | 699.990000 |
| 1 | 199.990000 |
| 2 | 449.990000 |
| 3 | 649.990000 |
+----------+------------+
4 rows in set (0.00 sec)
使用as：
as 可以将结果集的列名进行更改，切记并没有修改表的列名，仅是在查询集中进行了更改。
# 通过class_id进行分组，查询每个组中的课程数是多少，并且将班级数的列进行改名
mysql> select class_id, count(*) as class_count from course group by class_id;
+----------+-------------+
| class_id | class_count |
+----------+-------------+
| 0 | 1 |
| 1 | 3 |
| 2 | 2 |
| 3 | 2 |
+----------+-------------+
4 rows in set (0.00 sec)
10.having的使用：
概念：
- 用来分组查询后指定的一些条件来输出查询结果。
- having跟where一样，但having只能用户group by。
区别：
where是对from后面指定的表进行数据筛选，属于对原始数据的筛选。having是对group by 的结果集进行筛选。
举个栗子：
使用where：
# 查询班级号为1的所有数据
mysql> select * from course where class_id = 1;
+----+--------------+--------+----------+
| id | name | price | class_id |
+----+--------------+--------+----------+
| 1 | 上海财经大学 | 99.99 | 1 |
| 2 | 北京大学 | 199.99 | 1 |
| 3 | 天津大学 | 299.99 | 1 |
+----+--------------+--------+----------+
3 rows in set (0.00 sec)
使用having：
# 首先查看一下根据班级号进行分组
mysql> select * from course group by class_id;
+----+----------------+--------+----------+
| id | name | price | class_id |
+----+----------------+--------+----------+
| 8 | 华北大学 | 699.99 | 0 |
| 1 | 上海财经大学 | 99.99 | 1 |
| 4 | 哈尔滨工业大学 | 399.99 | 2 |
| 6 | 同济大学 | 599.99 | 3 |
+----+----------------+--------+----------+
4 rows in set (0.00 sec)
# 通过检索出的数据，having 将查询集进行筛选, 课程价格 大于400
mysql> select * from course group by class_id having price > 400;
+----+----------+--------+----------+
| id | name | price | class_id |
+----+----------+--------+----------+
| 8 | 华北大学 | 699.99 | 0 |
| 6 | 同济大学 | 599.99 | 3 |
+----+----------+--------+----------+
2 rows in set (0.00 sec)
11.排序查询：
概念：将查询出的结果进行排序。
语法：select [列1]，[列2]，[列n] from [表名] order by [列1] asc|desc
注意：
- 将行数据按照order by 后面指定的列进行排序，当列相同时，按照后面指定的列在进行排序。
- 默认情况，是从小到大排序。
- asc 从小到大排列，是升序
- desc从大到小排序，是降序
举个栗子：
# 按照course表中的id进行升序排序
mysql> select * from course group by class_id having price > 400 order by id;
+----+----------+--------+----------+
| id | name | price | class_id |
+----+----------+--------+----------+
| 6 | 同济大学 | 599.99 | 3 |
| 8 | 华北大学 | 699.99 | 0 |
+----+----------+--------+----------+
2 rows in set (0.00 sec)
# 按照course表中的id进行降序排序
mysql> select * from course group by class_id having price > 400 order by id desc;
+----+----------+--------+----------+
| id | name | price | class_id |
+----+----------+--------+----------+
| 8 | 华北大学 | 699.99 | 0 |
| 6 | 同济大学 | 599.99 | 3 |
+----+----------+--------+----------+
2 rows in set (0.00 sec)
12.Limit的使用：
概念：limit在语句的最后，起到限制条目的作用。
语法：limit [offset， ] N
- Offset 偏移量，指跳过几行
- N：取出条目
- Offset如果不写，相当于 limit 0, N
举个栗子：
# 查询course表中所有的数据
mysql> select * from course;
+----+----------------+--------+----------+
| id | name | price | class_id |
+----+----------------+--------+----------+
| 1 | 上海财经大学 | 99.99 | 1 |
| 2 | 北京大学 | 199.99 | 1 |
| 3 | 天津大学 | 299.99 | 1 |
| 4 | 哈尔滨工业大学 | 399.99 | 2 |
| 5 | 交通大学 | 499.99 | 2 |
| 6 | 同济大学 | 599.99 | 3 |
| 7 | 清华大学 | 699.99 | 3 |
| 8 | 华北大学 | 699.99 | 0 |
+----+----------------+--------+----------+
8 rows in set (0.00 sec)
# 查询course表中的数据, 并跳过1条，检索出3条数据
mysql> select * from course limit 1,3;
+----+----------------+--------+----------+
| id | name | price | class_id |
+----+----------------+--------+----------+
| 2 | 北京大学 | 199.99 | 1 |
| 3 | 天津大学 | 299.99 | 1 |
| 4 | 哈尔滨工业大学 | 399.99 | 2 |
+----+----------------+--------+----------+
3 rows in set (0.00 sec)
13.使用顺序：
select → from → where → group by → having → order by → limit
二、多表查询：
1.准备工作：
# 新创建一个basecourse表
mysql> CREATE TABLE basecourse (id INT AUTO_INCREMENT PRIMARY KEY, name CHAR(20) NOT NULL, price DECIMAL(8,2) NOT NULL,class_id int NOT NULL) charset utf8;
Query OK, 0 rows affected (0.03 sec)
mysql> desc basecourse;
+----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(20) | NO | | NULL | |
| price | decimal(8,2) | NO | | NULL | |
| class_id | int(11) | NO | | NULL | |
+----------+--------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
2.子查询：
概念：
子查询就是在原有的查询语句中，嵌入新的查询，来得到我们想要的结果集
2.1：where型子查询：
概念：把内层查询结果当作外层查询的比较条件。
不使用where子查询：
# 查询出course表中, 最贵的课程
mysql> select id, name, price from course order by price desc limit 1;
+----+----------+--------+
| id | name | price |
+----+----------+--------+
| 7 | 清华大学 | 699.99 |
+----+----------+--------+
1 row in set (0.00 sec)
使用where子查询：
# 查询出course表中, 最贵的课程
mysql> select id, name, price from course where price =
(select max(price) from course) limit 1;
+----+----------+--------+
| id | name | price |
+----+----------+--------+
| 7 | 清华大学 | 699.99 |
+----+----------+--------+
1 row in set (0.00 sec)
2.2：from型子查询：
概念：把内层的查询结果当成表供外层继续查询。
# 通过class_id 找出，每个班级中，最贵的课程
mysql> select * from
(select id, name, price, class_id from course order by price desc) as tmp
group by class_id ;
+----+----------------+--------+----------+
| id | name | price | class_id |
+----+----------------+--------+----------+
| 8 | 华北大学 | 699.99 | 0 |
| 1 | 上海财经大学 | 99.99 | 1 |
| 4 | 哈尔滨工业大学 | 399.99 | 2 |
| 6 | 同济大学 | 599.99 | 3 |
+----+----------------+--------+----------+
4 rows in set (0.00 sec)
2.3：exists 子查询：
概念：把外层的查询结果拿到内层，看内层的查询是否成立，not exists 相反 。
# 查询所有basecourse表中的课程
mysql> select id, name, price from basecourse where exists
(select * from course where basecourse.class_id=course.class_id);
+----+--------+--------+
| id | name | price |
+----+--------+--------+
| 1 | 上海 | 100.00 |
| 2 | 北京 | 200.00 |
| 3 | 天津 | 300.00 |
| 4 | 哈尔滨 | 400.00 |
| 5 | 北京 | 200.00 |
| 6 | 上海 | 100.00 |
| 7 | 北京 | 200.00 |
| 8 | 华北 | 400.00 |
+----+--------+--------+
8 rows in set (0.00 sec)
3.连接查询：
在数据库中，一张表就是一个集合，每一行就是集合中的一个元素
3.1：全相乘：
两表做全相乘：
- 从行的角度来看，就是2表每一行相互组合。
- 从列的角度来看，结果集中的列，是两表的列名相加。
# 可以看以下栗子
mysql> select * from course;
+----+----------------+--------+----------+
| id | name | price | class_id |
+----+----------------+--------+----------+
| 1 | 上海财经大学 | 99.99 | 1 |
| 2 | 北京大学 | 199.99 | 1 |
| 3 | 天津大学 | 299.99 | 1 |
| 4 | 哈尔滨工业大学 | 399.99 | 2 |
| 5 | 交通大学 | 499.99 | 2 |
| 6 | 同济大学 | 599.99 | 3 |
| 7 | 清华大学 | 699.99 | 3 |
| 8 | 华北大学 | 699.99 | 0 |
+----+----------------+--------+----------+
8 rows in set (0.01 sec)
mysql> select * from basecourse;
+----+--------+--------+----------+
| id | name | price | class_id |
+----+--------+--------+----------+
| 1 | 上海 | 100.00 | 1 |
| 2 | 北京 | 200.00 | 2 |
| 3 | 天津 | 300.00 | 3 |
| 4 | 哈尔滨 | 400.00 | 0 |
| 5 | 北京 | 200.00 | 2 |
| 6 | 上海 | 100.00 | 1 |
| 7 | 北京 | 200.00 | 2 |
| 8 | 华北 | 400.00 | 3 |
+----+--------+--------+----------+
8 rows in set (0.00 sec)
mysql> select * from course, basecourse;
+----+----------------+--------+----------+----+--------+--------+----------+
| id | name | price | class_id | id | name | price | class_id |
+----+----------------+--------+----------+----+--------+--------+----------+
| 1 | 上海财经大学 | 99.99 | 1 | 1 | 上海 | 100.00 | 1 |
| 2 | 北京大学 | 199.99 | 1 | 1 | 上海 | 100.00 | 1 |
| 3 | 天津大学 | 299.99 | 1 | 1 | 上海 | 100.00 | 1 |
| 4 | 哈尔滨工业大学 | 399.99 | 2 | 1 | 上海 | 100.00 | 1 |
| 5 | 交通大学 | 499.99 | 2 | 1 | 上海 | 100.00 | 1 |
| 6 | 同济大学 | 599.99 | 3 | 1 | 上海 | 100.00 | 1 |
| 7 | 清华大学 | 699.99 | 3 | 1 | 上海 | 100.00 | 1 |
| 8 | 华北大学 | 699.99 | 0 | 1 | 上海 | 100.00 | 1 |
| 1 | 上海财经大学 | 99.99 | 1 | 2 | 北京 | 200.00 | 2 |
| 2 | 北京大学 | 199.99 | 1 | 2 | 北京 | 200.00 | 2 |
| 3 | 天津大学 | 299.99 | 1 | 2 | 北京 | 200.00 | 2 |
| 4 | 哈尔滨工业大学 | 399.99 | 2 | 2 | 北京 | 200.00 | 2 |
| 5 | 交通大学 | 499.99 | 2 | 2 | 北京 | 200.00 | 2 |
| 6 | 同济大学 | 599.99 | 3 | 2 | 北京 | 200.00 | 2 |
| 7 | 清华大学 | 699.99 | 3 | 2 | 北京 | 200.00 | 2 |
| 8 | 华北大学 | 699.99 | 0 | 2 | 北京 | 200.00 | 2 |
| 1 | 上海财经大学 | 99.99 | 1 | 3 | 天津 | 300.00 | 3 |
| 2 | 北京大学 | 199.99 | 1 | 3 | 天津 | 300.00 | 3 |
| 3 | 天津大学 | 299.99 | 1 | 3 | 天津 | 300.00 | 3 |
| 4 | 哈尔滨工业大学 | 399.99 | 2 | 3 | 天津 | 300.00 | 3 |
| 5 | 交通大学 | 499.99 | 2 | 3 | 天津 | 300.00 | 3 |
| 6 | 同济大学 | 599.99 | 3 | 3 | 天津 | 300.00 | 3 |
| 7 | 清华大学 | 699.99 | 3 | 3 | 天津 | 300.00 | 3 |
| 8 | 华北大学 | 699.99 | 0 | 3 | 天津 | 300.00 | 3 |
| 1 | 上海财经大学 | 99.99 | 1 | 4 | 哈尔滨 | 400.00 | 0 |
| 2 | 北京大学 | 199.99 | 1 | 4 | 哈尔滨 | 400.00 | 0 |
| 3 | 天津大学 | 299.99 | 1 | 4 | 哈尔滨 | 400.00 | 0 |
| 4 | 哈尔滨工业大学 | 399.99 | 2 | 4 | 哈尔滨 | 400.00 | 0 |
| 5 | 交通大学 | 499.99 | 2 | 4 | 哈尔滨 | 400.00 | 0 |
| 6 | 同济大学 | 599.99 | 3 | 4 | 哈尔滨 | 400.00 | 0 |
| 7 | 清华大学 | 699.99 | 3 | 4 | 哈尔滨 | 400.00 | 0 |
| 8 | 华北大学 | 699.99 | 0 | 4 | 哈尔滨 | 400.00 | 0 |
| 1 | 上海财经大学 | 99.99 | 1 | 5 | 北京 | 200.00 | 2 |
| 2 | 北京大学 | 199.99 | 1 | 5 | 北京 | 200.00 | 2 |
| 3 | 天津大学 | 299.99 | 1 | 5 | 北京 | 200.00 | 2 |
| 4 | 哈尔滨工业大学 | 399.99 | 2 | 5 | 北京 | 200.00 | 2 |
| 5 | 交通大学 | 499.99 | 2 | 5 | 北京 | 200.00 | 2 |
| 6 | 同济大学 | 599.99 | 3 | 5 | 北京 | 200.00 | 2 |
| 7 | 清华大学 | 699.99 | 3 | 5 | 北京 | 200.00 | 2 |
| 8 | 华北大学 | 699.99 | 0 | 5 | 北京 | 200.00 | 2 |
| 1 | 上海财经大学 | 99.99 | 1 | 6 | 上海 | 100.00 | 1 |
| 2 | 北京大学 | 199.99 | 1 | 6 | 上海 | 100.00 | 1 |
| 3 | 天津大学 | 299.99 | 1 | 6 | 上海 | 100.00 | 1 |
| 4 | 哈尔滨工业大学 | 399.99 | 2 | 6 | 上海 | 100.00 | 1 |
| 5 | 交通大学 | 499.99 | 2 | 6 | 上海 | 100.00 | 1 |
| 6 | 同济大学 | 599.99 | 3 | 6 | 上海 | 100.00 | 1 |
| 7 | 清华大学 | 699.99 | 3 | 6 | 上海 | 100.00 | 1 |
| 8 | 华北大学 | 699.99 | 0 | 6 | 上海 | 100.00 | 1 |
| 1 | 上海财经大学 | 99.99 | 1 | 7 | 北京 | 200.00 | 2 |
| 2 | 北京大学 | 199.99 | 1 | 7 | 北京 | 200.00 | 2 |
| 3 | 天津大学 | 299.99 | 1 | 7 | 北京 | 200.00 | 2 |
| 4 | 哈尔滨工业大学 | 399.99 | 2 | 7 | 北京 | 200.00 | 2 |
| 5 | 交通大学 | 499.99 | 2 | 7 | 北京 | 200.00 | 2 |
| 6 | 同济大学 | 599.99 | 3 | 7 | 北京 | 200.00 | 2 |
| 7 | 清华大学 | 699.99 | 3 | 7 | 北京 | 200.00 | 2 |
| 8 | 华北大学 | 699.99 | 0 | 7 | 北京 | 200.00 | 2 |
| 1 | 上海财经大学 | 99.99 | 1 | 8 | 华北 | 400.00 | 3 |
| 2 | 北京大学 | 199.99 | 1 | 8 | 华北 | 400.00 | 3 |
| 3 | 天津大学 | 299.99 | 1 | 8 | 华北 | 400.00 | 3 |
| 4 | 哈尔滨工业大学 | 399.99 | 2 | 8 | 华北 | 400.00 | 3 |
| 5 | 交通大学 | 499.99 | 2 | 8 | 华北 | 400.00 | 3 |
| 6 | 同济大学 | 599.99 | 3 | 8 | 华北 | 400.00 | 3 |
| 7 | 清华大学 | 699.99 | 3 | 8 | 华北 | 400.00 | 3 |
| 8 | 华北大学 | 699.99 | 0 | 8 | 华北 | 400.00 | 3 |
+----+----------------+--------+----------+----+--------+--------+----------+
64 rows in set (0.00 sec)
注意：
全相乘的查询效率很低，全相乘会在内存中生成一个非常大的数据(临时表)，因为有很多不必要的数据。 不建议使用。- 如果有1千条数据，另一张表也有1千数据，两表全相乘就是100W条数据，是非常消耗内存的，并且全相乘不能合理利用索引，
因为全相乘会生成一张临时表，临时表是没有索引的，大大降低了查询效率。
如果在两表中有相同字段，做联合查询的时候，要区别表名，否则会报错：
# course表跟basecourse表中都有name,price字段，所有会报错
mysql> select name, price from course, basecourse;
ERROR 1052 (23000): Column 'name' in field list is ambiguous
# 将表中相同的字段，区分表名就不会报错了
mysql> select course.name as course_name, basecourse.name as basecourse_name from course, basecourse;
+----------------+-----------------+
| course_name | basecourse_name |
+----------------+-----------------+
| 上海财经大学 | 上海 |
| 北京大学 | 上海 |
| 天津大学 | 上海 |
| 哈尔滨工业大学 | 上海 |
| 交通大学 | 上海 |
| 同济大学 | 上海 |
| 清华大学 | 上海 |
| 华北大学 | 上海 |
| 上海财经大学 | 北京 |
| 北京大学 | 北京 |
| 天津大学 | 北京 |
| 哈尔滨工业大学 | 北京 |
| 交通大学 | 北京 |
| 同济大学 | 北京 |
| 清华大学 | 北京 |
| 华北大学 | 北京 |
| 上海财经大学 | 天津 |
| 北京大学 | 天津 |
| 天津大学 | 天津 |
| 哈尔滨工业大学 | 天津 |
| 交通大学 | 天津 |
| 同济大学 | 天津 |
| 清华大学 | 天津 |
| 华北大学 | 天津 |
| 上海财经大学 | 哈尔滨 |
| 北京大学 | 哈尔滨 |
| 天津大学 | 哈尔滨 |
| 哈尔滨工业大学 | 哈尔滨 |
| 交通大学 | 哈尔滨 |
| 同济大学 | 哈尔滨 |
| 清华大学 | 哈尔滨 |
| 华北大学 | 哈尔滨 |
| 上海财经大学 | 北京 |
| 北京大学 | 北京 |
| 天津大学 | 北京 |
| 哈尔滨工业大学 | 北京 |
| 交通大学 | 北京 |
| 同济大学 | 北京 |
| 清华大学 | 北京 |
| 华北大学 | 北京 |
| 上海财经大学 | 上海 |
| 北京大学 | 上海 |
| 天津大学 | 上海 |
| 哈尔滨工业大学 | 上海 |
| 交通大学 | 上海 |
| 同济大学 | 上海 |
| 清华大学 | 上海 |
| 华北大学 | 上海 |
| 上海财经大学 | 北京 |
| 北京大学 | 北京 |
| 天津大学 | 北京 |
| 哈尔滨工业大学 | 北京 |
| 交通大学 | 北京 |
| 同济大学 | 北京 |
| 清华大学 | 北京 |
| 华北大学 | 北京 |
| 上海财经大学 | 华北 |
| 北京大学 | 华北 |
| 天津大学 | 华北 |
| 哈尔滨工业大学 | 华北 |
| 交通大学 | 华北 |
| 同济大学 | 华北 |
| 清华大学 | 华北 |
| 华北大学 | 华北 |
+----------------+-----------------+
64 rows in set (0.00 sec)
3.2：内连接(INNER JOIN)：
注意：内连接是左右连接的交集。
以下连接查询，使用如下数据：
# 创建一个学生信息表：
mysql> CREATE TABLE student_info (id INT, name CHAR(20), sex CHAR(4)) charset utf8;
Query OK, 0 rows affected (0.04 sec)
# 查看表结构:
mysql> desc student_info;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(20) | YES | | NULL | |
| sex | char(4) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.01 sec)
# 创建学生分数表
mysql> CREATE TABLE student_score (id INT, score CHAR(20), grade CHAR(4)) charset utf8;
Query OK, 0 rows affected (0.06 sec)
# 查询表结构
mysql> desc student_score;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| score | char(20) | YES | | NULL | |
| grade | char(4) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
# 往学生信息表中插入数据：
mysql> INSERT INTO student_info values(1, '亚索', '男'),(2, '小鱼儿', '中性'), (3, '发条', '女'), (4, '安妮', '女'), (5, '男刀', '男');
Query OK, 5 rows affected (0.02 sec)
Records: 5 Duplicates: 0 Warnings: 0
# 查看插入的数据：
mysql> select * from student_info;
+------+--------+------+
| id | name | sex |
+------+--------+------+
| 1 | 亚索 | 男 |
| 2 | 小鱼儿 | 中性 |
| 3 | 发条 | 女 |
| 4 | 安妮 | 女 |
| 5 | 男刀 | 男 |
+------+--------+------+
5 rows in set (0.00 sec)
# 往学生分数表中插入数据
mysql> INSERT INTO student_score values (1, 96, '优秀'), (2, 88, '良好'), (3, 60, '合格'), (4, 56, '不合格');
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
# 查看学生分数表中的数据
mysql> select * from student_score;
+------+-------+--------+
| id | score | grade |
+------+-------+--------+
| 1 | 96 | 优秀 |
| 2 | 88 | 良好 |
| 3 | 60 | 合格 |
| 4 | 56 | 不合格 |
+------+-------+--------+
4 rows in set (0.00 sec)
1.等值连接：
概念：使用等号(=)比较两表的连接列的值，取两表连接列值相等的记录。
语法：select [列] from [表1] inner join [表2] on [表1.列] = [表2.列]
举个栗子：
# 查询学生信息表跟学生分数表中id相等的信息
mysql> select student_info.*, student_score.* from student_info inner join student_score ON student_info.id = student_score.id;
+------+--------+------+------+-------+--------+
| id | name | sex | id | score | grade |
+------+--------+------+------+-------+--------+
| 1 | 亚索 | 男 | 1 | 96 | 优秀 |
| 2 | 小鱼儿 | 中性 | 2 | 88 | 良好 |
| 3 | 发条 | 女 | 3 | 60 | 合格 |
| 4 | 安妮 | 女 | 4 | 56 | 不合格 |
+------+--------+------+------+-------+--------+
4 rows in set (0.02 sec)
2.非等值连接：
概念：不使用等于号，比较两个表的连接列的值，与上面的栗子相反。
语法：select [列] from [表1] inner join [表2] on [表1.列] <> [表2.列]
举个栗子：
mysql> select student_info.*, student_score.* from student_info inner join student_score ON student_info.id <> student_score.id;
+------+--------+------+------+-------+--------+
| id | name | sex | id | score | grade |
+------+--------+------+------+-------+--------+
| 1 | 亚索 | 男 | 2 | 88 | 良好 |
| 1 | 亚索 | 男 | 3 | 60 | 合格 |
| 1 | 亚索 | 男 | 4 | 56 | 不合格 |
| 2 | 小鱼儿 | 中性 | 1 | 96 | 优秀 |
| 2 | 小鱼儿 | 中性 | 3 | 60 | 合格 |
| 2 | 小鱼儿 | 中性 | 4 | 56 | 不合格 |
| 3 | 发条 | 女 | 1 | 96 | 优秀 |
| 3 | 发条 | 女 | 2 | 88 | 良好 |
| 3 | 发条 | 女 | 4 | 56 | 不合格 |
| 4 | 安妮 | 女 | 1 | 96 | 优秀 |
| 4 | 安妮 | 女 | 2 | 88 | 良好 |
| 4 | 安妮 | 女 | 3 | 60 | 合格 |
| 5 | 男刀 | 男 | 1 | 96 | 优秀 |
| 5 | 男刀 | 男 | 2 | 88 | 良好 |
| 5 | 男刀 | 男 | 3 | 60 | 合格 |
| 5 | 男刀 | 男 | 4 | 56 | 不合格 |
+------+--------+------+------+-------+--------+
16 rows in set (0.01 sec)
注意：也可以使用’<‘或’>’ 等…
3.3：外连接：
1.左外连接(LEFT OUTER JOIN)：
概念：将左表的所有记录与右表符合条件的记录连接，返回的结果是内连接的结果跟左表不符合条件的记录，并在右表相应列中填NULL。
语法：select [列] from [表1] left join [表2] on [表1.列] = [表2.列]
举个栗子：
# 使用左查询，来查询学生信息表跟学生分数表的数据
mysql> select student_info.*, student_score.* from student_info left join student_score ON student_info.id = student_score.id;
+------+--------+------+------+-------+--------+
| id | name | sex | id | score | grade |
+------+--------+------+------+-------+--------+
| 1 | 亚索 | 男 | 1 | 96 | 优秀 |
| 2 | 小鱼儿 | 中性 | 2 | 88 | 良好 |
| 3 | 发条 | 女 | 3 | 60 | 合格 |
| 4 | 安妮 | 女 | 4 | 56 | 不合格 |
| 5 | 男刀 | 男 | NULL | NULL | NULL |
+------+--------+------+------+-------+--------+
5 rows in set (0.00 sec)
技巧：
- 假设A表在左不动，B表在A表的右边滑动，A表与B表通过一个关系来筛选B表的行。
A left join B on 条件，条件为真，则B表对应的行取出。A left join B on 条件，形成的是一个结果集，可以看成一张表，假设为C，也可以对C表做查询，where，group，having，order by，limit
2.右外连接(RIGHT OUTER JOIN)：
概念：与左外连接相反。
语法：select [列] from [表1] right join [表2] on [表1.列] = [表2.列]
举个栗子：
mysql> select student_info.*, student_score.* from student_info right join student_score ON student_info.id = student_score.id;
+------+--------+------+------+-------+--------+
| id | name | sex | id | score | grade |
+------+--------+------+------+-------+--------+
| 1 | 亚索 | 男 | 1 | 96 | 优秀 |
| 2 | 小鱼儿 | 中性 | 2 | 88 | 良好 |
| 3 | 发条 | 女 | 3 | 60 | 合格 |
| 4 | 安妮 | 女 | 4 | 56 | 不合格 |
+------+--------+------+------+-------+--------+
3.全外连接(FULL JOIN)：
概念：指将左表所有记录与右表所有记录进行连接，返回的结果除内连接的结果，还有左表与右表不符合条件的记录，并在左表与右表相应列中填NULL。
简单理解：结果是左右连接的并集。
注意：Mysql不支持全外连接。
3.4：自然连接(NATURAL JOIN):
概念：指自动将表中相同名称的列进行匹配，返回结果集。
语法：select [列] from [表1] natural join [表2]
举个栗子：
# 将学生信息跟学生分数表中的对应相同的列，进行连接，返回查询结果集
mysql> select student_info.*, student_score.* from student_info natural join student_score;
+------+--------+------+------+-------+--------+
| id | name | sex | id | score | grade |
+------+--------+------+------+-------+--------+
| 1 | 亚索 | 男 | 1 | 96 | 优秀 |
| 2 | 小鱼儿 | 中性 | 2 | 88 | 良好 |
| 3 | 发条 | 女 | 3 | 60 | 合格 |
| 4 | 安妮 | 女 | 4 | 56 | 不合格 |
+------+--------+------+------+-------+--------+
4.组合查询(union):
概念：
- 我们想一次性查询多条SQL语句，并将每一条
SELECT查询的结果合并成一个结果集返回。就需要用到Union操作符，将多个SELECT语句组合起来，这种查询被称为并（Union）或者复合查询。
使用场景：
- 从多表中查询出相似的数据，并且返回一个结果集。
- 从单个表中多次SELECT查询，将结果合并成一个结果集返回。
举个简单栗子：
# 简单的查询学生信息表中， 性别='男' 和 id = 3 的学生信息
mysql> select * from student_info where sex = '男';
+------+------+------+
| id | name | sex |
+------+------+------+
| 1 | 亚索 | 男 |
| 5 | 男刀 | 男 |
+------+------+------+
2 rows in set (0.00 sec)
mysql> select * from student_info where id = 3;
+------+------+------+
| id | name | sex |
+------+------+------+
| 3 | 发条 | 女 |
+------+------+------+
1 row in set (0.00 sec)
使用union语句将上面的栗子进行组合：
mysql> select * from student_info where sex = '男'
UNION
select * from student_info where id = 3;
+------+------+------+
| id | name | sex |
+------+------+------+
| 1 | 亚索 | 男 |
| 5 | 男刀 | 男 |
| 3 | 发条 | 女 |
+------+------+------+
3 rows in set (0.00 sec)
# 当然也可以使用or来进行查询
注意规则：
1.Union必须由两条或者两条以上的SELECT语句组成，语句之间使用Union连接。
2.Union`中的每个查询必须包含相同的列、表达式或者聚合函数，他们出现的顺序可以不一致（这里指查询字段相同，表不一定一样）。
3.列的数据类型必须兼容，兼容的含义是必须是数据库可以隐含的转换他们的类型。
1.Union All的使用：
概念：Union All 操作符来取消合并功能，简单说可返回重复的数据，就是where子句完成不了的工作。
举个栗子：
mysql> select * from student_info where sex = '男'
UNION ALL
select * from student_info where id >= 3;
+------+------+------+
| id | name | sex |
+------+------+------+
| 1 | 亚索 | 男 |
| 5 | 男刀 | 男 |
| 3 | 发条 | 女 |
| 4 | 安妮 | 女 |
| 5 | 男刀 | 男 |
+------+------+------+
5 rows in set (0.00 sec)
2.排序：
使用Union组合查询时，只能使用一条order by子句对结果集进行排序，而且必须出现在最后一条出现的SELECT语句之后。因为``Union`不允许对于部分结果集进行排序，只能针对最终检索出来的结果集进行排序。
注意：由于在多表组合查询时候，可能表字段并不相同。所以，在对于结果集排序的时候需要使用检索出来的共同字段。
举个栗子：
# 将学生信息表 跟 学生分数表 union 然后再根据id 进行排序
mysql> (select * from student_info where sex = '男')
Union
(select * from student_score where score < 90 and score > 60)
order by id desc;
+------+------+------+
| id | name | sex |
+------+------+------+
| 5 | 男刀 | 男 |
| 2 | 88 | 良好 |
| 1 | 亚索 | 男 |
+------+------+------+
3 rows in set (0.00 sec)
3.多表组合查询：
多表查询的时候，并不要求两个表完全相同，仅需要检索出来的字段结构相似就可以了。
举个栗子：
mysql> select id, name, sex from student_info
Union
select id, score, grade from student_score;
+------+--------+--------+
| id | name | sex |
+------+--------+--------+
| 1 | 亚索 | 男 |
| 2 | 小鱼儿 | 中性 |
| 3 | 发条 | 女 |
| 4 | 安妮 | 女 |
| 5 | 男刀 | 男 |
| 1 | 96 | 优秀 |
| 2 | 88 | 良好 |
| 3 | 60 | 合格 |
| 4 | 56 | 不合格 |
+------+--------+--------+
9 rows in set (0.00 sec)
结论：
- 从上面检索结果能看到，我们将两个表的数据进行了组合，
union检索遇到不一致的字段名时，会使用第一条select的查询字段名称，或者也可以使用别名进行改变查询字段名称。
853
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
