MySQL必知必会 19章后

Group By子句列出的每个列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式，不能使用别名。

除聚集计算语句外，SELECT语句中的每个列都必须在GroupBy子句中给出

Insert中如果不提供列表，则必须给每个表列提供一个值。如果提供列名，则给出这些列的值，这些列可以与表中顺序不同，只要后面的值与列对应就行。

INSERT可以一次插入多个数据：

INSERT INTO customers(cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country)  VALUES('Pep E. LaPew', '100 Main Street', 'Lod Angeles', 'CA', '90046', 'USA'),('M. Martian', '42 Galaxy Way', 'New York', 'NY', '11213', 'USA');

插入的数据用逗号分开

insert select语句可以将一个表中的数据插入另一个表中，主键值不能重复

insert into customers(cust_id, cust_contact, cust_email, cust_name, cust_address) select cust_id, cust_contact, cust_email, cust_name, cust_address from custnew;

select中可以不使用列名，因为mysql的插入方式只是将后面的表的第一列插入前面表的第一列，第二列插入第二列。。。select子句可包含where子句过滤数据。

如果update与delete没有where子句，则更新所有的行

update语句更新多行时，如果出现一个或多个错误，则整个操作取消，恢复为原来的值，如果加入ingore关键字，则即使发生错误也继续更新（update ignore customers...）

DELETE：删除整行，或者删除所有行，但不删除表本身。如果想从表中删除所有行，不需要使用delete,可以使用truncate table，它完成相同的工作，但更快（实际上是删除原来的表并重写创建一个表，而不是逐行删除表中的数据）

AUTO_INCREMENT：每个表只运行有一个，且它必须被索引（如成为主键）

SELECT last_insert_id()函数返回最后一个AUTO_INCREMENT值，可以将它用作后续的MySQL语句。

memory引擎功能等同于myisam, 但数据存储在内存，不是磁盘，速度快，一般用来创建临时表。外键不能跨引擎

ALTER TABLE更改表结构

增加列：

删除列
alter table union_virus drop column is_virus;
增加列
alter table union_virus add column is_virus boolean;
更改列名
ALTER TABLE union_virus change column is_virus(原名) isvirus int（新的列名与类型）;
修改表名
ALTER TABLE union_normal RENAME TO union_all_normal;

增加外键

alter table orderitems add constraint fk_orderitems_orders foreign key(order_num) references orders(order_num);

DROP TABLE 删除表

RENAME TABLE customers2 TO customers:重命名表

视图：主要用来查询

是虚拟的表，不包含任何数据，包含的是一个SQL查询。一般地，视图应用于检索（SELECT）而不用于更新（INSERT，UPDATE和DELETE）。视图提供了一种MySQL的SELECT语句层次的封装，可用来简化数据处理以及重新格式化基础数据或保护基础数据。

视图可以嵌套。即可以利用从其他视图中检索出的数据的查询来构造视图。

order by可以用在视图中，但如果从该视图检索数据的select语句也含有order by,则视图的被覆盖。

视图不能索引，也不能有触发器或默认值，可以和表一起使用。

create view view_name as select... 创建视图

show create view viewname; 查看视图创建语句

drop view viewname;删除视图

更新视图时，可以先drop再create, 也可以直接用create or replace view,如果更新的视图不存在，则会创建一个视图，如果更新的视图存在，则更新语句会替换原有视图。

视图是可更新的，即可以用insert ,update, delete,更新视图会直接更新原表。但更新视图有很多限制，如果视图中定义了“分组”， “连结”， “子查询”， “并”， “聚集函数”，“distinct”, "导出列"的操作，则不可更新，所以呀。一般都不可以更新，而且视图的作用是查询而不是更新。

1 用视图简化复杂的联结，即简化复杂的SQL，这一般涉及到联结。

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;

创建一个名为productcustomers的视图，联结了三个表，返回已订购了任意产品的所有客户的列表，SELECT * FROM productcustomers将返回订购了任意产品的客户。

2 重新格式化检索出的数据

CREATE VIEW vendorlocations AS SELECT Concat(RTrim(vend_name), '(', RTrim
(vend_country), ')') AS vend_title FROM vendors ORDER BY vend_name;

3 过滤不想要的数据  4 使用视图与计算字段

存储过程：

CREATE PROCEDURE productpricing() 

BEGIN

       SELECT  Avg(prod_price) AS priceaverage FROM products;

END;

如果要在命令行内写，则如果要使存储过程的；正确使用，则首先临时更改分隔符DELIMITER //

在存储过程最后标明//，完成后恢复DELIMITER ;

CALL productpricing();   //调用存储过程

DROP PROCEDURE productpricing; //删除存储过程

如果不存在，则产生错误，可以写成

DROP PROCEDURE productpricing 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//

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

SELECT @pricehigh, @pricelow, @priceaverage;//查看三个值

CREATE PROCEDURE ordertotal(
   IN onumber INT,
   IN taxable BOOLEAN,
   OUT ototal DECIMAL(8, 2)
)
BEGIN
   -- Declare variable for total
   DECLARE total DECIMAL(8, 2);
   -- Declare tax percentage
   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
      SELECT total + (total/100 * taxable) INTO ototal;
   END IF;
      SELECT total INTO ototal;
END//      

CALL PROCEDURE ordertotal(20005, 0, @total);
SELECT @total;

CALL PROCEDURE ordertotal(20005, 1, @total);
SELECT @total;

使用游标：是存储在MySQL服务器上的数据库查询，只能用于存储过程。

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

打开游标：Open ordernumbers;

关闭游标：CLOSE ordernumbers;

触发器：delete, insert ,update,触发器有after,before，所以一个表最多支持6个触发器。触发器在每个表中的名字必须唯一，不同的表可以使用同一个名字。只有表才支持触发器，视图和临时表都不支持。如果before触发器失败，mysql则不执行请求的操作，如果before触发器或语句本身失败，则不执行after触发器。before触发器通用于数据验证和净化。触发器意味着表在执行以上任意语句时，自动执行一条mysql语句或一组语句（在begin, end中定义），即定义触发器时定义执行的语句或语句组。

创建触发器：

create trigger triname before/after insert/delete/update on table for each row begin...end;

删除触发器：drop trigger triname;

insert触发器：

insert触发器代码内，可引用一个名为NEW的虚拟表，访问被插入的行；

在before insert触发器中，NEW中值可以被更新

对于auto_increment列，NEW中对应的列在insert之前是0，在insert之后包含新的自动生成值。

delete触发器：

在delete触发器内，可以引用一个名为OLD的虚拟表，访问被删除的行；

OLD中的值都是只读的，不能更新

下面的列子：使用old保存将要被删除的行到一个存档表中

 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;

update触发器：

在update代码中，可以引用一个名为OLD的虚拟表访问以前的值，引用一个名为NEW的虚拟表访问更新的值。

在before update触发器中，new的值也可以被更新

old的值全部是只读的，不能更新

例子：保证州名缩写总是大写

create trigger updatevendor before update on vendors for each row 

set NEW.vend_state = Upper(NEW.vend_state)

insert:new   delete: old     insert :old, new

new虚拟表可更新，old虚拟表不可更新

事务：

事务（transaction）:指一组SQL语句；

回退（rollback）:撤销指定的SQL语句

提交（commit）：指将未存储的SQL语句结果写入数据库表

保留点（savepoint）:指事务处理中设置的临时占位符，可以回退到该保留点

事务开始：start transaction;

回退：rollback;   回退start transaction之后的所有语句，只能在一个事务处理内使用，可以回退insert, uodate,delete,不能回退select, create, drop语句。

例如：

SELECT * from orderitems;

START TRANSACTION;

DELETE FROM orderitems;

SELECT * from orderitems;

ROLLBACK;    //回退到事务开始，所以删除表的操作被回退

SELECT * from orderitems;  

mysql中是隐含提交，执行为一条语句后，会马上提交，是自动进行的，所作的更改立即生效，如果要更改，可以set autocommit = 0; 关闭自动提交。autocommit是针对每个连接而不是服务器。

事务中，提交不会隐式进行，为提交，必须明确使用commit语句。执行之后或rollback之后有回复自动提交。

部分回退：

设置保留点: SAVEPOINT delete1;

回退到保留点：ROLLBACK TO delete1;

保留点在事务处理完成后自动释放，也可以使用release savepoint显示释放。

安全管理：

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

查看所有用户：

use mysql;

select user from user;

创建用户帐号：create user ben identified by 'passwd'; //不一定需要口令

指定的口令保存到mysql中时会加密。grant也可以创建用户

GRANT ALL PRIVILEGES ON crash.* TO xlf@localhost identified by "mima" with grant option;

 

上面这句会增加一个新用户，并直接授权限。如果采用create user创建，则新用户没有任何权限，需要授权

重命名用户：rename user ben to bforta;

删除用户帐号：drop user bforta;

设置访问权限：

显示访问权限：show grants for bforta;   //USAGE ON *.*表示没有权限，

授权：grant select on crash.* to bforta;  //授予bforta在crash数据库上的select权限

回收权限：revoke select on crash.* from bforta;   //将select权限回收

整个服务器：GRANT ALL, REVOKE ALL

整个数据库：ON database.*

特定的表：ON database.table

更改口令：SET PASSWORD for bforta = Password('yayaya');  //使用password进行加密

设置自己的口令：SET PASSWORD = Password('yayaya');

数据库维护：

备份：mysqldump, mysqlhotcopy, backup table或select into outfile,数据可以使用restore table来复原，

为了保证所有数据被写到磁盘，应该在备份前输入flush table语句

analyze table orders;  //检查表键是否正确

check table用来针对许多问题对表进行检查。在mysiam表上还对索引进行检查。check table支持一系列的用于myisam表的方式。

changed检查自最后一次检查以来改动过的表，extended执行最彻底的检查，fast只检查未正常关闭的表，medium检查所有被删除的链接并进行键检查，quick只进行快速扫面。

check table tablename changed/extended/fast/medium/quick

如果myisam出现不正常和不一致的结果,可以用repair table来修复，如果从表中删除大量数据，应使用optimize table来收回所用的空间，从而优化表的性能。

查看日志文件

错误日志：包含启动和关闭问题以及任意关键错误的细节。通常名字为hostname.err,日志名可用--log-error命令行进行更改

查询日志：记录所有mysql活动，通常为hostname.log,日志名可用--log命令行进行更改

二进制日志：记录更新过的数据（或者可能更新过的数据）的所有语句，一般为hostname-bin,日志名可用--log-bin命令行进行更改

缓慢查询日志：记录执行缓慢的任何查询。可以确定数据库何处需要优化，名字通常为hostname-slow.log,日志名可用--log-slow-queries命令行进行更改

查看数据库当前设置:show variables; show status;

显示所有活动进程：show processlist,

使用explain语句查看mysql如何执行一条select语句

在导入数据时，应该关闭自动提交。还可以删除索引，然后在导入完成后再重建。

索引可以高山检索数据的性能，但损害数据插入，删除，更新的性能。

like很慢，一般说来，使用fulltext而不是like

如果有多个or条件，则可以通过使用多条select语句和连接他们的union，通常会更快些。