mysql数据库CRUD操作

数据库的 CRUD 操作

• create：添加数据
• read：读取数据
• update：修改数据
• delete：删除数据

准备数据

# 创建数据库
create database select001 charset=utf8;
# 创建表
create table goods(
    id int unsigned primary key auto_increment not null,
    name varchar(150) not null,    -- 商品名称
    cate 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   -- 是否打折
);
# 向表中插入数据
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 超极本','超级本','联想','4999',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,'hmz-t3w 头戴显示设备','笔记本配件','索尼','6999',default,default); 
insert into goods values(0,'商务双肩背包','笔记本配件','索尼','99',default,default);

• 查询所有价格大于平均价格的商品，并且按价格降序排序

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

• 查询类型为"超极本"的商品的价格

select price from goods where cate = "超极本" order by price desc;

查找插入数据和连接更新数据

• 现在将上面的表进行拆分为两张表：

create table if not exists goods_cates(cate_id int unsigned primary key auto_increment, cate_name varchar(40));

• 查询goods表的所有记录，并且按“类别”分组

select cate from goods group by cate;

• 将刚查询的数据插入到 goods_cates 这张表中

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

• 把cate_id这一列的数字换成对应的goods中的cate列
  
• 通过goods_cates数据表来更新goods表

update goods as g inner join goods_cates as c on g.cate = c.cate_name set g.cate = c.cate_id;

• 接下来再把品牌这一列用相同的方法进行替换

# 创建表并写入数据
create table goods_brands(id int unsigned primary key auto_increment not null, brand_name varchar(40)) select brand_name from goods group by brand_name;
# 将新表的id字段值写入到对应的商品表中的brand_name字段
update goods as g inner join goods_brands as b on g.brand_name = b.brand_name set g.brand_name = g.id;

• 修改商品表的结构

# 查看标的结构
desc goods;

# 修改表结构
alter table goods change cate cate_id int unsigned,change brand_name brand_id int unsigned;

多张表连接查询

# 查询所有商品的详细信息（内，左，右连接）
# 连接多张表
select * from goods as g inner join goods_cates as c on g.cate_id = c.cate_id inner join goods_brands as b on g.brand_id = b.id;

• 左连接的话，左边的第一个表就是主表。
• 右连接的话，最后一个表为主表，其他的都是从表。

外键约束的创建和删除

• 添加外键约束

# 添加外键约束
alter table goods add foreign key (brand_id) references goods_brands(id);
alter table goods add foreign key (cate_id) references goods_brands(cate_id);

• 如果添加失败的话，看一下数据本身是不是有问题
• 外键可以检测数据的有效性，要不删除错误的数据，要不修改错误的数据
• 注意：添加外键的那个字段的数据类型必须要与相关联的那个表的字段的数据类型一致。
• 删除外键约束：需要删除具体删除的外键的名字：

# 删除外键约束
alter table goods drop foreign key goods_ibfk_1;

• 现在外键没有了，但是多了两个key，下面把这个key也删除掉