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 查询条件

五种查询条件:

  1. 比较运算符、逻辑运算符

    • =:等于
    • <=>:安全等于
    • !=、<>:不等于
    • <、>、<=、>=:小于、大于、小于等于、大于等于

    • and、&&:所有查询条件均满足才会被查询出来
    • or、||:满足任意一个查询条件就会被查询出来
    • xor:满足其中一个条件,并且不满足另一个条件时,才会被查询出来
  2. between and 关键字

  3. is null 关键字

  4. in、exist 关键字

  5. 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的三种用法

  1. 指定初始位置
  2. 不指定初始位置
  3. 结合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;

以下两句, 效果一样

select * from user cross join user_detail on user_detail.user_detail_uid = user.user_id;
select * from user,user_detail where user.user_id = user_detail.user_detail_uid;

自连接

  • 一张表假设为 两张一样的表,分别对两张表(一样的两张表)进行联结得到笛卡儿积,再对笛卡尔积中的结果根据where进行 行过滤。
  • 自连接是同一个表不同实例的连接操作
  • 自连接必须指定别名(aliasName)区分不同实例
SELECT	b2.bookname,b2.borrowsum 
FROM	book AS b2,book AS b1 
WHERE	b2.borrowsum > b1.borrowsum AND b1.bookname = '中医的故事' 
ORDER BY b2.borrowsum DESC,b2.bookname;
SELECT	b2.bookname,b2.borrowsum 
FROM book AS b2 join book AS b1 on b2.borrowsum > b1.borrowsum 	
where b1.bookname = '中医的故事' 
ORDER BY b2.borrowsum DESC,b2.bookname;
  • 先复制出两张一样的表
  • 在对第一张表进行where过滤,得到bookname 等于中医的故事的borrowsum
  • 将表一与表二进行联结得到borrowsum 大于中医的故事的borrowsum
  • 再将表二中的bookname和borrowsum的信息展示出来

别名:此查询中两张表其实是一样的表,DBMS并不知道你要引用的是哪张表,所以解决这个问题需要用到别名

联合查询

  • 其实Mysql并没有全连接,Oracle才有全连接(full join)
  • 但是在MySQL中,union关键字可以达到同样的效果,所以这里也要介绍下union
[sql1]UNION [ALL | DISTINCT][sql2]UNION [ALL | DISTINCT][sql3]....
  • sql1、sql2、sql3:平时写的查询 sql,可以连接很多条 sql
  • ALL:可选参数,返回所有结果集,包含重复数据
  • distinct:可选参数,删除结果集中重复的数据(默认只写 union 也会删除重复数据,所以不加也没事)
select * from user left join user_detail on user.user_id = user_detail.user_detail_uid 
union 
select * from user right join user_detail on user.user_id = user_detail.user_detail_uid;
  • 使用 union 连接的多条sql,每个 sql 查询出来的结果集的字段名称要一致**【只需要名称一致即可,顺序可以不同,但建议相同】**,可以看看下面的栗子
  • 最终 union 连接查询的结果集的字段顺序会以第一个 sql 查出来结果集的字段顺序为基准

子查询

  • 子查询在我们查询方法中是比较常用的,通过子查询可以实现多表查询
  • 子查询是指:将一个查询语句嵌套在另一个查询语句中
  • 子查询可以在select、update、delete语句中使用,还可以进行多层嵌套
WHERE <表达式> <操作符> (子查询)
  • 操作符可以是比较运算符、in、not in、exists、not exists
  • not 当然就是取反啦

使用比较运算符的子查询

  • 查询价格高于机械设计手册的书籍的书籍号, 书籍名称, 书籍单价, 价格从高到低排序
SELECT	bookid,	bookname,bookprice
FROM	book 
WHERE	bookprice > ( SELECT bookprice FROM book WHERE bookname = '机械设计手册' ) 
ORDER BY bookprice DESC;
  • 查询类别是学生的读者信息, 包括读者编号, 读者姓名, 发证日期
SELECT	readerid,readername,readerdate 
FROM	reader 
WHERE	retypeid = ( SELECT retypeid FROM readertype WHERE typename = '学生' );

也可以使用连表查询…

SELECT	readerid,readername,readerdate 
FROM	reader	JOIN readertype ON readertype.retypeid = reader.retypeid 	AND typename = '学生';
  • 子查询的功能其实通过表连接(join)也可以完成
  • 一般来说,表连接(内连接、外连接等)都可以用子查询查询,但反过来却不一定,有的子查询不能用表连接来替换
  • 子查询比较灵活,适合作为查询的筛选条件
  • 表连接更适合查看连接表之后的数据集

[not] in 子查询

  • 查询已经借出的书籍id, 书籍名称
SELECT	bookid,	bookname 
FROM	book 
WHERE	bookid IN ( SELECT bookid FROM bookstorage WHERE bookstatus = '借出' );
  • 查询没有借出(在馆)的书籍id, 书籍名称
SELECT	booki,	bookname 
FROM	book
WHERE	bookid NOT IN ( SELECT bookid FROM bookstorage WHERE bookstatus = '借出' );
SELECT	bookid,	bookname 
FROM	book 
WHERE	bookid IN ( SELECT bookid FROM bookstorage WHERE bookstatus != '借出' );

any 子查询

> any 大于最小的 < any 小于最大的 = any 相当于in();

  • 选择book表中, 价格大于机械工业出版社最便宜价格的图书(图书ID, 图书名称, 出版社, 价格)
SELECT	booki,	bookname,bookpublisher,	bookprice 
FROM	book 
WHERE	bookprice > ANY ( SELECT bookprice FROM book WHERE bookpublisher = '机械工业出版社' );

all 子查询

> all 大于最大的

< all 小于最小的

  • 选择book表中, 价格大于机械工业出版社最贵价格的图书(图书ID, 图书名称, 出版社, 价格)
SELECT	bookid,	bookname,bookpublisher,	bookprice 
FROM	book 
WHERE	bookprice > all ( SELECT bookprice FROM book WHERE bookpublisher = '机械工业出版社' );

[not] exists子查询

  • 查看图书类别表中没有图书的类别id类别名称
SELECT	typeid,	typename 
FROM	booktype 
WHERE	NOT EXISTS ( SELECT * FROM book WHERE booktype.typeid = book.typeid );
  • 查看图书类别表中有图书的类别id类别名称
SELECT	typeid,	typename 
FROM	booktype 
WHERE	EXISTS ( SELECT * FROM book WHERE booktype.typeid = book.typeid );

in 和 exists的一个比较

in exists
当表达式与子查询返回的结果集中的某个值相等时,返回 TRUE,否则返回 FALSE; 用于判断子查询的结果集是否为空,若子查询的结果集不为空,返回 TRUE,否则返回 FALSE;
适合外表大而内表小的情况 适合内表大而外表小的情况
无论哪个表大,用 not exists 都比 not in 速度快
1、A是表达式,B是子查询结果集2、若A在B里面,则返回True

总结

子查询语句可以嵌套在 sql 语句中任何表达式出现的位置

字段、表名、查询条件都可以嵌套子查询!

select <子查询> from <表名> where <查询条件>
select <字段> from <子查询> as <别名> where <查询条件>
select <字段> from <表名> where <子查询>

常见错误写法

select * from (select * from emp);

这样写是会报错的,因为没有给子查询指定别名

正确写法

select * from (select * from emp) as t;

如果<表名>嵌套的是子查询,必须给表指定别名,一般会返回多行多列的结果集,当做一张新的临时表

只出现在子查询中而没有出现在父查询中的表不能包含在输出列中

  • 多层嵌套子查询的最终结果集只包含父查询(最外层的查询)的select 语句中出现的字段
  • 子查询的结果集通常会作为其外层查询的数据源或用于条件判断

数据练习举例来源于:猫哥的技术博客

上一篇 下一篇

评论 | 0条评论