数据库练习题（3）

原创已于 2024-11-17 18:29:25 修改 · 1k 阅读

26 ·

CC 4.0 BY-SA版权

文章标签：

#数据库

于 2024-11-17 11:44:02 首次发布

设计一张员工信息表newtable，

要求如下：

1、编号（纯数字） 2、员工工号（字符串类型，长度不超过10位） 3、员工姓名（字符串类型，长度不超过10位） 4、性别（男、女，存储一个汉字） 5、年龄（正常人年龄，不可能存储负数） 6、身份证号（二代身份证号码均为18位，身份证中有X这样的字符） 7、入职时间（取值年月日即可）

create table newtable

(

id int primary key auto_increment comment '编号', emp_id varchar(10) comment '员工工号',

name varchar(10) comment '员工姓名',

gender char(1) comment '员工性别',

age tinyint unsigned comment '员工年龄',

card_id char(18) comment '身份证号码',

entrydate date comment '入职时间' ) DEFAULT CHARSET=utf8 comment '员工表';

1、为newtable表增加一个新的字段”昵称”为nickname，类型为varchar(20)新增字段，修改数据类型。 alter table newtable add nickname varchar(20);

2、将newtable表的nickname字段修改为username，类型为varchar(30)。 alter table newtable change nickname username varchar(30);

3、将newtable表的字段username删除。 alter table newtable drop username;

4、将newtable表的表名修改为 employee。 alter table newtable rename employee;

5、删除表。 drop table employee;

6、给newtable表所有的字段添加数据。

insert into newtable(id, emp_id, name, gender, age, card_id, entrydate,address)

values (1,'00001','张三丰','男',63,'12345678987456321X','1987-10-10','chongqin'),
(2,'00002','张翠山','男',48,'123256789874563214','2011-1-19','shanghai'),
(3,'00003','张无忌','男',26,null,'2020-1-19','nanjing'),
(4,'00004','赵敏','女',23,'123256789874563211','2012-1-19','xian'),
(5,'00005','郭襄','女',19,'123256789874563212','2013-1-19','haha'),
(6,'00006','韦一笑','男',51,'123256789874563213','2014-1-19','aaa'),
(7,'00007','殷天正','男',47,'123256789874563215','2015-1-19','bbbbb'),
(8,'00008','玄冥一','男',68,'123256789874563216','2016-1-19','cccc'),
(9,'00009','周芷若','女',22,'123256789874563217','2017-1-19','ddddd'),
(10,'000010','灭绝','女',47,'123256789874563219','2018-1-19','eeeee');

7、修改id为1的数据，将name修改为itcaoqb。

update newtable set name='itcaoqb' where id = 1;

8、修改id为1的数据, 将name修改为小昭, gender修改为女。

update newtable set name='小昭',gender='女' where id = 1;

9、将所有的员工入职日期修改为 2008-01-01。

update newtable set entrydate='2008-01-01';

10、删除gender为女的员工。

delete from newtable where gender = '女';

11、查询指定字段 name, workaddress, age并返回。

select name,address,age from newtable;

12、查询返回所有字段。

select * from newtable;

13、查询所有员工的工作地址,起别名。

select address as workadress from newtable;

14、查询公司员工的上班地址有哪些(不要重复)。

select distinct address from newtable;

15、查询年龄等于 26 的员工信息.

select * from newtable where age =26 ;

16、查询年龄小于 51 的员工信息。

select * from newtable where age < 51;

17、查询年龄小于等于 51 的员工信息

select * from newtable where age <= 51;

18、查询没有身份证号的员工信息

select * from newtable where card_id is null;

19、查询有身份证号的员工信息

select * from newtable where card_id is not null;

20、查询年龄不等于30的员工信息

select * from newtable where age != 30;

select * from newtable where age <> 30;

21、查询年龄在20岁(包含) 到 50岁(包含)之间的员工信息

select * from newtable where age between 20 and 50;

22、查询性别为男且年龄小于 35岁的员工信息

select * from newtable where gender = '男' and age < 35;

23、查询年龄等于26 或 36 或 47 的员工信息

select * from newtable where age in(26,36,47);

24、查询姓名为两个字的员工信息

select * from newtable where name like ' ';

25、查询身份证号最后一位是6的员工信息

select * from newtable where card_id like '%6';

26、统计该企业员工数量

select count(*) as new_num

from newtable;

27、统计该企业员工的平均年龄

select avg(age) as new_avg from newtable;

28、统计该企业员工的最大年龄

select max(age) as new_max from newtable;

29、统计该企业员工的最小年龄

select min(age) as new_min from newtable;

30、统计西安地区员工的年龄之和

select sum(age) as new_sum

from newtable

where address = '西安';

31、根据性别分组 , 统计男性员工和女性员工的数量

select gender, count(*) from newtable group by gender ;

32、根据性别分组 , 统计男性员工和女性员工的平均年龄

select gender, avg(age) from newtable group by gender ;

33、查询年龄小于45的员工 , 并根据工作地址分组 , 获取员工数量大于等于3的工作地址

select address, count(*) address_count from newtable where age < 45 group by address having address_count >= 3;

34、统计各个工作地址上班的男性及女性员工的数量

select address, gender, count(*) '数量' from newtable group by gender , address ;

35、根据年龄对公司的员工进行升序排序

select * from newtable order by age asc;

36、根据入职时间, 对员工进行降序排序

select * from newtable order by entrydate desc;

37、根据年龄对公司的员工进行升序排序 , 年龄相同 , 再按照入职时间进行降序排序

select * from newtable order by age asc , entrydate desc;

38、查询第1页员工数据, 每页展示10条记录

select * from newtable limit 0,10;

39、查询第2页员工数据, 每页展示10条记录 --------> (页码-1)*页展示记录数

select * from newtable limit 10,10;

40、查询年龄为20,21,22,23岁的员工信息。

select * from newtable where age in(20,21,22,23);

41、查询性别为男，并且年龄在 20-40 岁(含)以内的姓名为三个字的员工。

select * from newtable where gender = '男' and ( age between 20 and 40 ) and name like '___';

42、统计员工表中, 年龄小于60岁的 , 男性员工和女性员工的人数。

select gender, count(*) from newtable where age < 60 group by gender;

43、查询所有年龄小于等于35岁员工的姓名和年龄，并对查询结果按年龄升序排序，如果年龄相同按入职时间降序排序。

select name , age from newtable where age <= 35 order by age asc , entrydate desc;

44、查询性别为男，且年龄在20-40 岁(含)以内的前5个员工信息，对查询的结果按年龄升序排序，年龄相同按入职时间升序排序。

select * from newtable where gender = '男' and age between 20 and 40 order by age asc ,entrydate asc limit 5 ;

61、为newtable表的dept_id字段添加外键约束,关联dept表的主键id。

create table dept

(

id int auto_increment comment 'ID' primary key,

dept_name varchar(50) not null comment '部门名称'

)DEFAULT CHARSET=utf8 comment '部门表';

INSERT INTO dept (id, dept_name)

VALUES

(1, '研发部'),

(2, '市场部'),

(3, '财务部'),

(4, '销售部'),

(5, '总经办'),

(6, '人事部');

62、删除newtable表的外键new_dept。

alter table newtable drop foreign key new_dept;

63、查询每一个员工的姓名,及关联的部门的名称(隐式内连接实现)。

select a.name,b.dept_name from newtable as a,dept as b where a.id = b.id;

64、查询每一个员工的姓名 , 及关联的部门的名称 (显式内连接实现)。

select n.name,d.dept_name from newtable as n join dept as d on n.id = d.id;

65、查询newtable表的所有数据, 和对应的部门信息，由于需求中提到，要查询emp的所有数据，所以是不能内连接查询的，需要考虑使用外连接查询。

select d.*,n.* from dept as d right join newtable as n on d.id = n.id;

66、查询dept表的所有数据, 和对应的员工信息(右外连接)

select d.*,n.* from dept as d right join newtable n on d.id = n.id;

67、查询员工及其所属领导的名字

select a.name,b.dept_leader from newtable as a,salary as b where a.emp_id = b.emp_id;

68、查询newtable所有员工及其领导的名字,如果员工没有领导,也需要查询出来。

select a.name,b.dept_leader from newtable as a,salary as b where a.emp_id = b.emp_id;

69、将薪资低于5000的员工,和年龄大于50岁的员工全部查询出来.联合查询,多方法.

select * from newtable where age > 50

union

select * from salary where salary < 5000;

70、查询 "市场部" 的所有员工信息。

select * from newtable where id in (select id from dept where dept_name = '市场部');

71、查询在 "赵敏" 入职之后的员工信息。

select * from newtable where entrydate > (select entrydate from newtable where name = '赵敏');

72、查询 "行政部" 和 "研发部" 的所有员工信息

select * from newtable where id in (select id from dept where dept_name in ('市场部','研发部'));

73、查询比财务部所有人工资都高的员工信息.

select * from salary where salary > (select max(salary) from newtable where id in (select id from dept where dept_name = '财务部'));

74、查询比研发部其中任意一人工资高的员工信息

select * from newtable where salary > any (select gz from newtable where id = (select id from dept where dept_name = '研发部'))

75、查询与 "张无忌" 的薪资及直属领导相同的员工信息

select * from newtable where (salary,manager) = (select salary,manager from newtable where name = '张无忌');

76、查询 "小昭" , "赵敏" 的职位和薪资

select name,(select dept_name from dept where id = id) as '职位' from newtable where name in ('小昭','赵敏');

77、查询入职日期是 "2016-01-01" 之后的员工信息 , 及其部门信息

select *,(select dept_name from dept where id = id) as '部门信息' from newtable where entrydate > '2016-01-01';

78、查询员工的姓名、年龄、部门信息（隐式内连接）

select n.name,n.age,d.dept_name from newtable as n join dept as d on n.id = d.id;

79、查询年龄小于30岁的员工的姓名、年龄、部门信息（显式内连接）

select a.name,a.age,b.dept_name from newtable as a join dept as b on a.id = b.id where age < 30;

80、查询所有员工的部门ID、部门名称

select * from dept;

81、查询所有年龄大于40岁的员工, 及其归属的部门名称; 如果员工没有分配部门, 也需要展示出来(外连接)

select a.name,b.dept_name from newtable as a left join dept as b on a.id = b.id where a.age > 40;

84、查询 "研发部" 员工的平均工资

select avg(salary) from salary where emp_id = (select id from dept where dept_name = '研发部');

85、查询工资比 "小昭" 高的员工信息。

select * from newtable where id in
(select emp_id from salary where salary > 10000);

86、查询比平均薪资高的员工信息

select avg(salary) from salary;

select * from newtable where id in
(select emp_id from salary where salary > 18600);

87、查询低于本部门平均工资的员工信息