MySQL必知必会读书笔记

最新推荐文章于 2021-02-08 08:54:51 发布

进击ing小白

最新推荐文章于 2021-02-08 08:54:51 发布

阅读量1k

点赞数 1

分类专栏：数据库

本文链接：https://blog.csdn.net/WSTONECH/article/details/90208902

版权

数据库专栏收录该内容

2 篇文章 0 订阅

订阅专栏

Mysql

数据库逻辑架构

并发控制：（锁，锁策略，锁粒度）

表锁：表锁会锁定整张表，最基本的锁也是开销最小的锁，写锁的优先级比读锁的高，写锁可以插入到读锁的前面。

行级锁：最大程度的支持并发，InnoDB就支持行级锁

事务及其四大特性：

事务是一系列在共享数据库上执行的行为，以达到更高层次更复杂逻辑的功能。事务是DBMS中最基础的单位，事务不可分割。
ACID四大特性，是指在可靠数据库管理系统（DBMS）中，事务(transaction)所应该具有的四个特性：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。
原子性是指事务是一个不可再分割的工作单位，事务中的操作要么都发生，要么都不发生。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
一致性是指事务使得系统从一个一致的状态转换到另一个一致状态。这是说数据库事务不能破坏关系数据的完整性以及业务逻辑上的一致性。

拿转账来说，假设用户A和用户B两者的钱加起来一共是5000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是5000，这就是事务的一致性。

隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。

　即要达到这么一种效果：对于任意两个并发的事务T1和T2，在事务T1看来，T2要么在T1开始之前就已经结束，要么在T1结束之后才开始，这样每个事务都感觉不到有其他事务在并发地执行。

持久性，意味着在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。即使出现了任何事故比如断电等，事务一旦提交，则持久化保存在数据库中。

隔离性及隔离级别：

　当多个线程都开启事务操作数据库中的数据时，数据库系统要能进行隔离操作，以保证各个线程获取数据的准确性，在介绍数据库提供的各种隔离级别之前，我们先看看如果不考虑事务的隔离性，会发生的几种问题：

脏读是指在一个事务处理过程里读取了另一个未提交的事务中的数据。

　　当一个事务正在多次修改某个数据，而在这个事务中这多次的修改都还未提交，这时一个并发的事务来访问该数据，就会造成两个事务得到的数据不一致。例如：用户A向用户B转账100元，对应SQL命令如下

update account set money=money+100 where name=’B’; (此时A通知B)

update account set money=money - 100 where name=’A’;

当只执行第一条SQL时，A通知B查看账户，B发现确实钱已到账（此时即发生了脏读），而之后无论第二条SQL是否执行，只要该事务不提交，则所有操作都将回滚，那么当B以后再次查看账户时就会发现钱其实并没有转。

不可重复读:指在对于数据库中的某个数据，一个事务范围内多次查询却返回了不同的数据值，这是由于在查询间隔，被另一个事务修改并提交了。

　　例如事务T1在读取某一数据，而事务T2立马修改了这个数据并且提交事务给数据库，事务T1再次读取该数据就得到了不同的结果，发送了不可重复读。

　　不可重复读和脏读的区别是，脏读是某一事务读取了另一个事务未提交的脏数据，而不可重复读则是读取了前一事务提交的数据。

虚读(幻读):幻读是事务非独立执行时发生的一种现象。

例如事务T1对一个表中所有的行的某个数据项做了从“1”修改为“2”的操作，这时事务T2又对这个表中插入了一行数据项，而这个数据项的数值还是为“1”并且提交给数据库。而操作事务T1的用户如果再查看刚刚修改的数据，会发现还有一行没有修改，其实这行是从事务T2中添加的，就好像产生幻觉一样，这就是发生了幻读。

幻读和不可重复读都是读取了另一条已经提交的事务（这点就脏读不同），所不同的是不可重复读查询的都是同一个数据项，而幻读针对的是一批数据整体（比如数据的个数）。

四种隔离级别：

　　现在来看看MySQL数据库为我们提供的四种隔离级别：

　　① Serializable (串行化)：可避免脏读、不可重复读、幻读的发生。

　　② Repeatable read (可重复读)：可避免脏读、不可重复读的发生。

　　③ Read committed (读已提交)：可避免脏读的发生。

④ Read uncommitted (读未提交)：最低级别，任何情况都无法保证。

　　以上四种隔离级别最高的是Serializable级别，最低的是Read uncommitted级别，当然级别越高，执行效率就越低。像Serializable这样的级别，就是以锁表的方式使得其他的线程只能在锁外等待，所以平时选用何种隔离级别应该根据实际情况。在MySQL数据库中默认的隔离级别为Repeatable read (可重复读)。

数据的锁的种类，加锁的方式

锁是网络数据库中的一个非常重要的概念，当多个用户同时对数据库并发操作时，会带来数据不一致的问题，所以，锁主要用于多用户环境下保证数据库完整性和一致性。
数据库锁出现的目的：处理并发问题；
并发控制的主要采用的技术手段：乐观锁、悲观锁和时间戳。
从数据库系统角度分为三种：排他锁、共享锁、更新锁。、

共享锁：用于不更改或不更新数据的操作（只读操作），如 SELECT 语句。

排他锁：用于数据修改操作，例如 INSERT、UPDATE 或 DELETE。确保不会同时同一资源进行多重更新。

更新锁：用于可更新的资源中。防止当多个会话在读取、锁定以及随后可能进行的资源更新时发生常见形式的死锁。

从程序员角度分为两种：一种是悲观锁，一种乐观锁。

乐观锁：完全依靠数据库来管理锁的工作。每次去拿数据的时候都认为别人不会修改，所以，不会上锁。但是在更新的时候会判断一下在此期间别人有没有更新这个数据，可以使用版本号等机制。

悲观锁：程序员自己管理数据或对象上的锁处理。每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人拿这个数据就会block（阻塞），直到它拿锁。

锁的粒度: 锁粒度是被封锁目标的大小,封锁粒度小则并发性高,但开销大,封锁粒度大则并发性低但开销小

锁粒度可以分为为行、页、键、键范围、索引、表或数据库获取锁

MVCC:

英文全称为Multi-Version Concurrency Control, 多版本并发控制。是乐观锁的一种实现方式。

MVCC是为了解决什么？

大多数的MYSQL事务型存储引擎,如,InnoDB，Falcon以及PBXT都不使用一种简单的行锁机制.事实上,他们都和MVCC–多版本并发控制来一起使用控制并发操作,但是其系统开销较大,而MVCC可以在大多数情况下代替行级锁,使用MVCC,能降低其系统开销。

众所周知，在MYSQL中，MyISAM使用的是表锁，InnoDB使用的是行锁。而InnoDB的事务分为四个隔离级别，其中默认的隔离级别REPEATABLE READ需要两个不同的事务相互之间不能影响，而且还能支持并发，这点悲观锁是达不到的，所以REPEATABLE READ采用的就是乐观锁，而乐观锁的实现采用的就是MVCC。正是因为有了MVCC，才造就了InnoDB强大的事务处理能力。

MVCC解决的问题是读写互相不阻塞的问题，每次更新都产生一个新的版本，读的话可以读历史版本。如果一个数据只有一个版本，那么多个事务对这个数据进行读写需要读写锁。

一个读写事务在运行的过程中在访问数据之前先加读/写锁这种实现叫做悲观锁，悲观体现在，先加锁，独占数据，防止别人加锁。

乐观锁呢，读写事务，在真正的提交之前，不加读/写锁，而是先看一下数据的版本/时间戳，等到真正提交的时候再看一下版本/时间戳，如果两次相同，说明别人期间没有对数据进行过修改，那么就可以放心提交。

乐观体现在，访问数据时不提前加锁。在资源冲突不激烈的场合，用乐观锁性能较好。如果资源冲突严重，乐观锁的实现会导致事务提交的时候经常看到别人在他之前已经修改了数据，然后要进行回滚或者重试，还不如一上来就加锁。

所以通常我们把没有开启MVCC特性的，使用原来的锁机制来保证数据一致性的这种锁叫悲观锁，而对开启MVCC机制的锁，叫做乐观锁。

基本原理

MVCC的实现，通过保存数据在某个时间点的快照来实现的。这意味着一个事务无论运行多长时间，在同一个事务里能够看到数据一致的视图。根据事务开始的时间不同，同时也意味着在同一个时刻不同事务看到的相同表里的数据可能是不同的。

基本特征

每行数据都存在一个版本，每次数据更新时都更新该版本。

修改时Copy出当前版本随意修改，各个事务之间无干扰。

保存时比较版本号，如果成功（commit），则覆盖原记录；失败则放弃copy（rollback）

InnoDB存储引擎MVCC的实现策略

在每一行数据中额外保存两个隐藏的列：当前行创建时的版本号和删除时的版本号（可能为空，其实还有一列称为回滚指针，用于事务回滚）。这里的版本号并不是实际的时间值，而是系统版本号。每开始新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询每行记录的版本号进行比较。

每个事务又有自己的版本号，这样事务内执行CRUD操作时，就通过版本号的比较来达到数据版本控制的目的。

4、查询操作：

在查询时要符合以下两个条件的记录才能被事务查询出来：

InnoDB会根据以下两个条件检查每行记录:
a.InnoDB只会查找版本早于当前事务版本的数据行(也就是,行的系统版本号小于或等于事务的系统版本号)，这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的.
b.行的删除版本要么未定义,要么大于当前事务版本号,这可以确保事务读取到的行，在事务开始之前未被删除.

1.MVCC只适用Msyql隔离级别的读已提交（Read committed）和可重复读（Repeatable Read）.

2.Read uncimmitted由于存在脏读，即能读到未提交事务的数据行，所以不适用MVCC.

原因是MVCC的创建版本和删除版本只有事务提交后才会产生。

3.串行化由于是会对所涉及到的表加锁，并非行锁，自然也就不存在行的版本控制问题。

4.通过以上总结，可知，MVCC主要作用于事务性的，有行锁控制的数据库模型。

数据库的索引类型，数据库索引的作用

数据库索引好比是一本书前面的目录，能加快数据库的查询速度。索引是对数据库表中一个或多个列（例如，employee 表的姓氏 (lname) 列）的值进行排序的结构。如果想按特定职员的姓来查找他或她，则与在表中搜索所有的行相比，索引有助于更快地获取信息。
优点

大大加快数据的检索速度; 创建唯一性索引，保证数据库表中每一行数据的唯一性；加速表和表之间的连接; 在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。

缺点

索引需要占用数据表以外的物理存储空间；创建索引和维护索引要花费一定的时间；当对表进行更新操作时，索引需要被重建，这样降低了数据的维护速度。

类型

唯一索引——UNIQUE，例如：create unique index stusno on student（sno）；表明此索引的每一个索引值只对应唯一的数据记录，对于单列惟一性索引，这保证单列不包含重复的值。对于多列惟一性索引，保证多个值的组合不重复。

主键索引——primary key，数据库表经常有一列或列组合，其值唯一标识表中的每一行。该列称为表的主键。在数据库关系图中为表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。当在查询中使用主键索引时，它还允许对数据的快速访问。

聚集索引（也叫聚簇索引）——cluster，在聚集索引中，表中行的物理顺序与键值的逻辑（索引）顺序相同。一个表只能包含一个聚集索引，如果某索引不是聚集索引，则表中行的物理顺序与键值的逻辑顺序不匹配。与非聚集索引相比，聚集索引通常提供更快的数据访问速度。

实现方式

B+树、散列索引、位图索引

聚集索引和非聚集索引的区别

聚集索引表示表中存储的数据按照索引的顺序存储，检索效率比非聚集索引高，但对数据更新影响较大。非聚集索引表示数据存储在一个地方，索引存储在另一个地方，索引带有指针指向数据的存储位置，非聚集索引检索效率比聚集索引低，但对数据更新影响较小。
聚集索引一个表只能有一个，而非聚集索引一个表可以存在多个。聚集索引存储记录是物理上连续存在，而非聚集索引是逻辑上的连续，物理存储并不连续。

MySQL存储引擎与索引

MySQL存储引擎主要分为 InnoDB 存储引擎与 MyISAM 存储引擎。都采用B+数的存储结构。

InnoDB 存储引擎

　　InnoDB 存储引擎是MySQL 的默认事物型引擎，是使用最广泛的存储引擎，采用聚簇索引。

　　1.支持ACID的事务，支持事务的四种隔离级别。

　　2.支持行级锁（默认），也支持表级索。

　　3. 主键索引采用聚簇索引（索引的数据域存储数据文件本身key+行记录），辅索引的数据域存储主键的值；因此从辅索引查找数据，需要先通过辅索引找到主键值，再访问辅索引；最好使用自增主键，防止插入数据时，为维持B+树结构，文件的大调整。

　 4. 不保存表的总行数

MyISAM 存储引擎

　　 MyISAM 存储引擎是MySQL默认的引擎，但是它不支持数据库事务、行级锁和外键，采用非聚簇索引，没有事物和行级锁。MyISAM对整张表加锁，读取时加共享锁，写入时加排他锁。

　　1. 不支持事物。

　　2. 支持表级锁，不支持行级锁。

　　3.采用非聚簇索引，索引文件的数据域存储指向数据文件的指针。辅索引与主索引基本一致，但是辅索引不用保证唯一性。

　 4. 保存总行数，MyISAM：select count(*) from table,MyISAM只要简单的读出保存好的行数，注意的是，当count(*)语句包含 where条件时，两种表的操作是一样的。

InnoDB适合：(1)可靠性要求比较高，要求事务；(2)大量 insert 和 update 。

MyISAM适合：(1)没有事务。(2)插入不频繁，大量 select 。

索引的实现

　　1. InnoDB 索引的实现

　　InnoDB 采用B+ 树的存储结构，树的叶子节点保存了完整的数据记录，该行记录。

　　InnoDB的辅助索引data域存储相应记录主键的值而不是地址。

　　辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

2. MyISAM 索引的实现

　　MyISAM 存储引采用的是 B+ 树的数据结构。

　　叶节点的data域存放的是数据记录的地址。

InnoDB 与 MyISAM 索引的区别

　　1. InnoDB 使用的是聚簇索引，将主键组织到一棵 B+树中，而行数据就储存在叶子节点上，若使用"where id=14"这样的条件查找主键，则按照 B+树的检索算法即可查找到对应的叶节点，之后获得行数据。若对 Name 列进行条件搜索，则需要两个步骤：第一步在辅助索引 B+树中检索 Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引 B+树种再执行一次 B+树检索操作，最终到达叶子节点即可获取整行数据。
　　2. MyISM 使用的是非聚簇索引，非聚簇索引的两棵 B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引 B+树的节点存储了主键，辅助键索引 B+树存储了辅助键。表数据存储在独立的地方，这两颗 B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通
过辅助键检索无需访问主键的索引树。
　　为了更形象说明这两种索引的区别，我们假想一个表如下图存储了 4 行数据。其中 Id 作为主索引，Name 作为辅助索引。图示清晰的显示了聚簇索引和非聚簇索引的差异。

三大范式：

关系数据库中的关系必须满足一定的要求。满足不同程度要求的为不同范式。数据库的设计范式是数据库设计所需要满足的规范。目前，主要有六种范式：第一范式、第二范式、第三范式、BC范式、第四范式和第五范式。满足最低要求的叫第一范式，简称1NF。在第一范式基础上进一步满足一些要求的为第二范式，简称2NF。其余依此类推。

范式可以避免数据冗余，减少数据库的空间，减轻维护数据完整性的麻烦，但是操作困难，因为需要联系多个表才能得到所需要数据，而且范式越高性能就会越差。要权衡是否使用更高范式是比较麻烦的，一般在项目中，用得最多的也就是第三范式，性能好而且方便管理数据。

第一范式(1NF)

定义：如果关系模式R的每个关系r的属性都是不可分的数据项，那么就称R是第一范式的模式。简单的说，每一个属性都是原子项，不可分割。

1NF是关系模式应具备的最起码的条件，如果数据库设计不能满足第一范式，就不称为关系型数据库。

例如(学生信息表)：

学生编号姓名性别联系方式

20080901 张三男 email:zs@126.com,phone:88886666

20080902 李四女 email:ls@126.com,phone:66668888

以上的表就不符合，第一范式：联系方式字段可以再分，所以变更为正确的是：

学生编号姓名性别电子邮件电话

20080901 张三男 zs@126.com 88886666

20080902 李四女 ls@126.com 66668888

第二范式（2NF）

定义：如果关系模式R是1NF，且每个非主属性完全函数依赖于候选键，那么就称R是第二范式。

简单的说，第二范式要满足以下的条件：首先要满足第一范式，其次每个非主属性要完全函数依赖与候选键，或者是主键。也就是说，每个非主属性是由整个主键函数决定的，而不能由主键的一部分来决定。

学生课程教师教师职称教材教室上课时间

李四 Spring 张老师 java讲师《Spring深入浅出》３０１ 08:00

张三 Struts 杨老师 java讲师《Struts in Action》３０２ 13:30

这里通过（学生，课程）可以确定教师、教师职称，教材，教室和上课时间，所以可以把（学生，课程）作为主键。但是，教材并不完全依赖于（学生，课程），只拿出课程就可以确定教材，因为一个课程，一定指定了某个教材。这就叫不完全依赖，或者部分依赖。出现这种情况，就不满足第二范式。

修改后，选课表：

学生课程教师教师职称教室上课时间

李四 Spring 张老师 java讲师３０１ 08:00

张三 Struts 杨老师 java讲师３０２ 13:30

课程表：

课程教材

Spring 《Spring深入浅出》

Struts 《Struts in Action》

所以，第二范式可以说是消除部分依赖。第二范式可以减少插入异常，删除异常和修改异常。

第三范式（3NF）

定义：如果关系模式R是2NF，且关系模式R（U，F）中的所有非主属性对任何候选关键字都不存在传递依赖，则称关系R是属于第三范式。

简单的说，第三范式要满足以下的条件：首先要满足第二范式，其次非主属性之间不存在函数依赖。由于满足了第二范式，表示每个非主属性都函数依赖于主键。如果非主属性之间存在了函数依赖，就会存在传递依赖，这样就不满足第三范式。

上例中修改后的选课表中，一个教师能确定一个教师职称。这样，教师依赖于（学生，课程），而教师职称又依赖于教师，这叫传递依赖。第三范式就是要消除传递依赖。

修改后，选课表：

学生课程教师教室上课时间

李四 Spring 张老师３０１ 08:00

张三 Struts 杨老师３０２ 13:30

教师表：

教师教师职称

张老师 java讲师

杨老师 java讲师

这样，新教师的职称在没被选课的时候也有地方存了，没人选这个教师的课的时候教师的职称也不至于被删除，修改教师职称时只修改教师表就可以了。

简单的说，

第一范式就是原子性，字段不可再分割；

第二范式就是完全依赖，没有部分依赖；

第三范式就是没有传递依赖。

MySQL必知必会语句


---基本常用
--------------------------------------------------------------------------------增   
--插入完整的行
--指定表名和被插入到新航中的值
--确定的给出列名 插入式值和列名对其，不必和真实表中的顺序相同，当表的结构变化，语句依然可用
--可以省略列：即给某些列不提供值
--省略列：1 该列第一允许NULL值 2 在表定义中给出默认值
--INSERT 操作很耗时，而且他可能降低等待处理的SELECT语句性能
--若果数据检索是最重要的，可以在用INSERT LOW_PRIORITY INTO 指示降低INSERT的优先级

INSERT INTO customers(cust_name,
	   cust_address,
	   cust_city,
	   cust_state,
	   cust_zip,
	   cust_country,
	   cust_contact,
	   cust_email)
VALUES('Pep E. LaPew',
       '100 Main Street',
	   'Los Angeles',
	   'CA',
	   '90046',
	   'USA',
	   NULL,
	   NULL);
	   
--若每条INSERT后面的列名相同，可以写这样
--处理多个插入比多条INSERT语句快

INSERT INTO customers(cust_name,
	   cust_address,
	   cust_city,
	   cust_state,
	   cust_zip,
	   cust_country,
	   cust_contact,
	   cust_email)
VALUES('Pep E. LaPew',
       '100 Main Street',
	   'Los Angeles',
	   'CA',
	   '90046',
	   'USA',
	   NULL,
	   NULL),    			--括号括起来并用逗号隔开。
	   ('M. Martian',
       '42 Galaxy Way',
	   'New York',
	   'NY',
	   '11213',
	   'USA');
---------------------------------------------------------------------------------------------删除一行

--删除数据
--DELETE 删除一行
DELETE 
FROM customers
WHERE cust_id = 10006;

-------------------------------------------------------------------------------------------------查
select as  列别名
from   as  表别名
where  and  or in not
order by
limit

-------------------------------------------------------------------------------------------------改
--更新数据
--不要省略WHERE 稍微不注意就更新或删除所有行

-- UPDATE 更新多行时，若一行出错，则更新停止所有行恢复原来的值
--若想继续更新忽略错误，则UPDATE IGNORE 
--为了删除某个值，可设置他为NULL

--从表中删除所有行 DELETE 用TRUNCATE TABLE 语句速度更快
--MySQL没有撤销undo命令，所以UODATE和DELETE应该非常谨慎。

UPDATE customers
SET cust_email = 'elmer@fudd.com',
    cust_name = 'The Fudds' 更新多列,逗号隔开
WHERE cust_id = 10005;
	  
--------------------------------------------------------------------------------------------------创建表

--创建表
--指定的表名必须不存在。否则将出错，如果仅仅想在一个表不存在时创建IF NOT EXISTS
CREAT TABLES customers2     					表的创建 表名紧跟其后，表的定义所有列在括号中，逗号隔开
(
  cust_id 		int NOT NULL AUTO_INCREMENT,   本列每当增加一行时自动增量，每个表只允许一个AUTO_INCREMENT列，且必须被索引(如成为主键)
												获取最后一个值 SELECT last_insert_id(),联合表增加时可用
  cust_name 	char(50) NOT NULL,				NOT NULL 指该列不可以缺省，不指定，默认为NULL
  cust_age 		int NOT NULL DEFAULT 1,       DEFAULT 默认值，未指定时可使用，只支持常量，不允许函数
  cust_birthday char(50) NOT NULL,
  cust_city 	char(50) NULL,
  cust_state	char(50) NULL,
  PRIMARY KEY (cust_id)						创建表时指定主键(唯一,不允许NULL) 多列组合唯一也可以 PRIMARY KEY (cust_name, cust_birthday)	
)ENGINE = InnoDB;						引擎类型：数据库内部具体管理数据的引擎，创建表时它创建，SELECT时它处理，
										不加ENGINE = 语句会使用默认引擎。InnoDB(可靠事物处理引擎) MyISAM(默认)
										
--更新表 表设计时充分考虑，尽量怒要更新表
ALTER TABLE vendors 
ADD vend_phone char(20);  --给表vendors增加一列vend_phone（指定类型）

ALTER TABLE vendors 
DROP vend_phone char;--给表vendors删除一列vend_phone
--删除表
DROP TABLE vendors;删除表vendors,不可撤销
--表的重命名
RENAME TABLE vendors TO vendors_2,
			customers TO customers2；


-----------------------------------------------------------------------------------------------------
			
--------------------------------------------------------------------------------------------------第3章

--使用Mysql
--显示数据库：

SHOW DATABASES;					--返回可用数据库的一个列表；
SHOW TABLES;					--返回一个数据库里表的列表；
SHOW COLUMNS FROM customers; 	--返回一个表（customers）的 表列；
HELP SHOW;						--显示允许的SHOW;

--使用数据库：
USE db_name;

-------------------------------------------------------------------------------------第4章搜索数据
--检索数据
--MYSQL忽略空格，所以可以任意添加，任意换行，分号结尾；
检索单个列：SELECT prod_name(列名) 
			FROM products；
检索多个列：SELECT prod_name,prod_price,prod_id
			FROM products;   (多个列用逗号隔开，最后一个不需要)
检索所有列：SELECT *
			FROM products； （通配符，检索所有列）
检索不同行：SELECT prod_name
			FROM products
			LIMIT 5;        (检索前5行 0-4行)
			LIMIT 5，5;     (从第5行开始，检索5行)
检索（有区别的行）：
			SELECT DISTINCT prod_name
			FROM products;  (从products中检索pro_name不同的行)
		    
			SELECT DISTINCT prod_name,prod_id
			FROM products;   (检索prod_name,prod_id均不同的行（&&），若只有一个不同则视为相同)
使用完全限定的表名：
			SELECT  products.prod_name
			FROM crushproducts.products; 
--------------------------------------------------------------------------------------第5章 排序检索
			
--排序显示数据
--子句：SQL语句由子句构成 关键字+ 数据

排序：
		SELECT prod_name,prod_price,prod_id
		FROM products
		ORDER BY prod_name;			(ORDER BY 根据prod_name排序，默认升序ASC)
		
降序：
		SELECT prod_name,prod_price,prod_id
		FROM products
		ORDER BY prod_name DESC;			(ORDER BY 根据prod_name排序，降序)

多属性排序：
		SELECT prod_name,prod_price,prod_id
		FROM products
		ORDER BY prod_price DESC，prod_name;(优先prod_price降序排列，同prod_price的在prod_name升序)
		LIMIT 1;(加上这一句，显示价格最便宜的 组合LIMIT 与 ORDER BY)
		
用非所选属性也可排序：
		SELECT prod_name,prod_id
		FROM products
		ORDER BY prod_price DESC;(SELECT 没有选prod_price但是可以排序)	

大小写：字典序中默认a与A同，MyQL和其他大部分数据库认为相同，但是数据库管理员可以修改。


----------------------------------------------------------------------------------------------第6章 过滤数据
--数据过滤(根据WHERE语句中指定条件进行过滤)
		--WHERE 支持 =  !=(也是不匹配<>) >	<	>=	<=	BETWEEN
			SELECT prod_name,prod_id,prod_price
		    FROM products
			WHERE prod_name = 'fuses';  	 	检索prod_name为fuses的行，不区分大小写，字符串加'';
			
			WHERE prod_price >= 10
			ORDER BY prod_price;	检索prod_price>=10的行，并按照prod_price排序
			
			WHERE prod_price ！= 10；检索不匹配的行
			WHERE prod_price <> 10；
			
			WHERE prod_price BETWEEN 5 AND 10;检索prod_price在5和10之间（包含）的所有行。
			
			WHERE prod_price IS NULL;检索prod_price为NULL(空值，建表时为空NULL有特殊意义，不匹配不会返回NULL)的行，
			
			
			
------------------------------------------------------------------------------------第7章	AND OR IN NOT IN BETWEEN 		
		--操作符，用来连接或改变WHERE子句中的子句的关键字  也成为逻辑操作符  AND OR
			SELECT prod_name,prod_id,prod_price
		    FROM products
			WHERE prod_id >1002 AND prod < 1004;    根据prod_id范围过滤数据库
			
			SELECT prod_name,prod_id,prod_price
		    FROM products
			WHERE prod_price >10 OR (prod_id >1002 AND prod < 1004);   AND操作符优先级比OR高，()可以明确的分组操作符
			
	    --IN操作 用来指定范围，范围为后面的清单，NOT可以否定范围NOT IN  NOT BETWEEN a AND b
		--IN 和OR的功能相同但是 1、 IN 的次序更容易管理，清晰 2、IN操作比OR 操作更快
			SELECT prod_name,prod_id,prod_price
		    FROM products
			WHERE prod_id IN (1002,1003)    	过滤prod_id为1002或者1003的数据
			ORDER BY prod_price;
			
			WHERE prod_id NOT IN (1002,1003)；	过滤prod_id不是1002或者1003的数据
			WHERE prod_id NOT BETWEEN 1002 AND 1004;过滤prod_id不在1002到1003范围的数据
			
	--------------------------------------------------------------------------------------第8章  通配符 LIKE
		--通配符 用来匹配字符的一部分特殊字符 %  _
		--LIKE操作符 后面跟搜索模式 利用通配符匹配
		--%用来代表0,1，多个字符
		--NULL 不能搜索出来，%也不行，'%avil'  不能搜出(若avil 后面有空格)，应该前后都加%
		--不要过度使用通配符，搜索时间比一般长。不要把通配符放在搜索模式开始处，否则搜索起来很慢
		
		SELECT prod_name,prod_id,prod_price
		FROM products	
		WHERE prod_name LIKE 'jet%';  	    搜索模式jet% 搜索prod_name以jet开头的数据 
		WHERE prod_name LIKE '%anvil%'; 	搜索prod_name包含（任意位置）anvil的数据
		WHERE prod_name LIKE 's%e';	 		搜索prod_name以s开头以e结尾的数据
		
		--  _用来代替一个字符
		SELECT prod_name,prod_id,prod_price
		FROM products	
		WHERE prod_name LIKE '_ ton avill';

------------------------------------------------------------------------------------第9章正则表达式
--用正则表达式进行搜索：匹配文本，将一个模式（正则表达式）与一个文本串进行比较
--MySQL支持正则表达式	一个很小的子集
--正则表达式匹配不分大小写，若要分则使用关键字BINARY
--.是正则表达式特殊字符，表示任意一个字符 

		SELECT prod_name,prod_id,prod_price
		FROM products	
		WHERE prod_name REGEXP '.ton avill'  在列值内任意进行匹配，LIKE必须按照通配符格式整列匹配  
		                            
		WHERE prod_name REGEXP BINARY '.ton avill'   区分大小写
									   
	
--进行OR匹配 |  1000|2000
--匹配几个字符之一[123] [1-9] [a-z]  
				[123] 等价于 [1|2|3] 若不加[] |匹配整个串 eg 1000|2000|3000 ton  3000 ton被看做一个整体
				^否定字符集合[^123]000;
				
		SELECT prod_name,prod_id,prod_price
		FROM products
		WHERE prod_name REGEXP '1000|2000';
		WHERE prod_name REGEXP '[12]000';
		WHERE prod_name REGEXP '[^12]000';
		
	--匹配特殊字符  \\.   \\-  也可用于转义字符（MySQL要求双斜杠） \\n换行 \\t制表
		WHERE prod_name REGEXP '\\.';
	--匹配字符类
		[:alnum:]
		[:alpha:]
		[:digital:]
		[:lower:]
		[:upper:]
		
    --匹配多个实例
	*    0个或多个匹配
	+    1个或多个匹配（等于{1，}）
	？    0个或1个匹配
	{n}    指定书目的匹配
	{n,}   不少于指定数目的匹配
	{n,m}   匹配数目的范围(m不超过255)
	
	WHERE prod_name REGEXP '\\([0-9]sticks?\\)';  ?前面字符（s）出现1次或0次 \\( 转义 (
	WHERE prod_name REGEXP '[[:digital:]]{4}' 		匹配连续出现的4个数字
	
	--定位符 匹配文本特定位置
	--^有两种用法，在集合中(用[和]定义)，用它来否定该集合，否则，用来指串的开始处
	^    文本的开始
	$    文本的结尾
	[[:<:]]   词的开始
	[[:>]]]   词的结尾
	WHERE prod_name REGEXP '\\(^[0-9]sticks?\\)';
	
	--简单的正则表达式测试
	SELECT 'hello' REGEXP '0-9';
	
	---------------------------------------------------------------------------------第10章 创建计算字段
	
	--计算字段：字段实际上是表中的列，计算字段即对表中的列进行运算（组合，计算）
	--计算字段并不是真实存在表中，运行SELECT时创建
	--只有数据库知道真实的表列，客户端看来计算字段和真实表列返回效果一样
	--拼接字段ConCat 将多个字符串拼接为一个字符串，用,隔开各个字符串
	-- RTrim LTrim Trim 删除字符串 左 右 两边 空格
	--使用别名 创建的字段为了方便引用起个别名  不会改变真实的数据库表列 每次运行SELECT 时创建 AS
	--支持+ - * /算数运算
	
	SELECT ConCat(vend_name,'(',vend_country,')')    连接列，可以显示，但是不能引用
	SELECT ConCat(vend_name,'(',vend_country,')') AS vend_title   连接列并且起别名，可以被引用
	SELECT prod_id,quantity,item_price,quantity*item_price  AS expend_price     进行表列运算并起别名
	FROM vendors
	ORDER BY vend_name;
	
		-----------------------------------------------------------------------------第11章 函数
		--函数的可移植性没有SQL的可移植性好
		--支持一下4类函数 文本函数 数值函数 时间函数 系统函数
		
		1、文本处理函数 常用的文本函数（Upper Lower LTrim RTrim Trim length Soundex）
		SELECT vend_name,Upper(vend_name) AS vend_name_uocase
		FROM vendors
		ORDER BY vend_name;

		SELECT cust_name,cust_contact
		FROM customers
		WHERE Soundex(cust_contact) = Soundex('Y lie')；找出cust_contact和'Y lie'相似的行
		
		
		2、日期和时间函数 Date() Month() Second() Time() Year()  p83
			日期格式必须是 yyyy-mm-dd
		SELECT cust_id,order_num
		FROM orders
		WHERE order_date  = '2005-09-01'; 精确找到order_date为'2005-09-01'的行
		WHERE Date(order_date ) = '2005-09-01'; 仅列取日期部分，避免表中类似'2005-09-01-12'等搜索不到
		WHERE Date(order_date ) BETWEEN = '2005-09-01' AND '2005-09-30'; 按时间检索某一月的数据
		WHERE Year(order_date )=2005 AND Month(order_date )=9; 
		
		3、数值处理函数 处理数值计算，各个;数据库函数最一致的类别。p85

------------------------------------------------------------------------------------第12章汇总数据

--聚集函数：运行在行组上，返回单个值的函数

--平均AVG()返回所有列或特定列的均值
--只能用于单列，多列得多次调用
--NULL AVG忽略

SELECT AVG(prod_price) AS avg_price    			 默认是ALL所有行
SELECT AVG(DISTINCT prod_price) AS avg_price  关键字DISTINCT表示必须是不同的prod_price
FROM products;        返回所有行的均值
WHERE vend_id = 1003;  返回指定行的均值

--COUNT
--COUNT(*)统计表列中所有的行数，包括NULL；
--COUNT(cloumn) 统计指定列的函数，或略NULL行

SELECT COUNT(cust_email) AS num_cust 		统计cust_email列中不为空的总行数
SELECT COUNT(*) AS num_cust
FROM customers;				返回customers表中总行数

--MAX
--返回指定列中的最大值，必须指定列名
--对于文本，若按照相应的排序，MAX返回最后一行
--忽略NULL行

SELECT max(prod_price) AS max_price    	检索prod_price列中最大的值
FROM products; 

--MIN


--SUM用于返回指定列的总和

SELECT SUM(quantity) AS items_ordered
SELECT SUM(items_price*quantity) AS total_price    利用标准算术操作符，所有聚集函数可以执行多个列
FROM ordefitems
WHERE order_num = 20005;

--组合聚集函数

SELECT COUNT(*) AS num_cust,
	   max(prod_price) AS max_price, 
	   min(prod_price) AS min_price,
	   AVG(prod_price) AS avg_price
FROM products; 	   
	   

-------------------------------------------------------------------------------------第13章分组过滤
--GROUP分组

SELECT vend_id,COUNT(*) AS num_prods
FROM products
GROUP BY vend_id;  -- 统计所有行并以vend_id为标准进行分组

--过滤分组
--依据cust_id分组，并过滤每个分组职工行数大于2的分组
--HAVING 的操作对象是分组，WHERE的操作对象是行，不可混淆

SELECT cust_id,COUNT(*) AS orders
FROM orders
GROUP BY cust_id WITH ROLLUP
HAVING COUNT(*) >= 2;

--分组和排序
--GROUP BY 分组， ORDER BY 排序这些分组
SELECT order_num,SUM(quantity*item_price) AS ordertotal
FROM orderitems
GROUP BY order_num
HAVING SUM(quantity*item_price) >=50
ORDER BY ordertotal;

--SELECT的子语句顺序
--SELECT  		FROM 		WHERE	 	GROUP BY 		HAVING 		ORDER BY 		LIMIT


-------------------------------------------------------------------------------------第14章 子查询

--子查询：在查询语句中嵌套其他查询语句   			 oredrs表                    orderitems表
--关系表：两个表中有相同的项，如客户id    			cust_id order_num           order_num    prod_id
--嵌套的子查询数目没有限制，但是实际中为了查询效率不能嵌套太多

SELECT cust_id
FROM orders
WHERE order_num IN(SELECT order_num           格式化SQL：分解为多行并适当缩进，便于观察理解
					FROM orderitems
					WHERE prod_id = 'TNT2');   找出所有prod_id='TNT'的客户cus_id
			
--作为计算字段创建子查询
SELECT cust_name,
	   cust_state,
	   (SElECT COUNT(*)
	   FROM orders
	   WHERE orders.cust_id = customers.cust_id)AS orders  相关查询，涉及外部查询必须用完全限定列名
	   FROM customers
	   ORDER BY cust_name;
--逐渐增加子查询来建立查询：
--查询从内而外，每次先建立里面的查询，返回硬编码，用硬编码测试外部查询，逐层反复直到到最外面的查询
--这样可以排除出错，否则一次写完查询，出错排查极为困难。


------------------------------------------------------------------------------------第15章 连接表
--关系表：关系表的设计就是保证把信息分解成多个表，一类数据一个表。各个表通过常用值（关系）进行相互关联
--主键：唯一标识符。
--外键：表中的某列包含另外一个表的主键，则此列为外键，定义了两个表之间的关系。


--创建连接
SELECT vend_name,prod_name,prod_price
FROM products,vendors
WHERE vendors.vend_id = products.vend_id    指示匹配vendors.vend_id = products.vend_id 
ORDER BY vend_name,prod_name;    			若没有WHERE子句的正确联结，则会出现笛卡尔乘积：
											检索的行数将是第一个表的行数乘第二个表的行数
										
--等值联结即内部连接 基于两个表相等 WHERE vendors.vend_id = products.vend_id 
--与上面WHERE功能相同 SQL 首选IN JOIN 且不易忘记联结条件on

SELECT vend_name,prod_name,prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id;

--联结多个表
 SELECT prod_name,prod_name,prod_price,quantity
FROM orderitems,vendors,products
WHERE vendors.vend_id = products.vend_id     关联vendors和products
	  AND orderitems.prod_id = products.prod_id   关联 products 和orderitems
	  AND order_num = 20005;    筛选条件
	  
--MySQL在运行时才关联表格，联结的表格越多，性能下降越厉害，所以不要关联无关的表格
--同一功能有不同的实现方法，多做实验测试不同方法的性能，选择最好的方法。

------------------------------------------------------------------------------------第16章 创建高级联结
--
--使用表别名 可以在SELECT中不止一次使用表
SELECT cust_name,cust_contact
FROM customers AS c,orders AS o,orderitems AS oi  表使用别名，只在查询时使用，
WHERE c.cust_id = o.cust_id					与列别名不同，表别名不返回客户机
AND oi.order_num = o.order_num
AND prod_id = 'TNT2';

--自联结  同一表使用两次 
SELECT prod_id,prod_name
FROM products
WHERE vend_id = (SELECT vend_id
				FROM products
				WHERE prod_id = 'DTNTR');

			
SELECT p1.prod_id,p2.prod_name		必须使用全名，否则两个表相同的列会引起混淆 两个表内连接（基于相等拼接）后有相同的两个列
FROM products AS p1,products AS p2
WHERE p1.vend_id = p2.vend_id
AND 	p1.prod_id = 'DTNTR';


-自然联结 其中你只能选择那些唯一的列，对表使用通配符，对所有其他表的列使用明确的子咧集完成
--https://blog.csdn.net/whywww/article/details/80116352
select * from instructor natural join teaches;只考虑两个关系中属性取值相同的对，ID出现一次，不存在NULL

SELECT c.*,o.order_num,o.order_date,
       oi.prod_id,oi.quantity,OI.item_price
FROM customers AS c,orders AS o,orderitems AS oi
WHERE c.cust_id = o.cust_id
AND oi.order_num = o.order_num
AND prod_id = 'FB';


--外联结
--与内连接不同的是，包含所有没有关联的行，用NULL表示
SELECT *
FROM customers LEFT OUTER JOIN orders
ON customers.cust_id = orders.cust_id;

SELECT customers.cust_id,orders.order_num
FROM customers LEFT OUTER JOIN orders
ON customers.cust_id = orders.cust_id;


--使用带聚集函数的联结
SELECT customers.cust_name,customers.cust_id,COUNT(orders.order_num) AS num_odd
FROM customers INNER JOIN orders
ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_id;


---------------------------------------------------------------------------------------第17章 组合查询
--UNION 组合查询将多个查询（SELECT）同时执行，并将结果作为单个查询结果返回，称为并
--UNION完成多个WHERE的功能 但是若要全部显示不合并 UNION ALL
SELECT vend_id,prod_id,prod_price
FROM products                      可以组合查询多个表
WHERE prod_price <= 5
UNION 							   默认去掉两个查询重复的行，若不想去掉则 UNION ALL
SELECT vend_id,prod_id,prod_price  所有SELECT必须相同的列，且数据类型相同，单列顺序不必相同
FROM products 
WHERE vend_id IN (1002,1002)
ORDER BY prod_id,prod_price; UNION 所有的查询排序标准必须一样，只能在最后一行ORDER BY

--WHERE实现
SELECT vend_id,prod_id,prod_price
FROM products
WHERE prod_price <= 5 
OR vend_id IN (1002,1002);

--------------------------------------------------------------------------------------第18章 全文本搜索
--检索单个列note_text Match()指定列进行搜索 Against()指定搜索文本
--搜索不区分大小写 除非BINARY方式
--
SELECT note_text
FROM productnote
WHERE Match(note_text) Against('rabbit');  按照搜索的良好成都排序，具有较高等级的现行返回
WHERE note_text LIKE '%rabbit%';           不排序，以不特别有用的排序返回

--查询扩展 
--有时候不仅要查找包含指定文本的文本行，还要找处不包含指定文本但是可能相关的行
--查询扩展：查询查询两遍，第一遍全文本查找指定关键词，并搜索这些行有用的关键词
--第二遍全文本搜索这些有用的关键词
--表中的行越多说明使用查询扩展的效果越好

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('rabbit' WITH QUERY EXPANSION); 

--布尔文本搜索 布尔方式搜索 可以包含 要匹配的词，要排斥的词，排列提示，表达式分组，另一些内容
-- 布尔操作符 
-- +包含 -排除 >包含，且增加等级值 <包含且减少等级值 （）把词组成表达式 ~取消一个词的排序 *词尾通配符
--搜索文本使用说明 p140
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('heavy -rope*' IN BOOLEAN MODE);  	搜索包含heavy但是不包含rope开头的行
WHERE Match(note_text) Against('+rabbit +bait' IN BOOLEAN MODE);    搜索匹配rabbit和bait的行
WHERE Match(note_text) Against('rabbit bait' IN BOOLEAN MODE);    没有指定操作符，搜索匹配rabbit和bait至少有一个的行
WHERE Match(note_text) Against('"rabbit bait"' IN BOOLEAN MODE);   搜索匹配短语rabbit bait的行，不是词
WHERE Match(note_text) Against('>rabbit <bait' IN BOOLEAN MODE); 搜索匹配rabbit和bait的行,增加前者等级，降低后这等级
WHERE Match(note_text) Against('+safe +( <bait)' IN BOOLEAN MODE); 搜索匹配词safe和bait降低后者等级



-------------------------------------------------------------------------------------第19章 插入数据
--插入完整的行
--指定表名和被插入到新航中的值
--确定的给出列名 插入式值和列名对其，不必和真实表中的顺序相同，当表的结构变化，语句依然可用
--可以省略列：即给某些列不提供值
--省略列：1 该列第一允许NULL值 2 在表定义中给出默认值
--INSERT 操作很耗时，而且他可能降低等待处理的SELECT语句性能
--若果数据检索是最重要的，可以在用INSERT LOW_PRIORITY INTO 指示降低INSERT的优先级

INSERT INTO customers(cust_name,
	   cust_address,
	   cust_city,
	   cust_state,
	   cust_zip,
	   cust_country,
	   cust_contact,
	   cust_email)
VALUES('Pep E. LaPew',
       '100 Main Street',
	   'Los Angeles',
	   'CA',
	   '90046',
	   'USA',
	   NULL,
	   NULL);

--插入多行，多个INSERT即可
INSERT INTO customers(cust_name,
	   cust_address,
	   cust_city,
	   cust_state,
	   cust_zip,
	   cust_country,
	   cust_contact,
	   cust_email)
VALUES('Pep E. LaPew',
       '100 Main Street',
	   'Los Angeles',
	   'CA',
	   '90046',
	   'USA',
	   NULL,
	   NULL)
INSERT INTO customers(cust_name,
	   cust_address,
	   cust_city,
	   cust_state,
	   cust_zip,
	   cust_country,
	   cust_contact,
	   cust_email)
VALUES('M. Martian',
       '42 Galaxy Way',
	   'New York',
	   'NY',
	   '11213',
	   'USA');
	   
--若每条INSERT后面的列名相同，可以写这样
--处理多个插入比多条INSERT语句快

INSERT INTO customers(cust_name,
	   cust_address,
	   cust_city,
	   cust_state,
	   cust_zip,
	   cust_country,
	   cust_contact,
	   cust_email)
VALUES('Pep E. LaPew',
       '100 Main Street',
	   'Los Angeles',
	   'CA',
	   '90046',
	   'USA',
	   NULL,
	   NULL),    			--括号括起来并用逗号隔开。
	   ('M. Martian',
       '42 Galaxy Way',
	   'New York',
	   'NY',
	   '11213',
	   'USA');
	   
--插入检索出的数据
--INSERT INTO .. SELECT ..FROM .. WHWRE..
--合并custnew和customer表
--custnew的列名不一定匹配customers的列名
--MySQL不关心SELECT的返回列名，只是单纯的使用列的位置，两个表的对应列填充
INSERT INTO customers(cust_name,
	   cust_address,
	   cust_city,
	   cust_state,
	   cust_zip,
	   cust_country,
	   cust_contact,
	   cust_email)
	   SELECT cust_name,
	   cust_address,
	   cust_city,
	   cust_state,
	   cust_zip,
	   cust_country,
	   cust_contact,
	   cust_email
	   FROM custnew;
	  
------------------------------------------------------------------------------------第20章 数据的更新和删除
--更新数据
--不要省略WHERE 稍微不注意就更新或删除所有行


-- UPDATE 更新多行时，若一行出错，则更新停止所有行恢复原来的值
--若想继续更新忽略错误，则UPDATE IGNORE 
--为了删除某个值，可设置他为NULL

UPDATE customers
SET cust_email = 'elmer@fudd.com',
    cust_name = 'The Fudds' 更新多列,逗号隔开
WHERE cust_id = 10005;
	  
--删除数据
--DELETE 删除一行
DELETE FROM customers
WHERE cust_id = 10006;

--从表中删除所有行 DELETE 用TRUNCATE TABLE 语句速度更快
--MySQL没有撤销undo命令，所以UODATE和DELETE应该非常谨慎。

RENAME TABLE custmoers2 TO customers;
--------------------------------------------------------------------------------------第21章 创建和操纵表
--

--创建表
--指定的表名必须不存在。否则将出错，如果仅仅想在一个表不存在时创建IF NOT EXISTS
CREAT TABLES customers2     					表的创建 表名紧跟其后，表的定义所有列在括号中，逗号隔开
(
  cust_id 		int NOT NULL AUTO_INCREMENT,   本列每当增加一行时自动增量，每个表只允许一个AUTO_INCREMENT列，且必须被索引(如成为主键)
												获取最后一个值 SELECT last_insert_id(),联合表增加时可用
  cust_name 	char(50) NOT NULL,				NOT NULL 指该列不可以缺省，不指定，默认为NULL
  cust_age 		int NOT NULL DEFAULT 1,       DEFAULT 默认值，未指定时可使用，只支持常量，不允许函数
  cust_birthday char(50) NOT NULL,
  cust_city 	char(50) NULL,
  cust_state	char(50) NULL,
  PRIMARY KEY (cust_id)						创建表时指定主键(唯一,不允许NULL) 多列组合唯一也可以 PRIMARY KEY (cust_name, cust_birthday)	
)ENGINE = InnoDB;						引擎类型：数据库内部具体管理数据的引擎，创建表时它创建，SELECT时它处理，
										不加ENGINE = 语句会使用默认引擎。InnoDB(可靠事物处理引擎) MyISAM(默认)
										
--更新表 表设计时充分考虑，尽量怒要更新表
ALTER TABLE vendors 
ADD vend_phone char(20);  --给表vendors增加一列vend_phone（指定类型）

ALTER TABLE vendors 
DROP vend_phone char;--给表vendors删除一列vend_phone
--删除表
DROP TABLE vendors;删除表vendors,不可撤销
--表的重命名
RENAME TABLE vendors TO vendors_2,
			customers TO customers2；
			
---------------------------------------------------------------------------------------第22章 视图（结构化语句（封装））
--视图（view）是一种虚拟存在的表，是一个逻辑表，本身并不包含数据。作为一个select语句保存在数据字典中的。就是封装语句
--通过视图，可以展现基表的部分数据；视图数据来自定义视图的查询中使用的表，使用视图动态生成。
--创建视图  视图名字必须唯一,一次创建多次使用
--删除视图  DROP VIEW 视图名
--查看视图（封装）的语句，SHOW CREATE VIEW viewname;
--更新视图：DROP 在CREATE或CREATE OR REPLACE VIEW

CREATE VIEW productcustomers AS    --创建视图productcustomers as 后面的语句（等效）
SELECT cust_name,cust_contact,prod_id
FROM customers,orders,orderitems
WHERE customers.cust_id = orders.cust_id
	AND orderitems.order_num = orders.order_num;
	
--使用视图 组合的SELECT语句 用于查询，尽量不要更新视图
SELECT cust_name,cust_contact
FROM productcustomers
ORDER BY prod_id;

-------------------------------------------------------------------------------------第23章 存储过程（函数）
--
--创建
--MYSQL命令行实用程序也用;为语句分隔符，若命令行实用程序要解释存储过程自身内存;会报错
--可视化软件；不会报错
--DELIMITER临时定义分隔符 
DELIMITER //
CREATE PROCEDURE productpricing()
BEGIN
	SELECT Avg(prod_price) AS priceaverage
	FROM products;
END //
--使用
CALL productpricing();
--删除存储过程
DROP PROCEDURE productpricing;  不加括号

--加参数
DELIMITER //
CREATE PROCEDURE productpricing(
OUT p1 DECIMAL(8,2),
OUT p2 DECIMAL(8,2),
OUT p3 DECIMAL(8,2)
)
BEGIN 
	SELECT Min(prod_price)
	INTO p1
	FROM products;
	SELECT Max(prod_price)
	INTO p2
	FROM products;
	SELECT Avg(prod_price)
	INTO p3
	FROM products;
END//
	
CALL productpricing(@pricelow,
@priceheigh,
@priceaverage)//  --上面使用DELIMITER //后命令行全部用//代替;

SELECT @priceaverage//

-- 定义智能存储过程
DELIMITER //
CREATE PROCEDURE ordertotal(
 IN onumber INT,     --输入类型 INT型变量
 IN taxable BOOLEAN, --输入类型，BOOLEAN型变量
 OUT ototal DECIMAL(8,2)  --输出变量
 )COMMENT 'Obtain order total, optionally adding tax'   --可有可无
 BEGIN
  -- declear variable for total
  DECLARE total DECIMAL(8,2); --声明临时变量
  -- declear tax percentage
  DECLARE taxrate INT DEFAULT 6;  --临时变量默认值DEFAULT
  -- get the order total
    SELECT Sum(item_price * quantity) FROM orderitems WHERE order_num = onumber INTO total;

-- IS this taxable?

IF taxable THEN   --IF ELSEIF ENDIF ELSE THEN
   -- yes ,so add taxrate to the total
   SELECT total+(total/100*taxrate)INTO total;
   END IF;
 -- finally ,save to out variable
 SELECT total INTO ototal;
END//

CALL ordertotal(20005,0,@total)//
SELECT @total//

CALL ordertotal(20005,0,@total)
SELECT @total//
--
--检查存储过程

SHOW CREATE PROCEDURE ordertotal； --输出创建存储过程的语句 
SHOW PROCEDURE STATUS； --获取存储过程的详细信息，包括何时，由谁创建等信息，输出的为数据库中所有的存储过程 
SHOW PROCEDURE STATUS LIKE ‘ordertotal’； --关键字LIKE限制过程输出，输出指定的存储过程
---https://blog.csdn.net/sinat_28978689/article/details/55270735

----------------------------------------------------------------------------------------第24章游标 cursor
--游标主要用于交互式应用，MySql中游标只能用于存储过程当中
--先定义  在打开游标  使用游标 关闭游标 若不关闭游标在到END时自动关闭
--FETCH 在游标打开后访问它的每一行，并且向前移动游标内部的行指针，使用FETCH语句检索下一行


CREATE PROCEDURE processorders()
BEGIN
	--声明临时变量,必须在所有之前

	DECLARE done BOOLEAN DEFAULT 0;
	DECLARE o INT;
	DECLARE t DECIMAL(8,2);
	
	--声明CURSOR 游标
	DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders;
	
	--局柄定义必须在游标之后
	--声明continue handler句柄 条件SQLSTATE '02000'达到时 SET done=1;被执行 当REPEAT没有更多行时条件出现
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
	
	--创建一个表存储结果
	CREATE TABLE IF NOT EXISTS ordertotals
	(order_num INT,total DECIMAL(8,2));
	
	--打开游标
	OPEN ordernumbers;
	
	--循环所有行 直到UNTIL done END REPEAT;即done为1时终止循环
	REPEAT
	--获取order number
	FETCH ordernumbers INTO o;
	
	--获得total给orders
	CALL ordertotal(o,1,t);
	
	--插入表中
	INSERT INTO ordertotals(order_num,total)
	VALUES(o,t);
	--END of loop
	UNTIL done END REPEAT;
	
	--关闭游标
	CLOSE ordernumbers;
END//



CREATE PROCEDURE processorders()
BEGIN
	DECLARE done BOOLEAN DEFAULT 0;
	DECLARE o INT;
	DECLARE t DECIMAL(8,2);
	DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;

	CREATE TABLE IF NOT EXISTS ordertotals
	(order_num INT,total DECIMAL(8,2));
	
	OPEN ordernumbers;

	REPEAT

	FETCH ordernumbers INTO o;
	

	CALL ordertotal(o,1,t);
	

	INSERT INTO ordertotals(order_num,total)
	VALUES(o,t);

	UNTIL done END REPEAT;
	
	CLOSE ordernumbers;
END//


------------------------------------------------------------------------------------------触发器
--触发器是指某个表在某个条件发生时触发事件自动执行
--触发条件一般为表的更改 如 DELETE UPDATAE INSERT 

--创建触发器 
--唯一的触发器名   触发器关联的表 触发器响应的活动（DELETE UPDATE INSERT） 触发器的时间（BEFORE AFTER）
--触发器只支持表

CREATE TRIGGER newproducts AFTER INSERT ON products --在表products插入之后 每一行插入都显示product added
FOR EACH ROW SELECT 'product added';

--删除触发器
DROP TRIGGER newproducts;

--INSERT 触发器  可以访问NEW虚拟表，指向插入的行

CREATE TRIGGER newproducts AFTER INSERT ON products
FOR EACH ROW SELECT NEW.order_num;  --没插入一行显示新插入行的订单号

--DELETE触发器 可以访问OLD的虚拟表，指向删除行

CREATE TRIGGER deleteproducts BEFORE DELETE ON products  --删除之前将OLD的属性保存在一个存档表中
FOR EACH ROW
BEGIN
INSERT INTO archives_orders(order_num,order_date,cust_id)
VALUES(OLD.order_num,OLD.order_date,OLD.cust_id);
END;


--update触发器 可以NEW访问之前的虚拟表 也可以访问之后的虚拟表OLD

CREATE TRIGGER updatevendor BEFORE UPDATE ON products
FOR EACH ROW SET NEW.vend_state = UPPER(NEW.vend_state);


------------------------------------------------------------------------------------事务管理
--事务处理：事物的四大特性ACID 引擎InnoDB支持事物  MyISAM不支持
--事物：一组sql语句
--回退：撤销sql语句的过程
--提交：指将为存储的sql语句写入数据库
--保留点：事物处理中设置临时占位符，可以对他发起回退

--事务处理用来处理 DELETE UPDATE INSERT

--ROLLBACK 只能在一个事物处理里面使用
SELECT *FROM productlist;  --显示productlist存在不为空
START TRANSACTION;
DELETE FROM productlist;--删除整张表
SELECT * FROM productlist ; --证明删除完毕
ROLLBACK --回滚START之后的语句
SELECT * FROM productlist; -- 证明回滚有效


--COMMIT语句 一般MySql语句都是直接针对数据库表编写的，所以隐含提交；但是事物不是，必须显示提交


START TRANSACTION ;
DELETE FROM orderitems WHERE order_num = 20010;
DELETE FROM orders WHERE orders_num = 20010;
COMMIT;  --避免只执行一条语句，导致删除一般，数据库紊乱  在两条语句正确执行后才会提交生效 否则自动回滚

--COMMIT ROLLBACK 执行后事物自动关闭（隐含事物关闭）

--保留点  SAVEPOINT  每个保留点都起一个独特的名字  回滚的时候可以会滚到指定的保留点
--保留点越多 对事物回滚的程度越精准  事物处理后保留点自动释放

SAVEPOINT DELETE1;
...
ROLLBACK DELETE1;

--修改默认提交  默认的MySql是自动提交的 可以修改 
SET AUTOCOMMIT = 0;


-------------------------------------------------------------------------------------数据库安全管理
--MySql服务器安全的基础是：用户对他们访问的数据有特定的访问控制权限，不能多也不能少
--管理访问控制需要创建管理用户账号

--查看用户账号  USAGE 表示无权限
USE mysql;
SELECT user from user;  --MySql有一张表user专门保存用户账号，有一个列user保存用户账号

--创建账号
CREATE USER ben IDENTIFIED BY 'password'  --创建用户时不一定需要口令 但是IDENTFIED 指定口令并进行哈希加密
--改用户名
RENAME USER ben to Tom

--删除用户名
DROP USER Tom

--给用户设置访问权限
--查看权限
SHOW GRANTS FOR ben;
--设置
GRANT SELECT ON crashcourse.* TO ben;  --给ben授予数据库crashcourse的所有表的SELECT权限
REVOKE SELECT ON crashcourse.* FROM ben;  --撤销权限
--一个GRANTS 只能设置一个权限
--GRANT/REVOKE ALL(整个服务器)  database.*(某个数据库) database.table(某个数据库的某个表) 行 存储过程等 TO/FROM
--一次性授予多个权限
GRANT SELECT,INSERT,UPDATE,DELETE ON crashcourse.goods TO Tom

--更改口令
SET PASSWORD FOR Ben = Password('passwordofben')；--给Ben设置新的口令，并调用Passworld函数进行加密
SET PASSWORD =Password('passwordofcurrentuser');--给当前用户设置新的密码