DQL查询数据(重点)
DQL(Data Query Language:数据查询语言)
- 所有的查询操作都用
- 简单的查询,复杂的查询
- 数据库最核心的语言
- 使用频率最高的语句
select
from table_name [ as table_alias]
[left | right | inner join table_name2] 联合查询
[where...] 指定结果需满足的条件
[group by...] 指定结果按照哪几个字段来分组
[having] 过滤分组的记录必须满足的次要条件
[order by...] 指定查询记录按一个或多个条件排序
[limit { [ offset, ] row_count | row_countOFFSE ofset } ] 指定查询的记录从哪条至哪条
select指定查询的字段
#查询学生所有信息
select * from student
select 字段1,字段2... from 表名
#查询学生表中学号和学生名字
select `StudentNo`,`StudentName`
from student
增加别名:在字段后面加as,也可以给表后面加as
#查询学生表中学号和学生名字
select `StudentNo` as '学号',`StudentName` as '学生姓名'
from student as s
函数 Concat(a,b)
select concat('姓名:',StudentName) as 新名字 from student
去重distinct
作用:去除select查询出来的结果中重复的数据,重复的数据只显示一条
#查询全部成绩
select * from result
#查询哪些同学参加了考试
#去掉重复数据distinct
select distinct `StudentNo` from result
数据库的列(表达式)
#查看系统的版本(函数)
select version()
#用作计算(计算表达式)
select 100*3-5 as '计算结果'
#查询自增的步长(变量)
select @@auto_increment_increment
数据库中的表达式:文本值、列、Null、函数、计算表达式、系统变量
where条件子句
作用:检索数据中符合条件的值。
搜索的条件由一个或者多个表达式组成,结果为布尔值
逻辑运算符
运算符 | 语法 | 描述 |
and && | a and b a && b | 逻辑与,两个都真,结果为真 |
or || | a or b a||b | 逻辑或,一个为真,结果为真 |
Not ! | not a ! a | 逻辑非,真为假,假为真 |
#查询95——100的学生成绩和学号
select studentNo,`StudentResult` from result
where `StudentResult`>=95 and `StudentResult`<=100
#查询除了1000号学生的成绩
select studentNo,`StudentResult` from result
where `StudenNo`!=1000#法二
select studentNo,`StudentResult` from result
where Not `StudenNo`=1000
模糊查询
比较运算符
运算符 | 语法 | 描述 |
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..其中的某一个值中,结果为真 |
like结合%(代表0到任意个字符) _(名字一个字符)
#查询姓刘的同学 like结合%(代表0到任意个字符) _(名字一个字符)
select studentNo,StudentName from student
where StudentName LIKE '刘%'#查询姓刘的同学,名字后面只有一个字符
select studentNo,StudentName from student
where StudentName like '刘_'#查询姓刘的同学,名字后面只有两个字符
select studentNo,StudentName from student
where StudentName like '刘__'#查询名字中间有佳字的同学 %佳%
select studentNo,StudentName from student
where StudentName like '%佳%'
in(具体的一个或多个值)
#查询1001,1002,1003号学生
select `StudentNo`,`StudentName` from `student`
where StudentNo in(1001,1002,1003)#查询在北京、上海学生
select `StudentNo`,`StudentName` from `student`
where StudentNo in('北京','上海')
null、not null
#查询地址为空的学生
select `studentNo`,`StudentName` from `student`
where address='' or address is null#查询有出生日期的学生
select `StudentNo`,`StudentName` from `student`
where `bornDate` is not null
join联表查询
join对比
join(连接的表) on(判断的条件):连接查询
where:等值查询
7种join理论
查询参加了考试的同学(学号,姓名,科目编号,分数)
#思路:
- 分析需求,分析查询的字段来自哪些表(连接查询)
- 确定使用哪种连接查询?(7种)
- 确定交叉点(这两个表中哪个数据是相同的)
- 判断条件:学生表中的studentNo=成绩表中的studentNo
#法一:inner join
select s1.Studentno,StudentName,SubjectNo,StudenResult
from student as s1
inner join `result` as s2
where s1.StudentNo=s2.StudentNo#法二:right join左表为student,里面存在一个学生成绩为空的值,返回结果中该空值不会返回
select s1.Studentno,StudentName,SubjectNo,StudenResult
from student as s1
right join `result` as s2
on s1.StudentNo=s2.StudentNo#法三:left join,左表为student,里面存在一个学生成绩为空的值,返回结果中该空值也会返回
select s1.Studentno,StudentName,SubjectNo,StudenResult
from student as s1
left join `result` as s2
on s1.StudentNo=s2.StudentNo
操作 | 描述 |
inner join | 如果表中至少有一个匹配,就返回行 |
left join | 即使右表中没有匹配,也会从左表中返回所有的值 |
right join | 即使左表中没有匹配,也会从右表中返回所有的值 |
--利用法三:left join,左表为student,里面存在一个学生成绩为空的值,返回结果中该空值
--也会返回,查询缺考的学生
select s1.Studentno,StudentName,SubjectNo,StudenResult
from student as s1
left join `result` as s2
on s1.StudentNo=s2.StudentNo
where StudenResult is null
查询了参加考试的同学信息:学号、学生姓名、科目名、分数
- 需要查询的信息分别来自三个表:student、result、subject
- 又由于只要参加了考试的同学信息,所以利用右查询
select s.studentNo,studentName,SubjectName,StudentRrsult
from student s
right join result r
on r.studentNo=s.studentNo
inner join subject sub
on r.SubjectNo=sub.SubjectNo
自连接
自己的表和自己的表连接,核心:一张表拆为两张一样的表
表一,如下
categoryid | pid | categoryName |
3 | 1 | 软件开发 |
5 | 1 | 美术设计 |
4 | 3 | 数据库 |
8 | 2 | 办公信息 |
2 | 1 | 信息技术 |
6 | 3 | web开发 |
7 | 5 | ps技术 |
拆分为两张表:
父类:a表
categoryid | categoryName |
2 | 信息技术 |
3 | 软件开发 |
5 | 美术设计 |
子类:b表
pid | categoryid | categoryName |
3 | 4 | 数据库 |
2 | 8 | 办公信息 |
3 | 6 | web开发 |
5 | 7 | ps技术 |
操作:查询父类对应的子类关系,表四如下:
父类 | 子类 |
信息技术 | 办公信息 |
软件开发 | 数据库 |
软件开发 | web开发 |
美术设计 | ps技术 |
根据表一查询到表四的结果:
select a.`categoryName` as '父栏目',b.`categoryName` as '子栏目'
from `category` as a,`category` as b
where a.`categoryid`=b.`pid`
limit分页和order by排序
order by排序:
- 升序 asc
- 降序 desc
order by 通过哪个字段排序 怎么排
select s.`studentNo`,`StudentName`,`SubjectName`,`StudentResult`
from student s
inner join `result` r
on s.`StudentNo`=r.`StudentNo`
inner join `subject` sub
on r.`SubjectNo`=sub.`SubjectNo`
where subjectName='数据结构'
order by StudentResult desc #根据学生成绩排序降序
limit分页:缓解数据库压力
limit 起始页,页面的大小(pageSize)
第一页 limit 0,5
第二页 limit 5,5
第n页:(n-1)*pageSize
数据总数/页面大小=总页数
子查询
where(这个值是计算出来的)
本质:在where语句中嵌套一个子查询的语句
where(select * from 表名)
eg:查询数据结构的所有考试结果(学号、科目编号、成绩),降序排列
#法一:使用连接查询
select `StudentNo`,r.`SubjectNo`,`StudentResult`
from `result` r
inner join `subject` sub
on r.`SubjectNo`=sub.`SubjectNo`
where `SubjectName`='数据结构'
order by `StudentResult` desc#法二:使用子查询(由里即外)
select `StudentNo`,`SubjectNo`,`StudentResult`
from `result`
where StudentNo=(#查询所有数据结构的学生学号select SubjectNo from `subject`where SubjectName='数据结构'
)
order by `StudentResult` desc
MySQL常用函数
常用函数
数学运算
- abs(-8) //8 绝对值
- ceiling(9.4) //10 向上取整
- floor(9.4) //9 向下取整
- rand() 随机返回0-1之间的随机数
- sing(-10) //-1 判断一个数的符号,0是0,负数返回-1,正数返回1
字符串函数
- char_length("bady don't cry") //14,空格也算一个字符 计算字符串长度
- concat('hello','数','据','库') //hello数据库 拼接字符串
- insert('hello,java',7,4,'数据库') //hello,数据库 从某个位置开始替换某个长度,该处是从第七个字符,替换4个字符的长度为数据库
- lower('SERENITY') //serenity 转为小写
- upper('serenity') // SERENITY 转为大写
- instr('Serenity','r') // 3 返回第一次出现的子串的索引
- replace('serenity检测数据',‘serenity’,'我们') //我们检测数据 替换出现的指定字符串
- substr('serenity检测数据',9,4) //j检测数据 返回指定的子字符串,截取第9个字符串,长度为4
- reverse('serenity检测数据') //据数测检ytineres 反转
时间和日期函数(记住)
- current_date() //2025-03-04 获取当前日期
- curdate() //2025-03-04 获取当前日期
- now() //2025-03-04 12:16:00 获取当前时间
- localtime() //2025-03-04 12:16:57 获取本地时间
- sysdate() //2025-03-04 12:18:10 系统时间
系统
- system_user() //root@localhost 系统当前用户
- 可简写为user()
- version() //8.0.19 系统版本
聚合函数(常用)
函数名称 | 描述 |
count() | 计数,不会返回null |
sum() | 求和 |
avg() | 平均值 |
max() | 最大值 |
min() | 最小值 |
... |
count()统计数据
select count(studentid) from student; #指定字段,会忽略null
select count(*) from student; #count(*),不会忽略null
select count(1) from student; #count(1),不会忽略null
sum()求和、avg()、max()、min()
select sum(studentresult) as 总和 from result;
select avg(studentresult) as 平均分 from result;
select max(studentresult) as 最高分 from result;
select min(studentresult) as 最高分 from result;
eg:查询不同课程的平均分、最高分、最低分,平均分大于80的学生
group by 指定结果按照哪几个字段来分组
having 过滤分组的记录必须满足的次要条件
#根据不同的课程,利用分组
select SubjectName,avg(StudentResult) as 平均分,max(StudentResult) as 最高分,min(StudentResult) as 最低分
from result r
inner join subject sub
on r.SubjectNo=sub.SubjectNo
group by r.SubjectNo #通过什么字段来分组,否则只能查到第一个
having avg(StudentResult)>80 #根据分组的记录需要满足的条件
数据库级别的md5加密(扩展)
MD5:消息摘要算法,一种被广泛使用的密码散列函数,由MD4、MD3、MD2改进而来,主要增强算法复杂度和不可逆性。具体的md5值是一样的。md5破解网站的原理,背后有一个字段。具备md5加密前的值与md5加密后的值。
#创建表
CREATE TABLE `testmd5`(
`id` int(4) not null,
`name` varchar(20) not null,
`pwd` varchar(50) not null,
PRIMARY KEY(`id`)
)ENGINE=innodb DEFAULT charset=utf8#插入数据,明文密码
insert into testmd5 values(1,'张三','123456'),(2,'李四','123123'),(3,'王五','111111')
使用md5加密,如下:
#给id为1的用户加密
update testmd5 set pwd=MD5(pwd) where id=1
#给所有用户加密
update testmd5 set pwd=MD5(pwd)
插入时加密,如下:
#插入时加密
insert into testmd5 values(4,'admin',MD5('123789'))
检验:前端传递数据时,也需要先加密,然后对比加密后的值
#校验,蒋用户传递进来的密码,进行md5加密,然后对比加密后的值
select * from testmd5 where `name`='admin' and `pwd`=MD5('123789')