扫码加入数据分析学习群

1.Sql的特点

-- 换行不会影响代码执行

create database test;

-- 不同关键词可以采用不同大小写，不会影响代码执行

CREATE database test;

-- 单个关键词内部大小写混用不会影响代码执行

creaTE database test;

-- 创建单行注释

# 创建单行注释

/*创建多行注释 */

2.DDL

-- 创建一个名为 test 的数据库

create database test;

-- 查看当前用户权限内的所有数据库

show databases;

+----------+

|Database |

|information schema |

|test |

|performance schema |

|Sys|

-- 使用创建好的 test 数据库

use test;

-- 删除创建好的 test 数据库

drop database test;

-- 1。使用 test 数据库

use test;

-- 2. 创建部门信息表

create table dept(

depid char(3)，

depname varchar(20),

peoplecount int

);

-- 查看当前数据库所有的表

show tables;

--查看表结构

describe test.dept;

-- 将 dept 表名改为 department

alter table dept rename department;

-- 修改字段名 depid为 depno

alter table dept change depid depno char(3);

-- 修改 depid 的字段类型为 varchar(5)

alter table dept modify depid varchar(5);

-- 在 dept 表中添加新字段 city

alter table dept add city varchar(10);

-- 修改字段 city 的排列位置

alter table dept modify city varchar(10) after depid;

-- 删除字段 city

alter table dept drop city;

-- 删除 dpet 表

drop table dept;

create table dept (

-- 单字段主键

depid char(3) ,primary key,

depname varchar(20),

peoplecount int

);

create table dept(

depid char(3)，

depname varchar(20),

peoplecount int,

多字段联合主键

primary key(depname,depid)

);

-- 删除表中的主键约束

alter table dept drop primary key;

create table dept(

depid char(3) primary key,

--创建表中的唯一约束

depname varchar(20)

uniquerpeoplecount int

-- 删除表中的唯一约束

alter table dept drop index depname;

create table example(

e_id int primary key auto increment，-- 创建表中的自动增长约束

name varchar(4),

math int default 0,

minmax float

);

- 删除表中的自动增长约束

alter table example modify e id int;

create table dept(

depid char(3) primary keyr

depname varchar(20),

peoplecount int not nul1,-- 创建表中的非空约束

);

-- 删除表中的非空约束

alter table dept modify peoplecount int;

create table dept(

depid char(3) primary keyr

depname varchar(20),

peoplecount int default 0 -- 创建表中的默认约束

);

-- 删除表中的默认约束

alter table dept modify peoplecount int;

3.DML

-- 指定字段名添加

insert into dept(depid,depname,peoplecount)values('p10’，’人力部’，,15)，(‘p20’,’研发部’,50);

-- 不指定字段名添加

insert into dept values

('p10’,’人力部’,15)，(‘p20',’研发部’,50);

-- 查看默认安全路径

show variables like ‘%secure%'

-- 为 Monthly Indicator 表导入外部 txt 文件

load data infile ‘c:/ProgramData/MySQL/MySQL Server 8.0/Uploads/xx.txt'

into table Monthly Indicator

fields terminated by ‘\ t’

[ignore l lines];

-- 检查导入内容 Monthly Indicator

select * from Monthly Indicator;

-- 检查导入数据总行数 Monthly Indicator

select count(x) from Monthly Indicator;

-- 检查表结构

desc Monthly Indicator;

--将部门 ID为 p01的信息单独保存到 department 表中

create table department as

select depid,depname,peoplecount

from dept

where depid='p01';

-- 将 dept 表中编号为 p11 的部门名称修改为后勤部

update dept set depname='后勤部 where depid='p11';

-- 删除 p20 部门的员工记录

delete from dept where depid=p20;

-- 删除所有的员工记录

delete from dept;

truncate dept;

4.DQL

4.1单表查询

-- 单独使用 select

select 1;

-- 在表中使用 select

select 1 from emp;

-- 单独使用 select

select 1+1

-- 在表中使用 select

select 1+1 from emp;

-- 单独使用 select，表达式 1+1=3 不成立，返回0

select 1+1=3

-- 表达式 1+1=2 成立，返回 (真值 )1

select 1+1=2 from emp;

-- 查询 emp 表中员工及其工资

select empno,ename,sal from emp;

-- 查询 emp 表的所有字段

select * from emp;

-- 查询 emp 表中的部门

select distinct deptno from emp;

-- 查询 emp 表中不同的部门有哪些职位

select distinct deptno,job from emp;

- 查询每位员工调整后的薪资(基本工资 +1000)

-- 使用 as 设置别名

select *,sal+1000 as 调薪 from emp;

-- 查询每位员工调整后的薪资 (基本工资 +1000)

- 使用空格设置别名

select *,sal+1000 ’调 薪’ from emp;

-- emp 表中的领导岗位员工

select * from emp where job='manager';

-- 查询 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;

-- 查询基本工资大于或等于 2000 且小于或等于 3000 的员工信息

select * from emp

where sal>=2000 and sal<=3000;

-- 写法等价，返回相同的结果

select * from emp where sal between 2000 and 3000;

-- 找出 emp 表中的最高领导人 (没有更高的上级员工)

select * from emp where mgr is null;

-- 空值与任何值进行运算，都只能返回空值

select * from emp where mgr = null;

-- 查询姓名不以 a 开头的员工信息

select * from emp where ename not like 'a%';

-- 查询姓名中包含 a 的员工信息

Select * from emp where ename like ‘%a%’;

- 查询姓名中第二个字符为 a 的员工信息

select * from emp where ename like ‘_a%’;

-- 查询部门 10 的员工信息并按 sal 降序显示

select *

from emp where deptno = 10 order by sal desc;

-- 查询所有员工信息并按 deptno 升序、sal 降序显示

select * from emp order by deptno,sal desc;

-- 查询基本工资最高的前 5 位员工

select * from emp order by sal desc limit 5;

-- 查询基本工资从高到低顺序第 6 行~第 10 行的员工

select * from emp order by sal desc limit 5,5;

- 根据职位字母顺序排序 emp 表

select * from emp order by job;

-- 只输出上述结果的前 4 行

select * from emp order by job limit 0,4;

聚合函数

-- 查询 emp 表中员工的最高工资、最低工资、平均工资及工资总和

select max(sal) 最高工资,min(sal) 最低工资,avg(sal) 平均工资，sum(sal) 工资总和 from emp;

-- 返回 emp 表的员工总数

select count(x) 员工总数 from emp;- 本质也是统计全表的记录数，与 * 效果一致

select count(1) 员工总数 from emp;

-- 返回 emp 表的部门总数

Select count(distinct deptno) 部门总数from emp;

-- 统计有奖金的员工人数

-- 忽略了 null，但是计算了 0

select count(comm) from emp;

-- 从业务逻辑上来讲，奖金金额为 0 的员工不应该视为有奖金的员工

select count(comm) from emp where comm!=0;

-- 查询公司内部不同奖金档位的获得人数

select comm,count(*) from emp group by comm;

-- 查询各部门不同职位的平均工资

select deptno,job,avg(sal) as 平均工资from emp group by deptno，job;

-- 查询各部门 clerk 的平均工资-

-- 用 having 子句筛选

select deptno,job,avg(sal) 平均工资 from emp group by deptno,job having job='clerk';

-- 用 where 子句筛选

select deptno,job,avg(sal) 平均工资 from emp where job= clerk' group by deptno,job;

-- 查询平均工资大于 2000 的部门

select deptno,avg(sal) 平均工资from emp group by deptno

having avg(sal)>2000;

4.2函数

-- 返回 -20的绝对值

select abs(-20);

-- 返回 -20 的平方根，负数没有平方根所以返回空值

Select sqrt(-20);

-- 查询 emp 表中收入最高的员工的工资是平均工资的多少倍，保留 2 位小数

Select round(max(sal)/avg(sal)，2) 倍数 from emp;

-- 将三个字符串合并为一个长字符串

select concat('CDA'，'数据，分析);

-- 参数中包含空值，返回 null

select concat('CDA',null，，’分析’);

-- 参数中包含空白值，不影响结果

select concat('CDA',’’,’分析’);

- 从字符串第 4 位开始，截取长度为 2个字符的字符串

select substring('CDA 数据分析，4,2);

-- 从字符串第 4 位开始，截取到字符串结束

select substring('CDA 数据分析，4);

- 将emp 表中 job 字段中的 manager 替换为 director

select job,replace(job，’manager','director') from emp;

-- 获取日期中的年月日 (日期时间、字符串或者数值均可被识别 )

select year('2020-01-30') 年份,month('20200130') 月份day(20200130)日;

按照日月年的格式返回日期

select date format('20-01-30 12:00:00'，%d-%m-%y') 日月年;

-- 计算 2020年1月1日2个月后的日期

select date add("2020-01-01",interval 2 month);

-- 计算员工的工龄 (雇佣日期与当前日期的差 )

select ename,floor(datediff(curdate(),hiredate)/365) as工龄 from emp;

-- 查询各部门员工姓名

select deptno,qroup_concat(ename order by ename separator/) 员工姓名列表 from emp group by deptno;

-- 查询公司所有部门编号

select group_concat(distinct deptno order by deptno) 部门

from emp;

-- 查询每位员工的实发工资 (基本工资 + 奖金)

select ename,sal+ifnull(comm，0) 实发工资

from emp;

查询员工的工资级别，3000 及以上为高，1500 及以下为低，其余为中

-- 使用 if 函数

select ename,sal,

if(sal>=3000，'高,if(sal<=1500，低,中')) 工资级别

from emp;-- 使用 case 逻辑表达式

select ename,sal,

case when sal>=3000 then r高

when sal<=1500 then 低 else中 end 工资级别

from emp;

-- 聚合函数，得到公司所有员工的平均工资

select avg(sal) 平均工资 from emp;

- 开窗函数，查询每位员工与公司所有员工的平均工资之间的情况

select *,avg(sal) over() 平均工资 from emp;

-- 聚合函数，查询各部门的平均工资

select deptno,avq(sal) from emp group by deptno;

- 开窗函数，查询每位员工与所属部门平均工资之间的情况

select *,avg(sal) over(partition by deptno) 平均工资from

emp;

-- 按入职日期查询各部门的累计工资

select *,sum(sal) over(partition by deptno order by hiredate) 累计工资 from emp;

查询各部门员工的工资排名

select *，

row number() over(order by sal desc) 排名1,

dense rank() over(order by sal desc) 排名2，

rank() over(order by sal desc) 排名 3

from emp;

4.3多表查询

--union

select *from t1

union

select *from t2;

-- union all，其中(a，12) 出现了2次

select * from t1

union all

select * from t2;

- 查询 manager 的姓名、所属部门名称和入职日期

-- 使用 on 声明连接条件

select ename,dname,job,hiredate

from emp

inner join dept on emp.deptno=dept.deptno

where job='manager';

-- 使用 using 声明连接条件

select ename,dname,job,hiredate

from emp

inner join dept using (deptno)

where job='manager';

查询每位员工的 ename、dname、sal

-- 使用 on 声明连接条件

select ename,dname,sal from emp

left join dept on emp.deptno=dept.deptno;

-- 使用 using 声明连接条件

select ename,dname,sal

from emp

left join dept using(deptno);

查询各地区的员工数量--

-- 使用 on 声明连接条件

select loc,count(empno) 员工数

from emp right join dept on emp.deptno=dept.deptno

group by loc;

-- 使用 using 声明连接条件

select loc,count(empno) 员工数

from emp

right join dept using (deptno)

group by loc;

-- 对表 t1 和表 t2 进行全连接

select * from tl left join t2 on t1.公共字段 1=t2。公共字段2

union

select * from tl right join t2 on t1.公共字段1=t2.公共字段2;

--查询所有员工的姓名及直属领导的姓名

select 员工表 .ename 员工姓名 ，领导表 .ename 领导姓名

from emp 员工表 left join emp 领导表 on 员工表 .mgr= 领导表empno;

查询入职日期早于直属领导的员工的姓名及所属部门

select 员工表 .empno，员工表 .ename,dname

from emp 员工表

left join emp 领导表 on 员工表 .mgr= 领导表 .empno

left join dept on 员工表 .deptno=dept.deptno

where 员工表 .hiredate< 领导表 .hiredate;

-- 使用 from 子句

select * from t1,t2;

-- 使用 cross join

select * from t1 cross join t2;

-- 表 t1和表 t2 中，均包含 4 条记录，最后返回 4*4，即 16 条记录

根据分组逻辑创建表--

create table salgrade(grade int,losal int,hisal int);

insert into salgrade values('低收入人群,700,1500)， ('中等收入人群，,1501,3000)， ('高收入人群，,3001,9999);

使用 on 声明不等值的连接条件

select empno,ename,sal,grade

from emp left join salgrade

on sal between losal and hisal;

-- 使用 where 声明不等值的连接条件

select empno,ename,sal,grade

from emp,salgrade

where sal between losal and hisal;

4.4子查询

-- 查询各部门员工人数占比

-- 查询整体员工数量

select count(*) from emp;

将整体员工数量作为子查询来查询各部门员工人数占比

select deptno,count(x) 员工人数

count(*)/(select count(x) from emp) 员工人数占比

from emp group by deptno;

-- 查询各部门最高工资

select deptno,max(sal) from emp group by deptno;

将各部门最高工资作为子查询，找出所有部门内部员工工资等于最高工资的员工

select empno,ename,sal,emp.deptno

from emp

left join (select deptno,max(sal) as最高工资from emp group by deptno) as t

on emp.deptno=t.deptno

where sal= 最高工资 ;

--where 本身只是查找所有工资等于最大值的记录，返回记录顺序是随机的如果需要有序返回，可以使用 order by 子句

-- 也可以直接使用开窗函数对工资进行排名，查询排名为 1的员工

select empno,ename,sal,deptno

from(select *,dense rank() over(partition by deptno order by sal desc) 工资排名 from emp) t

where 工资排名 =1;

-- 查询公司所有员工的平均工资

select avg(sal) from emp;

-- 使用比较操作字符将员工工资与子查询返回的平均工资进行比较

select * from emp where sal>(select avg(sal) from emp);

-- 查询 smith 的部门和职位

select deptno,job from emp where ename='smith';

使用比较操作字符进行子查询-

select empno,ename,job,deptno

from emp

where (deptno,job)=(select deptno,job from emp where ename='smith') and ename<>'smith';

查询各部门最高工资的员工

select empno,ename,sal,deptno

from emp

where (deptno,sal) in (select deptno,max(sal) from emp group by deptno);

查询哪些人不是普通员工，即领导的工号

select distinct mgr from emp where mgr is not null;

上述代码的查询结果如图3-103所示

将第一步作为子查询来筛选数据，具体整合后的代码如下

-- 将领导的工号作为子查询来筛选数据

select empno,ename,sal

from emp

where empno not in (select distinct mgr from emp where mgr is not null);

-- 查询公司部门情况，若公司有 20 的部门，则返回该部门的员工情况

select *

from emp

where exists (select *from dept where deptno=20) and deptno =20;

--exists 后接的子查询并不对数据进行筛选，只决定外查询执不执行-

select *

from emp

where exists (select * from dept where deptno=20);

- 查询 30 号部门所有员工的基本工资

select sal from emp where deptno=30;

-- 将 30 号部门所有员工的基本工资作为子查询对记录进行筛选

Select *

from emp

where sal>any(select sal from emp where deptno=30) and deptno<>30;

--查询基本工资高于 30 号部门所有员工的员工信息

--将any 改为 all

select *

from emp

where sal>all(select sal from emp where deptno=30);

5.视图

5.1创建视图

-- 创建一个名为 job_minsal_view，用来查看不同职位的最低工资及从事该工作的员工姓名和职位的视图

create view job_minsal_view as

(select ename,t.job，最低工资 from(select job,min(sal) 最低工资 from emp group by job) t

left join emp on t.job=emp.job where sal= 最低工资);

5.2修改视图

-- 修改视图 job_minsal_view，为了避免重名情况增加员工编码作为查询结果

alter view job minsal view as

select empno,ename,t.job，最低工资

from

(select job,min(sal) 最低工资 from emp group by job) t

left join emp on t.job= emp.job

where sal= 最低工资;

5.3删除视图

删除视图 job_minsal_view

drop view job_minsal_view;