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;