mssql

安装

windows版: https://blog.skyw.cc/archives/366.html

ubuntu版: https://fuuzen.github.io/cs/db/linux-mssql-server

docker版

docker-compose.yml

version: '3'
services:
  mssql:
    image: mcr.microsoft.com/mssql/server:2022-latest
    container_name: mssql
    environment:
      - ACCEPT_EULA=Y
      - SA_PASSWORD=Sql114514
    ports:
      - "1433:1433"
    hostname: sql1

启动！

1	`sudo docker-compose up -d`

进入容器

以普通用户进入：

1	`sudo docker exec -it mssql bash`

以root用户进入：

1	`sudo docker exec -it --user root mssql bash`

连接

命令行连接

1	`sqlcmd -S 127.0.0.1 -U sa -P Sql114514`

如果使用的端口不是1434，可指定端口

1	`sqlcmd -S 127.0.0.1:$port -U sa -P Sql114514`

python连接

请先参考本文https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-ver15 安装驱动(linux)

import pyodbc

class mssql:
    def __init__(self, server, database, username, password):
        self.server = server
        self.database = database
        self.username = username
        self.password = password
        self.connectionString = f"DRIVER={{ODBC Driver 18 for SQL Server}};SERVER={server};DATABASE={database};UID={username};PWD={password};TrustServerCertificate=yes;"
        self.conn = pyodbc.connect(self.connectionString)

    def exec(self, query):
        cursor = self.conn.cursor()
        cursor.execute(query)
        records = cursor.fetchall()
        return records

if __name__ == '__main__':
    server = '127.0.0.1'
    database = 'master'
    username = 'user'
    password = '114514'
    ms = mssql(server, database, username, password)
    records = ms.exec("SELECT name FROM master.dbo.sysdatabases;")
    print(records)

hello world

1	`PRINT 'Hello World!';`

添加中文支持

参考文献https://blog.csdn.net/Frost216/article/details/121177814

在每次创建数据库时，记得运行该语句

1	`ALTER DATABASE 数据库名称 COLLATE Chinese_PRC_CI_AS;`

检验

1	`select '好好好' as foo;`

期望能够看到中文，而不是三个问号

DDL

Data Definition Language

数据定义语言，用来定义数据库对象(数据库，表，字段)

数据库操作

查询所有数据库

1	`SELECT name FROM master.dbo.sysdatabases;`

或

1	`SELECT name FROM sys.databases`

创建数据库

当test1不存在时创建

1 2	`IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = 'test1') CREATE DATABASE test1;`

删库跑路

在删库前，您需要检查是否有任何连接到数据库，否则删库失败

1	`SELECT * FROM sys.dm_exec_sessions WHERE database_id = DB_ID('test1');`

当test1存在时删除

IF EXISTS (SELECT * FROM sys.databases WHERE name = 'test1') 
BEGIN
    DROP DATABASE test1;
END;

切换数据库

1	`use dbname;`

表操作

查询当前数据库所有表

1	`SELECT * FROM sys.tables`

查看指定表结构

1	`SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('table_name');`

创建表

以tb_user为例

CREATE TABLE tb_user(
    id INT,
    name VARCHAR(50),
    age INT,
    gender VARCHAR(1)
);

仅当tb_user不存在时创建

IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'tb_user')
CREATE TABLE tb_user(
    id INT,
    name VARCHAR(50),
    age INT,
    gender VARCHAR(1)
);

更加复杂的建表语句

CREATE TABLE emp (
    id INT,
    workno VARCHAR(10),
    name VARCHAR(10),
    gender CHAR(1),
    age TINYINT,
    idcard CHAR(18),
    workaddress VARCHAR(50),
    entrydate DATE
);

-- 添加注释
EXEC sp_addextendedproperty 
@name = N'MS_Description', @value = N'编号', 
@level0type = N'SCHEMA', @level0name = 'dbo', 
@level1type = N'TABLE', @level1name = 'emp', 
@level2type = N'COLUMN', @level2name = 'id';

EXEC sp_addextendedproperty 
@name = N'MS_Description', @value = N'工号', 
@level0type = N'SCHEMA', @level0name = 'dbo', 
@level1type = N'TABLE', @level1name = 'emp', 
@level2type = N'COLUMN', @level2name = 'workno';

EXEC sp_addextendedproperty 
@name = N'MS_Description', @value = N'姓名', 
@level0type = N'SCHEMA', @level0name = 'dbo', 
@level1type = N'TABLE', @level1name = 'emp', 
@level2type = N'COLUMN', @level2name = 'name';

EXEC sp_addextendedproperty 
@name = N'MS_Description', @value = N'性别', 
@level0type = N'SCHEMA', @level0name = 'dbo', 
@level1type = N'TABLE', @level1name = 'emp', 
@level2type = N'COLUMN', @level2name = 'gender';

EXEC sp_addextendedproperty 
@name = N'MS_Description', @value = N'年龄', 
@level0type = N'SCHEMA', @level0name = 'dbo', 
@level1type = N'TABLE', @level1name = 'emp', 
@level2type = N'COLUMN', @level2name = 'age';

EXEC sp_addextendedproperty 
@name = N'MS_Description', @value = N'身份证号', 
@level0type = N'SCHEMA', @level0name = 'dbo', 
@level1type = N'TABLE', @level1name = 'emp', 
@level2type = N'COLUMN', @level2name = 'idcard';

EXEC sp_addextendedproperty 
@name = N'MS_Description', @value = N'工作地址', 
@level0type = N'SCHEMA', @level0name = 'dbo', 
@level1type = N'TABLE', @level1name = 'emp', 
@level2type = N'COLUMN', @level2name = 'workaddress';

EXEC sp_addextendedproperty 
@name = N'MS_Description', @value = N'入职时间', 
@level0type = N'SCHEMA', @level0name = 'dbo', 
@level1type = N'TABLE', @level1name = 'emp', 
@level2type = N'COLUMN', @level2name = 'entrydate';

-- 添加表注释
EXEC sp_addextendedproperty 
@name = N'MS_Description', @value = N'员工表', 
@level0type = N'SCHEMA', @level0name = 'dbo', 
@level1type = N'TABLE', @level1name = 'emp';

注意:

MSSQL 不支持 COMMENT 语法, 改为使用 sp_addextendedproperty 添加注释。
MSSQL 中的 VARCHAR 有最大长度限制
MSSQL 不支持无符号整数。所以不支持TINYINT UNSIGNED之类的类型

修改表

修改表结构

加入名为nickname的列

1	`ALTER TABLE tb_user add nickname VARCHAR(20);`

重命名列

修改名为nickname的列为username

1	`EXEC sp_rename 'tb_user.nickname', 'username', 'COLUMN';`

修改列类型

修改名为username的列类型为VARCHAR(30)

1	`ALTER TABLE tb_user ALTER COLUMN username VARCHAR(30);`

删除列

删除名为username的列

1	`ALTER TABLE tb_user DROP COLUMN username;`

修改表名

将名为tb_user的列修改为tb_user1

1	`EXEC sp_rename 'tb_user', 'tb_user1';`

删除表

当tb_user存在时删除该表

1 2	`IF EXISTS (SELECT * FROM sys.tables WHERE name = 'tb_user') DROP TABLE tb_user;`

删除表并重新创建

清空tb_user表

1	`TRUNCATE TABLE tb_user;`

支持的数据类型

请参考https://learn.microsoft.com/zh-cn/sql/t-sql/data-types/data-types-transact-sql?view=sql-server-ver16

DML

Data Manipulation Language(数据操作语言)

用来对数据库中表的数据记录进行增、删、改操作

添加数据

1 2	`INSERT INTO tb_user VALUES(1, '张三', 18, '0'); INSERT INTO tb_user(id, name, age, gender) VALUES(2, '李四', 19, '1');`

批量添加数据

1	`INSERT INTO tb_user(id, name, age, gender) VALUES(3, '王五', 28, '3'), (4, '赵六', 57, '4');`

查询数据

1	`SELECT * FROM tb_user;`

修改数据

1	`update tb_user set age = 20, gender = '9' where id = 1;`

删除数据

1	`delete from tb_user where id = 1;`

DQL

Data Query Language(数据查询语言)，用来查询数据库中表的记录

准备示例数据

IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = 'demo1')
CREATE DATABASE demo1;
ALTER DATABASE demo1 COLLATE Chinese_PRC_CI_AS;
use demo1;
IF EXISTS (SELECT * FROM sys.tables WHERE name = 'emp')
DROP TABLE emp;
CREATE TABLE emp (
    id INT,
    workno VARCHAR(20),
    name VARCHAR(20),
    gender CHAR(20),
    age TINYINT,
    idcard CHAR(18),
    workaddress VARCHAR(50),
    entrydate DATE
);

-- 添加注释
EXEC sp_addextendedproperty 
@name = N'MS_Description', @value = N'编号', 
@level0type = N'SCHEMA', @level0name = 'dbo', 
@level1type = N'TABLE', @level1name = 'emp', 
@level2type = N'COLUMN', @level2name = 'id';

EXEC sp_addextendedproperty 
@name = N'MS_Description', @value = N'工号', 
@level0type = N'SCHEMA', @level0name = 'dbo', 
@level1type = N'TABLE', @level1name = 'emp', 
@level2type = N'COLUMN', @level2name = 'workno';

EXEC sp_addextendedproperty 
@name = N'MS_Description', @value = N'姓名', 
@level0type = N'SCHEMA', @level0name = 'dbo', 
@level1type = N'TABLE', @level1name = 'emp', 
@level2type = N'COLUMN', @level2name = 'name';

EXEC sp_addextendedproperty 
@name = N'MS_Description', @value = N'性别', 
@level0type = N'SCHEMA', @level0name = 'dbo', 
@level1type = N'TABLE', @level1name = 'emp', 
@level2type = N'COLUMN', @level2name = 'gender';

EXEC sp_addextendedproperty 
@name = N'MS_Description', @value = N'年龄', 
@level0type = N'SCHEMA', @level0name = 'dbo', 
@level1type = N'TABLE', @level1name = 'emp', 
@level2type = N'COLUMN', @level2name = 'age';

EXEC sp_addextendedproperty 
@name = N'MS_Description', @value = N'身份证号', 
@level0type = N'SCHEMA', @level0name = 'dbo', 
@level1type = N'TABLE', @level1name = 'emp', 
@level2type = N'COLUMN', @level2name = 'idcard';

EXEC sp_addextendedproperty 
@name = N'MS_Description', @value = N'工作地址', 
@level0type = N'SCHEMA', @level0name = 'dbo', 
@level1type = N'TABLE', @level1name = 'emp', 
@level2type = N'COLUMN', @level2name = 'workaddress';

EXEC sp_addextendedproperty 
@name = N'MS_Description', @value = N'入职时间', 
@level0type = N'SCHEMA', @level0name = 'dbo', 
@level1type = N'TABLE', @level1name = 'emp', 
@level2type = N'COLUMN', @level2name = 'entrydate';

-- 添加表注释
EXEC sp_addextendedproperty 
@name = N'MS_Description', @value = N'员工表', 
@level0type = N'SCHEMA', @level0name = 'dbo', 
@level1type = N'TABLE', @level1name = 'emp';


INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (1, '00001', '柳岩666', '女', 20, '123456789012345678', '北京', '2000-01-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (2, '00002', '张无忌', '男', 18, '123456789012345670', '北京', '2005-09-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (3, '00003', '韦一笑', '男', 38, '123456789712345670', '上海', '2005-08-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (4, '00004', '赵敏', '女', 18, '123456757123845670', '北京', '2009-12-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (5, '00005', '小昭', '女', 16, '123456769012345678', '上海', '2007-07-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (6, '00006', '杨逍', '男', 28, '12345678931234567X', '北京', '2006-01-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (7, '00007', '范瑶', '男', 40, '123456789212345670', '北京', '2005-05-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (8, '00008', '黛绮丝', '女', 38, '123456157123645670', '天津', '2015-05-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (9, '00009', '范凉凉', '女', 45, '123156789012345678', '北京', '2010-04-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (10, '00010', '陈友谅', '男', 53, '123456789012345670', '上海', '2011-01-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (11, '00011', '张士诚', '男', 55, '123567897123465670', '江苏', '2015-05-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (12, '00012', '常遇春', '男', 32, '123446757152345670', '北京', '2004-02-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (13, '00013', '张三丰', '男', 88, '123656789012345678', '江苏', '2020-11-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (14, '00014', '灭绝', '女', 65, '123456719012345670', '西安', '2019-05-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (15, '00015', '胡青牛', '男', 70, '12345674971234567X', '西安', '2018-04-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (16, '00016', '周芷若', '女', 18, null, '北京', '2012-06-01');

SELECT * FROM emp;

id	workno	name	gender	age	idcard	workaddress	entrydate
1	00001	柳岩666	女	20	123456789012345678	北京	2000-01-01
2	00002	张无忌	男	18	123456789012345670	北京	2005-09-01
3	00003	韦一笑	男	38	123456789712345670	上海	2005-08-01
4	00004	赵敏	女	18	123456757123845670	北京	2009-12-01
5	00005	小昭	女	16	123456769012345678	上海	2007-07-01
6	00006	杨逍	男	28	12345678931234567X	北京	2006-01-01
7	00007	范瑶	男	40	123456789212345670	北京	2005-05-01
8	00008	黛绮丝	女	38	123456157123645670	天津	2015-05-01
9	00009	范凉凉	女	45	123156789012345678	北京	2010-04-01
10	00010	陈友谅	男	53	123456789012345670	上海	2011-01-01
11	00011	张士诚	男	55	123567897123465670	江苏	2015-05-01
12	00012	常遇春	男	32	123446757152345670	北京	2004-02-01
13	00013	张三丰	男	88	123656789012345678	江苏	2020-11-01
14	00014	灭绝	女	65	123456719012345670	西安	2019-05-01
15	00015	胡青牛	男	70	12345674971234567X	西安	2018-04-01
16	00016	周芷若	女	18		北京	2012-06-01

该部分所有查询都基于上面的数据

基础用法

1	`select 'A' from emp;`

你会看到一列16个A

字符串通配

Intro%匹配任何以Intro开头的字符串。
%Comp%匹配任何包含Comp的字符串。
___匹配任何恰好包含三个字符的字符串。
___%匹配任何至少包含三个字符的字符串。

1	`select * from emp where idcard like '%446%'`

id	workno	name	gender	age	idcard	workaddress	entrydate
12	00012	常遇春	男	32	123446757152345670	北京	2004-02-01

执行顺序

集合操作

union 并集
intersect 交集
except 差集

例子（需要在School数据库运行）

找出和学生883794999或学生850955252的年级一样的学生的姓名

select distinct sname from STUDENTS where grade in (
    select grade from STUDENTS 
    where sid = '883794999'
) UNION
select distinct sname from STUDENTS where grade in (
    select grade from STUDENTS 
    where sid = '850955252'
)

聚合函数

avg:平均值
min:最小值
max:最大值
sum:值之和
count:值个数

分页查询

mssql不支持limit进行分页查询

跳过前5个结果，即从第6行开始，取接下来5行

1
2
3

SELECT * FROM emp ORDER BY age 
OFFSET 10 ROWS  -- 跳过前10行
FETCH NEXT 5 ROWS ONLY; -- 取接下来的5行

with子句

例子（只做示范，不可运行）

找到具有资产超过至少一个位于“Brooklyn”的分行的资产的所有分行名称。

这里认为分行的总资产为总储蓄额减去总贷款额, 首先使用三个with函数计算银行的总资产、总储蓄额、总贷款额

然后找到符合条件的分行名称

with total_balance(branch_name, sum_balance) as
(
    select branch_name,sum(balance) from account
    group by branch_name
),
total_loan(branch_name, sum_loan) as 
(
    select branch_name, sum(amount) from loan
    group by branch_name
),
total_money(branch_name, money) as 
(
    select b.branch_name, sum_balance - sum_loan from total_balance b
    join total_loan l on b.branch_name = l.branch_name
)
select branch_name from total_money 
where money > any(
    select money from total_money where branch_name in (
        select branch_name from branch where branch_city = 'Brooklyn'
    )
)

view

创建view

例子（只做示范，不可运行）

create view faculty as
select ID, name, dept_name
from instructor;

select name
from faculty
where dept_name = 'Biology';

大多数SQL实现只允许对简单的view进行更新:

from后面只有一个表
select子句只包含关系的属性名，没有任何表达式、聚合或distinct
任何没有在select子句中列出的属性都可以设置为null
没有group by或having

删除view

1	`drop view faculty`

查看所有视图

1	`select * from INFORMATION_SCHEMA.VIEWS;`

标量子查询

例子（只做示范，不可运行）

这里的子查询应该只返回一个值

select dept_name, 
	(select count(*) from instructor 
    where department.dept_name = instructor.dept_name)
	as num_instructors
from department;

join

natural join 为所有公共属性匹配具有相同值的元组,并且只保留每个公共列的一个副本，但是mssql不支持natural join

using可指定用于连接的同名属性，但是mssql也不支持

create table course(
    course_id VARCHAR(30),
    title VARCHAR(30),
    dept_name VARCHAR(30),
    credits INT
);
CREATE table prereq(
    course_id VARCHAR(30),
    prereq_id VARCHAR(30)
);
insert into course values
('BIO-301', 'Genetics', 'Biology', 4),
('CS-190', 'Game Design', 'Comp. Sci.', 4),
('CS-315', 'Robotixs', 'Comp. Sci.', 3);

insert into prereq values
('BIO-301', 'BIO-101'),
('CS-190', 'CS-101'),
('CS-347', 'CS-101')

示例

join/inner join

1
2
3

select c.course_id, title, dept_name, credits, prereq_id from course c join prereq p on c.course_id = p.course_id;
-- 等价于
select c.course_id, title, dept_name, credits, prereq_id from course c inner join prereq p on c.course_id = p.course_id;

course_id	title	dept_name	credits	prereq_id
BIO-301	Genetics	Biology	4	BIO-101
CS-190	Game Design	Comp. Sci.	4	CS-101

left join

1
2
3

select c.course_id, title, dept_name, credits, prereq_id from course c left join prereq p on c.course_id = p.course_id;
-- 等价于
select c.course_id, title, dept_name, credits, prereq_id from course c left outer join prereq p on c.course_id = p.course_id;

course_id	title	dept_name	credits	prereq_id
BIO-301	Genetics	Biology	4	BIO-101
CS-190	Game Design	Comp. Sci.	4	CS-101
CS-315	Robotixs	Comp. Sci.	3

right join

1	`select c.course_id, title, dept_name, credits, prereq_id from course c right join prereq p on c.course_id = p.course_id;`

course_id	title	dept_name	credits	prereq_id
BIO-301	Genetics	Biology	4	BIO-101
CS-190	Game Design	Comp. Sci.	4	CS-101
				CS-101

full join

1	`select c.course_id, title, dept_name, credits, prereq_id from course c full join prereq p on c.course_id = p.course_id;`

course_id	title	dept_name	credits	prereq_id
BIO-301	Genetics	Biology	4	BIO-101
CS-190	Game Design	Comp. Sci.	4	CS-101
CS-315	Robotixs	Comp. Sci.	3
				CS-101

DCL

Data Control Language(数据控制语言)，用来管理数据库用户、控制数据库的访问权限。

查询用户

1	`SELECT * FROM sys.database_principals;`

授予权限

授予所有用户对表 STUDENTS的查询权限

1	`grant select on students to public;`

授予所有用户对表 COURSES的查询和更新权限

1	`grant select, update on courses to public;`

授予USER1对表 TEACHERS的查询,更新工资的权限,且允许 USERI1可以传播这些权限

1	`grant select, update on teachers to user1 with grant option;`

函数

该部分为AI生成，未经验证

字符串函数

LEN(str)：返回指定字符串 str 的字符数。
CHARINDEX(sub, str[, start])：返回子字符串 sub 在字符串 str 中第一次出现的位置。如果未找到，则返回 0。
SUBSTRING(str, start, length)：从字符串 str 的指定位置开始获取指定长度的子串。
STUFF(str, start, length, newStr)：将新的文本插入到字符串 str 中的指定位置，同时删除原始字符串中相应数量的字符。
REPLACE(str, oldSub, newSub)：在字符串 str 中替换所有出现的旧子字符串 oldSub 为新子字符串 newSub。
LEFT(str, length) 或 SUBSTRING(str, 1, length)：返回字符串 str 的前 n 个字符（包括空格）。
RIGHT(str, length) 或 SUBSTRING(str, LEN(str) - (length-1), length)：返回字符串 str 的最后 n 个字符（包括空格）。
REVERSE(str)：返回输入字符串的反转版本。
UPPER(str) 和 LOWER(str)：将所有字母转换为大写或小写。
LTrim(str)、RTrim(str) 和 Trim(str)：删除字符串 str 左部、右部或两头空格。Trim() 函数会同时去除两端的空白字符。
CONCAT(str1, [str2], ... ,[strN]) 或使用 + 运算符（从 SQL Server 2016 版本开始支持）：连接多个字符串为一个新字符串。
REPLICATE(len, str)：创建由 str 复制 len 次组成的字符串。
CHAR(num): 返回 ASCII 编码表中指定的字符，例如 CHAR(65) 返回 ‘A’。
ASCII(str)：返回输入字符串的第一个非空格字符的 ASCII 值。

数值函数

在 Microsoft SQL Server（MSSQL）中，提供了多种用于操作和转换数据的数学函数。这些函数可以用来执行复杂的计算或简单的算术运算。下面是一些常见的数值函数：

ABS - 返回数值表达式的绝对值。
1
SELECT ABS(-10); -- 输出 10
CEILING / CEIL - 将数值向上舍入到最接近的整数或指定的小数位。
1
SELECT CEILING(3.14), CEILING(3.99); -- 分别输出 4 和 4
FLOOR - 将数值向下舍入到最接近的整数或指定的小数位。
1
SELECT FLOOR(3.14), FLOOR(3.99); -- 分别输出 3 和 3
ROUND - 对数值进行四舍五入。可以指定保留几位小数。
1
SELECT ROUND(3.14159, 2); -- 输出 3.14
TRUNCATE / TRUNC - 将数值截断到所需的小数位或整数位，不进行四舍五入。
1
SELECT TRUNCATE(3.14159, 2), TRUNC(3.14159); -- 输出 3.14 和 3
SQRT - 计算数值的平方根。
1
SELECT SQRT(16); -- 输出 4
POWER - 返回第一个参数的第二个参数次方的结果。
1
SELECT POWER(2, 3); -- 输出 8
EXP - 计算 e（自然对数的底）的指定指数值。
1
SELECT EXP(1); -- 输出约 2.71828
LOG / LOG10 - 返回数值的自然对数或以10为底的对数。
1
SELECT LOG(100), LOG10(100); -- 分别输出约 4 和 2
RAND - 返回一个在0到1之间的随机浮点数（不包括0和1）。
1
SELECT RAND();

SIGN - 返回数值的符号，即 -1、0 或 +1。

1	`SELECT SIGN(-5), SIGN(0), SIGN(5); -- 分别输出 -1, 0 和 1`

这些函数可以用于各种计算需求，从简单的算术操作到更复杂的数学分析。在实际应用中选择合适的函数取决于具体的数据处理或计算要求。

日期函数

Microsoft SQL Server 提供了许多用于操作和查询日期时间数据的内置函数。以下是一些常见的日期函数：

DATEADD - 添加或减少给定的时间单位（如年、月、日等）到一个特定的日期。
1
SELECT DATEADD(month, 3, '2023-04-01'); -- 结果会是'2023-07-01'
DATEDIFF - 计算两个日期之间的时间间隔，可以指定要计算的单位（如年、月、天等）。
1
SELECT DATEDIFF(day, '2023-04-01', '2023-05-01'); -- 结果会是30

DATE - 返回当前的系统日期。

1	`SELECT DATE(); -- 取决于服务器时间，显示当前日期`

DATENAME 和 DATEDPART - 计算并返回给定日期在特定日历项（如星期、月份等）中的值。

1 2	`SELECT DATENAME(weekday, '2023-04-10'); -- 可能会显示 'Monday' SELECT DATEDPART(weekday, '2023-04-10'); -- 返回数字表示，例如 5（星期天）`

GETDATE - 获取当前系统时间。
1
SELECT GETDATE();
CONVERT 或 CAST - 转换数据类型。在转换日期时很有用。
1
SELECT CAST('2023-04-01' AS DATE);
YEAR, MONTH, DAY, HOUR, MINUTE, SECOND - 从一个日期或时间值中提取特定的年、月、日等部分。
1
SELECT YEAR('2023-04-15'), MONTH('2023-04-15'), DAY('2023-04-15');

ADD_MONTHS 和 SUB_MONTHS - 对日期添加或减少指定的月份。

1	`SELECT ADD_MONTHS('2023-04-01', 2); -- 结果会是'2023-06-01'`

DATEPART 和 DATEDIFF - 类似于DATEDIFF和DATENAME，但提供更多的选项以获取特定部分的值（如小时、分钟、秒等）。
1
SELECT DATEPART(hour, '2023-04-15 12:34:56');
DATE_FROM_UNIX_EPOCH - 将从Unix纪元开始的整数转换为日期时间值（Unix纪元从1970年1月1日午夜开始）。
1
SELECT DATEFROMPARTS(2023, 4, 5); -- 根据当前时区计算结果，例如 '2023-04-05'

这些函数可以单独使用或组合在更复杂的查询中，用于数据分析、报表生成以及其他需要日期和时间处理的任务。

流程函数

Microsoft SQL Server 提供了一系列的流程（事务控制）函数和关键字来帮助管理数据库中的并发操作和事务的生命周期。以下是几个常用的与事务和流程相关的功能：

BEGIN TRAN / START TRANSACTION：开始一个新的事务。

BEGIN TRAN;
-- 执行一系列SQL语句...
COMMIT;  -- 提交事务，使更改永久化
ROLLBACK;  -- 回滚事务，取消所有更改并恢复到事务前的状态

ROLLBACK：回滚当前的事务。

1
2
3

BEGIN TRAN;
-- 执行SQL语句...
ROLLBACK;

COMMIT：提交正在进行的事务，使所有更改永久化。
1
2
3
BEGIN TRAN; -- 执行SQL语句... COMMIT;

SAVEPOINT：创建一个保存点，用于回退到事务的一部分。

BEGIN TRAN;
SAVEPOINT my_savepoint;
-- 进行更改并执行更多操作
ROLLBACK TO SAVEPOINT my_savepoint;  -- 回滚到指定的保存点
COMMIT;

SET XACT_ABORT：设置事务处理失败时的行为，可以立即终止事务。

SET XACT_ABORT ON;  
BEGIN TRAN;
-- 执行SQL语句...
SELECT * FROM invalid_table;  -- 这将导致事务结束并抛出错误
COMMIT;

TRY…CATCH：用于捕获和处理在执行过程中的异常。

TRY
    BEGIN TRAN;
    INSERT INTO table_name (column1, column2) VALUES ('value1', 'value2');
CATCH
    IF @@ERROR != 0
        PRINT 'Transaction failed with error number: ' + CAST(@@ERROR as varchar);
    ROLLBACK;  
END TRY
BEGIN TRAN;

RAISERROR：用于显式地在SQL Server中引发错误。

1	`RAISERROR('Error occurred', 16, 1); -- 引发一个严重错误`

SET NOCOUNT：关闭SQL语句的计数输出，避免在每条查询后显示执行行数。
1
2
SET NOCOUNT ON; -- 执行SQL语句...
DBCC CHECKDB（只读模式下）或 SQL Server Management Studio 中使用图形界面：检查数据库中的所有对象以查找可能导致数据损坏的错误。

这些函数和关键字帮助确保事务的正确执行，以及在出现错误时能够控制事务的状态。在处理大量并发操作、维护数据库完整性等方面非常有用。

约束

综述

1. 主键（Primary Key）

定义： 主键用于唯一标识表中的一行。一个表只能有一个主键，并且不能包含 NULL 值。

创建示例：

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY, 
    FirstName VARCHAR(255), 
    LastName VARCHAR(255)
);

查找主键

1
2
3

SELECT *
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' and table_name = 'Employees' ;

2. 外键（Foreign Key）

定义： 外键用于在两个表之间建立关系。它引用另一个表的主键或唯一索引。

创建示例：

CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName VARCHAR(100)
);

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(255),
    LastName VARCHAR(255),
    DepartmentID INT,
    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);

查看所有外键

1
2
3

SELECT *
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' and table_name = 表名;

增加外键

ALTER TABLE 表名
ADD CONSTRAINT 约束名
FOREIGN KEY (外键列)
REFERENCES 其他表名(主键列);

例如

ALTER TABLE Orders
ADD CONSTRAINT FK_Orders_Customers_114514
FOREIGN KEY (CustomerID)
REFERENCES Customers(CustomerID);

级联操作: 你可以指定在删除或更新父表数据时，对子表数据进行级联操作。例如：

ON DELETE CASCADE: 删除父表中的记录时，级联删除子表中关联的记录。
ON UPDATE CASCADE: 更新父表中的主键时，级联更新子表中的外键。

例如

ALTER TABLE Orders
ADD CONSTRAINT FK_Orders_Customers_114514
FOREIGN KEY (CustomerID)
REFERENCES Customers(CustomerID) ON UPDATE CASCADE;

删除外键

1	`alter table borrow drop constraint 外键名;`

删除/更新行为(AI生成)

在 Microsoft SQL Server 中，当涉及到表之间的关系（即外键）时，有几种常见的行为或动作可以定义在主表和从属表之间，以控制数据插入、更新和删除时的行为。这些行为通常通过 FOREIGN KEY 语句中的 ON DELETE 和 ON UPDATE 子句来指定。以下是主要的四种外键约束删除（Delete）和更新（Update）行为：

ON DELETE

当主表中的一行被删除时，以下有几种情况可以发生于从属表中的关联行：

NO ACTION (默认)：如果未定义任何特定的行为，则会尝试删除时引发错误。这是在不允许删除与外键约束相关的任何数据的情况下使用的情况。
RESTRICT：类似于 NO ACTION。这意味着当尝试删除主表中被引用的行时，SQL Server 将不执行删除操作并引发一个错误。
SET NULL：如果从属表中的外键列允许 NULL 值，并且在主表中该行被设置为 NULL 以匹配此行为，则从属表中的相关外键可以变为 NULL。这使得外键引用丢失，但是保留了从属表的完整性规则（例如非空约束）。
CASCADE：如果定义了 CASCADE 行为，那么当主表中被引用的行被删除时，SQL Server 会自动删除所有在从属表中的相关关联行。这意味着从属表的记录将与主表一起删除。
SET DEFAULT：如果在主表中被删除的行有一个默认值（如果存在），则尝试用该默认值来更新从属表中的外键列。这通常不太常见，除非有特别的设计需求。

ON UPDATE

当主表中的数据发生变化时（例如插入、更新或删除数据），与从属表中的引用相关的行为可以定义如下：

NO ACTION (默认)：如果未定义任何特定行为，则尝试更新或添加时可能会引发错误。
RESTRICT：不允许对主表进行修改以违反外键约束。这意味着在主表中无法执行更新或插入，除非所有相关引用都已正确更新或删除。
SET NULL：如果从属表的外键列允许 NULL 值，并且主表中被引用的数据被替换为默认值、设置为 NULL 或者不再存在时，则将从属表中的外键字段设置为 NULL。这有助于保持数据完整性，但可能需要额外的逻辑来确保从属表的其他约束（如非空）得到满足。
CASCADE：如果定义了 CASCADE 行为，那么主表中对引用值的任何更新都将自动导致从属表中的相关行进行相应的更新。这可以确保数据一致性，并在需要时强制执行外键规则。
SET DEFAULT：如果在主表中有默认值，并且使用了 SET DEFAULT 的 ON UPDATE 行为，则尝试用该默认值来更新从属表的外键列，前提是这些更改不会违反任何其他约束（例如非空约束）。

通过正确配置这些行为，可以确保数据库中的数据一致性，在不同操作场景下提供灵活和强大的数据管理功能。在实际应用中选择哪种行为通常取决于特定业务规则或应用程序需求。

3. 唯一约束（Unique Constraint）

定义： 确保表中一个或多个列的值不重复。

创建示例：

CREATE TABLE Product (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(50) UNIQUE NOT NULL,
    CategoryID INT,
    FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID)
);

4. 检查约束（Check Constraint）

定义： 确保数据满足特定的逻辑条件。

创建示例：

CREATE TABLE Sales (
    SaleID INT PRIMARY KEY,
    Amount DECIMAL(10,2) CHECK (Amount >= 0),
    SaleDate DATE
);

复杂的check示例

1	`check (id in (select id from student))`

5. 非空约束（Not Null Constraint）

定义： 确保表中的特定字段不能为 NULL。

创建示例：

CREATE TABLE Users (
    UserID INT PRIMARY KEY,
    FirstName VARCHAR(255) NOT NULL,
    LastName VARCHAR(255)
);

6. 默认值约束（Default Constraint）

定义： 当用户在插入数据时未指定特定字段的值时，使用预设的值。

创建示例：

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    OrderDate DATETIME DEFAULT GETDATE(),
    UserID INT FOREIGN KEY REFERENCES Users(UserID)
);

7. 唯一索引

虽然不是传统意义上的约束，但它们在确保数据的一致性和唯一性方面非常有用。

创建示例：

CREATE TABLE Products (
    ProductID int PRIMARY KEY IDENTITY(1,1),
    ProductName varchar(50) NOT NULL,
    CategoryID int NOT NULL,
    Price decimal(10,2)
);

CREATE UNIQUE NONCLUSTERED INDEX idx_ProductName 
ON Products (ProductName) INCLUDE (CategoryID);

CREATE UNIQUE NONCLUSTERED INDEX: 这部分表示我们要创建一个唯一的、非聚集的索引。

UNIQUE: 确保索引中的值是唯一的，即ProductName列不能有重复值。
NONCLUSTERED: 这个索引不会改变表中数据的物理存储顺序，它只是提供了一种快速查找数据的方式。
INDEX: 表示我们要创建一个索引。
idx_ProductName: 是我们给这个索引起的名字。

ON Product: 指定要创建索引的表是Product。

(ProductName): 索引基于ProductName列创建，查询时会根据ProductName列的值进行快速查找。

INCLUDE CategoryID: 表示除了ProductName列作为索引键之外，我们还希望在索引中包含CategoryID列的值。这样，在查询时，如果查询条件中涉及CategoryID列，就可以直接从索引中获取到相关数据，而不需要回表查询。

案例

P#为表PERSON的主键，具有唯一性约束，Page具有约束：大于18

CREATE TABLE PERSON
(
    P# CHAR(8) NOT NULL UNIQUE,
    Pname CHAR(20) NOT NULL,
    Page INT,
    PRIMARY KEY(P#),
    CHECK(Page>18)
);

R#是表ROOM的主键，具有唯一性约束。

CREATE TABLE ROOM
(
    R# CHAR(8) NOT NULL UNIQUE,
    Rname CHAR(20),
    Rarea FLOAT(10),
    PRIMARY KEY(R#)
)

表P-R中的P#，R#是外键

CREATE TABLE PR
(
    P# CHAR(8) NOT NULL UNIQUE,
    R# CHAR(8) NOT NULL UNIQUE,

    PRIMARY KEY(P#,R#),
    FOREIGN KEY(P#) REFERENCES PERSON ON DELETE CASCADE,
    FOREIGN KEY(R#) REFERENCES ROOM ON DELETE CASCADE
)

为ROOM表创建按R#降序排列的索引

1	`CREATE INDEX XCNO ON ROOM(R# DESC)`

为 PERSON表创建按P#升序排列的索引

1	`CREATE INDEX XSNO ON PERSON(P#) -- (默认升序）`

创建表 PERSON的按 Pname升序排列的唯一性索引

1	`CREATE UNIQUE INDEX RNUA ON PERSON (Pname ASC)`

取消 PERSON表P#升序索引

1	`DROP INDEX PERSON.XSNO`

多表查询

一对一/一对多

假设我们有三个表：Customers, Orders, 和 OrderDetails。这些表如下所示：

Customers 表

ColumnName	Type
CustomerID	int
Name	varchar(50)
Email	varchar(100)

Orders 表

ColumnName	Type
OrderID	int
CustomerID	int
OrderDate	datetime

OrderDetails 表

ColumnName	Type
DetailID	int
OrderID	int
ProductID	int
Quantity	int

我们首先创建这三个表，然后插入一些示例数据：

创建表

-- Customers 表
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    Name VARCHAR(50),
    Email VARCHAR(100)
);

-- Orders 表
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATETIME,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

-- OrderDetails 表
CREATE TABLE OrderDetails (
    DetailID INT PRIMARY KEY,
    OrderID INT,
    ProductID INT,
    Quantity INT,
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID)
);

插入数据

INSERT INTO Customers VALUES
(1, 'John Doe', 'john.doe@example.com'),
(2, 'Jane Smith', 'jane.smith@example.com');

INSERT INTO Orders VALUES
(101, 1, '2023-09-15 14:30:00'),
(102, 2, '2023-09-16 10:20:00');

-- 假设 Product 表中存在以下条目：
INSERT INTO OrderDetails VALUES
(1, 101, 501, 1),
(2, 101, 502, 3),
(3, 102, 501, 2);

-- 这里只是示例，实际 Product 表可能还有更多行。

多表查询

现在我们可以使用多表查询来获取特定客户的所有订单和订购产品的详细信息：

SELECT c.Name, o.OrderDate, d.ProductID, od.Quantity
FROM Customers AS c
JOIN Orders AS o ON c.CustomerID = o.CustomerID
JOIN OrderDetails AS od ON o.OrderID = od.OrderID;

这个查询将 Customers 表、Orders 表和 OrderDetails 表联接在一起，以获取每个客户的订单日期、订购的产品 ID 和数量。

输出效果：

Name	OrderDate	ProductID	Quantity
John Doe	2023-09-15 14:30:00	501	1
John Doe	2023-09-15 14:30:00	502	3
Jane Smith	2023-09-16 10:20:00	501	2

事务

1
2
3

select name, age from emp order by id
OFFSET 1 ROWS 
FETCH NEXT 5 ROWS ONLY;

name	age
张无忌	18
韦一笑	38
赵敏	18
小昭	16
杨逍	28

-- 开始事务处理
BEGIN TRANSACTION;
    UPDATE emp SET age = age + 1;
    UPDATE emp SET age = age + 1;
-- 回滚事务
ROLLBACK TRANSACTION;

再次执行上面的查询，发现age不变

-- 开始事务处理
BEGIN TRANSACTION;
    UPDATE emp SET age = age + 1;
    UPDATE emp SET age = age + 1;
-- 提交事务，保存所做的所有更改。如果任何一个步骤失败，则后续步骤不会执行，并自动回滚。
COMMIT TRANSACTION;

再次查询，观察到age+2

name	age
张无忌	20
韦一笑	40
赵敏	20
小昭	18
杨逍	30

发生语法错误时，会自动回滚

BEGIN TRANSACTION;
    UPDATE emp SET age = age + 1;
    114514 -- 制造语法错误
    UPDATE emp SET age = age + 1;
COMMIT TRANSACTION;

再次执行上面的查询

name	age
张无忌	20
韦一笑	40
赵敏	20
小昭	18
杨逍	30

专业技能

mssql

https://blog.algorithmpark.xyz/2024/10/07/mssql/index/

作者

CJL

发布于

2024年10月7日

更新于

2024年10月19日

许可协议

AI换脸入门上一篇

docker 下一篇