mysql操作笔记

1. 服务启动：打开cmd窗口，进入到 mysql bin目录的路径下。

net start myql

2.控制台连接，打开cmd窗口，进入到 mysql bin目录的路径下，在命令行输入：mysql -u 用户名 -p密码；回车；-h表示服务器名，localhost表示本地，-hlocalhost 可不输入；-u为数据库用户名，root是mysql默认用户名；-p为密码，如果设置了密码，可直接在-p后链接输入，如：-p123456，用户没有设置密码，显示Enter password时，直接回车即可。）
 

mysql -hlocalhost -uroot -proot

3. 查看整个库的存储大小

SELECT CONCAT(round(sum(DATA_LENGTH/1024/1024),2),"MB") as datazise FROM information_schema.TABLES;

4、查看指定库的存储大小

SELECT CONCAT(round(sum(DATA_LENGTH/1024/1024),2),"MB") as datazise FROM information_schema.TABLES WHERE table_schema="test";

5、查看指定表的存储大小

SELECT CONCAT(round(sum(DATA_LENGTH/1024/1024),2),"MB") as datazise FROM information_schema.TABLES WHERE table_schema="text" and table_name="xxx";

6、SQL语句中使用 limit 1  可以提高查询效率

LIMIT 1 适用于查询结果为1条（也可能为0）会导致全表扫描的sql语句。只要加上了limit 1 ，只要找到了一条对应的记录，就不会继续向下扫描了，效率会大大提高。如果条件是索引的话，就不用加上limit 1，如果是根据主键查询一条记录 也不用加上limit 1 ，因为主键也是索引。

SELECT * FROM t_user WHERE email=' aliyunzixun@xxx.com'; 耗时0.56 s 
SELECT * FROM t_user WHERE email=' aliyunzixun@xxx.com' LIMIT 1; 耗时0.00 s 

7、统计多张关联子表中的数量

select  ig.*,iai.num as AI,iao.num as AO,idi.num as DI,ido.num as DO from iot_gateway ig
left join (select ai.gateway_id,count(ai.gateway_id) as num from  iot_gateway_ai ai group by ai.gateway_id) iai on ig.gateway_id =iai.gateway_id 
left join  (select ao.gateway_id,count(ao.gateway_id) as num from  iot_gateway_ao ao group by ao.gateway_id) iao on ig.gateway_id =iao.gateway_id  
left join (select di.gateway_id,count(di.gateway_id) as num from  iot_gateway_di di group by di.gateway_id) idi on ig.gateway_id =idi.gateway_id 
left join  (select do.gateway_id,count(do.gateway_id) as num from  iot_gateway_do do group by do.gateway_id) ido on ig.gateway_id =ido.gateway_id  
group by ig.gateway_id ;

8、将各表count（*）结果求和。

      SELECT SUM(node) FROM (
            SELECT count(ai_id) as node FROM iot_gateway_ai WHERE gateway_id =#{gatewayId}
            UNION ALL
            SELECT count(ao_id) as node  FROM iot_gateway_ao WHERE gateway_id=#{gatewayId}
            UNION all
            SELECT count(di_id) as node FROM iot_gateway_di WHERE gateway_id=#{gatewayId}
            UNION all
            SELECT count(do_id) as node FROM iot_gateway_do WHERE gateway_id=#{gatewayId}
      ) as nodeNum

9、mysql数据库输入时间时没有输入时分秒（'2022-12-05'） 默认为 00:00:00 （'2022-12-05 00:00:00'）。
 

10、查询最近n天的数据，较好的写法，使用TO_DAYS() 函数，返回从0000年（公元1年）至当前日期的总天数。

# 1【效率高】
SELECT * FROM `data` WHERE TO_DAYS(now()) - TO_DAYS(update_time) <= 30;

11、时间查询，以字符串形式入参。（如'2023-03-16 00:00:00'）需要使用数据库的date()函数进行转化，包括匹配的时间查询的字段也需要date() 函数。DATE（）函数从表达式中提取日期值。

SELECT SUM(drp)  FROM yangzhourtu.stddb.st_pptn_r WHERE stcd = 'HT0061071151000010'  AND Date(tm)  BETWEEN date('2023-04-03 00:00:00') AND date('2023-04-03 18:50:40')

12、mysql 主键类型的选择，查询速度，int > bigint > varchar; 占用磁盘空间，int 存储大小为 4 个字节；bigint 存储大小为 8 个字节，int < bigint < varchar

13、mysql 去除字段中的空格

update  `work_fangxun_station_shuiqing`  set data_name = REPLACE(data_name ,' ','') ;

14、Mysql不能使用具体ip连接数据库问题

update user set host='%' where user='root';

//必须使用flush privileges的两种情况1、改密码。2、授权超用户。

flush privileges;

16、mysql 将整数型文本转为数字并求和，CAST()函数，把一个字段转成另一个字段，主要转化的是字段的类型，其语法为：cast(字段名 as 转换的类型 )，转换的类型共有： CHAR[(N)]  字符型、DATE  日期型、DATETIME  日期和时间型、DECIMAL    float型、SIGNED  int型、TIME   时间型。

sum(cast(fxdw_population AS SIGNED))

17、查看MySQL数据库存储路径

show variables like '%datadir%';