python---关系型数据库MySQL

最新推荐文章于 2023-12-05 17:50:00 发布

CodingAndCoCoding

最新推荐文章于 2023-12-05 17:50:00 发布

阅读量733

点赞数

分类专栏： MySQL Python 高级编程

本文链接：https://blog.csdn.net/Wangtuo1115/article/details/103927180

版权

Python 同时被 3 个专栏收录

43 篇文章 0 订阅

订阅专栏

高级编程

10 篇文章 0 订阅

订阅专栏

MySQL

7 篇文章 0 订阅

订阅专栏

文章目录

一、数据库简介
二、关系型数据库
三、MySQL简介与安装
四、数据库操作
五、查询操作
六、pymysql数据库编程

一、数据库简介

为什么需要数据库？
人类在进化的过程中，，**数据量越来越大，对于数据的记录和准确查找，**成为了一个重大难题。数据库系统解决的问题：持久化存储，优化读写，保证数据的有效性。

数据库分类
关系型数据库：指采用了关系模型来组织数据的数据库。关系模型指的就是二维表格模型，而一个关系型数据库就是由二维表及其之间的联系所组成的一个数据组织。主流的关系型数据库有: Oracle、Microsoft SQL Server、MySQL、PostgreSQL，SQLite、MariaDB(MySQL的一个分支)Microsoft Access、SAP。

非关系型数据库：指非关系型的，分布式的，以键值对存储且结构不固定，可以减少一些时间和空间的开销。非关系型数据库都是针对某些特定的应用需求，主要分为以下几类：
1). 面向海量数据访问的面向文档数据库：MongoDB、Amazon DynamoDB、Couchbase等。
2). 面向高性能并发读写的key-value数据库: Redis、 Memcached等。
3). 面向搜索数据内容的搜索引擎：Elasticsearch，Splunk，Solr，MarkLogic和Sphinx等。
4). 面向可扩展性的分布式数据库：Cassandra，HBase等。

二、关系型数据库

关系型数据库是依据关系模型来创建的数据库。
关系模型就是“一对一，一对多，多对多”等关系模型，关系模型就是指二维表格模型，因而一个关系型数据库就是由二维表及其之间的联系组成的一个数据组织。
E-R模型
当前物理的数据库都是按照E-R（实体关系）模型进行设计的,
• E表示entry，实体
• R表示relationship，关系
• 一个实体转换为数据库中的一个表
**注意：**关系描述两个实体之间的对应规则，包括: 一对一 ,一对多, 多对多

范式理解
经过研究和对使用中问题的总结，对于设计数据库提出了一些规范，这些规范被称为范式

• 第一范式（1NF)：列不可拆分 , 即无重复的域
• 第二范式（2NF)：唯一标识，即拥有实体的唯一标识(eg: 身份证、id号等)
• 第三范式（3NF)：引用主键 ,即每列数据都与主键直接相关。

三大范式只是一般设计数据库的基本理念，可以建立冗余较小、结构合理的数据库。如果有特殊情况，当然要特殊对待，数据库设计最重要的是看需求跟性能，需求>性能>表结构。所以不能一味的去追求范式建立数据库。

存在问题:
数据冗余，每条记录都含有相同的信息；
删除异常；
插入异常；
修改异常；

三大范式只是一般设计数据库的基本理念,可以建立冗余较小、结构合理的数据库。如果有特殊情况,当然要特殊对待,数据库设计最重要的是看需求跟性能,需求>性能>表结构。
所以不能一味的去追求范式建立数据库。

三、MySQL简介与安装

MySQL是一个关系型数据库管理系统,由瑞典MySQL AB 公司开发,目前属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一。

MySQL常用存储引擎分析：
数据库存储引擎是数据库底层软件组织,进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能, MySQL的核心就是存储引擎。
MySQL查询存储引擎SQL语句：show engines

安装：
在这里插入图片描述

数据库服务管理：
在这里插入图片描述
安全性密码设置：

mysql_secure_installation

在这里插入图片描述

允许远程连接：
用户授权：允许root用户通过westos密码在任意主机（%）远程登录并操作数据库

grant all privileges on *.* to root@'%' identified by 'westos';

数据库登录：

mysql -uroot -pwestos

四、数据库操作

1、数据库操作：
在这里插入图片描述
显示所有数据库：show databases；

创建数据库：
创建数据库Blog并指定编码格式为utf8(存储的数据为中文，需要设置);

create database Blog default charset='utf8';

显示所有数据库名称:

show databases;

在这里插入图片描述
选择/切换数据库：

use 数据库名;

查看当前选择的数据库

select database();

查看当前数据库的所有数据库表：

show tables；

在这里插入图片描述
查看数据库表格内容：
查看mysql数据库中user数据库表的三列内容: Host,User,Password

MariaDB [(none)]> select Host,User,Password  from mysql.user;

在这里插入图片描述
允许远程用户连接数据库：
172.25.254.40这台主机以root用户身份远程登录，密码为westos，访问数据库的所有内容(.)

任意一台主机以root用户身份远程登录，密码为westos，访问数据库的所有内容(.)，sql语句中的%等价于*：

MariaDB [(none)]> grant all on *.* to root@'%' identified by 'westos';

在这里插入图片描述
任意一台主机以root用户身份远程登录，密码为westos，访问mysql数据库的所有表(mysql.*)：

	MariaDB [(none)]> grant all on mysql.* to root@'%' identified by 'westos';
	MariaDB [(none)]> select Host,User,Password  from mysql.user;

远程登录：

 mysql -h 172.25.254.18 -uroot -pwestos

2、表操作
在这里插入图片描述
表创建: 数据完整性
• 一个数据库就是一个完整的业务单元,可以包含多张表,数据被存储在表中
• 在表中为了更加准确的存储数据,保证数据的正确有效,可以在创建表的时候,为表添加一些强制性的验证, 包括数据字段的类型、约束。

表的创建：字段类型
在mysql中包含的数据类型很多,这里主要列出来常用的几种:
• 数字:int,decimal, float
• 字符串:varchar,text
• 日期:datetime
• 布尔:bool
• 主键 primary key
• 非空 not null
• 惟一 unique
• 默认 default
• 外键 foreign key
• 自动增长 auto_increment
创建数据库表userinfo, 两列数据，varchar可变长字符串, not null数据非空,unique数据唯一。

MariaDB [Blog]> create table userinfo(
    ->          username varchar(20)  not null unique,
    ->          password varchar(20) not null)
    -> ;

查看表结构：

MariaDB [Blog]> desc userinfo;

添加数据到数据表中：

MariaDB [Blog]> insert into userinfo values('user2', 'passwd');
MariaDB [Blog]> insert into userinfo values('张三', 'passwd');

数据查询：

MariaDB [Blog]> select * from userinfo;

修改表结构: 添加一列信息、修改一列信息、删除一列信息：

MariaDB [Blog]> alter table userinfo add gender varchar(3);
MariaDB [Blog]> alter table userinfo change gender sex varchar(3);
MariaDB [Blog]> alter table userinfo drop sex;

数据表重命名：

MariaDB [Blog]> rename table userinfo to users;

查看表的常见语句：

MariaDB [Blog]> show create table users;

删除数据表：

MariaDB [Blog]> drop table users;

删除数据库：

MariaDB [Blog]> drop database Blog;

3、数据操作
在这里插入图片描述
增加数据
1、全列插入：

MariaDB [Blog]> insert into users values(1, 'user1', 'password');

2、缺省插入：

MariaDB [Blog]> insert into users(username) values('user2');

3、同时插入多条数据：

MariaDB [Blog]> insert into users(username) values('user3'),('user4'), ('user5');

修改数据：

MariaDB [Blog]> update users set password='666666' where username='user4';

删除数据：

MariaDB [Blog]> delete from users where username='user4';

4、备份与恢复
在这里插入图片描述
数据备份：

mysqldump -uroot -pwestos Blog > students.sql

数据恢复：

mysql -uroot -pwestos StudentProject <  students.sql

五、查询操作

1、查询的基本语法

select * from 表名;

• from关键字后面写表名,表示数据来源于是这张表
• select后面写表中的列名,如果是*表示在结果中显示表中所有列
• 在select后面的列名部分,可以使用as为列起别名,这个别名出现在结果集中
• 如果要查询多个列,之间使用逗号分隔

2、消除重复行
在select后面列前使用distinct可以消除重复的行

select distinct 列 from 表;

3、条件
使用where子句对表中的数据筛选,结果为true的行会出现在结果集中

select * from 表名 where 条件;

优先级
•小括号,not,比较运算符,逻辑运算符
•and比or先运算,如果同时出现并希望先算or,需要结合()使用

在这里插入图片描述
1、查询students表中的所有记录的sname、ssex和class列。

MariaDB [Blog]> select  sname,ssex,class from students;

2、查询教师所有的单位即不重复的Depart列。

MariaDB [Blog]> select distinct depart  from  teachers;

3、查询students表的所有记录。

MariaDB [Blog]> select  *  from students;

4、查询scores表中成绩在60到80之间的所有记录。

MariaDB [Blog]> select * from scores where degree between 60 and 80;

5、查询scores表中成绩为85，86或88的记录。

MariaDB [Blog]> select * from scores where degree=85 or degree=86 or degree=88;
MariaDB [Blog]> select * from scores where degree in (85,86,88);

6、查询课程名称包含数学的课程信息。(模糊查询)

MariaDB [Blog]> select * from courses where cname like '%数学%';
MariaDB [Blog]>  select * from courses where cname like '__数学';

4、分组
• 按照字段分组,表示此字段相同的数据会被放到一个组中
• 分组后,只能查询出相同的数据列,对于有差异的数据列无法出现在结果集中
• 可以对分组后的数据进行统计,做聚合运算

select 列1,列2,聚合... from 表名 group by 列1,列2,列3...

5、分组后的数据筛选
having后面的条件运算符与where的相同

select 列1，列2，聚合... from 表名 group by 列1，列2，列3... having 列1，...聚合...

1、查询scores表中至少有5名学生选修的并以3开头的课程的平均分数。
1). 至少有5名学生选修(聚合函数): count(*) >= 5
2). 以3开头的课程(模糊查询): cno like ‘3%’
3). 平均分数(聚合函数): avg(degree)

select cno,avg(degree) from scores where cno like '3%'  group by cno having count(cno)>=5;

2、查询最低分大于70，最高分小于90的sno列。

select * from scores where max(degree) < 90 and min(degree) > 70;(错误的SQL语句)
MariaDB [Blog]> select sno  from scores group by sno having max(degree)<90 and min(degree)>70;（正确的）

3、查询scores中选学一门以上课程的同学中分数为非最高分成绩的记录。

- 非最高分成绩： degree != max(degree)
- 选学一门以上课程: count(cno) > 1
- 分组: sno
MariaDB [Blog]> select * from scores group  by sno having count(cno)>1 and degree!=max(degree);

6、对比where与having
• where是对from后面指定的表进行数据筛选,属于对原始数据的筛选
• having是对group by的结果进行筛选

7、聚合
在这里插入图片描述
1、查询“95031”班的学生人数。

MariaDB [Blog]> select * from students where class='95031';
MariaDB [Blog]> select count(*) from students where class='95031';
MariaDB [Blog]> select count(*) as studentCount from students where class='95031'; (最终版)

2、查询‘3-105’号课程的平均分。

MariaDB [Blog]> select avg(degree) as avgScore  from scores where cno='3-105';

8、排序
为了方便查看数据，可以对数据进行排序：

select * from 表名 order by 列1 asc|desc，列2 asc|desc，...

• 将行数据按照列1进行排序,如果某些行列1的值相同时,则按照列2排序,以此类推
• 默认按照列值从小到大排列
• asc从小到大排列,即升序, desc从大到小排序,即降序

1、以class降序查询students表的所有记录。

MariaDB [Blog]> select * from students order by class desc;
MariaDB [Blog]> select * from students order by class;（默认升序）

1、以cno升序、degree降序查询scores表的所有记录。
以cno升序、degree降序: 当cno相同时，按照degree降序排列。
cno degree
1 3
1 2
2 3

MariaDB [Blog]> select * from scores order by cno,degree desc;

9、获取部分行
命令：

select * from 表名 limit start,count

• 从start开始,获取count条数据
• start索引从0开始

1、查询成绩最高的前5名学生信息：

MariaDB [Blog]> select * from scores order by degree desc limit 5;(默认从第0个索引开始， 拿前5条数据)

±----±------±-------+
| sno | cno | degree |
±----±------±-------+
| 103 | 3-105 | 92.0 |
| 107 | 3-105 | 91.0 |
| 105 | 3-105 | 88.0 |
| 103 | 3-245 | 86.0 |
| 101 | 6-166 | 85.0 |
±----±------±-------+

MariaDB [Blog]> select * from scores order by degree desc limit 2,3;(从第2个索引开始获取3条数据)

±----±------±-------+
| sno | cno | degree |
±----±------±-------+
| 105 | 3-105 | 88.0 |
| 103 | 3-245 | 86.0 |
| 101 | 6-166 | 85.0 |
±----±------±-------+
3 rows in set (0.01 sec)
10、多表查询
eg:
A表：
aid num
1 10
2 20
3 30

B表:
bid bname
1 粉条
3 粉丝
4 粉带

left join(左联接)： select * from A left join B on A.aid = b.bid;
right join(右联接): select * from A right join B on A.aid = b.bid;
inner join(等值联接): select * from A inner join B on A.aid = b.bid;

1、查询所有学生的sname、cno和degree列：

MariaDB [Blog]> select sname,cno,degree from students inner join scores on students.sno=scores.sno;

2、查询所有学生的sname、cname和degree列。

MariaDB [Blog]> select distinct  sname,cname,degree from students inner join scores
on(students.sno=scores.sno) inner join courses  on(scores.cno=courses.cno) order by sname;

总结：
完整的select语句：

select distinct * from 表名 where ...  group by ... having ... order by ... limit start,count

注意：
实际使用中,只是语句中某些部分的组合,而不是全部
执行顺序为:
1. from 表名
2. where …
3. group by …
4. select distinct *
5. having …
6. order by …
7. limit star,count

Blog数据库创建代码：

USE Blog; 

CREATE TABLE IF NOT EXISTS students
(sno VARCHAR(3) NOT NULL, 
sname VARCHAR(4) NOT NULL,
ssex VARCHAR(2) NOT NULL, 
sbirthday DATETIME,
class VARCHAR(5));

CREATE TABLE  IF NOT EXISTS courses
(cno VARCHAR(5) NOT NULL, 
cname VARCHAR(10) NOT NULL,
tno VARCHAR(10) NOT NULL);

CREATE TABLE  IF NOT EXISTS scores 
(sno VARCHAR(3) NOT NULL, 
cno VARCHAR(5) NOT NULL, 
degree NUMERIC(10, 1) NOT NULL);

CREATE TABLE  IF NOT EXISTS teachers 
(tno VARCHAR(3) NOT NULL, 
tname VARCHAR(4) NOT NULL, tsex VARCHAR(2) NOT NULL, 
tbirthday DATETIME NOT NULL, prof VARCHAR(6), 
depart VARCHAR(10) NOT NULL);




INSERT INTO students (sno,sname,ssex,sbirthday,class) VALUES (108 ,'曾华' ,'男' ,'1977-09-01',95033);
INSERT INTO students (sno,sname,ssex,sbirthday,class) VALUES (105 ,'匡明' ,'男' ,'1975-10-02',95031);
INSERT INTO students (sno,sname,ssex,sbirthday,class) VALUES (107 ,'王丽' ,'女' ,'1976-01-23',95033);
INSERT INTO students (sno,sname,ssex,sbirthday,class) VALUES (101 ,'李军' ,'男' ,'1976-02-20',95033);
INSERT INTO students (sno,sname,ssex,sbirthday,class) VALUES (109 ,'王芳' ,'女' ,'1975-02-10',95031);
INSERT INTO students (sno,sname,ssex,sbirthday,class) VALUES (103 ,'陆君' ,'男' ,'1974-06-03',95031);

INSERT INTO courses(cno,cname,tno)VALUES ('3-105' ,'计算机导论',825);
INSERT INTO courses(cno,cname,tno)VALUES ('3-245' ,'操作系统' ,804);
INSERT INTO courses(cno,cname,tno)VALUES ('6-166' ,'数据电路' ,856);
INSERT INTO courses(cno,cname,tno)VALUES ('9-888' ,'高等数学' ,100);

INSERT INTO scores(sno,cno,degree)VALUES (103,'3-245',86);
INSERT INTO scores(sno,cno,degree)VALUES (105,'3-245',75);
INSERT INTO scores(sno,cno,degree)VALUES (109,'3-245',68);
INSERT INTO scores(sno,cno,degree)VALUES (103,'3-105',92);
INSERT INTO scores(sno,cno,degree)VALUES (105,'3-105',88);
INSERT INTO scores(sno,cno,degree)VALUES (109,'3-105',76);
INSERT INTO scores(sno,cno,degree)VALUES (101,'3-105',64);
INSERT INTO scores(sno,cno,degree)VALUES (107,'3-105',91);
INSERT INTO scores(sno,cno,degree)VALUES (108,'3-105',78);
INSERT INTO scores(sno,cno,degree)VALUES (101,'6-166',85);
INSERT INTO scores(sno,cno,degree)VALUES (107,'6-106',79);
INSERT INTO scores(sno,cno,degree)VALUES (108,'6-166',81);

INSERT INTO teachers(tno,tname,tsex,tbirthday,prof,depart) VALUES (804,'李诚','男','1958-12-02','副教授','计算机系');
INSERT INTO teachers(tno,tname,tsex,tbirthday,prof,depart) VALUES (856,'张旭','男','1969-03-12','讲师','电子工程系');
INSERT INTO teachers(tno,tname,tsex,tbirthday,prof,depart) VALUES (825,'王萍','女','1972-05-05','助教','计算机系');
INSERT INTO teachers(tno,tname,tsex,tbirthday,prof,depart) VALUES (831,'刘冰','女','1977-08-14','助教','电子工程系');

六、pymysql数据库编程

在这里插入图片描述
数据库查询操作

fetchone():该方法下获取下一个查询结果集。结果集是一个对象
fetchall():接收全部的返回结果行
rowcount():这是一个只读属性，并返回执行execute()方法后影响的行数

数据库编程

import pymysql

# 1). 创建数据库连接, autocommit是否自动提交修改到数据库中.
conn = pymysql.connect(host='localhost', user='root', password='westos',
                db='Blog', port=3306, autocommit=True, charset='utf8')
# 2). 创建游标(用来执行sql语句的)
cur = conn.cursor()

# 3). 执行sql语句
insert_sql = 'insert into users(username) values ("user6");'
cur.execute(insert_sql)

print("插入数据成功.......")
# 4). 关闭游标
cur.close()
# 5）.关闭连接
conn.close()

数据库编程with

import pymysql

# 使用with语句时， pymysql.connect返回的是数据库游标。(具体的内容查看源代码)
with pymysql.connect(host='localhost', user='root', password='westos',
                db='Blog', port=3306, autocommit=True, charset='utf8') as cur:
    # 3). 执行sql语句(增删改)
    insert_sql = 'insert into users(username) values ("user7");'
    cur.execute(insert_sql)
    print("插入数据成功.......")

数据库批量操作

import pymysql

users = [('redhat' + str(i), 'passwd'+str(i)) for i in range(100)]
# users = [('redhat1', 'passwd1'), ('redhat2', 'passwd2'), .......]

# 使用with语句是， pymysql.connect返回的是数据库游标。(具体的内容查看源代码)
with pymysql.connect(host='localhost', user='root', password='westos',
                db='Blog', port=3306, autocommit=True, charset='utf8') as cur:
    # 3). 执行sql语句(增删改)
    insert_sql = 'insert into users(username, password) values (%s, %s);'
    # execute: 执行一条sql语句。
    # executemany: 执行多条sql语句。
    # 'insert into users(username, password) values (%s, %s);' %users[0]
    # 'insert into users(username, password) values (%s, %s);' %('redhat1', 'passwd1')
    # 'insert into users(username, password) values (%s, %s);' %users[1]
    # 'insert into users(username, password) values (%s, %s);' %users[2]
    cur.executemany(insert_sql, users)
    print("插入数据成功.......")

查询数据库信息

import pymysql

with pymysql.connect(host='localhost', user='root', password='westos',
                db='Blog', port=3306, autocommit=True, charset='utf8') as cur:
    # 3). 执行sql语句(增删改)
    query_sql = 'select * from users where username like "user%";'
    # Number of affected rows
    result = cur.execute(query_sql)
    print("符合条件的记录数: ", result)

    # print(cur.fetchone())
    # print(cur.fetchmany(2))
    users_info = cur.fetchall()

# 以表格方式美观的打印。
from  prettytable import  PrettyTable
pt = PrettyTable(field_names=['编号', '用户名', '密码'])
for user in users_info:
    pt.add_row(user)
print(pt)

CodingAndCoCoding

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
python---关系型数据库MySQL

一、数据库简介为什么需要数据库？人类在进化的过程中，，**数据量越来越大，对于数据的记录和准确查找，**成为了一个重大难题。数据库系统解决的问题：持久化存储，优化读写，保证数据的有效性。数据库分类关系型数据库：指采用了关系模型来组织数据的数据库。关系模型指的就是二维表格模型，而一个关系型数据库就是由二维表及其之间的联系所组成的一个数据组织。主流的关系型数据库有: Oracle、Micr...
复制链接

扫一扫