【新星计划2023】SQL SERVER (01) -- 基础知识
【新星计划2023】SQL SERVER -- 基础知识
- 1. Introduction
- 1.1 Official Website
- 1.2 Conn Tool
- 2. 基础命令
- 2.1 建库建表
- 2.2 Alter
- 2.3 Drop
- 2.3 Big Data -- Postgres
- 3.Awakening
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
在一秒钟内看到本质的人和花半辈子也看不清一件事本质的人，自然是不一样的命运。
