00-MySQL数据库的使用-下

一 多表查询

多表查询简介

笛卡尔乘积

笛卡尔乘积 ：

当一个连接条件无效或被遗漏时，其结果是一个笛卡尔乘积 (Cartesian product)，其中所有行的组合都被显示。第一个表中的所 有行连接到第二个表中的所有行。一个笛卡尔乘积会产生大量的 行，其结果没有什么用。你应该在 WHERE 子句中始终包含一个有 效的连接条件，除非你有特殊的需求，需要从所有表中组合所有的 行。

多表查询分类

• sql92标准：内连接(等值连接 、非等值连接 、 自连接)。
• sql99标准：内连接、外连接(左外、右外、全外(MySQL不支持全外连接))、交叉连接。

SQL92标准中的查询

等值连接

等值连接

为了确定一个雇员的部门名，需要比较 EMPLOYEES 表中的 DEPARTMENT_ID 列与DEPARTMENTS 表中的 DEPARTMENT_ID 列的值。在 EMPLOYEES 和DEPARTMENTS 表之间的关系是一个相 等 (equijoin) 关系，即，两个表 中DEPARTMENT_ID列的值必须相等。

等值连接特点：

• 多表等值连接的结果为多表的交集部分；
• n表连接，至少需要n-1个连接条件；
• 多表不分主次，没有顺序要求；
• 一般为表起别名，提高阅读性和性能；
• 可以搭配排序、分组、筛选….等子句使用；

注意： 等值连接也被称为简单连接 (simple joins) 或内连接 (inner joins)。

等值连接的使用

• SELECT 子句指定要返回的列名：

  • employee last name、employee number 和 department number，这些是 EMPLOYEES 表中的列
  • department number、department name 和 location ID， 这些 是 DEPARTMENTS 表中的列

• FROM 子句指定数据库必须访问的两个表：

  • EMPLOYEES 表
  • DEPARTMENTS 表

• WHERE 子句指定表怎样被连接：

  EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID，因为 DEPARTMENT_ID 列 是两个表的同名列，它必须用表名做前缀以避免混淆。

增加搜索条件

添加查询条件

除连接之外，可能还要求用 WHERE 子句在连接中限制一个或多个表中的行。

限制不明确的列名

• 需要在 WHERE 子句中用表的名字限制列的名字以避免含糊不清。没有表前缀，DEPARTMENT_ID 列可能来自 DEPARTMENTS 表，也可能来自 EMPLOYEES 表，这种情况下需要添加表前缀来执行查询。
• 如果列名在两个表之间不相同，就不需要限定列。但是，使用表前缀可以改善性能，因为MySQL服务器可以根据表前缀找到对应的列。
• 必须限定不明确的列名也适用于在其它子句中可能引起混淆的那些列，例如 SELECT子句或 ORDER BY 子句。

使用表别名

表别名定义原则

• 表别名不易过长，短一些更好。
• 表别名应该是有意义的。
• 表别名只对当前的 SELECT 语句有效

多表连接

示例一： 查询雇员 King 所在的部门名称。

select d.department_name 
from employees e,departments d   
where e.dept_id = d.department_id and e.last_name = 'King';
1
2
3

示例二： 显示每个雇员的 last name、departmentname 和 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

非等值连接

非等值连接

一个非等值连接是一种不同于等值操作的连接条件。 EMPLOYEES 表 和JOB_GRADES A 表之间的关系有一个非等值连接例子。在两个 表之间的关系是EMPLOYEES 表中的 SALARY 列必须是 JOB_GRADES 表的 LOWEST_SALARY 和HIGHEST_SALARY 列之间 的值。使用不同于等于 (=) 的操作符获得关系。

示例一： 创建 job_grades 表，包含 lowest_sal ，highest_sal ， grade_level。

create table job_grades(
    lowest_sal int,
    highest_sal int ,
    grade_level varchar(30)
);
1
2
3
4
5

示例二： 插入数据

1000 2999 A

2000 4999 B

5000 7999 C

8000 12000 D

insert into job_grades values(1000,2999,'A');
insert into job_grades values(2000,4999,'B');
insert into job_grades values(5000,7999,'C');
insert into job_grades values(8000,12000,'D');
1
2
3
4

示例三： 查询所有雇员的薪水级别。

select e.last_name,j.grade_level 
from employees e ,job_grades j 
where e.salary between j.lowest_sal and j.highest_sal;
1
2
3

自连接

自连接

连接一个表到它自己。有时需要连接一个表到它自己。为了找到每 个雇员的经理的名字，则需要连接EMPLOYEES 表到它自己，或执 行一个自连接。

图片中的例子连接 EMPLOYEES 表到它自己。为了在 FROM 子句中模拟两个表，对于相同的表 EMPLOYEES，用两个别名，分别为 worker 和 manager。在该例中，WHERE 子句包含的连接意味着 “一个工人的经理号匹配该经理的雇员号”。

示例一：

查询每个雇员的经理的名字以及雇员的名字，雇员名字列别名为 W，经理列别名为M。

SELECT
worker.LAST_NAME W,manager.LAST_NAME M
from employees worker,employees manager
where worker.MANAGER_ID = manager.EMPLOYEE_ID;
1
2
3
4

示例二：

查询Fox的经理是谁？显示他的名字。

SELECT
worker.LAST_NAME,manager.LAST_NAME
from employees worker,employees manager
where worker.MANAGER_ID = manager.EMPLOYEE_ID
AND
worker.LAST_NAME = 'Fox';
1
2
3
4
5
6

SQL99标准中的查询

MySQL5.7 支持部分的SQL99 标准。

SQL99中的交叉连接(CROSS JOIN)

示例： 使用交叉连接查询 employees 表与 departments 表。

select * from employees cross join departments;
1

SQL99中的自然连接(NATURAL JOIN)

自然连接

连接只能发生在两个表中有相同名字和数据类型的列上。如果列有 相同的名字，但数据类型不同，NATURAL JOIN 语法会引起错误。

自然连接查询

在图片例子中，LOCATIONS 表被用 LOCATION_ID 列连接到 DEPARTMENT表，这是在两个表中唯一名字相同的列。如果存在其它的同名同类型的列，自然连接会使用等值连接的方式连接他们， 连接条件的关系为and。 自然连接也可以被写为等值连接：

SELECT d.department_id, d.department_name, d.location_id , l.city 
FROM departments d , locations l 
WHERE d.location_id = l.location_id;
1
2
3

示例： 使用自然连接查询所有有部门的雇员的名字以及部门名称。

select e.last_name,d.department_name 
from employees e 
natural join departments d;
1
2
3

SQL99中的内连接(INNER JOIN)

语法：

• SELECT 查询列表;
• FROM 表1 别名;
• INNER JOIN 连接表(INNER关键字可省略);
• ON 连接条件;

用ON子句指定连接条件

用ON子句指定更多的连接条件

示例: 查询雇员名字为 Fox 的雇员 ID ，薪水与部门名称。

select
e.employee_id,e.salary,d.department_name 
from employees e 
inner JOIN departments d 
on e.department_id = d.department_id 
where e.last_name = 'Fox';
1
2
3
4
5
6

外连接查询(OUTER JOIN)

孤儿数据(Orphan Data)

孤儿数据是指被连接的列的值为空的数据。

左外连接(LEFT OUTER JOIN)

左外连接

左边的表 (EMPLOYEES) 中即使没有与 DEPARTMENTS 表中匹配的 行，该查询也会取回 EMPLOYEES 表中所有的行。

示例： 查询所有雇员的名字以及他们的部门名称，包含那些没有部门的雇员。

select e.last_name,d.department_name 
from employees e 
LEFT OUTER JOIN departments d 
on e.dept_id = d.department_id;
1
2
3
4

右外连接(RIGTH OUTER JOIN)

右外连接

右边的表 (DEPARTMENTS ) 中即使没有与 EMPLOYEES 表中匹配的 行，该查询也会取回 DEPARTMENTS 表中所有的行。

示例： 查询所有雇员的名字以及他们的部门名称，包含那些没有雇员的部门。

select e.last_name,d.department_name  
from employees e RIGHT  OUTER JOIN departments d 
on e.DEPARTMENT_ID = d.department_id;
1
2
3

全外连接(FULL OUTER JOIN)

注意： MySQL 中不支持 FULL OUTER JOIN 连接

可以使用 union 实现全完连接。

• UNION: 可以将两个查询结果集合并，返回的行都是唯一的，如同对整个结果集合使用了 DISTINCT。
• UNION ALL: 只是简单的将两个结果合并后就返回。这样，如果返回的两个结果集中有重复的数 据， 那么返回的结果集就会包含重复的数据了。

语法结构

(SELECT 投影列 FROM 表名 LEFT OUTER JOIN 表名 ON 连接条件)
UNION
(SELECT 投影列 FROM 表名 RIGHT OUTER JOIN 表 名 ON 连接条件)
1
2
3

示例： 查询所有雇员的名字以及他们的部门名称，包含那些没有雇员的部 门以及没有部门的雇员。

(select e.last_name,d.department_name 
from employees e LEFT OUTER JOIN 
departments d  on e.department_id = d.department_id)  
UNION  
(select e1.last_name,d1.department_name
from employees e1 RIGHT OUTER JOIN
departments d1 on d1.department_id = e1.department_id)
1
2
3
4
5
6
7

SQL99标准总结

• 自然连接 natural join

  select <选择列>
  from 表A natural join 表B
  1
  2

  将表A和表B进行自然连接：在A和B所有共同属性上做等值连接，然后再投影去掉重复属性。

  例如：

  select *
  from book natural join reader
  1
  2

• 内连接 inner join

  select <选择列>
  frome 表A inner join 表B on <连接条件>
  1
  2

  <连接条件> A.s θ B.k（s为表A的属性，k为表B的属性）
  θ为等号=时，是等值连接；θ不为等号=时，是不等值连接；
  将表A和表B进行内连接：当至少有一个满足连接条件时，返回满足条件的行。

  例如：

  select book_name
  from book inner join reader on book.borrower_id=reader.id
  1
  2

• 外连接 outer join

  • 左外连接 left join

    select <选择列>
    from 表A left join 表B on <连接条件>
    1
    2

    <连接条件>参考上文
    将表A和表B进行左外连接：在自然连接的结果上，如果t是A中不满足连接条件的元组，则将t添加到结果中，并且t本身不存在的属性取NULL

  • 右外连接 right join

    select <选择列>
    from 表A right join 表B on <连接条件>
    1
    2

    <连接条件>参考上文
    将表A和表B进行右外连接：在自然连接的结果上，如果t是B中不满足连接条件的元组，则将t添加到结果中，并且t本身不存在的属性取NULL

  • 全外连接 full join

    select <选择列>
    from 表A full join 表B on <连接条件>
    1
    2

    <连接条件>参考上文
    将表A和表B进行全外连接：在自然连接的结果上，
    1）如果t是A中不满足连接条件的元组，则将t添加到结果中，并且t本身不存在的属性取NULL
    2）如果s是B中不满足连接条件的元组，则将s添加到结果中，并且s本身不存在的属性取NULL

二 聚合函数

聚合函数介绍

聚合函数

聚合函数也称之为多行函数，组函数或分组函数。聚合函数不象单行函数，聚合函数对行的分组进行操作，对每组给出一个结果。如果在查询中没有指定分组，那么聚合函数则将查询到的结果集视为一组。

聚合函数类型

聚合函数说明：

聚合函数使用方式

使用聚合函数的原则

• DISTINCT 使得函数只考虑不重复的值；
• 所有聚合函数忽略空值。为了用一个值代替空值，用 IFNULL 或 COALESCE 函数

AVG 和 SUM 函数

AVG(arg)函数

对分组数据做平均值运算。 arg:参数类型只能是数字类型。

SUM(arg)函数

对分组数据求和。 arg:参数类型只能是数字类型。

示例： 计算员工表中工作编号含有REP的工作岗位的平均薪水与薪水总 和。

SELECT AVG(salary),SUM(salary)
FROM employees
WHERE job_id LIKE '%REP%';
1
2
3

MIN 和 MAX 函数

MIN(arg)函数

求分组中最小数据。 arg:参数类型可以是字符、数字、 日期。

MAX(arg)函数

求分组中最大数据。 arg:参数类型可以是字符、数字、 日期。

示例： 查询员工表中入职时间最短与最长的员工，并显示他们的入职时 间。

SELECT MIN(hire_date), MAX(hire_date) FROM employees;
1

COUNT 函数

返回分组中的总行数。

COUNT 函数有三种格式：

• COUNT(*)：返回表中满足 SELECT 语句的所有列的行数，包括重复行，包括有空值列的行。
• COUNT(expr)：返回在列中的由 expr 指定的非空值的数。
• COUNT(DISTINCT expr)：返回在列中的由 expr 指定的唯一的非空值的数。

使用 DISTINCT 关键字

• COUNT(DISTINCT expr) 返回对于表达式 expr 非空并且值不相同的行数
• 显示 EMPLOYEES 表中不同部门数的值

示例一： 显示员工表中部门编号是80中有佣金的雇员人数。

SELECT COUNT(commission_pct) FROM employees WHERE department_id = 80;
1

示例二： 显示员工表中的部门数。

SELECT COUNT(DISTINCT department_id) FROM employees;
1

组函数和 Null 值

在组函数中使用 IFNULL 函数

SELECT AVG(IFNULL(commission_pct, 0)) FROM employees;
1

三 数据分组(GROUP BY)

创建数据组

创建数据组

在没有进行数据分组之前，所有聚合函数是将结果集作为一个大的 信息组进行处理。但是，有时，则需要将表的信息划分为较小的 组，可以用 GROUP BY 子句实现。

GROUP BY 子句语法

原则

• 使用 WHERE 子句，可以在划分行成组以前过滤行。
• 如果有WHERE子句，那么GROUP BY 子句必须在WHERE的子句后面。
• 在 GROUP BY 子句中必须包含列。

使用 GROUP BY 子句

GROUP BY 子句

		select 
			聚合函数，
			列（要求出现在group by的后面）
		from 
			表
		where 
			筛选条件
		group by 
			分组的列表
		order by 
			子句
1
2
3
4
5
6
7
8
9
10
11

注意：除了出现在group by后面的字段，如果要在select后查询其他字段，必须用聚合函数进行聚合

特点：分组查询中的筛选条件分为两类：
分组前筛选： 数据源是原始表，用where，放在group by前面，因为在分组前筛选
分组后筛选：数据源是分组后的结果集 ，用having，放在group by后面，因为在分组后进行筛选。

示例： 计算每个部门的员工总数。

SELECT DEPARTMENT_ID, COUNT(*) FROM employees GROUP BY DEPARTMENT_ID;
1

在多列上使用分组

在组中分组

可以列出多个 GROUP BY 列返回组和子组的摘要结果。可以用 GROUP BY子句中的列的顺序确定结果的默认排序顺序。下面是图 片中的 SELECT 语句中包含一个 GROUP BY 子句时的求值过程：

• SELECT 子句指定被返回的列：

  − 部门号在 EMPLOYEES 表中

  − Job ID 在 EMPLOYEES 表中

  − 在 GROUP BY 子句中指定的组中所有薪水的合计

• FROM 子句指定数据库必须访问的表：EMPLOYEES 表。

• GROUP BY 子句指定你怎样分组行：

  − 首先，用部门号分组行。

  − 第二，在部门号的分组中再用 job ID 分组行。

如此 SUM 函数被用于每个部门号分组中的所有 job ID 的 salary 列。

示例： 计算每个部门的不同工作岗位的员工总数。

SELECT e.DEPARTMENT_ID, e.JOB_ID,COUNT(*)
FROM employees e
GROUP BY e.DEPARTMENT_ID,e.JOB_ID;
1
2
3

约束分组结果(HAVING)

HAVING 子句

HAVING 子句是对查询出结果集分组后的结果进行过滤。

约束分组结果

用 WHERE 子句约束选择的行，用 HAVING 子句约束组。

为了找到每个部门中的最高薪水，而且只显示最高薪水大于 $10,000 的那些部门，可以象下面这样做：

1. 用部门号分组，在每个部门中找最大薪水。
2. 返回那些有最高薪水大于 $10,000 的雇员的部门

SELECT department_id, MAX(salary) 
FROM employees 
GROUP BY department_id 
HAVING MAX(salary)>10000 ;
1
2
3
4

HAVING子句语法

示例：

显示那些合计薪水超过 13,000 的每个工作岗位的合计薪水。排除那 些JOB_ID中含有REP的工作岗位，并且用合计月薪排序列表。

SELECT job_id, SUM(salary) PAYROLL 
FROM employees 
WHERE job_id NOT LIKE '%REP%'
GROUP BY job_id 
HAVING SUM(salary) > 13000 
ORDER BY SUM(salary);
1
2
3
4
5
6

四 子查询

子查询介绍

用子查询解决问题

假如要写一个查询来找出挣钱比 Abel 的薪水还多的人。为了解决这 个问题，需要两个查询：一个找出 Abel 的收入，第二个查询找出收入高于 Abel 的人。可以用组合两个查询的方法解决这个问题。内查询或子查询返回一个值给外查询或主查询。使用一个子查询相当于执行两个连续查询并且用第一个查询的结果作为第二个查询的搜索 值。

子查询语法

子查询

子查询是一个 SELECT 语句，它是嵌在另一个 SELECT 语句中的子 句。使用子查询可以用简单的语句构建功能强大的语句。

可以将子查询放在许多的 SQL 子句中，包括：

• WHERE 子句
• HAVING 子句
• FROM 子句

使用子查询

使用子查询的原则

• 子查询放在圆括号中。
• 将子查询放在比较条件的右边。
• 在单行子查询中用单行运算符，在多行子查询中用多行运算符。

子查询类型

示例： 查询与Fox同一部门的同事，并显示他们的名字与部门ID。

select e.LAST_NAME,e.DEPARTMENT_ID FROM
employees e
where e.DEPARTMENT_ID =
(select e1.DEPARTMENT_ID from employees e1 where e1.last_name = 'Fox');
1
2
3
4

单行子查询

单行子查询

单行子查询是从内查询返回一行的查询。在该子查询类型中用一个单行操作符。

示例： 查询 Fox的同事，但是不包含他自己。

select  empl.last_name   
from   employees  empl   
where   empl.department_id   =  
	(select e.department_id from employees e where e.last_name = 'Fox') 
	and empl.last_name <> 'Fox';
1
2
3
4
5

多行子查询

多行子查询

子查询返回多行被称为多行子查询。对多行子查询要使用多行运算符而不是单行运算符。

使用ANY运算符

ANY 运算符

ANY 运算符比较一个值与一个子查询返回的每一个值。

• < ANY 意思是小于最大值。
• > ANY 意思是大于最小值。
• = ANY 等同于 IN。

使用ALL运算符

ALL 运算符比较一个值与子查询返回的每个值。

• < ALL 意思是小于最小值。
• > ALL 意思是大于最大值，

NOT 运算符可以与 IN运算符一起使用。

子查询中的空值

内查询返回的值含有空值，并因此整个查询无返回行，原因是用大 于、小于或不等于比较Null值，都返回null。所以，只要空值可能是子查询结果集的一部分，就不能用 NOT IN 运算符。NOT IN 运算符 相当于 <> ALL。

注意，空值作为一个子查询结果集的一部分，如果使用 IN 操作符的话，不是一个问题。IN 操作符相当于 =ANY。

SELECT emp.last_name 
FROM employees emp 
WHERE emp.employee_id IN (SELECT mgr.manager_id FROM employees mgr);
1
2
3

示例: 查找各部门收入为部门最低的那些雇员。显示他们的名字，薪水以及部门 ID。

select
em.last_name,em.salary,em.department_id  
from employees em  
where em.salary in 
	(select min(e.salary) from employees e group by e.department_id);
1
2
3
4
5

五 MySQL中的索引

索引介绍

索引是对数据库表中的一列或多列值进行排序的一种结构，使用索引可以快速访问数据库表中的特定信息。索引是一种特殊的文件， 它们包含着对数据表里所有记录的位置信息。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。MySQL索引的建立对于MySQL 的高效运行是很重要的，索引可以大大提高 MySQL 的检索速度。

索引的作用

索引相当于图书上的目录，可以根索引缺点据目录上的页码快速找到所需的 内容，提高性能（查询速度）。

索引优点：

• 通过创建唯一性索引，可以保证数据库表中的每一行数据的唯一性；
• 可以加快数据的检索速度；
• 可以加速表与表之间的连接；
• 在使用分组和排序进行检索的时候，可以减少查询中分组和排序的时间；

索引缺点

• 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加；
• 索引需要占用物理空间，数据量越大，占用空间越大；
• 会降低表的增删改的效率，因为每次增删改索引都需要进行动态维护；

什么时候需要创建索引

• 频繁作为查询条件的字段应该创建索引；
• 查询中排序的字段创建索引将大大提高排序的速度（索引就是排序加快速查找）；
• 查询中统计或者分组的字段；

什么时候不需要创建索引

• 频繁更新的字段不适合创建索引，因为每次更新不单单是更新记录，还会更新索引，保存索引文件；
• where条件里用不到的字段，不创建索引；
• 表记录太少，不需要创建索引；
• 经常增删改的表；
• 数据重复且分布平均的字段，因此为经常查询的和经常排序的字段建立索引。注意某些数据包含大 量重复数据，因此他建立索引就没有太大的效果，例如性别字段，只有男女，不适合建立索引；

MySQL中的索引类型

• 普通索引： 最基本的索引，它没有任何限制。
• 唯一索引： 索引列的值必须唯一，但允许有空值，如果是组合索引，则列值的组合必须唯一。
• 主键索引： 特殊的索引，唯一的标识一条记录，不能为空，一般用primary key来约束。
• 联合索引： 在多个字段上建立索引，能够加速查询到速度。

普通索引

是最基本的索引，它没有任何限制。在创建索引时，可以指定索引 长度。length 为可选参数，表示索引的长度，只有字符串类型的字 段才能指定索引长度，如果是 BLOB 和 TEXT 类型，必须指定 length。

创建索引时需要注意： 如果指定单列索引长度，length 必须小于这个字段所允许的最大字符个数。

查询索引

SHOW INDEX FROM table_name;
1

直接创建索引

CREATE INDEX index_name ON table(column(length));
1

示例：

为 emp3 表中的 name 创建一个索引，索引名为 emp3_name_index；

create index emp3_name_index ON emp3(name);
1

修改表添加索引

ALTER TABLE table_name ADD INDEX index_name (column(length));
1

示例：

修改 emp3 表，为 addrees 列添加索引，索引名为 emp3_address_index；

alter table emp3 add index emp3_address_index(address);
1

创建表时指定索引列

CREATE TABLE `table` (
    COLUMN TYPE ,
    PRIMARY KEY (`id`),
    INDEX index_name (column(length))
);
1
2
3
4
5

示例：

创建 emp4 表，包含 emp_id,name,address 列， 同时为 name 列 创建索引 ，索引名为 emp4_name_index。

create  table emp4(
    emp_id  int  primary  key auto_increment,
    name  varchar(30),
    address varchar(50),
    index emp4_name_index(name)
);
1
2
3
4
5
6

删除索引

DROP INDEX indexname ON tablename;
1

示例：

删除 mep3 表中索引名为 emp3_address_index 的索引。

drop index emp3_address_index on emp3;
1

唯一索引

唯一索引与普通索引类似，不同的就是： 索引列的值必须唯一，但允许有空值。

创建唯一索引

CREATE UNIQUE INDEX indexName ON table(column(length));
1

示例：

为 emp 表中的 name 创建一个唯一索引，索引名为 emp_name_index。

create unique index emp_name_index on emp(name);
1

修改表添加唯一索引

ALTER TABLE table_name ADD UNIQUE indexName (column(length));
1

示例：

修改 emp 表，为 salary 列添加唯一索引，索引名为 emp_salary_index。

alter table emp add unique emp_salary_index(salary);
1

创建表时指定唯一索引

CREATE TABLE `table` (
    COLUMN TYPE ,
    PRIMARY KEY (`id`),
    UNIQUE index_name (column(length))
);
1
2
3
4
5

示例：

创建 emp5 表，包含 emp_id,name,address 列，同时为 name 列 创建唯一索引。索引名为 emp5_name_index。

create table emp5(
    emp_id int primary key,
    name varchar(30),
    address varchar(30),
    unique emp5_name_index(name)
);
1
2
3
4
5
6

主键索引

主键索引是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引。

修改表添加主键索引

ALTER TABLE 表名 ADD PRIMARY KEY(列名);
1

示例：

修改 emp 表为 employee_id 添加主键索引。

1 alter table emp add primary key(employee_id);
1

创建表时指定主键索引

CREATE TABLE `table` (
    COLUMN TYPE ,
    PRIMARY KEY(column)
);

1
2
3
4
5

示例：

创建 emp6 表，包含 emp_id,name,address 列，同时为 emp_id 列创建主键索引。

create table emp6(
    employee_id int primary key auto_increment,
    name varchar(20),
    address varchar(50)
);
1
2
3
4
5

组合索引

组合索引是指使用多个字段创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用(最左前缀原则)。

最左前缀原则

就是最左优先。

如： 我们使用表中的 name ，address ，salary 创建组合索引，那么想要组合索引生效， 我们只能使用如下组合：

name/address/salary

name/address

name/

如果使用 addrees/salary 或者是 salary 则索引不会生效

添加组合索引

ALTER TABLE table_name ADD INDEX index_name (column(length),column(length));
1

示例：

修改 emp6 表，为 name ，address 列创建组合索引。

alter table emp6 add index emp6_index_n_a(name,address);
1

创建表时创建组合索引

CREATE TABLE `table` (
    COLUMN TYPE ,
    INDEX index_name (column(length),column(length))
);
1
2
3
4

示例：

创建 emp7 表，包含 emp_id,name,address 列，同时为 name,address 列创建组合索引。

create table emp7(
    emp_id int primary key auto_increment ,
    name varchar(20),
    address varchar(30),
    index emp7_index_n_a(name,address)
);
1
2
3
4
5
6

六 MySQL事务

事务简介

事务是指作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行。

事务定义(Transaction)

• 事务是一个最小的不可再分的工作单元；通常一个事务对应一个完整的业务(例如银行账户转账业 务，该业务就是一个最小的工作单元)
• 一个完整的业务需要批量的DML(insert、update、delete)语句共同联合完成
• 事务只和DML语句有关，或者说DML语句才有事务。这个和业务逻辑有关，业务逻辑不同，DML 语句的个数不同

事务四大特征(ACID)

• 原子性(ATOMICITY) 事务中的操作要么都不做，要么就全做。
• 一致性(CONSISTENCY) 一个事务应该保护所有定义在数据上的不变的属性(例如完整性 约束)。在完成了一个成功的事务时，数据应处于一致的状态。
• 隔离性(ISOLATION) 一个事务的执行不能被其他事务干扰。
• 持久性(DURABILITY) 一个事务一旦提交，它对数据库中数据的改变就应该是永久性 的。

事务类型

• 显式事务

  需要我们手动的提交或回滚。

  DML 语言中的所有操作都是显示事务操作。

• 隐式事务

  数据库自动提交不需要我们做任何处理，同时也不具备回滚性。

  DDL、DCL 语言都是隐式事务操作

使用事务

示例一：

创建account账户表，包含id、卡号、用户名、余额。

create table account(
    id int primary key auto_increment,
    cardnum varchar(20) not null,
    username varchar(30) not null,
    balance double(10,2)
);

1
2
3
4
5
6
7

示例二：

向account表中插入两条数据。

insert into account(cardnum,username,balance) VALUES('123456789','张三',2000);
insert into account(cardnum,username,balance) VALUES('987654321','李四',2000);
1
2

示例三：

在一个事务中完成转账业务。

START TRANSACTION
update account set balance = balance-200 where cardnum = '123456789';
update account set balance = balance+200 where cardnum = '987654321';
select * from account;
-- 当我们关闭数据库重新打开后，张三和李四的账户余额并没发生任何变化。
-- 这是因为当我们使用“START TRANSACTION”开启一个事务后，该事务的提交方式不再是自动的，  
-- 而是需要手动提交，而在这里，我们并没有使用事务提交语句COMMIT，  
-- 所以对account表中数据的修改并没有永久的保存到数据库中，也就是说我们的转账事务并没有执行成功  
-- 提交转账事务  
commit;
-- 事务的回滚让数据库恢复到了执行事务操作前的状态。  
-- 需要注意的是事务的回滚必须在事务提交之前，因为事务一旦提交就不能再进行回滚操作。
rollback;

1
2
3
4
5
6
7
8
9
10
11
12
13
14

事务的并发问题

脏读（读取未提交数据）

指一个事务读取了另外一个事务未提交的数据。

A事务读取B事务尚未提交的数据，此时如果B事务发生错误并执行 回滚操作，那么A事务读取到的数据就是脏数据。

不可重复读（前后多次读取，数据内容不一致）

在一个事务内读取表中的某一行数据，多次读取结果不同。

事务A在执行读取操作，由整个事务A比较大，前后读取同一条数据 需要经历很长的时间 。而在事务A第一次读取数据，比如此时读取 了小明的年龄为20岁，事务B执行更改操作，将小明的年龄更改为 30岁，此时事务A第二次读取到小明的年龄时，发现其年龄是30 岁，和之前的数据不一样了，也就是数据不重复了，系统不可以读取到重复的数据，成为不可重复读。

幻读（前后多次读取，数据总量不一致）

是指在一个事务内读取到了别的事务插入的数据，导致前后读取数量总量不一致。

事务A在执行读取操作，需要两次统计数据的总量，前一次查询数据 总量后，此时事务B执行了新增数据的操作并提交后，这个时候事务 A读取的数据总量和之前统计的不一样，就像产生了幻觉一样，平白 无故的多了几条数据，成为幻读。

事务的隔离级别

事务的隔离级别用于决定如何控制并发用户读写数据的操作。数据 库是允许多用户并发访问的，如果多个用户同时开启事务并对同一 数据进行读写操作的话，有可能会出现脏读、不可重复读和幻读问题，所以MySQL中提供了四种隔离级别来解决上述问题。

事务的隔离级别从低到高依次为：

• READ UNCOMMITTED
• READ COMMITTED
• REPEATABLE READ
• SERIALIZABLE

隔离级别越低，越能支持高并发的数据库操作。

查看MySQL默认事务隔离级别

SELECT @@transaction_isolation;
1

设置事务隔离级别

对当前session有效。

set session transaction isolation level read uncommitted;
set session transaction isolation level read committed;
set session transaction isolation level repeatable read;
set session transaction isolation level serializable;
1
2
3
4

七 MySQL的用户管理

MySQL 是一个多用户的数据库系统，按权限，用户可以分为两种： root 用户，超级管理员，和由 root 用户创建的普通用户。

用户管理

创建用户

CREATE USER username IDENTIFIED BY 'password';
1

查看用户

SELECT USER,HOST FROM mysql.user;
1

示例：

创建一个 u_sxt 的用户，并查看创建是否成功。

create user u_sxt IDENTIFIED by 'sxt';
select user,host from mysql.user;
1
2

权限管理

新用户创建完后是无法登陆的，需要分配权限。

GRANT 权限 ON 数据库.表 TO 用户名@登录主机 IDENTIFIED BY “密码”

登陆主机：

权限列表

GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password'
1

示例：

为 u_sxt 用户分配只能查询 bjsxt 库中的 emp 表，并且只能在本机 登陆的权限。

grant select ON bjsxt.emp to 'u_sxt'@'localhost' IDENTIFIED by 'sxt';
1

刷新权限

每当调整权限后，通常需要执行以下语句刷新权限。

FLUSH PRIVILEGES;
1

删除用户

DROP USER username@localhost;
1

示例：

删除 u_sxt 用户。

drop user 'u_sxt'@'localhost';
1

八 Navicat工具

创建用户

分配权限

删除用户

导出sql脚本文件

导入sql脚本文件

九 MySQL分页查询

MySQL 分页查询原则：

• 在 MySQL 数据库中使用 LIMIT 子句进行分页查询。
• MySQL 分页中开始位置为 0。
• 分页子句在查询语句的最后侧。

LIMIT子句

SELECT 投影列 FROM 表名 WHERE 条件 ORDER BY LIMIT 开始位置，查询数量;
1

示例：

查询雇员表中所有数据按 id 排序，实现分页查询，每次返回两条结果。

select * from employees order by employees_id limit 0,2;
1

LIMIT OFFSET子句

SELECT 投影列 FROM 表名 WHERE 条件 ORDER BY LIMIT 查询数量 OFFSET 开始位置;
1

示例：

查询雇员表中所有数据按 id 排序，使用 LIMIT OFFSET 实现分页查询，每次返回两条结果。

select * from employees order by employees_id limit 2 offset 4;
1