(day03):MySQL函数及多表查询

最新推荐文章于 2023-05-08 20:03:27 发布

爱上攻城狮2021

最新推荐文章于 2023-05-08 20:03:27 发布

阅读量813

点赞数

文章标签： mysql 数据库 database

本文链接：https://blog.csdn.net/xiaodi2016/article/details/121257325

版权

注意：开发中函数可以到MySql文档中查找，是函数都需要加().

一：练习

1.新建教室表
教室id 主键自增
课程非空

2.新建表 person
id 主键自增
名字非空
邮箱唯一
性别默认男
房间编号关联教室表教室id 级联策略删除父表数据子表对应数据设置为null 修改父表数据子表数据一同修改

3.分别插入三条数据

4.删除父表数据
修改父表数据

create table clazzroom(

	cid int primary key auto_increment,
	crouse varchar(20)
);

create table person(
	pid int primary key auto_increment,
	pname varchar(20) not null,
	email varchar(20) unique key,
	sex char default '男',
	roomid int,
	foreign key(roomid) references clazzroom(cid) on delete set null on update cascade
	
);


-- 插入数据向 教室表

insert into clazzroom values(null,"JAVA");
insert into clazzroom values(null,"H5");
insert into clazzroom values(null,"BigData");

select * from clazzroom;

-- 向学生表插入数据

insert into person(pid,pname,email,roomid) values(null,"李白","lb@163.com",1);
insert into person(pid,pname,email,roomid) values(null,"杜甫","df@163.com",2);
insert into person(pid,pname,email,roomid) values(null,"王安石","was@163.com",3);
-- 错误演示
insert into person(pid,pname,email,roomid) values(null,"李商隐","lsy@163.com",6);

select * from person;

-- 删除 cid =1 的数据

delete from clazzroom where cid =1;

-- 修改 将cid =2 的值 改为 cid =6


update clazzroom set cid = 6 where cid = 2;

(二):数学函数

-- 绝对值
select ABS(1),ABS(-1);
-- 向下取整floor()
-- 向上取整 ceil()
select FLOOR(3.6),FLOOR(4.3),CEIL(4.3),CEIL(5.6);
-- 四舍五入
-- round(3.56) 只要整数部分对小数部分四舍五入
select ROUND(3.56);
-- round(小数,整数) 保留整数位小数对整数位后面的小数进行四舍五入
select ROUND(3.12345,2);
select ROUND(3.12945,4);
-- 保留指定位数的小数不会进行四舍五入
select TRUNCATE(3.56,1);
-- 保留0位小数
select TRUNCATE(3.56,0);
-- 开平方
select SQRT(16);
-- x^y
select POW(3,3);

(三):日期函数

-- 3.日期函数
-- 获取当前的年月日，时分秒
select CURDATE(),CURTIME();
-- sysdate()执行那一刹那的时间 NOW()获取了一次值之后，第二次获取的是缓存中的
select NOW(),SYSDATE(),SLEEP(5),NOW(),SYSDATE();
-- 获取年月
select YEAR(NOW()),MONTH(NOW());
-- 获取月份的名字
select MONTHNAME(NOW()),MONTHNAME("2022-2-1");
-- dayofweek 周日是1 weekday 周一是 0
select DAYOFWEEK(NOW()),WEEKDAY(NOW());
-- 返回周几的英文字母
select DAYNAME(NOW());
-- 两个日期之间的差值 select datediff(date1,date2); date1 -date2
select DATEDIFF(NOW(),'2021-11-5');
-- 改变日期
select DATE_ADD(NOW(),interval -1 year);
select DATE_ADD(NOW(),interval 1 year);
select PASSWORD(123456);

(四):其他函数

-- 返回正在操作的数据库
select DATABASE();
-- 返回数据库的版本
select VERSION();
-- 返回正在操作数据库的用户
select USER();
-- 加密方式
select PASSWORD(123456);
select MD5("床前明月光")

(五)：流程函数

/*

if(value,t,f) value 真 t 假 f


*/
select * from t_employee;

select ename,salary from t_employee;
-- 如果薪资 >=15000 高工资  一般工资
select ename ,salary , IF(salary>=150000,"高工资","一半工资") from t_employee;
/*
ifnull(value1,value2)
	如果value1 不是null 返回value1

	如果value1 是null 返回默认值value2
*/
-- 获取年薪  保留两位小数
select ename , salary "月薪", TRUNCATE((salary * 12 *(IFNULL(commission_pct,0) + 1)),2)"年薪" from t_employee;
select ename , salary "月薪", ROUND((salary * 12 *(IFNULL(commission_pct,0) + 1)),2)"年薪" from t_employee;
/*
case when 条件 1 then result1
	when 条件 2 then result2
	else result3  end

*/

/*
判断薪资等级
     >20000          A
     15000 <= 20000  B
     10000 <=15000   C
     <10000          D


*/

select 
	ename , salary , 
case 
	when salary > 20000 then "A"
	when salary <= 20000 then "B"
	when salary <= 15000 then "C"
	else "D" end "工资等级"
from  	
	t_employee;
/*
根据入职日期 判断员工的 资深程度   2013 之前入职  老油条

                                   2013-2015      中油条
                                   
                                   2015 之后       小油条
*/

select 
	ename , hiredate ,
case 
	when hiredate < '2013-1-1' then "老油条"
	when hiredate >= '2013-1-1' && hiredate < '2015-1-1' then "中油条"
	else 
		"小油条"
		end "员工资历"
from t_employee;

/*
case expr when 常量值1 then 值1
		when 常量值2 then 值2

*/
-- 展现 每一个员工的部门名称
select * from t_department;

select 
	ename , did ,
case 	did 
when
	1 then "教学部"
when 	
	2 then "咨询部"
else	
	"无业部"
end 	"没有此部门"
from 	
	t_employee;

(六)：多行分组函数

avg() 求平均数
sum() 求总和
max() 求最大值
min() 求最小值
count() 求数量

注意：
   1. 把多条记录汇总为一条结果
   2. 会自动忽略null值
   3. 运行的效率(终止主键运行的效率是最高的，因为主键有索引)
       count(主键)>count(*)>count(3)
       count(主键)>count(1)>count(*)

/*
6.多行(分组)函数
 

*/


select MAX(salary),SUM(salary),AVG(salary),MIN(salary),COUNT(salary)from t_employee;
-- 单行函数与多行函数嵌套
select AVG(salary),ROUND(AVG(salary),2),TRUNCATE(AVG(salary),2) from t_employee;
-- 多行(分组)函数   会自动忽略null值  
select ename, commission_pct from t_employee where commission_pct is not null;
select COUNT(eid),COUNT(*),COUNT(1) from t_employee;
show create table t_employee;

(七):关联查询

关联查询: 去除笛卡尔积找到符合要求的结果
7.关联查询
   sql标准
sql92
   会将连接条件和筛选条件放到一起
sql99
   将连接条件和筛选条件分开
7.1 内连接
sql99
   select 展示的字段

   from A [inner] join B

   on 连接条件

   where 筛选条件；

sql92

select 展示的字段

from A,B

where 连接条件 ,筛选条件;
7.2 外连接

   左外连接
   left join
   右外连接
   right join

   全外连接
       union 将两个结果集并到一起
注意因为mysql中没有关键字支持全外连接，因此在使用左外连接和右外连接的时候，
   字段名以及其顺序要相同

select * from t_employee;
select * from t_department;
-- 查询 e.`eid` , e.ename , e.`did` ,d.dname
-- sql99
select e.`eid` , e.ename , e.`did` ,d.dname

from t_employee e inner join t_department d

on 
	e.`did` = d.`did`

-- sql92
select 
	e.`eid` , e.ename , e.`did` ,d.dname

from 
	t_employee e , t_department d
where 
	e.`did` = d.`did`;
	
	
-- 展现 每一个员工的 部门名称
-- sql 99
select	e.`ename` , e.`did` , d.`dname`
	
from 	
	t_employee e inner join t_department d

on
	e.`did` = d.`did`
	
-- sql 92
select	
	e.`ename` , e.`did` , d.`dname`
from 	
	t_employee e , t_department d
where 
	e.`did` = d.`did`;
	
-- -- 展现 每一个员工的 工作名称
-- sql 99
select e.`eid` , e.`ename` , j.`job_id` ,j.`job_name`

from 	
	t_employee e join t_job j
on
	e.`eid` = j.`job_id`;

-- sql 92

select e.`eid` , e.`ename` , j.`job_id` ,j.`job_name`

from t_employee e, t_job j

where 
	e.`eid` = j.`job_id`;
	

	
-- 展现 每一个员工的 部门名称 男员工 并且 薪资 >10000
-- sql 99
select  e.`ename`,e.`salary`,e.`gender`,d.`dname`,d.`description` 
 
from 
	t_employee e join t_department d
on
	e.`did` = d.`did`
where 
	e.`gender` = '男' and e.`salary` > 10000;
	
-- sql 92 

select  e.`ename`,e.`salary`,e.`gender`,d.`dname`,d.`description` 

from 
	t_employee e , t_department d
where	
	e.`did` = d.`did` and e.`gender` = '男' and e.`salary` > 10000;

(八)：全外连接关联查询的练习 union

1. 将姚笛的部门编号设为null

2.将陈赫的工作编号设为null

3. 在部门表新增一个部门人事部

4. 在工作表新增一个工作人事主管

员工表作为 A 表部门表作为B表

展现 A 中所有的数据 B 中所有的数据 A∪B

员工表作为 A 表工作表作为B表

展现 A 中所有的数据 B 中所有的数据 A∪B

左外连接展现的是左表所有的数据，右表中没有的话补null

右外连接展现的是右表所有的数据，左表中没有的话补null

全外连接展现的是左右表中所有的数据左表独有的单独占一行，右表补null

右表独有的单独占一行，左表补null

select e.`ename`,e.`job_id`,j.`job_name`,j.`description`

from t_employee e left join t_job  j

on e.`job_id`=j.`job_id`

union

select e.`ename`,e.`job_id`,j.`job_name`,j.`description`

from t_employee e right join t_job  j

on e.`job_id`=j.`job_id`;

列出薪资>9000的每一个员工的名字工作名称工作的描述部门名称部门描述

-- sql99

select e.`ename`,e.`salary`,j.`job_name`,j.`description`,d.`dname`,d.`description`

from t_employee e 

join t_job j on e.`job_id`=j.`job_id`

join t_department d  on e.`did`=d.`did`

where e.`salary`>9000;

-- sql92

select e.`ename`,e.`salary`,j.`job_name`,j.`description`,d.`dname`,d.`description`

from t_employee e,t_job j,t_department d 

where e.`job_id`=j.`job_id` and e.`did`=d.`did` and e.`salary`> 9000;

(九):自连接

-- 获取每一个员工的员工编号名字薪水上级编号以及上级员工的员工编号名字薪水入职日期

-- 当前员工的上级编号 = 上级员工的员工编号

-- 解题思路把当前表即作为员工表又作为上级表

-- 关键是员工表中的上级id 等于上级表中的员工id

-- sql 99
select 
	e.`eid` , e.`ename` , e.`salary` , e.`mid` , boss.`eid` , boss.`ename` , boss.`salary` , boss.`hiredate`

from 
	t_employee e join t_employee boss

on	
	e.`mid` = boss.`eid`;
	
-- sql 92
select 
	e.`eid` , e.`ename` , e.`salary` , e.`mid` , boss.`eid` , boss.`ename` , boss.`salary` , boss.`hiredate`
from 
	t_employee e, t_employee boss
where 
	e.`mid` = boss.`eid`

(十) ： select 七大子句上

1> from: 从哪些表中筛选

2> on: 关联多表查询时，去除笛卡尔积

3> where : 从表中筛选的条件

4> group by :分组依据

5> having : 在统计结果中再次筛选

6> order by : 排序

7> limit : 分页

select  ename from t_employee where salary >10000;
-- distinct 去重
select distinct did from t_employee;
-- distinct 字段1 字段2  只去除字段1 2 的组合重复情况
select distinct did , job_id from t_employee;
select 
	e.`ename` , d.`dname` , d.`description` , e.`salary` , e.`gender`
from 
	t_employee e , t_department d
where 
	e.`did` = d.`did` and e.`salary` > 10000 and e.`gender` = '女'

(十一) ： select 七大子句中

注意：
   1.group by 进行分组
   group by 分组的条件;

2. 在进行分组时 select 后面放分组字段放其他字段有结果没意义
(因为分组结束之后总数居的条数将减少，此时如果放其他字段的话，会发生数据与原数据错乱不吻合)
-- 统计 每一个部门的平均薪水
select e.`ename` , e.`did` , AVG(salary) "平均工资"
	
from t_employee e 

group by e.`did`
   3.如果对分组后的数据再次筛选使用having 子句

4.如果分组完毕就已经拿到了所有的分组结果可以根据自己的实际需求进行数据展示，
       没有展示出来的数据，也是已经分组后的

5.可以按照多个条件进行分组

-- 统计 每一个部门的最高薪水
select e.`did` , MAX(e.`salary`)

from t_employee e

group by e.`did`;


select salary from t_employee e where did = 1 order by salary desc limit 1;


-- 统计 男女员工的人数
select e.`gender` , COUNT(e.`eid`) "男女员工人数"

from t_employee e 

group by e.`gender`


-- 统计每一个工作的平均薪水

select e.`job_id` , AVG(e.salary)

from t_employee e

group by e.`job_id`

-- 统计 每一个部门的男生 平均薪水

select e.`did`,e.`ename` , AVG(e.`salary`)

from t_employee e

where e.`gender` = '男'

group by e.`did`

-- 统计 每一个部门的男生 平均薪水 显示 平均薪资>30000
select	e.`ename` , AVG(e.`salary`)

from t_employee e

where e.`gender` = '男'  # 部门中的男生

group by e.`did`   # 根据部门分组分组

having AVG(e.`salary`) > 30000    # having用在分组之后


--  统计 每一个部门的 平均薪水   该部门的人数>=2

select e.`did` , AVG(e.`salary`) , COUNT(1)

from t_employee e

group by e.`did`

having COUNT(e.`eid`) >3 

-- 显示每一个部门的最高薪水 及其部门名称
select e.`did` , MAX(e.`salary`) , d.dname
	
from 	t_employee e join t_department d

on  
	e.`did` =  d.did

group by e.`did`


-- 进行多个条件分组

select e.`did`, e.`job_id` ,AVG(e.`salary`)

from t_employee e

group by e.`did` , e.`job_id`;  # 只有多个条件都相同的才会被分成一组

(十二) : 子查询

where

   注意：
   如果子查询的结果数量>1 应该使用 all any 对结果进行修饰
   all 是与子查询所有结果比较
   any 是与子查询任意一个结果比较

from
   将子查询作为数据源


exists
   去除没有关联的数据

-- 薪资最高的人的信息

-- 先找到薪资最高是多少
select * 
from t_employee
where salary =
(
	select MAX(salary) from t_employee
);
-- 薪资比 孙红雷  黄晓明  贾乃亮 工资高的人的信息
select * 

from t_employee e

where salary > all

(select salary

from   t_employee e

where e.`ename` in ('孙红雷','黄晓明','贾乃亮'))
-- all  与 In 
select * 

from t_employee e

where salary > any

(select salary

from   t_employee e

where e.`ename` in ('孙红雷','黄晓明','贾乃亮'))

-- 求每一个部门的平均薪水 部门的名称 

select e.did , AVG(e.`salary`),d.dname

from t_employee e , t_department d

where e.`did` = d.did

group by e.`did`;

-- 子查询
-- 求每一个部门的平均薪水 部门的名称 
select e.`did` , AVG(e.`salary`) , d.dname

from t_employee e

group by e.`did`

join 

(select *

from t_department) d

on e.`did` = d.did;
 -- 求每一个部门的平均薪水 部门的名称 
select 	d.`did` , d.`dname` , d.`description` ,e.avgsal

from t_department d

join
(
select `did` ,AVG(salary) avgsal

from t_employee 

group by t_employee.`did`

) e

on d.`did` = e.did ;
 -- 找 有员工的部门 exists
select d.`did` , d.`dname`

from t_department d

where exists

(
select *

from t_employee  e

where 
	e.`did` = d.`did`
)

(十三) : select七大子句下

注意：
   1.group by 进行分组
   group by 分组的条件;

2. 在进行分组时 select 后面放分组字段放其他字段有结果没意义

3.如果对分组后的数据再次筛选使用having 子句

4.如果分组完毕就已经拿到了所有的分组结果可以根据自己的实际需求进行数据展示

5.可以按照多个条件进行分组


order by :排序
排序字段 asc : 从小到大
desc: 从大到小

如果安装多个字段排序： order by 字段1 ，字段2 ，字段3

limit: 分页

limit:(pageNo-1)*pageSize,pageSize;

pageNo:第几页
pageSize：每页显示的数量

select ename , salary , did from t_employee order by did asc 

select ename , salary,did  from t_employee order by salary desc;


-- 首先按照 did 从小到大 然后当did 相等 再按照薪水 从大到小
select *
from t_employee e
order by e.`did` ,
salary asc ;

-- 每页显示3条 显示第二页
select * from t_employee
limit 3,3
-- 求 员工的工资>8000 按照部门分组 求平均工资 
select AVG(salary),COUNT(eid),AVG(salary)
from t_employee
where salary > 8000
group by did
having COUNT(eid) >= 4
order by AVG(salary)
-- 显示部门人数>=2 的 结果 按照 最平均工资 排序 
limit 2,2

(十四) : 事务（保证了数据的安全性）

保证所有事务都作为一个工作单元来执行，即使出现了故障，都不能改变这种执行方式。当在一个事务中执行多个操作时，要么所有的事务都被提交(commit)，那么这些修改就永久地保存下来；要么数据库管理系统将放弃所作的所有修改，整个事务回滚(rollback)到最初状态。

实例

例如转账操作：A账户要转账给B账户，那么A账户上减少的钱数和B账户上增加的钱数必须一致，也就是说A账户的转出操作和B账户的转入操作要么全部执行，要么全不执行；如果其中一个操作出现异常而没有执行的话，就会导致账户A和账户B的转入转出金额不一致的情况，为而事实上这种情况是不允许发生的，所以为了防止这种情况的发生，需要使用事务处理。

事务的ACID属性：

1>原子性（Atomicity）

2>一致性（Consistency）

3>隔离性（Isolation）

4>持久性（Durability）

开启事务(作用效果使得当前会话永久有效)
   set autocommit = false;

   start transaction;只有一次有效

   rollback 回滚

   commit    提交
   如果进行一次回滚，或者进行了一次提交代表着当次事务的结束
注意：事务只对增删改有效

对 DDL 无效

create table account(

	id int primary key auto_increment,
	
	aname varchar(20) ,

	balance int 

)

desc account ;

 -- 开启事务
start transaction ;

-- 事务一直有效
set autocommit = false;


insert into account values(null,"张三",2000);

insert into account values(null,"李四",2000);

select * from account;

update account set balance = 2000  where id = 1;

update account set balance = balance - 1000  where id = 1;

update account set balance = balance + 1000 what id = 2;

rollback;

爱上攻城狮2021

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
(day03):MySQL函数及多表查询

注意：开发中函数可以到MySql文档中查找，是函数都需要加().一：练习1.新建教室表教室id 主键自增课程非空2.新建表 person id 主键自增名字非空邮箱唯一性别默认男房间编号关联教室表教室id 级联策略删除父表数据子表对应数据设置为null 修改父表数据子表数据一同修改 3.分别插入三条数据 4.删除父表数据修改父表数据create t...
复制链接

扫一扫