1.Navicat安装
Navicat是一套快速、可靠并价格相当便宜的数据库管理工具，专为简化数据库的管理及降低系统管理成本而设。它的设计符合数据库管理员、开发人员及中小企业的需要。Navicat 是以直觉化的图形用户界面而建的，让你可以以安全并且简单的方式创建、组织、访问并共用信息。使用Navicat可以提高我们管理数据库的效率
需要掌握的技能
掌握： #1. 测试+链接数据库 #2. 新建库 #3. 新建表，新增字段+类型+约束 #4. 设计表：外键 #5. 新建查询 #6. 建立表模型 #注意： 批量加注释：ctrl+？键 批量去注释：ctrl+shift+？键
下载地址:https://pan.baidu.com/s/1bpo5mqj
下载完成后解压安装,按照提示点击下一步,安装地址按照自己选择的地址安装
1.进入Navicat后首先测试链接数据库
点击连接按钮,选择想要连接的数据库,如我的电脑上安装的是MySQL
点击MySQL之后输入MySQL的端口和用户名和密码,
端口号MySQL默认是3306
用户名MySQL默认是root
密码,如果自己没有设置过默认为空,设置过则输入自己相应的密码
点击连接测试,如成功则按确定进入数据库
2.新建库
进入后右键点击红框选择新建数据库,如已有数据库也可以双击点开查看数据库
新建数据库时红框部分必填,首先是数据库名,填写自己需要的名字,字符集如无特殊情况一般都填utf8
点击确定新建数据库完成
3. 新建表，新增字段+类型+约束
打开新建的库,右键点击表,点击新建表,或者在右上方也有个新建表的按钮,点击之后即可进入创建表的功能
点击之后会进入新建字段的操作,选择字段名,字段类型、长度、是否为空等
需要注意的是设置主键的时候需要点击红框位置的自动递增,选择完成后点击橙框保存
点击保存后会让我们填写表名,需要注意不能跟统一数据库中的其他表名冲突,完成后点击确定
4.设计表,外键
右键点击表名选择设计表,或者点击红框位置的设计表都可以
这样可以重新进入设计表模式,点击外键,填写外键字段,参考的表名,参考表的字段名,级联更新、级联删除
点击保存就完成了
5.新建查询
点击红框处的按钮可以新建查询,出现输入框之后输入想查询的内容,按橙框运行键运行代码即可完成查询
注意:在输入框中的注释
批量加注释：ctrl+？键
批量去注释：ctrl+shift+？键
6.建立表模型
如果想要新建模型,则点击模型,并新建模型
如果想要查看已有表的模型则右键点击橙框,选择逆向数据库到模型即可查看模型
2.练习题
导入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;
拷贝上述代码，新建一个.sql文件，保存到桌面
弹出文件框，选中刚刚保存到桌面的.sql文件即可
快速建表
#准备表、记录 >>> 命令行 mysql> create database db1; mysql> use db1; mysql> source /root/init.sql # navicat建表
练习题
1、查询所有的课程的名称以及对应的任课老师姓名 4、查询平均成绩大于八十分的同学的姓名和平均成绩 7、 查询没有报李平老师课的学生姓名 8、 查询没有同时选修物理课程和体育课程的学生姓名 9、 查询挂科超过两门(包括两门)的学生姓名和班级
-- 1、查询所有的课程的名称以及对应的任课老师姓名
SELECT
course.cname,
teacher.tname
FROM
course
INNER JOIN teacher ON course.teacher_id = teacher.tid;
-- 4、查询平均成绩大于八十分的同学的姓名和平均成绩
SELECT
student.sname,
t1.avg_name
FROM
student
INNER JOIN ( SELECT student_id, avg( num ) AS avg_name FROM score GROUP BY student_id HAVING avg( num ) > 80 ) AS t1 ON t1.student_id = student.sid;
-- 7、 查询没有报李平老师课的学生姓名 SELECT student.sname FROM student WHERE student.sid NOT IN ( SELECT DISTINCT score.student_id FROM score WHERE score.course_id IN ( SELECT course.cid FROM course WHERE teacher_id IN ( SELECT teacher.tid FROM teacher WHERE teacher.tname = '李平老师' ) ) );
-- 8、 查询没有同时选修物理课程和体育课程的学生姓名 SELECT sname FROM student WHERE sid IN ( SELECT student_id FROM score WHERE score.course_id IN ( SELECT cid FROM course WHERE course.cname IN ( '物理', '体育' ) ) GROUP BY student_id HAVING count( course_id ) = 1 );
SELECT student.sname, class.caption FROM class INNER JOIN student ON class.cid = student.class_id WHERE student.sid IN ( SELECT student_id FROM score WHERE num < 60 GROUP BY student_id HAVING count( course_id ) >= 2 );
3.PyMySQL模块
什么是 PyMySQL？
PyMySQL 是在 Python3.x 版本中用于连接 MySQL 服务器的一个库，Python2中则使用mysqldb。
PyMySQL 遵循 Python 数据库 API v2.0 规范，并包含了 pure-Python MySQL 客户端库。
安装:pip3 insatll pymysql
1 # 导入模块 2 import pymysql 3 4 # 链接 5 conn = pymysql.connect( 6 host = '127.0.0.1', 7 port = 3306, 8 user = 'root', 9 password = '9527', 10 database = 'day38', 11 charset = 'utf8' # 编码不能加-,会直接报错 12 ) 13 14 # 游标 15 cursor=conn.cursor() 16 17 sql = 'select * from teacher' 18 # print(cursor.execute(sql)) # 返回的参数是5,代表了5个参数 19 cursor.execute(sql) 20 21 print(cursor.fetchone()) # 返回一个值(1, '张磊老师') 22 print(cursor.fetchone()) # 返回(2, '李平老师') 23 print(cursor.fetchone()) # 返回(3, '刘海燕老师') 24 print(cursor.fetchall()) # 返回((4, '朱云海老师'), (5, '李杰老师')) 25 ''' 26 fetchall只返回了两个剩余的值,推测fetchone相当于光标移动了一位, 27 三个就移动了三位,所以最后的fetchall才只返回了两个值 28 ''' 29 cursor.scroll(0,'absolute') # 绝对移动,从第一个开始移动0个,还是从头开始 30 print(cursor.fetchmany(3)) # 返回控制数量的值 31 32 print(cursor.fetchone()) # 返回第一个值 33 cursor.scroll(2,'relative') # 相对导入,不写默认是相对导入,是相对于当前位置想后移动2位,可以传负数,反向移动 34 print(cursor.fetchone()) # 返回:(4, '朱云海老师')相对于上一个位置向后移动2位,所以取到第四个
注意:需要注意文中fetchall,fetchone的用法
sql注入问题
# 不要手动去拼接查询的sql语句 username = input(">>>:").strip() password = input(">>>:").strip() sql = "select * from user where username='%s' and password='%s'"%(username,password) # 用户名正确 username >>>: jason' -- jjsakfjjdkjjkjs # 用户名密码都不对的情况 username >>>: xxx' or 1=1 --asdjkdklqwjdjkjasdljad password >>>: '' ''' 当输入这一类格式时,就算密码输入不正确也可以获取到信息, 这是因为'--'数据库把它辨识成注释, 所以不管输什么都能拿到数据 >>>sxc' or 1=1 -- sadasdasd >>>fasfas 当输入这一类格式时,不管输入什么甚至可以获取全部数据 因为后面有'or 1=1'这句话是什么时候都成立的, 所以不管输入什么都会获得所有数据 '''
增删改
conn = pymysql.connect( host = '127.0.0.1', port = 3306, user = 'root', password = '9527', database = 'day38', charset = 'utf8', autocommit = True ) # 增 sql = "insert into user(username,password) values(%s,%s)" rows = cursor.excute(sql,('jason','123'))
conn.commit()
conn.close() # 修改 sql = "update user set username='jasonDSB' where id=1" rows = cursor.excute(sql)
conn.commit()
conn.close() """ 增和改单单执行excute并不会真正影响到数据，需要再执行conn.commit()才可以完成真正的增改 但是我们可以通过在上方添加一个autocommit=True来完成这步操作 """ # 一次插入多行记录 res = cursor,excutemany(sql,[(),(),()]
conn.commit()
conn.close()
38