扫码加入数据分析学习群

-- 查看系统中有哪些数据库

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;