下面是自己回顾总结的关于常用数据库MS SQL Server, MySql, MS Access, Oracle的相关SQL语句。
下面的语句主要是针对在数据库创建时期,包括表创建,表属性修改,表约束(主键,外键,值约束),以及视图view的创建等SQL语句。基本都是在数据库设计初期会用到的一些基本的create,alter,drop语句。
-- 以下的SQL语句适合的数据库主要有SQL Server,Oracle,MS Access,MySql
-- 简写说明:
-- SS:SQL Server,O:Oracle,MA:MS Access,MS:MySQL
-- 非标注的为通用用法,标注的为特定数据的用法
-- create可能涉及的用法
CREATE DATABASE my_db;
CREATE TABLE Persons
(
-- 自增用法:AUTO_INCREMENT for MS; IDENTITY(1,1) for SS; AUTOINCREMENT(10,5) for MA
P_Id int NOT NULL PRIMARY KEY CHECK (P_Id>0), -- for SS, O, MA
Parent_Id int NOT NULL KEY REFERENCES Persons(P_Id), -- for SS, O, MA
LastName varchar(255) NOT NULL DEFAULT 'Sandnes',
FirstName varchar(255),
Address varchar(255),
City varchar(255),
Birthday date DEFAULT GETDATE(),
PRIMARY KEY (P_Id) -- for MS
FOREIGN KEY (Parent_Id) REFERENCES Persons(P_Id) -- for MS
-- 命名 PRIMARY KEY 约束,并定义多个列的 PRIMARY KEY 约束
CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
-- 命名 FOREIGN KEY 约束,并定义多个列的 FOREIGN KEY 约束
CONSTRAINT fk_ParentID FOREIGN KEY (Parent_Id) REFERENCES Persons(P_Id)
ON DELETE CASCADE --有外键约束时,设置父子级联删除
CHECK (P_Id>0) -- for MS
-- 命名 CHECK 约束,并定义多个列的 CHECK 约束
CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')
);
DROP DATABASE database_name;
DROP TABLE Persons;
-- 清空表中数据
TRUNCATE TABLE table_name;
CREATE SEQUENCE seq_person MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 10; -- for O
INSERT INTO Persons (ID,FirstName,LastName) VALUES (seq_person.nextval,'Lars','Monsen'); -- for O
CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition;
ALTER TABLE Persons ADD column_name int;
ALTER TABLE Persons DROP COLUMN column_name;
ALTER TABLE Persons ALTER COLUMN column_name int; -- for SS, MA
ALTER TABLE Persons MODIFY COLUMN column_name int; -- for MS, O
ALTER TABLE Persons MODIFY column_name int; -- for O (10G之后)
ALTER TABLE Persons ADD PRIMARY KEY (P_Id);
ALTER TABLE Persons ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName);
ALTER TABLE Persons DROP CONSTRAINT pk_PersonID; -- for SS, O, MA
ALTER TABLE Persons DROP PRIMARY KEY; -- for MS
ALTER TABLE Persons ADD FOREIGN KEY (P_Id) REFERENCES Persons(P_Id) ON DELETE CASCADE;
ALTER TABLE Persons ADD CONSTRAINT fk_ParentID FOREIGN KEY (P_Id) REFERENCES Persons(P_Id) ON DELETE CASCADE;
ALTER TABLE Persons DROP CONSTRAINT fk_ParentID; -- for SS, O, MA
ALTER TABLE Persons DROP FOREIGN KEY fk_ParentID; -- for MS
ALTER TABLE Persons ADD CHECK (P_Id>0);
ALTER TABLE Persons ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes');
ALTER TABLE Persons DROP CONSTRAINT chk_Person; -- for SS, O, MA
ALTER TABLE Persons DROP CHECK chk_Person; -- for MS
ALTER TABLE Persons ADD CONSTRAINT ab_c DEFAULT 'SANDNES' for City; -- for SS, MA
ALTER TABLE Persons MODIFY City DEFAULT 'SANDNES'; -- for o
ALTER TABLE Persons ALTER City SET DEFAULT 'SANDNES'; -- for MS
ALTER TABLE Persons ALTER COLUMN City DROP DEFAULT; -- for SS, O, MA
ALTER TABLE Persons ALTER City DROP DEFAULT; -- for MS
-- 创建索引的语法在不同的数据库中不一样。因此,检查您的数据库中创建索引的语法
CREATE [UNIQUE] INDEX PIndex ON Persons (LastName, FirstName);
DROP INDEX PIndex ON Persons; -- for MA
DROP INDEX Persons.PIndex; -- for SS
DROP INDEX PIndex; -- for DB2/O
ALTER TABLE Persons DROP INDEX PIndex; -- for MS
以上内容如有问题,还请留言指出,多谢!
突然发现关于数据库设计的语句,还少了触发器,存储过程没有写!