首页 学习学习我爱学习 数据库

# 针对库的操作

# 数据库的创建

SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE DATABASE <库名> [
    [
        ON [主键]
        {<描述磁盘文件的信息> [, ...n] }
        [, 
            {FILEGROUP <filegroupName> {<filespec [, ...n]} [, ...n]}
        ]
    ]
    [
        LOG ON {
            <filespec> [, ...n] 
        }
    ]
]

# 实例

SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE DATABASE ScoreDB
ON (
    NAME = ScoreDB,
    FILENAME = 'E:\s...\ScoreDB.mdf',
    SIZE = 5,       -- 初始大小 5MB
    MAXSIZE = 10,   -- 最大可拓展为 10MB
    FILEGROWTH = 1  -- 每次按 1MB 拓展
)
LOG ON (    
    NAME =  ScoreLog,
    FILENAME = 'e:\...\ScoreLog.ldf',
    SIZE = 2,
    MAXSIZE = 5,
    FILEGROWTH = 1
)

# 数据库的修改

SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
ALTER DATABASE <库名>
[
    {
        ADD FILE {
            <filespec> [, ...n]
        } 
        [TO FILEGROUP <filegroupName>]
        | ADD LOG FILE {
            <filespec> [, ...n]
        }
        | REMOVE FILE <logicalFileName>
        | ADD FILEGROUP <filegroupName>
        | REMOVE FILEGROUP <filegroupName>
        | MODIFY FILE <filespec>        
        | MODIFY FILEGROUP <filegroupName> <filegroupProperty>
    }
    [, ...n]
]

一次只可以修改其中一个选项

# 实例

SQL
1
2
3
4
5
ALTER DATABASE MyTmpDB
MODIFY FILE (
    NAME = TmpName,
    SIZE = 20MB
)

将逻辑文件名为 TmpName 的磁盘文件的初始大小修改为 20MB20MB

# 针对表的操作

# 创建基本表

SQL
1
2
3
4
5
6
7
8
9
10
CREATE TABLE Student (
	studentId VARCHAR(20) PRIMARY KEY,
	studentName VARCHAR(20),
	age INT,
	hometown VARCHAR(20),
	CONSTRAINT check_age_hometown CHECK (
		hometown NOT INT ('北京', '上海')
		OR age >= 17
	)
);

缺省最后的 ON <filegroupName> 表示存储在主逻辑设备上

# 修改基本表

SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 增加列
ALTER TABLE <表名>
    ADD <列名> <数据类型>

-- 删除列
ALTER TABLE <表名>
    DROP COLUMN <列名>

-- 增加约束
ALTER TABLE <表名>
    ADD CONSTRAINT <constraintName>

-- 删除约束
ALTER TABLE <表名>
    DROP <constraintName>

-- 修改列的数据类型
ALTER TABLE <表名>
    ALTER COLUMN <列名> <新数据类型>

# 删除基本表

SQL
1
2
3
4
DROP TABLE <表名> [
    RESTRICT | CASCADE
--  有限制条件   无限制条件
]

  • RESTRICT ：若选这个，则该基本表不能有视图、触发器和索引等，也不能被其他基本表引用
  • CASCADE ：删除没有限制条件，一并删除在该基本表基础上的所有其他东西

# 索引

不改变数据物理顺序，通过建立索引来实现数据记录的重新排列，称为逻辑排序

# 建立索引

SQL
1
2
3
4
5
6
7
8
CREATE
[UNIQUE] -- 唯一索引
[CLUSTERED | NONCLUSTERED] -- 聚集 / 非聚集索引, 默认为非聚集索引
INDEX <索引名>
ON <表名> (
    <列名1> [ASC | DESC]
    ...
) [ON <filegroupName]

# 实例

SQL
1
2
3
4
CREATE INDEX classBirthIdx ON Student (
    classNo,
    birthday DESC
)

这种索引设计可以显著提高基于班级和生日的查询的性能

# 删除索引

SQL
1
DROP INDEX <索引名> ON <表名>

# 数据更新语言

# 插入数据

# 插入一个元组

SQL
1
2
INSERT INTO <表名> [(<列名1>, ...)]
VALUES(<值1>, ...)

# 实例

SQL
1
2
INSERT INTO Student(studentName, birthday)
VALUES('zmc', '2006-03-20')

如果省略插入的列名，则默认需要插入所有的

# 插入多个元组

SQL
1
2
INSERT INTO <表名> [(列名1, ...)]
QUERY    -- 一个查询

# 实例

SQL
1
2
3
INSERT INTO Student (studentName, bitrhday)
SELECT ns.studentName, ns.bitrhday
FROM NewStudent ns

# 删除数据

SQL
1
2
3
DELETE FROM 表名 [
    WHERE <predicate>   -- 删除条件
]

# 实例

SQL
1
2
3
4
5
6
DELETE FROM Score
WHERE courseNo IN (
    SELECT courseNo
    FROM Course c
    WHERE c.courseName LIKE '%数学%'
)

# 修改数据

SQL
1
2
UPDATE 表名
SET 列名1 = 值1, ...

# 实例

SQL
1
2
3
4
5
6
7
UPDATE Score
SET score = score + 5
FROM Score a, Student b
WHERE a.studentNo = b.studentNo
    AND courseNo = '002'
    AND termNo = '211'
    AND studentName = 'zmc'

# 视图

视图是虚表，是从若干个基本表（或视图）中导出的表，数据库系统表中仅存放了创建视图的语句，而不存放视图对应的数据

# 视图的作用

  • 简化操作
  • 使用户能从多种角度看待同一数据库模式
  • 对重构数据库模式提供了一定程度的逻辑独立性
  • 能够对数据库的机密数据进行保护
  • 更清晰表达查询

# 创建视图

SQL
1
2
3
CREATE VIEW 视图名 [列名1, ...]
    AS <查询名>
    [WITH CHECK OPTION]

# 实例

SQL
1
2
3
4
5
CREATE VIEW StudentView
AS
    SELECT *
    FROM Student
    WHERE year(birthday) = 2006

当视图是基于一个基本表创建的，且保留了主码属性，这样的视图称为行列子集视图
视图可以建立在一个基本表上，也可以建立在多个基本表上，也可以建立在视图上

# 查询视图

和表的查询类型
但是，如果视图是一个基于聚合运算的视图，则对于聚合运算的值，不允许在 WHERE 中对聚合函数进行运算

# 更新视图

和表的更新类似

# 删除视图

SQL
1
DROP VIEW 视图名 [CASCADE]

  • CASCADE ：级联删除，删除出来它自身和其他基于它的