Python--第二阶段--06.MySQL数据库_表达式subster(“hello”,1,3)-CSDN博客

本文链接：https://blog.csdn.net/weixin_39127810/article/details/107944087

MySQL简介

MySQL数据库：开源的关系型（二维表）数据库
MySQL数据库主要在网站中运用的多

关系型数据库结构

数据元素 —> 记录（元组） —> 数据表 —> 数据库
一个MySQL服务可以有多个数据库
数据表：存放数据的表格，二维表
字段：每个列，用来表示该列的数据含义
记录：每个行，表示一组完整的数据，记录（元组）

MySQL

MySQL特点

1. 开源数据库，是使用C和C++编写
2. 可以工作在众多不同的平台上
3. 提供了用于各种语言的API接口
4. 存储结构优良，运行速度快
5. 功能全面丰富

MySQL安装

linux：
	Ubuntu：
	安装服务端：sudo apt-get install mysql-server
	安装客户端：sudo apt-get install mysql-client
	配置文件：/etc/mysql
	命令集： /usr/bin
	数据库存储目录：/var/lib/mysql
windows安装：
	www.mysql.com下载安装软件msi的安装文件（server）
	https://dev.mysql.com/downloads/installer/

MySQL启动

linux系统下：
查看数据库的状态：sudo /etc/init.d/mysql status
启动服务：sudo /etc/init.d/mysql stop
停止服务：sudo /etc/init.d/mysql start
重启服务：sudo /etc/init.d/mysql restart

Windows系统下：
启动服务：net start MySQL80（服务名字，8.0默认是MySQL80）
停止服务：net stop MySQL80（服务名字，8.0默认是MySQL80）

MySQL连接

mysql -h 主机地址 -P 端口号 -u 用户名 -p 密码（密码可以先不写，回车后会提示输入密码）
mysql -hlocalhost -uroot -p123456
-h默认是localhost，-P默认是3306
断开连接：Ctrl+d或者exit

SQL语句

定义：结构化查询语言，主要用于增删改查关系型数据库系统。
SQL语句使用特点：
	SQL语句基本独立数据库本身
	不同系统有细微差别
	每条命令以；结尾
	SQL命令关键字不区分字母大小写（表名和库名区分大小写）

数据类型

数字

数字数据类型

整数类型（精确值）：
	int（4字节，21亿），integer，tinyint（1个字节，-128~127或0-255），smallint（2个字节-32768~32767或0~），mediumint，bigint（21亿以上）
浮点型：
	float（单精度，小数点后38位），double（双精度，小数点后308位），decimal（指定精度decimal（M,D）M是一共多少位，D是小数点后多少位）
bit类型：
	布尔类型，0代表假，1代表真

数字函数

ROUND(45.925,2)：四舍五入取值，小数点后第n位；
TRUNCATE(45.926, 2)：数字截取，截取到小数点后第n位；

CELL(2.83)：进一取整；
FLOOR(2.83)：舍一取整；

MOD(1600,300)：取模

字符串

字符串数据类型

char和varchar：
	char（0-255个字节）：定长，效率高，一般用于固定长度的表单提交数据存储，默认1字符
	varchar（0-65535个字节）：不定长，效率偏低，必须写括号，根据实际长度开辟空间
text和blob：
	text：存文本的，tinytext（0-255字节），mediumtext（0-16777215字节），longtext（0-4294967295字节）
	blob：存二进制文件，字节串，tinyblob（0-255字节），mediumblob（0-16777215字节）16MB，longblob（0-4294967295字节）4096MB
可选字符串：
	enum：枚举，只能填写所给的内容，并且只能写一个
	set：只能填写所给的内容，并且能写多个

字符串函数

LOWER()：字母全部转换成小写字母。
UPPER()：字母全部转换成大写字母。

CONCAT('hello','world')：链接传入的字符串，可以多个；
SUBSTER('helloworld',1,5)：切片，从第一个开始取5个字符；
LENTH('HELLOWORLD')：求字符串长度；
INSTER('helloworld','w')：查询后面字符串在前一个字符串的位置索引；
LPAD('ABC',12,'-'):用第三个字符串的符号在字符串的左边将字符串补充到第二个参数的长度
RPAD('ABC',12,'-'):用第三个字符串的符号在字符串的右边将字符串补充到第二个参数的长度

日期时间型

日期类基本数据类型

数据类型：
	date：年月日3字节（1000-01-01到9999-12-31）
	datetime：年月日时分秒8字节，1000-01-01 00:00:00-9999-12-31 23:59:59
	timestamp：时间戳，1970-01-01 00:00:00--2038-01-19 11:14:07
	time：时间类型，-838：59：59到838：59：59
	year：年份，1901-2155年
数据格式
	date：“yyyy-mm-dd"
	datetime："yyyy-mm-dd hh:mm:ss"
	timestamp："yyyy-mm-dd hh:mm:ss"
	time："hh:mm:ss"
	year：'yyyy'
时期时间运算
	比较运算：
	算数运算+-：
		日期时间类型数据 +- interval 时间 时间单位unit；时间单位unit有day/month/year/minute/hour
			time('3:00:00') - interval 30 minute

日期类函数

now()：返回服务器的当前时间，"yyyy-mm-dd hh:mm:ss"------>datetime类型
curdate()：返回当前日期，“yyyy-mm-dd"------>date类型
curtime()：返回当前时间，"hh:mm:ss"------>time类型

DATEDIFF(日期1,日期2)：返回两个日期之间相差的天数
ADDDATE(日期,[interval] 30 [unit])：日期上面增加后面的时间，默认是天
DATE_SUB(日期,[INTERVAL] expr [unit])：日期上面减去后面的时间，默认是天
DAYNAME(日期)：返回当前日期是星期几；
DAYOFMONTH(日期)：一个月的第几天；
DAYOFYEAR(日期)：一年的第几天；
LAST_DAY(日期)：当月最后一天的日期；

运算符

算数运算符：

+ 加
- 减
* 乘
/ 除
% 取余数
powerx,y) 幂运算，x的y次方
SQRT(x) 开平方，x的开平方运算

比较运算符

= 判断是否相等
<>  !=  不等于
>大于
<小于
<=小于等于
>=大于等于
between and   在区间里
not between and   不在区间里
in    在范围内
not in  不在范围内
like   模糊查询（%代表0个或多个，_代表1个占位符）
regexp 正则表达式查询
is null  是空的
is not null   不是空的

逻辑运算符

not 或！ 不是
and   和  （两个都得是真）
or   或（只要有一个真就可以）
xor   抑或（一真一假）

位运算符：

>>       <<

数据库操作

1. 查看已有的数据库

show databases；

2. 创建库（指定字符集）
create database 库名 [charcter set utf8 | charset = utf8]
[]内的内容可以写也可以不写，8.0版本后默认编码格式是utf8
数据库名称不能重复

create database stu charcter set utf8;
create database stu charcter = utf8;

3. 查看创建库的语句（字符集）
show create database 数据库名

show create database stu

4. 查看当前所在库

select database();

5. 切换库（使用哪个数据库）
use 数据库名

use stu；

6. 删除库
drop database 库名;

drop database stu;

7. 库名命名规则
	数字字母下划线，但不能纯数字
	库名区分大小写
	不能使用特殊字符和mysql关键字

数据表的管理

表结构设计初步

	1. 分析存储内容
	2. 确定字段构成
	3. 设计字段类型

表的基本操作

创建表
	create table 表名(字段名 数据类型,字段名 数据类型,…);
	字段的特殊属性
		unsigned 无符号的数据
		not null 不能为空
		default 设置一个字段的默认值
		auto_increment 设置列为自增的属性，一般用于主键，数值会自动加1
		primary key 关键字用于定义列为主键，主键的值不能重复

create table 
create table interset(id int primary key auto_increment,name varchar(32) not null,hobby varchar(32))

查看数据表
	show tables;
查看创建表语句，已有表的字符集
	show create table 表名;
删除表
	drop table 表名;
查看表结构
	desc 表名；

数据基本操作(增删改查)

插入表记录（增）
	insert into 表名 values(字段1数据1,字段2数据1,…)，(字段1数据2,字段2数据2,…);
			数值的顺序按照表中字段的顺序填写，必须写所有的字段；
	insert into 表名 (需要的字段名1,需要的字段名2) values(对应的字段值1,对应的字段值2);
	
删除表记录（删）
	delete from 表名 where 条件；默认删除所有满足条件的记录
		delete from class where score<60;
		
更改表记录（改）
	update 表名 set 字段1=值1，字段2=值2，....where
		update class set name = 'Baron' where id=2;
		
查询表记录（查）
	select * from 表名；
	列的筛选：
		select 字段名，字段名 from 表名；
		select name,age from class;
	行的筛选：
		where 子句 条件筛选
		limit 从第几行开始选几行
	排序order by：
		select * from class_1 order by age;  默认按字段的升序排序asc
		逆序：select * from class_1 order by age desc; 加desc是按照降序排序，加在降序的字段后
	分页limit：
		限制操作的数量，limit放最后
		limit n，则是从头开始显示n行
		limit k,n，那么就是从第k个开始，选n行数据
	模糊查询
		like 只针对字符串有效，在like使用中：%代表0个或多个字符；_代表一个字符
		select * from class where name like '%b%';   # 匹配姓名中包含b的所有记录
		select * from class where name like '___';    # 匹配姓名长度为3个字符的所有记录
	正则表达式查询regexp
		select * from class_1 where name regexp '^B.+'    匹配姓名以B开头的所有记录
	联合查询（两个查询结果上下连接）：
		union [distinct|all]，前后都是select语句，并且要求字段数量相同，将两个查询结果上下连接起来，默认去除重复项，如果加上关键字all就不去除重复项，效率高。
		select * from class_1 where gender='w' union select * from class_1 where age>17;
		select * from class_1 where gender='w' union all select * from class_1 where age>17;

表字段的操作（alter）

语法：alter table 表名 执行动作
执行动作
	增加字段（add）
		alter table class add tel char(11);
		默认增加在最后，添加first或者after指定位置增加
		alter table class add tel char(11) after age;   # 在age字段后加tel字段
	删除字段（drop）
		alter table class drop tel；
	修改数据类型（modify）
		alter table class modify age tinyint unsigned;  # 把age字段修改为tinyint的无符号类型
	字段重命名（change）
		alter table class change sex xingbie char;  # 把sex字段更名为性别，数据类型更换为char
	表重命名
		alter table class rename class_1;  # 把表class名更换为class_1

数据备份

1.备份命令
备份某个数据库：mysqldump -u用户名 -p 源库名> ~/xxx.sql
备份所有数据库：mysqldump -u用户名 -p --all-databases> ~/xxx.sql 
备份某个数据库的某个表：mysqldump -u用户名 -p 源库名 表名1 表名2> ~/xxx.sql

2.恢复数据库
mysql -uroot -p 库名<~/xxx.sql
恢复前需要提前创建数据库

Python操作MySQL数据库

pymysql使用流程

建立数据库连接：db = pymysql.connect(…)
创建游标对象：c = db.cursor()
游标方法：c.execute(“数据库语句”)
提交到数据库：db.commit()——针对数据库写操作的（增删改）
关闭游标对象：c.close()
断开数据库连接：db.close()

常用的函数

db = pymysql.connect(host,port,user,password,database,charset)
	参数：
		host：主机地址，本地localhost
		port：端口号，默认端口3306
		user：用户名
		password：密码
		database：库名
		charset：编码方式，推荐使用utf8
		
数据库连接对象的方法：
	db.commit()提交数据库执行
	db.rollback() 回滚，修改错误的时候回滚到修改之前的状态
	cur = db.cursor() 返回游标对象，用于执行具体SQL命令
	db.close() 关闭连接
	
游标对象的方法：
	cur.execute(sql命令，[列表]) 执行SQL命令
	cur.close() 关闭游标对象
	cur.fetchone() 获取第一条查询数据
	cur.fetchmany(n)  返回前n条查询数据
	cur.fetchall()  返回所有的查询结果

插入操作演示

import pymysql
# 创建数据库连接
db = pymysql.connect(host = 'localhost',
                     port = 3306,
                     user = 'root',
                     password = '123456',
                     database = 'sunhao',
                     charset = 'utf8')
# 创建游标
cur = db.cursor()
# 将数据库语句写入变量，要求，sql语句在mysql运行没错，最后的分号可有可无
sql = "insert into student values (9,'孙皓','2020-02-02','男');"
# 执行语句
cur.execute(sql)
# 提交执行结果，只有写操作才需要提交，可以一次一提交，也可以多次写操作一同提交
db.commit()
# 关闭游标
cur.close()
# 关闭数据库
db.close()

读操作演示

import pymysql

# 创建数据库连接
db = pymysql.connect(host = 'localhost',
                     port = 3306,
                     user = 'root',
                     password = '123456',
                     database = 'sunhao',
                     charset = 'utf8')
# 创建游标
cur = db.cursor()
# 将数据库语句写入变量
sql = "select * from student;"
# 执行语句
cur.execute(sql)
# 获取查询结果
one_row = cur.fetchone()   # 结果是一个元组
print(one_row)
next_rows = cur.fetchmany(2)  # 由于第一个已经取出来了，所以就从第二个开始取两个
print(next_rows)
all = cur.fetchall()    # 取出剩下所有的数据，保存在元组里面
print(all)
# 关闭游标
cur.close()
# 关闭数据库
db.close()

写操作演示

写操作前会有个try，以保证运行错误后可以将操作回滚到操作前；

import pymysql

# 创建数据库连接
db = pymysql.connect(host = 'localhost',
                     port = 3306,
                     user = 'root',
                     password = '123456',
                     database = 'sunhao',
                     charset = 'utf8')
# 创建游标
cur = db.cursor()
# 将数据库语句写入变量
# 执行语句
try:
    sql = "insert into student values (10,'平新川','2020-02-01','男');"
    cur.execute(sql)
    db.commit()
except Exception as e:
    db.rollback()
    print(e)
# 获取查询结果
one_row = cur.fetchone()   # 结果是一个元组
print(one_row)
next_rows = cur.fetchmany(2)  # 由于第一个已经取出来了，所以就从第二个开始取两个
print(next_rows)
all = cur.fetchall()    # 取出剩下所有的数据，保存在元组里面
print(all)
# 关闭游标
cur.close()
# 关闭数据库
db.close()

给sql语句的value传参，可以用list传参的方法

import pymysql

# 创建数据库连接
db = pymysql.connect(host = 'localhost',
                     port = 3306,
                     user = 'root',
                     password = '123456',
                     database = 'sunhao',
                     charset = 'utf8')
# 创建游标
cur = db.cursor()
# 将数据库语句写入变量
# 执行语句
try:
    sql = "insert into student values (%s,%s,%s,%s);"
    cur.execute(sql,[10,'平新川','2020-02-01','男'])
    db.commit()
except Exception as e:
    db.rollback()
    print(e)
# 获取查询结果
one_row = cur.fetchone()   # 结果是一个元组
print(one_row)
next_rows = cur.fetchmany(2)  # 由于第一个已经取出来了，所以就从第二个开始取两个
print(next_rows)
all = cur.fetchall()    # 取出剩下所有的数据，保存在元组里面
print(all)
# 关闭游标
cur.close()
# 关闭数据库
db.close()

二进制文件存储

存储文件路径
将文件以二进制存储在数据库

将二级制文件存储到数据库：将图片存入

import pymysql

# 创建数据库连接
db = pymysql.connect(host = 'localhost',
                     port = 3306,
                     user = 'root',
                     password = '123456',
                     database = 'sunhao',
                     charset = 'utf8')
# 创建游标
cur = db.cursor()
# 读取文件数据
with open('touxiang.jpg','rb') as f:
    data = f.read()
# 执行语句
try:
    sql = "update student set img = %s where Sname = '孙皓';"
    cur.execute(sql,[data])
    db.commit()
except Exception as e:
    db.rollback()
    print(e)
# 关闭游标
cur.close()
# 关闭数据库
db.close()

将文件从数据库读取出来

import pymysql

# 创建数据库连接
db = pymysql.connect(host = 'localhost',
                     port = 3306,
                     user = 'root',
                     password = '123456',
                     database = 'sunhao',
                     charset = 'utf8')
# 创建游标
cur = db.cursor()
# 执行语句
sql = "select img from student where Sname = '孙皓';"
cur.execute(sql)
data = cur.fetchall()
# 将读取的二进制文件存放如文件
with open('touxiang1.jpg','wb') as f:
    f.write(data[0][0])
# 关闭游标
cur.close()
# 关闭数据库
db.close()