《干货系列》SQL语句-知无不言言无不尽

1.SQL , Structure Query Language,结构化查询语言,是一种申明式的语言。

SQL包括6部分:

1.DQL(Data Query Language)数据查询语言,SELECT语句等;

2.DML(Data Manipulation Language)数据操作语言,INSERT、UPDATE、DELETE等。

3.TPL(Transaction Process language)事务处理语言,BEGIN TRASACTION , COMMIT,ROLLBACK

4.DCL(Data Control Language)数据控制语言,GRANT,REVOKE等。

5.DDL(Data Define Language)数据定义语言,CREATE TABLE ,DROP TABLE ,创建索引等。

6.CCL(Cursor Control Language)指针控制语言,DECLARECURSOR,FETCH INTO 和UPDATE WHERE CURRENT等

2.SQL语句的大小写

首先,各类数据库对SQL的大小写是不敏感的(引号内的字符串除外),Oracle会将小写的sql语句转换成大写的,其实大小写对sql的效率影响很小的,主要是业务逻辑的复杂和sql的优化。

通常为了可读性,我们建议将关键字大写,非关键字小写,table name以及field name为了可读性都建议用小写。

3.SQL语句执行顺序

SQL语句的执行顺序与语法顺序并不一致,SQL语句的语法顺序为:

SELECT [DISTINCT]

FROM

<join_type> JOIN <right_table>

ON

WHERE

GROUP BY

HAVING

UNION

ORDER BY

LIMIT

实际的执行顺序:

FROM

ON

<join_type> JOIN <right_table>

WHERE

GROUP BY

HAVING

SELECT

DISTINCT

UNION

ORDER BY

LIMIT

说明:

1.select 是在where后面执行的,所有不能在where后面使用别名,比如:

select userId as id from user where id = 1;

where后面使用select中的别名,这条语句编译将报错。

2.第一步执行的是FROM,是将数据加载到数据缓存区,以便对数据进行处理。

3.UNION在子查询语句中使用ORDER BY进行排序,但并不代表UNION后的结果集仍然有序,这个在平时的工作中应该遇到过,比如:

(SELECT * FROM user u1 ORDER BY u1.id desc)UNION(SELECT * FROM user u2 ORDER BY u2.id desc)

UNION后的结果集不一定是有序的,要想保证有序,将UNION后的的结果在进行排序。

4.笛卡尔积

在数学中,两个集合X和Y的笛卡尓积(Cartesian Product),又称直积,表示为X × Y,第一个对象是X的成员,第二个对象是Y的成员。假设集合A={a, b},集合B={0, 1, 2},则两个集合的笛卡尔积为{(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}。

mysql不支持FULL OUTER JOIN

5.索引

这个在我们平时开发中会碰到的问题。

索引可以理解为像书的目录一样,加快数据的查找,主要是为了解决当我们的书越来越厚时,我们查找一个数据的效率就会越来越耗时,建了索引可以加快查找。

现在数据库的索引实现主要有B-Tree、Hash和BitMap。BitMap索引主要适用于字段值固定以及值的区分度非常低的情况,比如性别、状态等,散列索引根据对应键的hash值来找到最终的索引项,单值查询时会比较快;最常用的B树索引,在数据库中维护一个排序的树结构(实际使用的是B树的变种B+/B-树等)。其实索引并不是建的越多越好,因为数据库其实是对索引维护了一个额外的数据结构来加快查找,如果建的索引太多肯定是有代价的,一方面增加数据库的存储空间,另一方面如果插入和修改数据比较频繁时,会花费较多的时间来重建索引。

建立索引的原则:

1.表的主键、外键必须有索引,这个大家平时都会注意

2.在经常用作过滤器的字段上建立索引

3.在SQL语句中经常进行GROUP BY、ORDER BY的字段上建立索引

4.频繁进行数据操作的表,比如INSERT,UPDATE,不要建立太多的索引,因为可能会导致索引的重建。

5.在不同值较少的字段上不必要建立索引,如性别字段

6.索引列不能参与计算,保持列“干净”。

6.SQL的优化

1.只返回需要的字段,避免SELECT*。

2.对于like查询应该尽量避免在索引过的字符数据中使用非打头字母搜索,比如:

select * from user where name like '%L%';select * from user where name substring(name,1,2) ='L';select * from user where name like 'L%'

第一条和第二条语句将导致全表的扫描,第三个查询能够使用索引来加快操作 3.应尽量避免在 WHERE 子句中对字段进行表达式操作、函数操作等(即=左边),这将导致引擎放弃使用索引而进行全表扫描。

select * from user where amount/2 = 100;

应该修改为

select * from user where amount = 100 * 2;

还有第2点的对列的操作

select * from user where name substring(name,1,2) ='L';

应该修改为:

select * from user where name like 'L%';

可以把这个原则理解为,任何对列的操作都将导致全表的扫描,操作包括函数、计算表达式等,查询时要尽可能将操作移至等号右边。

4. 能用UNION ALL 就不要使用UNION

UNION在进行表链接后会筛选掉重复的记录,所以在表链接后会对所产生的结果集进行排序运算,删除重复的记录再返回结果,所以有些不会产生重复数据的情况下,尽量使用UNION进行多个表联合。

5. NOT

NOT可用来对任何逻辑运算符号取反。NOT运算符包含在另外一个逻辑运算符中,这就是不等于(<>)运算符。换句话说,即使不在查询where子句中显式地加入NOT词,NOT仍在运算符中,比如:

select * from user where status <> 1;

这种写法可以写成:

select * from user where status > 1 and status <1;

虽然这两种查询的结果一样,但是第二种查询方案会比第一种查询方案更快些

6. 应尽量避免在 WHERE 子句中对字段进行 null 值判断 判断字段是否为空一般是不会应用索引的,因为索引是不索引空值的。不能用null作索引,任何包含null值的列都将不会被包含在索引中。即使索引有多列这样的情况下,只要这些列中有一列含有null,该列就会从索引中排除。也就是说如果某列存在空值,即使对该列建索引也不会提高性能。任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的.对于这种情况我们可以使用相同功能的运算符替换掉,比如 a is not null 改为 a>0 或a>’’等,还可以对于该字段不允许为空值,可以使用一个缺省值。

原文发布于微信公众号 - 架构师之旅(TravelWithFrame)

原文发表时间:2016-08-04

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Spark学习技巧

Hive学习之Lateral View

Lateral view与UDTF函数如explode()一起使用,UDTF对每个输入行产生0或者多个输出行。Lateral view首先在基表的每个输入行应用...

3324
来自专栏me的随笔

T-SQL基础(三)之子查询与表表达式

在嵌套查询中,最外面查询结果集返回给调用方,称为外部查询。嵌套在外部查询内的查询称为子查询,子查询的结果集供外部查询使用。

1174
来自专栏数据和云

SQL 使用like &#39;%ABC&#39; 和 like &#39;%ABC%&#39;的优

一般情况下,sql中使用col_name like 'ABC%‘的情况才能使用到col_name字段上的索引。那么如果是col_name like '%ABC%...

5898
来自专栏张俊红

数据分析系列——SQL数据库

数据分析系列——SQL数据库 总第49篇 ▼ 本文知识只是用作于常用的数据分析中,并未涉及专业数据库搭建等知识。全篇分为四个部分:初识数据库、数据库的操作、数据...

3938
来自专栏乐沙弥的世界

MongoDB 部分索引(Partial Indexes)

a、部分索引就是带有过滤条件的索引,即索引只存在与某些文档之上 b、满足过滤条件的文档在查询时,其执行计划将使用该列上的索引,否则不会被使用 c、稀疏索...

1300
来自专栏me的随笔

T-SQL基础(三)之子查询与表表达式

在嵌套查询中,最外面查询结果集返回给调用方,称为外部查询。嵌套在外部查询内的查询称为子查询,子查询的结果集供外部查询使用。

961
来自专栏C#

C#常用的IO操作方法

public class IoHelper { /// <summary> /// 判断文件是否存在 /...

25010
来自专栏浪淘沙

Mysql 基本操作

2.数据库的创建 数据库是mysql系统中管理和存储数据的仓库 1)显示所有数据库 show databases; 2)建立数据库 ...

1153
来自专栏数据库

MySQL数据库学习之两情相悦

1. MySQL的语法: 2. MySQL语法的定义顺序: (1) 指定查询的字段(2) 指定是否去重(3) 指定表名(4) 指定联表方式(5) 指定联表条件(...

2045
来自专栏醉程序

学习笔记 | MySQL 数据类型

1423

扫码关注云+社区

领取腾讯云代金券