内容:MYSQL基本操作-select 查询语句【续】
最大值(max)
select max(bookprice) as '最贵的书' from book;
select min(borrowsum) as '最受嫌弃的书...' from book;
select count(bookid) from book;
select sum(borrowsum) from book;
select avg(bookprice) from book;
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的用法区别:
示例表:book
示例表: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 小于最小的
选择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 );
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 语句中出现的字段 子查询的结果集通常会作为其外层查询的数据源或用于条件判断