第一章 数据库基础
1.1 数据库体系结构
数据库领域公认的标准结构是三级模式结构，它包括外模式、概念模式、内模式，有效地组织、管理数据，提高了数据库的逻辑独立性和物理独立性。
1.1.1 用户级--> 外模式(反映了数据库系统的用户观)
外模式又称子模式或用户模式，对应于用户级。它是某个或某几个用户所看到的数据库的数据视图，是与某一应用有关的数据的逻辑表示。外模式是从模式导出的一个子集，包含模式中允许特定用户使用的那部分数据。用户可以通过外模式描述语言来描述、定义对应于用户的数据记录(外模式)，也可以利用数据操纵语言(Data Manipulation Language，DML)对这些数据记录进行操作。
1.1.2 概念级--> 概念模式（反映了数据库系统的整体观）
概念模式又称模式或逻辑模式，对应于概念级。它是由数据库设计者综合所有用户的数据，按照统一的观点构造的全局逻辑结构，是对数据库中全部数据的逻辑结构和特征的总体描述，是所有用户的公共数据视图(全局视图)。它是由数据库管理系统提供的数据模式描述语言(Data Description Language，DDL)来描述、定义的。
1.1.3 物理级 --> 内模式（反映了数据库系统的存储观）
内模式又称存储模式，对应于物理级。它是数据库中全体数据的内部表示或底层描述，是数据库最低一级的逻辑描述，它描述了数据在存储介质上的存储方式和物理结构，对应着实际存储在外存储介质上的数据库。内模式由内模式描述语言来描述、定义的。
总结：
总之，数据按外模式的描述提供给用户；按内模式的描述存储在磁盘上；而概念模式提供了连接这两级模式的相对稳定的中间层，并使得两级中任意一级的改变都不受另一级的牵制。
1.2数据库的两级映射
数据库系统在三级模式之间提供了两级映像：模式/内模式的映像、外模式/模式的映像。
- 模式/内模式的映像：实现概念模式到内模式之间的相互转换。
- 外模式/模式的映像：实现外模式到概念模式之间的相互转换。
1.3 数据库组成
主要数据文件：mdf 次要数据文件：ndf 日志文件：ldf
1.4 命名规则
@开头表示局部变量或参数；#开头表示临时表；##表示全局临时对象。
1.5 数据库基本数据类型
1.5.1 常见数据类型
|
类型 |
大小 |
用途 |
|
TINYINT |
1 字节 |
小整数值 |
|
INT或INTEGER |
4 字节 |
大整数值 |
|
BIGINT |
8 字节 |
极大整数值 |
|
DOUBLE |
8 字节 |
双精度 double（5，2）表示最多5位，其中必须有2位小数，即最大值为（999.99） |
1.5.2 字符串类型
|
类型 |
大小 |
用途 |
|
CHAR |
0-255字节 |
定长字符串 |
|
VARCHAR |
0-65535 字节 |
变长字符串 |
|
TEXT |
0-65 535字节 |
长文本数据 |
|
BLOB |
0-65 535字节 |
二进制形式的长文本数据 |
1.5.3 日期和时间类型
|
类型 |
大小 |
范围 |
格式 |
用途 |
|
DATE |
3 |
1000-01-01/9999-12-31 |
YYYY-MM-DD |
日期值 |
|
TIME |
3 |
'-838:59:59'/'838:59:59' |
HH:MM:SS |
Time类型不仅可以用于表示一天的时间（必须小于24小时）,还可能为某个过去的时间或两个事件的时间间隔（可以大于24小时，或者未负） |
|
YEAR |
1 |
1901/2155 |
YYYY |
年份值 |
|
DATETIME |
8 |
1000-01-01 00:00:00/9999-12-31 23:59:59 |
YYYY- MM-DD HH:MM:SS |
混合日期和时间值 |
|
TIMESTAMP |
4 |
1970-01-01 00:00:00/2038结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07 |
YYYYMMDD HHMMSS |
混合日期和时间值，时间戳 |
1.6 创建用户自定义数据类型
数据库—选择需要自定义的数据库—可编程性—类型。
1.7 数据库的数据完整性
实体完整性、域完整性和引用完整性三类。
1.7.1实体完整性
主键约束
主键约束强制要求被设置成主键的列取值不能为空，也不能重复。它通过PRIMARY KEY子句来实现主键的声明。可以在 CREATE TABLE 或 ALTER TABLE 语句中使用PRIMARY KEY子句，实现主键约束的创建、删除或修改。一个表中只能有一个主键，即只能设置一个主键约束对象。
/*------------------------------------创建时，设置单属性主键*/
CREATE TABLE Student
(
StudentID NCHAR(10) PRIMARY KEY,
Name NCHAR(6),
Sex NCHAR(2)
)
/*------------------------------------创建时，设置联合主键*/
CREATE TABLE Student
(
StudentID NCHAR(10),
Name NCHAR(6),
Sex NCHAR(2),
CONSTRAINT PK_Name2 PRIMARY KEY(StudentID, Name) /*声明联合主键*/
)
/* PS：联合主键是由多个属性组成一个主键。主键只有一个，并不是多个 */
/* PK_name 是主键约束对象的名字 */
/*------------------------------------修改时，设置单属性主键*/
ALTER TABLE Student
ADD myname NCHAR(4) PRIMARY KEY,
mysex NCHAR(2)
/*------------------------------------修改时，设置联合主键*/
ALTER TABLE Student
ADD myname NCHAR(4),
mystid NCHAR(4),
mysex NCHAR(5),
CONSTRAINT PK_name PRIMARY KEY(myname, mystid)
唯一性约束
主键约束一个表只能有一个，但很多时候我们需要除了主键列以外的列没有重复值。这就用到了唯一性约束。唯一性约束与主键约束的效果基本相同，但一个表可以由多个唯一性约束。它通过 UNIQUE子句来实现唯一性约束的声明，可以在 CREATE TABLE 或 ALTER TABLE 语句中使用UNIQUE子句，实现主唯一性约束的创建、删除或修改。
唯一性约束可以允许值为 NULL，单不允许值重复，包括重复的NULL。
/*------------------------------------创建时，设置唯一性约束*/
CREATE TABLE Student
(
StudentID NCHAR(10) PRIMARY KEY,
Name NCHAR(6) UNIQUE,
Sex NCHAR(2),
SchoolID NCHAR(2) UNIQUE,
--这种创建方法，唯一性约束以多个键对象的形式存在--
)
/*------------------------------------创建时，设置唯一性约束*/
CREATE TABLE Student
(
StudentID NCHAR(10) PRIMARY KEY,
Name NCHAR(6),
Sex NCHAR(2),
SchoolID NCHAR(2),
CONSTRAINT ue_name UNIQUE(Name, SchoolID)
--唯一性约束以一个键对象的形式存在--
)
/*------------------------------------修改时，设置唯一性约束*/
ALTER TABLE Student
ADD myname NCHAR(4) UNIQUE,
mysex NCHAR(4),
/*------------------------------------修改时，设置唯一性约束*/
ALTER TABLE Student
ADD myname NCHAR(4),
myscid NCHAR(2),
mysex NCHAR(4),
CONSTRAINT UN_name UNIQUE(myname, myscid)
1.7.2 域完整性
空值约束、默认约束、检查约束
空值约束
即设置列是否可以为空值，通过 NULL或NOT NULL来声明列是否可以为空值，可以在CREATE TABLE 或 ALTER TABLE 语句中使用NULL或NOT NULL子句，实现空值约束的创建、删除或修改。
/*------------------------------------创建时，设置空值约束*/
CREATE TABLE Student
(
StudentID NCHAR(10) PRIMARY KEY,
Name NCHAR(4) NOT NULL, --设置不能为空--
Age INT NULL --设置可以为空--
)
/*------------------------------------修改时，设置空值约束*/
ALTER TABLE Student
ADD Sex NCHAR(4) NULL
主键约束会自动要求不能为NULL，相当于自动设置了 NOT NULL
默认约束
即为列设置默认值。通过DEFAULT子句来为列设置默认值，可以在CREATE TABLE 或 ALTER TABLE 语句中使用DEFAULT子句，实现默认约束的创建、删除或修改。
/*------------------------------------创建时，设置默认约束*/
CREATE TABLE Student
(
StudentID NCHAR(10) PRIMARY KEY,
Name NCHAR(6) DEFAULT('阿珍'),
Sex NCHAR(2) DEFAULT('女'),
)
/*------------------------------------创建时，设置默认约束*/
CREATE TABLE Student
(
StudentID NCHAR(10) PRIMARY KEY,
Name NCHAR(6),
Sex NCHAR(2),
CONSTRAINT Fe_name DEFAULT '女' FOR Sex
)
/*------------------------------------修改时，设置默认约束*/
ALTER TABLE Student
ADD Hobby NCHAR(4) DEFAULT('吃')
/*------------------------------------修改时，设置默认约束*/
ALTER TABLE Student
ADD Hobby1 NCHAR(4),
Hobby2 nchar(2),
CONSTRAINT DF_name DEFAULT '玩' for Hobby1
检查约束
检查约束就是为列设置取值范围，规定数据的值域。可以在CREATE TABLE 或 ALTER TABLE 语句中使用DEFAULT子句来实现检查约束的创建。
/*------------------------------------创建时，设置检查约束*/
CREATE TABLE myinto
(
Name NCHAR(4),
Sex NCHAR(1) CHECK(Sex='男' or Sex='女'),--括号内可以是一个表达式--
Hobby NCHAR(10) NULL,
)
/*------------------------------------创建时，设置检查约束*/
CREATE TABLE myinto
(
Name NCHAR(4),
Sex NCHAR(1),
Hobby NCHAR(10) NULL,
CONSTRAINT CK_name CHECK(Sex='男' or Sex='女')
--一个约束对象只能检查一个列--
)
/*------------------------------------修改时，设置默认约束*/
ALTER TABLE myinto
ADD Lover NCHAR(2) CHECK(Lover='阿珍' or Lover='阿强')
/*------------------------------------修改时，设置默认约束*/
ALTER TABLE myinto
ADD Lover NCHAR(2),
Lover_name NCHAR(1),
CONSTRAINT CK_name CHECK(Lover='阿珍' or Lover='阿强')
1.7.3 引用完整性
第二章 T-SQL概述
2.1 T-SQL语言的组成
数据定义语言(DLL)：用于在数据库系统中对数据库，表，视图，索引等数据库对象进行创建和管理。
数据控制语言(DCL)：用于实现对数据库中数据的完整性，安全性等的控制。
数据操纵语言(DML)：用于插入，修改，删除和查询数据库中的数据。
2.1.1 定义语言
2.1.2 控制语言
2.1.3 操纵语言
2.2 语句结构
SELECT 子句
[INTO 子句]
FROM 子句
[WHERE 子句]
[GROUP BY 子句]
[HAVING 子句]
[ORDER BY 子句]
2.3 变量
2.3.1 声明局部变量
Declare @name char(10);
2.3.2 为局部变量赋值
一种是select 一种是set
Declare @name char(10);
Select @name=xc
Declare @name char(10);
set @name=xc
2.4 流程控制
2.4.1．IF…ELSE语句
declare @a int,@b varchar(100)
set @a=(select count(titles_id) from titles);
set @b=convert(varchar(100),@a);
if @a<14
select @a+1 as a
else
begin
select @b+'2' as b
select 900
end
2.4.2．BEGIN…END语句
declare @r int ,@s int ,@t int
select @r=2,@s=3
print @r
while @r <5
begin
select @t=100*@r+@s
print @t
select @r=@r+2
Select @s=1
end
2.4.3． WHILE…CONTINUE…BREAK语句
declare @r int ,@s int ,@t int
select @r=2,@s=3
print @r
while @r <5
begin
select @t=100*@r+@s
print @t
select @r=@r+2
Select @s=1
end
2.4.4． WAITFOR语句
WAITFOR time '11:45:00'
Select * from YGB
2.4.5． RETURN语句
2.4.6. CASE用法
select
(case when 1=2 or 'a' like '%b%' then 'a'
when 1=4 or 'b' in ('b','bb') then 'b'
else 'c'
end)as testField
from dual
select sum(case when gjname = '中国' then 1
else 0
end),
sum(case when gjname = '美国' then 1
else 0
end)
from tablename
2.4.7. GOTO用法
DECLARE @X INT
SELECT @X=1
loving:
PRINT @X
SELECT @X=@X+1
WHILE @X<=3 GOTO loving
第三章 SQL数据语言操作
3.1 数据库操作
3.1.1数据库创建
use master --打开master数据库，一般的创建语句都在master中执行.
go
if exists (select * from sysdatabases where name='数据库名')
drop database 数据库名 --检查有没有这个数据库，如果有就删除它。
go
create database 数据库名
on
(
name=主数据文件名, ←------------ 养成好习惯,数据文件加_data
filename='主数据文件物理路径名', ←------------ 一定要是.mdf的文件,代表主数据文件
size=默认数据库大小,
maxsize=最大容量,
filegrowth=增长量
)
log on
(
name=日志文件名, ←------------ 养成好习惯,日志文件加_log
filename='日志文件物理路径名', ←------------ 一定要是.ldf的文件,代表日志文件
size=默认数据库大小,
maxsize=最大容量,
filegrowth=增长量
)
use master --打开master数据库，一般的创建语句都在master中执行
go
if exists (select * from sysdatabases where name='students')
drop database students
go
CREATE DATABASE students /*创建数据库students*/
ON PRIMARY
(
name="students_data", /*主数据文件的逻辑名*/
filename="D:\test\students_data.mdf", /*主数据文件储存位置*/
size=3mb, /*主数据文件的初始大小*/
maxsize=100mb, /*主数据文件的最大大小，不写就是没有限制*/
filegrowth=1mb /*增量为1MB*/
)
log on
( /***日志文件****/
name="students_log",
filename="D:\test\students_log.ldf", /*日志文件储存位置*/
size=1mb, /*日志文件的初始大小*/
filegrowth=10% /*增长率为10%*/
)
3.1.2 修改数据库名
EXEC sp_renamedb 'students' , 'stu'
3.1.3 查看数据库
sp_helpdb:查看服务器中所有数据库信息
sp_helpdb 'students' :指定的数据库信息
3.1.4 删除数据库
删除数据库（DROP DATABASE 数据库名）
DROP DATABASE students
3.2 数据表操作
3.2.1创建表
create table student ( Sno char(5) not null unique,
Sname char(20) unique,
Ssex char(1),
Sage int,
Sdept char(15));
3.2.2 修改表非空约束
ALTER TABLE table_name alter column column_name column_type null
3.2.3 增加、删除、修改列
增加字段
alter table table_name add sex char(2)
修改列数据类型
alter table table_name alter column sex char(50)
删除表中列
alter table table_name drop column sex
3.2.4 删除表
drop table table_name
3.3 数据操作
SELECT 列名称 FROM 表名称
SELECT * FROM 表名称
SELECT DISTINCT 列名称 FROM 表名称
SELECT 列名称 FROM 表名称 WHERE 列 运算符 值
INSERT INTO 表名称 VALUES (值1, 值2,....)
INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)
UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
DELETE FROM 表名称 WHERE 列名称 = 值
DELETE FROM table_name 不删除表删除所有行
3.4.视图操作
创建视图
create view view_name as select * from table_name
重命名视图
sp_rename 'view1';'view_1'
删除视图
drop view view1,view2
向视图中添加数据
insert into view_name values('','','')
修改视图中数据
update view_name set sex='男' where NAME=''
3.5 SQL查询
3.5.1 into子句
select name ,age into newtable from oldtable
3.5.2 BETWEEN AND
between rangA and rangB 大于等于rangA小于rangB
3.5.3 with 用法
--相当于建了个e临时表
with e as (select * from scott.emp e where e.empno=7499)
select * from e;
--相当于建了e、d临时表
with
e as (select * from scott.emp),
d as (select * from scott.dept)
select * from e, d where e.deptno = d.deptno;
3.5.4 where子句
1.下面的比较操作符可在 WHERE 子句中使用：
---------------------------------------
操作符 描述
---------------------------------------
= 等于
<> 不等于
> 大于
< 小于
>= 大于等于
<= 小于等于
---------------------------------------
注释：在某些版本的 SQL 中，操作符 <> 可以写为 !=。
2.在 WHERE 子句中使用的逻辑操作符 :
--------------------------------------------------------------------------
操作符 描述
--------------------------------------------------------------------------
BETWEEN 值1 AND 值2 在两个值之间
NOT BETWEEN 值1 AND 值2 不在两个值之间
IN <值列表> 在特定的集合里（枚举）
NOT IN <值列表> 不在特定的集合里（枚举）
LIKE 匹配于一个模式
NOT LIKE 不匹配于一个模式
IS NULL 字段为空的
IS NOT NULL 字段不为空的
--------------------------------------------------------------------------
注释：操作符 LIKE 利用通配符把一个值与类似的值进行比较，通配符有两个：
百分号（%）代表任意长度的字符串；
下划线（_）代表任意单个字符。
例如，'A_Z' 表示所有以'A' 起头，另一个任何值的字符，且以'Z' 为结尾的字符串。'ABZ' 和'A2Z' 都符合这一个模式，而'AKKZ' 并不符合（因为在A和Z之间有两个字符，而不是一个字符）。
'ABC%' 表示所有以'ABC' 起头的字符串。举例来说，'ABCD' 和'ABCABC' 都符合这个模式。
'%XYZ' 表示所有以'XYZ' 结尾的字符串。举例来说，'WXYZ' 和'ZZXYZ' 都符合这个模式。
'%AN%' 表示所有含有'AN' 这个模式的字符串。举例来说，'LOS ANGELES' 和'SANFRANCISCO' 都符合这个模式。
3.5.5 like用法
1、% 包含零个或多个字符的任意字符串： 1、LIKE'Mc%' 将搜索以字母 Mc 开头的所有字符串（如 McBadden）。
2、LIKE'%inger' 将搜索以字母 inger 结尾的所有字符串（如 Ringer、Stringer）。
3、LIKE'%en%' 将搜索在任何位置包含字母 en 的所有字符串（如 Bennet、Green、McBadden）。
4、_（下划线） 任何单个字符：LIKE'_heryl' 将搜索以字母 heryl 结尾的所有六个字母的名称（如 Cheryl、Sheryl）。
5、[ ] 指定范围 ([a-f]) 或集合 ([abcdef]) 中的任何单个字符： 1，LIKE'[CK]ars[eo]n' 将搜索下列字符串：Carsen、Karsen、Carson 和 Karson（如 Carson）。
6、LIKE'[M-Z]inger' 将搜索以字符串 inger 结尾、以从 M 到 Z 的任何单个字母开头的所有名称（如 Ringer）。
7、[^] 不属于指定范围 ([a-f]) 或集合 ([abcdef]) 的任何单个字符：LIKE'M[^c]%' 将搜索以字母 M 开头，并且第二个字母不是 c 的所有名称（如MacFeather）。
8、* 它同于DOS命令中的通配符，代表多个字符：c*c代表cc,cBc,cbc,cabdfec等多个字符。
9、？同于DOS命令中的？通配符，代表单个字符 :b?b代表brb,bFb等
10、# 大致同上，不同的是代只能代表单个数字。k#k代表k1k,k8k,k0k 。
11、[!] 排除 它只代表单个字符
3.5.6 in 和 exists 用法
3.5.7 group by 和 order by 用法
1、order用法
select * from s
order by sno desc, sage asc
2、group by 用法
SELECT cno,count(sno) from sc group by cno
3、having 用法
select sno , count(cno)
from sc
where grade > 60
group by sno having count(cno) > 3
3.5.8 UNION 操作符
UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。
SQL UNION 语法
SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2
注释：默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。
SQL UNION ALL 语法
SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2
另外，UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。
