SQL用法示例

前言

一些上课学的,没有题目,只有答案.

数据库操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
--建立数据库 [例3-1]
CREATE DATABASE Teach
ON
( NAME=Teach_Data,
FILENAME='D:\TeachData.mdf',
SIZE=10,
MAXSIZE=50,
FILEGROWTH=5)
LOG ON
( NAME=Teach_Log,
FILENAME='D:\TeachLog.ldf',
SIZE=5,
MAXSIZE=25,
FILEGROWTH=5)
use Teach
--[例3-2] 修改Northwind数据库中的Northwind文件增容方式为一次增加2MB。
ALTER DATABASE Northwind
MODIFY FILE
( NAME = Northwind,
FILEGROWTH = 2mb
)
--系统存储过程显示数据库信息
EXEC Sp_helpdb Northwind
USE Northwind
EXEC Sp_helpfile
EXEC Sp_helpfilegroup
--[例3-3] 删除数据库Teach。
DROP DATABASE Teach

表格操作

建立学生信息表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
--[例3-4] 用SQL命令建立一个学生表S
--[例3-6] 定义SN为惟一键。
--[例3-7] 定义SN+SEX为惟一键,此约束为表约束。
SELECT * FROM S
ALTER TABLE S
ADD
Class_No CHAR(6),
Address CHAR(40)
CREATE TABLE S
( SNo CHAR(6) CONSTRAINT S_Prim PRIMARY KEY,
SN CHAR(8) CONSTRAINT SN_Uniq UNIQUE,
Sex CHAR(2) DEFAULT '男',
Age INT,
Dept VARCHAR(20),
CONSTRAINT S_UNIQ UNIQUE(SN, Sex))
--[例3-13] 在S表中增加一个班号列和住址列。
--使用此方式增加的新列自动填充NULL值,所以不能为增加的新列指定NOT NULL约束。
ALTER TABLE S
ADD
Class_No CHAR(6),
Address CHAR(40)

建立学生选课表

1
2
3
4
5
6
7
8
--[例3-9] 定义SNo+CNo为SC的主键
--[例3-10] 定义SNo,CNo为SC的外部键。
--[例3-11] 定义Score的取值范围为0~100之间。
CREATE TABLE SC
( SNo CHAR(5) NOT NULL CONSTRAINT S_Fore FOREIGN KEY REFERENCES S(SNo),
CNo CHAR(5) NOT NULL CONSTRAINT C_Fore FOREIGN KEY REFERENCES C(CNo),
Score NUMERIC(4,1) CONSTRAINT Score_Chk CHECK(Score>=0 AND Score <=100),
CONSTRAINT SC_Prim PRIMARY KEY(SNo,CNo))

修改表格

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
--[例3-14] 在SC表中增加完整性约束定义,使Score在0~100之间
ALTER TABLE SCo
ADD
CONSTRAINT Score_Chk CHECK(Score BETWEEN 0 AND 100)
--[例3-15] 把S表中的SN列加宽到10个字符。
ALTER TABLE S
ALTER COLUMN
SN CHAR(10)
--[例3-16] 删除S表中的主键约束。
ALTER TABLE S
DROP CONSTRAINT S_Prim
--[例-补] 删除S表中的一列“Address”
ALTER TABLE S
DROP COLUMN Address

索引

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
--[例3-18] 为表SC在SNo和CNo上建立惟一索引。
CREATE UNIQUE INDEX SCI ON SC(SNo,CNo)
--[例3-19] 为教师表T在TN上建立聚集索引。
CREATE CLUSTERED INDEX TI ON T(TN)
--[例3-20] 查看表SC的索引。
EXEC Sp_helpindex SC
--[例3-21] 更改T表中的索引TI名称为T_Index。
EXEC Sp_rename 'T.TI', 'T_Index', 'index'
EXEC Sp_helpindex T
--[例3-22] 删除表SC的索引SCI。
DROP INDEX SC.SCI

插入数据

1
2
--向S表插入数据
INSERT INTO S (SNo, SN, Age, Sex, Dept) VALUES ('S1', '赵亦', 17, '男', '计算机')

查询操作

1
2
SELECT SNo, SN, Age FROM S
SELECT DISTINCT SN Name, DISTINCT SNo, Age FROM S
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
--where条件用法
--[例3-28]
SELECT SNo,CNo,Score FROM SC WHERE Score>85
--[例3-29]
SELECT SNo, CNo, Score FROM SC WHERE (CNo = 'C1' OR CNo = 'C2') AND (Score >= 85)
--[例3-30]
SELECT TNo,TN,Prof,Sal FROM T WHERE Sal BETWEEN 1000 AND 1500
--例3-31]
SELECT TNo,TN,Prof,Sal FROM T WHERE Sal NOT BETWEEN 1000 AND 1500
--[例3-32]
SELECT SNo,CNo,Score FROM SC WHERE CNo IN('C1','C2')
--[例3-33]
SELECT SNo,CNo,Score FROM SC WHERE CNo NOT IN('C1','C2')
--[例3-35]
SELECT TNo, TN FROM T WHERE TN LIKE '__力%'
--[例3-36]
SELECT SNo, CNo FROM SC WHERE Score IS not NULL
1
2
3
4
5
6
7
8
9
10
--函数使用
--[例3-37]
SELECT SUM(Score) AS TotalScore, AVG(Score) AS AveScore FROM SC WHERE (SNo = 'S1')
SELECT SUM(Score) , AVG(Score) FROM SC WHERE (SNo = 'S1')
--[例3-38]
SELECT MAX(Score) AS MaxScore,
MIN(Score) AS MinScore,
MAX(Score)-MIN(Score) AS Diff FROM SC WHERE (CNo = 'C1')
--[例3-40]
SELECT COUNT(DISTINCT Dept) AS DeptNum FROM S
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
--[例3-44]
SELECT SNo, COUNT(*) AS SC_Num FROM SC GROUP BY SNo HAVING (COUNT(*) >= 2)
--[例3-46]
SELECT SNo, CNo, Score
FROM SC
WHERE (CNo IN ('C2', 'C3', 'C4', 'C5'))
ORDER BY SNo, Score DESC
--[例3-48]
SELECT R1.TNo, R2.TN, R1.CNo
FROM
(SELECT TNo,CNo FROM TC) AS R1
INNER JOIN
(SELECT TNo,TN FROM T WHERE TN='刘伟') AS R2
ON R1.TNo=R2.TNo
--[例3-49]
SELECT S.SNo,SN,CN,Score
FROM S,C,SC
WHERE S.SNo=SC.SNo AND SC.CNo=C.CNo
--[例3-50]
SELECT CN,TN,PROF,COUNT(SC.SNo)
FROM C,T,TC,SC
WHERE T.TNo=TC.TNo AND C.CNo=TC.CNo AND SC.CNo=C.CNo
GROUP BY CN,TN,PROF
--[例3-50]
SELECT SC.CNO,CN,TN,PROF,SC.SNo
FROM C,T,TC,SC
WHERE T.TNo=TC.TNo AND C.CNo=TC.CNo AND SC.CNo=C.CNo
ORDER BY CN,TN,SC.SNo
--外连接
SELECT S.SNo,SN,CN,Score
FROM S
LEFT OUTER JOIN SC
ON S.SNo=SC.SNo
LEFT OUTER JOIN C
ON C.CNo=SC.CNo
--内连接
SELECT S.SNo,SN,CN,Score
FROM S
INNER JOIN SC
ON S.SNo=SC.SNo
INNER JOIN C
ON C.CNo=SC.CNo
--[例3-55] 方法1
SELECT TN
FROM T
WHERE (TNo = ANY (SELECT TNo
FROM TC
WHERE CNo = 'C5'))
/*[例3-60]*/
SELECT TN
FROM T
WHERE EXISTS ( SELECT *
FROM TC
WHERE TNO=T.TNO AND CNO='C5')
--含有IN的查询通常可用EXISTS表示
SELECT TN
FROM T
WHERE (TNo IN (SELECT TNo
FROM TC
WHERE CNo = 'C5'))
--[例3-62]
SELECT SN
FROM S
WHERE (NOT EXISTS ( SELECT *
FROM C
WHERE NOT EXISTS ( SELECT *
FROM SC
WHERE SNo = S.SNo AND CNo = C.CNo)))

视图

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
--[例3-77]
CREATE VIEW S_SC_C(SNo, SN, CN, Score)
AS SELECT S.SNo, SN, CN, Score
FROM S, C, SC
WHERE S.SNo = SC.SNo AND SC.CNo = C.CNo
--[例3-79]
ALTER VIEW S_SC_C(SN, CN, Score)
AS SELECT SN, CN, Score
FROM S, C, SC
WHERE S.SNo = SC.SNo AND SC.CNo = C.CNo
--[例3-80]
DROP VIEW S_SC_C
--[例3-82]
INSERT INTO SUB_T
VALUES ('T6','李丹','副教授')
--[例3-84]
DELETE
FROM SUB_T
WHERE TN='刘伟'