栏目分类:
子分类:
返回
名师互学网用户登录
快速导航关闭
当前搜索
当前分类
子分类
实用工具
热门搜索
名师互学网 > IT > 软件开发 > 后端开发 > Java

数据库复习CSU

Java 更新时间: 发布时间: IT归档 最新发布 模块sitemap 名妆网 法律咨询 聚返吧 英语巴士网 伯小乐 网商动力

数据库复习CSU

必考
资料来源CSU数据库PPT,侵权删

01 数据库简介 数据库系统组成
  1. 数据库
  2. 数据库管理系统
  3. 数据库应用程序
    比如:银行的金库-数据库;银行的大大小小的管理机构-数据库管理系统,帮我们管理着它们;银行又提供了很多的营业厅自动取款机-数据库应用程序,我们可以操作我们的资金。
数据库系统的三级结构两级独立性

三级结构:物理级(内模式)、概念级(概念模式)、用户级(外模式)
两级独立性:逻辑独立性、物理独立性

02设计理论1-关系模型与完整性 1、数据的3种描述形式

(1)现实世界
(2)信息世界
(3)机器世界/计算机世界

2、E-R模型概念【概念层数据模型】

(实体-联系模型 ER) /关系型数据库(除此之外 还有层次模型、网状模型)
实体、实体集、属性、实体键(一定要加)
联系/关系:一对一、一对多、多对多

3、Key

4、完整性约束

(1)域完整性
属性取值必须取自于值域;
属性是否能取空值由其语义决定。域完整性是最基本的约束。
(2)实体完整性
主关键字值必须是唯一的且任何组成成份都不能是空值。
(3)引用/参考完整性
用来保证表之间的关系的完整
(4)用户自定义完整性。
创建业务规则来确保值的有效性,从而使之落在需要范围之内。

5、将实体联系模型转化为关系数据模型

规则
一个矩形就是一个表
一个属性就是一个列
关系:
1.1-N 【1的主键变为N的一个普通属性】
2.N-M 【新建表,两个的主键共同合起来变成主键,可能产生新的属性】
3.1-1 【参考1-N】
主键需要下划线

03 设计理论2函数依赖与范式 1、规范化问题的提出

问题:
(1)数据冗余
(2)插入异常
(3)删除异常
(4)更新异常
一个好的关系模式应该具备四个条件:
(1)尽可能少的数据冗余
(2)没有插入异常
(3)没有删除异常
(4)没有更新异常

2、函数依赖




3、范式

第一范式:每个属性不可分解
第二范式:每个非主属性完全依赖于主属性,不存在部分依赖
第三范式:每个非主属性不存在传递依赖于主属性


4、关系模式的分解

一个关系模式的分解可以是多种多样的,但不管怎样分解,最后产生的若干个关系模式应与原关系模式等价。

关于“等价”的概念存在3种不同的含义:
(1)分解具有“无损连接性”。
(2)分解要“保持函数依赖” 。
(3)分解既要“保持函数依赖”,又要具有“无损连接性”。

常见题型: 1、判断属于第几范式 2、判断候选码

判断规则
(1)如果有属性不在函数依赖集中出现,那么它必须包含在候选码中;
(2)如果有属性只在函数依赖集的右边出现,那么它必须不在候选码中;
(3)如果有属性只在函数依赖集的左边出现,则该属性一定包含在候选码中。
(4)如果有属性或属性组能唯一标识元组,则它就是候选码;(也就是说,通过函数依赖推导出,某属性或属性组,能决定所有属性)(也就是说,通过函数依赖推导出,某属性或属性组的闭包中有所有属性)它一个推导出其他所有

3、求最小函数依赖集合


4、关系模式分解,符合第二范式


5、关系模式分解,符合第三范式


SQL

1、数据定义语言 DDL:create alter drop /数据库、表、视图、存储过程、触发器
2、数据操纵语言 DML:insert/delete/update/select
3、数据控制语言 DCL 设置权限
4、其他语言要素 ALE 变量、注释、编辑、算术、流程控制

SELECT 语句 1. 最简单的

SELECT 选列
FROM 选表

SELECt *
FROM titles

SELECt title, price
FROM titles

SELECt title, price, * --后面所有依旧会出现
FROM titles

–注意查询是一个数据组织过程
SELECt title, title, title, title
FROM titles

2. SELECt中的运算

SELECT title, price * 0.9, price
FROM titles

SELECt title, price, ytd_sales, price * ytd_sales
FROM titles

–截取title的前三个部分内容
SELECt LEFt(title,3), title
FROM titles

SELECt title * price
FROM titles

结果:The Busy Executive’s Database Guide 的单价是:19.99
体现显示内容格式
convert 转变格式

SELECt title + ’ 的单价是:’ + convert(varchar(20), price)
FROM titles

SELECt au_fname, au_lname, au_fname + ’ '+au_lname
FROM authors

2. 列的别名

SELECt title as ‘书名’, price as ‘单价’, ytd_sales as ‘销量’
FROM titles

SELECt title ‘书名’, price ‘单价’, ytd_sales ‘销量’
FROM titles

SELECt title, price ‘原价’, price * 0.9 as ‘折后价格’
FROM titles

SELECt au_fname, au_lname, au_fname + ’ '+au_lname as ‘name’
FROM authors

3. DISTINCT 关键词

每一个只留下唯一值
SELECt titles.title_id
FROM titles

SELECt title, titles.type
FROM titles

SELECt titles.type
FROM titles

SELECt DISTINCT titles.type
FROM titles

SELECt DISTINCT titles.title
FROM titles

SELECt DISTINCT titles.type, title
FROM titles

SELECt title, type, pub_id
FROM titles

type,pub_id 两个作为一个属性,判断唯一性
两个都不同才唯一
SELECt DISTINCT type, pub_id
FROM titles

SELECt ALL type, pub_id
FROM titles

SELECt ‘my’ as ‘mycolum’
FROM titles

4.top n

SELECt TOP 3 *
FROM titles

5.FROM的一点点知识

SELECt *
FROM titles

SELECt titles.title, titles.price
FROM titles

SELECt title, price
FROM titles

SELECt t.title, t.price
FROM titles as t

SELECt t.title, t.price
FROM titles as ‘t’

6.INTO之句,存一个查询结果到一个表中,这个表是个新表,自动创建

SELECt au_fname, au_lname, au_fname + ’ ’ + au_lname as ‘full_name’
INTO mynamelist
FROM authors

SELECt *
FROM mynamelist

–消息 2714,级别 16,状态 6,第 1 行
–数据库中已存在名为 ‘mynamelist’ 的对象。

SELECt au_fname, au_lname, au_fname + ’ ’ + au_lname as ‘full_name’
INTO mynamelist
FROM authors

WHERe子句

SELECt 指定列
FROM 指定表
WHERe 指定行,提供条件,用来过滤行

1. = 注意区分好数据类型

SELECt *
FROM titles
WHERe price = 19.99

SELECt *
FROM titles
WHERe type = ‘business’

SELECt *
FROM titles
WHERe pubdate = ‘1991-06-12’

SELECt *
FROM titles
WHERe pubdate = ‘1991-06-12 12:00:00’

2.> < >= <= <> 注意区分好类型

可以用于数字、字符、日期
<>不等于

SELECt *
FROM titles
WHERe price > 19.99

SELECt *
FROM titles
WHERe price >= 19.99

SELECt *
FROM titles
WHERe type >= ‘business’

SELECt *
FROM titles
WHERe type >= ‘popular_comp’

SELECt *
FROM titles
WHERe pubdate > ‘1991-06-12’

3. and or not 与或非 多个条件

SELECt *
FROM titles
WHERe price > 10 and price < 20

SELECt *
FROM titles
WHERe price < 10 or price > 20

SELECt *
FROM titles
WHERe price < 10 or price > 20 or type = ‘business’

SELECt *
FROM titles
WHERe price < 10 and (price > 20 or type = ‘business’)

3. IS NULL, IS NOT NULL 条件控制空值

SELECt title, price
FROM titles
WHERe price < 10

SELECt title, price
FROM titles
WHERe price IS NULL

SELECt title, price
FROM titles
WHERe price IS NOT NULL

4. BETWEEN 提供2个数值,确定一个条件范围

SELECt title, price
FROM titles
WHERe price between 10 and 19.99

SELECt title, price
FROM titles
WHERe price >= 10 and price <= 19.99

5.in not in 用来做枚举控制

SELECt title, type, price, pub_id
FROM titles
WHERe type = ‘business’ or type = ‘mod_cook’ or type = ‘popular_comp’

SELECt title, type, price, pub_id
FROM titles
WHERe type in (‘business’, ‘mod_cook’, ‘popular_comp’)

SELECt title, type, price, pub_id
FROM titles
WHERe type not in (‘business’, ‘mod_cook’, ‘popular_comp’)

SELECt title, type, price, pub_id
FROM titles
WHERe price in (19.99, 2.99)

6. 模糊匹配

------like + 通配符% (全部通配,不管长度和字符)

SELECt *
FROM titles
WHERe title = ‘Computer’

SELECt *
FROM titles
WHERe title like ‘%Computer%’

SELECt *
FROM titles
WHERe title like ‘Computer%’

SELECt *
FROM titles
WHERe title like ‘%Computer’

SELECt *
FROM titles
WHERe title like ‘%r’

SELECt *
FROM titles
WHERe type like ‘b%’

SELECt *
FROM titles
WHERe price like ‘1%’

like + _ (全部通配,只有一个字符)

SELECt au_fname
FROM authors
WHERe au_fname like ‘_heryl’

SELECt au_fname
FROM authors
WHERe au_fname like ‘C_eryl’

like + [x-x](规定字符的通配范围,只通配一个字符)

SELECt au_fname
FROM authors
WHERe au_fname like ‘[a-c]heryl’

SELECt au_fname
FROM authors
WHERe au_fname like ‘[a-z]h[a-e]r_l%’


查询产品类别CategoryID是2,3,4的所有产品
用多种方法来实现:OR,BETWEEN,IN,<>

SELECt productname, SupplierID, CategoryID, unitprice
FROM products
WHERe CategoryID in (2,3,4)

SELECt productname, SupplierID, CategoryID, unitprice
FROM products
WHERe CategoryID < 5 and CategoryID >1

SELECt productname, SupplierID, CategoryID, unitprice
FROM products
WHERe CategoryID = 2 or CategoryID = 3 or CategoryID = 4

查询产品类别是2,3,4,并且供应商SupplierID不是11的所有产品

SELECt productname, SupplierID, CategoryID, unitprice
FROM products
WHERe CategoryID in (2,3,4) and SupplierID <> 11

SELECt productname, SupplierID, CategoryID, unitprice
FROM products
WHERe (CategoryID = 2 or CategoryID = 3 or CategoryID = 4) and SupplierID <> 11

查产品名ProduceName中以“C”或“D”或“E”开头的所有产品
也可以用多种方法实现,练习使用LIKE的模糊匹配

SELECt productname, SupplierID, CategoryID, unitprice
FROM products
WHERe productname like ‘[c-e]%’

查询库存UnitsInStock<订货量UnitsOnOrder的产品,并且在第一列添加常数“需要进货”

SELECt ‘需要进货’ as ‘需要进货’ ,productname, unitprice, UnitsOnOrder, UnitsInStock
FROM products
WHERe UnitsOnOrder > UnitsInStock

在上面的基础上,计算出每个产品进货的预算总额
利用表达式,计算出订货和库存的差距,并利用产品单价UnitPrice得出预算。
需要添加一个新的计算结果的列,叫做“进货预算”

SELECt productname, unitprice, UnitsOnOrder, UnitsInStock, (UnitsOnOrder - UnitsInStock) * unitprice as ‘进货的预算’
FROM products
WHERe UnitsOnOrder > UnitsInStock

查找雇员表中60年代出生在UK的员工
日期列用法例子: Birthday > ‘2000-1-1’

SELECt lastname, firstname, country, birthdate
FROM employees
WHERe Country = ‘UK’ and birthdate between ‘1960-1-1’ and ‘1969-12-31’

聚合函数

SUM()求和(行集合上的某个列)
AVG()求平均值(行集合上的某个列)
MIN()求最小值(行集合上的某个列)
MAX()求最大值(行集合上的某个列)
COUNT()求行数(行集合)

聚合函数是对查询的行集合(或行集合上某个列)进行统计运算,一个聚合函数只输出一个值

SELECt COUNT(*)
FROM titles

SELECt COUNT(*)
FROM titles
WHERe price > 10

SELECt COUNT(*) as ‘统计本数’
FROM titles
WHERe price > 10 and type = ‘business’

SELECt MAX(price)
FROM titles

SELECt MAX(price), MIN(price), AVG(price), SUM(price)
FROM titles

SELECt SUM(ytd_sales)
FROM titles

SELECt MAX(price), MIN(price), AVG(price), SUM(ytd_sales), AVG(ytd_sales)
FROM titles

SELECt MAX(price), MIN(price)
FROM titles
WHERe type = ‘business’

SELECt SUM(*) --报错
FROM titles

SELECt title, SUM(ytd_sales) --报错
FROM titles

SELECt MAX(type)
FROM titles

GROUP BY 分组聚合

SELECt AVG(price)
FROM titles
WHERe type = ‘business’

SELECt AVG(price)
FROM titles
WHERe type = ‘mod_cook’

SELECt AVG(price)
FROM titles
WHERe type = ‘popular_comp’

business 13.73
mod_cook 11.49
popular_comp 21.475
分组可以可以理解为分成几类,对那几个类再进行计算
分组:指定一个列,type,对列里面任意一个取值,对记录进行分组,分组用的列,只是用来分组,而不一定用来聚合
聚合:对每一个分组,计算某个列的一个聚合统计值,这个聚合列,通常不是分组列

SELECt type, AVG(price) as ‘按书类型的平均价格’
FROM titles
GROUP BY type

SELECt type, AVG(type) --报错
FROM titles
GROUP BY type

SELECt price, AVG(price), COUNT(*)
FROM titles
GROUP BY price

SELECt price, COUNT(*)
FROM titles
GROUP BY price

特别注意:分组后,被分组的列,可以出现在select的非聚合函数里面,但没有被分组的列,不能出现在select的非聚合函数里面

SELECT type, AVG(price)
FROM titles
GROUP BY type

SELECt AVG(price)
FROM titles
GROUP BY type

SELECt price, AVG(price) --报错
FROM titles
GROUP BY type

SELECt title, AVG(price) --报错
FROM titles
GROUP BY type

GROUP BY可以嵌套分层

SELECt type, pub_id, AVG(price), COUNT(*)
FROM titles
GROUP BY type, pub_id

SELECt pub_id, type, price, COUNT(*)
FROM titles
GROUP BY pub_id, type, price

SELECt *
FROM titles
WHERe price = 19.99 and type = ‘business’ and pub_id = ‘1389’

HAVINg 过滤分组的聚合结果

–按type分组,然后求聚合函数,聚合结果中不显示数量小于等于2的type
SELECt type, AVG(price), COUNT()
FROM titles
GROUP BY type
HAVINg COUNT() > 2

HAVINg是对分组聚合的结果进行过滤
HAVINg只能接聚合函数

SELECt type, AVG(price), COUNT(*)
FROM titles
GROUP BY type
HAVINg AVG(price) > 13

SELECt type, AVG(price), COUNT(*)
FROM titles
GROUP BY type
HAVINg MAX(price) > 13

SELECt type, AVG(price), COUNT(*)
FROM titles
GROUP BY type
HAVINg MAX(ytd_sales) > 13

SELECt type, AVG(price), COUNT(*)
FROM titles
GROUP BY type
HAVINg price > 2 --报错了

SELECt COUNT(*)
FROM titles
HAVINg price > 2

WHERe是对FROM的结果进行过滤

SELECt type, AVG(price), COUNT(*)
FROM titles
WHERe type not in (‘mod_cook’, ‘UNDECIDED’)
GROUP BY type

SELECt type, AVG(price), COUNT()
FROM titles
GROUP BY type
HAVINg COUNT() > 2

ORDER BY 排序

ASC 升序排列(默认)
DESC 降序排列

SELECt *
FROM titles
ORDER BY type ASC

SELECt *
FROM titles
ORDER BY type DESC

SELECt *
FROM titles
ORDER BY price DESC

SELECt *
FROM titles
ORDER BY price

SELECt *
FROM titles
ORDER BY pub_id, price
多个先执行第一个,第一个相同执行第二个
SELECt *
FROM titles
ORDER BY pub_id DESC, price

—练习—

显示单价最高的10个商品
需要先按降序排列,然后取TOP 10

SELECt TOP 10 productname, unitprice
FROM products
ORDER BY unitprice DESC

计算所有商品的总单价

SELECt SUM(unitprice)
FROM products

统计类型为1的商品的个数,和总库存
在作聚合函数的时候要选择2个聚合函数,而且要添加WHERe条件

SELECt COUNT(*), SUM(UnitsInStock)
FROM products
WHERe categoryid = 1

统计当前有多少个供应商给我们供货
要求只显示出供应商的SupplierID就可以了
2方法,GROUP BY,DISTINCT

SELECt DISTINCT SupplierID
FROM products

SELECt SupplierID
FROM products
GROUP BY supplierid

统计每个供应商ID提供的商品个数
按供应商ID分组,并添加聚合函数

SELECt SupplierID, COUNT(*)
FROM products
GROUP BY supplierid

按商品个数的升序排列刚才的结果
添加对聚合函数的结果的排序

SELECt SupplierID, COUNT()
FROM products
GROUP BY supplierid
ORDER BY COUNT()

SELECt productname, unitprice * unitsinstock as ‘我的资产’
FROM products
ORDER BY ‘我的资产’ DESC

只显示提供商品个数>3的供应商
使用Having子句

SELECt SupplierID, COUNT()
FROM products
GROUP BY supplierid
HAVINg COUNT() > 3
ORDER BY COUNT(*)

SELECt SupplierID, COUNT() as ‘数量’, AVG(unitprice) ‘平均价格’, MAX(unitprice) ‘最大单价’
INTO myfirstaggregatedtable
FROM products
WHERe unitprice > 3 and unitsinstock > 0
GROUP BY supplierid
HAVINg COUNT() > 3
ORDER BY COUNT(*)

SELECt *
FROM myfirstaggregatedtable

多表查询

在数据库设计理论中,E-R图、函数依赖、范式让不同的信息分布在不同表里面,这是逻辑视角
但是在用户查询的时候,是用户视角,他们希望所有信息都在一起

所有信息,一般在多个表里面,通过主外健关联,必须通过多表查询完成

写法1:From里面写多个表,然后Where中建立关联

–多表查询时,一定要注意写上where的两个表的关联条件
SELECt fname, lname, job_desc
FROM employee, jobs
WHERe employee.job_id = jobs.job_id

–多表查询时,多个表可能有同名的列,所以要加表限定信息
SELECt fname, lname, employee.job_id, job_desc
FROM employee, jobs
WHERe employee.job_id = jobs.job_id

SELECt e.fname, e.lname, e.job_id, j.job_desc
FROM employee as e, jobs as j
WHERe e.job_id = j.job_id

SELECt fname, lname, pub_name
FROM employee as e, publishers as p
WHERe e.pub_id = p.pub_id

SELECt fname, lname, pub_name, job_desc
FROM employee as e, publishers as p, jobs as j
WHERe e.pub_id = p.pub_id and e.job_id = j.job_id

SELECt fname, lname, pub_name, job_desc
FROM employee as e, publishers as p, jobs as j
WHERe e.pub_id = p.pub_id and e.job_id = j.job_id and country = ‘USA’

-------------练习 northwind-------------------
SELECt productname, unitprice, SupplierID, CategoryID
FROM products

SELECt supplierid, companyname
FROM suppliers

SELECt categoryid, categoryname
FROM Categories

SELECt productname, unitprice, s.CompanyName, c.CategoryName
FROM products as p, suppliers as s, categories as c
WHERe p.SupplierID = s.SupplierID and p.CategoryID = c.CategoryID

SELECt c.CategoryName, COUNT(*)
FROM products as p, categories as c
WHERe p.CategoryID = c.CategoryID
GROUP BY c.CategoryName

-----------------练习---------------

我们要查询在华盛顿州city=DC的出版社出版的所有的出版物名称,
显示3列:书名title,出版社名pub_name,州名state

SELECt title, pub_id FROM titles
SELECt pub_id, pub_name, state FROM publishers

SELECt t.title, p.pub_name, p.state
FROM titles as t, publishers as p
WHERe t.pub_id = p.pub_id and p.state = ‘DC’

写法2:用子查询,将子查询结果作为where中的条件

–查美国加州出版的书,要求看到书名和价格

SELECt title, price
FROM titles

SELECt *
FROM publishers
WHERe country = ‘USA’ and state = ‘CA’

SELECt *
FROM titles
WHERe pub_id = ‘1389’

SELECt title, price
FROM titles
WHERe pub_id =
(
SELECt pub_id
FROM publishers
WHERe country = ‘USA’ and state = ‘CA’
)

SELECt title, price
FROM titles
WHERe pub_id in
(
SELECt pub_id
FROM publishers
WHERe country = ‘USA’
)

注意1:子查询返回结果一定是一个列的值,不能是返回一行

SELECt title, price
FROM titles
WHERe pub_id =
(
SELECt * --报错
FROM publishers
WHERe country = ‘USA’ and state = ‘CA’
)

注意2:子查询返回结果很可能是多个值,要用in枚举运算符,不能用= > <等单值运算符

SELECt title, price
FROM titles
WHERe pub_id =
(
SELECt pub_id --报错
FROM publishers
WHERe country = ‘USA’
)

–查德国出版社工作的雇员,要求看到姓名就行了

SELECt fname, lname
FROM employee

SELECt *
FROM publishers
WHERe country = ‘France’

SELECt fname, lname
FROM employee
WHERe pub_id in
(
SELECt pub_id
FROM publishers
WHERe country = ‘France’
)

----------练习--------------
查询美国的供应商提供的商品的名字和单价

SELECt SupplierID
FROM Suppliers
WHERe country = ‘USA’

SELECt p.ProductName, p.UnitPrice
FROM products as p
WHERe p.SupplierID in
(
SELECt s.SupplierID
FROM Suppliers as s
WHERe s.country = ‘USA’
)

SELECt p.ProductName, p.UnitPrice, s.Country
FROM products as p, suppliers as s
WHERe p.SupplierID = s.SupplierID and s.Country = ‘USA’

方法1和方法2差别

核心差别:SELECt里面有多个表的列,必须用方法1
次要差别:过滤的时机不同,方法1先把2个表乘起来然后寻找符合条件的行,方法2先在查询中找符合条件的行,然后在主查询中找符合条件的行

---------练习---------------
查询加州出生的作者写的书的信息:书名,单价

方法1

SELECT t.title, t.price
FROM titles as t, authors as a, titleauthor as te
WHERe t.title_id = te.title_id and a.au_id = te.au_id and a.state = ‘CA’

SELECt t.title, t.price
FROM titles as t, authors as a, titleauthor as te
WHERe t.title_id = te.title_id and a.au_id = te.au_id and a.state = ‘CA’ and te.au_ord = 1

SELECt DISTINCT t.title, t.price
FROM titles as t, authors as a, titleauthor as te
WHERe t.title_id = te.title_id and a.au_id = te.au_id and a.state = ‘CA’

方法2

SELECt t.title, t.price
FROM titles as t
WHERe title_id in
(
SELECt title_id
FROM authors as a, titleauthor as te
WHERe a.au_id = te.au_id and a.state = ‘CA’
)

-------------------练习-----------------

找出所有生产过类别categoryid为2的产品的提供商supplierid的企业名称和联系电话
在northwind数据库的products表和suppliers表中
products表和suppliers表用supplierID关联

SELECt productname, CategoryID, SupplierID FROM products
SELECt supplierid, companyname, phone FROM Suppliers

SELECt DISTINCT s.CompanyName, s.Phone
FROM products as p, Suppliers as s
WHERe p.SupplierID = s.SupplierID and p.CategoryID =

SELECt CompanyName, Phone
FROM Suppliers
WHERe SupplierID in
(
SELECt DISTINCT SupplierID FROM Products WHERe CategoryID = 2
)

找出在1996-7-1到1996-7-10期间有过销售记录的美国员工的名字
在northwind数据库的employees表和orders(订单)表,
employee表有country字段,美国是‘USA’,
orders表销售日期是OrderDate列,两个表用employeeID关联

SELECt OrderID, EmployeeID, OrderDate FROM Orders
SELECt EmployeeID, LastName, FirstName, Country FROM Employees

SELECt LastName, FirstName
FROM Employees
WHERe Country = ‘USA’ and EmployeeID in
(
SELECt DISTINCT EmployeeID FROM Orders
WHERe Orderdate BETWEEN ‘1996-7-1’ AND ‘1996-7-10’
)

方法3:连接查询

SELECt s.stor_name, d.discounttype, s.stor_id, d.stor_id
FROM stores as s, discounts as d -18行

SELECt s.stor_name, d.discounttype, s.stor_id, d.stor_id
FROM stores as s, discounts as d
WHERe s.stor_id = d.stor_id

SELECt s.stor_name, d.discounttype, s.stor_id, d.stor_id
FROM stores as s inner join discounts as d on (s.stor_id = d.stor_id)

内连接

查询出两个表对应的数据,如果把两个表分别看成一个集合的话,内连接的结果即为两个表的交集。
两边都要相互匹配
和where是相等的
SELECt d.discounttype, s.stor_name
FROM stores as s inner join discounts as d on (s.stor_id = d.stor_id)

无论如何,我都要看到所有的折扣类型,如果某个折扣被某个商店用,就显示商店
折扣类型1 NULL
折扣类型2 NULL
折扣类型3 Bookbeat

左向外连接

当左边的行在右边的表中没有匹配行时,则右边的表的选择列表列包含空值
SELECt d.discounttype, s.stor_name
FROM discounts as d left outer join stores as s on (s.stor_id = d.stor_id)

右向外连接

SELECt d.discounttype, s.stor_name
FROM discounts as d right outer join stores as s on (s.stor_id = d.stor_id)

全向外连接

当某行在另一个表中没有匹配行时,则另一个表的选择列表列包含空值
SELECt d.discounttype, s.stor_name
FROM discounts as d full outer join stores as s on (s.stor_id = d.stor_id)

–交叉连接
SELECt d.discounttype, s.stor_name
FROM discounts as d cross join stores as s

------------------------练习----------------------

内联接检索与某个出版商居住在相同州和城市的作者:

SELECt * FROM publishers --8行
SELECt au_id, au_fname, au_lname, city, state FROM authors --23行

SELECt au_fname, au_lname, p.city, p.state
FROM publishers as p join authors as a
on (p.state = a.state and p.city = a.city)

与某个出版商居住在相同州和城市的作者,
若要在结果中包括所有的作者,而不管出版商是否住在同一个城市,左向外联接

SELECt au_fname, au_lname, p.city, p.state
FROM publishers as p right outer join authors as a
on (p.state = a.state and p.city = a.city)

若要在结果中包括所有作者和出版商,
,请使用完整外部联接。

SELECt au_fname, au_lname, p.city, p.state
FROM publishers as p full outer join authors as a
on (p.state = a.state and p.city = a.city)

--------------------练习---------------
查询商业书的书名和作者

1:搞清楚要什么表
2:搞清楚列在哪儿
3:搞清楚表间关系
4:搞清楚条件
5:选择编写策略

SELECt title, price FROM titles WHERe type = ‘business’
SELECt au_id, au_fname, au_lname FROM authors
SELECt au_id, title_id FROM titleauthor

SELECt title, au_lname, au_fname
FROM titleauthor as te join titles as t on (te.title_id = t.title_id)
join authors as a on (te.au_id = a.au_id)
WHERe t.type = ‘business’

SELECt title, au_lname, au_fname
FROM titleauthor as te, titles as t, authors as a
WHERe t.type = ‘business’ and te.title_id = t.title_id and te.au_id = a.au_id

---------查询没有写过business或者popular_comp类型书籍的作者姓名------

SELECt title_id, title, type FROM titles
SELECt au_id, au_lname, au_fname FROM authors
SELECt * FROM titleauthor

–这个写法有个问题,如果某个作者写了business书,还写了psychology书,那他可能会被包含在结果中,比如“locksley”,这是不合理的
SELECt DISTINCT au_lname, au_fname, type
FROM titleauthor as te, titles as t, authors as a
WHERe te.title_id = t.title_id and te.au_id = a.au_id
and type not in (‘business’, ‘popular_comp’)

–改进写法为子查询,先找到写过’business’, 'popular_comp’的作者,然后主查询过滤
SELECt au_lname, au_fname
FROM authors
WHERe au_id not in (
SELECt te.au_id
FROM titleauthor as te, titles as t
WHERe te.title_id = t.title_id
and type in (‘business’, ‘popular_comp’)
)

关系代数

关系代数的五种基本运算:并、差、笛卡尔积、选择、投影
并、差、交
选择、投影、连接、除



DML DELETe

SELECT *
INTO mytitle2
FROM titles

SELECt *
FROM mytitle1

1.删除一个表的所有数据

DELETe mytitle1 --删数据

DROP TABLE mytitle1 --删表

2.删除符合条件的行

DELETE mytitle2
WHERe type = ‘business’

SELECT * FROM mytitle2

SELECt *
FROM mytitle2
WHERe price > 10 and title like ‘%computer%’

DELETe mytitle2
WHERe price > 10 and title like ‘%computer%’

3.删除某个表符合条件的行,但是条件在另外一个表里面

删除加州CA出版社出版的所有的书

SELECT * FROM mytitle2
WHERe pub_id in
(SELECt pub_id FROM publishers WHERe state = ‘CA’)

SELECt *
FROM mytitle2 as my inner join publishers as p on (my.pub_id = p.pub_id)
WHERe p.state = ‘CA’

DELETe mytitle2
WHERe pub_id in
(SELECT pub_id FROM publishers WHERe state = ‘CA’)

DELETe mytitle2
FROM mytitle2 as my inner join publishers as p on (my.pub_id = p.pub_id)
WHERe p.state = ‘CA’

–真的做的时候,要注意引用完整性

DELETe titles
WHERe type = ‘business’

消息 547,级别 16,状态 0,第 1 行
DELETE 语句与 REFERENCE 约束
“FK__titleauth__title__1ED998B2”
冲突。该冲突发生于数据库"pubs",
表"dbo.titleauthor", column ‘title_id’。
语句已终止。

INSERT

SELECT * FROM publishers

([pub_id]=‘1756’ OR [pub_id]=‘1622’ OR [pub_id]=‘0877’
OR [pub_id]=‘0736’ OR [pub_id]=‘1389’
OR [pub_id] like ‘99[0-9][0-9]’)

INSERT publishers (pub_id, pub_name, city, state, country)
VALUES (‘9910’, ‘111’, ‘cs’, ‘CS’, ‘CHN’)

–尽量少写,列名和对应的value,务必注意数据完整性
INSERT publishers (pub_id)
VALUES (‘9911’)

SELECt * FROM jobs

INSERT jobs (job_desc, min_lvl, max_lvl)
VALUES (‘aaa’, 26, 101)

INSERT jobs (min_lvl, max_lvl)
VALUES (26, 101)

INSERT jobs (max_lvl)
VALUES (101)

INSERT jobs (job_desc, min_lvl, max_lvl)
VALUES (‘aaaaaa’, 35, 101)

INSERT jobs (job_desc, min_lvl, max_lvl)
VALUES (‘aaaaaa’, 200, 101)

INSERT jobs
VALUES (‘aaaaaa’, 200, 101)

INSERT publishers (pub_id, pub_name, city, state, country)
VALUES (‘9910’, ‘111’, ‘cs’, ‘CS’, ‘CHN’)

INSERT publishers
VALUES (‘9912’, ‘111’, ‘cs’, ‘CS’, ‘CHN’)

利用select 去insert

SELECT *
INTO 创建新表

INSERT mytitle2
SELECT * FROM titles

SELECt * FROM mytitle2

INSERT jobs
SELECt LEFt(title,10), 25, 101
FROM titles

SELECt * FROM jobs

update 1:update一个表里面所有行的某个/某几个列的信息

UPDATE titles
SET price = price * 100

SELECT * FROM titles
ORDER BY price desc

UPDATe titles
SET royalty = 1

UPDATE titles
SET price = price * 0.01, royalty = 10

2:update带条件,符合条件的才修改

UPDATE titles
SET price = price * 100
WHERe title like ‘%computer%’

3:update的条件来自另外一个表

加州出版社出版的书涨价200%

SELECT * FROM publishers WHERe state = ‘CA’

SELECt *
FROM publishers as p join titles as t
on (p.pub_id = t.pub_id)
WHERe p.state = ‘CA’

SELECt * FROM titles
WHERe pub_id in
(SELECt pub_id FROM publishers WHERe state = ‘CA’)

UPDATe titles
SET price = 111
WHERe pub_id in
(SELECT pub_id FROM publishers WHERe state = ‘CA’)

UPDATe titles
SET price = 1
FROM titles as t join publishers as p
on (p.pub_id = t.pub_id)
WHERe p.state = ‘CA’

–在书店表里面累计卖出去30本以上的书,降价10% (sales-qty, titles-price)
SELECt * FROM sales

SELECt title_id, sum(qty)
FROM sales
GROUP BY title_id
HAVINg sum(qty) > 30

UPDATe titles
SET price = price * 0.9
WHERe title_id in
(
SELECT title_id
FROM sales
GROUP BY title_id
HAVINg sum(qty) > 30
)

DDL

数据定义语句,定义:数据对象
数据对象是什么?表、列、索引、主键、默认值
DDL包含:增删改

增:CREATE
删:DROp
改:ALTER

DROP TABLE authors


SQL编程的语法元素 2个常用的数据类型转换函数

CAST ( expression AS data_type )
CONVERT (data_type[(length)], expression [, style])

SELECT title, price, title + ’ ’ + price --错误
FROM titles

SELECt title, price, title + ’ ’ + CAST(price as varchar(20))
FROM titles

SELECt title, price, title + ’ ’ + CONVERT(varchar(20), price)
FROM titles

–价格以“1”开头的书
SELECt *
FROM titles
WHERe CAST(price as varchar(20)) like ‘1%’

SELECt CONVERT(varchar(20), pubdate, 3)
FROM titles

变量

DECLARE
@myvar varchar(20)

SET @myvar = ‘business’

SELECt * FROM titles
WHERe type = @myvar

DECLARE
@mymaxprice int,
@myminprice int

SET @mymaxprice = 10
SET @myminprice = 1

SELECt * FROM titles
WHERe price BETWEEN @myminprice and @mymaxprice

DECLARE
@user_input varchar(20)

SET @user_input = ‘%’ + ‘Co’ + ‘%’

SELECt title, price, pub_name, au_fname, au_lname
FROM titleauthor as te inner join titles as t on (te.title_id = t.title_id)
inner join authors as a on (te.au_id = a.au_id)
inner join publishers as p on (t.pub_id = p.pub_id)
WHERe title like @user_input or pub_name like @user_input
or au_fname like @user_input or au_lname like @user_input

练习
查找雇员编号 = ??? 的记录 Employee表(用变量控制)

DECLARE
@myempid varchar(20)

SET @myempid = ‘PMA42628M’

SELECt * FROM employee
WHERe emp_id = @myempid

使用局部变量检索所有姓以 Ring 开头的作者信息,authors表

DECLARE
@mylname varchar(20)

SET @mylname = ‘Ring’

SELECt * FROM authors
WHERe au_lname like @mylname + ‘%’

IF ELSE

用户输入一个书名字,来查书
如果查到了,就返回查到的结果
如果没有查到推荐销量最高的3本书

控制变量的使用

DECLARE
@user_input varchar(100),
@control_count int

SET @user_input = ‘computer’
SET @control_count = (SELECt count(*) FROM titles
WHERe title like ‘%’ + @user_input + ‘%’)
print @control_count

–存在性检验EXIST,也是一种流程控制方法

基本格式

IF xxxxxxxx
BEGIN
xxxxxxxxxxxx
END
ELSE
BEGIN
xxxxxxxxxxxxxxx
END

DECLARE
@user_input varchar(100), --这是用户输入的信息,要模糊匹配
@control_count int --这是控制变量

SET @user_input = ‘xx–4444ikjfjekiwj’
SET @control_count = (SELECt count(*) FROM titles
WHERe title like ‘%’ + @user_input + ‘%’)–给控制变量赋值

IF (@control_count > 0)
BEGIN
SELECt title, price
FROM titles
WHERe title like ‘%’ + @user_input + ‘%’
print 'IF = yes ’ + CAST(@control_count as varchar(20))
END
ELSE BEGIN
SELECt TOP 3 title, price FROM titles
ORDER BY ytd_sales DESC --print ‘IF = no’ + CAST(@control_count as varchar(20))
END

--------例子----
如果存在价格在¥10~¥20书,就显示一句话“有”,
并且把书名和单价列表显示出来
如果没有就显示一句“没有”,并显示价格小于¥10的书名和单价

DECLARE
@inputminprice int,
@inputmaxprice int,
@price_control int

SET @inputminprice = 1
SET @inputmaxprice = 2
SET @price_control = (
SELECt count(*) FROM titles WHERe price BETWEEN @inputminprice and @inputmaxprice
)

IF @price_control > 0
BEGIN
print ‘Yes’
SELECt * FROM titles WHERe price BETWEEN @inputminprice and @inputmaxprice
END
ELSE
BEGIN
print ‘NO’
SELECt * FROM titles WHERe price < @inputminprice or price > @inputmaxprice
END

WHILE 循环

SELECt title, price
FROM titles

UPDATe titles
SET price = price * 1.1

DECLARE
@control_price numeric(10,3)

SET @control_price = (SELECT max(price) FROM titles)

WHILE (@control_price < 20)
BEGIN
print ‘!’

UPDATe titles
SET price = price * 1.1
SET @control_price = (SELECT max(price) FROM titles)

END

循环与流程结合

DECLARE
@control_price numeric(10,3)

SET @control_price = (SELECt max(price) FROM titles)

WHILE (@control_price < 30)
BEGIN
print ‘!’

UPDATe titles
SET price = price * 1.1

SET @control_price = (SELECT max(price) FROM titles)

IF (SELECt avg(price) FROM titles) > 25
BEGIN
  BREAK
END

END

CASE

SELECt pub_name,
CASE state
WHEN ‘MA’ THEN ‘马萨诸塞州’
WHEN ‘DC’ THEN ‘华盛顿州’
WHEN ‘CA’ THEN ‘加州’
ELSE ‘我也不知道’
END
FROM publishers

创建一个视图




方便用户查询一些复杂的数据关系
接着利用参数来从视图中查询数据

create View View_Test
as
SELECt dbo.authors.au_lname AS 姓, dbo.authors.au_fname AS 名, dbo.titles.title AS 书名,
dbo.titles.price * 0.9 AS 打折单价, dbo.publishers.pub_name AS 出版社
FROM dbo.authors INNER JOIN
dbo.titleauthor ON dbo.authors.au_id = dbo.titleauthor.au_id INNER JOIN
dbo.titles ON dbo.titleauthor.title_id = dbo.titles.title_id INNER JOIN
dbo.publishers ON dbo.titles.pub_id = dbo.publishers.pub_id

declare @MyPrice money

Set @MyPrice = 10

select * from view_test
where 打折单价 > @MyPrice


/使用带有复杂 SELECt 语句的简单存储过程 把视图的功能给实现了 注意实现的机制和方法不同/

create procedure p_Test
as
SELECT dbo.authors.au_lname AS 姓, dbo.authors.au_fname AS 名, dbo.titles.title AS 书名,
dbo.titles.price * 0.9 AS 打折单价, dbo.publishers.pub_name AS 出版社
FROM dbo.authors INNER JOIN
dbo.titleauthor ON dbo.authors.au_id = dbo.titleauthor.au_id INNER JOIN
dbo.titles ON dbo.titleauthor.title_id = dbo.titles.title_id INNER JOIN
dbo.publishers ON dbo.titles.pub_id = dbo.publishers.pub_id

exec p_test


/创建使用带单个参数的存储过程/

alter procedure p_Test @PPPrice money
as
SELECt dbo.authors.au_lname AS 姓, dbo.authors.au_fname AS 名, dbo.titles.title AS 书名,
dbo.titles.price * 0.9 AS 打折单价, dbo.publishers.pub_name AS 出版社
FROM dbo.authors INNER JOIN
dbo.titleauthor ON dbo.authors.au_id = dbo.titleauthor.au_id INNER JOIN
dbo.titles ON dbo.titleauthor.title_id = dbo.titles.title_id INNER JOIN
dbo.publishers ON dbo.titles.pub_id = dbo.publishers.pub_id
WHERe dbo.titles.price * 0.9 > @PPPrice

exec p_test 100
exec p_test @PPPrice = 100

存储过程

:是SQL Server中一组预先定义并编译好的SQL语句,可以接受参数、返回状态值和参数值,并且可以嵌套使用。

使用存储过程的优点:

在许多应用程序中,不同的程序经常运行一些相同的操作,将其转换成存储过程,以便简化和加快应用程序。
共享存储过程。程序员共享,无需自己编写,避免潜在的错误。减少开发时间:
程序员无需了解其内部结构,只需了解存储过程的名称、参数、返回值和作用即可。(模块性)
简化软件分发 存储过程将应用程序逻辑转移到服务器上,可独立于软件的升级;
使用内存高速缓存 SQL Server 可以将存储过程、数据和索引页都放到高速缓存中,从而避免从磁盘中读取。这种节省是相当可观的;
减少在服务器和客户端之间的数据交换 网络传输的仅是请求和计算结果,处理过程均在服务器端进行。

练习:

在Northwind中建立一个存储过程p_ProductQuery
输入一个产品的名称,就给出这个产品的产品名称ProductName,供应商的名称CompanyName,单价UnitPrice

EXEC p_ProductQuery ‘Hot Pepper’

ProductName CompanyName UnitPrice


Louisiana Fiery Hot Pepper Sauce New Orleans Cajun Delights 21.0500

练习:

在Northwind中建立一个存储过程p_CustomLastOrderDetail
输入一个顾客Customer的编号CustomerID,就给出这个顾客最近一次订单的清单(产品名称,订货个数)

exec p_CustomLastOrderDetail ‘TOMSP’

CustomerID ProductName Quantity OrderID OrderDate


TOMSP Teatime Chocolate Biscuits 12 10967 1998-03-23 00:00:00.000
TOMSP Maxilaku 40 10967 1998-03-23 00:00:00.000

提示:
创建存储过程用CREATE Procedure p_ProductQuery
@InputProductName varchar(50)
AS
…

  运行存储过程用exec p_test @PPPrice = 100
  删除存储过程用drop procedure p_ProductQuery
  修改存储过程用alter procedure p_ProductQuery

------------------------答案--------------------------

CREATE Procedure p_ProductQuery
@InputProductName varchar(50)
AS
SELECT dbo.Products.ProductName, dbo.Suppliers.CompanyName, dbo.Products.UnitPrice
FROM dbo.Products INNER JOIN dbo.Suppliers ON dbo.Products.SupplierID = dbo.Suppliers.SupplierID
WHERe dbo.Products.ProductName LIKE ‘%’+@InputProductName+‘%’

------------------------执行实例----------------------------
EXEC p_ProductQuery ‘Hot Pepper’

-----------------------答案A 子查询---------------------------

ALTER PROCEDURE p_CustomLastOrderDetail
@InputOrderID VarChar(20)
AS
SELECt dbo.Orders.CustomerID, dbo.Products.ProductName, dbo.[Order Details].Quantity,
dbo.Orders.OrderID, dbo.Orders.OrderDate
FROM dbo.[Order Details] INNER JOIN
dbo.Orders ON dbo.[Order Details].OrderID = dbo.Orders.OrderID INNER JOIN
dbo.Products ON dbo.[Order Details].ProductID = dbo.Products.ProductID
WHERe dbo.[Order Details].OrderID =
(
SELECt OrderID FROM Orders WHERe CustomerID = @InputOrderID AND OrderDate =
(
SELECt MAX(OrderDate) FROM Orders WHERe CustomerID = @InputOrderID
)
)

------------------------执行实例----------------------------
EXEC p_CustomLastOrderDetail ‘ALFKI’

-----------------------答案B 分步骤编写代码---------------------------

create procedure p_FindLastOrderDetail
@InputCustomerID varchar(30)
as
declare
@LastDatetime datetime,
@FindOrderID int

SET @LastDatetime =(select Max(orderdate) from orders where customerID = @InputCustomerID )

SET @FindOrderID = (select OrderID from orders
where orderdate = @LastDatetime and customerID = @InputCustomerID)

SELECt dbo.Orders.CustomerID, dbo.Products.ProductName, dbo.[Order Details].Quantity,
dbo.Orders.OrderID, dbo.Orders.OrderDate
FROM dbo.[Order Details] INNER JOIN
dbo.Orders ON dbo.[Order Details].OrderID = dbo.Orders.OrderID INNER JOIN
dbo.Products ON dbo.[Order Details].ProductID = dbo.Products.ProductID
where dbo.[Order Details].orderid = @FindOrderID

------------------------执行实例----------------------------
exec p_FindLastOrderDetail ‘TOMSP’


/创建使用带多个参数的存储过程/

Create procedure p_test
@Lname varchar(20),
@Fname varchar(30)
as

SELECt dbo.authors.au_lname AS 姓, dbo.authors.au_fname AS 名, dbo.titles.title AS 书名,
dbo.titles.price * 0.9 AS 打折单价, dbo.publishers.pub_name AS 出版社
FROM dbo.authors INNER JOIN
dbo.titleauthor ON dbo.authors.au_id = dbo.titleauthor.au_id INNER JOIN
dbo.titles ON dbo.titleauthor.title_id = dbo.titles.title_id INNER JOIN
dbo.publishers ON dbo.titles.pub_id = dbo.publishers.pub_id
WHERe dbo.authors.au_lname LIKE @Lname AND dbo.authors.au_fname LIKE @Fname

exec p_test @fname = ‘a%’ ,@lname = ‘b%’


/创建使用带默认值的多个参数的存储过程/

alter procedure p_Test
@Lname varchar(20) = ‘t%’,
@Fname varchar(30) = ‘a%’
as

SELECt dbo.authors.au_lname AS 姓, dbo.authors.au_fname AS 名, dbo.titles.title AS 书名,
dbo.titles.price * 0.9 AS 打折单价, dbo.publishers.pub_name AS 出版社
FROM dbo.authors INNER JOIN
dbo.titleauthor ON dbo.authors.au_id = dbo.titleauthor.au_id INNER JOIN
dbo.titles ON dbo.titleauthor.title_id = dbo.titles.title_id INNER JOIN
dbo.publishers ON dbo.titles.pub_id = dbo.publishers.pub_id
WHERe dbo.authors.au_lname LIKE @Lname AND dbo.authors.au_fname LIKE @Fname

exec p_test ‘t’,‘d’

exec p_test

exec p_test @Fname = ‘d%’,@Lname = ‘f%’



–return的用法
–正数的话 返回1,负数的话 返回-1,0的话 返回0

–嵌套if else
create procedure p_Test2
@Choice int
as
if @Choice > 0
begin
select 1 as ‘result’
end
else if @Choice < 0
begin
select -1 as ‘result’
end
else
begin
select 0 as ‘result’
end

exec p_test2 0

不嵌套if else 不用return

create procedure p_Test3
@Choice int
as
print ‘1 y’
if @Choice > 0
begin
select 1 as ‘result’
end;
print ‘2 y’
if @Choice < 0
begin
select -1 as ‘result’
end
print ‘3 n’
if @Choice = 0
begin
select 0 as ‘result’
end

exec p_test3 554

–不嵌套if else 用return
alter procedure p_Test2
@Choice int
as
print ‘1 y’
if @Choice > 0
begin
select 1 as ‘result’
return
end;
print ‘2 y’
if @Choice < 0
begin
select -1 as ‘result’
return
end
print ‘3 n’
if @Choice = 0
begin
select 0 as ‘result’
return
end

exec p_Test2 -45

直接用return

alter procedure p_Test2
@Choice int
as
if @Choice > 0 return 1
else if @Choice < 0 return -1
else return 0

DECLARE @return_status int

EXEC @return_status = p_Test2 -232

SELECT ‘Return Status’ = @return_status


–用存储过程调用另外一个存储过程 并且利用被调用的存储过程的 返回值

create procedure p_Test2_father
@Choice int
as

DECLARE @return_status int
EXEC @return_status = p_Test2 @Choice

if @return_status = 1
begin
select ‘is 正数’ as ‘result’
return
end;
if @return_status = -1
begin
select ‘is 负数’ as ‘result’
return
end
if @return_status = 0
begin
select ‘is 0’ as ‘result’
return
end

exec p_Test2_father 23232


–OUTPUT参数

CREATE PROCEDURE mathtutor
@m1 smallint, @m2 smallint, @result smallint OUTPUT
AS
SET @result = @m1 * @m2

DECLARE @answer smallint
EXECUTE mathtutor 5,6,@answer OUTPUT
SELECT ‘结果是:’, @answer


–Error控制
–@@ERROR 返回最后执行的 Transact-SQL 语句的错误代码。

–@@ERROR 检测一个特定错误
–下面的示例用 @@ERROR 在一个 UPDATE 语句中检测限制检查冲突。

USE pubs
GO
UPDATE authors SET au_id = ‘172 32 1176’
WHERe au_id = “172-32-1176”

INSERT authors
VALUES(‘409-56-7008’, ‘Bennet’, ‘Abraham’, ‘415 658-9932’,
‘6223 Bateman St.’, ‘Berkeley’, ‘CA’, ‘94705’, 1)

IF @@ERROR = 547
print “A check constraint violation occurred”

–用 @@ERROR 有条件地退出一个过程
–在此示例中,IF…ELSE 语句在存储过程中的 INSERT 语句后检测 @@ERROR。@@ERROR 变量的值将决定传给调用程序的返回值,以指示此过程的成功与失败。

USE pubs
GO

– Create the procedure.
CREATE PROCEDURE add_author
@au_id varchar(11),@au_lname varchar(40),
@au_fname varchar(20),@phone char(12),
@address varchar(40) = NULL,@city varchar(20) = NULL,
@state char(2) = NULL,@zip char(5) = NULL,
@contract bit = NULL
AS

– Execute the INSERT statement.
INSERT INTO authors
(au_id, au_lname, au_fname, phone, address,
city, state, zip, contract) values
(@au_id,@au_lname,@au_fname,@phone,@address,
@city,@state,@zip,@contract)

– Test the error value.
IF @@ERROR <> 0
BEGIN
– Return 99 to the calling program to indicate failure.
PRINT “An error occurred loading the new author information”
RETURN(99)
END
ELSE
BEGIN
– Return 0 to the calling program to indicate success.
PRINT “The new author information has been loaded”
RETURN(0)
END
GO

USE pubs
GO

– Create the procedure.
CREATE PROCEDURE add_author
@au_id varchar(11),@au_lname varchar(40),
@au_fname varchar(20),@phone char(12),
@address varchar(40) = NULL,@city varchar(20) = NULL,
@state char(2) = NULL,@zip char(5) = NULL,
@contract bit = NULL
AS

– Execute the INSERT statement.
INSERT INTO authors
(au_id, au_lname, au_fname, phone, address,
city, state, zip, contract) values
(@au_id,@au_lname,@au_fname,@phone,@address,
@city,@state,@zip,@contract)

– Test the error value.
IF @@ERROR <> 0
BEGIN
– Return 99 to the calling program to indicate failure.
RAISERROR (‘数据插入失败’,16,1)
RETURN(99)
END
GO


触发器

CREATE TRIGGER
ON
FOR DELETE, INSERT, UPDATE
AS
BEGIN
–RAISERROR (50009, 16, 10)
END

删除触发器
增加触发器
更新触发器

SELECT *
INTO aaa
FROM titles

SELECt * FROM aaa

CREATE TRIGGER t_del_something
ON aaa
FOR DELETe
AS
BEGIN
print ‘Just now, you conduct a delete operation’
END

DELETE aaa WHERe title_id = ‘BU1032’
DELETE aaa WHERe title_id = ‘BU1032999999’
DELETE aaa WHERe type = ‘business’

CREATE TRIGGER t_del_something_double_check
ON aaa
FOR DELETE
AS
BEGIN
print ‘This is a double check for the delete operation’
END

-------------------如何获取触发操作相关的数据---------------
在SQL SERVER中,所有的增删改操作,都会暂存到三个临时表里面
删除触发器 deleted
增加触发器 inserted
更新触发器 updated

ALTER TRIGGER t_del_iknow
ON aaa
FOR DELETE
AS
BEGIN
DECLARE @mytitle varchar(100), @mytype varchar(20)
SET @mytitle = (SELECT title FROM deleted)
SET @mytype = (SELECT type FROM deleted)
print 'Just now, the deleted book is ’ + @mytitle
print 'Just now, the type of the deleted book is ’ + @mytype
END


–每次添加一本书的销售纪录就要修改这本书的总销量
–每次添加一本书的销售纪录就要给写书的作者1元钱的版权费

CREATE TRIGGER t_sale_then_dosomething
ON sales
FOR INSERT
AS
BEGIN
DECLARE @mytitleid varchar(100), @myqty int
SET @mytitleid = (SELECT title_id FROM inserted)
SET @myqty = (SELECt qty FROM inserted)

UPDATe titles SET ytd_sales = ytd_sales + @myqty
WHERe title_id = @mytitleid

UPDATE titleauthor SET royaltyper = royaltyper + @myqty
WHERe title_id = @mytitleid
END

SELECT title_id, title, ytd_sales FROM titles
SELECt title_id, qty FROM sales
SELECt * FROM titleauthor WHERe title_id = ‘BU1032’
SELECt * FROM sales
INSERT INTO sales
VALUES (6380, ‘aaaa’, ‘1990-1-1’, 1000, ‘Net 60’, ‘BU1032’)


在雇员表里面增加一行记录,select * from employee
job_lvl 必须between 对应job_id的 min and max in jobs
SELECt * FROM jobs paolo的job_id是13,其min=25,max=100
如果输入的job_lvl小于min则=min,如果输入的job_lvl大于max则=max

CREATE TRIGGER t_emp_check_lvl
ON employee
FOR INSERT
AS
BEGIN
DECLARE @myempid varchar(20), @myjobid int, @myjoblvl int,
@realminlvl int, @realmaxlvl int

SET @myempid = (SELECt emp_id FROM inserted)
SET @myjobid = (SELECt job_id FROM inserted)
SET @myjoblvl = (SELECt job_lvl FROM inserted)

SET @realminlvl = (SELECt min_lvl FROM jobs WHERe job_id = @myjobid)
SET @realmaxlvl = (SELECt max_lvl FROM jobs WHERe job_id = @myjobid)

IF @myjoblvl < @realminlvl
BEGIN
UPDATe employee SET job_lvl = @realminlvl
WHERe emp_id = @myempid
END

IF @myjoblvl > @realmaxlvl
BEGIN
UPDATE employee SET job_lvl = @realmaxlvl
WHERe emp_id = @myempid
END

END

INSERT INTO employee
VALUES (‘PMA42888M’, ‘Zhao’, ‘M’, ‘Ying’, 13, 150, ‘0877’, ‘1992-2-2’)

SELECT * FROM employee

索引

簇索引
非簇索引


数据库的可靠性 备份 完整数据库备份

备份数据库的完整复本。

差异备份

备份自上一次完整数据库备份后修改过的数据库页。

事务日志备份

仅备份事务日志。

文件或文件组备份 事务是事务的ACID特性:

原子性(Atomicity):恢复和并发控制的基本单位,要不全执行,要不全不执行
一致性(Consistency):事务前和后数据库完整性保持住,但事务中可以变化
隔离性(Isolation):一个事务执行时不被其他事务干扰
持续性(Durability ):事务执行后,数据的改变将是永久性的

并发控制

为了提高系统的吞吐率,有多个事务往往被并发的执行。如果事务并发操作不当就会产生“丢失修改”、“读脏数据”和“不可重复读”等数据不一致的问题。

并发操作带来的数据不一致性
丢失修改(lost update)
不可重复读(non-repeatable read)
读“脏”数据(dirty read)

数据库的安全性 数据库的完整性


转载请注明:文章转载自 www.mshxw.com
本文地址:https://www.mshxw.com/it/986470.html
我们一直用心在做
关于我们 文章归档 网站地图 联系我们

版权所有 (c)2021-2022 MSHXW.COM

ICP备案号:晋ICP备2021003244-6号