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

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

科技颠覆者  论坛元老 | 2022-11-29 01:15:40 | 显示全部楼层 | 阅读模式
打印 上一主题 下一主题
楼主

主题 2194|帖子 2194|积分 6582

目录

多表查询的两种方法

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

内连接:

只有进行链接的两个标准，都存在与连接标准相匹配的数据才会被保留下来。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
  5. 1.浏览器搜索Navicat直接下载
  6.         版本很多、能够充当的数据库客户端也很多
  7. 2.破解方式
  8.         先试用在破解、直接下载破解版(老版本)、修改试用日期
  9. 3.常用操作
  10.         有些功能可能需要自己修改SQL预览
  11.             创建库、表、记录、外键
  12.             逆向数据库到模型、模型创建（通过画图的形式创表）
  13.             新建查询可以编写SQL语句并自带提示功能
  14.             SQL语句注释语法
  15.         --、#、\**\
  16.             运行、转储SQL文件
  17.             美化SQL 相当于pycharm格式化代码
  18.            
复制代码
建立连接


  • 链接服务端

  • 填写信息

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

基本使用 cursor=pymysql.cursors.DictCursor
  1. pymysql模块
  2.         pip3 install pymysql
  4. import pymysql
  6. # 1.连接MySQL服务端
  7. db = pymysql.connect(
  8.     host='127.0.0.1',
  9.     port=3306,
  10.     user='root',
  11.     password='123',
  12.     db='db4_03',
  13.     charset='utf8mb4'
  14. )
  15. # 2.产生游标对象
  16. # cursor = db.cursor()  # 括号内不填写额外参数 数据是元组 指定性不强  [(),()]
  17. cursor = db.cursor(cursor=pymysql.cursors.DictCursor)  # [{},{}]
  18. # 3.编写SQL语句
  19. # sql = 'select * from teacher;'
  20. sql = 'select * from score;'
  21. # 4.发送SQL语句
  22. affect_rows = cursor.execute(sql)  # execute也有返回值 接收的是SQL语句影响的行数
  23. print(affect_rows)
  24. # 5.获取SQL语句执行之后的结果
  25. res = cursor.fetchall()
  26. 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
  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. cursor = db.cursor(cursor=pymysql.cursors.DictCursor)
  13. # dict
  14. data = {
  15.     'id': '2200320',
  16.     'name': 'John Doe',
  17.     'age': '12',
  18. }
  19. table = 'students'
  20. keys = ','.join(data.keys())  # create a str like 'id,name,age'
  21. values = ','.join(['%s'] * len(data)) # make format symbols like '%s,%s,%s'
  22. sql = 'INSERT INTO {table} ({keys}) VALUES ({values})'.format(table=table, keys=keys, values=values)
  23. print(sql) # INSERT INTO students (id,name,age) VALUES (%s,%s,%s)
  25. try:
  26.     if cursor.execute(sql,tuples=(data.values())):
  27.         print('Success')
  28.         db.commit()
  29. except Exception:
  30.     print('Error')
  31.     db.rollback()
  32. 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;
  9. 2、查询学生表中男女生各有多少人
  10. SELECT gender,count(gender) FROM student GROUP BY gender
  12. 3、查询物理成绩等于100的学生的姓名
  13. SELECT
  14.         sname,
  15.         num
  16. FROM
  17.         ( SELECT student_id, num FROM score INNER JOIN course ON course.cid = score.course_id WHERE cname = '物理' AND num = 100 ) AS std_id
  18.         INNER JOIN student ON std_id.student_id = student.sid
  20. 4、查询平均成绩大于八十分的同学的姓名和平均成绩
  21. -- SELECT * FROM score WHERE num>=80
  22. -- 拼学生名字
  23. -- SELECT * FROM student INNER JOIN score ON score.student_id=student.sid WHERE num>=80
  24. -- 按学生分组
  25. SELECT
  26.         sname,
  27.         avg( num )
  28. FROM
  29.         student
  30.         INNER JOIN score ON score.student_id = student.sid
  31. WHERE
  32.         num >= 80
  33. GROUP BY
  34.         student_id
  36. 5、查询所有学生的学号，姓名，选课数，总成绩
  37. SELECT
  38.         student_id,
  39.         sname,
  40.         count( course_id ),
  41.         avg( num )
  42. FROM
  43.         student
  44.         INNER JOIN score ON student.sid = score.student_id
  45. GROUP BY
  46.         sname
  48. 6、 查询姓李老师的个数
  49. SELECT * FROM teacher WHERE tname like  '%李%'
  51. 7、 查询没有报李平老师课的学生姓名
  53. 8、 查询物理课程比生物课程高的学生的学号
  54. SELECT
  55.         wl.student_id
  56. FROM
  57.         ( SELECT * FROM score WHERE course_id = 1 ) AS sw
  58.         INNER JOIN ( SELECT * FROM score WHERE course_id = 2 ) AS wl ON sw.student_id = wl.student_id
  59. WHERE
  60.         wl.num > sw.num
  62. 9、 查询没有同时选修物理课程和体育课程的学生姓名
  63. SELECT
  64.         student_id
  65. FROM
  66.         score
  67.         INNER JOIN course ON score.course_id = course.cid
  68. WHERE
  69.         cname = '物理'
  70.         OR cname = '体育'
  71. GROUP BY
  72.         student_id
  73. HAVING
  74.         count( course_id ) = 1
  76. 10、查询挂科超过两门(包括两门)的学生姓名和班级
  77. 、查询选修了所有课程的学生姓名
  79. 12、查询李平老师教的课程的所有成绩记录
  80. SELECT num FROM score WHERE course_id in (2,4) #坑
  82. 13、查询全部学生都选修了的课程号和课程名
  83. SELECT GROUP_CONCAT(student_id) FROM score GROUP BY course_id
  85. 14、查询每门课程被选修的次数
  86. SELECT course_id,count(student_id) FROM score GROUP BY course_id
  87. 15、查询之选修了一门课程的学生姓名和学号
  88. SELECT student_id,count(course_id) as one FROM score GROUP BY student_id HAVING one = 1
  91. 16、查询所有学生考出的成绩并按从高到低排序（成绩去重）
  92. SELECT * FROM score ORDER BY num desc
  93. 17、查询平均成绩大于85的学生姓名和平均成绩
  95. 18、查询生物成绩不及格的学生姓名和对应生物分数
  97. 19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名
  99. 20、查询每门课程成绩最好的前两名学生姓名
  101. 21、查询不同课程但成绩相同的学号，课程号，成绩
  103. 22、查询没学过“叶平”老师课程的学生姓名以及选修的课程名称；
  105. 23、查询所有选修了学号为1的同学选修过的一门或者多门课程的同学学号和姓名；
  107. 24、任课最多的老师中学生单科成绩最高的学生姓名
复制代码
基于pymysql用户注册登录
  1. # user rigister and login application use pymysql instead
  3. import pymysql
  5. db = pymysql.connect(host='localhost',
  6.                      port=3306,
  7.                      user='root',
  8.                      passwd='123',
  9.                      db='db1',  # can be changed
  10.                      charset='utf8mb4',  # default
  11.                      autocommit=True,  # set to True
  12.                      )
  13. # 1.create cursor object
  14. cursor = db.cursor(cursor=pymysql.cursors.DictCursor)  # add cursor configuration
  16. # 2. get user data
  17. user_name = input('Enter user name>>>').strip()
  18. pass_word = input('Enter password>>>').strip()
  20. # 3.write SQL statement
  21. sql = "select * from userinfo where name='%s' and pwd='%s'"  # action to '%s' don't forget --> ''
  22. # solved problem: pymysql.err.OperationalError: (1054, "Unknown column 'jason' in 'where clause'")
  23. print(sql)
  25. # 4.execute SQL statemen
  26. cursor.execute(sql,(user_name, pass_word)) # execute function can solved SQL injection
  28. # 5.get result
  29. result = cursor.fetchall()
  31. # 6.rigister result
  32. if result:
  33.     print('welcome!')
  34.     print(result)
  35. else:
  36.     print('username or password is incorrect')
  38. # 7.sql injection problem
  39. # real sql = select * from userinfo where name='jason' -- kdwokodwdkoa' and pwd='123'
复制代码
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！
继续阅读请点击广告

本帖子中包含更多资源

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

x
回复

使用道具 举报

0 个回复

倒序浏览
返回列表

快速回复

高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 or 立即注册

本版积分规则

科技颠覆者

论坛元老
这个人很懒什么都没写!

楼主热帖

标签云

渠道 国产数据库 集成商 AI 运维 CIO 存储 服务器
微信订阅号
微信服务号
微信客服
小程序
H5
关于我们 商务合作 网站地图
©Copyright 2022-2024 杭州祥升科技有限公司 版权所有 浙ICP备20004199号
快速回复 返回顶部 返回列表