sql基本操作实例(一)创建数据库和表，并填入数据

1.创建数据库

create database pratice

2.创建表

use pratice;
create table Student(Sno char(3) not null primary key,Sname char(8) not null,Ssex char(2) not null,Sbirthday datetime,Class char(5));
create table Teacher(Tno char(3) not null primary key,Tname char(4) not null,Tsex char(2) not null,Tbirthday datetime,Prof char(6),Depart Varchar(10) not null);
create table Course(Cno char(5) not null primary key,Cname Varchar(10) not null,Tno char(3) not null, foreign key (Tno) References Teacher(Tno) on delete cascade);
create table Score(Sno char(3) not null,Cno char(5) not null,Degree Decimal(4,1),foreign key(Sno) references Student(Sno) on delete cascade,foreign key(Cno) references Course(Cno) on delete cascade,primary key (Sno,Cno));

要注意的是各表的顺序，被引用的表一定要先出现

3、存储数据

先填表一的数据

Insert Into Student values ('108','曾华','男','1977-09-01','95033');
insert into Student values ('105','匡明','男','1975-10-02','95031');
insert into Student values ('107','王丽','女','1976-01-23','95033');
insert into Student values ('101','李军','男','1976-02-20','95033');
insert into Student values ('109','王芳','女','1975-02-10','95031');
insert into Student values ('103','陆君','男','1974-06-03','95031');

再填表四。

insert into Teacher values('804','李诚','男','1958-12-02','教授','计算机系');
insert into Teacher values('856','张旭','男','1969-03-12','讲师','电子工程系');
insert into Teacher values('825','王萍','女','1972-05-05','助教','计算机系');
insert into Teacher values('831','刘冰','女','1977-08-14','助教','电子工程系');

再对表二进行赋值
;

insert into course values('3-105','计算机导论','825');
insert into course values('3-214','操作系统','804');
insert into course values('6-166','数字电路','856');
insert into course values('9-888','高等数学','831');

最后对表三进行赋值

insert into Score values('103','3-245','86');
insert into Score values('105','3-245','75');
insert into Score values('109','3-245','68');
insert into Score values('103','3-105','92');
insert into Score values('105','3-105','88');
insert into Score values('109','3-105','76');
insert into Score values('101','3-105','64');
insert into Score values('107','3-105','91');
insert into Score values('108','3-105','78');
insert into Score values('101','6-166','85');
insert into Score values('107','6-166','79');
insert into Score values('108','6-166','81');

同样是和定义一样，被引用的表要先赋值

4、DDL

上面使用到的语言是数据库定义语言DDL，包括Create Database,Create Table,Alter Database,Alter Table,drop table,drop database以及Create Index和Drop Index。
这里drop table,drop database用来删除表或者数据库，一键即可实现删除功能，不需要讲述。
主要要讲的是create语句相关的知识
(1)Create Database
create database DB_name

(2)Create Table
create table tablename (列表名 数据类型 Col_constr列约束,列表名2 数据类型 列约束,…,
table_constr表约束)

其中col_constr有：
not null,unique(列值唯一),Primary key,check(fcon)//条件只能含当前列值,
references tablename [(colname) [on delete {cascade//一并删除|set null //关联列删除后取null}]]
这些可以取一个或多个，相互之间用空格隔开。还可以在列约束前取名，这样方便以后删除，形式如下:
constraint name

table constr有
[constraint constraintname] //命名
{unique (colname[,colname2…])
| Primary key (colname[,colname2…])
| check (search_cond)
|Foreign key ( (colname[,colname2…]))
References tablename [ (colname[,colname2…])]//如果不加colname则默认为主码 [on delete cascade]
}

(3)Alter Database
一般用于改名

(4)Alter table
Alter table tablename
[ADD (colname datatype,…)]//加新列
[drop Column colname]//删除列
[alter Column colname datatype]//改变列的数据类型
[add constraint constraint_name]
[drop constraint constraintname]
[drop primary_key]

5、DML中的insert语句

insert into tablename [(col1,col2,…)]
values (, , , , , ,)
如果不写列名则按照表的列排列来填，若写则按照写的来。

6、数据类型