MySQL SQL100道基础练习题

1、客户端连接
mysql -uroot -h172.17.0.1 -P3306 -p123456
2、SQL分类
DDL，数据定义语言，用于创库表等。
DML，数据操作语言，用于增删改等。
DQL，数据查询语言，用于数据查询等。
DCL，数据控制语言，用于创建用户，权限控制等。
3、查询所有数据库
show databases;
4、查询当前数据库
select database();
5、创建一个itcast数据库, 使用数据库默认的字符集。
create database if not exists newdb;
6、创建一个itheima数据库，并且指定字符集。
create database if not exists newdb1 default charset utf8mb4;
7、删除itcast数据库。
drop database newdb1;
8、切换数据库。
use newdb;
9、查询当前数据库所有表。
show tables;
10、查看指定表结构。
desc score;
11、查询指定表的建表语句。
show create table score;
12、创建表结构
create table 表名(
    列名 类型 约束 comment '描述',
    ......
    列名 类型 约束 comment '描述'
);
13、设计一张员工信息表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 '入职时间'
) comment '员工表';
14、为newtable表增加一个新的字段”昵称”为nickname，类型为varchar(20)新增字段，修改数据类型。
alter table newtable add nickname varchar(20);
15、将newtable表的nickname字段修改为username，类型为varchar(30)。
alter table newtable change nickname username varchar(30);
16、将newtable表的字段username删除。
alter table newtable drop username;
17、将newtable表的表名修改为 employee。
alter table newtable rename employee;
18、删除表。
drop table employee;
19、清空表的数据。用两个方法。
delete from newtable;
truncate table newtable;
20、给employee表所有的字段添加数据。
insert into newtable(id, emp_id, name, gender, age, card_id, entrydate)
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');
21、修改id为1的数据，将name修改为itheima。
update newtable set name = 'itheima' where id = 1;
22、修改id为1的数据, 将name修改为小昭, gender修改为 女。
update newtable set name = '小昭',gender = '女' where id = 1;
23、将所有的员工入职日期修改为 2008-01-01。
update newtable set entrydate = '2008-01-01';
24、删除gender为女的员工。
delete from newtable where gender = '女';
25、删除所有员工。
delete from newtable;
truncate table newtable;
26、查询指定字段 name, workaddress, age并返回。
select name,workaddress,age from newtable;
27、查询返回所有字段。
select * from newtable;
28、查询所有员工的工作地址,起别名。
select workaddress as '工作地址' from newtable;
29、查询公司员工的上班地址有哪些(不要重复)。
select distinct workaddress from newtable;
30、查询年龄等于30的员工信息.
select * from newtable where age <= 30;
31、查询年龄小于 20 的员工信息。
select * from newtable where age < 20;
32、查询年龄小于等于 20 的员工信息
select * from newtable where age <= 20;
33、查询没有身份证号的员工信息
select * from newtable where card_id is null;
34、查询有身份证号的员工信息
select * from newtable where card_id is not null;
35、查询年龄不等于30的员工信息
select * from newtable where age != 30;
36、查询年龄在15岁(包含) 到 20岁(包含)之间的员工信息
select * from newtable where age >= 15 and age <= 20;
select * from newtable where age between 15 and 20;
37、查询性别为 女 且年龄小于 25岁的员工信息
select * from newtable where gender = '女' and age < 25;
38、查询年龄等于18 或 20 或 40 的员工信息
select * from newtable where age = 18 or age = 20 or age =40;
select * from newtable where age in (18,20,40);
39、查询姓名为两个字的员工信息
select * from newtable where name like '__';
40、查询身份证号最后一位是X的员工信息
select * from newtable where card_id like '%x';
41、统计该企业员工数量
select count(*) from newtable;
42、统计该企业员工的平均年龄
select avg(age) from newtable;
43、统计该企业员工的最大年龄
select max(age) from newtable;
44、统计该企业员工的最小年龄
select min(age) from newtable;
45、统计西安地区员工的年龄之和
select sum(age) from newtable where workaddress = '西安';
46、根据性别分组 , 统计男性员工 和 女性员工的数量
select  gender,count(*) from newtable group by gender;
47、根据性别分组 , 统计男性员工 和 女性员工的平均年龄
select gender,avg(age) from newtable group by gender;
48、查询年龄小于45的员工 , 并根据工作地址分组 , 获取员工数量大于等于3的工作地址
select workaddress from newtable where age < 45 group by workaddress having count(workaddress) > 3;
49、统计各个工作地址上班的男性及女性员工的数量
select workaddress,gender,count(*) from newtable group by workaddress, gender;
50、根据年龄对公司的员工进行升序排序
select * from newtable order by age;
51、根据入职时间, 对员工进行降序排序
select * from newtable order by entrydate desc;
52、根据年龄对公司的员工进行升序排序 , 年龄相同 , 再按照入职时间进行降序排序
select * from newtable order by age asc ,entrydate desc ;
53、查询第1页员工数据, 每页展示10条记录
select * from newtable limit 0,10;
54、查询第2页员工数据, 每页展示10条记录 --------> (页码-1)*页展示记录数
select * from newtable limit 10,10;
55、查询年龄为20,21,22,23岁的员工信息。
select * from newtable where age in (20,21,22,23);
select * from newtable where age = 20 or age = 21 or age = 22 or age = 23;
56、查询性别为 男 ，并且年龄在 20-40 岁(含)以内的姓名为三个字的员工。
select * from newtable where gender = '男' and age between 20 and 40 and name like '___';
57、统计员工表中, 年龄小于60岁的 , 男性员工和女性员工的人数。
select gender,count(*) from newtable where age < 60 group by gender;
58、查询所有年龄小于等于35岁员工的姓名和年龄，并对查询结果按年龄升序排序，如果年龄相同按
入职时间降序排序。
select name,age from newtable where age <= 35 order by age asc,entrydate desc;
59、查询性别为男，且年龄在20-40 岁(含)以内的前5个员工信息，对查询的结果按年龄升序排序，
年龄相同按入职时间升序排序。
select * from newtable where age between 20 and 40 order by age,entrydate desc limit 5;
60、查询用户。
select * from mysql.user;
61、创建用户itcast, 只能够在当前主机localhost访问, 密码123456;
create user itcast@'localhost' identified by '123456';
62、创建用户heima, 可以在任意主机访问该数据库, 密码123456;
create user heima@'%' identified by '123456';
63、修改用户heima的访问密码为654321。
alter user heima@'%' identified by '654321';
64、删除 itcast@localhost 用户。
drop user itcast@'localhost';
65、查询 'heima'@'%' 用户的权限。
show grants for heima@'%';
66、授予 'heima'@'%' 用户itcast数据库所有表的所有操作权限。(好像只能在root@localhost权限下才能执行成功)
grant all on itcast.* to heima@'%';
67、撤销 'heima'@'%' 用户的itcast数据库的所有权限。(好像只能在root@localhost权限下才能执行成功)
revoke all on itcast.* from heima@'%';
68、字符串拼接，全部转小写，全部转大写，左填充，右填充，去除空格，截取子字符串
select lower(card_id) from newtable where card_id like '%X'; 转小写
select upper(card_id) from newtable where card_id like '%X'; 转大写
select concat(name,age) from newtable where id < 5; 拼接
69、由于业务需求变更，企业员工的工号，统一为8位数，目前不足5位数的全部在前面补0。比如： 1号员
工的工号应该为00000001。
select lpad(emp_id,8,'0') from newtable;
70、向上取整，向下取整，取模，获取随机数，四舍五入
select ceil(10.1);
select floor(10.9);
select mod(11,2);
select rand();
select round(10.59);
71、通过数据库的函数，生成一个六位数的随机验证码。
select substring(rand(),3,6);
72、当前日期，当前时间，当前日期和时间，当前年、月、日，增加指定的时间间隔，获取两个日期相差的天数。
select curdate();
select curtime();
select now();
select year(now());
select month(now());
select day(now());
select datediff(now(),'2024-07-01');
73、查询所有员工的入职天数，并根据入职天数倒序排序。
select datediff(now(),entrydate) as lift from newtable order by lift desc;
74、if,ifnull练习。
select if(card_id,'ok','bu ok') from newtable;
select ifnull('ok','bu ok') from newtable;
75、查询newtable表的员工姓名和工作地址 (北京/上海 ----> 一线城市 , 其他 ----> 二线城市)。
select name,
       case
       when workaddress = '北京' then '一线城市'
       when workaddress = '广东' then '一线城市'
       else '二线城市' end
from newtable;
76、为newtable表的dept_id字段添加外键约束,关联dept表的主键id。
alter table newtable add constraint new_dept foreign key (dept_id) references dept(id) on update cascade on delete cascade ;
77、删除newtable表的外键new_dept。
alter table newtable drop foreign key new_dept;
78、查询每一个员工的姓名,及关联的部门的名称(隐式内连接实现)。
select a.name,b.dept_name from newtable as a,dept as b where a.dept_id = b.id;
79、查询每一个员工的姓名 , 及关联的部门的名称 (显式内连接实现)。
select n.name,d.dept_name from newtable as n join dept as d on n.dept_id = d.id;
80、查询newtable表的所有数据, 和对应的部门信息，由于需求中提到，要查询emp的所有数据，所以是不能内连接查询的，需要考虑使用外连接查询。
select *,d.dept_name from newtable as n left join dept as d on n.dept_id = d.id;
81、查询dept表的所有数据, 和对应的员工信息(右外连接)
select d.*,n.* from dept as d right join newtable n on d.id = n.dept_id;
82、查询员工及其所属领导的名字
select a.name,b.name from newtable as a,newtable as b where a.manager = b.emp_id;
83、查询newtable所有员工及其领导的名字,如果员工没有领导,也需要查询出来。
select a.name,b.name from newtable as a left join newtable as b on a.manager = b.emp_id;
84、将薪资低于5000的员工,和年龄大于50岁的员工全部查询出来.联合查询,多方法.
select * from newtable where gz < 5000
union
select * from newtable where age > 50;
select * from newtable where age > 50 or gz < 5000;
85、查询 "市场部" 的所有员工信息。
select * from newtable where dept_id = (select id from dept where dept_name = '市场部');
86、查询在 "黄蓉" 入职之后的员工信息。
select * from newtable where entrydate > (select entrydate from newtable where name = '黄蓉');
87、查询 "行政部" 和 "市场部" 的所有员工信息
select * from newtable where dept_id in (select id from dept where dept_name in ('市场部','行政部'));
88、查询比 财务部 所有人工资都高的员工信息.
select * from newtable where gz > (select max(gz) from newtable where dept_id =  (select id from dept where dept_name = '财务部'));
select * from newtable where gz > all (select gz from newtable where dept_id = (select id from dept where dept_name = '财务部'));
89、查询比研发部其中任意一人工资高的员工信息
select * from newtable where gz > any (select gz from newtable where dept_id = (select id from dept where dept_name = '研发部'))
90、查询与 "张无忌" 的薪资及直属领导相同的员工信息
select * from newtable where (gz,manager) = (select gz,manager from newtable where name = '张无忌');
91、查询 "黄语焉" , "李嘉欣" 的职位和薪资
select name,(select dept_name from dept where id = dept_id) as '职位',gz from newtable where name in ('黄语焉','李嘉欣');
92、查询入职日期是 "2016-01-01" 之后的员工信息 , 及其部门信息
select *,(select dept_name from dept where id = dept_id) as '部门信息' from newtable where entrydate > '2016-01-01';
93、查询员工的姓名、年龄、部门信息 （隐式内连接）
select name,age,(select dept_name from dept where id = dept_id) as '部门信息' from newtable;
94、查询年龄小于30岁的员工的姓名、年龄、部门信息（显式内连接）
select a.name,a.age,b.dept_name from newtable as a join dept as b on a.dept_id = b.id where age < 30;
95、查询拥有员工的部门ID、部门名称
select dept_id,(select dept_name from dept where id = dept_id) as '部门' from newtable group by dept_id;
96、查询所有年龄大于40岁的员工, 及其归属的部门名称; 如果员工没有分配部门, 也需要展示出
来(外连接)
select a.name,b.dept_name from newtable as a left join dept as b on a.dept_id = b.id where a.age > 40;
97、查询所有员工的工资等级，低于5000，为普通员工，5000-9000为中层，9000以上为核心骨干
select name,
       case
        when gz < 5000 then '普通员工'
        when gz between 5000 and 9000 then '中层'
        else '核心骨干'
end  as '工资等级'
from newtable;
98、查询 "研发部" 所有员工的信息及工资等级
select name,
       case
        when gz < 5000 then '普通员工'
        when gz between 5000 and 9000 then '中层'
        else '核心骨干'
end  as '工资等级'
from newtable where dept_id = (select id from dept where dept_name = '研发部');
99、查询 "研发部" 员工的平均工资
select avg(gz) from newtable where dept_id = (select id from dept where dept_name = '研发部');
100、查询工资比 "灭绝" 高的员工信息。
select * from newtable where gz > (select gz from newtable where name = '灭绝');
101、查询比平均薪资高的员工信息
select * from newtable where gz > (select avg(gz) from newtable);
102、查询低于本部门平均工资的员工信息
select a.* from newtable as a where a.gz < (select avg(b.gz) from newtable as b where b.dept_id = a.dept_id);
103、查询所有的部门信息, 并统计部门的员工人数
select b.*,(select count(*) from newtable as a where a.dept_id = b.id) from dept as b;
104、查询所有员工的就职情况, 展示出员工名称, 年龄, 部门名称
select a.name,a.age,(select b.dept_name from dept as b where b.id = a.dept_id) as '部门名称' from newtable as a;