mysql数据库note2:DQL数据查询语言

-- DQL data query language 数据查询语言

/*
	1.单表查询：
	2.多表查询
	3.子查询
	4.统计分析查询
*/

-- 一.单表查询
-- 查询全部数据
select * from admin;

-- 查询指定列
select adminId,adminCode'账户',adminPass'密码' from admin;				-- 取别名

-- 查经过计算后的值
select adminId+100,adminCode from admin;

-- 消除查询中的重复
select distinct adminPass from admin;

-- 查询满足条件的元组
/*
	比较：=,>,>=,<,<=,!=,!+上述操作符
	范围：between and,not between and
	集合：in,not in
	匹配：like,not like
	空值：is null,is not null(空值不是一个具体的值)
	多重条件：and,or,not
*/
-- 比较大小
select * from admin where adminId > 1;

-- 确定范围
select * from admin where adminId BETWEEN 1 and 10;

-- 确定集合
select * from admin where adminPass not in('root','123456');

-- 字符匹配
select * from admin where adminCode like 'A%';		-- %任意长度字符
select * from admin where adminCode like 'B_%';		-- _任意一个字符
select * from admin where adminCode not like 'A%' -- 首字母不是A的条件
select * from admin where adminCode like 'B/_%' ESCAPE '/'	-- /后的一个字符不转义。条件是like B_%
select * from admin where adminFlag is null;			-- 查询为空，不能用= NULL
select * from admin where adminPass = '123456' and adminId < 10;			-- 多重条件查询
select * from admin where adminCode='root' or adminPass = 'www' OR privilege is null;

-- 去重
select distinct adminPass from admin;

-- 列标题as
select adminId as '编号',adminCode as '账号',adminPass as '密码' from admin;

-- 虚拟临时表
select 'Aruiea' from dual;

-- 字符运算
select concat('Account:',adminCode,'   Password:',adminPass) as '信息' from admin;

-- 表别名
select a.adminCode,a.adminPass from admin a;

-- 对查询的数据排序
select * from admin ORDER BY adminId;		-- 默认升序 order by asc
select * from admin order by adminId desc;		-- 默认降序	order by desc
select * from admin where adminId between 1 and 5 ORDER BY adminId,adminPass desc;		-- id在1-5之间所有数据，根据adminId升序，adminPass降序排列。






-- 条件查询	（流程函数）	
-- case 列 whne 列数据  then '展示数据' else '默认选项'  end（结束）	
-- 类似于if else，case写在要查询的字段前
select 
case privilege
 when >0 then '普通优先级'
 when >1 then '超级优先级'
 else '默认权限'
end
'权限'
from admin;

-- 带条件的
select adminCode,
case when privilege <=0 then '一般权限'
		 when privilege <=1 then '有限权限'
	   when privilege <=2 then '超级权限'
		 else '没有权限'
end
'权限'
from admin;


-- 函数function：
/*
	单行函数：每行都有一个结果
					字符函数：类似String类（用的不多）
							concat(str)
							length(str)
							upper(str)/lower(str)
							substring(str,pos)      substring(str,pos,len)
							lpad(str,len,padstr)
							replace(str,from_str,to_str)
					数值函数：类似Math类
					日期函数：类似Date,Calendar类
					其他函数：转换，判断，加密等
	多行函数：完成一行或多行结果集的运算，最后返回一个结果。
					count(*):			统计每个组中元素个数（分组后）
					count(column):同级列中值个数
					sum(column):	计算列值和，必须时数值型
					avg(column):	计算列平均值，必须是数值型
					max(column):	一列中最大值
					min(column):	一列中最小值
*/

-- 结果和字符组合
select concat('account',adminCode) from admin;			-- 用不了中文的原因？

-- 获得结果字符长度
select length(adminCode) from admin;

-- 大小写转换
select upper(adminCode)'大写账号' from admin;
select lower(adminCode)'小写账号' from admin;

-- 截取字符串  ：从1开始
select substring(adminCode,1,5) from admin;  -- 从第一个截取，截取2个

-- 补零：l左   r右
select lpad(privilege,8,'x') from admin;		-- 不足8位，补上x
select rpad(privilege,8,'x') from admin;

-- 替换
select replace(adminCode,'a','xxx') from admin;		-- 把所有a替换成xxx

-- 其他字符函数，见mysql api







-- 数值函数:数学函数都能用，见api
select 3/6 ;	-- 0.5：会自动转换，不会丢失精度

-- 向上截取
select ceil(2.5);		-- 向上取整
select floor(-2.5);	-- 向下取整

-- 四舍五入
select round(-2.55)	-- 四舍五入 符号|+0.5，向下取整|
select round(2.555555,2)  -- 精度两位，四舍五入，2.56

-- 取模
select mod(-10,3)		-- 取余的结果只和被取余数有关。

-- 幂
select pow(2,4)		-- 2的4次幂

-- 截断
select truncate(2.55555555,2)		-- 直接截断
-- 







-- 日期函数
select now();		-- 当前日期时间
select curdate();	-- 只有当前日期
select curtime();	-- 只有当前时间
update admin set creDate = '2025-07-07' where adminId = 1;		-- mysql中字符串可以直接使用

-- 日期转换 date函数自动转换日期
update admin set creDate = date('2020/07/16') where adminId = 2;

-- 日期相加：adddate(now(),31) 从现在开始一个月时间
select NOW()'放假开始时间',adddate(NOW(),15)'结束时间';

-- 日期格式化date_format
select * from admin where date_format(creDate,'%Y') = 2020 ;	-- 日期是2020年的数据
select * from admin where date_format(creDate,'%m') = 7 ;			-- 月份是7的数据
select * from admin where date_format(creDate,'%Y-%m') = '2020-07' ;			-- 年份是2020，月份是7的数据
select * from admin where date_format(creDate,'%Y-%m-%d') = '2020-07-16' ;			-- 年份是2020，月份是7.天数是16的数据

-- 转换指定日期类型
select LAST_DAY('2020-04-05');
select STR_TO_DATE('12/27/2025'	,'%m/%d/%Y');		-- 按指定格式转换






-- 加密函数 🕵️‍♂️     md5:不可逆机密，但是一个值对应一个码，可以暴力破解
select md5('123456');		-- 按照md5加密函数
select password('Aruiea');	-- 按照MD5加密
select SHA1('123456');
select encrypt('123456');
select encode('123','account')		-- 加密
select decode('上面加密的函数','account');		-- 解密






🎈

-- 空处理
select ifnull(creDate,'0000-00-00') from admin;	-- 如果为null，默认为'0000-00-00'
select isnull(creDate) from admin;		-- 0为假，1为真。

-- 查询最后插入值的信息
select LAST_INSERT_ID() from admin;

-- uuid：唯一32位标识符
select uuid();






🍲
-- 多行函数等
select count(*) from admin;		-- 总的列数
select sum(adminId + privilege) from admin;		-- 求和
select avg(adminId + privilege) from admin;		-- 平均值

-- 分组查询->组合->多行
select count(*) from admin;			-- 查询分组个数






😁-- group by:分组
-- count(列名)：统计列有多少行数据。
select avg(adminId) from admin;		-- 多行函数都不统计null值。

-- group by 列：通过列分组，对分组数据进行处理
select tid,count(*) from student group by tid;		-- 统计每组中的组的个数
select tid,count(score) from student group by tid;		-- 统计每组中score下的数据个数，不算null

-- 使用多行函数，必须在select后出现，才可以在where后面使用。

-- having子句，对group by后的分组进行条件判断
select tid,count(*),max(score),min(score),round(avg(score),1) as avgscore from student		-- 查询tid,最高分，最低分，平均分
where score is not NULL 		-- 没参加考试的不统计
group by tid		-- 通过tid分组
having avgscore < 90		-- 对分组后的组再进行条件判断
order by avgscore desc;		-- 最后的结果通过order by排序

-- 考试前三名，top-n查询
select * from student
order by score desc
limit 0,3		-- 获得里面的前三条数据，0一般不写


-- 分页查询
select count(*) from student;		-- 数据总个数total
select ceil(11/3)			-- 页码个数：TotalNum
select * from student limit 0,3;		-- 每页查询三个元素，numPerPage
select * from student limit 3,3;		-- 从第4条开始，查询3条。当前页page第一个元素的下标index = (page-1)* numPerPage

-- 聚集函数只能用于select子句和group by中的having子句。







/*
	查询顺序
*/
-- 1.先写好要查询的信息列名
-- 2.再找到需要查询的表
-- 3.最后添加上连接条件和其他条件判断








-- 多表查询
/*
	连接查询：多表查询
	where子句中用=连接时，叫等值连接，使用其他运算符，叫非等值连接。（算数云运算符，between and）
	等值连接  ：
	非等值连接：
*/

-- 笛卡尔乘积
-- 两张表A,B 默认把A的每一条数据拿出来，和B的每一条数据相比较。
select student.*,sc.* from student,sc;	-- 默认集合相乘
select student.*,sc.* from student,sc where student.Sno = sc.Sno;		-- 给元组添加索引会更快

/*
	自然连接：
	在等值连接中把目标列中重复的属性列去掉则为自然连接。
*/
-- 查询学生情况以及学生的选课情况
select s.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
from student s,sc
where s.Sno = sc.Sno;

select * from student;
-- 查询选修2号课程且成绩在90分以上的所有学生的学号和姓名,选修课的具体成绩
select s.Sno,s.Sname ,sc.Grade
from student s,sc
where s.Sno = sc.Sno and sc.Cno=2 and sc.Grade>90;

/*
	自身链接：
	一个表和自身相连接
	使用情况：自身的列引用了自身的列数据
			加入学生情况中还有一个每个人的班长，班长又是从学生中选，就可以把学生id当作数据存入班长manager这个列
*/
-- 查询学生选课成绩大于90分，且班长是项羽的学生姓名，选修课成绩，年纪，性别。
select s1.Sname,s1.Sage,s1.Ssex,sc.Grade														-- 1.先写好要查询的信息列名
from student s1,sc,student s2																				-- 2.再找到需要查询的表
where s1.Sno = sc.Sno and s1.manager = s2.Sno												-- 3.最后添加上连接条件和其他条件判断
AND sc.Grade>90 
and s2.Sname = '秦始皇';			

/*
	外连接：等值查询中，只会显示有相等值得属性，如果我们想显示一个表得所有元组，则需要用到外连接
		左外连接：left outer join 列名 on(连接条件)
		右外连接：right outer join 列名 on(连接条件)
*/
-- 查询所有学生得情况以及每个人的选课情况（无论选没选课）
select student.*,sc.* 
from student left outer join sc on(student.Sno=sc.Sno);

/*
	多表连接：
		一张表和两张以上的表进行连接。
*/
--  查询每个学生的学号，姓名，选秀的课程名及成绩
select s.Sno,Sname,ECname,Grade
from student s,sc,extclass
where s.Sno = sc.Sno and sc.Cno = extclass.id;









/*
	嵌套查询：（子查询）
		把一个查询块嵌套到另一个查询块的where或者having子句中。
		相关子查询  ：
		不相关子查询：子查询得查询条件不依赖于父查询条件

		SQL语言允许多层嵌套，子查询的select语句中，不能使用orderby子句。
	order by只能对最终查询结果进行排序。
*/

/*
	以下是不相关子查询
*/
-- 查询学生选课为2查询学生姓名
select s.Sname
from student s
where s.Sno in (select c.Sno from sc c where c.Cno = 2);
-- in:在子查询集合之内		=any在子查询结果的所有结果中
select s.Sname
from student s
where s.Sno = any(select c.Sno from sc c where c.Cno = 2);

-- 当子查询结果多余一行是，不能使用=运算符
select s.Sname
from student s
where s.Sno = any(select c.Sno from sc c where c.Cno = 2);

-- 查询与刘晨在同一个系得所有学生。
select s.Sname
from student s
where s.Sdept in 
(select Sdept from student where Sname='刘晨')


/*
	以下是相关子查询：子查询得查询条件依赖于父查询
	这个查询也叫：相关嵌套查询
*/
-- 找出每个学生超出他自己选修课程平均成绩的课程号	
select c1.Cno					-- select后面的是从结果中选择的数据
from sc c1						-- from后面的才是查询结果。		这里相当于把sc(c1)当作一张表，从第一个元素，逐层向下匹配。
where c1.Grade >=			-- 每取出一条数据，就得到这条数据得grade列值，并判断是否>=后面得值
-- 子查询：查询第二张表sc(c2)，把外面表c1的当前条数据和第二张表c2逐条对比，得到结果（和当前c1.Sno相同的所有c2.Sno）
-- 底层查询就是用笛卡尔积的方式，这点需要注意！
(select avg(Grade) from sc c2 WHERE c1.Sno = c2.Sno) 		

-- 查询 除了计算机与应用系中  比软件工程系任意一个学生  年龄小  的学生姓名和年龄。
select Sname,Sage
from student
where Sage < ANY(select Sage FROM student where Sdept = '软件工程') 		-- 注意！子查询集合前要使用any
and Sdept != '计算机与应用';

/*
	用聚集函数查询通常比子查询效率要高
	=ANY : IN
	<ANY : <MAX
	!ANY : NOT IN
	<ALL : <MIN
*/

/*
	EXISTS:代表存在量词，只返回真/假
	NOT EXISTS:取反
*/
-- 查询没有选修1号课程的学生姓名
select Sname
from student s
WHERE NOT EXISTS
(select * FROM sc where sc.Sno = s.Sno AND sc.Cno = 1);

-- 查询与‘刘晨’在同一个系中的学生姓名
select Sname
from student
where Sdept 
in(select Sdept from student where Sname = '刘晨')

/*
	SQL中没有全称量词，所以需要转换使用.
  一个学生选修了所有的课程=没有一门课程是这个学生没选修的
*/
-- 查询选修了全部课程的学生姓名
select Sname
from student
where NOT EXISTS		-- 查询不存在？的学生：不存在这个学生没选的课程
(select * from extclass where not EXISTS		-- 查询不存在？的所有课程：查询他没选的课程
(select * from sc where Sno=student.Sno and Cno=extclass.id)		-- 查询当前条数据选修的所有课程：查询他选的课程。
)


/*
	集合查询：UNION（并）
		UNION:并集，去掉重复列
		UNION ALL:不去掉重复列
		参加集合操作的各查询结果列数必须相同；对应项的数据类型也必须相同。
*/
-- 查询计算机与应用系得学生及年龄不大于19岁的学生。
select * from student
where Sdept='计算机与应用'
-- UNION -- ALL
select * from student 
where Sage<=19;




-- 行列转换：给要展示的列取别名，把数据统计通过分组展示到每一组
-- 统计所有学生中男女的个数
-- 行列转换  给列取别名，通过分组，对每组进行条件判断操作。
select
count(case when ssex = '男' then ssex end) as '男性' ,
count(case when ssex = '女' then ssex end) as '女性' 
from student