本文共 2566 字，大约阅读时间需要 8 分钟。

SQL Server数据库管理指南

数据库组成与功能

在SQL Server中，数据库不仅仅是一个存储数据的地方，更是一个集成了多种功能的管理系统。以下是数据库的主要组成部分：

数据库对象管理

数据库对象创建

CREATE TABLE users (
    id INT,
    name VARCHAR(50),
    age INT
);

数据录入与查询

INSERT INTO users VALUES(2, '张三1', 13);
INSERT INTO users VALUES(4, '王五', 12, '山西省吕梁市', DEFAULT);

数据复制与表操作

CREATE TABLE student (
    id INT,
    name VARCHAR(50),
    age VARCHAR(50)
);
INSERT INTO student(id, name, age) SELECT id, name, age FROM users;

数据迁移与扩展

SELECT id, name, age INTO studentinfo FROM users;

数据更新与删除

UPDATE users SET sex = '女' WHERE name = '张三';
DELETE FROM users WHERE name = '张三1';

数据查询技巧

SELECT id AS '编号', name AS '姓名', age AS '年龄' FROM users;
SELECT id, '编号', name, '姓名', age, '年龄' FROM users;
SELECT name + '.' + address FROM studentinfo;

日期与数学函数

SELECT getdate();
SELECT DATEADD(yy, 5, '01/01/2009');
SELECT DATEDIFF(yy, '2017-05-08', '2019-05-04');
SELECT datename(dw, '2015-05-09');
SELECT datepart(day, '2014-05-09');

随机数与数学运算

SELECT rand();
SELECT ceiling(45.6);
SELECT abs(-8);
SELECT floor(32.8);
SELECT power(5, 2);
SELECT round(3.1415926, 2);
SELECT sign(-1);
SELECT sqrt(9);

数据转换与系统函数

SELECT CONVERT(VARCHAR(5), 12345);
SELECT CURRENT_USER;
SELECT DATALENGTH('中国A联盟');
SELECT HOST_NAME();
SELECT SYSTEM_USER;
SELECT USER_NAME(1);

SQL优化与案例

字符替换

UPDATE Card SET PassWord = REPLACE(PassWord, 'O', '0');
UPDATE Card SET PassWord = REPLACE(PassWord, 'i', '1');

数据排序

SELECT ListNumber FROM SellRecord
ORDER BY 
    CONVERT(int, LEFT(ListNumber, CHARINDEX('-', ListNumber) - 1)),
    CONVERT(int, STUFF(ListNumber, 1, CHARINDEX('-', ListNumber), ''));

年龄计算

DATEDIFF(DD, BornDate, GETDATE()) >= 365 * 20

域名提取

RIGHT(Email, LEN(Email) - CHARINDEX('@', Email))

随机数生成

RIGHT(RAND(), 4)

关联查询

连接查询

SELECT * FROM Students a
JOIN score b ON a.SCode = b.Score
JOIN course c ON b.CourseID = c.CourseID

左外连接

SELECT S.SName, C.CourseID, C.Score
FROM Students AS S
LEFT JOIN Score AS C ON C.StudentID = S.SCode

右外连接

SELECT S.SName, C.CourseID, C.Score
FROM Students AS S
RIGHT JOIN Score AS C ON C.StudentID = S.SCode

统计与分组查询

按年级统计学生人数

SELECT count(*) 人数, sgrade 年级 FROM students GROUP BY sgrade

分性别统计

SELECT count(*) 人数, sgrade, ssex FROM students GROUP BY sgrade, ssex

过滤人数

SELECT count(*) 人数, sgrade 年级 FROM students GROUP BY sgrade HAVING count(*) >= 2

关注我们

欢迎关注我们的技术博客，获取更多技术资讯！