1、查看数据库的版本

2、查看当前用户

3、查看当前数据库

4、计算表达式的结果; 任何一个数据库,无论大小,都首先是一个超级计算器

5、查看当前MySQL环境中所有的数据库; 系统数据库(只能看)和自定义数据库(任何操作)

6、先建数据库 gaoming

7、如果表已经存在,则创建不能成功

8、创建表的内容

9、查询表结构

10、向表中添加记录

11、查询表记录

12、ming.py

import json
import random
import time

import pymysql
from pymysql.constants import CLIENT

'''
  生成一个随机整数，如果无参，则返回100以内的整数
  如果传递了参数，则返回0-参数间的随机整数
'''
def rnd(value=100):
    return int(random.random() * value)
'''
  输入两个整数参数，返回两数之间的随机值
'''
def rd(a=0, b=100):
    return int(a + random.random() * (b - a))
'''
  一个通用的父类，主要是在输出类对象时，以JSON的方式输出
'''
def tojson(dic):
    return json.dumps(dic, indent=4, ensure_ascii=False,
                      default=lambda o: o.__dict__)
class BaseClass(object):
    def __str__(self):
        return json.dumps(self, indent=4,
                          ensure_ascii=False,
                          default=lambda obj: obj.__dict__)
'''
  以 10 进制表示的RGB 颜色
'''
def rgb():
    return 'rgb(' + str(random.randint(0, 255)) + "," + str(
        random.randint(0, 255)) + ',' + str(
        random.randint(0, 255)) + ")"
'''
  16 进制颜色
'''
def hexcolor():
    cstr = 'ABCDEF01234567890'
    rr = '#'
    for i in range(6):
        rr += cstr[rnd(len(cstr))]
    return rr
'''
  一个用于统计函数运行时间的装饰器类
'''
class MyTimer:
    def __init__(self, func):
        self.func = func
    def __call__(self, *args, **kwargs):
        begin = time.time()
        val = self.func(*args, **kwargs)
        end = time.time()
        print(f'用时 : {end - begin} 秒!')
        if val is not None:
            print('->', val)
'''
  一个用于带异常处理函数的装饰器类
'''
class ExpTry:
    def __init__(self, func):
        self.func = func
    def __call__(self, *args, **kwargs):
        try:
            ret = self.func(*args, **kwargs)
            if ret is not None:
                print('返回 ->', ret)
        except Exception as err:
            print('出错信息: ', err)
        return
'''
  一个用于统计时间的装饰器类
'''
class TimeIt:
    def __init__(self, prefix, postfix):
        self.prefix = prefix
        self.postfix = postfix
    def __call__(self, func):
        def wrapper(*args, **kwargs):
            start = time.time()
            val = func(*args, **kwargs)
            if val is not None:
                print('返回 ->', val)
            end = time.time()
            print(
                f'{self.prefix} : {end - start} {self.postfix}!')
        return wrapper




def getconn(gaoming):
    return pymysql.connect(
        host='localhost',  # 127.0.0.1  主机
        user='root',  # 用户,使用MySQL时只用这个用户;
        password='root',  # 密码;就是进入MySQL数据库的登录密码
        port=3306,  # 端口号
        database='gaoming',  # 一般为自定义的数据库名称
        client_flag=CLIENT.MULTI_STATEMENTS)
    # 是否支持命令字符串可以包含多条命令

13、在新建数据库中建表

from ming import getconn
conn = getconn('gaoming')
# # 谁来负责执行SQL命令语句? 连接不会直接执行! 而是 通过连接得到的游标;
cs = conn.cursor()
sql = 'drop database if exists  gaoming'
res = cs.execute(sql)
sql = 'create database gaoming'
cs.execute(sql)
print()
sql = 'use gaoming'
cs.execute(sql)
sql = 'drop table if exists person'
cs.execute(sql)
sql = ('create table person(sid int auto_increment primary key,'
       'sname varchar(20) not null,sage int not null)')
cs.execute(sql)
conn.commit()
print("*** ok ***")

向表添加记录

from ming import getconn, rd
from faker import Faker
import random  # 导入random模块

ff = Faker("zh_CN")
conn = getconn("gaoming")
cs = conn.cursor()

# 向表Person中插入一条记录
sql = 'insert into person(sname,sage) values(%s,%s)'
data = [(ff.name(), rd(16, 33)) for k in range(11)]
cs.executemany(sql, data)
conn.commit()

cs.close()
conn.close()
print("*** end  ***")

查看表person的内容：

14、利用With子句批量插入记录

from ming import getconn, rd
from faker import Faker
ff = Faker("zh_CN")
conn = getconn("xiaoming")
cs = conn.cursor()
#  向表Person中插入多条记录;
sql = 'insert into person(sname,sage) values(%s,%s)'
data = [(ff.name(), rd(16, 33)) for k in range(5)]
# cs = conn.cursor()
with conn.cursor() as cs:
        effect = cs.executemany(sql, data)
        conn.commit()
        print("*** 成功插入 %d 行记录! ***" % (effect))

15、删除记录

from ming import getconn, rd
from faker import Faker
ff = Faker("zh_CN")
conn = getconn("gaoming")
# cs = conn.cursor()
#  根据条件删除表中的记录;
sql = 'delete from person where sid > %s and sage > %s'
data = [(10, 25)]
with conn.cursor() as cs:
        effect = cs.executemany(sql, data)
        print("*** 成功删除 %d 行记录! ***" % (effect))
        conn.commit()

16、更新记录

from faker import Faker
from ming import getconn
ff = Faker("zh_CN")
conn = getconn("xiaoming")
#  根据条件更新表中的记录;
sql = 'update person set sage=sage+1 where sid < 27'
with conn.cursor() as cs:
        effect = cs.execute(sql)
        print("*** 成功更新 %d 行记录! ***" % (effect))
        conn.commit()

17、无条件查询;  select * from tablename

dept.py:

class Dept:

    def __init__(self, id, name, description):
        self.id = id

        self.name = name

        self.description = description

    def __str__(self):
        return f"Dept(id={self.id}, name={self.name}, description={self.description})"

from vip.ming import getconn
from vvip.dept import Dept
conn = getconn("gaoming")
# 无条件查询记录:
sql = 'select * from dept'
depts = []
with conn.cursor() as cs:
        res = cs.execute(sql)
        print("*** 共有 %d 条记录 ***" % res)
        # rows = cs.fetchone()
        # print(rows)
        # rows = cs.fetchmany(3)
        # print(rows)
        rows = cs.fetchall()
        for row in rows:
                dept = Dept(row[0], row[1], row[2])
                depts.append(dept)
for dept in depts:
        print(dept)

有条件单行查询; select * from tbname where id=22;

from ming import getconn
conn = getconn("gaoming")
# 有条件查询记录:
sql = 'select empno,ename,job,sal  from emp where empno=7369'
with conn.cursor() as cs:
        res = cs.execute(sql)
        print('res ->', res)
        rr = cs.fetchone()
        for r in rr:
                print('->', r)

有条件多行查询; select * from tbname where id >34;

from vip.ming import getconn
conn = getconn("gaoming")
# 有条件查询多条记录:
sql = ('select empno,ename,job,sal from emp where '
       'empno between %s  and %s')
data = [(7788, 7844)]
with conn.cursor() as cs:
        res = cs.executemany(sql, data)
        print("*** 读取到 %d 行记录! ***" % res)
        rows = cs.fetchall()
        for row in rows:
                print('->', row)

18、导入scott.sql

19、

from ming import getconn
conn = getconn("gaoming")
print()
sql = ("select * from dept;select empno,ename,sal from emp "
       "where empno in (7369, 7788, 7934);"
       "select * from person limit 3")
with conn.cursor() as cs:
        cs.execute(sql)
        rows = cs.fetchall()
        for row in rows:
                print('->', row)
        print()
        cs.nextset()
        rows = cs.fetchall()
        for row in rows:
                print('->', row)
        print()
        cs.nextset()
        rows = cs.fetchall()
        for row in rows:
                print('->', row)

20、

from ming import getconn
conn = getconn("gaoming")
print()
sql = ["select * from dept",
       "select empno,ename,job,sal from emp where empno in (%s,%s)",
       "select * from person limit %s,%s"]
params = [
        (),#查询 `dept` 表中的所有记录，不需要参数
        (7369, 7788),
        (3, 3)#查询 `person` 表中，从第4条开始取3条记录）。

]
cs = conn.cursor()
for cmd, param in zip(sql, params):
        cs.execute(cmd, param)
        rows = cs.fetchall()
        for row in rows:
                print('->', row)
        print()