传智黑马Python37例子 自学中在网上找的视屏看，算是小白吧！没有其他的学习方法，之前看了一边，后面有看了一遍，把课中代码打了一边。 如果你有什么好方法赶紧给我分享啊！
MySQL操作
第一节：在mysql中操作例子
一. 创建
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
);
二. 导入
insert into goods values(0,'r510vc 15.6英寸笔记本','笔记本','华硕','3399',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英寸平板电脑','平板电脑','苹果','1999',default,default);
insert into goods values(0,'iPad air 9.7英寸平板电脑','平板电脑','苹果','3388',default,default);
insert into goods values(0,'iPad mini 配置 retine 显示屏','平板电脑','苹果','2788',default,default);
insert into goods values(0,'ideacentre c3340 20英寸一体电脑','台式机','联想','3499',default,default);
insert into goods values(0,'vostro 3800-r1206 台式电脑','台式机','戴尔','2899',default,default);
insert into goods values(0,'15.6 寸电脑屏保护膜','电脑配件','爱戴尔','29',default,default);
insert into goods values(0,'优雅 复古 无线鼠标键盘','电脑配件','雷蛇','299',default,default);
insert into goods values(0,'15寸 4K 液晶显示屏','电脑配件','索尼','1899',default,default);
insert into goods values(0,'限量款 LOL 鼠标垫','电脑配件','唯爱','29',default,default);
三. 查询
1.简单查询
mysql> select id as 编号,name as 商品名称,cate_name as 类型,brand_name as 品牌,price as 价钱 from goods as 商品表;
+--------+---------------------------------------+--------------+-----------+--------+
| 编号 | 商品名称 | 类型 | 品牌 | 价钱 |
+--------+---------------------------------------+--------------+-----------+--------+
| 1 | r510vc 15.6英寸笔记本 | 笔记本 | 华硕 | 3399 |
| 2 | x550cc 15.6英寸笔记本 | 笔记本 | 华硕 | 2799 |
| 3 | x240 超极本 | 超极本 | 联想 | 4880 |
| 4 | u330p 13.3英寸超级本 | 超极本 | 联想 | 4299 |
| 5 | svp13226scb 触控超级本 | 超级本 | 索尼 | 7999 |
| 6 | ipad mini 7.9英寸平板电脑 | 平板电脑 | 苹果 | 1999 |
| 7 | iPad air 9.7英寸平板电脑 | 平板电脑 | 苹果 | 3388 |
| 8 | iPad mini 配置 retine 显示屏 | 平板电脑 | 苹果 | 2788 |
| 9 | ideacentre c3340 20英寸一体电脑 | 台式机 | 联想 | 3499 |
| 10 | vostro 3800-r1206 台式电脑 | 台式机 | 戴尔 | 2899 |
| 11 | 15.6 寸电脑屏保护膜 | 电脑配件 | 爱戴尔 | 29 |
| 12 | 优雅 复古 无线鼠标键盘 | 电脑配件 | 雷蛇 | 299 |
| 13 | 15寸 4K 液晶显示屏 | 电脑配件 | 索尼 | 1899 |
| 14 | 限量款 LOL 鼠标垫 | 电脑配件 | 唯爱 | 29 |
+--------+---------------------------------------+--------------+-----------+--------+
14 rows in set (0.00 sec)
mysql> select id as 编号,name as 商品名称,price as 价钱,brand_name as 品牌,cate_name as 类型 from goods as 商品表 where cate_name='笔记本';
+--------+----------------------------+--------+--------+-----------+
| 编号 | 商品名称 | 价钱 | 品牌 | 类型 |
+--------+----------------------------+--------+--------+-----------+
| 1 | r510vc 15.6英寸笔记本 | 3399 | 华硕 | 笔记本 |
| 2 | x550cc 15.6英寸笔记本 | 2799 | 华硕 | 笔记本 |
+--------+----------------------------+--------+--------+-----------+
2 rows in set (0.00 sec)
mysql> select id as 编号,name as 商品名称,price as 价钱,brand_name as 品牌,cate_name as 类型 from goods as 商品表 where 类型='笔记本';
ERROR 1054 (42S22): Unknown column '类型' in 'where clause'
mysql> select id as 编号,name as 商品名称,price as 价钱,brand_name as 品牌,cate_name as cate from goods as 商品表 where cate='笔记本';
ERROR 1054 (42S22): Unknown column 'cate' in 'where clause'
2.属性查询 与其他
mysql> select distinct brand_name from goods;
+------------+
| brand_name |
+------------+
| 华硕 |
| 联想 |
| 索尼 |
| 苹果 |
| 戴尔 |
| 爱戴尔 |
| 雷蛇 |
| 唯爱 |
+------------+
8 rows in set (0.00 sec)
-- 与 distinct 相似
mysql> select brand_name from goods group by cate_name;
+------------+
| brand_name |
+------------+
| 华硕 |
| 唯爱 |
| 戴尔 |
| 爱戴尔 |
| 索尼 |
| 联想 |
| 苹果 |
| 雷蛇 |
+------------+
8 rows in set (0.01 sec)
-- 相比 distinct 功能较多
mysql> select brand_name,group_concat(id) from goods group by brand_name;
+------------+------------------+
| brand_name | group_concat(id) |
+------------+------------------+
| 华硕 | 1,2 |
| 唯爱 | 14 |
| 戴尔 | 10 |
| 爱戴尔 | 11 |
| 索尼 | 5,13 |
| 联想 | 3,4,9 |
| 苹果 | 6,7,8 |
| 雷蛇 | 12 |
+------------+------------------+
8 rows in set (0.28 sec)
-- 可用 as
mysql> select brand_name,group_concat(id) as group_id from goods group by branby brand_name;
+------------+----------+
| brand_name | group_id |
+------------+----------+
| 华硕 | 1,2 |
| 唯爱 | 14 |
| 戴尔 | 10 |
| 爱戴尔 | 11 |
| 索尼 | 5,13 |
| 联想 | 3,4,9 |
| 苹果 | 6,7,8 |
| 雷蛇 | 12 |
+------------+----------+
8 rows in set (0.00 sec)
3.最值查询
-- 求平均值
mysql> select avg(price) from goods;
+------------+
| avg(price) |xiangsi
+------------+
| 2871.7857 |
+------------+
1 row in set (0.00 sec)
-- 保留两有效数字 #在以后实际开发的时候，尽量不要用小数，一般是放大10N倍存入，使用时在除
mysql> select round(avg(price),2) from goods;
+---------------------+
| round(avg(price),2) |
+---------------------+
| 2871.79 |
+---------------------+
1 row in set (0.00 sec)
-- 这有个问题，group_concat 不能这么用
mysql> select brand_name,group_concat(avg(price)) from goods group by brand_name;
ERROR 1111 (HY000): Invalid use of group function
mysql> select brand_name,avg(price) from goods group by brand_name;
+------------+------------+
| brand_name | avg(price) |
+------------+------------+
| 华硕 | 3099.0000 |
| 唯爱 | 29.0000 |
| 戴尔 | 2899.0000 |
| 爱戴尔 | 29.0000 |
| 索尼 | 4949.0000 |
| 联想 | 4226.0000 |
| 苹果 | 2725.0000 |
| 雷蛇 | 299.0000 |
+------------+------------+
8 rows in set (0.00 sec)
-- 查询 多类 平均，最贵，最便宜，几个
mysql> select brand_name,avg(price),max(price),min(price),count(*) from goods group by brand_name;
+------------+------------+------------+------------+----------+
| brand_name | avg(price) | max(price) | min(price) | count(*) |
+------------+------------+------------+------------+----------+
| 华硕 | 3099.0000 | 3399 | 2799 | 2 |
| 唯爱 | 29.0000 | 29 | 29 | 1 |
| 戴尔 | 2899.0000 | 2899 | 2899 | 1 |
| 爱戴尔 | 29.0000 | 29 | 29 | 1 |
| 索尼 | 4949.0000 | 7999 | 1899 | 2 |
| 联想 | 4226.0000 | 4880 | 3499 | 3 |
| 苹果 | 2725.0000 | 3388 | 1999 | 3 |
| 雷蛇 | 299.0000 | 299 | 299 | 1 |
+------------+------------+------------+------------+----------+
8 rows in set (0.00 sec)
-- 小于平均价格的
mysql> select * from goods where price < (select avg(price) from goods);
+----+-----------------------------------+--------------+------------+-------+---------+------------+
| id | name | cate_name | brand_name | price | is_show | is_saleoff |
+----+-----------------------------------+--------------+------------+-------+---------+------------+
| 2 | x550cc 15.6英寸笔记本 | 笔记本 | 华硕 | 2799 | | |
| 6 | ipad mini 7.9英寸平板电脑 | 平板电脑 | 苹果 | 1999 | | |
| 8 | iPad mini 配置 retine 显示屏 | 平板电脑 | 苹果 | 2788 | | |
| 11 | 15.6 寸电脑屏保护膜 | 电脑配件 | 爱戴尔 | 29 | | |
| 12 | 优雅 复古 无线鼠标键盘 | 电脑配件 | 雷蛇 | 299 | | |
| 13 | 15寸 4K 液晶显示屏 | 电脑配件 | 索尼 | 1899 | | |
| 14 | 限量款 LOL 鼠标垫 | 电脑配件 | 唯爱 | 29 | | |
+----+-----------------------------------+--------------+------------+-------+---------+------------+
7 rows in set (0.00 sec)
4.进阶 查询 join
-- 使用 left join 查询每类（品牌）最贵的商品信息
-- 第一：
mysql> select brand_name,max(price) as max_price from goods group by brand_name;
+------------+-----------+
| brand_name | max_price |
+------------+-----------+
| 华硕 | 3399 |
| 唯爱 | 29 |
| 戴尔 | 2899 |
| 爱戴尔 | 29 |
| 索尼 | 7999 |
| 联想 | 4880 |
| 苹果 | 3388 |
| 雷蛇 | 299 |
+------------+-----------+
8 rows in set (0.47 sec)
-- ×代码
select *
from (select brand_name,max(price) as max_price from goods group by brand_name) as b_new
left join goods as g
on b_new.brand_name=g.brand_name and b_new.max_price=g.price;
mysql> select *
-> from (select brand_name,max(price) as max_price from goods group by brand_name) as b_new
-> left join goods as g
-> on b_new.brand_name=g.brand_name and b_new.max_price=g.price;
+------------+-----------+------+----------------------------------+--------------+------------+-------+---------+------------+
| brand_name | max_price | id | name | cate_name | brand_name | price | is_show | is_saleoff |
+------------+-----------+------+----------------------------------+--------------+------------+-------+---------+------------+
| 华硕 | 3399 | 1 | r510vc 15.6英寸笔记本 | 笔记本 | 华硕 | 3399 | | |
| 联想 | 4880 | 3 | x240 超极本 | 超极本 | 联想 | 4880 | | |
| 索尼 | 7999 | 5 | svp13226scb 触控超级本 | 超级本 | 索尼 | 7999 | | |
| 苹果 | 3388 | 7 | iPad air 9.7英寸平板电脑 | 平板电脑 | 苹果 | 3388 | | |
| 戴尔 | 2899 | 10 | vostro 3800-r1206 台式电脑 | 台式机 | 戴尔 | 2899 | | |
| 爱戴尔 | 29 | 11 | 15.6 寸电脑屏保护膜 | 电脑配件 | 爱戴尔 | 29 | | |
| 雷蛇 | 299 | 12 | 优雅 复古 无线鼠标键盘 | 电脑配件 | 雷蛇 | 299 | | |
| 唯爱 | 29 | 14 | 限量款 LOL 鼠标垫 | 电脑配件 | 唯爱 | 29 | | |
+------------+-----------+------+----------------------------------+--------------+------------+-------+---------+------------+
8 rows in set (0.00 sec)
5.其他
四.修改
1.拆表
拆表 与其他有关操作
-- 拆表 将商品类型 和 商品品牌 拆出成新的表格
-- 1.新建 table (一个goods_cates 一个goods_brands)
mysql> create table if not exists goods_cates(
-> id int unsigned primary key auto_increment,
-> name varchar(40) not null
-> );
Query OK, 0 rows affected (0.49 sec)
mysql> create table if not exists goods_brands(
-< id int unsigned primary key auto_increment,
-< name varchar(40) not null );
Query OK, 0 rows affected (0.35 sec)
mysql> show tables;
+---------------------+
| Tables_in_jing_dong |
+---------------------+
| goods |
| goods_brands |
| goods_cates |
+---------------------+
3 rows in set (0.00 sec)
-- 2. 添加信息
mysql> insert into goods_cates (name) select cate_name from goods group by cate_name;
Query OK, 6 rows affected (0.07 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> insert into goods_brands (name) select brand_name from goods group by brand_name;
Query OK, 8 rows affected (0.06 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> select * from goods_cates;
+----+--------------+
| id | name |
+----+--------------+
| 1 | 台式机 |
| 2 | 平板电脑 |
| 3 | 电脑配件 |
| 4 | 笔记本 |
| 5 | 超极本 |
| 6 | 超级本 |
+----+--------------+
6 rows in set (0.00 sec)
mysql> select * from goods_brands;
+----+-----------+
| id | name |
+----+-----------+
| 1 | 华硕 |
| 2 | 唯爱 |
| 3 | 戴尔 |
| 4 | 爱戴尔 |
| 5 | 索尼 |
| 6 | 联想 |
| 7 | 苹果 |
| 8 | 雷蛇 |
+----+-----------+
8 rows in set (0.00 sec)
-- 3.换参数
-- 将 goods表中的cate_name 换成goods_cates 的id
mysql> mysql> update goods as g inner join goods_cates as c on g.cate_name=c.name set g.cate_name=c.id;
Query OK, 14 rows affected (0.07 sec)
Rows matched: 14 Changed: 14 Warnings: 0
mysql> select * from goods;
+----+--------
最低0.47元/天 解锁文章
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
