创建数据库和表的SQL脚本
-- -----------------------------------
-- 创建db10库、emp表并插入记录
-- -----------------------------------
-- 删除db10库(如果存在)
drop database if exists db10;
-- 重新创建db10库
create database db10 charset utf8;
-- 选择db10库
use db10;
-- 删除员工表(如果存在)
drop table if exists emp;
-- 创建员工表
create table emp(
id int primary key auto_increment, -- 员工编号
name varchar(50), -- 员工姓名
gender char(1), -- 员工性别
birthday date, -- 员工生日
dept varchar(50), -- 所属部门
job varchar(50), -- 所任职位
sal double, -- 薪资
bonus double -- 奖金
);
-- 往员工表中插入记录
INSERT INTO `emp` VALUES (null, '王海涛', '男', '1995-03-25', '培优部', '讲师', 1800, 400);
INSERT INTO `emp` VALUES (null, '齐雷', '男', '1994-04-06', '培优部', '讲师', 2500, 700);
INSERT INTO `emp` VALUES (null, '刘沛霞', '女', '1996-06-14', '培优部', '讲师', 1400, 400);
INSERT INTO `emp` VALUES (null, '陈子枢', '男', '1991-05-18', '培优部', '总监', 4500, 600);
INSERT INTO `emp` VALUES (null, '刘昱江', '男', '1993-11-18', '培优部', '讲师', 2600, 600);
INSERT INTO `emp` VALUES (null, '王克晶', '女', '1998-07-18', '就业部', '讲师', 3700, 700);
INSERT INTO `emp` VALUES (null, '苍老师', '男', '1995-08-18', '就业部', '总监', 4850, 500);
INSERT INTO `emp` VALUES (null, '范传奇', '男', '1999-09-18', '就业部', '讲师', 3200, 700);
INSERT INTO `emp` VALUES (null, '刘涛', '男', '1990-10-18', '就业部', '讲师', 2700, 500);
INSERT INTO `emp` VALUES (null, '韩少云', '男', '1980-12-18', NULL, 'CEO', 5000, null);
INSERT INTO `emp` VALUES (null, '董长春', '男', '1988-02-05', '培优部', '讲师', 3200, 300);
INSERT INTO `emp` VALUES (null, '张久军', '男', '1989-01-11', '培优部', '讲师', 4200, 500);
-- -----------------------------------
-- 创建db20库、dept表、emp表并插入记录
-- -----------------------------------
-- 删除db20库(如果存在)
drop database if exists db20;
-- 重新创建db20库
create database db20 charset utf8;
-- 选择db20库
use db20;
-- 删除部门表, 如果存在
drop table if exists dept;
-- 重新创建部门表, 要求id, name字段
create table dept(
id int primary key auto_increment, -- 部门编号
name varchar(20) -- 部门名称
);
-- 往部门表中插入记录
insert into dept values(null, '财务部');
insert into dept values(null, '人事部');
insert into dept values(null, '科技部');
insert into dept values(null, '销售部');
-- 删除员工表, 如果存在
drop table if exists emp;
-- 创建员工表, 要求id, name, dept_id
create table emp(
id int primary key auto_increment, -- 员工编号
name varchar(20), -- 员工姓名
dept_id int -- 部门编号
-- ,foreign key(dept_id) references dept(id)
);
insert into emp values(null, '张三', 1);
insert into emp values(null, '李四', 2);
insert into emp values(null, '老王', 3);
insert into emp values(null, '赵六', 4);
insert into emp values(null, '刘能', 4);
-- -----------------------------------
-- 创建db30库、dept表、emp表并插入记录
-- -----------------------------------
-- 删除db30库(如果存在)
drop database if exists db30;
-- 重新创建db30库
create database db30 charset utf8;
-- 选择db30库
use db30;
-- 删除部门表, 如果存在
drop table if exists dept;
-- 重新创建部门表, 要求id, name字段
create table dept(
id int primary key auto_increment, -- 部门编号
name varchar(20) -- 部门名称
);
-- 往部门表中插入记录
insert into dept values(null, '财务部');
insert into dept values(null, '人事部');
insert into dept values(null, '科技部');
insert into dept values(null, '销售部');
-- 删除员工表, 如果存在
drop table if exists emp;
-- 创建员工表(员工编号、员工姓名、所在部门编号)
create table emp(
id int primary key auto_increment, -- 员工编号
name varchar(20), -- 员工姓名
dept_id int -- 部门编号
);
-- 往员工表中插入记录
insert into emp values(null, '张三', 1);
insert into emp values(null, '李四', 2);
insert into emp values(null, '老王', 3);
insert into emp values(null, '赵六', 5);
-- -----------------------------------
-- 创建db40库、dept表、emp表并插入记录
-- -----------------------------------
-- 删除db40库(如果存在)
drop database if exists db40;
-- 重新创建db40库
create database db40 charset utf8;
-- 选择db40库
use db40;
-- 创建部门表
create table dept( -- 创建部门表
id int primary key, -- 部门编号
name varchar(50), -- 部门名称
loc varchar(50) -- 部门位置
);
-- 创建员工表
create table emp( -- 创建员工表
id int primary key, -- 员工编号
name varchar(50), -- 员工姓名
job varchar(50), -- 职位
topid int, -- 直属上级
hdate date, -- 受雇日期
sal int, -- 薪资
bonus int, -- 奖金
dept_id int, -- 所在部门编号
foreign key(dept_id) references dept(id)
);
-- 往部门表中插入记录
insert into dept values ('10', '培优部', '北京');
insert into dept values ('20', '就业部', '上海');
insert into dept values ('30', '大数据部', '广州');
insert into dept values ('40', '销售部', '深圳');
-- 往员工表中插入记录
insert into emp values ('1001', '王克晶', '办事员', '1007', '1990-12-17', '800', 500, '20');
insert into emp values ('1003', '齐雷', '分析员', '1011', '1991-02-20', '1900', '300', '10');
insert into emp values ('1005', '王海涛', '推销员', '1011', '1991-02-22', '2450', '600', '10');
insert into emp values ('1007', '刘苍松', '经理', '1017', '1991-04-02', '3675', 700, '20');
insert into emp values ('1009', '张慎政', '推销员', '1011', '1991-09-28', '1250', '1400', '10');
insert into emp values ('1011', '陈子枢', '经理', '1017', '1991-05-01', '3450', 400, '10');
insert into emp values ('1013', '张久军', '办事员', '1011', '1991-06-09', '1250', 800, '10');
insert into emp values ('1015', '程祖红', '分析员', '1007', '1997-04-19', '3000', 1000, '20');
insert into emp values ('1017', '韩少云', '董事长', null, '1991-11-17', '5000', null, null);
insert into emp values ('1019', '刘沛霞', '推销员', '1011', '1991-09-08', '1500', 500, '10');
insert into emp values ('1021', '范传奇', '办事员', '1007', '1997-05-23', '1100', 1000, '20');
insert into emp values ('1023', '赵栋', '经理', '1017', '1991-12-03', '950', null, '30');
insert into emp values ('1025', '朴乾', '分析员', '1023', '1991-12-03', '3000', 600, '30');
insert into emp values ('1027', '叶尚青', '办事员', '1023', '1992-01-23', '1300', 400, '30');
-- ------------------- 执行完毕 -----------------------
15、查询emp表中的所有员工,显示姓名,薪资,奖金
select name,sal,ifnull(bonus,0) from emp;
+--------+------+-----------------+
| name | sal | ifnull(bonus,0) |
+--------+------+-----------------+
| 王海涛 | 1800 | 400 |
| 齐雷 | 2500 | 700 |
| 刘沛霞 | 1400 | 400 |
| 陈子枢 | 4500 | 600 |
| 刘昱江 | 2600 | 600 |
| 王克晶 | 3700 | 700 |
| 苍老师 | 4850 | 500 |
| 范传奇 | 3200 | 700 |
| 刘涛 | 2700 | 500 |
| 韩少云 | 5000 | 0 |
| 董长春 | 3200 | 300 |
| 张久军 | 4200 | 500 |
+--------+------+-----------------+
16、查询emp表中的所有部门和职位
select distinct dept,job from emp;
+--------+------+
| dept | job |
+--------+------+
| 培优部 | 讲师 |
| 培优部 | 总监 |
| 就业部 | 讲师 |
| 就业部 | 总监 |
| NULL | CEO |
+--------+------+
17、查询emp表中【薪资大于3000】的所有员工,显示员工姓名、薪资
select name,sal from emp where sal>3000;
+--------+------+
| name | sal |
+--------+------+
| 陈子枢 | 4500 |
| 王克晶 | 3700 |
| 苍老师 | 4850 |
| 范传奇 | 3200 |
| 韩少云 | 5000 |
| 董长春 | 3200 |
| 张久军 | 4200 |
+--------+------+
18、查询emp表中【总薪资(薪资+奖金)大于3500】的所有员工,显示员工姓名、总薪资
select name,ifnull(bonus,0)+sal 总薪资 from emp where ifnull(bonus,0)+sal>3500;
+--------+--------+
| name | 总薪资 |
+--------+--------+
| 陈子枢 | 5100 |
| 王克晶 | 4400 |
| 苍老师 | 5350 |
| 范传奇 | 3900 |
| 韩少云 | 5000 |
| 张久军 | 4700 |
+--------+--------+
19、查询emp表中【薪资在3000和4500之间】的员工,显示员工姓名和薪资
select name, sal from emp where sal between 3000 and 4500;
+--------+------+
| name | sal |
+--------+------+
| 陈子枢 | 4500 |
| 王克晶 | 3700 |
| 范传奇 | 3200 |
| 董长春 | 3200 |
| 张久军 | 4200 |
+--------+------+
20、查询emp表中【薪资为 1400、1600、1800】的员工,显示员工姓名和薪资
select name, sal from emp where sal in(1400,1600,1800);
+--------+------+
| name | sal |
+--------+------+
| 王海涛 | 1800 |
| 刘沛霞 | 1400 |
+--------+------+
21、?查询薪资不为1400、1600、1800的员工,显示员工姓名和薪资
select name, sal from emp where sal not in(1400,1600,1800);
+--------+------+
| name | sal |
+--------+------+
| 齐雷 | 2500 |
| 陈子枢 | 4500 |
| 刘昱江 | 2600 |
| 王克晶 | 3700 |
| 苍老师 | 4850 |
| 范传奇 | 3200 |
| 刘涛 | 2700 |
| 韩少云 | 5000 |
| 董长春 | 3200 |
| 张久军 | 4200 |
+--------+------+
22、查询emp表中薪资大于4000和薪资小于2000的员工,显示员工姓名、薪资。
select name, sal from emp where sal>4000 or sal<2000;
+--------+------+
| name | sal |
+--------+------+
| 王海涛 | 1800 |
| 刘沛霞 | 1400 |
| 陈子枢 | 4500 |
| 苍老师 | 4850 |
| 韩少云 | 5000 |
| 张久军 | 4200 |
+--------+------+
23、查询emp表中薪资大于3000并且奖金小于600的员工,显示员工姓名、薪资、奖金。
select name, sal, ifnull(bonus,0) 奖金 from emp where sal>3000 and ifnull(bonus,0)<600;
+--------+------+------+
| name | sal | 奖金 |
+--------+------+------+
| 苍老师 | 4850 | 500 |
| 韩少云 | 5000 | 0 |
| 董长春 | 3200 | 300 |
| 张久军 | 4200 | 500 |
+--------+------+------+
24、查询没有部门的员工(即部门列为null值)
select name,ifnull(dept,'无') 部门 from emp where dept is null;
+--------+------+
| name | 部门 |
+--------+------+
| 韩少云 | 无 |
+--------+------+
25、查询有部门的员工(即部门列不为null值)
select name,ifnull(dept,'无') 部门 from emp where dept is not null;
+--------+--------+
| name | 部门 |
+--------+--------+
| 王海涛 | 培优部 |
| 齐雷 | 培优部 |
| 刘沛霞 | 培优部 |
| 陈子枢 | 培优部 |
| 刘昱江 | 培优部 |
| 王克晶 | 就业部 |
| 苍老师 | 就业部 |
| 范传奇 | 就业部 |
| 刘涛 | 就业部 |
| 董长春 | 培优部 |
| 张久军 | 培优部 |
+--------+--------+
? ?%:是通配符,可以表示0个或多个任意字符
?? ?_:是通配符,只能表示1个任意字符
26、查询emp表中姓名中以"刘"字开头的员工,显示员工姓名。
select name from emp where name like '刘%';
+--------+
| name |
+--------+
| 刘沛霞 |
| 刘昱江 |
| 刘涛 |
+--------+
27、列出emp表中姓名以'王'开头的员工,显示员工姓名?
select name from emp where name like '王%';
+--------+
| name |
+--------+
| 王海涛 |
| 王克晶 |
+--------+
28、?列出emp表中姓名以'涛'结尾的员工,显示员工姓名
select name from emp where name like '%涛';
+--------+
| name |
+--------+
| 王海涛 |
| 刘涛 |
+--------+
27、查询emp表中姓名中包含"涛"字的员工,显示员工姓名。
select name from emp where name like '%涛%';
+--------+
| name |
+--------+
| 王海涛 |
| 刘涛 |
+--------+
28、查询emp表中姓名以"刘"开头,并且姓名为两个字的员工,显示员工姓名。
select name from emp where name like '刘_';
+------+
| name |
+------+
| 刘涛 |
+------+
多行函数也叫聚合函数(聚集函数),常见的多行函数有:
?? ?(多行函数会默认过滤null值,即不统计null值)
?? ??? ?count(列名 | *):
?? ??? ??? ?count(列名):表示统计当前列的值有多少个
?? ??? ??? ?count(*):以行为单位,统计查询结果中有多少行记录
29、统计emp表中薪资大于3000的员工个数
select count(name) 薪资大于3000人数 from emp where sal>3000;
+------------------+
| 薪资大于3000人数 |
+------------------+
| 7 |
+------------------+
30、?统计emp表中的所有的男员工的人数。
select count(name)男员工数量 from emp where gender='男';
+------------+
| 男员工数量 |
+------------+
| 10 |
+------------+
31、统计每个职位的人数, 显示职位和对应人数
select job,count(name) 人数 from emp group by job;
+------+------+
| job | 人数 |
+------+------+
| CEO | 1 |
| 总监 | 2 |
| 讲师 | 9 |
+------+------+
?30、求emp表中的最高薪资
select max(sal) 最高薪资 from emp;
+----------+
| 最高薪资 |
+----------+
| 5000 |
+----------+
31、统计emp表中所有员工的薪资总和(不包含奖金)
select sum(sal) 薪资总和 from emp;
+----------+
| 薪资总和 |
+----------+
| 39650 |
+----------+
32、统计emp表员工的平均薪资(不包含奖金)
select avg(sal) 平均薪资 from emp;
+--------------------+
| 平均薪资 |
+--------------------+
| 3304.1666666666665 |
+--------------------+
33、统计emp表员工的平均总薪资(包含奖金)
select avg(sal + ifnull(bonus,0)) 平均薪资 from emp;
+--------------------+
| 平均薪资 |
+--------------------+
| 3795.8333333333335 |
+--------------------+
?34、统计emp表中所有员工奖金的平均值
select avg(ifnull(bonus,0)) 平均奖金 from emp;
+-------------------+
| 平均奖金 |
+-------------------+
| 491.6666666666667 |
+-------------------+
?35、按照员工年龄从小到大排序,显示姓名、出生年月、总薪资(薪资+奖金)
select name,birthday ,sal+ifnull(bonus,0) 总薪资 from emp order by birthday desc;
+--------+------------+--------+
| name | birthday | 总薪资 |
+--------+------------+--------+
| 范传奇 | 1999-09-18 | 3900 |
| 王克晶 | 1998-07-18 | 4400 |
| 刘沛霞 | 1996-06-14 | 1800 |
| 苍老师 | 1995-08-18 | 5350 |
| 王海涛 | 1995-03-25 | 2200 |
| 齐雷 | 1994-04-06 | 3200 |
| 刘昱江 | 1993-11-18 | 3200 |
| 陈子枢 | 1991-05-18 | 5100 |
| 刘涛 | 1990-10-18 | 3200 |
| 张久军 | 1989-01-11 | 4700 |
| 董长春 | 1988-02-05 | 3500 |
| 韩少云 | 1980-12-18 | 5000 |
+--------+------------+--------+
36、?查询下个月过生日的所有员工,显示员工姓名和出生日期
select name,birthday from emp where month(birthday)=month(curdate())+1;
+--------+------------+
| name | birthday |
+--------+------------+
| 陈子枢 | 1991-05-18 |
+--------+------------+
37、?求1995年入职的员工信息
select * from emp where year(birthday)=1995;
+----+--------+--------+------------+--------+------+------+-------+
| id | name | gender | birthday | dept | job | sal | bonus |
+----+--------+--------+------------+--------+------+------+-------+
| 1 | 王海涛 | 男 | 1995-03-25 | 培优部 | 讲师 | 1800 | 400 |
| 7 | 苍老师 | 男 | 1995-08-18 | 就业部 | 总监 | 4850 | 500 |
+----+--------+--------+------------+--------+------+------+-------+
语法:SELECT 列 | * FROM 表名 [WHERE子句] GROUP BY 列;
33、对emp表,按照部门对员工进行分组,查看分组后效果
select ifnull(dept,'无') 部门, count(*) 人数 from emp group by dept;
+--------+------+
| 部门 | 人数 |
+--------+------+
| 无 | 1 |
| 培优部 | 7 |
| 就业部 | 4 |
+--------+------+
34、对emp表按照职位进行分组,并统计每个职位的人数,显示职位和对应人数
select job,count(job) 人数 from emp group by job;
+------+------+
| job | 人数 |
+------+------+
| CEO | 1 |
| 总监 | 2 |
| 讲师 | 9 |
+------+------+
35、对emp表按照部门进行分组,求每个部门的最高薪资(不包含奖金),显示部门名称和最高薪资
select ifnull(dept,'CEO') 部门,max(sal) 最高薪资 from emp group by dept;
+--------+----------+
| 部门 | 最高薪资 |
+--------+----------+
| CEO | 5000 |
| 培优部 | 4500 |
| 就业部 | 4850 |
+--------+----------+
36、对emp表中所有员工的薪资进行升序(从低到高)排序,显示员工姓名、薪资。
select name,sal from emp order by sal asc;
+--------+------+
| name | sal |
+--------+------+
| 刘沛霞 | 1400 |
| 王海涛 | 1800 |
| 齐雷 | 2500 |
| 刘昱江 | 2600 |
| 刘涛 | 2700 |
| 董长春 | 3200 |
| 范传奇 | 3200 |
| 王克晶 | 3700 |
| 张久军 | 4200 |
| 陈子枢 | 4500 |
| 苍老师 | 4850 |
| 韩少云 | 5000 |
+--------+------+
37、对emp表中所有员工的奖金进行降序(从高到低)排序,显示员工姓名、奖金。
select name,ifnull(bonus,0) 奖金 from emp order by ifnull(bonus,0) desc;
+--------+------+
| name | 奖金 |
+--------+------+
| 齐雷 | 700 |
| 王克晶 | 700 |
| 范传奇 | 700 |
| 陈子枢 | 600 |
| 刘昱江 | 600 |
| 张久军 | 500 |
| 刘涛 | 500 |
| 苍老师 | 500 |
| 刘沛霞 | 400 |
| 王海涛 | 400 |
| 董长春 | 300 |
| 韩少云 | 0 |
+--------+------+
在mysql中,通过limit进行分页查询,查询公式为:?`limit (页码-1)*每页显示记录数, 每页显示记录数`
38、查询emp表中的所有记录,分页显示:每页显示3条记录,返回所有页的数据
select * from emp limit 0,3;
+----+--------+--------+------------+--------+------+------+-------+
| id | name | gender | birthday | dept | job | sal | bonus |
+----+--------+--------+------------+--------+------+------+-------+
| 1 | 王海涛 | 男 | 1995-03-25 | 培优部 | 讲师 | 1800 | 400 |
| 2 | 齐雷 | 男 | 1994-04-06 | 培优部 | 讲师 | 2500 | 700 |
| 3 | 刘沛霞 | 女 | 1996-06-14 | 培优部 | 讲师 | 1400 | 400 |
+----+--------+--------+------------+--------+------+------+-------+
39、求emp表中薪资最高的前3名员工的信息,显示姓名和薪资
? ? ? ?--根据薪资降序排序,每页显示3条,查询第一页就是薪资最高的前三名
select name,sal from emp order by sal desc limit 0,3;
+--------+------+
| name | sal |
+--------+------+
| 韩少云 | 5000 |
| 苍老师 | 4850 |
| 陈子枢 | 4500 |
+--------+------+
curdate() -- 获取当前日期: 年月日
curtime() -- 获取当前时间: 时分秒
sysdate()/now() -- 获取当前日期+时间, 年月日 时分秒
year('2020-8-10'): 返回日期中的年份, 2020
month('2020-8-10'): 返回日期中的月份, 8
day('2020-8-10'): 返回日期中的天数, 10
hour('2020-8-10 12:34:56'): 返回时间中的小时, 12
minute('2020-8-10 12:34:56'): 返回时间中的分钟数, 34
second('2020-8-10 12:34:56'): 返回时间中的秒值, 56
------------------------------------------
concat(s1,s2,...sn): 将 s1、s2、...sn 拼接在一起返回
例如: name('王海涛'),birthday('1995-03-25'),sal(2450)
select concat('王海涛', '1995-03-25', 2450);
concat_ws(x,s1,s2,...sn):
将 s1、s2、...sn 拼接在一起,并且每两个拼接时会通过x作为分隔符进行拼接,再返回
select concat_ws(',' ,'王海涛', '1995-03-25', 2450);
40、查询emp表中所有【在1993和1995年之间出生】的员工,显示姓名、出生日期。
select name,birthday from emp where year(birthday) between 1993 and 1995;
+--------+------------+
| name | birthday |
+--------+------------+
| 王海涛 | 1995-03-25 |
| 齐雷 | 1994-04-06 |
| 刘昱江 | 1993-11-18 |
| 苍老师 | 1995-08-18 |
+--------+------------+
41、查询emp表中本月过生日的所有员工
select name,month(birthday) 出生月份 from emp where month(birthday)= month(curdate());
+------+----------+
| name | 出生月份 |
+------+----------+
| 齐雷 | 4 |
+------+----------+
42、查询emp表中员工的姓名和薪资(薪资格式为: xxx(元) )
select name, concat(sal,'(元)') from emp;
+--------+--------------------+
| name | concat(sal,'(元)') |
+--------+--------------------+
| 王海涛 | 1800(元) |
| 齐雷 | 2500(元) |
| 刘沛霞 | 1400(元) |
| 陈子枢 | 4500(元) |
| 刘昱江 | 2600(元) |
| 王克晶 | 3700(元) |
| 苍老师 | 4850(元) |
| 范传奇 | 3200(元) |
| 刘涛 | 2700(元) |
| 韩少云 | 5000(元) |
| 董长春 | 3200(元) |
| 张久军 | 4200(元) |
+--------+--------------------+
43、查询emp表中员工的姓名和薪资(薪资格式为: xxx/元 )
select name, concat(sal,'/元') from emp;
+--------+-------------------+
| name | concat(sal,'/元') |
+--------+-------------------+
| 王海涛 | 1800/元 |
| 齐雷 | 2500/元 |
| 刘沛霞 | 1400/元 |
| 陈子枢 | 4500/元 |
| 刘昱江 | 2600/元 |
| 王克晶 | 3700/元 |
| 苍老师 | 4850/元 |
| 范传奇 | 3200/元 |
| 刘涛 | 2700/元 |
| 韩少云 | 5000/元 |
| 董长春 | 3200/元 |
| 张久军 | 4200/元 |
+--------+-------------------+
准备数据: 以下练习将使用db30库中的表及表记录,请先进入db30数据库!!!
44、
?
?
?
?
?
?
?
?
?
?
?
?
?
?
本文转载自微信公众号「Linux开发那些事儿」,作者 LinuxThings 。转载本文请联...
默认情况下,Map输出的结果会对Key进行默认的排序,但是有时候需要对Key排序的同...
自定义View从源码到应用 Android进阶的书看了一遍又一遍奈何总是看了又忘忘了又...
Intro# 由于项目需要,需要在基于 asp.net mvc 的 Web 项目框架中做权限的控制,...
一台电脑上有一个ssh key,在github上提交代码,由于其他原因 你可能会在一台电...
今天在用tp5做项目的时候发现,前台是可以绑定默认到index模块的,但是后台不好...
参考了Egret Wing,想像Egret Wing那样在上方titlebar最右边上面增加一个menu(这...
Git 服务器搭建 上一章节中我们远程仓库使用了 Github,Github 公开的项目是免费...
想了解更多内容,请访问: 51CTO和华为官方战略合作共建的鸿蒙技术社区 https://...
推荐阅读: 大早上更新了Visual Studio 2019 试用一下 一.前言 Visual Studio 20...