SQL Server 基础操作全指南：建表、约束、查询与存储过程

数据库与表结构管理

数据库操作始于结构的定义。首先需要创建数据库容器，随后在库中建立数据表，并通过约束保证数据的完整性和关联性。

第1步：创建数据库与基础表

目的：建立数据存储环境及基础实体结构。

--创建名为Studets的数据库
create database Studets

--创建学生表
create table student (
    sno char(5), 
    sname char(20), 
    ssex char(2), 
    sage smallint, 
    sdept char(15)
)

--创建课程表
create table course (
    cno char(3), 
    cname char(30), 
    cpno char(3), 
    ccredit smallint
)

--创建选课表（关联表）
create table sc (
    sno char(5), 
    cno char(3), 
    grade int
)

第2步：修改表结构与列属性

目的：调整表结构以适配业务需求变化。

--向student表添加出生日期列
alter table student add scome datetime

--修改scome列的数据类型
alter table student alter column scome char(50)

--删除scome列
alter table student drop column scome

--删除指定表
drop table student

第3步：设置完整性与主外键约束

目的：确保数据唯一性、非空性及引用完整性。

创建时直接定义：

create table student (
    sno char(5) not null unique, --非空且唯一
    sname char(20), 
    sex char(2), 
    sage smallint default 20,     --默认值
    sdept char(15), 
    constraint ssex check(sex in('男','女')) --检查约束
)

现有表添加约束：

--添加主键
alter table student add constraint PK_SNO primary key(sno)

--course表中cpno为外键，指向另一门课（此处示例逻辑需结合实际，通常指向course自身或先建主键）
--添加主键和外键示例：假设cpno引用course表的cno
alter table course add 
    constraint PK_CNO primary key(cno), 
    constraint FK_CPNO foreign key(cpno) REFERENCES course(cno)

--sc表添加复合主键及外键
alter table sc add 
    constraint PK_SC primary key(sno,cno), 
    constraint FK_SNO foreign key(sno) references student(sno), 
    constraint FK_CNO foreign key(cno) references course(cno)

删除约束：

ALTER TABLE [dbo].[sc] DROP CONSTRAINT [FK__sc__sno__0F975522]
ALTER TABLE [dbo].[sc] DROP CONSTRAINT [PK_SC]

数据插入与查询核心语法

掌握数据的增删改查（CRUD）是数据库使用的核心。SQL Server支持丰富的查询功能，包括聚合、连接和子查询。

第1步：插入数据

目的：向表中填充测试或真实数据。

insert into student values('10021','张三','男',20,'计科系') 
insert into student values('10022','李清照','女',24,'文法')

insert into course values('001','数据库','005',4)
insert into course values('002','高等数学','',2)

insert into sc values('10021','001',88) 

第2步：基础与格式化查询

目的：获取特定字段并自定义输出格式。

--查看表所有信息
select * from student

--别名与计算列格式化查询
select sname as '姓名', 2013-sage as '出生日期' from student 
select sname, '出生日期'=2013-sage from student

第3步：条件与模糊匹配查询

目的：精确筛选满足特定逻辑的数据行。

--范围查询
select * from course where ccredit between 2 and 5 

--集合包含查询
select * from course where ccredit in (2, 5)

--模糊匹配：刘后面跟任意两个字符
select * from student where sname like '刘__'
--模糊匹配：中间包含"表"
select * from student where sname like '%表%'

第4步：聚合函数与分组统计

目的：对数据进行汇总分析。

--计算选修不同学生的总人数
SELECT COUNT(DISTINCT sno) AS '选修的总人数' FROM sc 

--按学生分组统计总成绩，并过滤总分大于100的记录
SELECT sno, SUM(grade) AS '总成绩' 
FROM sc 
GROUP BY sno 
HAVING SUM(grade) > 100

第5步：多表连接查询

目的：跨表获取关联信息，包括等值连接、外连接等。

--内连接（等值连接）
SELECT DISTINCT A.*, B.* 
FROM student A, sc B 
WHERE A.sno = B.sno

--左外连接：保留左表所有记录
SELECT A.*, B.grade 
FROM student A 
LEFT JOIN sc B ON A.sno = B.sno 

--右外连接
SELECT A.*, B.grade 
FROM student A 
RIGHT JOIN sc B ON A.sno = B.sno

第6步：子查询与存在性判断

目的：基于其他查询结果进行复杂过滤。

--子查询：查询年龄高于平均年龄的学生
SELECT * FROM student WHERE sage > (SELECT AVG(sage) FROM student)

--EXISTS子查询：查找有选课记录的学生
SELECT * FROM student WHERE EXISTS(SELECT * FROM sc WHERE sc.sno = student.sno)

--NOT EXISTS：查找未选课的学生
SELECT * FROM student WHERE NOT EXISTS(SELECT * FROM sc WHERE sc.sno = student.sno)

进阶功能：视图、权限与高级编程

视图简化复杂查询，权限控制数据安全，而存储过程和触发器则实现了逻辑的业务化封装。

第1步：索引管理与碎片检查

目的：提升查询性能，非空约束通常会自动创建索引。

--创建唯一索引
CREATE UNIQUE INDEX IX_student_sno ON student(sno)

--删除索引
DROP INDEX IX_student_sno ON student

--查看索引碎片信息
DBCC SHOWCONTIG

第2步：视图的创建与维护

目的：提供虚拟表以简化访问或隐藏底层逻辑。注意：涉及多个基表或聚合函数的视图通常不可更新。

--创建视图
CREATE VIEW VIEW_STUGrade(学号,姓名,课程,成绩)
AS
SELECT student.sno, student.sname, course.cname, sc.grade 
FROM student, course, sc 
WHERE student.sno = sc.sno AND course.cno = sc.cno AND student.sdept='软件'

--修改视图并添加CHECK OPTION（确保更新符合视图定义条件）
ALTER VIEW VIEW_STUGrade(学号,姓名,课程,成绩)
AS
SELECT ... 
WITH CHECK OPTION

--删除视图
DROP VIEW VIEW_STUGrade

第3步：权限控制

目的：管理用户登录及对象访问权限。

--创建登录和用户（需结合系统存储过程）
EXEC sys.sp_addlogin 'bnc', 'password', 'Studets'
EXEC sp_adduser 'bnc', 'bnc'

--授予权限
GRANT SELECT ON student TO bnc

--收回权限
REVOKE SELECT ON student FROM bnc

第4步：存储过程的应用

目的：封装常用SQL逻辑，提高执行效率和代码复用性。

--创建带参数的查询存储过程
GO
CREATE PROC sel @sno CHAR(10)
AS
SELECT * FROM student WHERE sno=@sno

--执行存储过程
EXEC sel @sno='10021'

--创建用于更新的存储过程
CREATE PROC updat @sno CHAR(10), @sex CHAR(2)
AS
UPDATE student SET sex=@sex WHERE sno=@sno
EXEC updat @sno='10021', @sex='女'

第5步：触发器实现审计与控制

目的：在INSERT、UPDATE或DELETE操作前后自动执行特定逻辑。

--创建After Insert触发器
GO
CREATE TRIGGER insert_Tri ON student AFTER INSERT
AS PRINT '有新数据插入！'

--创建阻止删除特定数据的触发器
GO
CREATE TRIGGER delete_Tri ON student AFTER DELETE
AS
IF '王帅' IN (SELECT sname FROM deleted)
BEGIN
    PRINT '该信息不许删除！'
    ROLLBACK TRANSACTION
END

--执行测试
EXEC inser @sno='10029', @sname='王帅', @sex='男', @sage=25, @sdept='国贸'
EXEC dele @sno='10029' --此操作将被阻断

以上就是SQL Server数据库基本操作语句的详细内容，涵盖了从基础建表到高级编程的完整流程。更多关于数据库优化与实战技巧的资料请关注本站其它相关文章！