字符函数
字符函数
练习：
1.在hello的左右两边各添加5个’*’,返回‘*****hello*****’（三种方法实现）
方法1：
SELECT
LPAD(
RPAD('hello',10,'*'),
15,'*')
'sa'
方法1
方法2
SELECT
CONCAT('*****',
CONCAT('hello','*****')
)
'sa'
方法2
方法3
SELECT
REPLACE(
REPLACE( ' hello ' , ' o ' , ' o***** ' )
, ' h ' , ' *****h ' )
'sa'（#注意字符串要带引号）
方法3
2.从字符串‘abcdefghijklmn’中返回后三位字符，并转换为大写
SELECT
UPPER(
RIGHT('abcdefghijklmn',3)
)
'sa'
练习2
3.查询名字包含5个字符的员工的姓名，工资，奖金(奖金为空则显示为0)、职位、部门号
SELECT ename,job,IFNULL(comm,0),sal,deptno
FROM emp
WHERE LENGTH(ename)>=5
练习3
4.显示将员工姓名的第一个字符去掉后的字符串，如‘ALLEN’显示为‘LLEN’
SELECT empno,
REPLACE(ename,ename,
SUBSTRING(
ename,2,LENGTH('ename')
)
)
'ename',job,deptno
FROM emp
练习4
5.使用员工姓名的第一个字符，从左边将员工姓名补齐到长度为10
SELECT
LPAD(ename,10,LEFT(ename,1))
FROM emp
练习5
数字函数
日期函数
控制流函数
1、IF(expr,v1,v2)函数
如果表达式expr成立，返回结果v1；否则，返回结果v2
例1：SELECT IF(1>0,'正确','错误');
正确
例2：SELECT ename,IF(comm IS NULL,0,comm) FROM emp;
2、 IFNULL(v1,v2)函数
如果v1的值不为NULL，则返回v1，否则返回v2。
例：SELECT ename,IFNULL(comm,0) FROM emp;
3、 CASE函数（类型1）
CASE expr
WHEN e1 THEN v1
WHEN e2 THEN v2
...
ELSE vn
END
写法例：SELECT ename ,empno ,/*要跟“ , ”*/
CASE job
WHEN 'clerk' THEN sal+ 100
WHEN 'salesman' THEN sal + 1000
ELSE sal END
FROM emp;
4、 CASE函数（写法2）
CASE
WHEN e1 THEN v1
WHEN e2 THEN v2
...
ELSE vn
END
写法例：SELECT ename ,empno ,
CASE
WHEN job='CLERK'THEN '店员'
WHEN job='SALESMAN' THEN '销售'
ELSE '其它工作' END
FROM emp;
练习：
1.查询6月份入职的员工的姓名，工资，入职日期、职位、部门号
SELECT ename,sal,hiredate,job,deptno
FROM emp
WHERE DATE_FORMAT(hiredate,'%m')=06
2.查询1981年下半年入职的员工的编号、姓名、职位、入职日期、部门号
SELECT ename,sal,hiredate,job,deptno
FROM emp
WHERE DATE_FORMAT(hiredate,'%Y')='1981'
AND DATE_FORMAT(hiredate,'%m')>6
3.查询在某个月倒数第三天入职的员工的姓名、职位、入职日期
SELECT ename,sal,hiredate,job
FROM emp
WHERE
DATE_FORMAT(LAST_DAY(hiredate),'%e')/*入岗当月最后一天*/
-
DATE_FORMAT(hiredate,'%e')/*入岗当天*/
=2
多表连接
使用连接从多个表中查询数据
SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 = table2.column2;
在WHERE子句中写连接条件
在多个表中具有相同的列名
笛卡尔结果
笛卡尔结果形成于:
连接条件被省略
连接条件无效
第一个表的所有记录连接到第二个表的所有记录
为了避免笛卡尔结果我们总是在 WHERE 子句中使用有效连接
设A,B为集合，用A中元素为第一元素，B中元素为第二元素构成的有序对，所有这样的有序对组成的集合 叫做A与B的笛卡尔积，记作AxB。
连接的类型：
自连接
SELECT worker.ename, worker.mgr , ' works for ',manager.empno ,manager.ename
FROM emp worker, emp manager
WHERE worker.mgr = manager.empno
不等连接
不等连接
不等连接代码
不等连接结果图
外连接
等值连接
等值连接示意图
等值连接代码
等值连接结果图
限定连接
实用AND运算附加条件
显示KING的工号、姓名、部门号、部门地址
SELECT empno,ename,emp.deptno,loc
FROM emp,dept
WHERE ename='king'
AND emp.deptno=dept.deptno
多表连接
多表连接练习：
1.查询20号部门的员工姓名、职位、工资、部门名称
SELECT ename,job,sal,dept.dname
FROM emp,dept
WHERE emp.deptno=dept.deptno
AND emp.deptno=20
2.查询奖金为空的员工的姓名、工资、奖金(显示为0)、工资等级、部门号、部门名称
SELECT ename,sal,IFNULL(comm,0),salgrade.grade,emp.deptno,dept.dname
FROM emp,dept,salgrade
WHERE emp.deptno=dept.deptno
AND comm IS NULL
AND (sal BETWEEN losal AND hisal)
3.查询员工姓名、部门号、部门名称，要求将没有员工的部门也显示出来
SELECT ename,dept.deptno,dname
FROM emp RIGHT JOIN dept ON emp.deptno=dept.deptno
分组函数
AVG ([DISTINCT|ALL]n) 求平均数
SUM ([DISTINCT|ALL]n) 求和
COUNT ({ *|[DISTINCT|ALL]expr}) 计数
MAX ([DISTINCT|ALL]expr) 求最大值
MIN ([DISTINCT|ALL]expr) 最小值
使用AVG与SUM
SELECT AVG(sal), MAX(sal),MIN(sal), SUM(sal)
FROM emp
WHERE job LIKE 'SALES%'
使用MIN和MAX函数
SELECT MIN(hiredate), MAX(hiredate)
FROM emp
使用COUNT函数
COUNT(*) 返回检索行的数目， 不论其是否包含 NULL值。
SELECT COUNT(*)
FROM emp
WHERE deptno = 30
count(column_name)是对列中不为空的行进行计数
SELECT COUNT(comm)
FROM emp
WHERE deptno = 30
分组函数与空值
分组函省略列中的空值
SELECT AVG(comm)
FROM emp;
IFNULL函数强制分组函数包括空值
SELECT AVG(IFNULL(comm,0))
FROM emp;
GROUP BY 子句
SELECT column, group_function
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column];
使用GROUP BY子句将表分成小组
组函数忽略空值, 可以使用NVL,NVL2,COALESCE 等函数处理空值
所有用来分组的列在SELECT列表中不能使用分组函数
SELECT deptno, AVG(sal)
FROM emp
GROUP BY deptno
GROUP BY 列不在SELECT列表中(如果把分组列放到字段列表中可读性更强)
SELECT AVG(sal)
FROM emp
GROUP BY deptno
在多列上使用GROUP BY子句
SELECT deptno, job, sum(sal)
FROM emp
GROUP BY deptno,job
如果没有GROUP BY子句SELECT列表中的
任何列或表达式不能使用合计函数
使用分组函数时应该注意：
不能使用WHERE子句限定组
可使用HAVING子句限定组
HAVING子句
Having子句的作用是对行分组进行过滤
记录被分组
使用组函数
匹配HAVING子句的组被显示
SELECT column, group_function
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column]
例：SELECT deptno, max(sal)
FROM emp
GROUP BY deptno
HAVING max(sal)>2900
例：SELECT job, SUM(sal) PAYROLL
FROM emp
WHERE job NOT LIKE 'SALES%'
GROUP BY job
HAVING SUM(sal)>5000
ORDER BY SUM(sal)
练习
1.查询EMP表，输出每个部门的平均工资，并按部门编号降序排列.
SELECT AVG(sal),dept.dname,dept.deptno
FROM emp,dept
WHERE emp.deptno=dept.deptno
GROUP BY deptno
ORDER BY dept.deptno DESC
2.查询EMP表，输出每个职位的平均工资，按平均工资升序排列.
3.查询EMP表，输出每个部门的各个职位的平均工资，并按部门编号升序、平均工资降序排序。
SELECT AVG(sal),dept.dname,emp.deptno
FROM emp,dept
WHERE emp.deptno=dept.deptno
GROUP BY dept.dname,emp.deptno
ORDER BY emp.deptno ASC,AVG(sal) DESC
子查询
子查询在主查询前执行一次
主查询使用子查询的结果
养成一个好习惯括号与括号写在相对应的位置，减少编写类的失误
SELECT select_list
FROM table
WHERE expr operator
(SELECT select_list
FROM table
)
例：SELECT ename
FROM emp
WHERE sal >
(SELECT sal
FROM emp
WHERE empno=7566
)
使用子查询的规则
子查询要用括号括起来
将子查询放在比较运算符的右边
子查询中不要加ORDER BY子句
对单行子查询使用单行运算符
对多行子查询使用多行运算符
子查询的种类
单行子查询
返回一行记录
使用单行记录比较运算符
例：SELECT ename, job� 2 FROM emp
WHERE job =
(SELECT job
FROM emp
WHERE empno = 7369
)
AND sal >=
(SELECT sal
FROM emp
WHERE empno = 7876
)
在子查询中使用分组函数
SELECT ename, job, sal
FROM emp
WHERE sal =
(SELECT MIN(sal)
FROM emp
)
在子查询中使用HAVING子句
SELECT deptno, MIN(sal)
FROM emp
GROUP BY deptno
HAVING MIN(sal) >
(SELECT MIN(sal)
FROM emp
WHERE deptno = 20
)
多行子查询
返回多行
使用多行比较运算符
例：select ename
from emp
where deptno in
(select deptno
from emp
where ename = 'SMITH'
or ename = 'MILLER'
)
在多行子查询中使用any运算符
SELECT empno, ename, job
FROM emp
WHERE sal > ANY
(SELECT sal
FROM emp
WHERE job = 'CLERK'
)
AND job <> 'CLERK'
<ANY 指小于最大值
>ANY 指大于最小值
在多行子查询中使用ALL运算符
SELECT empno, ename, job ,sal
FROM emp
WHERE sal > ALL
(SELECT avg(sal)
FROM emp
GROUP BY deptno
)
>ALL 指大于最大值
<ALL 指小于最小值
网友评论