溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
這里不會涉及一些mysql數(shù)據(jù)庫安裝之類的知識，但是會將自己學(xué)習(xí)以及使用mysql一些最常用的mysql語句總結(jié)到本篇文章，作為自己的學(xué)習(xí)記錄和筆記。基本的環(huán)境為CentOS 6.5 數(shù)據(jù)庫為mysql5.6.30。
1、啟動和停止Mysql服務(wù)
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 檢查設(shè)置的開機自啟動
3、連接數(shù)據(jù)庫
mysql -h -P -u root -p -e 參數(shù)： -h 要連接的主機 -P 數(shù)據(jù)庫的端口 默認是3306 沒更改的話可以不用寫 -u 要連接數(shù)據(jù)庫的用戶名 -p 密碼 可以直接無空格的加在-p參數(shù)后面，但是這種方法，在別人查看你的歷史命令時 ，會獲得你的密碼不×××全，一般是連接的時候，回車輸入密碼。 -e 你可以輸入mysql語句但是不會進入客戶端。
4、查看基礎(chǔ)信息
select user(),version(),database(),now(); # 查看當(dāng)前用戶、版本、 當(dāng)前數(shù)據(jù)庫、當(dāng)前時間等信息 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設(shè)置密碼與設(shè)置密碼
mysql數(shù)據(jù)庫是默認給root沒有設(shè)置密碼的，本次實驗數(shù)據(jù)庫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 '******' #設(shè)置密碼
更改密碼
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）登陸到數(shù)據(jù)庫后用set password命令
格式：SET password for user@host =password("")；注意加密函數(shù)
例如：
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表
注意：這里要使用加密函數(shù)以及限制條件，不注意限制條件有可能會更改所有的用戶密碼。如下面的內(nèi)容
直接更改所有的內(nèi)容以及明文密碼。
update user set password=("123123");
Query OK, 6 rows affected (0.03 sec)，
Rows matched: 6 Changed: 6 Warnings: 0
mysql> 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: 0
mysql> 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: 0
mysql> 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、 刷新權(quán)限
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 方法2 mysql -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
執(zhí)行sql文件的方法
（1）mysql -u root -p test_db < /root/test.sql （2）cat /root/test.sql |mysql -u root -p test_db （3）登錄數(shù)據(jù)庫后source 加載
8、創(chuàng)建交互文件日志 可以用來評估和考量操作過程中出現(xiàn)哪些操作，可以用tee
mysql --tee=test.log -u root -p # 創(chuàng)建一個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 #結(jié)束記錄 檢查日志文件： [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 -A Database 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)
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進行舉報，并提供相關(guān)證據(jù)，一經(jīng)查實，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號
手機網(wǎng)站二維碼