文章首页 Web开发 Windows开发 编程语言 数据库技术 移动平台 系统服务 微信 设计 布布扣 其他

SQLserver

时间：2020-05-24 16:52:04   收藏：0   阅读：88

SQL数据定义语句（表结构的操作）

SQL语言的数据定义包括对数据库、基本表、视图、索引等数据库对象的创建和删除

一、数据库

数据库定义

数据库删除

二、基本表

常用完整性约束

    NULL |NOT NULL：该列值可以为空，或者不能为空。
    UNIQUE：唯一性约束，说明该列取值必须唯一。
    PRIMARY KEY：主码约束，说明该列为基本表的主码。
    FOREIGN KEY：外码约束，说明表之间的参照关系。
    CHECK：域完整性约束，说明该列的取值需要满足的约束条件。
    DEFAULT：默认值设置，表示该列在未定义时的默认取值。

SQL数据类型

    网址：https://www.w3school.com.cn/sql/sql_datatypes.asp
    插入数据时，啥样的数据加引号，啥样的不加？
      答：字符串类型的char,nchar,varchar,nvarchar,text,ntext都要加引号
      日期类型也要加引号,
      数值型,逻辑型的不用加引号. 
     （varchar,char,nvarchar,nchar,text,ntext,datetime都需要加单引号，int,numeric,bit不需要加）

基本表的定义

基本表的修改

基本表的删除

三、索引

说明

（1）建立索引的目的 ：加快查询速度
（2）为表设置索引要付出代价：

1. 增加了数据库的存储空间
2. 在插入和修改数据时要花费较多的时间

（3）索引分类

1. 聚集索引
    是指表中行的物理顺序与键值的逻辑（索引）顺序相同
    一个表只能包含一个聚集索引
2. 非聚集索引

(4)在哪些列上可以创建索引 ？

1. 在经常需要搜索的列上，可以加快搜索的速度；
2. 在作为主码的列上，强制该列的唯一性和组织表中数据的排列结构；
3. 在经常用在连接的列上，这些列主要是一些外码，可以加快连接的速度；
4. 在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；
5. 在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
6. 在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

(5)在哪些列上不能创建索引 ？

1. 对于那些在查询中很少使用或者参考的列不应该创建索引。
2. 对于那些只有很少数据值的列也不应该增加索引。
3. 对于那些定义为text, image和bit数据类型的列不应该增加索引。
4. 当修改性能远远大于检索性能时，不应该创建索引。

实现

  1. 语法格式

    CREATE [UNIQUE] [CLUSTERED] INDEX <索引名>
    ON <表名> (<列名>[<次序>][,<列名>[<次序>]]…)

说明：
1. UNIQUE：规定索引的每一个索引值只对应于表中唯一的记录
2. CLUSTER：聚簇索引
3. <次序>：可选ASC（升序）或DESC（降序）。若不指定，默认为升序。
4. 索引的排列方式：先以第一个列名值排序；该列值相同的记录，则按下一列名排序。

  2. 建立索引

    例：在Customer表的属性列custPhone上创建一个唯一索引。

    CREATE UNIQUE INDEX idx_uq_custPhone
        on Customer(custPhone)

说明：
（1）对于已含重复值的属性列不能建UNIQUE索引
（2）对某个列建立UNIQUE索引后，插入新记录时DBMS会自动检查新记录在该列上是否取了重复值。这相当于增加了一个UNIQUE约束
（3）索引建立以后，系统在存取数据时会自动选择合适的索引作为存取路径，用户不必也不能显式地选择索引。

  3. 删除索引

    语法：

    DROP INDEX <索引名> ON <基本表名>

说明：不适用于通过定义 PRIMARY KEY 或 UNIQUE约束创建的索引。若要删除该约束和相应的索引，可使用带有 DROP CONSTRAINT 子句的ALTER TABLE。

    例：删除Customer表的索引idxuqcustPhone。

    DROP INDEX idx_uq_custPhone ON Customer

四、视图

视图

1. 是外模式一级数据结构的基本单位
2. 虚表，由基本表或其它视图导出的表，其本身不存在于数据库中。
3. 只存放视图的定义，而不存放视图对应的数据。
4. 视图一经定义，就可被检索或删除，但更新操作有一定的限制，也可再定义其它视图。

语法

CREATE VIEW <视图名> [(<列名> [，<列名>]…)]
AS <子查询>
[WITH CHECK OPTION]

说明

（1）WITH CHECK OPTION：   
    对视图进行UPDATE，INSERT和DELETE操作时要保证更新、插入或删除的行满足视图定义中的谓词条件（即子查询中的条件表达式）
    对BJCustomer视图的更新操作：
     修改操作：RDBMS自动加上custCity=‘北京‘ 的条件
     删除操作：RDBMS自动加上custCity=‘北京‘ 的条件
     插入操作：RDBMS自动检查custCity=‘北京‘ 
       如果不是，则拒绝该插入操作
       如果没有提供custCity属性值，则自动定义custCity为‘北京‘ 
（2）子查询不允许含有ORDER BY子句和DISTINCT短语
（3）组成视图的属性列名，或者全部省略，或者全部指定。
   以下三种情况下必须明确指定全部属性列：
      1. 子查询SELECT子句里列名中有常数、聚集函数或列表达式。
      2. 子查询SELECT子句里列名中有从多个表中选出的同名属性列。
      3. 需要用更合适的新列名作视图列的列名。

语句

1. 建立一个所在城市为“北京”的客户信息视图BJCustomer。
    CREATE VIEW BJCustomer
    AS
    SELECT custID, custName , custPhone
    FROM Customer
    WHERE custCity=‘北京‘；
2. 建立一个生产厂商为“天津南生”的商品视图TJ_Product，并要求进行增、删、改操作时仍需保证该视图只有“天津南生”的商品。
    CREATE VIEW TJ_Product
    AS
    SELECT pdID,pdName,pdPrice
    FROM Product
    WHERE pdSupplier=‘天津南生‘
    WITH CHECK OPTION
3. 建立订购了“天津南生”的商品的订单信息视图TJ_Order，包括订单编号、客户名称和客户电话。
    CREATE VIEW TJ_Order(orderID, custName, custPhone)
    AS
    SELECT Orders.orderID, custName,custPhone
    FROM Customer, Orders, OrderDetail,Product
    WHERE Customer.custID= Orders.custID AND 
        Orders.orderID=OrderDetail.orderID AND 
        OrderDetail.pdID=Product.pdID AND 
            pdSupplier=‘天津南生‘
4. 建立所有商品的商品编号和销售总量的视图。
CREATE VIEW E_Product(pdID, total)
AS
SELECT pdID, SUM(quantity)
FROM OrderDetail
GROUP BY pdID

虚拟列（SUM(quantity)）
  派生的属性列
  在基本表中并不实际存在，可以减少冗余

5. 创建一个名为 Student_Score_120136 的视图，包含班号为“ 120136” 的所有学生的学号、姓名、所选课程名称和成绩。数据首先按照学号升序排列，学号相同的按照课程名称升序排列。
    create view Student_Score_120136
    as
    select top 100 percent  student.sno ,sname,cname,score
    from student,score,course
    where student.sno= score.sno 
    and score.cno = course.cno
    and clsno = ‘120136‘
    order by student.sno,sname;

【注】这里用到了top 100 percent  
ORDER BY子句的查询不能用作表的表达式，其中表的表达式包括：视图、内联表值函数、子查询、派生表和共用表达式。select+order by在视图、子查询中的返回值不是表，而且是游标，所以会报错。
解决方案：只要我们在嵌套子查询视图里面加入: top 100 percent 即可
其中top 100 percent 的意思是：返回符合条件的100%的记录，即所有符合条件的记录

格式

DROP VIEW <视图名>

语句

删除视图TJ_Product。

DROP VIEW TJ_Product

说明

1. 从用户角度：查询视图与查询基本表相同
2. 视图是不实际存在于数据库当中的虚表
3. RDBMS实现视图查询的方法:视图消解
   （1）进行有效性检查，检查查询的表、视图等是否存在。如果存在，则从数据字典中取出视图的定义
   （2）把视图定义中的子查询与用户的查询结合起来，转换成等价的对基本表的查询
   （3）执行修正后的查询

语句

（1）在视图TJ_Product中查找价格高于100元的商品信息。

SELECT pdID, pdName, pdPrice
FROM TJ_Product
WHERE pdPrice >100 ;

（2）在视图E_Product中查找销售总量高于100的商品编号、

SELECT pdID
FROM E_Product
WHERE total>100 ;

说明

（1）用户角度：更新视图与更新基本表相同
（2）由于视图是虚表，所以对视图的更新实际是转换成对基本表的更新。
（3）RDBMS实现视图更新的方法
     视图消解法
（4）指定WITH CHECK OPTION子句后
     DBMS在更新视图时会进行检查，防止用户通过视图对不属于视图范围内的基本表数据进行更新

（1）在视图BJCustomer中插入一个新元组(907,‘李陵‘，‘15103124078‘)。

INSERT INTO BJCustomer
VALUES(907, ‘李陵‘, ‘15103124078‘) ;

（2）视将图TJ_Product中的商品价格提高10%

UPDATE TJ_Product
SET pdPrice= pdPrice*1.1 ;

SQL的数据操纵

一、插入数据

二、删除数据

三、更改数据

SQL的数据查询

Select语句

    一般格式：
    SELECT [ALL | DISTINCT][TOP 表达式1 [PERCENT]] [WITH 
    TIES] 
        <列名或表达式> [列别名1] [,<列名或表达式> [列别名2]…] 
    [INTO 新基本表]
    FROM <表名或视图名> [表别名1] [,<表名或视图名> [表别名2]…]
    [WHERE <条件表达式1>]
    [GROUP BY <列名1> [HAVING <条件表达式2>]]
    [ORDER BY <列名2> [ASC | DESC]]
    说明：
    SELECT子句：指定查询结果要显示的结果列清单
    FROM子句：指定查询结果的数据来源，即查询所涉及的基本表或视图
    WHERE子句：指定从数据来源选取元组需要满足的条件
    GROUP BY子句和HAVING子句：用于分组和分组过滤处理。
    ORDER BY子句：决定查找出来的元组的排列顺序。
    语句的执行过程：
    从FROM子句指定的基本表或视图中，选取符合WHERE子句中指定的<条件表达式1>的元组，按SELECT子句中的目标列表，选出元组中的分量值形成结果表。
    若有INTO子句，则创建新的基本表，并将查询结果存入新建的基本表中。
    若有GROUP BY子句，则将符合<条件表达式1>的元组，按照指定的列名1的值分组，值相同的元组分在一组，每个组产生结果表中的一个元组；若有HAVING短语，则在分组结果中去掉不满足HAVING短语<条件表达式2>的分组。
    若有ORDER BY子句，则结果表要根据指定的列名2的值按升序或降序排序。

一、简单查询

WHERE子句常用的查询条件：

比较：<、<=、>、>=、=、!=、 < >、!>、!<
确定范围：BETWEEN A AND B 
         NOT BETWEEN A AND B 
确定集合：IN、NOT IN
字符匹配：LIKE，NOT LIKE
空值：IS NULL、IS NOT NULL
多重条件：AND、OR、NOT

二、连接查询

连接的操作执行过程

1. 首先在表1中找到第一个元组，然后从头开始扫描表2，逐一查找满足连接件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。
2. 表2全部查找完后，再找表1中第二个元组，然后再从头开始扫描表2，逐一查找满足连接条件的元组，找到后就将表1中的第二个元组与该元组拼接起来，形成结果表中一个元组。 
3. 重复上述操作，直到表1中的全部元组都处理完毕。

外连接与普通连接的区别

1. 普通连接操作只输出满足连接条件的元组
2. 外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出
3. 有利于更清晰的展现出表，比如商店的订单表，及时没有卖出去，也应该有商品的基本信息，用外连接要好一些

形如：

左外连接（LEFT JOIN…ON…） 
右外连接（RIGHT JOIN…ON…）

  1. 查询所有客户订单的客户编号、客户名称、订单编号和送货地址(用左外连接实现)

    SELECT Customer.custID, custName, orderID, 
orderAddress
FROM Customer LEFT JOIN Orders ON
Customer.custID=Orders.custID ;

三、分组及汇总查询

对查询结果进行分组计算和汇总计算

1. 分组查询用GROUP BY子句完成
2. 计算的函数称为聚合函数
    1. COUNT ( * )：统计元组个数
    2. COUNT (< 列名>)：统计一列中值的个数
    3. SUM (< 列名>)：计算一列值的总和
    4. AVG (< 列名>)：计算一列值的平均值
    5. MAX (< 列名>)：求一列中的最大值
    6. MIN (< 列名>)：求一列值的最小值

说明：

1. 除COUNT（*）外，其它聚集函数在计算时会忽略空值。
2. 在<列名>前加入DISTINCT保留字，会将查询结果的列去掉重复值在计算。

GROUPING SETS短语：在显示汇总的同时也显示明细信息
解决SELECT子句的列名列表中只能出现分组属性和集函数，可以出现多列明细表

查询所有商品信息的订单明细，并分类汇总各种商品的销售数量。

SELECT pdID, orderID, SUM(quantity) as Total
FROM OrderDetail
GROUP BY GROUPING SETS ( pdID, 
        ( pdID, orderID ) ) ;

GROUP BY 子句

1. 把元组按某一指定列（或一些列）上的值相等的原则分组，然后再对每组数据进行规定的操作。
2. 对分组进行汇总可通过聚合函数实现

HAVING短语

（1）与WHERE子句的区别：
 作用对象不同 
     WHERE子句作用于基表或视图，从中选择满足条件的元组。
     HAVING短语作用于组，从中选择满足条件的组。
（2）集函数的条件关系必须用HAVING ， WHERE中不应出现集函数

  1. 查询每一种商品的销售总量。

    SELECT pdID, SUM(quantity) AS Total
FROM orderDetail
GROUP BY pdID ;

  2. 查询每个城市的客户总数。

    SELECT custCity, COUNT(*) AS custCount
FROM Customer
GROUP BY custCity ;

  3. 查询被购买3次以上的商品的商品编号和销售数量的最大值和最小值。

    SELECT pdID, MAX(quantity) AS MAX_quantity, 
    MIN(quantity) AS MIN_quantity
FROM OrderDetail
GROUP BY pdID HAVING COUNT(*)>=3 ;

四、嵌套查询

定义：

将一个查询块(SELECT-FROM-WHERE)嵌套在另一个查询块的WHERE子句或HAVING短语的条件表达式中的查询 ，称为嵌套查询。

说明：

其中外层查询称为父查询，内层查询称为子查询。

执行过程:

是由里至外的，每一个子查询是在上一级查询处理之前完成的。

注意:

子查询中不能用ORDER BY 语句

五、需要查询支持的数据操作

原文：https://www.cnblogs.com/guo-2020/p/12951251.html

评论（0）
© 2014 bubuko.com 版权所有 - 联系我们:wmxa8@hotmail.com
打开技术之扣，分享程序人生！