安装
所有平台的 MySQL 下载地址为: MySQL 下载 。 挑选你需要的 MySQL Community Server 版本及对应的平台。
mysql管理
输入密码登录后进入终端
mysql -u root -p
查看数据库(注意分号结尾!!!)
show databases;
创建数据库
create DATABASE ARM_test;
选择数据库
use ARM_test;
查看表
show tables;
在所选数据库中建表
CREATE TABLE face_log(
id INT NOT NULL AUTO_INCREMENT,
dept_id VARCHAR(32),
camera_id VARCHAR(32),
face_pic BLOB NOT NULL,
timestamp TIMESTAMP,
face_vector BLOB NOT NULL,
PRIMARY KEY ( id )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
删除表
DROP TABLE face_log;
显示表数据
select * from face_log;
增加索引,提高表查询数据的效率
加索引前:
create index index_dept_id on person_count_face_log (dept_id) ;
create index index_timestamp on person_count_face_log (timestamp) ;
加索引后:
通过sqlalchemy对数据库进行查、增、减
sqltest.py
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, BLOB,TIMESTAMP
class face_log(declarative_base()):
__tablename__ = 'face_log'
id = Column('id', Integer, primary_key=True)
dept_id = Column('dept_id', String)
camera_id = Column('camera_id', String)
face_pic = Column('face_pic', BLOB)
timestamp = Column('timestamp', TIMESTAMP)
face_vector = Column('face_vector', BLOB)
DatabaseHelper.py
from datetime import datetime
import numpy as np
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from DBEntity.sqltest import face_log
class MySQLHelper:
def __init__(self, ip, port, username, password, db_name):
self.e = create_engine(
f"mysql+pymysql://{username}:{password}@{ip}:{port}/{db_name}?charset=utf8mb4&binary_prefix=true")
self.session = sessionmaker(bind=self.e)()
# 数据库查询操作
def get_data(self, start_ts, end_ts, place_id):
"""
获取时间段内的特征向量和人脸图像
:param start_ts: 开始时间
:param end_ts: 结束时间
:param place_id: 位置id
"""
all_result = self.session.query(face_log).filter(face_log.dept_id == place_id).filter(
face_log.timestamp.between(datetime.fromtimestamp(start_ts / 1000), datetime.fromtimestamp(end_ts / 1000))).all()
print('all_result: ',len(all_result))
if len(all_result) > 0:
to_return_imgs = [np.frombuffer(m_result.face_pic, dtype=np.uint8).reshape((88, 88, 3)) for m_result in
all_result]
to_return_timestamp = [m_result.timestamp for m_result in all_result]
return np.array(to_return_imgs), np.array(to_return_timestamp)
return None, None
# 数据库插入操作
def insert_data(self, place, device, img: np.ndarray, ts, feature_vector: np.ndarray):
to_insert = face_log()
try:
to_insert.dept_id = place
to_insert.camera_id = device
to_insert.face_pic = cv2.resize(img, (88, 88)).astype(np.uint8).tobytes()
to_insert.timestamp = datetime.fromtimestamp(ts / 1000)
to_insert.face_vector = feature_vector.astype(np.float16).tobytes()
self.session.add(to_insert)
self.session.commit()
except Exception as e:
print(e)
self.session.rollback()
# 数据库删除操作
def delete_data(self, start_time, end_time, place_id, tabel_name):
start_ts = int(time.mktime(time.strptime(start_time + ' 00:00:00', "%Y-%m-%d %H:%M:%S")))
end_ts = int(time.mktime(time.strptime(end_time + ' 00:00:00', "%Y-%m-%d %H:%M:%S")))
if tabel_name == "face_log":
if place_id == "None":
self.session.query(face_log).filter(
face_log.timestamp.between(datetime.fromtimestamp(start_ts),
datetime.fromtimestamp(end_ts))).delete(synchronize_session=False)
else:
self.session.query(face_log).filter(face_log.dept_id == place_id).filter(
face_log.timestamp.between(datetime.fromtimestamp(start_ts),
datetime.fromtimestamp(end_ts))).delete(synchronize_session=False)
self.session.commit()
else:
print('delete nothing')
排序
select dept_id,timestamp from person_count_face_log order by id desc limit 50;
ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。 默认情况下,它是按升序排列。
分组
GROUP BY 语句根据一个或多个列对结果集进行分组。在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。
select dept_id,count(*) from person_count_face_log group by dept_id;
连接的使用
OIN 按照功能大致分为如下三类:
- INNER JOIN(内连接,或等值连接):获取两个表中字段匹配关系的记录。
- LEFT JOIN(左连接):获取左表所有记录,即使右表没有对应匹配的记录。
- RIGHT JOIN(右连接): 与 LEFT JOIN 相反,用于获取右表所有记录,即使左表没有对应匹配的记录。
使用MySQL的INNER JOIN(也可以省略 INNER 使用 JOIN,效果一样)来连接以上两张表来读取runoob_tbl表中所有runoob_author字段在tcount_tbl表对应的runoob_count字段值:
SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a INNER JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;
runoob_tbl a 即 runoob_tbl AS a,表别名
以上 SQL 语句等价于:
SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a, tcount_tbl b WHERE a.runoob_author = b.runoob_author;
MySQL left join 与 join 有所不同。 MySQL LEFT JOIN 会读取左边数据表的全部数据,即便右边表无对应数据。以 runoob_tbl 为左表,tcount_tbl 为右表为例,该语句会读取左边的数据表 runoob_tbl 的所有选取的字段数据,即便在右侧表 tcount_tbl中 没有对应的 runoob_author 字段值,没有的字段值就为NULL。
SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a LEFT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;
MySQL 使用 SQL SELECT 命令及 WHERE 子句来读取数据表中的数据,但是当提供的查询条件字段为 NULL 时,该命令可能就无法正常工作。
为了处理这种情况,MySQL提供了三大运算符:
- IS NULL: 当列的值是 NULL,此运算符返回 true。
- IS NOT NULL: 当列的值不为 NULL, 运算符返回 true。
- <=>: 比较操作符(不同于 = 运算符),当比较的的两个值相等或者都为 NULL 时返回 true。
关于 NULL 的条件比较运算是比较特殊的。你不能使用 = NULL 或 != NULL 在列中查找 NULL 值 。
在 MySQL 中,NULL 值与任何其它值的比较(即使是 NULL)永远返回 false,即 NULL = NULL 返回false 。
MySQL 中处理 NULL 使用 IS NULL 和 IS NOT NULL 运算符。
正则表达式
MySQL中使用 REGEXP 操作符来进行正则表达式匹配。
下面是几个实例:
查找name字段中以'st'为开头的所有数据:
mysql> SELECT name FROM person_tbl WHERE name REGEXP '^st';
查找name字段中以'ok'为结尾的所有数据:
mysql> SELECT name FROM person_tbl WHERE name REGEXP 'ok$';
查找name字段中包含'mar'字符串的所有数据:
mysql> SELECT name FROM person_tbl WHERE name REGEXP 'mar';
查找name字段中以元音字符开头或以'ok'字符串结尾的所有数据:
mysql> SELECT name FROM person_tbl WHERE name REGEXP '^[aeiou]|ok$';
ALTER命令
当我们需要修改数据表名或者修改数据表字段时,就需要使用到MySQL ALTER命令。
如下命令使用了 ALTER 命令及 DROP 子句来删除以上创建表的 i 字段:
ALTER TABLE testalter_tbl DROP i;
索引
MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度。
创建索引时,你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。
实际上,索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录。
上面都在说使用索引的好处,但过多的使用索引将会造成滥用。因此索引也会有它的缺点:虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件。
例如为提高查询效率,这里因为筛选条件主要就是时间戳timestamp,执行以下命令添加普通索引:
create index index_timestamp on person_count_face_log (timestamp) ;
参考:
[3] MySQL 教程