-- 查看系统中有哪些数据库
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;