T-SQL:SQL语句处理顺序的坑(四)

首先看下面一条比较完成语句,都是比较常见的关键字。

SQL 必知必会

在mac终端操作sqlite:

  • cd 数据库所在的目录
  • sqlite3 数据库文件名 //带后缀)(此时已经打开数据库)
  • .tables //显示数据库中所有已经创建的表
  • .schema //显示所有表的模式
  • .headers on //显示字段名(查询时才会显示)
  • .mode column/list/line
  • 执行sql语句必须在末尾加分号
USE Temp;

SELECT empid, YEAR(orderdate) AS orderyear, COUNT(*) AS numorders
FROM Sales.Orders
WHERE custid = 71
GROUP BY empid, YEAR(orderdate)
HAVING COUNT(*) > 1
ORDER BY empid, orderyear;

distinct

 SELECT DISTINCT name FROM TB_BOOK_TAG;

关键字distinct,用于去除name列中所有行中重复元素。

我们来详细分析一下sql语句的逻辑处理顺序,虽然select在每条语句的第一位,但实际上它是被最后才处理的

limit

SELECT name FROM TB_BOOK_TAG LIMIT 5;

关键字limit,返回name列指定行数。

SELECT name FROM TB_BOOK_TAG LIMIT 5 OFFSET 0;
等同于下面写法(shortcut)
SELECT name FROM TB_BOOK_TAG LIMIT 0,5;

1.from  

limit ... offset ...

关键字LIMIT ... OFFSET ...,limit后跟的数字指定显示多少行,offset后跟的数字表示从什么位置开始。(0是第一行)

2.where

注释

 --this is a comment

关键--加注释,单行注释。

 /* comments */

关键/**/,多行注释。

3.group by

order by

 SELECT * FROM TB_BOOK_TAG ORDER BY name;

关键字:order by 字段名,按该字段所属列的首字母进行排序。要确保该子句是select语句中最后一条子句,否则会出现错误。

 SELECT publisher,pubdate FROM TB_BOOK_ENTITY ORDER BY publisher,pubdate;

关键字:order by 字段名 字段名,首先按publisher进行排序,然后按照pubdate进行排序。对于第二个字段的排序,当且仅当具有多个相同的publisher时才会对其按照pubdate进行排序,如果publisher列中所有值都是唯一的,则不会按pubdate进行排序。

4.having

desc

SELECT publisher,pubdate FROM TB_BOOK_ENTITY ORDER BY pubdate DESC;

关键字:desc,order by 默认是按升序进行排序,当在字段名后加desc后,将对该字段进行降序排列。

SELECT pubdate,price FROM TB_BOOK_ENTITY ORDER BY pubdate DESC,price;

pubdate按降序排列,price,仍然按照升序排列(在pubdate相同的行)。所以,如果想在多个列上进行降序,必须对每一列都指定desc关键字。

5.select

where

SELECT * FROM TB_BOOK_TAG WHERE count = 1;

关键字:where,指定搜索条件进行过滤。where子句在表名(from子句)之后给出。在同时使用whereorder by时,应该让order by位于where之后。

操作符 说明
= 等于
<> 不等于
!= 不等于
< 小于
<= 小于等于
!< 不小于
> 大于
>= 大于等于
!> 不大于
BETWEEN 在指定的两个值之间
IS NULL 为NULL值

注意:NULL和非匹配
通过过滤选择不包含(如<>)指定值的所有行时,你可能希望返回含NULL值的行,但是这做不到,因为NULL有特殊的含义,数据库不知道它们是否匹配,所以在进行匹配过滤或非匹配过滤时,不会返回这些结果。

6.order by

where...and...

select * from contacts where name = "fff" and mobile = "d";

关键字:and,组合where子句。

7.TOP

where...or...

select * from contacts where name = "fff" or mobile = "d";

关键字:or,组合where子句。

注意:在同时使用and和or时要注意求值顺序,and优先级大于or。因此在任何时候使用具有and和or操作符的where子句时,都应该使用圆括号明确地分组操作符

在仔细分析每个执行顺序代表的意思 (它的实际顺序)

where...in...

select * from contacts where mobile in ('12', '444') order by mobile;

关键字:in,用来指定条件范围,范围中的每个条件都可以进行匹配。in操作符一般比一组or操作符执行的更快。in最大的优点是可以包含其他select语句,能够更动态的建立where子句。

FROM Sales.Orders
WHERE custid = 71
GROUP BY empid, YEAR(orderdate)
HAVING COUNT(*) > 1
SELECT empid, YEAR(orderdate) AS orderyear, COUNT(*) AS numorders
ORDER BY empid, orderyear;

not

select * from contacts where not mobile = '12';

关键字:not,where子句中用来否定其后条件的关键字。上面的例子也可以用<>。在简单语句中,not没有什么优势,但是,在更复杂的子句中,not非常有用。例如,在与in操作符联合使用时,not可以非常简单的找出与条件列表不匹配的行。如下例子:

 SELECT * FROM CONTACTS WHERE NOT mobile IN ('111111', '3333');

1.从 Orders 表查询数据

like

通配符(wildcard)用来匹配值的一部分的特殊字符。
搜索模式(search pattern)由字面值,通配符或两者组合构成的搜索条件。

通配符搜索只能用于文本字段(字符串),非文本数据类型字段不能使用通配符搜索

2.根据条件筛选客户ID等于71的

%通配符

在搜索字符串中,%表示任何字符出现任意次数

select * from tb_book_tag where name like '计算机%';

注意字符串后面所跟的空格:
许多DBMS会用空格来填补字段内容。例如,如果某列有50个字符,而存储文本为Fish bean bag toy(17个字符),则为填满该列会在文本末尾追加33个空格。如果此时用‘F%y’来检索,便检索不到上述字符串。简单解决办法是‘F%y%’。更好的解决办法是用函数去掉空格。

'%' 不会匹配为NULL的行

3.对客户id和订单年度 进行分组

下划线_通配符

用途和%一样,但它只匹配单个字符,而不是多个。

select * from tb_book_tag where name like '计算机__';

使用通配符的技巧

SQL通配符搜索比其他搜索更耗时。

1. 不要过度使用通配符,如果其他操作能达到目的,使用其他操作。
2. 在确实需要使用的时候,也尽量不要把它用在搜索模式的开始处。把通配符置于开始处,搜索起来是最慢的。
3. 特别要注意通配符的位置不要放错。
  1. 再选出大于一个订单的组

创建计算字段

计算字段并不实际存在于数据库表中,计算字段是运行时在SELECT语句内创建的。

select rtrim('~    ') || name from tb_book_tag;

关键字:||rtrim()||拼接操作符。rtrim()去除文本右边的空格。trim()去除两边的空格。

5.返回查询出的数据 以及你要展示的字段

as

select name || 'is foolish' as title from contacts;

关键字:as,全称alias。它指示SQL创建一个包含指定计算结果的名为title的计算字段,任何客户端应用可以按名称引用这个列,就像一个实际表列一样。

6.最终对客户id 和订单 进行排序

执行算术计算

7.输出

- * /

select mobile, (mobile   1)*2 as count_mobile from contacts;

关键字: -*/

输入的键入顺序和处理顺序不一致是有原因的,SQL设计师是为了让用户按照英文的方式提供自己的请求

函数

一下所用到的是适用于sqlite的函数,不一定适用于其他DBMS。

建议、坑

upper()

select name ,upper(name) as name_upper from contacts;

关键字:upper()转大写

sqlite中常用于文本处理函数:

函数 说明
length() 返回字符串的长度
lower() 将字符串转小写
ltrim() 去掉字符串左边的空格
rtrim() 去掉字符串右边的空格
upper() 将字符串转大写
  1. from 表时  最好给定 库名和表名  Sales.Orders  让表显示表示 不用程序检索。

avg()

select avg(mobile) as avg_id from contacts;

关键字:avg(),对表中某列所有行或特定行中的数据求平均值。该函数会忽略值为NULL的行。

  1. where 子句相当重要  SQL Server 会对where 条件 进行评估访问请求数据要使用的索引,通过索引可以大大减少表扫描时间

count()

select count(*) as num_cust from contacts;

select count(name) as num_name from contacts;

关键字:count(),使用count(*),对表中行的数目进行计数,不管表列中是否包含NULL值。使用count(column_name),对特定列中具有值的行进行计数,忽略NULL值。

同时 where 子句检索 完成后  它返回的是检索结果为True的行  ,但始终记住, SQL 数据库使用三值谓词逻辑,也就是说有三个结果。

sum()

select sum(mobile) as sum_mobile from contacts;

关键字:sum(), 忽略NULL值

True,False 或 UNKNOWN ,  返回true 行 并不等同于 不返回False  实际上是不返回 False 行 和 UNKNOWN 行 以后会再博客中专门讲NULL。

聚集不同值

3.记住除count(*)之外,  聚合函数都是忽略NULL标记  如果有一组数据“1,1,3,4,5,null”列名为qty   表达式Count(*) 返回的是6 但是 Count(qty)

count(distinct name)

select count(distinct name) from tb_book_tag;

是5  count中给定显示值 就会默认寻找已知值  也可以  count(distinct qty ) 返回的是4 去重复  这个 可以用来 处理  返回每个不重复统计问题很方便 它和 select distinct 有很大性能区别 以后会细讲 也可以 sum(distinct qty ) 是13 也是用作统计不重复数据。

组合聚集函数

select count(*) as num_items, min(count) as count_min, max(count) as count_max, avg(count) as count_avg from tb_book_tag;

4.因为 group by 属于行处理 在having 先计算所以having 中可以出现  聚合函数 。

分组数据

5.像上面的 “YEAR(orderdate)” SQL Server 只对它运行一次  能识别查询中重复使用的相同表达式

group by

select name, count(*) as num_names from tb_book_tag group by name order by name;

关键字:group by,group by子句必须出现在where子句之后,order by子句之前。

6.最好别使用 select * 尽管你要查询 所有字段。

group by...having...

select name , count(*) as amounts from tb_book_tag group by name having amounts >= 10;

关键字:having。对分组进行过滤。而where对分组不起作用,它是针对表中每一行来过滤。

7.使用 order by 对有大量重复的字段进行排序是无效的  例如对日期进行排序 这样一个排序选10条 会有多个被认为是对的结果 所以我们要确保排序字段的数据唯一性, 以及在 select distinct  时 排序 会导致 单个结果对应多个源数据行。

使用子查询

select cust_id 
from orders 
where order_num in (select order_num 
                     from orderitems
                     where prod_id = 'RGAN01');

注意:
作为子查询的select语句只能查询单个列。企图检索多个列将返回错误。
同时要注意性能问题。

 

使用子查询作为计算字段

select cust_name, 
       cust_state,
       (select count(*) 
        from orders 
        where orders.cust_id = customers.cust_id) as orders from customers 
order by cust_name;

联结表

关系表

为理解关系表,来看一个例子:

有一个包含产品目录的数据库表,其中每类物品占一行,对于每种物品,要存储的信息包括产品描述,价格以及生产该产品的供应商。
现有同一供应商生产的多种物品,那么在何处存储供应商名联系方法等信息?将这些数据与产品信息分开存储的理由是:

  1. 同一供应商的每个产品,其供应商的信息是相同的,对每个产品重复此信息既浪费时间又浪费空间;
  2. 如果供应商信息发生变化,只需修改一次即可;
  3. 如果有重复数九,则很难保证每次输入该数据的方式都相同,

相同的数据出现多次决不是一件好事,这是关系数据库设计的基础。关系表的设计就是要把信息分解成多个表,一类数据一个表。各表通过某些共同的值相互关联(所以才叫关系数据库)。

如果数据存储在多个表中,怎样用一条select语句就检索出数据?
答案是使用联结,联结是一种机制,用来在一条select语句中关联表

select vend_name, prod_name, prod_price 
from products, vendors 
where vendors.vend_id = products.vend_id;

等同于下面的写法:

select vend_name, prod_name, prod_price 
from vendors inner join products 
on vendors.vend_id = products.vend_id;

在联结两个表时,实际要做的是将第一个表中的每一行与第二个表中的每一行配对。where子句作为过滤条件,只包含那些匹配给定条件的行。没有where子句,第一个表中的每一行将与第二个表中的每一行配对,而不管他们逻辑上是否能匹配在一起。这种联结称为等值联结(equijoin),也称为内联结(inner join)。

笛卡尔积(cartesian product):
由于没有联结条件的表关系返回的结果为笛卡尔积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。

返回笛卡尔积的联结也叫叉联结(cross join)。

SQL不限制一条select语句可以联结的表的数目。如下:

select prod_name, vend_name, prod_price, quantity 
from orderitems, products, vendors 
where products.vend_id = vendors.vend_id 
and orderitems.prod_id = products.prod_id 
and order_num = 20007;

注意:性能考虑
DBMS在运行时关联指定的每个表,以处理联结。这种处理可能非常耗资源,因此应该注意不要联结不必要的表。

创建高级联结

本文由太阳集团所有网址16877发布于太阳集团城网址送彩金,转载请注明出处:T-SQL:SQL语句处理顺序的坑(四)

您可能还会对下面的文章感兴趣: