【新星计划2023】SQL SERVER (01) -- 基础知识-Toy模板网

这篇具有很好参考价值的文章主要介绍了【新星计划2023】SQL SERVER (01) -- 基础知识。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

【新星计划2023】SQL SERVER (01) -- 基础知识

1. Introduction

1.1 Official Website

官方文档（小技巧）
Officail Website: https://learn.microsoft.com/en-us/sql/sql-server/?view=sql-server-ver16.
Officail Website(中文): https://learn.microsoft.com/zh-cn/sql/sql-server/?view=sql-server-ver16.

1.2 Conn Tool

官方自带的
SQL Server Management Studio (SSMS)
SSMS Install: https://learn.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver16.

我自己使用的
Dbeaver
Dbeaver Install: https://dbeaver.io/.

2. Command

2.1 Create

// create database
CREATE DATABASE database_name;

// create schema
CREATE SCHEMA schema_name  AUTHORIZATION dbo; 

//create table(主键自增)
CREATE TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }(
	stu_id int IDENTITY(1,1) NOT NULL,
	department_id int,
	stu_province nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	stu_city nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	stu_town nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	stu_address nvarchar(200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	stu_score int,
	CONSTRAINT PK__STUDENG__8379F1C4D400EC53 PRIMARY KEY (stu_id)
);

2.2 Alter

//add column
ALTER TABLE STUDENT ADD stu_hobby  nvarchar(200) not null default 0;

// 修改栏位名
ALTER TABLE STUDENT rename column A to B;

// 修改栏位类型
ALTER TABLE STUDENT alter column A type not null;

// 删除栏位
ALTER TABLE STUDENT drop column A;

// 添加主键
ALTER TABLE STUDENT ADD CONSTRAINT PK_STUDENT PRIMARY KEY(stu_id);;

2.3 Drop

// delete database
DROP DATABASE DatabaseName; 

// delete schema
DROP SCHEMA schema_name ;

// delete table
DROP TABLE dbo.STUDENT;

// delete column
ALTER TABLE STUDENT  drop column A;

// query primary key
SELECT name  FROM sys.key_constraints  WHERE type = 'PK' ANDOBJECT_NAME(parent_object_id) = N'tablename';

// delete primary key
ALTER TABLE STUDENT DROP CONSTRAINT PKname;

3. Transaction

【新星计划2023】SQL SERVER (01) -- 基础知识

3.1 事务四大特性

原子性（Atomicity）
原子性是指事务是一个不可分割的工作单位，事务中的操作要么全部成功，要么全部失败。

一致性（Consistency）
事务必须使数据库从一个一致性状态变换到另外一个一致性状态。

隔离性（Isolation）
事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。

持久性（Durability）
持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的

4. Variables

4.1 定义变量

DECLARE关键字，定义变量
DECLARE @MyCounter INT;
DECLARE @LastName NVARCHAR(30), @FirstName NVARCHAR(20), @StateProvince NCHAR(2);

变量赋值
1.set赋值指定的常量
DECLARE @MyCounter INT;
SET @MyCounter = 1;
2.select赋值是从表中查出的数据
SELECT @variable_name=value

4.2 官方Demo

建表，循环插入26笔数据

-- Create the table.
CREATE TABLE TestTable (cola INT, colb CHAR(3));
GO
SET NOCOUNT ON;
GO
-- Declare the variable to be used.
DECLARE @MyCounter INT;

-- Initialize the variable.
SET @MyCounter = 0;

-- Test the variable to see if the loop is finished.
WHILE (@MyCounter < 26)
BEGIN;
   -- Insert a row into the table.
   INSERT INTO TestTable VALUES
       -- Use the variable to provide the integer value
       -- for cola. Also use it to generate a unique letter
       -- for each row. Use the ASCII function to get the
       -- integer value of 'a'. Add @MyCounter. Use CHAR to
       -- convert the sum back to the character @MyCounter
       -- characters after 'a'.
       (@MyCounter,
        CHAR( ( @MyCounter + ASCII('a') ) )
       );
   -- Increment the variable to count this iteration
   -- of the loop.
   SET @MyCounter = @MyCounter + 1;
END;
GO
SET NOCOUNT OFF;
GO
-- View the data.
SELECT cola, colb
FROM TestTable;
GO
DROP TABLE TestTable;
GO

4.3 Example

4.3.1 输出使用 SET 初始化的变量值

DECLARE @myvar CHAR(20);  
SET @myvar = 'This is a test';  
SELECT @myvar;  
GO

4.3.2 在 SELECT 语句中使用由 SET 赋值的局部变量

USE AdventureWorks2019;  
GO  
DECLARE @state CHAR(25);  
SET @state = N'Oregon';  
SELECT RTRIM(FirstName) + ' ' + RTRIM(LastName) AS Name, City  
FROM HumanResources.vEmployee  
WHERE StateProvinceName = @state;
GO

4.3.3 为局部变量使用复合赋值

/* Example one */  
DECLARE  @NewBalance  INT ;  
SET  @NewBalance  =  10;  
SET  @NewBalance  =  @NewBalance  *  10;  
SELECT  @NewBalance;
GO
  
/* Example Two */  
DECLARE @NewBalance INT = 10;  
SET @NewBalance *= 10;  
SELECT @NewBalance;
GO