基础练习2-CSDN博客

本文链接：https://blog.csdn.net/2201_75666673/article/details/143837474

建表

create table newtable(
    id int primary key auto_increment comment '编号',
    emp_id varchar(10) comment '员工工号',
    dept_id varchar(10) comment'职位部门编号',
    name varchar(20) 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, dept_id, name, gender, age, card_id, entrydate,address)
values (1,'00001',1,'张三丰','男',63,'12345678987456321X','1987-10-10','chongqin'),
       (2,'00002',4,'张翠山','男',48,'123256789874563214','2011-1-19','shanghai'),
       (3,'00003',5,'张无忌','男',26,null,'2020-1-19','nanjing'),
       (4,'00004',4,'赵敏','女',23,'123256789874563211','2012-1-19','xian'),
       (5,'00005',1,'郭襄','女',19,'123256789874563212','2013-1-19','haha'),
       (6,'00006',2,'韦一笑','男',51,'123256789874563213','2014-1-19','xian'),
       (7,'00007',3,'殷天正','男',47,'123256789874563215','2015-1-19','chonqin'),
       (8,'00008',3,'玄冥一','男',68,'123256789874563216','2016-1-19','cccc'),
       (9,'00009',6,'周芷若','女',22,'123256789874563217','2017-1-19','beijin'),
       (10,'000010',2,'灭绝','女',47,'123256789874563219','2018-1-19','xian');

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' where id!=0;

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 workaddress from newtable;

14、查询公司员工的上班地址有哪些（不要重复）。
select distinct address 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;

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 in (18,20,40);

24、查询姓名为两个字的员工信息
select * from newtable where name like '__';

25、查询身份证号最后一位是X的员工信息
select * from newtable where card_id like '%X';

26、统计该企业员工数量

select count(name) as total from newtable ；

27、统计该企业员工的平均年龄
select avg(age) as avg_age from newtable;

28、统计该企业员工的最大年龄
select max(age) from newtable;

29、统计该企业员工的最小年龄
select min(age) from newtable;

30、统计西安地区员工的年龄之和
select sum(age) from newtable where address='xian';

31、根据性别分组，统计男性员工和女性员工的数量
select gender ,count(name) from newtable group by gender;
select gender ,group_concat(name),count(name) from newtable group by gender;

32、根据性别分组，统计男性员工和女性员工的平均年龄
select gender, avg(age) from newtable group by gender;

33、查询年龄小于45的员工，并根据工作地址分组，获取员工数量大于等于3的工作地址
select address from newtable where age < 45 group by address having count(address) >=3;

34、统计各个工作地址上班的男性及女性员工的数量
select address, count(name) from newtable group by address;

select address ,group_concat(gender),count(name) from newtable group by address;

35、根据年龄对公司的员工进行升序排序
select group_concat(name),age from newtable group by age;

36、根据入职时间,对员工进行降序排序
select group_concat(name),entrydate from newtable group by entrydate desc;

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

select name,age from newtable order by age,entrydate desc;

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(name) from newtable where age<60 group by gender;

43、查询所有年龄小于等于35岁员工的姓名和年龄，并对查询结果按年龄升序排序，如果年龄相同按入职时间降序排序。
select name,age from newtable where age <=35 order by age,entrydate desc;

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

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

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

update newtable set emp_id=concat('0',emp_id) where length(emp_id) < 8;

58、查询所有员工的入职天数，并根据入职天数倒序排序。
select group_concat(name) as name, (date(now())-entrydate)/24 as date from newtable group by entrydateydate ;

60、查询newtable表的员工姓名和工作地址
select name ,address from newtable;

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

create table dept1(
id int primary key auto_increment comment 'ID',
emp_name varchar(50) not null comment '部门名称',
constraint newtable_dept1 foreign key (id) references newtable(id)
)DEFAULT CHARSET=utf8 comment '部门表';

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

62、删除newtable表的外键newtable_dept1。
alter table dept1 drop foreign key newtable_dept1;

63、查询每一个员工的姓名,及关联的部门的名称(隐式内连接实现)。
select name,dept_name from newtable,dept1where newtable.dept_id=dept1.id;

64、查询每一个员工的姓名 , 及关联的部门的名称 (显式内连接实现)。
select name,dept_name from newtable inner join dept1 on newtable.dept_id=dept1.id;

65、查询newtable表的所有数据, 和对应的部门信息，由于需求中提到，
要查询dept的所有数据，所以是不能内连接查询的，需要考虑使用外连接查询。
select * from newtable left join dept1 on newtable.dept_id=dept1.id;

66、查询dept表的所有数据, 和对应的员工信息(右外连接)
select * from newtable right join dept1 on newtable.dept_id=dept1.id;

67、查询员工及其所属领导的名字
select name,manger from newtable ,dept1 where newtable.dept_id=dept1.id;

68、查询newtable所有员工及其领导的名字,如果员工没有领导,也需要查询出来。
select name,manger from newtable ,dept1 where newtable.dept_id=dept1.id or manger is null;

69、将薪资低于5000的员工,和年龄大于50岁的员工全部查询出来.联合查询,多方法.
select * from newtable where age>50 and salary < 5000;

70、查询 "市场部" 的所有员工信息。
select * from newtable where dept_id=
(select id from dept1 where dept_name='市场部');

71、查询在 "黄蓉" 入职之后的员工信息。
select * from newtable where entrydate <(select entrydate from newtable where name='黄蓉');

72、查询 "行政部" 和 "市场部" 的所有员工信息
select * from newtable where dept_id in
(select id from dept1 where dept_name in ('行政部','市场部'));

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

select * from newtable where salary > all (select salary from newtable where dept_id=(select id from dept1 where dept_name='财务部'));

74、查询比研发部其中任意一人工资高的员工信息
select * from newtable where salary > any (select salary from newtable where dept_id=(select id from dept1 where dept_name='研发部'));

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

select * from newtable,dept1 where (salary,manger)= (select salary ,manger from newtable ,dept1 where newtable.name='张无忌' and newtable.dept_id=dept1.id);

76、查询 "黄语焉" , "李嘉欣" 的职位和薪资
select dept_name,salary from newtable ,dept1 where newtable.name in ('黄语焉','李嘉欣');

77、查询入职日期是 "2016-01-01" 之后的员工信息 , 及其部门信息
select * from newtable where entrydate >'2016-01-01';

78、查询员工的姓名、年龄、部门信息（隐式内连接）
select name,age,dept_name from newtable,dept1 where newtable.dept_id = dept1.id;

79、查询年龄小于30岁的员工的姓名、年龄、部门信息（显式内连接）
select name,age,dept_name from newtable inner join dept1 on newtable.dept_id = dept1.id;

80、查询拥有员工的部门ID、部门名称
select id ,dept_name from dept1 where id in (select dept_id from newtable );

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

select name,dept_name from newtable inner join dept1 on newtable.age > 40 and newtable.dept_id=dept1.id union select name,dept_name from newtable inner join dept1 on newttable.age >40 and newtable.dept_id is null;

82、查询所有员工的工资等级，低于5000，为普通员工，5000-9000为中层，9000以上为
核心骨干。
select name, salary <= '5000' as '普通', salary between 5001 and 9000 as '中等',salary > '9000' as '核心' from newtable;

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

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

84、查询 "研发部" 员工的平均工资
select avg(salary) from newtable where dept_id=(select id from dept1 where dept_name='研发部');
85、查询工资比 "灭绝" 高的员工信息。
select * from newtable where salary > (select salary from newtable where name='灭绝');