基于emp的mysql查询

SQL命令

结构化查询语句：Structured Query Language

结构化查询语言是高级的非过程化变成语言，允许用户在高层数据结构上工作。是一种特殊目的的变成语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。

它不需要用户指定对数据的存放防范，也不需要用户了解具体的数据存放方式，所以具有完全不同底层结构的不同数据库系统，可以使用相同的结构化查询语言作为数据输入与管理的结构。数据化查询语句可以嵌套，这使它具有极大的灵猴性和强大的功能。

分类：

• DDL：创建数据库，创建表
• DML: 给创建好的表中添加数据的
• DQL: 查看数据–尽量模拟用户对软件的使用
• DCL: 授权的

问题：

上述四种类型的sql语句，哪个对于测试来讲最重要？

1+1 = ？

期望：2

实际结果：页面3 数据库2

那么就是bug，臭虫

1 准备工作

针对当前DBMS创建属于自己的数据库，数据库名字要求：

• 不要中文
• 不要有空格
• 不要数字开头
• 不要是已经存在的数据库名字

创建数据库：

• 通过命令进行创建
• 通过界面操作进行创建

创建表格：

• 通过命令进行创建

# 创建表 dept
create table dept(
	deptno int primary key auto_increment, -- 部门编号
	dname varchar(14) ,	  -- 部门名字
	loc varchar(13)   -- 地址
) ;
# 给dept中插入四条数据
insert into dept values(10,'财务部','北京');
insert into dept values(20,'研发部','上海');
insert into dept values(30,'销售部','广州');
insert into dept values(40,'行政部','深圳');


# 创建表emp
create table emp(
	empno int primary key auto_increment,-- 员工编号
	ename varchar(10), -- 员工姓名										-
	job varchar(9),	-- 岗位
	mgr int,	 -- 直接领导编号
	hiredate date, -- 雇佣日期，入职日期
	sal int, -- 薪水
	comm int,  -- 提成
	deptno int not null, -- 部门编号
	foreign key (deptno) references dept(deptno)
);

# 给emp表插入14条数据
insert into emp values(7369,'刘一','职员',7902,'1980-12-17',800,null,20);
insert into emp values(7499,'陈二','推销员',7698,'1981-02-20',1600,300,30);
insert into emp values(7521,'张三','推销员',7698,'1981-02-22',1250,500,30);
insert into emp values(7566,'李四','经理',7839,'1981-04-02',2975,null,20);
insert into emp values(7654,'王五','推销员',7698,'1981-09-28',1250,1400,30);
insert into emp values(7698,'赵六','经理',7839,'1981-05-01',2850,null,30);
insert into emp values(7782,'孙七','经理',7839,'1981-06-09',2450,null,10);
insert into emp values(7788,'周八','分析师',7566,'1987-06-13',3000,null,20);
insert into emp values(7839,'吴九','总裁',null,'1981-11-17',5000,null,10);
insert into emp values(7844,'郑十','推销员',7698,'1981-09-08',1500,0,30);
insert into emp values(7876,'郭十一','职员',7788,'1987-06-13',1100,null,20);
insert into emp values(7900,'钱多多','职员',7698,'1981-12-03',950,null,30);
insert into emp values(7902,'大锦鲤','分析师',7566,'1981-12-03',3000,null,20);
insert into emp values(7934,'木有钱','职员',7782,'1983-01-23',1300,null,10);

• 通过界面操作

2 DQL基础查询

数据查询语言：student：学生，class：班级

表与表之间关系：一对一，一对多，多对多

学生表对班级表：一个学生只有一个班级

班级表对学生表：一个班级对应多个学生

2.1 部门表字段解析

• emp：雇员表
  • empno：员工编号
  • ename：雇员名称
  • mgr：领导编号
  • job：岗位
  • sal：薪水
  • comm：奖金
  • hiredate：入职日期
  • deptno：部门编号
• dept：部门表
  • deptno：部门编号
  • dname：部门名称
  • loc：地址

2.2 单表简单查询

# 查看所在的数据库
select database();

# **********全查询
# 语法：select  什么 from 表名;
# 案例：查询emp表中所有的数据
select * from emp;  # 所有的列
# 案例：查询dept表中所有的数据
select * from dept;

# **********部分列查询
# 语法：select 列名1,列名2,列名3... from 表名;
# 案例：查询emp表中的员工姓名和员工工资
select ename,sal from emp ;
select * from emp;


# ********** 列名起别名
# 语法：select 列名 列的别名,列名2 列的别名 from 表名
# 案例：查询emp表中的员工姓名和员工工资，标题以姓名和工资显示
# mysql和oracle区别:oracle语法要比mysql严谨
select ename  '姓名', sal '工资' from emp;  # ****
select ename as  '姓名', sal as '工资' from emp;
select ename  姓名, sal 工资 from emp;


# ********** 限制查询
# 语法：select * from 表名 limit 开始的行，行数
# 案例：查询emp表中的第2行到第四行数据
select * from emp limit 1,3;

# ********** 排序查询
# 语法：select * from 表名 order by 列名 排序规则（asc：正向排序；desc：反向排序）;
# 案例：查询员工信息，工资按照降序排序
select * from emp order by sal desc;
# 案例：查询员工信息，部门按照升序排序，工资按照降序排序
select * from emp order by deptno asc,sal desc;

# ******** 去重查询
# 语法：select distinct(列名) from 表名;
# 案例：查询工作类型有哪些
select distinct(job) from emp;


# ********* 单条件查询
# 语法：select 列 from 表名 where 列名=值;
# 案例：查询张三的工资
select ename,sal from emp where ename = '张三';

# ********* 多条件查询
# 运算符：
# 	算数运算符：+ - * /
# 	逻辑运算符：and or not
# 	比较运算符：> < >= <= !=
# 语法：select 列 from 表 where 条件1 条件2 条件3;
# 案例：查询张三和李四1的工资
select ename,sal from emp where ename='张三' and ename='李四1';
select ename,sal from emp where ename='张三' or ename='李四1';
# 案例：查询工资大于1000小于3000的员工信息
# 案例：查询部门编号不是10的员工信息
# 案例：查询工资大于3000或者工资小于1000的员工信息
# 案例：查询工资为第二到第六的员工信息

# 练习
-- 1.查询dept表中的所有数据
-- 2.查询dept中地址为北京的部门名称
-- 3.查询入职时间在1983年以后的员工姓名和员工工资
-- 4.查询员工的年工资和员工姓名
-- 5.查询员工的薪水的百分之30

2.3 单表复杂查询

2.3.1 内置函数

1. 聚合函数

# ********** 聚合函数
# 关键字：求和sum(),求数量count(),求最大值max(),求最小值min(),求平均值avg()
# 语法：select 聚合函数 from 表名;
-- 案例：查询公司的最高工资
select sal from emp order by sal desc limit 1;
select max(sal) from emp ;

-- 案例：查询公司每个月支出的工资总数
select sum(sal) from emp;

-- 案例：查询公司总共有多少员工
select count(*) from emp;   # 统计emp表中的行数
select count(ename) from emp;  # 统计emp表中名字的个数
select count(comm) from emp;   # 4   
select count(mgr) from emp;  # 13

-- 案例：查询有奖金的员工信息
select * from emp where comm >=0;
select * from emp where comm is not Null;
-- 案例：查询没有奖金的员工信息
select * from emp where comm is  Null;

-- 案例：查询10部门员工的平均工资
select avg(sal) from emp where deptno = 10;

-- 案例：查询公司的最低工资
select sal from emp order by sal asc limit 1;
select min(sal) from emp;

2. 数字函数

# abs() 绝对值
select abs(-99999);
# POW(X,Y) x的y次方
select pow(5,3);

select round(153.123456789,-2);		# 四舍五入，可以定义保留的小数位数   200
select floor(123.9999);	# 123 向下取整
select ceil(123.1111111) # 124 向上取整

3. 字符串函数

# concat(s1,s2,s3,...)  拼接
select 'say hello world' ;		# 把字符串查询出来
select ename from emp;		# 查询emp中的员工姓名
select concat(ename,'say hello world') from emp;
select concat(ename,'的工资为',sal,'元') from emp;
# insert(s1,x,len,s2) 在字符串s1的x的位置，开始替换为s2的字符串长度为len
SELECT INSERT("某人欠我x钱", 5, 1, "1000000000000000000000000000");

4. 时间函数

# adddate(t,n)
select adddate('2021-09-01',INTERVAL 10 DAY);
select now();

2.3.2 复杂查询

# **************** 模糊查询 **************************************
# 通配符：_代表一个字符   %代表0-多个字符
# 语法：
# 案例：查询名字为三个字的员工信息
# 伪代码：选择 所有列 从 员工信息表  哪里  名字为3个字
select * from emp where ename like '__';
# 案例：查询工作类型第一个字是推的员工信息   # 推销员 推 推车
# 伪代码：选择 所有列 从 员工信息表  哪里  工作类型 推XXXX
select * from emp where job like '推%';


# **************** 集合操作 ****************************************
# 关键字：in not in
# 语法：select * from tbname where 列 in(值1，值2，值3);
# 案例：查询工 资为1000，2000,3000,4000,5000,6000的员工信息
# 伪代码：选择 所有列信息 从 员工表 哪里 工资为1000 工资为2000 ...
select * from emp where sal = 1000 or sal = 2000 or sal = 3000 
or sal = 4000 or sal = 5000 or sal = 6000;
select * from emp where sal in (1000,2000,3000,4000,5000,6000);
# 案例：查询工资不等于1000,2000,3000的员工信息
select * from emp where sal not in (1000,2000,3000);


# ******************* 分组查询 ***************************
# 语法：select 分组列，聚合函数 from tbname group by 分组的列; 
-- 案例：查询每个部门的人数 (先按部门划分，之后在对每个部门统计人数)
-- 问题：如果使用*，又使用分组，那么分组之后，就是三个组，每个组执行占用一行，
select deptno,count(*) from emp group by deptno;

-- 案例：查询每个部门的最高工资
select deptno,max(sal) from emp group by deptno;

-- 案例：查询每个部门的平均工资
select deptno,avg(sal) from emp group by deptno;

-- 案例：查询每个部门的最低工资
select deptno,min(sal) from emp group by deptno;

-- 案例：查询每个部门每个月支出工资总额
select deptno,sum(sal) from emp group by deptno;

-- 案例：查询不在10部门的其他部门的平均工资，保留两位小数
-- 结果：avg(sal)
-- 条件：where deptno not in (10) 或者 where deptno != 10
-- 注意：where只能跟在表后面
select deptno,round(avg(sal),2) from emp  where deptno != 10 group by deptno ;


-- 案例：查询平均工资大于2000的部门和部门平均工资
-- 思考：分组之前筛选条件？还是分组之后筛选条件
-- having: 有
select deptno,round(avg(sal),2) from emp where  avg(sal) > 2000 group by deptno;


-- 查询部门人数大于3的部门编号和人数
-- 查询每个部门中工作岗位为2个字符，且这样的员工人数大于3的部门编号和员工人数

3 DQL的进阶查询

3.1 子查询

3.1.1 单行子查询

子查询的结果只有一行数据

可以使用的运算符：

• 所有的比较运算符
• = > < >= <=

# ******************* 子查询
-- 案例： 查询工资比张三高的员工信息
-- 1.先查询出张三的工资
-- 2.查询比步骤1高的员工信息
select sal from emp where ename = '张三';  # 等价于1250
select * from emp where sal > (select sal from emp where ename = '张三');

-- 案例：查询跟张三同部门的员工信息
-- 1.查询张三的部门编号
-- 2.查询部门编号跟步骤1相同的员工信息
select deptno from emp where ename = '张三';  # 30
select * from emp where deptno = (select deptno from emp where ename = '张三');

3.1.2 多行子查询

子查询的结果有多行数据

-- 案例：查询工作地点在北京的员工信息-->deptno为10的员工信息
select * from emp;		# 没有工作地点的
select * from dept;		# loc=北京的部门编号是10

-- 1.查询出在北京的部门编号
-- 2.查询出部门编号在步骤1结果中的员工信息
select deptno from dept where loc = '北京';		# (10,20)
select * from emp where deptno in (select deptno from dept where loc = '北京');
select * from emp where deptno in (10,20);

3.2 多表查询

3.2.1 笛卡尔积查询

1. 语法：select * from tbname1, tbname2 where 连接条件;

-- 笛卡尔积查询
-- 案例：查询部门信息和员工信息
select * from dept,emp  where emp.deptno = dept.deptno ;		
select * from mydemo3.woniuclass;
-- 案例：查询员工姓名和该员工所在的部门名称
-- 结果：ename, dname
-- 表格：emp, dept
-- 条件：员工所在的部门
select emp.ename,dept.dname from dept,emp  where emp.deptno = dept.deptno ;		

-- mydemo3中看
-- 查询学生姓名和所在班级名称
-- 结果：学生姓名，班级名称

-- 查询学生信息和班级信息
select * from woniuclass,woniustudent;
-- 加入连接条件，查询学生姓名和班级名称
select woniuclass.cname,woniustudent.sname from woniuclass,woniustudent where woniuclass.cid = woniustudent.cid;

-- 如果连接结果中的列名是唯一的，可以不使用表名.列名方式访问
select cname,sname from woniuclass,woniustudent where woniuclass.cid = woniustudent.cid;

-- 存在列别名，那么也存在表别名
-- 表名.列名 == 表别名.列名
-- 语法：
select a.cname, b.sname from woniuclass a,woniustudent b where a.cid = b.cid;

上面案例中使用到的mydemo3数据库信息如下：

2. 练习题

数据库信息：

练习题如下：

-- -- 练习题 部门表中
-- select * from dept,emp  where emp.deptno = dept.deptno ;	
select * from dept,emp where dept.deptno=emp.deptno ;
	
-- 1.查询部门名称和员工信息   ------ 查询部门信息和员工信息
select dept.dname,emp.empno,emp.ename,emp.job,emp.mgr,emp.hiredate,emp.sal,emp.comm,emp.deptno from dept,emp where dept.deptno=emp.deptno ;
select dept.dname, emp.* from dept,emp where dept.deptno=emp.deptno ;

-- 2.查询部门名称和员工姓名，员工工资
-- 结果：dname,ename,sal
select dept.dname,emp.ename,emp.sal from dept,emp where dept.deptno=emp.deptno ;
select d.dname, e.ename, e.sal from dept d,emp e where d.deptno=e.deptno ;

-- 3.查询部门名称和部门下员工人数
select d.dname,count(e.empno) from dept d,emp e where d.deptno=e.deptno group by e.deptno ;

-- 4.查询部门名称和部门下最高工资
select d.dname,max(e.sal) from dept d,emp e where d.deptno=e.deptno group by e.deptno ;

-- 
-- -- 练习题 学生表
-- 1.查询老师姓名和其所带课程名称
-- 2.查询课程名称以及该课程下学生编号和分数
-- 3.查询学生姓名，课程名称，课程分数
-- 4.查询学生姓名，课程名称，老师姓名，课程分数

3.2.2 内连接 == 笛卡尔积

1. 语法：select * from A inner join B on A.xx = B.xx；

3.2.3 左外连

1. 语法：select * from A left [outer] join B on A.xx = B.xx；
2. 左表：在连接语句中，处于join左边的表，我们称之为左表
3. 右表：在连接语句中，处于join右表的表，我们称之为右表
4. 定义：两张表进行连接，结果会把符合连接条件的数据显示出来，同时，会把左表中不符合连接条件的数据也显示出来，且右表的列以空值进行填充

3.2.4 右外连

1. 语法：select * from A right [outer] join B on A.xx = B.xx；
2. 左表：在连接语句中，处于join左边的表，我们称之为左表
3. 右表：在连接语句中，处于join右表的表，我们称之为右表
4. 定义：两张表进行连接，结果会把符合连接条件的数据显示出来，同时，会把右中不符合连接条件的数据也显示出来，且左表的列以空值进行填充

3.2.5 全外连（mysql不支持全外连）

3.2.6 练习

-- 内连
-- select * from A inner join B on A.xx = B.xx；
-- 左外连
-- select * from A left [outer] join B on A.xx = B.xx；
-- 右外连
-- select * from A right [outer] join B on A.xx = B.xx；

-- 1.查询部门名称和员工姓名，只查询有员工的部门
select * from  dept, emp where dept.deptno = emp.deptno;
select dept.dname, emp.ename from dept inner join emp on dept.deptno=emp.deptno;  # 查询速度快

use mydemo1;
-- 2.查询部门名称和员工姓名，显示所有的部门
select dept.dname, emp.ename from dept left outer join emp on dept.deptno=emp.deptno;  # 查询速度快

-- 3.查询部门名称和员工姓名，显示所有的员工
select dept.dname, emp.ename from dept right outer join emp on dept.deptno=emp.deptno;  # 查询速度快


use mydemo3;
select * from woniuclass;
select * from woniustudent;
select * from woniuclass , woniustudent where woniuclass.cid = woniustudent.cid;
-- 查询学生信息以及班级信息，同时没有班级的学生也要查询出来
select * from woniuclass right outer join woniustudent  on woniuclass.cid = woniustudent.cid;
select * from woniustudent left outer join woniuclass  on woniuclass.cid = woniustudent.cid;

-- 查询学生信息以及班级信息，同时没有学生的班级也要显示
select * from woniuclass left outer join woniustudent  on woniuclass.cid = woniustudent.cid;