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