sql扫描

select(select count(*) from city)-count(*) from city where id<=5;这样的语句不用全表扫描，提高了效率，这条语句扫描了6次  小括号中的查询语句没有进行全表扫描，sql知道有多少行数，

count(*) from city where id<=5 sql语句中的这一部分语句查询了5行数据   
select * from city where id>5;扫描了95次 
select count(*) from city;速度更快，但是加了where就不一样了
#建表
create table if not exists cnt(id int,name varchar(10),age int,tel varchar(10));
#创建存储过程,循环插入100000条数据
delimiter $
create procedure cnt()
begin
declare i int default 0;定义一个循环变量
while(i<100000) do
　　begin
　　　　select i;  查询变量i i不区分大小写，sql关键字也不区分大小写
　　　　set i=i+1；
　　　　insert into cnt(id,name)values(i,"zhang");
　　end;
end while;
end $
delimiter;
call cnt();  调用存储过程
create index index_age on cnt(age);
select * from cnt where age>18;效率高
select * from cnt where age*2>36;效率低
delete from cnt;要使用这条语句删除所有数据，因为100000条数据有好几页，手动删除比较麻烦
select *,concat('%',';','%') from user2 where uname like concat('%',uname,'%');#concat()用来连接一个或多个字符串,第一个字符串%连接第二个字符串uname和第三个字符串%
select count(*) from emp where age in(select age from emp where id>100);较快
select count(*) from emp where exists(select age from emp where id>100);较慢
select * from emp where age in(100,150,200);in查询不连续的范围，不能使用索引

  #distinct去重:如果除了name列外其他列数据不同,SELECT DISTINCT *就显示所有数据，而SELECT DISTINCT name只显示一条数据

select distinct * from emp;
select distinct name from emp;


select * from emp group by name;去重,多个name相同的话取第一个name的数据

USE test;
-- 创建表
CREATE TABLE IF NOT EXISTS T1(processid INT,nextprocess INT);
-- 创建索引
CREATE INDEX IX_ProcessID ON T1(processid);
-- a语句不使用索引
SELECT * FROM T1 WHERE nextprocess = 1 AND processid IN (8,32,45);
-- b语句强制使用索引
SELECT * FROM T1 FORCE INDEX(IX_ProcessID)WHERE nextprocess = 1 AND processid IN (8,32,45);
-- 消除顺序读取 顺序读取是从头读到尾
CREATE TABLE IF NOT EXISTS orders(customer_num INT,order_num INT);
-- a语句不使用索引
SELECT * FROM orders WHERE (customer_num=104 AND order_num>1001) OR order_num=1008;
-- b语句拆分，使用索引
SELECT * FROM orders WHERE customer_num=104 AND order_num>1001
UNION
SELECT * FROM orders WHERE order_num=1008;
-- 模糊查询where like,母打头'l%'可以使用索引，非字母打头'%l%'不会使用索引
CREATE TABLE IF NOT EXISTS T2(NAME VARCHAR(20));
CREATE INDEX ix_name ON T2(NAME);
-- a语句不会使用索引 会进行全表扫描
SELECT * FROM T2 WHERE NAME LIKE '%L%';
SELECT * FROM T2 WHERE SUBSTRING(NAME,2,1)='L'; -- 截取name从第二个字符开始截取一个字符
-- b语句会使用索引
SELECT * FROM T1 WHERE NAME LIKE 'L%';
-- a.尽量不要修改主键字段
-- b.当修改varchar型字段时，尽量使用相同长度内容的值代替 update要看成动词，不要看成名词
-- c.尽量最小化对于含有update触发器的表的update操作
-- d.避免update将要复制到其他数据库的列
-- e.避免update建有很多索引的列 修改值索引就要重新排序 尽量不修改带有索引的字段
-- f.避免update在where字句条件中的列 例如：update a set name='n' where name = 'a'
-- union all不去重 快一些，union去重 去重就要分类，分类要排序，排序要消耗很多资源 比如姓张的分为一类
-- 避免使用null类型，尽量使用not null类型 null对于大多数数据库都需要特殊处理 可以写0或-1，这样会在磁盘中占个位置

#尽量不使用select into,因为会导致表锁定,其他用户就不能使用此表 能用between就不要用in

1.SELECT SUM(T1.C1) FROM T1 WHERE (SELECT COUNT(*)FROM T2 WHERE T2.C2=T1.C2>0)

2.SELECT SUM(T1.C1) FROM T1WHERE EXISTS(SELECT * FROM T2 WHERE T2.C2=T1.C2)

两者产生相同的结果，但是后者的效率显然要高于前者。因为后者不会产生大量锁定的表扫描或是索引扫描。如果你想校验表里是否存在某条纪录，不要用count(*)那样效率很低，而且浪费服务器资源。可以用EXISTS代替。如： IF (SELECT COUNT(*) FROM table_name WHERE column_name = ‘xxx’)可以写成：IF EXISTS (SELECT * FROM table_name WHERE column_name = ‘xxx’)