共計 2317 個字符,預計需要花費 6 分鐘才能閱讀完成。
這篇文章給大家分享的是有關 MySQL 常用拼接語句有哪些的內容。丸趣 TV 小編覺得挺實用的,因此分享給大家做個參考,一起跟隨丸趣 TV 小編過來看看吧。
1. 拼接查詢所有用戶
SELECT DISTINCT
CONCAT(
User: \ ,
USER,
\ @\ ,
HOST,
\
) AS QUERY
mysql.USER;
# 當拼接字符串中出現 時 需使用 \ 轉義符
2. 拼接 DROP table
SELECT
CONCAT(
DROP table ,
TABLE_NAME,
)
information_schema. TABLES
WHERE
TABLE_SCHEMA = test
3. 拼接 kill 連接
SELECT
concat(KILL , id, )
information_schema. PROCESSLIST
WHERE
STATE LIKE Creating sort index
4. 拼接創建數據庫語句
SELECT
CONCAT(
create database ,
` ,
SCHEMA_NAME,
` ,
DEFAULT CHARACTER SET ,
DEFAULT_CHARACTER_SET_NAME,
) AS CreateDatabaseQuery
information_schema.SCHEMATA
WHERE
SCHEMA_NAME NOT IN (
information_schema ,
performance_schema ,
mysql ,
sys
);
5. 拼接創建用戶的語句
SELECT
CONCAT(
create user \ ,
user,
\ @\ ,
Host,
\
IDENTIFIED BY PASSWORD \ ,
authentication_string,
\
) AS CreateUserQuery
mysql.`user`
WHERE
`User` NOT IN (
root ,
mysql.session ,
mysql.sys
);
#有密碼字符串哦 在其他實例執行 可直接創建出與本實例相同密碼的用戶
6. 導出權限腳本 這個 shell 腳本也用到了拼接
#!/bin/bash
#Function export user privileges
pwd=yourpass
expgrants()
{
mysql -B -u root -p${pwd} -N $@ -e SELECT CONCAT( SHOW GRANTS FOR , user, @ , host, ) AS query FROM mysql.user | \
mysql -u root -p${pwd} $@ | \
sed s/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/-- \1 /;/--/{x;p;x;}
}
expgrants /tmp/grants.sql
echo flush privileges; /tmp/grants.sql
7. 查找表碎片
SELECT t.TABLE_SCHEMA,
t.TABLE_NAME,
t.TABLE_ROWS,
concat(round(t.DATA_LENGTH / 1024 / 1024, 2), M ) AS size,
t.INDEX_LENGTH,
concat(round(t.DATA_FREE / 1024 / 1024, 2), M ) AS datafree
FROM information_schema.tables t
WHERE t.TABLE_SCHEMA = test order by DATA_LENGTH desc;
8. 查找無主鍵表 這個沒用到拼接 也分享出來吧
# 查找某一個庫無主鍵表
SELECT
table_schema,
table_name
information_schema.TABLES
WHERE
table_schema = test
AND TABLE_NAME NOT IN (
SELECT
table_name
FROM
information_schema.table_constraints t
JOIN information_schema.key_column_usage k USING (
constraint_name,
table_schema,
table_name
)
WHERE
t.constraint_type = PRIMARY KEY
AND t.table_schema = test
#查找除系統庫外 無主鍵表
SELECT
t1.table_schema,
t1.table_name
information_schema. TABLES t1
LEFT OUTER JOIN information_schema.TABLE_CONSTRAINTS t2 ON t1.table_schema = t2.TABLE_SCHEMA
AND t1.table_name = t2.TABLE_NAME
AND t2.CONSTRAINT_NAME IN (PRIMARY)
WHERE
t2.table_name IS NULL
AND t1.TABLE_SCHEMA NOT IN (
information_schema ,
performance_schema ,
mysql ,
sys
) ;
感謝各位的閱讀!關于“MySQL 常用拼接語句有哪些”這篇文章就分享到這里了,希望以上內容可以對大家有一定的幫助,讓大家可以學到更多知識,如果覺得文章不錯,可以把它分享出去讓更多的人看到吧!
正文完