…

sqlite

sqlite3 filename.db

.databases
.tables
.scheme tbname

sql 语句

select * from tbname;

join

A inner join B: A交B
A left join B: A表所有
A right join B: B表所有
A full outer join B: A和B的所有
cross join：笛卡尔积，A 乘 B

on 和 where
on: 只是限定 join 的匹配字段，返回时不管这个真假的
where: 限定过滤条件

union，合并两个或多个 SELECT 语句的结果集。

union: 选取不同的值
union all: 选取所有值

create table

CREATE TABLE table_name
(
column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
….
);

sql约束

NOT NULL - 指示某列不能存储 NULL 值。
UNIQUE - 保证某列的每行必须有唯一的值。
PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
CHECK - 保证列中的值符合指定的条件。
DEFAULT - 规定没有给列赋值时的默认值。

having

在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数一起使用。
where在group by前， having在group by 之后

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value;

exists

判断查询子句是否有记录，如果有一条或多条记录存在返回 True，否则返回 False。

SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);


SELECT
	websites.NAME,
	websites.url
FROM
	websites
WHERE
	EXISTS ( SELECT 1 FROM access_log WHERE websites.id = access_log.site_id AND count > 200 )

对外表做loop，每次做子查询，子查询如果有，就返回true

row_number ( ) over ( PARTITION BY tcb.vin, tcb.SERVICEID ORDER BY tcb.TIME_STAMP DESC ) rn

oracle
按照 tcb.vin, tcp.SERVICEID 分组，内部按照 tcb.TIME_STAMP 降序排列，最后的值 rn 是每组内部排序后的顺序编号(组内连续且唯一)

时间 int, timestamp, datetime 效率

对于 MyISAM 引擎，不建立索引的情况下（推荐），效率从高到低：int > UNIXTIMESTAMP(timestamp) > datetime（直接和时间比较）> timestamp（直接和时间比较）> UNIXTIMESTAMP(datetime) 。
对于 MyISAM 引擎，建立索引的情况下，效率从高到低：UNIXTIMESTAMP(timestamp) > int > datetime（直接和时间比较）>timestamp（直接和时间比较）>UNIXTIMESTAMP(datetime) 。
对于 InnoDB 引擎，没有索引的情况下(不建议)，效率从高到低：int > UNIXTIMESTAMP(timestamp) > datetime（直接和时间比较） > timestamp（直接和时间比较）> UNIXTIMESTAMP(datetime)。
对于 InnoDB 引擎，建立索引的情况下，效率从高到低：int > datetime（直接和时间比较） > timestamp（直接和时间比较）> UNIXTIMESTAMP(timestamp) > UNIXTIMESTAMP(datetime)。
一句话，对于 MyISAM 引擎，采用 UNIX_TIMESTAMP(timestamp) 比较；对于InnoDB 引擎，建立索引，采用 int 或 datetime直接时间比较。

mysql

mysql不支持子查询使用 limit

比如:
select t2.id, t2.uuid from tb_alarm_addinfo t2 where t2.uuid in (
select t1.uuid from tb_alarm_record t1 where t1.starttime <= UNIX_TIMESTAMP(‘2020-06-01 00:00:00’ limit 10 )

会报错

可以多加一层，建个虚拟表就行

select t2.id, t2.uuid from tb_alarm_addinfo t2 where t2.uuid in (
select tt.uuid from (
select t1.uuid from tb_alarm_record t1 where t1.starttime <= UNIX_TIMESTAMP(‘2020-06-01 00:00:00’) limit 10) as tt )

时间戳

mysql中存储 unix_timestamp, 用的是 UTC 时间

设置单次会话的时区
set time_zone=’+8:00’;

select from_unixtime(starttime), uuid from ivss.tb_alarm_record where starttime >= unix_timestamp(‘2022-10-06 00:00:00’) and starttime <= unix_timestamp(‘2022-10-07 00:00:00’) order by id desc limit 10;

二进制打印

select *,hex(from_base64(alarm_uuid)) from ivss.tb_alarm_extend order by id desc limit 10;

binlog

查看binlog：
show binary logs;
删除某个binlog文件之前的所有binlog文件，不包括该文件
purge binary logs to ‘mysql-bin.002639’;

Morning~Sun.

用到的mysql