MySQL

最新推荐文章于 2023-07-22 18:14:59 发布

通信程序猿

最新推荐文章于 2023-07-22 18:14:59 发布

阅读量135

点赞数

分类专栏： sql Python 文章标签： mysql

本文链接：https://blog.csdn.net/u011285477/article/details/102131520

版权

Python 同时被 2 个专栏收录

30 篇文章 0 订阅

订阅专栏

sql

4 篇文章 0 订阅

订阅专栏

安装

所有平台的 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) ;

参考：

[1] SQL基础(定义|基本语句|基本函数)

[2] 通过sqlalchemy对数据库进行增删改查

[3] MySQL 教程

通信程序猿

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL

安装所有平台的 MySQL 下载地址为：MySQL 下载。挑选你需要的MySQL Community Server版本及对应的平台。mysql管理输入密码登录后进入终端mysql -u root -p查看数据库（注意分号结尾！！！）show databases; 创建数据库create DATABASE ARM_test;选择数据库u...
复制链接

扫一扫