DQL查询数据
1 DQL
Data Query Language:数据查询语言
- 所有的查询操作都用它
- 数据库最核心的语言
先向创建数据库并插入一些数据:
CREATE DATABASE IF NOT EXISTS `school`; -- 创建一个school数据库 USE `school`;-- 创建学生表 DROP TABLE IF EXISTS `student`; CREATE TABLE `student`( `studentno` INT(4) NOT NULL COMMENT '学号', `loginpwd` VARCHAR(20) DEFAULT NULL, `studentname` VARCHAR(20) DEFAULT NULL COMMENT '学生姓名', `sex` TINYINT(1) DEFAULT NULL COMMENT '性别,0或1', `gradeid` INT(11) DEFAULT NULL COMMENT '年级编号', `phone` VARCHAR(50) NOT NULL COMMENT '联系电话,允许为空', `address` VARCHAR(255) NOT NULL COMMENT '地址,允许为空', `borndate` DATETIME DEFAULT NULL COMMENT '出生时间', `email` VARCHAR (50) NOT NULL COMMENT '邮箱账号允许为空', `identitycard` VARCHAR(18) DEFAULT NULL COMMENT '身份证号', PRIMARY KEY (`studentno`), UNIQUE KEY `identitycard`(`identitycard`), KEY `email` (`email`) )ENGINE=MYISAM DEFAULT CHARSET=utf8; -- 创建年级表 DROP TABLE IF EXISTS `grade`; CREATE TABLE `grade`( `gradeid` INT(11) NOT NULL AUTO_INCREMENT COMMENT '年级编号', `gradename` VARCHAR(50) NOT NULL COMMENT '年级名称', PRIMARY KEY (`gradeid`) ) ENGINE=INNODB AUTO_INCREMENT = 6 DEFAULT CHARSET = utf8; -- 创建科目表 DROP TABLE IF EXISTS `subject`; CREATE TABLE `subject`( `subjectno`INT(11) NOT NULL AUTO_INCREMENT COMMENT '课程编号', `subjectname` VARCHAR(50) DEFAULT NULL COMMENT '课程名称', `classhour` INT(4) DEFAULT NULL COMMENT '学时', `gradeid` INT(4) DEFAULT NULL COMMENT '年级编号', PRIMARY KEY (`subjectno`) )ENGINE = INNODB AUTO_INCREMENT = 19 DEFAULT CHARSET = utf8; -- 创建成绩表 DROP TABLE IF EXISTS `result`; CREATE TABLE `result`( `studentno` INT(4) NOT NULL COMMENT '学号', `subjectno` INT(4) NOT NULL COMMENT '课程编号', `examdate` DATETIME NOT NULL COMMENT '考试日期', `studentresult` INT (4) NOT NULL COMMENT '考试成绩', KEY `subjectno` (`subjectno`) )ENGINE = INNODB DEFAULT CHARSET = utf8;
2 查询指定字段
查询全部学生: select 字段 from 表
select * from student
查询指定字段
select `StudentNo`,`StudentName` from student
别名,给结果起一个名字 as可以给字段起别名,也可以给表起别名
select `studentNo` as 学号, `StudentName` as 学生姓名 from students as s
··函数 concat(a,b) --将a和b拼接到一起
select concat('姓名:',StudentName) as 新名字 from student
select * from result --查询全部的考试成绩
select `StudentNo` from result
--如果有重复数据,可以去重 distinct
select distinct `StudentNo` fom result
--学生考试成绩+1分查看
select `StudentNo`,`StudentResult`+1 as `提分后` from result
select
其他作用:数据库中的表达式:文本值,列,null,函数,计算表达式,系统变量
select version() --查询数据库系统版本(函数)
select 100*3-1 as 计算结果 --用来计算 (表达式)
select @@auto_increment_increment --查询自增的步长(变量)
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}]; --指定查询的记录从哪条到哪条
3 where条件子句
作用: 检索数据中符合条件的值
逻辑运算符:
运算符 | 语法 | 描述 |
---|---|---|
and 或 && | a and b 或 a && b | 逻辑与 |
or 或 || | – | 逻辑或 |
not 或 ! | – | 逻辑非 |
最好使用英文字母
--查询考试成绩在95-100分之间
select studentNo, `StudentResult` from result where StudentResult>=95 and StudentResult<=100
--模糊查询(区间)
select studentNo, `StudentResult` from result where StudentResult between 95 and 100
--除了1000号学生之外的同学的成绩
select studentNo, `StudentResult` from result where StudentNo != 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 ============================
--查询姓刘的同学
--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 =========================
--查询1000,1002,1003号学生
select `StudentNo`,`StudentName` from `student` where StudentNo in (1000,1002,1003)
--按照地址查询
select `StudentNo`,`StudentName` from `student` where `address` in ('安徽','河南洛阳')
========================== null not null ==============================
--查询地址为空的学生 null
select `StudentNo`,`StudentName` from `student` where address='' or address is null
--查询有出生日期的学生 不为空
select `StudentNo`,`StudentName` from `student` where `BornDate` is not null
4 联表查询 join
需要查询的数据不在同一个表格。比如要查询参加了考试的同学,且得到这些同学的学号、姓名、科目编号、分数,有的信息一张表没有,另外一张表有.
思路:
- 分析需求,分析查询的字段来自哪些表(连接查询)
- 确定使用那种连接查询? 7种
确定交叉点(这两个表哪个数据是相同的)
判断的条件: 学生表中的sutdentNo = 成绩表 studentNo
========================== 联表查询 join on ==============================
--join on 连接查询 join (连接的表) on (判断的条件)
--where 等值查询
select s.studentNo,studentName,SubjectNo,StudentResult
from student as s
inner join result as r
where s.studentNo = r.studentNo --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
操作 | 描述 |
---|---|
inner join | 如果表中至少有一个匹配,就返回行 |
left join | 会从左表中返回所有的值,即使右表中没有匹配 |
right join | 会从右表中返回所有的值,即使左表中没有匹配 |
===思考题===
查询参加了考试的同学的信息:学号、学生姓名、科目名、分数
/*思路
1.分析需求,分析查询的字段来自哪些表,student、result、subject(连接查询)
2.确定使用哪种连接查询?7种
确定交叉点(这两个表中的哪个数据是相同的)
判断的条件:学生表中的 studentNo = 成绩表studentNo
*/
select s.studentNo,studentName,SubjectName,`studentResult`
from student s
right join result r
on r.studentNo = s.studentNo
inner join `subject` sub
on r.SubjectNo = sub.SubjectNo
自连接(了解)
测试用数据:
CREATE TABLE `category`(
`categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主题id',
`pid` INT(10) NOT NULL COMMENT '父id',
`categoryName` VARCHAR(50) NOT NULL COMMENT '主题名字',
PRIMARY KEY(`categoryid`)
)ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8
INSERT INTO `category` (`categoryid`,`pid`,`categoryName`)
VALUES('2','1','信息技术'),
('3','1','软件开发'),
('4','3','数据库'),
('5','1','美术设计'),
('6','3','web开发'),
('7','5','ps技术'),
('8','2','办公信息');
自己的表和自己的表连接,核心:一张表拆为两张一样的表即可
父类
categoryid | categoryName |
---|---|
2 | 信息技术 |
3 | 软件开发 |
5 | 美术设计 |
子类:子类的pid就是就是父类的categoryid
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`
5 分页和排序
====================== 分页limit 和排序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 = '数据库结构-1'
order by StudentResult DESC --order by StudentResult ASC 按升序排
--分页,如每页只显示5条数据
--语法:limit 起始值 页面的大小
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 = '数据库结构-1'
order by StudentResult DESC --order by StudentResult ASC 按升序排
limit 0,5
--第1页 limit 0,5 (1-1)*5
--第2页 limit 5,5 (2-1)*5
--第3页 limit 10,5 (3-1)*5
--第n页 limit --- (n-1)*pageSize,pageSize
--【pageSize:页面大小】
--【(n-1)*pageSize:起始值】
--【n:当前页】
--【数据总数/页面大小=总页数】
6 子查询
where (算出开的值)
本质:在where语句中嵌套一个子查询语句
where(select * from)
========================= where =========================
查询数据库结构-1的所有考试结果(学号,科目,成绩),降序排列
方式一:使用连接查询
select `StudentNo`,r.`SubjectNo`,`StudentResult`
from `result` r
inner join `subject` sub
on r.SubjectNo = sub.SubjectNo
where SubjectName='数据库结构-1'
order by StudentResult DESC
方式二: 使用子查询()
select `StudentNo`,r.`SubjectNo`,`StudentResult`
from `result`
where SubjectNo = (
select SubjectNo from `subject`
where SubjectName = '数据库结构-1')--查询所有数据库结构-1的学生学号
order by StudentResult DESC