【数据库基础】数据库的创建与定义

目录

1.数据库的创建

2.数据库的维护

---

1.数据库的创建

首先让我们新建查询(使用软件Microsoft SQL Sever Management Studio 2008)

 

输入:

create database T2

点击执行,这样就完成了数据库T2的创建。

然后再创建逐个表:supplier,category,student,goods,salebill

create table supplier
(
	supplierno char(6) primary key,--主键
	suppliername nvarchar(10),
	address nvarchar(20),
	number char(11)
	
)

create table category
(
	categoryno char(5) primary key,--主键
	categoryname varchar(20),
	descriptions text
)

create table student
(
	sno char(3) primary key,--主键
	sname varchar(10),
	birthyear char(4),
	gender char(2),
	college varchar(10),
	major varchar(10),
	weixin char(5) 
)

create table goods
(
	goodsno char(6) primary key,--主键
	supplierno char(6),
	categoryno char(5),
	goodsname varchar(20),
	inprice float,
	saleprice float,
	number int,
	producttime datetime,
	foreign key (supplierno) references supplier(supplierno),--外键
	foreign key (categoryno) references category(categoryno)--外键
)

create table salebill
(
	goodsno char(6),
	sno char(3),
	happentime datetime,
	number int,
	primary key(goodsno,sno),
	foreign key (goodsno) references goods(goodsno),--外键
	foreign key(sno) references student(sno)--外键
)

接着，向这些表中插入数据，使用insert into table_name values( )，如下

insert into supplier
values('sup001','卡天食品','广东佛山','12348768900'),
('sup002','久润食品','广东东莞','13248768901'),
('sup003','飞鹤食品','重庆解放碑','12648768901'),
('sup004','南山日化','重庆南坪','11648768903'),
('sup005','缙云日化','重庆北碚','19648768903