4/7课后总结
多对多的表关系
# 多对多
'''
多对多我们以图书表和作者表为例
我们站在图书表的角度
问:一本图书能不能有多个作者?
答:可以
我们再站在作者表的角度
问:一个作者能不能写多本书
答:可以
得出结论:如果两个都可以,那么表关系就是'多对多'
'''
create table book(
id int primary key auto_increment,
name varchar(32),
price decimal(9,2)
);
create table author(
id int primary key auto_increment,
name varchar(32),
email varchar(32)
);
create table book2author(
id int primary key auto_increment,
author_id int,
book_id int,
foreign key(author_id) references author(id)
on update cascade
on delete cascade,
foreign key(book_id) references book(id)
on update cascade
on delete cascade
);
# 一对一
# 以作者表和作者详情表为例
# 外键一般都是建立在查询评率较高的那张表
create table author1(
id int primary key auto_increment,
name varchar(32),
gender varchar(32),
author_detail_id int unique,
foreign key(author_detail_id) references author_detail(id)
);
create table author_detail(
id int primary key auto_increment,
qq varchar(32),
email varchar(32)
);
总结:
"""
一对多的外键字段------>多的一方
一对一的外键字段------> 两边都可以,一般建在查询频率较高的一方
多对多的外键字段------>第三张表中
"""
数据准备
create table dep(
id int primary key auto_increment,
name varchar(20)
);
create table emp(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);
#插入数据
insert into dep values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营'),
(205,'保洁')
;
insert into emp(name,sex,age,dep_id) values
('jason','male',18,200),
('egon','female',48,201),
('kevin','male',18,201),
('nick','male',28,202),
('owen','male',18,203),
('jerry','female',18,204);
多表查询
1.查询kevin所在的部门名称
select name from dep where id = (select dep_id from emp where name = 'kevin');
"""
多表查询:
子查询:
一条SQL的执行结果当成另外一条SQL语句的执行条件.
连表查询:
把多张表拼接成一张虚拟表(不是真实存在的表,临时在内存中保存),然后按照单表查询思路查询
"""
inner join # 内连接----->要两张表的共有数据
select * from emp inner join dep on emp.dep_id=dep.id;
select * from emp as e inner join dep as d on e.dep_id=d.id left join # 左连接------>以左表为基准,查询左表中所有的数据,右表没有的数据用NULL填充
elect * from emp left join dep on emp.dep_id=dep.id;
right join # 右连接------>以右表为基准,查询右表中所有的数据,左表没有的数据用NULL填充
select * from emp right join dep on emp.dep_id=dep.id;
union # 全连接------>连接两个SQL语句的结果.
select * from emp left join dep on emp.dep_id=dep.id
select * from emp right join dep on emp.dep_id=dep.id;
"""
当一张表中存现字段冲突的时候,我们使用表名点字段名的方式解决.
"""
Navicat可视化软件
Navicat的使用需要下载
'''它不是免费的,收费的,所以:1. 花钱去买,2. 白嫖,3. 免费试用14天'''
#1. Windows版本
#2. Mac版本
去官网下载:https://www.navicat.com.cn/products/
连接mysql命令:mysql -h127.0.0.1 -P3306 -uroot -p123456
# 自己电脑
mysql -u root -p
# 127.0.0.1---------一样的-------->localhost
SQL文件
/*
数据导入:
Navicat Premium Data Transfer
Source Server : localhost
Source Server Type : MySQL
Source Server Version : 50624
Source Host : localhost
Source Database : sqlexam
Target Server Type : MySQL
Target Server Version : 50624
File Encoding : utf-8
Date: 10/21/2016 06:46:46 AM
*/
SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for `class`
-- ----------------------------
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
`cid` int(11) NOT NULL AUTO_INCREMENT,
`caption` varchar(32) NOT NULL,
PRIMARY KEY (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of `class`
-- ----------------------------
BEGIN;
INSERT INTO `class` VALUES ('1', '三年二班'), ('2', '三年三班'), ('3', '一年二班'), ('4', '二年九班');
COMMIT;
-- ----------------------------
-- Table structure for `course`
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`cid` int(11) NOT NULL AUTO_INCREMENT,
`cname` varchar(32) NOT NULL,
`teacher_id` int(11) NOT NULL,
PRIMARY KEY (`cid`),
KEY `fk_course_teacher` (`teacher_id`),
CONSTRAINT `fk_course_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of `course`
-- ----------------------------
BEGIN;
INSERT INTO `course` VALUES ('1', '生物', '1'), ('2', '物理', '2'), ('3', '体育', '3'), ('4', '美术', '2');
COMMIT;
-- ----------------------------
-- Table structure for `score`
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
`sid` int(11) NOT NULL AUTO_INCREMENT,
`student_id` int(11) NOT NULL,
`course_id` int(11) NOT NULL,
`num` int(11) NOT NULL,
PRIMARY KEY (`sid`),
KEY `fk_score_student` (`student_id`),
KEY `fk_score_course` (`course_id`),
CONSTRAINT `fk_score_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`),
CONSTRAINT `fk_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of `score`
-- ----------------------------
BEGIN;
INSERT INTO `score` VALUES ('1', '1', '1', '10'), ('2', '1', '2', '9'), ('5', '1', '4', '66'), ('6', '2', '1', '8'), ('8', '2', '3', '68'), ('9', '2', '4', '99'), ('10', '3', '1', '77'), ('11', '3', '2', '66'), ('12', '3', '3', '87'), ('13', '3', '4', '99'), ('14', '4', '1', '79'), ('15', '4', '2', '11'), ('16', '4', '3', '67'), ('17', '4', '4', '100'), ('18', '5', '1', '79'), ('19', '5', '2', '11'), ('20', '5', '3', '67'), ('21', '5', '4', '100'), ('22', '6', '1', '9'), ('23', '6', '2', '100'), ('24', '6', '3', '67'), ('25', '6', '4', '100'), ('26', '7', '1', '9'), ('27', '7', '2', '100'), ('28', '7', '3', '67'), ('29', '7', '4', '88'), ('30', '8', '1', '9'), ('31', '8', '2', '100'), ('32', '8', '3', '67'), ('33', '8', '4', '88'), ('34', '9', '1', '91'), ('35', '9', '2', '88'), ('36', '9', '3', '67'), ('37', '9', '4', '22'), ('38', '10', '1', '90'), ('39', '10', '2', '77'), ('40', '10', '3', '43'), ('41', '10', '4', '87'), ('42', '11', '1', '90'), ('43', '11', '2', '77'), ('44', '11', '3', '43'), ('45', '11', '4', '87'), ('46', '12', '1', '90'), ('47', '12', '2', '77'), ('48', '12', '3', '43'), ('49', '12', '4', '87'), ('52', '13', '3', '87');
COMMIT;
-- ----------------------------
-- Table structure for `student`
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`sid` int(11) NOT NULL AUTO_INCREMENT,
`gender` char(1) NOT NULL,
`class_id` int(11) NOT NULL,
`sname` varchar(32) NOT NULL,
PRIMARY KEY (`sid`),
KEY `fk_class` (`class_id`),
CONSTRAINT `fk_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of `student`
-- ----------------------------
BEGIN;
INSERT INTO `student` VALUES ('1', '男', '1', '理解'), ('2', '女', '1', '钢蛋'), ('3', '男', '1', '张三'), ('4', '男', '1', '张一'), ('5', '女', '1', '张二'), ('6', '男', '1', '张四'), ('7', '女', '2', '铁锤'), ('8', '男', '2', '李三'), ('9', '男', '2', '李一'), ('10', '女', '2', '李二'), ('11', '男', '2', '李四'), ('12', '女', '3', '如花'), ('13', '男', '3', '刘三'), ('14', '男', '3', '刘一'), ('15', '女', '3', '刘二'), ('16', '男', '3', '刘四');
COMMIT;
-- ----------------------------
-- Table structure for `teacher`
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
`tid` int(11) NOT NULL AUTO_INCREMENT,
`tname` varchar(32) NOT NULL,
PRIMARY KEY (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of `teacher`
-- ----------------------------
BEGIN;
INSERT INTO `teacher` VALUES ('1', '张磊老师'), ('2', '李平老师'), ('3', '刘海燕老师'), ('4', '朱云海老师'), ('5', '李杰老师');
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;
多表查询练习题
1、查询所有的课程的名称以及对应的任课老师姓名
SELECT cname,tname FROM course LEFT JOIN teacher on course.teacher_id = teacher.tid;
2、查询平均成绩大于八十分的同学的姓名和平均成绩
SELECT sname,AVG(num) FROM student LEFT JOIN score on student.sid =score.student_id GROUP BY sname HAVING AVG(num) >= 80;
3、查询没有报李平老师课的学生姓名
SELECT sname FROM student WHERE sname not in (SELECT sname FROM (SELECT cid FROM course right JOIN (SELECT tid FROM teacher WHERE tname = '李平老师')AS t on course.teacher_id = t.tid)as tt LEFT JOIN (SELECT course_id,sname FROM student LEFT JOIN score on student.sid = score.student_id)as ss on tt.cid = ss.course_id GROUP BY sname);
4、查询挂科超过两门(包括两门)的学生姓名和班级
SELECT sname,group_concat(distinct caption) FROM (SELECT sname,class_id FROM student LEFT JOIN score on student.sid = score.student_id WHERE num <60) as s LEFT JOIN class on s.class_id=class.cid GROUP BY sname HAVING count(sname)>=2;
'''可能有点难,自己做,能做几个做几个.'''
python操作MySQL
# 需要借助于第三方模块
# mysqldb
# mysqlclient... 在Django中用,要比这两个模块好用,一般情况下,安装不上
pip install pymysql;
import pymysql
# 1. 链接mysql
# 此时python链接mysql,python就是一个客户端
# 既然是客户端,那就需要链接服务端,保证服务端的正常运行
conn=pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
password='123',
db='db11',
charset='utf8',
autocommit=True
)
# 2. 获取一个游标
cursor=conn.cursor(cursor=pymysql.cursors.DictCursor)
# 3. 先写SQL语句
# sql = 'select * from t2'
# 插入数据
sql = 'insert into t2 (name, age) values ("tank", 22)'
# 4. 执行SQL语句
affect_rows=cursor.execute(sql)
# 二次确认,除查询之外的都要用
# conn.commit()
print(affect_rows) # 2
# 5. 拿到查询结果
# [{'id': 1, 'name': 'kevin', 'age': 20}, {'id': 2, 'name': 'jerry', 'age': 21}]
# print(cursor.fetchall())
# print(cursor.fetchone())
# print(cursor.fetchone())
# print(cursor.fetchmany(1))
# for i in cursor.fetchall():
# print('')
使用python结合MySQL写一个注册和登录的案例
import pymysql
conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
password='123',
db='db7',
charset='utf8',
autocommit=True
)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
def query(username,password = None):
sql = 'select *from t1'
cursor.execute(sql)
if password == None:
for i in cursor.fetchall():
if username in i['username']:
return True
else:
return False
else:
for i in cursor.fetchall():
if username == i['username'] and password == i['password']:
return True
else:
return False
if __name__ == '__main__':
while True:
print("""
1.注册
2.登陆
q.退出
""")
flag = input('请输入您的选择:')
if flag == '1':
while True:
username = input('请输入您的账户')
password = input('请输入您的密码')
if query(username):
print('账号已存在,无法继续添加')
continue
sql = f'insert into t1(username,password) values({username},{password})'
affect_rows = cursor.execute(sql)
conn.commit()
break
elif flag == '2':
while True:
username = input('请输入您的账户')
password = input('请输入您的密码')
if query(username,password):
print('登陆成功')
break
print('账号或者密码错误')
elif flag == 'q':
exit()
else:
print('输入有误请重新输入')
标签:总结,student,--,----------------------------,dep,int,课后,id
From: https://www.cnblogs.com/juzixiong/p/17303991.html