DataQueryLanguage（DQL）

DQL

语法：

select 要查询字段……
from 表……
where 条件…… -- 指定结果需要满足的条件
group by 字段……-- 指定结果按照那几个字段来分组
having 次要条件……-- 过滤分组的记录必须满足的次要条件
order by …… -- 指定查询记录按照一个或多个条件排序
limit -- 指定查询的记录从那条开始那条

1、指定查询字段

-- 查询全部 select 字段 from 表名 
select * from grade;
select * from result;
select * from student;
select * from `subject`;

-- 查询指定字段
select StudentNo,studentName from student;

-- 别名，给结果取个小名，AS 可以给字段取别名，也可以给表取别名(as可以省略)
select StudentNo as 学号,StudentName as 学生姓名 from student as s;

-- 函数 concat(a,b)
select concat('姓名：',studentName) 新名字 from student;

语法： select 字段 …… from 表名

去重 distinct作用是去除select查询出来的重复数据

-- 发现重复数据，去重
select distinct studentNo from result;

数据库的列 （表达式）

select VERSION() -- 查询系统版本
select 100*9 as 计算;
SELECT @@auto_increment_increment -- 查询自增步长

-- 学员考试成绩 + 1分查看
select studentNo,studentResult + 1 提分后 from result;

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

• select 表达式 from 表

2、where条件子句

作用：检索数据中符合条件的值
检索的条件由一个或多个条件组成，返回值经常为一个布尔值

• 逻辑运算符：与（and）、或（or）、非（not）

select studentNO,studentResult from result
where studentresult >= 95 and studentresult <= 100;

select studentNO,studentResult from result
where studentresult >= 95 && tudentresult <= 100;
-- 模糊查询区间
select studentNO,studentResult from result
where studentresult between 95 and 100;

-- 除了1000号学生之外的同学成绩
select studentNo,studentResult from result
where not studentno = 1000;

3、模糊查询

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

• % （代表0到任意个字符） _(代表一个字符)

-- ==============  模糊查询  ====================
-- 查询姓张的同学
-- like 结合 % （代表0到任意个字符） _(代表一个字符)
select studentNo,studentName from student
where studentname like '张%';

select studentNo,studentName from student
where studentname like '张_';

-- 查询1000，1002,1003的学生
select studentNo,studentName from student
where studentNo in(1000,1002,1003);
-- 查询地址
select studentNo,studentName from student
where address in('四川成都');

四、联表查询 Join

-- 查询参加了考试的同学（学号，姓名，科目编号，分数）
select s.studentno,studentname,`subjectno`,studentResult
from student as s
inner join result as r
where s.studentno = r.studentno;

-- RIGHT JOIN
select s.studentno,studentname,`subjectno`,studentResult
from student s
right join result r
on s.studentno = r.studentno;

-- left JOIN
select s.studentno,studentname,`subjectno`,studentResult
from student s
left join result r
on s.studentno = r.studentno;

-- 查询缺考的同学
select s.studentno,studentname,`subjectno`,studentResult
from student s
left join result r
on s.studentno = r.studentno
where studentResult is null;


-- 三表查询
select s.StudentNo,StudentName,SubjectName,Studentresult
from student s
right join result r
on r.studentno = s.studentno
inner join  `subject` as sub 
on r.subjectno = sub.subjectno;

-- join on 连接查询
-- where 等值查询 
/*
思路 
1、分析需求，分析查询的字段来自那些表
2、确定用那种连接查询
3、确定交集
4、判断条件
*/
-- 联表查询练习
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` s
INNER JOIN grade g
on s.gradeid = g.gradeid;



-- 查询了参加 高等数学-1 考试的同学信息：学号、学生姓名、科目名、分数
SELECT s.studentno as '学号',studentname as '姓名',`subjectname` as '学科名称',studentresult as '分数'
from student s
INNER JOIN result r
on s.studentno = r.studentno
INNER JOIN `subject` as sub 
on sub.subjectno = r.subjectno
WHERE sub.subjectname = '高等数学-1'

区别

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

五、自连接

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)
)

INSERT INTO category
 VALUES('2','1','信息技术'),('3','1','软件开发'),('4','3','数据库'),('5','1','美术设计'),('6','3','web开发'),('7','5','ps技术'),('8','2','办工信息');

select a.categoryName as '一级菜单',b.categoryName as '二级菜单'
from category as a,category as b
WHERE a.categoryid = b.pid;

select * from category;

思想就是把一张表看成两张表，就像一二级目录一样

六、分页和排序

排序语法 ：ORDER BY 字段 asc或desc;

-- 排序： 升序asc 降序 desc

SELECT s.studentno as '学号',studentname as '姓名',`subjectname` as '学科名称',studentresult as '分数'
from student s
INNER JOIN result r
on s.studentno = r.studentno
INNER JOIN `subject` as sub 
on sub.subjectno = r.subjectno
WHERE sub.subjectname = '高等数学-1'
ORDER BY studentresult asc; -- 升序排序

分页

-- 分页语法 limit 起始值,
select * 
from `subject`
LIMIT 0,5;
-- 查询了参加 高等数学-1 考试的同学前三的信息（学号、学生姓名、科目名、分数）
SELECT s.studentno as '学号',studentname as '姓名',`subjectname` as '学科名称',studentresult as '分数'
from student s
INNER JOIN result r
on s.studentno = r.studentno
INNER JOIN `subject` as sub 
on sub.subjectno = r.subjectno
WHERE sub.subjectname = '高等数学-1' AND studentresult >= 80
ORDER BY studentresult desc
LIMIT 0,3;

七、子查询

-- ================= 子查询 ======================

-- where （这个值是计算出来的）

-- 本质：在where语句中嵌套一个子查询
-- where (select * from)

-- 查询高等数学-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;
-- 查询所有高等数学-1的学生学号

select studentno 
from `subject`
where subjectname = '高等数学-1';

八、MySQL常用函数

官网地址 https://dev.mysql.com/doc/refman/8.0/en/built-in-function-reference.html

常用函数

-- 数学函数
SELECT ABS(-8)-—绝对值
SELECT CEILING (9.4)--向上取整
SELECT FLOOR(9.4)-―向下取整
SELECT RAND ()--返回一个0~l 之间的随机数
SELECT SIGN (10)--判断一个数的符号0-0负数返回-1，正数返回1
-- 宁符串函数
SELECT CHAR LENGTH ('即使再小的帆也能远航') --字符串长度
SELECT CONCAT('我'，'爱'，'你们') --拼接字符串
SELECT INSERT('我爱编程helloworld'，1,2,'超级热爱')--查询，从某个位置开始替换某个长度
SELECTLOWER('Kuangshen') -―小写字母
SELECT UPPER('KuangShen') --大写字母
SELECT INSTR ( ' kuangshen', 'h') -- 返回第一次出现的子串的索引
SELECT REPLACE ('狂神说坚持就能成功', '坚持', '努力') --替换出现的指定字符串
SELECT SUBSTR ('狂神说坚持就能成功',4,6) --返回指定的子字符串 (源字符串,截取的位置，截取的长度)
SELECT REVERSE（'清晨我上马')—-反转


--时间和日期函数(记住)
SELECT CURRENT_DATE()--获取当前日期
SELECT CURDATE()--获取当前日期
SELECT NOW()--获取当前的时间
SELECT LOCALTIME（)--本地时间
SELECT SYSDATE()-―系统时间
SELECT YEAR (NOw())
SELECT MONTH (NOW() )
SELECT DAY (NOW ())
SELECT HOUR (NOw())
SELECT MINUTE (NOw())
SELECT SECOND (NOW ())

聚合函数

-- ============= 聚合函数 =========
--都能够统计表中的数据(想查询一个表中有多少个记录，就使用这个count ())SELECT COUNT (`BornDate ') FROM student; -- Count(字段)，会忽略所有的null值
SELECT COUNT(*) FROM student; -- Count (*)，不会忽略 null值，本质计算行数
SELECT COUNT (1) FROM result;-- Count (1)，不会忽略忽略所有的 null值本质计算行数
SELECT SUM ( `StudentResult `) AS总和FROM result
SELECT AVG ( `studentResult ` ) As 平均分FROM result
SELECT MAX (~StudentResult`) AS 最高分FROM result

-- 查询不同课程的平均分，最高分,最低分--核心:(根据不同的课程分组)
SELECT SubjectName，AVG(studentResult),MAX(StudentResult),MIN(StudentResult)
FROM result r
INNER JOIN `subject` sub
ON r. `SubjectNo` = sub.subjectNo
GROUP BY r.subjectNo --通过什么宁字段来分组

九、测试用的数据库sql

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行
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,3,'13800002222','广东深圳','1990-1-1','text111@qq.com','123456199001011233');

-- 插入成绩数据  这里仅插入了一组，其余自行添加
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);

-- 插入年级数据
insert into `grade` (`gradeid`,`gradename`) values(1,'大一'),(2,'大二'),(3,'大三'),(4,'大四'),(5,'预科班');

-- 插入科目数据
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);