非选择的列也可以使用) | **只可能使用选择列或表达式列,SELECT中出现的**,而且必须使用每个选择列表达式 | | 不一定需要 | **如果与聚集函数一起使用列(或表达式),则必须使用**, | 千万不要仅依赖GROUP BY排序数据。SELE ```sql SELECT order_num ,SUM(quantity*item_prices) AS order_total FROM orderitems GROUP BY order_num//需要和select中的列相同,除了聚集函数 HAVING SUM(quantity*item_price)>=50 ORDER BY order_total; ``` ### select语句只有select子句是必须的,且select后面跟的是表达式 利用子查询进行过滤,select的结果可用于另一条SELECT语句的WHERE子句。**内层的SELECT语句的列和外层的WHERE列相同** ```sql SELECT order_num FROM orderitems WHERE prod_id='INIT2';//20002,20004 SELECT cust_id FROM orders WHERE order_num IN(20002,20004) 合并 SELECT cust_id FROM orders WHERE order_num IN(SELECT order_num FROM orderitems WHERE prod_id='INT2');//执行流程:从内向外。 //查询ID的信息。 SELECT cust_name,cust_contact FROM customers WHERE cust_id IN(SELECT cust_id//返回客户ID FROM orders WHERE order_num IN(SELECT order_num//返回订单号列表 FROM orderitems WHERE prod_id='INT2')); ``` **作为计算字段使用子查询**,子查询:过滤或者使用计算字段 ```sql --每个客户的总订单数 思路:先求单个客户的,然后使用子查询 SELECT COUNT(*) AS orders FROM orders WHERE cust_id=10001; //为了对每个可以执行COUNT(*) ,子查询 SELECT cust_name, cust_state, (SELECT COUNT(*) FROM orders WHERE orders.cust_id=customers.cust_id) AS orders FROM customers ORDER BY cust_name; ``` ### 子查询构造过程:从内到外,逐渐进行。 测试时外层循环用硬编码,eg:where cust_id=23223; 子查询最常见的使用是==在WHERE子句的IN操作符中==,以及==用来填充计算列== **一类数据一个表**。各表通过某些常用的值(即关系设计中的关系(relational))互相关联。 外键(foreign key) 外键为某个表中的一列,它包含**另一个表的主键值**,定义了两个表之间的关系。 ### 连接:要连接的==所有表==和==连接方式== ```sql SELECT vend_name,prod_name,prod_price FROM vendors AS v,products AS p--所有表 WHERE v.vend_id=p.vend_id-- 连接方式:equijoin,等值连接,又称内部联结 ORDER BY vend_name,prod_name; 完全限定列名消除二义性!! SELECT vend_name,prod_name,prod_price FROM vendors INNER JOIN products ON vendors.vend_id=products.vend_id; //多个表。列出所有,然后关联!! ``` 其实,对于这种联结(等值连接、内部连接)可以使用稍微不同的语法来明确指定联结的类型。`FROM A,B WHERE A.col1=B.col2`可以简写为:inner join。联结是SQL中最重要最强大的特性 **使用哪种语法? ANSI SQL规范首选INNER JOIN语法**,连接条件。 **别名除了用于列名和计算字段外,SQL还允许给表名起别名。** - 缩短SQL语句; - **允许在单条SELECT语句中==多次使用相同的表==。**自连接 ```sql --物品有问题,该供应商的其他商品是否也有问题 SELECT prod_id,prod_name FROM products WHERE vend_id=(SELECT Vend_id FROM products WHERE prod_id='DTNTR') --子查询 -- 联结 SELECT 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语法时,必须使用RIGHT或LEFT关键字指定**包括其所有行的表**(RIGHT指出的是OUTER JOIN右边的表,而LEFT指出的是OUTER JOIN左边的表) ```sql SELECT customers.cust_id,COUNT(orders.order_num) AS num_ord FROM customers RIGHT OUTER JION orders ON orders.cust_id=customers.cust_id ``` **应该==总是提供联结条件==,否则会得出笛卡儿积。** ### 多条SQL 的组合查询 UNION MySQL也允许执行多个查询(多条SELECT语句),并将结果作为单个查询结果集返回,`union`或者复合查询(compound query),**组合查询和多个WHERE条件** 所需做的只是给出每条SELECT语句,在各条语句之间放上关键字UNION。==UNION中的每个查询必须包含相同的列、表达式或聚集函数==,因为返回的结果合为一个表 ```sql --UNION从查询结果集中自动去除了重复的行 SELECT vend_id,prod_id,prod_price FROM products WHERE prod_price<=5 UNION ALL--保留重复 SELECT vend_id,prod_id,prod_price FROM proucts WHERE vend_id IN(1001,1002) ORDER BY vend_id,prod_price;--能使用一条ORDER BY子句,它必须出现在最后一条SELECT语句之后 ----等价于多个where条件 SELECT vend_id ,prod_id,prod_price FROM products WHERE prod_price<=5 OR vend_id IN (1001,1002) ``` UNION几乎总是完成与多个WHERE条件相同的工作。UNION ALL为UNION的一种形式,它完成WHERE子句完成不了的工作。**如果确实需要每个条件的匹配行全部出现(包括重复行),则必须使用UNION ALL而不是WHERE**。 在用UNION组合查询时,只能使用一条ORDER BY子句,它必须出现在最后一条SELECT语句之后 ### 全文索引 ```sql CREATE TABLE product_notes( note_id int NOT NULL AUTO_INCREMENT, prod_id char(10) NOT NULL, note_date datetime NOT NULL, note_text text NULL, PRIMARY KEY(note_id), FULLTEXT(note_text) )ENGINE=MyISAM; ``` **MyISAM支持全文本搜索(对结果排序,较高优先级的行先返回,自带停用词列表(stopword)),而InnoDB不支持** 子句`FULLTEXT(列名称)`的指示对它进行索引,**先导入,后定义索引**,在索引之后,使用两个函数Match()和Against()执行全文本搜索,其中Match()指定被搜索的列,Against()指定要使用的搜索表达式。 ```sql SELECT note_text FROM product_notes WHERE Math(note_text) Against('rabbit') AS rank; -- 效果上等价于LIKE ‘%rabbit%’,但效率不同。 SELECT note_text FROM product_notes WHERE note_text LIKE '%rabbit%'; ``` ### 插入完整的行 insert into tables values() ```sql INSERT INT Customers(可以指定列名顺序) VALUES(a),VALUES(b);-- 插入多个行 ``` ==总是使用列的列表==。插入时提高整体性能,可以使用`INSERT LOW_PRIORITY INTO` **插入检索出的数据 insert select**,列的位置。 ```sql INSERT INTO customers(cust_id,cust_contact,cust_email,cust_name) SELECT cust_id,cust_contact,cust_email,cust_name FROM custnew; ``` ### 更新数据(update,以where结束)和删除数据(delete) ```sql UPDATE (IGNORE) customers-- 表名 SET cust_email='xxx@ffdd.com',cust_name='ddd' WHERE cust_id=10004; ``` 不要省略WHERE子句(对update和delete都有效),UPDATE语句总是**以要更新的表的名字开始**. **DELETE FROM要求指定从中删除数据的表名**。WHERE子句过滤要删除的行。 ```sql DELETE FROM customers WHERE cust_id=10004; ``` DELETE不需要列名或通配符。==DELETE删除整行而不是删除列。为了删除指定的列,请使用UPDATE语句==。 更快的删除:truncate:TRUNCATE实际是删除原来的表并重新创建一个表,而不是逐行删除表中的数据在对**UPDATE或DELETE语句使用WHERE子句前,应该先用SELECT进行测试**,保证它过滤的是正确的记录,以防编写的WHERE子句不正确。应该使用引用完整性。使用默认值而不是NULL值 ### 创建表:表名+列名 ```sql CREATE TABLE orders( order_num int NOT NULL AUTO_INCREMENT, cust_id int NOT NULL DEFAULT 1,-- 默认值 PRIMARY KEY(order_num) )ENGINE=InnoDB; ``` NULL为默认设置.使用的最简单的编号是下一个编号.AUTO_INCREMENT; **每个表只允许一个AUTO_INCREMENT列,而且它必须被索引(如,通过使它成为主键)。**通过`last_insert_id()函数`获得最后一个AUTO_INCREMENT.存储引擎负责创建表等操作。 在表的设计过程中需要花费大量时间来考虑,**以便后期不对该表进行大的改动**。 **更新表 alter table** ```sql ALTER TABLE vendors ADD vend_phone CHAR(20);-- 增加列。 ALTER TABLE vendors DROP COLUMN vend_phone; ``` ALTER TABLE的一种常见用途是==定义外键== ```sql ALTER TABLE order_items ADD CONSTRAINT fk_item_orders FOREIGN KEY(order_num) REFERECES orders(order_num); ``` **使用ALTER TABLE要极为小心,应该在进行改动前做一个完整的备份**,没有撤回操作。 ==删除表==:`DROP TABLE 表名` ==重命名表==:`RENAME TABLE 原来的表名 TO 新的表名` ```sql RENAME TABLE backup_a TO a, backup_b TO b; ``` ### 视图 ==视图是虚拟的表==。与包含数据的表不一样,**视图只包含使用时动态检索数据的查询**。 视图作用: - **重用SQL**。 - 简化复杂的SQL操作。 **视图本身不包含数据,因此它们返回的数据是从其他表中检索出来的**。在添加或更改这些表中的数据时,视图将返回改变过的数据。 视图用CREATE VIEW语句来创建。 SHOW CREATE VIEW viewname 其语法为DROP VIEW viewname; 视图更新:先drop后create或者:`CREATE OR REPLACE VIEW` ==视图的最常见的应用之一是隐藏复杂的SQL==;视图的另一常见用途是==重新格式化检索出的数据==。视图对于简化计算字段的使用特别有用。 ```sql CREATE VIEW product_customers AS SELECT cust_name,cust_contact,prod_id FROM customers,orders,order_items WHERE customers.cust_id=orders.cust_id AND order_items.order_num=orders.order_num;-- 连接条件 -- 体会,这儿就是将连接之后的逻辑的表作为视图 -- 应用 SELECT * FROM product_customers; SELECT cust_name,cust_contact FROM product_customers WHERE prod_id='TNT2'; -- 利用视图,可一次性编写基础的SQL,然后根据需要多次使用。作用类似于函数 CREATE VIEW location AS SELECT Concat(RTrim(vend_name),' (',RTrim(vend_country),' )') AS title FROM vendors ORDER BY vend_name; SELECT * FROM location;-- 应用视图 -- 用视图过滤不想要的数据 CREATE VIEW list AS SELECT cust_id,cust_name,cust_email FROM customers WHERE cust_eamil IS NOT NULL; -- SELECT * FROM list;-- WHERE子句与WHERE子句将自动合并。 CREATE VIEW infomation AS SELECT order_num,prod_id,quantity,item_price,quentity*item_prices AS total_price FROM order_items; -- 应用 SLECT * FROM infomation WHERE order_num=20005; ``` 基本上可以说,**如果MySQL不能正确地确定被更新的==基数据==,则不允许更新图(包括插入和删除视)**。这实际上意味着,如果视图定义中有以下操作,则不能进行视图的更新:`GROUP BY 、JOIN、UNION、聚集函数(MIN(),COUNT(),SUM(),DISTINCT)` ### 使用存储过程 **存储过程**简单来说,就是为以后的使用而==保存的一条或多条MySQL语句的集合==。可将**其视为批文件**,虽然它们的作用不仅限于批处理。 MySQL称存储过程的执行为调用,CALL ```sql CALL product_pricing( @pricelow, @pricehigh, @price_average);-- 调用。 -- -- -- MYSQL 调用时需要修改结束分割符号。 DELEMITER // CREATE PROCEDURE prices() BEGIN SELECT Avg(prod_price) AS price_avg FROM products; END// DELEMITER ; -- 应用 CALL prices(); -- 删除 DROP PROCEDURE IF EXISTS prices; ``` **带参数的存过程** ```sql -- 声明变量 ;DECLARE price_low DECIMAL(8,2); DELEMITER // CREATE PROCEDURE prices( OUT p1 DECIMAL(8,2), OUT ph DECIMAL(8,2)) BEGIN SELECT MIN(prod_price) INTO p1 FROM products; SELECT MAX(prod_price) INTO ph FROM products; END // DELEMITER ; CALL prices(@price_low,@proce_high);所有MySQL变量都必须以@开始。 -- 以上调用不显示任何的数据,它返回变量; 应用 SELECT @price_low; -- COMMENT关键字 SHOW CREATE PROCEDURE ``` ### 游标cursor:逐行处理结果集 需要在检索出来的行中前进或后退一行或多行。这就是使用游标的原因。==游标(cursor)是一个存储在MySQL服务器上的数据库查询==,它不是一条SELECT语句,而是被该语句检索出来的结果集。**MySQL游标只能用于存储过程**(和函数)。**使用FETCH来读取所需数据** ```sql BEGIN DECLARE o INT; DECLARE done BOOLEAN DEFAULT 0; DECLARE order_nums CURSOR FOR -- 声明游标 ,是一种类型。 SELECT order_num FROM orders; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1; --使用 OPEN order_nums; --循环 REPEAT FETCH order_nums INTO o; UNTIL done END REPEAT; --关闭 CLOSE order_nums; END; -- SQLSTATE'02000'是一个未找到条件 ``` ### 触发器:SQL语句与事件关联 应用场景: - insert时检查电话号格式。 - delete时保存副本等。 **触发器**是MySQL响应以下语句(`DELETE`、`INSERT`、`UPDATE`)而==自动执行的一条MySQL语句==(**或**位于BEGIN和END语句之间的一组语句). ```sql CREATE TRIGGER new_product AFTER INSERT ON products FOR EACH ROW SELECT 'product added'; -- 删除,触发器不支持更新 DROP TRIGGER new_product; ``` 只有表才支持触发器,视图不支持(临时表也不支持)。**每个表最多支持6个触发器**(每条INSERT、UPDATE和DELETE的之前和之后) ```sql CREATE TRIGGER new_order AFTER INSERT ON orders FOR EACH ROW SELECT NEW.order_num;-- FOR EACH ROW 跟具体的SQL语句。 -- 应用 INSERT INTO orders(order_date,cust_id) VALUES(Now(),10001); -- 返回值就是触发器定义的内容。 -- DELETE 触发器 CREATE TRIGGER delete_order BEFORE DELETE ON orders FOR EACH ROW BEGIN INSERT INTO archive_orders VALUES(OLD.order_num,OLD.order_date,OLD.cust_id); END -- 引用一个名为OLD的虚拟表 -- UPDATE 触发器的应用,数据验证 CREATE TRIGGER update_vendor BEFORE UPDATE ON vendors FOR EACH ROW SET NEW.vend_state=Upper(NEW.vend_state) ``` **触发器的一种非常有意义的使用是创建审计跟踪**。==使用触发器,把更改(如果需要,甚至还有之前和之后的状态)记录到另一个表非常容易.== ### 事务处理 **事务(transaction)指一组SQL语句;** 1. 回退(rollback)指==撤销==指定SQL语句的过程; 2. 提交(commit)指将未存储的SQL语句结果==写入数据库表==; 3. 保留点(savepoint)指事务处理中设置的==临时占位符(placeholder)==,你可以对它发布回退(与回退整个事务处理不同)。 **事务处理用来管理INSERT、UPDATE和DELETE语句**,不能回退SELECT、CREATE、DROP。 ```sql SELECT * FROM order_totals; START TRANSACTION; DELETE FROM order_totals; SELECT * FROM order_totals; ROLLBACK;-- 回退到START TRANSACTION DELETE FROM order_items WHERE order_num=20001; DELETE FROM orders WHERE order_num=23334; SELECT * FROM order_totals; -- 提交。 COMMIT; ``` 隐含提交(implicit commit),即提交(写或保存)操作是自动进行的。**在事务处理时,提交不会隐含地进行**. 为了支持==回退部分事务处理==,必须能在事务处理块中合适的位置放置占位符。这样,如果需要回退,可以回退到某个占位符。**这些占位符称为保留点`SAVEPOINT`** ```sql SAVEPOINT delete1; ROLLBACK TO delete1;-- 保留点越多越好。RELEASE SAVEPOINT ``` 更改默认的提交行为(**连接专用**):`SET autocommit=0;` ### 全球化和本地化:字符集 - [ ] 字符集为字母和符号的集合; - [ ] 编码为某个字符集成员的内部表示; - [ ] ==校对`COLLATE`:规定字符如何**比较**的指令==。 ```sql SHOW CHARACTER SET; SHOW COLLATION; SHOW VARIABLES LIKE 'character%' -- 创建表时指定字符集和校对。 -- 当不指定COLLATE,则使用数据库默认。 CREATE TABLE mytable( column1 INT, columns2 VARCHAR(10) )DEFAULT CHARACTER SET hebrew COLLATE hebrew_general_ci; ``` **校对在对用==ORDER BY子句== 检索出来的数据排序时起重要的作用。** COLLATE可用于`ORDER BY`、`GROUP BY`、`HAVING`、聚集函数、别名等。 ```sql SELECT * FROM customers ORDER BY lastname,firstname COLLATE latin1_general_cs; ``` ### 安 全 管 理 访问控制:**设置权限,使用GRANT语句** **MySQL用户账号**和信息存储在名为`mysql`的库中. ```sql USE mysql; SELECT user FROM user; ``` MySQL的权限**用用户名和主机名**结合定义,不指定`采用默认值` ```sql CREATE USER 'yyq' identified by '密码' ;-- 可以不指定密码,指定时需要用identified by RENAME USER yyq TO 'new_name'; ``` `GRANT`:权限名称-数据库或者表-用户名。**回收权限:revoke** ```sql GRANT SELECT on test.* TO yyq; REVOKE SELECT ON test.* TO yyq; ``` GRANT和REVOKE可在**几个层次**上控制访问权限: ==整个服务器==,使用GRANT ALL和REVOKE ALL; ==整个数据库==,使用ON database.*; 特定的表,使用ON database.table; 特定的列; 特定的存储过程。 简化多次授权:`GRANT SELECT,INSERT ON test.* TO yyq` **更改口令** ```sql SET PASSWORD FOR yyq=Password('ddd'); ``` ### 数据库维护:备份mysqldump mysqlhotcopy,BACKUP TABLE ```sql analyze table user; check table user; ``` **查看日志**:1.错误日志·`hostname.err`;2.查询日志`hostname.log`;3.二进制日志(更新):`hostname-bin`;4.慢查询日志`hostname-slow.log` ### 改善性能 `SHOW VARIABLES;SHOW STATUS;SHOW PROCESSLIST`。 总是有不止一种方法编写同一条SELECT语句。==应该试验联结、并、子查询等,找出最佳的方法== **使用`EXPLAIN`命令**检查SQL执行过程。 - [x] 应该总是使用正确的数据类型。**串类型、数值类型、日期和时间**、**二进制类型Blob(Binary long Object)** - [ ] ==复杂的OR条件用多条SQL语句+UNION代替==。 - [ ] LIKE很慢。一般来说,最好是使用FULLTEXT而不是LIKE。 如果**数值是计算(求和、平均等)中使用的数值**,则**应该存储在数值数据类型列**中。如果**作为字符串(可能只包含数字)使用,则应该保存在串数据类型列中**。 不使用引号 与串不一样,数值不应该括在引号内。