黑马程序员—SQL系列 (三)

---------------------- ASP.Net+Android+IO开发S、 .Net培训、期待与您交流！ ----------------------

前言：可通过目录快速查阅

SQL语句入门

5.索引与约束

1)索引

创建索引

--在T_Person 表的FName和FAge 字段上创建索引，idx_person_nameage
CREATE INDEX idx_person_nameage ON T_Person(FName,FAge) 

删除索引

DROP INDEX T_Person.idx_person_name; 
DROP INDEX T_Person.idx_person_nameage; 

2)约束

非空约束 

CREATE  TABLE  T_Person  (FNumber  VARCHAR(20)  NOT  NULL  ,FName 
VARCHAR(20),FAge INT)
INSERT INTO T_Person (FNumber, FName, FAge) VALUES ( NULL , 'kingchou', 20) 
错误信息： 不能将值 NULL 插入列 'FNumber'，表 'demo.dbo.T_Person'；列不允许有空值。INSERT 失败

唯一约束 
单字段唯一约束：

CREATE TABLE T_Person (FNumber VARCHAR(20) UNIQUE, 
FName VARCHAR(20),FAge INT) 
--已存在FNumber 为2的记录
INSERT INTO T_Person (FNumber, FName, FAge) VALUES ( '2' , 'kitty', 20) 
错误信息： 违反了 UNIQUE KEY 约束 'UQ__T_Person__1A14E395'。不能在对象 'dbo.T_Person' 中插入重复键

复合唯一约束：

CREATE TABLE T_Person (FNumber VARCHAR(20), 
FDepartmentNumber VARCHAR(20), 
FName VARCHAR(20),FAge INT, 
CONSTRAINT unic_dep_num UNIQUE(FNumber,FDepartmentNumber))
--FNumber等于'2'且FDepartmentNumber等于'sales001'的记录在表中已经存在
INSERT INTO T_Person (FNumber, FDepartmentNumber,FName, FAge)  VALUES ( '2' , 'sales001', 'daxia', 30)
错误信息： 违反了 UNIQUE KEY 约束 'unic_dep_num'。不能在对象 'dbo.T_Person' 中插入重复键

在已经创建好的数据表上添加新的唯一约束

--为T_Person表添加一个建立在字段FName和字段FAge上的新的唯一约束
ALTER TABLE T_Person ADD  CONSTRAINT unic_3 UNIQUE(FName, FAge) 

删除已经创建好的复合唯一约束

ALTER TABLE T_Person DROP CONSTRAINT unic_1; 
ALTER TABLE T_Person DROP CONSTRAINT unic_2; 
ALTER TABLE T_Person DROP CONSTRAINT unic_3; 

CHECK约束 

CREATE TABLE T_Person ( 
FNumber VARCHAR(20),FName VARCHAR(20), 
FAge INT CHECK(FAge >0), 
FWorkYear INT CHECK(FWorkYear>0)) 
INSERT INTO T_Person(FNumber, FName, FAge, FWorkYear)   VALUES('001','John',25,-3) 
错误信息： INSERT 语句与 CHECK 约束"CK__T_Person__FWorkY__24927208"冲突。该冲突发生于数据库"demo"，表"dbo.T_Person", column 'FWorkYear'。

在CHECK约束中使用函数

CREATE TABLE T_Person ( 
FNumber VARCHAR(20) CHECK (LEN(FNumber)>12), 
FName VARCHAR(20), 
FAge INT CHECK(FAge >0), 
FWorkYear INT CHECK(FWorkYear>0)) 

引用其他列字段

CREATE TABLE T_Person ( 
FNumber VARCHAR(20), 
FName VARCHAR(20), 
FAge INT, 
FWorkYear INT , 
CONSTRAINT ck_1 CHECK(FWorkYear< FAge))

添加新的约束

ALTER TABLE T_Person ADD CONSTRAINT ck_2 CHECK(FAge>14) 

删除CHECK约束

ALTER TABLE T_Person DROP CONSTRAINT ck_2;

主键约束

单一主键

CREATE TABLE T_Person (FNumber VARCHAR(20) PRIMARY KEY, 
FName VARCHAR(20),FAge INT) 

复合主键

CREATE TABLE T_Person (FNumber VARCHAR(20), 
FName VARCHAR(20),FAge INT, 
CONSTRAINT pk_1 PRIMARY KEY(FNumber,FName))

创建表以后添加主键

--组成主键的字段必须包含NOT NULL约束
CREATE TABLE T_Person (FNumber VARCHAR(20) NOT NULL, 
FName VARCHAR(20) NOT NULL,FAge INT) 
ALTER TABLE T_Person ADD CONSTRAINT pk_1 PRIMARY KEY(FNumber,FName) 

删除主键约束

ALTER TABLE T_Person DROP CONSTRAINT pk_1; 

外键约束 

CREATE TABLE T_AUTHOR 
( 
  FId VARCHAR(20) PRIMARY KEY, 
  FName VARCHAR(100), 
  FAge INT, 
  FEmail VARCHAR(20) 
); 
CREATE TABLE T_Book 
( 
  FId VARCHAR(20) PRIMARY KEY, 
  FName VARCHAR(100), 
  FPageCount INT, 
  FAuthorId VARCHAR(20) , 
  FOREIGN KEY (FAuthorId) REFERENCES T_AUTHOR(FId) 
); 

创建表之后添加外键约束

ALTER TABLE T_Book ADD CONSTRAINT fk_book_author FOREIGN KEY (FAuthorId) REFERENCES T_AUTHOR(FId)

6.表连接 

1)内连接（INNER JOIN）

INNER JOIN table_name ON condition 
其中table_name为被关联的表名，condition则为进行连接时的条件

--检索所有的客户姓名为MIKE的客户的订单号以及订单价格 
SELECT FNumber,FPrice FROM T_Order INNER JOIN T_Customer 
ON FCustomerId= T_Customer.FId WHERE T_Customer.FName='TOM' 

使用表别名来简化SQL语句的编写

--检索所有的订单以及它们对应的客户的相关信息的功能
SELECT o.FId,o.FNumber,o.FPrice, c.FId,c.FName,c .FAge 
FROM T_Order o JOIN T_Customer c ON o.FCustomerId= c.FId 

2)不等值连接 

--检索价格小于每个客户的年龄的五倍值的订单列表
SELECT T_Order.FNumber,T_Order.FPrice, T_Customer.FName,T_Customer.FAge 
FROM T_Order  INNER JOIN T_Customer 
ON T_Order.FPrice< T_Customer.FAge*5 

3)交叉连接

隐式

SELECT T_Customer.FId, T_Customer.FName, T_Customer.FAge, 
T_Order.FId, T_Order.FNumber, T_Order.FPrice 
FROM T_Customer, T_Order 

显式

SELECT T_Customer.FId, T_Customer.FName, T_Customer.FAge, 
T_Order.FId, T_Order.FNumber, T_Order.FPrice 
FROM T_Customer 
CROSS JOIN T_Order 

4)自连接

SELECT o1.FNumber,o1.FPrice,o1.FTypeId,
o2.FNumber,o2.FPrice,o2.FTypeId 
FROM T_Order o1 
INNER JOIN T_Order o2 
ON o1.FTypeId=o2.FTypeId and o1.FId<o2.FId 

5)外部连接 

外部连接的语法与内部连接几乎是一样的，主要区别就是对于空值的处理

外部连接分为三种类型：右外部连接（RIGHT OUTER JOIN）、左外部连接（LEFT OUTER JOIN）和全外部连接（FULL OUTER JOIN）
三者的共同点是都返回符合连接条件的数据，这一点是和内部连接是一样的

三者不同点说明如下： 
左外部连接还返回左表中不符合连接条件的数据； 
左外部连接还返回右表中不符合连接条件的数据； 
全外部连接还返回左表中不符合连接条件的数据以及右表中不符合连接条件的数据，它其实是左外部连接和左外部连接的合集。 

左外部连接 

--查询每张订单的订单号、价格、对应的客户姓名以及客户年龄，如果没有对应的客户，则在客户信息处显示空格
SELECT o.FNumber,o.FPrice,o.FCustomerId, c.FName,c.FAge 
FROM T_Order o LEFT OUTER JOIN T_Customer c 
ON o.FCustomerId=c.FId 

右外部连接 

--查询每张订单的信息以及对应的客户信息，与左外部连接正好相反，在右外部连接中不管是否成功匹配连接条件都会返回右表中的所有记录
SELECT o.FNumber,o.FPrice,o.FCustomerId, c.FName,c.FAge 
FROM T_Order o 
RIGHT OUTER JOIN T_Customer c 
ON o.FCustomerId=c.FId 

全外部连接 

--使用全外部连接查询每张订单的信息以及对应的客户信息，返回左表中不符合连接条件的数据以及右表中不符合连接条件的数据 
SELECT o.FNumber,o.FPrice,o.FCustomerId, c.FName,c.FAge 
FROM T_Order o 
FULL OUTER JOIN T_Customer c 
ON o.FCustomerId=c.FId

7.子查询 

1)子查询入门

单值子查询(标量子查询)

如果一个子查询返回值不止一行记录或者有多个列的话都不能当作标量子查询使用，否则会出错

SELECT  1  AS  f1,2,(SELECT  MIN(FYearPublished)  FROM  T_Book),(SELECT MAX(FYearPublished)  FROM T_Book) AS f4 

列值子查询 (表子查询)

SELECT T_Reader.FName,t2.FYear,t2.FName ,t2.F3 
FROM T_Reader, 
(SELECT FYearPublished AS FYear,FName,1+2 as F3 FROM T_Book WHERE FYearPublished < 1800) t2 

2)SELECT列表中的标量子查询 

--标量子查询完全可以返回随当前查询记录而变化的值，返回一类图书的最新出版年份
SELECT FId,FName, 
( 
SELECT MAX(FYearPublished) 
FROM T_Book 
WHERE T_Book.FCategoryId= T_Category.FId 
) 
FROM T_Category 

3)WHERE子句中的标量子查询 

--检索每一种书籍类别中出版年份最早的书籍的名称，如果有两本或者多本书籍在同一年出版，则均显示它们的名字
SELECT T_Category.FId, T_Book. FName,T_Book.FYearPublished 
FROM T_Category  
INNER JOIN T_Book ON T_Category.FId=T_Book.FCategoryId 
WHERE T_Book.FYearPublished= 
  ( 
  SELECT MIN(T_Book.FYearPublished)  
  FROM T_Book  
  WHERE T_Book.FCategoryId=T_Category.FId 
  ) 

4)集合运算符与子查询 

IN 运算符 

SELECT * FROM T_Reader  
WHERE FYearOfJoin IN 
( 
select FYearPublished FROM T_Book 
) 

 ANY和SOME运算符
ANY，SOME的用法和功能一模一样

--检索在任何一个会员出生之前出版的图书
SELECT * FROM T_Book 
WHERE FYearPublished<ANY 
( 
  SELECT FYearOfBirth FROM T_Reader 
) 

“=ANY”等价于IN 运算符，而“<>ANY”则等价于NOT IN 运算符，和IN 运算符不同，ANY运算符不能与固定的集合相匹配
ALL运算符 

--检索在所有会员入会之前出版的图书： 
SELECT * FROM T_Book 
WHERE FYearPublished<ALL 
( 
  SELECT FYearOfJoin FROM T_Reader 
) 

ALL运算符同样不能与固定的集合相匹配
EXISTS运算符

EXISTS运算符用来检查每一行是否匹配子查询，可以认为EXISTS就是用来测试子查询的结果是否为空，如果结果集为空则匹配结果为false，否则匹配结果为true

--存在检索出所有的，不存在的话执行结果为空
SELECT * FROM T_Book 
WHERE EXISTS 
( 
  SELECT * FROM T_Reader 
  WHERE FProvince='ShanDong' 
) 

5)在其他类型SQL语句中的子查询应用 

在其他类型SQL语句中的子查询应用

--将T_ReaderFavorite中的输入复制插入到T_ReaderFavorite2表
INSERT INTO T_ReaderFavorite2(FCategoryId,FReaderId) SELECT FCategoryId,FReaderId FROM T_ReaderFavorite 

子查询在UPDATE语句中的应用

--将图书的出版日期全部更新为所有图书中的最新出版日期
UPDATE T_Book  SET FYearPublished= (SELECT MAX(FYearPublished) FROM T_Book) 

子查询在DELETE语句中的应用

--将所有同类书本书超过3本的图书删除： 
DELETE FROM T_Book b1 
WHERE  
( 
SELECT COUNT(*) FROM T_Book b2  
WHERE b1. FCategoryId=b2. FCategoryId 
)>3 

---------------------- ASP.Net+Android+IO开发S、 .Net培训、期待与您交流！ ----------------------