SQL SERVER 基础语句学习(三)

1. Join
Tips:
(1). Inner Join 同 Join

Select alias_name1.column_name, alias_name2.column_name
From table_name1 as alias_name1
Inner join table_name2 as alias_name2
On alias_name1.column3 = alias_name2.column4
Order by alias_name1.column_name1

(2). Left Join, 即使右表没有匹配，也返回左表所有行

Select alias_name1.column_name, alias_name2.column_name
From table_name1 as alias_name1
Left join table_name2 as alias_name2
On alias_name1.column_name3 = alias_name2.column_name4
Order by alias_name1.column_name1

(3). Right Join, 即使左表没有匹配，也返回右表所有行

Select alias_name1.column_name, alias_name2.column_name
From table_name1 as alias_name1
Right join table_name2 as alias_name2
On alias_name1.column_name3 = alias_name2.column_name4
Order by alias_name1.column_name1

(4). Full Join, 返回所有行

Select alias_name1.column_name, alias_name2.column_name
From table_name1 as alias_name1
Full join table_name2 as alias_name2
On alias_name1.column_name3 = alias_name2.column_name4
Order by alias_name1.column_name1

 2. Union 合并多个select 语句的结果

Tips:
Select 语句必须有相同数量的列，且有相似的数据类型，列的顺序也必须相同
Union 默认返回不同的值
Union All 允许返回重复的值
结果中的列名为第一个select 语句中的列名

Select column_name(s) from table_name1
Union
Select column_name(2) from table_name2

3. Select Into 从一个表选数据插入另一个表， 常用于备份复件或存档记录

Select *
Into new_table_name
From old_table_name

Tips:
(1). 从externaldatabase 选

Select *
Into new_table_name
From externaldatabase_name.dbo.table_name

(2). 如果表2已经存在，则用 Insert Into

Insert Into table2_name(column1, column2,…)
Select table1_column1,table1_column2,… From table1_name

4. Constraint  约束

(1). Not Null

(2). Unique  约束唯一，每个表可有多个unique

可以不设置unique 名，系统有默认值，但是建议设置，方便使用; 可以同时设置多列

Alter Table table_name  Add unique (column_name)

Alter Table table_name Add constraint uq_name unique (column_name1，column_name2) 

Alter Table table_name Drop constraint uq_name

(3). Primary Key  约束唯一，每个表只有一个primary key

Alter Table table_name Add constraint pk_name Primary Key (column_name1，column_name2)

Alter Table table_name Drop constraint pk_name

(4). Foreign key

Alter Table table_name1 Add constraint fk_name Foreign Key(column_name1) References table_name2(column_name2)

Alter Table table_name Drop constraint fk_name

(5). Check 约束列中值的取值范围

Alter Table table_name Add constraint chk_name Check (column_name1 > 3 and column_name2 like '%abc%')

Alter Table table_name Drop constraint chk_name

(6). Default 设置默认值

Alter Table table_name Add constraint df_name default 'dafult_value' for column_name

Alter Table table_name Drop constraint df_name

查询表是否有default 约束

Select * from sys.default_constraints Where sys.default_constraints.parent_object_id=OBJECT_ID('table_name')

查看表中的约束

EXEC sp_helpconstraint 'table_name'