mysql高级

一、联合查询：union   /     union all

1、作用：可以将两组结构相同的数据纵向合并

2、注意事项：

1、合并的数据结构必须相等

  2、union不允许重复（性能相对较低），union all允许重复（性能高，去重的条件：数据完全一致才会去重）

  3、联合查询返回的列名是由第一个select查询的字段名决定

  4、所有select查询的数量必须一致，类型不作要求

  5、有排序和分页要求时，先使用子查询。如下：

SELECT e.* FROM (SELECT id,`name` FROM order2014

UNION

SELECT id ,`name`FROM order2015) e LIMIT 3,3;

 

(SELECT id,`name` FROM order2014)

UNION

(SELECT id ,`name`FROM order2015) ORDER BY id DESC LIMIT 0,3;

二、索引

1、索引的作用：优化查询效率

2、哪些地方适合建索引？

1、表的主键、外键自带索引；

2、数据量超过300的表应该有索引；

3、经常与其他表进行连接的表，在连接字段上应该建立索引；

4、经常出现在Where子句中的字段，特别是大表的字段，应该建立索引；

5、索引应该建在选择性高的字段上；

6、索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；

3、创建索引的方式（工具与命令行模式）

Sql语句创建和删除索引：

创建索引:

CREATE  INDEX 索引名称 ON 表名 (列名)

删除索引:

方式一：

DROP  INDEX 索引名 ON 表名

方式二:  

ALTER  TABLE 表名 DROP INDEX 索引名

三、视图

1、什么是视图？

视图是由SELECT语句组成的虚拟表。

注意：

1.视图是虚拟表，可以把视图看着一个表，，但视图不会存储数据，它的数据来源于SELECT查询语句。

2.视图每次执行，都会执行一次SELECT查询，获取最新的数据。相当于一次子查询。

2、为什么要使用视图？

1.简化复杂的查询。常用的、比较复杂的SELECT语句，创建一个视图，就能方便以后多次调用。

2.安全原因。比如，只想让权限低的用户看到一个表的部分字段，而隐藏机密的部分。

3、视图应用场景

视图（虚拟表，是建立在实际表的基础上）主要使用在查询的场景中。一般不使用视图进行增删改的操作（因为在视图中进行增删查的操作，实际表也会进行）

注意：

如果视图包含下述结构中的任何一种，那么它就是不可更新的：

（1）聚合函数；

（2）DISTINCT关键字；

（3）GROUP BY子句；

（4）ORDER BY子句；

（5）HAVING子句；

（6）UNION运算符；

（7）位于选择列表中的子查询；

（8）FROM子句中包含多个表；

（9）SELECT语句中引用了不可更新视图；

（10）WHERE子句中的子查询，引用FROM子句中的表；

（11）ALGORITHM 选项指定为TEMPTABLE（使用临时表总会使视图成为不可更新的）。

 

4、视图的相关语法（视图就是一个表，对视图的操作和实际表一样）

增加视图

create view 视图名 as  SQL语句;

修改视图

alter view 视图名 as  SQL语句;

删除视图

drop view 视图名;

5 视图注意点

1. 视图的名字必须唯一，不能和其他表或视图重名。
2. 视图可以嵌套使用，也就是视图里面可以再次调用视图。
3. 视图的创建和删除只影响视图本身，不影响所使用的基本表，

四、事务

1、什么是事务？

事务是一组连续不可分割的SQL，要求要么全部成功要么全部撤销。

注意：

MySQL的表类型必须是InnoDB，才能支持事务。

2为什么需要事务

1、事务是数据库商用最基本最重要的特点。

2、事务是保证数据库数据完整性和安全性最重要的技术。

3、事务是要求一组连续的SQL语句要么全部成功执行，要么数据返回到开启事务前的状态。不允许部分SQL语句成功，部分不成功。

3事务使用场景

1、操作步骤需要回滚的。比如，银行转账、购物下单等。

2、遇到异常情况，数据需要回滚的。比如，断电、磁盘坏死等。

4、事务的基本语法

语法如下；

start transaction;

insert 语句

delete 语句

update 语句

select 语句

commit;

其中，start transaction是开启事务。commit是提交，表示一些列sql语句成功执行，提交到数据库。

 

start transaction;

insert 语句

delete 语句

update 语句

select 语句

rollback;

其中，start transaction是开启事务。rollback是提交，表示一些列sql语句没有全部成功执行，撤销所有的改动。

5、事务注意点

事务要求满足4点(简称ACID)：

1.原子性(Atomicity):事务中的多个操作，不可分割，要么都成功，要么都失败。

2.一致性(Consistency): 事务操作之后, 数据库所处的状态和业务规则是一致的; 比如a,b账户相互转账之后，总金额不变。

3.隔离性(Isolation):事务必须与其他事务进行的数据更改相隔离。这意味着没有其他操作可以改变中间态(没有提交的)的数据。为了避免中间态数据被更改，事务必须要么等待来自其他事务的更改被提交，要么只能查看到处于上一个提交状态的数据。

4.持久性(Durability):在一个事务完成，这些更改的数据将永久存在。

五、表关系设计（了解）

六、数据库的三范式

第一范式（1NF）是对关系模式的基本要求，不满足第一范式（1NF）的数据库就不是关系数据库。

   是指数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性。

一张表有固定的列与不定的行，每列名称不能重复且只存在一个值

 

第二范式（2NF）要求数据库表中的每个实例或行必须可以被惟一地区分。

要求每张表必需要有主键

 

第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。

外键关联，只能存关连表的主键(唯一)信息

转载于:https://www.cnblogs.com/limengkun/p/10808864.html