|
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、数据类型
|