1.数据库基本概念
- 程序运行，数据是存在内存中，当程序终止，通常需要将数据保存到磁盘中。
- 数据库是存放数据仓库，它储存空间很大，可以存放百万条、千万条、上亿条数据，数据库存放数据有一定规则
2.数据库编程接口
- Python Database API 2.0规范，定义Python数据库API接口各个部分，如模块接口、连接对象、游标对象、类型对象和构造器、DB API可扩展一级可选得错误处理等机制
2.1 连接对象
- 数据库连接对象(Connection Object)提供数据库游标对象和提交/回滚事务方法，如何关闭设局库连接
- 使用connect()函数，获取连接对象
参数 | 说明 |
dsn | 数据源名称，给出该参数表示数据库依赖 |
user | 用户名 |
password | 用户密码 |
host | 主机名/ip地址 |
database | 数据库名 |
安装PyMySQL模块：pip install pymysql# 使用PyMySQL模块连接MySQL数据库
import pymysql
db = pymysql.connect(host='192.168.3.164',
user = 'root',
password = '/Ay#5Qgi1taR',
databse = 'sgsw',
charset = 'utf=8'
)
# 或者这样写
# db = pymysql.connect("192.168.3.164","root","/Ay#5Qgi1taR","sgsw")- 连接方法及使用说明
方法名 | 说明 |
close() | 关闭数据库连接 |
commit() | 提交事务 |
rollback() | 回滚事务 |
cursor() | 获取游标对象，操作数据库，比如执行DML操作，调用存储过程等 |
2.2 游标对象
- 游标对象(Cursor Object）代表数据中得游标，用于指示抓取数据库操作得上下文，主要提供执行SQL语句、调用存储过程、获取查询方法
游标对象属性：
1.description：数据库列类型和值得描述信息
2.rowcount ：返回结束得行数统计信息，如SELECT,UPDATE,CALLPROC等
3.MySQL安装基础操作
- MySQL最早是由瑞典得MySQL AB公司开发一个开放源码得关系数据库管理系统，该公司与2008年被昇阳微系统公司(Sun Microsystems)收购。2009年，甲骨文公司(Oracle)收购昇阳微系统公司，因此MySQL成为了Oracle旗下产品
- MySQL过去由于性能高蹲、可靠性好、已经称为了最流行得开发源数据库，因此被广泛得应用于中小型网站开发。随着MySQL的不断成熟，它也逐渐被应用于更多大规模网站和应用。
3.1 MySQL安装(windows版本)
下载网址：https://dev.mysql.com/downloads/installer/- 步骤一：下载8.0.19,MySQL版本会不时更新(2020-3-17 15:02)
- 步骤二：直接下载
- 步骤三：开始安装，一路默认到端口默认3306，Accounts and Roles设置密码，
- 步骤四：设置环境变量，右键点击“此电脑” =>> “属性” =>>“高级系统设置” =>> “环境变量” =>> “选择Path变量” =>> “编辑” =>> “新建” ，将如下默认安装环境变量写在变量值中
默认安装变量：C:\Program Files\MySQL\MySQL Server 8.0\bin
- 步骤四：打开cmd，登录数据库：mysql -u root -p
3.2 操作数据库
1.连接mysql数据库的命令
mysql -h 服务器地址 -u 用户名 -p # 不要在P后面直接跟密码
2.查看库
show databases;
3.创建库
create database 数据库名 default charset=utf8; # 数据库名不要纯数字，不要用汉字
4.删除库
drop databas 数据库名;
5.选中库
use 数据库名；
6.查看表
show tables;
7.查看表结构
desc 表;
8.查看数据库创建语句
show create databas 数据库名；
9.查看选中的数据库
select database()；
10.修改数据库字符集
alter database student default charset=utf8
注意：每条命令结束必须使用；或者\g结束
退出mysql使用命名quit或exit3.3 数据库表
1.删除表
drop table 表名;
2.赋值表结构
create table 表明 like 其他表明
3.查看建表语句
show create table 表名；
4.修改字段类型
alter table 表明 modify 字段名 类型 [限制];
5.增加字段
alter table 表明 add [column] 字段名 类型 [限制];
6.删除字段
alter table 表明 drop [column] 字段名；
7.修改字段名和类型
alter table 表明 change [column] 旧字段名 新字段名 类型 [限制];
8.修改表明
alter table 表明 rename 新表明
alter table 表明 [engine=myisam] [default charset=utf8];
9.通过first、after指定插入位置
alter table student add sno varchar(3) not null after sid; # 在sid列后插入
alter table student add sid int primary key auto_increment first; # 在第一列插入
- 字段限制
primary key 不允许空值 唯一
not null 非空
unique 唯一
default 缺省，默认值3.4 数据类型
- 数值型：尽量使用整形，包括int、smallint tinyint
- int(3)或tinyint(2),3或者2不会限制你所存储数据的长度，只有在匹配合法zerofill零填充的时候才有意义
- 浮点数 deoble、decimal
类型 | 大小 | 范围(有符号) | 范围(无符号) | 用途 |
tinyint | 1字节 | -128-127 | 0,255 | 最小整数 |
int | 4字节 | -2147483648-2147483647 | 最大整数 | |
float(m,n) | 4-8字节 | 单精度浮点型(浮点数) | ||
double(m,n) | 8字节 | 双精度浮点型(浮点数) | ||
decimal(m,n) | 变长 | 浮点数(更加精确) |
- 字符型
类型 | 大小 | 用途 |
char | 0-255字节 | 存储定长的字符串 |
varchar | 0-65535字节 | 边长字符串 |
text | 0-65535字节 | 长文本数据 |
blob | 0-65535字节 | 二进制的文本(不建议) |
enum(‘w’,‘n’) | 65535个成员 | 枚举 |
set(‘w’,‘n’) | 64个成员 | 集合 |
注意：char 和 varchar 的区别：
char的执行效率高于varchar,varchar相对于char节省存储空间
如果使用char传入的数据的长度小于指定的长度的时候，存储的实际长度不够的会拿空格来填充
如果使用varchar传入的数据的长度小于指定的长度的时候存储的实际长度为传进来的数据的长度- 日期时间型
类型 | 大小 | 范围 | 格式 | 用途 |
date | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |
time | 3 | -838:59:59:238:59:59 | HH:MM:SS | 时间值 |
year | 4 | 1901/2155 | YYYY | 年份值 |
datetime | 8 | 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间 |
3.4 数据操作
- insert：向表中插入新行
语法：
写法一：insert into 表名(字段1,字段2...) values (值1,值2...)
省略字段列表，则按照建表时的字段顺序进行插入，每一行都要给定值
方法二：insert into 表名 values (值1,值2...)
插入多个记录
方法三：insert into 表名(字段1,字段2...) values (值1,值2...),(值1,值2...)
字符串类型需要用引号括起来
举例：
mysql> insert into sw_box_spec (box_spec,width,height,depth) values ('B4',300,300,300);
Query OK, 1 row affected (0.01 sec)- update：语句可用来修改表中的数据,
语法：update 表明 set 字段1=值1,字段2=值2... where 条件 # 不加where修改的所有的记录
mysql> update sw_cab_info set cabinet_port=9000 where cabinet_id=01;- delete：删除表中得数据，自增主键的值不会重新开始
语法：
delete from 表明 where 条件； # 如果不加条件，会删除表中所有的数据，慎重使用
alter table 表名 auto_increment = 5 # 设置自增主键开始值
清空表，自增主键的值重新开始编号
truncate tables 表明，清空表中所有的记录，等价于 delete from 表明；
delete 和 truncate差别，truncate后，表中自增主键值从1开始
举例：删除B6数据
mysql> delete from sw_box_spec where box_spec='B6';
Query OK, 1 row affected (0.01 sec)3.5 数据查询
- 基本机构：select 字段名列表 from 表名
举例：
mysql> select box_spec,width from sw_box_spec; # 查询指定字段数据
mysql> select box_spec as 规则,width as 深度 from sw_box_spec; # 给字段起别名
mysql> select * from sw_box_spec; # 查询所有数据
mysql> select * from sw_cab_info \G # 竖着展示
mysql> select distinct width from sw_box_spec; # 去重
mysql> select * from sw_cab_info limit 2,5; # 查询2 到 5 数据 也可以是一个参数
关系查询：
mysql> select * from sw_cab_info where cabinet_name='08号柜';
mysql> select * from sw_cab_info where cabinet_name<'08号柜';
mysql> select * from sw_cab_info where cabinet_name>'02号柜' and cabinet_id<04;
mysql> select * from sw_cab_info where cabinet_name in ('03号柜','04号柜');
模糊查询：
mysql> select * from sw_cab_info where cabinet_id like '%8'; # 结尾为8
排序查询：
mysql> select * from sw_cab_info order by cabinet_id;
集合函数：
mysql> select count(*) from sw_cab_info; # 查询多少条记录，可以给字段名，可以给*
mysql> select max(cabinet_id) from sw_cab_info; # 求最大值
mysql> select min(cabinet_id) from sw_cab_info; # 求最小值
mysql> select sum(cabinet_id) from sw_cab_info; # 求合
分组：按照某个字段进行统计
mysql> select box_spec,count(*) from sw_mat_spec group by box_spec; # 对类型分组(两个操作，先排序后统计）
mysql> select min_depth,count(*) from sw_mat_spec group by min_depth having count(*) > 1; # 对分组过滤
mysql> select min_depth,count(*) num from sw_mat_spec group by min_depth having num>1; # 起个别名num,对分组过滤3.6 存储引擎
mysql> show engines; # 数据库存储引擎
myisam 和 innodb的区别：
1.myisam查询速度快，不支持事务，不支持外键，支持表锁
2.innodb增删改查效率高，支持事务，支持外键，自持行锁4.MySQL数据库高级操作
4.1 子查询
- 子查询嵌入到其他查询语句中的查询语句，子查询只能出现在from，where，having中
- 子查询不要使用select * ,exists除外
mysql> select box_spec from sw_box_spec where box_spec='B3';
+----------+
| box_spec |
+----------+
| B3 |
+----------+
1 row in set (0.00 sec)
mysql> select box_name from sw_box_info where box_spec in (select box_spec from sw_box_spec where box_spec='B3');
//等价：mysql> select box_name from sw_box_info where box_spec='B3';
+----------+
| box_name |
+----------+
| 01号箱 |
| 02号箱 |
| 03号箱 |
| 04号箱 |
| 05号箱 |
| 06号箱 |
| 07号箱 |
| 08号箱 |
| 09号箱 |
| 10号箱 |
| 11号箱 |
| 12号箱 |
| 13号箱 |
| 14号箱 |
| 15号箱 |
| 16号箱 |
| 17号箱 |
| 18号箱 |
| 19号箱 |
| 20号箱 |
| 21号箱 |
| 22号箱 |
| 23号箱 |
| 24号箱 |
+----------+
24 rows in set (0.00 sec)4.2 多表查询
- 多表连接必须要有连接条件，否则结果没有意义
- 多表连接有两种写法：隐式(标准SQL)和显试内连接
- 隐式(标准SQL)连接：连接条件写到where字句中
先拼接一个表，然后再把满足条件的留下
mysql> select * from sw_cab_info,sw_mat_spec; # 不加任何条件 表一一行 乘以 表二所有行 如此循环
隐式内连接：
mysql> select * from sw_box_spec,sw_mat_spec where sw_box_spec.box_spec = sw_mat_spec.box_spec; # 按规拼接查询
显示内连接：
mysql> select * from sw_box_spec s inner join sw_mat_spec w on s.box_spec = w.box_spec;
mysql> select s.*,depth from sw_box_spec s inner join sw_mat_spec w on s.box_spec = w.box_spec;4.3 外连接
- 俩张表关联查询，根据以那种表为主可以分为左外连接和右外连接
- 以左表为主(left join)，如果右边的表里没有匹配的记录，则添加一个万能记录(个字段都为null)与之连接
- 以右表为主(right join)，如果左边的表里没有匹配的记录，则增加一个哇能记录与之连接
左外连接：左表记录必须记录在表中
mysql> select * from sw_box_spec s left outer join sw_mat_spec w on s.box_spec=w.box_spec;
+----------+-------+--------+-------+----------+----------+------------+----------------+-----------+-----------+-----------+-----------+------------+------------+
| box_spec | width | height | depth | mat_spec | box_spec | store_mode | store_quantity | min_depth | max_depth | min_width | max_width | min_height | max_height |
+----------+-------+--------+-------+----------+----------+------------+----------------+-----------+-----------+-----------+-----------+------------+------------+
| A1 | 795 | 470 | 600 | L1W1 | A1 | 0 | 450 | 290 | 600 | 450 | 795 | 0 | 0 |
| A2 | 470 | 470 | 600 | L1W2 | A2 | 0 | 450 | 290 | 600 | 280 | 450 | 0 | 0 |
| A3 | 300 | 470 | 600 | L1W3 | A3 | 0 | 450 | 290 | 600 | 0 | 280 | 0 | 0 |
| B2 | 470 | 470 | 400 | SFP | B2 | 1 | 12 | 0 | 150 | 0 | 0 | 0 | 0 |
| B3 | 300 | 470 | 400 | L2W3 | B3 | 0 | 450 | 150 | 290 | 0 | 280 | 0 | 0 |
| B4 | 300 | 300 | 300 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| B5 | 300 | 300 | 300 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| B6 | 300 | 300 | 300 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
+----------+-------+--------+-------+----------+----------+------------+----------------+-----------+-----------+-----------+-----------+------------+------------+
8 rows in set (0.00 sec)
右外连接：右表记录必须存在表中4.4 集合操作
- 可以使用union将两个查询结果合并，mysql只支持并，不支持差和交
- 两个结果集中字段数一样，对应字段类型兼容
- 自动去除重复记录，不去出重复记录用union all
- order by 放到最后
mysql> select * from sw_box_spec where width=470
-> union
-> select * from sw_box_spec where height=300
-> ;
+----------+-------+--------+-------+
| box_spec | width | height | depth |
+----------+-------+--------+-------+
| A2 | 470 | 470 | 600 |
| B2 | 470 | 470 | 400 |
| B4 | 300 | 300 | 300 |
| B5 | 300 | 300 | 300 |
| B6 | 300 | 300 | 300 |
+----------+-------+--------+-------+
5 rows in set (0.00 sec)
格式化时间
mysql> select DATE_FORMAT(now(),'%Y-%m-%d %H:%i:%s');
+----------------------------------------+
| DATE_FORMAT(now(),'%Y-%m-%d %H:%i:%s') |
+----------------------------------------+
| 2020-03-20 16:43:58 |
+----------------------------------------+
1 row in set (0.00 sec)
0-1随机小数
mysql> select rand();
+--------------------+
| rand() |
+--------------------+
| 0.4324598446790686 |
+--------------------+
1 row in set (0.00 sec)
0-100随机小数
mysql> select rand()*100;
+-------------------+
| rand()*100 |
+-------------------+
| 78.93433289503152 |
+-------------------+
1 row in set (0.00 sec)
签名：
md5(str)：计算str的哈希值，返回⼀个 32位⼗六进制数字的⼆ 进制字符串
(通过碰撞可以还原,不在安全）
mysql> select md5('123');
+----------------------------------+
| md5('123') |
+----------------------------------+
| 202cb962ac59075b964b07152d234b70 |
+----------------------------------+
1 row in set (0.00 sec)
sha1(str)/sha(str)：计算str的哈希值，返回⼀个 40位⼗六进制数字的⼆ 进制字符串（安全）
mysql> select sha1('123');
+------------------------------------------+
| sha1('123') |
+------------------------------------------+
| 40bd001563085fc35165329ea1ff5c5ecbdbbeef |
+------------------------------------------+
1 row in set (0.00 sec)5.数据控制
5.1 事务
- 事务把一组操作看作一个整体，要不都操作成功，要不都操作失败。
- 表的数据库引擎必须是innodb，innodb支持事务，myisam不支持事务
- 修改表引擎：alter table 表名 engine=innodb
mysql> select @@autocommit; # (1为⾃动提交 0为⼿动提交)
mysql> set autocommit = 0; # 闭⾃动提交
# 关闭自动提交后，需要手动提交否则数据存在内存中，没有保存
mysql> update sw_cab_info set cabinet_port=9000 where cabinet_id=01; # 手动修改数据
mysql> commit; # 手动提交
mysql> rollback # 回滚撤销刚才的操作
-> ;
Query OK, 0 rows affected (0.00 sec)
mysql> start transaction /begin; #开启事务,中间所有操作看作整体
mysql> rollback; # 开始一个事务后，中间任何操作可以看作一个整体，撤销所有操作。6.索引
- 索引是关系型数据库中用来提升查询性能最为重要的手段，关系型数据库中的索引就像是一本书的目录，创建索引虽然会带来存储空间开销，但是牺牲空间换来的查询时间的减少是非常显著的
- MySQL中，所有的数据类型的列表都可以被索引，常用的存储引擎InnoDB和MyISAM能支持每个表创建16个索引，使用底仓算法是B-tree(B数)，B-tree是一种自平衡的树，类似于平衡二叉排序树，能保持数据有序。这种数据结构能让查找数据，顺序访问，插入数据及删除数据都在对数时间内完成
6.1 索引的优点
- 可以大大加快数据检索速度
- 唯一索引可以保障数据唯一性
- 可以降低分组、排序的时间
- 可以使用查询优化器提高系统性能
6.2 索引的缺点
- 建立索引会建立对应的索引文件，占用大量空间
- 建立索引会降低增，删，改的效率
6.3 不建立索引
- 频繁更新的字段不要建立索引
- 没出现在where,having，不要建立索引
- 数量少的表没有必要建立索引
- 唯一性比较差的字段不要建立索引
6.4 索引分类
- 普通索引
mysql> show index from sw_box_info; # 查询索引，主键会自动创建索引
mysql> create index index_min_depth on sw_mat_spec(min_depth asc); # 默认主键索引显示，按分数查询速度会很快
mysql> drop index index_min_depth on sw_mat_spec; # 删除索引
mysql> explain select * from sw_mat_spec where min_depth=150;
+----+-------------+-------------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | sw_mat_spec | NULL | ref | index_min_depth | index_min_depth | 4 | const | 1 | 100.00 | NULL |
+----+-------------+-------------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
ref = const # 百分百命中，没有索引会全表扫描
filterd # 过滤效果- 唯一索引：唯一索引列不能右重复值，增加和修改会受影响
create unique index 索引名 on 表名(字段 asc/desc) 默认asc升- 主键索引：创建表，主键索引会⾃动添加，要求在主键上不能有重复值，不能有空值
- 删除索引
drop index 索引名 on- 查看索引
show index from 表 \G
# 查看spl性能
mysql> explain select * from sw_mat_spec where min_depth=150;
+----+-------------+-------------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | sw_mat_spec | NULL | ref | index_min_depth | index_min_depth | 4 | const | 1 | 100.00 | NULL |
+----+-------------+-------------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
type：MySQL在表中找到满⾜条件的⾏的⽅式，也称为访问类型，包括：ALL（全 表扫描）、index（索引全扫描）、range（索引范围扫描）、ref（⾮唯⼀索引扫 描）、eq_ref（唯⼀索引扫描）、const/system、NULL。
在所有的访问类型中， 很显然ALL是性能最差的，它代表了全表扫描是指要扫描表中的每⼀⾏才能找到匹 配的⾏。
possible_keys：MySQL可以选择的索引，但是有可能不会使⽤。
key： MySQL真正使⽤的索引。 rows：执⾏查询需要扫描的⾏数，这是⼀个预估值7.视图
- 视图时关系数据库中将一组查询指令构成的查询结果集合成可查询的数据表的对象
mysql> create view v_box_spec(box_spec) as select box_spec from sw_mat_spec;
# 自动创建一个虚拟表，直接查询表OK
# 视图提高安全性
# 视图程序数据库分离开
# 不要增删改 查询就OK8.数据备份与恢复
- 备份
不⽤登录mysql，直接执⾏mysqldump命令,将指定数据库备份到家⽬录下的指定 ⽂件
mysqldump –uroot –p 数据库名 > ~/备份⽂件名.sql;- 恢复
⾸先要创建⼀个mysql数据库，然后退出mysql，执⾏以下命令
mysql -uroot –p 数据库名 < ~/备份⽂件.sql # Windows 直接写绝对路基那个，没有~号9.使用PyMySQL操作MySQl
- 由于MySQL服务器以独立的进程运行，并通过网络对外服务，所以需要支持Python的MySySQL驱动来连接到MySQL服务器，在Python中支持MySQL的数据库模块有很多，我们选择使用PyMySQL
9.1 创建数据表
import pymysql
#打开数据库连接
db = pymysql.connect("127.0.0.1","root","root","demo")
#使用cursor()方法获取操作游标
cursor = db.cursor()
#使用execute()方法执行SQL，如果表存在则删除
cursor.execute("DROP TABLE IF EXISTS sw_cab_info")
cursor.execute("DROP TABLE IF EXISTS sw_box_spec")
cursor.execute("DROP TABLE IF EXISTS sw_mat_spec")
cursor.execute("DROP TABLE IF EXISTS sw_box_info")
#使用预处理语句创建表
sql = '''
CREATE TABLE `sw_cab_info` (
`cabinet_id` VARCHAR(32) NOT NULL COMMENT '智能柜ID' COLLATE 'utf8mb4_general_ci',
`cabinet_name` VARCHAR(32) NOT NULL COMMENT '智能柜名称' COLLATE 'utf8mb4_general_ci',
`cabinet_ip` VARCHAR(16) NOT NULL COMMENT '智能柜IP' COLLATE 'utf8mb4_general_ci',
`cabinet_port` VARCHAR(8) NOT NULL COMMENT '智能柜端口' COLLATE 'utf8mb4_general_ci',
`cabinet_status` VARCHAR(2) NOT NULL DEFAULT '0' COMMENT '智能柜状态(0：正常；1：异常)' COLLATE 'utf8mb4_general_ci',
PRIMARY KEY (`cabinet_id`)
)
COMMENT='智能柜信息表'
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;
'''
sq2 = '''
CREATE TABLE `sw_box_spec` (
`box_spec` VARCHAR(8) NOT NULL COMMENT '箱体规格编码' COLLATE 'utf8mb4_general_ci',
`width` INT(11) NOT NULL COMMENT '宽度',
`height` INT(11) NOT NULL COMMENT '高度',
`depth` INT(11) NOT NULL COMMENT '深度',
PRIMARY KEY (`box_spec`)
)
COMMENT='箱体规格配置表'
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;
'''
sq3 = '''
CREATE TABLE `sw_mat_spec` (
`mat_spec` VARCHAR(8) NOT NULL COMMENT '物资规格编码' COLLATE 'utf8mb4_general_ci',
`box_spec` VARCHAR(8) NOT NULL COMMENT '箱体规格编码' COLLATE 'utf8mb4_general_ci',
`store_mode` VARCHAR(2) NOT NULL COMMENT '存储模式(0，按高度；1，按数量)' COLLATE 'utf8mb4_general_ci',
`store_quantity` INT(11) NOT NULL COMMENT '最大存储高度或存储数量',
`min_depth` INT(11) NOT NULL COMMENT '最小深度（长度）',
`max_depth` INT(11) NOT NULL COMMENT '最大深度（长度）',
`min_width` INT(11) NOT NULL COMMENT '最小宽度',
`max_width` INT(11) NOT NULL COMMENT '最大宽度',
`min_height` INT(11) NOT NULL COMMENT '最小高度',
`max_height` INT(11) NOT NULL COMMENT '最大高度',
PRIMARY KEY (`mat_spec`),
INDEX `box_spec_idx` (`box_spec`),
CONSTRAINT `mat_box_spec` FOREIGN KEY (`box_spec`) REFERENCES `sw_box_spec` (`box_spec`)
)
COMMENT='物资规格配置表'
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;
'''
sq4 = '''
CREATE TABLE `sw_box_info` (
`cabinet_id` VARCHAR(32) NOT NULL COMMENT '智能柜ID' COLLATE 'utf8mb4_general_ci',
`box_id` VARCHAR(32) NOT NULL COMMENT '箱体ID' COLLATE 'utf8mb4_general_ci',
`box_name` VARCHAR(32) NOT NULL COMMENT '箱体名称' COLLATE 'utf8mb4_general_ci',
`box_type` VARCHAR(8) NOT NULL COMMENT '箱体类型（0：箱体；1：中控机）' COLLATE 'utf8mb4_general_ci',
`box_spec` VARCHAR(8) NOT NULL COMMENT '箱体规格' COLLATE 'utf8mb4_general_ci',
`box_status` VARCHAR(2) NOT NULL DEFAULT '0' COMMENT '箱体闭合状态（0：关；1：开）' COLLATE 'utf8mb4_general_ci',
`light_status` VARCHAR(2) NOT NULL DEFAULT '0' COMMENT '灯状态（0：关；1：开）' COLLATE 'utf8mb4_general_ci',
`row` INT(11) NOT NULL COMMENT '所在行',
`column` INT(11) NOT NULL COMMENT '所在列',
`mat_spec` VARCHAR(8) NULL DEFAULT NULL COMMENT '存放物资规格' COLLATE 'utf8mb4_general_ci',
`store_mode` VARCHAR(2) NULL DEFAULT NULL COMMENT '存储模式（0，按高度；1，按数量）' COLLATE 'utf8mb4_general_ci',
`remain_space` INT(11) NULL DEFAULT NULL COMMENT '剩余存储空间（根据store_mode为高度或个数）',
PRIMARY KEY (`cabinet_id`, `box_id`),
INDEX `box_spec_idx` (`box_spec`),
INDEX `mat_spec_idx` (`mat_spec`),
CONSTRAINT `box_spec` FOREIGN KEY (`box_spec`) REFERENCES `sw_box_spec` (`box_spec`),
CONSTRAINT `cabinet_id` FOREIGN KEY (`cabinet_id`) REFERENCES `sw_cab_info` (`cabinet_id`),
CONSTRAINT `mat_spec` FOREIGN KEY (`mat_spec`) REFERENCES `sw_mat_spec` (`mat_spec`)
)
COMMENT='箱体信息表'
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;
'''
#执行SQL语句
cursor.execute(sql)
cursor.execute(sq2)
cursor.execute(sq3)
cursor.execute(sq4)
#关闭数据库的连接
db.close()9.2 操作数据表
import pymysql
# 打开数据库连接
db = pymysql.connect("localhost","root","root","demo",charset="utf8")
# 使用cursor()方法获取操作游标
cursor = db.cursor()
# 数据表
date = [('A1', 795, 470, 600),
('A2', 470, 470, 600),
('A3', 300, 470, 600),
('B2', 470, 470, 400),
('B3', 300, 470, 400),
]
try:
# 执行sql语句，插入多条数据
cursor.executemany("INSERT INTO `sw_box_spec`(`box_spec`, `width`, `height`, `depth`) values (%s,%s,%s,%s)",date)
# 提交数据
db.commit()
print('成功')
except:
# 发生错误回滚
db.rollback()
print('失败')
# 关闭数据库连接
db.close()
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
