以下只针对SQL Server,其它数据库类型可能不支持某些操作或操作名不同。
SQL语句分类:
①、DCL:用户授权,grant、revoke。
②、DDL:操作表,create、alter、drop、truncate。
③、DML:操作数据:insert、update、delete。
④、查询:select。
⑤、事务控制:commit、rollback、savepoint。
create table 用来创建表。
not null:非空约束
该列值不能为空。
create table person([ID] int not null, --ID列中值不能为空值
[name] varchar(20),
[city] varchar(30));
unique:值唯一约束
该列不能有相同的数值,sql server中空值会作为相同的值来处理,MySQL则不会。
值唯一的列会自动建立索引。
constraint 用来定义有名约束。
create table person([ID] int not null unique,--无名值唯一约束
[name] varchar(20) ,
[city] varchar(30),
);
create table person([ID] int not null constraint uq1_person unique,--有名值唯一约束
[name] varchar(20) constraint uq2_person unique,--有名值唯一约束
[city] varchar(30),
);
create table person([ID] int not null unique,--无名值唯一约束
[name] varchar(20) ,
[city] varchar(30)
constraint person_uk unique(name, city) --有名多列值唯一约束
);
alter table person add unique([name]), unique([city]); //在表已经存在的情况建立无名值唯一约束
alter table person add constraint uq1_person unique([ID]), constraint uq2_person unique([name]);--在表已经存在的情况下创建有名值唯一约束
alter table person drop constraint uq1_person, uq2_person;--撤消值唯一约束
alter table person modify name varchar[255] unique; --对MySQL,更改列的数据类型时增加值唯一约束
alter table person drop index uq1_person, uq2_person; --对于MySQL, 撤消值唯一约束
identity:自增长标识字段
当插入新的数据时,该列的字段会自动填充一个数字,数值默认起始值是1,逐渐递增。create table test_table1([ID] int not null identity,//起始值为1,逐渐递增1
[name] varchar(20),
[city] varchar(30));
create table test_table2([ID] int not null identity(10, 2),//起始值为10,逐渐递增2
[name] varchar(20),
[city] varchar(30));
使用insert插入一条记录的时候,对于identiry标识字段可以不插入数据,使用其默认值,如:
create table person(
[ID] int primary key identity,
[name] varchar(20),
[city] varchar(20)
);
insert person values('张三', '北京');
insert person([name], [city]) values('李四', '上海');
insert person values('王五', '广州');
insert person([name], [city]) values('赵六', '深圳');
select * from person;
输出为:
ID name city
1 张三 北京
2 李四 上海
3 王五 广州
4 赵六 深圳
对于MySQL,使用auto_increment来设置自增长标识字段。
primary key:主键约束
我们把表中的列称为“字段”,也叫做“属性”,一行数据叫做一条“记录”,又称“元组”。存放在表中行列交叉处的数据叫做“值”,它的位置由这个表中的记录和字段来定义。
主键是表里用于表示记录唯一性的字段,故主键的键值不能重复,且不能为空值;主键可以由一个或多个字段组成,表中每一行都由它的主键来唯一标识;每个表都应有一个主键,且表只能有一个主键。
primary key自动拥有unique约束和not null约束,故不能有重复的值和空值。一般为了方便还人工将主键添加identity属性。主键会自动建立索引。
create table person(
[ID] int identity primary key,--无名主键约束
[name] varchar(20),
[city] varchar(30)
);
create table person(
[ID] int identity constraint pk_person primary key,--有名主键约束
[name] varchar(20) not null,
[city] varchar(30)
);
alter table person add constraint pk_person primary key([ID]);--在表已经存在的情况下创建有名主键
alter table person drop constraint pk_person;--撤销主键约
对于MySQL,主键名只能为默认的PRIMARY, 删除主键为:
alter table person drop primary key;
主键可以由多个字段组成,即组合主键:
create table persons(
[ID] int,
[name] varchar(10),
[city] varchar(10),
constraint pk_persons primary key([ID], [name])//有名组合主键
);
一般用数字表示主键,不建议用字符串来表示主键。
主键的值通常都不允许修改,如果会改动主键的值,那么就是主键定义有误。
一般将主键定义成"表名_ID",如persons_ID。
references/foreign key references:外键约束
外键的值来自另一个表里的值唯一字段(经常为另一个表的主键),通常通过设置外键来体现主表和从表之间的一对多关系(如老师—学生),或者一对一关系(如国家—首都,此时的外键列应该为值唯一)。
外键可以有重复的值,也可以为空值,如以下表order_的外键customer_ID来自表customer的主键ID:
需要注意的是外键的值也可以来自本表的主键,这称为“自关联”。
customer
ID name age city
1 张三 25 北京
2 李四 30 上海
3 王五 28 上海
4 赵六 32 广州
5 孙七 30 重庆
order_
order_number time customer_ID
10001 2014-10-10 13:05:56.233 3
10002 2014-10-10 13:20:05.120 3
10003 2014-10-10 13:40:13.653 3
10004 2014-10-10 14:20:05.120 1
一个表中可以有两个或多个外键,所以这个表可以体现与另外两个表之间的关系。
还可以将这两个外键字段设置为这张表的组合主键,如果两个外键组合没有重复值的话。
create table books(
[ID] int not null,
[name] varchar(20),
[city] varchar(30),
[ID_p1] char(3) not null references titles([title_id])--无名外键约束
);
create table books(
[ID] int not null,
[name] varchar(20),
[city] varchar(30),
[ID_p1] char(3) not null,
foreign key([ID_p1]) references titles([title_id])--无名外键约束
);
create table books(
[ID] int not null,
[name] varchar(20),
[city] varchar(30),
[ID_p1] int not null constraint fk1_books foreign key references person1([ID]),--有名外键约束
[ID_p2] int not null constraint fk2_books foreign key references person2([ID])--有名外键约束
);
create table books(
[ID] int not null,
[name] varchar(20),
[city] varchar(30),
[ID_p1] char(3) not null,
constraint fk_books foreign key([ID_p1]) references titles([title_id])--有名外键约束
);
alter table books add constraint fk1_books foreign key([ID_p1]) references person1([ID]),
constraint fk2_books foreign key([ID_p2]) references person2([ID]);
--在表已存在的情况下创建两个有名外键
alter table books drop constraint fk1_books, fk2_books;--撤销外键约束
数据库中有一个数据库关系图选项,右击选择新建数据库关系图,选择要查看的有关的表,就可以得到一张图,这个图里面显示了各个表的外键来自哪个表中的主键,右击表还可以手动向这个表添加和删除约束、索引等。
含有外键的表叫外键(从)表,外键字段来自的那张表叫主键(主)表。
在删除表时如果出错,可能是因为其它表的外键引用了本表中的主键。
对于MySQL,外键约束的列会自动建立索引,而且应该通过foreign key references在所有字段定义之后再指定外键,因为使用references直接在字段定义后来建立外键是无效的,删除外键也不太相同。还可以在建立外键语句之后添加on delete cascade(删除主表记录时,从表记录联动删除)或on delete set null(删除主表记录时,从表记录设为null)。
alter table books drop foreign key fk1_books, fk2_books;--对于MySQL, 撤销外键约束
check():值约束或值范围约束
create table persom(
[ID] int not null constraint ck_persom primary key check ([ID]>0 and [ID]<1000),--无名值范围约束
[name] varchar(20),
[city] varchar(30)
);
create table persom(
[ID] int not null constraint pk_persom primary key constraint ck1_persom check([ID]>0 and [ID]<1000),--有名值范围约束
[name] varchar(20),
[city] varchar(30) constraint ck2_persom check([city]='beijing')--有名值范围约束
);
alter table persom add constraint ck1_persom check([ID]>0 and [city]='beijing'),
constraint ck2_persom check([city]='北京');--在表已经存在的情况下创建值范围约束
alter table persom drop constraint ck1_persom, ck2_persom;--撤销值范围约束
目前MySQL的check约束无效,可以借助触发器来实现。
default:默认值约束
create table person(
[ID] int not null constraint pk_person primary key,
[name] varchar(20),
[time] datetime default getdate()--无名默认值约束
);
create table person(
[ID] int not null constraint pk_person primary key,
[name] varchar(20),
[city] varchar(30) constraint df1_person default 'beijing',--有名默认值约束
[time] datetime constraint df2_person default getdate()--有名默认值约束
);
alter table person add constraint df1_person default 'beijing' for [city],
constraint df2_person default getdate() for [time];--在表已经存在的情况下创建多个有名默认值约束
alter table person drop constraint df1_person, df2_person;--撤销默认值约束
带默认值的字段插入数据示例:
create table person(
[ID] int not null constraint pk_person primary key,
[name] varchar(20),
[city] varchar(30) constraint df1_person default 'beijing',--有名默认值约束
[time] datetime constraint df2_person default getdate()--有名默认值约束
);
insert into person([ID], [name]) values(1001, 'leon');
insert into person([ID], [name], [city]) values(1002, 'leon', 'beijing');
insert into person values(1003, 'leon', 'hangzhou', '2000-01-01 01:01:01');
对于MySQL,约束保存在information_schema数据库下的TABLE_CONSTRAINTS表下,可以查询该表以查看各表的约束。