文章目录
- 第三章 使用DQL命令查询数据
- 1. DQL语言
- 1.2 指定查询字段SELECT
- 1.3 AS子句(别名)
- 1.4 DISTINCT关键字
- 1.5 where条件语句
- 1.6 BETWEEN AND范围查询
- 1.7 LIKE模糊查询
- 1.8 使用IN进行范围查询
- 1.9 NULL空值条件查询
- 2. 连接查询(多表查询)
- 2.1 内连接 ( inner join)
- 2.2 外连接 ( out join )
- 3. ORDER BY排序
- 4. LIMIT分页查询
- 5. 子查询
- 6. MySQL函数
- 6.1 统计函数
- 7. GROUP BY分组
- 8. 表合并(UNION与UNION ALL)
第三章 使用DQL命令查询数据
1. DQL语言
DQL(Data Query Language,数据查询语言)
1.2 指定查询字段SELECT
-
查询表中所有的数据列结果,采用“*”符号
select * from student;
-
可指定查询的结果数据列
sql">#select 列名... from 表名 [where 条件]
#查询成绩大于90分的考试信息
select StudentNo,SubjectNo,StudentResult,ExamDate from result where StudentResult>90;
#查询时注意:*代表所有列
1.3 AS子句(别名)
作用:
- 可给数据列取一个新别名
- 可给表取一个新别名
- 可把经计算或总结的结果用另外一个新名称来代替
用法:
sql">#别名 AS(可以省略)
#列别名 列名 as 别名
#表别名 表名 as 别名
SELECT StudentNo AS “学号” FROM student;
SELECT a.StudentNo FROM student AS a;
SELECT Phone+1 AS Tel FROM student;
1.4 DISTINCT关键字
作用: 去掉SELECT查询返回的记录结果中重复的记录(所有返回列的值都相同),只返回一条
SELECT DISTINCT 字段名1, 字段名2... FROM 表名
ALL 关键字是默认的,返回所有的记录,与之相反
sql">#结果去重 distinct
# select distinct 列名.....
SELECT distinct StudentNo from result WHERE StudentNo=1000;
1.5 where条件语句
用于检索数据表中符合条件的记录,搜索条件可由一个或多个逻辑表达式组成,结果一般为真或假
搜索条件的组成:逻辑操作符、比较操作符
逻辑操作符:
操作符名称 | 语法 | 描述 |
---|---|---|
AND或&& | a AND b 或 a && b | 逻辑与,同时为真,结果才为真 |
OR或|| | a OR b 或 a||b | 逻辑或,只要一个为真,则结果为真 |
NOT或! | NOT a 或 !a | 逻辑非,若操作数为假,结果则为真 |
比较操作符:
操作符名称 | 语法 | 描述 |
---|---|---|
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…中的某一个,则结果为真 |
1、数值数据类型的记录之间才能进行算术运算
2、相同数据类型的数据之间才能进行比较
is null 查询是空数据
is not null 查询非空数据
列 != “” 空字符串 非空数据
1.6 BETWEEN AND范围查询
-
根据一个范围值来检索
sql">SELECT 字段列1,字段2 ,…FROM 表名 WHERE 字段x BETWEEN 值1 AND 值2
-
等同于 >= 和 <= 联合使用
sql">#查询课程表中课时在110和120之间的所有记录SELECT * FROM subject WHERE ClassHour BETWEEN 110 AND 120; 等同于:SELECT * FROM subject WHERE ClassHour >= 110 AND ClassHour <=120;
1.7 LIKE模糊查询
在WHERE子句中,使用LIKE关键字进行模糊查询:
- 与
“%”
一起使用,表示匹配0或任意多个字符 - 与
“_”
一起使用,表示匹配单个字符
sql">#查询包含“数学”的所有课程
SELECT * FROM subject WHERE SubjectName LIKE "%数学%";
#查询所有姓名为“李**”三个字的学生信息
SELECT StudentNo,StudentName FROM student WHERE StudentName LIKE "李__";
1.8 使用IN进行范围查询
在WHERE子句中使用IN进行范围查询:
SELECT 字段列1,字段2 ,…FROM 表名 WHERE 字段x IN ( 值1,值2,值3…)
- 查询的字段x的值,至少与括号中的一个值相同
- 多个值之间用英文逗号隔开
sql"> #普通处理方式
SELECT * FROM subject
where ClassHour = 100 OR ClassHour =110 OR ClassHour = 120;
#使用IN进行查询方式,更为简洁,效率更高
# in 类似 OR
SELECT * FROM subject where ClassHour IN ( 100, 110,120 );
1.9 NULL空值条件查询
- NULL代表“无值”
- 区别于零值0和空符串“ ”
- 只能出现在定义允许为NULL的字段
- 须使用 IS NULL 或 IS NOT NULL 比较操作符去比较
2. 连接查询(多表查询)
2.1 内连接 ( inner join)
在表中至少一个匹配时,则返回记录
内连接:返回两张表共有的数据
sql">SELECT 字段1,字段2,… FROM table_1
INNER JOIN table_2 ON table_1.字段x = table_2.字段y;
# INNER JOIN 与 JOIN 是相同的;
# 如table_1中的行在table_2中没有匹配,则不返回;#要求:从subject和grade数据表查询课程名称和所属年级名称
SELECT SubjectName,GradeName FROM subject INNER JOIN grade ON subject.GradeID= grade.GradeID;
-
等值和非等值的连接查询
与单表查询类似,都是SELECT语句
把多个表放到FROM后,并用逗号隔开
可使用AS关键字取别名,便于引用
如无重名查询字段则可省略数据表的指定
sql">#要求:从subject和grade数据表查询课程名称和所属年级名称
#非等值连接查询
SELECT SubjectName, GradeName FROM subject, grade;
#等值查询
SELECT SubjectName, GradeName FROM subject, grade
WHERE subject.GradeID = grade.GradeID;
-
自身连接查询
数据表与自身进行连接
sql">#自连接
#获得河南省下所有的市
select * from city a LEFT JOIN city b on
a.city_pid = b.city_id where b.city_name = "河南省";
2.2 外连接 ( out join )
-
左连接(LEFT JOIN)
从左表(table_1)中返回所有的记录,即便在右(table_2)中没有匹配的行
sql">SELECT 字段1,字段2,… FROM table_1 LEFT [ OUTER ] JOIN table_2 ON table_1.字段x = table_2.字段y;
-
右连接 ( RIGHT JOIN)
从右表(table_2)中返回所有的记录,即便在左(table_1)中没有匹配的行
sql">SELECT 字段1,字段2,… FROM table_1 RIGHT [ OUTER ] JOIN table_2 ON table_1.字段x = table_2.字段y;
JOIN对比:
操作符名称 | 描述 |
---|---|
INNER JOIN ( JOIN ) | 如果表中有至少一个匹配,则返回行 |
LEFT JOIN | 不论右表是否有匹配,都会返回左表的所有行 |
RIGHT JOIN | 不论左表是否有匹配,都会返回右表的所有行 |
sql">#主表 子表
#查询所有学生信息,并显示学生姓名与年级名称
#左连接:以左表为基础,返回左表所有数据
select a.StudentName,b.GradeName from
student a LEFT JOIN grade b on a.GradeId = b.GradeID;#右连接:以右表为基础,返回右表所有数据
select b.StudentName,a.GradeName
from grade a RIGHT JOIN student b on a.GradeID = b.GradeId ;
3. ORDER BY排序
ORDER BY排序查询:
- 对SELECT语句查询得到的结果,按某些字段进行排序
- 与 DESC 或 ASC 搭配使用,默认为ASC
sql">select a.StudentNo,b.SubjectName,a.ExamDate,a.StudentResult
from result a
LEFT JOIN `subject` b on a.SubjectNo = b.SubjectNo
where b.SubjectName="高等数学-1"
ORDER BY StudentResult desc,a.StudentNo desc ;
4. LIMIT分页查询
LIMIT 常用于分页显示
LIMIT [m,]n
或 LIMIT n OFFSET m
- 限制SELECT返回结果的行数
- m 制定第一个返回记录行的偏移量(m不指定则偏移量为0,从第一条开始返回前n条记录)
- n 制定返回记录行的最大数目
sql">SELECT * FROM `result` LIMIT 5 #返回前5条记录
SELECT * FROM `result` LIMIT 5,10 #返回6-15条记录
sql">#查询所有《数据库结构-2》的考试成绩,并按照由高到低显示,同时把该成绩对应的学生的学号、姓名打印出来
select a.StudentNo,c.StudentName,b.SubjectName,a.ExamDate,a.StudentResult
from result a
LEFT JOIN `subject` b on a.SubjectNo = b.SubjectNo
LEFT JOIN student c on c.StudentNo = a.StudentNo
where b.SubjectName="数据库结构-2"
order by StudentResult desc LIMIT 15,5;
select语句全语法:
sql">SELECT [ALL | DISTINCT]
{ * | table.* | [ table.field1 [ as alias1] [,table.field2 [as alias2]][,…]] }
FROM table_name [ as table_ alias ][left|out|inner join table_name2] #联合查询[ WHERE … ] #指定结果需满足的条件[ GROUP BY …] #指定结果按照哪几个字段来分组[ HAVING …] #过滤分组的记录必须满足的次要条件[ ORDER BY… ] #指定查询记录按一个或者多个条件排序[ LIMIT {[ offset,] row_count|row_count OFFSET offset}]; #指定查询的记录从哪条至哪条
5. 子查询
在查询语句中的WHERE条件子句中,又嵌套了另外一个查询语句
子查询返回的结果一般都是集合,故而建议使用 IN 关键字
列子查询 : 单列单行
表子查询
条件子查询 : = (单列单行) in(单列多行)
sql">#列子查询:
#查询考试成绩,并返回学生姓名,考试成绩
select a.StudentNo,a.StudentResult,(
select b.StudentName from student b where b.StudentNo = a.StudentNo
) as "姓名"
from result a ;#表子查询
select t.*
from (select a.StudentNo,b.StudentName,a.StudentResult from result a LEFT JOIN student b on a.StudentNo=b.StudentNo where b.StudentName = "郭靖"
) as t
ORDER BY t.StudentResult desc;#条件子查询
#要求:查询课程为《高等数学-2》且分数不小于80分的学生的学号和姓名
-- 分析:条件课程名与分数均不在student表中,也没有外键,表连接无法使用。
-- 1.以学号为条件,使用in ,因为可能存在多名学生
-- 2.课程表与成绩表存在外键,所以子查询使用表连接
-- 3.写出课程表语成绩表中两个条件
-- 4.返回满足的学生学号
select a.StudentNo,a.StudentName
from student a
where a.StudentNo in
(
select b.SubjectNo
from `subject` b LEFT JOIN result c on
b.SubjectNo=c.StudentNo
where b.SubjectName = '高等数学-2' and c.StudentResult>80
);
#先运行子查询,在运行外面的查询
6. MySQL函数
- 数学函数
- 字符串函数 https://www.cnblogs.com/geaozhang/p/6739303.html
- 日期和时间函数 https://www.cnblogs.com/ggjucheng/p/3352280.html
- 系统信息函数
6.1 统计函数
函数名称 | 描述 |
---|---|
COUNT( ) | 返回满足SELECT条件的记录总和数,如 SELECT COUNT(*)… |
SUM( ) | 返回数字字段或表达式列作统计,返回一列的总和 |
AVG( ) | 通常为数值字段或表达列作统计,返回一列的平均值 |
MAX( ) | 可以为数值字段、字符字段或表达式列作统计,返回最大的值 |
MIN( ) | 可以为数值字段、字符字段或表达式列作统计,返回最小的值 |
7. GROUP BY分组
使用GROUP BY关键字对查询结果分组
语法:
SELECT 列名1,列名2 FROM 表名 [ WHERE 条件 ] [ GROUP BY …]
;
- 对所有的数据进行分组统计
- 分组的依据字段可以有多个,并依次分组
- 与HAVING结合使用,进行分组后的数据筛选
8. 表合并(UNION与UNION ALL)
UNION与UNION ALL的区别:
1、对重复结果的处理:UNION在进行表链接后会去重,UNION All不会。
2、对排序的处理:Union将会按照字段的顺序进行排序;UNION ALL只是简单的将两个结果合并后就返回。
UNION ALL 要比UNION效率高,所以,如果可以确认合并的两个结果集中不包含重复数据且不需要排序时的话,那么就使用UNION ALL。