# 用户表 create table user( `uid` int not null primary key auto_increment, `uname` varchar(20) not null, `cid` int not null unique comment '外键约束,关联card表主键' );
# 身份证表 create table card( `cid` int not null primary key auto_increment, `caddress` varchar(20) not null );
# 基于外键关联 ① 在哪一方设置外键都可以,比如我在user设置外键cid ② 添加外键和约束之后这是一个一对多关联 ③ 给外键列设置唯一约束 unique ALTER TABLE `user` ADD FOREIGN KEY (cid) REFERENCES card(cid);
-- 查询名字(LAST_NAME)是King的员工 SELECT * FROM employees WHERE LAST_NAME='King';
-- 查询名字(LAST_NAME)是King并且FIRST_NAME是Steven的员工。 SELECT * FROM employees WHERE LAST_NAME='King' AND FIRST_NAME="Steven";
-- 查询员工编号（employee_id）为100,101,102号的员工 SELECT * FROM employees WHERE EMPLOYEE_ID IN (100,101,102);
-- 查询员工编号（employee_id）不为100,101,102号的员工 SELECT * FROM employees WHERE EMPLOYEE_ID NOT IN (100,101,102);
--查询COMMISSION_PCT为空或者不为空的记录
SELECT * FROM employees WHERE COMMISSION_PCT IS NOT NULL; -- 不为NULL的另一种方式 SELECT * FROM employees WHERE NOT COMMISSION_PCT IS NULL;
--查询工资大于9000小于10000的员工信息 SELECT * FROM employees WHERE SALARY>9000 AND SALARY<10000;
-- 查询公司大于等于9000小于等于10000的员工信息 BETWEEN…AND 带边界 SELECT * FROM employees WHERE SALARY BETWEEN 9000 AND 10000;
-- 查询名字不是King的员工信息 SELECT * FROM employees WHERE LAST_NAME !="KING"; SELECT * FROM employees WHERE LAST_NAME <>"KING"; SELECT * FROM employees WHERE NOT LAST_NAME="KING";
使用 ORDER BY 子句进行排序
1 2 3 4
– ASC（ascend） : 升序[默认升序] – DESC（descend） : 降序
ORDER BY 子句放在SELECT语句的结尾
1 2 3 4 5 6 7 8 9 10 11 12 13 14
SELECT last_name, job_id, department_id, hire_date FROM employees ORDER BY hire_date DESC;
-- 按照别名进行排序 SELECT employee_id, last_name, salary*12 annsal FROM employees ORDER BY annsal;
-- 多列排序 SELECT employee_id, last_name, salary*12 as annsal FROM employees ORDER BY annsal DESC,last_name ASC;
3.8 关联查询(多表连查)
从多个表中获取数据（查询员工编号,员工名称,所在部门信息）
1 2 3
SELECT EMPLOYEE_ID,LAST_NAME,department_name FROM employees,departments WHERE employees.DEPARTMENT_ID= departments.department_id;
SELECT employees.employee_id, employees.last_name, employees.department_id, departments.department_id, departments.location_id FROM employees, departments WHERE employees.department_id = departments.department_id;
多个连接条件与AND操作符
1 2 3 4 5
SELECT employees.employee_id, employees.last_name, employees.department_id, departments.department_id, departments.location_id FROM employees, departments WHERE employees.department_id = departments.department_id AND employees.salary>12000;
使用表的别名区分重复的列名
1 2 3 4 5
SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id FROM employees e, departments d WHERE e.department_id = d.department_id;
多表联查
1 2 3 4 5
-- 查询出公司员工的 last_name, department_name, city
SELECT e.LAST_NAME,d.department_name,l.city FROM employees e,departments d,locations l WHERE e.department_id=d.department_id AND d.location_id=l.location_id;
非等值连接
1 2 3 4 5
-- EMPLOYEES表中的列工资应在JOB_GRADES表中的最高工资与最低工资之间的员工名字,工资以及工资级别信息. SELECT e.last_name, e.salary, j.grade_level FROM employees e, job_grades j WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;
自连接
1 2 3 4 5
-- 查询出 last_name 为 'Chen' 的员工的 manager 的信息
SELECT manager.EMPLOYEE_ID,manager.LAST_NAME FROM employees work,employees manager WHERE work.MANAGER_ID=manager.EMPLOYEE_ID AND work.LAST_NAME="CHen";
左外连接 [left]JOIN..ON..语法
1 2 3 4
不止将等值连接的数据查询出来，还会将左侧不符合条件的数据查询出来(不满足等值的数据)
SELECT e.employee_id,e.LAST_NAME,e.DEPARTMENT_ID, d.department_name,d.DEPARTMENT_ID FROM employees e LEFT JOIN departments d ON e.department_id=d.department_id;
右外连接 [right]JOIN..ON..语法
1 2 3 4
不止将等值连接的数据查询出来，还会将右侧不符合条件的数据查询出来(不满足等值的数据)
SELECT e.employee_id,e.LAST_NAME,e.DEPARTMENT_ID, d.department_name,d.DEPARTMENT_ID FROM employees e right JOIN departments d ON e.department_id=d.department_id;
等值连接(二) [INNER]JOIN..ON..语法
1 2
SELECT e.employee_id,e.LAST_NAME,e.DEPARTMENT_ID, d.department_name,d.DEPARTMENT_ID FROM employees e INNER JOIN departments d ON e.department_id=d.department_id;
-- 可以对数值型数据使用 AVG、SUM、MIN、MAX 函数 SELECT AVG(salary), MAX(salary), MIN(salary), SUM(salary) FROM employees WHERE job_id LIKE '%REP%';
-- 可以对任何类型使用 MIN 和 MAX 函数 SELECT MIN(hire_date), MAX(hire_date) FROM employees;
-- COUNT(*) 返回表中记录总数,适用于任意数据类型 SELECT COUNT(*) FROM employees WHERE department_id = 50;
COUNT(expr) 返回expr不为空的记录总数,如果要查询的数据数据为空，则不被记录 SELECT COUNT(commission_pct) FROM employees WHERE department_id = 50;
组函数会忽略空值,不将NULL值的列加入运算
1 2 3
可以看一下这例子,从这个例子中就会发现组函数，值为NULL的不加入运算 Select avg(commission_pct),sum(commission_pct)/107, sum(commission_pct)/count(commission_pct) From employees;
3.11 分组查询
1
分组查询使用 GROUP BY 进行分组
求出employees表中各部门的平均工资？
1
SELECT DEPARTMENT_ID,AVG(SALARY) FROM employees GROUP BY DEPARTMENT_ID;
求每个部门的部门编号,以及每个部门工资大于8000的人数
1
SELECT DEPARTMENT_ID,count(*) FROM employees WHERE salary>8000 GROUP BY DEPARTMENT_ID;
包含在 GROUP BY 子句中的列不必包含在SELECT列表中。
1
SELECT AVG(salary) FROM employees GROUP BY department_id;
在SELECT列表中所有未包含在组函数中的列都应该包含在GROUP BY子句中。
使用多个列分组
1 2 3 4
-- 通过部门和工种进行分组查询某个部门的某个工种的工人的总工资 SELECT department_id, job_id, SUM(salary) FROM employees GROUP BY department_id, job_id ;
非法使用组函数
1 2 3 4 5 6 7 8 9
所有包含于SELECT 列表中，而未包含于组函数中的列都必须包含于 GROUP BY 子句中。查询出来的数据是错误的。
SELECT department_id, COUNT(last_name) FROM employees;
不能在 WHERE 子句中使用组函数,如果组函数作为过滤条件,那么使用HAVING 替换WHERE进行过滤。
SELECT DEPARTMENT_ID, AVG(salary) FROM employees GROUP BY DEPARTMENT_ID HAVING AVG(SALARY)>8000
注：having与where的区别：
1 2 3 4 5
1. HAVING 是在分组后对数据进行过滤,where是在分组前对数据进行过滤 2. HAVING 后面可以使用分组函数(统计函数) 3. WHERE 后面不可以使用分组函数。 4. WHERE 是对分组前记录的条件，如果某行记录没有满足WHERE子句的条件，那么这行记录不会参加分组 5. 而HAVING是对分组后数据的约束
过滤分组HAVING子句
1 2 3 4 5 6 7
1. 行已经被分组。 2. 使用了组函数。 3. 满足HAVING 子句中条件的分组将被显示。
eg：部门最高工资 比 10000 高的部门 SELECT DEPARTMENT_ID,MAX(SALARY) FROM EMPLOYEES GROUP BY DEPARTMENT_ID HAVING MAX(SALARY) > 10000;
SELECT e.LAST_NAME,e.JOB_ID,e.SALARY FROM employees e WHERE e.JOB_ID=(SELECT JOB_ID FROM employees WHERE EMPLOYEE_ID=140) AND e.SALARY>(SELECT SALARY FROM employees WHERE EMPLOYEE_ID=143)
在子查询中使用组函数
1 2
-- 返回公司工资最少的员工的last_name,job_id和salary SELECT LAST_NAME,JOB_ID,SALARY FROM employees WHERE SALARY=(SELECT MIN(SALARY) FROM employees);
在子查询中的HAVING子句
1 2 3 4 5 6
-- 查询最低工资大于50号部门最低工资的部门id和其最低工资
SELECT department_id,MIN(SALARY) FROM employees GROUP BY department_id HAVING MIN(SALARY) > (SELECT MIN(SALARY) FROM employees WHERE DEPARTMENT_ID=50);
子查询中的空值问题
1 2 3 4 5
SELECT last_name, job_id FROM employees WHERE job_id =(SELECT job_id FROM employees WHERE last_name = 'Haas');
不返回任何行
多行子查询
返回多行
使用多行比较操作符
操作符
含义
IN
等于列表中的任意一个(重点)
ANY
和子查询返回的某一个值比较
ALL
和子查询返回的所有值比较
IN 操作符使用:
1 2 3
-- 查询employee_id 为 110，111，112，113的员工信息
SELECT * FROM employees WHERE employee_id IN(110,111,112,113);
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 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';
给用户分配所有库的所有表的所有权限 ALL(所有权限)，. 所有库的所有表 - GRANT ALL ON *.* TO 'username'@'%'; 给用户分配database库下所有表的所有权限 ALL(所有权限)，database.* database库的所有表 - GRANT ALL ON database.* TO 'username'@'%'; 将新增的reader用户分配查询权限 - GRANT SELECT ON *.* TO 'reader'@'%';