python3 数据库基本操作及与python的连接
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了python3 数据库基本操作及与python的连接,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含8027字,纯文字阅读大概需要12分钟。
内容图文
分组查询和过滤
过滤:
SELECT 列名 FROM 表名 WHERE 过滤条件
使用where,可以用来过滤单行,如果想要过滤分组或者聚合之后的数据,要加having
正确:
查看每个国家的总人数,年龄总和,平均年龄,最高年龄,最低年龄,但是排除某个国家
SELECT country,COUNT(uname),SUM(age),AVG(age),MAX(age),MIN(age) FROM t_user WHERE country!=‘吴国’ GROUP BY country;
列出每个国家小于20岁的人
SELECT country,uname FROM t_user WHERE age<20 GROUP BY country;
错误:
显示每个国家的平均年龄,但是仅显示那些总年龄超过100的国家
SELECT country,AVG(age) FROM t_user WHERE SUM(age)>100 GROUP BY country;
上面的写法报错,where在聚合前先筛选记录,但此时表中并没有sum(age)这条记录
正确:
having在聚合后对组记录进行筛选
SELECT country,AVG(age) FROM t_user GROUP BY country HAVING SUM(age)>100;
注意顺序:having放在分组之后,因为作用的对象不同。WHERE 子句作用于表和视图,HAVING 子句作用于组
总结:
having要跟在group by之后,对分组查询的结果进行过滤(过滤分组)
where要出现在group by之前,执行表中所有数据来进行过滤(过滤行)
另外,having可以用聚合函数,并支持所有where子句操作符
主键,外键
主键约束(primary key):要求主键列数据唯一,并且不能为空
外键约束(foreign key):用于两表间建立关系
主键约束(primary key): 确定一个表的唯一性
主键约束是数据库中最重要的一种约束
在关系中,主键值不能为空,也不允许出现重复(非空且唯一)
一个表中只允许一个主键
主键是表中能够唯一确定一个行数据的字段
主键字段可以是单字段或多字段的组合
外键:用来和其他表建立联系
外键具有保持数据完整性和一致性的机制,目前MySQL只在InnoDB引擎下支持
(ENGINE=INNODB)
关系:
成绩表中的学号不是成绩表的主键,但它和学生表中的学号相对应,并且学生表中的学号是学生表的主键,则可以设置成绩表中的学号是学生表的外键同理 成绩表中的课程号是课程表的外键
CREATE TABLE t_student(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
sname VARCHAR(25),
sex VARCHAR(10)
);
INSERT INTO t_student VALUES (1,"小明","男"),(2,"小红","女");
CREATE TABLE t_class(
cname VARCHAR(25),
cid INT,
FOREIGN KEY(cid) REFERENCES t_student(id)
);
索引和视图
索引:当数据库中存在很多条记录,例如几十万条,查询速度就成了一个问题
此时可以建立类似目录的数据库对象,实现快速查询,这就是索引
例如在书中查询某个内容时,先在目录中查询,然后根据目录所示的页码找到查询内容,大大缩短了查询时间
索引的作用:索引用于快速找出在某个列中有一特定值的行,不使用索引,MySQL必须从第一条记录开始读完整个表,直到找出相关的行,表越大,查询数据所花费的时间就越多,如果表中查询的列有一个索引,MySQL能够快速到达一个位置去搜索数据文件,而不必查看所有数据,那么将会节省很大一部分时间
CREATE INDEX index_1 ON t_user(uname)
创建一个索引 索引名 从 表名 在哪个字段
CREATE TABLE mytable( -- 创建表时直接指定
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
INDEX index_1 (username)
删除索引
DROP INDEX index_1 ON t_user
);
视图:就是一条SELECT语句执行后返回的结果集(显示结果是一个表)
-- 查看每个国家的总人数,年龄总和,平均年龄,最高年龄,最低年龄
SELECT country,COUNT(uname),SUM(age),AVG(age),MAX(age),MIN(age) FROM t_user GROUP BY country;
为上面的语句创建视图:
CREATE VIEW v_user AS
SELECT country,COUNT(uname),SUM(age),AVG(age),MAX(age),MIN(age) FROM t_user GROUP BY country;
SELECT * FROM v_user; --以后在使用上面的查询语句时只需要使用视图名
视图的作用:专门进行某些查询
方便操作:减少复杂的SQL语句,增强可读性
更加安全:在外界访问你的数据库时,经常不想让他访问所有数据这时候可以建立视图,在视图中写好查询语句,同时限制外界的访问权限只能通过你给定的视图去查询
事务
事务:数据库的不一致性:
事物:是一个操作序列,这些操作只能都做,或者都不做,是一个不可分割的工作单位
例子:
假设该场景发生于一个银行转账背景下,月中到了发工资的日子。学校打算给A老师发放一个月的工资:
1.学校财务核对A老师工资单
2.确认学校账上还有这么多钱
3.向银行提出转账申请,银行扣除学校财务卡上的指定金额
4.银行向A老师工资卡打入指定金额
5.银行汇报双方交易完成
但是,当这个过程执行完第3步的时候,突然断电。待电力系统回复之后,银行并不会继续执行4、5步甚至连1,2,3步的操作记录都丢失了。此时出现了如下的问题:
学校认为,工资已经发出
A老师认为,学校还没有发工资
银行认为,从来就没有发生过转账的事情
整个过程可以用一个词来描述:数据库中的数据产生了“不一致性”
事物:是一个操作序列,这些操作只能都做,或者都不做,是一个不可分割的工作单位
1.在操作MySQL过程中,对于一般简单的业务逻辑或中小型程序而言,无需考虑应用MySQL事务
事务主要用于处理操作量大,复杂度高的数据
2.MySQL中,事务由单独单元中的一个或多个SQL语句组成。在这个单元中,每个MySQL语句是相互依赖的。而整个单独单元作为一个不可分割的整体,要么都做,或者都不做
3.如果单元中某条SQL语句一旦执行失败或产生错误,可以让整个单元回滚。所有受到影响的数据将返回到事务开始以前的状态(保证了数据的完整性)
事物语句:只有使用了 Innodb 数据库引擎的数据库或表才支持事务
开启:begin 开启一个事物
提交:commit 将事务中的SQL语句提交给数据库
回滚:rollback 取消掉之前的所有操作(撤销事务
在这里插入代码片CREATE TABLE t_person(
id INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(10),
money INT
) TYPE=INNODB;创建一个InnoDB类型的数据表
或者在创建表之后改变:ALTER TABLE t_person TYPE=INNODB;
INSERT INTO t_person VALUES(1,'小明',1000);
INSERT INTO t_person VALUES(2,'丽丽',2000);
上面的代码中小明有1000块钱,丽丽有2000块钱
接下来要实现小明给丽丽转账500元
BEGIN; -- 开始事物
UPDATE t_person SET money=money-500 WHERE id=1;
UPDATE t_person SET money=money+500 WHERE id=2;
SELECT * FROM t_person; -- 查询结有误果是否
COMMIT; -- 发现结果无误,提交事物,提交后数据库中数据会修改
创建事务的一般过程是:开始事务、创建事务、应用SELECT语句查询数据、提交事务
BEGIN; -- 开始事物
UPDATE t_person SET money=money-500 WHERE id=1;
UPDATE t_person SET money=money+600 WHERE id=2;
SELECT * FROM t_person; -- 查询结有误果是否
ROLLBACK; -- 结果有误,回滚事物,取消所有操作
总结:
我们可以声明一个事务的开始,在确认提交或者指明放弃前的所有操作,都先在一个叫做事务日志的临时环境中进行操作。待操作完成,确保了数据一致性之后,那么我们可以手动确认提交,也可以选择放弃以上操作。
注意: 一旦选择了提交,就不能再利用回滚来撤销更改了
关联关系
表的关系:mysql相互关联的表之间存在一对一,一对多(多对一),多对多的关系:
1,一对一的关系:表1中的一条数据,对应表2中的一条数据
这种关系即多个表具有相同的主键,A表中的一条数据对应B表中的一条数据。实际中用的并不多,因为完全可以将这种关系的合并为一张表(一夫一妻)
2.一对多(多对一)的关系:表1中的一条数据对应表2中的多条数据
其中表1的主键是表2的外键,(即表1的某字段作为主键,表2的相同字段绑定到表1的主键字段上)
CREATE TABLE stu( -- 学生表
stuId INT,
name VARCHAR(10) NOT NULL,
PRIMARY KEY(stuId)
);
CREATE TABLE score_1( -- 成绩表
stuId INT,
score VARCHAR(32), #一个学生有多门成绩
FOREIGN KEY (stuId) REFERENCES stu(stuId)
);
3.多对多的关系:
比如:一个老师教很多学生的课,一个学生选了很多老师的课。那么,老师和学生之间就是多对多的关系多对多的关系要借助于第3张表
1,首先创建老师表,设置id为主键
CREATE TABLE teacher(
teacherId INT,
NAME VARCHAR(10) NOT NULL,
PRIMARY KEY(teacherId)
);
2,然后创建学生表,同样设置id为主键
CREATE TABLE stu(
stuId INT,
NAME VARCHAR(10) NOT NULL,
PRIMARY KEY(stuId)
);
3,最后创建一个课程表,将前两张表关系起来
CREATE TABLE score(
scoresname VARCHAR(32)
stuId INT,
teacherId INT,
FOREIGN KEY (stuId) REFERENCES stu(stuId),
FOREIGN KEY (teacherId) REFERENCES teacher(teacherId)
);
子查询
子查询:子查询在主查询前执行一次,主查询使用子查询的结果
CREATE TABLE stu(
stuID INT,
sname VARCHAR(32),
score INT,
PRIMARY KEY(stuID)
);
INSERT INTO stu VALUES (1,‘xiaoming’,60), (2,‘xiaoli’,70);
1,如何查询所有比小明成绩高的学生名字
SELECT sname FROM stu WHERE score > (SELECT score FROM stu WHERE sname = 'xiaoming');
2,查询成绩高于平均成绩的学生姓名和成绩
SELECT sname,score FROM stu WHERE score > (SELECT AVG(score) FROM stu);
数据库与python的连接
def exceDML(sql): #用来执行插入
conn = pymysql.connect(host='localhost', user='root', password='123123', db='test1', charset='utf8')
cur = conn.cursor()
# 通过cursor的对象去执行SQL语句
cur.execute(sql)
# 提交事物
conn.commit()
def close(): #用来关闭连接
if cur:
cur.close()
if conn:
conn.close()
from day3 import mysqlHelper
name = input("请输入名字:")
id = input("请输入ID:")
sql1 = 'insert into t_user values(%d,"%s")'%(int(id),name)
sql2 = 'select * from t_user'
mysqlHelper.exceDML(sql1)
print(mysqlHelper.getall(sql2))
mysqlHelper.close()
内容总结
以上是互联网集市为您收集整理的python3 数据库基本操作及与python的连接全部内容,希望文章能够帮你解决python3 数据库基本操作及与python的连接所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。