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='刘伟'