关闭

mysql使用sql语句记录

标签: mysqlsql
215人阅读 评论(0) 收藏 举报
分类:

一、基础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 ,这样可以避免系统表的较长时间锁定。

0
0

猜你在找
【直播】机器学习&数据挖掘7周实训--韦玮
【套餐】系统集成项目管理工程师顺利通关--徐朋
【直播】3小时掌握Docker最佳实战-徐西宁
【套餐】机器学习系列套餐(算法+实战)--唐宇迪
【直播】计算机视觉原理及实战--屈教授
【套餐】微信订阅号+服务号Java版 v2.0--翟东平
【直播】机器学习之矩阵--黄博士
【套餐】微信订阅号+服务号Java版 v2.0--翟东平
【直播】机器学习之凸优化--马博士
【套餐】Javascript 设计模式实战--曾亮
查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:642537次
    • 积分:7484
    • 等级:
    • 排名:第2860名
    • 原创:201篇
    • 转载:69篇
    • 译文:0篇
    • 评论:77条
    文章分类
    最新评论