sql 优化

mysql  中sql优化：

 

show procedure procedure_name;

show procedure status like 'procedure_name'; //显示所有存储过程

 

1.用自连接而不是用子查询    自联接通常作为外部语名用来替代从相同表中检索数据时使用的子查询语名。虽然最终的结果是相同的，但有时候处理联结比处理子查询快得多。应该试一下两种方法，以确定哪一种的性能更好。

         select c.* ,o.order_num,o.order_date  from customers as c,orders as o,orderitems as oi and oi.order_num=o.order_num and prod_id='FB';

 

       每一个内部联结都是自然联结。

 

2.分组  select *,count(*) from T_Category group by Pid 会报错？

             只能是这样的：select Pid, count(*) from T_Category group by Pid

 

使用带聚集函数的联结  (如下图)

             select customers.cust_name,

                        customers.cust_id,

                        count(orders.order_num) as num_ord

                 from customers inner join orders  on

                  customers.cust_id=orders.cust_id group by customers.cust_id;

 

3.mysql 分页： select * from T_Category limit  20,30

                        20表示第20行开始，查询结果为30行

 

4 .mysql的group by优化

             select count(*)   c ,CategoryOrder from T_Category group by CategoryOrder having c>=200;

5.mysql 拼接字段：select concat(CategoryName,'----',CategoryOrder)  cc from T_Category

                在mysql中用concat()函数来连接字符串

6.外部联接的类型     存在两种基本的外部联接形式：左外部联接和右外部联结。它们之间的唯一差别是所关联的表的顺序不同。换名话说，左外部联结可通过颠倒from 或 where 子句中表的顺序转换为右外部联结。因此，两种类型的外部联结可互换使用，而究竟使用哪一种纯粹是要据方便而定。

        select customers.cust_id,orders.order_num from customers left outer join orders on

                 customers.cust_id=orders.cust_id;

1, 从 INSERT 返回 IDENTITY

　　SELECT @@IDENTITY

 

7.mysql存储过程     大多数sql语句都是针对一个或多个表的单条语句。并非所有操作都这么简单，经常 会有一个完整的操作需要多条语句才能完成。

         如果你使用的是mysql命令行实用程序，需要使用delimiter  // (创建procedure时，以防止和存储过程中的;)

   所有Mysql变量都必须以@开始。

       

           delimiter //
create procedure testproc(
       out plow decimal(8,2) ,
       out phigh decimal(8,2),
       out pavg decimal(8,2))
   begin
          select min(prod_price) into plow from products;
          select max(prod_price) into phigh from products;
          select avg(prod_price) into pavg from products;
 end //
delimiter ;

select @priceaverage;

----------------------------------------------------------------------------------------------------------------

create procedure ordertotal(
       in onumber int,
       in taxable boolean,
       out ototal decimal(8,2))
          comment 'Obtain order total,optionally adding tax'
        begin
               declare total decimal(8,2);
               declare taxrate int default 6;   //在mysql存储过程中定义变量
           
             select  sum(item_price*quantity) from orderitems where
                   order_id=onumber into total;

                if taxable then
                     select total+(total/100*taxrate)  into total; 
                  end if;
              select total into ototal;       //给变量赋值
              end //

8.在mysql中使用游标

            只能用于存储过程    不像多数DBMS,Mysql游标只能用于存储过程(和函数)。

           （1）创建游标         declare mycusor cursor for select * from orders;

            (2)打开游标            open  mycusor;

            (3) 关闭游标            close  mycusor;

              隐含关闭        如果你不明确关闭游标，Mysql将会在到达end语名时自动关闭它。

                 使用游标数据     declare var int;

                                   fetch mycusor into var;

  

             create procedure proccursor2()
begin
declare done boolean default 0;
declare o int;
declare mycusor cursor for
select quantity from orderitems;
declare continue handler for sqlstate '02000' set done=1;  //当没有数据时，sqlstate=02000
open mycusor;
repeat
fetch mycusor into o;
select o;
until done end repeat;   //done=1结束循环
close mycusor;
end //

-------------------------------------------------------------------------------------------------------

create procedure proccursor3()
begin
declare done boolean default 0;
declare o int;
declare t decimal(8,2);
declare  mycursor cursor for
select ord_id from orders;
declare continue handler for sqlstate '02000' set done=1;
create table if not exists temp(order_id int,total decimal(8,2));  //创建 一个表
open mycursor;
repeat
fetch mycursor into o;
call ordertotal(o,1,t);
insert into temp(order_id,total) values(o,t);
until done end repeat;
close mycursor;
end //