1. 创建数据库
CREATE DATEBASE database-name;
2. 删除数据库
DROP DATABASE dbname
3. 备份数据库
USE master
EXEC sp_addumpdevice 'disk','testBack','d:\test.dat';
BACKUP DATABSE test TO testBack;
4. 修改数据库名称
sp_renamedb 'old_name','new_name';
5. 查看当前数据库所有存储过程
SELECT name AS pname FROM sysobjects WHERE xtype='P';
6. 创建新表
CREATE TABLE tablename(
col1 type1 [not null] [PRIMARY KEY],
col2 type2 [not null],
...
);
7. 根据已有的表创建新表
CREATE TABLE tab_new LIKE tab_old;
或者
CREATE TABLE table_new AS SELECT col1,col2,... FROM tab_old definition only;
8. 添加表注释
execute sp_addextendedproperty 'MS_Description','表注释','user','dbo','table','表名',null,null;
9. 查询一个表的字段和数据类型
SELECT column_name,data_type FROM information_schema,columns WHERE table_name='tablename';
10. 复制数据表结构和数据。
SELECT * INTO b FROM a;
11. 复制表结构
SELECT * INTO b FROM a WHERE 1<>1;
或者
SELECT TOP 0 * INTO b FROM a;
12. 拷贝数据
INSERT INTO b(col1,col2,col3) select col4,col5,col6 FROM a;
13. 修改表名
sp_rename 'newname','oldname';
14. 删除新表
DROP TABLE tabname;
15. 初始化数据表
TRUNCATE TABLE tablename;
16. 查看与某一个表相关的视图、存储过程、函数
SELECT a.* FROM sysobjects a,syscomments b WHERE a.id=b.id AND b.text LIKE '%tablename%';
17. 增加一个字段
ALTER TABLE tablename ADD COLUMN col type;
18. 添加字段注释
execute sp_addextendedproperty 'MS_Description','字段注释','user','dbo','table','表名,'column','字段';
19. 修改一个字段
ALTER TABLE tablename ALTER COLUMN col type;
20. 修改字段名称
exec sp_rename 'tablename.columnname','new_columnname';
21. 修改字段注释
execute sp_updateextendedproperty 'MS_Description','新的字段注释','user','dbo','table','表名','column','字段名';
22. 修改字段默认值
ALTER TABLE 表名 ADD CONSTRAINT 新的约束标识名 DEFAULT(新的默认值) FOR 字段;
23. 删除一个字段
ALTER TABLE tablename DROP COLUMN col;
24. 删除约束
ALTER TABLE 表名 DROP CONSTRAINT 约束标识名;
25. 增加主键
ALTER TABLE tablename ADD PRIMARY KEY(col);
26. 删除主键
ALTER TABLE tablename DROP PRIMARY KEY(col);
27. 创建索引
CREATE [unique] index idxname ON tablename(col,...);
28. 删除索引
DROP index idexname;
29. 创建视图
CREATE VIEW viewname AS SELECT statement;
30. 删除视图
DROP VIEW viewname;
31. 查、增、改、删
SELECT col1,col2,... FROM tablename WHERE condition;
INSERT INTO tablename(field1,field2,...) VALUES(value1,value2,...);
DELETE FROM tablename WHERE condition;
UPDATE tablename SET field1=value1 WHERE condition;
32. 子查询
SELECT col1,col2,col3 FROM a WHERE col1 IN (SELECT col4 FROM b);
33. 在线视图查询
SELECT * FROM (SELECT col1,col2,col3 FROM a) t WHERE t.a>1;
34. 删除副表中已经在主表中没有的信息。
DELETE FROM table2 WHERE NOT EXISTS (SELECT * FROM table1 WHERE table1.field1=table2.field1);
35. 查询前10条数据
SELECT TOP 10 * FROM tablename WHERE condition;
36. 随机取出10条数据
SELECT TOP 10 * FROM tablename ORDER BY newid();
37. 给int存储类型字段createtime添加默认值为添加数据时当前时间戳(之前没有设置默认值)。
ALTER TABLE wxxcx_xxk_user ADD DEFAULT DATEDIFF(second,'1970-01-01 08:00:00',GETDATE()) FOR createtime;
38. 查询数据表中某字段重复的记录
SELECT * FROM 表名 WHERE 字段名 IN (SELECT [字段名] FROM 表名 GROUP BY 字段名 HAVING COUNT(*)>1);
39. 删除掉同名数据中所有大的id(无效)的数据
DELETE FROM 表名 WHERE EXISTS(SELECT * FROM (SELECT MIN(id) id,字段名 FROM 表名 GROUP BY 字段名 HAVING(count(1)>1)) AS tt WHERE users.id>tt.id AND 表名.字段名=tt.字段名);
40. 创建外键约束同时创建级联修改、级联删除
ALTER TABLE
userInfo --从表名
WITH CHECK ADD CONSTRAINT
[FK_USERINFO_USERGROUP] FOREIGN KEY
(department)--从表需要进行联级更新的列名
REFERENCES departmentInfo (department) --主表(列名)
ON UPDATE CASCADE
ON DELETE CASCADE