【www.9159.com】1.     索引及查询优化,操作语言

作者: www.9159.com  发布:2019-11-20

order by子句对查询结果集进行排序

  • 多列和拼接

多列的方式就很简单了

select firstname,lastname 
from person.person
order by lastname,firstname;

这句话表示根据lastnamefirstname两列进行排序,并且是先按照lastname进行排序如果有相同的值就按照firstname进行排序。

拼接很有意思,可以写成这个样子

select 
lastname+','+firstname as fullname
from person.person
order by lastname+','+firstname;

这是把表达式放在了order by子句中了,当然也可以把order by子句中的表达式换成select子句中的别名fullname

另外还有一种写法是把列的位置写在order by子句中,还不是很了解这样写法的目的,之后应该有更加详细的讲解。

  • 可以使用case

select description,len(description) as textlength from production.productdescription where description like 'replacement%' order by case when left(Description,5)='This ' then stuff(description,1,5,'') else description end;
这句话的意思是根据description排序,case的作用是如果description值得第一个单词是This就把它截掉。

1.     索引及查询优化

 

top谓词

我印象中top的作用很小,也就是查询某一个表里的前几条数据,但是这次知道了还有percentwith ties的存在

  • percent

遇到求百分比结果集的时候可以把top和percent连用

select top(3) percent ... from ....

这样的写法是表示查询前百分之30的数据。

  • with ties

应对查询成绩前三名的同学如果简单的使用 select top(3) ... from 这样的句式其实是有问题的,如果满足条件的数据不只三条,比如说有好几个人并列第三。with ties就是来解决这个问题的。

select top(3) with ties ...from ...

这样的写法表示,如果最后一条有满足条件的数据也包含在查询结果里,所以最终查出来的结果并不一定是3条数据。

索引的类型

结构化查询语言SQL(STRUCTURED QUERY LANGUAGE)是最重要的关系数据库操作语言,并且它的影响已经超出数据库领域,

Ø 普通索引:这是最基本的索引类型,没唯一性之类的限制。

得到其他领域的重视和采用,如人工智能领域的数据检索,第四代软件开发工具中嵌入SQL的语言等。

www.9159.com,Ø 唯一性索引:和普通索引基本相同,但所有的索引列值保持唯一性。

什么是 SQL?

  • SQL 指结构化查询语言
  • SQL 使我们有能力访问数据库
  • SQL 是一种 ANSI 的标准计算机语言

Ø 主键:主键是一种唯一索引,但必须指定为”PRIMARY KEY”。

SQL 能做什么?

  • SQL 面向数据库执行查询
  • SQL 可从数据库取回数据
  • SQL 可在数据库中插入新的记录
  • SQL 可更新数据库中的数据
  • SQL 可从数据库删除记录
  • SQL 可创建新数据库
  • SQL 可在数据库中创建新表
  • SQL 可在数据库中创建存储过程
  • SQL 可在数据库中创建视图
  • SQL 可以设置表、存储过程和视图的权限

 SQL 对大小写不敏感!

 

sql语法

可以把 SQL 分为两个部分:数据操作语言 (DML) 和 数据定义语言 (DDL)。

查询和更新指令构成了 SQL 的 DML 部分:

SELECT - 从数据库表中获取数据
UPDATE - 更新数据库表中的数据
DELETE - 从数据库表中删除数据
INSERT INTO - 向数据库表中插入数据

 

简单介绍一下数据操作语言:

下面的例子是一个名为 "Persons" 的表:

Id LastName FirstName Address City
1 Adams John Oxford Street London
2 Bush George Fifth Avenue New York
3 Carter Thomas Changan Street Beijing

 

注意:

SQL 使用单引号来环绕文本值(大部分数据库系统也接受双引号)。如果是数值,不要引号。

基本语句

1、避免重复:distinct

eg:select distinct lastname from Persons

2、限制条件 where

eg:select * from Persons where lastname=‘Bush’

结果是

Id LastName FirstName Address City
 2           Bush         George Fifth Avenue New York

下面的运算符可在 WHERE 子句中使用:

操作符 描述
= 等于
<> 不等于
> 大于
< 小于
>= 大于等于
<= 小于等于
BETWEEN 在某个范围内
LIKE 搜索某种模式

3、AND 和 OR 运算符

AND 和 OR 可在 WHERE 子语句中把两个或多个条件结合起来。

eg:

select * from Persons where fristname='Thomas' and lastname='Carter'

我们也可以把 AND 和 OR 结合起来(使用圆括号来组成复杂的表达式):

select * from Persons where (FirstName='Thomas' OR 

firstName='William')and LastName='Carter'

4、order  by

用于对结果集进行排序。默认按照升序对记录进行排序。 降序DESC 

eg:select  *  from Persons  order by  lastname   desc,firstname asc  按lastname降序,按firstname升序

 

5、insert  into 

用于向表格中插入新的行。

eg:insert  into  Persons values ('Gates', 'Bill', 'Xuanwumen 10', 'Beijing')

也可用于所要插入数据的列

eg:insert  into Persons (LastName, Address) values ('Wilson', 'Champs-Elysees')

6、update

修改表中的数据(注意set关键字)

eg:update Person set firstname = 'Fred' where lastname = 'Wilson' 

更新某一行中的一个列

update Person set address = 'Zhongshan 23', city = 'Nanjing'  where 

lastname = 'Wilson'

Ø 全文索引:MYSQL从3.23.23开始支持全文索引和全文检索。在MYSQL中,全文索引的索引类型为FULLTEXT。全文索引可以在VARCHAR或者TEXT类型的列上创建。

更新某一行中的若干列

7、DELETE 

用于删除表中的行。

eg:delete from Persons where lastname=‘Wilson’删除某行

 

 loading......

 

大多数MySQL索引(PRIMARY KEY、UNIQUE、INDEX和FULLTEXT)使用B树中存储。空间列类型的索引使用R-树,MEMORY表支持hash索引。

单列索引和多列索引(复合索引)

索引可以是单列索引,也可以是多列索引。对相关的列使用索引是提高SELECT操作性能的最佳途径之一。

多列索引:

MySQL可以为多个列创建索引。一个索引可以包括15个列。对于某些列类型,可以索引列的左前缀,列的顺序非常重要。

多列索引可以视为包含通过连接索引列的值而创建的值的排序的数组。一般来说,即使是限制最严格的单列索引,它的限制能力也远远低于多列索引。

最左前缀

多列索引有一个特点,即最左前缀(Leftmost Prefixing)。假如有一个多列索引为key(firstname lastname age),当搜索条件是以下各种列的组合和顺序时,MySQL将使用该多列索引:

firstname,lastname,age

firstname,lastname

firstname

也就是说,相当于还建立了key(firstname lastname)和key(firstname)。

索引主要用于下面的操作:

Ø 快速找出匹配一个WHERE子句的行。

Ø 删除行。当执行联接时,从其它表检索行。

Ø 对具体有索引的列key_col找出MAX()或MIN()值。由预处理器进行优化,检查是否对索引中在key_col之前发生所有关键字元素使用了WHERE key_part_# = constant。在这种情况下,MySQL为每个MIN()或MAX()表达式执行一次关键字查找,并用常数替换它。如果所有表达式替换为常量,查询立即返回。例如:

SELECT MIN(key2), MAX (key2)  FROM tb WHERE key1=10;

Ø 如果对一个可用关键字的最左面的前缀进行了排序或分组(例如,ORDER BY key_part_1,key_part_2),排序或分组一个表。如果所有关键字元素后面有DESC,关键字以倒序被读取。

Ø 在一些情况中,可以对一个查询进行优化以便不用查询数据行即可以检索值。如果查询只使用来自某个表的数字型并且构成某些关键字的最左面前缀的列,为了更快,可以从索引树检索出值。

SELECT key_part3 FROM tb WHERE key_part1=1

有时MySQL不使用索引,即使有可用的索引。一种情形是当优化器估计到使用索引将需要MySQL访问表中的大部分行时。(在这种情况下,表扫描可能会更快些)。然而,如果此类查询使用LIMIT只搜索部分行,MySQL则使用索引,因为它可以更快地找到几行并在结果中返回。

合理的建立索引的建议:

(1)  越小的数据类型通常更好:越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间,处理起来更快。

(2)  简单的数据类型更好:整型数据比起字符,处理开销更小,因为字符串的比较更复杂。在MySQL中,应该用内置的日期和时间数据类型,而不是用字符串来存储时间;以及用整型数据类型存储IP地址。

(3)  尽量避免NULL:应该指定列为NOT NULL,除非你想存储NULL。在MySQL中,含有空值的列很难进行查询优化,因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值

这部分是关于索引和写SQL语句时应当注意的一些琐碎建议和注意点。

1.当结果集只有一行数据时使用LIMIT 1

2.避免SELECT *,始终指定你需要的列

从表中读取越多的数据,查询会变得更慢。他增加了磁盘需要操作的时间,还是在数据库服务器与WEB服务器是独立分开的情况下。你将会经历非常漫长的网络延迟,仅仅是因为数据不必要的在服务器之间传输。

3.使用连接(JOIN)来代替子查询(Sub-Queries)

连接(JOIN).. 之所以更有效率一些,是因为MySQL不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。

4.使用ENUM、CHAR而不是VARCHAR,使用合理的字段属性长度

5.尽可能的使用NOT NULL

6.固定长度的表会更快

7.拆分大的DELETE或INSERT语句

8.查询的列越小越快

Where条件

在查询中,WHERE条件也是一个比较重要的因素,尽量少并且是合理的where条件是很重要的,尽量在多个条件的时候,把会提取尽量少数据量的条件放在前面,减少后一个where条件的查询时间。

有些where条件会导致索引无效:

Ø where子句的查询条件里有!=,MySQL将无法使用索引。

Ø where子句使用了Mysql函数的时候,索引将无效,比如:select * from tb where left(name, 4) = ‘xxx’

Ø 使用LIKE进行搜索匹配的时候,这样索引是有效的:select * from tbl1 where name like ‘xxx%’,而like ‘%xxx%’ 时索引无效

本文由9159.com发布于www.9159.com,转载请注明出处:【www.9159.com】1.     索引及查询优化,操作语言

关键词:

上一篇:没有了
下一篇:没有了