电商网站建设与课程设计,广告推广有哪些平台,搜素引擎优化,wordpress 同步 微信系列文章目录前言一、简单查询SELECT子句SELECT后面之间跟列名DISTINCT,ALL列表达式列更名WHERE子句WHERE子句中可以使用的查询条件比较运算BETWEEN...AND...集合查询#xff1a;IN模糊查询LIKE空值比较#xff1a;IS NULL多重条件查询SELECT 的基本结构ORDER BY子句排序聚集…
系列文章目录前言一、简单查询SELECT子句SELECT后面之间跟列名DISTINCT,ALL列表达式列更名WHERE子句WHERE子句中可以使用的查询条件比较运算BETWEEN...AND...集合查询IN模糊查询LIKE空值比较IS NULL多重条件查询SELECT 的基本结构ORDER BY子句排序聚集函数分组查询【GROUP BY和HAVING 子句】二、嵌套子查询比较运算中使用子查询带有IN的子查询SOME(子查询)ALL(子查询)EXISTS子查询三、多表连接查询内连接外连接左外连接右外连接全外连接四、复杂查询复杂查询UNION [ALL]前言
一、简单查询
select 表示查询 from 后接表名表示查询哪张表 where 是筛选条件满足条件的会被查询出来
SELECT子句
SELECT后面之间跟列名
【例】查询 Student 表中的学号、姓名、班级
SELECT 学号、姓名、班级
FROM Student【例】查询 Student 表中全部数据
SELECT 学号、年龄、姓名、性别、联系方式、班级
FROM Student等价于
SELECT * FROM Student当想要选取一个表中全部列时全部列可以用【*】表示
DISTINCT,ALL
如果在结果中重复的数据不想显示出来可以使用distinct关键字 对比结果 【例】
SELECT pubilsh FROM Book
对比
SELECT DISTINCT pubilsh FROM Book也可以指明不要去除重复元祖使用ALL关键字
SELECT ALL sex FROM Student列表达式
【例】查询每一本书九折后的价格
Book表 book_IDnameprice1A025B1计算机文化基础28.002A025B2C语言58.003A025B3JAVA25.004A025B4Python27.00
SELECT book_ID,name,price*0.9 FROM Book列更名
SQL 提供了为关系表和属性重新命名的机制。
经过计算的列、函数的列和常量列的显示结果都没有列名也可以通过这样的方式指定列名。
语法格式旧列名 | 表达式 [ AS ] 新列名 或新列名 旧列名 | 表达式 【例】
SELECT sno AS 学号、name AS 姓名、bno AS 班级
FROM StudentWHERE子句
语法格式
SELECT 列名列表 FROM 表名
WHERE 条件表达式【例】查询成绩大于90分的学生的全部信息
SELECT * FROM Student
WHERE Score 90WHERE子句中可以使用的查询条件 比较运算
【例】查询所有的女老师的信息
Select * from Teacher where sex女【例】查询学生成绩在60到90分之间的学生
SELECT * FROM Student
WHERE Score 60 AND Score 90【例】查询“高等教育出版社”或者“清华大学出版社”出版的图书 SELECT * FROM Book WHERE publish 高等教育出版社 or publish 清华大学出版社BETWEEN…AND…
用法BETWEEN下限值AND上限值
【例】查询学生成绩在60到90分之间的学生
SELECT * FROM Student
WHERE Score BETWEEN 60 AND 90等价于
SELECT * FROM Student
WHERE Score 60 AND Score 90它可以查找上限值与下限值之间的元组也可以查找不在上限值与下限值之间的元组
【例】查询学生成绩不在60到100分之间的学生
SELECT * FROM Student
WHERE Score NOT BETWEEN 60 AND 100等价于
SELECT * FROM Student
WHERE Score 60 OR price 100集合查询IN
【例】查找“机械工业出版社”“清华大学出版社”“高等教育出版社”出版的全部图书
SELECT * FROM Book
WHERE publish IN(机械工业出版社,清华大学出版社,高等教育出版社)【例】查找不是“机械工业出版社”“清华大学出版社”“高等教育出版社”出版的全部图书
SELECT * FROM Book
WHERE publish NOT IN (机械工业出版社,清华大学出版社,高等教育出版社)模糊查询LIKE
LIKE的用法 列名 LIKE 字符串
在字符串中我们可以使用通配符 _代表任意一个字符 %代表任意多个字符
【例】查询姓全部 “张” 的读者的信息。
SELECT * FROM Reader
WHERE name LIKE 张%【例】查询1999年出生的学生的信息
SELECT * FROM Student
WHERE birthdate LIKE %1999%空值比较IS NULL
【例】查询性别为空的学生的信息
SELECT * FROM Student
WHERE sex IS NULL【例】查询出生日期不为空的学生的姓名
SELECT name FROM Student
WHERE birthdate IS not NULL多重条件查询
【例】查询1992年以后出生的女学生的姓名
SELECT name FROM Student
WHERE birthdate 1992-1-1 AND sex女SELECT 的基本结构
SELECT子句选择表中列 WHERE子句筛选表中元组 ORDER BY子句对查询结果排序 聚集函数统计 GROUP BY子句分组查询 HAVING子句对分组结果筛选
ORDER BY子句排序
ORDER BY 列名 [ASC|DESC][,…n]ASC表示升序排序DESC表示降序排序 默认情况为升序排序
• 注对于空值若按照升序排序含空值的元组将最后 显示。若按降序排序空值的元组将最先显示
【例】查询学生的信息按出生日期的升序显示
SELECT * FROM Student
ORDER BY birthdate ASC【例】查询老师的信息按出生日期的降序显示
SELECT * FROM Teacher
ORDER BY birthdate DESC【例】查询图书的信息查询结果按照出版社的名称升序排序同一出版社的按照价格的降序排序。
SELECT * FROM Book
ORDER BY publish ASC,price DESC聚集函数
聚集函数也叫集合函数方便用户统计一些数据。 COUNT(*) 统计表中元组个数 COUNT(列名)统计本列列值个数 SUM(列名)计算列值总和必须是数值型列 AVG(列名)计算列值平均值必须是数值型列 MAX(列名)求列值最大值 MIN(列名)求列值最小值。
【例】统计全部学生的平均成绩
SELECT AVG(Score) FROM Student【例】查询最高的学生成绩
SELECT MAX(Score) FROM Student【例】统计老师的总人数
SELECT COUNT(*) FROM Teacher聚合函数不能出现在 WHERE 子句中
分组查询【GROUP BY和HAVING 子句】
【例】统计每个出版社的出版图书的数目 分析如果能够将所有的图书按照出版社的名称进行分组然后我们在统计每一组的元组的个数我们就能能到得到期望的数据。如图
Book_IDNameAuthorPublicPrice1256B10001数据库原理张三电子工业出版社25.002256B10001离散数学NULL高等教育出版社28.003256B10001线形数学李四高等教育出版社51.004256B10001大学语文张龙机械工业出版社31.005256B10001C语言赵虎机械工业出版社22.006256B10001JAVA王朝清华大学出版社42.007256B10001Python马汉清华大学出版社21.00
可以使用GROUP BY 列名进行分组
在列名上值相同的元组被分在一组该列称为分组依据列。然后可以使用聚集函数统计每一组的数据。
SELECT COUNT(*) , publish FROM Book
GROUP BY publish【例】统计每个人所借图书的数目。
SELECT COUNT(book_ID), Reader_id
FROM Borrow
GROUP BY Reader_id
Having COUNT(book_ID)2【例】统计每个出版社出版图书的平均价格并显示每个出版社的名称
SELECT publish, AVG(price) AS 平均价格
FROM Book
GROUP BY publish【例】查询出版图书平均价格高于30元的出版社名称并显示其图书平均价格。 HAVING 子句用于对分组统计后的结果进行筛选。满足HAVING 子句条件将会保留在结果中
SELECT publish,AVG(price) FROM Book
GROUP BY publish
HAVING AVG(price)30【例】查询出版图书多于2本的出版社名称和出版图书数目
SELECT publish, COUNT(*) FROM Book
GROUP BY publish
HAVING COUNT(*)2二、嵌套子查询
在SQL语言中一个SELECTFROM语句称为一个查询块。
如果一个SELECT语句嵌套在另一个SELECT、INSERT、UPDATE或DELETE语句中则称之为子查询或内层查询而包含子查询的语句则称为主查询或外层查询。
执行顺序先内层后外层先子查询后主查询
比较运算中使用子查询
【例】查询成绩最好的学生的姓名
SELECT name FROM Student WHERE Score (SELECT MAX(Score) FROM Student)带有IN的子查询
【例】查询与C语言在同一出版社的图书信息
SELECT * FROM Book WHERE publish IN (SELECT publish FROM Book WHERE nameC语言)【例】查询王旭所借图书的图书编号
SELECT book_ID FROM Borrow WHERE reader_ID IN (SELECT reader_ID FROM Reader WHERE name王旭)【例】查询王旭所借的图书的名称
SELECT name FROM Book WHERE book_ID IN(SELECT book_ID FROM Borrow WHERE reader_ID IN (SELECT reader_ID FROM Reader WHERE name王旭))查询过程 第1步查询王旭的reader_ID。 第2步依据reader_ID在Borrow表中找王旭所借图书的book_ID 第3步依据book_ID在Book表中找到图书名称。
【例】查询借书价格在20-40之间的读者的姓名
select name from reader where reader_id in(select reader_id from borrow where book_id in(select book_id from book where price between 20 and 40))查询张三 ‘借阅’ 计算机文化基础’的日期
select borrowdate from borrow
where reader_id in(select reader_id from reader where name张三)and book_id in(select book_id from book where name计算机文化基础)查询借书价格在20-40之间的读者的姓名
select name from reader where reader_id in(select reader_id from borrow where book_id in(select book_id from book where price between 20 and 40))SOME(子查询)
表示子查询的结果集合中某一个元素
【例】查询除不是最低价格外的所有图书
SELECT * FROM Book WHERE priceSOME(SELECT price FROM Book)【例】查询价格最低的图书信息
SELECT * FROM Book WHERE NOT(priceSOME(SELECT price FROM Book))ALL(子查询)
表示子查询的全部结果
【例】查询书价最高的图书的信息
SELECT * FROM Book WHERE price ALL(SELECT price FROM Book)【例】查询评价书价最高的出版社名称
SELECT * FROM Book
GROUP BY public
HAVING AVG(price)ALL(SELECT AVG(price) FROM BookGROUP BY public)EXISTS子查询
判断子查询是否存在结果
当子查询存在结果时EXISTS(子查询)返回值为true否则返回值为false。
先外层查询后内层查询将外层的值代入内层进行查询根据内层查询是否存在结果判断外层的元组是否保留在结果集中。
【例】查询借阅了图书的读者的姓名
SELECT name FROM reader WHERE
EXISTS ( SELECT * FROM borrow WHERE
borrow.reader_idreader.reader_id)【例】查询被借出的图书的信息
SELECT * FROM Book WHERE EXISTS
(SELECT * FROM Borrow WHERE Borrow.book_IDBook.book_ID)三、多表连接查询
JOIN 表示连接inner表示内连接outer表示外连接缺省情况就是内连接。 ON后面接连接条件
内连接
语句格式FROM 表1 [inner] JOIN 表2 ON 连接条件 满足连接条件的元组保留到连接的结果中其中存在不满足连接条件的元组会被舍弃。 表X和Y内连接结果
SELECT a, X.b, Y.b, c
FROM X
JOIN Y
ON X.bY.b一张表和自身连接。必须给表其别名
• 【例】查询与C语言在同一出版社的图书信息 分析C语言一书在Book表中与它在同一出版社的图书也在Book表中。二者通过publish相等进行连接
SELECT BookW.* FROM Book BookC JOIN
Book BookW on
BookC.publishBookW.publish WHERE
BookC.name‘C语言’ AND BookW.name C语言• 内连接的特点满足连接条件的元组保留到连接的结果中其中存在不满足连接条件的元组会被舍弃。
外连接
和内连接相对的就是外连接。 外连接的特点满足连接条件的元组保留到连接的结果中其中不满足连接条件的元组也会显示到连接结果中。 外连接分类
左外连接
语法格式FROM 表1 LEFT [OUTER] JOIN 表2 ON 连接条件
LEFT OUTER JOIN表示左外连接 OUTER 可省略
【例】表 X 和 Y 进行左外连接语句及连接结果
SELECT a,X.b,Y.b,c from x left outer join y ON x.bY.b在连接过程中左侧表一的元组r 如果不能满足连接条件r 保留在连接的结果中 在连接结果中和 r 对应的表二的例值用NULL来代替
右外连接
语法格式FROM 表1 RIGHT [OUTER] JOIN 表2 ON 连接条件
【例】表 X 和 Y 进行右外连接语句及连接结果
SELECT a, X.b, Y.b, c FROM X RIGHT JOIN Y ON X.bY.b右侧表Y的元组3Q虽未能在表X中找到相匹配的元组单保留在结果当中 同时 aX.b 的值用NULL填充
全外连接
语法格式FROM 表1 FULL [OUTER] JOIN 表2 ON 连接条件
【例】表 X 和 Y 进行右外连接语句
SELECT a, X.b, Y.b, c FROM X FULL OUTER JOIN Y ON X.bY.b【例】查询所有读者借阅情况包括没借阅图书的读者显示读者编号读者姓名图书编号和书名。
SELECT R.reader_ID, R.name, B.book_ID, B.name
FROM Reader AS R LEFT JOIN Borrow
AS BW
ON R.reader_IDBW.reader_ID LEFT
JOIN Book AS B ON B.book_IDBW.book_ID四、复杂查询
复杂查询UNION [ALL]
使用UNION语句可以合并两个或多个查询的结果。
UNION语句用第二个查询结果合并第一个查询结果。
它不显示两个查询中的重复的行。
如果想显示所有行包括重复行则可以在UNION后面添加ALL谓词
union的语法格式
SELECT 目标列名序列 FROM 数据源 [WHERE 检索条件表达式] [GROUP BY 分组依据列] [HAVING 组提取条件]
UNION [ALL]SELECT 目标列名序列 FROM 数据源 [WHERE 检索条件表达式] [GROUP BY 分组依据列] [HAVING 组提取条件][ORDER BY 排序依据列 [ASC|DESC]]注意如果使用使用ORDER BY 字句进行排序则该子句只出现最后一个查询的后面如果不希望去除重复的元组可以使用关键字ALL。
• 【例】
SELECT * FROM Book
WHERE publish清华大学出版社
UNION ALL
SELECT * FROM Book
WHERE price25
ORDER BY name因为使用了关键字ALL结果中的重复元组被保留 在这个查询中的第二个查询后使用了ORDER BY 子句使并集结果按书名name排序
