数据库建表

数据库建表

1.建表

CREATE TABLE Student(
	Sno char(7) PRIMARY KEY,
	Sname char(10) NOT NULL,
	Ssex char(2),
	Sage tinyint,
	Sdept varchar(20)
)
CREATE TABLE Course(
	Cno char(6) NOT NULL,
	Cname varchar(20) NOT NULL,
	Credit tinyint,
	Semester tinyint,
	PRIMARY KEY(Cno)
)
CREATE TABLE SC(
	Sno char(7) NOT NULL,
	Cno char(6) NOT NULL,
	Grade tinyint,
	PRIMARY KEY(Sno,Cno),
	FOREIGN KEY(Sno) REFERENCES Student(Sno),
	FOREIGN KEY(Cno) REFERENCES Course(Cno),
)

2.数据输入

代码如下：

insert into Student values('9512101','李勇','男',19,'计算机系')
insert into Student values('9512102','刘晨','男',20,'计算机系')
insert into Student values('9512103','王敏','女',20,'计算机系')
insert into Student values('9521101','张立','男',22,'信息系')
insert into Student values('9521102','吴斌','女',21,'信息系')
insert into Student values('9521103','张海','男',20,'信息系')
insert into Student values('9511101','钱晓平','女',18,'数学系')
insert into Student values('9511102','王大力','男',19,'数学系')

insert into Course values('c01','计算机文化学',3,1)
insert into Course values('c02','java',2,3)
insert into Course values('c03','计算机网络',4,7)
insert into Course values('c04','数据库网络',4,6)
insert into Course values('c05','高等数学',8,2)
insert into Course values('c06','数据结构',5,4)
insert into Course values('c07','操作系统',4,5)
insert into Course values('c08','离散数学',6,3)

insert into SC values('9512101','c01',90)
insert into SC values('9512101','c02',86)
insert into SC values('9512101','c06',null)
insert into SC values('9512101','c07',88)
insert into SC values('9512102','c02',78)
insert into SC values('9512102','c04',66)
insert into SC values('9521102','c01',82)

insert into SC values('9521102','c02',75)
insert into SC values('9521102','c04',92)
insert into SC values('9521102','c05',50)
insert into SC values('9521103','c02',68)
insert into SC values('9521103','c06',null)
insert into SC values('9521103','c07',76)
insert into SC values('9531101','c01',80)
insert into SC values('9531101','c05',95)
insert into SC values('9531102','c05',85)

3.更新数据

代码如下：

Update Student Set Sage=Sage-1
 Where seSx='男'
Update Student Set Sdept='金融系'
Where Sname='王敏'
Update Course Set Semster='4'
Where Cname='数据库基础'
Update SC Set Grade=Grade+2
Where Sno=9512101
Update Course Set Grade=Grade+2
Where Cname='数据库基础'

Delete From SC Where Grade is NULL
Delete From Course Where Cname='VB'
Delete From SC Where Cno=’c05’

总结

对于大数据字段，独立表进行存储，以便影响性能。

使用varchar类型代替char，因为varchar会动态分配长度，char指定长度是固定的。

给表创建主键，对于没有主键的表，在查询和索引定义上有一定的影响。