MySQL中的DQL
DQL语言
DQL(Data Query Language),数据查询语句
查询数据库数据,如SELECT语句
简单的单表查询或多表的复杂查询和嵌套查询
数据库语言中最核心,最重要的语句
使用频率最高的语句
SELECT语句
语法:
SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1] [,table.field[as alias2]][,...]]}
FROM table_name [as table_alias]
[left | right | inner join table_name2] #联合查询
[WHERE ...] #指定结果需满足的条件
[GROUP BY ...] #指定结果按照哪几个字段来分组
[HAVNG ...] #过滤分组的记录必须满足的次要条件
[ORDER BY ...] #指定查询记录按一个或多个条件排序
[LIMIT { [offset,]row_count | row_count OFFSET offset}]; #指定查询的记录从哪条至哪条
[]括号代表可选的;
{}括号代表必须的;
#为MySQL语句中的注释符,也可以用 /**/
指定查询字段
(1)查询表中所有的数据列结果,采用"*"符号
SELECT * FROM 表名;
(2)可指定查询的结果数据列
SELECT 字段1,字段2,.... FROM 表名
如需要区分连接查询时两个表有同名的字段
SELECT 表1.字段1,表2.字段2,字段3,.... FROM 表1,表2;
共有的字段需要在前面加上指定表名点出该字段来区分.
AS子句
作用:
1.可给数据列取一个新别名
2.可给表取一个新别名
3.可把经计算或总结的结果用另外一个新名称来代替
用法:
SELECT StudentNo AS "学号" FROM student;
SELECT a.StudentNo FROM student AS a;
SELECT Phone+1 AS Tel FROM student;
AS也可以省略不写
DISTINCT
去掉SELECT查询返回的记录结果中重复的记录(所有返回列的值都相同),只返回一条
语法:
SELECT DISTINCT 字段名1,字段名2... FROM 表名
ALL关键字是默认的(不加DISTINCT默认ALL),返回所有的记录,与DISTINCT相反
使用表达式的列
表达式一般由文本值、列值、NULL、函数和操作符等组成
应用场景:
1.SELECT语句返回结果列中使用
2.SELECT语句的ORDER BY 、HAVING等子句中使用
3.DML语句中的WHERE条件语句中使用表达式
需要避免SQL返回结果中包含".","*"和括号等干扰开发语言程序
WHERE条件语句
用于检索数据表中符合条件的记录
搜索条件可由一个或多个逻辑表达式组成,结果一般为真或假
搜索条件的组成
1.逻辑操作符
2.比较操作符
1.逻辑操作符
操作符名称 | 语法 | 描述 |
---|---|---|
AND或&& | a AND b 或 a && b | 逻辑与,同时为真,结果才为真 |
OR 或 || | a OR b 或 a ||b | 逻辑或,只要一个为真,则结果为真 |
NOT或 ! | NOT a 或 !a | 逻辑非,若操作数为假,结果则为真 |
2.比较操作符
操作符名称 | 语法 | 描述 |
---|---|---|
IS NULL | a IS NULL | 若操作数为NULL,则结果为真 |
IS NOT NULL | a IS NOT NULL | 若操作数不为NULL,则结果为真 |
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,a3,…中的某一个,则结果为真 |
注意:
1.数值数据类型的记录之间才能进行算数运算
2.相同数据类型的数据之间才能进行比较
BETWEEN AND范围查询
根据一个范围值来检索
SELECT 字段1,字段2,.... FROM 表名 WHERE 字段X BETWEEN 值1 AND 值2
等同于>=和<=联合使用
LIKE模糊查询
在WHERE子句中,使用LIKE关键字进行模糊查询
与"%“一起使用,表示匹配0或任意多个字符
与”_"一起使用,表示匹配单个字符
如:
查询包含"李"字,则"%李%"
查询"李"姓,则"李%"
查询"李"后面只有一个字的,则"李_"
如果需要显示%或者_,可以使用转义字符" \ "
也可以用ESCAPE关键字自定义转义符,如:
SELECT studentno,studentname FROM student WHERE studentname LIKE '%:%%' ESCAPE ':';
IN范围查询
在WHERE子句中使用IN进行范围查询
SELECT 字段列1,字段2,...FROM 表名 WHERE 字段X IN (值1,值2,值3...)
查询的字段X的值,至少与括号中的一个值相同
多个值之间用英文逗号隔开
NULL空值条件查询
NULL代表"无值"
区别于零值0和空字符串""
只能出现在定义允许为NULL的字段
须使用IS NULL 或 IS NOT NULL比较操作符去比较
连接查询(多表查询)
如需要多张数据表进行查询,则可通过连接运算符实现多个查询
分类包括内连接和外连接
内连接(inner join)
在表中至少一个匹配时,则返回记录
语法:
SELECT 字段1,字段2,... FROM table_1
INNER JOIN table_2
ON table_1.字段X =table_2.字段Y;
INNER JOIN 和JOIN是相同的,
如果table_1中的行在table_2中没有匹配,则不返回行
等值连接
与单表查询类似,都是SELECT语句
把多个表放在FROM后,并用逗号隔开
可使用AS关键字取别名,便于引用
如无重名查询字段则可省略数据表的指定
语法:
SELECT 字段1,字段2,.... FROM table_1,table_2
WHERE table_1.字段X =table_2.字段Y;
等效于内连接
非等值连接
与单表查询类似,都是SELECT语句
把多个表放在FROM后,并用逗号隔开
可使用AS关键字取别名,便于引用
如无重名查询字段则可省略数据表的指定
语法:
SELECT 字段1,字段2,.... FROM table_1,table_2 ;
返回记录数为两表记录数的乘积
自连接
数据表与自身进行连接
SELECT 字段1,字段2,.... FROM 表1 别名1,表1 别名2;
外连接(out join)
左连接
从左表(table_1)中返回所有的记录,即便在右表(table_2)中没有匹配的行
语法:
SELECT 字段1,字段2,... FROM table_1
LEFT JOIN table_2
ON table_1.字段X =table_2.字段Y;
右连接
从右表(table_2)中返回所有的记录,即便在左表(table_1)中没有匹配的行
语法:
SELECT 字段1,字段2,... FROM table_1
RIGHT JOIN table_2
ON table_1.字段X =table_2.字段Y;
不同的SQL JOIN对比
操作符名称 | 描述 |
---|---|
INNER JOIN (JOIN) | 如果表中至少有一个匹配,则返回行 |
LEFT JOIN | 无论右表是否有匹配,都会返回左表的所有行 |
RIGHT JOIN | 无论左表是否有匹配,都会返回右表的所有行 |
GROUP BY
对所有数据进行分组统计。分组的依据字段可以有多个,并依次分组
与HAVING结合使用,进行分组后的数据筛选
GROUP BY的语句顺序在WHERE后面,ORDER BY 的前面
通常在对数据使用计算统计的时候,会用到GROUP BY分组
MySQL函数
包括1.数学函数
2.字符串函数
3.日期和时间函数
4.系统信息函数
1.数学函数
常用的有:
(1)ABS() 绝对值
(2)CEILING() 大于等于我的最小整数(天花板)
(3)FLOOR() 小于等于我的最大整数(地板)
(4)RAND() 返回0~1之间的随机数
(5)SIGN() 符号函数,正数返回1,负数返回-1,0返回0
2.字符串函数
常用有:
(1)CHAR_LENGTH() 返回字符串中包含的字符数
(2)CONCAT() 合并字符串,参数可有多个,用逗号隔开
(3)INSERT() 替换字符串,从某个位置开始,替换某个长度,如果起始位置超过字符串长度,则返回源字符串
如: INSERT(‘我爱课工场’,1,3,‘很爱’)
(4)LOWER() 变小写
(5)UPPER() 变大写
(5)LEFT() 从左边截取几位,如:LEFT(‘你好’,2)
(6)RIGHT() 从右边截取几位
(7)REPLACE() 替换指定字符,如:REPLACE(‘欢迎你,你好’,‘你’,‘你好’)
(8)SUBSTR() 截取,从哪个位置开始截取,截取多长
如:SUBSTR(‘课工场欢迎你’,1,3)
(9)REVERSE() 反转字符串
3.日期和时间函数
常用的有:
(1)CURRENT_DATE()
等同于 CURDATE()
获取当前日期
(2)NOW()
等同于 LOCALTIME()
等同于 SYSDATE()
获取当前日期和时间
(3)YEAR(NOW())
MONTH(NOW())
DAY(NOW())
HOUR(NOW())
MINUTE(NOW())
SECOND(NOW())
分别获取当前日期中的某个部分
4.系统信息函数
常用的有:
(1)VERSION() 显示版本信息
(2)USER() 菜单用户信息
统计函数(聚合函数)
函数名称 | 描述 |
---|---|
COUNT() | 返回满足SELECT条件的记录总和数,如 SELECT COUNT( * ),不建议使用 *,效率低, ()中也可以使用1,或者列名 COUNT()为非空值计数,不会计算空值 |
SUM() | 返回数字字段或表达式列作统计,返回一列的总和 ()中通常加列名 |
AVG() | 通常为数值字段或表达式列作统计,返回一列的平均值 ()中通常为列名 |
MAX() | 可以为数值字段、字符字段或表达式列作统计,返回最大的值 |
MIN() | 可以为数值字段、字符字段或表达式列作统计,返回最小的值 |
HAVING
过滤分组的记录必须满足的次要条件。SELECT语句中,在GROUP BY分组之后再进行条件筛选,就不能使用WHERE,而是在GROUP BY后面通过HAVING进行分组后的条件筛选。HAVING的作用等同于WHERE
ORDER BY
对SELECT语句查询得到的结果,按某些字段进行排序
与DESC或ASC搭配使用,默认为ASC
ASC为升序排列
DESC为降序排列
语法:
ORDER BY 列名1 ASC/DESC[,列名2 ASC/DESC,...]
LIMIT
分页显示,对用户体验、网络传输、查询压力上都有好处
语法:
LIMIT[m,]n
或 LIMIT n OFFSET m
限制SELECT返回结果的行数
m指定第一个返回记录行的偏移量(显示的起始位置)
n指定返回记录行的最大数目(显示行数)
m不指定则偏移量为0,从第一条开始返回前n条记录
在MySQL中,显示每页的行数可以使用 LIMIT (页码-1)*行数,行数
子查询
在查询语句中的WHERE条件子句中,又嵌套了另外一个查询语句
嵌套查询可由多个子查询组成,求解的方式是由里及外
子查询返回的结果一般都是集合,故而建议使用IN关键字
例如:
SELECT studentno "学号",studentname "姓名" FROM student
WHERE studentno IN (SELECT studentno FROM result
WHERE subjectno =(SELECT subjectno FROM `subject`
WHERE subjectname ='高等数学-2')
AND studentresult >=80)