一.SQL分类

1.DQL （Data Query Language）数据查询语言

（1）SELECT

2.DML（Data Manipulation Language）数据操纵语言

（2）INSERT
（3）UPDATE
（4）DELETE

3.DDL（Data Definition Language）数据定义语言

（1）CREATE
（2）DROP
（3）ALTER

4.DCL（Data Control Language）数据控制语言

（1）GRANT
（2）REVOKE

二.SQL特点

1.综合统一

2.高度非过程化

3.面向集合的操作系统

4.以同一种语法提供多种使用方式

5.语言简洁 易学易用

三.SQL规范

1.书写

（1）SQL 可以写在一行或者多行。为了提高可读性，各子句分行写，必要时使用缩进
（2）每条命令以 ; 或 \g 或 \G 结束
（3）关键字不能被缩写也不能分行

2.标点符号

（1）必须保证所有的()、单引号、双引号是成对结束的
（2）必须使用英文状态下的半角输入方式
（3）字符串型和日期时间类型的数据可以使用单引号（’ ‘）表示
（4）列的别名，尽量使用双引号（” “），而且不建议省略as

3大小写规范

（1）数据库名、表名、表的别名、变量名是严格区分大小写的
（2）关键字、函数名、列名(或字段名)、列的别名(字段的别名) 是忽略大小写的。
（3）推荐采用统一的书写规范：

  • 数据库名、表名、表别名、字段名、字段别名等都小写

  • SQL 关键字、函数名、绑定变量等都大写

    4.注释

    （1）单行注释：#或— （—后有一个空格）
    （2）多行注释：/开始 /结束

    5.命名规则

    （1）数据库、表名不得超过30个字符，变量名限制为29个
    （2）必须只能包含 A–Z, a–z, 0–9, _共63个字符
    （3）数据库名、表名、字段名等对象名中间不要包含空格
    （4）同一个MySQL软件中，数据库不能同名；同一个库中，表不能重名；同一个表中，字段不能重名
    （5）必须保证你的字段没有和保留字、数据库系统或常用方法冲突。

  • 如果坚持使用，请在SQL语句中使用 ` (着重号)引起来、

（6）保持字段名和类型的一致性，在命名字段并为其指定数据类型的时候一定要保证一致性。假如数据类型在一个表里是整数，那在另一个表里可就别变成字符型了

四.数据库导入

1.命令行：source 文件全路径名

2.sqlyog：”工具”->”执行sql脚本”->选择对应的数据即可

五.基本的SELECT语句（查询）

1.SELECT -FROM

（1）SELECT 字段1，字段2…. FROM 表名；——从表名中查询指定字段内容
（2）SELECT * FROM 表名；——从表中查询所有信息

  1. #1 在员工表查id，name
  2. SELECT employ_id,employ_name FROM employees;
  3. #2 在员工表查所有信息
  4. SELECT * FROM employees;

2.列的别名

（1）格式：SELECT 字段1 别名，字段2…. FROM 表名；——空格+别名
（2）别名可以用引号引起来
（3）别名中不能有空格 有空格必须用双引号
（4）别名不能再WHERE中使用，只能在ORDER BY中使用

  1. SELECT employ_id a_id,employ_name a_name FROM employees;
  2. #必须是空格+别名

3.去除重复行

（1）用关键字DISTINCT

  1. SELECT DISTINCT department_id
  2. FROM empioyees;

4.空值参与运算（NULL）

（1）NULL不等同于0，’ ‘,’null’
（2）NULL参与运算，结果也一定是NULL

5.着重号-“`”

（1）着重号是123左边的，跟~一个按键
（2）一般用于表名或其他名与关键字保留字相同的情况

  1. SELECT * FROM order#不加着重号被认为关键字
  2. SELECT * FROM `order`

6.查询常量

（1）常量会自动补全给每一行

  1. SELECT '案例'，123，employee_id   # 案例和123为常量 每一行都会显示
  2. FROM employees;

7.显示表结构

（1）关键字——DESCRIBE或DESC

  1. DESC employees;#显示表中所有字段（列）的详细信息
  2. DESCRIBE employees;

8.过滤数据

（1）过滤数据就是找到满足条件的数据
（2）关键字——WHERE
（3）WHERE紧跟着FROM 语句写在FROM后边

  1. #查询部门ID为90的员工
  2. SELECT *
  3. FROM employees
  4. WHERE department_id = 90；
  5. #查询last_name叫king的信息
  6. SELECT *
  7. FROM employees
  8. WHERE last_name = 'King';

9.查询语句书写顺序

  1. SELECT 字段
  2. FROM 表名
  3. WHERE 条件
  4. GROUP BY 分组
  5. HAVING 分组后过滤
  6. ORDER BY 排序
  7. LIMIT 分页

10.练习

  1. #查询员工12个月的工资总和，并起别名为ANNUAL SALARY
  2. SELECT name,salary * 12 "ANNUAL SALARY"
  3. FROM employees；
  4. #查询employees表中去除重复的job_id后的数据
  5. SELECT DISTINCT job_id
  6. FROM employees
  7. #查询工资大于12000的员工姓名和工资
  8. SELECT name,salary
  9. FROM employees
  10. WHERE salary > 12000;
  11. #查询员工工号为176的员工的姓名和部门号
  12. SELECT name,department_id
  13. FROM employees
  14. WHERE department_id = 176;
  15. #显示表departments的结构，并查询其中全部数据
  16. DESC department
  17. #或者
  18. SELECT * FROM departments;

六.排序与分页

1.排序规则(使用ORDER BY子句排序)

（1）ASC（ascend）：升序
（2）DESC（descend）：降序
（3）不指定升序降序默认升序
（4）如果没排序，会按照添加数据的顺序显示

2.单列排序

  1. #显示工资，按照部门编号（10，20，30）降序排列
  2. SELECT salary 
  3. FROM employees 
  4. WHERE department_id IN (10,20,30) 
  5. ORDER BY department_id DESC;

3.多列排序（二级排序）

（1）单列排序在值相同的情况下，其他列并没有排序，所以需要多列排序
（2）多列排序时，要有相同的列值才会进行下一列的排序

  1. SELECT employ_id,employ_id,salary 
  2. FROM employees 
  3. WHERE department_id IN (10,20,30) 
  4. ORDER BY department_id DESC,employ_id ASC;

4.分页实现规则（使用LIMIT关键字）

（1）为什么分页：有时候返回信息太多，所以要分页
（2）LIMIT+偏移量+每页显示的数量
（3）偏移量=（当前第几页-1）*每页显示的数据量

  • LIMIT =（page_num-1）* page_size , page_size;
    1. #每页显示20条，此时显示第一页（偏移量为0）
    2. SELECT employ_name，salary FROM employees LIMIT 0，20;
    3. #每页显示20条，此时显示第二页（偏移量为20）
    4. SELECT employ_name，salary FROM employees LIMIT 20，20;

5.分页拓展

（1）LIMIT WHERE ORDER BY声明顺序：先WHERE筛选，然后ORDER BY排序，最后LIMIT实现换页
（2）显示32，33条数据

  • 注意：分页显示第一条数据从0开始，所以想找第N条，就要写N-1

（3）SQL8.0可用”LIMIT 3 ODFFSET 4” 表示获取第五条数据开始的后边三条数据，结果等同LIMIT 4，3；
（4）分页的好处：约束返回结果的数量可以减少数据表的网络传输量，提高查询效率

  1. SELECT employ_name,salary
  2. FROM employees
  3. WHERE salary BETWEEN 6000 AND 12000
  4. ORDER BY salary DESC
  5. LIMIT 31,2;

6.练习

  1. # 查询员工姓名，部门号和年薪，按年薪降序，按姓名升序排列
  2. SELECT employ_name,department_id,salary * 12 a
  3. FROM employees
  4. ORDER BY a DESC,employ_name ASC;
  5. #查询工资不在8000-17000的员工姓名和工资，按工薪降序，显示第21-40条数据
  6. SELECT employ_name,salary
  7. FROM employees
  8. WHERE salary NOT BETWEEN 8000 AND 17000
  9. ORDER BY salary DESC
  10. LIMIT 20,20
  11. #查询邮箱中包含e的员工信息，并先按邮箱的字节数将降序，再按部门号升序
  12. SELECT * 
  13. FROM employees
  14. WHERE employ_email LIKE '%e%'--或WHERE employ_email REGEXP '[e]' 正则
  15. ORDER BY LENGTH(employ_email) DESC,department_id ASC;--ASC可省略

七.多表查询（关联查询）

1.多表关系：

（1）一对多(多对一)：再多的一方建立外键，指向一的一方的主键

  • 例如：一个员工只能在一个部门工作，一个部门有多个员工

（2）多对多：建一个中间表，中间表至少包含两个外键，分别关联两方的主键

  • 例如：一个学生可以选择多门课程，一门课程也可以被多名学生选

（3）一对一：在任意一方加入外键，关联另一方的主键，并且设置外键为唯一

2.多表查询的正确方式：

  • 需要连接条件
  • 建议无论什么查询都起别名
  • 若查询语句中出现了多个表中都存在的字段，必须指明来自那个字段
  • 建议多表查询时，每个字段前都指明其所在的表
  • 可以给表起别名 用在SELECT和WHERE中
  • 表一旦起了别名，就不能用原名，必须用别名

  • N个表实现多表查询，则至少需要N-1个连接条件

    1. SELECT employee_id,department_name
    2. FROM employees
    3. WHERE employees.department_id = departments.department_id;

    3.多表连接的分类：

    （1）连接查询

  • 内连接：相当于查询a，b交集部分的数据

  • 外连接：
    • 左外连接：查询左表所有数据，以及两张表交集部分数据
    • 右外连接：查询右表所有数据，以及两张表交集部分数据
  • 自连接：当前表与自身的连接查询，自连接必须使用表的别名

（2）子查询

4.内连接：查询两张表的交集部分

（1）隐式内连接：

  • 语法：SELECT 字段列表 FROM 表1，表2 WHERE 条件…；

    1. //查询每一个员工的姓名，及关联的部门名
    2. SELECT emp.name,dept.name FROM emp,dept WHERE emp.id = dept.id;

    （2）显式内连接：

  • 语法：SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件…；//INNER可以省略

    1. //查询每一个员工的姓名，及关联的部门名
    2. SELECT emp.name,dept.name FROM emp INNER JOIN dept ON emp.id = dept.id;

    5.外连接

    （1）左外连接：完全包含表1的数据和表1表2交集的部分

  • 语法：SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件…；

    1. //查询emp表的所有数据和对应的部门信息
    2. SELECT e.*,d.name FROM emp e LEFT OUTER JOIN dept d ON e.id=d.id;

    （2）右外连接：完全包含表2的数据和表1表2交集的部分

  • 语法：SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件…；

    1. //查询dept表的所有数据和对应的员工信息
    2. SELECT e.name,d.* FROM emp e RIGHT OUTER JOIN dept d ON e.id=d.id;

    6.自连接：自己连接自己

    （1）自连接查询可以是内连接查询，也可以是外连接查询
    （2）语法：SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件…

  • 自连接必须使用别名

    1. //查询员工与所属领导的名字（表中有领导id，对应员工id）
    2. SELECT a.employee,b.manger FROM employees a JOIN employees b ON a.employ_id=b.manger_id;

    八.联合查询

    1.语法：SELECT 字段列表 FROM 表A UNION [ALL] SELECT 字段列表 FROM 表B

    （1）UNION ALL：查询所有的结果直接拼在一起
    （2）UNION：将查询结果去重在拼在一起

    2.联合查询就是将多次查询的结果合并，形成新的查询结果集

    3.联合查询必须保证查询的列数和字段类型是一一对应的

    1. //将工资小于5000的员工和年龄大于50的员工全部查出来
    2. SELECT * FROM emp e WHERE e.salary<5000
    3. UNION
    4. SELECT * FROM emp e WHERE e.age>50;

    九.子查询（嵌套查询）

    1.概述：

    （1）子查询：在一个查询语句中嵌套另一个查询语句
    （2）查询有外查询（或主查询），内查询（或子查询）
    （3）子查询在主查询之前一次执行完成
    （4）子查询的结果被主查询使用
    （5）子查询要放在括号里，放在比较条件的右侧（可读性更好）
    （6）单行操作符对应单行子查询，多行操作符对应多行子查询
    （7）子查询的分类

  • 标量子查询：子查询结果为单个值

  • 列子查询：子查询结果为一列
  • 行子查询：子查询结果为一行

  • 表子查询：子查询的结果为多行多列

    2.标量子查询

    （1）标量子查询常用操作符：= < > <= >= <>

  • 不能比较多行的数据，不能比较带有GROUP BY的子查询

  • 比较多行就成为非法使用子查询，会报错

（2）HAVING中的子查询：

  • 首先执行子查询
  • 向主查询中的HAVING子句返回结果

（3）子查询中有空值，则不返回任何行
（4）练习：

  1. //根据销售部部门ID，查询所有员工信息
  2. SELECT * FROM emp WHERE dept.id=(SELECT id from dept where name="销售部");
  3. //查询在方东白入职之后入职的员工信息
  4. SELECT * from emp where date>(SELECT date from emp WHERE name="方东白");

3.列子查询

（1）子查询返回结果是一列
（2）列子查询常用操作符：IN，NOT IN，ANY，SOME，ALL

操作符 含义
IN 在指定范围内多选一
NOT IN 不在指定范围内
ANY 子查询返回的列表中，有任意一个满足即可
SOME 等同于ANY，使用SOME的地方都可以使用ANY
ALL 子查询返回的列表中所有值都必须满足

（3）练习：

  1. //查询销售部和市场部的所有员工信息
  2. SELECT * FROM emp WHERE dept_id IN (SELECT id in dept WHERE name="销售部" OR "市场部");
  3. //查询比财务部所有人工资都高的员工信息
  4. SELECT * FROM emp 
  5. WHERE salary > all(SELECT salary FROM emp WHERE dept_id = (SELECT id FROM dept WHERE name="财务部"));
  6. //查询比财务部其中任意一人工资高的员工信息--就是把上个语句的ALL改为ANY或SOME

4.行子查询

（1）子查询返回结果是一行
（2）行子查询常用操作符：=，<>，IN，NOT IN
（3）练习：

  1. //查询与张无忌薪资及领导相同的员工信息
  2. SELECT * FROM emp WHERE (salary,manger_id) = (SELECT salary,manager_id FROM emp WHERE name="张无忌")

5.表子查询

（1）子查询返回结果是多行多列，相当于一张表
（2）常见操作符：IN
（3）练习：

  1. //查询和鹿杖客，宋远桥的职位和薪资相同的员工信息
  2. SELECT * FROM emp WHERE (job,salary) IN (SELECT job,salary FROM emp WHERE name name = "鹿杖客" OR name = "宋远桥");