mysql数据查询总结

--查询全部数据
select * from grade;
--别名as
select `gradename`  as 年级名称 ,`gradeid` as 年级编号 from grade;
--加条件查询
select `gradename`  as 年级名称 ,`gradeid` as 年级编号 from grade where `gradeid`>=3;
--拼接函数concat
select CONCAT('年级编号', gradeid) as gradeid from grade;

--去重distinct,重复数据仅显示一条
select distinct `studentno` from `result`;

--按列批量加
select `studentno`, `studentresult`+1 as new_core from result ;

逻辑运算符and&&、or||、not！

---

模糊查询like、in 结果为布尔值

运算符号	语法	备注
like	where a like ‘%xxx_’	%多个字符、_单个字符
in	where value in (Val1,val2,val3,…）	[集合中val为具体值]
is null	where a is null
is not null	a is not null
between	a between b and c	[b,c] 闭区间

---

联表查询join

操作	描述
Left join	会从左表中返回所有值，即使右表中没有匹配
Right join	会从右表中返回所有值，即使左表中没有匹配
Inner join	如果表中至少一个匹配，返回匹配值

说明：join on连接查询、where等值查询

/*分析思路：
1. 分析需求-表信息、确定连接查询方式（可根据下一步需求分析）
2. 确定查询方式：交叉点、判断条件
*/
--查询缺考同学
select s.studentno from student as s
left join result as r
on s.studentno=r.studentno
where studentresult is NULL;

--查询参加考试的同学信息（学号、姓名、科目名称、分数）
select st.studentno,studentname,studentresult,subjectname 
from `student` as st 
right join `result` as r
on r.studentno=st.studentno 
inner join `subject` as sb
on r.subjectno=sb.subjectno;

---

自联接–一张表拆分为两张一样的表即可（了解）

create table `category`(
	`categoryid` int(4) unsigned not null auto_increment comment '主题id',
	`pid` int(4) unsigned not null comment '父id',
	`categoryname` varchar(50) not null comment '主题名称',
	primary key (`categoryid`) 
	)engine=innodb default charset=utf8;
/*
2	1	信息技术
3	1	软件开发
4	3	数据库
5	1	美术设计
6	3	web开发
7	5	ps技术
8	2	办公信息
*/

-- 查询父子类的对应信息
select p.categoryname as '父栏目' ,c.categoryname as '子栏目' 
from `category` as c, `category` as p
where p.categoryid = c.pid;
/*
父栏目	子栏目
软件开发	数据库
软件开发	web开发
美术设计	ps技术
信息技术	办公信息
*/

---

分页limit和排序order by asc ｜desc

-- 按成绩降序
select st.studentno,studentname,studentresult,subjectname 
from `student` as st 
right join `result` as r
on r.studentno=st.studentno 
inner join `subject` as sb
on r.subjectno=sb.subjectno
order by studentresult desc;

--分页：缓解数据库压力
select st.studentno,studentname,studentresult,subjectname 
from `student` as st 
right join `result` as r
on r.studentno=st.studentno 
inner join `subject` as sb
on r.subjectno=sb.subjectno
order by studentresult desc
limit 1,1; -- 查询起始值，页面大小


-- 查询 ‘Java程序设计-1’ 课程排名前十的学生，分数大于80：学号、姓名、课程名称、分数
select st.studentno as '学号' ,studentname as '姓名',subjectname as '课程名称', studentresult as '分数'
from student as st
right join result as r
on r.studentno=st.studentno
inner join `subject` as sb
on r.subjectno=sb.subjectno
where subjectname='Java程序设计-1' and studentresult >=80
order by studentresult desc
limit 0,10 ;

子查询-where值不确定，where语句中嵌套一个子查询语句

-- 查询‘数据库结构-1’的所有考试结果:学号、科目编号、成绩，降序排列
select studentresult,studentno,subjectno
from result as r where r.subjectno = (
	select subjectno from `subject` 
	where subjectname='数据库结构-1' 
) 
order by studentresult desc;

-- 查询‘数据库结构-1’ 且 分数不小于80的同学的学号和姓名（执行过程由里及外）
select studentno,studentname from student where studentno in (
	select studentno from result where subjectno = (
		select subjectno from subject where subjectname='数据库结构-1') and studentresult>=80);

---

mysql函数:基础函数+聚合函数（常用）

函数	含义
select CURRENT_DATE select curdate() select NOW() select LOCALTIME select sysdate() select YEAR(NOW())	时间和日期
select user() select version()	系统root@localhost 8.0.21
select char_length(‘i love keke’) select CONCAT(‘love’, ‘keke’) select LOWER(‘FLYING’) select upper(‘icey’)	字符串函数:长度、拼接、大小写转化 、

聚合函数count、sum、avg、max、min[important]

聚合函数名称	描述
select count(studentname) from student;	忽略所有null值
select COUNT(1) from student; select COUNT(*) from student;	不会忽略null，本质计算行数
select sum(studentresult) as ‘总和’ from result;	求和

分组过滤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`=r.`subjectno`
group by subjectname
having 平均分>70;


select subjectname from subject where subjectno in (
	select subjectno from result);


select avg(studentresult) as 平均分,max(studentresult) as 最高分 ,min(studentresult) as 最低分
from result where subjectno in (
	select subjectno from subject)
group by subjectno;


-- select查询字段须在表中存在？？子查询怎么写？报错：Unknown column 'subjectname' in 'field list'
select subjectname,avg(studentresult) as 平均分,max(studentresult) as 最高分 ,min(studentresult) as 最低分
from result where subjectno in (
	select subjectno from subject)
group by subjectno;

---

测试md5加密

-- 明文密码
insert into `student` values
(1010,'123456','test',1,1,'13800002222','广东','1990-1-1','text111@qq.com','123456199001011266');

-- 更新密码
update `student` set loginpwd=md5(loginpwd) where `studentno`=1010;

-- 修改密码约束
alter table `student` modify loginpwd varchar(60);

-- 插入数据时加密
insert into `student` values
(1011,md5('123456'),'fighting',1,2,'13800002222','广东','1990-2-1','text111@qq.com','123456199001011267');

-- 数据校验
select studentname from student where studentno=1011 and loginpwd=(md5('123456'));

说明：课程来源哔哩哔哩狂神说Java