Educoder头歌--MySQL开发技巧 - 批量数据入库及检索

第1关：MySQL数据库连接

connect方法的参数含义如下：

1. host：数据库主机名，默认是用本地主机
2. user：数据库登陆名，默认是当前用户
3. passwd：数据库登陆的密码，默认为空
4. charset:连接时的编码格式，要求与数据库的编码一致

# coding=utf-8
import pymysql
def connect():
# 请在下面添加连接数据库的代码，完成相应功能
# ###### Begin ######
    conn =pymysql.connect(host='localhost',user='root',password='123123',charset='utf8')
####### End #######
####### 请不要修改以下代码 #######
    return conn.get_host_info()

第2关：数据库与数据表创建

主要是数据表的创建

create table tablename (字段名 字段属性，字段名 字段属性，……)

# coding = utf-8

# 连接数据库，建立游标cursor
import pymysql

def create():
    conn = pymysql.connect(host='localhost', user='root', passwd='123123', charset='utf8')
    cursor = conn.cursor()
    # -----------Begin----------
    # 创建enroll数据库
    cursor.execute('create database enroll')
    conn.select_db('enroll')
    # 创建nudt数据表
    cursor.execute('create table nudt(year int,province varchar(100),firstBatch int,gcMax int,gcMin int,gcMean int,xlMax int,xlMin int,xlMean int)')
    # ------------End-----------

第3关：批量数据入库与检索

用sql = "insert into %s (indata, inNum, brand) values ('%s', '%s', '%s')" % (tablename, '2017-8-19', 1000, 'Chevrolet')时注意tablename要加引号

import pymysql

def insert(year,province,firstBatch,gcMax,gcMin,gcMean,xlMax,xlMin,xlMean):
    conn = pymysql.connect(host='localhost', user='root', passwd='123123', charset='utf8')
    cursor = conn.cursor()
    conn.select_db('enroll')
    # -----------Begin----------
    # 请在下面输入插入数据的语句，完成相应功能
    sql = "insert into %s (year,province,firstBatch,gcMax,gcMin,gcMean,xlMax,xlMin,xlMean) values('%s','%s','%s','%s','%s','%s',%s,%s,%s)"%('nudt',year,province,firstBatch,gcMax,gcMin,gcMean,xlMax,xlMin,xlMean)
    cursor.execute(sql)
    # ------------End-----------
    
    # 提交数据到数据库
    conn.commit()
    # 关闭数据库连接
    cursor.close() 
    conn.close()

def select():
    conn = pymysql.connect(host='localhost', user='root', passwd='123123', charset='utf8')
    cursor = conn.cursor()
    conn.select_db('enroll')
    # -----------Begin----------
    # 请在下面输入查询数据的语句，完成相应功能
    sql = 'select * from %s' % 'nudt'
    cursor.execute(sql)
    # 请在下面输入获取数据的语句，完成相应功能
    records = cursor.fetchall()
    # ------------End-----------
    for record in records:
        print(record)

    # 关闭数据库连接
    cursor.close() 
    conn.close()

第4关：多表创建与连接查询

建表不是重点，主要是让我们掌握多表直接汇总，等值连接和自然连接

多表直接汇总

select * from A, B（A，B为表名）

等值连接

select * from  A, B where A.a = B.a（A，B为表名，a为A,B的共有字段）

自然连接

两表的自然连接可以直接用  select * from A natural join B(A,B为表名）

这里要我们用的语法是

select A.a, A.b, A.c, B.d, B.e from A , B(A,B为表名：a,b,c,d,e为A,B表中的不同字段）

import pymysql

def create(cursor):
    # -----------Begin----------
    # 创建provincialEntryScore表
    sql = 'create table provincialEntryScore(year int,province varchar(100),entryScore int)'
    cursor.execute(sql)
    # 创建nudtTechScore表
    sql = 'create table nudtTechScore(year int,province varchar(100),techMax int,techMin int,techMean int)'
    cursor.execute(sql)
    # 创建nudtMilScore表
    sql = 'create table nudtMilScore(year int,province varchar(100),milMax int,milMin int,milMean int)'
    cursor.execute(sql)
    # ------------End-----------

def insert(cursor,year,province,entryScore,techMax,techMin,techMean,milMax,milMin,milMean):
    # -----------Begin----------
    # 请在下面输入将数据插入provincialEntryScore表中的语句
    sql ="insert into %s (year,province,entryScore) values(%s,'%s',%s)"%('provincialEntryScore',year,province,entryScore)
    cursor.execute(sql)
    # 请在下面输入将数据插入nudtTechScore表中的语句
    sql ="insert into %s (year,province,techMax,techMin,techMean) values(%s,'%s',%s,%s,%s)"%('nudtTechScore',year,province,techMax,techMin,techMean)
    cursor.execute(sql)
    # 请在下面输入将数据插入nudtMilScore表中的语句
    sql ="insert into %s (year,province,milMax,milMin,milMean) values(%s,'%s',%s,%s,%s)"%('nudtMilScore',year,province,milMax,milMin,milMean)
    cursor.execute(sql)
    # ------------End-----------

def selectAll(cursor):
    # -----------Begin----------
    # 请在下面输入多表直接汇总的语句
    sql ='select * from provincialEntryScore, nudtTechScore,nudtMilScore'
    cursor.execute(sql)
    records = cursor.fetchall()
    return records
    # ------------End-----------

def selectEqual(cursor):
    # -----------Begin----------
    # 请在下面输入等值连接的语句
    sql ='select * from provincialEntryScore ,nudtTechScore,nudtMilScore where provincialEntryScore.year = nudtTechScore.year and provincialEntryScore.year= nudtMilScore.year and provincialEntryScore.province=nudtTechScore.province and provincialEntryScore.province = nudtMilScore.province'
    cursor.execute(sql)
    records = cursor.fetchall()
    return records
    # ------------End-----------

def selectNatural(cursor):
    # -----------Begin----------
    # 请在下面输入自然连接的语句
    sql ="select provincialEntryScore.year,provincialEntryScore.province,provincialEntryScore.entryScore,nudtTechScore.techMax,nudtTechScore.techMin,nudtTechScore.techMean,nudtMilScore.milMax,nudtMilScore.milMin,nudtMilScore.milMean from  provincialEntryScore provincialEntryScore ,nudtTechScore nudtTechScore,nudtMilScore nudtMilScore where provincialEntryScore.year = nudtTechScore.year and provincialEntryScore.year= nudtMilScore.year and provincialEntryScore.province=nudtTechScore.province and provincialEntryScore.province = nudtMilScore.province"
    cursor.execute(sql)
    records = cursor.fetchall()
    return records
    # ------------End-----------