SQL总结

最新推荐文章于 2025-12-20 22:59:04 发布

原创最新推荐文章于 2025-12-20 22:59:04 发布 · 154 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql #数据库

本文详细介绍了SQL的基础知识，包括创建和删除模式、表定义、数据类型、完整性约束、索引、查询语句、视图、安全性管理和权限控制等内容。此外还讲解了触发器、函数和存储过程的创建与使用。

模式 scheme

CREATE SCHEMA schema_name_clause [ <schema_element> [ ...n ] ]

<schema_name_clause> ::=
    {
        schema_name
    | AUTHORIZATION owner_name
    | schema_name AUTHORIZATION owner_name
    }

<schema_element> ::= 
    { 
        table_definition | view_definition | grant_statement | 
        revoke_statement | deny_statement 
    }

--删除模式 模式中不能含任何表
DROP SCHEMA schema_name

表

CREATE

create table table_name(
	column1 int [constraint <约束名>] primary key,	--主码约束
	column2 int [constraint <约束名>] foreign key references table2(column1), --表示列 要有括号
	column2 int foregin key references table3(column1) 
		[on { delete | update}   cascade | on action ],		--违约处理 删除或更新时，外码自动修改/拒绝执行
		
	--用户定义的完整性： unique\null\not null
	column3 int [constraint <约束名>] unique,		--唯一
	column4 int null,		--可为null 默认
	column5 int not null,
	column6 int check (<表达式>)
);

create table table_name(
	column1 int,
	column2 int,
	
    --表级完整性约束
	[constraint <约束名>] primary key(column1, column2),
	[constraint <约束名>] foreign key (column1) references table2(column1),
	foreign key (column2) references table2(column2)
		[on { delete | update}   cascade | on action ]		--违约处理 删除或更新时，外码自动修改/拒绝执行
);

-- MySQL 设置存储引擎和默认字符编码
create table table_name(
	....
)ENGINE=InnoDB DEFAULT CHATSET=utf8;

数据类型
- int --4bytes
- smallint --2bytes
- bigint --8bytes
- tinyint --1bytes
- decimal(总位数, 小数点后位数) 定点数
- numeric( , ) 同decimal
- real --单精度浮点数（取决于机器）
- double precision --双精度浮点数(取决于机器）
- float(10) --可选精度浮点数
- boolean
- date 日期
- time 时间
- char(n) 长度为n的定长字符串 (指定字符串长度为n字节，而不是字符数)
- varchar(n | max) 最大长度为n的字符串
- nchar(n) 指定字符串的长度为2n字节（n个双字节）
- nvarchar(n | max)
- set — MySQL
- enum — MySQL
set

create table test(
	myset set('a','b','c')
);

insert into test values('a,b');

enum

create table test(
	gender enum('m', 'n');
);

insert into test values('m');

nchar / char
- nchar -> notional char 以双字节为单位
- char 单字节为单位
varchar / char
- varchar(n) 可变长,储存长度(SQL Server)为字符串字节数+2, 用于储存长度。比较时结尾填充的空格不会计入。
- char(n) 固定长度，n字节。结尾以空格填充。比较时结尾填充的空格不会计入。
ALTER

--添加列
alter table table_name
add [column] <列定义>; --!!!!SQL Server 不加column!!!!

--添加约束
alter table table_name
add [constraint <约束名>] <约束>
--添加主键约束 (列必须 not null)
alter table table_name add [constraint <约束名>] primary key(列...);
--添加外码约束
alter table table_name add [constraint <约束名>]
foreign key (设为外码的列...) references another_table(主码...)
[on { delete | update}   cascade | on action | set default ];		--违约处理 删除或更新时，外码自动修改/拒绝执行
	
--删除约束
alter table table_name
drop constraint <约束名>;

--修改列	--null not null unique只能通过这种方式来改变
alter table table_name
alter column <列定义>

--删除列
alter table table_name
drop [column] <列名>;

--修改时添加unique约束
alter table table_name
add constraint <约束名> unique(列名);

--修改时添加default约束
alter table table_name
add constraint <...> default ... for 列名;

索引：加速查询，但占用空间，增加删改的成本
- unique 唯一约束
- clustered 聚集索引 一个表上只能建立一个聚类索引

--SQL Server:
--次序可为升序ASC/降序DESC，默认为 升序
create [unique] [clustered] index <索引名> on <表名>(列名 [次序]......);
--删除索引
drop index <索引名> on <表名> --SQL Server 加表名

SELECT
- 注意distinct的使用
- 聚集函数内的distinct
  - sum([distinct | all] 列明)
- 聚集函数只能用在select子句和having子句中
- count() 计入null
- 除运算： R(A,B) ÷ S(B, C)
```
select A from R	r1		--对于这个A
where not exists (		--不存在这样一个B
	select * from S where not exists (  --这个B不在这个A的象集中
		select * from R r2 where r2.A = r1.A and r2.B = S.B;
	)
)
```
- 谓词 ANY ALL：比较运算符只能在右边
- 集合操作 union并、intersect交、except差。对应列的数据类型要兼容。若要UNION保留重复元组使用UNION ALL
```
<select ... > union/intersect/except <select ...>
```
- like ‘…’
  - % 任意长度所有字符
  - _ 任意单个字符
  - ‘…_…’ escape ‘’ 定义\为转义字符
- 选取部分
```
select [all|distinct] 
select [TOP n | TOP n PERCENT][WITH TIES]	-- SQL Server with ties 表示与最后一个元素相等的元素是否可以超出限制
```
INSERT

insert into <表名>[(列...)]
values(....), ....;

insert into <表名>[(列...)]
<select...>

UPDATE DELTE

update <表名>
set .. = ..
where ...;	--可带有（相关）子查询

delete <表名>
where ...; --可带有（相关）子查询

视图

create view <name>[(column_name, ...)]
as <select ...>
[with check option]

with check option：对视图进行update\insert\delete时保证行满足select中定义的条件
行列子集视图：从单个基本表中导出，且保留了主码。一般可以更新。

安全性

创建用户 T-SQL

create user <user_name> 
for login <login_name>;

--使用储存过程
exec sp_adduser "login_name", "user_name"

创建角色

create role 角色名 [authorization 数据库用户或角色]

赋予用户/角色角色

--数据库角色
exec sp_addrolemember 'role' , 'role/user';

--数据库角色
-- exec sp_addsrvrolemember 'login_name', 'role';

grant

grant <权限>... on <数据库对象>
to <用户/角色>...
[with grant option];	--该被授权的用户是否可以转授

public 表示所有用户
权限：
- SELECT、INSERT、UPDATE和DELETE —> 整个表或视图上
- SELECT和UPDATE --> 表或视图中的单个列上
- EXECUTE --> 存储过程和函数上
update某列的权限 (select某列相同)

grant update(<列>) on <表>
to ...
....

* T-SQL：全部权限
grant all on ... to ...;

revoke

revoke ... on ... from ... [cascade | restrict] -- T-SQL只可以写cascade

(T-SQL)deny – 拒绝为主体授予权限。防止该主体通过组或角色成员身份继承权限。 DENY 优先于所有权限，但 DENY 不适用于 sysadmin 固定服务器角色的对象所有者或成员

deny ... on <表名|视图名> to <数据库用户名|角色名>

创建登录名 T-SQL

create login <登录名> with password = '123456', DEFAULT_DATABASE = xsgl;

--使用储存过程
execute sp_addlogin 'loginname','password', 'default_database' ...
sp_addlogin
[@loginname=] login_id                                --登录名称
[,[@passwd=]passwd]                                   --登录密码
[,[@defdb=]defdb]                                     --默认数据库
[,[@deflanguage=]deflanguage]                   	  --默认语言
[,[@sid=]sid]                                         --安全标识号
[,[@encryptopt =]encryption_option]         		  --密码是否加密

修改登录密码 T-SQL

sp_password 
[ [ @old = ] old_password , ]            --旧登录密码
{ [ @new =] new_password }            	 --新登录密码
[ , [ @loginame = ] login ]              --登录名称

修改登录名

ALTER LOGIN login_name 
{ 
    <status_option>  | WITH <set_option> [ ,... ] 
}

--修改密码
alter login login_name with password = '11111'
--改名
alter login login_name with name = 'tttt'

触发器 - SQL Server

DML触发器
- after 执行后
- instead of 取代原有的操作

create trigger trigger_name
	ON { table_name | view }
	[WITH ENCRYPTION ]
	{ FOR | AFTER | INSTEAD OF } 
	{ [ INSERT ] [ , ]  [ DELETE ] [ , ]  [UPDATE ] }
	AS sql_statement [...n ]

inserted --改动后、插入的记录
deleted --改动前、删除的记录

函数 - SQL Server

标量函数

CREATE FUNCTION [schema_name.]function_name
([{@parameter_name [AS] parameter_data_type [=default]}[,...n]])	-- 参数列表
RETURNS return_data_type
[AS]
BEGIN
function_body
RETURN expression
END

--调用
select schema_name.function_name()

内嵌表值函数 - 返回的一个直接select语句结果集或一个临时表中的数据

create function [schema_name.]function_name
([{@parameter_name [AS] parameter_data_type [=default]}[,...n]])
returns table
return select ...

--调用
select * from [schema_name.]function_name()

多语句表值函数

create function [schema_name.]function_name
([{@parameter_name [AS] parameter_data_type [=default]}[,...n]])
returns @return_variable table <table_type_definition>
[as]
begin
function_body
return;
end

--调用
select * from [schema_name.]function_name()

储存过程 - SQL Server

CREATE PROC[ EDURE ] 存储过程名[;number] 
[ { @参数名  数据类型 } [ = default ] [OUTPUT] -- output参数可将信息传回，调用时也需要加
] [ , ... n ]
  AS  
    SQL语句 [ ... n ]

--执行
execute ... [参数...]

游标

DECLARE  游标名[insensitive][scroll] CURSOR 
FOR SELECT 语句
[for{read only|update [of<列名>[,…,n]]}]

open <游标>

FETCH  [ [ NEXT | PRIOR | FIRST | LAST | ABSOLUTE { n | @nvar } | RELATIVE { n | @nvar } ]
FROM 游标名  [ INTO @variable_name [,...n ] ]

fetch relative 1 from cur;
fetch next from cur;
fetch prior from cur;
fetch absolute 2 from cur;
fetch last from cur;
fetch first from cur;

close <游标>
deallocate <游标> 	--销毁

--使用@@FETCH_STATUS全局变量判断数据提取的状态。
@@FETCH_STATUS = 0 成功
@@FETCH_STATUS = -1 FETCH语句失败或此行不在结果集中
@@FETCH_STATUS = -2 被提取的行不存在


--逐行读取
fetch next from cur;
while @@FETCH_STATUS = 0
begin
	fetch next from cur
end

--update delete
update ... where current of <游标>
delete ... where current of <游标>

insensitive 是否对修改敏感
scroll 可以双向移动

其他

断言 – MySql SQL Server 都不支持

create assertion <断言名> <check子句>