GuangchaoSun's Blog

MySQL必知必会-下

发表于  |    |   分类于   |  

19. 插入数据

如果数据检索是最重要的（通常是这样），则你可以通过在INSERT和INTO之间添加关键字LOW_PRIORITY，指示MySQL降低INSERT语句的优先级：
INSERT LOW_PRIORITY INTO
使用INSERT SELECT从custnew中将所有数据导入customers：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
INSERT INTO customers(cust_id,
    cust_contact,
    cust_email,
    cust_name,
    cust_address,
    cust_city,
    cust_state,
    cust_zip,
    cust_country)
SELECT cust_id,
    cust_contact,
    cust_email,
    cust_name,
    cust_address,
    cust_city,
    cust_state,
    cust_zip,
    cust_country
FROM costnew;

20. 更新和删除数据

更新数据

UPDATE由3部分组成：

  • 要更新的表；
  • 列名和它们的新值；
  • 确定要更新行的过滤条件；

注意：不要忘记where条件，不然会更新表中指定类列的所有行

1
2
3
4
UPDATE customers
SET cust_name = 'The Fudds',
    cust_email = 'elmer@fudd.com'
WHERE cust_id = 10005;

IGNORE关键字
如果用UPDATE语句更新多行，并且在更新这些行中的一行或多行时出一个现错误，则整个UPDATE操作被取消（错误发生前更新的所有行被恢复到它们原来的值）。为即使是发生错误，也继续进行更新，可使用IGNORE关键字，如下所示：UPDATE IGNORE customers…

删除数据

DELETE不需要列名或通配符。DELETE删除整行而不是删除列。为了删除指定的列，请使用UPDATE语句。
如果想从表中删除所有行，建议使用TRUNCATE TABLE

使用UPDATE或DELETE时的注意事项：

  • 除非确实打算更新和删除每一行，否则绝对不要使用不带WHERE子句的UPDATE或DELETE语句。
  • 保证每个表都有主键（如果忘记这个内容，请参阅第15章），尽可能像WHERE子句那样使用它（可以指定各主键、多个值或值的范围）。
  • 在对UPDATE或DELETE语句使用WHERE子句前，应该先用SELECT进行测试，保证它过滤的是正确的记录，以防编写的WHERE子句不正确。
  • 使用强制实施引用完整性的数据库（关于这个内容，请参阅第15章），这样MySQL将不允许删除具有与其他表相关联的数据的行。

21. 创建和操纵表

为利用CREATE TABLE创建表，必须给出下列信息：

  • 新表的名字，在关键字CREATE TABLE之后给出；
  • 表列的名字和定义，用逗号分隔。

允许NULL值得列也允许在插入行时不给出该列的值。
主键中只能使用不允许NULL值的列。
AUTO_INCREMENT高速MySQL，本列每当增加一行时自动增量。
SELECT last_insert_id()此语句返回最后一个AUTO_INCREMENT值。

1
2
3
4
5
6
7
8
9
CREATE TABLE orderitems
(
  order_num   int          NOT NULL,
  order_item  int          NOT NULL,
  prod_id     char(10)     NOT NULL,
  quantity    int          NOT NULL  DEFAULT 1,
  item_price  decimal(8,2) NOT NULL,
  PRIMARY KEY(order_num, order_item)
 ) ENGING=InnoDB;

关于引擎ENGING=

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

更新表定义，可用ALTER TABLE

给表添加一个列：

1
2
ALTER TABLE vendors
ADD vend_phone CHAR(20);

删除刚刚添加的列：

1
2
ALTER TABLE vendors
DROP COLUMN vend_phone;

更常见的用途是定义外键：

1
2
3
ALTER TABLE orderitems
ADD CONSTRAINT fk_orderitems_orders
FORGEIN KEY (order_num) REFERENCES orders (order_num);

22. 使用视图(complete)

定义：视图是虚拟的表，只包含使用时动态检索数据的查询。

视图的常见应用：

  • 重用SQL语句
  • 简化复杂的SQL操作。在编写查询后，可以方便地重用它而不必知道它的基本查询细节
  • 使用表的组成部分而不是整个表
  • 保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限
  • 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据

在视图创建之后，可以用与表基本相同的方式利用它们。可以对视图行SELECT操作，过滤和排序数据，将视图联结到其他视图或表，甚至能添加和更新数据

视图的规则和限制：

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

视图的创建：

  • 视图用CREATE VIEW语句来创建
  • 使用SHOW CREATE VIEW viewname:来查看创建视图的语句
  • 使用DROP删除视图时，其语法为DROP VIEW viewname;
  • 更新视图时，可以先用DROP再用CREATE，也可以直接用CREATE OR REPLACE VIEW。
1
2
3
4
5
CREATE VIEW productcustomers AS
SELECT cust_name, cust_contact, prod_id
FROM customers, orders, orderitems
WHERE customers.cust_id = orders.cust_id
  AND orderitems.order num = orders.order num

更新视图：
如果MySQL不能正确地确定被更新的基数据，则不允许更新。

视图提供了一种MySQL的SELECT语句层次的封装，可用来简化数据处理以及重新格式化基础数据或保护基础数据

23. 使用存储过程

为什么要使用存储过程

  • 通过把处理封装在容易使用的单元中，简化复杂的操作
  • 由于不要求反复建立一系列处理步骤，这保证了数据的完整性。
    如果所有开发人员和应用程序都使用同一（试验和测试）存储过程，则所使用的代码都是相同的。
    这一点的延伸就是防止错误。需要执行的步骤越多，出错的可能性就越大。防止错误保证了数据的一致性。
  • 简化对变动的管理。如果表名、列名或业务逻辑（或别的内容）有变化，只需要更改存储过程的代码。使用它的人员甚至不需要知道这些变化。
  • 提高性能。因为使用存储过程比使用单独的SQL语句要快。
  • 存在一些只能用在单个请求中的MySQL元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码

一个返回产品平均价格的存储过程

1
2
3
4
5
CREATE PROCEDURE productionpricing()
BEGIN
    SELECT Avg(prod_price) AS priceaverage
    FROM products;
END;

如果存储过程接受参数，它们将在()中列举出来。此存储过程没有参数，但后跟的()仍然需要。BEGIN和END语句用来限定存储过程体，过程体本身仅是一个简单的SELECT语句
CALL productpricing();执行刚创建的存储过程并显示返回的结果。

删除存储过程
DROP PRCEODURE productionpricing();、

使用参数
关键字OUT指出相应的参数用来从存储过程传出一个值（返回给调用者）。 MySQL支持IN（传递给存储过程）、OUT（从存储过程传出，如这里所用）和INOUT（对存储过程传入和传出）类型的参数。存储过程的代码位于BEGIN和END语句内，如前所见，它们是一系列SELECT语句，用来检索值，然后保存到相应的变量（通过指定INTO关键字）。

练习：

  • 获得合计
  • 把营业税添加到合计
  • 返回合计
1
2
3
4
5
6
7
8
9
10
CREATE PROCEDURE ordertotal(
    IN onumber INT,
    OUT ototal DECIMAL(8,2)
)
BEGIN
    SELECT Sum(item_pirce*quantity)
    FROM orderitems
    WHERE order_num = onumber
    INTO ototal;
END;

调用上面的存储过程：CALL ordertotal(20005, @total);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
-- Name: ordertotal
-- Parameters: onumber = order number
--			   taxable = 0 if not taxable, 1 if taxable
--             ototal  = order total variable
CREATE PROCEDURE ordertotal(
	IN onumber INT,
	IN taxable BOOLEAN,
	OUT ototal DECIMAL(8,2)
	)COMMENT 'Obtain order total, optionally adding tax'
BEGIN
	--Declare variable fot total
	DECLARE total DECIMAL(8,2);
	--Declare tax precentage
	DECLARE taxrate INT DEFAULT 6;
	--Get the order total
	SELECT Sum(item_price*quantity)
	FROM orderitems
	WHERE order_num = onumber
	INTO total;
	--Is this taxable?
	IF taxable THEN
		--Yes, so add taxrate to the total
		SELECT total+(total/100*taxrate) INTO total;
	END IF;
	--And finally, save to out variable
	SELECT total INTO ototal;
END;

24. 使用游标

游标(cursor)是一个存储在MySQL服务器上的数据库查询，它不是一条SELECT语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。
MySQL游标只能用于存储过程和函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
CREATE PROCEDURE processorders()
BEGIN
    --Declare local variables
    DELCERE o INT;
    --Delcare the CURSOR
    DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;
    
    --Open the cursor
    OPEN ordernumbers;
    
    --Get order number
    FETCH ordernumbers INTO o;
    
    --Close the cursor
    CLOSE ordernumbers;
END;

2017.6.24

25. 使用触发器

需求：想要某条语句在事件发生时自动执行。
创建触发器时，需要给出4条信息：

  • 唯一的触发器名
  • 触发器关联的表
  • 触发器应该相应的活动(DELETE、INSERT或UPDATE)
  • 触发器何时执行（处理之前或之后）
    ）
1
2
CREATE TRIGGER newproduct AFTER INSERT ON products
FOR EACH ROW SELECT 'Product added'

26. 管理事务处理(complete)

事务处理(transaction processing)可以用来维护数据库的完整性，它保证成批的MySQL要么完全执行，要么完全不执行。
ROLLBACK用来回退（撤销）MySQL语句

1
2
3
4
5
6
SELECT * FROM ordertotals;
START TRANSACTION;
DELETE FROM ordertotals;
SELECT * FROM ordertotals;
ROLLBACK;
SELECT * FROM orderstotals;

那些语句可以回退？
事务处理用来管理INSERT、UPDATE和DELETE语句。你不能回退SELECT语句。（这样做也没有什么意义。）你不能回退CREATE或DROP操作。事务处理块中可以使用这两条语句，但如果你执行回退，它们不会被撤销。

使用COMMIT

在事务处理模块中，提交不会隐含地进行。为了进行明确的提交，使用COMMIT语句，如下所示：

1
2
3
4
START TRANSACTION
DELETE FROM orderitems WHERE order_num = 20010;
DELETE FROM orders WHERE order_num = 20010;
COMMIT;

使用保留点：保留点指事务处理中的临时占位符(place-holder)，你可以对它发布回退

1
2
SAVEPOINT delete1;
ROOLBACK TO delete1;

保留点在事务处理完成(执行一条ROLLBACK或COMMIT)后自动释放

默认的MySQL自动提交所有修改，为指示MySQL不自动提交：SET autocommit=0;

27. 指定字符集和校对顺序

1
2
3
4
5
6
CREATE RABLE mytable
(
    column1    INT,
    column2    CARCHAR(10)
)DEFAULT CHARCTER SET hebrew
COLLATE hebrew_general_ci;

28. 安全管理

MySQL用户账号和信息存储在名为mysql的MySQL数据库中。

1
2
USE mysql;
SELECT user FROM user;

创建用户账号

1
CREATE USER ben IDENTIFIED BY 'password'

重命名一个用户账号：

1
RENAME USER ben TO btorta;

删除用户账号：

1
DROP USER btorta;

设置访问权限：

1
2
-- SHOW GRANTS FOR bforta;
GRANT SELECT ON crashcourse.* TO bforta;

此GRANT允许用户在crashcourse.*（ crashcourse数据库的所有表）上使用SELECT。通过只授予SELECT访问权限，用户bforta对crashcourse数据库中的所有数据具有只读访问权限。

GRANT的反操作为REVOKE,用它来撤销指定的权限。

1
REVOKE SELECT ON crashcourse.* TO bforta;

GRANT和REVOKE可在几个层次上控制访问权限：

  • 整个服务器，使用GRANT ALL和REVOKE ALL;
  • 整个数据库，使用ON database.*;
  • 特定的表，使用ON database.table;
  • 特定的列
  • 特定的存储过程

更改口令：

1
SET PASSWORD FOR bforta = Password('n3w p@$$w0rd');

不指定用户名时，SET PASSWORD更新当前登录用户的口令。

29. 数据库维护