SQL入门教程（六）：MySQL与Python交互

1. 创建数据库、数据表

--创建数据库
create database jing_dong charset=utf8;

--使用数据库
use jing_dong;

--创建数据表
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);

--查询数据表
show tables;

2. 向数据表中插入数据

insert into goods values(0, '15.6寸笔记本', '超级本', '联想', '2999', default, default);
insert into goods values(0, '13.3寸笔记本', '工作站', '华硕', '3999', default, default);
insert into goods values(0, '15.6寸笔记本', '笔记本', '小米', '1999', default, default);
insert into goods values(0, '15.6寸笔记本', '笔记本', '戴尔', '7999', default, default);
insert into goods values(0, '14寸笔记本', '超级本', '拯救者', '6999', default, default);
insert into goods values(0, '15.6寸笔记本', '笔记本', '华为', '9999', default, default);
insert into goods values(0, '15.6寸笔记本', '笔记本', '联想', '5999', default, default);
insert into goods values(0, '14寸笔记本', '超级本', '联想', '3999', default, default);
insert into goods values(0, '15.6寸笔记本', '工作站', '联想', '4299', default, default);
insert into goods values(0, '13.3寸笔记本', '笔记本', '小米', '4959', default, default);
insert into goods values(0, '15.6寸笔记本', '工作站', '联想', '4299', default, default);

3. 查找数据

--查询商品种类及价格
select name as 商品名称, price as 商品价格 from goods where cate_name='超级本';

--查询商品种类
select distinct cate_name from goods;
select cate_name from goods group by cate_name;

--显示商品种类及对应的商品名称
select cate_name, group_concat(name) from goods group by cate_name;

--求各电脑产品的平均价格，保留两位小数
select cate_name, round(avg(price), 2) as 平均价格 from goods group by cate_name;

4. 表的拆分，创建商品分类表

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

查询goods表中的商品种类

select cate_name from goods group by cate_name;

将分组结果写入到goods_cates数据表

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

建立两表之间的关系

update goods as g inner join goods_cates as c on g.cate_name=c.name set g.cate_name=c.id;

改变字段名及类型

alter table goods change cate_name cate_id int unsigned not null;

插入外键

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

展示外键名称

show create table goods;

删除外键

alter table goods drop foreign key goods_ibfk_1;