Navicat、Pycharm连接Mysql

一、Navicat使用

链接：https://pan.baidu.com/s/15SEMLk-oUSZgGRi6JJAEdA
提取码：m9ra
下载后，将破解补丁放入你Navicat的安装目录，就破解成功了。

1.进入后点击“连接”

2.点击“MySQL”

 

3.连接名可以选填，端口号填写3306，用户名root，如果你设置了密码，就填写你设置的密码，如果没有直接跳过。

二、查询练习

导入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.拷贝上述代码，新建一个.sql文件，保存到桌面

2.打开navicat新建数据库day41，选中新建的数据库鼠标右键选择运行SQL文件

3.弹出文件框，选中刚刚保存到桌面的.sql文件即可

练习题：

'''
1、查询所有的课程的名称以及对应的任课老师姓名
2、查询平均成绩大于八十分的同学的姓名和平均成绩
3、 查询没有报李平老师课的学生姓名
4、 查询没有同时选修物理课程和体育课程的学生姓名
5、 查询挂科超过两门(包括两门)的学生姓名和班级
'''

参考答案：

#1、查询所有的课程的名称以及对应的任课老师姓名
SELECT
    course.cname,
    teacher.tname 
FROM
    teacher
    INNER JOIN course ON course.teacher_id = teacher.tid

#2、查询平均成绩大于八十分的同学的姓名和平均成绩
SELECT
    student.sname,
    t1.平均成绩 
FROM
    student
    INNER JOIN ( SELECT student_id, avg( num ) AS 平均成绩 FROM score GROUP BY student_id HAVING avg( num ) > 80 ) AS t1 ON student.sid = t1.student_id

#3.查询没有报李平老师课的学生姓名
select student.sname from student where student.sid not in
(
SELECT DISTINCT
    student_id
FROM
    score  
WHERE
    score.course_id IN (
SELECT
    course.cid 
FROM
    course
    INNER JOIN teacher ON course.teacher_id = teacher.tid 
WHERE
    teacher.tname = '李平老师'
    ) 
)

#4.查询没有同时选修物理课程和体育课程的学生姓名
SELECT
    student.sname 
FROM
    student 
WHERE
    student.sid IN (
SELECT
    score.student_id 
FROM
    score
    INNER JOIN course ON score.course_id = course.cid 
WHERE
    cname IN ( '物理', '体育' ) 
GROUP BY
    student_id 
HAVING
    count( course_id ) < 2 
    )

#5.查询挂科超过两门的（包括两门）的学生姓名和班级
SELECT
    student.sname,
    class.caption 
FROM
    student
    INNER JOIN class ON student.class_id = class.cid 
WHERE
    student.sid IN ( SELECT student_id FROM score WHERE num < 60 GROUP BY student_id HAVING count( course_id ) >= 2 )

三、python中的pymysql模块

#1.安装：pymysql

#2.代码连接
import pymysql
#链接
conn = pymysql.connect(
    host = '127.0.0.1',
    port = 3306,
    user = 'root',
    password = '123',
    database = 'day38',
    charset = 'utf8'
)
#cursor = conn.cursor() 默认是返回一个元组
cursor = conn.cursor(pymysql.cursors.DictCursor) # 产生一个游标对象，以字典的形式返回查询出来的数据
键是表的字段 值是表的字段对应的信息
sql = 'select * from teacher'
cursor.execute(sql) # 执行传入的sql语句
# print(cursor.execute(sql)) 执行语句返回的是数据条数
print(cursor.fetchone()) # 只获取一条数据
print(cursor.fetchone())
print(cursor.fetchone())
cursor.scroll(1,'absolute') # 控制光标移动 absolute相对于起始位置往后移动几位
cursor.scroll(2,'relative') # relative相对于当前位置 往后移动几位
print(cursor.fetchall()) # 获取所有数据 返回的结果是一个列表

四、注入问题

# 记住不要手动去拼接查询的sql语句
username = input('username>>>:').strip()
password = input('password>>>:').strip()

# 错误！会导致注入问题！
sql = "select * from user where name = ’%s‘ and password = ’%s‘“%(username,password) 

#注入问题 导致的现象：
username>>>: jason' -- asdakjshdkjsds

#不知道用户名和密码的情况下：
username>>> xxx' or 1=1 --askjfkjhfjaljfha
password = ''

#正确操作
cursor.execute(sql,(username,password)) #能够自动帮你过滤特殊符号， 避免sql注入问题

五、增删改

#增
sql = 'insert into user values("jerry","666")'

cursor.execute(sql)

conn.commit()

#改
sql = 'update user set password = "456"'

cursor.execute(sql)

conn.commit()

#删

sql = "delete from user where name ='jerry'"

cursor.execute(sql)

conn.commit()