SQL提高篇（一）

（1）创建数据库

CREATE DATABASE 数据库名称

（2）创建表

CREATE TABLE 表名称
(
列名称1 数据类型,
列名称2 数据类型,
.......
)
例如：
CREATE TABLE Person 
(
LastName varchar(30),
FirstName varchar,
Address varchar,
Age int(3)
)

（3）修改表

ALTER TABLE table_name  语法

如增加一列column_name，数据类型为datatype

ALTER TABLE table_name ADD column_name datatype

（4）备份表
下面语句的意思是将表storehouse 备份，备份的表名字为storehouse_bf

select * into storehouse_bf  from storehouse 

（5）临时表
①创建临时表

select * into #临时表名 from 你的表， --用于将现有的表复制形成一个临时表
select * into ##临时表名 from 你的表
create table #a    --用于创建一个空的临时表   
(
id int,
name varchar(50)
)

②查询临时表

select * from #临时表名
select * from ##临时表名

③删除临时表

drop table #临时表名
drop table ##临时表名

注：
（a）以上的#代表局部临时表，##代表全局临时表
（b）临时表在你每次你断开数据库连接时或是关闭当前数据库对话时会自动删除，但是还是建议是使用完临时表后，加一个drop语句进行删除，局部临时表当前用户退出后自动删除，全局临时表要所有人使用的用户退出后才删除
（c）临时表可以任意修改且不会影响原始表数据，使用临时表就可以做很多修改来达到自己的查询、对比等目的，又不影响原始表结构
（d）很频繁的删除创建临时表会占用大量的系统表资源、内存等，一定程度上会影响性能

（6）group by分组与having
①group by必须配合聚合函数来使用，单独的group by没有意义,having则是对聚合函数的值进行筛选，相当于where条件，只不过是用于聚合函数。

select location_id,s_id,p_id,sum(quantity) as 数量,sum(costtotal) as 总价   
from storehouse  group by location_id,s_id,p_id

这个语句根据group by后面的字段进行合并，完全一样的合并，不一样的不合并，其余不在group by 后面的字段，就好像统统放入一个隐藏的盒子里面，如要显示可以用聚合函数进行计算，以聚合函数的值作为新的字段进行显示。不显示就不用管

例如:

②count
很多时候我们采用group by 加count函数来进行筛重，count（*）就是针对group by 里的字段进行统计，统计出现的次数，最后我们用having对count()进行判断，从而得出重复的数据

③合并一张表里的重复行
a.将表备份
b.利用group by 将一样的字段合并，用sum（）将需要叠加的项加起来，用max()和min()函数取某些项的极值，然后插入一张新表A中，插入前对比查询出的数据与老表结果是否一致，比如汇总项最后的值是否一样等，保证前后不得改变数值
c.将老表的数据用truncate清空
d.将A中的数据插回老表中
e.将表A删除
例如：

```sql
--1 
select* into storehouse_2020_06-30 from storehouse 
--2
select sum(quantity) quantity,sum(costtotal) costtotal,sum(costtaxtotal)costtaxtotal ,max(yhdate) yhdate,max(inorder) inorder,
instoretime,location_id,s_id,p_id,supplier_id,costprice,batchno,makedate,validdate,y_id,costtaxprice,factoryid ,taxrate,
AgreedCostPrice, SteBatchNo, SteValidDate
into bss 
from storehouse  
group by instoretime,location_id,s_id,p_id,supplier_id,costprice,batchno,makedate,validdate,y_id,costtaxprice,factoryid ,taxrate,
AgreedCostPrice, SteBatchNo, SteValidDate
--3.
truncate table storehouse
--4.
insert storehouse(quantity,costtotal,costtaxtotal,yhdate,inorder,
instoretime,location_id,s_id,p_id,supplier_id,costprice,batchno,makedate,validdate,y_id,costtaxprice,factoryid ,taxrate,
AgreedCostPrice, SteBatchNo, SteValidDate ) 
select instoretime,location_id,s_id,p_id,supplier_id,costprice,batchno,makedate,validdate,y_id,costtaxprice,factoryid ,taxrate,
AgreedCostPrice, SteBatchNo, SteValidDate 
from bss
--5
drop table bss

**（7）跨数据库操作与跨表操作**
①当前数据库操作其它数据库的表数据

```sql
use 数据库名字  --这个语句执行以后将会跳转到对应的数据库

②跨数据库的插入

insert into 目标数据库名字…table(sysname,sysvalue,comment,tagNO,sysflag,Y_id)
select sysname,sysvalue,comment,tagNO,sysflag,Y_id from 数据来源数据库名字…table

③将表A的某一列的值复制到表B的某一列

update P set P.TC2 = p1.price2
from products P inner join Price p1 on p.product_id = p1.p_id
and p.unit1_id = p1.u_id and p1.unittype = 1

（8）约束
①SQL NOT NULL 约束
NOT NULL 约束强制列不接受 NULL 值。
NOT NULL 约束强制字段始终包含值。这意味着，如果不向字段添加值，就无法插入新记录或者更新记录。

CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

②SQL PRIMARY KEY 约束（主键约束）
PRIMARY KEY 约束唯一标识数据库表中的每条记录。
（a）主键必须包含唯一的值。
（b）主键列不能包含 NULL 值。
（c）每个表都应该有一个主键，并且每个表只能有一个主键。

MySQL写法:

CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (Id_P)
)

SQL Server / Oracle / MS Access写法:

CREATE TABLE Persons
(
Id_P int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

③SQL UNIQUE 约束
UNIQUE 约束唯一标识数据库表中的每条记录。
UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。
PRIMARY KEY 拥有自动定义的 UNIQUE 约束。
请注意，每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。
MySQL写法:

CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
UNIQUE (Id_P)
)

SQL Server / Oracle / MS Access写法:

CREATE TABLE Persons
(
Id_P int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

④SQL FOREIGN KEY 约束
一个表中的 FOREIGN KEY 指向另一个表中的 PRIMARY KEY。
FOREIGN KEY 约束用于预防破坏表之间连接的动作。
FOREIGN KEY 约束也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。
MySQL写法:

CREATE TABLE Orders
(
Id_O int NOT NULL,
OrderNo int NOT NULL,
Id_P int,
PRIMARY KEY (Id_O),
FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)
)

SQL Server / Oracle / MS Access写法:

CREATE TABLE Orders
(
Id_O int NOT NULL PRIMARY KEY,
OrderNo int NOT NULL,
Id_P int FOREIGN KEY REFERENCES Persons(Id_P)
)

（9）CREATE INDEX 语句用于在表中创建索引。
在不读取整个表的情况下，索引使数据库应用程序可以更快地查找数据。
①CREATE INDEX 实例
本例会创建一个简单的索引，名为 “PersonIndex”，在 Person 表的 LastName 列：

CREATE INDEX PersonIndex
ON Person (LastName) 

假如您希望索引不止一个列，您可以在括号中列出这些列的名称，用逗号隔开：

CREATE INDEX PersonIndex
ON Person (LastName, FirstName)

②SQL CREATE UNIQUE INDEX 语法
在表上创建一个唯一的索引。唯一的索引意味着两个行不能拥有相同的索引值。

CREATE UNIQUE INDEX index_name
ON table_name (column_name)