必赢365net手机版mysql学习笔记

必赢365net手机版 13

 

必赢365net手机版 1

create table day9_work(
id int auto_increment comment
‘编号’,
compensation float comment
‘薪资’,
postcode char(6) comment ‘邮编’,
name varchar(20) comment ‘姓名’,
birthday datetime comment
‘出生具体时间’,
birthdayday date comment
‘出寿辰期’,
intro text comment ‘介绍’,
sex enum(‘男’,’女’) comment
‘性别’,
hobby set(‘台球’,’篮球’,’足球’) comment
‘爱好’,
primary key(id),
key(name)
) engine=innodb,charset=utf8;
alter table day9_work add age int default
18;
alter table day9_work change postcode
postcode char(10) comment ‘邮编’;
alter table day9_work rename
day9_workrename;
create table day9_workrename2 like
day9_workrename;
insert into day9_workrename
values(3,2.4,’12345′,’mpc’,’1995-9-28 15:34:00′,’1995/9/28′,’my name is
mpc’,’男’,’台球,篮球’,’21’);
insert into day9_workrename
values(null,2.4,’12345′,’mpc2′,’1995-9-28 15:34:00′,’1995/9/28′,’my name
is mpc2′,’男’,5,’21’);
insert into day9_workrename2 select *
from day9_workrename;

数据库设计:
1NF:须要数据达到原子性使数据不可再分:

必赢365net手机版 2

2NF:要求数据每意气风发行统筹唯生龙活虎性(保险数据不重复卡塔尔国,消释数据部分注重:
必赢365net手机版 3

 

主键一定调整其余字段,其余字段一定信任于主键;
必赢365net手机版 4

免去部分正视正是确认保证完全依附;

必赢365net手机版 5

改进:
必赢365net手机版 6

3NF:独立性,消释传递正视:

必赢365net手机版 7

改进:

必赢365net手机版 8

 

必赢365net手机版 9

数量操作语言:

多少查询语言:
骨干查询:

 

day10_work:
必赢365net手机版 10

必赢365net手机版 11

 

create table day10_work(
商品ID int,
商品名称 varchar(100),
类型ID int,
商品价位 int,
商品品牌 varchar(20),
商品生产区 varchar(20),
primary key(商品ID)
);
insert into
day10_work(商品ID,商品名称,类型ID,商品价位,商品品牌,商品生产区) values

(1,’创维(ChangHong卡塔 尔(英语:State of Qatar)42英寸全高清液晶TV’,1,1998,’ChangHong’,’柏林(Berlin卡塔尔’);
insert into
day10_work(商品ID,商品名称,类型ID,商品价位,商品品牌,商品生产区) values

(15,’Acer(HP卡塔 尔(英语:State of Qatar)黑白激光打字与印刷机’,3,1169,’Dell’,’里昂’),
(3,’长虹(长虹卡塔 尔(英语:State of Qatar)55英寸智能TV’,1,4199,’Hisense’,’马斯喀特’),
(2,’索尼(SONY)4G手机(黑色)’,2,3238,’索尼’,’深圳’),
(5,’索尼(Sony卡塔尔(SONY卡塔尔国13.3英寸触控一级本’,3,11499,’Sony’,’达卡’),
(11,’Sony(SONY卡塔 尔(英语:State of Qatar)60英寸全高清液晶电视机’,1,6999,’索尼(Sony卡塔尔’,’日本东京’),
(12,’联想(异极卡塔尔14.0英寸台式机Computer’,3,2999,’联想’,’北京’),
(13,’联想
双卡双待3G手提式有线电话机’,2,988,’联想’,’新加坡’),
(4,’联想(Dell)14.0英寸台式机Computer’,3,5499,’联想’,’香港’);

select * from day10_work order by
商品价位 desc limit 0,1;
select 类型ID from day10_work order by
类型ID desc limit 0,1;
select * from day10_work order by
商品价位 asc limit 0,1;
select 类型ID from day10_work order by
类型ID asc limit 0,1;
select count(*) as 商品数量总和 from
day10_work;
select avg(商品价位) as 平均价格 from
day10_work;
select avg(商品价位) as 联想品牌平平均价格格from day10_work where 商品品牌=’联想’;
select * from day10_work order by
商品价位 desc;
select * from day10_work order by 类型ID
desc,商品价位 asc;
select * from day10_work order by
商品价位 desc limit 0,3;
select 商品产区,count(*) as
各类生产地区各有的多寡 from day10_work group by 商品产区;
select 商品品牌,count(*) as
每个品牌各有的多少 from day10_work group by 商品品牌;

 

 

 

day11作业:

必赢365net手机版 12

必赢365net手机版 13

 

Leave a Comment.