f Oracle-连表操作join 子查询 SQL补充 数据库软件navicat pymysql模块 - Powered by qidao123.com技术社区

连表操作join 子查询 SQL补充 数据库软件navicat pymysql模块 ...

打印 上一主题 下一主题

主题 2194|帖子 2194|积分 6582

目录

多表查询的两种方法

方式1:连表操作
  1. 语法:
  2. select * from (表1) inner\right\left\union join (表2) on (拼接条件)
  3. inner join     内连接
  4.         select * from emp inner join dep on emp.dep_id=dep.id;
  5.         只连接两张表中公有的数据部分
  6.         
  7. left join      左连接
  8.         select * from emp left join dep on emp.dep_id=dep.id;
  9.         以左表为基准 展示左表所有的数据 如果没有对应项则用NULL填充
  10.          
  11. right join     右连接
  12.         select * from emp right join dep on emp.dep_id=dep.id;
  13.         以右表为基准 展示右表所有的数据 如果没有对应项则用NULL填充
  14.         
  15. union          全连接
  16.         select * from emp left join dep on emp.dep_id=dep.id
  17. union
  18.         select * from emp right join dep on emp.dep_id=dep.id;
  19.         以左右表为基准 展示所有的数据 各自没有的全部NULL填充
  20. '''
  21. 学会了连表操作之后也就可以连接N多张表
  22.         思路:将拼接之后的表起别名当成一张表再去与其他表拼接 再起别名当一张表 再去与其他表拼接 其次往复即可
  23. '''
复制代码
准备拼接的表:

内连接:

只有进行链接的两个标准,都存在与连接标准相匹配的数据才会被保留下来。emp1.dep_id和dep1.id这两列,有数据是重复的。保留这些重复数据(200,201,201,202,203)所在的那一整行,
丢弃每个表独有的:如(emp的dep_id=204这一行,dep1的id=205这一行)。
左连接:

关键字left join左边的emp1为左表,以左表为基准 展示左表所有的数据 右表有数据对应就对应 没有数据对应就写NULL
右连接:

union 全连接:全部展示

方式2:子查询
  1. 子查询:将一条SQL语句用括号括起来当成另外一条SQL语句的查询条件
  2.         题目:求姓名是jason的员工部门名称
  3.         子查询类似于我们日常生活中解决问题的方式>>>:分步操作
  4.            步骤1:先根据jason获取部门编号
  5.            select dep_id from emp where name='jason';
  6.               步骤2:再根据部门编号获取部门名称
  7.                   select name from dep where id=200;
  8.         总结
  9.         select name from dep where id=(select dep_id from emp where name='jason');
  10. '''
  11. 很多时候多表查询需要结合实际情况判断用哪种  更多时候甚至是相互配合使用
  12. '''
复制代码
通过第一条查询获取到dep_id=200:

将第一条语句作为第二条语句的查询条件:

SQL补充知识点

1.分组之前字段拼接 concat concat_ws
  1. concat用于分组之前的字段拼接操作
  2.         select concat(name,'$',gender) from emp;
  3. concat_ws拼接多个字段并且中间的连接符一致 concat偷懒版本
  4.         select concat_ws('|',name,gender,age,dep_id) from emp;
复制代码
concat使用:

concat_ws使用:

2.SQL执行判断条件 exists
  1.         语法:sql1 exists sql2
  2.             sql2有结果的情况下才会执行sql1 否则不执行sql1 返回空数据
复制代码

exists前面的语句是否执行 取决于后面的sql语句是否有结果
如果有结果就执行 前面这个sql语句 没有结果就不执行前面这个sql语句
相当于exist括号里面装的是一个判断条件 根据这个来决定前面的sql是否执行
3.表相关SQL补充

原表:

修改表名 alter table ... rename ...
  1. alter table 表名 rename 新表名;  # 修改表名
复制代码

添加字段 alter table ... add ... after/first
  1. alter table 表名 add 字段名 字段类型(数字) 约束条件;  # 添加字段
复制代码
  1. alter table 表名 add 字段名 字段类型(数字) 约束条件 after 已有字段;
复制代码
  1. alter table 表名 add 字段名 字段类型(数字) 约束条件 first;
复制代码
修改字段名 alter table ... change/modify
  1. alter table 表名 change 旧字段名 新字段名 字段类型(数字) 约束条件;
  2. alter table 表名 modify 字段名 新字段类型(数字) 约束条件;
复制代码
删除字段名 alter table ... drop
  1. alter table 表名 drop 字段名;  # 删除字段
复制代码
给表添加注释 commit

可以给表添加注释:

注释有两个查看位置:

  • 使用代码show create table 表名
  • 去数据库information_schema里面查看。
    information_schema这是个临时库,存储数据在内存。use一下这个库,然后查询:
    select * from tables \G可以在众多结果找到我们刚刚创建的表:

可视化软件navicat
  1. 第三方开发的用来充当数据库客户端的简单快捷的操作界面
  2.         无论第三方软件有多么的花里胡哨 底层的本质还是SQL
  3. 能够操作数据库的第三方可视化软件有很多 其中针对MySQL最出名的就是Navicat
  4. 1.浏览器搜索Navicat直接下载
  5.         版本很多、能够充当的数据库客户端也很多
  6. 2.破解方式
  7.         先试用在破解、直接下载破解版(老版本)、修改试用日期
  8. 3.常用操作
  9.         有些功能可能需要自己修改SQL预览
  10.             创建库、表、记录、外键
  11.             逆向数据库到模型、模型创建(通过画图的形式创表)
  12.             新建查询可以编写SQL语句并自带提示功能
  13.             SQL语句注释语法
  14.         --、#、\**\
  15.             运行、转储SQL文件
  16.             美化SQL 相当于pycharm格式化代码
  17.            
复制代码
建立连接


  • 链接服务端

  • 填写信息

    这里的主机指的是mysql服务端,mysql运行在本地就填localhost,在公网就填公网ip。
    先点击测试连接,再点击确定。
转储和运行SQL文件

Navicat可以将自己的数据库打包成SQL文件,给别人使用。也可以运行别人打包的SQL文件,获取别人的数据库全部信息。
转储生成sql文件:

运行sql文件:

  • 首先需要先新建一个数据库或者打开一个数据库(重要)

  • 右键数据库,点击运行SQL文件:

    选择要运行的SQL文件即可
添加外键的注意事项


  • 先创建被外键关联的表,给被关联的表录入数据。

  • 再创建有外键的表,先创建外键字段,保存表:

  • 再将创建的外键进行绑定

    这里有个坑就是:
    添加外键的时候 要注意 自己表外键的数据类型 和参考的目标表的字段数据类型要相同
    不然会报错error:  cannot add foreign key constraint
    创建好了就保存可以按快捷键ctrl+s
  • 创建好的外键

    补充: 创键一对一外键时 可以修改SQL语句
    1.比如给userinfo添加对于user_msg的外键

    2.还是先创建字段

    3.添加外键
    无法修改SQL 如何添加一对一外键?
多表查询练习题
  1. """
  2. 编写复杂的SQL不要想着一口气写完
  3.         一定要先明确思路 然后一步步写一步步查一步步补
  4. """
  5. 1、查询所有的课程的名称以及对应的任课老师姓名
  6. 4、查询平均成绩大于八十分的同学的姓名和平均成绩
  7. 7、查询没有报李平老师课的学生姓名
  8. 8、查询没有同时选修物理课程和体育课程的学生姓名
  9. 9、查询挂科超过两门(包括两门)的学生姓名和班级
  10. -- 1、查询所有的课程的名称以及对应的任课老师姓名
  11. # 1.先确定需要用到几张表  课程表 分数表
  12. # 2.预览表中的数据 做到心中有数
  13. -- select * from course;
  14. -- select * from teacher;
  15. # 3.确定多表查询的思路 连表 子查询 混合操作
  16. -- SELECT
  17. --         teacher.tname,
  18. --         course.cname
  19. -- FROM
  20. --         course
  21. -- INNER JOIN teacher ON course.teacher_id = teacher.tid;
  22. -- 4、查询平均成绩大于八十分的同学的姓名和平均成绩
  23. # 1.先确定需要用到几张表 学生表 分数表
  24. # 2.预览表中的数据
  25. -- select * from student;
  26. -- select * from score;
  27. # 3.根据已知条件80分 选择切入点 分数表
  28. # 求每个学生的平均成绩 按照student_id分组 然后avg求num即可
  29. -- select student_id,avg(num) as avg_num from score group by student_id having avg_num>80;
  30. # 4.确定最终的结果需要几张表 需要两张表 采用连表更加合适
  31. -- SELECT
  32. --         student.sname,
  33. --         t1.avg_num
  34. -- FROM
  35. --         student
  36. -- INNER JOIN (
  37. --         SELECT
  38. --                 student_id,
  39. --                 avg(num) AS avg_num
  40. --         FROM
  41. --                 score
  42. --         GROUP BY
  43. --                 student_id
  44. --         HAVING
  45. --                 avg_num > 80
  46. -- ) AS t1 ON student.sid = t1.student_id;
  47. -- 7、查询没有报李平老师课的学生姓名
  48. # 1.先确定需要用到几张表  老师表 课程表 分数表 学生表
  49. # 2.预览每张表的数据
  50. # 3.确定思路 思路1:正向筛选 思路2:筛选所有报了李平老师课程的学生id 然后取反即可
  51. # 步骤1 先获取李平老师教授的课程id
  52. -- select tid from teacher where tname = '李平老师';
  53. -- select cid from course where teacher_id = (select tid from teacher where tname = '李平老师');
  54. # 步骤2 根据课程id筛选出所有报了李平老师的学生id
  55. -- select distinct student_id from score where course_id in (select cid from course where teacher_id = (select tid from teacher where tname = '李平老师'))
  56. # 步骤3 根据学生id去学生表中取反获取学生姓名
  57. -- SELECT
  58. --         sname
  59. -- FROM
  60. --         student
  61. -- WHERE
  62. --         sid NOT IN (
  63. --                 SELECT DISTINCT
  64. --                         student_id
  65. --                 FROM
  66. --                         score
  67. --                 WHERE
  68. --                         course_id IN (
  69. --                                 SELECT
  70. --                                         cid
  71. --                                 FROM
  72. --                                         course
  73. --                                 WHERE
  74. --                                         teacher_id = (
  75. --                                                 SELECT
  76. --                                                         tid
  77. --                                                 FROM
  78. --                                                         teacher
  79. --                                                 WHERE
  80. --                                                         tname = '李平老师'
  81. --                                         )
  82. --                         )
  83. --         )
  84. -- 8、查询没有同时选修物理课程和体育课程的学生姓名(报了两门或者一门不报的都不算)
  85. # 1.先确定需要的表  学生表 分数表 课程表
  86. # 2.预览表数据
  87. # 3.根据给出的条件确定起手的表
  88. # 4.根据物理和体育筛选课程id
  89. -- select cid from course where cname in ('物理','体育');
  90. # 5.根据课程id筛选出所有跟物理 体育相关的学生id
  91. -- select * from score where course_id in (select cid from course where cname in ('物理','体育'))
  92. # 6.统计每个学生报了的课程数 筛选出等于1的
  93. -- select student_id from score where course_id in (select cid from course where cname in ('物理','体育'))
  94. -- group by student_id
  95. -- having count(course_id) = 1;
  96. # 7.子查询获取学生姓名即可
  97. -- SELECT
  98. --         sname
  99. -- FROM
  100. --         student
  101. -- WHERE
  102. --         sid IN (
  103. --                 SELECT
  104. --                         student_id
  105. --                 FROM
  106. --                         score
  107. --                 WHERE
  108. --                         course_id IN (
  109. --                                 SELECT
  110. --                                         cid
  111. --                                 FROM
  112. --                                         course
  113. --                                 WHERE
  114. --                                         cname IN ('物理', '体育')
  115. --                         )
  116. --                 GROUP BY
  117. --                         student_id
  118. --                 HAVING
  119. --                         count(course_id) = 1
  120. --         )
  121. -- 9、查询挂科超过两门(包括两门)的学生姓名和班级
  122. # 1.先确定涉及到的表        分数表 学生表 班级表
  123. # 2.预览表数据
  124. -- select * from class
  125. # 3.根据条件确定以分数表作为起手条件
  126. # 步骤1 先筛选掉大于60的数据
  127. -- select * from score where num < 60;
  128. # 步骤2 统计每个学生挂科的次数
  129. -- select student_id,count(course_id) from score where num < 60 group by student_id;
  130. # 步骤3 筛选次数大于等于2的数据
  131. -- select student_id from score where num < 60 group by student_id having count(course_id) >= 2;
  132. # 步骤4 连接班级表与学生表 然后基于学生id筛选即可
  133. SELECT
  134.         student.sname,
  135.         class.caption
  136. FROM
  137.         student
  138. INNER JOIN class ON student.class_id = class.cid
  139. WHERE
  140.         student.sid IN (
  141.                 SELECT
  142.                         student_id
  143.                 FROM
  144.                         score
  145.                 WHERE
  146.                         num < 60
  147.                 GROUP BY
  148.                         student_id
  149.                 HAVING
  150.                         count(course_id) >= 2
  151.         );
复制代码
SQL文件:
链接:https://pan.baidu.com/s/1LUu3U8VHc4oxT58zhJyfRg
提取码:pur1
pymysql模块

基本使用 cursor=pymysql.cursors.DictCursor
  1. pymysql模块
  2.         pip3 install pymysql
  3. import pymysql
  4. # 1.连接MySQL服务端
  5. db = pymysql.connect(
  6.     host='127.0.0.1',
  7.     port=3306,
  8.     user='root',
  9.     password='123',
  10.     db='db4_03',
  11.     charset='utf8mb4'
  12. )
  13. # 2.产生游标对象
  14. # cursor = db.cursor()  # 括号内不填写额外参数 数据是元组 指定性不强  [(),()]
  15. cursor = db.cursor(cursor=pymysql.cursors.DictCursor)  # [{},{}]
  16. # 3.编写SQL语句
  17. # sql = 'select * from teacher;'
  18. sql = 'select * from score;'
  19. # 4.发送SQL语句
  20. affect_rows = cursor.execute(sql)  # execute也有返回值 接收的是SQL语句影响的行数
  21. print(affect_rows)
  22. # 5.获取SQL语句执行之后的结果
  23. res = cursor.fetchall()
  24. print(res)
复制代码
获取数据 fetchall
  1. fetchall()  获取所有的结果
  2. fetchone()  一次读一个数据,每次都基于上次的位置往后面读。
  3. fetchmany() 获取指定数量的结果集数据
  4. ps:注意三者都有类似于文件光标移动的特性
  5. 读取结果集可以类比读取文件 光标读取到末尾了 再继续读就没有内容了 所以多次使用fetchall 第二次及以后都获取的是空。
复制代码
fetchall:

fetchone:

移动光标 scroll
  1. cursor.scroll(1,'relative')  # 基于当前位置往后移动1位
  2. cursor.scroll(1,'absolute')  # 基于数据的开头往后移动一位
  3. 注意这个输入的数字 是要≥0的。
复制代码

≥0的
增删改二次确认 commit autocommit=True
  1. 在pymysql.connect里添加autocommit=True
  2. # 可以针对增 删 改的操作 进行自动确认(无需写代码二次确认)
  3. db.commit()  # 针对 增 删 改 需要二次确认(代码确认)
复制代码
补充方法 rowcount rollback
  1. 对于光标对象:
  2. cursor.rowcount # 用于获取查询结果的条数
  3. 对于connect方法产生的db对象:
  4. rollback # 数据回滚 用于增删改出错的情况 将数据库状态回复到commit之前 相当于什么事都没发生
复制代码
确保数据的一致性

插入一条数据,要么全部插入要么都不插入,不会出现插入一半的情况,这称之为事务的原子性。
增删改都是对数据库进行更改的操作,而更改操作都必须是一个事务,所以这些操作的标注写法是:
  1. try:
  2.         cursor.execute(sql)
  3.         db.commit()
  4. except:
  5.         db.rollback()
复制代码
引入动态字典生成SQL语句

添加字段的时候需要修改我们构建的SQL语句,这不是我们想要的。所以引入字典动态生成SQL:
  1. import pymysql
  2. db = pymysql.connect(host='localhost',
  3.                      port=3306,
  4.                      user='root',
  5.                      passwd='123',
  6.                      db='db1',  # can be changed
  7.                      charset='utf8mb4',  # default
  8.                      autocommit=True,  # set to True
  9.                      )
  10. cursor = db.cursor(cursor=pymysql.cursors.DictCursor)
  11. # dict
  12. data = {
  13.     'id': '2200320',
  14.     'name': 'John Doe',
  15.     'age': '12',
  16. }
  17. table = 'students'
  18. keys = ','.join(data.keys())  # create a str like 'id,name,age'
  19. values = ','.join(['%s'] * len(data)) # make format symbols like '%s,%s,%s'
  20. sql = 'INSERT INTO {table} ({keys}) VALUES ({values})'.format(table=table, keys=keys, values=values)
  21. print(sql) # INSERT INTO students (id,name,age) VALUES (%s,%s,%s)
  22. try:
  23.     if cursor.execute(sql,tuples=(data.values())):
  24.         print('Success')
  25.         db.commit()
  26. except Exception:
  27.     print('Error')
  28.     db.rollback()
  29. db.close()
复制代码
更新数据时的主键配置 ON DUPLICATE KEY UPDATA

在某些应用情境下,我们关心表中会不会出现重复数据,如果出现了,我们希望更新数据而不是重复保存一次。我们需要实现:如果数据存在,就更新数据;如果数据不存在,则插入数据。
可以给插入语句添加约束条件:ON DUPLICATE KEY UPDATA 意思是如果主键已经存在,就执行更新操作。
完整的SQL写法:INSERT INTO stundent(id,name,age) VALUES(%s,%s,%s) ON DUPLICATE KEY UPDATA id = %s,name = %s, age = %s 注意这里%s是占位符,可以通过execute第二个参数给他传进去。这样写即可实现主键不存在便插入数据,主键存在则更新数据。
while循环 + fetchone

fetchall方法全部获取数据,如果数据量很大,那么占用的开销也会非常高
所以可以使用:
  1. sql = 'SELECT * FROM students WHERE age >= 20'
  2. try:
  3.         cursor.execute(sql)
  4.         print('count:',cursor.rowcount)
  5.         row = cursor.fetchone()
  6.         while row:
  7.                 print('row:',row)
  8.                 row = cursor.fetchone()
  9. excpet:
  10.         print('error')
复制代码
练习

SQL练习题
  1. 1、查询所有的课程的名称以及对应的任课老师姓名
  2. SELECT
  3.         cname,
  4.         tname
  5. FROM
  6.         course
  7.         INNER JOIN teacher ON course.teacher_id = teacher.tid;
  8. 2、查询学生表中男女生各有多少人
  9. SELECT gender,count(gender) FROM student GROUP BY gender
  10. 3、查询物理成绩等于100的学生的姓名
  11. SELECT
  12.         sname,
  13.         num
  14. FROM
  15.         ( SELECT student_id, num FROM score INNER JOIN course ON course.cid = score.course_id WHERE cname = '物理' AND num = 100 ) AS std_id
  16.         INNER JOIN student ON std_id.student_id = student.sid
  17. 4、查询平均成绩大于八十分的同学的姓名和平均成绩
  18. -- SELECT * FROM score WHERE num>=80
  19. -- 拼学生名字
  20. -- SELECT * FROM student INNER JOIN score ON score.student_id=student.sid WHERE num>=80
  21. -- 按学生分组
  22. SELECT
  23.         sname,
  24.         avg( num )
  25. FROM
  26.         student
  27.         INNER JOIN score ON score.student_id = student.sid
  28. WHERE
  29.         num >= 80
  30. GROUP BY
  31.         student_id
  32. 5、查询所有学生的学号,姓名,选课数,总成绩
  33. SELECT
  34.         student_id,
  35.         sname,
  36.         count( course_id ),
  37.         avg( num )
  38. FROM
  39.         student
  40.         INNER JOIN score ON student.sid = score.student_id
  41. GROUP BY
  42.         sname
  43. 6、 查询姓李老师的个数
  44. SELECT * FROM teacher WHERE tname like  '%李%'
  45. 7、 查询没有报李平老师课的学生姓名
  46. 8、 查询物理课程比生物课程高的学生的学号
  47. SELECT
  48.         wl.student_id
  49. FROM
  50.         ( SELECT * FROM score WHERE course_id = 1 ) AS sw
  51.         INNER JOIN ( SELECT * FROM score WHERE course_id = 2 ) AS wl ON sw.student_id = wl.student_id
  52. WHERE
  53.         wl.num > sw.num
  54. 9、 查询没有同时选修物理课程和体育课程的学生姓名
  55. SELECT
  56.         student_id
  57. FROM
  58.         score
  59.         INNER JOIN course ON score.course_id = course.cid
  60. WHERE
  61.         cname = '物理'
  62.         OR cname = '体育'
  63. GROUP BY
  64.         student_id
  65. HAVING
  66.         count( course_id ) = 1
  67. 10、查询挂科超过两门(包括两门)的学生姓名和班级
  68. 、查询选修了所有课程的学生姓名
  69. 12、查询李平老师教的课程的所有成绩记录
  70. SELECT num FROM score WHERE course_id in (2,4) #坑
  71. 13、查询全部学生都选修了的课程号和课程名
  72. SELECT GROUP_CONCAT(student_id) FROM score GROUP BY course_id
  73. 14、查询每门课程被选修的次数
  74. SELECT course_id,count(student_id) FROM score GROUP BY course_id
  75. 15、查询之选修了一门课程的学生姓名和学号
  76. SELECT student_id,count(course_id) as one FROM score GROUP BY student_id HAVING one = 1
  77. 16、查询所有学生考出的成绩并按从高到低排序(成绩去重)
  78. SELECT * FROM score ORDER BY num desc
  79. 17、查询平均成绩大于85的学生姓名和平均成绩
  80. 18、查询生物成绩不及格的学生姓名和对应生物分数
  81. 19、查询在所有选修了李平老师课程的学生中,这些课程(李平老师的课程,不是所有课程)平均成绩最高的学生姓名
  82. 20、查询每门课程成绩最好的前两名学生姓名
  83. 21、查询不同课程但成绩相同的学号,课程号,成绩
  84. 22、查询没学过“叶平”老师课程的学生姓名以及选修的课程名称;
  85. 23、查询所有选修了学号为1的同学选修过的一门或者多门课程的同学学号和姓名;
  86. 24、任课最多的老师中学生单科成绩最高的学生姓名
复制代码
基于pymysql用户注册登录
  1. # user rigister and login application use pymysql instead
  2. import pymysql
  3. db = pymysql.connect(host='localhost',
  4.                      port=3306,
  5.                      user='root',
  6.                      passwd='123',
  7.                      db='db1',  # can be changed
  8.                      charset='utf8mb4',  # default
  9.                      autocommit=True,  # set to True
  10.                      )
  11. # 1.create cursor object
  12. cursor = db.cursor(cursor=pymysql.cursors.DictCursor)  # add cursor configuration
  13. # 2. get user data
  14. user_name = input('Enter user name>>>').strip()
  15. pass_word = input('Enter password>>>').strip()
  16. # 3.write SQL statement
  17. sql = "select * from userinfo where name='%s' and pwd='%s'"  # action to '%s' don't forget --> ''
  18. # solved problem: pymysql.err.OperationalError: (1054, "Unknown column 'jason' in 'where clause'")
  19. print(sql)
  20. # 4.execute SQL statemen
  21. cursor.execute(sql,(user_name, pass_word)) # execute function can solved SQL injection
  22. # 5.get result
  23. result = cursor.fetchall()
  24. # 6.rigister result
  25. if result:
  26.     print('welcome!')
  27.     print(result)
  28. else:
  29.     print('username or password is incorrect')
  30. # 7.sql injection problem
  31. # real sql = select * from userinfo where name='jason' -- kdwokodwdkoa' and pwd='123'
复制代码
免责声明:如果侵犯了您的权益,请联系站长,我们会及时删除侵权内容,谢谢合作!
继续阅读请点击广告

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有账号?立即注册

x
回复

使用道具 举报

0 个回复

倒序浏览

快速回复

您需要登录后才可以回帖 登录 or 立即注册

本版积分规则

科技颠覆者

论坛元老
这个人很懒什么都没写!
快速回复 返回顶部 返回列表