MySQL基础（二）

 

部门表 dept : 

create table dept (
    deptno numeric(2),
    dname varchar(14),
    loc varchar(13)
);

工资等级表：

create table salgrade (
    grade numeric,
    losal numeric,
    hisal numeric
);

员工表：

create table emp (
    empno numeric(4) not null,
    ename varchar(10),
    job varchar(9),
    mgr numeric(4),
    hiredate datetime,
    sal numeric(7, 2),
    comm numeric(7, 2),
    deptno numeric(2)
);

 

函数

普通函数：

# 字符类型的函数
SELECT LOWER('ab c DEF'),UPPER('abc D EF')
FROM DUAL;

#在sql中涉及索引的话，从1开始
SELECT SUBSTR('helloworld',2,5) 
FROM DUAL;

SELECT INSTR('helloworld', 'k') #返回指定字符首次出现的位置.若没有，返回0
FROM DUAL;


SELECT TRIM(' ' FROM '  hhhellohw orldhh  ')
FROM DUAL;

SELECT REPLACE('hello','l','m')
FROM DUAL;

# 数值类型的函数
#round:四舍五入
SELECT ROUND(123.456),ROUND(123.456,0),ROUND(123.456,2),ROUND(155.456,-2)
FROM DUAL;

# truncate:截断
SELECT TRUNCATE(123.556,0),TRUNCATE(123.999,2),TRUNCATE(154.23,-2)
FROM DUAL; 

# 结果的符号与被模数一致
SELECT MOD(12,5),MOD(12,-5),MOD(-12,5),MOD(-12,-5)
FROM DUAL;

# 日期型的函数
SELECT NOW()
FROM DUAL;

# 通用型的函数
SELECT ename,sal,12 * salary * (1 + IFNULL(comm,0))
FROM emp;

# 条件表达式： case .. when .. then .. when .. then .. else .. end

#查询所有部门的员工信息, 
#若部门号为 10, 则打印其工资的 1.1 倍, 
#20 号部门, 则打印其工资的 1.2 倍, 
#30 号部门打印其工资的 1.3 倍数,
#其他部门，打印其工资的1.4倍
SELECT ename,deptno,sal,CASE deptno WHEN 10 THEN sal * 1.1
							 WHEN 20 THEN sal * 1.2
							 WHEN 30 THEN sal * 1.3
							 ELSE sal * 1.4 END "new salary"
FROM emp;

聚合函数：

#聚合 函数  sum() max() min() count() avg() 
#分组 语法  group by  ....  having ....

# avg() / sum() : 只适用于数值型的变量。不适用于字符型 和日期型
# as xxx 别名
SELECT AVG(sal) as salavg , SUM(sal) as salsum
FROM emp;

# max() / min(): 适用于数值型；字符型；日期型的变量
SELECT MAX(sal),MIN(sal),MAX(ename),MIN(ename)
FROM emp;


# count()：求所对应列中非空数值的个数。适用于数值型；字符型；日期型的变量
SELECT COUNT(empno),COUNT(ename),COUNT(comm)
FROM emp;

group by .... having 的使用

#求每个部门的所有人的薪水和
SELECT deptno,SUM(sal) as salsum
FROM emp
GROUP BY deptno;

#group by 后面出现多少个字段，那么select 后面也要一模一样



#查询不同部门，不同岗位的员工的薪水和
SELECT deptno,job,SUM(salary)
FROM emp
GROUP BY deptno,job;


#having的使用
#薪水和比1500高的部门

SELECT deptno,SUM(sal)
FROM emp
GROUP BY deptno
HAVING SUM(sal) > 1500;


#注：包含非聚合函数的过滤条件要声明在where中，包含聚合函数的过滤条件声明在having中

子查询

#谁的工资比 King 高?

SELECT ename,sal
FROM emp
WHERE salary > (
		SELECT sal
		FROM emp
		WHERE ename = 'King'
		);



#题目：返回job与141号员工相同，salary比143号员工多的员工姓名，job和工资
SELECT ename,job,sal
FROM emp
WHERE job = (
		SELECT job
		FROM emp
		WHERE empno = 141
		)
AND sal > (
		SELECT sal
		FROM emp
		WHERE empno = 143
		);

#返回公司工资最少的员工的ename,job和sal

SELECT ename,job,sal
FROM emp
WHERE sal = (
		SELECT MIN(sal)
		FROM emp
		);


#查询最低工资大于60号部门最低工资的部门id和其最低工资

SELECT deptno,MIN(sal)
FROM emp
GROUP BY deptno
HAVING MIN(sal) > (
		      SELECT MIN(sal)
		      FROM emp
		      WHERE deptno = 60
		     );

                 
                 
                 
#返回其它部门中比job为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job 以及sal

SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary < ANY(
		SELECT salary
		FROM employees
		WHERE job_id = 'IT_PROG'
		)
AND job_id != 'IT_PROG';

#题目：返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工
#的员工号、姓名、job_id 以及salary
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary < ALL(
		SELECT salary
		FROM employees
		WHERE job_id = 'IT_PROG'
		)
AND job_id != 'IT_PROG';
                
#总结：
SELECT ...,...,...,...(包含组函数)
FROM ...,...,...
WHERE 连接条件 
AND 过滤条件(不包含组函数的)
GROUP BY ...,...,...(查询中非组函数的列)
HAVING 过滤条件(包含组函数的)
ORDER BY ... ASC/DESC , ... ASC/DESC,...
LIMIT ...

或

SELECT ...,...,...,...(包含组函数)
FROM ... JOIN ...
ON ...
JOIN ...
ON ...
WHERE 过滤条件(不包含组函数的)
GROUP BY ...,...,...(查询中非组函数的列)
HAVING 过滤条件(包含组函数的)
ORDER BY ... ASC/DESC , ... ASC/DESC,...
LIMIT ...

多表查询：

#查询员工的id,姓名和部门名称

SELECT empno,ename,dname
FROM emp,dept
WHERE emp.deptno = dept.deptno;



#查询员工的id,姓名,部门id和部门名称
SELECT emp.empno,emp.ename,dept.dname,dept.deptno
FROM emp,dept
WHERE emp.deptno = dept.deptno;

#关于表的别名
SELECT e.empno,e.ename,d.dname,d.deptno
FROM emp e,dept d
WHERE e.deptno = d.deptno;


# 如果查询中涉及到n个表，则至少有 n - 1 个连接条件



# 非等值连接：
# 查询员工的姓名，工资，和工资的等级
SELECT ename,sal,grade
FROM emp e,salgrade j
WHERE e.sal BETWEEN j.losal AND j.hisal;

# 自连接：
# 查询员工的empno,ename和其管理者的empno,ename
SELECT e1.empno,e1.ename,e2.empno,e2.ename
FROM emp e1,emp e2
WHERE e1.`mgr` = e2.`empno`;

#内连接: 只能查询出两个表中匹配的行。
SELECT last_name,e.department_id,department_name
FROM employees e,departments d
WHERE e.`department_id` = d.`department_id`;

# 左外连接：除了查询出两个表中匹配的行之外，还可以返回左表中不满足条件的行
# 右外连接：除了查询出两个表中匹配的行之外，还可以返回右表中不满足条件的行


SELECT ename,e.deptno,dname
FROM emp e JOIN dept d
ON e.`deptno` = d.`deptno`;


# 右外连接
SELECT ename,e.deptno,dname
FROM emp e RIGHT JOIN dept d
ON e.`deptno` = d.`deptno`;

练习 ： 每个部门的薪水最高两位的工种的两位有哪些？ （分组取前几位）

#1.每个部门每个工种的薪水和
CREATE VIEW sal 
AS 
SELECT deptno,job,SUM(sal+IFNULL(comm,0)) as sal
FROM  emp 
GROUP BY deptno,job;

#2.从1结果集 找出哪些部门的工种薪水和最高的前2位工种是什么？
SELECT a.*
FROM sal a 
WHERE 
(
SELECT COUNT(*) FROM sal b 
WHERE a.deptno=b.deptno AND a.sal>b.sal 
) 

#取出每个部门薪水最高的工种
SELECT a.* 
FROM sal a
WHERE (
SELECT COUNT(*) FROM sal b 
WHERE a.deptno=b.deptno AND a.sal<b.sal
) = 0  #比自己小的数量为0的值，即为最大的值
ORDER BY a.deptno;


SELECT 
a.* 
FROM sal a
WHERE (
SELECT count(*) FROM sal b WHERE a.deptno=b.deptno AND a.sal<b.sal
) <= 1 #包含0和1 ，即最大和第二大
ORDER BY a.deptno ASC ,a.sal DESC;