校招准备:（七）：数据库1.语句、视图、存储过程、触发器、外键约束、索引与优化

1.数据库语句

1.1建表语句：

CREATE TABLE IF NOT EXISTS `runoob_tbl`(
   `runoob_id` INT UNSIGNED AUTO_INCREMENT,
   `runoob_title` VARCHAR(100) NOT NULL,
   `runoob_author` VARCHAR(40) NOT NULL,
   `submission_date` DATE,
   PRIMARY KEY ( `runoob_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

创建 MySql 的表时，表名和字段名外面的符号 ` 不是单引号，而是英文输入法状态下的反单引号，也就是键盘左上角 esc 按键下面的那一个 ~ 按键，坑惨了。

反引号是为了区分 MySql 关键字与普通字符而引入的符号，一般的，表名与字段名都使用反引号。

1.2修改语句：

将 id 为 5 的手机号改为默认的 - : update students set tel=default where id=5;

将所有人的年龄增加 1: update students set age=age+1;

将手机号为 13288097888 的姓名改为 "小明", 年龄改为 19: update students setname="小明", age=19 where tel="13288097888";

mysql alter命令

添加列：

ALTER TABLE tb_test01 add id_02 int(10) FIRST # 增加字段 id_02 并且放在第一个字段
ALTER TABLE tb_test01 add id_03 int(10) AFTER id # 增加字段 id_03 并且放在id 后面

• 删除

ALTER TABLE tb_test01 drop id_03; #删除字段 id_03
ALTER TABLE tb_test01 drop PRIMARY KEY; #删除主键,注: 如果表有主键,不删除主键,无论添加主键,还是在修改的时候增加主键设置,都会失败,需要先删除主键,才可以对主键进行操作

• 修改:

关键字:modify, change, rename, alter [field] set default [num]
modify: 修改字段类型设置,不可以更改字段名
change: 修改字段类型设置 可以更改字段名
ALTER TABLE tb_test modify id_01 int(5) not null; # 把 id_01 字段类型修改 int(5),和非空
ALTER TBALE tb_test change id_01 id_test int(10); 把 id_01 更名 id_test 并且字段类型设置为 INT(10)
ALTER TABLE tb_test RENAME tb_test02; # 把表 tb_test 更名为 tb_test02;
ALTER TABLE tb_test ALTER id_01 set default 1000; #把表tb_test 的字段 id_01 设置默认值为1000,其他的约束不变
ALTER TABLE tb_test ALTER id_01 DROP DEFAULT; # 删除 字段 id_01 的默认设置值

ALTER TABLE tb_test ENGINE = MYISAM; # 把 表tb_test 类型设置为 MYISAM; myqsl默认 INNODB

1.3创建索引：

--创建普通索引CREATE INDEX index_name ON table_name(col_name);

--创建唯一索引CREATE UNIQUE INDEX index_name ON table_name(col_name);

--创建普通组合索引CREATE INDEX index_name ON table_name(col_name_1,col_name_2);

--创建唯一组合索引CREATE UNIQUE INDEX index_name ON table_name(col_name_1,col_name_2);

 通过修改表结构创建索引：ALTER TABLE table_name ADD INDEX index_name(col_name);

 创建表时直接指定索引

1.4删除索引：

--直接删除索引DROP INDEX index_name ON table_name;

--修改表结构删除索引ALTER TABLE table_name DROP INDEX index_name;

1.5查询语句：

 

2视图

2.1什么是视图？作用是什么？

CREATE VIEW <视图名> AS <SELECT语句>

视图兼有“表”和“查询”的特点，与查询类似的地方是，可以用来从一个或多个相关联的表中提取有用信息：与表相类似的地方是，可以用来更新其中的信息，并将更新结果永久地保存在磁盘上。可以从本地表(包括数据库表和自由表)、其他视图、存储在服务器上的表或远程数据源中创建视图来自百度文库（数据库表和自由表的区别）

如果需要经常执行某项复杂查询，可以基于这个复杂查询建立视图，此后查询此视图即可，简化复杂查询；
视图本质上就是一条SELECT语句，所以当访问视图时，只能访问到所对应的SELECT语句中涉及到的列，对基表中的其它列起到安全和保密的作用，可以限制数据访问。

视图本身并不包含数据，只是基表数据的逻辑映射。当基表数据发生变化，视图数据也随之变化。所以当对视图执行DML操作时，实际上是对基表的DML操作。对视图执行DML操作的基本原则：
•简单视图能够执行DML操作，下列情况除外：在基表中定义了非空列，但简单视图对应的SELECT语句并没有包含这个非空列，导致这个非空列对视图不可见，这时无法对视图执行INSERT操作；
•如果视图定义中包含了函数、表达式、分组语句、DISTINCT关键字或ROWNUM伪列，不允许执行DML操作；
•DML操作不能违反基表的约束条件。

MySQL可以嵌套定义视图，即在一个视图上在定义另一个视图

作用：

对于一些关联表的复杂查询，使用视图有时候会大大简化问题，因此在许多场合下都可以看到视图的身影，

首先视图可以简化应用上层的操作，让应用更专注于其所关心的数据。
其次，视图能够对敏感数据提供安全保护，比如：对不同的用户定义不同的视图，可以使敏感数据不出现在不应该看到这些数据的用户视图上；
也可以使用视图实现基于列的权限控制，而不需要真正的在数据库中创建列权限。
再者，视图可以方便系统运维，比如：在重构schema的时候使用视图，使得在修改视图底层表结构的时候，应用代码还可以继续运行不报错。

2.2视图能否加快查询速度

MySQL底层通过两种算法来实现视图：临时表算法（TEMPTABLE）和合并算法（MERGE）。所谓临时表算法就是将SELECT语句的结果存放到临时表中，当需要访问视图的时候，直接访问这个临时表即可。而合并算法则是重写包含视图的查询，将视图定义的SQL直接包含进查询SQL中。

可以在EXPLAN EXTENDED之后使用SHOW WARNINGS来查看使用视图的查询重写后的结果。如果采用临时表算法实现的视图，EXPLAIN中会显示为派生表（DERIVED），注意EXPLAIN时需要实际执行并产生临时表，所以有可能会很慢。临时表上没有任何索引，而且优化器也很难优化临时表上的查询。因此，如有可能，尽量使用合并算法会有更好的性能。

一般来说，只要原表记录和视图中的记录无法建立一一映射的关系时，MySQL都将使用临时表算法来实现视图。比如创建视图的SQL中包含GROUP BY、DISTINCT、UNION、聚合函数、子查询的时候，视图都将采用临时表算法&