您的位置:金沙游乐场85155 > 大数据库 > T-SQL:SQL语句处理顺序的坑(四)

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

发布时间:2019-10-02 09:54编辑:大数据库浏览(113)

    7.输出

    插入完整的行

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

    insert into... select...from...

    insert into customers(cust_id,
                          cust_name,
                          cust_address, 
                          cust_city, 
                          cust_state, 
                          cust_zip, 
                          cust_country, 
                          cust_contact, 
                          cust_email) 
    select cust_id, 
           cust_name, 
           cust_address, 
           cust_city, 
           cust_state, 
           cust_zip, 
           cust_country, 
           cust_contact, 
           cust_email
    from CustNew;
    

    select语句从CustNew检索出要插入的值,而不是列出他们。DBMS不关心select返回的列名,它使用的是列的位置,因此select的第一列(不管列名如何)将用来填充表列中指定的第一列,如此等等。

    insert select 语句可以包含where子句。

    7.TOP

    自联结

    select  cust_id, cust_name, cust_contact 
    from customers 
    where cust_name = (select cust_name 
                       from customers 
                       where cust_contact = 'Jim Jones');
    

    以上子查询效果等同于自联结:

    select c1.cust_id, c1.cust_name, c1.cust_contact 
    from customers as c1, customers as c2 
    where c1.cust_name = c2.cust_name 
    and c2.cust_contact = 'Jim Jones';
    

    通常情况下,许多DBMS处理联结远比处理子查询快得多

    6.order by

    下划线_通配符

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

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

    使用通配符的技巧

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

    2.where

    主键

    create table Orders
    (
        order_num integer not null primary key,
        cust_id char(10) not null references Customers(cust_id)
    );
    

    表中任意列只要满足以下条件,都可以用于主键。

    • 任意两行主键值都不相同。
    • 每行都具有一个主键值(既列中不允许NULL)。
    • 包含主键的列从不修改或更新。
    • 主键值不能重用。
    1. from 表时  最好给定 库名和表名  Sales.Orders  让表显示表示 不用程序检索。

    sum()

    select sum(mobile) as sum_mobile from contacts;
    

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

    4.having

    联结表

    1.from  

    检查约束 check

    create table OrderItems
    (
        ...
        quantity integer not null check (quantity > 0),
        ...
    )
    

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

    union规则

    • union必须由两条或两条以上的select语句组成,语句之间用关键字union分隔。
    • union中的每个查询必须包含相同的列,表达式或聚集函数(不过,各个列不需要以相同的次序列出)。
    • 列数据类型必须兼容:类型不必完全相同,但必须是DBMS可以隐含转换的类型。

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

    where

    SELECT * FROM TB_BOOK_TAG WHERE count = 1;
    

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

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

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

    1. 再选出大于一个订单的组

    关系表

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

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

    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在运行时关联指定的每个表,以处理联结。这种处理可能非常耗资源,因此应该注意不要联结不必要的表。
    

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

    注释

     --this is a comment
    

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

     /* comments */
    

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

    3.group by

    约束(constraint)

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

    创建计算字段

    计算字段并不实际存在于数据库表中,计算字段是运行时在SELECT语句内创建的。
    
    select rtrim('~    ') || name from tb_book_tag;
    

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

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

    列出触发器

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

    删除视图 drop view

    drop view customeremaillist;
    

    建议、坑

    limit ... offset ...

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

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

    对组合查询结果排序

    select cust_name, cust_contact, cust_email 
    from customers 
    where cust_state in ('IL', 'IN', 'MI') 
    union 
    select cust_name, cust_contact, cust_email 
    from customers 
    where cust_name = 'Fun4All'
    order by cust_name, cust_contact;
    

    在用union组合查询时,只能使用一条order by子句,它必须位于最后一条select语句之后,DBMS用它来排序所有的select语句返回的所有结果。

    1.从 Orders 表查询数据

    not

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

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

     SELECT * FROM CONTACTS WHERE NOT mobile IN ('111111', '3333');
    
    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;
    

    delete

    delete不需要列名或通配符,因为它删除的是整行而不是删除列,要删除指定列,使用update

    delete from custcopy 
    where cust_id = '1000000008';
    

    如果省略where子句,它将删除表中的每个顾客。如果想从表中删除所有行,不要使用delete,可使用truncate table语句,它的速度更快,因为不记录数据的变动。

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

    group by...having...

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

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

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

    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;
    

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

    alert table

    使用alert table更改表的结构,必须给出下面的信息:

    • alter table之后给出要更改的表名。
    • 列出要做出哪些更改。
    alter table Vendors
    add vend_phone char(20);
    
    SQLite对使用alter table执行的操作有所限制。最重要的一个限制是,它不支持使用alter table定义主键和外键。
    
    使用alter table要极为小心,应该在进行改动钱做完整的备份(表结构和数据的备份)。数据库表的更改不能撤销,如果增加了不需要的列,也许无法删除他们。
    

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

    distinct

     SELECT DISTINCT name FROM TB_BOOK_TAG;
    

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

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

    更新和删除数据

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

    组合聚集函数

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

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

    使用子查询

    select cust_id 
    from orders 
    where order_num in (select order_num 
                         from orderitems
                         where prod_id = 'RGAN01');
    
    注意:
    作为子查询的select语句只能查询单个列。企图检索多个列将返回错误。
    同时要注意性能问题。
    
    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;
    

    where...or...

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

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

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

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

    管理事务处理

    使用事务处理(transaction processing),通过确保成批的SQL操作要么完全执行,要么完全不执行,来维护数据库的完整性。

    关于事务处理的一些术语:

    • 事务(transaction)指一组SQL语句;
    • 回退(rollback)指撤销指定SQL语句的过程;
    • 提交(commit)指将未存储的SQL语句结果写入数据库表;
    • 保留点(savepoint)指事务处理中设置的临时占位符,可以对它发布回退(与回退整个事务处理不同)。
    可以回退哪些语句:
    insert,update,delete
    

    管理事务的关键在于将SQL语句分解为逻辑块,并明确规定数据何时应该回退,何时不应该回退。

    begin;
    delete from orders where order_num = 20009;
    delete from orderitems where order_num = 20009;
    commit;
    

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

    union all

    DBMS不取消重复行。

    5.select

    upper()

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

    关键字:upper()转大写

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

    函数 说明
    length() 返回字符串的长度
    lower() 将字符串转小写
    ltrim() 去掉字符串左边的空格
    rtrim() 去掉字符串右边的空格
    upper() 将字符串转大写

     

    update...set... where...

    update customers 
    set cust_email = 'chenzhen@gmainl.com' 
    where cust_id = '1000000008';
    

    更新多个列时,只需使用一条set命令:

    update customers 
    set cust_email = 'lala@qq.com',
        cust_contact = 'sam' 
    where cust_id = '1000000008';
    

    没有where子句,DBMS将会更新表中所有行。

    union

    select cust_name, cust_contact, cust_email 
    from customers 
    where cust_state in ('IL', 'IN', 'MI') 
    union 
    select cust_name, cust_contact, cust_email 
    from customers 
    where cust_name = 'Fun4All';
    

    组合查询

    主要有两种情况需要使用组合查询:

    • 在一个查询中从不同的表返回结构数据
    • 对一个表执行多个查询,按一个查询返回数据

    使用表别名

    select cust_name, cust_contact 
    from customers as c, orders as o, orderitems as oi 
    where c.cust_id = o.cust_id 
    and oi.order_num = o.order_num 
    and prod_id = 'RGAN01';
    

    使用表别名的两个主要理由:

    • 缩短SQL语句
    • 允许在一条select语句中多次使用相同的表

    本文由金沙游乐场85155发布于大数据库,转载请注明出处:T-SQL:SQL语句处理顺序的坑(四)

    关键词: