mysql常用方法
一、基本用法
-- MySQL創建唯一索引
CREATE UNIQUE INDEX 索引名 ON 表名(列名1,列名2,...);
--也可以使用ALTER TABLE語句給現有表添加唯一索引（UNIQUE）
ALTER TABLE 表名 ADD CONSTRAINT 索引名 UNIQUE KEY(列名1,列名2,...);
alter table car_data add constraint car_data_evtId_uindex unique (evtId)
-- 刪除索引
ALTER TABLE plan_project DROP KEY plan_project_field_project_number_UN;
-- 時間戳互轉
SELECT FROM_UNIXTIME(1617184000); -- 假設1617184000是你的Unix時間戳
SELECT UNIX_TIMESTAMP('2021-03-31 00:00:00');
-- 添加列
alter table people_data Add photoBase64 longtext AFTER photo;
-- 把applyTime的值置為null
update people_data set applyTime = null;-- 創建數據庫
create database db_example;
CREATE DATABASE `test-test`;
-- 創建用戶
create user 'springuser'@'localhost' identified by '123456'
grant all on db_example.* to 'springuser'@'localhost' -- 查看一個數據中所有表的相關信息
show table status \G
show table status like 'face_data' \G;
-- 根據表2字段值跟新表1字段值
update device_channel T1 ,test T2
set T1.longitude = T2.longitude , T1.latitude = T2.latitude, T1.longitudeGcj02 = T2.longitude , T1.latitudeGcj02 = T2.latitude, T1.longitudeWgs84 = T2.longitude , T1.latitudeWgs84 = T2.latitude
where T1.deviceId = T2.deviceId
-- update select用法
update device_data T3
left join
(select T1.id,T1.name,T2.latitude,T2.longitude from device_data T1 LEFT JOIN device_channel T2 on T1.name = T2.name)T
on T3.id = T.id
set T3.lat = T.latitude,T3.lng = T.longitude
-- mysql姓名、身份證號、手機號脫敏
SELECT T1.id,
if(LENGTH(T1.name)>6,CONCAT(LEFT(T1.name,1), '*',RIGHT(T1.name,1) ),CONCAT(LEFT(T1.name,1), '*' )) AS name,
CONCAT(LEFT(T1.phone,4), '***' ,RIGHT(T1.phone,4)) AS phone,CONCAT(LEFT(T1.idCard,6), '********' ,RIGHT(T1.idCard,4))
FROM people_data T1 limit 100;
-- mysql導出數據
mysqldump -u 用戶名 -p 數據庫名 表名 > 導出文件名
mysqldump -u root -p test facedata > facedata.sql
mysqldump -u root -p --no-data test2 facedata > facedata.sql-- mysql解決死鎖
SELECT trx_mysql_thread_id , trx_query FROM INFORMATION_SCHEMA.INNODB_TRX;
SHOW PROCESSLIST;
kill trx_mysql_thread_id
-- coalesce函數用法
coalesce(expression_1, expression_2, ...,expression_n)
依次參考各參數表達式，遇到非null值即停止并返回該值。如果所有的表達式都是空值，最終將返回一個空值。
把photo中的http://192.168.1.123:8080替換為https://afxq.com
REPLACE(photo,'http://192.168.1.123:8080','https://afxq.com')
UPDATE papply_data set photo = REPLACE(photo,'http://192.168.1.123:8080','https://afxq.com') where communityId = '57558d7c1eee46399e216946d655afae' and photo like 'http://192.168.1.123:8080%' -- mysql導出數據字典
-- 單個表導出
SELECTcolumn_name AS '字段名',column_type AS '字段類型',( CASE WHEN is_nullable = 'YES' THEN '是' ELSE '否' END ) AS '是否可空',( CASE WHEN column_key = 'PRI' THEN '是' ELSE '否' END ) AS '是否主鍵',column_comment AS '注釋'
FROMinformation_schema.COLUMNS
WHEREtable_schema = 'test2' AND table_name = 'frp_port';-- 整個庫導出
SELECTtable_name AS '表名',column_name AS '字段名',column_type AS '字段類型',( CASE WHEN is_nullable = 'YES' THEN '是' ELSE '否' END ) AS '是否可空',( CASE WHEN column_key = 'PRI' THEN '是' ELSE '否' END ) AS '是否主鍵',column_comment AS '注釋'
FROMinformation_schema.COLUMNS
WHEREtable_schema = 'sl-cloud'-- limit用法
LIMIT 接受一個或兩個數字參數。參數必須是一個整數常量。如果給定兩個參數，第一個參數指定第一個返回記錄行的偏移量，第二個參數指定返回記錄行的最大數目。
初始記錄行的偏移量是 0(而不是 1)
-- 取第一條
SELECT communityId FROM (select communityId,count(*) AS num from face_data fd where flag = 0 group by communityId )T order by num desc limit 0,1
-- 取第二條到第十條
SELECT communityId FROM (select communityId,count(*) AS num from face_data fd where flag = 0 group by communityId )T order by num desc limit 1,9 -- 查看當前時區
在MySQL客戶端，可以通過如下命令查看當前時區：
SELECT @@global.time_zone;
查看當前時區的設置，可以通過如下命令：
SHOW VARIABLES LIKE '%time_zone%'; -- 獲取字符串中逗號的個數
SELECT '0,100,101,183',LENGTH('0,100,101,183')-LENGTH(REPLACE('0,100,101,183',',',''))
-- 獲取字符串中指定位置的值
SELECT REGEXP_SUBSTR('0,100,101,183', '[^,]+', 1, 3); -- mysql8 分組排序
SELECT * FROM (SELECT *, ROW_NUMBER()OVER(PARTITION BY name ORDER BY create_time DESC) AS rowNumber FROM monitor_info
)t
WHERE rowNumber = 1 -- 根據收入和支出統計銷售額
SELECT T1.center_id,ifnull(sum(case when T1.status != 3 then amount END),0) AS total, -- 總營業額ifnull(sum(case when T1.status = 3 then amount END),0) AS refund, -- 退款ifnull(sum(case when T1.status = 3 then -amount else amount END),0) AS netRevenue, -- 凈營業額count(distinct order_id) AS orderNum -- 訂單數
FROM `log` T1
LEFT JOIN center T2
ON T1.center_id = T2.id
GROUP BY T1.center_id
ORDER BY T1.update_time DESC
二、常見問題
1、Mysql刪除數據后磁盤空間未釋放的解決辦法
使用delete刪除的時候，mysql并沒有把數據文件刪除，而是將數據文件的標識位刪除，沒有整理文件，因此不會徹底釋放空間
官方推薦使用 OPTIMIZE TABLE命令來優化表，該命令會重新利用未使用的空間，并整理數據文件的碎片。
（1）、drop table table_name 立刻釋放磁盤空間 ，不管是 Innodb和MyISAM ;
（2）、truncate table table_name 立刻釋放磁盤空間 ，不管是 Innodb和MyISAM 。
truncate table其實有點類似于drop table 然后create,只不過這個create table 的過程做了優化，比如表結構文件之前已經有了等等。所以速度上應該是接近drop table的速度;
（3）、delete from table_name刪除表的全部數據,對于MyISAM會立刻釋放磁盤空間（應該是做了特別處理，也比較合理）,InnoDB 不會釋放磁盤空間;
（4）、對于delete from table_name where xxx帶條件的刪除, 不管是innodb還是MyISAM都不會釋放磁盤空間;
（5）、delete操作以后使用optimize table table_name 會立刻釋放磁盤空間。不管是innodb還是myisam 。所以要想達到釋放磁盤空間的目的，delete以后執行optimize table 操作。
對于myisam可以直接使用 optimize table table_name, 當是InnoDB引擎時，會報“Table does not support optimize, doing recreate + analyze instead”，
一般情況下，由myisam轉成innodb，會用alter table table.name engine='innodb’進行轉換，優化也可以用這個。
所以當是InnoDB引擎時我們就用alter table table.name engine='innodb’來代替optimize做優化就可以
（6）、delete from表以后雖然未釋放磁盤空間，但是下次插入數據的時候，仍然可以使用這部分空間。
通過alter table A engine=innodb來重建表，實現數據庫空間回收
參考博客：https://blog.51cto.com/u_710020/5680099
2、Unsupported character encoding ‘utf8mb4’
更改 JDBC 連接字符串
原連接字符串：
jdbc:mysql://localhost:3306/db_xxxxx?useSSL=false&characterEncoding=utf8mb4
修改后的連接字符串：
jdbc:mysql://localhost:3306/db_xxxxx?useSSL=false&character_set_server=utf8mb4
參考博客：https://blog.csdn.net/mingjunlintian/article/details/134818941
3、mysql中字符串截取與拆分
參考博客：https://blog.csdn.net/liqinglonguo/article/details/134673961
4、MySQL 連接數過多的處理方法 Too many connections
查看最大連接數
SHOW VARIABLES LIKE ‘max_connections’;
臨時調整當前 MySQL 連接數
set GLOBAL max_connections = 300;
5、MySQL中的not in和null
當我們在MySQL中使用not in時，例如
select id
from user
when id not in(…)
如果not in(…)數據中有null時，返回的結果是空表
錯誤在于判斷 a not in B的方法的本質是a 使用 != 與B中的每一條進行判斷
在MySQL中, null代表的就是缺失未知值而不是空值, null與任何值用<>=等運算符判斷時候返回的都是null
所以在使用not in 時需要注意數據中是否有null，并且null的判斷使用的是is null, 或者is not null, 使用=是不行的
參考博客：https://blog.csdn.net/qq_52059326/article/details/130906129
)
)
