员工表
| 员工号 | 姓名 | 职位 | 直属领导 | 入职时间 | 薪水 | 提成 | 部门号 |
| 7369 | smith | clerk | 7902 | 1980-12-17 | 800 | 20 | |
| 7499 | allen | salesman | 7698 | 1981-02-20 | 1600 | 300 | 30 |
| 7521 | ward | salesmaz | 7698 | 1981-02-22 | 1250 | 500 | 30 |
| 7566 | jones | manager | 7839 | 1981-04-02 | 2975 | 20 | |
| 7654 | martin | salesman | 7698 | 1981-09-28 | 1250 | 1400 | 30 |
| 7698 | blake | manager | 7839 | 1981-05-01 | 2850 | 30 | |
| 7782 | clark | manager | 7839 | 1981-06-09 | 2450 | 10 | |
| 7788 | scott | analyst | 7566 | 1987-04-19 | 3000 | 20 | |
| 7839 | king | persident | 1981-11-17 | 5000 | 10 | ||
| 7844 | turner | salesman | 7698 | 1981-09-08 | 1500 | 0 | 30 |
| 7876 | adams | clerk | 7788 | 1987-05-23 | 1100 | 20 | |
| 7900 | james | clerk | 7698 | 1981-12-03 | 950 | 30 | |
| 7902 | ford | analyst | 7566 | 1981-12-03 | 3000 | 20 | |
| 7934 | miller | clerk | 7782 | 1982-01-23 | 1300 | 10 |
-- 查看系统中有哪些数据库
show databases;
-- 创建test数据库
create database test;
-- 选择进入数据库
use test;
-- 删除数据库(慎用)
drop database test;
-- 创建数据表
create table diyige(
deptno int,
dname varchar(15),
loc varchar(10)
);
-- 查看当前数据库中有哪些表
show tables ;
-- 查看表结构
desc diyige;
-- 删除数据表(慎用)
drop table diyige;
-- 创建带有约束条件的表(因为两张表中有主外键约束,所以需要先创建主键所在的dept,再创建外键所在的emp)
create table dept(
deptno int primary key ,
dname varchar(15),
loc varchar(10)
);
create table employee(
empno int primary key auto_increment,
ename varchar(15) unique,
job varchar(10) not null default '-',
mgr int,
hiredate date,
sal float default 0,
comm float,
deptno int,
foreign key(deptno) references dept(deptno)
);
desc employee;
-- 修改表名
alter table employee rename emp;
desc emp;
-- 修改字段名
alter table emp change empno empid int;
-- 修改字段类型
alter table emp modify empid int auto_increment;
-- 添加字段
alter table emp add city varchar(10);
-- 修改字段的排列位置：
alter table emp modify city varchar(10) after ename;
-- 删除字段
alter table emp drop city;
-- 插入数据：字段名与字段值的数据类型、个数、顺序必须一一对应
insert into dept(deptno,dname,loc)
values (10,'accounting','new york'),
(20,'research','dallas');
desc dept;
select * from dept;
insert into dept values (30,'sales','chicago'),(40,'operations','boston');
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; -- 检查导入数据总行数
-- 更新数据
update emp set sal=sal+1000 where deptno=10;
set sql_safe_updates=0; -- 设置数据库安全权限
update emp set sal=sal+1000 ;
-- 删除数据
delete from emp where deptno=30;
-- 清空数据
truncate emp;
-- 查询指定列：查询emp表中ename,job,sal
select ename,job,sal from emp;
-- 设置别名：查询每位员工调整后的薪资(基本工资+1000)
select ename,job,sal,sal+1000 as 新薪资 from emp;
-- 练习：查询每位员工的年薪(基本工资*12):empno,ename,年薪
select empid,ename,sal*12 as 年薪 from emp;
-- 查询不重复的数据：查询emp表中有哪些部门
select distinct deptno from emp;
-- 条件查询
-- 查询基本工资大于等于2000小于等于3000的员工信息
select * from emp where sal between 2000 and 3000;
-- 查询10号部门和20号部门中sal低于2000的员工信息
select * from emp where sal < 2000 and deptno in(10,20);
-- 练习：查询salesman的所属部门：姓名,职位,所在部门
select ename,job,deptno from emp where job='salesman';
-- 空值查询
-- 查询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 deptno asc, sal desc;
-- 限制查询结果数量
-- 查询基本工资最高的前5位员工
select * from emp order by sal desc limit 5;
-- 查询基本工资第6到10名的员工
select * from emp order by sal desc limit 5,5;
-- 练习：查询最后入职的5位员工
select * from emp order by hiredate desc limit 5;
-- 聚合运算
-- 查询emp表中员工总数、最高工资、最低工资、平均工资及工资总和
select count(*),max(sal),min(sal),avg(sal),sum(sal) from emp;
-- 分组查询
-- 查询各部门的平均工资
select deptno,group_concat(ename),avg(sal) from emp group by deptno;
-- 查询各部门不同职位的平均工资
select deptno,job,avg(sal) from emp group by deptno,job;
-- 练习：查询各部门的员工数
select deptno,count(*) from emp group by deptno;
-- 练习：查询各部门不同职位的人数
select deptno,job,count(*) from emp group by deptno,job;
-- 分组后筛选
-- 查询各部门clerk的平均工资
select deptno,avg(sal) from emp where job='clerk' group by deptno;
-- 查询平均工资大于2000的部门
select deptno,avg(sal) from emp group by deptno 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;
-- 内连接diyige
select * from t1 inner join t2 on t1.key1=t2.key2;
-- 左连接
select * from t1 left join t2 on t1.key1=t2.key2;
-- 右连接
select * from t1 right join t2 on t1.key1=t2.key2;
select * from t1 left join t2 on t1.key1=t2.key2
union
select * from t1 right join t2 on t1.key1=t2.key2;
select * from t1 left join t2 on t1.key1=t2.key2
where key2 is null;
-- 合并查询
-- union去重
select * from t1 union select * from t2 ;
-- union all不去重
select * from t1 union all select * from t2 ;
-- 多表查询练习
select * from emp;
select * from dept;
select * from salgrade;
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);
-- 查询每位员工的ename,dname,sal
select emp.ename,dept.dname,emp.sal from emp left join dept
on emp.deptno=dept.deptno
-- 查询各地区的员工数（统计每个地区，没有员工计为0）
select loc,count(ename) from emp right join dept
on emp.deptno=dept.deptno
group by dept.loc;
-- 查询manager的姓名、所属部门名称和入职日期：ename,dname,job,hiredate(内连接/笛卡尔积连接)
select ename,dname,job,hiredate from emp join dept
on emp.deptno=dept.deptno
where job='manager';
select * from emp,dept
where job='manager' and emp.deptno=dept.deptno;
select emp2.ename,emp2.job,emp2.hiredate from emp emp1 join emp emp2
on emp1.mgr=emp2.empid;
-- 查询每位员工的工资等级；empno,ename,sal,grade(不等值连接)
select empid,ename,sal,grade from emp,salgrade
where sal between losal and hisal;
-- 查询各工资等级的员工数
select grade,count(empid) from emp,salgrade
where sal between losal and hisal
group by grade;
-- 查询所有员工姓名及其直属领导姓名（自连接：通过别名，将同一张表视为多张表）
select emp1.ename,emp2.ename from emp emp1 left join emp emp2
on emp1.mgr=emp2.empid;
-- 查询入职日期早于其直属领导的员工：empno,ename,dname
select emp1.empid,dept.dname from emp emp1 left join emp emp2
on emp1.mgr=emp2.empid
left join dept
on emp1.deptno=dept.deptno
where emp1.hiredate<emp2.hiredate;
-- 子查询
-- 标量子查询：
-- 查询基本工资高于公司平均工资的员工信息
select * from emp where sal>(select avg(sal) from emp);
select avg(sal) from emp;
-- 练习：查询和allen同一个领导的员工：empno,ename,job,mgr
select empid,ename,job,mgr from emp
where mgr =(select mgr from emp where ename='allen'
-- 行子查询
-- 查询和smith同部门同职位的员工：empno,ename,job,deptno
select empid,ename,job,deptno from emp
where (deptno,job)=(select deptno,job from emp where ename='smith') and ename!='smith';
-- 列子查询：
-- 查询普通员工的工资等级：empno,ename,sal,grade
select empid,ename,sal,grade from emp left join salgrade on sal between losal and hisal
where empid not in(select distinct mgr from emp where mgr is not null);
-- 练习：查询员工数不少于5人的部门的所有员工：empno,ename,deptno
select empid,ename,deptno from emp
where deptno in(select deptno from emp group by deptno having count(empid)>=5);
-- 查询基本工资高于30号部门任意员工的员工信息
select * from emp
where sal>any(select sal from emp where deptno=30) and deptno!=30;
-- 查询基本工资高于30号部门所有员工的员工信息
select * from emp
where sal>all(select sal from emp where deptno=30);
-- from子查询
-- 查询各部门最高工资的员工：empno,ename,sal,deptno
select empid,ename,sal,deptno from emp
where (deptno,sal)=any(select deptno,max(sal) from emp group by deptno) ;
select empid,ename,sal,emp.deptno from emp
left join (select deptno,max(sal) as m from emp group by deptno) as t
on emp.deptno=t.deptno where sal=m;
-- 字符串函数
-- 练习：将每位员工的姓名首字母转换为大写
select concat(upper(left(ename,1)),lower(mid(ename,2))) from emp;
-- 日期函数
-- 练习：查询每位员工的工龄（年）：ename,hiredate,工龄
select ename,hiredate,floor(datediff(curdate(),hiredate)/365) as 工龄 from emp;
select unix_timestamp();
-- 分组合并函数
-- 练习：查询各部门的员工姓名
select deptno,group_concat(ename) from emp group by deptno;
-- 逻辑函数
-- if函数：查询每位员工的工资级别：3000及以上为高，1500-3000为中，1500及以下为低
select *,(if(sal>=3000,'高',if(sal>1500,'中','低'))) as jibie from emp;
-- 逻辑表达式 case when ...then... else ... end
select *,case when sal=3000 then'高'
when sal>1500 then '中'
else '低'
end
as jibie from emp;
扫一扫
645
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
