MySQL查询操作(1)

MySQL查询操作(1)

本文以部门表、员工表和工资等级表这三张表为例，来学习MySQL 查询操作。

1 初始化三张表

这三张表的SQL文件下载地址为：https://download.csdn.net/download/weixin_43263961/10685637

1.1 部门表dept

部门表结构如下：

序号	字段名称	字段类型	描述
1	deptNo	INT	部门编号
2	dName	VARCHAR	部门名称
3	loc	VARCHAR	部门位置

部门表数据如下：

1.2 员工表emp

员工表结构如下：

序号	字段名称	字段类型	描述
1	empNo	INT	员工编号
2	eName	VARCHAR	员工姓名
3	job	VARCHAR	职位
4	mgr	INT	员工领导编号
5	hireDate	DATE	入职时间
6	sal	INT	基本工资
7	comm	INT	奖金
8	deptNo	INT	员工所属部门编号

员工表数据如下：

1.3 工资等级表salgrade

工资等级表结构如下：

序号	字段名称	字段类型	描述
1	grade	INT	工资等级编号
2	loSal	INT	该等级的最低工资
3	hiSal	INT	该等级的最高工资

工资等级表数据如下：

2 简单查询

2.1 查询员工表emp中的所有数据

2.2 投影

投影就是显示表中的部分字段，如果SQL中写了三个字段，最后的结果应该展示三列。

2.3 别名

2.4 去掉重复内容

使用DISTINCT关键字去掉重复内容。


注：DISTINCT的去除重复原理是以一行数据来判断的，只有两行数据中的每列数据都一样才叫重复。

2.5 连接显示

连接显示就是将两列数据合并成一列来显示，MySQL中通过concat()函数来实现合并操作。

2.6 限定查询

限定查询就是按照条件查询，通过WHERE关键字指定筛选条件。

• 统计基本工资高于2000的员工信息
  

• 查询工资范围在1500~3000（包括1500和3000）的所有员工信息

通过AND关键字

• 查询10部门或20部门的所有员工信息

第一种方法：通过OR关键字

第二种方法：通过IN关键字

• 判断是否为空
  
  
  

• 范围查询

范围查询可以用IN或NOT IN进行查询。

• 模糊查询

模糊查询通过关键字LIKE进行查询，LIKE是满足条件，NOT LIKE是不满足条件。

• 排序

SQL可以通过使用关键字ORDER BY完成排序，默认是升序排序。此外，还可以通过使用关键字ASC(升序)和DESC(降序)来指定升序还是降序。

• 查询除了ALLEN以外的所有员工信息
  

3 多表查询

多表查询是在一条SQL语句中，从多张表里一起取出所需要的数据。多表查询有时会出现一种现象“笛卡尔积”，消除笛卡尔积就需要添加表和表的关联条件。

3.1 产生笛卡尔积

下面的查询数据中就出现了笛卡尔积现象，原因是没有添加emp表和dept表的关联条件，使得两张表中的每条数据都能互相匹配。
emp表中有14条数据，dept表中有4条数据，因此最后的结果产生了56条数据。

SELECT e.* FROM emp e,dept d;

3.2 消除笛卡尔积

SELECT e.* FROM emp e,dept d WHERE e.deptNo = d.deptNo;

消除笛卡尔积后，结果产生了14条数据。

3.3 查询所有员工，要求显示员工编号、员工姓名、部门编号、部门名称

SELECT
	e.empNo,
	e.eName,
	d.deptNo,
	d.dName
FROM
	emp e,
	dept d
WHERE
	e.deptNo = d.deptNo;

3.4 连接查询

• 自连接

自连接是指两张关联表是同一张表，通过自身关联来查询。
eg: 查询出每个员工的员工编号、员工姓名、领导编号、领导姓名

SELECT
	e.empNo,
	e.eName,
	e.mgr,
	m.eName
FROM
	emp e,
	emp m
WHERE
	e.mgr = m.empNo;

结果产生了13条数据，其中缺少下图中没有领导的King一项。

• 左连接

左连接以左表为主，左边的数据全部显示，右边的数据如果匹配到了就显示，如果没有匹配到就用空值代替。
eg: 查询所有的员工编号、员工姓名、领导编号、领导姓名

SELECT
	e.empNo,
	e.eName,
	e.mgr,
	m.eName
FROM
	emp e
LEFT JOIN emp m ON (e.mgr = m.empNo);

结果产生了14条数据，因为以左表为主。

• 右连接

右连接以左表为主，右边的数据全部显示，左边的数据如果匹配到了就显示，如果没有匹配到就用空值代替。
eg: 查询所有的员工编号、员工姓名、部门编号、部门名称

SELECT
	e.empNo,
	e.eName,
	d.deptNo,
	d.dName
FROM
	emp e
RIGHT JOIN dept d ON (e.deptNo = d.deptNo);

使用右连接，结果产生了15条数据，因为以右表为主，部门40没有员工，也显示出来了。 而此例若使用左连接，则结果产生14条数据。

• 结果集合并

将两个查询结果合并可以通过UNION或UNION ALL来操作。
UNION：返回查询结果的全部内容，重复数据不显示。
UNION ALL： 返回查询结果的全部内容，重复数据也显示。

4 常用函数

4.1 字符函数

序号	函数名称	描述
1	UPPER(字符串)	将字符串的内容全部转为大写
2	LOWER(字符串)	将字符串的内容全部转为小写
3	LENGHT(字符串)	求出字符串的长度
4	SUBSTR(字符串,起始索引,长度)	截取字符串
5	REPLACE(字符串)	字符串替换
6	TRIM(字符串)	去掉左右空格
7	INSTR(字符串,要查找的字符串)	查找字符串中某个字符的索引

• UPPER

SELECT UPPER(name) FROM t_user;

• LOWER

SELECT LOWER(eName) FROM emp;

• LENGTH

SELECT LENGTH(eName) FROM emp;

• SUBSTR

SELECT SUBSTR(name,1,3) FROM t_user;

结果为：adm
注：Java中subString函数是从0开始的，而MySQL中的SUBSTR函数是从1开始的。

• REPLACE

SELECT REPLACE(name,'m','xx') FROM t_user;

结果为：adxxin

• TRIM

若t_user表中的name属性值为’ ad min ’

SELECT TRIM(name) FROM t_user;

结果为：ad min

• INSTR

找到就返回字符串的索引，找不到就返回0。

SELECT INSTR(name,'d') FROM t_user;
SELECT INSTR(name,'z') FROM t_user;

结果为：2和0

4.2 数值函数

序号	函数名称	描述
1	ROUND(数字,保留位数)	对小数进行四舍五入，可以指定保留的位数，如果不指定保留的位数则表示将小数点后的数字全部四舍五入
2	MOD(数字,数字)	取模

• ROUND

若t_user表中的sal属性值为11345.65723

SELECT ROUND(sal,2) FROM t_user;

结果为：11345.66

4.3 日期函数

• 获取当前系统时间

SELECT NOW() FROM emp WHERE empNo = 7369;

• ADDDATE

制定的日期加上指定的天数，求出新的日期。

SELECT ADDDATE(hireDate,3) FROM emp WHERE empNo = 7369;

• LAST_DAY

求出当前月的最后一天。

SELECT LAST_DAY(NOW()) FROM emp WHERE empNo = 7369;

4.4 通用函数

序号	函数名称	描述
1	IFNULL(数字,默认值)	如果显示数据是NULL，就使用默认值
2	CASE WHEN 表达式1 THEN 显示结果1…ELSE 表达式2 END	用于实现多条件判断，在WHEN之后写条件，在THEN之后写满足条件的显示操作。如果都不满足则使用ELSE中的表达式处理

• IFNULL

如果是空值，就用默认值代替。

SELECT IFNULL(mgr,'这是空值') FROM emp WHERE empNo = 7839;

• CASE WHEN

该函数可以代替if…else…

SELECT
	CASE
WHEN empNo = 7369 THEN
	'A'
ELSE
	'B'
END
FROM
	emp
WHERE
	empNo = 7369;

4.5 统计函数

• COUNT

eg: 查询总共有多少员工

SELECT COUNT(1) FROM emp;
SELECT COUNT(*) FROM emp; -- 数值不太大时可以用*

• SUM

eg: 求出每个月工资支出的总和

SELECT SUM(sal) FROM emp;

eg: 求出每个月工资与奖金支出的总和

SELECT SUM(sal)+SUM(comm) AS sum FROM emp;

• AVG, MAX, MIN

eg: 求出公司的最高工资、最低工资、平均工资

SELECT
	MAX(sal) AS '最高工资',
	MIN(sal) AS '最低工资',
	ROUND(AVG(sal),2) AS '平均工资'
FROM
	emp;