一、DQL
Data Query Language:数据查询语言
- 所有的查询操作都使用 Select
- 简单的查询,复杂的查询都能做
- 数据库中最核心的语言,最重要的语句
- 使用频率最高的语句
Select 完整的语法:
SELECT
[ALL | DISTINCT | DISTINCTROW]
[HIGH_PRIORITY]
[STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
select_expr [, select_expr ...]
[FROM table_references
[PARTITION partition_list]
[WHERE where_condition}
[GROUP BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_condition]
[ORDER BY {col_name | expr | positon}
[ASC | DESC], ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[PROCEDURE procedure_name(argument_list)]
[INTO OUTFILE 'file_name'
[CHARACTER SET charset_name]
export_options
| INTO DUMPFILE 'file_name'
| INTO var_name [,var_name]]
[FOR UPDATE | LOCK IN SHARRE MODE]]
二、 指定查询字段
--查询全部的学生 select 字段 from 表
SELECT * FROM student
--查询指定的字段
SELECT `studentNo` ,`studentName` FROM stundet
--别名,给结果起一个名 AS 可以给字段起别名,也可以给表起别名
SELECT `student` AS '学号', 'studentName' AS '学生名字' FROM student AS s
--函数 Concat(a,b),将a和b拼接为一个结果
SELECT CONCAT('姓名:',studentName) AS 新名字 FROM student
语法:SELECT 字段 .... FROM 表
去重 distinct
作用:去除 SELECT 查询出来的结果中重复的数据,重复的数据只显示一条
--查询一下有哪些同学参加了考试
SELECT DISTINCT student from result
数据库的列(表达式)
SELECT VERSION() --查询系统版本
SELECT 100*3-1 AS '计算结果' --计算表达式
SELECT @@auto_increment_increment -- 查询自增步长(变量)
--学员成绩 +1 查看
SELECT `studentNO`,`studentResult` + 1 AS '提分后' FROM result
数据库中的表达式:文本值,列,null,函数,计算表达式,系统变量
三、where条件子句
作用:检索数据中符合条件的值
搜索的条件由一个或多个表达式组成,结果为布尔值
运算符 | 语法 | 描述 |
---|---|---|
and | a and b | 逻辑与,一假则假 |
or | a or b | 逻辑或,一真则真 |
not | not a | 逻辑非,真为假,假为真 |
模糊查询:比较运算符
运算符 | 语法 | 描述 |
---|---|---|
IS NULL | a is null | 如果字段 a 为空,则结果为真 |
IS NOT NULL | a is not null | 如果字段 a 不为空,则结果为真 |
BETWEEN | a between b and c | 若 a 在 b 和 c 之间,则结果为真 |
LIKE | a like b | SQL匹配,如果a 匹配 b,则结果为真 |
IN | a in (a1,a2,a3.....) | 假设a在a1,或者a2,其中某一个值中,则结果为真 |
注意:
- like结合 %
%代表包含,有开头、结尾的位置。%此%意思为包含 此 的字符。
而 _ 代表一个字符。例如,声_代表字符以声开头并且后面只跟一字 - in (具体的一个或者多个值,是完全匹配其中一个才为真)
四、联表查询
SELECT语法
- 注意:[]括号代表可选的,{}括号代表必选的
SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,.....]]}
FROM table_name [as table_alias]
[left | right | inner join table_name2] -- 联合查询
[WHERE ...] -- 指定结果需满足的条件
[GROUP BY ...] -- 指定结果按照那几个字段分组
[HAVING] -- 过滤分组的记录必须满足的次要条件
[ORDER BY ...] -- 指定查询记录按一个或多个条件排序
[LIMIT {[offset,]row_count | row_countOFFSET offset}];
-- 指定查询的记录从哪天到哪条
DQL七种联表
三个基本连接
操作 | 描述 |
---|---|
inner join | 如果表中至少有一个匹配,就返回行 |
left join | 会从左表中返回所有的值,即使右表中没有匹配 |
right join | 会从右表中返回所有的值,即使左表中没有匹配 |
连表查询的思路
- 我要查询哪些数据 select....
- 从哪几个表中查 from 表 xxx join 连接的表 on 交叉条件
- 确定交叉点(表之间有哪些数据是相同的,可以作为连接条件的)
- 假设存在多张表连接,就一个一个的连接,直到所有的表连接起来
from a left join b on xxx
from a right join b on xxx
- join (连接的表) on (判断条件) --连接查询
- where 等值查询
SELECT s.studentNO,studentName,subjectNo,studentResult
FROM student AS s
INNER JOIN result AS r
ON s.studentNO = r.studentNO
-- Right Join
SELECT s.studentNO,studentName,subjectNo,studentResult
FROM student AS s
RIGHT JOIN result AS r
ON s.studentNO = r.studentNO
--left join
SELECT s.studentNO,studentName,subjectNo,studentResult
FROM student AS s
LEFT JOIN result AS r
ON s.studentNO = r.studentNO
自连接
自己的表和自己的表连接,核心:一张表拆开两(多)张表看
- 自连接练习的数据库
CREATE TABLE `school`.`category`(
`categoryid` INT(3) NOT NULL COMMENT 'id',
`pid` INT(3) NOT NULL COMMENT '父id 没有父则为1',
`categoryname` VARCHAR(10) NOT NULL COMMENT '种类名字',
PRIMARY KEY (`categoryid`)
) ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci;
INSERT INTO `school`.`category` (`categoryid`, `pid`, `categoryname`) VALUES ('2', '1', '信息技术');
insert into `school`.`CATEGOrY` (`categoryid`, `pid`, `categoryname`) values ('3', '1', '软件开发');
insert into `school`.`category` (`categoryid`, `PId`, `categoryname`) values ('5', '1', '美术设计');
insert iNTO `School`.`category` (`categoryid`, `pid`, `categorynamE`) VAlUES ('4', '3', '数据库');
insert into `school`.`category` (`CATEgoryid`, `pid`, `categoryname`) values ('8', '2', '办公信息');
insert into `school`.`category` (`categoryid`, `pid`, `CAtegoryname`) values ('6', '3', 'web开发');
inserT INTO `SCHool`.`category` (`categoryid`, `pid`, `categoryname`) valueS ('7', '5', 'ps技术');
- 分开为两个表来看:
(一)学科分类表:
学科id(categoryid) | 学科名(categoryname) |
---|---|
2 | 信息技术 |
3 | 软件开发 |
5 | 美术设计 |
(二)课程隶属表
课程id(categoryid) | 课程名(categoryname) | 学科id(pid) |
---|---|---|
4 | 数据库 | 3 |
6 | web开发 | 3 |
7 | ps技术 | 5 |
8 | 办公信息 | 2 |
- 可以看出上面两个表的交叉点是学科id
-
表结构
--查询课程名和课程所属学科名
--=====使用inner连接=====那么结果只有课程表里面的课程
SELECT c1.categoryname AS '课程名',c2.categoryname AS '学科名'
FROM category AS c1 --当作 课程表
INNER JOIN category AS c2 --当作 学科表
ON c1.pid = c2.categoryid --课程的隶属学科id等于学科的id
-=====使用left连接=====结果以左表为基础,左表数据全保留,然后去匹配右表,(right join右连接则反过来)
-=====但是在自连接中使用左连接或右连接要注意交叉条件的改变,因为查询逻辑会随着表连接方式不同而改变,不能单纯的改变连接关键字就想得到一样的逻辑查询结果
自然连接的结果 | |
---|---|
左连接的结果 | |
右连接的结果 | |
\ |
五、分页和排序
排序
关键字: order by [ASC | DESC]
不写,默认是升序。
ASC 表示升序,DESC 表示降序。
分页
关键字: limit
limit (n - 1) * pageSize , pageSize
pagaSize:页面大小
n:当前页
(n - 1)* pageSize:起始值
总页数:(向上取整)数据总数/页面大小
语法:
limit(查询起始的下标,总共查询的总数)
从第(n - 1) * pageSize数据开始取,直到取够了pageSize这么多的数量
六、子查询和嵌套查询
嵌套查询指的是一个查询语块可以嵌套在另外一个查询语句块的where子句或者having子句中,前者为子查询或内查询,后者为父查询或外查询。
- 不相关子查询:子查询里面没有依赖父查询。
- 相关子查询:子查询条件依赖于父查询。
in,子查询
- in 关键字用于判断表达式是否存在多值列表中,有则true,无则false。返回在多值列表中的记录。
带比较运算符的子查询
- 指父查询与子查询之间通过比较运算符连接,并且子查询返回的是单值,才可以用 = 、<、 >、 != 、>=、 <= 等比较运算符连接。
any、all,子查询
使用条件:子查询返回多值序列
any:只需要满足与多值序列中的一个值,满足比较关系就返回true。
all:满足与多值序列中的全部值,满足比较关系才返回true。
exists,子查询
exists代表存在量词,带有EXISTS的子查询不返回任何数据,只产生逻辑真值“true”或者逻辑假值“false”。
使用exists的嵌套语句,若子查询结果不为空,则exists返回true,否则返回false。
使用exists引出的子查询,其目标表达式列可以使用*,因为带exists的子查询只返回真值或假值,给出列名无实际含义。
派生表查询
- select 查询的结果也是一张表,可以作为出现在from子句后面作为派生表进行查询。
七、分组和过滤
聚合函数 [aggregate_function (expression) ]
因为聚合函数对一组值进行操作,所以它通常与SELECT
语句的GROUP子句一起使用。 GROUP BY
子句将结果集划分为值分组,聚合函数为每个分组返回单个值。
COUNT() | 计数 |
---|---|
SUN() | 求和 |
AVG() | 平均值 |
MAX() | 最大值 |
MIN() | 最小值 |
- 除COUNT(*)函数外,SQL聚合函数忽略null值。只能将聚合函数用作表达式。而COUNT(确定的字段)这个是会忽略null值。
GROUP BY
- GROUP BY语句用来与聚合函数(aggregate functions such as COUNT, SUM, AVG, MIN, or MAX.)联合使用来得到一个或多个列的结果集。
--语法:
SELECT column1, column2, ... column_n, aggregate_function (expression)
FROM tables
WHERE predicates
GROUP BY column1, column2, ... column_n;
注意
因为聚合函数通过作用于一组数据而只返回一个单个值,因此,在SELECT语句中出现的元素,要么为一个聚合函数的输入值,要么为GROUP BY语句的参数,否则会出错。
HVAING
having是分组(group by)后的筛选条件,分组后的数据组内再筛选。
而where则是在分组前筛选HAVING语句通常与GROUP BY语句联合使用,用来过滤由GROUP BY语句返回的记录集。
HAVING语句的存在弥补了WHERE关键字不能与聚合函数联合使用的不足。
--语法:
SELECT column1, column2, ... column_n, aggregate_function (expression)
FROM tables
WHERE predicates
GROUP BY column1, column2, ... column_n
HAVING condition1 ... condition_n;