MySQL（下）

组合查询

创建组合查询

使用UNION

SELECT id,title,author FROM first_test WHERE TITLE = 'js'
UNION
SELECT id,title,author FROM first_test WHERE author = 'aaa';
+----+-------+--------+
| id | title | author |
+----+-------+--------+
|  1 | js    | lala   |
|  8 | xml   | aaa    |
+----+-------+--------+

第一条找出title是js的，第二条找出author是aaa的

UNION规则
- UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔（因此，如果组合4条SELECT语句，将要使用3个UNION关键字）。
- UNION中的每个查询必须包含相同的列、表达式或聚集函数（不过各个列不需要以相同的次序列出）。
- 列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含地转换的类型（例如，不同的数值类型或不同的日期类型）
UNION会去除重复的行，如果想匹配所有行可以使用UNION ALL
将ORDER BY放在最后，为最终结果排序

数据插入

INSERT用来插入
- 插入完整的行
- 插入行的一部分
- 插入多行
- 插入某些查询的结果
  插入完整的行
需要指定表名和被插入到新行中的值
1
INSERT INTO first_test VALUES(NULL,'CSS','bbb','2018-01-31');
因为第一个条目id为自增量，不想给出又不能忽略可以给出NULL由MySQL自行添加
插入语句没有输出
这种写法比较危险，可以采用下面的写法

1	INSERT INTO first_test(title,author,date) VALUES('CSS','bbb','2018-01-31');

在表后的括号中为列名与VALUES后括号中的值一一对应

插入多个行

可以使用多个INSERT插入多行，也可是使用这种方法
1
INSERT INTO first_test(title,author,date) VALUES('python','ddd','2018-01-31'),('php','eee','2018-01-31');
使用圆括号包住插入式，插入多条以逗号分隔
用单条INSERT插入多个行比使用多条INSERT快

插入检索出的数据

INSERT SELECT

1	INSERT INTO oldTable(id,title,author) VALUES id,title,author FROM newTable;

从newTable中检索出id,title,author然后插入到oldTable中

更新和删除数据

更新数据

使用UPDATE语句
- 更新表中特定行；
- 更新表中所有行；
  1
  UPDATE first_test SET author='eee' WHERE title = 'js';
  如果不使用WHERE则会更新表中的所有行，如果想要更改多个列则SET字段后需要使用,分隔
如果某一行出现错误则操作被取消（之前改的也会恢复到原来的值）使用IGNORE可以在发生错误的时候继续前进
1
UPDATE IGNORE first_test ...

为了删除某列的值可以设置它为NULL

1	UPDATE first_test SET author=NULL WHERE title = 'js';

删除数据

使用DELETE语句
- 从表中删除特定的行；
- 从表中删除所有的行
  1
  DELETE FROM first_test WHERE id=10;
  如果想要删除表中的所有行可以使用TRUNCTE，此命令比DELETE更快，它的含义是删除掉原来的表并重新创建一个

更新和删除的指导原则

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

创建和操纵表

创建表

使用CREATE TABLE创建表，并给出以下信息：

新表的名字，在关键字CREATE TABLE之后给出

表列的名字和定义，用逗号分隔

CREATE TABLE coupon_recharge_record
(
  id               INT(11) UNSIGNED AUTO_INCREMENT
    PRIMARY KEY,
  action_user_name VARCHAR(50)                         NULL
  COMMENT '操作用户name',
  user_id          BIGINT(11)                          NULL
  COMMENT '充值用户ID',
  core_customer_id BIGINT(11)                          NULL
  COMMENT '核心用户ID',
  amount           DOUBLE(8, 2)                        NULL
  COMMENT '红包金额',
  reason           VARCHAR(512)                        NULL
  COMMENT '红包发放事由',
  coupon_id        BIGINT(11)                          NULL
  COMMENT '红包记录ID',
  order_no         VARCHAR(20)                         NULL
  COMMENT '充值订单号',
  state            TINYINT(2)                          NULL
  COMMENT '状态：0-默认，1-红包创建成功，2-充值到资产服务失败，3-充值到资产服务成功',
  create_time      TIMESTAMP DEFAULT CURRENT_TIMESTAMP NULL
  COMMENT '创建时间',
  update_time      TIMESTAMP                           NULL ON UPDATE CURRENT_TIMESTAMP
  COMMENT '更新时间',
  CONSTRAINT IDX_COUPONID
  UNIQUE (coupon_id)
)
  COMMENT '红包充值记录'
  ENGINE = InnoDB;

创建新表时，指定的表名必须不存在，否则会出错，如果你仅想在一个表不存在时创建它，应该在表明后给出IF NOT EXISTS，它会查看表名是否存在，并且仅在表名不存在时创建它

创建表时可以规定该列可不可以为NULL
通过PRIMARY KEY(…)设置主键，其中可以设置多个值用逗号分隔来设置多列组成的主键
AUTO_INCREMENT可以让MySQL在本列增加一行时自动增量，每个表只允许一个AUTO_INCREMENT列，而且它必须被索引
使用SELECT last_insert_id()可以返回最后一个AUTO_INCREMENT值

如果在差入行是没有给出值，可以使用在CREATE TABLE中定义的默认值

CREATE TABLE orderitems(
	order_num int NOT NULL ,
	quantity int NOT NULL DEFAULT 1 ,
	PRIMARY KEY (order_num, order_item)
) ENGINE=InnoDB;

默认值必须为常量不能使用函数

ENGINE=指定引擎类型：
- InnoDB是一个可靠的事务处理引擎，不支持全文搜索；
- MEMORY在功能等同于MyISAM，但由于数据存储在内存中（不是磁盘）中，速度很快（特别适合于临时表）；
- MyISAM是一个性能极高的引擎，它支持全文本搜索，但不支持事务处理。
  
  同一个数据库中引擎可以混用，但是外键不能跨引擎，即使用一个引擎的表不能引用具有使用不同引擎的表的外键。

更新表

更新表定义使用ALTER TABLE语句，而且必须给出下面的信息：
- 在ALTER TABLE之后给出要更改的表名
- 所做更改的列表
增加列
1
ALTER TABLE vendors ADD vend_phone CHAR(20);
这条语句给vendors表增加一个名为vend_phone的列，必须明确数据类型

删除列

1	ALTER TABLE Vendors DROP COLUMN vend_phone;

定义外键

1	ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_orders FOREIGN KEY (order_num) REFERENCES orders (order_num);

删除表

使用DROP TABLE
1
DROP TABLE customers2;

重命名表

使用RENAME TABLE

1	RENAME TABLE customers2 TO customers;

也可以对多个表重命名

1
2
3

RENAME TABLE backup_customers TO customers,
				 backup_vendors TO vendors,
				 backup_products TO products;

使用视图

视图

视图是虚拟的表，视图只包含使用时动态检索数据的查询
视图的常见应用：
- 重用SQL语句
- 简化复杂的SQL操作
- 使用表的组成部分而不是整个表
- 保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限
- 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据
性能问题
因为视图不包含数据，所以每次使用视图时，都必须处理查询执行时所需的任一个检索。如果你用多个联结和过滤创建了复杂的视图或者嵌套了视图，可能会发现性能下降得很厉害。因此，在部署使用了大量视图的应用前，应该进行测试。
视图的使用规则和限制
- 与表一样，视图必须唯一命名（不能给视图取与别的视图或或表相同的名字）。
- 对于可以创建的视图数目没有限制。
- 为了创建视图，必须具有足够的访问权限。这些限制通常由数据库管理人员授予。
- 视图可以嵌套，即可以利用从其他视图中检索数据的查询来构造一个视图。
- ORDERBY可以用在视图中，但如果从该视图检索数据SELECT中也含有ORDERBY，那么该视图中的ORDERBY将被覆盖。
- 视图不能索引，也不能有关联的触发器或默认值。
- 视图可以和表一起使用。例如，编写一条联结表和视图的SELECT语句。

使用视图

视图的创建
- 使用CREATE VIEW语句创建
- 使用SHOW CREATE VIEW viewname;来查看创建视图的语句
- 用DROP删除视图，其语法为DROP VIEW viewname;
- 更新视图时，可以先用DROP再用CREATE，也可以直接用CREATE OR REPLACE VIEW。如果要更新的视图不存在则会创建一个视图
利用视图简化复杂的联结
1
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*FROMproductcustomers，将列出订购了任意产品的客户。
如果此时检索订购了TNT2的客户可以进行如下操作：
1
SELECT cust_name, cust_contact FROM productcustomers WHERE prod_id = 'TNT2';

用视图重新格式化检索出的数据

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

用视图过滤不想要的数据

1	CREATE VIEW customeremaillist AS SELECT cust_id, cust_name, cust_email FROM customers WHERE cust_email IS NOT NULL;

更新视图
通常视图是可更新的（可以使用INSERT,UPDATE和DELETE）。更新一个视图将更新其基表。
如果定义视图时有以下操作则不能更新：
- 分组（GROUP BY和HAVING）
- 联结
- 子查询
- 并
- 聚集函数（Min(),Count(),Sum()等）
- DISTINCT
- 导出（计算）列

使用存储过程

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

执行存储过程

1
2
3

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

创建存储过程

CREATE PROCEDURE productpricing()
GEGIN
	SELECT Avg(prod_price) AS priceaverage
	FROM products;
END;

执行方式如下

1	CALL productpricing();

删除存储过程
1
DROP PROCEDURE productpricing;
如果productpricing不存在，则会产生错误，如果想要过程不存在也不产生错误的话可以使用DROP PROCEDURE IF EXISTS

触发器

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

创建触发器

创建触发器需要4条信息：
- 唯一的触发器名
- 触发器关联的表
- 触发器应该响应的活动
- 触发器何时执行（处理之前或之后）
使用CREATE TRIGGER语句创建
1
CREATE TRIGGER newprodut AFTER INSERT ON products FOR EACH ROW SELECT 'Product added';
只有表才支持触发器，视图不支持（临时表也不支持）
触发器按每个表每个事件每次地定义，每个表每个事件每次只允许一个触发器。因此，每个表最多支持6个触发器（每条INSERT、UPDATE和DELETE的之前和之后）。单一触发器不能与多个事件或多个表关联，所以，如果你需要一个对INSERT和UPDATE操作执行的触发器，则应该定义两个触发器。

触发器失败：如果BEFORE触发器失败，则MySQL将不执行请求的操作。此外如果BEFORE触发器或语句本身失败，MySQL将不执行AFTER触发器（如果有的话）。

删除触发器

使用DROP TRIGGER语句
1
DROP TRIGGER newproduct;
触发器不能更新或覆盖。为了修改一个触发器，必须先删除它，然后再重新创建。

使用触发器

INSERT触发器
- 在INSERT触发器代码内，可引用一个名为NEW的虚拟表，访问被插入的行；
- 在BEFOREINSERT触发器中，NEW中的值也可以被更新（允许更改被插入的值）；
- 对于AUTO_INCREMENT列，NEW在INSERT执行之前包含0，在INSERT执行之后包含新的自动生成值。
  1
  CREATE TRIGGER neworder AFTER INSERT ON orders FOR EACH ROW SELECT NEW.order_num;

DELETE触发器

在DELETE触发器代码内，你可以引用一个名为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的虚拟表访问以前（UPDATE语句前）的值，引用一个名为NEW的虚拟表访问新更新的值；
- 在BEFOREUPDATE触发器中，NEW中的值可能也被更新（允许更改将要用于UPDATE语句中的值）；
- OLD中的值全都是只读的，不能更新。
  1
  CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors FOR EACH ROW SET NEW.VEND_STATE = Upper(NEW.vuend_state);
其他介绍：
- 与其他DBMS相比，MySQL5中支持的触发器相当初级。未来的MySQL版本中有一些改进和增强触发器支持的计划。
- 创建触发器可能需要特殊的安全访问权限，但是，触发器的执行是自动的。如果INSERT、UPDATE或DELETE语句能够执行，则相关的触发器也能执行。
- 应该用触发器来保证数据的一致性（大小写、格式等）。在触发器中执行这种类型的处理的优点是它总是进行这种处理，而且是透明地进行，与客户机应用无关。
- 触发器的一种非常有意义的使用是创建审计跟踪。使用触发器，把更改（如果需要，甚至还有之前和之后的状态）记录到另一个表非常容易。
- 遗憾的是，MySQL触发器中不支持CALL语句。这表示不能从触发器内调用存储过程。所需的存储过程代码需要复制到触发器内。

#SQL

组合查询

创建组合查询

数据插入

插入完整的行

插入多个行

插入检索出的数据

更新和删除数据

更新数据

删除数据

更新和删除的指导原则

创建和操纵表

创建表

更新表

删除表

重命名表

使用视图

视图

使用视图

使用存储过程

触发器

创建触发器

删除触发器

使用触发器