数据库笔记(4)SQL练习题及Python与数据库交互

最新推荐文章于 2022-10-18 14:10:09 发布
最新推荐文章于 2022-10-18 14:10:09 发布
阅读量731 收藏 7
点赞数 1
分类专栏： 数据库 文章标签： SQL练习 python交互
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_44282382/article/details/90141871
版权

一、数据的准备

1.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
);

1.2插入数据

--向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 显示屏','平板电脑','苹果','27888',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);

二、sql演练

2.1语句强化

1.查询类型cate_name为‘超级本’的商品名称、价格
2.显示商品种类
3.求所有电脑产品的平均价格，并保留两位小数
4.显示每种商品的平均价格
5.查询每种类型的商品中 最贵、最便宜、平均价、数量
6.查询所有价格大于平均价格的商品，并且按降序排列
7.查询每种类型中最贵的电脑信息
更多mysql练习

#1.查询类型cate_name为‘超级本’的商品名称、价格
select name as 商品名称,price as 商品价格 from goods 
where cate_name="超级本";

#2.显示商品种类,下面两种都可以
select  distinct cate_name from goods;
select cate_name from goods group by cate_name;

#3.求所有电脑产品的平均价格，并保留两位小数
select round(avg(price),2) from goods;

#4.显示每种类型商品的平均价格
select cate_name,avg(price) from goods group by cate_name;

#5.查询每种类型的商品中  最贵、最便宜、平均价、数量
select cate_name,max(price),min(price),avg(price),count(*) from goods group by cate_name;

#6.查询所有价格大于平均价格的商品，并且按降序排列
select name,price from goods 
where price>(select round(avg(price),2) from goods)
order by price desc;

#7.查询每种类型中最贵的电脑信息
select g_new.cate_name,g.name,g.price  
from (select cate_name,max(price) as max_price from goods group by cate_name) as g_new 
left join goods as g 
on g_new.cate_name=g.cate_name and g_new.max_price=g.price ;

在这里插入图片描述

2.2创建商品表

-创建商品分类表，！！如果不存在才可以建
create table if not exists goods_cates(
	id int unsigned primary key auto_increment,
	name varchar(40) not null
);
--查询商品表中的商品的种类
select cate_name from goods group by cate_name;
--将商品的种类插入新表中
insert into goods_cates (name) select cate_name from goods group by cate_name;

2.3同步表数据

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

2.4创建“商品品牌表”表

CREATE TABLE `goods_brands` (
   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
   `name` varchar(40) NOT NULL,
   PRIMARY KEY (`id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8；

2.5 同步品牌表数据

update goods as g inner join goods_brands as b on g.brand_name=b.name set g.brand_name=b.id;

2.6修改表结构

  • 查看goods数据表结构，会发现cate_name和brand_name对应的类型为 varchar 但是存储的都是数字
  • 通过alter table语句修改表结构
desc goods;
alter table goods
change cate_name cate_id int unsigned not null,
change brand_name brand_id int unsigned not null;

2.7 外键

三、python与数据库交互

下面是使用python 访问数据库流程图

#第一步，链接数据库
conn = connect(host=‘localhost’,port=3306,user=‘root’,password=‘1111’,database=‘jing_dong’,charset=‘utf8’)
#第二步，获取游标对象
cursor = conn.cursor()
#第三步，通过execute执行SQL语句，进行增删改查
cursor.execute(“select * from goods;”)
#如果是个查询语句，她就在这个游标对象中存着
#以下三条是逐一取出
#查询一行数据，返回的是元组
line_content = cursor.fetchone()
#查询几行数据，返回的是元组套元组
lines = cursor.fetchmany(3)
#取出剩余的所有数据
line_all =cursor.fetchmany()
#第四步，关闭游标，关闭链接
cursor.close()
conn.close()

3.1 查询模板

import pymysql

def main():
	#创建Connection连接
	conn = connect(host='localhost',port=3306,user='root',password='****',database='jing_dong',charset='utf8')
	#获得Cursor对象
	cs1 = conn.cursor()
	#执行select语句，并返回受影响的行数：查询一条数据
	count = cs1.execute('select id,name from goods where id>=4')
	#打印受影响的行数
	print("查询到%d条数据："%count)
	
	for i in range(count):
		#获取查询的结果
		result = cs1.fetchone()
		#打印查询的结果
		print(result)
		#获取查询的结果
	
	#关闭Cursor对象
	cs1.close()
	conn.close()

__name__=='__main__'
	main()

补充：

游标：游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果。
用户可以用SQL语句逐一从游标中获取记录，并赋值给主变量，交由python进一步处理，一组主变量一次只能存放一条记录。

  • 仅使用主变量并不能完全满足SQL语句向应用程序输出数据的要求

  • 游标和游标的优点：在数据库中，游标是一个十分重要的概念。游标提供了一种从表中检索出的数据进行操作的灵活手段，就本质而言，游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。游标总是与一条SQL选择语句相关联因为游标由结果集（可以是零条，一条或由相关的选择语句检索出的多条记录）和结果集中指向特定记录的游标位置组成。当决定对结果进行处理时，必须声明一个指向该结果的游标。

3.1.1京东商城查询实战

from pymysql import connect

class JD(object):
    def __init__(self):
        #创建Connection连接
        self.conn = connect(host='localhost',port=3306,user='root',password='1111',database='jing_dong',charset='utf8')
        #获得Cursor对象
        self.cursor = self.conn.cursor()
 
    def __del__(self):
        #关闭Cursor对象
        self.cursor.close()
        self.conn.close()
        
    def execute_sql(self,sql):
        self.cursor.execute(sql) 
        for temp in self.cursor.fetchall():
            print(temp)
            
    def show_all_items(self):
        """显示所有商品"""
        sql = "select * from goods;"
        self.execute_sql(sql)
        
    def show_cates(self):
        sql = "select name from goods_cates;"
        self.execute_sql(sql)    

    def show_brands(self):
        sql = "select name from goods_brands;"
        self.execute_sql(sql)
        
    @staticmethod   
    def print_menu():
        print("-----京东-----")
        print("1:所有的商品")
        print("2:所有的商品分类")
        print("1:所有的商品品牌分类")
        return input("请输入功能对应的序号：")        
    
    def run(self):
        while True:
            num = self.print_menu()
            if num == "1":
                #查询所有的商品
                self.show_all_items()
            elif num =="2":
                #查询分类
                self.show_cates()
            elif num =="3":
                #查询品牌分类
                self.show_brands()
            else:
                print("输入有误，重新输入.....")
        
def main():
    #1.创建一个京东商城对象
    jd = JD()
    
    #2.调用这个函数的run方法，让其运行
    jd.run()
        
if __name__ == "__main__":
    main()

3.2增、删、改模板

import pymysql

def main():
	#创建Connection连接
	conn = connect(host='localhost',port=3306,user='root',password='****',database='jing_dong',charset='utf8')
	#获得Cursor对象
	cs1 = conn.cursor()
	#执行select语句，并返回受影响的行数：添加一条数据
	count = cs1.execute('insent into goods_cates(name) values("硬盘")')
	#打印受影响的行数
	print(count)
	
	count = cs1.execute('insent into goods_cates(name) values("光盘")')
	print(count)
		
	##更新
	# count = cs1.execute('upset goods_cate set name="机械键盘" where name="硬盘"')
	##删除
	#  count = cs1.execute('delete from goods_cates where id=6')
	
    **提交之前的操作，如果之前已经执行过多次execute，那么就都进行提交**
	conn.commit()
	
	#关闭Cursor对象
	cs1.close()
	conn.close()

__name__=='__main__'
	main()
  • 注比查询多一个conn.commit()的过程，如果操作的需要重新提交，可以用conn.rollback()进行回滚
xiaoqian_ww
关注
  • 1
    点赞
  • 踩
  • 7
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Linux课堂笔记及练习题和答案.zip
09-05
在本压缩包“Linux课堂笔记及练习题和答案.zip”中，您将获得一份全面的Linux学习资源，包括详尽的课堂笔记以及Python练习题，这些资料对于想要深入理解和掌握Linux系统及其相关技能的初学者或者希望巩固基础的IT...
python培训班笔记
09-06
在培训班笔记中，这些主题可能还包括实际的示例代码、练习题和项目，以便学员能够将理论知识应用到实践中。通过深入研究这些笔记，你不仅可以掌握Python的基本概念，还能了解到如何在实际问题中运用这些概念，从而...
【python练习】python与mysql交互示例
aineko520的专栏
05-17 216
今天进行了python与mysql交互，可以实现使用python操作SQL语言而得出想要的查询结果，练习代码如下： from pymysql import * class JD(object): def __init__(self): #创建Connection连接 self.conn = connect(host='192.168.190.130',port=3306,use...
Python MySQL数据库交互
qq_43629857的博客
03-11 3833
利用 PyMySQL 数据库驱动，实现 MySQL 数据库的增删改查及事务处理
Mysql之in 小知识点
toomemetoo的博客
05-23 479
使用IN关键字检索数据表中指定的数据内容。 带IN关键字的查询 IN关键字被用在WHERE语句的后边，用来过滤你所需要查询的内容。更形象的说，IN关键字的使用情形就像点名，点到谁谁就要站出来。 语法规则为： SELECT 字段名 FROM 表名 WHERE 字段名 IN (n1,n2,n3,…); 。 其中，括号内的数字必须为INT格式的数字。被“点到名”的这些括号里数字对应的内容，都要乖乖的站到前边来展示给大家看。 举个例子 假设我们现在有一张表Mall_products，内容如下： 我们会使用IN关
MySQL语法练习---常见题型练习
weixin_48453178的博客
08-24 1639
前言 这些都是在Navicat上实现的 一、创建表 CREATE TABLE products ( id int(11) NOT NULL AUTO_INCREMENT, name varchar(40) DEFAULT NULL, price double DEFAULT NULL, category varchar(40) DEFAULT NULL, pnum int(11) DEFAULT NULL, description varchar(255) DEFAULT NULL, PRIMARY KEY
learning-mongodb：使用MongoDB数据库发现和学习
02-04
首先，MongoDB是NoSQL数据库的一个代表，它采用的是键值对、文档、集合和数据库的结构，这与传统的SQL数据库有很大不同。文档是MongoDB中的基本数据单位，通常以JSON（JavaScript Object Notation）格式存储，支持...
Python:视频课程和URI练习
04-14
在"Python-main"这个文件夹中，可能包含了课程的笔记、代码示例、练习题及答案。学习者应该按照视频课程的顺序逐步学习，同时通过URI练习来巩固和拓展理论知识。完成这些练习后，学习者可以期待在Python编程方面有...
Veronica:Veronica SQL考试
03-06
对于“Veronica-main”这个文件，它可能包含了Veronica SQL课程或考试的源代码、练习题、解决方案、笔记或其他相关资源。通过深入研究这些文件，你可以进一步掌握SQL的基础和高级用法，以及如何在Python环境中高效地...
使用python爬取淘宝商品信息
12-21
1.首先要安装两个库，requests和re库 2.定义一个获取页面的函数 ef getHTMLText(url): kv = {'cookie'：'淘宝页面的cookie'} try: r = requests.get(url, headers=kv,timeout=30) r.raise_for_status() r.encoding = r.apparent_encoding return r.text except: return "" 浏览器查看cookie的方法，摁下F2，在网
数据库学习（十）— 查询演练
噗噗bug博客
12-01 2833
drop table if exists goods; create table goods ( id int unsigned primary key auto_increment, name varchar(150), cate varchar(40), brand_name varchar(40) , price decimal(10,3) default 0, is_show bit default 1, is_saleoff bit default 0 ); insert .
python版超市信息管理系统源代码，基于tkinter带界面
最新发布
Python代码大全
10-18 3124
所用技术：SQLite3,tkinter,openpyxl,pandas。使用Java开发中的MVC思想，代码虽有冗余，但更容易维护。默认账号：admin，密码：123456。
MySql简单操作总结
zl15145428的博客
08-05 442
MySql简单操作总结
html锚点的具体代码（包含return top）
whatidonot的博客
10-20 1055
<!DOCTYPE html> <html> <head> <meta charset="utf-8"> <title></title> </head> <body> <h5><a name="m6"></a></h5> <a href="#m1">产品一</a> <a href="#m2">产品二&lt
搜索商品列表SQL
12november的博客
07-24 546
SELECT i.id as itemId, i.item_name as itemName, i.sell_counts as sellCounts, ii.url as imgUrl, tempSpec.price_discount as price FROM items i LEFT JOIN ...
MySQL子查询关联商品表和订单表
FellowYourHeart的博客
08-30 5316
      在撸代码写程序的时候，免不了要用到MySQL关联表查询语句，而今天在做商品订单销量排行的时候，就更用到MySQL子查询了。子查询，说的通俗一点就是把一个查询得到的结果，作为查询条件嵌套在另一个查询当中，输出结果。如下： //goods是商品表，order是订单表 //子查询语句 SELECT DISTINCT goods_id FROM `order` GROUP BY goods...
mysql数据库练习题
林中明月间丶
11-08 7609
有一段时间想好好的练习一下数据库 就去网上找了好多练习题目。但是这些练习题目都感觉好少 所以我把我搞到的所有数据库练习题 综合了一下起来。喜欢的小好伙伴们赶紧收藏好了撒。一次够你练习几个小时了 别坚持不下去了撒   那我就贴出来了 //登录和退出数据库 1：mysql -u root -p//输入密码 2: exit 3: mysql -u root -p create
商品模块数据库设计
baidu_36681154的博客
01-25 2626
1创建数据库 create database shopcz charset utf8; 2选择数据库 use shopcz; 商品模块创建商品类别表 create table cz_category(     cat_id smallint unsigned not null auto_increment primary key comment '商品类别ID',     cat_name v...
数据库笔记(3)数据库三大范式
weixin_44282382的博客
05-12 3583
数据库设计 关系型数据库建议在E_R模型的基础上，我们需要根据产品经理的设计策划，抽取出来模型与关系，制定出表结构，这是项目开始的第一部 在开发时有很多设计数据库软件，常用的如power designer，db desigener等，这些软件可以直观地实体及实体间的关系 设计数据库，可能由专门的数据库设计人员完成，也可能是项目经理带组员完成 现阶段不需要独立完成数据库设计 三范式 经过研究和...
数据库复习笔记综合整理1
08-03
数据库复习笔记是关于数据库系统的应用和相关技术的综合整理。本文主要内容包括：引言、数据库视图、数据库语言、关系数据库、数据库设计、数据存储和查询、事务、数据库用户和管理员、关系模型介绍、初级SQL等。 ...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值