视图
作用:
重用 sql语句。简化 复杂的sql。保护 数据,给予用户部分数据的访问权限而不是整个表。更改数据的格式和表示 。 视图不包含数据,是虚拟 的表。每次使用视图时,必须处理查询的所有检索,所以复杂视图和嵌套视图会大大降低性能。 视图不能索引 ,也不能有关联的触发器。 视图通常用于查询 ,而不用于行的增删。
使用
CREATE VIEW 创建视图。DROP VIEW 删除视图。CREATE OR REPLACE VIEW 更新视图。简化复杂联结,可以重用 。
CREATE VIEW productcustomers AS
SELECT cust_name, cust_contact, prod_id
FROM customers, orders, orderitems
WHERE customers.cust_id = orders.cust_id AND orders.order_num = orderitems.order_num;
SELECT cust_name, cust_contact
FROM productcustomers
WHERE prod_id = 'TNT2' ;
CREATE VIEW vendorlocation AS
SELECT CONCAT(RTRIM(vend_name), ' (' , RTRIM(vend_country), ')' ) AS vend_title
FROM vendors
ORDER BY vend_name;
SELECT vend_title FROM vendorlocation;
CREATE VIEW customeremaillist AS
SELECT cust_id, cust_name, cust_email
FROM customers
WHERE cust_email IS NOT NULL ;
SELECT cust_id, cust_name, cust_email FROM customeremaillist;
CREATE VIEW orderitemsexpanded AS
SELECT order_num, prod_id, quantity, item_price, quantity*item_price AS expanded_price
FROM orderitems;
SELECT order_num, expanded_price FROM orderitemsexpanded;
索引
使用索引可以更快的查找 数据,但是更新一个包含索引的表需要更多时间,因为索引也需要更新。
使用
CREATE INDEX 允许使用重复的值。CREATE UNIQUE INDEX 两行不能有相同的索引值。DESC 可以使用在列名后降序索引某列。
CREATE UNIQUE INDEX orderindex
ON orders(order_num DESC , order_date);
SELECT * FROM salaries FORCE INDEX(idx_emp_no)
WHERE emp_no = 10005 ;
ALTER TABLE orders DROP INDEX orderindex;
存储过程
作用:
封装操作 。简化 使用操作,减少错误,减少基础数据的访问从而提高安全 性。提高性能 。存储过程比单独使用sql要快。进行较复杂的逻辑操作。
使用
CALL 调用存储过程,可以将存储过程理解为一个函数。mysql变量名以@开始。 IN 表示传入参数, OUT 表示传出参数。INOUT 表示传入传出参数。INTO 表示将值赋予某个变量。DECLARE 声明变量。COMMENT 对存储过程说明。IF THEN 进行逻辑操作。
CREATE PROCEDURE ordertotal2(
IN onumber INT ,
IN taxable boolean,
OUT ototal DECIMAL (8 ,2 )
) COMMENT 'obtain orer total with tax'
BEGIN
-- var
DECLARE total DECIMAL (8 ,2 );
DECLARE taxrate INT DEFAULT 6;
SELECT SUM (item_price*quantity) FROM orderitems
WHERE order_num = onumber
INTO total;
IF taxable THEN
SELECT total*(1 +taxrate/100 ) INTO total;
END IF ;
SELECT total INTO ototal;
END ;
CALL ordertotal2(20005 , 0 , @total);
SELECT @total;
DROP PROCEDURE IF EXISTS ordertotal2;
SHOW CREATE PROCEDURE ordertotal;
SHOW PROCEDURE STATUS LIKE 'ordertotal%' ;
游标
对检索出来的结果集的行 进行操纵,如下一行、前10行。 游标只能用于存储过程 。
使用
DECLARE 创建游标, OPEN 、CLOSE 打开关闭游标。FETCH 游标访问结果集的每一行。CONTINUE HANDLER 表示在条件出现时执行代码,SQLSTATE ‘02000’ 是未找到条件,即没有更多行可以访问。REPEAT 和UNTIL 为循环语句。
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 ordertotal2(o, 1 , t);
INSERT INTO ordertotals(order_num, total) VALUES (o, t);
UNTIL done END REPEAT;
CLOSE ordernumbers;
END ;
CALL processorders;
触发器
针对关联的表 , 在DELETE 、INSERT 、UPDATE 之前或者之后执行操作。是一种特殊的存储过程 。 只有表 支持触发器,视图不支持。
使用
CREATE TRIGGER 创建触发器。NEW 访问操作之后的虚拟表 。OLD 访问操作之前的虚拟表。可以使用BEGIN END 执行多条语句。
CREATE TRIGGER neworder AFTER INSERT ON orders
FOR EACH ROW
SELECT NEW.order_num INTO @t;
CREATE TRIGGER deleteorder BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
INSERT INTO archive_orders(order_num, order_date, cust_id)
VALUES (OLD.order_num, OLD.order_date, OLD.cust_id);
END ;
CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors
FOR EACH ROW
SET NEW.vend_state = UPPER(NEW.vend_state);
DROP TRIGGER updatevendor;
事务处理
事务处理可以用来维护数据库的完整性 ,保证成批的sql操作要么完全执行,要么完全不执行 。 START TRANSACTION
标识事务的开始 。ROLLBACK 用于撤销sql语句,但是不能撤销CREATE,DROP,SELECT 操作。
START TRANSACTION ;
DELETE FROM ordertotals;
ROLLBACK ;
一般sql语句隐含提交 (implicit commit),即自动提交。但是事务处理中,不会隐含提交,需要使用COMMIT 。COMMIT或者ROLLBACK之后,事务会自动关闭,将来更改会隐含提交。
START TRANSACTION ;
DELETE FROM orderitems WHERE order_num = 20010 ;
DELETE FROM orders WHERE order_num = 20010 ;
COMMIT ;
SAVEPOINT 可以实现部分回退功能。 保留点在事务处理完成之后自动释放 ,也可以使用RELEASE SAVEPOINT 明确释放保留点。
SAVEPOINT delete1;
ROLLBACK TO delete1;
autocommit标识是否提交更改,针对每个连接 而不是服务器。SET autocommit = 0
设为不自动提交更改。SET autocommit = 1
设为自动提交更改。
字符集和校对
字符集 :字母和符号的集合。编码 :字符集成员的内部表示。校对 :规定字符如何比较。一个字符集可以有不止一种校对,有些校对区分大小写(_cs结尾区分大小写,_ci结尾不区分大小写)。 通过CHARACTER SET 指定字符集,COLLATE 指定校对。 如果不指定,则使用数据库的默认值 。 也可以为每个列设置字符集和校对。
CREATE TABLE mytable
(
col1 INT ,
col2 VARCHAR (10 ),
col3 VARCHAR (10 ) CHARACTER SET latin1 COLLATE latin1_general_ci,
PRIMARY KEY (col1)
)
CHARACTER SET hebrew
COLLATE hebrew_general_ci;
可以在SELECT中临时指定校对,区分大小写,从而影响最终排序。
SELECT * FROM mytable
ORDER BY col3
COLLATE latin1_general_cs;
安全管理
用户应该对他们所需要的数据具有适当的权限 ,既不能多也不能少。 访问控制有助于防止无意的错误。尽量不要使用root。
设置账户
查找账号列表。账号信息存储在mysql数据库的user表中。账号为user@host
,host可以为%,表示不限制登录主机。
USE mysql;
SELECT user FROM user ;
CREATE 创建账号, IDENTIFIED BY PASSWORD 给出口令。
CREATE USER aaa IDENTIFIED BY 'aaa' ;
RENAME USER aaa TO bbb;
DROP USER bbb;
SET PASSWORD FOR aaa = PASSWORD('bbb' );
SET PASSWORD = PASSWORD('bbb);
设置账户权限
设置访问权限。GRANT 赋予权限,REVOKE 取消权限。
GRANT SELECT ,INSERT ON mysql_crash_course.* TO aaa;
REVOKE INSERT ON mysql_crash_course.* FROM aaa;
权限的范围,ON 之后。
整个服务器。 ON *.*
。 整个数据库。 ON database.*
。 特定的表。 ON database.table
。 特定的列。GRANT SELECT(col1, col2) ON database.table
。 特定的存储过程。GRANT EXECUTE ON PROCEDURE database.procedureName
。 权限的类型。
性能分析
EXPLAIN