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的查询字段名称
,或者也可以使用别名进行改变查询字段名称。