1、筛选行

尽量将select * 替换成 指定字段查询，实际工作中 将 * 替换成 数据表所有列(物理顺序排序)，或者用到哪些列

就只查那些列，从而提高查询效率。

2、创建有意义的列名

确保列名具有可读性且易于理解。

3、在 WHERE 子句中引用别名列

错误实例：select `Name` as nickname from UserInfo where nickname='malbin';

把查询包装为一个内嵌视图，就能引用别名了

正确实例：select * from (select `Name` as nickname from UserInfo) as tp where nickname='malbin';

扩展实例：select Number from (select (Age*10) as Number from UserInfo) as tp where Number>0;

select (Age*10) as Number from UserInfo where (Age*10)>0;

4、串联多列的值select concat(`Name`," age is ", Age, " years old. ") from UserInfo;

5、在 SELECT 语句里使用条件逻辑select `Name`,Age,

case when Age<=10 then concat(Age,' years old')

when Age>=30 then concat(Age,' years old')

else concat('young ',Age,' years old')

end as msg

from userinfo;

6、随机返回若干行记录select * from UserInfo order by rand() limit 1;

7、把 Null 值转换为实际值select coalesce(`Describe`,"此用户太懒") from UserInfo where `Describe` is null;

select ifnull(`Describe`,"此用户太懒") from UserInfo where `Describe` is null;

8、多字段排序select * from UserInfo order by Age desc, `Describe` asc;

解释：如果Age排序中 Age有相同的值时，则以Age分组后，根据Describe进行二次排序

9、依据字符串列的某一个字母排序select *,substr(`Name`,length(`Name`)-1,1) as lastN from UserInfo

order by substr(`Name`,length(`Name`)-1,1) asc ;

解释：substr(字符串,起始位置(从0开始),长度);

10、排序时对 Null 值的处理select * from

(

select `Name`,Age,`Describe`,

case when `Describe` is null then 0 else 1 end as describe_is_null

from UserInfo

) x order by describe_is_null desc,`Describe` asc;

解释：希望所有的 Describe=null 的数据都在数据列表的最后面或最前面,通过case添加虚拟列用来标识null数据和非null数据。

11、依据条件逻辑动态调整排序项select Id,`Name`,Age,`Describe`,

case when `Name`='balbo' then `Name` else `Describe` end as ordered

from UserInfo order by ordered;

解释：利用 case 表达式动态调整 虚拟字段的值，用虚拟字段进行排序。

12、叠加两个表的行集合select * from

(

select Id,`Name`,Age,`Describe` from UserInfo where id>0

union all

select Id,`Name`,Age,`Describe` from MemberInfo where id>0

) x order by age desc;

解释：Union因为要进行重复值扫描，所以效率低。如果合并没有刻意要删除重复行，那么就使用Union All。

13、合并相关行select u.`Name`,m.`Name` from UserInfo as u, MemberInfo as m where u.Id=m.Id;

select u.`Name`,m.`Name` from UserInfo as u inner join MemberInfo as m on u.Id=m.Id;

笛卡尔积数学概念：

笛卡尔积是指在数学中，两个集合X和Y的笛卡尓积(Cartesian product)，又称直积，表示为X × Y，第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员。

笛卡尔积又叫笛卡尔乘积，是一个叫笛卡尔的人提出来的。 简单的说就是两个集合相乘的结果。

假设集合A={a, b}，集合B={0, 1, 2}，则两个集合的笛卡尔积为{(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}。

SQL中的笛卡尔积：

我们对数据库表进行操作时，经常会对多张表进行关联，多表连接查询大家肯定不会陌生，但是一不小心很容易出来庞大冗余的数据。

CROSS JOIN 笛卡尔乘积(所有可能的行对)

FULL OUTER JOIN (MySQL不支持)可以用 LEFT OUTER OUTER 和 RIGHT OUTER OUTER中所有行的超集，用Union ALL 合并起来

14、查找两个表中相同的行select u.`Name`,m.`Name` from UserInfo as u inner join MemberInfo as m

on (u.Id=m.Id and u.Age=m.Age and u.`Describe`=m.`Describe`);

15、查找只存在于一个表的数据

查询`Name`值，UserInfo在MemberInfo中不存在的数据select * from UserInfo where `Name` not in(select `Name` from MemberInfo);

注意：如果关联表UserInfo MemberInfo 中的`Name`存在null时，则不会查出任何数据select * from UserInfo as u where not exists (select null from MemberInfo as m where m.`Name`=u.`Name`);

注意：如果关联表UserInfo MemberInfo 中的`Name`存在null时，不影响查询

16、插入默认值insert into UserRecordInfo VALUES(default,1,default,default);

insert into UserRecordInfo

(Id,UserId,ConsumptionAmount,ConsumptionDescription)

VALUES(default,1,default,default);

insert into UserRecordInfo

(UserId,ConsumptionAmount,ConsumptionDescription)

VALUES(1,100,"lunch");

17、复制数据到另一个表insert into UserInfo (`Name`,Age,`Describe`) select `Name`,Age,`Describe` from MemberInfo where Id>1;

18、复制表定义，只复制表结构不复制数据create table UserInfo_2 as select * from UserInfo where 1=0;

注意：主键和自动递增的设置不能复制

19、禁止插入特定列

实现思路：为此表创建视图，视图字段只保留需要插入数据的列，针对视图插入数据create view View_UserInfo as select `Name` from UserInfo;

insert into View_UserInfo (`Name`) values('aaaaaa');

视图插入很复杂，如果不是针对最简单的视图做插入操作，那么问题会立刻变得超级复杂。如果想使用视图插入功能，需要仔细研读和全面理解相关的数据库关于此功能的帮助文档。

20、查询数据库中的表和视图select table_name from information_schema.tables where table_schema='test';

21、查询表的字段信息select ordinal_position as 序号,column_name as 列名,data_type as 列类型,column_comment as 列注释 from information_schema.columns where table_schema='test' and table_name='UserInfo';

22、查询表的索引列表show index from userinfo;

23、查询表的约束列select tc.constraint_name,kcu.column_name,tc.constraint_type

from information_schema.table_constraints tc, information_schema.key_column_usage kcu

where tc.table_name='UserInfo' and tc.table_schema='test'

and tc.table_name=kcu.table_name and tc.table_schema=kcu.table_schema

and tc.constraint_name=kcu.constraint_name;

24、查询非索引外键，查询表的外键是否加入了索引

使用SHOW INDEX命令获取诸如索引名称、索引列和列位置序号之类的索引信息。除此之外，我们还可以通过查询INFORMATION_SCHEMA.KEY_COLUMN_USAGE列出表的外键。对于MySQL 5而言，外键虽然默认是加入索引的，但事实上却可以被删掉。要确认外键列的索引是否已经被删除，可以针对特定的表执行SHOW INDEX命令，并比较其输出结果与INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME的异同。如果KEY_COLUMN_USAGE里有对应的COLUMN_NAME，但是SHOW INDEX输出的结果里却没有，那么就说明该列没有索引。

24、用SQL生成SQLselect concat("insert into ",

_table.table_name,

"(",

group_concat("`",_column.column_name,"`" order by _column.ordinal_position asc),

") values(",

group_concat(

case when _column.column_key='PRI' then concat("default")

when (_column.data_type='bigint' or _column.data_type='int') then concat("0")

when _column.data_type='varchar' then concat("'",0,"'")

end

order by _column.ordinal_position asc

)

,");") as insert_sql

from information_schema.tables as _table

inner join information_schema.columns as _column

on (_table.table_name=_column.table_name and _table.table_schema=_column.table_schema)

where _table.table_schema='test' group by _table.table_name;

生成结果insert into memberinfo(`Id`,`Name`,`Age`,`Describe`) values(default,'0',0,'0');

insert into userinfo(`Id`,`Name`,`Age`,`Describe`) values(default,'0',0,'0');

insert into userinfo_2(`Id`,`Name`,`Age`,`Describe`) values(default,'0',0,'0');

insert into userrecordinfo(`Id`,`UserId`,`ConsumptionAmount`,`ConsumptionDescription`) values(default,0,'0');

insert into view_userinfo(`Name`) values('0');

25、遍历字符串select substr(m.`Name`,iter.pos,1) as tit from

(select `Name` from MemberInfo where id=1) as m,(select Id as pos from Table10) as iter

where iter.pos<=char_length(m.`Name`);

解决方案：

使用笛卡儿积生成以每行一个字符的形式来显示字符串所需要的行数。然后，使用数据库内置的字符串解析函数提取我们感兴趣的字符(如果是SQL Server的话，要用SUBSTRING替换SUBSTR)。要遍历一个字符串里的全部字符，关键在于要先和另一个表做连接查询，该表必须有足够多的行以保证循环操作的次数。本例使用的是Table10表，该表有10行记录(它只有一列，列名为Id，它的值分别是从1到10)。也就是说，上述查询最多返回10行。把Table10表作为一个数据透视表是常用技巧。

26、统计字符出现的次数set @title = 'abc,d,ef,123321,abc';-- 字符串

set @str = 'abc';-- 要统计出现次数的字符串

select (length(@title)-length(replace(@title,@str,'')))/length(@str) as cnt;

27、分隔数据转换为多值IN列表select * from UserInfo where Id in('1,2,3,4,5,6');

将 '1,2,3,4,5,6' 字符串拆分成 数字类型的数组 在进行in查询select * from UserInfo where Id in(

select substring_index(substring_index(list.vals,',',iter.pos),',',-1) u

from (select Id as pos from Table10) as iter, (select '1,2,3,4,5,6' as vals) as list

where iter.pos<=(length(list.vals)-length(replace(list.vals,',','')))+1

);