`MYSQL`基本操作-select 查询语句

针对数据表里面的每条记录，select查询语句叫做数据查询语言（DQL）

select的语法格式

SELECT
{* | <字段列名>}
[
FROM <表 1>, <表 2>…
[WHERE <表达式>
[GROUP BY <group by definition>
[HAVING <expression> [{<operator> <expression>}…]]
[ORDER BY <order by definition>]
[LIMIT[<offset>,] <row count>]
]

示例表：book

bookid	bookname	typeid	bookauthor	bookpublisher	bookprice	borrowsum
013452	离散数学	2	张小新	机械工业出版社	45.5	10
R/345677	中医的故事	8	李奇德	国防工业出版社	20	5
TH/2345	机械设计手册	7	黄明凡	人民邮电出版社	40	10
TP/3452	JSP程序设计案例	3	刘城清	电子工业出版社	42.8	8
TP39/1712	Java程序设计	3	陈永红	机械工业出版社	35.5

简单查询

查询表的所有字段

select * from book;

* 通配符，代表查询所有字段
使用 * 时，只能按照数据表中字段的顺序进行排列，不能自定义字段排序
建议：不知道所需查询的列名称时，才用 * ，否则获取不需要的列数据会降低查询和所使用应用程序的效率

查询表的部分字段

select bookid, bookname from book;

可以指定表的所有字段，然后更改字段顺序，这种查询所有字段的写法比较灵活
也可以只指定某几个字段，多个字段用 , 隔开

查询表的字段并添加计算

select bookid, bookname,bookprice * borrowsum from book;

使用别名

谨记：as是可以忽略不写的哦

<表名> [AS] <别名> 
<字段名> [AS] <别名>

select bookid as 图书ID, bookname  图书名称,bookprice * borrowsum as 图书总价 from book;

注意：表名取的别名不能和其他表名相同，字段名取的别名不能和其他字段名相同

消除重复行

distinct只能在select语句中使用
distinct必须在所有字段前面
如果有多个字段需要去重，则会对多个字段进行组合去重，即所有字段的数据重复才会被去重

SELECT DISTINCT <字段名>,<字段名>, FROM <表名>;

select distinct borrowsum from book;

select count(distinct typeid) from yyTest;

注意：当使用distinct的时候，只会返回指定的字段，其他字段都不会返回，所以查询语句就变成去重查询语句

条件查询

条件查询应该是作为测试平时用到最多的关键字了！！
它可以用来指定查询条件，减少不必要的查询时间

WHERE 查询条件

五种查询条件：

比较运算符、逻辑运算符
- =：等于
- <=>：安全等于
- !=、<>：不等于
- <、>、<=、>=：小于、大于、小于等于、大于等于
- and、&&：所有查询条件均满足才会被查询出来
- or、||：满足任意一个查询条件就会被查询出来
- xor：满足其中一个条件，并且不满足另一个条件时，才会被查询出来
between and 关键字
is null 关键字
in、exist 关键字
like 关键字

单一条件的查询栗子

一般单一条件查询用的就是比较运算符

select * from book where borrowsum > 10;
select * from book where typeid = 1;
select * from book where typeid != 1;
select * from book where borrowsum > 8;
select * from book where borrowsum >= 10;

多条件的查询栗子

多条件的查询都需要使用逻辑运算符

select * from book where borrowsum >= 10 and typeid =3;

指定范围查询

between and可以判断值是否在指定范围内，包含边界
取值1：范围的起始值
取指2：范围的终止值
NOT：取反，不在取值范围内的值将被返回

select * from book where borrowsum < 10 or borrowsum > 30;
select * from book where borrowsum < 10 or borrowsum > 30;
select * from book where borrowsum between 10 and 30;
select * from book where borrowsum not between 10 and 30;

查询值为空的行

is null是一个关键字来的，用于判断字段的值是否为空值（NULL）
空值 ≠ 0，也 ≠ 空字符串""

select * from book where borrowsum is null;

指定集合查询

select * from book where bookname in ('离散数学','Java程序设计');
select * from book where bookname not in ('离散数学','Java程序设计');

模糊查询

LIKE  '字符串'
NOT LIKE  '字符串'

NOT：取反，不满足指定字符串时匹配
字符串：可以是精确的字符串，也可以是包含通配符的字符串
LIKE支持 % 和 _ 两个通配符
% 应该是最常用的通配符了，它代表任意长度的字符串，包括0，如：a%b 表示以字母 a 开头，以字母 b 结尾的任意长度的字符串
_ 只能代表单个字符，字符的长度不能等于0，即字符长度必须等于1；
如果查询的字符串包含%，可以使用 \ 转义符，如： like "%\%"
如果需要区分大小写，需要加入 binary 关键字，如： like binary "TEST_";

select * from book where bookname like 'java_';
select * from book where bookname like '%java';
select * from book where bookname like '%java%';
select * from book where bookname like 'java%';

查询排序

order by的语法格式

ORDER BY <字段名> [,<字段名>...] [ASC | DESC]

ASC：升序排序，默认值
DESC：降序排序

`ASC`

select * from book order by bookprice asc;

`DESC`

select * from book order by bookprice desc;

多字段排序

select * from book order by borrowsum asc ,typeid desc;
select * from book order by borrowsum, typeid desc;

如果字段值是NULL，则当最小值处理
如果指定多个字段排序，则按照字段的顺序从左往右依次排序
对多个字段排序时，只有第一个排序字段有相同的值，才会对第二个字段进行排序，以此类推
如果第一个排序字段的所有数据都是唯一的，将不会对第二个排序字段进行排序，以此类推
按字母（A-Z进行排序，大小写不敏感）

限制条数limit

limit的三种用法

指定初始位置
不指定初始位置
结合offset使用

limit指定初始位置

LIMIT 初始位置，记录数

select * from book limit 0, 3;
-- 从第1条记录开始，一共返回三条记录
select * from book limit 2, 2;
-- 从第3条记录开始，一共返回两条记录

注意：初始位置和记录数都必须为正整数

limit不指定初始位置的栗子

LIMIT 记录数

select * from book limit 3;
-- 一共返回3条记录

记录数大于表里总记录数的话，就返回所有记录
默认初始位置就是第1条记录

limit + offset组合使用的栗子

LIMIT 记录数 offset 初始位置

select * from book limit 5 offset 1;
-- 从第2条记录开始，一共返回五条记录

和 limit 初始位置, 记录数用法一样，只是多了个offset，参数位置换了下而已

聚合函数

最大值(max)

select max(bookprice) as '最贵的书' from book;

最小值(min)

select min(borrowsum) as '最受嫌弃的书...' from book;

数量(count)

select count(bookid) from book;

总和(sum)

select sum(borrowsum) from book;

平均值(avg)

select avg(bookprice) from book;

分组查询

group by 关键字可以根据一个或多个字段对查询结果进行分组
group by 一般都会结合Mysql聚合函数来使用
如果需要指定条件来过滤分组后的结果集，需要结合 having 关键字；原因：where不能与聚合函数联合使用并且where 是在 group by 之前执行的

 GROUP BY  <字段名>[,<字段名>,<字段名>]

简单分组

select borrowsum, count(bookid) from book group by borrowsum;

筛选分组结果

having关键字对group by分组后的数据进行过滤
having支持where的所有操作符和语法

select borrowsum, count(bookid) from book group by borrowsum having count(bookid) = 1;

分组排序

select borrowsum, count(bookid) from book group by borrowsum order by count(bookid) desc;

统计功能分组查询

select 
	borrowsum,group_concat(bookname) 
from book 
group by borrowsum 
order by borrowsum desc;
-- group_concat将group by产生的同一个分组中的值连接起来 返回一个字符串结果，将分组后每个组内的值都显示出来

多个分组查询

建表语句以及插入数据

-- ----------------------------
-- Table structure for choose_course
-- ----------------------------
DROP TABLE IF EXISTS `choose_course`;
CREATE TABLE `choose_course` (
  `course_name` char(10) DEFAULT NULL,
  `semester_number` int(11) DEFAULT NULL,
  `student_name` char(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of choose_course
-- ----------------------------
INSERT INTO `choose_course` VALUES ('语文', '1', '李雷');
INSERT INTO `choose_course` VALUES ('语文', '1', '韩梅梅');
INSERT INTO `choose_course` VALUES ('语文', '1', '露西');
INSERT INTO `choose_course` VALUES ('语文', '2', '莉莉');
INSERT INTO `choose_course` VALUES ('语文', '2', '格林');
INSERT INTO `choose_course` VALUES ('数学', '1', '李雷');
INSERT INTO `choose_course` VALUES ('数学', '1', '名字真难起...');

SELECT
	course_name,
	semester_number,
	count('hello')
FROM
	choose_course
GROUP BY
	course_name,
	semester_number;

多个字段分组查询时，先按照第一个字段分组，如果第一个字段有相同值，则把分组结果再按第二个字段进行分组，以此类推
如果第一个字段每个值都是唯一的，则不会按照第二个字段再进行分组了

group by 字句也和where条件语句结合在一起使用。当结合在一起时，where在前，group by 在后。即先对select xx from xx的记录集合用where进行筛选，然后再使用group by 对筛选后的结果进行分组使用having字句对分组后的结果进行筛选
需要注意having和where的用法区别：

having只能用在group by之后，对分组后的结果进行筛选(即使用having的前提条件是分组)。
where肯定在group by 之前
where后的条件表达式里不允许使用聚合函数，而having可以。

当一个查询语句同时出现了where,group by,having,order by的时候，执行顺序和编写顺序是：

执行where xx对全表数据做筛选，返回第1个结果集。
针对第1个结果集使用group by分组，返回第2个结果集。
针对第2个结果集中的每1组数据执行select xx，有几组就执行几次，返回第3个结果集。
针对第3个结集执行having xx进行筛选，返回第4个结果集。
针对第4个结果集排序。

多表查询

多表查询的区别

cross join：交叉连接
inner join：内连接
left join：左外连接
right join：右外连接
union、union all：全连接

查询类型	简述	图表
inner join	获取两个表中字段相互匹配关系的记录即两表记录都不为null才会返回
left join	查询出左表中所有的记录，右表中满足连接条件的记录，右表为空的字段补null
right join	获取右表所有记录，左表中满足连接条件的记录，左表为空的字段补null
union	获取左右表所有记录
cross join	两张表的笛卡尔积

内连接

示例表：book

bookid	bookname	typeid	bookauthor	bookpublisher	bookprice	borrowsum
013452	离散数学	2	张小新	机械工业出版社	45.5	10
R/345677	中医的故事	8	李奇德	国防工业出版社	20	5
TH/2345	机械设计手册	7	黄明凡	人民邮电出版社	40	10
TP/3452	JSP程序设计案例	3	刘城清	电子工业出版社	42.8	8
TP39/1712	Java程序设计	3	陈永红	机械工业出版社	35.5

示例表：reader

readerid	readername	readerpass	retypeid	readerdate	readerstatus
0016	苏小东	123456	1	1999/9/9 0:00:00	有效
0017	张明	123456	1	2010/9/10 0:00:00	有效
0018	梁君红	123456	1	2010/9/10 0:00:00	有效
0021	赵清远	123456	2	2010/7/1 0:00:00	有效
0034	李瑞清	123456	3	2009/8/3 0:00:00	有效
0042	张明月	123456	4	1997/4/23 0:00:00	有效

示例表：readertype

retypeid	typename	borrowquantity	borrowday
1	学生	10	30
2	教师	20	60
3	管理员	15	30
4	职工	15	20

使用from子句

SELECT * FROM book, reader;
-- 将两个表合并成一张表返回
select book.bookid,book.bookname,reader.readerid,reader.readername from book,reader;
-- 将两张表的指定字段合并返回

在where中指定连接条件

SELECT * FROM readertype, reader 
WHERE reader.retypeid = readertype.retypeid;

使用join关键字连接—使用inner join

SELECT <字段名> FROM <表1> INNER JOIN <表2> [ON子句]
-- inner join通过 on 来设置条件表达式，如果没有加on的话，inner join和cross join是相同的
-- cross join ... on  和  inner join ... on  其实效果也是一样的（但在标准sql中，cross join是不支持on的，只是Mysql支持）
-- inner join 可以连接 ≥ 两个的表
-- inner join 也可以使用 where 来指定连接条件，但是 inner join ... on 是官方标准写法，而且 where 可能会影响查询性能
-- inner join 也可以只写 join 不加 inner

select * from readertype join reader on reader.retypeid = readertype.retypeid;

select * from readertype inner join reader on reader.retypeid = readertype.retypeid;

为数据表使用别名

select readertype.retypeid,readertype.borrowquantity,reader.readerstatus
from readertype join reader on reader.retypeid = readertype.retypeid;
select a.retypeid,a.borrowquantity,b.readerstatus 
from readertype as a join reader as b on b.retypeid = a.retypeid;

外连接

示例表：user

user_id	user_name	user_sex
1	张三	1
2	李四	1
3	王五	1
4	赵六	1
5	钱七	1
6	孙八	1
7	周老九	1
8	吴老十	1

示例表：user_detail

user_detail_id	user_detail_address	user_detail_phone	user_detail_uid
1	河南平顶山	15639279531	1
2	河南平顶山	15639279532	2
3	河南平顶山	15639279533	3
4	河南平顶山	15639279534	4
11	河南平顶山	15639279521	11
12	河南平顶山	15639279522	12
13	河南平顶山	15639279523	13
14	河南平顶山	15639279524	14

外连接分为两种：left join、right join
外连接显示的内容要比内连接多，是对内连接的补充
left join的主表是左表，从表是右表
right join的主表是右表，从表是左表
外连接会返回主表的所有数据，无论在从表是否有与之匹配的数据，若从表没有匹配的数据则默认为空值（NULL）
外连接只返回从表匹配上的数据
重点：在使用外连接时，要分清查询的结果，是需要显示左表的全部记录，还是右表的全部记录

SELECT <字段名> FROM <表1> LEFT OUTER JOIN <表2> <ON子句>
SELECT <字段名> FROM <表1> RIGHT OUTER JOIN <表2> <ON子句>

outer可以省略，只写 left join 、 right join
on是设置左连接的连接条件，不能省略

left join 的栗子

select * from user left join user_detail on user.user_id = user_detail.user_detail_uid;

左连接后的检索结果是显示 user的所有数据和 user_detail中满足where 条件的数据。

right join 的栗子

select * from user right join user_detail on user.user_id = user_detail.user_detail_uid;

右连接后的检索结果是user_detail的所有数据和user中满足where 条件的数据。

交叉连接 cross join

笛卡尔积

假设，有两个集合A、B

A = {1,2}B = {3,4}

集合A和集合B的笛卡尔积 = 集合A * 集合B；即，两表相乘，如下：

AxB = {(1,3),(1,4),(2,3),(2,4)}

在Mysql中，表与表之间的笛卡尔积不建议使用，会产生大量不合理的数据；

SELECT <字段名> FROM <表1> CROSS JOIN <表2> 
[WHERE]SELECT <字段名> FROM <表1>, <表2> [WHERE子句]

以下两句, 效果一样

select * from user cross join user_detail ;

select * from user,user_detail;