日常收集整理常见的mysql sql技巧

废话不多说了,直接给大家贴代码了。

1,数字辅助表

//创建表
create table test(id int unsigned not null primary key);
delimiter //
create procedure pnum(cnt int unsigned)
begin
declare i int unsigned default 1;
insert into num select i;
while i*2 < cnt do
insert into num select i+id from num ;
set i=i*2;
end while;
end
//
delimiter ;
#####列值不连续问题:
表a中id值为1,2,3,100,101,110,111
set @q=0;
select id,@q:=@q+1 as cn from a;
#####对不连续的进行分组
set @a=0;
select min(id) as start_v,max(id) as end_v from (
select id,cn,id-cn as diff from (
select id,@a:=@a+1 as cn from pi) as p ) as pp group by diff;
#####对不连续的值填充
use test;
DROP TABLE if EXISTS pincer;
create table pincer(a int UNSIGNED);
insert into pincer values(1),(2),(5),(100),(101),(103),(104),(105);
select a+1 as start ,(select min(a)-1 from pincer as ww where ww.a>qq.a) as end from pincer as qq where
not exists (select * from pincer as pp where qq.a+1=pp.a)
and a<(select max(a) from pincer);
################
select id,num,ranknum,diff from (select id,num,ranknum,num-ranknum as diff from (select id,num,if(@id=id,@rownum:=@rownum+1,@rownum:=1) ranknum,@id:=id from tt,(select @rownum:=0,@id:=null) a ) b) c group by id,diff having count(*)>=2;
################

2,生日问题

select name,birthday,if(cur>today,cur,next) as birth_day
from(
select name,birthday,today,date_add(cur,interval if(day(birthday)=29 && day(cur)=28,1,0) day)as cur, date_ad(next,interval if(day(birthday)=29 && day(next)=28,1,0) day) as next
from(
select name,birthday,today,
  date_add(birthday,interval diff year) as cur,
  date_add(birthday,interval diff+1 year) as next,
from(
select concat(laster_name,'',first_name) as name,
  birth_date as birthday,
  (year(now())-year(birth_date) )as diff,
  now() as today
  from employees) as a
) as b
) as c

3,日期问题----计算工作日

create table sals(id int ,date datetime ,cost int,primary key(id);
select date_add('1900-01-01',
            interval floor(datediff(date,'1900-01-01')/7)*7 day)
            as week_start,
            date_add('1900-01-01',
            interval floor(datediff(date,'1900-01-01')/7*7+6 day)
            as week_end,
            sum(cost) from sales;
计算工作日(指定2个日期段 有多少工作日)
create procedure pgetworkdays (s datetime,e datetime)
begin
select floor(days/7)*5+days%7
case when 6 between wd and wd+days%7-1 then 1 else 0 end
case then 7 between wd and wd+days%7-1 then 1 else 0 end
from
(select datediff(e,s)+1 as days,weekday(s)+1 as wd) as a;
end;

mysql sql语句大全

1、说明:创建数据库
CREATE DATABASE database-name

2、说明:删除数据库

drop database dbname

3、说明:备份sql server
--- 创建 备份数据的 device
USE master
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat'
--- 开始 备份
BACKUP DATABASE pubs TO testBack

4、说明:创建新表

create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)
根据已有的表创建新表:
A:create table tab_new like tab_old (使用旧表创建新表)
B:create table tab_new as select col1,col2… from tab_old definition only

5、说明:删除新表

drop table tabname

6、说明:增加一个列
Alter table tabname add column col type
注:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。

7、说明:添加主键: Alter table tabname add primary key(col)
说明:删除主键: Alter table tabname drop primary key(col)

8、说明:创建索引:create [unique] index idxname on tabname(col….)

删除索引:drop index idxname

注:索引是不可更改的,想更改必须删除重新建。

9、说明:创建视图:create view viewname as select statement

删除视图:drop view viewname

10、说明:几个简单的基本的sql语句
选择:select * from table1 where 范围
插入:insert into table1(field1,field2) values(value1,value2)
删除:delete from table1 where 范围
更新:update table1 set field1=value1 where 范围
查找:select * from table1 where field1 like '%value1%' ---like的语法很精妙,查资料!
排序:select * from table1 order by field1,field2 [desc]
总数:select count as totalcount from table1
求和:select sum(field1) as sumvalue from table1
平均:select avg(field1) as avgvalue from table1
最大:select max(field1) as maxvalue from table1
最小:select min(field1) as minvalue from table1

11、说明:几个高级查询运算词
A: UNION 运算符
UNION 运算符通过组合其他两个结果表(例如 TABLE1 和 TABLE2)并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时(即 UNION ALL),不消除重复行。两种情况下,派生表的每一行不是来自 TABLE1 就是来自 TABLE2。
B: EXCEPT 运算符
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL),不消除重复行。
C: INTERSECT 运算符
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL),不消除重复行。
注:使用运算词的几个查询结果行必须是一致的。

12、说明:使用外连接

A、left (outer) join:
左外连接(左连接):结果集几包括连接表的匹配行,也包括左连接表的所有行。
SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
B:right (outer) join:
右外连接(右连接):结果集既包括连接表的匹配连接行,也包括右连接表的所有行。
C:full/cross (outer) join:
全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。
12、分组:Group by:
  一张表,一旦分组完成后,查询后只能得到组相关的信息。
 组相关的信息:(统计信息) count,sum,max,min,avg  分组的标准)
    在SQLServer中分组时:不能以text,ntext,image类型的字段作为分组依据
 在selecte统计函数中的字段,不能和普通的字段放在一起;

13、对数据库进行操作:

分离数据库: sp_detach_db; 附加数据库:sp_attach_db 后接表明,附加需要完整的路径名

14.如何修改数据库的名称:

sp_renamedb 'old_name', 'new_name'

时间: 2015-11-30

MySQL中视图的使用及多表INNER JOIN的技巧分享

创建视图   Sql代码 CREATE VIEW view_name AS SELECT t1.xxx, t2.xxx, t3.xxx FROM (table1 t1 INNER JOIN table2 t2 ON t1.fid = t2.fid) INNER JOIN table3 t3 ON t1.mid = t3.mid; 这里使用了3表关联,对于多表关联的 INNER JOIN 写法有一个技巧 1. 先写最简单的2表关联 INNER JOIN 2. 然后使用 () 从 FROM 之后到语

mysql技巧之select count的区别分析

1.测试环境 OS:LinuxDB:mysql-5.5.18table:innodb存储引擎 表定义如下: 2. 测试场景与分析[统计表group_message的记录数目] (1)select count(*)方式 (2)select count(1)方式 (3)select count(col_name)方式 分别使用 select count(group_id) select count(user_id) select count(col_null) 通过上述测试结果可以看到,select

mysql技巧:提高插入数据(添加记录)的速度

问题描述: 普通台式机,采集数据,表中已经有>1000万数据量. 采集回来的数据插入表中的时候很慢,每条约100毫秒. 解决方法: 1.加大mysql配置中的bulk_insert_buffer_size,这个参数默认为8M bulk_insert_buffer_size=100M 2.改写所有insert语句为insert delayed 这个insert delayed不同之处在于:立即返回结果,后台进行处理插入. 还有一个技巧是在一跳insert中插入多条数据,类似insert into

php导入大量数据到mysql性能优化技巧

本文实例讲述了php导入大量数据到mysql性能优化技巧.分享给大家供大家参考.具体分析如下: 在mysql中我们结合php把一些文件导入到mysql中,这里就来分享一下我对15000条记录进行导入时分析与优化,需要的朋友可以参考一下. 之前有几篇文章,说了最近tiandi在帮朋友做一个小项目,用于统计电话号码的,每次按需求从数据库里随机生成打包的电话号码,然后不停地让人打这些电话号码推销产品(小小鄙视一下这样的行为).但是朋友要求帮忙,咱也不能不帮啊,是吧.程序两个星期前已经做好,测试完毕交工

MySQL索引类型总结和使用技巧以及注意事项

在数据库表中,对字段建立索引可以大大提高查询速度.假如我们创建了一个 mytable表: 复制代码 代码如下: CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL  ); 我们随机向里面插入了10000条记录,其中有一条:5555, admin. 在查找username="admin"的记录 SELECT * FROM mytable WHERE username='admin';时,如果在

分析MySQL中优化distinct的技巧

有这样的一个需求:select count(distinct nick) from user_access_xx_xx; 这条sql用于统计用户访问的uv,由于单表的数据量在10G以上,即使在user_access_xx_xx上加上nick的索引, 通过查看执行计划,也为全索引扫描,sql在执行的时候,会对整个服务器带来抖动: root@db 09:00:12>select count(distinct nick) from user_access; +--------+ | count(dis

MySQL中的binlog相关命令和恢复技巧

操作命令: 复制代码 代码如下: show binlog events in 'mysql-bin.000016' limit 10; reset master 删除所有的二进制日志flush logs  产生一个新的binlog日志文件 show master logs; 或者 show binary logs; 查看二进制文件列表和文件大小 复制代码 代码如下: ./mysqlbinlog --start-datetime="2012-05-21 15:30:00" --stop-

教你如何快捷的使用cmd访问mysql小技巧

以window7为例,右击"计算机" - 单击"属性" - 单击"高级系统设置" - 单击"环境变量",剩下看图: <图1> 右下角"环境变量". <图2>选择"PATH",  编辑. <图3>变量值是你所安装开发环境里mysql的命令目录所在地址, 确定. OK,到此配置结束,进入cmd后,可以方便使用mysql命令了: mysql -u用户 -p密

101个MySQL优化技巧和提示

MySQL是一个功能强大的开源数据库.随着越来越多的数据库驱动的应用程序,人们一直在推动MySQL发展到它的极限.这里是101条调节和优化MySQL安装的技巧.一些技巧是针对特定的安装环境的,但这些思路是通用的.我已经把他们分成几类,来帮助你掌握更多MySQL的调节和优化技巧. MySQL 服务器硬件和操作系统调节: 1. 拥有足够的物理内存来把整个InnoDB文件加载到内存中--在内存中访问文件时的速度要比在硬盘中访问时快的多. 2. 不惜一切代价避免使用Swap交换分区 – 交换时是从硬盘读

解析MySQL数据库性能优化的六大技巧

数据库表表面上存在索引和防错机制,然而一个简单的查询就会耗费很长时间.Web应用程序或许在开发环境中运行良好,但在产品环境中表现同样糟糕.如果你是个数据库管理员,你很有可能已经在某个阶段遇到上述情况.因此,本文将介绍对MySQL进行性能优化的技巧和窍门. 1.存储引擎的选择如果数据表需要事务处理,应该考虑使用InnoDB,因为它完全符合ACID特性.如果不需要事务处理,使用默认存储引擎MyISAM是比较明智的.并且不要尝试同时使用这两个存储引擎.思考一下:在一个事务处理中,一些数据表使用Inno

Mysql数据库性能优化一

今天,数据库的操作越来越成为整个应用的性能瓶颈了,这点对于Web应用尤其明显.关于数据库的性能,这并不只是DBA才需要担心的事,而这更是我们程序员需要去关注的事情.当我们去设计数据库表结构,对操作数据库时(尤其是查表时的SQL语句),我们都需要注意数据操作的性能.这里,我们不会讲过多的SQL语句的优化,而只是针对MySQL这一Web应用最多的数据库. mysql的性能优化无法一蹴而就,必须一步一步慢慢来,从各个方面进行优化,最终性能就会有大的提升. Mysql数据库的优化技术 对mysql优化是

Mysql数据库性能优化三(分表、增量备份、还原)

接上篇Mysql数据库性能优化二 对表进行水平划分     如果一个表的记录数太多了,比如上千万条,而且需要经常检索,那么我们就有必要化整为零了.如果我拆成100个表,那么每个表只有10万条记录.当然这需要数据在逻辑上可以划分.一个好的划分依据,有利于程序的简单实现,也可以充分利用水平分表的优势.比如系统界面上只提供按月查询的功能,那么把表按月拆分成12个,每个查询只查询一个表就够了.如果非要按照地域来分,即使把表拆的再小,查询还是要联合所有表来查,还不如不拆了.所以一个好的拆分依据是 最重要的

Mysql数据库性能优化之子查询

记得在做项目的时候, 听到过一句话, 尽量不要使用子查询, 那么这一篇就来看一下, 这句话是否是正确的. 那在这之前, 需要介绍一些概念性东西和mysql对语句的大致处理. 当Mysql Server的连接线程接收到Client发送过来的SQL请求后, 会经过一系列的分解Parse, 进行相应的分析, 然后Mysql会通过查询优化器模块, 根据该Sql所涉及到的数据表的相关统计信息进行计算分析. 然后在得出一个Mysql自认为最合理最优化的数据访问方式, 也就是我们常说的"执行计划",

Mysql数据库性能优化二

在上篇文章给大家介绍了mysql数据库性能优化一,今天继续接着上篇文章给大家介绍数据库性能优化相关知识.具体内容如下所示: 建立适当的索引 说起提高数据库性能,索引是最物美价廉的东西了.不用加内存,不用改程序,不用调sql,只要执行个正确的'create index',查询速度就可能提高百倍千倍,这可真有诱惑力.可是天下没有免费的午餐,查询速度的提高是以插入.更新.删除的速度为代价的,这些写操作,增加了大量的I/O. 是不是建立一个索引就能解决所有的问题?ename上没有建立索引会怎样? sel

MySQL性能优化的一些技巧帮助你的数据库

你完成了你的品牌新的应用程序,一切工作就像一个魅力.用户来使用你的网络.每个人是幸福的. 然后,突然间,一个大爆发的用户杀死你的MySQL服务器,您的网站已关闭.出了什么问题?你怎么能阻止它吗? 以下是MySQL性能优化的一些技巧,将帮助你,帮助你的数据库. 大处着眼 在早期的发展阶段,你应该知道预期到您的应用程序的用户数.如果你希望很多用户来说,你应该想想大,从一开始,计划进行复制,可扩展性和性能. 但是,如果你优化你的SQL代码,架构和索引策略,也许你不会需要大环境.你必须总是三思而后行的性

Oracle数据库性能优化技术开发者网络Oracle

正在看的ORACLE教程是:Oracle数据库性能优化技术开发者网络Oracle.介绍:细处着手,巧处用功.高手和菜鸟之间的差别就是:高手什么都知道,菜鸟知道一些.电脑小技巧收集最新奇招高招,让你轻松踏上高手之路.  摘要: Oracle数据库是当前应用最广泛的大型数据库之一,而其性优化直接关系到系统的运行效率.本文以数据库性能优化的基本原则为出发点,阐述了在数据库设计阶段如何避免竞争和如何优化数据访问,在数据库运行阶段如何从操作系统和数据库实例级别上调整内存和I/O来达到数据库性能优化的各种技

实例分析ORACLE数据库性能优化

ORACLE数据库的优化方式和MYSQL等很大的区别,今天通过一个ORACLE数据库实例从表格.数据等各个方便分析了如何进行ORACLE数据库的优化. tsfree.sql视图 这个sql语句迅速的对每一个表空间中的空间总量与每一个表空间中可用的空间的总量进行比较 表空间是数据库的逻辑划分,一个表空间只能属于一个数据库.所有的数据库对象都存放在指定的表空间中.但主要存放的是表, 所以称作表空间. SELECT FS.TABLESPACE_NAME "Talbspace", (DF.TO

SQL Server数据库性能优化技术第1/2页

设计1个应用系统似乎并不难,但是要想使系统达到最优化的性能并不是一件容易的事.在开发工具.数据库设计.应  用程序的结构.查询设计.接口选择等方面有多种选择,这取决于特定的应用需求以及开发队伍的技能.本文以SQL  Server为例,从后台数据库的角度讨论应用程序性能优化技巧,并且给出了一些有益的建议. 1 数据库设计  要在良好的SQL Server方案中实现最优的性能,最关键的是要有1个很好的数据库设计方案.在实际工作中,许多SQL  Server方案往往是由于数据库设计得不好导致性能很差.

mysql服务性能优化—my.cnf_my.ini配置说明详解(16G内存)

此配置是老男孩生产线上使用的配置,在培训的时候,他给的,我在这里,对各参数添加了中文说明 这配置已经优化的不错了,如果你的mysql没有什么特殊情况的话,可以直接使用该配置参数 MYSQL服务器my.cnf配置文档详解 硬件:内存16G [client] port = 3306 socket = /data/3306/mysql.sock [mysql] no-auto-rehash [mysqld] user = mysql port = 3306 socket = /data/3306/my