1SQL代码
添加字段
alter table emp add qq char(11) comment 'qq号';
修改字段
alter table emp change qq qq_num char(13) comment 'qq号';
删除字段
alter table emp drop qq_num;
删除表
drop table student;
插入对应数据
insert into emp(username,password,name,gender,phone,create_time,update_time)
values('zhangsan','123456','张三','1','13800000000',now(),now());
-- 插入员工信息记录
-- 向员工表中插入一条完整的员工记录，包含员工的所有字段信息
insert into emp
values(2,'lisi','123456','李四','1','13800000001',1,10000,'1.jpg','2020-10-01',now(),now());
-- 插入指定字段的员工信息记录
-- 向员工表中插入一条员工记录，明确指定要插入的字段，不包含头像字段
insert into emp(id,username,password,name,gender,phone,job,salary,create_time,update_time)
values(3,'wangwu','123456','王五','2','13800000002',2,15000,now(),now());
-- 批量插入多条员工信息记录
-- 向员工表中批量插入两条员工记录，提高数据插入效率
insert into emp(id,username,password,name,gender,phone,job,salary,create_time,update_time)
values(4,'zhaoliu','123456','赵六','1','13800000003',3,20000,now(),now()),
(5,'zhaoliu2','123456','赵七','1','13800000004',3,20000,now(),now());
-- 更新指定员工的姓名信息
-- 根据员工ID更新员工姓名，并更新最后修改时间
update emp set name='张四',update_time =now() where id=1;
-- 批量更新员工入职日期
-- 更新所有员工的入职日期和最后修改时间
update emp set entry_date='2020-10-01',update_time=now();
delete from emp where id=1;
delete from emp;
create table emp(
id int unsigned primary key auto_increment comment 'ID,主键',
username varchar(20) not null unique comment '用户名',
password varchar(32) not null comment '密码',
name varchar(10) not null comment '姓名',
gender tinyint unsigned not null comment '性别, 1:男, 2:女',
phone char(11) not null unique comment '手机号',
job tinyint unsigned comment '职位, 1:班主任,2:讲师,3:学工主管,4:教研主管,5:咨询师',
salary int unsigned comment '薪资',
image varchar(300) comment '头像',
entry_date date comment '入职日期',
create_time datetime comment '创建时间',
update_time datetime comment '修改时间'
) comment '员工表';
-- 准备测试数据
INSERT INTO emp(id, username, password, name, gender, phone, job, salary, image, entry_date, create_time, update_time)
VALUES (1,'shinaian','123456','施耐庵',1,'13309090001',4,15000,'1.jpg','2000-01-01','2023-10-27 16:35:33','2023-10-27 16:35:35'),
(2,'songjiang','123456','宋江',1,'13309090002',2,8600,'2.jpg','2015-01-01','2023-10-27 16:35:33','2023-10-27 16:35:37'),
(3,'lujunyi','123456','卢俊义',1,'13309090003',2,8900,'3.jpg','2008-05-01','2023-10-27 16:35:33','2023-10-27 16:35:39'),
(4,'wuyong','123456','吴用',1,'13309090004',2,9200,'4.jpg','2007-01-01','2023-10-27 16:35:33','2023-10-27 16:35:41'),
(5,'gongsunsheng','123456','公孙胜',1,'13309090005',2,9500,'5.jpg','2012-12-05','2023-10-27 16:35:33','2023-10-27 16:35:43'),
(6,'huosanniang','123456','扈三娘',2,'13309090006',3,6500,'6.jpg','2013-09-05','2023-10-27 16:35:33','2023-10-27 16:35:45'),
(7,'chaijin','123456','柴进',1,'13309090007',1,4700,'7.jpg','2005-08-01','2023-10-27 16:35:33','2023-10-27 16:35:47'),
(8,'likui','123456','李逵',1,'13309090008',1,4800,'8.jpg','2014-11-09','2023-10-27 16:35:33','2023-10-27 16:35:49'),
(9,'wusong','123456','武松',1,'13309090009',1,4900,'9.jpg','2011-03-11','2023-10-27 16:35:33','2023-10-27 16:35:51'),
(10,'lichong','123456','林冲',1,'13309090010',1,5000,'10.jpg','2013-09-05','2023-10-27 16:35:33','2023-10-27 16:35:53'),
(11,'huyanzhuo','123456','呼延灼',1,'13309090011',2,9700,'11.jpg','2007-02-01','2023-10-27 16:35:33','2023-10-27 16:35:55'),
(12,'xiaoliguang','123456','小李广',1,'13309090012',2,10000,'12.jpg','2008-08-18','2023-10-27 16:35:33','2023-10-27 16:35:57'),
(13,'yangzhi','123456','杨志',1,'13309090013',1,5300,'13.jpg','2012-11-01','2023-10-27 16:35:33','2023-10-27 16:35:59'),
(14,'shijin','123456','史进',1,'13309090014',2,10600,'14.jpg','2002-08-01','2023-10-27 16:35:33','2023-10-27 16:36:01'),
(15,'sunerniang','123456','孙二娘',2,'13309090015',2,10900,'15.jpg','2011-05-01','2023-10-27 16:35:33','2023-10-27 16:36:03'),
(16,'luzhishen','123456','鲁智深',1,'13309090016',2,9600,'16.jpg','2010-01-01','2023-10-27 16:35:33','2023-10-27 16:36:05'),
(17,'liying','12345678','李应',1,'13309090017',1,5800,'17.jpg','2015-03-21','2023-10-27 16:35:33','2023-10-27 16:36:07'),
(18,'shiqian','123456','时迁',1,'13309090018',2,10200,'18.jpg','2015-01-01','2023-10-27 16:35:33','2023-10-27 16:36:09'),
(19,'gudasao','123456','顾大嫂',2,'13309090019',2,10500,'19.jpg','2008-01-01','2023-10-27 16:35:33','2023-10-27 16:36:11'),
(20,'ruanxiaoer','123456','阮小二',1,'13309090020',2,10800,'20.jpg','2018-01-01','2023-10-27 16:35:33','2023-10-27 16:36:13'),
(21,'ruanxiaowu','123456','阮小五',1,'13309090021',5,5200,'21.jpg','2015-01-01','2023-10-27 16:35:33','2023-10-27 16:36:15'),
(22,'ruanxiaoqi','123456','阮小七',1,'13309090022',5,5500,'22.jpg','2016-01-01','2023-10-27 16:35:33','2023-10-27 16:36:17'),
(23,'ruanji','123456','阮籍',1,'13309090023',5,5800,'23.jpg','2012-01-01','2023-10-27 16:35:33','2023-10-27 16:36:19'),
(24,'tongwei','123456','童威',1,'13309090024',5,5000,'24.jpg','2006-01-01','2023-10-27 16:35:33','2023-10-27 16:36:21'),
(25,'tongmeng','123456','童猛',1,'13309090025',5,4800,'25.jpg','2002-01-01','2023-10-27 16:35:33','2023-10-27 16:36:23'),
(26,'yanshun','123456','燕顺',1,'13309090026',5,5400,'26.jpg','2011-01-01','2023-10-27 16:35:33','2023-10-27 16:36:25'),
(27,'lijun','123456','李俊',1,'13309090027',5,6600,'27.jpg','2004-01-01','2023-10-27 16:35:33','2023-10-27 16:36:27'),
(28,'lizhong','123456','李忠',1,'13309090028',5,5000,'28.jpg','2007-01-01','2023-10-27 16:35:33','2023-10-27 16:36:29'),
(29,'songqing','123456','宋清',1,'13309090029',5,5100,'29.jpg','2020-01-01','2023-10-27 16:35:33','2023-10-27 16:36:31'),
(30,'liyun','123456','李云',1,'13309090030',NULL,NULL,'30.jpg','2020-03-01','2023-10-27 16:35:33','2023-10-27 16:36:31');
select name ,entry_date from emp;
查询所有字段
select * from emp;
起别名
select name as 姓名,entry_date as 入职时间 from emp;
select name as '姓名',entry_date as '入职时间' from emp;
select name as "姓名",entry_date as"入职时间" from emp;
查询emp表中的哪些职位
select distinct job from emp;
查询姓名为宋江的员工
select * from emp where name='宋江';
查询薪资小于5000的员工信息
select *from emp where salary<5000;
查询密码不的等于123456的员工信息
select * from emp where password <> '123456';
select * from emp where password != '123456' ;
查询日入职日期在2000-01-01到2010-01-01的员工信息
select * from emp where entry_date between '2000-01-01' and '2010-01-01';
select * from emp where entry_date >= '2000-01-01' and entry_date <= '2010-01-01';
查询入职日期在2000-01-01到2010-01-01之间的员工信息并且为女员工的信息
select * from emp where entry_date between '2000-01-01' and '2010-01-01' and gender=2;
查询职位是2，3，4的员工信息
select from emp where job=2 or job=3 or job=4;
select from emp where job in (2,3,4);
查询姓名为为两个字的员工信息
select * from tb_user where username like '__';
查询姓为阮的员工信息
select * from emp where name like '阮%';
select * from emp where name like '%小%';
select * from emp where name like '%深';
统计该企业的员工数量
select count(*) from emp;
select count(id) from emp;
select count(name) from emp;
select count(job) from emp;
select count(0) from emp;
统计员工的平均薪资
select avg(salary) from emp;
统计员工的最大薪资
select max(salary) from emp;
统计员工的最小薪资
select min(salary) from emp;
统员工薪资总和
select sum(salary) from emp;
根据性别分组统计男女员工数量
select gender,count(*) from emp group by gender;
查询入职时间在 '2015-01-01' (包含) 以前的员工 , 并对结果根据职位分组 , 获取员工数量大于等于2的职位
select job,count() from emp where entry_date <='2015-01-01' group by job having count()>=2;
根据入职时间, 对员工进行升序排序
select * from emp order by entry_date asc;
根据入职时间，对员工进行降序排序
select * from emp order by entry_date desc;
根据入职时间对公司的员工进行升序排序，入职时间相同，再按照更新时间进行降序排序
select * from emp order by entry_date asc,update_time desc;
从起始索引0开始查询员工数据, 每页展示5条记录
select from emp limit 0,5;
select from emp limit 5;
select from emp limit 5,5;
select from emp limit 10,5;
create table emp(
id int unsigned primary key auto_increment comment 'ID,主键',
username varchar(20) not null unique comment '用户名',
password varchar(32) not null comment '密码',
name varchar(10) not null comment '姓名',
gender tinyint unsigned not null comment '性别, 1:男, 2:女',
phone char(11) not null unique comment '手机号',
job tinyint unsigned comment '职位, 1:班主任,2:讲师,3:学工主管,4:教研主管,5:咨询师',
salary int unsigned comment '薪资',
image varchar(300) comment '头像',
entry_date date comment '入职日期',
create_time datetime comment '创建时间',
update_time datetime comment '修改时间'
) comment '员工表';
-- 准备测试数据
INSERT INTO emp(id, username, password, name, gender, phone, job, salary, image, entry_date, create_time, update_time)
VALUES (1,'shinaian','123456','施耐庵',1,'13309090001',4,15000,'1.jpg','2000-01-01','2023-10-27 16:35:33','2023-10-27 16:35:35'),
(2,'songjiang','123456','宋江',1,'13309090002',2,8600,'2.jpg','2015-01-01','2023-10-27 16:35:33','2023-10-27 16:35:37'),
(3,'lujunyi','123456','卢俊义',1,'13309090003',2,8900,'3.jpg','2008-05-01','2023-10-27 16:35:33','2023-10-27 16:35:39'),
(4,'wuyong','123456','吴用',1,'13309090004',2,9200,'4.jpg','2007-01-01','2023-10-27 16:35:33','2023-10-27 16:35:41'),
(5,'gongsunsheng','123456','公孙胜',1,'13309090005',2,9500,'5.jpg','2012-12-05','2023-10-27 16:35:33','2023-10-27 16:35:43'),
(6,'huosanniang','123456','扈三娘',2,'13309090006',3,6500,'6.jpg','2013-09-05','2023-10-27 16:35:33','2023-10-27 16:35:45'),
(7,'chaijin','123456','柴进',1,'13309090007',1,4700,'7.jpg','2005-08-01','2023-10-27 16:35:33','2023-10-27 16:35:47'),
(8,'likui','123456','李逵',1,'13309090008',1,4800,'8.jpg','2014-11-09','2023-10-27 16:35:33','2023-10-27 16:35:49'),
(9,'wusong','123456','武松',1,'13309090009',1,4900,'9.jpg','2011-03-11','2023-10-27 16:35:33','2023-10-27 16:35:51'),
(10,'lichong','123456','林冲',1,'13309090010',1,5000,'10.jpg','2013-09-05','2023-10-27 16:35:33','2023-10-27 16:35:53'),
(11,'huyanzhuo','123456','呼延灼',1,'13309090011',2,9700,'11.jpg','2007-02-01','2023-10-27 16:35:33','2023-10-27 16:35:55'),
(12,'xiaoliguang','123456','小李广',1,'13309090012',2,10000,'12.jpg','2008-08-18','2023-10-27 16:35:33','2023-10-27 16:35:57'),
(13,'yangzhi','123456','杨志',1,'13309090013',1,5300,'13.jpg','2012-11-01','2023-10-27 16:35:33','2023-10-27 16:35:59'),
(14,'shijin','123456','史进',1,'13309090014',2,10600,'14.jpg','2002-08-01','2023-10-27 16:35:33','2023-10-27 16:36:01'),
(15,'sunerniang','123456','孙二娘',2,'13309090015',2,10900,'15.jpg','2011-05-01','2023-10-27 16:35:33','2023-10-27 16:36:03'),
(16,'luzhishen','123456','鲁智深',1,'13309090016',2,9600,'16.jpg','2010-01-01','2023-10-27 16:35:33','2023-10-27 16:36:05'),
(17,'liying','12345678','李应',1,'13309090017',1,5800,'17.jpg','2015-03-21','2023-10-27 16:35:33','2023-10-27 16:36:07'),
(18,'shiqian','123456','时迁',1,'13309090018',2,10200,'18.jpg','2015-01-01','2023-10-27 16:35:33','2023-10-27 16:36:09'),
(19,'gudasao','123456','顾大嫂',2,'13309090019',2,10500,'19.jpg','2008-01-01','2023-10-27 16:35:33','2023-10-27 16:36:11'),
(20,'ruanxiaoer','123456','阮小二',1,'13309090020',2,10800,'20.jpg','2018-01-01','2023-10-27 16:35:33','2023-10-27 16:36:13'),
(21,'ruanxiaowu','123456','阮小五',1,'13309090021',5,5200,'21.jpg','2015-01-01','2023-10-27 16:35:33','2023-10-27 16:36:15'),
(22,'ruanxiaoqi','123456','阮小七',1,'13309090022',5,5500,'22.jpg','2016-01-01','2023-10-27 16:35:33','2023-10-27 16:36:17'),
(23,'ruanji','123456','阮籍',1,'13309090023',5,5800,'23.jpg','2012-01-01','2023-10-27 16:35:33','2023-10-27 16:36:19'),
(24,'tongwei','123456','童威',1,'13309090024',5,5000,'24.jpg','2006-01-01','2023-10-27 16:35:33','2023-10-27 16:36:21'),
(25,'tongmeng','123456','童猛',1,'13309090025',5,4800,'25.jpg','2002-01-01','2023-10-27 16:35:33','2023-10-27 16:36:23'),
(26,'yanshun','123456','燕顺',1,'13309090026',5,5400,'26.jpg','2011-01-01','2023-10-27 16:35:33','2023-10-27 16:36:25'),
(27,'lijun','123456','李俊',1,'13309090027',5,6600,'27.jpg','2004-01-01','2023-10-27 16:35:33','2023-10-27 16:36:27'),
(28,'lizhong','123456','李忠',1,'13309090028',5,5000,'28.jpg','2007-01-01','2023-10-27 16:35:33','2023-10-27 16:36:29'),
(29,'songqing','123456','宋清',1,'13309090029',5,5100,'29.jpg','2020-01-01','2023-10-27 16:35:33','2023-10-27 16:36:31'),
(30,'liyun','123456','李云',1,'13309090030',NULL,NULL,'30.jpg','2020-03-01','2023-10-27 16:35:33','2023-10-27 16:36:31');
1. 往emp表中插入一条测试数据，全部的字段都需要插入值 。
insert into emp values(null,'list','123456','list',1,'13309090031',1,5300,'31.jpg','2012-11-01',now(),now());
2.批量往emp表中插入两条测试数据， 全部的字段都需要插入值 。
insert into emp values(32,'morant1','123456','morant1',1,'13309090032',1,5300,'32.jpg','2012-11-01',now(),now()),
(33,'morant2','123456','morant2',1,'13309090033',1,5300,'33.jpg','2012-11-01',now(),now());
3. 删除id为30,31的员工信息
delete from emp where id in(30,31);
4.将id为6，7，9，12这个几个员工的 入职日期修改为 '2018-09-10' , 密码修改为 '1234567'
update emp set entry_date='2018-09-10',password='1234567' where id in(6,7,9,12);
1. 查询指定字段 name,entry_date 并设置别名为 姓名、入职日期 并返回
select name as 姓名,entry_date as 入职日期 from emp;
2.查询返回所有字段 （两种写法）
select * from emp;
select id,username,password,name,gender,phone,job,salary,image,entry_date,create_time,update_time from emp;
3.查询性别为女 且 有职位的员工，要求查询出 name,entry_date 并起别名 (姓名、入职日期)
select name as 姓名,entry_date as 入职日期 from emp where gender=2 and job is not null;
4.查询已有的员工关联了 哪几种 职位(不要重复)
select distinct job from emp;
5.查询 姓名 为 '童威' 或 入职时间在 '2000-01-01' 到 '2010-01-01' 的员工
select * from emp where name='童威' or entry_date between '2000-01-01' and '2010-01-01';
6.查询在 薪资小于等于5000 的员工信息
select * from emp where salary<=5000;
7.查询没有分配职位的员工信息
select * from emp where job is null;
8.查询有职位的员工信息
select * from emp where job is not null;
9.查询密码不等于'123456'或没有职位的员工信息
select * from emp where password!='123456' or job is null;
10.查询入职日期 在 '2000-01-01' (包含) 到 '2015-01-01'(包含) 之间的员工信息
select * from emp where entry_date between '2000-01-01' and '2015-01-01';
11.查询入职时间在'2000-01-01' (包含)到'2015-01-01'(包含)之间且性别为女的员工信息
select * from emp where entry_date between '2000-01-01' and '2015-01-01' and gender=2;
12.查询职位是2(讲师)或3(学工主管)或4(教研主管)的员工信息（两种写法实现）
select from emp where job in(2,3,4);
select from emp where job=2 or job=3 or job=4;
13.查询姓名为两个字且性别为男且薪资高于5000的员工信息
select * from emp where name like '__' and gender=1 and salary>5000;
14.查询姓名中包含 '小' 的员工信息且入职时间在 '2008-01-01' 之后入职的员工
select * from emp where name like '%小%' and entry_date>'2008-01-01';
15.查询出性别为男 , 或入职时间在'2010-01-01'之后入职的员工
select * from emp where gender=1 or entry_date>'2010-01-01';
16.查询有职位的， 且 姓名为两个字 的员工信息
select * from emp where job is not null and name like'__';
17.查询所有的性别为男(gender 为 1)的讲师(job 为 2) 的员工信息
select * from emp where gender=1 and job=2;
18.查询薪资小于8000或入职时间在 2010-10-09 之后入职的员工信息
select *from emp where salary<8000 or entry_date>'2010-10-09';
19.根据性别分组 , 统计男性和女性员工的数量
select gender,count(*) from emp group by gender;
20.查询入职时间在 '2015-01-01' (包含) 以前的员工 , 并对结果根据职位分组 , 获取员工数量大于等于2的职位
select job,count() from emp where entry_date<='2015-01-01' group by job having count()>=2;
2.#添加字段
alter table emp add qq char(11) comment 'qq号';
修改字段
alter table emp change qq qq_num char(13) comment 'qq号';
删除字段
alter table emp drop qq_num;
删除表
drop table student;
插入对应数据
insert into emp(username,password,name,gender,phone,create_time,update_time)
values('zhangsan','123456','张三','1','13800000000',now(),now());
-- 插入员工信息记录
-- 向员工表中插入一条完整的员工记录，包含员工的所有字段信息
insert into emp
values(2,'lisi','123456','李四','1','13800000001',1,10000,'1.jpg','2020-10-01',now(),now());
-- 插入指定字段的员工信息记录
-- 向员工表中插入一条员工记录，明确指定要插入的字段，不包含头像字段
insert into emp(id,username,password,name,gender,phone,job,salary,create_time,update_time)
values(3,'wangwu','123456','王五','2','13800000002',2,15000,now(),now());
-- 批量插入多条员工信息记录
-- 向员工表中批量插入两条员工记录，提高数据插入效率
insert into emp(id,username,password,name,gender,phone,job,salary,create_time,update_time)
values(4,'zhaoliu','123456','赵六','1','13800000003',3,20000,now(),now()),
(5,'zhaoliu2','123456','赵七','1','13800000004',3,20000,now(),now());
-- 更新指定员工的姓名信息
-- 根据员工ID更新员工姓名，并更新最后修改时间
update emp set name='张四',update_time =now() where id=1;
-- 批量更新员工入职日期
-- 更新所有员工的入职日期和最后修改时间
update emp set entry_date='2020-10-01',update_time=now();
delete from emp where id=1;
delete from emp;
create table emp(
id int unsigned primary key auto_increment comment 'ID,主键',
username varchar(20) not null unique comment '用户名',
password varchar(32) not null comment '密码',
name varchar(10) not null comment '姓名',
gender tinyint unsigned not null comment '性别, 1:男, 2:女',
phone char(11) not null unique comment '手机号',
job tinyint unsigned comment '职位, 1:班主任,2:讲师,3:学工主管,4:教研主管,5:咨询师',
salary int unsigned comment '薪资',
image varchar(300) comment '头像',
entry_date date comment '入职日期',
create_time datetime comment '创建时间',
update_time datetime comment '修改时间'
) comment '员工表';
-- 准备测试数据
INSERT INTO emp(id, username, password, name, gender, phone, job, salary, image, entry_date, create_time, update_time)
VALUES (1,'shinaian','123456','施耐庵',1,'13309090001',4,15000,'1.jpg','2000-01-01','2023-10-27 16:35:33','2023-10-27 16:35:35'),
(2,'songjiang','123456','宋江',1,'13309090002',2,8600,'2.jpg','2015-01-01','2023-10-27 16:35:33','2023-10-27 16:35:37'),
(3,'lujunyi','123456','卢俊义',1,'13309090003',2,8900,'3.jpg','2008-05-01','2023-10-27 16:35:33','2023-10-27 16:35:39'),
(4,'wuyong','123456','吴用',1,'13309090004',2,9200,'4.jpg','2007-01-01','2023-10-27 16:35:33','2023-10-27 16:35:41'),
(5,'gongsunsheng','123456','公孙胜',1,'13309090005',2,9500,'5.jpg','2012-12-05','2023-10-27 16:35:33','2023-10-27 16:35:43'),
(6,'huosanniang','123456','扈三娘',2,'13309090006',3,6500,'6.jpg','2013-09-05','2023-10-27 16:35:33','2023-10-27 16:35:45'),
(7,'chaijin','123456','柴进',1,'13309090007',1,4700,'7.jpg','2005-08-01','2023-10-27 16:35:33','2023-10-27 16:35:47'),
(8,'likui','123456','李逵',1,'13309090008',1,4800,'8.jpg','2014-11-09','2023-10-27 16:35:33','2023-10-27 16:35:49'),
(9,'wusong','123456','武松',1,'13309090009',1,4900,'9.jpg','2011-03-11','2023-10-27 16:35:33','2023-10-27 16:35:51'),
(10,'lichong','123456','林冲',1,'13309090010',1,5000,'10.jpg','2013-09-05','2023-10-27 16:35:33','2023-10-27 16:35:53'),
(11,'huyanzhuo','123456','呼延灼',1,'13309090011',2,9700,'11.jpg','2007-02-01','2023-10-27 16:35:33','2023-10-27 16:35:55'),
(12,'xiaoliguang','123456','小李广',1,'13309090012',2,10000,'12.jpg','2008-08-18','2023-10-27 16:35:33','2023-10-27 16:35:57'),
(13,'yangzhi','123456','杨志',1,'13309090013',1,5300,'13.jpg','2012-11-01','2023-10-27 16:35:33','2023-10-27 16:35:59'),
(14,'shijin','123456','史进',1,'13309090014',2,10600,'14.jpg','2002-08-01','2023-10-27 16:35:33','2023-10-27 16:36:01'),
(15,'sunerniang','123456','孙二娘',2,'13309090015',2,10900,'15.jpg','2011-05-01','2023-10-27 16:35:33','2023-10-27 16:36:03'),
(16,'luzhishen','123456','鲁智深',1,'13309090016',2,9600,'16.jpg','2010-01-01','2023-10-27 16:35:33','2023-10-27 16:36:05'),
(17,'liying','12345678','李应',1,'13309090017',1,5800,'17.jpg','2015-03-21','2023-10-27 16:35:33','2023-10-27 16:36:07'),
(18,'shiqian','123456','时迁',1,'13309090018',2,10200,'18.jpg','2015-01-01','2023-10-27 16:35:33','2023-10-27 16:36:09'),
(19,'gudasao','123456','顾大嫂',2,'13309090019',2,10500,'19.jpg','2008-01-01','2023-10-27 16:35:33','2023-10-27 16:36:11'),
(20,'ruanxiaoer','123456','阮小二',1,'13309090020',2,10800,'20.jpg','2018-01-01','2023-10-27 16:35:33','2023-10-27 16:36:13'),
(21,'ruanxiaowu','123456','阮小五',1,'13309090021',5,5200,'21.jpg','2015-01-01','2023-10-27 16:35:33','2023-10-27 16:36:15'),
(22,'ruanxiaoqi','123456','阮小七',1,'13309090022',5,5500,'22.jpg','2016-01-01','2023-10-27 16:35:33','2023-10-27 16:36:17'),
(23,'ruanji','123456','阮籍',1,'13309090023',5,5800,'23.jpg','2012-01-01','2023-10-27 16:35:33','2023-10-27 16:36:19'),
(24,'tongwei','123456','童威',1,'13309090024',5,5000,'24.jpg','2006-01-01','2023-10-27 16:35:33','2023-10-27 16:36:21'),
(25,'tongmeng','123456','童猛',1,'13309090025',5,4800,'25.jpg','2002-01-01','2023-10-27 16:35:33','2023-10-27 16:36:23'),
(26,'yanshun','123456','燕顺',1,'13309090026',5,5400,'26.jpg','2011-01-01','2023-10-27 16:35:33','2023-10-27 16:36:25'),
(27,'lijun','123456','李俊',1,'13309090027',5,6600,'27.jpg','2004-01-01','2023-10-27 16:35:33','2023-10-27 16:36:27'),
(28,'lizhong','123456','李忠',1,'13309090028',5,5000,'28.jpg','2007-01-01','2023-10-27 16:35:33','2023-10-27 16:36:29'),
(29,'songqing','123456','宋清',1,'13309090029',5,5100,'29.jpg','2020-01-01','2023-10-27 16:35:33','2023-10-27 16:36:31'),
(30,'liyun','123456','李云',1,'13309090030',NULL,NULL,'30.jpg','2020-03-01','2023-10-27 16:35:33','2023-10-27 16:36:31');
select name ,entry_date from emp;
查询所有字段
select * from emp;
起别名
select name as 姓名,entry_date as 入职时间 from emp;
select name as '姓名',entry_date as '入职时间' from emp;
select name as "姓名",entry_date as"入职时间" from emp;
查询emp表中的哪些职位
select distinct job from emp;
查询姓名为宋江的员工
select * from emp where name='宋江';
查询薪资小于5000的员工信息
select *from emp where salary<5000;
查询密码不的等于123456的员工信息
select * from emp where password <> '123456';
select * from emp where password != '123456' ;
查询日入职日期在2000-01-01到2010-01-01的员工信息
select * from emp where entry_date between '2000-01-01' and '2010-01-01';
select * from emp where entry_date >= '2000-01-01' and entry_date <= '2010-01-01';
查询入职日期在2000-01-01到2010-01-01之间的员工信息并且为女员工的信息
select * from emp where entry_date between '2000-01-01' and '2010-01-01' and gender=2;
查询职位是2，3，4的员工信息
select from emp where job=2 or job=3 or job=4;
select from emp where job in (2,3,4);
查询姓名为为两个字的员工信息
select * from tb_user where username like '__';
查询姓为阮的员工信息
select * from emp where name like '阮%';
select * from emp where name like '%小%';
select * from emp where name like '%深';
统计该企业的员工数量
select count(*) from emp;
select count(id) from emp;
select count(name) from emp;
select count(job) from emp;
select count(0) from emp;
统计员工的平均薪资
select avg(salary) from emp;
统计员工的最大薪资
select max(salary) from emp;
统计员工的最小薪资
select min(salary) from emp;
统员工薪资总和
select sum(salary) from emp;
根据性别分组统计男女员工数量
select gender,count(*) from emp group by gender;
查询入职时间在 '2015-01-01' (包含) 以前的员工 , 并对结果根据职位分组 , 获取员工数量大于等于2的职位
select job,count() from emp where entry_date <='2015-01-01' group by job having count()>=2;
根据入职时间, 对员工进行升序排序
select * from emp order by entry_date asc;
根据入职时间，对员工进行降序排序
select * from emp order by entry_date desc;
根据入职时间对公司的员工进行升序排序，入职时间相同，再按照更新时间进行降序排序
select * from emp order by entry_date asc,update_time desc;
从起始索引0开始查询员工数据, 每页展示5条记录
select from emp limit 0,5;
select from emp limit 5;
select from emp limit 5,5;
select from emp limit 10,5;
create table emp(
id int unsigned primary key auto_increment comment 'ID,主键',
username varchar(20) not null unique comment '用户名',
password varchar(32) not null comment '密码',
name varchar(10) not null comment '姓名',
gender tinyint unsigned not null comment '性别, 1:男, 2:女',
phone char(11) not null unique comment '手机号',
job tinyint unsigned comment '职位, 1:班主任,2:讲师,3:学工主管,4:教研主管,5:咨询师',
salary int unsigned comment '薪资',
image varchar(300) comment '头像',
entry_date date comment '入职日期',
create_time datetime comment '创建时间',
update_time datetime comment '修改时间'
) comment '员工表';
-- 准备测试数据
INSERT INTO emp(id, username, password, name, gender, phone, job, salary, image, entry_date, create_time, update_time)
VALUES (1,'shinaian','123456','施耐庵',1,'13309090001',4,15000,'1.jpg','2000-01-01','2023-10-27 16:35:33','2023-10-27 16:35:35'),
(2,'songjiang','123456','宋江',1,'13309090002',2,8600,'2.jpg','2015-01-01','2023-10-27 16:35:33','2023-10-27 16:35:37'),
(3,'lujunyi','123456','卢俊义',1,'13309090003',2,8900,'3.jpg','2008-05-01','2023-10-27 16:35:33','2023-10-27 16:35:39'),
(4,'wuyong','123456','吴用',1,'13309090004',2,9200,'4.jpg','2007-01-01','2023-10-27 16:35:33','2023-10-27 16:35:41'),
(5,'gongsunsheng','123456','公孙胜',1,'13309090005',2,9500,'5.jpg','2012-12-05','2023-10-27 16:35:33','2023-10-27 16:35:43'),
(6,'huosanniang','123456','扈三娘',2,'13309090006',3,6500,'6.jpg','2013-09-05','2023-10-27 16:35:33','2023-10-27 16:35:45'),
(7,'chaijin','123456','柴进',1,'13309090007',1,4700,'7.jpg','2005-08-01','2023-10-27 16:35:33','2023-10-27 16:35:47'),
(8,'likui','123456','李逵',1,'13309090008',1,4800,'8.jpg','2014-11-09','2023-10-27 16:35:33','2023-10-27 16:35:49'),
(9,'wusong','123456','武松',1,'13309090009',1,4900,'9.jpg','2011-03-11','2023-10-27 16:35:33','2023-10-27 16:35:51'),
(10,'lichong','123456','林冲',1,'13309090010',1,5000,'10.jpg','2013-09-05','2023-10-27 16:35:33','2023-10-27 16:35:53'),
(11,'huyanzhuo','123456','呼延灼',1,'13309090011',2,9700,'11.jpg','2007-02-01','2023-10-27 16:35:33','2023-10-27 16:35:55'),
(12,'xiaoliguang','123456','小李广',1,'13309090012',2,10000,'12.jpg','2008-08-18','2023-10-27 16:35:33','2023-10-27 16:35:57'),
(13,'yangzhi','123456','杨志',1,'13309090013',1,5300,'13.jpg','2012-11-01','2023-10-27 16:35:33','2023-10-27 16:35:59'),
(14,'shijin','123456','史进',1,'13309090014',2,10600,'14.jpg','2002-08-01','2023-10-27 16:35:33','2023-10-27 16:36:01'),
(15,'sunerniang','123456','孙二娘',2,'13309090015',2,10900,'15.jpg','2011-05-01','2023-10-27 16:35:33','2023-10-27 16:36:03'),
(16,'luzhishen','123456','鲁智深',1,'13309090016',2,9600,'16.jpg','2010-01-01','2023-10-27 16:35:33','2023-10-27 16:36:05'),
(17,'liying','12345678','李应',1,'13309090017',1,5800,'17.jpg','2015-03-21','2023-10-27 16:35:33','2023-10-27 16:36:07'),
(18,'shiqian','123456','时迁',1,'13309090018',2,10200,'18.jpg','2015-01-01','2023-10-27 16:35:33','2023-10-27 16:36:09'),
(19,'gudasao','123456','顾大嫂',2,'13309090019',2,10500,'19.jpg','2008-01-01','2023-10-27 16:35:33','2023-10-27 16:36:11'),
(20,'ruanxiaoer','123456','阮小二',1,'13309090020',2,10800,'20.jpg','2018-01-01','2023-10-27 16:35:33','2023-10-27 16:36:13'),
(21,'ruanxiaowu','123456','阮小五',1,'13309090021',5,5200,'21.jpg','2015-01-01','2023-10-27 16:35:33','2023-10-27 16:36:15'),
(22,'ruanxiaoqi','123456','阮小七',1,'13309090022',5,5500,'22.jpg','2016-01-01','2023-10-27 16:35:33','2023-10-27 16:36:17'),
(23,'ruanji','123456','阮籍',1,'13309090023',5,5800,'23.jpg','2012-01-01','2023-10-27 16:35:33','2023-10-27 16:36:19'),
(24,'tongwei','123456','童威',1,'13309090024',5,5000,'24.jpg','2006-01-01','2023-10-27 16:35:33','2023-10-27 16:36:21'),
(25,'tongmeng','123456','童猛',1,'13309090025',5,4800,'25.jpg','2002-01-01','2023-10-27 16:35:33','2023-10-27 16:36:23'),
(26,'yanshun','123456','燕顺',1,'13309090026',5,5400,'26.jpg','2011-01-01','2023-10-27 16:35:33','2023-10-27 16:36:25'),
(27,'lijun','123456','李俊',1,'13309090027',5,6600,'27.jpg','2004-01-01','2023-10-27 16:35:33','2023-10-27 16:36:27'),
(28,'lizhong','123456','李忠',1,'13309090028',5,5000,'28.jpg','2007-01-01','2023-10-27 16:35:33','2023-10-27 16:36:29'),
(29,'songqing','123456','宋清',1,'13309090029',5,5100,'29.jpg','2020-01-01','2023-10-27 16:35:33','2023-10-27 16:36:31'),
(30,'liyun','123456','李云',1,'13309090030',NULL,NULL,'30.jpg','2020-03-01','2023-10-27 16:35:33','2023-10-27 16:36:31');
1. 往emp表中插入一条测试数据，全部的字段都需要插入值 。
insert into emp values(null,'list','123456','list',1,'13309090031',1,5300,'31.jpg','2012-11-01',now(),now());
2.批量往emp表中插入两条测试数据， 全部的字段都需要插入值 。
insert into emp values(32,'morant1','123456','morant1',1,'13309090032',1,5300,'32.jpg','2012-11-01',now(),now()),
(33,'morant2','123456','morant2',1,'13309090033',1,5300,'33.jpg','2012-11-01',now(),now());
3. 删除id为30,31的员工信息
delete from emp where id in(30,31);
4.将id为6，7，9，12这个几个员工的 入职日期修改为 '2018-09-10' , 密码修改为 '1234567'
update emp set entry_date='2018-09-10',password='1234567' where id in(6,7,9,12);
1. 查询指定字段 name,entry_date 并设置别名为 姓名、入职日期 并返回
select name as 姓名,entry_date as 入职日期 from emp;
2.查询返回所有字段 （两种写法）
select * from emp;
select id,username,password,name,gender,phone,job,salary,image,entry_date,create_time,update_time from emp;
3.查询性别为女 且 有职位的员工，要求查询出 name,entry_date 并起别名 (姓名、入职日期)
select name as 姓名,entry_date as 入职日期 from emp where gender=2 and job is not null;
4.查询已有的员工关联了 哪几种 职位(不要重复)
select distinct job from emp;
5.查询 姓名 为 '童威' 或 入职时间在 '2000-01-01' 到 '2010-01-01' 的员工
select * from emp where name='童威' or entry_date between '2000-01-01' and '2010-01-01';
6.查询在 薪资小于等于5000 的员工信息
select * from emp where salary<=5000;
7.查询没有分配职位的员工信息
select * from emp where job is null;
8.查询有职位的员工信息
select * from emp where job is not null;
9.查询密码不等于'123456'或没有职位的员工信息
select * from emp where password!='123456' or job is null;
10.查询入职日期 在 '2000-01-01' (包含) 到 '2015-01-01'(包含) 之间的员工信息
select * from emp where entry_date between '2000-01-01' and '2015-01-01';
11.查询入职时间在'2000-01-01' (包含)到'2015-01-01'(包含)之间且性别为女的员工信息
select * from emp where entry_date between '2000-01-01' and '2015-01-01' and gender=2;
12.查询职位是2(讲师)或3(学工主管)或4(教研主管)的员工信息（两种写法实现）
select from emp where job in(2,3,4);
select from emp where job=2 or job=3 or job=4;
13.查询姓名为两个字且性别为男且薪资高于5000的员工信息
select * from emp where name like '__' and gender=1 and salary>5000;
14.查询姓名中包含 '小' 的员工信息且入职时间在 '2008-01-01' 之后入职的员工
select * from emp where name like '%小%' and entry_date>'2008-01-01';
15.查询出性别为男 , 或入职时间在'2010-01-01'之后入职的员工
select * from emp where gender=1 or entry_date>'2010-01-01';
16.查询有职位的， 且 姓名为两个字 的员工信息
select * from emp where job is not null and name like'__';
17.查询所有的性别为男(gender 为 1)的讲师(job 为 2) 的员工信息
select * from emp where gender=1 and job=2;
18.查询薪资小于8000或入职时间在 2010-10-09 之后入职的员工信息
select *from emp where salary<8000 or entry_date>'2010-10-09';
19.根据性别分组 , 统计男性和女性员工的数量
select gender,count(*) from emp group by gender;
20.查询入职时间在 '2015-01-01' (包含) 以前的员工 , 并对结果根据职位分组 , 获取员工数量大于等于2的职位
select job,count() from emp where entry_date<='2015-01-01' group by job having count()>=2;
2.树状图
【Xmind思维导图】MySqlday02 https://ai.xmind.cn/share/1Z9rzCbk
