mysql实验第二章

/**
第二次实验报告
*/


/*1完成patron 表，menu 表，food_item 表的创建*/
#顾客表
create table patron (
patron_id int,
patron_name varchar(20),
address varchar(30),#送餐地址
email varchar(30)
);


#菜系
create table menu (
menu_id int,
menu_name varchar(20),
menu_date date
);


#餐单
create table food_item (
food_id int,
food_name varchar(20),
food_description varchar(50),
food_price varchar(10),
menu_id int
);


/*分辨创建主键、外键*/
alter table patron add constraint pk_patron primary key(patron_id);
alter table patron drop primary key;


alter table menu add constraint pk_menu primary key(menu_id);
alter table menu drop primary key;




alter table food_item add constraint pk_food primary key(food_id);
alter table food_item drop primary key;
alter table food_item add constraint fk_food foreign key(menu_id) references menu(menu_id);
alter table food_item drop foreign key;




/*2完成修改patron 表的结构，增加一个字段patron 的身份证号，然后再删除这个字段*/
alter table patron add column id varchar(18);
alter table patron drop column id;


/*3任意新建一张表，然后删除此表*/
create table test(
test_id int,
name varchar(45)
);
select * from test;
drop table test;


/*4查询patron 表中patron_name字段的长度小于3的所有的人的记录*/
insert into patron values(1,'涛哥LOL','江苏苏州','[e-mail]');
insert into patron values(2,'唐磊','江苏南京','[e-mail]');
insert into patron values(3,'wow','江苏苏州','[e-mail]');
insert into patron values(4,'李彦宏','江苏苏州','[e-mail]');
insert into patron values(5,'马云','浙江省嵊州市谷来镇','[e-mail]');
insert into patron values(6,'tl','浙江省嵊州市谷来镇','[e-mail]');
insert into patron values(7,'dota','浙江省嵊州市谷来镇','[e-mail]');
insert into patron values(8,'my','浙江省嵊州市谷来镇','[e-mail]');
select * from patron;
select * from patron where length(patron_name)<3;
select *,length(patron_name) 长度 from patron;


/*5增加一种菜类型（menu），并增加这种类型的菜2~3 样（food_item）*/
insert into menu values(1,'川菜','2015-08-24');
insert into food_item values(1,'夫妻肺片','好吃1','20','1');
insert into food_item values(2,'水煮鱼','好吃2','30','1');
insert into food_item values(3,'麻婆豆腐','好吃3','40','1');
insert into food_item values(4,'老乡鸡','好吃4','50','4');
insert into food_item values(5,'酸辣土豆丝','好吃5','60','1');
insert into food_item values(7,'剁椒鱼头','好吃7','70','3');
insert into food_item values(8,'黄埔炒蛋','炒吃8','100','2');
#insert into food_item values(6,'麻婆豆腐','好吃3','40','10000');报错，由于有外键


/*6新建一个视图，包含 patron 表中的patron_name,email 两个字段*/
create view patron_view as select patron_name,email from patron;
select * from patron_view;


/*7查询food_item 表中的总记录数*/
select count(*) from food_item;


/*8查询patron 表中手机尾号为’5728’的记录*/
alter table patron add column cellphone varchar(11);
update patron set cellphone='12345678902' where patron_id=1;
update patron set cellphone='12345678182' where patron_id=2;
update patron set cellphone='12345629302' where patron_id=3;
update patron set cellphone='12345633902' where patron_id=4;
update patron set cellphone='12345670002' where patron_id=5;
update patron set cellphone='12345695728' where patron_id=6;
update patron set cellphone='12345685728' where patron_id=7;
update patron set cellphone='12345675728' where patron_id=8;


select * from patron where cellphone like'%5728';


/*9将menu表按时间排序（升序）*/
insert into menu values(2,'粤菜','2015-08-22');
insert into menu values(3,'湘菜','2015-07-20');
insert into menu values(4,'徽菜','2015-09-19');


select * from menu order by menu_date;


/*10查询出food_item 表中menu_id 以及对应的menu_id 的菜的个数*/
select menu_id,count(food_name) 个数
from food_item 
group by menu_id;


/*11查询出patron 表中的前5 条记录*/
select * from patron limit 5;
select * from patron limit 0,3;


/*12查询出菜名为’土豆丝’的菜的价格，菜的描述以及它所属的菜的种类(menu)的名字*/
select food_price 价格,food_description 菜的描述, menu_name 菜的种类 
from menu,food_item
where menu.menu_id=food_item.menu_id 
and food_name like '%土豆丝%';


/*13查询出food_item 表中和’土豆丝’一样菜价的菜的名字，价格*/
update food_item set food_price='60' where food_price='20';


select food_name 菜的名字,food_price 价格
from food_item 
where food_price in
(select food_price from food_item 
where food_name like '%土豆丝%'
) 
and food_name not like '%土豆丝%';