SQL 札记（三）

1. 组合查询	包含或取消重复的行（使用UNION 时，重复的行被自动取消）	select vend_id, prod_id, prod_price from products where prod_price <= 5    -> union all    -> select vend_id, prod_id, prod_price from products     -> where vend_id in (1001, 1002);	+---------+---------+------------+	| vend_id | prod_id | prod_price |	+---------+---------+------------+	|    1003 | FC      | 2.5        |	|    1002 | FU1     | 3.42       |	|    1003 | SLING   | 4.49       |	|    1003 | TNT1    | 2.5        |	|    1001 | ANV01   | 5.99       |	|    1001 | ANV02   | 9.99       |	|    1001 | ANV03   | 14.99      |	|    1002 | FU1     | 3.42       |	|    1002 | OL1     | 8.99       |	+---------+---------+------------+		对组合查询拍讯	在UNION 组合查询时，只能使用一条ORDER BY子句，它必须出现在最后一条SELECT语句之后。	2. 全文本搜索	MySQL 支持几种基本的数据库引擎。两个最常使用的为MyISAMySQL和InnoDB，前者支持全文本搜索，	而后者不支持。	为了使用全文本搜索，必须索引被搜索的列，而且要随着数据的改变不断地重新索引。在对表进行适当	的设计后，MySQL 会自动进行所有的索引和重新索引。	在索引之后，SELECT可与Match() 和 Against() 一起使用以实际执行搜索。		启用全文本搜索支持	在创建表时启用，create table 语句时接受 FULL TEXT 子句，它给出被索引列的一个逗号分隔的列表。		create table sample (    -> note_id int not null auto_increment,    -> prod_id char(10) not null,    -> note_date datetime not null,    -> note_text text null,    -> primary key(note_id),    -> FULLTEXT(note_text)	// 在定义后MySQL 自动维护该索引    -> )ENGINE=MyISAM;		进行全文搜索	Match() 指定被搜索的列，Against() 指定要使用的搜索表达式。	select note_text from productnotes where Match(note_text) Against('rabbit');	+---------------------------------------------------------------------------------------------------------------------+	| note_text                                                                                                           |	+---------------------------------------------------------------------------------------------------------------------+	| Customer complaint: rabbit has been able to detect trap, food apparently less effective now.                        |	| Quantity varies, sold by the sack load.	All guaranteed to be bright and orange, and suitable for use as rabbit bait. |	+---------------------------------------------------------------------------------------------------------------------+		查看计算的权重	select note_id, note_text , Match(note_text) Against('rabbit') as rank from productnotes;		使用查询扩展	select note_text from productnotes where Match(note_text) against('anvils');	select note_text from productnotes where Match(note_text) against('anvils' WITH QUERY EXPANSION);	 	布尔文本搜索	及时没有FULLTEXT 索引也可以使用 	为了匹配heavy 但不包含rope 开始的词的行	select note_text from productnotes where    -> Match(note_text) Against('heavy -rope*' IN BOOLEAN MODE);		全文本boolean 操作符	+	包含	-	排除	>	包含，而且增加等级值	<	包含，且减少等级值	()	把词组成子表达式	~	取消一个词的排序值	*	词尾的通配符	""	定义一个短语（与单个词的列表不一样，它匹配整个短语以便包含或排除这个短语）	3. 插入数据	如果数据检索式最重要的，则可以通过	insert low_priority into 降低 insert 的优先级。		插入多条数据，只要insert 语句中的列名（和次序）相同，可以如下组合各语句：	insert into customers(cust_name,		cust_address,		cust_city,		cust_state)	values(		'Pep',		'',		''	),	(	'Pep',		'',		''	)		插入检索出来的数据	insert into customers() select cust_id,.... from custnew;	4. 更新和删除数据	如果想从表中删除所有行，不要使用DELETE。可以使用TRUNCATE TABLE。	该语句不是逐行删除，而是删除表，再构建一个空表。		更新和删除的知道原则：	5. 创建和操纵表	主键： primary key() 多个值由逗号隔开。	PRIMARY KEY(order_num, order_item)		AUTO_INCREMENT 每个表只允许一个auto_increment，而且它必须被索引。		如何获得 auto_increment 的值？	select last_insert_id();		默认值：	MySQL 与大多数DBMS 不一样，MySQL不允许使用函数作为默认值，它只支持常量。		混用存储引擎一个大缺陷是外键不能跨引擎。	6. 更新表	添加列	alter table vendors add vend_phone char(20);		删除列	alter table vendors drop column vend_phone,		ALTER TABLE 的一种常见用途是定义外键。	alter table orderitems add constraint fk_orderitems_orders foreign key(order_num) 	refferences order (order_num)	7. 删除表	drop table customers2;	8. 重命名表	rename table customers2 to customers;	9. 使用视图（view）10. 存储过程	可以将存储过程视为批处理文件。		执行存储过程	call productpricing(@pricelow, @pricehigh, @priceaverage);		创建存储过程	create procedure productpricing()    -> begin    -> select Avg(prod_price) as priceaverage from products;    -> end;		改变命令行客户机的分隔符	delimiter //	create procedure productpricing()	begin		select Avg(prod_price) as priceagerage from products;	end //		// 恢复分隔符	delimiter ;		调用 call productpricing();	call productpricing();	+--------------+	| priceaverage |	+--------------+	| 16.133571    |	+--------------+		删除存储过程， 删除的时候只给出存储过程名	drop procedure productpricing;	drop procedure if exists		创建带参数的存储过程	 create procedure productpricing(    -> out pl decimal(8,2),    -> out ph decimal(8,2),    -> out pa decimal(8,2))    -> begin    -> select min(prod_price) into pl from products;    -> select max(prod_price) into ph from products;    -> select avg(prod_price) into pa from products;    -> end;		参数类型	IN OUT INOUT	11. 游标的使用（MySQL的游标只能用于存储过程）	a. 在能够使用之前，必须定义它。	b. 一旦声明之后，必须打开游标以供使用。	c. 对于填有数据的游标，根据需要取出检索各行。	d. 在结束游标使用时，必须关闭游标。	create PROCEDURE processorders()	BEGIN		Declare ordernumbers CURSOR		FOR 		SELECT order_num FROM orders;	END;		打开游标	OPEN ordernumbers;		关闭游标	CLOSE ordernumbers;		使用游标数据	12. 触发器	触发器是MySQL响应DELETE,  INSERT, UPDATE 语句而自动执行的一条MySQL语句。	CREATE TRIGGER newproduct AFTER INSERT ON products FOR EACH ROW SELECT 'Product added';		只有表才支持触发器，视图不支持。（临时表也不支持）		触发器按每个表每个事件每次地定义，每个表每个事件每次只允许一个出发器。因此，每个表最多支持	6个触发器（每条insert update 和 delete 的之前和之后）。单一触发器不能与多个事件或多个表关联。		删除触发器	drop trigger newproducts;	触发器不能更新或覆盖。为了修改一个触发器，必须先删除它，然后再重新创建。	在insert 触发器代码内，可以引用一个名为NEW 的虚拟表，访问被插入的行。	create trigger neworder after insert on orders for each row select NEW.order_num;		DELETE 触发器代码内。可以引用一个名为OLD的虚拟表。访问被删除的行。	13. 管理事物	事物处理用来管理insert、update和delete 语句。不能回退select 语句。事物处理中可以使用	create 或drop 操作。但是也不能回退。	14. 更改默认的提交行为。	set autocommit = 0;	15. 全球化和本地化	SHOW CHARACTER SET; 	显示系统所有可用的字符集。		查看所支持校对的完整列表。使用如下语句：	SHOW COLLATION;		显示所用的字符集和校对：	show variables like 'character%';	show variables like 'collation%';		创建表时指定字符集和校对：	create table mytable(		column1 INT,		column2 varchar(10)	) DEFAULT CHARACTER SET hebrew 	COLLATE hebrew_general_ci;		为列指定默认的字符集	create table mytable(		column1 INT,		column2 varchar(10) character set latin1 collate latin1_general_ci	) DEFAULT CHARACTER SET hebrew 	COLLATE hebrew_general_ci;		可以再select 语句中使用collate 指定一个备用的校对顺序。	select * from customers order by lastname, firstname collater latin1_general_cs;	(一种临时区分大小写的技术。)	16. 安全管理	管理用户 MySQL 数据库有一个名为user 的表，它包含所有的用户账号。user 表有一个名为user 的列。	它存储用户登录名。		创建用户账号	create user ben identified by 'zhang';	重命名用户	rename user ben to bforta;		删除用户	drop user bforta;		设置访问权限，查看用户账号的权限	GRANT 要求你至少给出以下信息：	要授予的权限、被授予访问权限的数据库或表、用户名	grant select on carshcouse.* to bforta;	show grants for bforta;		取消权限的语句	REVOKE select on crashcourse.* from beforta;		GRANT 和REVOKE可在几个层次上控制访问权限：	整个服务器，使用GRANT ALL 和 REVOKE ALL	整个数据库，使用 ON database.*	特定的表，使用 ON database.table	特定的列	特定的存储过程		简化多次授权：	GRANT SELECT， INSERT ON crashcourse.* TO bforta;		更改口令：	SET PASSWORD FOR bforta = PASSWORD('zzy');	在不指定用户名时，SET PASSWORD 是更新当前登录用户的口令。	17. 数据库维护	进行数据库维护 ANALYZE TABLE 	 analyze table orders;	+-----------------+---------+----------+----------+	| Table           | Op      | Msg_type | Msg_text |	+-----------------+---------+----------+----------+	| sqltest2.orders | analyze | status   | OK       |	+-----------------+---------+----------+----------+		check table 用来针对许多问题对表进行检查。	changed 检查最后几次检查依赖改动过的表。	extended 执行最彻底的检查。	fast 只检查未正常关闭的表。	medium 检查所有被删除的链接并进行键检验。	quick 只进行快速扫面。		MyISAM 表访问产生不正确和不一致的结果，可能需要用repair table来修复相应的表。	如果从一个表中删除大量的数据，应该使用optimize table 来回收所用的空间。从而优化	表的性能。		诊断启动问题：	MySQL 服务器自身通过在命令行执行mysqld 启动。	18. 改善性能	like 很慢，一半来说最好使用fulltext 而不是 like。