十四书劝、MySQL基本使用

一列粪、SQL語(yǔ)句

分類：

DDL: Data Defination Language 數(shù)據(jù)定義語(yǔ)言
CREATE，DROP酒来，ALTER
DML: Data Manipulation Language 數(shù)據(jù)操縱語(yǔ)言
INSERT卢未，DELETE，UPDATE
DCL：Data Control Language 數(shù)據(jù)控制語(yǔ)言
GRANT，REVOKE辽社，COMMIT伟墙，ROLLBACK
DQL：Data Query Language 數(shù)據(jù)查詢語(yǔ)言
SELECT

1.1 數(shù)據(jù)查詢語(yǔ)言

語(yǔ)法：
SELECT
[ALL | DISTINCT | DISTINCTROW ]
[SQL_CACHE | SQL_NO_CACHE]

select_expr [, select_expr ...]
[FROM table_references
[WHERE where_condition]
[GROUP BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_condition]
[ORDER BY {col_name | expr | position}
[ASC | DESC], ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[FOR UPDATE | LOCK IN SHARE MODE]

字段顯示可以使用別名：
col1 AS alias1, col2 AS alias2, ...
WHERE子句：指明過(guò)濾條件以實(shí)現(xiàn)“選擇”的功能：
過(guò)濾條件：布爾型表達(dá)式
算術(shù)操作符：+, -, *, /, %
比較操作符：=,<=>（相等或都為空）, <>, !=(非標(biāo)準(zhǔn)SQL), >, >=, <, <=
BETWEEN min_num AND max_num
IN (element1, element2, ...)
IS NULL
IS NOT NULL
GROUP：根據(jù)指定的條件把查詢結(jié)果進(jìn)行“分組”以用于做“聚合”運(yùn)算
avg(), max(), min(), count(), sum()
HAVING: 對(duì)分組聚合運(yùn)算后的結(jié)果指定過(guò)濾條件
ORDER BY: 根據(jù)指定的字段對(duì)查詢結(jié)果進(jìn)行排序
升序：ASC
降序：DESC
LIMIT [[offset,]row_count]：對(duì)查詢的結(jié)果進(jìn)行輸出行數(shù)數(shù)量限制
對(duì)查詢結(jié)果中的數(shù)據(jù)請(qǐng)求施加“鎖”
FOR UPDATE: 寫鎖，獨(dú)占或排它鎖滴铅，只有一個(gè)讀和寫
LOCK IN SHARE MODE: 讀鎖远荠，共享鎖，同時(shí)多個(gè)讀

1.2 常用DQL語(yǔ)句

導(dǎo)入hellodb_innodb.sql生成數(shù)據(jù)庫(kù)失息，用于測(cè)試

[root@localhost ~]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 27
Server version: 10.2.25-MariaDB-log Source distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> source hellodb_innodb.sql;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Database changed
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.04 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 14 rows affected (0.00 sec)
Records: 14  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 15 rows affected (0.00 sec)
Records: 15  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 25 rows affected (0.01 sec)
Records: 25  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.03 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> show databases;
+--------------------+
| Database           |
+--------------------+
| db                 |
| db1                |
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
6 rows in set (0.00 sec)

MariaDB [hellodb]> use hellodb;show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| teachers          |
| toc               |
+-------------------+
7 rows in set (0.00 sec)

示例1：在students表中譬淳，查詢年齡大于25歲，且為男性的同學(xué)的名字和年齡

MariaDB [hellodb]> select name,age from students where age>25 and Gender='M'; 
+--------------+-----+
| name         | age |
+--------------+-----+
| Xie Yanke    |  53 |
| Ding Dian    |  32 |
| Yu Yutong    |  26 |
| Shi Qing     |  46 |
| Tian Boguang |  33 |
| Xu Xian      |  27 |
| Sun Dasheng  | 100 |
+--------------+-----+
7 rows in set (0.00 sec)

示例2：以ClassID為分組依據(jù)盹兢，顯示每組的平均年齡

MariaDB [hellodb]> select classid,avg(age) as 'avg age' from students group by classid;
+---------+---------+
| classid | avg age |
+---------+---------+
|    NULL | 63.5000 |
|       1 | 20.5000 |
|       2 | 36.0000 |
|       3 | 20.2500 |
|       4 | 24.7500 |
|       5 | 46.0000 |
|       6 | 20.7500 |
|       7 | 19.6667 |
+---------+---------+
8 rows in set (0.00 sec)

示例3：顯示第2題中平均年齡大于30的分組及平均年齡

MariaDB [hellodb]> select classid,avg(age) as 'avg age' from students group by classid having avg(age)>30;
+---------+---------+
| classid | avg age |
+---------+---------+
|    NULL | 63.5000 |
|       2 | 36.0000 |
|       5 | 46.0000 |
+---------+---------+
3 rows in set (0.00 sec)

示例4：顯示以L開(kāi)頭的名字的同學(xué)的信息

MariaDB [hellodb]> select * from students where name like 'L%';
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name        | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
|     8 | Lin Daiyu   |  17 | F      |       7 |      NULL |
|    14 | Lu Wushuang |  17 | F      |       3 |      NULL |
|    17 | Lin Chong   |  25 | M      |       4 |      NULL |
+-------+-------------+-----+--------+---------+-----------+
3 rows in set (0.00 sec)

二邻梆、MySQL用戶和權(quán)限管理

管理類：
CREATE TEMPORARY TABLES
CREATE USER
FILE
SUPER
SHOW DATABASES
RELOAD
SHUTDOWN
REPLICATION SLAVE
REPLICATION CLIENT
LOCK TABLES
PROCESS
程序類： FUNCTION、PROCEDURE绎秒、TRIGGER
CREATE
ALTER
DROP
EXCUTE
庫(kù)和表級(jí)別：DATABASE浦妄、TABLE
ALTER
CREATE
CREATE VIEW
DROP
INDEX
SHOW VIEW
GRANT OPTION：能將自己獲得的權(quán)限轉(zhuǎn)贈(zèng)給其他用戶

權(quán)限對(duì)象：

數(shù)據(jù)操作
SELECT
INSERT
DELETE
UPDATE
字段級(jí)別
SELECT(col1,col2,...)
UPDATE(col1,col2,...)
INSERT(col1,col2,...)
所有權(quán)限
ALL PRIVILEGES 或 ALL

授權(quán)語(yǔ)法：
GRANT priv_type [(column_list)],... ON [object_type] priv_level TO 'user'@'host' [IDENTIFIED BY 'password'] [WITH GRANT OPTION];

priv_type: ALL [PRIVILEGES]
object_type:TABLE | FUNCTION | PROCEDURE
priv_level: (所有庫(kù)) | . | db_name. | db_name.tbl_name | tbl_name(當(dāng)前庫(kù)的表) | db_name.routine_name(指定庫(kù)的函數(shù),存儲(chǔ)過(guò)程,觸發(fā)器)
with_option: GRANT OPTION
| MAX_QUERIES_PER_HOUR count
| MAX_UPDATES_PER_HOUR count
| MAX_CONNECTIONS_PER_HOUR count
| MAX_USER_CONNECTIONS count

示例：授權(quán)magedu用戶，允許192.168.1.0/24網(wǎng)段可以連接mysql

MariaDB [hellodb]>
GRANT USAGE ON *.* TO 'magedu'@'192.168.1.%' IDENTIFIED BY '000000';
MariaDB [hellodb]> SHOW GRANTS FOR 'magedu'@'192.168.1.%';
+-----------------------------------------------------------------------------------------------------------------+
| Grants for magedu@192.168.1.%                                                                                   |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'magedu'@'192.168.1.%' IDENTIFIED BY PASSWORD '*032197AE5731D4664921A6CCAC7CFCE6A0698693' |
+-----------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

三见芹、Mysql常見(jiàn)存儲(chǔ)引擎以及特點(diǎn)

常見(jiàn)存儲(chǔ)引擎：MyISAM剂娄、Innodb

MyISAM引擎特點(diǎn)

不支持事務(wù)
表級(jí)鎖定
讀寫相互阻塞，寫入不能讀玄呛，讀時(shí)不能寫
只緩存索引
不支持外鍵約束
不支持聚簇索引
讀取數(shù)據(jù)較快阅懦，占用資源較少
不支持MVCC（多版本并發(fā)控制機(jī)制）高并發(fā)
崩潰恢復(fù)性較差
MySQL5.5.5前默認(rèn)的數(shù)據(jù)庫(kù)引擎

MyISAM存儲(chǔ)引擎適用場(chǎng)景：
只讀（或者寫較少）、表較信锹痢（可以接受長(zhǎng)時(shí)間進(jìn)行修復(fù)操作）

MyISAM引擎文件
tbl_name.frm 表格式定義
tbl_name.MYD 數(shù)據(jù)文件
tbl_name.MYI 索引文件

InnoDB引擎特點(diǎn)

行級(jí)鎖
支持事務(wù)耳胎，適合處理大量短期事務(wù)
讀寫阻塞與事務(wù)隔離級(jí)別相關(guān)
可緩存數(shù)據(jù)和索引
支持聚簇索引
崩潰恢復(fù)性更好
支持MVCC高并發(fā)
從MySQL5.5后支持全文索引
從MySQL5.5.5開(kāi)始為默認(rèn)的數(shù)據(jù)庫(kù)引擎
InnoDB引擎文件
所有InnoDB表的數(shù)據(jù)和索引放置于同一個(gè)表空間中
表空間文件：datadir定義的目錄下
數(shù)據(jù)文件：ibddata1, ibddata2, ...
每個(gè)表單獨(dú)使用一個(gè)表空間存儲(chǔ)表的數(shù)據(jù)和索引
啟用：innodb_file_per_table=ON

默認(rèn)ON (>= MariaDB 5.5)

兩類文件放在數(shù)據(jù)庫(kù)獨(dú)立目錄中
數(shù)據(jù)文件(存儲(chǔ)數(shù)據(jù)和索引)：tb_name.ibd
表格式定義：tb_name.frm

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者

人面猴
序言：七十年代末，一起剝皮案震驚了整個(gè)濱河市惕它，隨后出現(xiàn)的幾起案子怕午，更是在濱河造成了極大的恐慌，老刑警劉巖淹魄，帶你破解...
沈念sama閱讀 219,427評(píng)論 6贊 508
死咒
序言：濱河連續(xù)發(fā)生了三起死亡事件郁惜，死亡現(xiàn)場(chǎng)離奇詭異，居然都是意外死亡甲锡，警方通過(guò)查閱死者的電腦和手機(jī)兆蕉，發(fā)現(xiàn)死者居然都...
沈念sama閱讀 93,551評(píng)論 3贊 395
救了他兩次的神仙讓他今天三更去死
文/潘曉璐我一進(jìn)店門，熙熙樓的掌柜王于貴愁眉苦臉地迎上來(lái)搔体，“玉大人恨樟，你說(shuō)我怎么就攤上這事半醉【尉悖” “怎么了？”我有些...
開(kāi)封第一講書人閱讀 165,747評(píng)論 0贊 356
道士緝兇錄：失蹤的賣姜人
文/不壞的土叔我叫張陵缩多，是天一觀的道長(zhǎng)呆奕。經(jīng)常有香客問(wèn)我养晋，道長(zhǎng)，這世上最難降的妖魔是什么梁钾？我笑而不...
開(kāi)封第一講書人閱讀 58,939評(píng)論 1贊 295
?港島之戀（遺憾婚禮）
正文為了忘掉前任绳泉，我火速辦了婚禮，結(jié)果婚禮上姆泻，老公的妹妹穿的比我還像新娘零酪。我一直安慰自己，他們只是感情好拇勃，可當(dāng)我...
茶點(diǎn)故事閱讀 67,955評(píng)論 6贊 392
惡毒庶女頂嫁案：這布局不是一般人想出來(lái)的
文/花漫我一把揭開(kāi)白布四苇。她就那樣靜靜地躺著，像睡著了一般方咆。火紅的嫁衣襯著肌膚如雪月腋。梳的紋絲不亂的頭發(fā)上，一...
開(kāi)封第一講書人閱讀 51,737評(píng)論 1贊 305
城市分裂傳說(shuō)
那天瓣赂，我揣著相機(jī)與錄音榆骚，去河邊找鬼。笑死煌集，一個(gè)胖子當(dāng)著我的面吹牛妓肢，可吹牛的內(nèi)容都是我干的。我是一名探鬼主播苫纤，決...
沈念sama閱讀 40,448評(píng)論 3贊 420
雙鴛鴦連環(huán)套：你想象不到人心有多黑
文/蒼蘭香墨我猛地睜開(kāi)眼职恳，長(zhǎng)吁一口氣：“原來(lái)是場(chǎng)噩夢(mèng)啊……” “哼！你這毒婦竟也來(lái)了方面？” 一聲冷哼從身側(cè)響起放钦，我...
開(kāi)封第一講書人閱讀 39,352評(píng)論 0贊 276
萬(wàn)榮殺人案實(shí)錄
序言：老撾萬(wàn)榮一對(duì)情侶失蹤，失蹤者是張志新（化名）和其女友劉穎恭金，沒(méi)想到半個(gè)月后操禀，有當(dāng)?shù)厝嗽跇?shù)林里發(fā)現(xiàn)了一具尸體，經(jīng)...
沈念sama閱讀 45,834評(píng)論 1贊 317
?護(hù)林員之死
正文獨(dú)居荒郊野嶺守林人離奇死亡横腿，尸身上長(zhǎng)有42處帶血的膿包…… 初始之章·張勛以下內(nèi)容為張勛視角年9月15日...
茶點(diǎn)故事閱讀 37,992評(píng)論 3贊 338
?白月光啟示錄
正文我和宋清朗相戀三年颓屑，在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了。大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片耿焊。...
茶點(diǎn)故事閱讀 40,133評(píng)論 1贊 351
活死人
序言：一個(gè)原本活蹦亂跳的男人離奇死亡揪惦，死狀恐怖，靈堂內(nèi)的尸體忽然破棺而出罗侯，到底是詐尸還是另有隱情器腋，我是刑警寧澤，帶...
沈念sama閱讀 35,815評(píng)論 5贊 346
?日本核電站爆炸內(nèi)幕
正文年R本政府宣布，位于F島的核電站纫塌，受9級(jí)特大地震影響诊县，放射性物質(zhì)發(fā)生泄漏。R本人自食惡果不足惜措左，卻給世界環(huán)境...
茶點(diǎn)故事閱讀 41,477評(píng)論 3贊 331
男人毒藥：我在死后第九天來(lái)索命
文/蒙蒙一依痊、第九天我趴在偏房一處隱蔽的房頂上張望。院中可真熱鬧怎披，春花似錦胸嘁、人聲如沸。這莊子的主人今日做“春日...
開(kāi)封第一講書人閱讀 32,022評(píng)論 0贊 22
一樁弒父案缴渊，背后竟有這般陰謀
文/蒼蘭香墨我抬頭看了看天上的太陽(yáng)。三九已至鱼炒，卻和暖如春衔沼，著一層夾襖步出監(jiān)牢的瞬間，已是汗流浹背昔瞧。一陣腳步聲響...
開(kāi)封第一講書人閱讀 33,147評(píng)論 1贊 272
情欲美人皮
我被黑心中介騙來(lái)泰國(guó)打工指蚁，沒(méi)想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留，地道東北人自晰。一個(gè)月前我還...
沈念sama閱讀 48,398評(píng)論 3贊 373
代替公主和親
正文我出身青樓凝化，卻偏偏與公主長(zhǎng)得像，于是被迫代替她去往敵國(guó)和親酬荞。傳聞我的和親對(duì)象是個(gè)殘疾皇子搓劫，可洞房花燭夜當(dāng)晚...
茶點(diǎn)故事閱讀 45,077評(píng)論 2贊 355