基础练习2

设计一张员工信息表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 '入职时间'

 address varchar(255)

)DEFAULT CHARSET=utf8 comment '员工表';

 insert into newtable(id, emp_id, name, gender, age, card_id, entrydate,address)                        values (1,'00001','张三丰','男',63,'12345678987456321X','1987-10-10','重庆'),
 (2,'00002','张翠山','男',48,'123256789874563214','2011-1-19','上海'),
 (3,'00003','张无忌','男',26,null,'2020-1-19','西安'),
 (4,'00004','赵敏','女',23,'123256789874563211','2012-1-19','深圳'),
 (5,'00005','郭襄','女',19,'123256789874563212','2013-1-19','广东'),
 (6,'00006','韦一笑','男',51,'123256789874563213','2014-1-19','广西'),
 (7,'00007','殷天正','男',47,'123256789874563215','2015-1-19','成都'),
 (8,'00008','玄冥一','男',68,'123256789874563216','2016-1-19','贵州'),
 (9,'00009','周芷若','女',22,'123256789874563217','2017-1-19','北京'),
 (10,'000010','灭绝','女',47,'123256789874563219','2018-1-19','辽宁');

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表所有的字段添加数据。

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, address, age并返回。

 select name ,address,age from newtable;

12、查询返回所有字段。

 select * from newtable;

或 select id,emp_id,name,gender,age,card_id,entrydate,address from newtable;

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

select address as workaddress from newtable;

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

 select distinct address as workaddress from newtable;

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

select * from newtable where age = 30;

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

select * from newtable where age <20;

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

select * from newtable where age<=20;

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、查询年龄在15岁(包含) 到 20岁(包含)之间的员工信息

select * from newtable where age between 15 and 20;

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

select * from newtable where gender = '女' and age <=25;

23、查询年龄等于18 或 20 或 40 的员工信息

select * from newtable where age=18 or age=20 or age=40;

或 select * from newtable where age in (18,20,40);

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

 select * from newtable where name like '__';

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

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

26、统计该企业员工数量

select count(*) as num from newtable;

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

select avg(age) as avg_age from newtable;

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

select max(age) as max_age from newtable;

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

select min(age) as min_age from newtable;

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

select sum(age) as sum_age from newtable where address = '西安';

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

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

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

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

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

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

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

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

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 gender,age,name 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 desc;

54、由于业务需求变更，企业员工的工号，统一为8位数，目前不足5位数的全部在前面补 0。 比如： 1号员工的工号应该为00000001。

 update newtable set emp_id = '00000001' where id = 1;

 update newtable set emp_id = '00000002' where id = 2;

 update newtable set emp_id = '00000003' where id = 3;

 update newtable set emp_id = '00000004' where id = 4;

 update newtable set emp_id = '00000005' where id = 5;

 update newtable set emp_id = '00000006' where id = 6;

 update newtable set emp_id = '00000007' where id = 7;

 update newtable set emp_id = '00000008' where id = 8;

 update newtable set emp_id = '00000009' where id = 9;

 update newtable set emp_id = '00000010' where id = 10;

58、查询所有员工的入职天数，并根据入职天数倒序排序。

 select name,datediff(curdate(),entrydate) as day from newtable order by day desc;

60、查询newtable表的员工姓名和工作地址 。

select name,address from newtable;

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

create table dept4(

id int auto_increment comment 'ID' primary key,

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

)comment '部门表';

INSERT INTO dept4 (id, dept4_name) VALUES

(1, '研发部'), (2, '市场部'),(3, '财务部'), (4, '销售部'), (5, '总经办'), (6, '人事部');

62、删除newtable表的外键new_dept。

alter table newtable drop foreign key new_dept4;

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

select newtable.name , dept4.dept4_name from newtable,dept4 where newtable.id=dept4.id ;

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

 select n.name,d.dept4_name from newtable as n join dept4 as d on n.id = d.id;

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

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

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

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

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

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

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

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

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

select * from salary  where  salary<5000 union select  * from newtable where age >50;

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

select * from newtable where id in(select id from dept4 where dept4_name = '市场部');

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

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

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

select * from newtable where id in (select id from dept4 where dept4_name in ('市场部','行政部'));

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

select * from newtable where id in(select emp_id from salary) > (select max(salary) from salary where dept4_id =(select id from dept4 where dept4_name = '财务部'));

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

select * from newtable where id in(select emp_id from salary) > any (select salary from salary where dept4_id = (select id from dept4 where dept4_name = '研发部'));

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

 select * from newtable where id in (select emp_id from salary) = (select emp_id from newtable where name = '张无 忌');

76、查询 "黄语焉" , "李嘉欣" 的职位和薪资

 select dept4_name,salary from newtable ,dept4 where newtable.name in ('黄语焉','李嘉欣');

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

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

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

 select name,age,(select dept4_name from dept4 where id = newtable.id) as '部门信息' from newtable;

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

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

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

 select id,(select dept4_name from dept4 where id = newtable.id) as '部门' from newtable group by newtable.id;

81、查询所有年龄大于40岁的员工, 及其归属的部门名称; 如果员工没有分配部门, 也需要

展示出来(外连接)

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

82、查询所有员工的工资等级，低于5000，为普通员工，5000-9000为中层，9000以上为

核心骨干。

select name, salary <= '5000' as '普通', salary between 5001 and 9000 as '中等',salary > '9000' as '核心' from newtable,salary;

83、查询 "研发部" 所有员工的信息及工资等级

 select n.*,salary < 5000 as '普通',salary between 5000 and 9000 as '中等',salary > 9000 as '核心' from newtable as n,salary,dept4 as d where n.id=d.id and d.dept4_name='研发部';

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

select avg(salary) from salary where dept4_id = (select id from dept4 where dept4_name = '研发部');

85、查询工资比 "灭绝" 高的员工信息。

select * from newtable where id > (select salary from salary where name = '灭绝');

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

select * from newtable where salary >(select avg(salary) from newtable );

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

select * from newtable where salary <(select avg(salary) from newtable );

88、查询所有的部门信息, 并统计部门的员工人数

select b.*,(select count(*) from newtable as a where a.id = b.id) from dept4 as b;                     

89、查询所有员工的就职情况, 展示出员工名称, 年龄, 部门名称

select a.name,a.age,(select b.dept4_name from dept4 as b where b.id = a.id) as '部门名称' from newtable as a;