1、创建表*/
DROP TABLE IF EXISTS mysql.Student;
create table Student
(
Sno varchar(
20),
Sname varchar(
50),
primary key (Sno)
)
ENGINE = InnoDB
DEFAULT CHARSET
= utf8;
DROP TABLE IF EXISTS mysql.Course;
create table Course
(
Cno varchar(
20),
Cname varchar(
50),
Tno varchar(
20),
primary key (Cno)
)
ENGINE = InnoDB
DEFAULT CHARSET
= utf8;
DROP TABLE IF EXISTS mysql.SC;
create table SC
(
Sno varchar(
20),
Cno varchar(
20),
score int,
primary key (Sno, Cno)
)
ENGINE = InnoDB
DEFAULT CHARSET
= utf8;
DROP TABLE IF EXISTS mysql.Teacher;
create table Teacher
(
Tno varchar(
20),
Tname varchar(
50),
primary key (Tno)
)
ENGINE = InnoDB
DEFAULT CHARSET
= utf8;
/*2、插入表数据*/
INSERT INTO `Student` (Sno, Sname)
VALUES (
‘001‘,
‘陈一‘);
INSERT INTO `Student` (Sno, Sname)
VALUES (
‘002‘,
‘郭二‘);
INSERT INTO `Student` (Sno, Sname)
VALUES (
‘003‘,
‘张三‘);
INSERT INTO `Student` (Sno, Sname)
VALUES (
‘004‘,
‘李四‘);
INSERT INTO `Student` (Sno, Sname)
VALUES (
‘005‘,
‘王五‘);
INSERT INTO `Teacher` (Tno, Tname)
VALUES (
‘001‘,
‘张老师‘);
INSERT INTO `Teacher` (Tno, Tname)
VALUES (
‘002‘,
‘王老师‘);
INSERT INTO `Teacher` (Tno, Tname)
VALUES (
‘003‘,
‘钱老师‘);
INSERT INTO `Teacher` (Tno, Tname)
VALUES (
‘004‘,
‘刘老师‘);
INSERT INTO `Teacher` (Tno, Tname)
VALUES (
‘005‘,
‘胡老师‘);
INSERT INTO `Course` (Cno, Cname, Tno)
VALUES (
‘001‘,
‘语文‘,
‘001‘);
INSERT INTO `Course` (Cno, Cname, Tno)
VALUES (
‘002‘,
‘数学‘,
‘002‘);
INSERT INTO `Course` (Cno, Cname, Tno)
VALUES (
‘003‘,
‘英语‘,
‘003‘);
INSERT INTO `Course` (Cno, Cname, Tno)
VALUES (
‘004‘,
‘物理‘,
‘004‘);
INSERT INTO `Course` (Cno, Cname, Tno)
VALUES (
‘005‘,
‘政治‘,
‘005‘);
INSERT INTO `SC` (Sno, Cno, score)
VALUES (
‘001‘,
‘001‘,
50);
INSERT INTO `SC` (Sno, Cno, score)
VALUES (
‘001‘,
‘002‘,
60);
INSERT INTO `SC` (Sno, Cno, score)
VALUES (
‘001‘,
‘003‘,
70);
INSERT INTO `SC` (Sno, Cno, score)
VALUES (
‘001‘,
‘004‘,
80);
INSERT INTO `SC` (Sno, Cno, score)
VALUES (
‘001‘,
‘005‘,
90);
INSERT INTO `SC` (Sno, Cno, score)
VALUES (
‘002‘,
‘001‘,
90);
INSERT INTO `SC` (Sno, Cno, score)
VALUES (
‘002‘,
‘002‘,
80);
INSERT INTO `SC` (Sno, Cno, score)
VALUES (
‘002‘,
‘003‘,
70);
INSERT INTO `SC` (Sno, Cno, score)
VALUES (
‘002‘,
‘004‘,
60);
INSERT INTO `SC` (Sno, Cno, score)
VALUES (
‘002‘,
‘005‘,
50);
INSERT INTO `SC` (Sno, Cno, score)
VALUES (
‘003‘,
‘001‘,
81);
INSERT INTO `SC` (Sno, Cno, score)
VALUES (
‘003‘,
‘002‘,
82);
INSERT INTO `SC` (Sno, Cno, score)
VALUES (
‘003‘,
‘003‘,
83);
INSERT INTO `SC` (Sno, Cno, score)
VALUES (
‘003‘,
‘004‘,
84);
INSERT INTO `SC` (Sno, Cno, score)
VALUES (
‘003‘,
‘005‘,
85);
INSERT INTO `SC` (Sno, Cno, score)
VALUES (
‘004‘,
‘001‘,
71);
INSERT INTO `SC` (Sno, Cno, score)
VALUES (
‘004‘,
‘002‘,
72);
INSERT INTO `SC` (Sno, Cno, score)
VALUES (
‘004‘,
‘003‘,
73);
INSERT INTO `SC` (Sno, Cno, score)
VALUES (
‘004‘,
‘004‘,
74);
INSERT INTO `SC` (Sno, Cno, score)
VALUES (
‘004‘,
‘005‘,
75);
INSERT INTO `SC` (Sno, Cno, score)
VALUES (
‘005‘,
‘001‘,
75);
INSERT INTO `SC` (Sno, Cno, score)
VALUES (
‘005‘,
‘002‘,
74);
INSERT INTO `SC` (Sno, Cno, score)
VALUES (
‘005‘,
‘003‘,
73);
INSERT INTO `SC` (Sno, Cno, score)
VALUES (
‘005‘,
‘004‘,
72);
INSERT INTO `SC` (Sno, Cno, score)
VALUES (
‘005‘,
‘005‘,
71);
/*运行sql文件出错，解决办法：使用Notepad++打开文件，选择 格式->无BOM的UTF8格式编码*/
/*2、单表查询_表数据*/
select * from Teacher;
select * from Student;
select * from Course;
select * from SC;
/*3、多表查询_表数据*/
select
s.Sname as "学生",
t.Tname as "教师",
c.Cname as "课程",
sc.score as "分数"
from Student s, SC sc, Course c, Teacher t
where sc.Cno
= c.Cno
and sc.Sno
= s.Sno
and c.Tno
= t.Tno
order by s.Sname,
t.Tname,
c.Cname,
sc.score;
输出查询结果【部分截图】，能够正常显示，说明环境配置正常
紧接着：python代码去模拟手工操作数据库
# coding:utf-8
import pymysql
‘‘‘
新添加一个mysql数据库
ip:192.168.112.54 端口：3306 账号：root 密码：123456
‘‘‘
# 连接mysq数据库
connection = pymysql.connect(host=‘192.168.112.54‘,
port=3306,
user=‘root‘,
password=‘123456‘,
db=‘mysql‘,
charset=‘utf8‘,
cursorclass=pymysql.cursors.DictCursor)
# 实例化——创建数据库游标
cursor = connection.cursor()
# 使用execute方法执行SQL语句
id = ‘001‘
i = cursor.execute("select * from Student s where s.Sno = %s" % id)
print("记录数：%d;" % i, "类型：", type(i))
# 使用 fetchone() 方法获取一条数据
data = cursor.fetchone()
print("data:", data, "type:", type(data))
# fetchone()返回字典类型，通过字典访问值
print("Student Number : %s " % data["Sno"])
# 使用 fetchall() 方法获取多条数据
j = cursor.execute("select * from Teacher ")
print("记录数：%d;" % j, "类型：", type(j))
datas = cursor.fetchall()
# 循环遍历查找字典值
for k in datas:
print(k["Tname"])
print("datas:", datas, "type:", type(datas))
# fetchall()返回list类型，通过list访问值
print(datas[0]["Tname"])
# 关闭数据库连接
cursor.close()
connection.close()
输出查询结果【部分截图】
数据库修改等基本操作，请查阅mysql相关知识
-----------------------------------------------------------------------------
特别鸣谢以下大佬：
Anges黎梦 博客地址：https://www.cnblogs.com/AngesZhu/
快捷通道：
想学自动化测试（接口自动化、selenium自动化、appium自动化等）请联系，上海悠悠
悠悠大佬博客：
https://www.cnblogs.com/yoyoketang/
雷总博客：
https://www.cnblogs.com/leiziv5/
【python3 自动化之mysql操作】python3下的mysql入门基础
标签：www 信息 配置 ODB 格式 mod engine 分享 l数据库
