约束。

#单行子查询

#单行操作符 = =! <> > >= < <= 不等于

#习题：查询工资大于149号员工工资的员工信息
SELECT employee_id,last_name,salary
FROM employees
WHERE salary >(
SELECT salary
FROM employees
WHERE department_id=149
);

SELECT salary
FROM employees
WHERE department_id=149

#习题：返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资

SELECT job_id,last_name,salary
FROM employees
WHERE job_id =(
SELECT job_id
FROM employees
WHERE employee_id)=141;

SELECT job_id
FROM employees
WHERE employee_id=141

SELECT last_name,job_id,salary
FROM employees
WHERE job_id = ()

#中英互换
#返回公司员工工资最少的last_name,job_id
SELECT last_name,job_id,salary
FROM employees
WHERE salary = (
SELECT MIN(salary)
FROM employees;
)
SELECT MIN(salary)
FROM employees;

#子查询编写技巧（步骤）
#1 从里往外写
#2 从外往里写


/*习题，查询与141号员工的manager_id和departent_id
相同的其他员工的employee_id,manger_id,department_id
*/
SELECT manager_id,department_id
FROM employees
WHERE job_id =(
SELECT job_id
FROM employees
WHERE employee_id)=141;

SELECT manager_id
FROM employees
WHERE employee_id = 141;

SELECT department_id
FROM employees
WHERE employee_id = 141;


SELECT employee_id,manager_id,department_id
FROM employees
#方式1

#方式2

SELECT employee_id,manager_id,department_id
FROM employees
WHERE (manager_id,department_id) = (
SELECT manager_id,department_id
FROM employees
WHERE employee_id=141)
AND employee_id <> 141;

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

SELECT department_id,MIN(salary)
FROM employees
#WHERE department_id
GROUP BY department_id
HAVING MIN(salary) >(
SELECT MIN(salary)
FROM employees
WHERE department_id=110
);
#8条
AND department_id is not null;

#非法使用子查询
SELECT employee_id,last_name
FROM employees
WHERE salary = (
SELECT MIN(salary)
FROM employees
GROUP BY department_id
);

#返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资
SELECT last_name,job_id,salary
FROM employees
WHERE job_id =(
SELECT salary
FROM employees
WHERE
)

SELECT last_name,job_id,salary
FROM employees
WHERE job_id =(


)
SELECT job_id
FROM employees
WHERE employee_id = 141
)
AND salary > (
SELECT salary
FROM employees
WHERE employee_id=143);
#多行子查询的操作符
/*in 等于列表中的任意一个

ANY
需要和单行比较操作符一起使用，和子查询返回的某一个值比较（任意）
*/
#非法子查询
SELECT employee_id,last_name
FROM employees
WHERE salary IN (
SELECT MIN(salary)
FROM employees
GROUP BY department_id
);
#ANY / ALL

#返回其他job_id为'IT_PROG'部门任一(ANY)(可为所有ALL)工资低的员工的员工号，姓名，job_id以及salary

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


#mysql中聚合函数不能嵌套

SELECT MIN(AVG(salary))
FROM employees
GROUP BY department_id;

#第九章 约束

/*
1基础知识
1.1为什么要约束？为了保证数据的完整性
1.2什么叫约束？对表中字段的限制
1.3约束的分类
角度1：约束的字段个数
单列约束 VS 多列约束
角度2：约束的作用范围
列级约束：将此表约束声明在对应字段的后面
表级约束：在表中所有字段都声明完，在所有字段的后面声明的约束
角度3：约束的作用（或功能）
（1）not null 非空约束
（2）UNIQUE 唯一约束
（3）PRIMARY KEY 主建约束
（4）FOREIGN KEY 外键约束
（5）CHECK 检查约束
（6）DEFAULT 默认值约束
1.4如何添加/删除约束
CREATE

*/
SELECT * FROM
information_schema.TABLES_constraints WHERE
table_name='employees';
#not null 非空约束
#只能用列级约束，不能用表级约束
CREATE DATABASE dbtest;
#在creat table 时添加约束
CREATE TABLE test1(
id int not null
last_name VARCHAR(10) not null
email VARCHAR(15)
salary int
);
desc test1;
INSERT into test1(id,last_name,email,salary)
VALUES(1,'TOM','tom@126.com',5000);

INSERT into test1(id,last_name,email,salary)
VALUES(2,'TOM','tom@126.com',5000);
last_name不能为空