[大数据]mysql

第一章概述：

数据库的概念

DB
数据库（database）：存储数据的“仓库”。它保存了一系列有组织的数据。
DBMS
数据库管理系统（Database Management System）。数据库是通过DBMS创
建和操作的容器
SQL
结构化查询语言（Structure Query Language）：专门用来与数据库通信的语
言

数据库的特点：

类型：关系型数据库：SQLite、Oracle、mysql

特性：

1、关系型数据库，是指采用了关系模型来组织数据的数据库；
　　2、关系型数据库的最大特点就是事务的一致性；
　　3、简单来说，关系模型指的就是二维表格模型，而一个关系型数据库就是由二维表及其之间的联系所组成的一个数据组织。

优点：

1、容易理解：二维表结构是非常贴近逻辑世界一个概念，关系模型相对网状、层次等其他模型来说更容易理解；
　　2、使用方便：通用的SQL语言使得操作关系型数据库非常方便；
　　3、易于维护：丰富的完整性(实体完整性、参照完整性和用户定义的完整性)大大减低了数据冗余和数据不一致的概率；
　　4、支持SQL，可用于复杂的查询。

缺点：

1、为了维护一致性所付出的巨大代价就是其读写性能比较差；
　　2、固定的表结构；
　　3、高并发读写需求；
　　4、海量数据的高效率读写

类型：非关系型数据库MongoDb、redis、HBase

特性：

1、使用键值对存储数据；
　　2、分布式；
　　3、一般不支持ACID特性；
　　4、非关系型数据库严格上不是一种数据库，应该是一种数据结构化存储方法的集合。

优点：

1、无需经过sql层的解析，读写性能很高；
　　2、基于键值对，数据没有耦合性，容易扩展；
　　3、存储数据的格式：nosql的存储格式是key,value形式、文档形式、图片形式等等，文档形式、图片形式等等，而关系型数据库则只支持基础类型。

缺点：

1、不提供sql支持，学习和使用成本较高；
　　2、无事务处理，附加功能bi和报表等支持也不好；

SQL的优点：

1、不是某个特定数据库供应商专有的语言，几乎所有DBMS都支持SQL
2、简单易学
3、虽然简单，但实际上是一种强有力的语言，灵活使用其语言元素，可以进行非常复杂和高级的数据库操作。

SQL语言分类

1、DML（Data Manipulation Language):数据操纵语句，用于添加、删除、修改、查询数据库记录，并检查数据完整性
包括如下SQL语句：
①INSERT：添加数据到数据库中
②UPDATE：修改数据库中的数据
③DELETE：删除数据库中的数据
④SELECT：选择（查询）数据 SELECT是SQL语言的基础，最为重要。
2、DDL（Data Definition Language):数据定义语句，用于库和表的创建、修改、删除。
包括如下SQL语句：
①CREATE TABLE：创建数据库表
②ALTER TABLE：更改表结构、添加、删除、修改列长度
③DROP TABLE：删除表
④CREATE INDEX：在表上建立索引
⑤DROP INDEX：删除索引
3、DCL（Data Control Language):数据控制语句，用于定义用户的访问权限和安全级别。
包括如下SQL语句：
①GRANT：授予访问权限
②REVOKE：撤销访问权限
③COMMIT：提交事务处理
④ROLLBACK：事务处理回退
⑤SAVEPOINT：设置保存点
⑥LOCK：对数据库的特定部分进行锁定

第二章mysql安装与使用：

MySQL产品的特点

1. MySQL数据库隶属于MySQL AB公司，总
   部位于瑞典，后被oracle收购。
2. 优点：
   – 成本低：开放源代码，一般可以免费试用
   – 性能高：执行很快
   – 简单：很容易安装和使用
3. DBMS分为两类：
   – 基于共享文件系统的DBMS （Access ）
   – 基于客户机——服务器的DBMS（MySQL、Oracle、SqlServer）

MySQL的版本

社区版（免费） 企业版（收费）
下载地址：http://dev.mysql.com/downloads/mysql

启动和停止MySQL服务

方式一：通过计算机管理方式右击计算机—管理—服务—启动或停止MySQL服务
方式二：通过命令行方式 启动：net start mysql服务名 停止：net stop mysql服务名

MySQL服务端的登录和退出

登录 mysql –h 主机名 –u用户名 –p密码 退出 exit

MySQL语法规范

1. 不区分大小写
2. 每句话用;或\g结尾
3. 各子句一般分行写
4. 关键字不能缩写也不能分行
5. 用缩进提高语句的可读性

MySQL连接

1. 进入 mysql, 在命令行中输入: mysql –uroot –p#### (其中：####表示密码）

mysql –uroot –p####

2. 查看 mysql 中有哪些个数据库: show databases;

show databases; 

3. 使用一个数据库: use 数据库名称;

use mysql;

4. 新建一个数据库: create database 数据库名

create database test;

第三章创建管理表：

创建表

1. 语法

CREATE TABLE dept
(deptno INT(2),
dname VARCHAR(14),
loc VARCHAR(13));

2. 查看

DESCRIBE dept;

常用数据类型

创建表

CREATE TABLE emp (
#int类型，自增
emp_id INT AUTO_INCREMENT, #最多保存20个中英文字符
emp_name CHAR (20),
#总位数不超过15位
salary DOUBLE,
#日期类型
birthday DATE,
#主键
PRIMARY KEY (emp_id)
);

使用子查询创建表

1. 使用 AS subquery 选项，将创建表和插入数据结合起来

CREATE TABLE table
[(column, column…)]
AS subquery;

2. 指定的列和子查询中的列要一一对应
3. 通过列名和默认值定义列
4. 复制现有的表:

create table emp1 as select * from employees;
create table emp2 as select * from employees where 1=2;
--创建的emp2是空表。

使用子查询创建表举例

ALTER TABLE 语句

使用 ALTER TABLE 语句可以实现:

1. 向已有的表中添加列

ALTER TABLE dept80 
ADD job_id varchar(15);

2. 修改现有表中的列
   ①可以修改列的数据类型, 尺寸和默认值

ALTER TABLE dept80
MODIFY (last_name VARCHAR(30));

②对默认值的修改只影响今后对表的修改

ALTER TABLE dept80
MODIFY (salary double(9,2) default 1000);

3. 删除现有表中的列
   使用 DROP COLUMN 子句删除不再需要的列

ALTER TABLE dept80
DROP COLUMN job_id;

4. 重命名现有表中的列
   使用 CHANGE old_column new_column dataType子句重命名列

ALTER TABLE dept80
CHANGE department_name dept_name varchar(15);

删除表

?数据和结构都被删除
? 所有正在运行的相关事务被提交
? 所有相关索引被删除
? DROP TABLE 语句不能回滚

DROP TABLE dept80;

清空表

? TRUNCATE TABLE 语句:
① 删除表中所有的数据
② 释放表的存储空间

TRUNCATE TABLE detail_dept;

? TRUNCATE语句不能回滚
? 可以使用 DELETE 语句删除数据,可以回滚

改变对象的名称

? 执行RENAME语句改变表, 视图的名称

ALTER table dept
RENAME TO detail_dept;

? 必须是对象的拥有者

第四章数据增删改：

数据操纵语言

? DML(Data Manipulation Language –
数据操纵语言) 可以在下列条件下执行:
– 向表中插入数据
– 修改现存数据
– 删除现存数据
? 事务是由完成若干项工作的DML语句组成的

插入数据

INSERT 语句语法
? 使用 INSERT 语句向表中插入数据。

INSERT INTO table [(column [, column…])]
VALUES (value [, value…]);

? 使用这种语法一次只能向表中插入一条数据。
? 为每一列添加一个新值。
? 按列的默认顺序列出各个列的值。
? 在 INSERT 子句中随意列出列名和他们的值。
? 字符和日期型数据应包含在单引号中。

INSERT INTO departments(department_id, department_name, 
manager_id, location_id)
VALUES (70, 'Public Relations', 100, 1700);

INSERT INTO 
employees(employee_id,last_name,email,hire_date,job_id)
VALUES (300,’Tom’,’tom@126.com’,to_date(‘2012-3-
21’,’yyyy-mm-dd’),’SA_RAP’);

向表中插入空值

? 隐式方式: 在列名表中省略该列的值。

? 显示方式: 在VALUES 子句中指定空值。

插入指定的值

NOW()函数：记录当前系统的日期和时间。

从其它表中拷贝数据

? 在 INSERT 语句中加入子查询。


? 不必书写 VALUES 子句。
? 子查询中的值列表应与 INSERT 子句中的列名对
应

更新数据

UPDATE 语句语法
? 使用 UPDATE 语句更新数据。

UPDATE table
SET column = value [, column = value, …]
[WHERE condition];

? 可以一次更新多条数据。
? 如果需要回滚数据，需要保证在DML前，进行设置：SET AUTOCOMMIT = FALSE;
? 使用 WHERE 子句指定需要更新的数据。

? 如果省略 WHERE 子句，则表中的所有数据都将被更新

UPDATE copy_emp
SET department_id = 110;

删除数据

使用 DELETE 语句从表中删除数据。

DELETE FROM table
[WHERE condition];

? 使用 WHERE 子句删除指定的记录。

DELETE FROM departments
WHERE department_name = 'Finance';
1 row

? 如果省略 WHERE 子句，则表中的全部数据将被删除

DELETE FROM emp;

第五章数据查询：

基本 SELECT 语句

SELECT *| {[DISTINCT] column|expression [alias],…}
FROM table;

? SELECT 标识选择哪些列。
? FROM 标识从哪个表中选择。

选择全部列

选择特定的列

注 意:
? SQL 语言大小写不敏感。
? SQL 可以写在一行或者多行
? 关键字不能被缩写也不能分行
? 各子句一般要分行写。
? 使用缩进提高语句的可读性

列的别名

? 重命名一个列。
? 便于计算。
? 紧跟列名，也可以在列名和别名之间加入关键字
‘AS’，别名使用双引号，以便在别名中包含空
格或特殊的字符并区分大小写。

字符串

? 字符串可以是 SELECT 列表中的一个字符,数字,日
期。
? 日期和字符只能在单引号中出现。
? 每当返回一行时，字符串被输出一次。

显示表结构

使用 DESCRIBE 命令，表示表结构

DESC[RIBE] tablename

DESCRIBE employees

过滤和排序数据

过滤

? 使用WHERE 子句，将不满足条件的行过滤掉。

? WHERE 子句紧随 FROM 子句。

比较运算

其它比较运算

BETWEEN

使用 BETWEEN 运算来显示在一个区间内的值

IN

使用 IN运算显示列表中的值

LIKE

1. 使用 LIKE 运算选择类似的值
2. 选择条件可以包含字符或数字:
   ①% 代表零个或多个字符(任意个字符)。
   ②_ 代表一个字符。
   
3. ‘%’和‘-’可以同时使用。
   

NULL

使用 IS (NOT) NULL 判断空值。

逻辑运算

AND

AND 要求并的关系为真。

OR

OR 要求或关系为真。

NOT

排序

? 使用 ORDER BY 子句排序
– ASC（ascend）: 升序
– DESC（descend）: 降序
? ORDER BY 子句在SELECT语句的结尾。

降序排序

按别名排序

多个列排序

? 按照ORDER BY 列表的顺序排序

? 可以使用不在SELECT 列表中的列排序。

分组函数

什么是分组函数

分组函数作用于一组数据，并对一组数据返回一个值。

组函数类型

? AVG()
? COUNT()
? MAX()
? MIN()
? SUM()

1. 组函数语法
   
2. AVG（平均值）和 SUM （合计）函数
   可以对数值型数据使用AVG 和 SUM 函数。
   
3. MIN（最小值）和 MAX（最大值）函数
   可以对任意数据类型的数据使用 MIN 和 MAX 函数。
   
4. COUNT（计数）函数
   ①COUNT(*) 返回表中记录总数,适用于任意数据类型。
   
   ②COUNT(expr) 返回expr不为空的记录总数。
   

分组数据

GROUP BY 子句语法
1. 可以使用GROUP BY子句将表中的数据分成若干组

2. 在SELECT 列表中所有未包含在组函数中的列都应该包含
在 GROUP BY 子句中。

3. 包含在 GROUP BY 子句中的列不必包含在SELECT 列表中

使用多个列分组

1. 在GROUP BY子句中包含多个列
   

非法使用组函数

? 不能在 WHERE 子句中使用组函数。
? 可以在 HAVING 子句中使用组函数。

过滤分组

过滤分组： HAVING 子句

1. 行已经被分组。
2. 使用了组函数。
3. 满足HAVING 子句中条件的分组将被显示。
   
   

多表查询

表关系

常见的表关系分为以下三种：一对多(多对一)·、一对一、多对多



建表：

-- 部门表
create table dept(
	id int primary key auto_increment, -- 编号
	name varchar(14) 	  -- 部门名字
) ;
-- 员工表
create table emp(
	id int primary key auto_increment,-- 员工编号
	name varchar(10), -- 员工姓名										-
	dept_id int not null -- 部门编号
);
insert into dept values(10,'财务部');
insert into dept values(30,'销售部');
insert into dept values(40,'行政部');
insert into emp values(1,'刘一',10);
insert into emp values(2,'陈二',30);
insert into emp values(3,'张三',30);
insert into emp values(4,'李四',20);
insert into emp values(5,'王五',null);

1. 查询部门和部门对应的员工信息

select * from dept,emp;

上面的查询中存在大量错误的数据，一般我们不会直接使用这种查询。

笛卡尔积查询：所谓笛卡尔积查询就是指，查询两张表，其中一张表有m条记录，另一张表有n条记录，查询的结果是m*n条。

虽然笛卡尔积查询中包含大量错误数据，但我们可以通过where子句将错误数据剔除，保留下来的就是正确数据。

-- 通过where子句将笛卡尔积查询结果中错误的数据剔除,保留正确数据!
select * from dept,emp 
where emp.dept_id=dept.id;
-- 因为id在两张表中都存在,所以为了区分,必须在列名前面加上[表名.]

通过where子句将笛卡尔积查询中的错误数据剔除,保留正确的数据,这就是连接查询!

2. 区分重复的列名:
   ? 使用表名前缀在多个表中区分相同的列。
   ? 在不同表中具有相同列名的列可以用表的别名
   加以区分。
   ? 如果使用了表别名，则在select语句中需要使
   用表别名代替表名
   ? 表别名最多支持32个字符长度，但建议越少越
   好
3. 表的别名
   ? 使用别名可以简化查询。
   ? 使用表名前缀可以提高执行效率上面的查询可以换成下面的查询：

SELECT bt.id,NAME,boyname
FROM beauty bt,boys b;
WHERE bt.boyfriend_id=b.id ;

内连接

select * from dept inner join emp 
on emp.dept_id=dept.id;
-- 内连接查询,结果和上面的连接查询相同!

左外连接

查询【所有部门】及部门对应的员工，如果某个部门下没有员工，员工显示为null

-- 查询的结果中都是有员工的部门和有部门的员工
select * from dept, emp
where emp.dept_id=dept.id;
-- 如果要查询部门表中的所有部门,以及部门对应的员工
select * from dept left join emp
on emp.dept_id=dept.id;

左外连接查询：可以将左边表中的所有记录都查询出来，右边表只显示和左边相对应的数据，如果左边表中某些记录在右边没有对应的数据，右边显示为null即可。

右外连接

查询【所有员工】及员工所属的部门，如果某个员工没有所属部门，部门显示为null即可

-- 查询的结果中都是有员工的部门和有部门的员工
select * from dept, emp
where emp.dept_id=dept.id;
-- 如果要查询所有员工及员工对应的部门
select * from dept right join emp
on emp.dept_id=dept.id;

右外连接查询：可以将右边表中的所有记录都查询出来，左边表只显示和右边相对应的数据，如果右边表中某些记录在左边没有对应的数据，可以显示为null。

扩展：如果想将两张表中的所有数据都查询出来(左外+右外并去除重复记录)，可以使用全外连接查询，但是mysql又不支持全外连接查询。

select * from dept left join emp on emp.dept_id=dept.id
union
select * from dept right join emp on emp.dept_id=dept.id;

可以使用union将左外连接查询的结果和右外连接查询的结果合并在一起，并去除重复的记录。例如

需要注意的是：union可以将两条SQL语句执行的结果合并，但是有前提:
1）两条SQL语句查询的结果列数必须一致
2）两条SQL语句查询的结果列名、顺序也必须一致
并且union默认就会将两个查询中重复的记录去除(如果不希望去除重复记录，可以使用union all)。

连接多个表

? 连接 n个表,至少需要 n-1个连接条件。 例如：连接三个表，至少需要两个连接条件。

SQL99：使用ON 子句创建连接

? 自然连接中是以具有相同名字的列为连接条件的。
? 可以使用 ON 子句指定额外的连接条件。
? 这个连接条件是与其它条件分开的。
? ON 子句使语句具有更高的易读性。

Join连接总结

第六章常见函数：

字符函数

1. 大小写控制函数 ： 这类函数改变字符的大小写。

2. 字符控制函数： 这类函数控制字符:

数学函数

? ROUND: 四舍五入

? TRUNCATE: 截断

? MOD: 求余

日期函数

? now：获取当前日期
? str_to_date: 将日期格式的字符转换成指定格式的日期
STR_TO_DATE(‘9-13-1999’,‘%m-%d-%Y’)
1999-09-13
? date_format:将日期转换成字符
DATE_FORMAT(‘2018/6/6’,‘%Y年%m月%d日’)
2018年06月06日

条件表达式

? 在 SQL 语句中使用IF-THEN-ELSE 逻辑
? 使用方法: – CASE 表达式

CASE expr WHEN comparison_expr1 THEN return_expr1
[WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr]
END

第七章子查询：

概念：

出现在其他语句内部的select语句，称为子查询或内查询
内部嵌套其他select语句的查询，称为外查询或主查询
示例：

select first_name from employees where 
department_id in(
select department_id from departments
where location_id=1700
)

注意事项:
? 子查询要包含在括号内。
? 将子查询放在比较条件的右侧。
? 单行操作符对应单行子查询，多行操作符对应
多行子查询

子查询类型

单行子查询

? 只返回一行。
? 使用单行比较操作符。

子查询语法

? 子查询 (内查询) 在主查询之前一次执行完成。
? 子查询的结果被主查询(外查询)使用 。

使用子查询解决问题

谁的工资比 Abel 高?

子查询中的 HAVING 子句

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

多行子查询

? 返回多行。
? 使用多行比较操作符。

使用 ANY 操作符:

使用 ALL 操作符

总 结:在查询时基于未知的值时，应使用子查询。

第八章数据类型：

整型

小数

位类型

char和varchar类型 说明：用来保存MySQL中较短的字符串

binary和varbinary类型
说明：类似于char和varchar，不同的是它们包含二进制字符串而不包含非二
进制字符串

Enum类型

说明:又称为枚举类型哦，要求插入的值必须属于列表中指定的值之一。
如果列表成员为1~255，则需要1个字节存储
如果列表成员为255~65535，则需要2个字节存储
最多需要65535个成员

Set类型

说明：和Enum类型类似，里面可以保存0~64个成员。和Enum类型最大的区
别是：SET类型一次可以选取多个成员，而Enum只能选一个
根据成员个数不同，存储所占的字节也不同

日期类型

datetime和timestamp的区别

1、Timestamp支持的时间范围较小，取值范围：19700101080001——2038年的某个时间Datetime的取值范围：1000-1-1 ——9999—12-31
2、timestamp和实际时区有关，更能反映实际的日期，而datetime则只能反映出插入时的当地时区
3、timestamp的属性受Mysql版本和SQLMode的影响很大

第九章约束与分页：

什么是约束

? 为了保证数据的一致性和完整性，SQL规范以约束的方式对表数据进行额外的条件限制。
? 约束是表级的强制规定
? 可以在创建表时规定约束（通过 CREATE TABLE 语句），或者在表创建之后也可以（通过 ALTER TABLE 语句）

有以下六种约束

– NOT NULL 非空约束，规定某个字段不能为空
– UNIQUE 唯一约束，规定某个字段在整个表中是唯一的
– PRIMARY KEY 主键(非空且唯一) – FOREIGN KEY 外键
– CHECK 检查约束
– DEFAULT 默认值
注意： MySQL不支持check约束，但可以使用check约束，而没有任何效果

? 根据约束数据列的限制，约束可分为：
– 单列约束：每个约束只约束一列
– 多列约束：每个约束可约束多列数据
? 根据约束的作用范围，约束可分为：
– 列级约束只能作用在一个列上，跟在列的定义后面
– 表级约束可以作用在多个列上，不与列一起，而是单独定义

NOT NULL 约束

1. 非空约束用于确保当前列的值不为空值，非空约束只能出现在表对象的列上。
2. Null类型特征：
   ①所有的类型的值都可以是null，包括int、float等数据类型
   ②空字符串””不等于null，0也不等于null
   
   一、创建 not null 约束：

CREATE TABLE emp(
id INT(10) NOT NULL,
NAME VARCHAR(20) NOT NULL DEFAULT ‘abc’,
sex CHAR NULL
);

二、增加 not null 约束：

ALTER TABLE emp
MODIFY sex VARCHAR(30) NOT NULL;

三、取消 not null 约束：

ALTER TABLE emp
MODIFY sex VARCHAR(30) NULL;

四、取消 not null 约束，增加默认值：

ALTER TABLE emp
MODIFY NAME VARCHAR(15) DEFAULT ‘abc’ NULL;

UNIQUE 约束

? 唯一约束，允许出现多个空值：NULL。
? 同一个表可以有多个唯一约束，多个列组合的约束。
在创建唯一约束的时候，如果不给唯一约束名称，就
默认和列名相同。
? MySQL会给唯一约束的列上默认创建一个唯一索引

CREATE TABLE USER(
id INT NOT NULL,
NAME VARCHAR(25),
PASSWORD VARCHAR(16),
#使用表级约束语法
CONSTRAINT uk_name_pwd UNIQUE(NAME,PASSWORD)
);

? 表示用户名和密码组合不能重复

一、添加唯一约束

ALTER TABLE USER
ADD UNIQUE(NAME,PASSWORD);

ALTER TABLE USER
ADD CONSTRAINT uk_name_pwd UNIQUE(NAME,PASSWORD) ;

ALTER TABLE USER
MODIFY NAME VARCHAR(20) UNIQUE;

二、删除约束

ALTER TABLE USER
DROP INDEX uk_name_pwd;

PRIMARY KEY 约束

? 主键约束相当于唯一约束+非空约束的组合，主键约束列不允许重复，也不允许出现空值
? 如果是多列组合的主键约束，那么这些列都不允许为空值，并且组合的值不允许重复。
? 每个表最多只允许一个主键，建立主键约束可以在列级别创建，也可以在表级别上创建。
? MySQL的主键名总是PRIMARY，当创建主键约束时，系统默认会在所在的列和列组合上建立对应的唯一索引。

1. 列级模式

CREATE TABLE emp4(
id INT AUTO_INCREMENT PRIMARY KEY,
NAME VARCHAR(20)
);

2. 表级模式

CREATE TABLE emp5(
id INT NOT NULL AUTO_INCREMENT,
NAME VARCHAR(20),
pwd VARCHAR(15),
CONSTRAINT emp5_id_pk PRIMARY KEY(id)
);

3. 组合模式

CREATE TABLE emp6(
id INT NOT NULL,
NAME VARCHAR(20),
pwd VARCHAR(15),
CONSTRAINT emp7_pk PRIMARY KEY(NAME,pwd)
);

一、删除主键约束

ALTER TABLE emp5
DROP PRIMARY KEY;

二、添加主键约束

ALTER TABLE emp5
ADD PRIMARY KEY(NAME,pwd);

三、修改主键约束

ALTER TABLE emp5
MODIFY id INT PRIMARY KEY;

FOREIGN KEY 约束

? 外键约束是保证一个或两个表之间的参照完整性，外键是构建于一个表的两个字段或是两个表的两个字段之间的参照关系。
? 从表的外键值必须在主表中能找到或者为空。当主表的记录被从表参照时，主表的记录将不允许删除，如果要删除数据，需要先删除从表中依赖该记录的数据，然后才可以删除主表的数据。
? 还有一种就是级联删除子表数据。
? 注意：外键约束的参照列，在主表中引用的只能是主键或唯一键约束的列
? 同一个表可以有多个外键约束
一、创建外键约束:
主表

CREATE TABLE dept(
dept_id INT AUTO_INCREMENT PRIMARY KEY,
dept_name VARCHAR(20)
);

从表

CREATE TABLE emp(
emp_id INT AUTO_INCREMENT PRIMARY KEY,
last_name VARCHAR(15),
dept_id INT,
); CONSTRAINT emp_dept_id_fk FOREIGN KEY(dept_id)
REFERENCES dept(dept_id)

二、创建多列外键组合，必须使用表级约束:
主表

CREATE TABLE classes(
id INT,
NAME VARCHAR(20),
number INT,
PRIMARY KEY(NAME,number)
);

从表

CREATE TABLE student(
id INT AUTO_INCREMENT PRIMARY KEY,
classes_name VARCHAR(20),
classes_number INT,
FOREIGN KEY(classes_name,classes_number)
REFERENCES classes(NAME,number)
);

三、删除外键约束：

ALTER TABLE emp
DROP FOREIGN KEY emp_dept_id_fk;

四、增加外键约束：

ALTER TABLE emp
ADD [CONSTRAINT emp_dept_id_fk] FOREIGN KEY(dept_id)
REFERENCES dept(dept_id);

FOREIGN KEY 约束的关键字

– FOREIGN KEY: 在表级指定子表中的列
– REFERENCES: 标示在父表中的列
–ON DELETE CASCADE(级联删除): 当父表中的列被删除
时，子表中相对应的列也被删除
–ON DELETE SET NULL(级联置空): 子表中相应的列置空

CREATE TABLE student(
id INT AUTO_INCREMENT PRIMARY KEY,
NAME VARCHAR(20),
classes_name VARCHAR(20),
classes_number INT,
/表级别联合外键/
FOREIGN KEY(classes_name, classes_number)
REFERENCES classes(NAME, number) ON DELETE CASCADE);

CHECK 约束

? MySQL可以使用check约束，但check约束对数据验证没有任何作用,添加数据时，没有任何错误或警告

CREATE TABLE temp(
id INT AUTO_INCREMENT,
NAME VARCHAR(20),
age INT CHECK(age > 20),
PRIMARY KEY(id)
);

分页

MySQL中使用limit实现分页
? 背景
查询返回的记录太多了，查看起来很不方便，怎么样能够实现分页查询呢？
? 分页原理
所谓分页显示，就是将数据库中的结果集，一段一段显示出来需要的条件

? 怎么分段，当前在第几段（每页有几条，当前在第几页）
前10条记录：

SELECT * FROM table LIMIT 0,10;

第11至20条记录：

SELECT * FROM table LIMIT 10,10;

第21至30条记录：

SELECT * FROM table LIMIT 20,10;

公式：
（当前页数-1）*每页条数，每页条数

SELECT * FROM table LIMIT(PageNo - 1)*PageSize,PageSize;

注意： limit子句必须放在整个查询语句的最后！

第十章事务：

概念：

事务由单独单元的一个或多个SQL语句组成，在这个单元中，每个MySQL语句是相互依赖的。 而整个单独单元作为一个不可分割的整体，如果单元中某条SQL语句一
旦执行失败或产生错误，整个单元将会回滚。所有受到影响的数据将返回到事物开始以前的状态；如果单元中的所有SQL语句均执行成功，则事物被顺利执行。

MySQL 中的存储引擎：

1、概念：在mysql中的数据用各种不同的技术存储在文件（或内存）中。
2、通过show engines；来查看mysql支持的存储引擎。
3、 在mysql中用的最多的存储引擎有：innodb，myisam ,memory 等。其中innodb支持事务，而myisam、memory等不支持事务

事务的ACID(acid)属性

1. 原子性（Atomicity）
   原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

2. 一致性（Consistency）
   事务必须使数据库从一个一致性状态变换到另外一个一致性状态。

3. 隔离性（Isolation）
   事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

4. 持久性（Durability）
   持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响

5. 以第一个 DML 语句的执行作为开始

6. 以下面的其中之一作为结束: ? COMMIT 或 ROLLBACK 语句
   ①DDL 或 DCL 语句（自动提交）
   ②用户会话正常结束
   ③系统异常终了

数据库的隔离级别

1. 对于同时运行的多个事务, 当这些事务访问数据库中相同的数据时, 如果没有采取必要的隔离机制, 就会导致各种并发问题:
   ① 脏读: 对于两个事务 T1, T2, T1 读取了已经被 T2 更新但还没有被提交的字段. 之后, 若 T2 回滚, T1读取的内容就是临时且无效的.
   ② 不可重复读: 对于两个事务T1, T2, T1 读取了一个字段, 然后 T2 更新了该字段. 之后, T1再次读取同一个字段, 值就不同了.
   ③ 幻读: 对于两个事务T1, T2, T1 从一个表中读取了一个字段, 然后 T2 在该表中插 入了一些新的行. 之后, 如果 T1 再次读取同一个表, 就会多出几行.
2. 数据库事务的隔离性: 数据库系统必须具有隔离并发运行各个事务的能力,
   使它们不会相互影响, 避免各种并发问题.
3. 一个事务与其他事务隔离的程度称为隔离级别. 数据库规定了多种事务隔
   离级别, 不同隔离级别对应不同的干扰程度, 隔离级别越高, 数据一致性就
   越好, 但并发性越弱.
4. 数据库提供的 4 种事务隔离级别:
   
   ①Oracle 支持的 2 种事务隔离级别：READ COMMITED, SERIALIZABLE。 Oracle 默认的事务隔离级别为: READ COMMITED
   ②Mysql 支持 4 种事务隔离级别. Mysql 默认的事务隔离级别为: REPEATABLE REA

在 MySql 中设置隔离级别

1. 每启动一个 mysql 程序, 就会获得一个单独的数据库连接. 每个数据库连接都有一个全局变量 @@tx_isolation, 表示当前的事务隔离级别.
2. 查看当前的隔离级别: SELECT @@tx_isolation;
3. 设置当前 mySQL 连接的隔离级别:

. set transaction isolation level read committed;

4. 设置数据库系统的全局的隔离级别:

set global transaction isolation level read committed;

第十一章视图：

概念：

MySQL从5.0.1版本开始提供视图功能。一种虚拟存在的表，行和列的数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的，只保存了sql逻辑，不保存查询结果

应用场景：

– 多个地方用到同样的查询结果
– 该查询结果使用的sql语句较复杂
示例：

CREATE VIEW my_v1
AS
SELECT studentname,majorname
FROM student s
INNER JOIN major m
ON s.majorid=m.majorid
WHERE s.majorid=1;

视图的好处:

? 重用sql语句
? 简化复杂的sql操作，不必知道它的查询细节
? 保护数据，提高安全性

一、创建视图的语法：

create [or replace] view view_name
As select_statement
[with|cascaded|local|check option]

create  view view_name
AS 
select * from dept;

二、修改视图的语法：

alter view view_name
As select_statement
[with|cascaded|local|check option]

alter   view view_name
AS 
select name from dept;

三、视图的可更新性和视图中查询的定义有关系，以下类型的视图是不能更新的。
? 包含以下关键字的sql语句：分组函数、distinct、group by、having、union或者union all
? 常量视图
? Select中包含子查询
? join
? from一个不能更新的视图
? where子句的子查询引用了from子句中的表
四、删除视图的语法：
用户可以一次删除一个或者多个视图，前提是必须有该视图的drop权限

drop view [if exists] view_name,view_name …[restrict|cascade]

drop view if exists view_name;

五、查看视图的语法：

show tables;

如果需要查询某个视图的定义，可以使用show create view命令进行查看：

show create view view_name;

第十二章存储过程和函数：

概念：

事先经过编译并存储在数据库中的一段sql语句的集合

使用好处：
1、简化应用开发人员的很多工作
2、减少数据在数据库和应用服务器之间的传输
3、提高了数据处理的效率

一、创建存储过程：

create procedure 存储过程名 ([proc_parameter[,…]])
[characteristic…]routine_body

delimiter$$
create procedure d(in id varchar(20))
begin
select name from emp
where dept_id=id;
end $$
delimiter ;

调用： call 存储过程名(参数列表)

call d(20);

二、创建函数：

create function 函数名([func_parameter[,…]])
returns type
[characteristic…]routine_body

delimiter $$
create function func1(sid varchar(20)) returns varchar(20) #在函数名后面一定要加上returns 函数返回类型
READS SQL DATA    #一定加上这句不然会报错
begin
declare cid varchar(20);      #在函数中定义一个变量，用来接收函数返回值
select emp.name into cid   #把查询结果赋值给cid变量
from emp
where emp.dept_id=sid;
return cid;                   #函数返回值，返回cid
end $$
delimiter;

调用： Select 函数名(参数列表)

select func1(20);

三、修改存储过程：

alter procedure 存储过程名 [charactristic…]

四、修改函数：

alter function 函数名 [charactristic…]

五、删除存储过程或函数：
说明：一次只能删除一个存储过程或者函数，并且要求有该过程或函数的alter routine 权限

drop procedure [if exists] 存储过程名
drop function [if exists] 函数名

六、查看存储过程或函数

1.查看存储过程或函数的状态：

show {procedure|function} status like 存储过程或函数名

2.查看存储过程或函数的定义：

show create {procedure|function} 存储过程或函数名

3.通过查看information_schema.routines了解存储过程和函数的信息（了解）

select * from rountines where rounine_name =存储过程名|函数名

第十三章流程控制结构：

case结构——作为表达式

case结构——作为独立的语句

循环结构