SQL表处理详解
-----------------------------------------------------------------------------
建表
CREATE TABLE
[ database_name.[ owner ] .| owner.]
table_name
( { < column_definition >| column_name AS computed_column_expression|< table_constraint >} [,…n])
[ ON { filegroup | DEFAULT } ]
[ TEXTIMAGE_ON { filegroup | DEFAULT } ]
< column_definition > ::= { column_name data_type }
[ COLLATE < collation_name > ]
[ [ DEFAULT constant_expression ]
| [ IDENTITY [ ( seed , increment ) [ NOT FOR REPLICATION ] ] ] ]
[ ROWGUIDCOL]
[ < column_constraint > ] [ ...n ]
< column_constraint > ::=[ CONSTRAINT constraint_name ]
{ [ NULL | NOT NULL ]
| [ { PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
[ WITH FILLFACTOR = fillfactor ]
[ON {filegroup | DEFAULT} ] ] }
|FOREIGN KEY [(column [,…n])]
REFERENCES ref_table [ ( ref_column [,…n]) ] [NOT FOR REPLICATION]
| CHECK [ NOT FOR REPLICATION ]
( logical_expression )}
#如果觉得上面的太复杂就来看一些例子吧
例1:在GZGL数据库中创建一个JBXX的数据库表
use GZGL
create table jbxx(
employee_id char(6) not null primary key,
name char(10) not null,
birthday datetime not null,
sex char(2) default '男'
constraint uk_name unique(employee_id)
constraint chk_sex check(sex in ('男','女'))
)
-----------------------------------------------------------------------------
删除表
DROP TABLE table_name
-----------------------------------------------------------------------------
增加、删除和修改表字段
ALTER TABLE table
{ [ ALTER COLUMN column_name
{ new_data_type [ ( precision [ , scale ] ) ]
[ COLLATE < collation_name > ]
[ NULL | NOT NULL ]
| {ADD | DROP } ROWGUIDCOL } ]
ADD
{ [ < column_definition > ]
| column_name AS computed_column_expression
} [ ,...n ]
| [ WITH CHECK | WITH NOCHECK ] ADD
{ < table_constraint > } [ ,...n ]
DROP
{ [ CONSTRAINT ] constraint_name
| COLUMN column } [ ,...n ]
| { CHECK | NOCHECK } CONSTRAINT
{ ALL | constraint_name [ ,...n ] }
| { ENABLE | DISABLE } TRIGGER
{ ALL | trigger_name [ ,...n ] }
}
-----------------------------------------------------------------------------
查看表结构,更改表名
sqlserver中:
exec sp_help [[@objname=]name] --查看表结构,sp_help还可查看其他
exec sp_rename [@objname=]'object_name',[@newname=] 'new_name'[,[@objtype=] 'object_type']
--可更改对象名,objtype可为COLUMN,DATABASE,INDEX,USERDATATYPE,OBJECT。默认OBJECT
mysql中:
desc table_name --查看表结构
alter table table_name rename to new_table_name --更改表名
-----------------------------------------------------------------------------
表数据的进行插入、修改和删除
插入数据
INSERT [INTO]{ table_name| view_name } {
[( column_list )]
VALUES( { DEFAULT | NULL | expression } [ ,...n] )
}
更新数据
UPDATE { table_name | view_name }
[ FROM { < table_source > } [ ,...n ]
SET column_name = { expression | DEFAULT | NULL }[ ,...n ]
[ WHERE search_condition > ]
删除数据
DELETE [ FROM ]
{ table_name WITH ( < table_hint_limited > [ ...n ] ) | view_name }
[ WHERE < search_condition > ]
例1:向数据库gzgl中的表输入数据
use gzgl
insert into jbxx
values('0111112','李子林','1973_5_3','1','310107196206088243','交通路5号',)
例2:将表jbxx中编号为020805的记录的部门号改为003
use gzgl
update jbxx set department_id='003' where employee_id='020805'
例3:将表jbxx中的年龄增加1岁。
use gzgl
update jbxx
set age=age+1
例4:将表jbxx中所有记录的党员属性改为'党员'
use gzgl
update jbxx
set polity='党员' --没有where子句,将修改表中的所有行数据
例5:删除jbxx表中编号为020805的记录。use gzgl
delete from jbxx where employee_id='020805'
--清空整个表,可用delete和truncate(速度更快)
-----------------------------------------------------------------------------
最后,附上一个大点的例子,类似原《开天辟地III》里讲Visual Foxpro里的数据库例子:
#在MySQL测试通过
create database jadge;
use jadge;
create table tproduct(
pid smallint auto_increment primary key,
pname varchar(20) not null,
ptype char(4) not null default '教育',
pprice decimal(4),
pinfo text
);
create table torder(
oid int primary key,
pid smallint,
cid mediumint,
onumber mediumint not null default 1,
odata date,
);
alter table torder add constraint fk_pid foreign key torder(pid) references tproduct(pid) on delete set null;
create table tcustomer(
oid int,
cid mediumint primary key,
cname varchar(10) not null,
ctel char(20),
constraint fk_oid foreign key tcustomer(oid) references torder(oid) on delete cascade
);
alter table tcustomer ( modify cid mediumint first );
alter table tcustomer change ctel ctele char(20);
insert into tproduct (pname,ptype,pprice,pinfo) values('C++ Primer','书籍',43.5,'C++经典');
insert into tproduct (pname,ptype,pprice,pinfo) values('C++沉思录','书籍',43.5,'C++经典——大师的沉思');
-----------------------------------------------------------------------------
建表
CREATE TABLE
[ database_name.[ owner ] .| owner.]
table_name
( { < column_definition >| column_name AS computed_column_expression|< table_constraint >} [,…n])
[ ON { filegroup | DEFAULT } ]
[ TEXTIMAGE_ON { filegroup | DEFAULT } ]
< column_definition > ::= { column_name data_type }
[ COLLATE < collation_name > ]
[ [ DEFAULT constant_expression ]
| [ IDENTITY [ ( seed , increment ) [ NOT FOR REPLICATION ] ] ] ]
[ ROWGUIDCOL]
[ < column_constraint > ] [ ...n ]
< column_constraint > ::=[ CONSTRAINT constraint_name ]
{ [ NULL | NOT NULL ]
| [ { PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
[ WITH FILLFACTOR = fillfactor ]
[ON {filegroup | DEFAULT} ] ] }
|FOREIGN KEY [(column [,…n])]
REFERENCES ref_table [ ( ref_column [,…n]) ] [NOT FOR REPLICATION]
| CHECK [ NOT FOR REPLICATION ]
( logical_expression )}
#如果觉得上面的太复杂就来看一些例子吧
例1:在GZGL数据库中创建一个JBXX的数据库表
use GZGL
create table jbxx(
employee_id char(6) not null primary key,
name char(10) not null,
birthday datetime not null,
sex char(2) default '男'
constraint uk_name unique(employee_id)
constraint chk_sex check(sex in ('男','女'))
)
-----------------------------------------------------------------------------
删除表
DROP TABLE table_name
-----------------------------------------------------------------------------
增加、删除和修改表字段
ALTER TABLE table
{ [ ALTER COLUMN column_name
{ new_data_type [ ( precision [ , scale ] ) ]
[ COLLATE < collation_name > ]
[ NULL | NOT NULL ]
| {ADD | DROP } ROWGUIDCOL } ]
ADD
{ [ < column_definition > ]
| column_name AS computed_column_expression
} [ ,...n ]
| [ WITH CHECK | WITH NOCHECK ] ADD
{ < table_constraint > } [ ,...n ]
DROP
{ [ CONSTRAINT ] constraint_name
| COLUMN column } [ ,...n ]
| { CHECK | NOCHECK } CONSTRAINT
{ ALL | constraint_name [ ,...n ] }
| { ENABLE | DISABLE } TRIGGER
{ ALL | trigger_name [ ,...n ] }
}
-----------------------------------------------------------------------------
查看表结构,更改表名
sqlserver中:
exec sp_help [[@objname=]name] --查看表结构,sp_help还可查看其他
exec sp_rename [@objname=]'object_name',[@newname=] 'new_name'[,[@objtype=] 'object_type']
--可更改对象名,objtype可为COLUMN,DATABASE,INDEX,USERDATATYPE,OBJECT。默认OBJECT
mysql中:
desc table_name --查看表结构
alter table table_name rename to new_table_name --更改表名
-----------------------------------------------------------------------------
表数据的进行插入、修改和删除
插入数据
INSERT [INTO]{ table_name| view_name } {
[( column_list )]
VALUES( { DEFAULT | NULL | expression } [ ,...n] )
}
更新数据
UPDATE { table_name | view_name }
[ FROM { < table_source > } [ ,...n ]
SET column_name = { expression | DEFAULT | NULL }[ ,...n ]
[ WHERE search_condition > ]
删除数据
DELETE [ FROM ]
{ table_name WITH ( < table_hint_limited > [ ...n ] ) | view_name }
[ WHERE < search_condition > ]
例1:向数据库gzgl中的表输入数据
use gzgl
insert into jbxx
values('0111112','李子林','1973_5_3','1','310107196206088243','交通路5号',)
例2:将表jbxx中编号为020805的记录的部门号改为003
use gzgl
update jbxx set department_id='003' where employee_id='020805'
例3:将表jbxx中的年龄增加1岁。
use gzgl
update jbxx
set age=age+1
例4:将表jbxx中所有记录的党员属性改为'党员'
use gzgl
update jbxx
set polity='党员' --没有where子句,将修改表中的所有行数据
例5:删除jbxx表中编号为020805的记录。use gzgl
delete from jbxx where employee_id='020805'
--清空整个表,可用delete和truncate(速度更快)
-----------------------------------------------------------------------------
最后,附上一个大点的例子,类似原《开天辟地III》里讲Visual Foxpro里的数据库例子:
#在MySQL测试通过
create database jadge;
use jadge;
create table tproduct(
pid smallint auto_increment primary key,
pname varchar(20) not null,
ptype char(4) not null default '教育',
pprice decimal(4),
pinfo text
);
create table torder(
oid int primary key,
pid smallint,
cid mediumint,
onumber mediumint not null default 1,
odata date,
);
alter table torder add constraint fk_pid foreign key torder(pid) references tproduct(pid) on delete set null;
create table tcustomer(
oid int,
cid mediumint primary key,
cname varchar(10) not null,
ctel char(20),
constraint fk_oid foreign key tcustomer(oid) references torder(oid) on delete cascade
);
alter table tcustomer ( modify cid mediumint first );
alter table tcustomer change ctel ctele char(20);
insert into tproduct (pname,ptype,pprice,pinfo) values('C++ Primer','书籍',43.5,'C++经典');
insert into tproduct (pname,ptype,pprice,pinfo) values('C++沉思录','书籍',43.5,'C++经典——大师的沉思');