视图
- 作用:
- 重用 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;
- 修改密码。FOR省略,则更新当前登录用户的口令。
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
- 分析sql语句的执行及数据库索引的使用。
- 详细解释:https://blog.csdn.net/jathamj/article/details/53909614
版权声明:本文为qq_40369829原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。