【新星计划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. 基础命令

2.1 建库建表

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

2.3 Big Data – Postgres

3.Awakening

     在一秒钟内看到本质的人和花半辈子也看不清一件事本质的人，自然是不一样的命运。