操作MySQL常用及基礎(chǔ)知識

本文主要給大家簡單講講操作MySQL常用及基礎(chǔ)知識，相關(guān)專業(yè)術(shù)語大家可以上網(wǎng)查查或者找一些相關(guān)書籍補充一下，這里就不涉獵了，直奔主題，希望可以給大家?guī)硪恍嶋H幫助。                                                           

1、啟動和關(guān)閉mysql服務(wù)器：

service mysql start
service mysql stop

2、重啟MySQL服務(wù)：

service mysql restart

3、確認(rèn)是否啟動成功，mysql節(jié)點處于LISTEN狀態(tài)表示啟動成功：

sudo netstat -tap | grep mysql

4、進(jìn)入mysql shell界面：

mysql -u root -p

5、連接MYSQL：

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

1、連接到本機上的MYSQL

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

2、連接到遠(yuǎn)程主機上的MYSQL

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

mysql -h20.0.0.1 -uroot -p123

6、退出MYSQL命令 ：

exit （回車）

7、查詢mysql正在執(zhí)行的進(jìn)程：

show processlist;

8、查看用戶：

use mysql；
select * from user;

9、新建用戶：

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

user_name：要創(chuàng)建用戶的名字。

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

password：新創(chuàng)建用戶的登陸數(shù)據(jù)庫密碼，如果沒密碼可以不寫。

例：

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

10、授權(quán)用戶：

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

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

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

’username‘@‘host’：表示授權(quán)給哪個用戶。

例如：

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

注意：

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

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

相關(guān)

11、限制ip登錄：

例如，設(shè)置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、顯示數(shù)據(jù)表模式：

use 數(shù)據(jù)庫名；

show columns from 表名;

14、mysql數(shù)據(jù)類型：

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

MySQL支持所有標(biāo)準(zhǔn)SQL數(shù)值數(shù)據(jù)類型。作為SQL標(biāo)準(zhǔn)的擴展，MySQL也支持整數(shù)類型TINYINT、MEDIUMINT和BIGINT

15、插入數(shù)據(jù)：

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

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

16、查詢數(shù)據(jù)：

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

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

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

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

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

你可以使用 LIMIT 屬性來設(shè)定返回的記錄數(shù)。

你可以通過OFFSET指定SELECT語句開始查詢的數(shù)據(jù)偏移量。默認(rèn)情況下偏移量為0。

17、limit和offset用法

mysql里分頁一般用limit來實現(xiàn)

1、select* from article LIMIT 1,3

2、select * from article LIMIT 3 OFFSET 1

上面兩種寫法都表示取2,3,4三條條數(shù)據(jù)

當(dāng)limit后面跟兩個參數(shù)的時候，第一個數(shù)表示要跳過的數(shù)量，后一位表示要取的數(shù)量,例如

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

當(dāng) limit后面跟一個參數(shù)的時候，該參數(shù)表示要取的數(shù)據(jù)的數(shù)量

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

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

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

18、更新數(shù)據(jù)：

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

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

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

你可以在一個單獨表中同時更新數(shù)據(jù)。

19、模糊查詢：

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

通配符的分類:

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

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

20、排序

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
ORDER BY field1 [ASC [DESC][默認(rèn) ASC]], [field2...] [ASC [DESC][默認(rèn) ASC]]

可以添加 WHERE…LIKE 子句來設(shè)置條件

21、分組

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

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: 當(dāng)列的值是 NULL,此運算符返回 true。
IS NOT NULL: 當(dāng)列的值不為 NULL, 運算符返回 true。
<=>: 比較操作符（不同于=運算符），當(dāng)比較的的兩個值為 NULL 時返回 true。

23、in

in常用于where表達(dá)式中，其作用是查詢某個范圍內(nèi)的數(shù)據(jù)。

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

例子：

查詢book表中id為2和4的所有數(shù)據(jù)：

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、導(dǎo)入數(shù)據(jù)：

mysql 命令導(dǎo)入

使用 mysql 命令導(dǎo)入語法格式為：

mysql -u用戶名    -p密碼    <  要導(dǎo)入的數(shù)據(jù)庫數(shù)據(jù)(runoob.sql)

實例：

# mysql -uroot -p123456 < runoob.sql

以上命令將將備份的整個數(shù)據(jù)庫 runoob.sql 導(dǎo)入。

source 命令導(dǎo)入

source 命令導(dǎo)入數(shù)據(jù)庫需要先登錄到數(shù)庫終端：

mysql> create database abc;      # 創(chuàng)建數(shù)據(jù)庫
mysql> use abc;                  # 使用已創(chuàng)建的數(shù)據(jù)庫 
mysql> set names utf8;           # 設(shè)置編碼
mysql> source /home/abc/abc.sql  # 導(dǎo)入備份數(shù)據(jù)庫

使用 LOAD DATA 導(dǎo)入數(shù)據(jù)

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

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

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

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

使用 mysqlimport 導(dǎo)入數(shù)據(jù)

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

從文件 dump.txt 中將數(shù)據(jù)導(dǎo)入到 mytbl 數(shù)據(jù)表中, 可以使用以下命令：

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

27、導(dǎo)出數(shù)據(jù)：

使用SELECT…INTO OUTFILE語句來簡單的導(dǎo)出數(shù)據(jù)到文本文件上。

#將數(shù)據(jù)表 runoob_tbl 數(shù)據(jù)導(dǎo)出到 /tmp/runoob.txt 文件中: 
mysql> SELECT * FROM runoob_tbl    
    -> INTO OUTFILE '/tmp/runoob.txt';
    
#通過命令選項來設(shè)置數(shù)據(jù)輸出的指定格式，以下實例為導(dǎo)出 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句法。為了將一個數(shù)據(jù)庫的數(shù)據(jù)寫入一個文件，使用SELECT … INTO

OUTFILE，為了將文件讀回數(shù)據(jù)庫，使用LOAD DATA INFILE。

SELECT…INTO OUTFILE

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

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

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

導(dǎo)出表作為原始數(shù)據(jù)

mysqldump 是 mysql 用于轉(zhuǎn)存儲數(shù)據(jù)庫的實用程序。它主要產(chǎn)生一個 SQL 腳本，其中包含從頭重新創(chuàng)建數(shù)據(jù)庫所必需的命令 CREATE TABLE INSERT 等。

使用 mysqldump 導(dǎo)出數(shù)據(jù)需要使用 --tab 選項來指定導(dǎo)出文件指定的目錄，該目標(biāo)必須是可寫的。

以下實例將數(shù)據(jù)表 runoob_tbl 導(dǎo)出到 /tmp 目錄中：

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

導(dǎo)出 SQL 格式的數(shù)據(jù)

導(dǎo)出 SQL 格式的數(shù)據(jù)到指定文件，如下所示：

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

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

在源主機上執(zhí)行以下命令，將數(shù)據(jù)備份至 dump.txt 文件中:

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

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

如果你需要將備份的數(shù)據(jù)庫導(dǎo)入到MySQL服務(wù)器中，可以使用以下命令，使用以下命令你需要確認(rèn)數(shù)據(jù)庫已經(jīng)創(chuàng)建：

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

你也可以使用以下命令將導(dǎo)出的數(shù)據(jù)直接導(dǎo)入到遠(yuǎn)程的服務(wù)器上，但請確保兩臺服務(wù)器是相通的，是可以相互訪問的：

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

以上命令中使用了管道來將導(dǎo)出的數(shù)據(jù)導(dǎo)入到指定的遠(yuǎn)程主機上。

28、 MySQL 事務(wù)

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

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

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

MYSQL 事務(wù)處理主要有兩種方法：

1、用 BEGIN, ROLLBACK, COMMIT來實現(xiàn)

BEGIN 開始一個事務(wù)
ROLLBACK 事務(wù)回滾
COMMIT 事務(wù)確認(rèn)

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

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

29、字符集設(shè)置：

Ubuntu下設(shè)置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服務(wù)

sudo service mysql restart

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

進(jìn)入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/ |
+--------------------------+----------------------------+

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

字符集修改：

show character set; #查看當(dāng)前MySQL服務(wù)實例支持的字符集、字符序以及字符集占用的最大字節(jié)長度等信息

如下圖：

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

結(jié)果如圖：

character_set_client: 客戶端來源數(shù)據(jù)使用的字符集

character_set_connection: 數(shù)據(jù)通信鏈路的字符集，當(dāng)MySQL客戶機向服務(wù)器發(fā)送請求時，數(shù)據(jù)以該字符集進(jìn)行編碼

character_set_database: 數(shù)據(jù)庫字符集

character_set_filesystem: MySQL服務(wù)器文件系統(tǒng)的字符集，該值是固定的binary。

character_set_results: 結(jié)果集的字符集，MySQL服務(wù)器向MySQL客戶機返回執(zhí)行結(jié)果時，執(zhí)行結(jié)果以該字符集進(jìn)行編碼

character_set_server: 內(nèi)部操作字符集(MySQL服務(wù)實例字符集) character_set_system: 元數(shù)據(jù)(字段名、表名、數(shù)據(jù)庫名等)的字符集默認(rèn)為utf8

修改字符集：

set character_set_results=gbk;

如圖：

30、時間同步：

tzselect設(shè)置時區(qū)命令，根據(jù)選項選到中國的北京時間即可。

執(zhí)行如下命令：

vi .bash_profile

在末尾加入這兩行：

TZ='Asia/Shanghai'
export TZ

然后運行命令刷新配置：

source .bash_profile

再次運行date命令發(fā)現(xiàn)時區(qū)已經(jīng)成功修改：

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

linux時區(qū)重新設(shè)置后，發(fā)現(xiàn)mysql插入的數(shù)據(jù)還是原來時區(qū)的時間，下面是重新設(shè)置mysql時區(qū)的方法：

進(jìn)入mysql控制臺運行如下指令查看mysql時間。

select CURTIME();

查詢后發(fā)現(xiàn)和date命令查詢出來的時間不一樣，下面開始修改：

運行臨時解決命令：

SET time_zone = '+8:00';

刷新配置：

flush privileges;

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

vi /etc/my.cnf

添加如下配置：

[mysqld]
default_time_zone = '+8:00'

31、grep命令：

1.作用

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

2.格式

grep [options]

簡單實例：

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

32、awk命令：

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

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

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