时间戳

首页 > 技术文章 > sql server 基本使用

sql server 基本使用

shiyuzuxia 2019-08-01 11:37 原文

表操作

表的类型：已分区表 临时表（tempdb 本地# 全局##） 系统表（视图查看） 宽表 

1.建表

CREATE TABLE dbo.student
(
ID UNIQUEIDENTIFIER NOT NULL,
sno INT NOT NULL,
sname NVARCHAR(20) NOT NULL,
age INT NULL,
class NVARCHAR(20) NOT NULL,
)

2.删表

不能先删除有外键约束的表 

如果删除的表包含带有 FILESTREAM 属性的 varbinary (max) 列，则不会删除在文件系统中存储的任何数据。

DROP TABLE dbo.PurchaseOrderDetail;

3.复制表

  1. 请确保您已经连接到要在其中创建表的数据库并在对象资源管理器中选中该数据库。

  2. 右键单击要复制的表，指向 “编写表脚本为”，然后指向 “CREATE 到”，再选择 “新查询编辑器窗口”。

  3. 更改表的名称。

  4. 删除新表中不需要的列。

  5. 单击“执行

4.表的重命名

GO

EXEC sp_rename 'oldname', 'newname';

ex:

GO
EXEC sys.sp_rename @objname = N'studentt', -- nvarchar(1035)
　　　　　　　　　　@newname = student -- sysname

EXEC命令有两种用法，一种是执行一个存储过程，另一种是执行一个动态的批处理。以下所讲的都是第二种用法。

 5.主键

创建：

表建好后：

ALTER TABLE dbo.student
ADD CONSTRAINT PK_student_ID PRIMARY KEY CLUSTERED(ID)

建表时：

CREATE TABLE [dbo].[student1](
[ID] INT IDENTITY(1,1) NOT NULL,
--TransactionID int IDENTITY (1,1) NOT NULL
[sno] [INT] NOT NULL,
[sname] [NVARCHAR](20) NOT NULL,
[age] [INT] NULL,
[class] [NVARCHAR](20) NOT NULL,
CONSTRAINT PK_student1_ID PRIMARY KEY CLUSTERED (ID)

)

identity(1,1) 从1开始自增 自增额度是1

删除：

alter table 表名

drop constraint  主键名

6.外键

与另一表的主键约束连接  引用另一表中的unique约束的列

仅能用于同一服务器的同一数据库的表  跨数据库的引用完整性必须通过触发器实现

自引用 引用同一表的其他列

有外键约束的主表 的数据删除后 才能删除外键表的相关联的数据

创建外键：

创建表时创建外键：

CREATE TABLE Sales.TempSalesReason
(
TempID int NOT NULL, Name nvarchar(50)
, CONSTRAINT PK_TempSales PRIMARY KEY NONCLUSTERED (TempID) --主健 
, CONSTRAINT FK_TempSales_SalesReason FOREIGN KEY (TempID)--外键
REFERENCES Sales.SalesReason (SalesReasonID)
ON DELETE CASCADE
ON UPDATE CASCADE
)
;

NONCLUSTERED  ：非聚集索引

ON DELETE CASCADE  ON UPDATE CASCADE 保证对外键表的更改能传播到主表上

创建表后创建外键：

ALTER TABLE Sales.TempSalesReason
ADD CONSTRAINT FK_TempSales_SalesReason FOREIGN KEY (TempID)  --主表的外键字段
REFERENCES Sales.SalesReason (SalesReasonID)  --关联的外键表 和 外键表的主健
ON DELETE CASCADE
ON UPDATE CASCADE
;

删除：

ALTER TABLE 表名

DROP CONSTRAINT 外键名;

强制用于复制  enforce for replication
指示在将表复制到另一个数据库中时是否强制该约束。
强制外键约束  enforce foreign key constraint
指示如果对关系中列数据的更改将破坏外键关系的完整性，是否允许进行这些更改。如果不允许进行这些更改，则选择“是”，如果允许进行这些更改，则选择“否”。

7.生成guid

declare @ui uniqueidentifier
set @ui=newid()
select @ui

 

8.索引

聚集索引 CLUSTERED

　　每个表只允许一个聚集索引   物理上实现数据排序   主键约束默认为聚集索引

非聚集索引 NONCLUSTERED

　　逻辑上的排序   unique约束默认为非聚集索引 

　　

可以为主键创建聚集索引或非聚集索引

 

9.约束

唯一约束：unique

主键也是唯一的

检查约束：check

唯一约束：

创建

GO
CREATE TABLE Production.TransactionHistoryArchive4
(
TransactionID int NOT NULL,
CONSTRAINT AK_TransactionID UNIQUE(TransactionID)
);
GO

----

GO
ALTER TABLE Person.Password
ADD CONSTRAINT AK_Password UNIQUE (PasswordHash, PasswordSalt);
GO

删除：

ALTER TABLE dbo.DocExc

DROP CONSTRAINT UNQ_ColumnB_DocExc;

检查约束：

ALTER TABLE dbo.DocExc

ADD ColumnD int NULL

CONSTRAINT CHK_ColumnD_DocExc CHECK (ColumnD > 10 AND ColumnD < 50);

9.填充因子：

 10.视图

基于sql的结果集的可视化表

虚拟表 有列和行数据 从一个或多个表导出

视图基于基本表 对视图的修改会改变基本表

标准视图：比起基本表，可以重点放在特定的数据上

索引视图：

分区视图：

创建视图：

CREATE VIEW ASView AS
SELECT sno,sname,age cname
FROM student,sclass
WHERE student.classno = sclass.ID

 11.触发器

INSERTed:插入临时表

ex:

--新增学生表的学生时 班级表的对应班级总人数加1
--CREATE TRIGGER addStu_classCount
--ON student
--after insert
--AS
--declare @classID UNIQUEIDENTIFIER
--SELECT @classID=student.classno FROM student INNER JOIN inserted ON dbo.student.ID = INSERTed.ID
--UPDATE dbo.sclass SET count+=1 WHERE ID = @classID

12.

游标

临时数据库对象  存放数据库表中数据行的副本 指向数据行的指针

遍历数据行  保存查询结果 创建游标后，查询数据可从游标里查询 比直接数据库查询快

创建：

declare cursor_name  --游标名称，唯一标识
[insensitive] [scroll] cursor 
for
select_statement --查询语句
[for {read only| update [of column_name [,...n]]}]

scroll : 表示可以指针可以随意指向  没设定表示只能向前

insensitive : 查询结果的临时副本 不用指向数据库中的源数据

　　　　不能通过游标修改源数据  源数据改变后 也不会反映到游标中

　　　　static 也有同样的意思

fetch:

next:下一行　　 prior:上一行 　　first:结果集第一行     last:结果集最后一行   

absolute: 从首行向后移动n行(absolute 3) 指针位置在首行    relative:向后移动n行 指针位置可以移动

@@Fetch_Status

全局变量@@Fetch_Status的值，获得提取状态信息，该状态用于判断Fetch语句返回数据的有效性。当执行一条Fetch语句之后，@@Fetch_Status可能出现3种值：0，Fetch语句成功。-1：Fetch语句失败或行不在结果集中。-2：提取的行不存在。

利用游标增删改基表数据：

Update 基表名 Set 列名=值[,...] Where Current of 游标名
--游标删除当前数据语法
Delete 基表名  Where Current of 游标名

游标关闭：

Update 基表名 Set 列名=值[,...] Where Current of 游标名
--游标删除当前数据语法
Delete 基表名  Where Current of 游标名

close :不会释放其占用的数据结构 游标指针会回到首行    

deallocate:删除游标中的数据 将游标作为对象从数据库中删除。

ex:

-游标
--DECLARE stu_cursor SCROLL CURSOR
--FOR
--SELECT sno,sname FROM dbo.student
--OPEN stu_cursor
DECLARE @sno INT,@sname NVARCHAR(20)
FETCH next FROM stu_cursor
FETCH ABSOLUTE 3 FROM stu_cursor
---INTO @sno,@sname
--PRINT '学号' + CAST(@sno AS VARCHAR) +'姓名'+ @sname
--CLOSE stu_cursor

100.

sql语句：

SELECT DISTINCT：过滤掉重复数据

order By 排序 结合 desc asc

 

 insert to:

INSERT INTO 语句可以有两种编写形式。

第一种形式无需指定要插入数据的列名，只需提供被插入的值即可：

INSERT INTO table_name
VALUES (value1,value2,value3,...);

第二种形式需要指定列名及被插入的值：

INSERT INTO (column1,column2,column3,...)
VALUES (value1,value2,value3,...);
 
update:
修改表中已存在的数据
UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;
 

 delete:

用于删除表中的行

DELETE FROM table_name
WHERE some_column=some_value;

SELECT TOP

 

like:配合通配符

select xx from xx where xx like '%g'

%  替代1个或多个字符

- 替代一个字符

下面2条不适用 sql server:

[] 字符串中的任何一个单字符

[^ ] [! ] 不在字符串中的任何一个字符    ‘^[a-h]’ 以a到h开头 (^代表开头)

 

in, between :

结合where

where name in (xx,xx,xx)

where age between 12 and 20

 

as:

指定别名

select name as n  -- name被n 代替

 

join:

inner join 表名 on xxx :结果只会显示主表的外键和外键表的主键匹配上的

 

left join 表名 on xxx:以主表为主，主表的全显示，外键表只显示能配到主表的行,配不上的用null替代

 

 

right join 表名 on xxx:以外键表为主, 和left join 相反

 

full join:同上面 为null的会全部显示：

union, union all:

合并多个select语句的结果集   select应该拥有相同的列数 各个字段的类型也要类似

 union会去重  union all不会去重

 

select into :

从一个表选择数据复制到新表中  注意是新表

select * 

into 新表

form 旧表

 

as:

select的时候可以把字段重命名

select sno as ssnno

 

insert into select :

把数据从表中复制到一个已存在的表中。表名1-〉表名2

insert into 表名2（字段名，字段名）

select 字段名 字段名 from 表名1

 

'  '和 "  "的区别：

 

 

 

推荐阅读