|
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生成SQL select 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 ); |