系统学习数据库day05

多行子查询

all

表示和子查询的所有行进行比较, 每一行必须都满足条件

• < ALL 表示小于子查询结果集合中的所有行, 即小于最小值
• 大于 ALL 表示大于子查询结果集合中的所有行, 即大于最大值就可以
• = ALL表示等于子查询结果集合中的所有行, 即等于所有值, 通常无意义

-- 查询部门编号不为20，且工资比20部门所有员工工资高的
-- 员工编号，姓名，职位，工资。
SELECT EMPNO, ename, JOB, SAL from emp
WHERE sal > ALL(SELECT SAL from emp where DEPTNO=20)
and DEPTNO<>20;

• 查询入职日期比10部门任意一个员工晚的员工姓名、入职日期，不包括10部门员工

SELECT * from emp where HIREDATE>
any(SELECT HIREDATE from emp where deptno=10) AND deptno <>10;  

• 查询入职日期比10部门所有员工晚的员工姓名、入职日期，不包括10部门员工

SELECT * from emp where HIREDATE>
all(SELECT HIREDATE from emp where deptno=10) AND deptno <>10;  

• 查询职位和10部门任意一个员工职位相同的员工姓名，职位，不包括10部门员工

SELECT * from emp where JOB=
any(SELECT JOB from emp where deptno=10) AND deptno <>10;

• 子查询中有空的情况

SELECT ename, sal from emp
WHERE EMPNO  NOT in (SELECT MGR from emp)

子查询中结果有空值， 会导致主查询没有结果
结论：只要空值有可能成为子查询的一部分， 就不能使用not in

在from 中使用子查询

• 查询比自己部门平均工资高的员工姓名，工资，部门编号，部门平均工资
  

SELECT e.ENAME, e.SAL, e.DEPTNO, deptavg.avgsal from emp e, (SELECT DEPTNO, avg(sal) avgsal FROM emp GROUP BY DEPTNO ) deptavg
WHERE e.DEPTNO = deptavg.deptno AND e.SAL > deptavg.avgsal

• 查询岗位不为SALESMAN，工资和大于等于2500的岗位及每种岗位的工资和。

SELECT
    e.job, 
    sum(e.sal) 
FROM
    emp e
WHERE
    e.job <> 'SALESMAN'
GROUP BY
    e.job
HAVING
    sum(e.sal) >= 2500;

• 显示经理号码和经理姓名，这个经理所管理员工的最低工资，没有经理的KING也要显示，不包括最低工资小于3000的，按最低工资由高到低排序

SELECT manager.EMPNO 经理号码, manager.ENAME 经理姓名, 
MIN(worker.SAL) 管理员工的最低工资 from emp worker LEFT 
 OUTER  JOIN  emp manager ON
worker.mgr=manager.EMPNO GROUP BY manager.EMPNO, manager.ENAME
HAVING MIN(worker.SAL) >=3000 ORDER BY 3 DESC

• 查询工资高于编号为7782的员工工资，并且和7369号员工从事相同工作的员工的编号、姓名及工资。

SELECT
	e.EMPNO,
	e.ENAME,
	e.SAL
FROM
	emp e
WHERE
	SAL > (
		SELECT
			sal
		FROM
			emp
		WHERE
			EMPNO = 7782
	)
AND job = (
	SELECT
		job
	FROM
		emp
	WHERE
		EMPNO = 7369
)

• 查询工资最高的员工姓名和工资。

select ename,sal from emp where sal=(select max(sal) from emp)

• 查询部门最低工资高于10号部门最低工资的部门的编号、名称及部门最低工资。

select d.deptno, d.dname , min(e.sal)
from emp e, dept d
where e.deptno = d.deptno
group by d.deptno
having  min(e.sal) > (select min(sal) from emp where deptno = 10);

• 查询员工工资为其部门最低工资的员工的编号和姓名及工资。

SELECT
	e.empno,
	e.ename,
	e.sal
FROM
	emp e,
	(
		SELECT
			deptno,
			min(sal) minsal
		FROM
			emp
		GROUP BY
			deptno
	) deptminsal
WHERE
	e.deptno = deptminsal.deptno
AND e.sal = deptminsal.minsal;

• 显示经理是KING的员工姓名，工资。

select ename,sal from emp where mgr=(select empno from emp where ename='king')

不用子查询

select worker.ename, worker.sal from emp worker,emp manager  
WHERE worker.MGR = manager.EMPNO and manager.ENAME = 'king'

• 显示比员工SMITH参加工作时间晚的员工姓名，工资，参加工作时间

SELECT HIREDATE,ENAME,SAL FROM EMP WHERE HIREDATE>(SELECT HIREDATE FROM EMP WHERE ENAME='SMITH')

数据库的管理

• 创建表

CREATE TABLE 表名(
字段名1 数据类型 [约束条件],
字段名2 数据类型 [约束条件],
…
[其他约束条件],
[其他约束条件]
)其他选项（例如存储引擎、字符集等选项）

创建一个表

CREATE TABLE Person (
	id INT (4) NOT NULL,
	NAME VARCHAR (20) NOT NULL,
	address VARCHAR (20)
);

数据类型

时间戳： 返回1970年1月1号到现在的毫秒数
# 约束

CREATE TABLE `emp` (
  `EMPNO` int(4) NOT NULL,
  `ENAME` varchar(10) DEFAULT NULL,
  `JOB` varchar(9) DEFAULT NULL,
  `MGR` int(4) DEFAULT NULL,
  `HIREDATE` date DEFAULT NULL,
  `SAL` int(7) DEFAULT NULL,
  `COMM` int(7) DEFAULT NULL,
  `DEPTNO` int(2) DEFAULT NULL,
  PRIMARY KEY (`EMPNO`),
  KEY `FK_DEPTNO` (`DEPTNO`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;