首页 > 数据库 >Navicat可视化软件、Navicat可视化软件、python操作MySQL、语法知识补充

Navicat可视化软件、Navicat可视化软件、python操作MySQL、语法知识补充

时间：2022-08-20 21:45:33浏览次数：70  
标签：name -- Navicat cursor 可视化 student 软件 id select

今日内容概要

  • Navicat可视化软件
  • 多表查询练习题
  • python操作MySQL
  • 知识补充

今日内容详细

连接MySQL

在官网上下载并安装好最新的Navicat软件后，首先点击连接、选择MySQL,然后填写连接名、主机、用户名密码

image

创建数据库

鼠标右键点击连接名，选择新建数据库，填写库名和字符集，字符集选择utf8mb4

image

创建表

双击要建立表的数据库名，右键点击表，选择新建表，之后就可以填写字段名了

image

插入数据

直接鼠标右键点击打开表，就可以插入数据了

转储SQL文件、运行SQL文件

当我们想要将自己的数据库文件拷贝下来法给别人时，直接点击库的右键、点击转储SQL文件、点击结构和数据、选择路径

当我们想要运行别人的数据库文件时，直接点击库的右键、点击运行SQL文件、选择文件路径即可

查询

直接点击新建查询，编写查询代码即可

查看表关系

选择逆向到数据库模型，就可看见表与表之间的关系了

多表查询练习

1、查询所有的课程的名称以及对应的任课老师姓名

# 1.需要的表：course teacher
# 2.查看表
-- select * from course
-- SELECT * from teacher
# 3.思路：连表操作、INNER JOIN
SELECT
	course.cname,
	teacher.tname 
FROM
	course
	INNER JOIN teacher ON course.teacher_id = teacher.tid

2、查询平均成绩大于八十分的同学的姓名和平均成绩

# 1.需要的表：score student 
# 2.查看表
-- select * from score
-- SELECT * from student
# 3.思路：先分组，再找出平均成绩大于80的同学,根据学生id，找出学生名字
SELECT student_id,avg(num) as avg_num from score GROUP BY student_id having avg(num) > 80
# 根据学生id，找出学生名字
SELECT
	student.sname,
	t1.avg_num 
FROM
	student
	INNER JOIN ( SELECT student_id, avg( num ) AS avg_num FROM score GROUP BY student_id HAVING avg( num ) > 80 ) AS t1 ON student.sid = t1.student_id

3、查询没有报李平老师课的学生姓名

# 1.需要的表：teacher course score student
# 2.查看表
select * from course
-- SELECT * from teacher
SELECT * from score
# 3.思路：查找李萍的id ，根据id获取他教的课程，得到课程号，学生号，获取学生姓名
-- select tid from teacher where tname = '李平老师'
-- select cid from course WHERE teacher_id in (select tid from teacher where tname = '李平老师')
-- select DISTINCT student_id from score WHERE course_id in (select cid from course WHERE teacher_id in (select tid from teacher where tname = '李平老师'))
SELECT
	sname
FROM
	student 
WHERE
	sid NOT IN (
	SELECT DISTINCT
		student_id 
	FROM
		score 
	WHERE
		course_id IN (
		SELECT
			cid 
		FROM
			course 
		WHERE
		teacher_id IN ( SELECT tid FROM teacher WHERE tname = '李平老师' )))

4、查询没有同时选修物理课程和体育课程的学生姓名

# 1.需要的表：course student score
# 2.查看表
-- select * from course
-- SELECT * from student
-- select * from score
# 3.思路：
# 查找课程的编号
-- select cid from course where cname in ('物理','体育')
# 筛选在score表中有没物理号和体育号的
-- select * from score where course_id IN (select cid from course where cname in ('物理','体育'))
# 分组 找出没有选两个的
-- select student_id from score where course_id IN (select cid from course where cname in ('物理','体育')) GROUP BY student_id HAVING count(student_id) = 1

SELECT
	sname 
FROM
	student 
WHERE
	sid IN (
	SELECT
		student_id 
	FROM
		score 
	WHERE
		course_id IN (
		SELECT
			cid 
		FROM
			course 
		WHERE
		cname IN ( '物理', '体育' )) 
	GROUP BY
		student_id 
	HAVING
	count( student_id ) = 1)

5、查询挂科超过两门(包括两门)的学生姓名和班级

# 1.需要的表：score student class
# 2.查看表
select * from score
-- SELECT * from student
-- select * from class
# 3.思路：
# 找出挂科的，在分组,获取学生号
-- SELECT student_id from score where num < 60 group by student_id HAVING count(student_id) >=2
# 拼接表
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( student_id ) >= 2)

python操作MySQL

1.连接服务器

import pymysql

conn = pymysql.connect(
    host = '127.0.0.1',
    port = 3306,
    user = 'root',
    password='321',
    database='d6',
    charset='utf8mb4',
    autocommit=True  # 执行增、改、删除自动执行 = conn.commit(二次确认)
)

2.产生以个游标对象(等待输入命令)

cursor = pymysql.cursors.DictCursor 使查询的数据以字典的方式，便于查看

cursor = conn.cursor(cursor = pymysql.cursors.DictCursor)

3.编写一个SQL语句

语句自定义

sql1 = 'select * from emp'

4.发送给服务端

cursor.execute(sql1)的返回值是执行SQL语句表中受影响的行数，没有实际作用

cursor.execute(sql1)

5.获取命令的执行结果

cursor.fetchall()是获取所有的数据，结果是列表套字典

res =cursor.fetchall()
print(res)

获取结果

cursor.fetchall()

获取结果中的所有数据

res =cursor.fetchall()
print(res)
res1 = cursor.fetchall()
print(res1)

结果：
[{...},{...},{...}...{...},{...},{...}]
[]
cursor.fetchone()

获取结果中的一条数据，之后再获取数据，直接往后取数据

res = cursor.fetchone()
print(res)
res1 = cursor.fetchone()
print(res1)

结果:
{'id':1,....}
{'id':2,...}
cursor.fetchmany(n)

获取结果中指定的数据条数，如果n大于表中数据条数，就获取全部数据就可以了

res = cursor.fetchmany(5)
print(res)
res1 = cursor.fetchmany(4)
print(res1)

结果:
[{'id':1,...},...,{'id':5,...}]
[{'id':6,...},...,{'id':9,...}]
cursor.scroll(n,mode= 'relative')

基于当前位置往后移动n条数据，类似于文件光标得概念

res = cursor.fetchmany(5)
print(res)

cursor.scroll(3,mode= 'relative')

res1= cursor.fetchone()
print(res1)

结果:
[{'id':1,...},...,{'id':5,...}]
{'id':9,...}
cursor.scroll(n,mode='absolute')

基于数据集开头的位置往后移动n条数据

res = cursor.fetchmany(5)
print(res)

cursor.scroll(3,mode='absolute')

res1= cursor.fetchone()
print(res1)

结果
[{'id':1,...},...,{'id':5,...}]
{'id':4,...}

SQL注入问题

导入

import pymysql

# 1.连接服务器
conn = pymysql.connect(
    host = '127.0.0.1',
    port = 3306,
    user = 'root',
    password='321',
    database='d10',
    charset='utf8mb4',
    autocommit=True
)
# 2.产生以个游标对象
cursor = conn.cursor(cursor = pymysql.cursors.DictCursor)
# 3.编写一个SQL语句
user_name = input('username>>>:').strip()
password = input('password>>>:').strip()
sql = "select * from t1 where name='%s' and pwd ='%s' "%(user_name,password)
#4.发送给服务端
cursor.execute(sql)
# 5.获取命令的执行结果
res =cursor.fetchall()
if res:
    print("登陆成功")
    print(res)
else:
    print("用户名或密码错误")

缺陷：

1.只需要用户名，就可以登陆成功

username>>>:jason' -- hddnf
password>>>:
select * from t1 where name='jason' -- hddnf' and pwd ='' 
登陆成功
[{'id': 2, 'name': 'jason', 'pwd': '123'}]

2.不需要用户名和密码也能成功

username>>>:xxx' or 1=1 -- hsfdgd
password>>>:
select * from t1 where name='xxx' or 1=1 -- hsfdgd' and pwd ='' 
登陆成功
[{'id': 1, 'name': 'nana', 'pwd': '123'}, {'id': 2, 'name': 'jason', 'pwd': '123'}, {'id': 3, 'name': 'kevin', 'pwd': '123'}, {'id': 4, 'name': 'oscar', 'pwd': '123'}, {'id': 5, 'name': 'tony', 'pwd': '123'}]

原因

特殊的字符产生了特殊的含义

1.针对：只需要用户名，就可以登陆成功。

mysql中有四种注释：1、单行注释“--”，语法“-- 注释内容”；2、单行注释“#”，语法“#注释内容”；3、多行注释“/**/”，语法“/*注释内容 */”；4、内联注释“/*! */”，语法“/*!注释内容 */”。

也就是用户名后面的被注释掉了：select * from t1 where name='jason' -- hddnf' and pwd ='' 

2.针对：不需要用户名和密码也能成功  

where 条件后结果为True 的话，直接执行sql语句。

也就是：name='xxx' or 1=1 结果为True,所以直接执行了查询语句:select * from t1 where name='xxx' or 1=1 -- hsfdgd' and pwd =''

注入的本质

利用了一些特殊符号的组合产生了特殊的含义从而避免了正常业务逻辑

措施

针对用户输入的数据，不需要我们自己处理,因为cursor.execute()会自动识别%s,并自动过滤各种符号，最后合并数据

# 3.编写一个SQL语句
user_name = input('username>>>:').strip()
password = input('password>>>:').strip()
sql = "select * from t1 where name=%s and pwd =%s "
print(sql)
#4.发送给服务端
cursor.execute(sql,(user_name,password))

补充

cursor.executemany()可以一次性插入很多数据

import pymysql
# 1.连接服务器
conn = pymysql.connect(
    host = '127.0.0.1',
    port = 3306,
    user = 'root',
    password='321',
    database='d10',
    charset='utf8mb4',
    autocommit=True
)
# 2.产生以个游标对象
cursor = conn.cursor(cursor = pymysql.cursors.DictCursor)

# 3.编写一个SQL语句
sql = 'insert into t1(name,pwd) values(%s,%s)'
cursor.executemany(sql,[('nana1','123'),('nana2','123'),('nana3','123'),('nana4','123'),('nana5','123'),('nana6','123')])

知识点补充

1.as 语法

给字段起别名、起表名

SELECT
	student.sname,
	t1.avg_num 
FROM
	student
	INNER JOIN ( SELECT student_id, avg( num ) AS avg_num FROM score GROUP BY student_id HAVING avg( num ) > 80 ) AS t1 ON student.sid = t1.student_id

2.comment语法

给表添加注释信息，可以用 show create table 表名查看，也可以用use information_schema;select * from tables where table_name = 't1'\G;查看

 create table t1(id int primary key auto_increment,name varchar(32)) comment '这是学生信息';
 
 mysql> show create table t1\G;
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='这是学生信息'



mysql> use information_schema;

mysql> show tables;
mysql> select * from tables where table_name = 't1'\G;

*************************** 7. row ***************************
  TABLE_CATALOG: def
   TABLE_SCHEMA: d9
     TABLE_NAME: t1
     TABLE_TYPE: BASE TABLE
         ENGINE: InnoDB
        VERSION: 10
     ROW_FORMAT: Compact
     TABLE_ROWS: 0
 AVG_ROW_LENGTH: 0
    DATA_LENGTH: 16384
MAX_DATA_LENGTH: 0
   INDEX_LENGTH: 0
      DATA_FREE: 0
 AUTO_INCREMENT: 1
    CREATE_TIME: 2022-08-18 19:28:52
    UPDATE_TIME: NULL
     CHECK_TIME: NULL
TABLE_COLLATION: utf8_general_ci
       CHECKSUM: NULL
 CREATE_OPTIONS:
  TABLE_COMMENT: 这是学生信息

给字段添加注释

	create table t2(
    	id int comment '用户编号',
       name varchar(16) comment '用户名'
    ) comment '用户表';

3.concat、concat_ws语法

concat用于分组之前多个字段数据的拼接；

mysql>  select concat(name,salary) from emp limit 5;
+---------------------+
| concat(name,salary) |
+---------------------+
| jason7300.33        |
| tom1000000.31       |
| kevin8300.00        |
| tony3500.00         |
| owen2100.00         |
+---------------------+

mysql>  select concat(name,'|',salary,'|',office) from emp limit 5;
+------------------------------------+
| concat(name,'|',salary,'|',office) |
+------------------------------------+
| jason|7300.33|401                  |
| tom|1000000.31|401                 |
| kevin|8300.00|401                  |
| tony|3500.00|401                   |
| owen|2100.00|401                   |
+------------------------------------+

concat_ws如果有多个字段，并且分隔一致，可以使用该方法减少代码

mysql> select concat_ws('|',name,salary,office) from emp limit 5;
+-----------------------------------+
| concat_ws('|',name,salary,office) |
+-----------------------------------+
| jason|7300.33|401                 |
| tom|1000000.31|401                |
| kevin|8300.00|401                 |
| tony|3500.00|401                  |
| owen|2100.00|401                  |
+-----------------------------------+

4.exists语法

exists后面的sql语句如果有结果那么会执行exists前面的sql语句，否则，就不执行

mysql>  select * from emp1 where exists (select * from emp where id <100);
+----+--------+--------+------+--------+
| id | name   | gender | age  | dep_id |
+----+--------+--------+------+--------+
|  1 | jason  | male   |   18 |    200 |
|  2 | dragon | female |   48 |    201 |
|  3 | kevin  | male   |   18 |    201 |
|  4 | nick   | male   |   28 |    202 |
|  5 | owen   | male   |   18 |    203 |
|  6 | jerry  | female |   18 |    204 |
+----+--------+--------+------+--------+

mysql>  select * from emp1 where exists (select * from emp where id >100);
Empty set (0.00 sec)

标签：name,--,Navicat,cursor,可视化,student,软件,id,select
From： https://www.cnblogs.com/luonacx/p/16608697.html

相关文章

  • Bridge 2022 for Mac(br 2022文件管理软件)中文版
    Bridge2021forMac是一款组织工具应用，从AdobeBridge2021中可以查看、搜索、排序、管理和处理图像文件,还可以使用AdobeBridge2021mac中文版来创建新文件夹、对......
  • 备份软件Veritas NetBackup(NBU)8.1.1的Linux客户端部署
    企业级备份软件VeritasNetBackup(NBU)8.1.1的Linux客户端部署原创 yuanfan2012 WalkingCloud 2022-06-1300:00 发表于浙江收录于合集#Linux学习笔记268个#C......
  • Adobe Bridge 2022 for mac(br2022)资源管理软件
    Bridge2022mac版更新了，这是一款Adobe系列的资源管理软件，它拥有强大的媒体集中资产访问功能：可以查看、搜索、排序、管理和处理图像文件。软件可以使用高级过滤器、集合和......
  • 车间3D可视化管控平台
    自主研发的基于HTML5的3D图形渲染引擎+mmsDT开发平台，为Web可视化提供了丰富的展现形式和视觉效果。木棉树软件聚焦工业数字孪生的生产管控、智慧城市的监控运维等可视化应......
  • centos常用命令及软件安装
    常用命令pwd查看当前所在目录free-h查看内存使用情况df-lh查看磁盘空间使用情况uname-a查看系统版本nohub[命令]&后台运行ps-ef|grep[关键字]查看相关进程的......
  • 软件设计原则
    1开闭原则对扩展开放，对修改关闭。在程序需要进行拓展的时候，不能去修改原有的代码，实现一个热插拔的效果。简言之，是为了使程序的扩展性好，易于维护和升级。想要达到这样的......
  • 软件测试100天上岸3-测试有哪些最高原则
    测试原则是一个测试人员时刻要铭记在心的，甚至要形成一种本能，指导测试工作。原则1：测试找不出所有的Bug软件的复杂性仅次于生命体，甚至现在很多软件都已经有了人工智能的属......
  • Tableau Desktop 2019 for Mac(数据可视化分析)中文版
    TableauDesktop2019forMac是一款方便实用，功能强大的数据可视化分析软件。Tableau利用技术优势，能够将数据图片转化为数据库查询，利用视觉观察图案的天赋能力，识别趋势，在......
  • 轻松设计流程图的Mac软件
    NCHClickChartsProMac是一款可以轻松设计流程图和图表的软件。NCHClickCharts可以快速创建组织，流程，思维导图，UML图表等的强大视觉效果。映射您的价值流和数据流。找出流......
  • IDA Pro for Mac(静态反编译软件)中文版
    idaproformac是一款静态反编译软件之一，这款mac安全工具它不仅可以应用在反编译和动态调试等强大的逆向工程领域，还支持对多种处理器不同类型的可执行模块进行反汇编处理，......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99