MySQL(3日目)

文書ディレクトリ

データベース操作拡張

1.データテーブルクエリー(単一テーブル集約関数)

2.テーブル構造の変更(7ステップ)

3.外部キーの使用(データ制約)

データベース操作の拡張
1.データテーブルクエリー(単一テーブル集約関数)

--    "  "    
create database jing_dong charset=utf8;

--    "  "    
use jing_dong;

--       goods   
create table goods(
    id int unsigned primary key auto_increment not null,
    name varchar(150) not null,
    cate_name varchar(40) not null,
    brand_name varchar(40) not null,
    price decimal(10,3) not null default 0,
    is_show bit not null default 1,
    is_saleoff bit not null default 0
);

--  goods      

insert into goods values(0,'r510vc 15.6     ','   ','  ','3399',default,default); 
insert into goods values(0,'y400n 14.0       ','   ','  ','4999',default,default);
insert into goods values(0,'g150th 15.6     ','   ','  ','8499',default,default); 
insert into goods values(0,'x550cc 15.6     ','   ','  ','2799',default,default); 
insert into goods values(0,'x240    ','   ','  ','4880',default,default); 
insert into goods values(0,'u330p 13.3     ','   ','  ','4299',default,default); 
insert into goods values(0,'svp13226scb      ','   ','  ','7999',default,default); 
insert into goods values(0,'ipad mini 7.9      ','    ','  ','1998',default,default);
insert into goods values(0,'ipad air 9.7      ','    ','  ','3388',default,default); 
insert into goods values(0,'ipad mini    retina    ','    ','  ','2788',default,default); 
insert into goods values(0,'ideacentre c340 20       ','   ','  ','3499',default,default); 
insert into goods values(0,'vostro 3800-r1206     ','   ','  ','2899',default,default); 
insert into goods values(0,'imac me086ch/a 21.5      ','   ','  ','9188',default,default); 
insert into goods values(0,'at7-7414lp      linux ）','   ','  ','3699',default,default); 
insert into goods values(0,'z220sff f4f06pa   ','   /   ','  ','4288',default,default); 
insert into goods values(0,'poweredge ii   ','   /   ','  ','5388',default,default); 
insert into goods values(0,'mac pro       ','   /   ','  ','28888',default,default); 
insert into goods values(0,'hmz-t3w       ','     ','  ','6999',default,default); 
insert into goods values(0,'      ','     ','  ','99',default,default); 
insert into goods values(0,'x3250 m4      ','   /   ','ibm','6888',default,default); 
insert into goods values(0,'      ','     ','  ','99',default,default);

-- sql    ( goods    )

--      cate_name   '   '       name 、   price 
select name,price from goods where cate_name = '   ';


--        
-- 1      ( group by ) 

select cate_name from goods group by cate_name;


-- 2      ( distinct )
select distinct cate_name from goods ;



--              avg ,        ( round )

select round(avg(price),2) from goods;



--         cate_name (        )      

select cate_name,avg(price) from goods group by cate_name;



--                 max 、    min 、    avg 、   count


select cate_name,max(price),min(price),
 avg(price),count(*) from goods group by cate_name;



--                  ，            order desc
--1.    
select avg(price) from goods;

--2.      
select * from goods where price > (select avg(price) from goods)
 order by price desc;



--               ( )

-- 1               max_price   
select cate_name,max(price) from goods group by cate_name;


select * from goods where (cate_name,price) = ('   ',9188);

-- 2        

select * from goods where (cate_name,price) in(
    select cate_name,max(price) from goods group by cate_name
);

2.テーブル構造の変更(7ステップ)

--     (     )

--        
--create table if not exists    (
--id int unsigned primary key auto_increment,      
--name varchar(40) not null);     


--       "     " -goods_cates
--id   

create table goods_cates(id int unsigned primary key auto_increment,
name varchar(50) not null);


--       "     " -goods_brands
--id   

create table goods_brands(id int unsigned primary key auto_increment,
name varchar(50) not null);






--           
--1.         

select cate_name from goods group by cate_name;


--2.        
--insert into goods_cates (name) values('123');

insert into goods_cates(name) 
(select cate_name from goods group by cate_name);





#        


--          
--1.           

select brand_name from goods group by brand_name;


--2.       
insert into goods_brands(name)
 (select brand_name from goods group by brand_name);



--                 id
--1.               


select * from goods  inner join goods_cates 
on goods.cate_name = goods_cates.name;



--2.      update     
#            select * from        

update (goods  inner join goods_cates 
on goods.cate_name = goods_cates.name) 
set goods.cate_name = goods_cates.id;



--                id
--1.              

select * from goods inner join goods_brands 
 on goods.brand_name = goods_brands.name; 



--2.     update       

update (goods inner join goods_brands 
 on goods.brand_name = goods_brands.name)
  set goods.brand_name = goods_brands.id;





	
--        (                )
-- alter table    change           
alter table goods change cate_name cate_id int unsigned;
alter table goods change brand_name brand_id int unsigned;

3.外部キーの使用(データ制約)

--                ,       (         )          
--            (   goods_cates   goods_brands          )

--      (  )

--  goods            "      "
--             ,         


--                foreign key
-- alter table     add foreign key (     ) references    (    );
alter table goods add foreign key(cate_id) references goods_cates(id);


--            (   goods_cates   goods_brands          )
-- foreign key (     ) references    (  )
create table if not exists goods_key(
    id int primary key auto_increment not null,
    name varchar(40) default '',
    price decimal(5,2),
    cate_id int unsigned,
    brand_id int unsigned,
    is_show bit default 1,
    is_saleoff bit default 0,
 	--     
    foreign key(cate_id) references goods_brands(id)
     
);


--         
--            ,          ,                
--show create table   ;
show create table goods;


--                     
--alter table    drop foreign key    ;

alter table goods drop foreign key goods_ibfk_1;

alter table goods drop key cate_id;