-- 查看系统中有哪些数据库
show databases;
-- 创建test数据库
create database test;
-- 选择进入数据库
use test;
-- 删除数据库(慎用)
-- drop database test;
-- 创建数据表
use test;
create table departmentd(deptid int ,
dname varchar(15),
num int
);
-- 查看当前数据库中有哪些表
show tables;
-- 查看表结构
desc department;
-- 删除数据表(慎用)
drop table departmentd;
-- 创建带有约束条件的表(因为两张表中有主外键约束,所以需要先创建主键所在的dept,再创建外键所在的emp)
create table dept(
deptno int primary key,
dname varchar(15),
loc varchar(10)
);
create table employee(
empid int primary key auto_increment,
ename varchar(10) unique,
job varchar(10) not null default '-',
mgr int,
hiredata date,
sal float default 0,
comm float,
deptid int,
foreign key(deptid) references dept(deptno)
);
show tables;
desc employee;
select * from emp;
-- 修改表名
alter table employee rename emp;
-- 修改字段名
desc emp;
alter table emp change empid empno int;
alter table emp change deptid deptno int;
desc emp;
-- 修改字段类型
alter table emp modify empno int auto_increment;
alter table emp modify sal decimal default 0;
desc emp;-- 查看表结构
-- 添加字段
alter table emp add city varchar(10) first;
alter table emp add address varchar(20) not null default '不详' first;
desc emp;
-- 修改字段的排列位置:
alter table emp modify city varchar(10) after ename;
desc emp;
-- 删除字段
alter table emp drop city;
alter table emp drop address;
desc emp;
-- 插入数据:字段名与字段值的数据类型、个数、顺序必须一一对应
insert into dept(deptno,dname,loc) values (10,'accounting','new york'),(20,'research','dallas');
insert into dept values (30,'sales','chicago'),(40,'operations','boston');
insert into dept values (50,'sales','chicago'),(60,'operations','boston');
select * from dept;-- 检查表中的数据内容
-- 批量导入数据(路径中不能有中文,‘\’在编程语言中是转义符,需要将‘\’改为‘\\’或‘/’)
-- 先有部门,才能存储每个部门的员工信息,所以先添加dept的部门信息,再导入emp的员工信息
show variables like '%secure%';-- 查看安全路径
load data infile "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/employee.csv"
into table emp
fields terminated by ','
ignore 1 lines;
select * from emp; -- 检查导入数据内容
select count(*) from emp; -- 检查导入数据总行数
select * from emp where mgr is null;
-- 更新数据
-- set sql_safe_updates=0; -- 设置数据库安全权限
update emp set sal=sal+1000 where deptid=10;
update emp set sal=sal+1000;
update emp set sal=5000 where deptid=10;
select * from emp;
-- 删除数据
delete from emp where deptid=30;
delete from dept where deptno=60;
-- 清空数据
truncate emp;
-- 单表查询(虚拟结果集)
select * from emp;
-- 查询指定列:查询emp表中ename,job,sal
select ename,job,sal from emp;
-- 设置别名:查询每位员工调整后的薪资(基本工资+1000)
select ename,job,sal,sal+1000 as 薪资 from emp;
select ename,job,sal,sal+5000 as xinzi from emp;
select ename 姓名,job,sal,sal+1000 薪资 from emp;
-- 练习:查询每位员工的年薪(基本工资*12):empno,ename,年薪
select empno,ename,sal,sal*12 年薪 from emp;
select empno,ename,sal,sal*12 年薪 from emp;
-- 查询不重复的数据:查询emp表中有哪些部门
select distinct deptid from emp;
select distinct deptid,job from emp;
-- 条件查询
-- 查询10号部门和20号部门中sal低于2000的员工信息
select * from emp where (deptno=10 or deptno=20) and sal<2000;
select * from emp where deptno in(10,20) and sal<2000;-- in为取值范围;
-- 练习:查询基本工资大于等于2000小于等于3000的员工信息
select * from emp where sal>=2000 and sal<=3000;
select * from emp where sal between 2000 and 3000;
-- 空值查询
-- 查询mgr为空的记录
select * from emp where mgr is null;
-- 练习:查询comm不为空的记录
select * from emp where comm is not null;
-- 模糊查询
-- 查询姓名以a开头的员工信息
select * from emp where ename like 'a%';
-- 查询姓名中包含a的员工信息(出现在开头、结尾都可以)
select * from emp where ename like '%a%';
-- 查询姓名中第二个字符为a的员工信息
select * from emp where ename like '_a%';
-- 练习:查询员工姓名中不包含s的员工信息
select * from emp where ename not like '%s%';
-- 查询结果排序
-- 单字段排序:查询所有员工信息按sal降序显示
select * from emp order by sal desc;
-- 多字段排序:查询所有员工信息按deptno升序、sal降序显示(先按照部门的升序排,然后再按照同一个部门的降序排)
select * from emp order by deptid,sal desc;
-- 限制查询结果数量
-- 查询基本工资最高的前5位员工
select * from emp order by sal desc limit 5;
select * from emp order by sal desc limit 0,5;
-- 查询基本工资第6到10名的员工
select *from emp order by sal desc limit 5,5;
-- 练习:查询最后入职的5位员工
desc emp;
alter table emp change deptid deptno int;
alter table emp change hiredata hiredate int;
select * from emp order by hiredate desc limit 5;
-- 聚合运算
-- 查询emp表中员工总数、最高工资、最低工资、平均工资及工资总和
select count(*),max(sal),min(sal),avg(sal),sum(sal) from emp;
-- 分组查询:分组查询之后,分组结果中,只能显示分组字段、聚合字段以及跟分组字段一一对应的字段
-- 查询各部门的平均工资
select * from emp group by deptno;-- 显示的分组字段
select deptno,avg(sal) from emp group by deptno;-- 分组后的各部门的平均工资
-- 查询各部门不同职位的平均工资
select deptno,job,avg(sal)
from emp
group by deptno,job;
-- 练习:查询各部门的员工数
select deptno,count(empno)
from emp
group by deptno;
-- 练习:查询各部门不同职位的人数
select deptno,job,count(empno)
from emp
group by deptno,job;
-- 分组后筛选
-- 查询各部门clerk的平均工资
select deptno,job,avg(sal)
from emp
group by deptno,job
having job='clerk';
select deptno,job,avg(sal)
from emp
where job='clerk'
group by deptno,job;
-- 查询平均工资大于2000的部门
select deptno,job,avg(sal)
from emp
group by deptno,job
where avg(sal)>2000;-- 报错原因:语法书写顺序错误
select deptno,job,avg(sal)
from emp
group by deptno,job
having avg(sal)>2000;
-- 多表连接查询
create table t1(key1 char,v1 int);
create table t2(key2 char,v2 int);
insert into t1 values('a',1),('a',2),('b',3),('c',4),('a',13);
insert into t2 values('b',10),('b',11),('a',12),('a',13),('e',14);
select * from t1;
select * from t2;
-- 内连接
select * from t1 inner join t2 on t1.key1=t2.key2;-- .限定符
-- 左连接
select * from t1 left join t2 on t1.key1=t2.key2;-- left join左边为主biao
-- 右连接
select * from t1 right join t2 on t1.key1=t2.key2;-- t2为主表,RIGHT JOIN 右边为主表
-- 合并查询
-- union去重
select * from t1
union
select * from t2;
-- union all 不去重
-- union all不去重
select * from t1
union all
select * from t2;
-- MYSQL实现全连接的方法
select * from t1 left join t2 on t1.key1=t2.key2
union
select * from t1 right join t2 on t1.key1=t2.key2;
-- MYSQL实现左反连接的方法
select * from t1 left join t2 on t1.key1=t2.key2
where t2.key2 is null;
-- 多表查询练习
create table salgrade(grade int,losal int,hisal int);
insert into salgrade values(1,700,1200),
(2,1201,1400),
(3,1401,2000),
(4,2001,3000),
(5,3001,9999);
select * from salgrade;-- 5
select * from emp;-- 14
select * from dept;-- 4
-- 查询每位员工的ename,dname,sal
select *
from emp
left join dept
on emp.deptno=dept.deptno;
-- 查询各地区的员工数(统计每个城市,没有员工计为0)(1对多)
select loc,count(empno)
from dept
left join emp
on dept.deptno=emp.deptno
group by loc;
-- 查询manager的姓名、所属部门名称和入职日期:ename,dname,job,hiredate(内连接/笛卡尔积连接)
select ename,dname,job,hiredate
from emp
left join dept
on dept.deptno=emp.deptno
where job='manager';
select ename,dname,job,hiredate
from emp,dept
where dept.deptno=emp.deptno and job='manager';
-- 查询所有员工姓名及其直属领导姓名(自连接:通过别名,将同一张表视为多张表)
select 员工表.ename as 员工姓名,领导表.ename as领导姓名
from emp as 员工表
left join emp as 领导表
on 员工表.mgr=领导表.empno;
-- 查询入职日期早于其直属领导的员工姓名及其所属部门:empno,ename,dname (两张以上的多表连接)
-- 连接条件:
select 员工表.empno,员工表.ename,dname
from emp as 员工表
left join emp as 领导表 on 员工表.mgr=领导表.empno
left join dept on 员工表.deptno=dept.deptno
where 员工表.hiredate<领导表.hiredate;
-- 查询每位员工的工资等级;empno,ename,sal,grade(不等值连接)
select *
from emp
left join salgrade
on sal between losal and hisal;
select empno,ename,sal,grade
from emp
left join salgrade
on sal between losal and hisal;
-- 子查询
-- 标量子查询:
-- 查询基本工资高于公司平均工资的员工信息
use test;
select *
from emp
where sal>avg(sal);-- 报错:where子句中不能用聚合函数;
#查询公司的平均工资
select avg(sal) from emp;
select *
from emp
where sal>2073.2143;
select *
from emp
where sal>(select avg(sal) from emp);
-- 练习:查询和allen同一个领导的员工:empno,ename,job,mgr
#查询allen的直属领导的工号
select mgr from emp where ename='allen';
select empno,ename,job,mgr
from emp
where mgr=7698 and ename<>'allen';
select empno,ename,job,mgr
from emp
where mgr=(select mgr from emp where ename='allen') and ename<>'allen';
-- 行子查询
-- 查询和smith同部门同职位的员工:empno,ename,job,deptno
#先查询smith的部门号和职位
select deptno,job from emp where ename='smith';
select *
from emp
where deptno=20 and job='clerk';
select *
from emp
where (deptno,job)=(20,'clerk');-- 有问题!
select *
from emp
where (deptno,job)=(select deptno,job from emp where ename='smith') and ename!='smith';
-- 列子查询:
-- 查询普通员工的工资等级:empno,ename,sal,grade
#查询那些人不是普通员工,即领导的工号
select * from emp;
select mgr from emp where mgr is not null;
select *
from emp
left join salgrade
on sal between losal and hisal
where empno not in (select mgr from emp where mgr is not null);
-- 联系 查询员工数不少于5人的部门的所有员工,empno,ename,sal,deptno
#查询员工数不少于5人的部门
select deptno,count(empno) from emp group by deptno having count(empno)>=5;
select empno,ename,sal,deptno
from emp
where deptno in (select deptno from emp group by deptno having count(empno)>=5);
-- 查询基本工资高于30号部门任意员工的员工信息
#查询30号部门任意员工的基本工资
select sal from emp where deptno=30;
select * from emp
where sal>any(select sal from emp where deptno=30) and deptno<>30;
select * from emp
where sal>(select min(sal) from emp where deptno=30) and deptno<>30;
-- 查询基本工资高于30号部门所有员工的员工信息
select sal from emp where deptno=30;
select * from emp
where sal>all(select sal from emp where deptno=30) and deptno<>30;
select * from emp
where sal>(select max(sal) from emp where deptno=30) and deptno<>30;
-- from子查询
-- 查询各部门最高工资的员工:empno,ename,sal,deptno
#查询各部门最高工资
select deptno,max(sal) from emp group by deptno;
select * from emp
left join (select deptno,max(sal) as 最高工资 from emp group by deptno) as t
on emp.deptno=t.deptno
where sal=最高工资;
-- select子查询
-- 查询各部门员工人数占比
select deptno,count(empno)
from emp
group by deptno;
select count(empno) from emp;
select deptno,round(count(empno)/(select count(empno) from emp),2)
from emp
group by deptno;
-- 常用函数
select abs(-32);
select floor(1.23);
select floor(-1.23);-- -2
select ceiling(1.23);
select ceiling(-1.23);-- -1
select round(1.58);
select round(1.58,1);-- 保留一位小数
-- 数学函数
-- 练习:查询各部门员工人数占比(保留两位小数)
-- 字符串函数
-- 练习:查询各部门员工人数占比(以百分比显示)
select concat('CDA','数据', '分析');
select concat('CDA',null, '分析');-- null
select instr('CDA', 'A');
select instr('ADA', 'A');-- 返回1,找到以后就再也不找了
select instr('数据分析', 'CDA');-- 找不到就是0
select left('CDA数据分析', 3);
select right('CDA数据分析', 4);
select mid('CDA数据分析', 4, 2);-- MID(str,pos,len) 返回字符串str的位置pos起len个字
select mid('CDA数据分析', 4);-- 数据分析
select substring('CDA数据分析',1,3);
select substring('CDA数据分析',1);-- 提取1开始所有字符
select ltrim(' CDA数据分析');
select rtrim('CDA数据分析 ');
select trim(' CDA数据分析 ');
select replace('CDA数据分析', 'CDA', 'cda');
select upper('cda');
select lower('CDA');
-- 练习:查询各部门员工人数占比(以百分比显示)
select deptno,concat(round(count(empno)/(select count(empno) from emp)*100,2),'%')
from emp
group by deptno;
-- 日期函数
select date('20200101');
select date('2020-01-01 00:00');
select week('2020-01-01',1);
select week('2020-01-01');-- 默认这一周为2020年第0周、2019年最后一周
select week('2020-07-08');
select year('20-01-01'); -- 返回指定日期的年份(范围在1000到9999)
select month('2020-06-01');-- 返回指定日期的月份(范围在1到12)
select day('2020-01-31');-- 返回指定日期的日(范围在1到31)
select hour('2020-01-01 12:00:00');-- 返回的是0-23的数值
select date_add("2020-01-01",interval 1 day);-- 加一天
select date_add("2020-01-01",interval 2 year);
select date_add("2020-01-01",interval 2 month);
select date_add("2020-01-01",interval -2 month);-- 可以是加负数的
select date_sub("2020-01-01", interval 1 day);-- 减一天
select date_sub("2020-01-01", interval 1 year);
select date_sub("2020-01-01", interval 1 month);
select date_format('20-01-01 12:00:00','%Y-%m');-- 结果:2020-01 根据format字符串格式化date值 在format字符串中可用标志符
select date_format('20-01-01 12:00:00','%m');-- 结果:01
select date_format('20-01-01 12:00:00','%Y-%m-%d');-- 结果:2020-01-01
select curdate();-- 以'yyyy-mm-dd'或yyyymmdd格式返回当前日期值(根据返回值所处上下文是字符串或数字)
select curtime();-- 以'hh:mm:ss'或hhmmss格式返回当前时间值(根据返回值所处上下文是字符串或数字)
select curdate() + 0;-- 结果:20200708
select now();-- 以'yyyy-mm-dd hh:mm:ss'或yyyymmddhhmmss格式返回当前日期时间(根据返回值所处上下文是字 符串或数字)
select curtime() + 0;-- 结果:172634
select datediff(curdate(),'2020-7-5');-- 结果:3 返回结束日expr1和起始日expr2之间的天数
select unix_timestamp();-- 1594200486 返回一个unix时间戳(从'1970-01-01 00:00:00'开始的秒数,date默认值为当前时间)
select unix_timestamp('2020-01-01');-- 1577808000
select from_unixtime(1594191822);-- 2020-07-08 15:03:42 以'yyyy-mm-dd hh:mm:ss'或yyyymmddhhmmss格式返回时间戳的值(根据返回值所处上下文是字符 串或数字
-- 练习:查询每位员工的工龄(年):ename,hiredate,工龄
select ename,floor(datediff(curdate(),hiredate)/365) as 工龄 from emp;
-- 分组合并函数
/*分组合并函数
GROUP_CANCAT([distinct] str [order by str asc/desc] [separator])
将group by产生的同一个分组中的值连接起来,返回一个字符串结果。*/
-- 练习:查询各部门的员工姓名
select deptno,group_concat(ename)
from emp
group by deptno;
select deptno,group_concat(distinct ename)-- 去重
from emp
group by deptno;
select deptno,group_concat(distinct ename order by ename separator '/')-- 可以按照基本工资降序排列、按照入职日期排列
from emp
group by deptno;
-- 逻辑函数
/*IFNULL(expression, alt_value)
判断第一个表达式是否为 NULL,如果为 NULL 则返回第二个参数的值,如果不为 NULL 则返回第一个 参数的值*/
-- ifnull函数:查询每位员工的实发工资(基本工资+提成,没有提成计为0)
select * ,sal+ifnull(comm,0) as 实发工资
from emp;
-- if函数:查询每位员工的工资级别:3000及以上为高,1500-3000为中,1500及以下为低
/*IF(expr1,expr2,expr3) 如果expr1的值为true,则返回expr2的值,如果expr1的值为false,则返回expr3的值。*/
select *,if(sal>=3000,'高',if(sal>=1500,'中','低')) as 工资级别
from emp;
-- 逻辑表达式 case when ...then... else ... end
/*CASE WHEN expr1 THEN expr2 [WHEN expr3 THEN expr4...ELSE expr] END
如果expr1的值为true,则返回expr2的值,如果expr3的值为true,则返回expr4的值...*/
select *,
case when sal>=3000 then '高'
when sal>=1500 then '中'
else '低'
end as 工资级别
from emp;
select *,
case when sal>=3000 then '高'
when sal>=1500 then '中'
else '低'
end as 工资级别
from emp;
-- 开窗函数
-- 开窗函数的本质还是聚合运算,只不过它更具灵活性
-- 聚合函数用于开窗函数
-- 查询所有员工的平均工资
select avg(sal) as 平均工资 from emp;-- 普通的把多行计算为一行;
select *,avg(sal) over() as avg_sal from emp;-- 行数不变
#当over中没有指定分区、排序、滑动窗口时,表中所有记录为一个区,默认计算的是分区内的所有行;
-- 查询各部门的平均工资
select deptno,avg(sal) as avg_sal
from emp
group by deptno;
select *,deptno,avg(sal) over(partition by deptno) as avg_sal from emp;-- 同一个区内计算所有行
#当over中没有指定排序和滑动窗口时,默认计算的是分区内的所有行
-- 查询各部门员工按照入职日期的累计工资总和
select *,sum(sal) over(partition by deptno order by hiredate) as sum_sal from emp;
#当over中指定排序但是没有指定滑动窗口时,默认计算的是分区内的第一行
-- 查询各部门员工的移动平均工资
select *,avg(sal) over(partition by deptno order by hiredate rows between 1 preceding and 1 following) as avg_sal from emp;
#当over中指定了滑动窗口,计算的是滑动窗口内的所有行
-- 序号函数
-- row_number() 显示分区中不重复不间断的序号(不会显示并列的函数)
-- 查询公司所有员工工资排名
select * ,row_number() over(order by sal desc) as 排名 from emp;-- 不会显示并列的情况
-- 查询各部门员工工资排名
select *,row_number() over(partition by deptno order by sal desc) as 排名 from emp;
select *,row_number() over(partition by deptno order by sal desc) as 排名1,
dense_rank() over(partition by deptno order by sal desc) as 排名2,
rank() over(partition by deptno order by sal desc) as 排名3
from emp;
在第一节基础上 更新了函数和执行顺序:实操了字符数函数、时间函数、字段截取函数、interval函数等,除最后顺序实操因未导入数据库,导致无法执行