前言:?
目前創(chuàng)新互聯(lián)已為上1000家的企業(yè)提供了網(wǎng)站建設(shè)、域名、網(wǎng)絡(luò)空間、網(wǎng)站托管、服務(wù)器托管、企業(yè)網(wǎng)站設(shè)計、城西網(wǎng)站維護(hù)等服務(wù),公司將堅持客戶導(dǎo)向、應(yīng)用為本的策略,正道將秉承"和諧、參與、激情"的文化,與客戶和合作伙伴齊心協(xié)力一起成長,共同發(fā)展。
日常工作或?qū)W習(xí)過程中,我們可能會經(jīng)常用到某些SQL,建議大家多多整理記錄下這些常用的SQL,這樣后續(xù)用到會方便很多。筆者在工作及學(xué)習(xí)過程中也整理了下個人常用的SQL,現(xiàn)在分享給你!可能有些SQL你還不常用,但還是希望對你有所幫助,說不定某日有需求就可以用到。
注:下文分享的SQL適用于MySQL 5.7 版本,低版本可能稍許不同。有些SQL可能執(zhí)行需要較高權(quán)限。
# 查看實例參數(shù) 例如:
show variables like '%innodb%';
show global variables like '%innodb%';
# 查看實例狀態(tài),例如:
show status like 'uptime%';
show global status like 'connection%';
# 查看數(shù)據(jù)庫鏈接:
show processlist;
show full processlist;
# 查詢某個表的結(jié)構(gòu):
show create table tb_name;
# 查詢某個表的詳細(xì)字段信息:
show full columns from tb_name;
# 查詢某個表的全部索引信息:
show index from tb_name;
# 查詢某個庫以cd開頭的表:
show tables like 'cd%';
# 查詢某個庫中的所有視圖:
show table status where comment='view';
# 查詢某個用戶的權(quán)限:
show grants for 'test_user'@'%';
# 這里先介紹下CONCAT函數(shù):在MySQL中 CONCAT()函數(shù)用于將多個字符串連接成一個字符串,
利用此函數(shù)我們可以將原來一步無法得到的sql拼接出來,后面部分語句有用到該函數(shù)。
# 當(dāng)拼接字符串中出現(xiàn)''時 需使用\轉(zhuǎn)義符
# 查看所有用戶名:
SELECT DISTINCT
CONCAT(
'User: \'',
user,
'\'@\'',
host,
'\';'
) AS QUERY
FROM
mysql.user;
# 查看用戶詳細(xì)信息:
SELECT user,
host,
authentication_string,
password_expired,
password_lifetime,
password_last_changed,
account_locked
FROM
mysql.user;
# 下面列舉SQL只是拼接出kill 鏈接的語句,若想執(zhí)行 直接將結(jié)果復(fù)制執(zhí)行即可。
# 殺掉空閑時間大于2000s的鏈接:
SELECT
concat( 'KILL ', id, ';' )
FROM
information_schema.`PROCESSLIST`
WHERE
Command = 'Sleep'
AND TIME > 2000;
# 殺掉處于某狀態(tài)的鏈接:
SELECT
concat( 'KILL ', id, ';' )
FROM
information_schema.`PROCESSLIST`
WHERE
STATE LIKE 'Creating sort index';
# 殺掉某個用戶的鏈接:
SELECT
concat( 'KILL ', id, ';' )
FROM
information_schema.`PROCESSLIST`
WHERE
where user='root';
# 拼接創(chuàng)建數(shù)據(jù)庫語句(排除系統(tǒng)庫):
SELECT
CONCAT(
'create database ',
'`',
SCHEMA_NAME,
'`',
' DEFAULT CHARACTER SET ',
DEFAULT_CHARACTER_SET_NAME,
';'
) AS CreateDatabaseQuery
FROM
information_schema.SCHEMATA
WHERE
SCHEMA_NAME NOT IN (
'information_schema',
'performance_schema',
'mysql',
'sys'
);
# 拼接創(chuàng)建用戶語句(排除系統(tǒng)用戶):
SELECT
CONCAT(
'create user \'',
user,
'\'@\'',
Host,
'\''
' IDENTIFIED BY PASSWORD \'',
authentication_string,
'\';'
) AS CreateUserQuery
FROM
mysql.`user`
WHERE
`User` NOT IN (
'root',
'mysql.session',
'mysql.sys'
);
# 有密碼字符串哦 在其他實例執(zhí)行 可直接創(chuàng)建出與本實例相同密碼的用戶。
# 查看整個實例占用空間大?。?SELECT
concat( round( sum( data_length / 1024 / 1024 ), 2 ), 'MB' ) AS data_length_MB,
concat( round( sum( index_length / 1024 / 1024 ), 2 ), 'MB' ) AS index_length_MB
FROM
information_schema.`TABLES`;
# 查看各個庫占用大?。?SELECT
TABLE_SCHEMA,
concat( TRUNCATE ( sum( data_length )/ 1024 / 1024, 2 ), ' MB' ) AS data_size,
concat( TRUNCATE ( sum( index_length )/ 1024 / 1024, 2 ), 'MB' ) AS index_size
FROM
information_schema.`TABLES`
GROUP BY
TABLE_SCHEMA;
# 查看單個庫占用空間大?。?SELECT
concat( round( sum( data_length / 1024 / 1024 ), 2 ), 'MB' ) AS data_length_MB,
concat( round( sum( index_length / 1024 / 1024 ), 2 ), 'MB' ) AS index_length_MB
FROM
information_schema.`TABLES`
WHERE
table_schema = 'test_db';
# 查看單個表占用空間大小:
SELECT
concat( round( sum( data_length / 1024 / 1024 ), 2 ), 'MB' ) AS data_length_MB,
concat( round( sum( index_length / 1024 / 1024 ), 2 ), 'MB' ) AS index_length_MB
FROM
information_schema.`TABLES`
WHERE
table_schema = 'test_db'
AND table_name = 'tbname';
# 查看某個庫下所有表的碎片情況:
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_db'
ORDER BY
datafree DESC;
# 收縮表,減少碎片:
alter table tb_name engine = innodb;
optimize table tb_name;
# 查找某一個庫無主鍵表:
SELECT
table_schema,
table_name
FROM
information_schema.`TABLES`
WHERE
table_schema = 'test_db'
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_db'
);
# 查找除系統(tǒng)庫外 無主鍵表:
SELECT
t1.table_schema,
t1.table_name
FROM
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'
) ;
總結(jié):?
希望這些SQL語句能對你有所幫助,可以收藏一下,說不定某次就用到了呢!原創(chuàng)不易,感謝大家支持。
文章名稱:常用SQL語句分享
標(biāo)題路徑:http://bm7419.com/article38/jcippp.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián),為您提供關(guān)鍵詞優(yōu)化、靜態(tài)網(wǎng)站、網(wǎng)站導(dǎo)航、外貿(mào)網(wǎng)站建設(shè)、網(wǎng)站策劃、云服務(wù)器
聲明:本網(wǎng)站發(fā)布的內(nèi)容(圖片、視頻和文字)以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主,如果涉及侵權(quán)請盡快告知,我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場,如需處理請聯(lián)系客服。電話:028-86922220;郵箱:631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載,或轉(zhuǎn)載時需注明來源: 創(chuàng)新互聯(lián)