创建数据库常用SQL语句

/*
1、新建数据库 DataBaseName
2、执行下面的sql语句添加表
3、执行下面的sql语句添加数据
3、给主键添加自增类型
5、添加主键
4、添加外键关系
*/

use DataBaseName;
/*删除已创建的外键和索引：先执行下面这句sql，复杂执行结果，执行，在执行下面的sql
select 'IF EXISTS(Select 1 from sys.foreign_keys Where name= '''+fk.name+''') ALTER TABLE ' + o.name + ' DROP CONSTRAINT ' + fk.name + ';'  AS Command
from sys.foreign_keys fk  JOIN sys.all_objects o ON (fk.parent_object_id = o.object_id);

select 'IF EXISTS (Select 1 from sys.indexes Where name= '''+ix.name+''') DROP INDEX '+o.name+'.' + ix.name + ';'  AS Command
from sys.indexes ix  JOIN sys.all_objects o ON (ix.object_id = o.object_id) where ix.name like 'IX_%';
*/

IF EXISTS(Select 1 From Sysobjects Where Name='TableName')
DROP table TableName;
CREATE TABLE TableName (
	id 			int IDENTITY(1,1) NOT NULL,
	title 	 		varchar (30) NULL ,
	summary varchar (MAX) NULL ,	
	remark 	 text NULL ,	
	money 	numeric(18,2) NULL ,
	state 		int NULL ,
	user_id 	varchar (60) NULL ,
	sort_no	varchar (60) NULL ,
    upd_time	datetime,
    add_time	datetime
); 
 /*INSERT INTO 当插入自增主键时，需要开启和关闭*/
set identity_insert TableName on;
INSERT INTO TableName (id,title,summary,remark,money,state,user_id) VALUES (1,'标题','简介','备注','99.90',1,'xiaoming123');
set identity_insert TableName off;

/*增加主键*/
ALTER TABLE TableName ADD CONSTRAINT PK_TableName PRIMARY KEY (id); 

/*
DROP DATABASE 数据库名;  
DROP TABLE 表名称;           
TRUNCATE TABLE表名称;                          
DELETE FROM 表名称 WHERE列名称 = 值 ; 

ALTER TABLE tb ADD CONSTRAINT PK_id PRIMARY KEY (id);
ALTER TABLE tb DROP CONSTRAINT PK_id;
ALTER TABLE tb1 ADD CONSTRAINT FK_tb1_tb2 FOREIGN KEY(tb1_id) REFERENCES tb2(tb2_id); 
ALTER TABLE tb ADD 列名 类型 null;
ALTER TABLE tb ALTER COLUMN 列名 类型 not null;
CREATE NONCLUSTERED INDEX IX_索引名 ON 表名(索引列名);  
[创建索引](https://blog.youkuaiyun.com/hanxin0311/article/details/116144697)
*/

/*创建视图*/
IF EXISTS(SELECT 1 FROM sys.views WHERE name='view_TableName') DROP VIEW view_TableName
GO
CREATE VIEW view_TableName AS
SELECT CONVERT(varchar(100), add_time, 23) + '-' + CONVERT(varchar(32), user_id) AS id，CONVERT(varchar(100), add_time, 23) as add_time ,sum(money) as total
FROM TableName
GROUP BY  CONVERT(varchar(100), add_time, 23) ,user_id
GO