mysql使用sql语句记录

一、基础sql语句记录

1、将查询结果插入到一个已存在的表，t_name_1表字段顺序的类型与查询结果集字段顺序类型要保持一致

insert into t_name_1(field1,field2) select field1,field2 from t_name_2;

或者如果结果集字段与目标表字段完全一致

insert into t_name_1 select field1,field2 from t_name_2;

将查询结果插入到一个新的表中

create table t_name_1 
as
select * from t_name_2;

注：mysql中不支持select into语句。

2、mysql delete语句中使用别名

语法：
delete <alias> from <table> <alias> where <alias>.<field>...
例句：
delete t from t_name t where t.id='100';

3、查询语句按条件显示字段信息：case when then …. else end as

例句：
select case when t.sex=1 then '男' when t.sex=0 then '女' else '双性' end as sex from t_user t;

使用case when then .... else end实现行转列

SELECT name ,
MAX(CASE NAME WHEN '数学' THEN score ELSE 0 END ) 数学,
MAX(CASE NAME WHEN '语文' THEN score ELSE 0 END ) 语文,
MAX(CASE NAME WHEN '英语' THEN score ELSE 0 END ) 英语 
FROM student  GROUP BY name;

4、在指定日期/时间上增加或减少一个日期/时间间隔

语法：
select DATE_ADD(date,INTERVAL expr type) from t_name t;

注：date 参数是合法的日期表达式。expr 参数是您希望添加的时间间隔，type 参数可以是下列值：

　[type值 含义 期望的expr格式]:  
　SECOND 秒 SECONDS    
　MINUTE 分钟 MINUTES    
　HOUR 时间 HOURS    
　DAY 天 DAYS    
　MONTH 月 MONTHS    
　YEAR 年 YEARS    
　MINUTE_SECOND 分钟和秒 "MINUTES:SECONDS"    
　HOUR_MINUTE 小时和分钟 "HOURS:MINUTES"    
　DAY_HOUR 天和小时 "DAYS HOURS"    
　YEAR_MONTH 年和月 "YEARS-MONTHS"    
　HOUR_SECOND 小时, 分钟， "HOURS:MINUTES:SECONDS"    
　DAY_MINUTE 天, 小时, 分钟 "DAYS HOURS:MINUTES"    
　DAY_SECOND 天, 小时, 分钟, 秒 "DAYS

例句：

mysql> SELECT "1997-12-31 23:59:59" + INTERVAL 1 SECOND;  
　　-> 1998-01-01 00:00:00   

mysql> SELECT INTERVAL 1 DAY + "1997-12-31";    
　　-> 1998-01-01    

mysql> SELECT "1998-01-01" - INTERVAL 1 SECOND;    
　　-> 1997-12-31 23:59:59    

mysql> SELECT DATE_ADD("1997-12-31 23:59:59",INTERVAL 1 SECOND);    
　　-> 1998-01-01 00:00:00    

mysql> SELECT DATE_ADD("1997-12-31 23:59:59",INTERVAL 1 DAY);    
　　-> 1998-01-01 23:59:59    

mysql> SELECT DATE_ADD("1997-12-31 23:59:59",INTERVAL "1:1" MINUTE_SECOND); 
　　-> 1998-01-01 00:01:00   

mysql> SELECT DATE_ADD("1998-01-01 00:00:00", INTERVAL "-1 10" DAY_HOUR);  
　　-> 1997-12-30 14:00:00  

mysql> SELECT DATE_SUB("1998-01-01 00:00:00",INTERVAL "1 1:1:1" DAY_SECOND);    
　　-> 1997-12-30 22:58:59  

mysql> SELECT DATE_SUB("1998-01-02", INTERVAL 1 DAY);    
　　-> 1998-01-01    

注：
DATE_ADD是加函数，DATE_SUB是减函数，如果expr的值为负数，加函数与减函数对调；
expr的值的正负以表达式整体来表示，比如：DAY_HOUR正值为 1 10，在原时间上增加1天10小时，负值为 -1 10，在原时间上减少1天10小时。

5、创建索引

建表时创建：
create table t_name(
id int(10) NOT NULL COMMENT 'id',
code varchar(10) not null comment 'code',
create_date datetime DEFAULT NULL,
primary key (id),
key index_key_code(code),
index index_key_create_date(create_date)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

创建表之后增加索引：
alter table t_name add primary key(id);
alter table t_name add unique(code);
alter table t_name add index index_key_code(code);

或者直接创建索引：
create index index_key_code on t_name(code);
create index index_key_create_date on t_name(create_date);

删除索引：
drop index index_name ON talbe_name;

alter table table_name drop index index_name;

alter talbe table_name drop primary key;

查看索引：

show index from t_name;

show keys from t_name;

二、sql语句优化

1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

2.应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

3.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num is null
可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
select id from t where num=0

4.应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num=10 or num=20
可以这样查询：
select id from t where num=10
union all
select id from t where num=20

5.应尽量避免前后模糊查询，下面的查询也将导致全表扫描：
select id from t where name like ‘%abc%’
可以这样查询：
select id from t where name like ‘abc%’;
或者可以考虑全文检索。

6.in 和 not in 也要慎用，否则会导致全表扫描，如：
select id from t where num in(1,2,3)
对于连续的数值，能用 between 就不要用 in 了：
select id from t where num between 1 and 3

7.应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：
select id from t where num/2=100
应改为:
select id from t where num=100*2

8.应尽量避免在where子句中对字段(即“=”左边)进行函数、算术运算或其他表达式运算，这将导致引擎放弃使用索引而进行全表扫描。如：
select id from t where substring(name,1,3)=’abc’–name以abc开头的id
select id from t where datediff(day,createdate,’2005-11-30’)=0–‘2005-11-30’生成的id
应改为:
select id from t where name like ‘abc%’
select id from t where createdate>=’2005-11-30’ and createdate<’2005-12-1’

9.很多时候用 exists 代替 in 是一个好的选择：
select num from a where num in(select num from b)
用下面的语句替换：
select num from a where exists(select 1 from b where num=a.num)

10.并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。

11.索引并不是越多越好，索引可以提高select效率，但同时也降低了insert及update的效率，因为insert或update时有可能会重建索引。

12.尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

13.尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

14.任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。

15.避免频繁创建和删除临时表，以减少系统表资源的消耗。

16.在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。

17.如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。