# 练习题
# 1)创建user表
create table `user` (
uid int,
uname VARCHAR(20),
`password` VARCHAR(20),
birthday date
);
SELECT * from `user`
# 2) 向用户表中新增一个email字段
alter table `user` add email VARCHAR(20)
desc `user` # 查看表结构
# 3) 向用户表最前面添加字段status
alter table `user` add `status` INT FIRST
# 4)向用户表password字段后面添加sage,INT类型
alter table `user` add sage INT after `password`
# 5)修改sage字段的名字为age
alter table `user` change sage age int
# 6)修改password字段的长度为50
alter table `user` change `password` `password` VARCHAR(49)
alter table `user` modify `password` VARCHAR(50)
# 7)修改status到Email之后的位置
alter table `user` modify `status` int after email
alter table `user` modify `status` `status` int after email
# 8)删除字段status
alter table `user` drop `status`
#9)修改表名
rename table `user` to Myuser
# 10)清空数据库表
truncate table myuser
delete from myuser
# 11)销毁数据库表user
drop table myuser
-- 1.查询工资提升100元后超过2k的所有员工信息
select * from emp WHERE sal + 100>2000;
select * from emp where sal >1900 # 相对第一个查询速度会更快
-- 2.查询工资在1000-2000之间的10号部门的最高工资的员工信息
# 方式一,升序排序进行取第一个
select * from emp where (sal BETWEEN 1000 and 2000) and deptno = '10号' ORDER BY sal desc limit 0,1
# 方式二:
SELECT * from emp where sal = (
select max(sal) from emp where (sal BETWEEN 1000 and 2000) and deptno = '10'
)
-- 3.将所有工资低于2000的员工工资提升5%
update emp set sal = sal + sal*0.5 where sal < 2000
-- 4.查询名字包含s的并且在20号部门的员工信息
select * from emp where name like '%s%' and deptno = '10'
-- 5.查询工资大于1000的10号部门的前5条记录
select * FROM emp where sal >1000 and deptno = 10 limit 0,5
-- 6.将奖金小于500的员工奖金加100元
update emp set comm =IFNULL(comm,100) where comm <500 or comm is null
-- 7.大于20号部门的平均工资的20号部门的员工信息8.
GROUP BY deptno having sal > avg(sal)
select * from emp where sal > (
SELECT avg(sal) FROM emp WHERE deptno = 20
) and deptno = 20
8.查询大于所在部门平均工资的员工信息
# ①每个部门的平均工资
select deptno,avg(sal) from emp GROUP BY deptno
# ②每个部门的员工信息,员工的sal>部门平均
SELECT * from emp,(select deptno,avg(sal) as sasl from emp GROUP BY deptno) as
avgsal where (emp.deptno = avgsal.deptno) and sal > sasl
9.查询工资大于20号部门员工工资的所有员工信息
SELECT * from emp where sal >(
SELECT max(sal) from emp where deptno =20 #20号部门最高工资
)
10.薪水大于2000的员工所在部门的信息
SELECT * from dept,(select * from emp where sal > 2000) e where dept.deptno = e.deptno