数据库知识点04

第8节. 集合运算符

A. SQL Server Union(并集)

SQL Server UNION 是一组集合操作，用于将两个SELECT语句的结果组合到一个结果集中，该结果集包括属于 union 中 SELECT 语句的所有行。

select distinct first_name,last_name from sales.customers

union

select first_name,last_name from sales.staffs

union

select '张','san'

order by first_name

select  first_name,last_name from sales.customers

union all

select last_name,first_name from sales.staffs

union all

select '张','san'

order by first_name

B. SQL Server Intersect(交集)

SQL Server INTERSECT 组合了两个或多个查询的结果集，并返回两个查询输出的不同行。

--1 4 5 6 7

select distinct category_id

from production.products

where brand_id = 9

intersect

--4 6 7

select distinct category_id

from production.products

where brand_id = 8

C. SQL Server Except(差积)

SQL Server EXCEPT 比较两个查询的结果集，并返回第一个查询中不是由第二个查询输出的不同行。 换句话说， EXCEPT 从一个查询结果中减去另一个查询的结果集。

-- 1 4 5 6 7

select distinct  category_id

from production.products

where brand_id = 9

except

--4 6 7

select distinct  category_id

from production.products

where brand_id = 8

1. 修改数据

创建表：

 ---------------------------------------

create table sales.promotions(

    promotion_id int primary key identity(1,1),

promotion_name varchar (255) not null,

discount numeric (3,2) default 0,

start_date date not null,

expired_date date not null

);

---------------------------------------

1. INSERT语句

INSERT 语句向表中添加新行

基本INSERT

insert into sales.promotions(promotion_name,discount,start_date,expired_date)

values ('lili',0.5,'2022-01-01','2022-12-12')

select * from sales.promotions

将显式值插入标识列

--当 IDENTITY_INSERT 设置为 OFF 时，不能为表 'promotions' 中的标识列插入显式值。

set IDENTITY_INSERT  sales.promotions on;

insert into sales.promotions(promotion_id, promotion_name,discount,start_date,expired_date)

values (4,'list1',0.12,'2022-01-12','2022-12-12')

set IDENTITY_INSERT  sales.promotions off;

insert into sales.promotions(promotion_name,discount,start_date,expired_date)

values ('lili',0.5,'2022-01-01','2022-12-12')

Insert多行（添加多行数据）

insert into sales.promotions

(promotion_name,discount,start_date,expired_date)

values ('lissan2',0.22,'2022-01-01','2022-12-12')

,('lissan3',0.23,'2022-01-02','2022-12-13');

B. INSERT INTO SELECT语句

创建表：

---------------------------------------

CREATE TABLE sales.addresses (    

    address_id INT IDENTITY PRIMARY KEY,    

street VARCHAR (255) NOT NULL,    

city VARCHAR (50),    

state VARCHAR (25),    

zip_code VARCHAR (5) );

---------------------------------------

将表的所有行插入另一张表

delete from sales.addresses;

select * from sales.addresses;

select * from sales.customers;

insert into sales.addresses

--(street,city,state,zip_code)

select street,city,state,zip_code

from sales.customers ;

C. UPDATE语句

创建表：

---------------------------------------

CREATE TABLE sales.taxes (    

    tax_id INT PRIMARY KEY IDENTITY (1, 1),    

    state VARCHAR (50) NOT NULL UNIQUE,    

state_tax_rate DEC(3, 2),    

avg_local_tax_rate DEC(3, 2),    

combined_rate AS state_tax_rate + avg_local_tax_rate,    

max_local_tax_rate DEC (3, 2),    

updated_at datetime );

---------------------------------------

select * from sales.taxes

update sales.taxes

set updated_at = '2022-03-10'

1. 更新所有行

--getdate() 当前时间

update sales.taxes

set updated_at =getdate();

select * from sales.taxes

where state = 'Alabama'

2. 更新多列示例

update sales.taxes

set max_local_tax_rate = max_local_tax_rate+0.01

,avg_local_tax_rate = avg_local_tax_rate +0.01

where state = 'Alabama';

update sales.taxes

set max_local_tax_rate += 0.01

,avg_local_tax_rate +=0.01

where state = 'Alabama'

D. DELETE语句

Delete 删除表中的现有数据

删除 sales.taxes 表中 state（州） 为 ‘Alabama’的数据

delete from sales.taxes where state = 'Alabama'

删除表中所有数据

delete from sales.taxes ;

select * from sales.category t ;

select * from sales.category_staging t ;

)

第10节. 数据定义

A. SQL Server创建表

create table table01(

clo1 int,

clo2 varchar(10),

clo3 nvarchar(10),

clo4 date,

clo5 numeric(10,2)

)

select * from table01

--SQL Server CREATE TABLE示例

CREATE TABLE sales.visits ( 

    visit_id INT PRIMARY KEY IDENTITY (1, 1), 

first_name VARCHAR (50) NOT NULL, 

last_name VARCHAR (50) NOT NULL, 

visited_at DATETIME, 

phone VARCHAR(20), 

store_id INT NOT NULL, 

FOREIGN KEY (store_id) REFERENCES sales.stores (store_id) 

);

在上面示例，sales.visits 表名和所处模式，FOREIGN KEY设置表的主键。

insert into sales.visits (

first_name,last_name,visited_at,phone,store_id)

values('li','si','2022-03-15','123243',1)

 A. SQL Server复制表 

 --SQL Server中，如果目标表存在：

 insert into 目标表 [column] select * from 原表;

 --SQL Server中,，如果目标表不存在：

 select * into 目标表 from 原表;

 select * into sales.stores_2 from sales.stores;

B. SQL Server标识列

delete from sales.visits;

truncate table sales.visits;

insert into sales.visits (

first_name,last_name,visited_at,phone,store_id)

values('li','si','2022-03-15','123243',10)

DBCC CHECKIDENT ('表名', RESEED, new_value)(重置新的标识值，new_value为新值)

select * from sales.visits

select IDENT_CURRENT('sales.visits')

select @@IDENTITY

DBCC CHECKIDENT('sales.visits')

DBCC CHECKIDENT('sales.visits',reseed,@@IDENTITY)

C. SQL Server向表添加列

create table table02(

clo1 int,

clo2 varchar(10),

clo3 nvarchar(10),

clo4 date,

clo5 numeric(10,2)

)

select * from table02

--添加列

alter table table02 add clo6 nvarcher(10) not null;

--修改列数据类型

alter table table02 alter column clo6 nvarcher(100) not null;

--删除列

alter table table02 alter column clo6;

D. SQL Server修改表的列 

CREATE TABLE sales.visits ( 

    visit_id INT PRIMARY KEY IDENTITY (1, 1), 

first_name VARCHAR (50) NOT NULL, 

last_name VARCHAR (50) NOT NULL, 

visited_at DATETIME, 

phone VARCHAR(20), 

store_id INT NOT NULL, 

FOREIGN KEY (store_id) REFERENCES sales.stores (store_id) 

);

查询数据 

select * from sales.visits

修改列数据类型

alter table sales.visits alter column first_name varchar(50) not null;

alter table sales.visits alter column phone varchar(10) not null;

E. SQL Server删除表

1. 删除不存在的表

--if exists  是否存在

drop table if exists sales.aaa;

2. 删除单个表示例

创建表：

CREATE TABLE sales.delivery ( 

    delivery_id INT PRIMARY KEY, 

delivery_note VARCHAR (255) NOT NULL, 

delivery_date DATE NOT NULL 

);

删除表：

DROP TABLE sales.delivery;

3. 删除具有外键约束的表

CREATE SCHEMA procurment; 

GO

CREATE TABLE procurment.supplier_groups ( 

    group_id INT IDENTITY PRIMARY KEY, 

group_name VARCHAR (50) NOT NULL 

);

CREATE TABLE procurment.suppliers ( 

    supplier_id INT IDENTITY PRIMARY KEY, 

    supplier_name VARCHAR (50) NOT NULL, 

    group_id INT NOT NULL, 

    FOREIGN KEY (group_id) REFERENCES procurement.supplier_groups (group_id) 

);

--SQL Server不允许删除外部约束引用的表。 要删除此表，必须先删除引用外键约束或引用表。

--在这种情况下，必须先删除 supplier 表或 supplier 表中的外键约束，然后再删除 supplier_groups

--表。

DROP TABLE procurement.supplier_groups; 

DROP TABLE procurement.suppliers;

--如果使用单个 DROP TABLE 语句删除两个表，则必须将引用表放在前面，如下面的查询所示：

DROP TABLE procurement.suppliers, procurement.supplier_groups;

F. SQL Server截断表

TRUNCATE TABLE 类似于没有 WHERE 子句的 DELETE 语句。 但是， TRUNCATE 语句执行得更快，并且使用的系统和事务日志资源更少。