/*1、创建表*/

DROP TABLE IF EXISTS mysql.Student;
create table Student
(
  Sno   ),
  Sname ),
  primary key (Sno)
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

DROP TABLE IF EXISTS mysql.Course;
create table Course
(
  Cno   ),
  Cname ),
  Tno   ),
  primary key (Cno)
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

DROP TABLE IF EXISTS mysql.SC;
create table SC
(
  Sno   ),
  Cno   ),
  score int,
  primary key (Sno, Cno)
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

DROP TABLE IF EXISTS mysql.Teacher;
create table Teacher
(
  Tno   ),
  Tname ),
  primary key (Tno)
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8;
/*2、插入表数据*/
', '陈一');
', '郭二');
', '张三');
', '李四');
', '王五');

', '张老师');
', '王老师');
', '钱老师');
', '刘老师');
', '胡老师');

');
');
');
');
');

);
);
);
);
);

);
);
);
);
);

);
);
);
);
);

);
);
);
);
);

);
);
);
);
);

/*运行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;

# 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=',
                             db='mysql',
                             charset='utf8',
                             cursorclass=pymysql.cursors.DictCursor)
# 实例化——创建数据库游标
cursor = connection.cursor()
# 使用execute方法执行SQL语句
id = '
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()