操作MySQL常用及基礎知識

本文主要給大家簡單講講操作MySQL常用及基礎知識，相關專業術語大家可以上網查查或者找一些相關書籍補充一下，這里就不涉獵了，直奔主題，希望可以給大家帶來一些實際幫助。                                                           

1、啟動和關閉mysql服務器：

service mysql start
service mysql stop

2、重啟MySQL服務：

service mysql restart

3、確認是否啟動成功，mysql節點處于LISTEN狀態表示啟動成功：

sudo netstat -tap | grep mysql

4、進入mysql shell界面：

mysql -u root -p

5、連接MYSQL：

格式： mysql -h主機地址 -u用戶名 －p用戶密碼 （注：u與root可以不用加空格，其它也一樣）

1、連接到本機上的MYSQL

找到mysql的安裝目錄，一般可以直接鍵入命令mysql -uroot -p，回車后提示你輸密碼，如果剛安裝好MYSQL，超級用戶root是沒有密碼的，故直接回車即可進入到MYSQL中了.

2、連接到遠程主機上的MYSQL

假設遠程主機的IP為：10.0.0.1，用戶名為root,密碼為123。則鍵入以下命令：

mysql -h20.0.0.1 -uroot -p123

6、退出MYSQL命令 ：

exit （回車）

7、查詢mysql正在執行的進程：

show processlist;

8、查看用戶：

use mysql；
select * from user;

9、新建用戶：

CREATE USER  'user_name'@'host'  IDENTIFIED BY  'password'；

user_name：要創建用戶的名字。

host：表示要這個新創建的用戶允許從哪臺機登陸，如果只允許從本機登陸，則填　‘localhost’ ，如果允許從遠程登陸，則填 ‘%’

password：新創建用戶的登陸數據庫密碼，如果沒密碼可以不寫。

例：

CREATE USER  ‘aaa’@‘localhost’ IDENTIFED BY ‘123456’； //表示創建的新用戶，名為aaa，這個新用戶密碼為123456，
只允許本機登陸
CREATE USER  'bbb'@'%' IDENTIFED BY '123456'；//表示新創建的用戶，名為bbb，這個用戶密碼為123456，
可以從其他電腦遠程登陸mysql所在服務器
CREATE USER  ‘ccc’@‘%’ ；//表示新創建的用戶ccc，沒有密碼，可以從其他電腦遠程登陸mysql服務器

10、授權用戶：

GRANT privileges ON  databasename.tablename  TO  ‘username’@‘host’

privileges：表示要授予什么權力，例如可以有 select ， insert ，delete，update等，如果要授予全部權力，則填 ALL

databasename.tablename：表示用戶的權限能用在哪個庫的哪個表中，如果想要用戶的權限很作用于所有的數據庫所有的表，則填 * . *，*是一個通配符，表示全部。

’username‘@‘host’：表示授權給哪個用戶。

例如：

grant all on *.* to 'test'@'localhost'; #給test用戶授權，讓test用戶能給所有庫所有表實行所有的權力
GRANT  select，insert  ON  zje.zje  TO ‘aaa’@‘%’；//表示給用戶aaa授權，讓aaa能給zje庫中的zje表 實行 insert 
和 select。

注意：

用以上命令授權的用戶不能給其他用戶授權，如果想這個用戶能夠給其他用戶授權，就要在后面加上 WITH GRANT OPTION

如： GRANT ALL ON *.* TO ’aaa‘@'%' WITH GRANT OPTION；

相關

11、限制ip登錄：

例如，設置mysql只有172.29.8.72和192.168.3.39可以連接上

GRANT ALL ON *.* TO 'username'@'172.29.8.72' IDENTIFIED BY 'password' WITH GRANT OPTION;
GRANT ALL  ON *.* TO 'username'@'192.168.3.39' IDENTIFIED BY 'password' WITH GRANT OPTION;
flush privileges;

12、刪除用戶：

命令：DROP USER ‘user_name’@‘host’

例：

drop user 'test'@'%';  #刪除用戶test

13、顯示數據表模式：

use 數據庫名；

show columns from 表名;

14、mysql數據類型：

MySQL支持多種類型，大致可以分為三類：數值、日期/時間和字符串(字符)類型。

MySQL支持所有標準SQL數值數據類型。作為SQL標準的擴展，MySQL也支持整數類型TINYINT、MEDIUMINT和BIGINT

15、插入數據：

INSERT INTO table_name ( field1, field2,...fieldN )
                       VALUES
                       ( value1, value2,...valueN );

如果數據是字符型，必須使用單引號或者雙引號，如：“value”。

16、查詢數據：

   SELECT column_name,column_name
    FROM table_name
    [WHERE Clause]
    [LIMIT N][ OFFSET M]

查詢語句中你可以使用一個或者多個表，表之間使用逗號(,)分割，并使用WHERE語句來設定查詢條件。

SELECT 命令可以讀取一條或者多條記錄。

你可以使用星號（*）來代替其他字段，SELECT語句會返回表的所有字段數據

你可以使用 WHERE 語句來包含任何條件。

你可以使用 LIMIT 屬性來設定返回的記錄數。

你可以通過OFFSET指定SELECT語句開始查詢的數據偏移量。默認情況下偏移量為0。

17、limit和offset用法

mysql里分頁一般用limit來實現

1、select* from article LIMIT 1,3

2、select * from article LIMIT 3 OFFSET 1

上面兩種寫法都表示取2,3,4三條條數據

當limit后面跟兩個參數的時候，第一個數表示要跳過的數量，后一位表示要取的數量,例如

select* from article LIMIT 1,3 就是跳過1條數據,從第2條數據開始取，取3條數據，也就是取2,3,4三條數據

當 limit后面跟一個參數的時候，該參數表示要取的數據的數量

例如 select* from article LIMIT 3 表示直接取前三條數據，類似sqlserver里的top語法。

當 limit和offset組合使用的時候，limit后面只能有一個參數，表示要取的的數量,offset表示要跳過的數量 。

例如select * from article LIMIT 3 OFFSET 1 表示跳過1條數據,從第2條數據開始取，取3條數據，也就是取2,3,4三條數據

18、更新數據：

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]

你可以同時更新一個或多個字段。

你可以在 WHERE 子句中指定任何條件。

你可以在一個單獨表中同時更新數據。

19、模糊查詢：

select c field1,field2,…fieldN
from table_name
where field1 like condition1 [and[or]] field2=‘somevalue’;

通配符的分類:

%百分號通配符: 表示任何字符出現任意次數 (可以是0次).

_下劃線通配符:表示只能匹配單個字符,不能多也不能少,就是一個字符。

20、排序

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
ORDER BY field1 [ASC [DESC][默認 ASC]], [field2...] [ASC [DESC][默認 ASC]]

可以添加 WHERE…LIKE 子句來設置條件

21、分組

GROUP BY 語句根據一個或多個列對結果集進行分組。在分組的列上我們可以使用 COUNT, SUM, AVG等函數。

SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

例子：

SELECT name, COUNT(*) FROM   employee_tbl GROUP BY name;

22、null值處理

MySQL 中處理 NULL 使用 IS NULL 、 IS NOT NULL 、<=>運算符。

IS NULL: 當列的值是 NULL,此運算符返回 true。
IS NOT NULL: 當列的值不為 NULL, 運算符返回 true。
<=>: 比較操作符（不同于=運算符），當比較的的兩個值為 NULL 時返回 true。

23、in

in常用于where表達式中，其作用是查詢某個范圍內的數據。

用法：select * from table where field in (value1,value2,value3,…);

例子：

查詢book表中id為2和4的所有數據：

select * from book where id in（2，4）

24、not in

not in與in作用相反，用法和示例如下：

用法：select * from where field not in (value1,value2,value3,…);

25、exists

本示例所示查詢查找由位于以字母 B 開頭的城市中的任一出版商出版的書名：

SELECT title
FROM titles
WHERE EXISTS
    (SELECT *
    FROM publishers
    WHERE pub_id = titles.pub_id
    AND city LIKE 'B%')

用IN：

SELECT title
FROM titles
WHERE pub_id IN
    (SELECT pub_id
    FROM publishers
    WHERE city LIKE 'B%')

26、導入數據：

mysql 命令導入

使用 mysql 命令導入語法格式為：

mysql -u用戶名    -p密碼    <  要導入的數據庫數據(runoob.sql)

實例：

# mysql -uroot -p123456 < runoob.sql

以上命令將將備份的整個數據庫 runoob.sql 導入。

source 命令導入

source 命令導入數據庫需要先登錄到數庫終端：

mysql> create database abc;      # 創建數據庫
mysql> use abc;                  # 使用已創建的數據庫 
mysql> set names utf8;           # 設置編碼
mysql> source /home/abc/abc.sql  # 導入備份數據庫

使用 LOAD DATA 導入數據

MySQL 中提供了LOAD DATA INFILE語句來插入數據。 以下實例中將從當前目錄中讀取文件 dump.txt ，將該文件中的數據插入到當前數據庫的 mytbl 表中。

mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl;

如果指定LOCAL關鍵詞，則表明從客戶主機上按路徑讀取文件。如果沒有指定，則文件在服務器上按路徑讀取文件。

你能明確地在LOAD DATA語句中指出列值的分隔符和行尾標記，但是默認標記是定位符和換行符。

使用 mysqlimport 導入數據

mysqlimport 客戶端提供了 LOAD DATA INFILEQL 語句的一個命令行接口。mysqlimport 的大多數選項直接對應 LOAD DATA INFILE 子句。

從文件 dump.txt 中將數據導入到 mytbl 數據表中, 可以使用以下命令：

$ mysqlimport -u root -p --local mytbl dump.txt
password *****

27、導出數據：

使用SELECT…INTO OUTFILE語句來簡單的導出數據到文本文件上。

#將數據表 runoob_tbl 數據導出到 /tmp/runoob.txt 文件中: 
mysql> SELECT * FROM runoob_tbl    
    -> INTO OUTFILE '/tmp/runoob.txt';
    
#通過命令選項來設置數據輸出的指定格式，以下實例為導出 CSV 格式：
    mysql> SELECT * FROM passwd INTO OUTFILE '/tmp/runoob.txt'
    -> FIELDS TERMINATED BY ',' ENCLOSED BY '"'
    -> LINES TERMINATED BY '\r\n';
#生成一個文件，各值用逗號隔開。這種格式可以被許多程序使用。
    SELECT a,b,a+b INTO OUTFILE '/tmp/result.text'
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    LINES TERMINATED BY '\n'
    FROM test_table;

SELECT … INTO OUTFILE 語句有以下屬性:

LOAD DATA INFILE是SELECT … INTO

OUTFILE的逆操作，SELECT句法。為了將一個數據庫的數據寫入一個文件，使用SELECT … INTO

OUTFILE，為了將文件讀回數據庫，使用LOAD DATA INFILE。

SELECT…INTO OUTFILE

'file_name’形式的SELECT可以把被選擇的行寫入一個文件中。該文件被創建到服務器主機上，因此您必須擁有FILE權限，才能使用此語法。

輸出不能是一個已存在的文件。防止文件數據被篡改。

你需要有一個登陸服務器的賬號來檢索文件。否則 SELECT … INTO OUTFILE 不會起任何作用。

導出表作為原始數據

mysqldump 是 mysql 用于轉存儲數據庫的實用程序。它主要產生一個 SQL 腳本，其中包含從頭重新創建數據庫所必需的命令 CREATE TABLE INSERT 等。

使用 mysqldump 導出數據需要使用 --tab 選項來指定導出文件指定的目錄，該目標必須是可寫的。

以下實例將數據表 runoob_tbl 導出到 /tmp 目錄中：

$ mysqldump -u root -p --no-create-info 
–tab=/tmp RUNOOB runoob_tbl
password ******

導出 SQL 格式的數據

導出 SQL 格式的數據到指定文件，如下所示：

$ mysqldump -u root -p RUNOOB runoob_tbl > dump.txt
password ******

如果你需要將數據拷貝至其他的 MySQL 服務器上, 你可以在 mysqldump 命令中指定數據庫名及數據表。

在源主機上執行以下命令，將數據備份至 dump.txt 文件中:

$ mysqldump -u root -p database_name table_name > dump.txt
password *****

如果完整備份數據庫，則無需使用特定的表名稱。

如果你需要將備份的數據庫導入到MySQL服務器中，可以使用以下命令，使用以下命令你需要確認數據庫已經創建：

$ mysql -u root -p database_name < dump.txt
password *****

你也可以使用以下命令將導出的數據直接導入到遠程的服務器上，但請確保兩臺服務器是相通的，是可以相互訪問的：

$ mysqldump -u root -p database_name 
| mysql -h other-host.com database_name

以上命令中使用了管道來將導出的數據導入到指定的遠程主機上。

28、 MySQL 事務

MySQL 事務主要用于處理操作量大，復雜度高的數據。比如說，在人員管理系統中，你刪除一個人員，你即需要刪除人員的基本資料，也要刪除和該人員相關的信息，如信箱，文章等等，這樣，這些數據庫操作語句就構成一個事務！

在 MySQL 中只有使用了 Innodb 數據庫引擎的數據庫或表才支持事務。
事務處理可以用來維護數據庫的完整性，保證成批的 SQL 語句要么全部執行，要么全部不執行。
事務用來管理 insert,update,delete 語句

一般來說，事務是必須滿足4個條件（ACID）：：原子性（Atomicity，或稱不可分割性）、一致性（Consistency）、隔離性（Isolation，又稱獨立性）、持久性（Durability）。

MYSQL 事務處理主要有兩種方法：

1、用 BEGIN, ROLLBACK, COMMIT來實現

BEGIN 開始一個事務
ROLLBACK 事務回滾
COMMIT 事務確認

2、直接用 SET 來改變 MySQL 的自動提交模式:

SET AUTOCOMMIT=0 禁止自動提交
SET AUTOCOMMIT=1 開啟自動提交

29、字符集設置：

Ubuntu下設置MySQL字符集為utf8

1.mysql配置文件地址

/etc/mysql/my.cnf

2.在[mysqld]在下方添加以下代碼

[mysqld]
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake

3.重啟mysql服務

sudo service mysql restart

4.檢測字符集是否更新成utf8.

進入mysql，mysql -u root -p,輸入show variables like '%character%' 查看字符集

+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

注意事項：在修改字符集之前已經建立的數據庫，character_set_database值不會發生改變，往數據庫中插入中文數據仍然會顯示亂碼，所以最好在安裝完MySQL后就將字符集改成utf8，否則后續修改會較麻煩。

字符集修改：

show character set; #查看當前MySQL服務實例支持的字符集、字符序以及字符集占用的最大字節長度等信息

如下圖：

show variables like 'character%';#查看當前MySQL會話使用的字符集

結果如圖：

character_set_client: 客戶端來源數據使用的字符集

character_set_connection: 數據通信鏈路的字符集，當MySQL客戶機向服務器發送請求時，數據以該字符集進行編碼

character_set_database: 數據庫字符集

character_set_filesystem: MySQL服務器文件系統的字符集，該值是固定的binary。

character_set_results: 結果集的字符集，MySQL服務器向MySQL客戶機返回執行結果時，執行結果以該字符集進行編碼

character_set_server: 內部操作字符集(MySQL服務實例字符集) character_set_system: 元數據(字段名、表名、數據庫名等)的字符集默認為utf8

修改字符集：

set character_set_results=gbk;

如圖：

30、時間同步：

tzselect設置時區命令，根據選項選到中國的北京時間即可。

執行如下命令：

vi .bash_profile

在末尾加入這兩行：

TZ='Asia/Shanghai'
export TZ

然后運行命令刷新配置：

source .bash_profile

再次運行date命令發現時區已經成功修改：

hadoop@Master:~$ date -R
Tue, 30 Jul 2019 19:42:41 +0800

linux時區重新設置后，發現mysql插入的數據還是原來時區的時間，下面是重新設置mysql時區的方法：

進入mysql控制臺運行如下指令查看mysql時間。

select CURTIME();

查詢后發現和date命令查詢出來的時間不一樣，下面開始修改：

運行臨時解決命令：

SET time_zone = '+8:00';

刷新配置：

flush privileges;

修改配置文件使得下次重啟mysql服務之后永久生效

vi /etc/my.cnf

添加如下配置：

[mysqld]
default_time_zone = '+8:00'

31、grep命令：

1.作用

Linux系統中grep命令是一種強大的文本搜索工具，它能使用正則表達式搜索文本，并把匹 配的行打印出來。grep全稱是Global Regular Expression Print，表示全局正則表達式版本，它的使用權限是所有用戶。

2.格式

grep [options]

簡單實例：

$ grep ‘test’ d* #顯示所有以d開頭的文件中包含 test的行。
$ grep ‘test’ aa bb cc  #顯示在aa，bb，cc文件中匹配test的行。

32、awk命令：

awk是一個強大的文本分析工具，相對于grep的查找，sed的編輯，awk在其對數據分析并生成報告時，顯得尤為強大。簡單來說awk就是把文件逐行的讀入，以空格為默認分隔符將每行切片，切開的部分再進行各種分析處理。

使用方法 ： awk '{pattern + action}' {filenames}

操作MySQL常用及基礎知識就先給大家講到這里，對于其它相關問題大家想要了解的可以持續關注我們的行業資訊。我們的板塊內容每天都會捕捉一些行業新聞及專業知識分享給大家的。