约束。
#单行子查询
#单行操作符 = =! <> > >= < <= 不等于
#习题：查询工资大于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不能为空