实验1 数据库及表的管理和数据操纵

一、实验项目：

数据库及表的管理和数据操纵。

二、实验目的

1、能够使用SQL语句创建、修改和删除数据库。

2、能够使用SQL语句创建、修改和删除数据表。

3、能够使用SQL语句插入、修改和删除表数据。

三、实验内容

（一）：2学时

使用SQL语句完成下列题目：

1、创建一个名为agristore的数据库，采用字符集latin1和校对规则latin1_swedish_ci。

2、修改数据库agristore的默认字符集gb2312，校对规则为gb2312_chinese_ci。

3、在数据库agristore中创建如下表，表结构如下：

用户表account

属性名称	含义	数据类型	为空性	备注
userid	用户编号	Char(5)	NOT NULL	主键
fullname	用户名	Varchar(10)	NOT NULL
password	密码	Varchar(20)	NOT NULL
sex	性别	Char(2)	NOT NULL
address	住址	Varchar(40)	NULL
email	邮箱	Varchar(20)	NULL
phone	电话	Char(11)	NOT NULL

商品分类表category

属性名称	含义	数据类型	为空性	备注
catid	类别编号	Char(3)	NOT NULL	主键
catname	分类名称	Varchar(20)	NOT NULL
cades	类别描述	text	NULL

商品表product

属性名称	含义	数据类型	为空性	备注
productid	商品编号	Char(7)	NOT NULL	主键
catid	类别编号	Char(3)	NOT NULL
name	商品名	Varchar(30)	NOT NULL
descn	商品介绍	text	NULL
listprice	市场价格	Decimal(10,2)	NULL
unitcost	成本价格	Decimal(10,2)	NULL
qty	数量	Int	NOT NULL

订单表orders

属性名称	含义	数据类型	为空性	备注
orderid	订单号	Int	NOT NULL	主键，按订单生成顺序自动编号
userid	用户编号	Char(5)	NOT NULL
orderdate	订单日期	datetime	NOT NULL	当前日期
totalprice	订单总价	Decimal(10,2)	NULL
status	订单状态	Tinyint	NULL

订单明细表lineitem

属性名称	含义	数据类型	为空性	备注
orderid	订单号	Int	NOT NULL	主键
productid	商品编号	Char(7)	NOT NULL	主键
quantity	数量	Int	NOT NULL
unitprice	单价	Decimal(10,2)	NOT NULL

4、修改用户表account，向表中增加 新列“喜好”，数据类型为varchar(50)，允许为空。

5、将account表中的列address的数据类型修改为varchar(50)。

6、删除account表中的“喜好”列。

（二）：2学时

1、对orders表中userid（客户编号）列引用了account表中的userid（客户编号）。要求为orders表中userid（客户编号）列创建外键，以保证当要删除和更新account表中的数据时，只要orders表中还有该客户的订单，就拒绝对account表进行的删除和更新操作。

2、对lineitem表中productid（商品编号）列引用了product表中的productid（商品编号）。要求为lineitem表中productid（商品编号）列创建外键，以保证当要删除和更新product表中的商品编号时，自动删除或更新lineitem匹配的行。

3、在agristore数据库的下列表中插入如下数据：

用户表account数据

userid	fullname	password	sex	address	email	phone
u0001	刘晓和	123456	男	广东深圳市	liuxh@163.com	13512345678

商品表product数据

productid	catid	name	descn	listprice	unitcost	qty
0010001	001	金龙鱼一级大豆油5L	品质好	50.99	40.00	100

订单表orders数据

orderid	userid	orderdate	totalprice	status
20230411	u0001	2023-04-11 15:07:34	800	0

4、新进一批金龙鱼一级大豆油5L，数量为50桶，进价为45元，按库存与新进商品的平均值调整商品的成本价格。该商品将以高出成本价格20%的市场价格卖出，调整商品的市场价格和数量。

5、订单号为20230411的订单已经发货，在订单表中将该订单的状态修改为1。

6、删除商品表中名称为“金龙鱼一级大豆油5L”的商品信息。

四、实验参考代码

1、create database agristore

character set latin1

collate latin1_swedish_ci;

show create database agristore;

2、alter database agristore

default character set gb2312

default collate gb2312_chinese_ci;

show create database agristore;

3、（1）use agristore;

CREATE TABLE  account  (

   userid  char(5) NOT NULL,

   fullname  varchar(10) NOT NULL,

   password  varchar(20) NOT NULL,

   sex  char(2) NOT NULL,

   address  varchar(40) NULL,

   email  varchar(20) NULL,

   phone  char(11) NOT NULL,

  PRIMARY KEY  ( userid )

) ;

desc account;

（2）

CREATE TABLE  category  (

   catid  char(3) NOT NULL,

   catname  varchar(20) Not NULL,

   cades  text NULL,

  PRIMARY KEY  ( catid )

);

desc category;

（3）

CREATE TABLE  product  (

   productid  char(7) NOT NULL,

   catid  char(3) NOT NULL,

   name  varchar(30) Not NULL,

   descn  text NULL,

   listprice  decimal(10,2)  NULL,

   unitcost  decimal(10,2)  NULL,

   qty  int NOT NULL,

  PRIMARY KEY  ( productid )

) ;

desc product;

（4）

CREATE TABLE  orders  (

   orderid  int  NOT NULL auto_increment,

   userid  char(5) NOT NULL,

   orderdate  datetime NOT NULL DEFAULT NOW(),/*由于MySQL5.6以下的版本不支持datetime类型default now()的写法，故此处的也可以写成orderdate timestamp NOT NULL default now()*/

   totalprice  decimal(10,2)  NULL,

   status  tinyint  NULL,

  PRIMARY KEY( orderid )

) ;

desc orders;

（5）

CREATE TABLE  lineitem  (

   orderid  int NOT NULL,

   productid  char(7) NOT NULL,

   quantity  int NOT NULL,

   unitprice  decimal(10,2) NOT NULL,

  PRIMARY KEY  ( orderid , productid )

) ;

desc lineitem;

4、Alter table account add column 喜好 varchar(50) null;

Desc account;

5、Alter table account modify column address varchar(50);

Desc account;

6、Alter table account drop column 喜好;

Desc account;

（二）1、alter table orders

add constraint foreign key(userid)

references account(userid)

on update restrict

on delete restrict;

show create table orders\G

2、alter table lineitem

add constraint foreign key(productid)

references product(productid)

on update cascade

on delete cascade;

show create table lineitem\G

3、（1）

INSERT INTO  account  VALUES ('u0001', '刘晓和', '123456', '男', '广东深圳市', 'liuxh@163.com', '13512345678');

select * from account;

（2）INSERT INTO  product  VALUES ('0010001', '001', '金龙鱼一级大豆油5L', '品质好', 50.99, 40.00, 100);

select * from product;

（3）INSERT INTO  orders  VALUES (20230411, 'u0001', '2023-04-11 15:07:34', 800.00, 0);

select * from orders;

4、update  product

Set unitcost=(qty*unitcost+45*50)/(qty+50),listprice=unitcost*1.2,qty=qty+50

where name='金龙鱼一级大豆油5L';

修改前：select * from product;

修改后：select * from product;

5、update orders set status=1 where orderid=20230411;

修改前：select * from orders;

修改后：select * from orders;

6、delete from product where name='金龙鱼一级大豆油5L';

删除前：select * from product;

删除后：select * from product;