-- 实训一 T-SQL操作数据库
-- 一、创建数据库eshop（网上商城数据库），写出下列操作的命令语句
-- 设置客户端字符集编码为gbk
set names gbk;
-- 1． 创建数据库eshop
create database eshop;
create database eshop;
-- 2． 打开数据库eshop
use eshop;
use eshop;
-- 3． 显示数据库中所有表
show tables;
show tables;
-- 二、创建数据表
-- 4． 在ESHOP数据库中建立“会员”表，用于存放会员信息：
create table 会员
(
会员编号 varchar(20) not null primary key,
姓名 varchar(20) not null,
性别 char(2) not null default '男',
出生日期 date,
住址 varchar(50),
工资 float not null,
密码 varchar(20) not null default '111111',
电话号码 varchar(11) not null unique
);
create table 会员
(
会员编号 varchar(20) not null primary key,
姓名 varchar(20) not null,
性别 char(2) not null default '男',
出生日期 date,
住址 varchar(50),
工资 float not null,
密码 varchar(20) not null default '111111',
电话号码 varchar(11) not null unique
);-- 在ESHOP数据库中建立“产品”表，用于存放产品信息：
create table 产品
(
产品编号 char(10) not null primary key,
产品名称 varchar(30) not null,
生产日期 date,
数量 int,
价格 float,
备注信息 varchar(50)
);
-- 在ESHOP数据库中建立"订单"表，用于存放订单信息：
create table 订单
(
订单编号 int not null auto_increment primary key,
会员编号 varchar(20) not null,
产品编号 char(10) not null,
订购数量 int not null,
订单日期 date not null,
确认状态 char(1) not null,
支付状态 char(1) not null,
发货状态 char(1) not null
);
-- 5、在“订单”表中创建外键，分别参照'会员'表的会员编号、"产品"表的'产品编号'
alter table 订单
add constraint fk_会员_订单 foreign key(会员编号) references 会员(会员编号)
on delete cascade on update cascade;
alter table 订单
add constraint fk_产品_订单 foreign key(产品编号) references 产品(产品编号)
on delete cascade on update cascade;
alter table 订单
add constraint fk_会员_订单 foreign key(会员编号) references 会员(会员编号)
on delete cascade on update cascade;
alter table 订单
add constraint fk_产品_订单 foreign key(产品编号) references 产品(产品编号)
on delete cascade on update cascade;-- 三、录入数据到数据表
-- 6． 录入数据到ESHOP数据库的会员表：
-- 对应命令如下：
insert into 会员(会员编号,姓名,性别,出生日期,住址,工资,密码,电话号码)
values
('Jinjin','津津有味','女','1982-04-14','北京市',8200.0,'jinjin','13112345678'),
('Lfz','刘法治','男','1976-08-26','天津市',4500.0,'lfz0826','13156785678'),
('liuzc518','刘志成','男','1972-05-18','江西九江',3500.0,'liuzc518','13112341234'),
('wangj','王娟','女','1979-05-18','江西九江',3500.0,'Wj1972','13612345670'),
('wangxj','王晓娟','女','1979-05-18','江西九江',3500.0,'Wxj1972','13612345678'),
('Wangym','王咏梅','女','1974-08-06','湖南长沙',4000.0,'wangym0806','13211111111'),
('wenh','翁红','男','1970-04-20','江西南昌',5500.0,'888888','15345678123'),
('zhangsan','张三','男','1982-04-14','北京市',8200.0,'Zs88888','13512345678'),
('Zhangzl','张自亮','男','1975-04-20','江西九江',4300.0,'zhangzl','13312312312'),
('zhao888','赵云','男','1972-02-12','江西九江',5500.0,'zhao888','13412345678');
-- 录入数据到ESHOP数据库的产品表：
-- 对应命令如下：
insert into 产品(产品编号,产品名称,生产日期,数量,价格,备注信息)
values
('0130810324','清华同方电脑','2005-12-11',7,8000.0,'优惠多多'),
('0140810330','洗衣粉','2005-05-31',1000,8.6,'特价销售'),
('0140810332','红彤彤腊肉','2005-05-20',43,15.0,'是一种卫生食品'),
('0140810333','力士牌香皂','2005-05-06',22,6.0,'是一种清洁用品'),
('0240810330','电动自行车','2005-05-31',10,1586.0,'价廉物美'),
('0240810333','自行车','2005-05-31',10,586.0,'价廉物美'),
('0910810001','爱国者MP3','2005-05-31',100,450.0,'价廉物美'),
('0910810002','商务通','2005-05-20',10,850.0,'价廉物美'),
('0910810003','名人好记星','2005-05-31',100,550.0,'价廉物美'),
('0910810004','奥美嘉U盘','2005-05-31',100,350.0,'价廉物美');
-- 录入数据到ESHOP数据库的订单表：
-- 注意：第6条记录中的产品编号的值0140810324在产品表中不存在，因为外键的关系，不能输入该条记录，所以将0140810324改为0130810324
-- 注意：第六列字段名'确定状态'应该是'确认状态'
insert into 订单(订单编号,会员编号,产品编号,订购数量,订单日期,确
认状态,支付状态,发货状态)
values
(null,'jinjin','0910810004',2,'2005-06-06','1','0','0'),
(null,'jinjin','0910810004',1,'2005-08-09','1','1','1'),
(null,'lfz','0910810001',5,'2005-08-09','0','0','0'),
(null,'lfz','0910810004',2,'2005-06-06','1','1','1'),
(null,'lfz','0910810004',2,'2005-08-09','1','1','1'),
(null,'liuzc518','0140810324',1,'2005-10-09','0','0','0'),
(null,'liuzc518','0910810001',1,'2005-10-09','1','1','0'),
(null,'liuzc518','0910810004',12,'2005-10-09','1','1','0'),
(null,'wangym','0910810001',1,'2005-08-09','1','0','0'),
(null,'zhao888','0240810333',2,'2005-06-06','1','1','0');
-- 四、ESHOP数据库中表和表记录的操作
-- 7． 查看'会员'表的建表语句
show create table 会员;
show create table 会员;-- 8． 查看会员表的结构。
desc 会员;
desc 会员;-- 9． 在会员表中新增一列电子邮件m_mail（VARCHAR类型，长度20）。
alter table 会员
add m_mail varchar(20);
alter table 会员
add m_mail varchar(20);-- 10． 删除会员表中列m_mail。
alter table 会员
drop m_mail;
alter table 会员
drop m_mail;-- 五、表数据操作
-- 11． 向'订单'表插入一条订单信息。
insert into 订单(订单编号,会员编号,产品编号,订购数量,订单日期,确认状态,支付状态,发货状态)
values
(null,'jinjin','0910810004',2,'2005-06-06','1','0','0');
insert into 订单(订单编号,会员编号,产品编号,订购数量,订单日期,确认状态,支付状态,发货状态)
values
(null,'jinjin','0910810004',2,'2005-06-06','1','0','0');
-- 12． 将性别为“男”，且家庭地址为“江西九江”的会员的工资增加1000。
update 会员
set 工资=工资+1000
where 性别='男' and 住址='江西九江';
update 会员
set 工资=工资+1000
where 性别='男' and 住址='江西九江';
-- 13． 删除住址为“赣州”的会员记录。
delete from 会员
where 住址='赣州';
delete from 会员
where 住址='赣州';
-- 14．查询所有会员的会员编号,姓名,性别,住址,工资，并按住址升序排列，住址相同的按工资降序排列。
select 会员编号,姓名,性别,住址,工资
from 订单
order by 住址,工资 desc;
select 会员编号,姓名,性别,住址,工资
from 订单
order by 住址,工资 desc;-- 15．查询女会员的会员编号,姓名,性别,住址
select 会员编号,姓名,性别,住址
from 会员
where 性别='女';
select 会员编号,姓名,性别,住址
from 会员
where 性别='女';
-- 16. 查询工资前5名的会员信息。
select *
from 会员
order by 工资 desc
limit 0,5;
select *
from 会员
order by 工资 desc
limit 0,5;-- 17．查询每个城市（住址）的会员工资平均值，列名指定为为“各会员月薪平均值”。
select 会员编号,avg(工资) as 各会员月薪平均值
from 会员
group by 会员编号;
select 会员编号,avg(工资) as 各会员月薪平均值
from 会员
group by 会员编号;
-- 18. 查询每个城市（住址）的会员人数，要求输出会员数大于2的住址和人数。
select 住址,count(*)
from 会员
group by 住址
having count(*)>=2;
select 住址,count(*)
from 会员
group by 住址
having count(*)>=2;
-- 19. 查询产品名称为“清华同方电脑”的购买信息，包括会员编号、姓名、订单编号、产品名称
select 会员.会员编号,姓名,订单编号,产品名称
from 会员 inner join 订单 on 会员.会员编号=订单.会员编号
inner join 产品 on 订单.产品编号=产品.产品编号
WHERE 产品名称='清华同方电脑';
select 会员.会员编号,姓名,订单编号,产品名称
from 会员 inner join 订单 on 会员.会员编号=订单.会员编号
inner join 产品 on 订单.产品编号=产品.产品编号
WHERE 产品名称='清华同方电脑';
-- 20. 用命令方式备份eshop数据库到e盘根目录eshop.sql 文件(要包含建库语句）
mysqldump -uroot -p123456 --databases eshop > e:\eshop.sql
mysqldump -uroot -p123456 --databases eshop > e:\eshop.sql
672
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
