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

3.1 事務四大特性

• 原子性（Atomicity）
  原子性是指事務是一個不可分割的工作單位，事務中的操作要么全部成功，要么全部失敗。
• 一致性（Consistency）
  事務必須使數(shù)據(jù)庫從一個一致性狀態(tài)變換到另外一個一致性狀態(tài)。
• 隔離性（Isolation）
  事務的隔離性是多個用戶并發(fā)訪問數(shù)據(jù)庫時，數(shù)據(jù)庫為每一個用戶開啟的事務，不能被其他事務的操作數(shù)據(jù)所干擾，多個并發(fā)事務之間要相互隔離。
• 持久性（Durability）
  持久性是指一個事務一旦被提交，它對數(shù)據(jù)庫中數(shù)據(jù)的改變就是永久性的

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賦值是從表中查出的數(shù)據(jù)
  SELECT @variable_name=value

4.2 官方Demo

建表，循環(huán)插入26筆數(shù)據(jù)

-- 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

4.3.4 使用 SELECT @local_variable 返回單個值

4.3.5 使用 SELECT @local_variable 返回 null

5. Awakening

?????在一秒鐘內看到本質的人和花半輩子也看不清一件事本質的人，自然是不一樣的命運。

文章來源地址http://www.zghlxwxcb.cn/news/detail-445274.html

到了這里，關于【新星計劃2023】SQL SERVER (01) -- 基礎知識的文章就介紹完了。如果您還想了解更多內容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關文章，希望大家以后多多支持TOY模板網(wǎng)！