msql 学习笔记整理

学习mysql的几天，将笔记整理一下

SQL（Structured Query Language）: 结构化查询语言。

数据库软件称为： DBMS（数据管理系统库）

DBMS可分为两类：

• 基于共享文件系统的DBMS。Microsoft Access、FileMaker
• 基于客户机——服务器的DBMS。MySQL、Oracle、Microsoft SQL Server

---

数据库（database）： 数据库是一个以某种有组织的方式存储的数据集合。通常是一个文件或一组文件。

表（table）: 是一种结构化的文件，可用来存储某种特定类型的数据。数据库中的每个表都有一个名字，用来标识自己，此名字是唯一的。

列（column）： 表中的一个字段，所有表都是由一个或多个列组成的。

分解数据： 正确的将数据分解成多个列极为重要，通过把他们分解开，才有可能利用特定的列对数据进行排序和过滤。

数据类型（datatype）： 数据类型限制可存储在列中的数据类型。

行（row）：表中的一条记录。

主键（primary key）：

外键（foreign key）：

自增量（auto_increment）：

默认值（default）：

描述（comment）：

存储引擎

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

注意：外键不能跨引擎。

---

MySQL是一种DBMS，即一种数据库管理软件。

MySQL的特点：

1. 可伸缩性（scale）：

====================================================
show database;
show tables;
show colunms from table_name;
show status; 用于显示广泛是服务器状态信息
show create database / show create table; 显示创建特定数据库或表的SQL语句
show grants; 从来显示授权用户（所有用户或特定用户）的安全权限
show errors / show warnings; 显示服务器错误或警告的信息

help show 来获取更多的命令

检索数据

select [distinct] column_name, .... from table_name;
distinct： 只返回不同的值（该关键字应用于所有列）

使用通配符：
select * from table_name;

检索返回指定的条数：
limit num;
从指定位置开始检索指定的条数：
limit start_index, num; （index起始值为0）

limit num offset start_index;

注意：有的时候需要使用完全限定名：
db_name.table_name
table_name.column_name

排序：

按单个列排序：
order by column_name [DESC|ASC];
按多个列排序：(每个列都要指明排序的方向)
order by column_name1 [DESC|ASC], column_name2 [DESC|ASC], ...;
在按多个列排序时，排序完全按所规定的顺序进行。（仅有在多个行具有相同的column_name1时，才会按column_name2排序。如果column_name1中的所有值都是唯一的，则不会按column_name2排序）

默认的排序方向为升序（ASC）:
ASC ===> A-->Z
DESC===> Z-->A

SELECT子句的顺序：

• select
• from
• where
• group by
• having
• order by
• limit

过滤数据

使用 WHERE 子句
MySQL在执行匹配时，默认不区分大小写。
使用单引号''限定字符串，

where子句的操作符：
= 等于
<> 不等于
!= 不等于
< 小于
<= 小于等于
> 大于
>= 大于等于
BETWEEN 在指定的两个值之间

BETWEEN start_value AND end_value; （包括 start_value和end_value）

空值检查:
WHERE column_name IS NULL;

组合 WHERE 子句：

• AND 操作符，可以添加多个过滤条件，使用 AND 连接。

• OR 操作符
  注意：（操作时使用圆括号明确地分组操作符。）

• IN (value1, value2) 指定条件的范围
  注意：IN 操作符 与 OR 操作符功能相同，可以包含其他的 SELECT 语句，能够更动态地建立WHERE子句。

• NOT 操作符
  用来否定后跟的条件。

注意：MYSQL中支持使用 MOT 对IN、BETWEEN、EXISTS子句取反的操作

用通配符进行过滤：

LIKE 子句， 后跟匹配的条件

• 百分号（%）通配符：表示任意字符出现任意次数。
  'jre%' ---- 以 jre 开头
  '%jar%'--- 包含 jar

• 下划线（_）通配符：只匹配单个字符。

注意： 通配符的搜索一般要比其他搜索所花的时间更长。

1. 不要过度使用通配符，如果其他的操作符能达到相同的目的，应该使用其他的操作符。
2. 除非绝对有必要，否则不要把他们用在搜索模式的开始处，搜索起来是最慢的。
3. 注意使用通配符所在位置。

WHERE子句内使用正则表达式进行搜索：

REGEXP子句，后跟匹配的条件。默认匹配不区分大小写，
可使用REGEXP BINARY 子句来是的区分大小写。

'.' ：表示匹配任意一个字符。'.name'
'|'： 正则表达式中的OR操作符。'a|b'-----匹配a或b
'[]'：匹配任何单一字符，也是OR操作符飞另一种形式。[abc]---匹配a或b或c
'[^abc]'----匹配除a、b、c以外的字符。
'[0-9]'：匹配0~9.
'[a-z]'
'\\.'：表示查找 '.'

计算字段

拼接字段：
在MySQL中 Concat() 函数用来拼接字段串，即拼接在一起显示特定的信息。
多个字段用,隔开。
RTirm()函数，删除数据右侧多余的空格。
LTirm()函数，删除数据左侧多余的空格。

---

使用别名：
AS alias_name;

SELECT Concat(RTrim(vend_nmae), '(', RTrim(vend_country), ')') AS wend_title FROM vendors ORDER BY vend_name;

列别名：

表别名：表别名只在查询中使用，表别名不返回到客户机。

---

执行算术计算：
+
-
*
/

SELECT prod_id, quantity, item_price, 
quantity*item_price AS expanded_price
FROM orderitems
WHERE order_num = 20005;

数据分组

group by

SELECT vend_id, COUNT(*) AS num_prods
FROM products
GROUP BY vend_id;

过滤分组：
having

having 支持所有 where操作。

SELECT cust_id, COUNT(*) AS orders
FROM orders
GROUP BY cust_id
HAVING COUNT(*) >= 2;

---

having 和 where 的区别：

• where 过滤行，having过滤分组；
• where 在数据分组前进行过滤，having在数据分组后进行过滤；
• where排除的行不包括在分组中。

子查询

子查询：嵌套在其他查询语句中的查询

联结

foreign key
外键：为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系。

---

联结：
是一种机制，用来在一条 SELECT 语句中关联表。
使用特殊语法，可以联结多个表返回一组输出。联结在运行时关联表中正确的行。

联结在实际的数据库表中并不存在，联结由MySQL根据需要建立，它存在于查询的执行当中。

SELECT vend_name, prod_name, prod_price
FROM vendors, products
WHERE vendors.vend_id = products.vend_id
ORDER BY vend_name, prod_name;

注意：

• 在联结中的WHERE子句是很关键的，应该保证所有联结都有WHERE子句，否则MySQL将返回比想要的数据多的多的数据。
• 还应保证WHERE子句的正确性，不正确的过滤条件将导致mysql返回不正确的数据。

---

内部联结（最常用的形式）：

到目前为止，所有的联结都称为等值联结，它基于两个表之间的相等测试，这种联结也称为内部联结。

SELECT vend_name, prod_name, prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id;

这样的写法与上边的写法的结果是等同的，

---

联结多个表：

SQL对于一条SELECT语句中可以联结的表的数目没有限制。
创建的规则也基本相同，首先列出所有表，然后定义表之间的关系。

SELECT prod_name, vend_name, prod_price, quantity 
FROM orderitems, vendors, products 
WHERE products.vend_id = vendors.vend_id 
AND orderitems.prod_id = products.prod_id
AND order_num = 20005;

自联结：

用一个问题来说明

SELECT prod_id, prod_name
FROM products 
WHERE vend_id = (SELECT vend_id FROM products WHERE prod_id = 'DTNTR');

下面使用自联结将达到相同的效果：

SELEC p1.prod_id, p1.prod_name
FROM products AS p1, products AS p2
WHERE p1.vend_id = p2.vend_id
AND p2.prod_id = 'DTNTR';

自联结通常作为外部语句用来代替从相同表中检索数据时使用的子查询语句。

自然联结：

外部联结：

许多联结将一个表中的行与另一个表中的行相关联。但有时候会需要包含没有关联行的那些行。这种类型的联结称为外部联结。

LEFT|RIGHT OUTER JOIN 表示从左边表或右边表中选择所有行。

组合查询

多数SQL查询都只包含从一个或多个表中返回数据的单条SELECT语句。
MySQL也允许执行多个查询（多条SELECT语句），并将结果作为单个查询结果集返回。

有两种情况需要使用组合查询：

1. 在单个查询中从不同的表返回类似结构的数据。
2. 对单个表执行多个查询，按单个查询返回数据。

UNION

1. 必须由两条或两条以上的SELECT语句组成，SELECT语句之间用UNION关键字分割。
2. UNION中的每个查询必须包含相同的列，表达式或聚集函数（各个列不需要以相同的次序列出）。
3. 列数据类型必须兼容，类型不必完全相同，但必须是DBMS可以隐含转换的类型（例如不用的数值类型或不同的日期类型）。

---

消掉重复的行：

UNION 默认返回结果会消掉重复的行；
UNION ALL 则不消掉重复的行。

---

对组合查询结果进行排序：

SELECT语句的输出用order by 语句排序，再用 UNION 组合查询时，只能使用一条 order by子句，
它必须出现在最后一条SELECT语句之后。

---

可以组合不同的表

插入数据

再插入数据时需要注意的问题：

1. 一般使用明确给出列的列表的insert语句。即使表结构发生改变，也可以正确插入。
2. 省略某些列：
   省略列必须满足以下的条件：
   该列定义为允许NULL值（无值或空值）。
   在表的定义中给出默认值。

不满足上述的条件则会产生一条错误的消息，并且相应的行插入不成功。

3. 使用 insert low_priority into 语句可以指示mysql降低insert语句的优先级。
   因为insert语句执行很耗时，这样可以提高性能。
   这也适用于update和delete语句。

4. 插入检索出的数据

insert into table_name(column_name1, column_name2,...) 
values(value1, value2...)
select column_nam1, column_name2,... from table_name;

更新语句

update table_name set colunm_name1=value1, column_name2=value2,... where 过滤条件;

如果用 update 更新语句更新多行，并且在更新这些行找中的一行或者多行出现错误，则整个的update操作将被取消。
为了即使是发生了错误，也继续进行更新，可使用 ignore 关键字。
update ignore table_name set....;

删除语句

delete from table_name where 过滤条件;

1. delete语句删除表中的行，甚至是所有的行，但是不删除表本身。
2. 若想要更快的删除表中的所有数据，可使用：
   truncate table 语句，（实际是删除了原来的表并重新创建了一个表，而不是逐行删除表中的数据）。

对于表的操作

创建表：
create table [IF NOT EXISTS] table_name();

更新表：
alert table table_name 要操作的语句;

添加一个字段
ALERT TABLE vendors ADD vend_phone char(20);

删除一个字段
ALERT TABLE vendors DROP COLUMN vend_phone;

alert的一种常见操作是定义外键。
ALERT TABLE table_name ADD CONSTRAINT fk_tableName1_tableName2 FOREIGN KEY (column_name) REFERENCES table_name(column_name);

删除表：
drop table table_name;

重命名表：
rename table old_table_name to new_table_name;

全文搜索

MyISAM引擎支持全文搜索。

在使用全文本搜索时，MySQL不需要分别查看每个行，不需要分别分析和处理每个词，MySQL创建指定列中个词的一个索引，搜索可以针对这些词进行。这样，MySQL就可以快速有效地决定哪些词匹配（哪些行包含他们），哪些词不匹配 ，他们的匹配频率，等等。

使用全文本搜索：

为了进行全文本搜索，必须索引被搜索的列，而且要随着数据的改变不断地重新索引，在对表列进行适当的设计后，MySQL会自动进行所有的索引和重新索引。

启用全文搜索支持（创建索引）：

一般在创建表时启用全文本搜索，

CREATE TABLE productnotes(
  note_id INT NOT NULL AUTO_INCREMENT,
  prod_id VARCHAR(10) NOT NULL,
  note_date DATETIME NOT NULL,
  note_text TEXT NULL,
  PRIMARY KEY (note_id),
  FULLTEXT (note_text)   // 多个索引列用逗号隔开
) ENGINE = MyISAM;  

在定义之后，MySQL会自动的维护该索引，在增加、更新、修改行时，索引随之自动更新。

---

进行全文搜索：

在索引之后，使用两个函数 Match() 和 Against() 执行全文搜索。
Match()--指定被搜索的列
Against()--指定要使用的搜索表达式（要搜索的东西）

SELECT note_text FROM productnotes
WHERE Match(note_text) Against('rabbit');

注意：传递给Match()的值必须与 FULL TEXT() 定义中的相同，如果指定多个列，则必须列出他们（而且次序正确）。
搜索不区分大小写，除非使用BINARY方式。

全文本搜搜的一个重要的部分就是会对搜索结果进行排，具有较高等级的先返回。
（两个行包含同样的词，但包含该词作为第3个词的行的等级比作为第20个词的行高）

---

使用查询扩展：

查询扩展用来设法放宽所返回的全文本搜索结果的范围。（返回相关的数据，也就是数有联系的）

SELECT note_text FROM productnotes
WHERE Match(note_text) Against('rabbit' WITH QUERY EXPANSION );

---

布尔文本搜索：

布尔文本搜索是MySQL支持全文本搜索的另外一种形式。

布尔文本搜索可以提供关于如下内容的细节：

1. 要匹配的词
2. 要排斥的词（某行包含该词将不返回）
3. 排列提示（指定某些词比其他词更重要，重要的词等级更高）
4. 表达式分组
5. 另外的一些内容

SELECT note_text FROM productnotes
WHERE Match(note_text) Against('rabbit' IN BOOLEAN MODE );

匹配包含 heavy 但不包含任意以 rope 开头的词的行，如下：

SELECT note_text FROM productnotes
WHERE Match(note_text) Against('heavy -rope*' IN BOOLEAN MODE );

---

全文本布尔搜索操作符：

+ ：包含，词必须存在
-：排除，词必须不出现
> ：包含，且增加等级值
<：包含，且减小等级值
() ：用于组成子表达式
~ ：取消一个词的排序值
*：
""：定义一个短语

视图

MySQL5 增加了对视图的支持。

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

视图的一些常见应用：

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

在视图创建之后，可以用域表基本相同的操作方式来来操作它。
可以对视图执行select操作，过滤、排序、联结到其他视图或表、添加数据、更新数据（添加和更新数据存在某些限制）。

---

视图的规则和限制：

1. 视图必须唯一命名。
2. 对于可以创建的视图数目没有限制。
3. 为了创建视图，必须具有足够的访问权限。通常由数据库管理人员授予。
4. 视图可以嵌套。即可以使用视图中的检索查询来构造一个视图。
5. order by 可以用在视图中，但如果从该视图中检索数据的select中也含有order by，
   那么该视图中的order by 将被覆盖。
6. 视图不能索引，也不能有关联的触发器和默认值。
7. 视图可以和表一起使用。比如和表联结。

---

创建视图：
create view as select语句

查看创建的视图：
show create view view_name;

删除视图：
drop view view_name;

更新视图：

1. 先使用drop，在用create
2. 直接使用 create or replace view
   如果要更新的视图不存在，该语句会创建一个视图；
   如果要更新的视图存在，该语句会替换原有的视图。

视图是可更新的（insert、update、delete），更新一个是将更新其基表。

并非所有的视图都是可更新的，如果视图定义中有如下的操作，则不能进行视图的更新：

1. 分组（使用group by或 having）
2. 联结
3. 子查询
4. 并
5. 聚集函数
6. distinct
7. 导出（计算）列

注意：一般应该将视图用于检索，而不是更新。

存储过程

MySQL5增加了对存储过程的支持。

存储过程：为以后的使用而保存的一条或多条MySQL语句的集合。

为什么要使用存储过程：

1. 通过把处理封装在容易使用的单元中，简化复杂的操作。
2. 不再反复建立一系列的处理步骤，保证了数据的完整性。
   比如，开发人员和应用程序都使用同一存储过程，则所使用的代码都是相同的。这样就保证数据的一致性，不容易出错。
3. 简化对变动的管理。如表名、列名、或业务逻辑等有变化，只需改动存储过程的代码。
4. 提高性能。
5. 存储过程可以用来编写功能更强更灵活的代码。

创建存储过程需要访问权限，这通常是由数据库管理员赋予。

---

创建存储过程：

create procedure procedure_name([参数1, 参数2, ...])
begin
    存储过程执行代码（过程体）
end;

使用存储过程：

call procedure_name([@参数1, @参数2, ...]);

删除存储过程：

drop procedure procedure_name;

仅当存在时删除，不存在会产生错误，可使用：

drop procedure if exists procedure_name;

---

参数：

一般存储过程并不返回结果，而是把结果返回给你指定的变量。

CREATE PROCEDURE productpricing(
  OUT p1 DECIMAL(8, 2),
  OUT p2 DECIMAL(8, 2),
  OUT p2 DECIMAL(8, 2)
)
  BEGIN
    SELECT min(prod_price)
      INTO p1
    FROM products;
    SELECT max(prod_price)
      INTO p2
    FROM products;
    SELECT avg(prod_price)
      INTO p3
    FROM products;
  END;

该存储过程接受了三个参数，每个参数必须具有指定的类型
IN传递给存储过程
OUT 从存储工程传出
INOUT对存储过程传入和传出

INTO 保存到相应的变量

注意：不能通过一个参数返回多个行和列。

使用该存储过程：

CALL productpricing(@pricelow, @pricehigh, @priceaverage);

显示该存储过程的结果：

SELECT @pricelow, @pricehigh, @priceaverage;

CREATE PROCEDURE ordertotal(
  IN onumber INT,
  OUT ototal DECIMAL(8,2)
)
  BEGIN
    SELECT sum(item_price*quantity)
      FROM orderitems
        WHERE order_num = onumber
    INTO ototal;
  END;

CALL ordertotal(20005, @total);

SELECT @total;

局部变量：

在过程体中定义局部变量（BEGIN--END之间），使用：

DECLARE taxrate INT DEFAULT 6;

局部变量要指定变量名和数据类型。

CREATE PROCEDURE ordertotal(
  IN onumber INT,
  IN taxable BOOLEAN,
  OUT ototal DECIMAL(8,2)
)
  BEGIN
    -- 创建局部变量
    DECLARE total DECIMAL(8, 2);
    DECLARE taxrate INT DEFAULT 6;

    SELECT sum(item_price*quantity)
      FROM orderitems
        WHERE order_num = onumber
    INTO total;
    
    -- ELSEIF | ELSE
    IF taxable THEN
      SELECT total+(total/100*taxrate) INTO total;
    END IF;

    SELECT total INTO ototal;
  END;
-- 0为假，非0为真
CALL ordertotal(20005, 1, @total);

SELECT @total;

检查存储过程：

显示创建存储过程的create语句：

show create procedure procedure_name;

获得详细的存储信息列表：

show procedure status;

游标

MySQL5添加了对游标的支持。

MySQL检索操作返回一组称为结果集的行。返回的行都是与sql语句相匹配的行（零行或多行）。有的时候是需要在检索出来的行中前进或后退一行或多行，使用简单的select语句是没有办法得到的。这就是使用游标的原因了。

游标（cursor）：是一个存储在mysql服务器上的数据库查询，它不是一条select语句。而是被该语句检索出来的结果集。
在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。

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

MySQL游标只能用于存储工程（和函数）。

---

使用游标的步骤：

1. 在使用游标前必须声明（定义）它，这个过程实际上没有检索数据，它只是定义要使用的select语句。
2. 一旦声明后，必须打开游标以供使用。这个过程要用到前面定义的select语句把数据实际检索出来。
3. 对于填有数据的游标，根据需要取出（检索）各行。
4. 在结束游标使用时，必须关闭游标。

在声明游标后可根据需要频繁地打开和关闭游标。在游标打开后，可根据需要频繁地执行取操作。

---

创建游标：

游标使用 DECLARE，DECLARE命名游标，并定义相应的select 语句。

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

存储过程处理完后，游标就消失（因为它局限于存储过程）。

打开和关闭游标：

在定义了游标之后就可以打开它。

OPEN ordernumbers;

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

关闭：

CLOSE ordernumbers;

close释放游标使用的所有内部内存和资源，每个游标在不需要时都应该关闭。

游标关闭后，没有重新打开是不能使用的。
使用过的游标不需要再次声明，使用open打开就可以了。
如果没有明确的关闭游标（CLOSE），mysql会在 END 时自动关闭它。

CREATE PROCEDURE processorders()
  BEGIN
    DECLARE ordernumbers CURSOR FOR
      SELECT order_name FROM orders;
    
    OPEN ordernumbers;
    
    相应的处理操作
    
    CLOSE ordernumbers;
  END;

使用游标数据：

使用 FETCH 可以访问它的每一行：

CREATE PROCEDURE processorders()
  BEGIN
    DECLARE o INT;

    DECLARE ordernumbers CURSOR FOR
      SELECT order_name FROM orders;

    OPEN ordernumbers;

    FETCH ordernumbers INTO o;

    CLOSE ordernumbers;
  END;

该例只访问的是第一行数据。

---

CREATE PROCEDURE processorders()
  BEGIN
    DECLARE done BOOLEAN DEFAULT 0;
    DECLARE o INT;

    DECLARE ordernumbers CURSOR FOR
      SELECT order_name FROM orders;
    
    -- SQLSTATE '02000' 是一个未找到条件
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;

    OPEN ordernumbers;

    REPEAT
      FETCH ordernumbers INTO o;
    UNTIL done END REPEAT;

    CLOSE ordernumbers;
  END;

循环检索数据，从第一行到最后一行。

---

CREATE PROCEDURE processorders()
  BEGIN
    DECLARE done BOOLEAN DEFAULT 0;
    DECLARE o INT;
    DECLARE t DECIMAL(8, 2);

    DECLARE ordernumbers CURSOR FOR
      SELECT order_name FROM orders;

    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;

    DROP TABLE IF EXISTS ordertotals;

    CREATE TABLE IF NOT EXISTS ordertotals(
      order_num INT,
      total DECIMAL(8, 2)
    );

    OPEN ordernumbers;

    REPEAT

      FETCH ordernumbers INTO o;
      CALL ordertotal(o, 1, t);
      INSERT INTO ordertotals(order_num, total) VALUES (o, t);

    UNTIL done END REPEAT;

    CLOSE ordernumbers;
  END;

SELECT * FROM ordertotals;

触发器

MySQL5增加了对触发器的支持。

想要某条语句（或某些语句）在事件发生时自动执行，这时就用到了触发器。

触发器：是MySQL响应以下任意一条语句而自动执行的一条SQL语句（或位于BEGIN 和 END语句之间的一组语句 ）。

1. delete
2. update
3. insert

其他的语句不支持触发器。

注意：只有表支持触发器，视图不支持（临时表也不支持）。

---

创建触发器：

在创建触发器时的 4 部分：

1. 唯一的触发器名。（在表中唯一）建议：在数据库中使触发器名唯一。
2. 触发器关联的表。
3. 触发器应该响应的事件（delete、update、insert）。
4. 触发器何时执行after|before（处理之前或之后）。

CREATE TRIGGER newproduct   // 创建名为newproduct的触发器
AFTER INSERT ON products    // 在insert语句成功执行后执行
FOR EACH ROW    // 对每个插入行执行
  BEGIN
    SELECT 'Product added' INTO @o; // 触发器执行的动作
  END;
  
SELECT @o; // 在插入之后查看该信息

每个表每个事件每次只允许有一个触发器，因此，每个表最多支持6个触发器（每条insert、update、delete的之前和之后）。

---

删除触发器：

DROP TRIGGER newproduct;

触发器不能更新或覆盖，为了修改一个触发器，必须先删除它，然后再重新创建。

使用触发器：

---

事务处理

事务处理（transaction processing）：可以用来维护数据库的完整性，它保证成批的MySQL操作要么完全执行，要么完全不执行。

事务处理是一种机制。

在事务处理中的几个术语：
事务（transaction）：一组SQL语句。
回退（rollback）：撤销指定SQL语句的过程。
提交（commit）：将未存储的SQL语句结果写入到数据库。
保留点（savepoint）：事务处理中设置的临时占位符（placeholder），你可以对它发布回退（与回退整个事务处理不同，是部分的）。

---

控制事务处理：

管理事务处理的关键在于将SQL语句组分解为逻辑块，并明确规定数据何时应该回退，何时不应该回退。

标识事务开始：

START TRANSACTION;

---

使用回滚（rollback）：

SELECT * FROM ordertotals;
START TRANSACTION;
DELETE FROM ordertotals;
SELECT * FROM ordertotals;
ROLLBACK ;
SELECT * FROM ordertotals;

rollback只能在一个事务处理内使用（在执行一条start transaction语句之后）。

注意：事务处理用来管理insert、update、delete，不能回退select语句。
也不能回退create或drop操作，在事务处理块中可以有这两条语句，但如果你执行回退，他们不会被撤销。

---

使用提交（commit）：

在事务处理块中，提交不是自动进行的，需要使用 commit 明确地提交。

START TRANSACTION ;
DELETE FROM orderitems WHERE order_num = 20010;
DELETE FROM orders WHERE order_num = 20010;
COMMIT ;

注意：当commit 或 rollback 语句执行后，事务会自动关闭。

---

使用保留点（标记）：

为了支持回退部分事务处理，必须要在事务处理块中合适的位置放置占位符（保留点），这样，如果需要回退，可以回退到某个占位符。

创建占位符：

SAVEPOINT delete1;
-- 回滚到保留点
ROLLBACK TO delete1;

保留点标识名要唯一。以便在回退时知道在何处。

释放保留点：

1. 在执行一条rollback 或 commit后自定释放保留点。
2. MySQL5以后可以使用 RELEASE SAVEPOINT delete1;明确地释放保留点。

---

修改MySQL默认的提交行为：

SET autocommit=0; // 不自动提交

注意：autocommit标志是针对每个连接的，而不是服务器。（标志为连接专用）

MySQL的全球化和本地化

在MySQL的正常的数据库活动中，不需要操心太多的东西，使用何种字符局和校对，决定在服务器、数据库和表级进行。

MySQL支持众多的字符局。

SHOW CHARACTER SET

显示所有可用的字符集以及每个字符集的描述和默认校对。

SHOW COLLATION

显示所有可用的校对，以及他们适用的字符集。
_cs：表示区分大小写。
_ci：表示不区分大小写。

---

对整个表指定：

CREATE TABLE mytable(
  column1 INT,
  column2 VARCHAR(20)
) DEFAULT CHARACTER SET hebrew COLLATE hebrew_general_ci;

指定了一个字符集和一个校对顺序。

---

对某个列指定：

CREATE TABLE mytable(
  column1 INT,
  column2 VARCHAR(20),
  column3 VARCHAR(10) CHARACTER SET latinl COLLATE latinl_general_ci
) DEFAULT CHARACTER SET hebrew COLLATE hebrew_general_ci;

安全管理

访问控制：管理访问控制需要创建和管理用户账号。并赋予相应的权限。

注意：在日常的工作中，决不能使用root，应该创建一系列 的账号来使用，有的用于管理，有的供用户使用，有的用于开发者使用。

---

管理用户：

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

USE mysql;
SELECT user FROM user;

创建用户账号：

CREATE USER user_name IDENTIFIED BY 'password';

重新命名用户账号：（MySQL5支持）

RENAME USER old_name TO new_name;

删除用户账号：（删除用户及相关权限）

DROP USER user_name;

在MySQL5之前只能使用update来更新。
在5版本之前 DROP USER只能用来删除用户，不能删除相关权限，需先使用 REVOKE 来删除相关的权限，在删除用户。

更改指定用户的口令：

SET PASSWORD FOR user_name = Password('password');

新口令必须传递到Password()函数进行加密。

更改当前用户的口令：

SET PASSWORD = Password('password');

---

设置访问权限：

在创建用户账号之后，接着给该账号分配权限，否则登陆上mysql什么也干不了。

查看用户所拥有的权限：

SHOW GRANTS FOR user_name;

设置权限：

GRANT SELECT ON db_name.* TO user_name;

这样就赋予了用户对该数据库所有表的select权限。

GRANT要求至少要给出以下信息：

1. 要授予的权限。
2. 被授予访问权限的数据库或表。
3. 用户名。

撤销特定的权限：

REVOKE SELECT ON db_name.* FROM user_name;

本撤销的访问权限必须存在，否则会出错。

多个授权：（用逗号隔开）

GRANT SELECT, INSERT ON db_name.* TO user_name;

---

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

1. 整个服务器，使用GRANT ALL 和 REVOKE ALL
2. 整个数据库，使用 ON databaseName.*
3. 特定的表，使用 ON databaseName.tableName
4. 特定的列
5. 特定的存储过程

---

MySQL的权限表：