SQL Server

DDL（Data Definition Language，数据定义语言）

DDL（数据定义语言）是一种用于创建和修改数据库对象（如表、索引和用户）的语法。DDL 语句类似于用于定义数据结构（尤其是数据库模式）的计算机编程语言。DDL 语句的常见示例包括 CREATE 、 ALTER 和 DROP 。

创建数据库

  • 利用SQL语句(命令方式)创建数据库student，所有文件存放在D:\db文件夹中。

    要求：主数据文件20MB,最大尺寸为70MB，文件增长率为10%，

    日志文件为5MB，最大尺寸10MB，文件增长为1MB，

    次数据文件20MB,最大尺寸为50MB，文件增长率为10%。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
create database student
on primary (				-- 此处 primary 可以省略
	name = 'student',
	filename = 'D:\db\student.mdf',
	size = 20 mb,
	maxsize = 70 mb,
	filegrowth = 10%		-- 此处一定不能加 ,
),
(
	name = 'student2',
	filename = 'D:\db\student2.ndf',
	size = 20 mb,
	maxsize = 50 mb,
	filegrowth = 10%
)
log on (
	name = 'student_log',
	filename = 'D:\db\student_log.ldf',
	size = 5 mb,
	maxsize = 10 mb,
	filegrowth = 1 mb
);

在 SQL Server 中，前缀 N 表示后面的字符串是一个 Unicode 字符串。当需要在字符串中包含 Unicode 字符（如非英文字母、特殊符号等）时，需要在字符串前加上 N 前缀来表示该字符串为 Unicode 字符串。

例如，N'课程管理_Data' 中的 N 前缀表示 '课程管理_Data' 是一个 Unicode 字符串，可以包含中文字符。

在创建数据库时，使用 NAME = N'student_Data' 的目的是确保数据库文件的逻辑名称是一个 Unicode 字符串，以适应可能包含非英文字符的情况。

修改数据库

  • 用SQL语句命令方式修改数据库student的主数据文件大小为10mb
1
alter database student modify file(name = 'student', size = 10mb);
  • 利用SQL语句(命令方式)修改课程管理数据库，将日志文件初始大小改为20MB，增长方式为20%
1
alter database 课程管理 modify file(name = 'course', size = 20 mb, filegrowth = 20%);
  • 利用SQL语句(命令方式)删除数据库student的次数据文件
1
alter database student remove file student2;
  • 利用SQL语句(命令方式)修改数据库student名称为ss
1
alter database student modify name = ss;

  • 将数据库teacher移动到“E:\”,并保证逻辑数据库不受影响，说明解决办法。

    1. 首先，备份数据库 “teacher”：在执行任何文件移动操作之前，强烈建议备份数据库以防止数据丢失。使用适当的备份方法（如 SQL Server Management Studio）创建数据库 “teacher” 的完整备份。
    2. 使用以下 SQL 命令修改文件路径：使用 ALTER DATABASE 语句以及 MODIFY FILE 子句来修改数据库文件的路径。在 MODIFY FILE 子句中，指定要修改的文件的名称（逻辑名称）和新的文件路径。
    1
    2
    3
    -- sql 语句
    ALTER DATABASE teacher MODIFY FILE (NAME = 'logical_file_name', FILENAME = 'E:\new_path\teacher.mdf');
    ALTER DATABASE teacher MODIFY FILE (NAME = 'logical_log_name', FILENAME = 'E:\new_path\teacher_log.ldf');

    请将 'logical_file_name' 替换为实际的逻辑数据文件名，'logical_log_name' 替换为实际的逻辑日志文件名。

    1. 将实际的物理文件移动到新路径：在 SQL Server 中，数据库文件的物理位置由 SQL Server 实例管理。因此，您需要手动将实际的物理文件从旧路径移动到新路径（”E:\new_path”）。确保在移动文件之前，数据库处于离线状态。

    2. 启动数据库并验证：重新启动数据库 “teacher”，并验证数据库是否能够正常启动并且逻辑数据库没有受到影响。

删除数据库

1
2
3
4
-- 语法
-- drop database [if exists] 表名

drop database if exists sc;

使用 SQL Server 将数据库设置为读写。

image-20240311214447220

  1. 确认执行此过程的用户帐户是每个数据库中的 db_owner 固定数据库角色的成员。
  2. 启动 SQL Server Management Studio。
  3. 右键单击要设置为只读的内容数据库，然后单击“属性”。
  4. 选择“选项”页，然后在“其他选项”列表中，滚动至“状态”部分。
  5. 在“只读数据库”行中，单击“True”旁边的箭头，选择“False”，然后单击“确定”。

创建表

  • 创建 student（学生）、course（选课）、sc（成绩） 表
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
create table student(
	Sno char(9) primary key,	-- 学生学号
	Sname char(6) not null,		-- 姓名
	Ssex char(2),				-- 性别
	Sage int,					-- 年龄
	Sdept varchar(20)			-- 所在院系
);

create table course(
	Cno char(4) primary key,					-- 课程编号
	Cname varchar(20) not null,					-- 课程名称
	Cpno char(4),								-- 先修课程号
	Ccredit Int,								-- 课程学分
	foreign key(Cpno) references course(Cno)	-- 外键，参考 Cno 取值
);

create table sc(
	Sno char(9),
	Cno char(4),
	Grade float,
	primary key(sno, cno),						-- 将 sno、cno 共同作为一个主键
	foreign key(sno) references student(Sno),	-- 外键，参考 student.Sno 取值
	foreign key(cno) references course(Cno)		-- 外键，参考 student.Cno 取值
);
约束类型 介绍 作用
not null 非空 指示某列不能存储 NULL 值
unique 惟一 保证某列的每行必须有唯一的值
primary key 主键
（一个表最多只能有一个主键，但是一个主键可以包含多个字段）		 NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录
foreign key 外键 保证一个表中的数据匹配另一个表中的值的参照完整性
check 约束 保证列中的值符合指定的条件
default 默认 规定没有给列赋值时的默认值

修改表

  • 向表中添加约束
1
2
3
4
5
-- 语法
-- alter table 表名 add constraint 约束名 约束

alter table sc add constraint CHK_Grade check(Grade >= 0);
alter table student add constraint CHK_sdept check(sdept in ('中医系', '中药系', '信息系', '人文系', '经济系'));

对于外键约束，每个操作的外键约束都要单独设置，如 delete、update 等所有涉及到外键约束的操作

1
2
alter table sc add foreign key(sno) references student(Sno) on delete cascade;
alter table sc add foreign key(cno) references course(Cno) on delete cascade;
外键约束 介绍 作用
no action 拒绝（默认） 无操作：如果检查约束时仍存在任何引用行，则会引发错误。如果未指定任何内容，则这是默认行为。从本质上讲，如果有引用父表的子行，它可以防止从父表中删除它。
cascade 级联 如果删除了父行，则所有相应的子行也会自动删除。
set null 设置为空 如果删除父行，则子行中的外键列设置为 NULL。

删除表

1
2
3
4
-- 语法
-- drop table [if exists] 表名

drop table if exists sc;

DML（Data Manipulation Language， 数据操作语言）

DQL（Data Query Language， 数据查询语句）

数据查询语言（Data Query Language, DQL）是用于从数据库或信息系统中查询数据的计算机语言

查询数据语法

1
2
3
4
5
6
7
8
9
select 
	<字段1 , 字段2 ...> 
	[聚合函数(count() as 别名1, max() as 别名2, min(), avg(), sum() ...)]
from <表>
[join <表2> on 表2.字段 = 表.字段]
[where 条件判断]
[group by 分组字段]
[having 条件判断]
[order by [asc / desc]]

查询条件

  • <字段1, 字段2, 字段3 …>

    查询后需要返回的字段，* 表示全部字段

    如果查询的多张表中由有重名字段，需要通过 表名.字段 的方式指定字段

  • 聚合函数 count(), max(), min() …

    聚合函数计算一组值并返回单个值

    聚合函数 描述
    count(字段) 返回该字段的行数
    max(字段) 返回该字段中的最大值
    min(字段) 返回该字段中的最小值
    avg(字段) 返回该字段值的平均值
    sum(字段) 返回该字段中值的总和，sum() 中可以加条件判断 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- 按课程号降序显示选修各个课程的总人数、最高分、最低分和平均分。
select 
	course.Cno, 
	count(course.Cno) as 'count_Cno', 
	max(Grade) as 'max_Grade', 
	min(Grade) as 'min_Grade', 
	avg(Grade) as 'average_Grade'
from course
join sc on sc.Cno = course.Cno
group by course.Cno
order by course.Cno desc;

-- 查询各门课程的选修及格人数、及格比率。
select 
	course.Cno, 
	sum(case when Grade >= 60 then 1 else 0 end) as pass_nums,
	count(*) as all_nums,
	round(sum(case when Grade >= 60 then 1 else 0 end) / count(*) * 100, 2) as pass_rate
from course
join sc on sc.Cno = course.Cno
group by course.Cno;

  • join … on …

    join 子句用于把来自两个或多个表的行结合起来，基于这些表之间的共同字段。

    1
    2
    3
    4
    5
    6
    7
    -- 查询所有学生的数据库原理与应用课程成绩信息，展示学号、姓名、所在系、课程名称和成绩。
    select
    	student.Sno, Sname, Sdept, Cname, Grade 
    from student
    join sc on sc.Sno = student.Sno
    join course on course.Cno = sc.Cno
    where Cname = '数据库原理';

  • where 条件判断

    where 子句用于提取那些满足指定条件的记录

    1
    2
    3
    4
    5
    6
    7
    -- 3、按照课程号、成绩降序显示课程成绩在70到80之间的学生学号、课程号及成绩。
    select * 
    from sc 
    where 
    	Grade between 70 and 80
    order by 
    	Cno, Grade desc;

    • where 子句必须在 group by 分组前使用

    • where 不能使用聚合函数

  • group by 分组字段

    group by 语句用于结合聚合函数，根据一个或多个列对结果集进行分组。

    1
    2
    3
    4
    5
    6
    -- 统计各院系学生人数。
    select 
    	Sdept, count(*) as count_nums
    from student
    group by Sdept
    order by count(*) asc;

  • having 条件判断

    在 SQL 中增加 having 子句原因是，where 关键字无法与聚合函数一起使用。

    having 子句可以让我们筛选分组后的各组数据。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    -- 显示平均成绩大于“S0001”学生平均成绩的各个学生的学号、平均成绩。
    select 
    	Sno, avg(Grade) as 'average_Grade'
    from sc
    where Sno <> 'S0001'
    group by Sno
    having 
    	avg(Grade) > (select avg(Grade) from sc where Sno = 'S0001');
    	
    -- 列出有两门以上课程（含两门）不及格的学生的学号及该学生的平均成绩。
    select 
    	Sno,
    	avg(Grade) as average_Grade
    from sc
    where grade < 60
    group by Sno
    having count(*) >= 2;
    • having 可以使用聚合函数
    • having 可以在 group by 分组后使用

  • order by <字段> asc/desc

    按照字段 升序（默认） / 降序 排列

条件判断中的运算符

运算符 描述
= 等于
<> 不等于
>
>=		 大于
大于等于
<
<=		 小于
小于等于
between a and b 在 [a, b] 中
like 模糊匹配
in 在里面

DCL（Data Control Language， 数据控制语言）