mysql

---

聚合函数 sum max min avg count

---

group by

group by 子句可以包含任数目的字段group by 子句中每个列都是建所列或有效表达式(不能是聚合函数)除了聚会计算语句之外,select语句中的每个列都必须在group中出现如果分组中有null值,则分为一组group by 子句必须在where字句之后,order by子句之前

where过滤行,having过滤的是分组,where没有分组的概念

一般在使用group by子句时,也应该给出order by子句,这是保证数据正确排序的唯一方法,不能依赖group by排序数据例`SELECT order_num,SUM(quantity*item_price) AS ordertotal

FROM orderitems GROUP BY order_num HAVING ordertotal >50 ORDER BY ordertotal`

使用子查询 子查询最常用在where和 in 操作中 SELECT cust_name,cust_state,(SELECT COUNT(*) FROM orders WHERE orders.cust_id=customers.`cust_id`) AS orders FROM customers ORDER BY cust_name union规则:

union必须有2个select语句组成union的每个查询必须包含相同的列,表达式,聚合函数列数据类型相同

union all 保留重复的数据 union默认去除重复的数据

---

MySQL的全文索引

Match()指定被搜索的列,Against()指定要使用的搜索表达式Myisam支持全文索引,innodb并不支持创建全文索引fulltext(column)SELECT note_text FROM products WHERE MATCH(note_text) AGAINST('rabbit')

---

插入数据 insert table(xx) values (xx) 更新数据 update table set xx = xx 删除数据 delete from table where xx=xx

注意 delete并不删除表本身,只是删除一行,删除所有行用 truncate table

创建试图 ,试图本身不包含数据,最常用的就是隐藏复杂的sql,创建格式为create view viewname as select xx

---

创建存储过程 DELIMITER // CREATE PROCEDURE productpricing() BEGIN SELECT avg(prod_price) AS priceavg FROM products; END // DELIMITER ; CALL productpricing(); DROP PROCEDURE productpricing 存储过程接收三个参数 pl(product lower)产品最低价 ph 最高价,pa平均价 DELIMITER // 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 // DEALLOCATE ; CALL productpricing(@pricelow,@pricehigh,@priceavg) SELECT @pricelow low ,@pricehigh high,@priceavg AVG 存储过程接受一个参数,输出一个参数 (In Out) delimiter // create procedure productpricing(IN onnumber Int ,OUT total decimal(8,2)) BEGIN select sum(item_price*quantity) INTO total from orderitems where order_num=onnumber ; end // delimiter ; call productpricing(20005,@total); select @total; 带业务的存储过程 1表示true,0表示false DELIMITER // CREATE PROCEDURE ordertotal(IN number INT,IN taxable BOOLEAN ,OUT ototal DECIMAL(8,2) ) BEGIN -- decalare var for total DECLARE total DECIMAL(8,2); -- decalare tax percentage DECLARE taxrate INT DEFAULT 6; -- get total the order SELECT SUM(item_price*quantity) INTO total FROM orderitems WHERE order_num=number; -- is this taxable IF taxable THEN -- yes SELECT total+(total/100*taxrate) INTO total; END IF; -- and finally save our var SELECT total INTO ototal; END // DELIMITER ; CALL ordertotal(20005,0,@total); SELECT @total; CALL ordertotal(20005,1,@total); SELECT @total; 使用游标

1.使用游标前,必须声明(定义它),这个过程实际没有检索数据,他只是定义要使用的select语句2.一旦声明后就要打开游标已供使用3.在游标结束使用时,必须关闭游标4.打开游标后使用fetch进行使用

创建游标 DELIMITER // CREATE PROCEDURE processorders() BEGIN -- declare var DECLARE done BOOLEAN DEFAULT 0; DECLARE o INT; DECLARE t DECIMAL(8,2); -- declare cursor DECLARE ordernums CURSOR FOR SELECT order_num FROM orders; -- declare continue handler , this is the end of row DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1; -- create table CREATE TABLE IF NOT EXISTS ordertotals(order_num INT ,total DECIMAL(8,2)); -- open cursor OPEN ordernums; -- loop all rows REPEAT -- get order number FETCH ordernums INTO o; -- get total for this order CALL ordertotal(o,1,t); -- insert order and total INSERT INTO ordertotals(order_num,total) VALUES(o,t); -- end of loop UNTIL done END REPEAT; -- close cursor CLOSE ordernums; END // DELIMITER ; CALL processorders(); 使用游标例子2 DELIMITER // CREATE PROCEDURE processorders() BEGIN DECLARE obj INT; DECLARE target DECIMAL (8,2); DECLARE done INT DEFAULT FALSE; DECLARE cur CURSOR FOR SELECT order_num FROM orders; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; CREATE TABLE IF NOT EXISTS order_total(order_num INT,total DECIMAL(8,2)); -- open cursor OPEN cur; read_loop: LOOP FETCH cur INTO obj; IF done THEN LEAVE read_loop; END IF; CALL ordertotal(obj,1,target); INSERT INTO order_total(order_num,total) VALUES(obj,target); END LOOP; -- close cursor CLOSE cur; END // DELIMITER ; CALL processorders(); 触发器的使用

1 .在创建触发器时,需要给出4条信息:1. 唯一的触发器名,2.触发器关联表,3.触发器应该响应的活动(delete,update,insert),触发器何时执行(处理之前或之后)2 . 只有表才支持触发器,试图和临时表不支持触发器

触发器例子(但是MySQL 5.0 之后不能返回结果集) CREATE TRIGGER newproduct2 AFTER INSERT ON order_total FOR EACH ROW SELECT 'products add ';

---

事物管理 transaction rollback commit save point