Mysql综合案例一

目录

一、DDL练习

代码实现

二、DML练习

代码实现

三、DQL练习

DQL练习1-学生表

代码实现

DQL练习2-员工表

代码实现

总结

一、DDL练习

  1. 创建数据库sql1、sql2
  2. 删除数据库sql2
  3. 使用数据库sql1
  4. 创建学生表：id、姓名
  5. 删除学生表

代码实现

-- 创建数据库sql1、sql2
create database sql1;
create database sql2;

-- 删除数据库sql2
drop database sql2;

-- 使用数据库sql1
use sql1;

-- 创建学生表：id、姓名
create table if not exists student (
id int,  -- 学生id
name varchar(20)  -- 学生姓名
);

-- 删除学生表
drop table student;

二、DML练习

  1. 创建一个数据库mydb
  2. 使用mydb数据库
  3. 创建一个员工表employee 列为 员工编号id 员工姓名name 员工性别gender 员工薪资salary
  4. 插入数据：张三 男 2000 李四 男 1000 李红 女 4000
  5. 更新数据：所有人工资变成5000
  6. 更新数据：张三薪资变成3000id变成0
  7. 更新数据：所有人工资再加1000
  8. 删除数据：删除李红的数据
  9. 清空表

代码实现

-- 创建数据库
create database mydb;

使用mydb数据库
use mydb;

-- 创建一个员工表employee 列为 员工编号id 员工姓名name 员工性别gender 员工薪资salary
create table employee(
id int,  -- 员工id
name varchar(20),  -- 员工姓名
gender varchar(5), -- 员工性别
salary int  -- 员工薪资
);

-- 插入数据:张三 男 2000 李四 男 1000 李红 女 4000
insert into employee(name,gender,salary) values
('张三','男',2000),
('李四','男',1000),
('李红','女',4000);

-- 更新数据：所有人工资变成5000
update employee set salary=5000;

-- 更新数据：张三薪资变成3000id变成0
update employee set salary=3000,id=0 where name='张三';

-- 更新数据：所有人工资再加1000
update employee set salary=salary+1000;

-- 删除数据：删除李红的数据
delete from employee where name='李红';

-- 清空表
drop table employee;

三、DQL练习

DQL练习1-学生表

创建如下学生表 

create table student(
id int,
name varchar(20),
gender varchar(20),
chinese int,
math int,
english int
);		

insert into student values 
(1,'张明','男',89,78,90),
(2,'李进','男',67,53,95),
(3,'王五','女',87,78,77),
(4,'李一','女',88,98,92),
(5,'李财','男',82,84,67),
(6,'张宝','男',55,85,45),
(7,'黄蓉','女',75,65,30),	
(7,'黄蓉','女',75,65,30);

 完成下列要求

  1. -- 查询表中所有学生的信息
  2. -- 查询表中所有学生的姓名和英语成绩
  3. -- 过滤表中的重复数据
  4. -- 统计每个学生的总分
  5. -- 所有学生总分上加上10
  6. -- 使用别名表示学生分数
  7. -- 查询英语成绩大于90分的所有同学
  8. -- 查询总分大于200分的所有同学
  9. -- 查询英语成绩在80到90之间的所有同学
  10. -- 查询英语成绩不在80到90之间的所有同学
  11. -- 查询数学分数为89，90，91的同学
  12. -- 查询数学分数不为89,90,91的同学
  13. -- 查询所有姓李的学生英语成绩
  14. -- 查询数学分80且语文80分的同学
  15. -- 查询英语80或者总分200的同学
  16. -- 对学生数学成绩进行降序排序后输出
  17. -- 对总分从高到低的顺序输出
  18. -- 对姓李的学生按总成绩升序排序输出
  19. -- 查询男生和女生分别有多少人，并将人数降序输出
  20. -- 查询男生和女生分别有多少人，并将人数降序输出，并查询出大于9的性别人数信息

代码实现

-- 查询表中所有学生的信息
select * from student;

-- 查询表中所有学生的姓名和英语成绩
select name,english from student;

-- 过滤表中的重复数据
select distinct * from student; 
            -- distinct只能过滤一次

-- 统计每个学生的总分
select name,chinese+english+math as total_score from student; 
            -- 列用sum行用算术运算符即可

-- 所有学生总分上加上10
select name,(chinese+english+math)+10 from student; 
            --由于本身没有总分所以先弄出总分再加10，配用运算符

-- 使用别名表示学生分数
select name '姓名'，chinese '语文'，english '英语' , math '数学' from student;

-- 查询英语成绩大于90分的所有同学
select * from where english>90;

-- 查询总分大于200分的所有同学
select name,math+chinese+english as total_score from student where math+chinese+english>200;
            -- where后面不能直接用别名total_score是因为where优先于select,此时还没有total_score这个别名

-- 查询英语成绩在80到90之间的所有同学
select * from student where english>=80 && english<=90;

-- 查询英语成绩不在80到90之间的所有同学
select * from student where english<80 || english >90;
select * from student where !(english>=80 && english <=90);   -- !和not一样

--  查询数学分数为89，90，91的同学
select * from student where math in(89,90,91);

-- 查询数学分数不为89,90,91的同学
select * from student where math not in(89,90,91);

-- 查询所有姓李的学生英语成绩
select * from student where name like '李%';
            -- manual 手动 corresponds 对应 syntax 语法  报错

-- 查询数学分80且语文80分的同学
select * from student where math=80 && chinese=80;

-- 查询英语80或者总分200的同学
select * from student where english=80 || (math + english + chinese)=200;

-- 对学生数学成绩进行降序排序后输出
select * from student order by math desc;

-- 对总分从高到低的顺序输出
select * from student order by (math+english+chinese) desc;

-- 对姓李的学生按总成绩升序排序输出
select * from student where name like '李%' order by  (math+english+chinese) asc;  
            -- 注意sql的书写顺序

-- 查询男生和女生分别有多少人，并将人数降序输出
select gender,count(*)as rs from student group by gender  order by rs desc ; 

-- 查询男生和女生分别有多少人，并将人数降序输出，并查询出大于9的性别人数信息
select gender,count(*) as rs from student group by gender having rs>9 order by rs desc;

执行顺序
--select from where group by having order by limit

DQL练习2-员工表

创建如下员工表

create table emp(
empno int,  -- 员工编号
ename varchar(20),  -- 员工名字
job varchar(20),  -- 工作名字
mgr int,  -- 上级领导编号
hiredate date,  -- 入职日期
sal int,  -- 薪资
comm int,  -- 奖金
deptno int  -- 部门编号
);

insert into emp values
(7369,'SMITH','CLERK',7902,'1980-12-17',800,null,20),
(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30),
(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30),
(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20),
(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30),
(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30),
(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,20),
(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20),
(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10),
(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30),
(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20),
(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30),
(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20),
(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);

完成下列要求

  1. -- 按员工编号升序排列不在10号部门工作的员工信息
  2. -- 查询姓名第二个字母不是'A'且薪水大于1000元的员工信息，按薪水降序排列
  3. -- 按年薪+奖金降序排序
  4. -- 求每个部门的平均薪水
  5. -- 求每个部门的最高薪水
  6. -- 求每个部门每个岗位的最高薪水并美观
  7. -- 求平均薪水大于2000的部门编号
  8. -- 将部门平均薪水大于1500的部门编号列出来，按部门平均薪水降序排列
  9. -- 选择公司中有奖金和员工姓名和工资
  10. -- 查询员工最高工资和最低工资的差距

代码实现

-- 按员工编号升序排列不在10号部门工作的员工信息
select * from emp where deptno!=10 order by empno asc;

-- 查询姓名第二个字母不是'A'且薪水大于1000元的员工信息，按薪水降序排列
select * from emp where ename not like  '_A%' && sal>1000 order by sal desc;

-- 按年薪+奖金降序排序
select * from emp where ename not like  '_A%' && sal>1000 order by (sal*12 + ifnull(comm,0))desc;
            -- ifnull的用法 如果comm为null，则当作0来计算，不为null就是原来值计算
            --格式ifnull(,)

-- 求每个部门的平均薪水 
select deptno,avg(sal) from emp group by deptno;
            -- 审题不清,接下来平均薪资降序并降序，接下来改正
select deptno,avg(sal) as avg_sal from emp group by deptno order by avg_sal desc;
            -- 要明白oder by 和 limit 是在select之后运行的,所以select后面的别名可以识别到。

-- 求每个部门的最高薪水
select deptno,max(sal) from emp group by deptno;

-- 求每个部门每个岗位的最高薪水并美观
select deptno,job,max(sal) from emp group by deptno,job order by deptno asc;
            -- 有分组查询出现的时候 select后面只能跟分组字段和聚合函数的字段

-- 求平均薪水大于2000的部门编号
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;
            -- 又考察到了执行顺序不能用where只能用having 分组之后的字段条件用having

-- 将部门平均薪水大于1500的部门编号列出来，按部门平均薪水降序排列
select deptno,avg(sal) from emp group by deptno having avg(sal)>1500 order by avg(sal) desc;

-- 选择公司中有奖金和员工姓名和工资
select ename,sal+comm as xs from emp where comm is not null;

-- 查询员工最高工资和最低工资的差距
select max(sal),min(sal),max(sal)-min(sal) as cj from emp;
            -- 考察到同一关键字内运行顺序,，接下来改正
select max(sal)-min(sal) as cj from emp;
            -- 正确,聚合查询或者其他可以一起使用成为一个

总结