? ? 這里不會涉及一些mysql數據庫安裝之類的知識，但是會將自己學習以及使用mysql一些最常用的mysql語句總結到本篇文章，作為自己的學習記錄和筆記。基本的環境為CentOS 6.5 數據庫為mysql5.6.30。

1、啟動和停止Mysql服務

1、??/etc/init.d/mysql?restar???#重啟 2、??/etc/init.d/mysql?stop?????#停止 3、??/etc/init.d/mysql?start????#啟動 4、??/etc/init.d/mysql?reload???#平滑重啟 5、??service??mysql???reload????#平滑重啟 6、??service??mysql???stop??????#停止 7、??service??mysql???start?????#啟動

2、加入開機自啟動

chkconfig???mysql????on???#加入開機自啟動chkconfig?????--list???|grep??mysql????檢查設置的開機自啟動

3、連接數據庫

mysql???-h???-P???-u??root?????-p???-e參數：-h???要連接的主機???-P???數據庫的端口???默認是3306???沒更改的話可以不用寫-u????要連接數據庫的用戶名???-p????密碼???可以直接無空格的加在-p參數后面，但是這種方法，在別人查看你的歷史命令時，會獲得你的密碼不×××全，一般是連接的時候，回車輸入密碼。-e???你可以輸入mysql語句但是不會進入客戶端。

4、查看基礎信息

select??user(),version(),database(),now();???#??查看當前用戶、版本、 當前數據庫、當前時間等信息mysql>?select??user(),version(),database(),now(); +----------------+-----------+------------+---------------------+ |?user()?????????|?version()?|?database()?|?now()???????????????| +----------------+-----------+------------+---------------------+ |?root@localhost?|?5.6.30????|?NULL???????|?2016-06-16?10:08:01?| +----------------+-----------+------------+---------------------+ 1?row?in?set?(0.11?sec)

5、為root設置密碼與設置密碼

? ?mysql數據庫是默認給root沒有設置密碼的，本次實驗數據庫rpm包安裝的，有初始密碼，mariadb在初始化的時候提示我們輸入密碼。

cat??/root/.mysql_secret? #?The?random?password?set?for?the?root?user?at?Sun?Jun?12?22:02:31?2016?(local?time):nR7PKQyH5DU2zjKM???這一部分為初始密碼，mysqladmin??-u??root???password???'******'???#設置密碼?更改密碼select??host,user,password??from??mysql.user?; +-----------------------+------------+-------------------------------------------+ |?host??????????????????|?user???????|?password??????????????????????????????????| +-----------------------+------------+-------------------------------------------+ |?localhost?????????????|?root???????|?*E56A114692FE0DE073F9A1DD68A00EEB9703F3F1?| |?localhost.localdomain?|?root???????|?*47F6BC7F709C7CCFCB1EAF451FFE6D89F1377D84?| |?127.0.0.1?????????????|?root???????|?*47F6BC7F709C7CCFCB1EAF451FFE6D89F1377D84?| |?::1???????????????????|?root???????|?*23AE809DDACAF96AF0FD78ED04B6A265E05AA257?| |?192.168.1.%???????????|?tomcat?????|?*6FDD34EE54803B8CC847CC5D7158702BCC21FCF6?| |?%?????????????????????|?winnerlook?|?*23AE809DDACAF96AF0FD78ED04B6A265E05AA257?| +-----------------------+------------+-------------------------------------------+（1）mysqladmin?-u用戶名?-p舊密碼?password?"******"例如：?mysqladmin???-u??winner??password?"123"?-p???[root@localhost?~]#?mysqladmin???-u??winner???password?"123"?-p??? Enter?password:? Warning:?Using?a?password?on?the?command?line?interface?can?be?insecure.?（2）登陸到數據庫后用set?password命令 格式：SET??password????for???user@host?=password("")；注意加密函數 例如：set???password?for???root@'::1'?=password("123");?????????? Query?OK,?0?rows?affected?(0.05?sec)mysql>?flush???privileges; Query?OK,?0?rows?affected?(0.00?sec)mysql>??set???password?for???tomcat@'192.168.1.%'?=password("123123");????? Query?OK,?0?rows?affected?(0.00?sec)mysql>?flush???privileges; Query?OK,?0?rows?affected?(0.00?sec) （3）登陸后用update直接操作user表 注意：這里要使用加密函數以及限制條件，不注意限制條件有可能會更改所有的用戶密碼。如下面的內容 直接更改所有的內容以及明文密碼。update???user??set??password=("123123"); Query?OK,?6?rows?affected?(0.03?sec)， Rows?matched:?6??Changed:?6??Warnings:?0mysql>?select??host,user,password??from??mysql.user?; +-----------------------+------------+----------+ |?host??????????????????|?user???????|?password?| +-----------------------+------------+----------+ |?localhost?????????????|?root???????|?123123???| |?localhost.localdomain?|?root???????|?123123???| |?127.0.0.1?????????????|?root???????|?123123???| |?::1???????????????????|?root???????|?123123???| |?192.168.1.%???????????|?tomcat?????|?123123???| |?%?????????????????????|?winnerlook?|?123123???| +-----------------------+------------+----------+正確更改的方式：update???user??set??password=password("123123");? Query?OK,?6?rows?affected?(0.02?sec) Rows?matched:?6??Changed:?6??Warnings:?0mysql>??select??host,user,password??from??mysql.user?; +-----------------------+------------+-------------------------------------------+ |?host??????????????????|?user???????|?password??????????????????????????????????| +-----------------------+------------+-------------------------------------------+ |?localhost?????????????|?root???????|?*E56A114692FE0DE073F9A1DD68A00EEB9703F3F1?| |?localhost.localdomain?|?root???????|?*E56A114692FE0DE073F9A1DD68A00EEB9703F3F1?| |?127.0.0.1?????????????|?root???????|?*E56A114692FE0DE073F9A1DD68A00EEB9703F3F1?| |?::1???????????????????|?root???????|?*E56A114692FE0DE073F9A1DD68A00EEB9703F3F1?| |?192.168.1.%???????????|?tomcat?????|?*E56A114692FE0DE073F9A1DD68A00EEB9703F3F1?| |?%?????????????????????|?winnerlook?|?*E56A114692FE0DE073F9A1DD68A00EEB9703F3F1?| +-----------------------+------------+-------------------------------------------+ 6?rows?in?set?(0.00?sec)使用where字句?添加限制條件 mysql>??update???user??set??password=password("123")?where?user='tomcat';? Query?OK,?1?row?affected?(0.05?sec) Rows?matched:?1??Changed:?1??Warnings:?0mysql>?select??host,user,password??from??mysql.user?; +-----------------------+------------+-------------------------------------------+ |?host??????????????????|?user???????|?password??????????????????????????????????| +-----------------------+------------+-------------------------------------------+ |?localhost?????????????|?root???????|?*E56A114692FE0DE073F9A1DD68A00EEB9703F3F1?| |?localhost.localdomain?|?root???????|?*E56A114692FE0DE073F9A1DD68A00EEB9703F3F1?| |?127.0.0.1?????????????|?root???????|?*E56A114692FE0DE073F9A1DD68A00EEB9703F3F1?| |?::1???????????????????|?root???????|?*E56A114692FE0DE073F9A1DD68A00EEB9703F3F1?| |?192.168.1.%???????????|?tomcat?????|?*23AE809DDACAF96AF0FD78ED04B6A265E05AA257?| |?%?????????????????????|?winnerlook?|?*E56A114692FE0DE073F9A1DD68A00EEB9703F3F1?| +-----------------------+------------+-------------------------------------------+ 6?rows?in?set?(0.00?sec)

6、 刷新權限 ?

?mysql>?flush???privileges; Query?OK,?0?rows?affected?(0.14?sec)

7、mysql 客戶端技巧

echo?"select?*??from?tb_emp8;"??|mysql?-u??root?-p?test_db?>>test.txt [root@localhost?~]#?echo?"select?*??from?tb_emp8;"??|mysql?-u??root?-p?test_db?>test.txt Enter?password:? [root@localhost?~]#?cat??test.txt? id??????names???deptId??salary 1???????Lucy????NULL????1000 2???????Lura????NULL????1200 3???????Kevin???NULL????1500 4???????Lucy????NULL????1000 5???????Lura????NULL????1200 6???????Kevin???NULL????1500 7???????Lucy????NULL????1000 8???????Lura????NULL????1200 9???????Kevin???NULL????1500 10??????Lucy????NULL????1000 11??????Lura????NULL????1200 12??????Kevin???NULL????1500 13??????Lucy????NULL????1000 14??????Lura????NULL????1200方法2mysql?-u??root?-p??-e?"select?*??from?test_db.tb_emp8;">test2.txt Enter?password:? [root@localhost?~]#?cat??test2.txt? id??????names???deptId??salary 1???????Lucy????NULL????1000 2???????Lura????NULL????1200 3???????Kevin???NULL????1500 4???????Lucy????NULL????1000 5???????Lura????NULL????1200

執行sql文件的方法

（1）mysql??-u??root??-p???test_db???<?/root/test.sql （2）cat??/root/test.sql??|mysql?-u??root?-p?test_db （3）登錄數據庫后source???加載

8、創建交互文件日志 可以用來評估和考量操作過程中出現哪些操作，可以用tee

?mysql??--tee=test.log??-u??root?-p???#?創建一個test.log日志文件 Logging?to?file?'test.log' Enter?password:? Welcome?to?the?MySQL?monitor.??Commands?end?with?;?or?\g. Your?MySQL?connection?id?is?34 Server?version:?5.6.30?MySQL?Community?Server?(GPL)Copyright?(c)?2000,?2016,?Oracle?and/or?its?affiliates.?All?rights?reserved.Oracle?is?a?registered?trademark?of?Oracle?Corporation?and/or?its affiliates.?Other?names?may?be?trademarks?of?their?respective owners.Type?'help;'?or?'\h'?for?help.?Type?'\c'?to?clear?the?current?input?statement.mysql>?\T?test.log?????????????????????#開始記錄日志文件 Logging?to?file?'test.log' mysql>?select?user(),version(),now(); +----------------+-----------+---------------------+ |?user()?????????|?version()?|?now()???????????????| +----------------+-----------+---------------------+ |?root@localhost?|?5.6.30????|?2016-10-07?17:14:25?| +----------------+-----------+---------------------+ 1?row?in?set?(0.11?sec)mysql>?show??databases; +--------------------+ |?Database???????????| +--------------------+ |?information_schema?| |?booksdb????????????| |?company????????????| |?mysql??????????????| |?performance_schema?| |?sakila?????????????| |?team???????????????| |?test???????????????| |?test_db????????????| |?winner?????????????| |?world??????????????| +--------------------+ 11?rows?in?set?(0.00?sec)mysql>?\t??#結束記錄 檢查日志文件： [root@localhost?~]#?cat??test.log? Welcome?to?the?MySQL?monitor.??Commands?end?with?;?or?\g. Your?MySQL?connection?id?is?34 Server?version:?5.6.30?MySQL?Community?Server?(GPL)Copyright?(c)?2000,?2016,?Oracle?and/or?its?affiliates.?All?rights?reserved.Oracle?is?a?registered?trademark?of?Oracle?Corporation?and/or?its affiliates.?Other?names?may?be?trademarks?of?their?respective owners.Type?'help;'?or?'\h'?for?help.?Type?'\c'?to?clear?the?current?input?statement.mysql>?\T?test.log mysql>?select?user(),version(),now(); +----------------+-----------+---------------------+ |?user()?????????|?version()?|?now()???????????????| +----------------+-----------+---------------------+ |?root@localhost?|?5.6.30????|?2016-10-07?17:14:25?| +----------------+-----------+---------------------+ 1?row?in?set?(0.11?sec)mysql>?show??databases; +--------------------+ |?Database???????????| +--------------------+ |?information_schema?| |?booksdb????????????| |?company????????????| |?mysql??????????????| |?performance_schema?| |?sakila?????????????| |?team???????????????| |?test???????????????| |?test_db????????????| |?winner?????????????| |?world??????????????| +--------------------+ 11?rows?in?set?(0.00?sec)mysql>?use??world; Reading?table?information?for?completion?of?table?and?column?names You?can?turn?off?this?feature?to?get?a?quicker?startup?with?-ADatabase?changed mysql>?show??tables; +-----------------+ |?Tables_in_world?| +-----------------+ |?city????????????| |?country?????????| |?countrylanguage?| +-----------------+ 3?rows?in?set?(0.00?sec)mysql>?desc??city; +-------------+----------+------+-----+---------+----------------+ |?Field???????|?Type?????|?Null?|?Key?|?Default?|?Extra??????????| +-------------+----------+------+-----+---------+----------------+ |?ID??????????|?int(11)??|?NO???|?PRI?|?NULL????|?auto_increment?| |?Name????????|?char(35)?|?NO???|?????|?????????|????????????????| |?CountryCode?|?char(3)??|?NO???|?????|?????????|????????????????| |?District????|?char(20)?|?NO???|?????|?????????|????????????????| |?Population??|?int(11)??|?NO???|?????|?0???????|????????????????| +-------------+----------+------+-----+---------+----------------+ 5?rows?in?set?(0.51?sec)mysql>?select??count(*)??from???city; +----------+ |?count(*)?| +----------+ |?????4079?| +----------+ 1?row?in?set?(0.00?sec)

轉載于:https://blog.51cto.com/dreamlinux/1837470

總結

以上是生活随笔為你收集整理的mysql基础命令学习笔记的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。