MYSQL笔记21-25

创建和操纵表

创建表

一般有两种创建表的方法：

• 使用具有交互式创建和管理表的工具

• 表也可以用Mysql语句操纵

  为了用程序创建表，可使用SQL的CREATE TABLE语句，实际上我们在使用navicat等交互式工具时，实际上也是在使用MYSQL语句，界面工具会自动生成并执行相应的MySQL语句；

表创建基础

利用CREATE TABLE创建表，需要给出下列信息：

• 新表的名字，在关键字CREATE TABLE之后给出；

• 表列的名字和定义，用逗号分隔。

  CREATE TABLE语句也可以包含其他关键字或选项，至少要包括表的名字和列的细节。

CREATE TABLE customers
(
	cust_id 	int NOT NULL AUTO_INCREMENT,
	cust_name 	char(50) NOT NULL,
	cust_address char(50) NULL,
	cust_city	char(50) NULL,
	cust_state	char(5)	NULL,
	cust_zip	char(10) NULL,
	cust_country char(50) NULL,
	cust_contact char(50) NULL,
	cust_email char(255) NULL,
	PRIMARY KEY(cust_id)
)ENGINE=InnoDB;
           

表名紧跟在CREATE TABLE 关键字后面，表定义括在圆括号之后，各列之间用逗号隔开，这个表由9列组成，每列的定义以列名开始，后跟列的数据类型；表的主键由PRIMARY KEY关键字指定，列cust_id指定作为主键列，整条语句由右圆括号后面的分号结束，暂时忽略ENGINE=innoB；

使用NULL值

之前说NULL值就是没有值或缺值，允许NULL值的列也允许在插入行时不给出列的值，不允许NULL值的列不接受该列没有值的行，也就是说没有写入NULL值时，该列必须要有内容；

每个表列或者NULL列或者NOT NULL列，这种状态在创建时由表的定义规定。

CREATE TABLE orders
(
	order_num int NOT NULL AUTO_INCREMENT,
	order_date datetime NOT NULL,
	cust_id int NOT NULL,
	PRIMARY KEY (order_num)
)ENGINE=InnoDB;
           

这条语句创建了order_num,order_date,cust_id三个列表，每个列都定义了NOT NULL,这将会去阻止插入没有值得列，如果试图插入没有值得列，将返回错误，且插入失败

CREATE TABLE vendors
(
	vend_id int NOT NULL AUTO_INCREMENT
	vend_name char(50) NOT NULL,
	vend_address char(50) NULL,
	vend_city char(50) NULL,
	vend_state char(5) NULL,
	vend_zip char(10) NULL,
	vend_country char(50) NULL,
	PRIMARY KEY(vend_id)
)ENGINE = InnoDB;
           

这条语句创建了vendors表，将vend_name设置为NOT NULL,其他的列都允许NULL，这是一个混合表，当并指定NOT NULL时，NULL为默认设置，如果不指定NOT NULL,则认为指定是NULL；

主键

主键值必须是唯一，表中的每个行必须具有唯一的主键值，如果主键使用单个列，则它的值必须是唯一，如果使用多个列，则这些列的组合值必须是唯一的；

为创建由多个列组成的主键，必须以逗号分隔的列表给出各列名：

CREATE TABLE orderitems
(
	order_num int NOT NULL,
	order_item int NOT NULL,
	prod_id char(10) NOT NULL,
	quantity int NOT NULL,
	item_price deciaml(8,2) NOT NULL,
	PRIMARY KEY (order_num,order_item)
)ENGINE=InnoDB;
           

创建orderitems表，设置order_num,order_item的组合是唯一的，从而设置主键，其定义为：

主键可以在创建表时定义，或者在创建表之后定义；

使用AUTO_INCREMENT

AUTO_INCREMENT告诉MySQL，本列每当增加一列时自动增量，每次执行一个INSERT操作时，MySQL自动对该列增量，给该列赋予下一个可用的值，这样给每个行分配一个唯一的cust_id从而可用作主键值；

每个表只允许一个AUTO_INCREMENT列，而且必须被索引；

指定默认值

如果再插入行时没有给出值，MySQL允许指定此时使用的默认值，默认值用CREATE TABLE语句的列定义的DEFAULT关键字指定。

CREATE TABLE orderitems
(
	order_num int NOT NULL,
	order_num int NOT NULL,
	prod_id char(10) NOT NULL,
	quantity int NOT NULL DEFAULT 1,
	item_price decimal(8,2) NOT NULL,
	PRIMARY KEY(oder_num,order_item)
)ENGINE=InnoDB;
           

这条语句创建orderitems表，quantity描述添加 DEFAULT 1指示MySQL，在未给出数据的情况下，使用默认值1；

引擎类型

上述创建的表都是以ENGINE = InnoDB语句结束；

MySQL有一个具体管理和处理数据的内部引擎，在使用CREATE TABLE语句时，该引擎也在具体创建表，而在使用SELECT语句或进行其他处理时，该引擎在内部处理该请求，多数情况下，都会隐藏在DBMS内；

MYSQL与其他DBMS不一样的是，它具有多种引擎，打包多个引擎，这些引擎都隐藏在MySQL服务器内，全部都可以执行CREATE TABLE和SELECT等命令；

MySQL下有多个引擎，一般情况下，都有默认引擎；

有需要知道的几个引擎：

• InnoDB是一个可靠的事务处理引擎，它不支持全文本搜索
• MEMORY在功能等同于MyISAM，但由于数据存储在内存中，速度很快（适合临时表）
• MyISAM是一个性能极高的引擎，它支持全文本搜索，但不支持事务处理；

更新表

更新表定义，可使用ALTER TABLE语句，为了使用ALTER TABLE更改表结构，必须给出下面信息：

• 在ALTER TABLE之后给出要更改的表名（该表必须存在，否则将出错）
• 所作更改的列表

ALTER TABLE
	ADD vend_phone CHAR(20);
           

这条语句给vendors表增加一个名为vend_phone的列，必须明确其数据类型；

删除刚刚添加的列，也可以这样做：

ALTER TABLE Vendors
DROP COLUMN vend_phone;
           

删除表

删除表非常简单，使用DROP TABLE语句

这条语句删除customers 2表，删除表没有确认，也不能撤销，执行会永久删除该表

重命名表

使用RENAME TABLE语句可以重命名一个表：

RENAME TABLE所做的仅是重名一个表，可以对多个表进行重命名：

RENAME TABLE backup_customers TO customers,
			backup_vendors TO vendors,
			backup_products TO products;
           

使用视图

视图

需要MySQL5 MySQL5增加了对视图的支持；

视图是虚拟的表。与包含数据的表不一样，视图只包含使用时动态检索数据的查询

比如之前我们使用SELECT从三个表中检索数据：

SELECT cust_name,cust_contact
FORM customers,orders,orderitems,
WHERE customers.cust_id=orders.cust_id
	AND orderitems.order_num=orders.order_num
	AND prod_id='TNT2';
           

使用SELECT检索三个表中的数据；

假如可以把整个查询包装成一个名为productcustomers的虚拟表：

SELECT cust_name,cust_contact
FROM productcustomers
WHERE prod_id='TNT2';
           

这个就是视图作用：将检索出来的内容包装为一个表；

为什么使用视图？

视图的常见应用：

1. 重用SQL语句
2. 简化复杂的SQL操作，在编写查询后，可以方便的重用它而不必知道它的基本查询细节
3. 使用表的组成部分而不是整个表
4. 保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限

5. 更改数据格式和表示，视图可返回与底层表示和格式不同的数据

   在视图创建后，可以以表的基本相同的方式使用它们；

   重要的是知道视图不仅仅是用来查看存储在别处的数据的一种设施，视图本身不包含数据，因此它们返回的数据是从其他表中检索出来的，在添加或更改这些表中的数据时，视图将返回改变过的数据。

视图的规则和限制

视图创建和使用的规则和限制：

• 与表一样，视图必须唯一命名；
• 对于可以创建的视图数目没有限制；
• 为了创建视图，必须具有足够的访问权限；
• 视图可以嵌套，利用从其他视图中检索数据的查询来构造一个视图
• ORDER BY可以用在视图中，但如果从该视图检索数据SELECT中也含有ORDER BY，那么该视图中的ORDER BY被覆盖
• 视图不能索引，不能有关联的触发器或默认值
• 视图可以和表一起使用；

使用视图

什么是视图？如何创建视图？

• 视图由CREATE VIEW 语句来创建
• 使用SHOW CREATE VIEW viewname; 来查看创建视图的语句
• 用DROP删除视图，其语法为ORDER VIEW viewname;
• 更新视图时，可以先用DROP再用CREATE，在直接用CREATE OR REPLACE VIEW，如果要更新的视图不存在，则第二条更新语句会创建一个视图，如果要更新的视图存在，则第二条语句更新语句会替换原有的视图；

利用视图简化复杂的联结

视图简化隐藏复杂的SQL：

CREATE VIEW productcustomers AS
SELECT cust_name,cust_contact,prod_id
FROM customers,orders,orderitems
WHERE customers.cust_id=order.cust_id
	AND orderitems.order_num=orders.order_num;
           

这条语句创建了productcustomers 的视图，它联结了三个表，返回数据，我们可以使用SELECT来查看该视图

视图极大的简化了复杂SQL语句的使用，利用视图，可一次性编写基础的SQL，然后根据需要使用多次；

用视图重新格式化检索出的数据

利用视图可以重新格式化检索出的数据

如果经常需要一个格式化的结构，不需要每次需要时执行，创建一个视图，每次需要时使用它就可，为此需要将其转换为视图：

CREATE VIEW vendorlocations AS
SELCT Concat（RTrim(vend_name),'(',RTrim(vend_country),')') AS vend_title
FROM vendors
ORDER BY vend_name;
           

将其转换为视图，多次使用，并且可以通过SELECT进行其他的筛选；

使用视图与计算字段

CREATE VIEW orderitemsexpanded AS
SELECT order_num,
		prod_id,
		quantity,
		item_price
		quantity*item_price AS expanded_price
FROM orderitems;
           

根据创建的视图，视图可以极大的简化复杂的数据处理；

更新视图

视图是可更新的：更新一个视图将其更新其基表，如果对视图增加或删除行，实际上对其基表进行增加或删除行；

但是，并非所有视图可更新的，如果MySQL不能正确的确定被更新基数据，则不允许更新，如果视图定义中以下的操作则不能对视图进行更新：

• 分组
• 联结
• 子查询
• 并
• 聚集函数
• DISTINCT
• 导出列

使用存储过程

存储过程

大多数SQL语句都是针对一个或多个表的单条语句，经常会有一个完整的操作需要多条语句才能完成。但是每次进行操作又显得繁琐

可以创建存储过程，存储过程简单的来说，就是为了以后的使用而保存的一条或多条MySQL语句的集合，可将其为批文件，虽然作用不仅限与批处理

为什么要使用存储过程？

为什么要使用存储过程？

• 通过把处理封装在容易使用的单元，简化复杂的擦欧总
• 由于不要求反复建立这一系列处理步骤，保证了数据的完整性，如果开发人员和应用程序都使用同一存储过程，则使用的代码是相同的，这一点来看，我们避免错误，防止错误保证了数据的一致性

• 简化对变动的管理，如果表名，列名或业务逻辑，有变化，只需要改变存储过程的代码，使用人员甚至不需要知道这些变化；

  这些延伸就是安全性，通过存储过程限制对基础数据的访问减少了数据的讹误的机会；

  使用存储过程的有三个主要的好处，简单，安全，高性能，它们很重要，将SQL代码转换为存储过程前，必须要知道它的缺点

• 存储过程的编写比基本SQL语句复杂，编写存储过程需要更高的技能和熟练度
• 可能没有创建存储过程的安全访问权限，许多数据库管理员限制存储过程的创建权限，允许用户使用存储过程但不允许它们创建存储过程

执行存储过程

MySQL称存储过程的执行为调用，因此MySQL执行存储过程的语句为CALL，CALL接受存储过程的名字以及需要传递给它的任意参数：

CALL producatpricing(@pricelow,
					@pricehtigh,
					@priceaverage);
           

执行名为productpricing的存储过程，它计算并返回其结果；

创建存储过程

CREATE PROCEDURE productpricing()
BEGIN
	SELECT Avg(prod_price)AS priceaverage
	FROM products;
END;
           

此存储过程名为productpricing，用CREATE PROCEDURE productpricing()语句定义，如果存储过程接受参数，将在()中列举处理，此存储过程没有参数，但是括号还是要加的，BEGIN和END语句用来显得存储过程体，过程体本身仅是一个简单的SELECT语句；

MySQL处理这段代码的适合，创建了一个新的存储过程prodyctpricing，没有返回数据，因为这段代码并未调用存储过程；

这样我们就可以使用它了，执行并返回结果；

删除存储过程

存储过程在创建之后，被保存在服务器上使用，直至被删除，删除目录，从服务器中删除存储过程。

这条语句就将存储过程删掉了；

使用参数

productpricing只是一个简单的存储过程，它简单的显示了SELECT语句的机构，一般存储过程并不显示结果，而是把结果返回给指定的变量；

CREATE PROCEDURE productpricing(
		OUT pl DECIMAL(8,2),
		OUT ph DECIMAL(8,2),
		OUT pa DECIMAL(8,2),
		)
		BEGIN
			SELECT Min(prod_price)
			INTO p1
			FROM products;
			SELECT Max(prod_price)
			INTO ph
			FROM products;
			SELECT Avg(prod_price)
			INTO pa
			FROM products;
		ENG;
           

这个存储过程接受3各参数，pl,ph,pa,每个参数都必须具有指定的类型，关键字OUT指定相应的参数用来从存储过程传出一个值，通过INTO返回给参数；

为了调用这个存储过程，我们需要三个参数

CALL productpricing(@pricelow,
					@pricehigh,
					@priceaverage);
					//不会显示值，将检索的数据返给变量
SELECT @pricelow;	//显示数据
           

检查存储过程

用来创建一个存储过程的CREATE语句，使用SHOW CREATE PROCEDURE语句，

获得包含何时，由谁创建等详细信息的存储过程列表，使用SHOW CREATE PROCEDURE STATUS；

使用游标

MYSQL检索操作返回一组称为结果集的行，这组返回的行都是与SQL语句相匹配的行，使用间的SELECT语句，有时需要在检索处理的行中前进或后退一行或多行，这就是为什么我们需要使用游标；

游标是应该存储在MySQL服务器上的数据库查询，他不是一条SELECT语句，而是被该语句检索出来的结果集，存储了游标之后，应用程序可以根据需要滚动或者浏览其中的内容；

游标主要用于交互式应用，其中用户需要滚动屏幕上的数据，并对数据进行浏览或做出更改

使用游标

• 在能够使用游标前，必须声明它，这个过程实际上没有检索数据，只是定义要使用的SELECT语句
• 一旦声明后，必须打开游标以供使用，这个过程用前面定义的SELECT语句把数据实际检索处理
• 对于填有数据的游标。更具需要取出各列
• 在结束游标使用后，必须关闭游标

创建游标

游标用关键字DECLARE语句创建。DECLARE命名游标，并定义其相应的SELECT语句，根据需要带WHERE和其他子句；

CREATE PROCEDURE processorders()
BEGIN
	DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders;
END;
           

这里创建定义了名为ordernumbers的游标，使用了可以检索其他订单的SELCT语句

DECLARE语句用来定义和命名游标。存储过程处理完成后，游标就消失。

在定义游标之后，可以打开它

打开和关闭游标

游标用OPEN CURSOR语句来打开：

在处理OPEN语句时执行查询。存储检索出的数据以供浏览和滚动

关闭游标则是

CLOSE ordernumbers;

CLOSE 释放游标使用的所有内部内存和资源，因此每个游标不再需要时都应该关闭；

使用游标数据

在一个游标被打开后，可以使用FETCH语句分别访问它的每一行，FETCH指定检索声明数据，检索出来的数据存储在声明地方，它还向前移动游标中的内部行指针，使下一条FETCH语句检索下一行。

CREATE PROCEDURE processorders()
BEGIN
		DECLARE o INT;
		
		DECLARE ordernumbers CURSOR
		FOR 
		SELECT order_num FROM orders;
		OEPN ordernumbers;
		FETCH ordernumbers INTO o;
		CLOSE ordernumbers;
END;
           

其中EFTCH用来检索当前行的order_num列到一个名为o的局部声明的变量中，对检索出的数据不做任何处理；

这个例子循环检索数据，从第一行到最后一行：

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

	OPEN ordernumbers;
	REPET
		FETCH ordernumbers INTO o;

	UNTIL done END REPEAT;
	
	CLOSE ordernumbers;
END;
           

这个例子使用FETCH检索当前order_num到声明的名为o的变量，这个例子中的FETCH是在REPEAT中，因此它反复执行到done为真，为使它起作用，用到一个DEFAULT 0定义了done；一直到结束；

触发器

什么是触发器

触发器（Trigger）：监视某种情况，并触发某种操作。在MySQL Server里面也就是对某一个表的一定的操作，触发某种条件（Insert,Update,Delete 等），从而自动执行的一段程序。

注意：你必须拥有相当大的权限才能创建触发器（CREATE TRIGGER），如果你已经是Root用户，那么就足够了。这跟SQL的标准有所不同。

触发器作用

那么为什么要使用数据库对象触发器呢？在具体开发项目时，经常会遇到如下实例：

　　（1）在学生表中拥有字段学生姓名，字段学生总数，每当添加一条学生信息时，学生的总数就必须同时更改。

　　（2）在学生表中还会有学生姓名的缩写，学生住址等字段，添加学生信息时，往往需要检查电话、邮箱等格式是否正确。

　　上面的例子使用触发器完成时具有这样的特点，需要在表发生改变时，自动进行一些处理。MySQL在触发DELETE/UPDATE/INSERT语句时就会自动执行所设置的操作，其他SQL语句则不会激活触发器。

触发器四要素

监视地点：table

监听事件：insert/update/delete

触发时间：after/before

触发事件：insert/update/delete

触发器用法

触发器语法：

CREATE TRIGGER <触发器名称> --触发器必须有名字，最多64个字符，可能后面会附有分隔符.它和MySQL中其他对象的命名方式基本相象.

　　{ BEFORE | AFTER } --触发器有执行的时间设置：可以设置为事件发生前或后。

　　{ INSERT | UPDATE | DELETE } --同样也能设定触发的事件：它们可以在执行insert、update或delete的过程中触发。

　　ON <表名称> --触发器是属于某一个表的:当在这个表上执行插入、 更新或删除操作的时候就导致触发器的激活. 我们不能给同一张表的同一个事件安排两个触发器。

　　FOR EACH ROW --触发器的执行间隔：FOR EACH ROW子句通知触发器 每隔一行执行一次动作，而不是对整个表执行一次。

　　<触发器SQL语句> --触发器包含所要触发的SQL语句：这里的语句可以是任何合法的语句， 包括复合语句，但是这里的语句受的限制和函数的一样。

　　create trigger tg_name after/before insert/update/delete on table
　　for each row ####这句话在MySQL中是固定的
　　begin
　　sql语句;
　　end$
           

分析：由于在以上代码段中的“sql语句;”是以分号结尾，所以需要将MySQL中的结尾标志换成“$”，更换MySQL命令结束标志的命令：delimiter $;

创建触发器

(1）创建两张表

　　create table tb_goods(id int primary key auto_increment,name varchar(20),num int);
　　create table tb_orders(id int primary key auto_increment,good_id int,much int);
           

(2）在商品表中插入数据

(3)假如我们卖了3个商品1

没有触发器：

往订单表中插入一条记录：insert into tb_orders(good_id,much) values(1,3);

　　　　更新商品表中商品1的剩余数量：update tb_goods set num=num-3 where id=1;

创建触发器：

　　create trigger tg_1 after insert on tb_orders
　　for each row
　　begin
　　update tb_goods set num=num-3;
　　end$
           

这个时候如果执行insert into tb_orders(good_id,much) values(1,3);会发现商品的数量变为7了，说明在插入一条订单的时候，触发器自动做了更新操作。

触发器对值d的引用

上述触发器有一个问题，因为在触发器中写死了num和id，所以不管买哪个商品，最终更新的都是商品1的数量。这个时候，需要将触发器中的值变为动态获取。

对于insert来说，新插入的行用new来表示，行中的每一列的值用“new.列名”来表示：

新建能动态获取值的触发器：

　　create trigger tg_2 after insert on tb_orders
　　for each row
　　begin
　　update tb_goods set num=num-new.much where id=new.id;
　　end$
           

删除第一个触发器：drop trigger tg_1;

　　插入一条订单记录：insert into tb_orders(good_id,much) values(2,3)$

　　执行完发现商品的数量变为7了，这样子就是合适的。

触发器实例

　　1）创建表tab1　

　　DROP TABLE IF EXISTS tab1;
　　CREATE TABLE tab1(
　　    tab1_id varchar(11)
　　);

　　2）创建表tab2

　　DROP TABLE IF EXISTS tab2;
　　CREATE TABLE tab2(
　　    tab2_id varchar(11)
　　);

　　3）创建触发器：t_afterinsert_on_tab1　 

DROP TRIGGER IF EXISTS t_afterinsert_on_tab1;
CREATE TRIGGER t_afterinsert_on_tab1 
AFTER INSERT ON tab1
FOR EACH ROW
BEGIN
     insert into tab2(tab2_id) values(new.tab1_id);
END;

　　4）想tab1插入数据　

INSERT INTO tab1(tab1_id) values('0001');

　　5）查看变化　

```sql
SELECT * FROM tab1;
SELECT * FROM tab2;