SELECT * FROM sys.databases WHERE name = 'testdb';
切换数据库
USE testdb
GO
SELECT db_name();
删除数据库
DROP DATABASE testdb;
Schema操作
创建Schema
CREATE SCHEMA sch_demo;
查看Schema
SELECT * FROM sys.schemas AS sch WHERE sch.name = 'sch_demo';
创建Schema下表
CREATE TABLE sch_demo.tb_demo(id int);
SELECT
sch.name AS schema_name,
tb.name AS table_name
FROM sys.tables AS tb
INNER JOIN sys.schemas AS sch
ON tb.schema_id = sch.schema_id
WHERE tb.name = 'tb_demo';
删除Schema
说明 如果Schema下存在表,需要先删除表后,再删除Schema。
DROP TABLE sch_demo.tb_demo;
GO
DROP SCHEMA sch_demo;
GO
表操作
新建表
USE testdb
GO
CREATE TABLE dbo.tb_test(
id int not null IDENTITY(1,1) PRIMARY KEY,
name varchar(50))
GO
查询表
SELECT sche.name AS schema_name, tb.name AS table_name
FROM sys.tables AS tb
INNER JOIN sys.schemas AS sche
ON tb.schema_id = sche.schema_id
WHERE tb.name = 'tb_test';
GO
新增字段
ALTER TABLE dbo.tb_test ADD col_added bigint null;
GO
修改表字段
ALTER TABLE dbo.tb_test ALTER column col_added varchar(50);
GO
删除表字段
ALTER TABLE dbo.tb_test DROP column col_added;
GO
创建索引
CREATE INDEX ix_tb_test_name ON tb_test(name);
GO
删除索引
DROP INDEX ix_tb_test_name ON tb_test;
GO
数据库操作
INSERT
INSERT INTO dbo.tb_test
SELECT 'A' UNION ALL
SELECT 'B';
GO
SELECT
SELECT * FROM dbo.tb_test;
UPDATE
UPDATE TOP(1) dbo.tb_test
SET name = 'A_updated';
GO
DELETE
DELETE TOP(1) FROM dbo.tb_test;
GO
SELECT * FROM dbo.tb_test;
存储过程
创建存储过程
USE testdb
GO
CREATE PROC dbo.UP_getDemoData(
@id int
)
AS
BEGIN
SET NOCOUNT ON
SELECT *
FROM dbo.tb_test
WHERE id = @id
END;
GO
查看存储过程
SELECT *
FROM sys.procedures
WHERE name = 'up_getdemodata';