mysql使用sql语句记录

原创 2017年01月03日 16:14:41

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

Mysql 将结果保存到文件 从文件中执行sql语句 记录操作过程(tee 命令的使用)

1.  有时候我们可能需要记录我们对mysql的操作过程,这时我们可以使用mysql的tee命令              1)第一种情况是在链接数据库的时候使用tee              ...

mysql查询当天所有数据sql语句、查询数据表中的记录

用select查询数据表中的记录  select 语句的基本语法如下: select selection_list 选择那些列 from table_list 从那个表去选择行 where p...

Mysql删除多表及多表记录sql语句

Mysql删除多表及多表记录sql语句

关于mysql中删除重复记录,并保留重复数据中的一条数据的SQL语句理解

正好想写一条删除重复语句并保留一条数据的SQL,网上查了一部分资料写的很详细,但还是在这里写下自己的理解,以遍后续学习 。如下: 表字段和数据:  SQL语句:  DELETE ...
  • dsiori
  • dsiori
  • 2016年10月13日 14:30
  • 945

Hibernate SQLQuery查询返回空List,在mysql命令下直接执行sql语句可以正常得到记录

遇到的问题如标题所述。 用Hibernate的SQLQuery来list记录, 背景: 1、前一天还能正常返回数据,今天就不可以用了,代码绝对没有变动过。 2、是多表查询,且在where条件中...
  • sxl1993
  • sxl1993
  • 2016年01月05日 16:35
  • 2156

MySQL数据库中,如何记录SQL执行语句

今天有个同事让我帮忙看一张报表, 这张报表是一个开源的系统产生出来的,后台数据库用的是MySQL.问题是一张报表的数据是错误的,当时另外一张报表是对的,所以希望能够找出后台逻辑,这样可以对报表进行修改...

mysql数据库 sql语句学习笔记03 查询 聚合 表连接 记录联合

聚合     SELECT [field1,field2 , ....] function_name FROM tablename [WHERE where_contition] [GROUP BY ...

mysql 获取最新若干条记录的sql语句

获取最新10条记录的sql语句如下: select * from some_table order by id desc limit 0,10   SELECT * FROM table LIM...

mysql 的 占位符使用 以及一次执行多行sql语句

占位符的使用:drop PROCEDURE if EXISTS test12; -- 使用drop 来删除存储过程或者表create PROCEDURE test12() -- 创建存储过程 命名为t...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:mysql使用sql语句记录
举报原因:
原因补充:

(最多只允许输入30个字)