Mysql学习(四)DQL

DQL

DQL（Data QueryLanguage 数据查询语言）：

基本结构是由SELECT子句，FROM子句，WHERE子句组成的查询块：
SELECT <字段名表>
FROM <表或视图名>
WHERE <查询条件>

所有的查询操作都用它 select
数据库中最核心的语言，使用频率最高的语句

指定查询字段

-- 查询全部的学生     select 字段 form 表
SELECT * FROM `student`;
-- 查询指定字段
SELECT `name`,`sex` FROM `student`;
-- 别名，给结果起一个名字 AS
SELECT `studentno` AS 学号,`studentname`AS 学生姓名 FROM student
-- 函数 concat(a,b)：起连接作用
SELECT CONCAT('姓名：',`studentname`) AS 新名字 FROM student
select distinct 字段名 from 表名 -- distinct：去除重复数据

数据库的列（表达式）

数据库中的表达式：文本值 ，列 ， Null ，函数 。计算表达式。系统变量…

-- 查询系统的版本号 (函数)
SELECT VERSION();
-- 用来计算   （表达式）
SELECT 100*300+1 AS 计算结果;
-- 查询自增的步长（变量）
SELECT @@auto_increment_increment;
-- 学院考试成绩加一分查看
SELECT`studentno`,`studentresult`+1 AS '加一分后' FROM result;

where 条件子句

作用：检索数据中符合条件的值

搜索的条件由一个或者多个表达式组成，结果是布尔值

运算符	语法	描述
and &&	a and b a&&b	逻辑与，两个都为真，结果为真
or \	\		a or b a\	\	b	逻辑或，其中一个为真则结果为真
not !	not a !a	逻辑非

尽量使用英文

-- where练习
SELECT `studentno`,`studentresult` FROM result;
-- 查询考试成绩在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 NOT `studentno`=1000;

模糊查询： 比较运算符

运算符	语法	描述
is null	a is null	如果操作符null 则结果为真
is not null	a is not null	如果操作符null 则结果为假
BETWEEN AND	a betweeb b and c	若a 在b和c 之间 则结果为真
like	a like b	SQL匹配，如果a匹配b,则结果为真
in	a in (a1,a2,……)	假设a在a1,或者 a2……其中的某一个值中，结果为真

-- 模糊查询
-- 查询姓张的同学
-- 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 '张__';
-- 查询名字中间有 大字的同学
SELECT `studentno`,`studentname` FROM `student`
WHERE `studentname` LIKE '_大_';
-- in 操作
--in后面加括号，值加单引号，多值用逗号隔开（具体的一个或者多个值）
-- 查询 1001 1000 学员
SELECT `studentno`,`studentname` FROM `student`
WHERE `studentno` IN(1000,1001);
-- 查询在北京的学生
SELECT `studentno`,`studentname` FROM `student`
WHERE `address` IN('广东深圳');
-- null not null 操作
-- 查询地址为空的学生 null
SELECT `studentno`,`studentname` FROM `student`
WHERE `address`='' OR address IS NULL;

联表查询

join 对比

操作	描述
inner join	如果表中至少有一个匹配，就返回行
left join	会从左表中返回所有的值，即使右表中没有匹配
right join	会从右表中返回所有的值，即使左表中没有匹配

join(连接的表) on (判断的条件) 连接查询

where 等值查询

七种Join：

测试

学生表

最后一名吴谦同学是没有成绩的

成绩表

科目表

年级表

/*
连接查询
   如需要多张数据表的数据进行查询,则可通过连接运算符实现多个查询
内连接 inner join
   查询两个表中的结果集中的交集
外连接 outer join
   左外连接 left join
       (以左表作为基准,右边表来一一匹配,匹配不上的,返回左表的记录,右表以NULL填充)
   右外连接 right join
       (以右表作为基准,左边表来一一匹配,匹配不上的,返回右表的记录,左表以NULL填充)
       
等值连接和非等值连接

自连接
*/

-- 查询参加了考试的同学信息(学号,学生姓名,科目编号,分数)
SELECT * FROM student;
SELECT * FROM result;

/*思路:
(1):分析需求,确定查询的列来源于两个类,student result,连接查询
(2):确定使用哪种连接查询?(内连接)
*/
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
INNER JOIN result 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 , result r
WHERE 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

-- 思考题:查询参加了考试的同学信息(学号,学生姓名,科目名,分数)
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 `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	categoryName
3	4	数据库
2	8	办公信息
3	6	web开发
5	7	ps设计

需求:从一个包含栏目ID , 栏目名称和父栏目ID的表中 查询父栏目名称和其他子栏目名称

-- 编写SQL语句,将栏目的父子关系呈现出来 (父栏目名称,子栏目名称)
-- 核心思想:把一张表看成两张一模一样的表,然后将这两张表连接查询(自连接)
SELECT a.categoryName AS '父栏目',b.categoryName AS '子栏目'
FROM category AS a,category AS b
WHERE a.`categoryid`=b.`pid`

父类	子类
信息技术	办公信息
软件开发	数据库
软件开发	web开发
美术设计	ps技术

排序

语法 : ORDER BY
ORDER BY 语句用于根据指定的列对结果集进行排序。
ORDER BY 语句默认按照ASC升序对记录进行排序。
如果您希望按照降序对记录进行排序，可以使用 DESC 关键字。

学生表

成绩表

科目表

-- 查询高等数学-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 DESC

分页

好处 : (用户体验,网络传输,查询压力)

瀑布流:刷不完的首页推荐内容

语法： limit 当前页 页面的大小
– 规律
每页显示5条数据
– 第一页 limit 0,5 -----> (1-1)* 5
– 第二页 limit 5,5 -----> (2-1)* 5
– 第三页 limit 10,5 -----> (3-1)* 5
– 第N页 limit 0,5 -----> (n-1)* pageSize
pageSize:页面大小
(n-1)* pageSize： 起始值
– 总页数： 数据综述/页面大小

-- 每页显示3条数据
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,3

-- 查询 JAVA第一学年 课程成绩前3名并且分数大于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='高等数学-1'
ORDER BY StudentResult DESC
LIMIT 0,3

子查询

在查询语句中的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

-- 在上面SQL基础上,添加需求:课程为 高等数学-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'
  )
)

/*
练习题目:
   查 C语言-1 的前5名学生的成绩信息(学号,姓名,分数)
   使用子查询,查询郭靖同学所在的年级名称
*/