0%

数据库系统的用户接口以及SQL语言(一)

一、DBMS的用户数据接口

(一)查询语言;

​ 形式化查询语言 Formal Query Language

​ 表格式查询语言 Tabular Query Language

​ 图形化查询语言 Graphic Query Language

​ 受限的自然语言 Limited Natural Language Query Language

(二)图形化用户工具(GUI)
(三)APIs:应用程序的访问接口
(四)Class Library:类库

二、SQL Language

(一)数据操纵语言(DML):主要用来处理数据库中的数据内容。允许用户对数据库中的数据进行查询 ,插入,更新和删除等操作
DML语句 功能说明
SELECT 从表或视图中检索数据行
INSERT 插入数据到表或视图
UPDATE 更新数据
DELETE 删除数据
CALL 调用过程
MERGE 合并(插入或修改)
COMMIT 将当前事务所做的更改永久化(写入数据库)
ROLLBACK 取消上次提交以来的所有操作
(二)数据定义语言(DDL):是一组SQL命令,用于创建和定义数据库对象,并且将对这些对象的定义保存到数据字典中。通过DDL语句可以创建数据库对象,修改数据库对象和删除数据库对象等。
DDL语句 功能说明
CREATE 创建数据库结构
ALTER 修改数据库结构
DROP 删除数据库结构
RENAME 更改数据库对象的名称
TRUNCATE 删除表的全部内容
(三)数据控制语言(DCL):数据控制语言用于修改数据库结构的操作权限。DCL语句主要有2种:
DCL语句 功能说明
CRANT 授予其他用户对数据库结构的访问权限
REVOKE 收回用户访问数据库结构的权限

三、基本SQL查询

​ SELECT [DISTINCT(用来消除重复元组)] target-list

​ FROM relation-list

​ WHERE qualification(结果应该满足的条件)

例表:

R:水手预定船的信息表:

sid(水手编号) bid(船编号) day(预定船日期)
22 101 10/10/96
58 103 11/12/96

B:船的信息表:

bid bname color
101 tiger red
103 lion green
105 hero blue

S:水手基本信息表:

sid sname rating age
22 Dustin 7 45.0
31 Ludder 8 55.5
58 Rusty 10 35.0
嵌套查询实例1:查询订了103号船的水手
SELECT S.name
FROM Sailors
WHERE EXISTS(SELECT *
                FROM Reserves R
                WHERE R.bid=103 AND S.sid=R.sid)
嵌套查询实例2:查询比名字叫Horatio水手级别高的所有人
SELECT *
FROM Sailors S
WHERE S.rating > ANY(SELECT S2.rating
                        FROM Sailors S2
                        WHERE S2.sname='Horatio')
嵌套查询实例3:查询订了所有船的水手(利用否定之否定,即没有一条船没有订过的水手)
SELECT S.name
FROM Sailors S
WHERE NOT EXISTS(SELECT B.bid
                 FROM Boats B
                 WHERE NOT EXISTS(SELECT R.bid
                                 FROM Reserves R
                                 WHERE R.bid=B.bid
                                 AND R.sid=S.sid))

四、聚集函数运算

(一)函数说明
函数 作用
COUNT(*) 统计关系内有多少元组
COUNT([DISTINCT] A) 计算关系中属性A有多少个不同的值
SUM([DISTINCT] A) 在A属性上的不同的值做求和
AVG([DISTINCT] A) 在A的属性上的不同的值做平均值
MAX(A) 求A的属性上的所有值中的最大值
MIN(A) 求A的属性上的所有值中的最小值
例1:查询所有叫Bob的水手有多少个不同的级别
SELECT COUNT(DISTINCT S.rating)
FROM Sailors S
WHERE S.sname='Bob'
例2:查询所有级别为10的水手的不同年龄的平均值(不是所有水手的年龄平均值)
SELECT AVG(DISTINCT S.age)
FROM Sailors S
WHERE S.rating=10
例3:查询最大级别水手的名单
SELECT S.sname
FROM Sailors S
WHERE S.rating=(SELECT MAX(S2.rating)
               FROM Sailors S2)
(二)关键字
1.GROUP BY

​ 对select查询出来的结果集按照某个字段或者表达式进行分组,获得一组组的集合,然后从每组中取出一个指定字段或者表达式的值。

​ 在使用group by的SQL语句中,select中返回的字段,必须包含在group by语句的后面,作为分组的依据;且这些字段须包含在聚合函数中。

2.HAVING

​ 用于对where和group by查询出来的分组经行过滤,查出满足条件的分组结果。它是一个过滤声明,是在查询返回结果集以后对查询结果进行的过滤操作。

(1)having只能用于group by(分组统计语句中);
(2)where 是用于在初始表中筛选查询,having用于在where和group by 结果分组中查询;
(3)having 子句中的每一个元素也必须出现在select列表中;
(4)having语句可以使用聚合函数,而where不使用。

例1:每个年龄18岁以上的级别中最年轻的水手的年龄及其级别,且每个级别的大于18岁的水手有两名以上
SELECT S.rating,MIN(S.age) As minage
FROM Sailors S
WHERE S.age >= 18
GROUP BY S.rating
HAVING COUNT(*) > 1
例2:每个年龄18岁以上的级别中最年轻的水手的年龄及其级别,且每个级别的水手有两名以上(嵌套)
SELECT S.rating,MIN(S.age)
FROM Sailors S
WHERE S.age >= 18
GROUP BY S.rating
HAVING 1 < (SELECT COUNT(*)
           FROM Sailors S2
           WHERE S2.rating = S.rating)
例3:查询水手年龄平均值最低的级别(利用Temp)
SELECT Temp.rating
FROM (SELECT S.rating,AVG(S.age) As avgage
     FROM Sailors S
     GROUP BY S.rating) As Temp
WHERE Temp.avgage = (SELECT MIN(Temp.abgage)
                    FROM Temp)

五、空值

当值不可用或者缺值时,引用空值(表示不知道,并非为0)

布尔条件:真,假,空

(一)CAST表达式
1.作用

​ 函数调用时实现实参与形参的匹配;

​ 改变计算的精度;

​ 给空值赋予一个数据类型。

2.参数

​ expression:任何有效的SQServer表达式。

​ AS:用于分隔两个参数,在AS之前的是要处理的数据,在AS之后是要转换的数据类型。

​ data_type:目标系统所提供的数据类型,包括bigint和sql_variant,不能使用用户定义的数据类型。

(二)CASE表达式

用于区分情况,在有条件分歧的时候使用它。

-- 简单 CASE表达式
CASE 列(或表达式)
     WHEN <匹配值1> THEN <表达式>
     WHEN <匹配值2> THEN <表达式>
     ......
     ELSE <表达式>
END

-- 搜索 CASE表达式
CASE WHEN <判断表达式> THEN <表达式>
     WHEN <判断表达式> THEN <表达式>
     WHEN <判断表达式> THEN <表达式>
     ......
     ELSE <表达式>
END
-- 例:新建表Machines(serialno,type,year,hours_used,accidents)
-- 查找链锯设备的故障时间在总故障时间里占得比率
SELECT sum(CASE
                  WHEN type='chain saw' THEN accidents
                  ELSE 0e0
          END)/sum(accidents)
FROM Machines;
-- 例:求每种类型的设备出故障的比率
SELECT type,CASE
                WHEN sum(hours_used)>0 THEN
                     sum(accidents)/sum(hours_used)
                ELSE NULL
            END AS accident_rate
FROM Machines
GROUP BY type;