Navicat软件

简化简单操作：创建表、修改表结构、插入、查询、更新、删除

测试题-上

自行创建测试数据

在成绩表中添加学生id对应的姓名和课程id对应的课程名称

select
    score.sid,
    score.student_id,
    student.sname,
    score.course_id,
    course.cname,
    score.num
form
    score
    
left join student on score.student_id = student.sid
left join course on score.course_id = course.cid

数据库导入、导出

导出现有数据库数据：导出 >

mysqldump -u用户名 -p密码数据库名称 >导出文件路径 # 结构+数据
mysqldump -u用户名 -p密码 -d 数据库名称 >导出文件路径 # 结构

导入现有数据库数据：导入 <

mysqldump -uroot -p密码数据库名称 < 文件路径

表结构和数据

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

View Code

查询平均成绩大于60分的同学的学号和平均成绩

/* 思路：根据学生分组，使用avg获取平均值，通过having对avg进行筛选 */

select student_id,avg(num) from score group by student_id having avg(num) > 60

/* -- ----------------------------- */

select max(cid) from class; /* 获取最大值 */
select sum(cid) from class; /* 取和 */

select * from class; 
select avg(cid) from class; /* 取平均数方法一  avg：默认先求和，再除以个数 */
select sum(cid)/count(cid) from class; /* 取平均数方法二  总和除以个数 */

select student_id,avg(num) from score group by student_id /* 计算每个学生的平均分 */
select student_id,avg(num) from score group by student_id having avg(num) > 60 /* 取平均分大于60的学生id */

/* -- ----------------------------- */

/* 需求：再加上学生名字 */

/* 1.连表操作 */
/* 连表操作方法一 */
select student_id, avg(num), sname from score
left join student on score.student_id = student.sid /* 将score表与学生表联合 */
group by student_id; /* 对学生id进行分组 */
/* 连表操作方法二 */
select * from score, student where score.student_id = student.id
group by student_id;

/* 2.子查询 */
select T.student_id, T.a, student.sname from (select student_id,avg(num) as a from score group by student_id having avg(num) > 60) as T /* 把已取到的平均数结果当作一个表，表名为T */
left join student on T.student_id = student.sid;

查询姓“李”的老师的个数

select count(tid) from teacher where tname like '李%'

/* 方法一 ------------------------ */
select * from teacher where tname like '李%' /* 取出全部姓李的老师 */
select count(tid) from teacher where tname like '李%' /* count取个数 */

/* -- 方法二：临时表 ---------------- */
select count(1) from (select tid from teacher where tname like '李%') as B

查询学过“001”并且也学过编号“002”课程的同学的学号、姓名

/* 思路：
先查到既选择001又选择002课程的所有同学；
根据学生进行分组，如果学生数量等于2表示，两门均已选择 */

select student_id,sname from

(select student_id,course_id from score where course_id = 1 or course_id = 2) as B
      
left join student on B.student_id = student.sid group by student_id HAVING count(student_id) > 1

查询学过“李平”老师所教的所有课的同学的学号、姓名

/* 同上，将001和002变成 in (李平老师的所有课) */

select * from score where course_id in (
    select cid from course left join teacher on course.teacher_id = teacher.tid where teacher.tname = '李平老师' 
) group by student_id /* group by 去重 */
...

查询有课程成绩小于60分的同学的学号、姓名

select sid,sname from student where sid in (
    select distinct student_id from score where num < 60
) /* distinct 去重 */

查询没有学全所有课的同学的学号、姓名

/* 思路：
在分数表中根据学生进行分组，获取每一个学生选课数量；
如果数量 == 总课程数量，表示已经选择了所有课程 */

select student_id,sname
from score left join student on score.student_id = student.sid
group by student_id HAVING count(course_id) = (select count(1) from course)

/* ------------------------------------------ */
select student_id/count(student_id) from score 
group by student_id 
having count(student_id) < (select count(1) as c from course)
...

查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名

/* 思路：
获取 001 同学选择的所有课程
获取课程在其中的所有人以及所有课程
根据学生筛选，获取所有学生信息
再与学生表连接，获取姓名 */
 
select student_id,sname, count(course_id)
from score left join student on score.student_id = student.sid
where student_id != 1 and course_id in (select course_id from score where student_id = 1) group by student_id

/* ------------------------------------------ */

select student_id from score where student_id != 1 and course_id in (
    select course_id from score where student_id = 1) group by student_id
...

查询至少学过“学号为‘001’同学所有课”的其他同学学号和姓名

（其他同学指包含但不限于001同学所学的课）

/* 先找到和001的学过的所有人
然后个数 ＝ 001所有学科 ＝＝》 其他人可能选择的更多 */
    
select student_id,sname, count(course_id)
from score left join student on score.student_id = student.sid
where student_id != 1 and course_id in (select course_id from score where student_id = 1) 
group by student_id 
having count(course_id) ＝ (select count(course_id) from score where student_id = 1)

/* ------------------------------------------ */
 
select student_id, count(student_id) from score where course_id in
(select course_id from score where student_id = 1) group by student_id
having count(student_id) = (select count(1) from score where student_id = 1)
...

查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名

/* 个数相同
    002学过的也学过 */

select student_id,sname from score left join student on score.student_id = student.sid where student_id in (
select student_id from score  where student_id != 1 group by student_id HAVING count(course_id) = (select count(1) from score where student_id = 1)
    ) and course_id in (select course_id from score where student_id = 1) group by student_id HAVING count(course_id) = (select count(1) from score where student_id = 1)
 
/* ------------------------------------------ */

/* 和002选择的个数相同的学生id */
select student_id from score group by student_id having count(student_id) =(
    select count(1) as a from score where student_id = 2
)
...

删除学习“李平”老师课的score表记录

delete from score where course_id in (
    select cid from course left join teacher on course.teacher_id = teacher.tid where teacher.name = '李平'
)

向SC(score)表中插入一些记录

条件：①没有上过编号“002”课程的同学学号；②插入“002”号课程的平均成绩

/* 思路：
由于insert 支持 
    inset into tb1(xx,xx) select x1,x2 from tb2;
所有，获取所有没上过002课的所有人，获取002的平均成绩 */

insert into score(student_id, course_id, num) select sid,2,(select avg(num) from score where course_id = 2)
from student where sid not in (
    select student_id from score where course_id = 2
) group by student_id

/* ------------------------------------------ */

insert into score(student_id, course_id, num) select
    student_id,
    2,
    (
    select avg(num) from score where course_id
    ) as a
from score where student_id not in (
    select student_id from score where course_id = 2
    )
group by student_id

PyMySQL

基本插入数据

#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql

# 创建连接
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='', db='sqlexample', charset = 'utf8')
# 创建游标
cursor= conn.cursor()

# 关闭游标
cursor.close()
# 关闭连接
conn.close()

增删改查

# 执行SQL受影响的行数


# 添加 insert ------------------------------------------------------------------------

# 参数传递，必须使用参数形式；一次传一个数据
inp = input('请输入班级：')
r = cursor.execute('insert into class(caption) values(%s)', inp)
conn.commit()  # 提交，不然无法保存新建或者修改的数据

# 一次传一组数据，用元组形式
r = cursor.execute('insert into student(gender, class_id, sname) values(%s, %s, %s)', ('女', 1, '鸭蛋'))
conn.commit()

# 一次传多组数据,executemany
l = [
    ('女', 1, '鸭蛋1'),
    ('女', 1, '鸭蛋2'),
    ('女', 1, '鸭蛋3')
]
r = cursor.executemany('insert into student(gender, class_id, sname) values(%s, %s, %s)', l )
conn.commit()


# 更新 update ------------------------------------------------------------------------
r = cursor.execute('update student set sname=%s where sid=%s',('马大狗', 1))
conn.commit()


# 删除 delete ------------------------------------------------------------------------
r = cursor.execute('delete from score where sid=%s', (54))
conn.commit()


# 查询 fetchall （数量可叠加）-----------------------------------------------------------
r = cursor.execute('select * from student')
print(r)

# 查看所有
result = cursor.fetchall()
print(result)

# 查看一个
result = cursor.fetchone()
print(result)

# 查看多个（自定义个数）
result = cursor.fetchmany()
print(result)


# 绝对定位（不常用）
cursor.scroll(0, mode = 'absolute')

# 相对定位（不常用）
cursor.scroll(1, mode = 'absolute')  # 1表示往下走一个，-1表示往上回一个


# -----------------------------------------------------------------------------

# 上述execute的返回值表示为：受影响的函数个数

SQL注入

sql = 'select username, password from userinfo where username = "&s" and password = "%s"'
sql = sql %('chung or 1=1 -- ', 123)  # -- 在这里表示注入 如果输入结果为其他，默认打印chung123
cursor.execute(sql)
result = cursor.fetchone()
print(result)

其他

定制游标，字典形式数据

关于默认获取的数据是元祖类型，创建cursor时候获取字典类型的数据

sql = 'select username, password from userinfo where username = "&s" and password = "%s"'
sql = sql %('chung or 1=1 -- ', 123)
cursor.execute(sql)  # 字典类型：创建cursor时候执行类型
result = cursor.fetchone()
print(result)

新创建数据获取自增ID lastrowid

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
cursor = conn.cursor()
cursor.executemany("insert into hosts(host,color_id)values(%s,%s)", [("1.1.1.11",1),("1.1.1.11",2)])
conn.commit()
cursor.close()
conn.close()
  
# 获取最新自增ID
new_id = cursor.lastrowid