mysql面试题2

MySQL实训_02

1. 以自己的姓名创建一个数据库。
   create database chenxiulong;
2. 在此数据库下创建如下3表，数据类型，宽度，是否为空等，根据实际情况自己定义。
   A． 雇员表（employee）：
   雇员编号（empid），
   姓名（name），
   性别（gender），
   职称（title），
   出生日期（birthday），
   所在部门编号（depid）；
   其中雇员编号为主键；
   create table employee( empid int(11) unsigned auto_increment notnull primary key,name varchar(32) not null, gender enum(‘男’,‘女’) notnull, title varchar(32) not null, birthday datetime, depid int(11) unsigned not null);
   B． 部门表（department）：
   部门编号（depid），
   部门名称（depname）；
   其中部门编号为主键。
   create table department(depid int(11) unsigned primary key not null,depname varchar(32) not null);
   C． 工资表（salary）：
   雇员编号（empid），
   基本工资（base_salary），
   职务工资（title_salary），
   扣除（deduction）。
   其中雇员编号为主键。
   create table salary( empid int(11) unsigned not null primary key,base_salary int(11) not null, title_salary int(11), deduction int(11));
3. 修改表结构，在部门表中添加一个”部门简介”字段。
   alter table department add depintroduction varchar(32);
4. 在上面的3个表中各输入若干条记录，内容如下。
   雇员表：
   雇员编号 姓名 性别 职称 出生日期 所在部门编号
   1001 张三 男 高级程师 1975-1-1 111
   1002 李四 女 助工 1985-1-1 111
   1003 王五 男 工程师 1978-11-11 222
   1004 赵六 男 工程师 1979-1-1 222
   insert into employee values(1001,’张三’,’男’,’高级工程师’,’1975-1-1’,111)(1002,‘李四’,‘女’,‘助工’,‘1985-1-1’,111),(1003,‘王五’,‘男’,‘工程师’,‘1978-11-11’,222),(1004,‘赵六’,‘男’,‘工程师’,‘1979-1-1’,222);
   部门表：
   部门编号 部门名称 部门简介
   111 生产部 Null
   222 销售部 Null
   333 人事部 Null
   insert into department values(111,‘生产部’,null), (222,‘销售部’,null),(333,‘人事部’,null);
   工资表：
   雇员编号 基本工资 职务工资 扣除
   1001 2200 1100 200
   1002 1200 200 100
   1003 1900 700 200
   1004 1950 700 150

insert into salary values(1001,2200,1100,200),(1002,1200,200,100),(1003,1900,700,200),(1004,1950,700,150);

5. 将李四的职称改为“工程师”，并将她的基本工资改为5700元，职务工资为600。
   update salary set title = ‘工程师’,base_salary = 5700,title_salary = 600 where empid =(select empid from employee where name = ‘李四’);

6. 查询出每个雇员的雇员编号，姓名，职称，所在部门，实发工资和应发工资。
   select e.empid as 雇员编号,name as 职称,depname as 所在部门,(base_salary+title_salary-deduction) as 实发工资,(base_salary+title_salary) as 应发工资 from employee e,department d,salary s where e.empid =s.empid and e.depid = d.depid;

7. 查询姓“张”且年龄小于40岁的员工的记录。
   select empid as 雇员编号,name as 姓名,gender as 性别,datediff(‘2021-4-26’,birthday) / 365 as 年龄 from employee where datediff(‘2021-4-26’,birthday) / 365 < 40 and name like ‘张%’;

8. 查询销售部所有雇员的雇员编号，姓名，职称，部门名称，实发工资。

9. 统计各类职称的人数。

10. 统计各部门的部门名称，实发工资总和，平均工资。

create database a;
use a;

create table employee(
empid varchar(10) primary key not null,
name varchar(10),
gender varchar(10),
title varchar(20),
birthday date,
depid varchar(10));

create table department(
depid varchar(10) primary key not null,
depname varchar(20));

create table salary(
empid varchar(10) primary key not null,
base_salary decimal(8,2),
title_salary decimal(8,2),
deduction int);

#修改表结构，在部门表中添加一个”部门简介”字段
alter table employee add 部门简介 varchar(100);

insert into employee values(‘1001’,‘张三’,‘男’,‘高级工程师’,‘1980-01-01’,‘1111’,null);
insert into employee values(‘1002’,‘李四’,‘女’,‘助理工程师’,‘1980-11-01’,‘1111’,null);
insert into employee values(‘1003’,‘王五’,‘男’,‘工程师’,‘1980-01-21’,‘2222’,null);
insert into employee values(‘1004’,‘赵六’,‘男’,‘工程师’,‘1980-01-11’,‘2222’,null);

insert into department(depid,depname) values
(‘1111’,‘生产部’),
(‘2222’,‘销售部’),
(‘3333’,‘人事部’);

insert into salary values
(‘1001’,3200,1200,200),
(‘1002’,4200,1100,100),
(‘1003’,5200,2200,200),
(‘1004’,2000,1500,150);

#5将李四的职称改为“工程师”，并将她的基本工资改为5700元，职务工资为1600。
update employee set title=“工程师” where name=“李四”;

update salary set base_salary=5700,title_salary=600
where empid=(select empid from employee where name=“李四”);

#6查询出每个雇员的雇员编号，姓名，职称，所在部门，实发工资和应发工资。
#涉及employee表和salary表、department表
select employee.empid,name,title,depname,(base_salary+title_salary-deduction) as 实发工资,
(base_salary+title_salary) as 应发工资 from employee
left join salary on employee.empid=salary.empid
left join department on department.depid=employee.depid;

#7查询姓“张”且年龄小于40岁的员工的记录。
select * from employee where name like"张%" and (year(curdate())-year(birthday))<40;

#8查询销售部所有雇员的雇员编号，姓名，职称，部门名称，实发工资。
#涉及employee表和salary表、department表
select employee.empid,name,title,depname,
(base_salary+title_salary-deduction) as 实发工资 from employee
left join salary on employee.empid=salary.empid
left join department on department.depid=employee.depid where depname=“销售部”;

#9统计出各类职称的人数。
#按职称分组
select title,count(*)as 人数 from employee group by title;

#10.统计各部门的部门名称，实发工资总和，平均工资。
#涉及三张表及group by分组
select depname,sum(base_salary+title_salary-deduction)as 实发工资,
avg(base_salary+title_salary-deduction) as 平均工资 from employee
left join salary on employee.empid=salary.empid
left join department on department.depid=employee.depid group by depname;

select * from employee;
select * from salary;
select * from department;