Mysql基础-MySQL&Python

1.MySQL强化

1.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 
                  );
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.2查询数据

-- 查询每个品牌最贵的产品
select group_concat(name) 产品名称,brand_name 品牌,
max(price) 价格 from goods group by brand_name;

-- 查看所有的产品种类
select distinct cate_name 类型 from goods; 

-- 将超级本修正为超极本
update goods set cate_name="超极本" where cate_name="超级本"; 

-- 查看各个种类的产品数据，并按价格排序
select a.name 名称,b.cate_name 类型,a.price 价格 
       from goods as a inner join 
        (select cate_name from goods group by cate_name having cate_name="超极本") as b 
           on a.cate_name=b.cate_name order by a.price;
           
-- 查看小于平均价格的电脑配件
select name 名称,cate_name 类型,price 价格 
    from goods where 
    price<(select avg(price) from goods group by cate_name having cate_name="电脑配件") 
    and cate_name="电脑配件" order by price;

1.3拆分数据表

-- 首先建立一个类型表单，将类型添加到表单
create table if not exists name_type (
    id int(10) unsigned not null primary key auto_increment,
    name varchar(20) not null 
) select distinct cate_name as name from goods;
#PS:起的别名一定要对应新建表中的字段名

-- 将原表中的名称换成id
update goods as a inner join name_type as b on a.cate_name=b.name set a.cate_name=b.id;

-- 修改字段类型
alter table goods change cate_name name_id int(10) unsigned;
-- 查看修改后的信息
desc goods;
select * from goods;

-- 外键关联
alter table goods add foreign key(name_id) references name_type(id);
-- 删除外键，数据过多时，外键会降低效率，因为一般数据存储在硬盘中，程序运行在内存里，程序判断要快的多。
-- 查看外键信息,在字段最后多出来一个外键goods_ibfk_1
show create table goods;
-- 删除外键
alter table goods drop foreign key goods_ibfk_1;

1.4数据备份

-- 在shell里执行，不登录数据库
-- 备份指定库 mysqldump -uroot -p`密码` `库` > `目标地址`
mysqldump -uroot -p****** jing_dong > C:\Users\Administrator\Desktop\jd.sql

返回信息：mysqldump: [Warning] Using a password on the command line interface can be insecure.

-- 备份所有内容
mysqldump -uroot -p****** --all-databases --lock-all-tables> C:\Users\Administrator\Desktop\al.sql

2.Python调用

# 使用pycharm先添加pymysql的package
from pymysql import * 
# 定义一个简单的查询函数
def search ():
    #创建connection连接，输入端口，用户名，密码和使用的库
    conn = connect(host='localhost',port=3306,user='root',\
                   password='password',database='jing_dong',charset='utf8')
    #获得游标cursor对象
    cs1=conn.cursor()
    #以上两句为开头固定使用
    
    #执行sql的查询语句,返回值为数据数
    count=cs1.execute('select id,name from goods where id >=4')
    print("查询到%d条数据"%count)
    #打印所有的数据
    for i in range(count):
        #获取查询的结果
        result = cs1.fetchone()
        print(result)
        
    #关闭游标对象和连接
    cs1.close()
    conn.close()
    #最后两句固定
if __name__ =='__main__':
    search()

3.查询练习

-- MySQL
-- 添加一个用户密码表
create table if not exists user(
    name varchar(10) not null primary key unique,
    pass varchar(20) default="000000");
-- 添加一名用户
insert into user values("用户名","密码");

# Python
# 使用pymysql包
# connect()连接库  connect().cursor() 连接游标
# connect().close() 断开库连接  connect().cursor.close() 断开游标连接
# connect().cursor.execute(`sql代码`) 向数据库发送指令
# connect().cursor.fetchone()/fetchmany(num)/fetchall() 得到查询的一条/num条/所有的信息
'''
功能实现基本要求：
1.能够用户登录
2.用户能够查询所有商品信息
3.用户可以查询所有品牌
4.用户可以查询指定品牌商品信息
5.用户可以查询指定品牌的平均价格
'''
from pymysql import connect
# 一般类名大写
class JD(object):
    #类下函数调用开始前连接数据库
    def __init__(self):
        # 创建connection连接，输入端口，用户名，密码和使用的库
        self.conn = connect(host='localhost', port=3306, user='root', \
                       password='******', database='jing_dong', charset='utf8')
        # 获得游标cursor对象
        self.cursor = self.conn.cursor()
    #类下函数调用结束后关闭数据库
    def __del__(self):
        # 关闭游标对象和连接
        self.cursor.close()
        self.conn.close()
    #用户登录    
    def link(self):
        while True:
            # 链接用户表，查询用户是否存在
            user_name=input('请输入用户名（回车确认）：')
            count = self.cursor.execute('select name from user where name="%s";'% user_name)
            # 在引号内还存在引号的情况，最外面括号最好是""" %s """
            # 用户存在对照密码，验证是否正确
            if count:
                user_pass = input('请输入密码（回车确认）：')
                self.cursor.execute('select pass from user where name="%s";' % user_name)
                if self.cursor.fetchone()[0]==user_pass:
                    print("登录成功")
                    break
                else:
                    print("请重新尝试")
            # 用户不存在，是否添加信息
            else:
                print("无用户名")
    #1.查询所有商品            
    def search_all(self):
        self.cursor.execute('select name 名称,brand_name 品牌,price 价格 from goods;')
        for i in self.cursor.fetchall():
            print(i)
    #2.查询所有品牌        
    def search_brand(self):
        self.cursor.execute('select distinct brand_name from goods;')
        for i in self.cursor.fetchall():
            print(i)
    #3.查询一个品牌的所有商品        
    def search_brand_all(self):
        brand = input("品牌（回车确认）：")
        self.cursor.execute('select name 名称,price 价格 from goods where brand_name="%s";'%brand)
        for i in self.cursor.fetchall():
            print(i)
    #4.查询某一品牌商品的平均价格        
    def search_price(self):
        brand = input("品牌（回车确认）：")
        self.cursor.execute('select avg(price)  from goods where brand_name="%s";'%brand)
        print("%s的价格是%f"%(brand,self.cursor.fetchone()[0]))
    #打印菜单,静态类放在静态块
    @staticmethod
    def mune_print():
        print("-------欢迎--------")
        print("#1.查询所有商品")
        print("#2.查询所有品牌")
        print("#3.查询一个品牌的所有商品")
        print("#4.查询某一品牌商品的平均价格")
        print("#5.退出")
        return input("请输入指令序号（回车确认）：")
    #串联功能
    def run(self):
        self.link()
        while True:
            user_cmd = self.mune_print()
            if user_cmd=="1":
                self.search_all()
            elif user_cmd=="2":
                self.search_brand()
            elif user_cmd=="3":
                self.search_brand_all()
            elif user_cmd=="4":
                self.search_price()
            elif user_cmd=="5":
                break
            else:
                pass
#实现功能
def main():
    # 创建京东商城对象
    jd = JD()
    # 调用对象run方法，让其运行
    jd.run()
    print("退出成功")
if __name__ == '__main__':
    main()

4.增删改练习

# python
# 增删改操作相对于查询操作来说，改动了数据库
# 新增方法connect().commit() 提交 connect().rollback() 回滚
# 对于输入的增删改指令，不会直接生效，commit之后才会生效
# rollback会回滚到上次commit后
'''
在查询功能下，新添加功能
1.不存在用户提供注册
2.向商品列表添加新商品
3.向品牌列表添加新商品类型
4.修改商品价格
5.删除某个商品
'''
from pymysql import connect
# 一般类名大写
class JD(object):
    # 类下函数调用开始前连接数据库
    def __init__(self):
        # 创建connection连接，输入端口，用户名，密码和使用的库
        self.conn = connect(host='localhost', port=3306, user='root', \
                            password='password', database='jing_dong', charset='utf8')
        # 获得游标cursor对象
        self.cursor = self.conn.cursor()

    # 类下函数调用结束后关闭数据库
    def __del__(self):
        # 关闭游标对象和连接
        self.cursor.close()
        self.conn.close()
    # 用户登录
    def link(self):
        while True:
            # 链接用户表，查询用户是否存在
            user_name = input('请输入用户名（回车确认）：')
            count = self.cursor.execute("""select name from user where name="%s";""" % user_name)
            # 在引号内还存在引号的情况，最外面括号最好是""" %s """
            # 用户存在对照密码，验证是否正确
            if count:
                user_pass = input('请输入密码（回车确认）：')
                self.cursor.execute('select pass from user where name=%s;', [user_name])
                # -------------->PS:简单防止sql注入，如果自己先拼好sql代码有可能会被识别成其他代码，但函数自己拼装不会
                if self.cursor.fetchone()[0] == user_pass:
                    print("登录成功")
                    break
                else:
                    print("请重新尝试")
            # 用户不存在，是否添加信息
            else:
# ---------------------->添加用户注册<-----------------------#
                print('无用户名--->0.返回\n---------->1.注册')
                user_cmd = input("请输入指令（回车确认）：")
                if user_cmd == "0":
                    pass
                elif user_cmd == "1":
                    user_pass = input('请输入密码（回车确认）：')
                    self.cursor.execute('insert into user values(%s,%s);', [user_name, user_pass])
                    self.conn.commit()
# ---------------------------------------------------------#
    # 1.查询所有商品
    def search_all(self):
        self.cursor.execute('select name 名称,brand_name 品牌,price 价格 from goods;')
        for i in self.cursor.fetchall():
            print(i)
    # 2.查询所有品牌
    def search_brand(self):
        self.cursor.execute('select distinct brand_name from goods;')
        for i in self.cursor.fetchall():
            print(i)
    # 3.查询一个品牌的所有商品
    def search_brand_all(self):
        brand = input("品牌（回车确认）：")
        self.cursor.execute('select name 名称,price 价格 from goods where brand_name=%s;', [brand])
        for i in self.cursor.fetchall():
            print(i)
    # 4.查询某一品牌商品的平均价格
    def search_price(self):
        brand = input("品牌（回车确认）：")
        self.cursor.execute('select avg(price)  from goods where brand_name=%s;',[brand])
        print("%s的价格是%.2f" % (brand, self.cursor.fetchone()[0]))
# --------------->添加新添功能说明<-----------------#
    # 5.向商品列表添加新商品
    def add_one(self):
        goods_name = input("添加商品名称（回车确认）：")
        goods_brand = input("添加商品品牌（回车确认）：")
        goods_type = input("添加商品类型（回车确认）：")
        goods_price = round(float(input("添加商品价格（回车确认）：")),2)
        self.cursor.execute('select id from name_type where name=%s', [goods_type])
        goods_type_id = self.cursor.fetchone()[0]
        count = self.cursor.execute(\
               'insert into goods(name,name_id,brand_name,price) values(%s,%s,%s,%s);',\
                   [goods_name,goods_type_id, goods_brand, goods_price])
#------------------->指令中只用%s即可,无需关注类型
        self.conn.commit()
        print("成功添加%d行" % count)
    # 6.向品牌列表添加商品类型
    def add_type(self):
        goods_type = input("添加商品类型（回车确认）：")
        count = self.cursor.execute('select * from name_type where name = %s;', [goods_type])
        if count:
            print("添加失败，已存在")
        else:
            count = self.cursor.execute('insert into name_type values(0,%s) ;', [goods_type])
            self.conn.commit()
            print("成功添加%d行" % count)
    # 7.修改商品价格
    def update_price(self):
        goods_name = input("商品名称（回车确认）：")
        goods_price = round(float(input("新商品价格（回车确认）：")),2)
        count = self.cursor.execute('update goods set price=%s where name=%s;',\
                                    [goods_price, goods_name])
        self.conn.commit()
        print("成功修改%d行" % count)
    # 8.删除某个商品
    def drop_one(self):
        goods_name = input("删除商品名称（回车确认）：")
        count = self.cursor.execute('delete from goods where name=%s;',[goods_name])
        self.conn.commit()
        print("成功删除%d行" % count)
# -----------------------------------------------#
    # 打印菜单,静态类放在静态块
    @staticmethod
    def mune_print():
        print("-------欢迎--------")
        print("#1.查询所有商品")
        print("#2.查询所有品牌")
        print("#3.查询一个品牌的所有商品")
        print("#4.查询某一品牌商品的平均价格")
# --------------->添加新添功能说明<-----------------#
        print("#5.向商品列表添加新商品")
        print("#6.向品牌列表添加商品类型")
        print("#7.修改商品价格")
        print("#8.删除某个商品")
# -----------------------------------------------#
        print("#9.退出")
        return input("请输入指令序号（回车确认）：")

    # 串联功能
    def run(self):
        self.link()
        while True:
            user_cmd = self.mune_print()
            if user_cmd == "1":
                self.search_all()
            elif user_cmd == "2":
                self.search_brand()
            elif user_cmd == "3":
                self.search_brand_all()
            elif user_cmd == "4":
                self.search_price()
# --------------->添加新添功能调用<-----------------#
            elif user_cmd == "5":
                self.add_one()
            elif user_cmd == "6":
                self.add_type()
            elif user_cmd == "7":
                self.update_price()
            elif user_cmd == "8":
                self.drop_one()
# -----------------------------------------------#
            elif user_cmd == "9":
                break
            else:
                pass
# 实现功能
def main():
    # 创建京东商城对象
    jd = JD()
    # 调用对象run方法，让其运行
    jd.run()
    print("退出成功")

if __name__ == '__main__':
    main()