.jpg)
mysql系統(tǒng)信息函數(shù)有：1、使用【SHOW PROCESSLIST】命令輸出當(dāng)前用戶的連接信息；2、 使用【CHARSET()】函數(shù)返回字符串使用的字符集；3、使用【COLLATION()】函數(shù)返回字符串排列方式。
mysql系統(tǒng)信息函數(shù)有：
一、查看當(dāng)前MySQL版本號
mysql>?select?VERSION(); +-----------+ |?VERSION()?| +-----------+ |?5.7.22????| +-----------+ 1?row?in?set?(0.00?sec)
相關(guān)學(xué)習(xí)推薦：mysql視頻教程
二、查看當(dāng)前用戶的連接數(shù)
mysql>?SELECT?CONNECTION_ID(); +-----------------+ |?CONNECTION_ID()?| +-----------------+ |???????????????2?| +-----------------+ 1?row?in?set?(0.00?sec)
三、使用SHOW PROCESSLIST命令輸出當(dāng)前用戶的連接信息
mysql>?SHOW?PROCESSLIST; +----+------+------+------+---------+------+----------+------------------+ |?Id?|?User?|?Host?|?db???|?Command?|?Time?|?State????|?Info?????????????| +----+------+------+------+---------+------+----------+------------------+ |??2?|?root?|??????|?test?|?Query???|????0?|?starting?|?SHOW?PROCESSLIST?| +----+------+------+------+---------+------+----------+------------------+ 1?row?in?set?(0.00?sec)
四、查看當(dāng)前使用的數(shù)據(jù)庫
mysql>?SELECT?DATABASE(),SCHEMA(); +------------+----------+ |?DATABASE()?|?SCHEMA()?| +------------+----------+ |?test???????|?test?????| +------------+----------+ 1?row?in?set?(0.00?sec)
五、獲取當(dāng)前登錄用戶名稱
mysql>?SELECT?USER(),?CURRENT_USER(),?SYSTEM_USER(); +--------+-----------------------------------+---------------+ |?USER()?|?CURRENT_USER()????????????????????|?SYSTEM_USER()?| +--------+-----------------------------------+---------------+ |?root@??|?skip-grants?user@skip-grants?host?|?root@?????????| +--------+-----------------------------------+---------------+ 1?row?in?set?(0.00?sec)
六、 使用CHARSET()函數(shù)返回字符串使用的字符集
SELECT?CHARSET('abc'), ?????CHARSET(CONVERT('abc'?USING?latin1)), ?????CHARSET(VERSION()); +----------------+--------------------------------------+--------------------+ |?CHARSET('abc')?|?CHARSET(CONVERT('abc'?USING?latin1))?|?CHARSET(VERSION())?| +----------------+--------------------------------------+--------------------+ |?utf8???????????|?latin1???????????????????????????????|?utf8???????????????| +----------------+--------------------------------------+--------------------+ 1?row?in?set?(0.00?sec)
七、使用COLLATION()函數(shù)返回字符串排列方式
mysql>?SELECT?COLLATION('abc'),COLLATION(CONVERT('abc'?USING?utf8)); +------------------+--------------------------------------+ |?COLLATION('abc')?|?COLLATION(CONVERT('abc'?USING?utf8))?| +------------------+--------------------------------------+ |?utf8_general_ci??|?utf8_general_ci??????????????????????| +------------------+--------------------------------------+ 1?row?in?set?(0.00?sec)
八、使用SELECT LAST_INSERT_ID查看最后一個(gè)自動(dòng)生成的列值
1、一次插入一條記錄
（1）、首先創(chuàng)建表worker，其Id字段帶有AUTO_INCREMENT約束
CREATE?TABLE?worker?(Id?INT?AUTO_INCREMENT?NOT?NULL?PRIMARY?KEY, ??????Name?VARCHAR(30)); Query?OK,?0?rows?affected?(0.23?sec)
（2）、分別單獨(dú)向表worker中插入2條記錄：
mysql>?INSERT?INTO?worker?VALUES(NULL,?'jimy'); Query?OK,?1?row?affected?(0.03?sec) mysql>?INSERT?INTO?worker?VALUES(NULL,?'Tom'); Query?OK,?1?row?affected?(0.02?sec) mysql>?SELECT?*?FROM?worker; +----+------+ |?Id?|?Name?| +----+------+ |??1?|?jimy?| |??2?|?Tom??| +----+------+ 2?rows?in?set?(0.00?sec)
（3）、查看已經(jīng)插入的數(shù)據(jù)可以發(fā)現(xiàn)，最后一條插入的記錄的Id字段值為2，使用LAST_INSERT_ID()查看最后自動(dòng)生成的Id值：
mysql>?SELECT?LAST_INSERT_ID(); +------------------+ |?LAST_INSERT_ID()?| +------------------+ |????????????????2?| +------------------+ 1?row?in?set?(0.01?sec)
2、一次同時(shí)插入多條記錄
（1）、接下來，向表中插入多條記錄
INSERT?INTO?worker?VALUES ?????(NULL,?'Kevin'),(NULL,'Michal'),(NULL,'Nick'); Query?OK,?3?rows?affected?(0.03?sec) Records:?3??Duplicates:?0??Warnings:?0
（2）、查詢已經(jīng)插入的的記錄，
mysql>?SELECT?*?FROM?worker; +----+--------+ |?Id?|?Name???| +----+--------+ |??1?|?jimy???| |??2?|?Tom????| |??3?|?Kevin??| |??4?|?Michal?| |??5?|?Nick???| +----+--------+ 5?rows?in?set?(0.00?sec) mysql>?SELECT?LAST_INSERT_ID(); +------------------+ |?LAST_INSERT_ID()?| +------------------+ |????????????????3?| +------------------+ 1?row?in?set?(0.00?sec)
? 版權(quán)聲明
文章版權(quán)歸作者所有，未經(jīng)允許請勿轉(zhuǎn)載。
THE END
喜歡就支持一下吧
相關(guān)推薦
.png)
