创建京东数据库
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);
数据库设计
创建商品分类表
create table goods_cates(
id int unsigned primary key auto_increment not null,
name varchar(40) not null
);
创建商品品牌表
create table goods_brands (
id int unsigned primary key auto_increment not null,
name varchar(40) not null
);
创建商品表
create table goods(
id int unsigned 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_cates(id),
foreign key(brand_id) references goods_brands(id)
);
创建顾客表
create table customer(
id int unsigned auto_increment primary key not null,
name varchar(30) not null,
addr varchar(100),
tel varchar(11) not null
);
创建订单表
create table orders(
id int unsigned auto_increment primary key not null,
order_date_time datetime not null,
customer_id int unsigned,
foreign key(customer_id) references customer(id)
);
创建订单状态表
create table order_detail(
id int unsigned auto_increment primary key not null,
order_id int unsigned not null,
goods_id int unsigned not null,
quantity tinyint unsigned not null,
foreign key(order_id) references orders(id),
foreign key(goods_id) references goods(id)
);
import pymysql
if __name__ == '__main__':
conn = pymysql.connect(host='',port=3306,database='jing_dong',user='root',password='123456',charset='utf8')
cursor = conn.cursor()
# 增加
count = cursor.execute('insert into goods(name,cate_name,brand_name) values("硬盘","","")')
print(count)
count = cursor.execute('insert into goods(name) values("光盘")')
print(count)
# 更新
count = cursor.execute('update goods set name="机械硬盘" where name="硬盘"')
# 删除
count = cursor.execute('delete from goods where id=6')
print(count)
conn.commit()
cursor.close()
conn.close()
查询一条数据
import pymysql
if __name__ == '__main__':
conn = pymysql.connect(host='',port=3306,database='jing_dong',user='root',password='123456',charset='utf8')
cursor = conn.cursor()
count = cursor.execute('select id,name from goods where id<=4')
print(count)
for i in range(count):
ret = cursor.fetchone()
print(ret)
conn.commit()
cursor.close()
conn.close()
查询多条数据
import pymysql
if __name__ == '__main__':
conn = pymysql.connect(host='',port=3306,database='jing_dong',user='root',password='123456',charset='utf8')
cursor = conn.cursor()
count = cursor.execute('select id,name from goods where id<=4')
print(count)
ret = cursor.fetchall()
print(ret)
conn.commit()
cursor.close()
conn.close()
参数化
sql语句的参数化，可以有效防止sql注入
import pymysql
if __name__ == '__main__':
conn = pymysql.connect(host='',port=3306,database='jing_dong',user='root',password='123456',charset='utf8')
cursor = conn.cursor()
count = cursor.execute('select id,name from goods where id<=%s',4)
print(count)
ret = cursor.fetchall()
print(ret)
conn.commit()
cursor.close()
conn.close()
数据库
发布于：2021-06-26 11:13
|
阅读数：511
|
评论：0
QQ好友和群
QQ空间
