目录
步骤
1、创建表单
2、添加数据
3、显示所有职工的基本信息
4、查询所有职工所属部门的部门号,不显示重复的部门号
5、 求出所有职工的人数
6、 列出最高工资和最低工资
7、 列出职工的平均工资和总工资
8、 创建一个只有职工号、姓名和参加工作的新表,名为工作日期表
9、显示所有女职工的年龄
10、列出所有姓刘的职工的职工号、姓名和出生日期
11、列出1960年以前出生的职工的姓名、参加工作日期
12、列出工资在1000-2000之间的所有职工姓名
13、 列出所有陈姓和李姓的职工姓名
14、 列出所有部门号为2和3的职工号、姓名、党员否
15、将职工表worker中的职工按出生的先后顺序排序
16、 显示工资最高的前3名职工的职工号和姓名
17、 求出各部门党员的人数
18、 统计各部门的工资和平均工资
19、列出总人数大于4的部门号和总人数
步骤
1、创建表单
命令:
create table worker( wo_dep_no int(11) not null comment'部门号', wo_no int(11) not null comment'职工号', wo_time date not null comment'工作时间', wo_salary float(8,2) not null comment'工资', wo_po varchar(10) not null default'群众' comment'政治面貌', wo_name varchar(20) not null comment'姓名', wo_age tinyint not null comment'年龄', wo_gender char(1) not null default'M' comment'性别', wo_bd date not null comment'出生日期', primary key(wo_no) )engine=InnoDB default charset=utf8 row_format=dynamic;
2、添加数据
命令:
insert into worker values (101,1001,'2015-5-4',3500.00,'群众','张三',33,'M','1990-7-1'),(101,1002,'2017-2-6',3200.00,'团员','李四',26,'M','1997-2-8'),(101,1003,'2014-5-4',4500.00,'党员','王五',43,'M','1980-7-9'),(101,1004,'2013-5-4',1500.00,'群众','陈濯',73,'M','1950-6-9'),(101,1005,'2003-5-4',4500.00,'团员','邵顺',68,'M','1955-4-10'),(102,1006,'2020-7-5',1900.00,'群众','李倝',28,'M','1995-8-10'),(102,1007,'2010-8-5',1800.00,'群众','刘妶',25,'F','1998-9-1'),(102,1008,'2018-8-25',4580.00,'党员','陈淑',22,'F','2001-5-1'),(102,1009,'2014-8-25',3200.00,'团员','丁珍',29,'F','1994-10-1'),(103,1010,'2004-8-15',4200.00,'团员','胡竹',31,'F','1992-6-1'),(103,1011,'2015-9-16',4500.00,'党员','刘允',41,'F','1982-6-5'),(103,1012,'1990-4-26',5900.00,'党员','刘影',71,'M','1952-6-5');
3、显示所有职工的基本信息
命令:
select *from worker;
4、查询所有职工所属部门的部门号,不显示重复的部门号
命令:
select distinct wo_dep_no from worker;
distinct:
使用distinct关键字可从查询结果中清除重复行
作用范围是后面所有字段的组合
5、 求出所有职工的人数
命令:
select count(*) from worker;
使用count这个聚合函数完成表中数量的统计
*:代表所有 ,任意字符
count(*)表示返回表中的记录数(包括所有列),相当于统计表的行数(不会忽略列值为NULL的记录)
6、 列出最高工资和最低工资
命令:
select max(wo_salary),min(wo_salary) from worker;
7、 列出职工的平均工资和总工资
命令:
select avg(wo_salary),sum(wo_salary) from worker;
8、 创建一个只有职工号、姓名和参加工作的新表,名为工作日期表
命令:
create table work_date select wo_no,wo_name,wo_time from worker;select *from work_date;
9、显示所有女职工的年龄
命令:
select wo_name,wo_age from worker where wo_gender='F';
10、列出所有姓刘的职工的职工号、姓名和出生日期
命令:
select wo_no,wo_name,wo_bd from worker where wo_name like '刘%';
另一种方式:
正则表达式
select wo_name,wo_no,wo_bd from worker where wo_name regexp '^刘';
补充:
以李字开头的 :'李%'
以李字结尾的:'%李'
包含李字的:'%李%'
第二个字是李的:'_李%'
11、列出1960年以前出生的职工的姓名、参加工作日期
命令:
select wo_name,wo_time from worker where wo_bd<19600000;
命令(第二种):
select wo_name,wo_time from worker where wo_bd<'1960-01-01';
12、列出工资在1000-2000之间的所有职工姓名
命令:
select wo_name,wo_salary from worker where wo_salary between 1000 and 2000;
13、 列出所有陈姓和李姓的职工姓名
命令:
方法一:
select wo_name from worker where wo_name like '陈%' or wo_name like '李%';
方法二(正则表达式):
select wo_name from worker where wo_name regexp '^[陈|李]';
^ :匹配开始
[abc]:匹配单个字符,匹配a或者b或者c,集合
^[陈|李]:匹配以陈或李开头的姓名
14、 列出所有部门号为2和3的职工号、姓名、党员否
命令:
方法一:
select wo_dep_no,wo_no,wo_name,wo_po='党员' from worker where wo_dep_no in (102,103);
方法二:
select wo_dep_no,wo_no,wo_name,if(wo_po='党员','是','否') as '是否党员' from worker where wo_dep_no in (101,102);
方法三:
select wo_dep_no,wo_no,wo_name,if(wo_po='党员','是','否') as '是否党员' from worker where wo_dep_no =101
union
select wo_dep_no,wo_no,wo_name,if(wo_po='党员','是','否') as '是否党员' from worker where wo_dep_no =102;
wo_po='党员' 结果为0或1
0表示非党员,1表示为党员
in:成员运算符
15、将职工表worker中的职工按出生的先后顺序排序
命令:
select wo_name,wo_bd from worker order by wo_bd;
order by: 排序 ,默认为升序
16、 显示工资最高的前3名职工的职工号和姓名
命令:
select wo_no,wo_name,wo_salary from worker order by wo_salary desc limit 3;
desc(descend)
order by wo_salary desc:按薪资降序排列
limit:限制;来限定查询结果返回的起始记录和总数量。
用法:SELECT … LIMIT offset_start,row_count;
offset_start:第一个返回记录行的偏移量。默认为0.
row_count:要返回记录行的最大数目。
select wo_no,wo_name,wo_salary from worker order by wo_salary desc limit 3;检索前三个记录
17、 求出各部门党员的人数
命令:
select wo_dep_no,count(wo_po) from worker where wo_po='党员' group by wo_dep_no;
group by:分组
18、 统计各部门的工资和平均工资
命令:
select wo_dep_no,sum(wo_salary),avg(wo_salary) from worker group by wo_dep_no;
19、列出总人数大于4的部门号和总人数
命令:
select wo_dep_no,count(*) from worker group by wo_dep_no;
select wo_dep_no,count(*) from worker group by wo_dep_no having count(wo_dep_no)>4;
数据分组--限定组的结果:HAVING子句
HAVING子句用来对分组后的结果再进行条件过滤。
HAVING子句可以使用聚合函数。