SQL Server

SQL Server

INSERT INTO

INSERT INTO Websites (name, url, alexa, country)
VALUES ('百度','https://www.baidu.com/','4','CN');

INSERT INTO Websites (name, url, alexa, country)
VALUES ('百度','https://www.baidu.com/','4','CN');

JOIN

A inner join B 取交集。
A left join B 取 A 全部，B 没有对应的值为 null。
A right join B 取 B 全部 A 没有对应的值为 null。
A full outer join B 取并集，彼此没有对应的值为 null。
对应条件在 on 后面填写，eg：
select table1.a, table2.b from **table1 inner join table2 on** table1.a = table2.a

select p.FirstName,p.LastName,a.City,a.State
from
person p left join address a
on
p.personid=a.personid
-- 两个 join（牛客：SQL100 确定最佳顾客的另一种方式（二））
select distinct
    Customers.cust_name,
    sum(OrderItems.item_price * OrderItems.quantity) as total_price
from
    Orders
    inner join OrderItems on Orders.order_num = OrderItems.order_num
    inner join Customers on Orders.cust_id = Customers.cust_id
group by
    Customers.cust_name
having
    sum(OrderItems.item_price * OrderItems.quantity) >= 1000
order by
    total_price

-- union left & right == full outer join 
select
    v.vend_id,
    count(p.prod_id) as prod_id
from
    Vendors v
    full outer join  Products p on p.vend_id = v.vend_id
where v.vend_id is not null
group by
    v.vend_id
order by vend_id
-- == 等价于
select
    v.vend_id,
    count(p.prod_id) as prod_id
from
    Vendors v
    left join Products p on p.vend_id = v.vend_id
group by
    v.vend_id
union
select
    v.vend_id,
    count(p.prod_id) as prod_id
from
    Products p
    right join Vendors v on p.vend_id = v.vend_id
group by
    v.vend_id

order by vend_id

UNION

1，SQL UNION 操作符合并两个或多个 SELECT 语句的结果
2，UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。
3，UNION 操作符去重，如果允许重复的值，用 UNION ALL

SELECT country, name FROM Websites
WHERE country='CN'
UNION ALL
SELECT country, app_name FROM apps
WHERE country='CN'
ORDER BY country;

SELECT * INTO

从一个表复制数据，然后把数据插入到另一个新表中

SELECT Websites.name, access_log.count, access_log.date
INTO WebsitesBackup2016
FROM Websites
LEFT JOIN access_log
ON Websites.id=access_log.site_id;

SELECT *
INTO newtable
FROM table1
WHERE 1=0;

UPDATE

update examination_info set tag= 'Python'
where tag = 'PYTHON'

INSERT INTO SELECT

1，从一个表复制数据，然后把数据插入到一个已存在的表中。目标表中任何已存在的行都不会受影响
2，select into from 和 insert into select 都是用来复制表
两者的主要区别为： select into from 要求目标表不存在，因为在插入时会自动创建；insert into select from 要求目标表存在。

INSERT INTO Websites (name, country)
SELECT app_name, country FROM apps
WHERE id=1;
-- year() ：有自增ID所以要null 或者 select不要查询自增ID
INSERT INTO
    exam_record_before_2021
SELECT
    null,
    uid, 
    exam_id,
    start_time,
    submit_time,
    score
FROM
    exam_record
WHERE
    year (submit_time) < 2021

CREATE DATABASE

CREATE DATABASE max_1;

CREATE TABLE

CREATE TABLE Persons
(
  PersonID int,
  LastName varchar(255),
  FirstName varchar(255),
  Address varchar(255),
  City varchar(255)
);

insert into Persons values (0001, 'LILY', 'JACK','海淀', 'BEIJING')
insert into Persons(PersonID, LastName, FirstName) values (0001, 'MACK', 'YOYO')

表的创建、修改与删除：

• 1.1 直接创建表：

CREATE TABLE
[IF NOT EXISTS] tb_name -- 不存在才创建，存在就跳过
(column_name1 data_type1 -- 列名和类型必选
  [ PRIMARY KEY -- 可选的约束，主键
   | FOREIGN KEY -- 外键，引用其他表的键值
   | AUTO_INCREMENT -- 自增ID
   | COMMENT comment -- 列注释（评论）
   | DEFAULT default_value -- 默认值
   | UNIQUE -- 唯一性约束，不允许两条记录该列值相同
   | NOT NULL -- 该列非空
  ], ...
) [CHARACTER SET charset] -- 字符集编码
[COLLATE collate_value] -- 列排序和比较时的规则（是否区分大小写等）

• 1.2 从另一张表复制表结构创建表： CREATE TABLE tb_name LIKE tb_name_old
• 1.3 从另一张表的查询结果创建表： CREATE TABLE tb_name AS SELECT * FROM tb_name_old WHERE options
• 2.1 修改表：ALTER TABLE 表名 修改选项 。选项集合：

    { ADD COLUMN <列名> <类型>  -- 增加列
     | CHANGE COLUMN <旧列名> <新列名> <新列类型> -- 修改列名或类型
     | ALTER COLUMN <列名> { SET DEFAULT <默认值> | DROP DEFAULT } -- 修改/删除 列的默认值
     | MODIFY COLUMN <列名> <类型> -- 修改列类型
     | DROP COLUMN <列名> -- 删除列
     | RENAME TO <新表名> -- 修改表名  EXEC sp_rename 'exam_record', 'exam_records'
     | CHARACTER SET <字符集名> -- 修改字符集
     | COLLATE <校对规则名> } -- 修改校对规则（比较和排序时用到）

• 3.1 删除表：DROP TABLE [IF EXISTS] 表名1 [ ,表名2]。

SQL 约束（Constraints）

NOT NUL

指示某列不能存储 NULL 值
ALTER TABLE Persons MODIFY Age int NOTNULL; MYSQL可以用modify，sql server不能这样改

ALTER TABLE Persons1 ALTER column Age int NOT NULL;

UNIQUE （唯一值）

保证某列的每行必须有唯一的值

CREATE TABLE Persons
(
P_Id int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL
)

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
City varchar(255),
CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
)

ALTER TABLE Persons
ADD UNIQUE (P_Id)

ALTER TABLE Persons
ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)

-- alter table 表名 add constraint 约束名称 约束类型（列名）
--【约束名称是自己命名的，可以设置为列名+属性，不可重复】

PRIMARY KEY （主键）

NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。

CREATE TABLE Persons
(
P_Id int NOT NULL PRIMARY KEY,
--1，创建表时只设置一个主键
--创建时在 "P_Id" 列上创建 PRIMARY KEY 约束
LastName varchar(255) NOT NULL,
City varchar(255),
CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
--2，创建表时一个主键但是由两列组成
--只有一个主键 PRIMARY KEY（pk_PersonID）。
--pk_PersonID 的值是由两个列（P_Id 和 LastName）组成的
)

s_id int identity(1001,1) primary key not null,

-- 定义单列的 PRIMARY KEY 约束
ALTER TABLE Persons
ADD PRIMARY KEY (P_Id)
-- 定义多个列的 PRIMARY KEY 约束
ALTER TABLE Persons
ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
-- 撤销 PRIMARY KEY 约束
ALTER TABLE Persons
DROP CONSTRAINT pk_PersonID

FOREIGN KEY （外键）

主表要先设置主键，子表才能建立外键
保证一个表中的数据匹配另一个表中的值的参照完整性
预防破坏表之间连接的行为，防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一

CREATE TABLE Orders
(
O_Id int NOT NULL PRIMARY KEY,
OrderNo int NOT NULL,
P_Id int FOREIGN KEY REFERENCES Persons(P_Id)
-- 在 "Orders" 表创建时在 "P_Id" 列上创建 FOREIGN KEY 约束
CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
-- 定义多个列的 FOREIGN KEY 约束
)

-- 定义多个列的 FOREIGN KEY 约束
ALTER TABLE Orders
ADD FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
-- 定义多个列的 FOREIGN KEY 约束：constraint 约束名称 约束类型（列名）
ALTER TABLE Orders
ADD CONSTRAINT fk_PerOrders
FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
-- 撤销 FOREIGN KEY 约束
ALTER TABLE Orders
DROP CONSTRAINT fk_PerOrders

CHECK （约束）

1. 保证列中的值符合指定的条件
2. CHECK 约束用于限制列中的值的范围
3. 如果对单个列定义 CHECK 约束，那么该列只允许特定的值
4. 如果对一个表定义 CHECK 约束，那么此约束会基于行中其他列的值在特定的列中对值进行限制
5. 当数据表中有数据时，增加约束会出现报错“ALTER TABLE 语句与 CHECK 约束冲突”

-- 一个CHECK
CREATE TABLE Persons
(
P_Id int NOT NULL CHECK (P_Id>0),
)
-- 多个CHECK
CREATE TABLE Persons
(
P_Id int NOT NULL,
City varchar(255),
CONSTRAINT chk_Person CHECK (P_Id>0 AND City=