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