一、约束定义：
在MYSQL数据库中，建表时就可以进行对表的各项进行一些操作，例如设置主键或者非空约束，这里主要讲讲如何在建表后进行添加约束和删除约束。
约束条件在创建表时可以使用， 也可以修改表的时候添加约束条件。
约束是一种限制，它通过对表的行或列的数据做出限制，来确保表的数据的完整性、唯一性。
二、约束分类--5种
※ 主键约束 ：primary key
※ 唯一性约束：unique key
※ 外键约束：foreign key
※ 非空约束：not null
※ 默认值约束：default
1.主键约束 ：primary key(pk)
- 每张数据表只能存在一个主键
- 主键保证记录的唯一性，主键的值不重复
- 主键分为“单字段主键”和“多字段联合主键”
- 主键自动为 NOT NULL
-- 基本模式
create table temp(
id int primary key,
name varchar(20)
);
-- 组合模式
create table temp(
id int ,
name varchar(20),
pwd varchar(20),
primary key(id, name)
);
-- 删除主键约束
alter table temp drop primary key;
-- 添加主键约束
alter table temp add primary key(id,name);
-- 修改主键约束
alter table temp modify id int primary key；
2.外键约束：foreign key(fk)
- 保持数据的一致性，完整性
- 实现1对1或1对n关系
- 要求在从表中设置外键关系
- 建立表与表之间的关系，建立参照完整性，一个表可以有多个外键，每个外键必须参照另一个主键。
注意：
1.通常先创建主表，再创建从表
2.插入数据时，先插入主表，再插入从表
删除数据时，先删除从表，再删除主表
-- 基本模式
-- 主表
create table temp(
id int primary key,
name varchar(20)
);
-- 副表
create table temp2(
id int,
name varchar(20),
classes_id int,
foreign key(id) references temp(id)
);
-- 多列外键组合，必须用表级别约束语法
-- 主表
create table classes(
id int,
name varchar(20),
number int,
primary key(name,number)
);
-- 副表
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)
);
-- 删除外键约束
alter table student drop foreign key student_id;
-- 增加外键约束
alter table student add foreign key(classes_name, classes_number) references classes(name, number);
3. 唯一性约束：unique key
- 唯一约束可以保证记录的唯一性
- 唯一约束的字段可以为空值（NULL）
- 每张数据表可以存在多个唯一约束
-- 创建表时设置，表示用户名、密码不能重复
create table temp(
id int not null ,
name varchar(20),
password varchar(10),
unique(name,password)
);
-- 添加唯一约束
alter table temp add unique (name, password);
-- 修改唯一约束
alter table temp modify name varchar(25) unique;
-- 删除约束
alter table temp drop index name;
4.非空约束：not null
- 强制列不能为 NULL 值，约束强制字段始终包含值。
- 这意味着，如果不向字段添加值，就无法插入新记录或者更新记录。
Null类型特征：
所有的类型的值都可以是null，包括int、float 等数据类型
-- 创建table表，ID 为非空约束，name 为非空约束 且默认值为abc
create table temp(
id int not null,
name varchar(255) not null default 'abc',
sex char null
)；
-- 增加非空约束
alter table temp
modify sex varchar(2) not null;
-- 取消非空约束
alter table temp modify sex varchar(2) null;
-- 取消非空约束，增加默认值
alter table temp modify sex varchar(2) default 'abc' null;
5.默认值约束：default
- 当插入记录时，如果没有明确为字段赋值，则自动赋予默认值
-- 创建table表，ID 为非空约束，name 为非空约束 且默认值为abc
create table temp(
id int not null,
name varchar(255) not null default 'abc',
sex char null
)；
-- 增加非空约束
alter table temp
modify sex varchar(2) not null;
-- 取消非空约束
alter table temp modify sex varchar(2) null;
-- 取消非空约束，增加默认值
alter table temp modify sex varchar(2) default 'abc' null;
主键和唯一的区别：
外键：
1.要求在从表中设置外键关系
2.从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求。
3.主表的关联列必须时一个Key（一般为主键或唯一，外键也可以但无意义）
4.插入数据时，先插入主表，再插入从表
删除数据时，先删除从表，再删除主表
三、数据库设计三范式
设计数据库表的时候所依据的规范,共三个规范:
第一范式: 要求每一个字段原子性不可在分
第二范式: 要求所有非主键字段完全依赖主键,不能产生部分依赖
第三范式: 所有非主键字段和主键字段之间不能产生传递依赖
四、表之间的关系
1.表关系的概念
在现实生活中,实体与实体之间肯定是有关系的。比如：员工和部门，老师和学生等。那么我们在设计表的时候，就应该体现出表与表之间的这种关系。
2. 一对一
一对一（1:1） ：在实际的开发中应用不多，因为一对一可以创建成一张表。
- 两种建表原则：
===========例子一：用户表和管理员表=========
先建被关联的表
create table user(
id int primary key auto_increment, #主键自增
name char(10)
);
在建关联表
create table admin(
id int primary key auto_increment,
user_id int unique,
password varchar(16),
foreign key(user_id) references user(id)
on delete cascade
on update cascade
);
insert into user(name) values('susan1'),
('susan2'),
('susan3'),
('susan4'),
('susan5'),
('susan6');
insert into admin(user_id,password) values(4,'sds156'),
(2,'531561'),
(6,'f3swe');
3. 一对多
- 建表原则:
在从表(多方)创建一个字段,字段作为外键指向主表(一方)的主键
4. 多对多
多对多（m:n） 例如：老师和学生，学生和课程，用户和角色
- 建表原则:
多对多关系建表原则: 需要创建第三张表，中间表中至少两个字段，这两个字段分别作为外键指向各自一方的主键。
书和作者（我们可以再创建一张表，用来存book和author两张表的关系）
要把book_id和author_id设置成联合唯一
联合唯一：unique(book_id，author_id)
联合主键：alter table t1 add primary key(id,avg)
5. 表与表之间的关系小结
6.如何写？？？
1、先确立关系
2、找到多的一方，吧关联字段写在多的一方
一、多对一或者一对多（左边表的多条记录对应右边表的唯一一条记录）
需要注意的：1.先建被关联的表，保证被关联表的字段必须唯一。
2.在创建关联表，关联字段一定保证是要有重复的。
这是一个书和出版社的一个例子，书要关联出版社（多个书可以是一个出版社，一个出版社也可以有好多书）。
谁关联谁就是谁要按照谁的标准。
五、 多表查询
1.内连接
内连接的语法格式如下：
SELECT <字段名> FROM <表1> INNER JOIN <表2> [ON子句]
语法说明如下。- 字段名：需要查询的字段名称。
- <表1><表2>：需要内连接的表名。
- INNER JOIN ：内连接中可以省略 INNER 关键字，只用关键字 JOIN。
- ON 子句：用来设置内连接的连接条件。
2.左外连接
左外连接又称为左连接，使用 LEFT OUTER JOIN 关键字连接两个表，并使用 ON 子句来设置连接条件。
左连接的语法格式如下：
SELECT <字段名> FROM <表1> LEFT OUTER JOIN <表2> <ON子句>
语法说明如下。
- 字段名：需要查询的字段名称。
- <表1><表2>：需要左连接的表名。
- LEFT OUTER JOIN：左连接中可以省略 OUTER 关键字，只使用关键字 LEFT JOIN。
- ON 子句：用来设置左连接的连接条件，不能省略。
上述语法中，“表1”为基表，“表2”为参考表。左连接查询时，可以查询出“表1”中的所有记录和“表2”中匹配连接条件的记录。如果“表1”的某行在“表2”中没有匹配行，那么在返回结果中，“表2”的字段值均为空值（NULL）。
3.右外连接
右外连接又称为右连接，右连接是左连接的反向连接。使用 RIGHT OUTER JOIN 关键字连接两个表，并使用 ON 子句来设置连接条件。
右连接的语法格式如下：
SELECT <字段名> FROM <表1> RIGHT OUTER JOIN <表2> <ON子句>
语法说明如下。
- 字段名：需要查询的字段名称。
- <表1><表2>：需要右连接的表名。
- RIGHT OUTER JOIN：右连接中可以省略 OUTER 关键字，只使用关键字 RIGHT JOIN。
- ON 子句：用来设置右连接的连接条件，不能省略。
与左连接相反，右连接以“表2”为基表，“表1”为参考表。右连接查询时，可以查询出“表2”中的所有记录和“表1”中匹配连接条件的记录。如果“表2”的某行在“表1”中没有匹配行，那么在返回结果中，“表1”的字段值均为空值（NULL）。
4.子查询（就是嵌套）
1.什么是子查询
定义:把一个查询的结果作为另外一个查询语句的查询条件
2.子查询出现的位置
where后,把select查询出的结果当做另外一个select的条件值
from后,把查询出的结果当作一个新表
子查询在 WHERE 中的语法格式如下：
WHERE <表达式> <操作符> (子查询)
其中，操作符可以是比较运算符（<”、“<=”、“>”、“>=”、“！=”等）和 IN、NOT IN、EXISTS、NOT EXISTS 、 ANY（SOME）等关键字。
1、单行子查询 （就是查询结果是一个值用比较运算符）：单行子查询是指子查询的返回结果只有一行数据。当主查询语句的条件语句中引用子查询结果时可用单行比较符号（＝, >, <, >=, <=, <>）来进行比较。
例：
select ename,deptno,sal
from emp
where deptno=(select deptno from dept where loc='NEW YORK')；
2、多行子查询（就是查询结果是多个值用in，all，any）：多行子查询即是子查询的返回结果是多行数据。当主查询语句的条件语句中引用子查询结果时必须用多行比较符号（IN，ALL,ANY）来进行比较。其中，IN的含义是匹配子查询结果中的任一个值即可（"IN"
操作符，能够测试某个值是否在一个列表中），ALL则必须要符合子查询的所有值才可，ANY要符合子查询结果的任何一个值即可。
注意：ALL和ANY 操作符不能单独使用，而只能与单行比较符（=、>、< 、>= 、<= 、<>）结合使用。
1）IN | NOT IN
当表达式与子查询返回的结果集中的某个值相等时，返回 TRUE，否则返回 FALSE；若使用关键字 NOT，则返回值正好相反。2）EXISTS | NOT EXISTS
用于判断子查询的结果集是否为空，若子查询的结果集不为空，返回 TRUE，否则返回 FALSE；若使用关键字 NOT，则返回的值正好相反。
1. 使用IN进行子查询
使用in进行子查询，这个我们在日常写sql的时候是经常遇到的。in的意思就是指定的一个值是否在这个集合中，如何在就返回TRUE；否则就返回FALSE了。
in是“=any”的别名，在使用“=any”的地方，我们都可以使用“in”来进行替换。
有了in，肯定就有了not in；not in并不是和<>any是同样的意思，not in和<>all是一个意思。
2. ANY进行子查询
any关键词的意思是“对于子查询返回的列中的任何一个数值，如果比较结果为TRUE，就返回TRUE”。
好比“10 >any(11, 20, 2, 30)”，由于10>2，所以，该该判断会返回TRUE；只要10与集合中的任意一个进行比较，得到TRUE时，就会返回TRUE。
any的意思比较好明白，直译就是任意一个，只要条件满足任意的一个，就返回TRUE。
any和all的区别：
any关键字表示满足其中的任意一个条件，使用any关键字时，只要满足内层查询语句结果的的任意一个，就可以通过该条件来执行外层查询语句。
all和any刚好是相反的，all关键字表示满足所有结果，使用all关键字，要满足内层查询语句的所有结果，才可以通过该条件来执行外层查询语句。
3. 使用SOME进行子查询
some是any的别名，用的比较少。
4. 使用ALL进行子查询
all必须与比较操作符一起使用。all的意思是“对于子查询返回的列中的所有值，如果比较结果为TRUE，则返回TRUE”。
好比“10 >all(2, 4, 5, 1)”，由于10大于集合中的所有值，所以这条判断就返回TRUE；而如果为“10 >all(20, 3, 2, 1, 4)”，这样的话，由于10小于20，所以该判断就会返回FALSE。
<>all的同义词是not in，表示不等于集合中的所有值，这个很容易和<>any搞混，平时多留点心就好了。
5. 带exists的子查询
exists: 是否存在的意思, exists子查询就是用来判断某些条件是否满足(跨表),
exists是接在where之后
exists返回的结果只有0和1（也就是true和false）.
例：如果存在成绩大于90的人则列出整个表的记录
select *
from student
where exists
(select *from student where score>90);六、 mysql事务
1，事务是什么？
事务是一组SQL语句（包含一个或者多个SQL语句），要么全部执行成功，要么全部执行失败。通常一个事务对应一个完整的业务(例如银行账户转账业务，该业务就是一个最小的工作单元)。
事务是MySQL区别于NoSQL的重要特征，是保证关系型数据库数据一致性的关键技术。事务可看作是对数据库操作的基本执行单元，可能包含一个或者多个SQL语句。这些语句在执行时，要么都执行，要么都不执行。
事务中一些概念：
事务（Transaction）：一批操作（一组DML）
开启事务：Start Transaction
事务的提交：COMMIT
事务的回滚：ROLLBACK
事务的关闭：CLOSE
禁用或启用事务的自动提交模式：SET AUTOCOMMIT
- SET AUTOCOMMIT=0 禁止自动提交
- SET AUTOCOMMIT=1 开启自动提交
默认情况下一个SQL语句为一个事务。
关于事务的回滚需要注意：只能回滚insert、delete和update语句，不能回滚select（回滚select没有任何意义），对于create、drop、alter这些无法回滚事务只对DML有效果。
注意：rollback，或者commit后事务就结束了。
如何使用：
（1）在执行sql语句之前，我们要开启事务 start transaction;
（2）正常执行我们的sql语句
（3）当sql语句执行完毕，存在两种情况：
1，全都成功，我们要将sql语句对数据库造成的影响提交到数据库中，committ
2，某些sql语句失败，我们执行rollback（回滚），将对数据库操作赶紧撤销
2，事务的ACID特性--4大特性
● 原子性（Atomicity）
整个事务中的所有操作，必须作为一个单元全部完成（或全部取消）。
事务是最小单元，不可再分，要么全部执行成功，要么全部失败回滚。
● 一致性（Consistency）
在事务开始之前与结束之后，数据库都保持一致状态。
一致性是指事务必须使数据库从一个一致的状态变到另外一个一致的状态，也就是执行事务之前和之后的状态都必须处于一致 的状态。不一致性包含三点：脏读，不可重复读，幻读
● 隔离性(Isolation)
一个事务不会影响其他事务的运行。
隔离性是指当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所 干扰，多个并发事务之间要相互隔离
● 持久性(Durability)
在事务完成以后，该事务对数据库所作的更改将持久地保存在数据库之中，并不会被回滚。
DBMS（数据库管理系统）对数据的修改是永久性的。
3，事务的并发读写问题
当数据库出现有多个事务同时执行时（也就是并发情况下），就可能出现脏读，幻读，不可重复读等问题。
（1）脏读：当前事务中读到其他事务未提交的数据，也就是脏数据。
以上图为例，事务A在读取文章的阅读量时，读取到了事务B为提交的数据。如果事务B最后没有顺利提交，导致事务回滚，那么实际上阅读量并没有修改成功，而事务A却是读到的修改后的值，显然不合情理。
（2）不可重复读：在事务A中先后两次读取同一个数据，但是两次读取的结果不一样。脏读与不可重复读的区别在于：前者读到的是其他事务未提交的数据，后者读到的是其他事务已提交的数据。
以上图为例，事务A在先后读取文章阅读量的数据时，结果却不一样。说明事务A在执行的过程中，阅读量的值被其它事务给修改了。这样使得数据的查询结果不再可靠，同样也不合实际。
（3）幻读：在事务A中按照某个条件先后两次查询数据库，两次查询结果的行数不同，这种现象称为幻读。不可重复读与幻读的区别可以通俗的理解为：前者是数据变了，后者是数据的行数变了。
以上图为例，当对0<阅读量<100的文章进行查询时，先查到了一个结果，后来查询到了两个结果。这表明同一个事务的查询结果数不一，行数不一致。这样的问题使得在根据某些条件对数据筛选的时候，前后筛选结果不具有可靠性。
4，事务的四个隔离级别
当数据库出现有多个事务同时执行时，就可能出现脏读，幻读，不可重复读等问题，隔离级别就是为了解决这些问题的。隔离的越严实，效率就越低，并发越低，安全性越高。
脏读：一个事务处理过程里读取了另一个未提交的事务中的数据
可重复读：一个事务在它运行期间，两次查找相同的表，出现了不同的数据
幻读：在一个事务中读取到了别的事务插入的数据，导致前后不一致
A事务读取了B事务已经提交的新增数据。和不可重复读的区别，这里是新增，不可重复读是更改（或删除）。 这两种情况对策是不一样的，对于不可重复读，只需要采取行级锁防止该记录数据被更改或删除，然而对于幻读必 须加表级锁，防止在这个表中新增一条数据。
读未提交：（read uncommitted）,可能发生脏读、不可重复读和幻读
读提交：（read committed）可能发生不可重复读和幻读
可重复读（repeatable read）可能发生幻读
串行化（serializable ）都不可能发生
-
读未提交（read uncommitted，RU）
一个事务还未提交时，它做的变更就可以被别的事务看到。 -
读提交（read committed，RC）
事务提交以后，它做的变更才能被其它事务看到。但是在这个事务未提交之前，数据库中发生的变更，这个事务也能看见。 -
可重复度（repeatable read，RR）
事务总是只能看见在启动的那个时刻，数据库的状态。事务未提交之前做的变更，其它事务看不见。事务执行期间，数据库中已经发生的变更，这个事务也看不见。只能看见事务刚启动时刻，数据库的状态。 -
串行化（serializable）
事务对某一行的操作会加锁，“写”会加“写锁”，“读”会加“读锁”，在锁释放掉之前，其它的事务都无法都这一行的记录进行操作。必须等之前的事务执行完毕，释放锁。后面的事务又会重新加锁。
5，事务的启动方式
- 显式启动：
begin或start transaction。配套的提交语句是commit，回滚语句是rollback。 - 也可以使用
set autocommit=0，此时不需要显式启动，比如执行了一个select语句就直接启动了事务，但是需要再执行一条commit来提交；
因此建议设置set autocommit=1，此时需要用begin来开启事务，如果觉得多了一次交互，比较麻烦的话，也可以使用commit work and chain，表示提交当前事务，并且再启动一个新的事务，这样就只有一次begin了。
6，锁机制
事务之间的隔离，是通过锁机制实现的。当一个事务需要对数据库中的某行数据进行修改时，需要先给数据加锁；加了锁的数据，其它事务是不运行操作的，只能等待当前事务提交或回滚将锁释放。
锁机制并不是一个陌生的概念，在许多场景中都会利用到不同实现的锁对数据进行保护和同步。而在MySQL中，根据不同的划分标准，还可将锁分为不同的种类。
按照粒度划分：行锁、表锁、页锁
按照使用方式划分：共享锁、排它锁
按照思想划分：悲观锁、乐观锁
锁机制的知识点很多，由于篇幅不好全部展开讲。这里对按照粒度划分的锁进行简单介绍。
粒度：指数据仓库的数据单位中保存数据的细化或综合程度的级别。细化程度越高，粒度级就越小；相反，细化程度越低，粒度级就越大。
MySQL按照锁的粒度划分可以分为行锁、表锁和页锁。
行锁：粒度最小的锁，表示只针对当前操作的行进行加锁；
表锁：粒度最大的锁，表示当前的操作对整张表加锁；
页锁：粒度介于行级锁和表级锁中间的一种锁，表示对页进行加锁。
这三种锁是在不同层次上对数据进行锁定，由于粒度的不同，其带来的好处和劣势也不一而同。
表锁在操作数据时会锁定整张表，因而并发性能较差；
行锁则只锁定需要操作的数据，并发性能好。但是由于加锁本身需要消耗资源(获得锁、检查锁、释放锁等都需要消耗资源)，因此在锁定数据较多情况下使用表锁可以节省大量资源。
MySQL中不同的存储引擎能够支持的锁也是不一样的。MyIsam只支持表锁，而InnoDB同时支持表锁和行锁，且出于性能考虑，绝大多数情况下使用的都是行锁。
7， MySQL日志系统
了解完MySQL的基本架构，大体上能够对MySQL的执行流程有了比较清晰的认知。接下来我将在讲述MySQL事务之前，先为大家介绍以下日志系统，以方便之后更好的理解事务的特性和实现。
MySQL日志系统是数据库的重要组件，用于记录数据库的更新和修改。若数据库发生故障，可通过不同日志记录恢复数据库的原来数据。因此实际上日志系统直接决定着MySQL运行的鲁棒性和稳健性。
MySQL的日志有很多种，如二进制日志（binlog）、错误日志、查询日志、慢查询日志等，此外InnoDB存储引擎还提供了两种日志：redo log（重做日志）和undo log（回滚日志）。这里将重点针对InnoDB引擎，对重做日志、回滚日志和二进制日志这三种进行分析。
重做日志（redo log）
重做日志（redo log）是InnoDB引擎层的日志，用来记录事务操作引起数据的变化，记录的是数据页的物理修改。
重做日记的作用其实很好理解，我打个比方。数据库中数据的修改就好比你写的论文，万一哪天论文丢了怎么呢？以防这种不幸的发生，我们可以在写论文的时候，每一次修改都拿个小本本记录一下，记录什么时间对某一页进行了怎么样的修改。这就是重做日志。
InnoDB引擎对数据的更新，是先将更新记录写入redo log日志，然后会在系统空闲的时候或者是按照设定的更新策略再将日志中的内容更新到磁盘之中。这就是所谓的预写式技术（Write Ahead logging）。这种技术可以大大减少IO操作的频率，提升数据刷新的效率。
脏数据刷盘
值得注意的是，redo log日志的大小是固定的，为了能够持续不断的对更新记录进行写入，在redo log日志中设置了两个标志位置，checkpoint和write_pos，分别表示记录擦除的位置和记录写入的位置。redo log日志的数据写入示意图可见下图。
8， 主从复制
主从复制的概念很简单，就是从原来的数据库复制一个完全一样的数据库，原来的数据库称作主数据库，复制的数据库称为从数据库。从数据库会与主数据库进行数据同步，保持二者的数据一致性。
主从复制的原理实际上就是通过bin log日志实现的。bin log日志中保存了数据库中所有SQL语句，通过对bin log日志中SQL的复制，然后再进行语句的执行即可实现从数据库与主数据库的同步。
主从复制的过程可见下图。主从复制的过程主要是靠三个线程进行的，一个运行在主服务器中的发送线程，用于发送binlog日志到从服务器。两外两个运行在从服务器上的I/O线程和SQL线程。I/O线程用于读取主服务器发送过来的binlog日志内容，并拷贝到本地的中继日志中。SQL线程用于读取中继日志中关于数据更新的SQL语句并执行，从而实现主从库的数据一致。
之所以需要实现主从复制，实际上是由实际应用场景所决定的。主从复制能够带来的好处有：
1 通过复制实现数据的异地备份，当主数据库故障时，可切换从数据库，避免数据丢失。
2 可实现架构的扩展，当业务量越来越大，I/O访问频率过高时，采用多库的存储，可以降低磁盘I/O访问的频率，提高单个机器的I/O性能。
3 可实现读写分离，使数据库能支持更大的并发。
4 实现服务器的负载均衡，通过在主服务器和从服务器之间切分处理客户查询的负荷。
七、mysql视图
1、什么是视图
视图是从一个或多个表中查询出来的数据保存为视图，是一种虚拟存在的表。视图和表很相似，它也包含行和列，所以可以直接对它进行查询操作。
视图是一张虚表，所谓虚表，就是说这张表里面没有数据，他的数据是从别的基础表中获取到的。视图在被定义时，就确定了和基础表的联系，比如视图的某个字段是来自基础表A，另外的几个字段是来自基础表B；所以说视图是基于基础表的。
视图也是一张表，对于基础表的所有基础操作（增删改查），视图也适用。当然，当你操作视图的时候，数据库通过中间过程的处理，实际上是操作的基础表。
对于查询操作，视图一般都没问题；不过删除、新增和修改操作，一般会有限制，你得仔细考虑代码的正确性，举个例子，比如视图中的字段来自两个基础表，但是并不包含两个基础表的所有非空字段，当你执行插入操作时，肯定是会报错的。
注意:
数据库中只存放了视图的定义，而没有存放视图中的数据，这些数据存放在原来的表中。
使用视图查询数据时，数据库系统会从原来的表中取出对应的数据。
视图中的数据依赖于原来表中的数据，一旦表中数据发生改变，显示在视图中的数据也会发生改变。
2、为什么要使用视图
重复利用SQL语句
简化SQL查询，快速取数据
只用知道表的部分结构
保护数据，根据特定授权(分配用户一定权限,安全性)
更改数据格式，视图可返回与底层表的格式不同的数据表,展现出来。
视图有以下好处：
●访问数据变得简单
●可被用来对不同用户显示不同的表的内容
用来协助适配表的结构以适应前端现有的应用程序。
视图作用：
●提高检索效率
●隐藏表的实现细节【面向视图检索】
优点:
视图可以让用户或者程序开发人员只看到他们所需要的数据，而不需要把表中的所有信息与字段暴露出来，这样增强了数据的安全性。
3、视图有什么用
视图的作用就是缓存数据，可以这么理解，我把一条sql的查询结果装在了一个视图里，我下次再查的时候，就不用走sql了，直接取这个视图里就可以了，神奇的是我可以修改这个视图里的数据，就等于修改了原表数据。
它的优点有很多，什么简单、安全、简化操作、数据独立。。。。
4、视图与表的区别：
- 表要占用磁盘空间，视图不需要
- 视图不能添加索引
- 使用视图可以简化查询
- 视图可以提高安全性
5 .创建视图
定义的语法结构
完整版：CREATE
[OR REPLACE]
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
[DEFINER = user]
[SQL SECURITY { DEFINER | INVOKER }]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
语法解读：
1）OR REPLACE：表示替换已有视图，如果该视图不存在，则CREATE OR REPLACE VIEW与CREATE VIEW相同。
2）ALGORITHM：表示视图选择算法，默认算法是UNDEFINED(未定义的)：MySQL自动选择要使用的算法 ；merge合并；temptable临时表，一般该参数不显式指定。
3）DEFINER：指出谁是视图的创建者或定义者，如果不指定该选项，则创建视图的用户就是定义者。
4）SQL SECURITY：SQL安全性，默认为DEFINER。
5）select_statement：表示select语句，可以从基表或其他视图中进行选择。
6）WITH CHECK OPTION：表示视图在更新时保证约束，默认是CASCADED。
其实我们日常创建视图时，无需指定每个参数，一般情况下，建议这样创建视图：
精简版：CREATE VIEW 视图名 AS 查询语句;
create view 视图名称 as select * from 表明 where 条件;
如：create view v_t1 as select * from t1 where id>4 and id<11;
查看命令
SELECT * FROM 视图名;
八、mysql索引
一、索引概述
1. 简介
1）索引就好比一本书的目录，它能让你更快的找到自己想要的内容。
2）让获取的数据更有目的性，从而提高数据库检索数据的性能。
索引用于快速找出在某个列中有一特定值的行，不使用索引，MySQL必须从第一条记录开始读完整个表，直到找出相关的行，表越大，查询数据所花费的时间就越多，如果表中查询的列有一个索引，MySQL能够快速到达一个位置去搜索数据文件，而不必查看所有数据，那么将会节省很大一部分时间。
例如：有一张person表，其中有2W条记录，记录着2W个人的信息。有一个Phone的字段记录每个人的电话号码，现在想要查询出电话号码为xxxx的人的信息。
如果没有索引，那么将从表中第一条记录一条条往下遍历，直到找到该条信息为止。
如果有了索引，那么会将该Phone字段，通过一定的方法进行存储，好让查询该字段上的信息时，能够快速找到对应的数据，而不必在遍历2W条数据了。
举例说明索引：如果把数据库中的某一张看成一本书，那么索引就像是书的目录，可以通过目录快速查找书中指定内容的位置，对于数据库表来说，可以通过索引快速查找表中的数据。
除此之外，索引的目的在于提高查询效率，可以类比字典，如果要查“mysql”这个单词，我们肯定需要定位到m字母，然后从下往下找到y字母，再找到剩下的sql。如果没有索引，那么你可能需要把所有单词看一遍才能找到你想要的。
2. 索引的原理
索引的目的在于提高查询效率，与我们查阅图书所用的目录是一个道理：先定位到章，然后定位到该章下的一个小节，然后找到页数。相似的例子还有：查字典，查火车车次，飞机航班等
本质都是：通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。
数据库也是一样，但显然要复杂的多，因为不仅面临着等值查询，还有范围查询(>、<、between、in)、模糊查询(like)、并集查询(or)等等。数据库应该选择怎么样的方式来应对所有的问题呢？我们回想字典的例子，能不能把数据分成段，然后分段查询呢？最简单的如果1000条数据，1到100分成第一段，101到200分成第二段，201到300分成第三段......这样查第250条数据，只要找第三段就可以了，一下子去除了90%的无效数据。但如果是1千万的记录呢，分成几段比较好？稍有算法基础的同学会想到搜索树，其平均复杂度是lgN，具有不错的查询性能。但这里我们忽略了一个关键的问题，复杂度模型是基于每次相同的操作成本来考虑的。而数据库实现比较复杂，一方面数据是保存在磁盘上的，另外一方面为了提高性能，每次又可以把部分数据读入内存来计算，因为我们知道访问磁盘的成本大概是访问内存的十万倍左右，所以简单的搜索树难以满足复杂的应用场景。
3. 索引的优点
建立索引的目的是加快对表中记录的查找或排序！
① 建立索引的列可以保证行的唯一性，生成唯一的rowId
② 建立索引可以有效缩短数据的检索时间
③ 建立索引可以加快表与表之间的连接
④ 为用来排序或者是分组的字段添加索引可以加快分组和排序顺序
4. 索引的缺点
① 创建索引和维护索引需要时间成本，这个成本随着数据量的增加而加大
② 创建索引和维护索引需要空间成本，每一条索引都要占据数据库的物理存储空间，数据量越大，占用空间也越大（数据表占据的是数据库的数据空间）
③ 会降低表的增删改的效率，因为每次增删改索引需要进行动态维护，导致时间变长
二、索引的使用场景
数据库中表的数据量较大的情况下，对于查询响应时间不能满足业务需求，可以合理的使用索引提升查询效率。
三、索引类型介绍
1)BTREE:B+树索引 ( Btree B+tree B*tree)
2)HASH：HASH索引 (hash key)
3)FULLTEXT：全文索引
4)RTREE：R树索引
Mysql索引主要有两种结构：B+Tree索引和Hash索引，也就是用树或者Hash值来存储该字段。我们平常所说的索引，如果没有特别指明，一般都是指B树结构组织的索引(B+Tree索引)。
四、索引分类
#1. 索引的功能就是加速查找 #2. mysql中的primary key，unique，联合唯一也都是索引，这些索引除了加速查找以外，还有约束的功能
索引分类
1.普通索引index :加速查找
2.唯一索引
主键索引：primary key ：加速查找+约束（不为空且唯一）
唯一索引：unique：加速查找+约束 （唯一）
3.联合索引
-primary key(id,name):联合主键索引
-unique(id,name):联合唯一索引
-index(id,name):联合普通索引
4.全文索引fulltext :用于搜索很长一篇文章的时候，效果最好。
5.空间索引spatial :了解就好，几乎不用
五、索引的分类和创建和修改删除等命令
注意：索引是在存储引擎中实现的，也就是说不同的存储引擎，会使用不同的索引
MyISAM和InnoDB存储引擎：只支持BTREE索引， 也就是说默认使用BTREE，不能够更换
MEMORY/HEAP存储引擎：支持HASH和BTREE索引
MySQL 提供了三种创建索引的方法：
1) 使用 CREATE INDEX 语句
可以使用专门用于创建索引的 CREATE INDEX 语句在一个已有的表上创建索引，但该语句不能创建主键。语法格式：
CREATE <索引名> ON <表名> (<列名> [<长度>] [ ASC | DESC])
语法说明如下：
<索引名>：指定索引名。一个表可以创建多个索引，但每个索引在该表中的名称是唯一的。<表名>：指定要创建索引的表名。<列名>：指定要创建索引的列名。通常可以考虑将查询语句中在 JOIN 子句和 WHERE 子句里经常出现的列作为索引列。<长度>：可选项。指定使用列前的 length 个字符来创建索引。使用列的一部分创建索引有利于减小索引文件的大小，节省索引列所占的空间。在某些情况下，只能对列的前缀进行索引。索引列的长度有一个最大上限 255 个字节（MyISAM 和 InnoDB 表的最大上限为 1000 个字节），如果索引列的长度超过了这个上限，就只能用列的前缀进行索引。另外，BLOB 或 TEXT 类型的列也必须使用前缀索引。ASC|DESC：可选项。ASC指定索引按照升序来排列，DESC指定索引按照降序来排列，默认为ASC。
CREATE INDEX indexName ON table_name (column_name)
2) 使用 CREATE TABLE 语句
create table t_dept(
no int not null primary key,
name varchar(20) null,
sex varchar(2) null,
info varchar(20) null,
unique index index_no(no)
）
3) 使用 ALTER TABLE 语句
语法如下：
1.PRIMARY KEY（主键索引）
mysql>ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )
2.UNIQUE(唯一索引)
mysql>ALTER TABLE `table_name` ADD UNIQUE (`column` )
3.INDEX(普通索引)
mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column` )
4.FULLTEXT(全文索引)
mysql>ALTER TABLE `table_name` ADD FULLTEXT ( `column` )
5.多列索引
mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
