Transact-SQL基本用法整理

Sql Server在服务器开发中用的不是最多,但也属于主流数据库服务器之一。对于各种不同的数据库服务器,数据库查询语句有细微差别。这边文章用于做为T-SQL语句的整理,一方面列出各种用法作为参考,另一方面也为其他不同类型数据库使用提供另一种实现思路。

常用数据类型:

1
2
3
4
5
6
7
8
9
10
INT        --整数,不带宽度
FLOAT      --浮点数,不带宽度
DATE       --日期,不带宽度
datetime   --日期时间,不带宽度
CHAR       --字符,带宽度
NCHAR      --Unicode字符,带宽度
VARCHAR    --变长字符,带宽度
nvarchar   --变长Unicode字符,带宽度
DECIMAL    --定点数,带宽度
NUMERIC    --定点数,带宽度

除了常用类型外还有二进制流类型、Image类型等,因为用的非常少所以就不全部列举了。

5种约束:

1
2
3
4
5
PRIMARY KEY    --主键,指定列或多个列不允许重复,查询数据库默认以主键排序
UNIQUE         --唯一键,指定列或多个列不允许重复
CHECK          --检查键,插入的数据必须符合此条件
DEFAULT        --默认键。当插入数据时写入的值为default,那么值将被设置为默认键指定的值
FOREIGN KEY    --外键。指定键必须与其他表某列相关联。

估计玩数据库的朋友们最讨厌的就是外键了,清空表时各种删不掉,改外键值也导致各种关联错误。Sql Server在外键方面提供了两个限制语句极大方便了外键的操作。只需在创建表时写上这个语句即可实现功能:

1
2
ON DELETE cascade  --同步删除。删除其他表数据时,同步删除这张表的关联数据
ON UPDATE cascade  --同步更新。修改其他表关联的这个列的数据时,同步修改这个外键的数据

数据库作为数据存储的基本载体,使用文件的形式进行存放数据。Sql Server通常使用*.mdf、*.ldf两个文件来存放数据库的东西。mdf用来存储所有数据,ldf用来存储操作日志
数据库的增删改查:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
--创建数据库
CREATE datebase 数据库名 ON
(
    name='数据库名_data',                              --数据库名定义
    filename='D:/MyDataBase/数据库名_data.dbf',        --数据文件存储位置
    SIZE=5mb,                                         --初始大小
    maxsize=20mb,                                     --最大大小
    filegrowth=10%                                    --文件大小递增方式
)
log ON
(
    name='数据库名_log',                               --数据库日志名定义
    filename='D:/MyDataBase/数据库名_data.dbf',        --数据日志文件存储位置
    SIZE=2mb,                                         --初始大小
    maxsize=5mb,                                      --最大大小
    filegrowth=1mb                                    --文件大小递增方式
)
 
--删除数据库
DROP DATABASE 数据库名
 
--重命名数据库:
EXECUTE sp_renamedb 数据库旧名, 数据库新名
 
--引用数据库:
USE 数据库名

表的增删改查:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
--创建表
CREATE TABLE [数据库名.]表名 (
    --列名      类型         其他1         INT          CONSTRAINT PK_表名 PRIMARY KEY    IDENTITY(1, 1),2         CHAR(10)     NOT NULL,
    CONSTRAINT2 DF_列2 DEFAULT ('abcd')
)
 
--删除
DROP TABLE [数据库名.]表名
 
--改名
EXECUTE sp_rename 旧表名,新表名
 
--查询所有表名
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'

列的增删改查:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
--增加
ALTER TABLE [数据库名.]表名 ADD 列名 INT
 
--删除
ALTER TABLE [数据库名.]表名 DROP COLUMN 列名[, 列名2]
 
--修改
EXECUTE sp_rename '[数据库名.]表名.旧列名', '新列名', 'COLUMN'          --修改列名
ALTER TABLE [数据库名.]表名 ALTER COLUMN 列名 NOT NULL                 --修改列属性
ALTER TABLE [数据库名.]表名 ADD CONSTRAINT 约束名 约束类型 约束表达式  --添加约束
ALTER TABLE [数据库名.]表名 DROP CONSTRAINT 约束名                     --删除约束
 
--查询
SELECT * FROM [数据库名.]表名 WHERE 1=2         --只使用列名就行

行作为最基本的数据存储单元。普通对于数据库的操作用的最多的都是行操作。
行的增删改查:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
--增加
INSERT INTO [数据库名.]表名 VALUES ('列1值', '列2值')
 
--删除
DELETE FROM [数据库名.]表名 WHERE 条件
DELETE FROM [数据库名.]表名      --清空
TRUNCATE TABLE [数据库名.]表名   --清空
 
--修改
UPDATE [数据库名.]表名 SET2='列2新值' WHERE1='列1旧值'
 
--查询
SELECT1 AS1显示名,2显示名 =2 FROM [数据库名.]表名     --别名
SELECT1指标 =                                                  --临时改变
    CASE
        WHEN1 < 100 THEN '少'
        WHEN1 < 200 THEN '一般'
        WHEN1 >= 200 THEN '多'
    END
    FROM [数据库名.]表名
SELECT1,2, 临时列1 = (1*2) FROM [数据库名.]表名  --临时列
SELECT DISTINCT1 FROM [数据库名.]表名                   --不重复
SELECT top 50 * FROM [数据库名.]表名                       --前50行
SELECT top 15 percent * FROM [数据库名.]表名               --前15%行
 
--分页
SELECT top 10 * FROM 表名 WHERE 表名.某列 NOT IN (SELECT top 90 表名.某列 FROM 表名)

视图在简单的数据库使用中几乎不被用到,但它也有非常方便的地方比如修改视图可以直接同步进数据库。用来抽象数据表就非常方便了。
视图的增删改查:

1
2
3
4
5
6
7
8
9
10
11
--增加
CREATE VIEW 视图名 AS <SELECT语句>
 
--删除
DROP VIEW 视图名
 
--修改
ALTER VIEW 视图名 AS <SELECT语句>
 
--查询(当做一张表进行查询,可修改,可使用where等等几乎所有限定条件)
SELECT * FROM 视图名

存储过程是Sql Server中非常强大的一个功能,对于类似高并发的数据库服务访问,存储过程可以确保所得结果的正确性,对于出错过程自动回滚。
存储过程的增删改查:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
--增加
CREATE PROCEDURE 存储过程名(          --创建存储过程,procedure可简写为proc
    @参数1 INT =0 output,              --变量可用于输出
    @参数2 nvarchar(10) ='默认数据'
)
AS
    DECLARE @变量1          --定义变量
    SET @变量1 = @参数1     --赋值
    print @参数1            --存储过程中输出参数1的值
 
CREATE PROCEDURE 存储过程名
WITH recompile              --不缓存
AS SQL语句
 
CREATE PROCEDURE 存储过程名
WITH encryption             --加密
AS SQL语句
 
--删除
DROP PROCEDURE 存储过程名
 
--修改
ALTER PROCEDURE 存储过程名 AS SQL语句
 
--执行
EXECUTE 存储过程名 参数1, 参数2      --调用存储过程,execute可简写为exec

自定义函数与存储过程类似,但它作用于另外一块,比如使用一个数据库语句几乎不可能完成的操作,使用自定义函数一步一步实现,就会非常方便。
自定义函数的增删改查:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
--增加
CREATE FUNCTION 函数名 (@参数名 参数类型, ...)
    RETURNS 返回值类型
    [WITH {Encryption | Schemabinding }]
AS BEGIN
    DECLARE @变量 变量类型
    SET @变量 = ...
    RETURN @变量
END
 
--删除
DROP FUNCTION 函数名
 
--执行
SELECT 函数名 (@参数名, ...)

触发器是一个比较神奇的东西。通常在某个表添加数据、修改数据时触发。对于一些已经完成的项目需要新加一些什么功能但又不想把代码改乱,那么使用触发器来实现就比较适合。
触发器的增删改查:

1
2
3
4
5
6
7
8
9
10
11
12
13
--增加
CREATE TRIGGER 触发器名
    ON 表名或视图名
    { FOR | after | instead OF } [INSERT, UPDATE, DELETE]
AS
    <SQL语句>
 
--删除
DROP TRIGGER 触发器名
 
--修改
ALTER TRIGGER 触发器名
    ON ...

以上整理只列出了基本的各种对象的操作,不包括任何高级用法。这篇文章用来作为对数据库使用方法的整理做速查用。对于学习此文的朋友们不要觉得此文看懂就是高手了。你们的数据库学习之路才刚刚开始(~ ̄▽ ̄)~

发布者

fawdlstty

又一只萌萌哒程序猿~~

发表评论

电子邮件地址不会被公开。 必填项已用*标注