mysql tricks – Memory of 42

查看數據庫中各表的大小

SELECT TABLE_NAME,DATA_LENGTH+INDEX_LENGTH,
TABLE_ROWS,concat(round((DATA_LENGTH+INDEX_LENGTH)/1024/1024,2), 'MB') 
as data FROM TABLES WHERE TABLE_SCHEMA='dbname';

#mariadb
SELECT table_schema as `DB`, table_name AS `Table`, 
  ROUND(((data_length + index_length) / 1024 / 1024), 2) `Size (MB)` 
  FROM information_schema.TABLES 
  ORDER BY (data_length + index_length) DESC;

修改數據庫表文件的儲存位置（詳細步驟）

#登入數據庫
mysql -u root -p
#查看數據庫的存放位置，即datadir的值
show variables like '%dir%';
#退出數據庫
quit
#停止mysql，失敗的話用root權限
service mysqld stop
#創建新的數據儲存位置
mkdir /data/mysql
#將舊文件移動到新位置，第一個參數是前面datadir的值
mv /usr/local/mysql/data/* /data/mysql
#修改新位置的權限
chown mysql:mysql -R /data/mysql/
#修改數據庫配置，修改這一行datadir=/data/mysql
nano /etc/my.cnf
##修改數據庫配置，修改這一行datadir=/data/mysql
##這個我並沒有找到，所以不修改應該也可以
##nano /etc/init.d/mysqld
#啟動數據庫服務
service mysqld start

參考鏈接：mysql数据库目录存放位置更改

導入與導出mysql數據庫

#導出數據庫，前面是mysql的程序位置
/data/soft/mysql-5.5.30/client/mysqldump -u  root -p video > db_video.sql
#壓縮一下便於傳輸
tar -zcvf db_video.tar.gz db_video.sql
#導入某數據庫
mysql -u user42 -p -h localhost video < db_video.sql
#導出所有數據庫
mysqldump -u root -p --all-databases | gzip > alldb.sql.gz
#導入所有數據庫
gunzip < alldb.sql.gz | mysql -u root -p
#僅導出一張表
mysqldump -p --user=username dbname tableName > tableName.sql
#將導出的一張表導入數據庫
mysql -u username -p -D dbname < tableName.sql

新增用戶及設置權限

转载自：MySQL新增用户以及数据库访问授权

mysql -u root -p
# 允许本地 IP 访问 localhost, 127.0.0.1
CREATE USER 'user42'@'localhost' IDENTIFIED BY 'PASSWORD';
# 允许外网 IP 访问
CREATE USER 'user42'@'%' IDENTIFIED BY 'PASSWORD';
# 刷新授权
flush privileges;
# 创建数据库
create database db42 DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;
# 授予用户通过外网IP对于该数据库的全部权限
GRANT ALL ON db42.* TO 'user42'@'%';
# 刷新权限
flush privileges;
# 退出 root 重新登录
\q
# 已新帐号 user42 登录，由于使用的是 % 任意IP连接，所以需要指定外部访问IP
mysql -u user42 -h 192.168.1.168 -p

新建一個庫一張表

CREATE DATABASE `djangoTask` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE TABLE `bonus` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `userid` int(10) NOT NULL,
  `bonus` int(10) DEFAULT NULL COMMENT '奖励金',
  `level` int(3) DEFAULT NULL COMMENT '等级',
  `nowTimestamp` int(13) DEFAULT NULL COMMENT '插入时间戳',
  `nowDateTime` datetime DEFAULT NULL COMMENT '插入时间',
  `updateInfo` varchar(128) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=66156 DEFAULT CHARSET=utf8 COMMENT='bonus';

對某個時間段內某個值出現的次數做統計

SELECT info, count(info) FROM aso.aso_apple 
where nowTimestamp>'1489161600' GROUP BY info;

對某個時間段內的數據按天展示統計

SELECT COUNT(id) as 'sum', DATE_FORMAT(nowDatetime, '%Y-%m-%d') as 'day'
FROM aso.aso_apple WHERE nowTimestamp>'1488643200'
GROUP BY DATE_FORMAT(nowDatetime, '%Y-%m-%d');

昨天各商品的銷售總額

SELECT product_id, 
SUM (order_price) 
FROM orders WHERE create_time > CURDATE()-1
GROUP BY product_id;

使用inner join查看關聯數據

SELECT * FROM test.t_account a 
INNER JOIN test.t_client b 
ON a.cId = b.id WHERE b.sn like '%sz%';

刪除inner join查詢到的關聯數據

DELETE a.* FROM test.t_account a
INNER JOIN test.t_client b ON a.cId = b.id
WHERE (b.sn like '%sz%');

更新inner join查詢到的關聯數據

UPDATE task_new.t_account AS a 
INNER JOIN task_new.t_client AS b 
	ON a.cId = b.id 
	AND b.sn like '%sz%'
SET a.status='1' ;

使用子查詢

SELECT id, first_name
FROM student_details
WHERE first_name IN (SELECT first_name
FROM student_details
WHERE subject= 'Science'); 
<h3>使用新表來去重</h3>
[code language="sql"]#先新建一張表
CREATE TABLE newtable LIKE oldtable; 
#將去重後的數據插入新表
INSERT INTO newtable SELECT * FROM oldtable GROUP BY key;
#把舊表備份下
ALTER TABLE oldtable RENAME TO  oldtable_bak ;
#把新表改名為舊錶
ALTER TABLE newtable RENAME TO  oldtable ;

表結構發生變化後，從舊表複製數據到新表

#先新建一張表
 INSERT INTO `NEW_DB`.`NEW_TABLE` (`latitude`, `longitude`,`accuracy`,`altitude_accuracy`,
 `heading`,`speed`,`loc_time_text`, `loc_time`, `insert_time`, `address`, `ip`, `isp`, `text`, `owner_id`) 
 SELECT `latitude`, `longitude`,FLOOR(`accuracy`),`altitudeAccuracy`,
 `heading`,`speed`,`locTime`, `locTime`, `locTime`, `geoCode`, `ip`, `isp`, `text`, 1 
FROM `OLD_DB`.`OLD_TABLE`;

extendsclass.com提供了很多免費的在線測試工具，其中有一個免費的在線測試 MySQL 語句的工具可供大家測試 MySQL 語句。

重置 MariaDB 的 root 用戶密碼

如果我轉述的有不通的地方，請參考原文：How To Reset Your MySQL or MariaDB Root Password on Ubuntu 20.04，原文還包含了重置 MySQL 數據庫 root 用戶密碼的步驟。

首先非常建議安裝一個和要恢復密碼相同的數據庫版本相同的測試數據庫以供測試，你都把 root 密碼搞丟了，還是不要直接在產品數據庫上做實驗了。你可以通過 mysql --version 來查看數據庫的版本。

開始恢復前先關閉數據庫

sudo systemctl stop mariadb

然後用不檢查權限的方式運行數據庫

sudo systemctl set-environment MYSQLD_OPTS="--skip-grant-tables --skip-networking"
sudo systemctl start mariadb

現在你就可以登入數據庫並重置密碼了

sudo mysql -u root
FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
FLUSH PRIVILEGES;
#下面兩行我執行後報錯
#但是並不影響，我還是成功修改了密碼
UPDATE mysql.user SET authentication_string = '' WHERE user = 'root';
UPDATE mysql.user SET plugin = '' WHERE user = 'root';
exit

可以恢復到正常模式啓動數據庫並檢驗新密碼了

sudo systemctl unset-environment MYSQLD_OPTS
sudo systemctl restart mariadb
mysql -u root -p

本文更新於 2023/11/21。