文档章节

那些年我们一起追过的稀奇古怪的SQL

zimingforever
 zimingforever
发布于 2013/02/05 20:05
字数 1529
阅读 5899
收藏 202

很久没写博客了,快过年了,今天工作比较轻松,晚上把之前的一些积累的东西整理一下

文章的题目是我的一个小分享,主要是收集了一些不太常用的sql语法。打算之后做一个oracle的姊妹篇。

ADELETE语法

1 delete可以和orderby及limit同时使用,比较适用于删除某种排序时的前若干条记录

DELETE  FROM `city` where CountryCode='AFG' ORDER BY ID limit 1;

另外这条语句锁表的话是锁全表还是只锁一行?

答案是只锁一行,验证如下:

会话1:delete from tttt order by id limit 1;
会话2:select * from tttt where id =7 for update; -->锁住
select * from tttt where id =9 for update -->没有锁住
2 delete可是适用LOW_PRIORITY关键字

如果指定LOW_PRIORITY,则DELETE的执行被延迟,直到没有其它客户端读取本表时再执行

3 delete可以使用QUICK关键字

对于MyISAM表,如果您使用QUICK关键词,则在删除过程中,存储引擎不会合并索引端结点,这样可以加快部分种类的删除操作的速度

4 delete可以使用IGNORE关键字

在删除行的过程中,IGNORE关键词会使MySQL忽略所有的错误。(在分析阶段遇到的错误会以常规方式处理。)由于使用本选项而被忽略的错误会作为警告返回。

5 如果删除了auto_increment最大的字段,那么下次再插入的时候是否会重复使用这个最大字段?

myisam及innodb不会重复适用,bdb会重新用这个字段

6 多表删除没有order by及limit的关键字

7 多表删除支持*的语法,如果删除的字段不是*,是具体的某个字段可以么?

是不可以的,是会报错的

DELETE city.CountryCode, countrylanguage.* FROM country, city, countrylanguage WHERE city.CountryCode = country.`Code` AND countrylanguage.CountryCode = country.`Code` AND country.`Code` = 'AFG'
Unknown table 'countrycode' in MULTI DELETE
B INSERT语法

1 insert可以使用DELAYED关键字

如果您使用DELAYED关键字,则服务器会把待插入的行放到一个缓冲器中,而发送INSERT DELAYED语句的客户端会继续运行。如果表正在被使用,则服务器会保留这些行。当表空闲时,服务器开始插入行,并定期检查是否有新的读取请求。如果有新的读取请求,则被延迟的行被延缓执行,直到表再次空闲时为止。

客户端使用INSERT DELAYED时,会立刻从服务器处得到一个确定。并且行被排入队列,当表没有被其它线程使用时,此行被插入。另外 INSERT DELAYED仅适用于MyISAM, MEMORY和ARCHIVE表。

与delead有关的一些参数如下:

show status like '%Delayed%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Not_flushed_delayed_rows | 0 |
+--------------------------+-------+
2 insert可以什么都不写进行插入,前提是字段不能有非空限定或者有默认值,如下的语句也是可以的,会插入一行空值
INSERT INTO tbl_name () VALUES()
3 insert中没有and关键字,但是下面的语句也不会报错,只是会有逻辑错误


insert into tttt set name='1' and name='3' ,age=3
4 insert有ON DUPLICATE KEY UPDATE的用法,如果插入行导致一个unique索引或者主键冲突而导致插入失败后,on duplicate key 后面的语句依旧会执行
insert into tttt set id=1 ,name='name3' ,age=5 
on DUPLICATE key update age=age*3;
插入时遇到id冲突 依旧会执行 age×3的操作

5 ON DUPLICATE KEY UPDATE语法支持values()函数

insert into tttt set id=1 ,name='name3' ,age=5
on DUPLICATE key update age=VALUES(id);
6 insert ...select语法没有 DELAYED关键字

C REPLACE语法

1 replace语法的流程如下:

如果表中的一个旧记录与一个用于PRIMARY KEY或一个UNIQUE索引的新记录具有相同的值,则在新记录被插入之前,旧记录被删除
尝试把新行插入到表中
当因为对于主键或唯一关键字出现重复关键字错误而造成插入失败时:
a.    从表中删除含有重复关键字值的冲突行
b.    再次尝试把新行插入到表中
2如果replace的时候同时发生了主键及唯一键冲突的时候会执行什么操作?
REPLACE into tttt set id =1 ,name='name5'
会有id和name同时冲突的话,原有的id=1的id=2,name=name5的数据被删除,只保留一条id=1 ,name=name5的数据

D SELECT语法

1 别名的AS是可以选择使用的,别名用于gruop by,order by 和having中是可以的

SELECT id as num,CONCAT(CountryCode,'*',District) AS t from city where num>6
select中的别名用于gruop by,order by 和having中,如果别名和现有字段冲突会优先使用别名
SELECT MAX(Population) as ID FROM `city` GROUP BY CountryCode HAVING id>669181;
city表中已经有了ID字段,我们强制把 population别名为ID,在having中使用ID作为分组条件,结果显示使用别名代表的字段即max(population)作为过滤条件

2 order by, group by 可以使用列位置

SELECT id  from city ORDER BY 1

3 group by也可以排序

SELECT a, COUNT(b) FROM test_table GROUP BY a DESC
4 limit可以使用OFFSET关键字
SELECT *  from city LIMIT 1 OFFSET 10;
SELECT *  from city LIMIT 10,1;
这两个语句是等价的


5 在使用行子查询的时候可以使用ROW关键字

SELECT * FROM t1 WHERE (1,2) = (SELECT column1, column2 FROM t2);
SELECT * FROM t1 WHERE ROW(1,2) = (SELECT column1, column2 FROM t2);
6 select中有SQL_CALC_FOUND_ROW关键字,做分页的时候很好用,其好处就是只查询一次数据库,效率提高了一半,其用法如下:
SELECT * FROM city;
SELECT FOUND_ROWS();
总结一下,本文列出了一些mysql中容易被忽略的语法,主要是inert,delete,replace及select,update的语法规则与前面几个相像,所以没有找到特殊的用法,本文的语法规则主要以mysql5.1为准,与5.5的版本是兼容的。


© 著作权归作者所有

zimingforever
粉丝 142
博文 266
码字总数 315040
作品 0
杭州
程序员
私信 提问
加载中

评论(8)

r
roywang
我见过最怪的SQL是,select * from t1 where id in (select id from table where id=1),作者想表达什么意思呢?
陶邦仁
陶邦仁

引用来自“xmut”的评论

这些东西有点“旁门左道”,且没有特别的效率提升,并且都有现成的解决方法,这些只属于“技术控”

比较赞同,感觉就只是针对解决某个问题的一个SQL,没有系统化,典型“技术控”
短短的歼击机
短短的歼击机
用这种sql语法的项目,基本上是一堆乱麻,看了就想吐。
xmut
xmut
这些东西有点“旁门左道”,且没有特别的效率提升,并且都有现成的解决方法,这些只属于“技术控”
王道中强流
王道中强流
学习了 group by也可以排序
常氏孤儿
常氏孤儿
楼主搞得太深入 !
FGQ
FGQ
谢谢分享,有用!
疯狂的流浪
疯狂的流浪
不错的东西,其实平时我也有收集这些东西的习惯,但没楼主深入
爱就大声说出来-源创会年度盛典祝福视频征集

OSC一路走过了六年的岁月 ,跟众多 OSCer 一起成长,OSCer 与 OSC 发生了许多“不可告人”的事情,现在我们要把所有事情透明化,SO,就有了以下的重大决定~ 本月23号 开源中国源创会千人年度...

oschina
2014/11/03
3.3K
41
[J+]移动互联网沙龙——即时通信与golang

Duang!新的一年,我们又重新踏上了新的起点,无论过去有多少遗憾,我们愿和你在未来携手同肩。J+像个孩子还在成长,我们也在不断的探索,你的参与和支持将是我们前进的最大动力。废话少说,...

缪斯的情人
2015/03/05
80
0
[J+]移动互联网沙龙——即时通信与golang

Duang!新的一年,我们又重新踏上了新的起点,无论过去有多少遗憾,我们愿和你在未来携手同肩。J+像个孩子还在成长,我们也在不断的探索,你的参与和支持将是我们前进的最大动力。废话少说,...

缪斯的情人
2015/03/05
7.4K
49
5月2日云栖精选夜读丨阿里云对象存储OSS之通过URL形式进行图片处理

阿里云对象存储服务(Object Storage Service,简称 OSS),是阿里云提供的海量、安全、低成本、高可靠的云存储服务。它具有与平台无关的RESTful API接口,能够提供99.999999999%(11个9)的...

yq传送门
2018/05/02
0
0
那些年我们一起追过的51学院

从51学院的免费到现在的会员,走过几个春秋,最开始的时候,我刚刚读大学,那时候还读大一,由于我上的是软件工程专业,正好和计算机编程打交道,最开始的时候我在网上搜索学习网站,找了好多,但是大...

上古神龙
2017/07/03
0
0

没有更多内容

加载失败,请刷新页面

加载更多

Excel创建数据透视表:优点·数据汇总速度快

· 创建数据透视表: 插入选项卡------>数据透视表 1.可自由选择区域, 可选择是展示在本表还是其他表 2. 数据透视表布局展示: 3.行标签:展示数据类 ; 计数项:如果选择区域是文本展示的是...

东方墨天
13分钟前
2
1
Linux 分区内存扩充(centos7)

我的为例: df -h 查看当前系统磁盘使用状况,发现 根(/)目录即将满盘:如下图 我要做的就是把挂载点为 / 的分区在不影响原有数据的情况下增加可用空间! 1、首先在虚拟机上扩充“物理空间...

东方神祇
昨天
1
0
docker的使用

一、起名Dockerfile没有后缀名的文件,编辑如下指令 # Pull base image FROM java:8 VOLUME /tmp# 添加 ADD spring-provider-1.0-SNAPSHOT.jar app.jarRUN bash -c 't...

BobwithB
昨天
1
0
聊聊nacos的NacosDiscoveryAutoConfiguration

序 本文主要研究一下nacos的NacosDiscoveryAutoConfiguration NacosDiscoveryAutoConfiguration nacos-spring-boot-project/nacos-discovery-spring-boot-autoconfigure/src/main/java/com/a......

go4it
昨天
10
0
如何保证消息的顺序性?

面试题 如何保证消息的顺序性? 面试官心理分析 其实这个也是用 MQ 的时候必问的话题,第一看看你了不了解顺序这个事儿?第二看看你有没有办法保证消息是有顺序的?这是生产系统中常见的问题...

米兜
昨天
18
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部