字符函数

字符函数

---

练习：

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  指小于最小值