# 查看端口
show variables like 'port';
# 查看表结构
desc tablename;
# 查看mysql版本
select version() from dual;
# 查看mysql时区
show variables like '%time_zone';

system_time_zone    CST
time_zone    +08:00

set global time_zone = '+08:00';
flush privileges;
# 全局时区和当前session时区
select @@global.time_zone, @@session.time_zone;
# 获取当前时间戳，各个类型
SELECT TIME_TO_SEC(NOW()),
       NOW(),
       current_timestamp(),
       REPLACE(unix_timestamp(NOW(3)), '.', ''),
       REPLACE(unix_timestamp(current_timestamp(3)), '.', ''),
       current_timestamp(3),
       unix_timestamp(current_timestamp(3));
# 审计日志相关
show variables like 'init_connect';
show variables like '%log_bin%';# 查看是否开启binlog
show variables like '%binlog%'; # 查看binlog相关参数
show variables like '%audit%';
set global init_connect=NULL;
show binlog events;# 只查看第一个binlog文件的内容
show binlog events in 'mysql-bin.000002'; # 查看指定binlog文件的内容
show master status;# 查看当前正在写入的binlog文件
show binary logs;# 获取binlog文件列表

drop user 'rehoni'@'%'; # 删除用户
select Host,user from mysql.user; # 查看用户
create user 'rehoni'@'%' identified by 'rehoni'; # 创建用户
ALTER USER dbadmin@localhost IDENTIFIED BY 'newpasswd3'; # 修改用户密码
update user set host='%' where mysql.user = 'his'; #开通远程访问权限
show grants for 'rehoni'@'%'; # 查看权限
grant all privileges on *.* to 'rehoni'@'%';# 赋予权限
flush privileges ; # 刷新权限
show grants for 'root'@'%';# 查看权限

alter table imp_transform_ocg
    add vc varchar(64) null comment 'c2h4/c2h6气体比值';

drop view imp_transform_ocg_latest;
create view imp_transform_ocg_latest as
select a.*
from imp_transform_ocg a,
     (
         select uri, max(ACQTM) acqtm
         from imp_transform_ocg
         group by uri) b
where a.URI = b.URI
  and a.ACQTM = b.acqtm
order by a.URI;

drop table operate_user;
create table
    operate_user
as
select NAME as username, '1' as priority, now() as control_time
from nrcloud.users;

select max(priority)
from operate_user
where control_time between
# 注意这里的data_sub和interval的用法
    date_sub(now(), interval '599999999' minute) and now()

-- 按空值排序
select FETIME, CLOSE_VAL, ROBOT_SCHEME, VIDEO_SCHEME
from ems_status e
order by
       # 1、传入这个条件，则此两个字段均为空的排在后边
       # e.ROBOT_SCHEME = '' and e.VIDEO_SCHEME = '';
       # 2、直接desc，逐个按顺序排列下去
       e.ROBOT_SCHEME desc, e.VIDEO_SCHEME desc;

-- 按长度排序
select FETIME, CLOSE_VAL, ROBOT_SCHEME, VIDEO_SCHEME, length(ROBOT_SCHEME) len
from ems_status
order by len desc;

select (@i := @i + 1) as row_num, A.*
from (
        # 需要添加行号的视图
         select b.id,
                a.SIGNAL_NAME,
                a.ID as signal_id,
                b.VIDEO_SCHEME,
                b.SOUND,
                b.SOUND_TYPE,
                b.video,
                b.PICTURE,
                d.STATION_NAME
         from base_signal a
                  left join relation_signal_scheme b on b.SIGNAL_ID = a.ID
                  left join relation_id c on c.signal_id = a.ID
                  left join base_station d on d.ID = c.station_id
     ) A,
     (Select @i := 0) B;

create view imp_transform_ocg_latest as
# as 后跟需要创建好的视图
select a.*
from imp_transform_ocg a,
     (
         select uri, max(ACQTM) acqtm
         from imp_transform_ocg
         group by uri) b
where a.URI = b.URI
  and a.ACQTM = b.acqtm
order by a.URI;

SELECT
CONCAT('ALTER table ',t2.table_schema,'.',t2.table_name,' CHANGE ',' \`',t2.column_name,'\` \`',UPPER(t2.column_name),'\` ',
t2.column_type,' ',CASE WHEN t2.is_nullable='NO' THEN 'not null' ELSE 'null' END,' COMMENT ''',t2.column_comment,''';') AS c
FROM information_schema.tables t1, information_schema.columns t2
WHERE t1.table_schema = 'sdjk'
AND t1.table_type = 'base table'
AND t1.table_schema = t2.table_schema
AND t1.table_name = t2.table_name;

show character set ;
select concat('alter table `',TABLE_NAME,'` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;')
as target_tables
from information_schema.TABLES
where TABLE_SCHEMA = 'standard'
and TABLE_TYPE = 'base table';

-- 1. to_date ==> str_to_date
str_to_date(#{startTime},'%Y-%m-%d %H:%i:%s')
-- 2. to_char ==> date_format 
date_format(acqtm,'%Y-%m-%d %H:%i:%s')
-- 3. || ==> concat
-- 4. sysdate() | sysdate

> select curtime();  #或select now()也可以
+-----------+
| curtime() |
+-----------+
| 15:18:10 |
+-----------+
> show variables like "%time_zone%";
+------------------+--------+
| Variable_name  | Value |
+------------------+--------+
| system_time_zone | CST  |
| time_zone    | SYSTEM |
+------------------+--------+
2 rows in set (0.00 sec)

> set global time_zone = '+8:00'; ##修改mysql全局时区为北京时间，即我们所在的东8区
> set time_zone = '+8:00'; ##修改当前会话时区
> flush privileges; #立即生效

# vim /etc/my.cnf ##在[mysqld]区域中加上
default-time_zone = '+8:00'
# /etc/init.d/mysqld restart ##重启mysql使新时区生效

mysql_query("SET time_zone = '+8:00'")

-- 创建 tbl 表，字段 data 为 json 类型
mysql> create table tbl (data JSON);
Query OK, 0 rows affected (0.17 sec)

-- 插入 json 对象
mysql> insert into tbl values ('{"id": 1, "name": "Will"}');
Query OK, 1 row affected (0.04 sec)

-- 插入 json 数组
mysql> insert into tbl values ('[1, 42, 1024]');
Query OK, 1 row affected (0.01 sec) 

-- 使用 json_object() 创建 json 对象
mysql> insert into tbl values (json_object('id', 2, 'name', 'Joe'));
Query OK, 1 row affected (0.02 sec)

-- 使用 json_array() 创建 json 数组
mysql> insert into tbl values (json_array(1, "abc", null, true, curtime()));
Query OK, 1 row affected (0.02 sec)

-- 查询 tbl 表数据
mysql> select * from tbl;
+-------------------------------------------+
| data                                      |
+-------------------------------------------+
| {"id": 1, "name": "Will"}                 |
| [1, 42, 1024]                             |
| {"id": 2, "name": "Andy"}                 |
| [1, "abc", null, true, "20:27:41.000000"] |
+-------------------------------------------+
4 rows in set (0.00 sec)

mysql> SELECT JSON_EXTRACT('{"name":"Zhaim","tel":"13240133388"}',"$.name");
+---------------------------------------------------------------+
| JSON_EXTRACT('{"name":"Zhaim","tel":"13240133388"}',"$.name") |
+---------------------------------------------------------------+
| "Zhaim"                                                       |
+---------------------------------------------------------------+

# 可以使用 column->path 的形式提取元素的值，对应字符串类型的 category->'$.name' 中还包含着双引号，可以用 JSON_UNQUOTE 函数将双引号去掉
# 从 MySQL 5.7.13 起也可以通过这个操作符 ->> 这个和 JSON_UNQUOTE 是等价的
# 用 -> 直接提取时要注意元素值的类型
mysql> SELECT * FROM lnmp WHERE category->>'$.name' = 'lnmp.cn';
+----+------------------------------+-----------+
| id | category                     | tags      |
+----+------------------------------+-----------+
|  1 | {"id": 1, "name": "lnmp.cn"} | [1, 2, 3] |
+----+------------------------------+-----------+

mysql> UPDATE lnmp SET category = JSON_INSERT(category, '$.name', 'lnmp', '$.url', 'www.lnmp.cn') WHERE id = 1;

mysql> SELECT * FROM lnmp;
+----+----------------------------------------------------+-----------+
| id | category                                           | tags      |
+----+----------------------------------------------------+-----------+
|  1 | {"id": 1, "url": "www.lnmp.cn", "name": "lnmp.cn"} | [1, 3, 4] |
|  2 | {"id": 2, "name": "php.net"}                       | [1, 3, 5] |
+----+----------------------------------------------------+-----------+

mysql> UPDATE lnmp SET category = JSON_SET(category, '$.host', 'www.lnmp.cn', '$.url', 'http://www.lnmp.cn') WHERE id = 1;

mysql> SELECT * FROM lnmp;
+----+----------------------------------------------------------------------------------+-----------+
| id | category                                                                         | tags      |
+----+----------------------------------------------------------------------------------+-----------+
|  1 | {"id": 1, "url": "http://www.lnmp.cn", "host": "www.lnmp.cn", "name": "lnmp.cn"} | [1, 3, 4] |
|  2 | {"id": 2, "name": "php.net"}                                                     | [1, 3, 5] |
+----+----------------------------------------------------------------------------------+-----------+

mysql> UPDATE lnmp SET category = JSON_REPLACE(category, '$.name', 'php', '$.url', 'http://www.php.net') WHERE id = 2;

mysql> SELECT * FROM lnmp;
+----+----------------------------------------------------------------------------------+-----------+
| id | category                                                                         | tags      |
+----+----------------------------------------------------------------------------------+-----------+
|  1 | {"id": 1, "url": "http://www.lnmp.cn", "host": "www.lnmp.cn", "name": "lnmp.cn"} | [1, 3, 4] |
|  2 | {"id": 2, "name": "php"}                                                         | [1, 3, 5] |
+----+----------------------------------------------------------------------------------+-----------+

mysql> UPDATE lnmp SET category = JSON_REMOVE(category, '$.url', '$.host') WHERE id = 1;

mysql> SELECT * FROM lnmp;
+----+------------------------------+-----------+
| id | category                     | tags      |
+----+------------------------------+-----------+
|  1 | {"id": 1, "name": "lnmp.cn"} | [1, 3, 4] |
|  2 | {"id": 2, "name": "php"}     | [1, 3, 5] |
+----+------------------------------+-----------+

SELECT a.ID, b.Name, b.Date FROM Customers a, Sales b WHERE a.ID = b.ID;

SELECT a.ID, b.Name, b.Date FROM Customers a INNER JOIN Sales b ON a.ID = b.ID;

ts_time timestamp NOT NULL DEFAULT NOW();
1

ts_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP();
1

ts_time1 time NOT NULL DEFAULT NOW();
ts_time3 yearNOT NULL DEFAULT NOW();
ts_time2 date NOT NULL DEFAULT CURRENT_TIMESTAMP();
ts_time2 datetime NOT NULL DEFAULT CURRENT_TIMESTAMP();1234

select current_timestamp();

select current_date();

select current_time();

select now();

# 添加 创建 更新 时间字段
ALTER TABLE `表名`
ADD COLUMN `create_time`  datetime NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `最后一个列名`,
ADD COLUMN `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER `create_time`;
# 修改已存在字段.

ALTER TABLE 表名
MODIFY COLUMN create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER create字段前面一个字段名,
MODIFY COLUMN update_time timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER create_time;

show columns from 表名;
desc 表名;
describe 表名;

select * from information_schema.columns
where TABLE_SCHEMA = '数据库名称'
and TABLE_NAME = '表名称';

select * from information_schema.columns
where TABLE_SCHEMA = '数据库名称';

-- 下面的sql有明显的问题,不过这个只是一个示意,
-- 从一个表中查询不需要用到交集和差集,条件可以合并在一起直接查询出来的.能明白意思就好
-- 下面的sql的意思是找到所有非技术部的员工的id,code和name
SELECT a.* FROM(
    SELECT id,code,name FROM test_emp
    UNION ALL
    SELECT id,code,name FROM test_emp WHERE dept='JSB'
)a GROUP BY a.id HAVING COUNT(a.id)=1

-- 下面的sql的意思是找到所有技术部年龄大于25的员工
SELECT a.* FROM(
    SELECT id,code,name FROM test_emp WHERE age>25
    UNION ALL
    SELECT id,code,name FROM test_emp WHERE dept='JSB'
)a GROUP BY a.id HAVING COUNT(a.id)=2

-- 下面的sql的意思是找到所有技术部的员工和年龄大于30的员工
-- union可以自动去除重复的内容,得到不重复的结果集
SELECT a.* FROM(
    SELECT id,code,name FROM test_emp WHERE age>25
    UNION
    SELECT id,code,name FROM test_emp WHERE dept='JSB'
)a

alter table alarm_global add id int(11) primary key AUTO_INCREMENT; # 添加自增主键
# 给已存在的字段重新配置主键
alter table alarm_camera_info add global_id int(11);
alter table alarm_robot_info drop PRIMARY KEY ;
alter table alarm_robot_info add PRIMARY KEY(global_id);

SELECT * FROM t_user WHERE username != "陈哈哈";
SELECT * FROM t_user WHERE username <> "陈哈哈";

select * from gyzd_yysinfo order by cast(yysid as SIGNED INTEGER)

一、手动转化类型(直接+0)
select server_id from cardserver where game_id = 1 order by server_id+0 desclimit 10

二、使用MySQL函数CAST
select server_id from cardserver where game_id = 1 order by CAST(server_id as SIGNED) desc limit 10;

三、使用MySQL函数CONVERT
select server_id from cardserver where game_id = 1 order by CONVERT(server_id,SIGNED)desc limit 10;