mysql练习

一

表准备

create table emp(
	id int primary key auto_increment,
	name varchar(32) not null,
	sex enum('male','female') not null default 'male',
	age  int(3) unsigned not null default 23,
	hire_date date not null,
	post varchar(50),
	post_comment varchar(100),
	salary double(15,2),
	office int,    #一个部门一个屋子
	depart_id int
);
"""插入记录"""
#三个部门：教学，销售，运营
insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values
('song','male',32,'19990911','teacher',10000000,401,1),
('zhang','male',27,'20100708','teacher',80000,401,1),
('san','female',42,'20070401','teacher',740000,401,1),
('lisi','female',29,'20150316','teacher',14000,401,1),
('wangwu','male',22,'20000311','teacher',67000,401,1),
('哈哈','male',29,'20170311','sale',68000.94,402,2),
('喜喜','male',20,'20081111','sale',780000.74,402,2),
('蛇姐','female',23,'20140111','sale',17000.09,402,2),
('二二','female',21,'20011212','sale',670000.44,402,2),
('喜之郎','female',24,'20061012','sale',190000.38,402,2),
('傻逼','male',21,'20170311','operation',48000,403,3),
('喜哈','male',20,'20051111','operation',78000,403,3),
('裸李','female',19,'20040112','operation',79000,403,3),
('三八','male',20,'20011201','operation',60000,403,3),
('程咬金','male',21,'20060112','operation',195000,403,3),
('罗丽莉','female',18,'20001201','operation',650000,403,3),
('青霞','female',17,'20040112','operation',1950500,403,3);

练习题

"""#1. 查询id大于等于3小于等于6的数据"""
select id,name from emp where id>=3 and id<=6;
select id,name from emp where id between 3 and 6;
"""#2.查询薪资是80000或者60000或者79000的数据"""
select * from emp where salary=80000 or salary=60000 or salary=79000;
select * from emp where salary in(80000,60000,79000);
select * from emp where salary not in(80000,60000,79000);

"""3.查询员工姓名中包含o的员工的姓名和薪资"""
"""
#模糊查询 like 
		% 匹配任意多个字符
		_匹配任意单个字符
"""
select name,salary from emp where name like '%o%';

"""4.查询员工姓名是由四个字符组成的，姓名和薪资"""
select name,salary from emp where name like '____';#四个下划线
select name,salary from emp where char_length(name)=4;

"""5. 查询id小于3或者大于6的数据"""
select * from emp where id<3 or id>6;
select * from emp where id  not between 3 and 6;

"""6. 查询薪资不在20000，18000，17000的数据"""
select * from emp where salary not in (2000,17000,18000);
"""7.查询岗位描述为空的员工的姓名和岗位名"""#针对NULL不能用=号，要用is
select name,post from emp where post_comment is NULL;

"""1.获取每个部门的最高薪资"""
select post, max(salary) from emp group by post;
select post as '部门' ,max(salary) as '薪资' from emp group by post;
#as 可以给字段起别名，也可以直接忽略不写，但是不推荐，因为省略的话语义不明确，容易混乱
"""2. 获取每个部门的最低薪资"""
select post ,min(salary) from emp group by  post;
"""3.获取每个部门的平均薪资"""
select post ,avg(salary) from emp group by post;
"""4.获取每个部门的工资总和"""
select post,sum(salary) from emp group by post;
"""5.获取每个部门的人数"""
select post,couont(salry) from emp group by post;
select post,count(id) from emp group by post;
select post,count(age) from emp group by post;
select post,count(post_comment) from emp group by post;#报错，null不可以计数
"""6.查询分组之后的部门名称和每个部门下的所有员工姓名 
group_concat"""
#group_concat不单单可以支持获取分组之后的其他字段，还支持拼接操作
select post ,group_concat(name) from emp group by post;
select post ,group_concat(name,'_DSB') from emp group by post;
select post ,group_concat(name,':',salary) from emp group by post;
#concat，不分组的时候用
select concat('NAME:',name) ,concat('SALARY:',salary) from emp;

"""统计各部门年龄在20岁以上的员工的平均薪资"""
	1.先求所有年龄大于20岁的员工
	select * from emp where age>20;
	2. 在对结果进行分组
	select * from emp where age>20 group by post;
	或者，两步并一步
	select post,avg(salary) from emp where age>20 group by post;

"""1.统计各部门年龄在20岁以上的员工的平均工资，并且保留平均工资大于20000的部门"""
select post,avg(salary) from emp 
	where age>20 
	group by post 
	having avg(salary) >20000;

"""统计各部门在20岁以上的员工的平均工资，并且保留平均工资大于1000的部门，然后对工资降序排"""
select post,avg(salary) from emp 
	where age>20 
	group by post 
	having avg(salary) >1000
	order by avg(salary) desc;

查询名字以j开头，以n或者y结尾的所有数据
select * from emp where name regexp '^j.*(n|y)$';

二

表准备

create table dep(
	id int,
	name varchar(20)
);
create table emp(
	id int primary key auto_increment,
	name varchar(20),
	sex enum('male','female') not null default 'male',
	age int,
	dep_id int
);

"""插入数据"""
insert into dep values(200,'技术'),(201,'人力资源'),(202,'销售'),(203,'运营');

insert  into emp(name,sex,age,dep_id) values
('jason','male',18,200),
('egon','female',23,201),
('kevin','male',22,202),
('lisi','male',44,203),
('herry','female',19,204);

练习题

#查询部门是技术或者人力资源的员工信息
1. 先获取部门的id号
select id from dep where name="技术" or name="人力资源";
2. 再去员工表里筛选对应的
select name from emp where dep_id  in (200,201);
3.合并
select * from emp where dep_id in (select id from dep where name="技术" or name="人力资源";);

"""
查询平均年龄再25岁以上的部门名称

"""
#1.联表操作
1. 先拿到部门和员工表，拼接后的结果
2. 分析语义，得出要分组
select * from emp inner join dep on emp.dep_id=dep.id
group by dep.name 
having avg(age)>25
;
涉及到多表操作的时候，一定要加上前缀
#2.子查询
select name from dep where id in 
(select dep_id from emp group by dep_id
having avg(age) >25);

三

表准备

#学生表
create table `student` (
`id` int unsigned primary key auto_increment,
`name` char(32) not null unique,
`sex` enum('男', '女') not null,
`city` char(32) not null,
`description` text,
`birthday` date not null default '1995-1-1',
`money` float(7, 2) default 0,
`only_child` boolean
) charset=utf8;
insert into `student`
(`name`, `sex`, `city`, `description`, `birthday`, `money`, `only_child`)
values
('郭德纲', '男', '北京', '班长', '1997/10/1', rand() * 100, True),
('陈乔恩', '女', '上海', NULL, '1995/3/2', rand() * 100, True),
('赵丽颖', '女', '北京', '班花, 不骄傲', '1995/4/4', rand() * 100, False),
('王宝强', '男', '重庆', '超爱吃火锅', '1998/10/5', rand() * 100, False),
('赵雅芝', '女', '重庆', '全宇宙三好学生', '1996/7/9', rand() * 100, True),
('张学友', '男', '上海', '奥林匹克总冠军！', '1993/5/2', rand() * 100, False),
('陈意涵', '女', '上海', NULL, '1994/8/30', rand() * 100, True),
('赵本山', '男', '南京', '副班长', '1995/6/1', rand() * 100, True),
('张柏芝', '女', '上海', NULL, '1997/2/28', rand() * 100, False),
('吴亦凡', '男', '南京', '大碗宽面要不要？', '1995/6/1', rand() * 100, True),
('鹿晗', '男', '北京', NULL, '1993/5/28', rand() * 100, True),
('关晓彤', '女', '北京', NULL, '1995/7/12', rand() * 100, True),
('周杰伦', '男', '台北', '小伙人才啊', '1998/3/28', rand() * 100, False),
('马云', '男', '南京', '一个字：贼有钱', '1990/4/1', rand() * 100, False),
('马化腾', '男', '上海', '马云死对头', '1990/11/28', rand() * 100, False);

#成绩表
create table score (
`id` int unsigned primary key auto_increment,
`math` float not null default 0,
`english` float not null default 0
) charset=utf8;
insert into score (`math`, `english`)
values
(49, 71), (62, 66.7), (44, 86), (77.5, 74), (41, 75),
(82, 59.5), (64.5, 85), (62, 98), (44, 36), (67, 56),
(81, 90), (78, 70), (83, 66), (40, 90), (90, 90);

练习题

#1. 查找每个城市，数学最高的学生。
select student.id,name,sex,birthday,math from student join score on
student.id=score.id where math in (select max(math) from student join score
on student.id=score.id group by city);
/* 方法二 */
create view v_math as select city,max(math) from student join score on
student.id=score.id group by city;
select student.id,name,sex,city,birthday,math from student join score on
student.id=score.id where math in (select `max(math)` from v_max_math);

# 2.计算男生和女生的数学和英语平均分
select sex as '性别',round(avg(math),2) as '数学平均',round(avg(english),2) as
'英语平均分' from student join score on student.id=score.id group by sex;

#3. 计算最大学生和最小学生差多少天。
select datediff(max(birthday),min(birthday)) as '最大相差天数' from student;

四

表准备

"部门表【部门标号deptno，部门名称dname,部门地址loc】"
create table dept(
    deptno int primary key ,
   	dname varchar(14),
  	loc varchar(13)
   );
 insert into dept values(10,'accounting','NEW YORK'),(20,'research','DALLAS');
 insert into dept values(30,'sales','CHICAGO');
 insert into dept values(40,'operations','BOSTON');

"员工表emp 【员工编号empno,员工姓名ename,员工工作job,员工直属领导mgr,入职时间hiredate,工资sal,奖金comm,部门编号deptno】"
create table emp(
empno int primary key,
ename varchar(10),
job varchar(9),
mgr int,
hiredate DATE,
sal double,
comm double,
deptno int
);
insert into emp values(7369,'smith','clerk',7902,'1980-12-17',800,NULL,20);
insert into emp values(7499,'allen','saleman',7698,'1981-02-20',1600,300,30);
insert into emp values(7521,'ward','saleman',7698,'1981-02-22',1250,500,30);
insert into emp values(7566,'jones','manager',7839,'1981-04-02',2957,NULL,20);
insert into emp values(7654,'martin','saleman',76989,'1981-09-28',1250,1400,30);
insert into emp values(7698,'blake','manager',7839,'1981-05-01',2580,NULL,30);
insert into emp values(7782,'clark','manager',7839,'1981-06-11',2450,NULL,10);
insert into emp values(7788,'scott','alalyst',7566,'1987-07-03',3000,NULL,20);
insert into emp values(7839,'king','president',NULL,'1981-11-17',5000,NULL,10);
insert into emp values(7844,'turner','saleman',7698,'1981-09-08',1500,0,30);
insert into emp values(7876,'adams','clerk',7788,'1987-07-13',1100,NULL,20);
insert into emp values(7900,'james','clerk',7698,'1981-12-03',3000,NULL,20);
insert into emp values(7902,'fork','analyst',7566,'1981-12-03',980,NULL,30);
insert into emp values(7934,'miller','clerk',7782,'1981-01-23',1300,NULL,10);

"工资等级表salgrade【grade等级，lowsal最低工资，hisal最高工资】"
 create table salgrade(
                       grade int,
                       lowsal double,
                        hisal double
                        );

insert into salgrade values(1,700,1200);
insert into salgrade values(2,1201,1400),(3,1401,2000),(4,2001,3000),(5,3001,9999);

1. 找出名字以a,b,s开始的员工

"方式一"：正则表达式
select * from emp where ename regexp '^[abs]';

"方式二"：left(字段，个数)获取指定字段中左边的指定个数的子串
select * from emp where left(ename,1) in('a','b','s');

2. 返回员工的详细信息并按照姓名排序

"升序"
select * from emp order by ename;
"降序"
select * from emp order by ename desc;

3. 返回员工的详细信息按照工作降序工资升序排序

"先按照工作降序排序，工作一样的情况下 按照工资升序排序"
select * from emp order by job desc,sal;

4. 返回拥有员工的部门名，部门号

select dept.deptno,dept.dname from emp  inner join dept  on emp.deptno=dept.deptno group by deptno;

5. 工资高于smith的员工信息

"分析：
	1. 先获取smith的工资，
	2. 在查询整个表，根据上一个表进行判断
采用里子查询的操作

"
select * from emp where sal > (select sal from emp where ename='smith');

6. 返回员工和所属经理的姓名

"分析
员工和经理都在一张表，每个员工都有自己对应的经理编号，
也就是要进行子连接查询， --自己表跟自己表连接
表是可以起别名的，为了区分，可以给其中 的一个起别名为 manager，另一个保持本意，就是员工表，
**连接的条件是员工的经理编号  与 经理表的员工id一致**
"
select emp.ename,manager.ename from emp inner join emp as manager on emp.mgr=manager.empno;

7. 返回雇员的雇佣日期早于其经理雇佣日期的员工以及经理的姓名

select emp.ename,manager.ename from emp inner join emp as manager on emp.mgr=manager.empno where emp.hiredate < manager.hiredate;

8. 返回员工姓名及其所在的部门名称

"多表查询
部门名称在部门表
员工名称在员工表
两个表绑定的条件就是 员工的部门编号 与 部门表中的部门编号一致

"
select ename,dname from emp inner join dept on emp.deptno=dept.deptno;

9. 返回从事clerk工作的员工姓名和所在的部门名称

select emp.ename,dept.dname from emp inner join dept on emp.deptno=dept.deptno where emp.job='clerk';

10. 返回部门号及其部门的最低工资

"分析
按照部门进行分组，拿到最低的薪资
" 
select deptno,min(sal) as 最低薪资 from emp group by deptno;

11. 返回销售部(sales)所有员工的姓名

select ename from emp inner join dept on emp.deptno=dept.deptno where dname='sales';

12. 返回与scott从事相同工作的员工

"找到scott从事的工作，然后再以此为条件查询对应的员工的信息"
select * from emp where job=(select job from emp where ename ='scott');

13. 返回员工的详细信息（包括部门名称以及部门地址)

select emp.*,dept.dname,dept.loc from emp inner join dept on emp.deptno=dept.deptno;

14.返回员工工作及其从事工作的最低工资

/*
员工的工作 及其 该工作的最低薪资
以工作进行分组，找到该分组下的最低薪资
*/

select job,min(sal) as 最低薪资 from emp group by job;

15. 返回工资处于第四级别的员工的姓名

"方式一"
select * from emp where sal between (select lowsal from salgrade where grade=4) and  (select hisal from salgrade where grade =4);

"方式二"
select * from emp,salgrade where emp.sal between salgrade.lowsal and salgrade.hisal and salgrade.grade=4;
"方式三"
select * from emp inner join salgrade on emp.sal between salgrade.lowsal and salgrade.hisal where salgrade.grade=4;

16. 返回工资为二等级的职员名字，部门所在地、和二等级的最低工资和最高工资,(难)

select ename,sal,loc,lowsal,hisal from emp,dept,salgrade where salgrade.grade=2 and emp.deptno=dept.deptno and  emp.sal between salgrade.lowsal and salgrade.hisal;

17. 工资等级高于smith的员工信息,(难)

"1.获取smith的工资等级
  select grade from salgrade,emp where ename='smith' and sal between lowsal and hisal;  "
"2.然后再获取所有的员工的薪资等级
  select emp.*,grade from emp,salgrade where sal between lowsal and hisal;
"
"3.再其基础上，添加判断，提取等级大于smith的等级的"


select emp.* ,grade from emp,salgrade where sal between lowsal and hisal and grade > (select grade from
 salgrade,emp where ename='smith' and sal between lowsal and hisal);

五

表准备

 create table employee(
                          eno int primary key auto_increment,
                          ename char(16) not null,
                          eage tinyint unsigned,
                          esex enum('男','女') default '男',
                          ejob varchar(8),
                          ehiredate date,
                          ebonus float(7,2),
                          ebsalary decimal(7,2),
                          deptno int)charset=utf8

insert into employee values
			(1,'李鹏飞',32,'男','经理','2016-11-12',5000,8000,10),
			(2,'王培芬',27,'男','销售员','2018-10-20',2000,1000,10),
			(3,'肖美丽',24,'女','前台','2019-03-21',0,3500,20),
			(4,'王乐乐',30,'女','经理','2017-03-02',0,9000,20),
			(5,'张丽丽',28,'女','行政人员','2019-11-11',0,5000,20),
			(6,'徐华',33,'女','销售员','2019-11-17',500,4000,10),
			(7,'赵晖',40,'男','经理','2016-11-17',0,50000,30),
			(8,'王伟',35,'男','开发工程师','2018-11-28',0,30000,30),
			(9,'钱慧慧',28,'女','开发工程师','2019-04-17',0,25000,30),
			(10,'孙雯彩',29,'女','开发工程师','2017-09-15',0,20000,30);

1. 修改肖美丽的奖金为500

update employee set ebonus=500 where ename='肖美丽';

2. 删除名为孙雯彩的员工

delete from employee where ename='孙雯彩';

3. 查询出部门编号为30的所有员工

 select * from employee where deptno=30;

4. 所有销售员的姓名，编号，和部门编号

 select ename,eno,deptno from employee where ejob='销售员';

5. 找出奖金高于底薪的员工

select * from employee where ebonus>ebsalary;

6. 找出奖金高于工资60%的员工

 select * from employee where ebonus>ebsalary*0.6;

7. 找出部门编号为20中的经理，和部门编号为10中所有销售员的详细资料

 select * from employee where (deptno=20 and ejob='经理') or(deptno=10 and ejob='销售员');

8. 找出部门编号为20中所有经理，部门编号为10中所有销售员，还有既不是经理又不是销售员但是其工资大于或等于20000的所有员工详细资料

select * from employee where (deptno=20 and ejob='经理') or
(deptno=10 and ejob='销售员') or ejob not in ('经理','销售员'
) and ebsalary>=20000;

9. 查询有奖金的员工的职位

select distinct ejob from employee where ebonus>0;
或者
 select distinct ejob from employee where ebonus !=0;

10 查询名字由三个字组成的员工

select * from employee where ename like '___';
或者
select * from employee where  char_length(ename)=3;

11 查询2017年入职的员工信息

 select * from employee where ehiredate like '2017%';
或者
select * from employee where year(ehiredate)=2017;
或者
 select * from employee where ehiredate regexp '2017.*';

12 底薪加奖金的薪资在3000到7000之间的

select * from employee where (ebonus+ebsalary) between 3000 and 7000;