MySQL学习笔记（四）------DQL查询数据（重点）
DQL语言
- Data Query Language：数据查询语言
- 所有查询操作都用它 select
- 简单的查询，复杂的查询都能做
- 数据库中最核心的语言，最重要的语言
- 使用频率最高的语言
- 字段名大小写不敏感
练习用数据表
create database if not exists `school`;
-- 创建一个school数据库
use `school`;
-- 创建年级表
-- 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;
-- 插入年级数据
insert into `grade` (`gradeid`,`gradename`) values(1,'大一'),(2,'大二'),(3,'大三'),(4,'大四'),(5,'预科班');
-- 创建成绩表
-- 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;
-- 插入成绩数据 其余自行添加
insert into `result`(`studentno`,`subjectno`,`examdate`,`studentresult`)
values
(1000,1,'2013-11-11 16:00:00',85),
(1000,2,'2013-11-12 16:00:00',70),
(1000,3,'2013-11-11 09:00:00',68),
(1000,4,'2013-11-13 16:00:00',98),
(1000,5,'2013-11-14 16:00:00',58);
-- 创建学生表
-- 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;
-- 插入学生数据 其余自行添加
insert into `student` (`studentno`,`loginpwd`,`studentname`,`sex`,`gradeid`,`phone`,`address`,`borndate`,`email`,`identitycard`)
values
(1000,'123456','张伟',0,2,'13800001234','北京朝阳','1980-1-1','text123@qq.com','123456198001011234'),
(1001,'123456','张三',1,1,'13800005678','辽宁大连','1988-2-5','text222@qq.com','123456198001015621'),
(1002,'123456','李四',1,5,'13800001562','吉林长春','1986-6-7','text333@qq.com','123456198001014598'),
(1003,'123456','王五',0,2,'13800002468','河北承德','1997-12-5','text444@qq.com','123456198001014623'),
(1004,'123456','赵六',0,4,'13800003579','上海浦东','1995-11-25','text555@qq.com','123456198001014865'),
(1005,'123456','丁七',1,2,'13800004562','浙江杭州','1999-4-30','text666@qq.com','123456198001019576'),
(1006,'123456','杜八',0,1,'13800004892','辽宁沈阳','1982-9-4','text777@qq.com','123456198001013567'),
(1007,'123456','赵强',1,3,'13800004652','广东深圳','1991-8-26','text111@qq.com','123456199001014659');
-- 创建科目表
-- 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 = 18 default charset = utf8;
-- 插入科目数据 其余自行添加
insert into `subject`(`subjectno`,`subjectname`,`classhour`,`gradeid`)values
(1,'高等数学-1',110,1),
(2,'高等数学-2',110,2),
(3,'高等数学-3',100,3),
(4,'高等数学-4',130,4),
(5,'C语言-1',110,1),
(6,'C语言-2',110,2),
(7,'C语言-3',100,3),
(8,'C语言-4',130,4),
(9,'Java程序设计-1',110,1),
(10,'Java程序设计-2',110,2),
(11,'Java程序设计-3',100,3),
(12,'Java程序设计-4',130,4),
(13,'数据库结构-1',110,1),
(14,'数据库结构-2',110,2),
(15,'数据库结构-3',100,3),
(16,'数据库结构-4',130,4),
(17,'C#基础',130,1);
指定查询字段
查询全部信息
select * from [表名]
select * from student
查询指定字段
select [字段1],[字段2]... from [表名]
select `studentno`,`studentname` from student
别名 as
- 给结果起一个名字 as
- 作用：有时候列名不是那么见名知意，就可以起别名
- as可以省略
select [字段1] as [别名1],[字段2] as [别名2]... from [表名] as [别名]
select `studentno` as 学号,`studentname` as 学生姓名 from student as s
函数 concat(a,b)
select concat('[前缀拼接]',[字段]) as [别名] from [表名]
select concat('姓名：',studentname) as 新名字 from student
去重 distinct关键字
- 作用：去除select查询出来的结果中重复的数据，重复数据只显示一条
select distinct [字段名] from [表名]
select distinct `studentno` from result
查询系统版本
select version()
查询计算表达式结果
select [计算式] as [别名]
select 100*3-10 as 计算结果
查询自增的步长
select @@auto_increment_increment
所有学员考试成绩统一修改
select [字段1],[字段2][计算式] ... as [别名] from [表名]
select `studentno`,`studentresult`+1 as 加分后 from result
数据库中的表达式
- 文本值
- 列
- NULL
- 函数
- 计算表达式
- 系统变量
- …
整体格式：select 表达式 from 表
where条件子句
- 作用：检索数据中符合条件的值
- 搜索的条件由一个或多个表达式组成，结果均为布尔值
逻辑运算符
- 尽量使用英文字母
| 运算符 | 语法 | 描述 |
|---|---|---|
| and && | a and b a&&b | 逻辑与，两个都为真，结果为真 |
| or || | a or b a || b | 逻辑或，其中一个为真，结果为真 |
| not ！ | not a ! a | 逻辑非，真为假，假为真 |
语法
select [字段名] from [表名] where子句
实例
-- 查询考试成绩在95-100之间的
SELECT Studentno,StudentResult FROM result
WHERE StudentResult>=95 AND StudentResult<=100;
-- AND也可以写成 &&
SELECT Studentno,StudentResult FROM result
WHERE StudentResult>=95 && StudentResult<=100;
-- 模糊查询(对应的词:精确查询)
SELECT Studentno,StudentResult FROM result
WHERE StudentResult BETWEEN 95 AND 100;
-- 除了1000号同学,要其他同学的成绩
SELECT studentno,studentresult FROM result
WHERE studentno!=1000;
-- 使用NOT
SELECT studentno,studentresult FROM result
WHERE NOT studentno=1000;
模糊查询：比较运算符
| 运算符 | 语法 | 描述 |
|---|---|---|
| is null | a is null | 如果a为null，结果为真 |
| is not null | a is not null | 如果a为not 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…其中的某一个，结果为真 |
注意事项
- 查询姓名中含有特殊字符的需要使用转义符号 ‘\’
- 自定义转义符关键字: ESCAPE ‘:’
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
- in后接具体的一个或者多个值
-- 查询学号为1000,1001,1002的学生姓名
SELECT studentno,studentname FROM student
WHERE studentno IN (1000,1001,1002);
-- 查询地址在北京,南京,河南洛阳的学生
SELECT studentno,studentname,address FROM student
WHERE address IN ('北京','南京','河南洛阳');
null 和 not null
- 不能直接写=NULL , 这是错误的 , 用 is null
- 空字符串不等于null
-- 查询出生日期没有填写的同学
SELECT studentname FROM student
WHERE BornDate IS NULL;
-- 查询出生日期填写的同学
SELECT studentname FROM student
WHERE BornDate IS NOT NULL;
-- 查询没有写家庭住址的同学(空字符串不等于null)
SELECT studentname FROM student
WHERE Address='' OR Address IS NULL;
联表查询
-
join关键词
-
join 对比
-
基本的join
-
7种join理论（了解）
3种基本join操作
- from [左表] XXX join [右表]
- 内连接 inner join
- 外连接 outer join：左外连接 left join，右外连接 right join
| 操作 | 描述 |
|---|---|
| inner join | 如果表中至少有一个匹配，就返回行 |
| left join | 会从左表中返回所有的值，即使右表中没有匹配 |
| right join | 会从右表中返回所有的值，即使左表中没有匹配 |
on和where
-
一般情况下可以通用
-
on是先筛选后关联，where是先关联后筛选
-
join on：链接查询
-
where：等值查询
-
一般用了join 后要用on 不用where
-- 等值连接
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s , result r
WHERE r.studentno = s.studentno
实例
-- 查询参加了考试的同学信息(学号,学生姓名,科目编号,分数)
SELECT * FROM student;
SELECT * FROM result;
/*思路:
(1)分析需求,确定查询的字段来自哪些表,student result,连接查询
(2)确定使用哪种连接查询?(内连接)
(3)确定交叉点（这两个表中哪个数据是相同的）
判断的条件：
学生表中的studentno = 成绩表中的studentno
*/
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student as s
INNER JOIN result as r
ON r.studentno = s.studentno
-- 右连接(也可实现)
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
RIGHT JOIN result r
ON r.studentno = s.studentno
-- 左连接 (查询了所有同学,不考试的也会查出来)
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
LEFT JOIN result r
ON r.studentno = s.studentno
-- 查一下缺考的同学(左连接应用场景)
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
LEFT JOIN result r
ON r.studentno = s.studentno
WHERE StudentResult IS NULL
-- 思考题:查询参加了考试的同学信息(学号,学生姓名,科目名,分数)
-- 三表查询
-- from A表 join B表 on（A和B表） join C表 on（B和C表）
SELECT s.studentno,studentname,subjectname,studentresult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON sub.subjectno = r.subjectno
练习
-- 思考题:查询参加了考试的同学信息(学号,学生姓名,科目名,分数)
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON sub.subjectno = r.subjectno
-- 查询学员及所属的年级(学号,学生姓名,年级名)
SELECT studentno AS 学号,studentname AS 学生姓名,gradename AS 年级名称
FROM student s
INNER JOIN grade g
ON s.`GradeId` = g.`GradeID`
-- 查询科目及所属的年级(科目名称,年级名称)
SELECT subjectname AS 科目名称,gradename AS 年级名称
FROM SUBJECT sub
INNER JOIN grade g
ON sub.gradeid = g.gradeid
-- 查询 数据库结构-1 的所有考试结果(学号 学生姓名 科目名称 成绩)
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='数据库结构-1'
自连接（了解）
练习用数据表
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技术');
- 自己的表和自己的表链接
核心
- 一张表拆为两张一样的表即可
拆表实例
- 父类
| categoryid | categoryname |
|---|---|
| 2 | 信息技术 |
| 3 | 软件开发 |
| 5 | 美术设计 |
- 子类
| pid | categoryid | categoryname |
|---|---|---|
| 3 | 4 | 数据库 |
| 2 | 8 | 办公信息 |
| 3 | 6 | web开发 |
| 5 | 7 | PS技术 |
- 操作：查询父类对应的子类关系
| 父类 | 子类 |
|---|---|
| 信息技术 | 办公信息 |
| 软件开发 | 数据库 |
| 软件开发 | web开发 |
| 美术设计 | PS技术 |
实例
-- 编写SQL语句,将栏目的父子关系呈现出来 (父栏目名称,子栏目名称)
-- 核心思想:把一张表看成两张一模一样的表,然后将这两张表连接查询(自连接)
SELECT a.categoryName AS '父栏目',b.categoryName AS '子栏目'
FROM category AS a,category AS b
WHERE a.`categoryid`=b.`pid`
分页和排序
排序 （order by）
语法
order by [字段名] [asc/desc]
-
升序：asc ， 降序：desc
-
ORDER BY，根据哪个字段排序，怎么排
-
ORDER BY 语句用于根据指定的列对结果集进行排序。
实例
- 查询 数据库结构-1 的所有考试结果(学号 学生姓名 科目名称 成绩)，按成绩升序排序
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='数据库结构-1'
ORDER BY StudentResult asc
分页 （limit）
- 为什么要分页？
- 为了缓解数据库压力，给人体验更好
语法
limit [当前数据起始值],[页面容量大小]
-- 起始页数从0开始计
示例
-- 显示1~5条数据
limit 0,5
-- 显示2~6条数据
limit 1,5
-- 显示6~10条数据 即第二页
limit 5,5
-- 公式 pageSize :页面大小 (n-1)*pageSize :起始值 n :当前页
limit (n-1)*pageSize,pageSize
实例
- 每页显示5条数据
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='数据库结构-1'
ORDER BY StudentResult DESC , studentno
LIMIT 0,5
-- 查询 JAVA第一学年 课程成绩前10名并且分数大于80的学生信息(学号,姓名,课程名,分数)
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='JAVA第一学年'
ORDER BY StudentResult DESC
LIMIT 0,10
子查询（由内及外）
什么是子查询
- 在查询语句中的WHERE条件子句中,又嵌套了另一个查询语句
- 子查询返回的结果一般都是集合,故而建议使用IN关键字
优点
- 子查询效率高，联表查询相对来说慢一些
-- 查询 数据库结构-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,subjectno,StudentResult
FROM result
WHERE subjectno=(
SELECT subjectno FROM `subject`
WHERE subjectname = '数据库结构-1'
)
ORDER BY studentresult DESC;
-- 查询课程为 高等数学-2 且分数不小于80分的学生的学号和姓名
-- 方法一:使用连接查询
SELECT s.studentno,studentname
FROM student s
INNER JOIN result r
ON s.`StudentNo` = r.`StudentNo`
INNER JOIN `subject` sub
ON sub.`SubjectNo` = r.`SubjectNo`
WHERE subjectname = '高等数学-2' AND StudentResult>=80
-- 方法二:使用连接查询+子查询
-- 分数不小于80分的学生的学号和姓名
SELECT r.studentno,studentname FROM student s
INNER JOIN result r ON s.`StudentNo`=r.`StudentNo`
WHERE StudentResult>=80
-- 在上题的基础上,添加需求:课程为 高等数学-2
SELECT r.studentno,studentname FROM student s
INNER JOIN result r ON s.`StudentNo`=r.`StudentNo`
WHERE StudentResult>=80 AND subjectno=(
SELECT subjectno FROM `subject`
WHERE subjectname = '高等数学-2'
)
-- 方法三:使用子查询
-- 分步写简单sql语句,然后将其嵌套起来
SELECT studentno,studentname FROM student WHERE studentno IN(
SELECT studentno FROM result WHERE StudentResult>=80 AND subjectno=(
SELECT subjectno FROM `subject` WHERE subjectname = '高等数学-2'
)
)
分组和过滤
Group by
- 通过…来分组
having
- 过滤分组后必须满足的条件
实例
- 查询不同课程的平均分，最高分，最低分
SELECT subjectname,AVG(studentresult) AS 平均分,MAX(StudentResult) AS 最高分,MIN(StudentResult) AS 最低分
FROM result AS r
INNER JOIN `subject` AS s
ON r.subjectno = s.subjectno
GROUP BY r.subjectno
HAVING 平均分>80;
扫一扫
2205
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
