MySQL 从入门到入魔 (02)

2021 年 12 月 17 日
本文字数：5057 字
阅读完需：约 17 分钟

练习:

创建数据库 mydb4 字符集 utf8 并使用

create database mydb4 character set utf8;  use mydb4;

复制代码

在数据库中创建员工表 emp 字段:id,name,sal,deptId(部门 id) 字符集 utf8

create table emp(id int,name varchar(10),sal int,deptId int)charset=utf8;

复制代码

创建部门表 dept 字段:id,name,loc(部门地址) 字符集 utf8

create table dept(id int,name varchar(10),loc varchar(20))charset=utf8;

复制代码

部门表插入以下数据: 1 神仙部天庭 2 妖怪部盘丝洞

insert into dept values(1,'神仙部','天庭'),(2,'妖怪部','盘丝洞');

复制代码

员工表插入一下数据: 1 悟空 5000 1 , 2 八戒 2000 1 , 3 蜘蛛精 8000 2, 4 白骨精 9000 2

insert into emp values(1,'悟空',5000,1),(2,'八戒',2000,1),(3,'蜘蛛精',8000,2),(4,'白骨精',9000,2);

复制代码

查询工资 6000 以下的员工姓名和工资

 select name,sal from emp where sal<6000;

复制代码

修改神仙部的名字为取经部

update dept set name='取经部' where id=1;

复制代码

给员工添加奖金 comm 字段

 alter table emp add comm int;

复制代码

修改部门 id 为 1 的部门奖金为 500

 update emp set comm=500 where deptId=1;

复制代码

把取经部的地址改成五台山

update dept set loc='五台山' where id=1;

复制代码

删除两个表

drop table emp;  drop table dept;

复制代码

主键约束 primary key

主键: 表示数据唯一性的字段称为主键
约束: 约束是创建表时给表字段添加的限制条件
主键约束: 限制主键的值唯一且非空
测试:

create table t1(id int primary key,name varchar(10));    insert into t1 values(1,'aaa');    insert into t1 values(1,'bbb');   //报错 主键值不能重复 Duplicate entry '1' for key 'PRIMARY'    insert into t1 values(null,'ccc');//报错 主键值不能为null Column 'id' cannot be null

复制代码

###主键+自增 auto_increment

自增规则:从历史最大值+1
测试:

create table t2(id int primary key auto_increment,name varchar(10));    insert into t2 values(null,'aaa');           1    insert into t2 values(null,'bbb');           2    insert into t2 values(10,'ccc');             10    insert into t2 values(null,'ddd');           11    delete from t2 where id>=10;    insert into t2 values(null,'eee');           12

复制代码

导入*.sql 文件

把下载的 emp.sql 放到 d 盘根目录
source d:/emp.sql;
如果导入后执行 select * from emp 发现有乱码执行 set names gbk;

去重 distinct

查询员工表中有哪些不同的工作

select distinct job from emp;

复制代码

is null 和 is not null

判断某个字段的值为 null 时不能使用=

查询没有上级领导的员工信息

select * from emp where mgr is null;

复制代码

查询有上级领导的员工姓名和上级领导编号

select ename,mgr from emp where mgr is not null;

复制代码

比较运算符 > < >= <= = !=和<>

查询员工工资小于等于 3000 的员工姓名和工资

select ename,sal from emp where sal<=3000;

复制代码

查询工作不是程序员的员工姓名和工作(两种写法)

select ename,job from emp where job!='程序员';  select ename,job from emp where job<>'程序员';

复制代码

and 和 or

如果查询数据时使用了多个条件,多个条件同时满足使用 and, 多个条件满足一个就可以使用 or
and 类似 java 中的 && , or 类似 java 中的||

查询 1 号部门工资大于 1500 的员工信息

select * from emp where deptno=1 and sal>1500;

复制代码

查询工作是人事或者工资大于 3000 的员工姓名,工作和工资.

select ename,job,sal from emp where job='人事' or sal>3000;

复制代码

in(x,y,z)

当查询某个字段的值为多个的时候使用 in 关键字

查询工资为 1500,3000,5000 的员工信息

select * from emp where sal=1500 or sal=3000 or sal=5000;  select * from emp where sal in(1500,3000,5000);

复制代码

between x and y

查询工资在 1000 到 2000 之间的员工信息(包括 1000 和 2000)

select * from emp where sal>=1000 and sal<=2000;  select * from emp where sal between 1000 and 2000;

复制代码

查询工资在 1000 到 2000 以外的员工信息

select * from emp where sal not between 1000 and 2000;

复制代码

查询工资不等于 5000,3000,800 的员工信息

select * from emp where sal not in(800,3000,5000);

复制代码

综合练习

查询有上级领导并且是 3 号部门的员工信息

select * from emp where mgr is not null and deptno=3;

复制代码

查询 2 号部门工资在 1000 到 2000 之间的员工姓名工资和部门编号

select ename,sal,deptno from emp where deptno=2 and sal between 1000 and 2000;

复制代码

查询 1 号部门工资为 800 和 1600 的员工信息

select * from emp where deptno=1 and sal in(800,1600);

复制代码

查询 1 号和 2 号部门工资高于 2000 的员工信息

select * from emp where deptno in(1,2) and sal>2000;

复制代码

查询员工表中出现的部门编号有哪几个

select distinct deptno from emp;

复制代码

模糊查询 like

_:代表 1 个未知字符
%:代表 0 或多个未知字符
举例:
以 x 开头 x%
以 x 结尾 %x
包含 x %x%
第二个字符是 x _x%
倒数第三个是 x %x__
以 x 开头倒数第二个是 y x%y_

查询员工表中姓孙的员工姓名

select ename from emp where ename like '孙%';

复制代码

查询名字以精结尾的员工信息

select * from emp where ename like '%精';

复制代码

查询工作中包含销售的员工姓名和工作

select ename,job from emp where job like '%销售%';

复制代码

排序 order by

格式: order by 字段 asc(默认升序)/desc 降序写在 SQL 语句的后面

查询工资高于 2000 的员工信息,按照工资升序排序

select * from emp where sal>2000 order by sal;

复制代码

查询每个员工的姓名,工资和部门编号,按照部门编号降序排序

select ename,sal,deptno from emp order by deptno desc;

复制代码

多字段排序,在 order by 后面写多个字段通过逗号分隔

查询每个员工的姓名,工资和部门编号,按照部门编号降序排序,如果部门编号一致则按照工资降序排序

select ename,sal,deptno from emp order by deptno desc,sal desc;

复制代码

查询 1 号和 2 号部门的员工信息按照部门编号升序排序,如果部门编号一致则按照工资降序排序

select * from emp where deptno in(1,2) order by deptno, sal desc;

复制代码

分页查询

格式: limit 跳过的条数,请求条数(也代表每页条数) , 写在 SQL 语句的最后

请求员工表中按照工资降序排序前 3 条数据(请求第一页的 3 条数据)

select * from emp order by sal desc limit 0,3;

复制代码

查询员工表中工资降序排序的第 4 5 6 条数据

select * from emp order by sal desc limit 3,3;

复制代码

查询员工表中第三页的 2 条数据(请求第 5 和第 6 条数据)

select * from emp limit 4,2;

复制代码

查询工资最低的员工信息

select * from emp order by sal limit 0,1;

复制代码

查询员工表中工资升序第四页的 2 条数据

select * from emp order by sal limit 6,2;

复制代码

综合练习题

查询员工表中名字里第二个字是八的员工姓名和工资

select ename,sal from emp where ename like '_八%';

复制代码

查询工作中包含售字的员工姓名和工作

select ename,job from emp where job like '%售%';

复制代码

查询工资高于 1000 块钱的员工姓名和工资,按照工资降序排序,查询第二页的 3 条数据

select ename,sal from emp where sal>1000 order by sal desc limit 3,3;

复制代码

数值计算+ - * /

查询每个员工的姓名,工资和年终奖(3 个月的工资)

select ename,sal,sal*3 from emp;

复制代码

别名,可以对查询的字段起别名

select ename as '名字',sal as '工资',sal*3 as '年终奖' from emp;  select ename '名字',sal '工资',sal*3 '年终奖' from emp;  select ename 名字,sal 工资,sal*3 年终奖 from emp;

复制代码

查询每个员工姓名,工资和涨薪 5 块钱之后的工资

select ename,sal,sal+5 涨薪后 from emp;

复制代码

聚合函数

聚合函数是对查询的多条数据进行统计查询,包括:求平均值,最大值,最小值,求和,计数
平均值 avg

查询 1 号部门的平均工资

select avg(sal) from emp where deptno=1;

复制代码

查询程序员的平均工资

select avg(sal) from emp where job='程序员';

复制代码

最大值 max

查询 3 号部门的最高工资

select max(sal) from emp where deptno=3;

复制代码

最小值 min

查询销售的最低工资

select min(sal) from emp where job='销售';

复制代码

求和 sum

查询 2 号部门的工资总和

select sum(sal) from emp where deptno=2;

复制代码

计数 count

查询工资高于 2000 的员工人数

select count(*) from emp where sal>2000;

复制代码

查询 2 号部门的人数

select count(*) from emp where deptno=2;

复制代码

练习题

查询员工表中工资高于 2000 的员工姓名和工资,按照工资升序排序,查询第二页的 2 条数据

select ename,sal from emp where sal>2000 order by sal  limit 2,2;

复制代码

查询和销售相关的工作的工资总和

select sum(sal) from emp where job like '%销售%';    6625

复制代码

查询程序员人数

select count(*) from emp where job='程序员';      2

复制代码

查询 1 号部门中有领导的员工中的最高工资

select max(sal) from emp where deptno=1 and mgr is not null;

复制代码

查询 2 号部门的最高工资和最低工资起别名

select max(sal) 最高工资,min(sal) 最低工资 from emp where deptno=2;

复制代码

查询 1 号部门里面名字中包含空字的员工姓名

select ename from emp where deptno=1 and ename like '%空%';

复制代码

分组查询

题目需求中每个 xx 就以 xx 作为分组的字段
查询员工表的平均工资

select avg(sal) from emp;

复制代码

查询 1 号部门的平均工资

select avg(sal) from emp where deptno=1;

复制代码

查询每个部门的平均工资

select deptno,avg(sal) from emp group by deptno;

复制代码

查询每个工作的最高工资

select job,max(sal) from emp group by job;

复制代码

查询每个部门的人数

select deptno,count(*) from emp group by deptno;

复制代码

查询每种工作的工资总和

select job,sum(sal) from emp group by job;

复制代码

查询每个部门工资高于 1000 块钱的员工人数

select deptno,count(*) from emp where sal>1000 group by deptno;

复制代码

查询 1 号和 2 号部门的最高工资

select deptno,max(sal) from emp where deptno in(1,2) group by deptno;

复制代码

查询 1 号和 2 号部门中每种工作的工资总和

select job,sum(sal) from emp where deptno in(1,2) group by job;

复制代码

having

where 后面只能写普通字段条件,聚合函数不能写在 where 后面
having 后面专门写聚合函数的条件,而且是和分组查询结合使用
各个关键字的顺序: select .....from 表名 where 普通字段条件 group by 分组字段名 having 聚合函数条件 order by 排序字段名 limit ...;

查询每个部门的平均工资,只查询平均工资高于 2000 的信息

select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;

复制代码

查询每种工作的人数,只查询人数为 1 的信息

select job,count(*) from emp group by job having count(*)=1;

复制代码

查询每个部门的平均工资,只查询工资在 1000 到 3000 之间的,并且过滤掉平均工资低于 2000 的信息.

select deptno,avg(sal) from emp where sal between 1000 and 3000 group by deptno having avg(sal)>=2000;

复制代码

综合练习

查询没有上级领导的员工编号 empno，姓名，工资select empno,ename,sal from emp where mgr is null;
查询有奖金的员工姓名和奖金select ename,comm from emp where comm>0;
查询名字中包含精的员工姓名和工资select ename,sal from emp where ename like '%精%';
查询名字中第二个字是八的员工信息select * from emp where ename like '_八%';
查询 1 号部门工资大于 2000 的员工信息select * from emp where deptno=1 and sal>2000;
查询 2 号部门或者工资低于 1500 的员工信息select * from emp where deptno=2 or sal<1500;
查询工资为 3000，1500，5000 的员工信息按照工资升序排序select * from emp where sal in(3000,1500,5000) order by sal;
查询 3 号部门的工资总和select sum(sal) from emp where deptno=3;
查询每个部门工资大于 1000 的员工人数，按照人数升序排序select deptno,count() from emp where sal>1000 group by deptno order by count();
查询每种工作中有领导的员工人数按照人数降序排序select job,count() from emp where mgr is not null group by job order by count() desc;
查询所有员工信息，按照部门编号升序排序，如果部门编号一致则工资降序select * from emp order by deptno,sal desc;
查询有领导的员工，每个部门的编号和最高工资select deptno,max(sal) from emp where mgr is not null group by deptno;
查询有领导的员工，按照工资升序排序，第 3 页的 2 条数据select * from emp where mgr is not null order by sal limit 4,2;
查询每个部门的工资总和，只查询有上级领导的员工并且要求工资总和大于 5400，最后按照工资总和降序排序,只查询结果中的第一条数据deptno,sum(sal) from emp where mgr is not null group by deptno having sum(sal)>5400 order by sum(sal) desc limit 0,1;

发布于: 2 小时前阅读数: 6

原文链接:【http://xie.infoq.cn/article/1cbb58c64a776b94b53abf2a5】。

海拥(haiyong.site)

关注

公众号：海拥 2021.11.29 加入

【个人网站】haiyong.site 【软件技能】Java，Python，JS 【兴趣爱好】学习使我快乐，编程令我永生【个人称号】HDZ核心组成员,CSDN原力作者

发布

暂无评论

创作场景

MySQL 从入门到入魔 (02)

练习:

主键约束 primary key

导入*.sql 文件

去重 distinct

is null 和 is not null

比较运算符 > < >= <= = !=和<>

and 和 or

in(x,y,z)

between x and y

综合练习

模糊查询 like

排序 order by

分页查询

综合练习题

数值计算+ - * /

聚合函数

练习题

分组查询

having

综合练习

海拥(haiyong.site)

评论