一. MariaDB or MySQL基礎知識
層次模型 –> 網狀模型 –> (Codd) 關系模型
DBMS –> RDBMS
RDBMS：
范式：第一范式、第二范式、第三范式；
表：row, column；
關系運算：
選擇
投影
數據庫：表、索引、視圖（虛表）、SQL、存儲過程、存儲函數、觸發器、事件調度器；
DDL：CREATE，ALTER，DROP
DML：INSERT/UPDATE/DELETE/SELECT
約束：
主鍵約束：惟一、非空；一張表只能有一個；
惟一鍵約束：惟一，可以存在多個；
外鍵約束：參考性約束；
檢查性約束：check；
三層模型：
物理層 –> SA
邏輯層 –> DBA
視圖層 –> Coder
實現：
Oracle, DB2, Sybase, Infomix, SQL Server；
MySQL, MariaDB, PostgreSQL, SQLite；
===========================================================
二. MySQL
1. 版本: 5.1 –> 5.5 –> 5.6 –> 5.7 –> 8.0
MariaDB：5.5.x –> 10.x
2. 特性：
插件式存儲引擎
單進程多線程
3. 安裝MySQL：
OS Vendor：rpm
MySQL：
source code：cmake
binary package：
i686, x86_64;
glibc VERSION
prepackage：rpm, deb
os, arch,
4. 服務端程序：
mysqld, mysqld_safe, mysqld_multi
客戶端程序：
mysql, mysqldump, mysqlbinlog, mysqldmin, …
非客戶端類程序：
myisamchk, myisampack, …
5. 配置文件：
讀取多處的多個配置文件，而且會以指定的次序的進行；
命令: my_print_defaults
#用于顯示mysql 相關配置以及選項:
配置文件加載順序: /etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf
#Default options are read from the following files in the given order:
不同的配置文件中出現同一參數且擁有不同值時，后讀取將為最終生效值；
修改默認讀取的配置文件（mysqld_safe命令）：
–defaults-file=file_name
于讀取的默認配置文件之外再加載一個文件：
–defaults-extra-file=path
6. 配置文件格式：ini風格的配置文件，能夠為mysql的各種應用程序提供配置信息：
[mysqld]
[mysqld_safe]
[mysqld_multi]
[server]
[mysql]
[mysqldump]
[client]
…
PARAMETER = VALUE
PARAMETER：
innodb_file_per_table
innodb-file-per-table
7. 程序文件：
服務端程序：mysqld_safe, mysqld_multi
客戶端程序：mysql, mysqldump, mysqladmin
工具程序：myisampack, …
====================================================
三. mysql 交互式CLI工具
( mysql –> mysql protocol –> mysqld )
1. mysql [options] db_name
常用選項：
–host=host_name, -h host_name：服務端地址；
–user=user_name, -u user_name：用戶名；
–password[=password], -p[password]：用戶密碼；
–port=port_num, -P port_num：服務端端口；
–protocol={TCP|SOCKET|PIPE|MEMORY}：
本地通信：基于本地回環地址進行請求，將基于本地通信協議；
Linux：SOCKET
Windows：PIPE，MEMORY
非本地通信：使用非本地回環地址進行的請求； TCP協議；
–socket=path, -S path
–database=db_name, -D db_name：
–compress, -C：數據壓縮傳輸
–execute=statement, -e statement：非交互模式執行SQL語句；
–vertical, -E：查詢結果縱向顯示；
2. 客戶端以及服務端命令：
客戶端命令：于客戶端執行；
服務端命令：SQL語句，需要一次性完整地發往服務端,語句必須有結束符"；"
? (\?) Synonym for `help'.
clear (\c) Clear the current input statement.
connect (\r) Reconnect to the server. Optional arguments are db
and host.
delimiter (\d) Set statement delimiter.
edit (\e) Edit command with $EDITOR.
ego (\G) Send command to mysql server, display result vertically.
exit (\q) Exit mysql. Same as quit.
go (\g) Send command to mysql server.
help (\h) Display this help.
nopager (\n) Disable pager, print to stdout.
notee (\t) Don't write into outfile.
pager (\P) Set PAGER [to_pager]. Print the query results via PAGER.
print (\p) Print current command.
prompt (\R) Change your mysql prompt.
quit (\q) Quit mysql.
rehash (\#) Rebuild completion hash.
source (\.) Execute an SQL script file. Takes a file name as an
argument.
status (\s) Get status information from the server.
system (\!) Execute a system shell command.
tee (\T) Set outfile [to_outfile]. Append everything into given
outfile.
use (\u) Use another database. Takes database name as
argument.
charset (\C) Switch to another charset. Might be needed for
processing binlog with multi-byte charsets.
warnings (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.
3. mysql命令的使用幫助：
# man mysql
# mysql –help –verbose
4. sql腳本運行：
mysql [options] [DATABASE] < /PATH/FROM/SOME_SQL_SCRIPT
=======================================================
四. mysqld服務器程序：工作特性的定義方式
1.
服務器參數/變量：設定MySQL的運行特性；
mysql> SHOW GLOBA|[SESSION] VARIABLES [LIKE clause];
狀態（統計）參數/變量：保存MySQL運行中的統計數據或狀態數據；
mysql> SHOW GLOBA|[SESSION] STATUS [LIKE clause];
#顯示全局 | 會話級別的 狀態信息;
eg:
SHOW GLOBA VARIABLES;
#顯示全局變量
SHOW SESSION VARIABLES;
#顯示會話級別的變量
—————————————————————————————–
顯示單個變量設定值的方法：
mysql> SELECT @@[global.|session.]system_var_name
%：匹配任意長度的任意字符；
_：匹配任意單個字符；
eg:
—————————————————————————————–
變量/參數級別：
全局：為所有會話設定默認；
會話：跟單個會話相關；會話建立會從全局繼承；
—————————————————————————————–
服務器變量的調整方式：
運行時修改：
global：僅對修改后新建立的會話有效；
#且僅有擁有全局權限(管理權限)的用戶才能修改;
session：僅對當前會話有效，且立即生效；
—————————————————————————————–
啟動前通過配置文件修改：
重啟后生效；
注意: 強烈建議在啟動前修改對應參數, 部分值,是在數據庫初始化時候生效,
若啟用后再修改,可能會發生錯誤
2 . 運行時修改變量值操作方法：
mysql> HELP SET
SET [GLOBAL | SESSION] system_var_name = expr
SET [@@global. | @@session. | @@]system_var_name = expr
注意: 僅對新創建的會話有效,對當前會話無效
eg:
3. 安裝完成后的安全初始化：
mysql_secure_installation
4. 運行前常修改的參數：
innodb_file_per_table=ON
#每個表都使用獨立的文件存放
skip_name_resolve=ON
#跳過名字解析,防止名字反解的時候無法實現而報錯
========================================================
五. MySQL的數據類型：
1. 數據類型:
字符型
數值型
日期時間型
內建類型
2. 字符型：
CHAR(#)， BINARY：定長型；CHAR不區分字符大小寫，而BINARY區分；
VARCHAR(#)， VARBINARY：變長型
TEXT：TINYTEXT，TEXT，MEDIUMTEXT，LONGTEXT
BLOB：TINYBLOB，BLOB，MEDIUMBLOB， LONGBLOB
3. 數值型：
浮點型：近似
FLOAT
DOUBLE
REAL
BIT
4.整型：精確
INTEGER：TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT
DECIMAL
5. 日期時間型：
日期：DATE
時間：TIME
日期j時間：DATETIME
時間戳：TIMESTAMP
年份：YEAR(2), YEAR(4)
6. 內建：
ENUM：枚舉
ENUM('Sun','Mon','Tue','Wed')
SET：集合
7. 類型修飾符：
字符型：NOT NULL，NULL，DEFALUT ‘STRING’，CHARACET SET
‘CHARSET’，COLLATION ‘collocation'
整型：NOT NULL， NULL， DEFALUT value, AUTO_INCREMENT,
UNSIGNED
日期時間型：NOT NULL， NULL， DEFAULT
8. SQL MODE：定義mysqld對約束等違反時的響應行為等設定；
常用的MODE：
TRADITIONAL
#傳統模式,當違反了數據定義規則,都不允許寫入;
STRICT_TRANS_TABLES
#僅對事務型表嚴格限定;
STRICT_ALL_TABLES
#對所有表做嚴格限定;
9. 修改方式：
mysql> SET GLOBAL sql_mode='MODE';
mysql> SET @@global.sql_mode='MODE';
=========================================================
六. SQL：DDL，DML 語句
1). DDL：
mysql> HELP Data Definition
2. DDL 涉及命令:
CREATE, ALTER, DROP
DATABASE, TABLE
INDEX, VIEW, USER
FUNCTION, FUNCTION UDF, PROCEDURE, TABLESPACE, TRIGGER,
SERVER
3. 數據庫操作：
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name CHARACTER
SET [=] charset_name COLLATE [=] collation_name
ALTER {DATABASE | SCHEMA} [db_name] CHARACTER SET [=]
charset_name COLLATE [=] collation_name
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name
4. 表操作：
CREATE
(1) CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(create_definition,…)
[table_options]
[partition_options]
CREATE TABLE [IF NOT EXISTS] tble_name (col_name
data_typ|INDEX|CONSTRAINT);
table_options：
ENGINE [=] engine_name
查看支持的所有存儲引擎：
mysql> SHOW ENGINES;
查看指定表的存儲引擎：
mysql> SHOW TABLE STATUS LIKE clause;
ROW_FORMAT [=]
{DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
(2) CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,…)]
[table_options]
[partition_options]
select_statement
直接創建表，并將查詢語句的結果插入到新創建的表中；
(3) CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
{ LIKE old_tbl_name | (LIKE old_tbl_name) }
復制某存在的表的結構來創建新的空表；
DROP:
DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name];
ALTER：
ALTER TABLE tbl_name
[alter_specification [, alter_specification] …]
可修改內容：
(1) table_options
(2) 添加定義：ADD
字段、字段集合、索引、約束
(3) 修改字段：
CHANGE [COLUMN] old_col_name new_col_name
column_definition [FIRST|AFTER col_name]
MODIFY [COLUMN] col_name column_definition
[FIRST | AFTER col_name]
(4) 刪除操作：DROP
字段、索引、約束
表重命名：
RENAME [TO|AS] new_tbl_name
查看表結構定義：
DESC tbl_name；
查看表定義：
SHOW CREATE TABLE tbl_name
查看表屬性信息：
SHOW TABLE STATUS [{FROM | IN} db_name] [LIKE
'pattern' | WHERE expr]
5. 索引：
創建：
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [index_type]
ON tbl_name (index_col_name,…)
查看：
SHOW {INDEX | INDEXES | KEYS} {FROM | IN} tbl_name [{FROM | IN}
db_name] [WHERE expr]
刪除：
DROP INDEX index_name ON tbl_name
索引類型：
聚集索引、非聚集索引：索引是否與數據存在一起；
主鍵索引、輔助索引
稠密索引、稀疏索引：是否索引了每一個數據項；
BTREE（B+）、HASH、R Tree、FULLTEXT
BTREE：左前綴；
EXPLAIN：分析查詢語句的執行路徑；
6. 視圖：VIEW
虛表：存儲下來的SELECT語句；
#即在基表(原表)的基礎上,再創建一個臨時的新表,將SELECT執行的結果保存
#到此表中
且,虛表時候可以修改,取決于基表中的剩余字段的約束部分;
示例:
創建：
CREATE VIEW view_name [(column_list)] AS select_statement
修改：
ALTER VIEW view_name [(column_list)] AS select_statement
刪除：
DROP VIEW [IF EXISTS] view_name [, view_name] …
SHOW TABLES STATUS\G;
#可以查看表狀態,并可以用于區分哪些是虛表;
======================================================
2). DML:
mysql> HELP Data Manipulation
1. 涉及命令:
INSERT/REPLACE, DELETE, SELECT, UPDATE
INSERT/REPLACE，DELETE，UPDATE，SELECT
2. INSERT：
單行插入
批量插入
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,…)]
{VALUES | VALUE} ({expr | DEFAULT},…),(…),…
[ ON DUPLICATE KEY UPDATE
col_name=expr
[, col_name=expr] … ]
Or:
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name
SET col_name={expr | DEFAULT}, …
[ ON DUPLICATE KEY UPDATE
col_name=expr
[, col_name=expr] … ]
Or:
INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,…)]
SELECT …
[ ON DUPLICATE KEY UPDATE
col_name=expr
[, col_name=expr] … ]
3. DELETE：
DELETE FROM tbl_name [WHERE where_condition] [ORDER BY …] [LIMIT
row_count]
注意：一定要有限制條件，否則將清空整個表；
限制條件：
[WHERE where_condition]
[ORDER BY …] [LIMIT row_count]
4. UPDATE：
UPDATE table_reference SET col_name1={expr1|DEFAULT} [, col_name2=
{expr2|DEFAULT}] …
[WHERE where_condition]
[ORDER BY …]
[LIMIT row_count]
注意：一定要有限制條件，否則將修改整個表中指定字段的數據；
限制條件：
[WHERE where_condition]
[ORDER BY …] [LIMIT row_count]
注意：sql_safe_updates變量可阻止不帶條件更新操作；
——————————————————————————————
3). SELECT：
(1) SELECT語句的執行流程：
FROM (找表)–> WHERE(子集) –> Group By(過濾分組,用于聚合) –> Having(描述
group by 的結果再過濾) –> Order BY(排序) –> SELECT(挑選字段) –> Limit (顯示限定)
#先取行,再去字段;
(2) Query Cache：緩存查詢的執行結果；
緩存的組織形式:
key：查詢語句的hash值；
value：查詢語句的執行結果；
SQL語句的編寫方式：
SELECT name FROM tbl2;
select name from tbl2;
#mysql中,表名區分大小寫,但是關鍵字不區分;
注意:這兩種情況下,緩存時的hash值是不一樣的,因此,需要嚴格限定sql語句的編寫,
以提高緩存命中率;
查詢執行路徑：
請求–>查詢緩存
請求–>查詢緩存–>解析器–>預處理器–>優化器–>查詢執行引擎–>存儲引
擎–>緩存–>響應
(3) 單表查詢：
SELECT
[ALL | DISTINCT | DISTINCTROW ]
[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
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}]
用法：
SELECT col1, col2, … FROM tble_name；
SELECT col1, col2, … FROM tble_name WHERE clause；
SELECT col1, col2, … FROM tble_name [WHERE clause] GROUP BY
col_name [HAVING clause]；
#以指定字段分組,再做聚合;
DISTINCT：數據去重；
緩存相關:
SQL_CACHE：顯式指定緩存查詢語句的結果；
SQL_NO_CACHE：顯式指定不緩存查詢語句的結果；
query_cache_type服務器變量有三個值：
ON：啟用；
SQL_NO_CACHE：不緩存；默認符合緩存條件都緩存；
OFF：關閉；
DEMAND：按需緩存；
SQL_CACHE：緩存；默認不緩存；
字段可以使用別名 ：
col1 AS alias1, col2 AS alias2, …
五種常用的聚合方法:
avg(), max(), min(), sum(), count()
WHERE子句：指明過濾條件以實現“選擇”功能；
過濾條件：布爾型表達式；
[WHERE where_condition]
算術操作符：+, -, *, /, %
比較操作符：=, <>, !=, <=>, >, >=, <, <=
IS NULL， IS NOT NULL
區間：BETWEEN min AND max
IN：列表；
LIKE：模糊比較，%和_；
RLIKE或REGEXP
邏輯操作符：
AND， OR， NOT， XOR
GROUP BY：根據指定的字段把查詢的結果進行“分組”以用于“聚合”運算；
avg(), max(), min(), sum(), count()
HAVING：對分組聚合后的結果進行條件過濾；
#基于gender對表進行分組,然后顯示age的平均值,和gender 兩個字段;
ORDER BY：根據指定的字段把查詢的結果進行排序；
升序：ASC
降序：DESC
LIMIT：對輸出結果進行數量限制
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
eg:
以年齡排序后，顯示年齡最大的前10位同學的信息；
SELECT name,age FROM students ORDER BY age DESC LIMIT 10;
===================================================
(4.) 多表查詢：
連接操作：
(a) 交叉連接：笛卡爾乘積；
(b) 內連接：略
(c) 等值連接：讓表之間的字段以等值的方式建立連接；
——————————————————————————–
(d) 不等值連接：略
自然連接:
(e) 自連接:
#即引用自己表內的內容來創建一張新的表
示例:
#對基表中,teacherID 與基表中的 stuID 建立連接關系, teacherID的值作為鍵,值即為
#StuID對應的值
#例如: teacherID = 3 , 后面先連接stuID為3 的同學信息
————————————————————————————-
(f) 外連接：(即外鍵功能,僅innodb引擎支持)
左外連接：
FROM tb1 LEFT JOIN tb2 ON tb1.col = tb2.col
(以左表為準,左表有的信息,全部保留)
右外連接：
FROM tb1 RIGHT JOIN tb2 ON tb1.col = tb2.col
(以右表為準,右表有的信息,全部保留)
(6 ) 子查詢：在查詢中嵌套查詢；
用于WHERE子句中的子查詢；
(1) 用于比較表達式中的子查詢：子查詢僅能返回單個值；
(2) 用于IN中的子查詢：子查詢可以返回一個列表值；
(3) 用于EXISTS中的子查詢：
示例:
—————————————————-
用于FROM子句中的子查詢；
SELECT tb_alias.col1, … FROM (SELECT clause) AS tb_alias WHERE
clause;
示例:
注意:此類型查詢,性能不高,mysql中對此類子查詢未充分優化過;
能用連接查詢,盡可能勿用子查詢
聯合查詢：將多個查詢語句的執行結果相合并；
#多個表中,查詢執行后,執行的結果非常相似,便可將多個查詢的表,合成一個臨時的新表
UNION
SELECT clause UNION SELECT cluase；
============================================================
七. 存儲引擎
1. 表類型：表級別概念，不建議在同一個庫中的表上使用不同的ENGINE；
創建表時,可以指明使用指定的存儲引擎:
CREATE TABLE … ENGINE[=]STORAGE_ENGINE_NAME …
( 若不指明使用什么存儲引擎,則使用默認引擎,)
查看默認引擎:
查看表使用的引擎:
SHOW TABLE STATUS
查看指定搜索引擎的狀態信息:
show engine innodb status;
2. 常見的存儲引擎：
MyISAM, Aria, InnoDB, MRG_MYISAM, CSV, BLACKHOLE, MEMORY,
PERFORMANCE_SCHEMA, ARCHIVE, FEDERATED
查看數據庫支持的引擎:
3 . InnoDB：InnoBase
Percona-XtraDB, Supports transactions, row-level locking, and foreign keys
三大特性: 行級鎖
事務
外鍵
特性概述:
事務型存儲引擎，適合對事務要求較高的場景中；但較適用于處理大量短期事務；
基于MVCC（Mutli Version Concurrency Control）支持高并發；
#基于快照功能實現,每個連接所操作的數據都是在修改前的一個瞬時快照,
然后在此基礎上處理數據;
支持四個隔離級別，默認級別為REPEATABLE-READ；間隙鎖以防止幻讀；
使用聚集索引(數據與索引存放在一起,找到數據即可找到索引)（主鍵索引）
#即在btree索引中,在每個葉子節點處,聚集索引存放的是數據,
# 而非聚集索引在葉子節點上存放的是數據的存放地址;
支持”自適應Hash索引“(當使用等值搜索時,性能極好)
鎖粒度：行級鎖；間隙鎖；
相關變量查看: show variables like '%innodb%'
數據存儲:
#數據存儲于“表空間(table space)"中：
#存儲路徑一般是mysql的工作目錄,如/var/lib/mysql/
(1) 所有InnoDB表的數據和索引存儲于同一個表空間中；
表空間文件：datadir定義的目錄中
文件：ibdata1, ibdata2, …
(2) innodb_file_per_table=ON，意味著每表使用單獨的表空間文件；
數據文件（數據和索引，存儲于數據庫目錄）: tbl_name.ibd
表結構的定義：在數據庫目錄，tbl_name.frm
示例:
創建表tbl3, 會產生表結構定義未見tbl3.frm 以及 表數據存放文件tbl3.ibd
buffer_pool 緩沖池: (此類為必調項目)
#可以用于緩沖表數據,元數據,以及索引數據
總結：
數據存儲：表空間；
并發：MVCC，間隙鎖，行級鎖；
索引：聚集索引、輔助索引(索引中的值可以重復)；
性能：預讀操作、內存數據緩沖、內存索引緩存、自適應Hash索引、插入操作緩
存區；
備份：支持熱備；
4. MyISAM：
部分特點::
支持全文索引（FULLTEXT index）、壓縮、空間函數（GIS,空間索引）；
不支持事務
鎖粒度：表級鎖
崩潰無法保證表安全恢復
適用場景：只讀或讀多寫少的場景、較小的表（以保證崩潰后恢復的時間較短）；
文件：每個表有三個文件，存儲于數據庫目錄中
tbl_name.frm：表格式定義；
tbl_name.MYD：數據文件；
tbl_name.MYI：索引文件；
特性：
加鎖和并發：表級鎖；
修復：手動或自動修復、但可能會丟失數據；
索引：非聚集索引；
延遲索引更新；
表壓縮；
行格式：
{DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
5. 其它的存儲引擎：
CSV：將CSV文件（以逗號分隔字段的文本文件）作為MySQL表文件；
MRG_MYISAM：將多個MyISAM表合并成的虛擬表；
BLACKHOLE：類似于/dev/null，不真正存儲數據；
MEMORY：內存存儲引擎，支持hash索引，表級鎖，常用于臨時表；
FEDERATED: 用于訪問其它遠程MySQL服務器上表的存儲引擎接口；
MariaDB額外支持很多種存儲引擎：
OQGraph、SphinxSE、TokuDB、Cassandra、CONNECT、SQUENCE、…
可以使用show engine ; 查看支持的其他引擎:
6. 搜索引擎：
lucene, sphinx
lucene：Solr, Elasticsearch
=========================================================
八. 并發控制：
鎖：Lock
鎖類型 ：
讀鎖：共享鎖，可被多個讀操作共享；
寫鎖：排它鎖，獨占鎖；
鎖粒度：
表鎖：在表級別施加鎖，并發性較低；
行鎖：在行級另施加鎖，并發性較高；
鎖策略：在鎖粒度及數據安全性之間尋求一種平衡機制；
存儲引擎：級別以及何時施加或釋放鎖由存儲引擎自行決定；
MySQL Server：表級別，可自行決定，也允許顯式請求；
鎖類別：
顯式鎖：用戶手動請求的鎖；
隱式鎖：存儲引擎自行根據需要施加的鎖；
顯式鎖的使用：
(1) LOCK TABLES
LOCK TABLES tbl_name read|write, tbl_name read|write, …
#讀鎖中, 多個并發可以同時施加讀鎖,
#但是若有其他連接施加讀鎖,其他連接則無法再同時施加寫鎖
UNLOCK TABLES
#解除對應表的全部鎖
(2) FLUSH TABLES
FLUSH TABLES tbl_name,… [WITH READ LOCK];
#將內存的指定表強行同步到磁盤,并關閉表
WITH READ LOCK: 將內存的指定表強行同步到磁盤,并關閉表
后再次以指定的鎖打開
UNLOCK TABLES;
(3) SELECT cluase
[FOR UPDATE | LOCK IN SHARE MODE]
#鎖定指定表中指定行的鎖定操作
讀寫鎖示例:
1. 對表施加讀鎖,并嘗試寫入操作:
在另一個連接中同時施加讀鎖(成功), 再嘗試施加寫鎖(失敗);
#被堵塞
堵塞時間:
============================================================
練習
導入hellodb.sql生成數據庫
——————————————————————————–
單表查詢:
(1) 在students表中，查詢年齡大于25歲，且為男性的同學的名字和年齡；
SELECT name,age FROM students WHERE age>25 and gender="M";
(2) 以ClassID為分組依據，顯示每組的平均年齡；
SELECT ClassID,avg(age) FORM students GROUP BY classID;
(3) 顯示第2題中平均年齡大于30的分組及平均年齡；
SELECT ClassID,avg(age) FORM students GROUP BY classID Having
avg(age) > 30;
(4) 顯示以L開頭的名字的同學的信息；
SELECT * FROM students WHERE name RLIKE "^L .*$";
(5) 顯示TeacherID非空的同學的相關信息；
SELETT name FROM students WHERE TeacherID IS NOT NULL;
(6) 以年齡排序后，顯示年齡最大的前10位同學的信息；
SELECT name,age FROM students ORDER BY age DESC LIMIT 10;
(7) 查詢年齡大于等于20歲，小于等于25歲的同學的信息；用三種方法；
SELECT name,age FROM students WHERE age in (20..25);
SELECT name,age FROM students WHERE age>=20 and age<=25;
==========================================================
練習：導入hellodb.sql，以下操作在students表上執行
1、以ClassID分組，顯示每班的同學的人數；
SELECT ClassID,count(StuID) FROM students GROUP BY ClassID;
2、以Gender分組，顯示其年齡之和；
SELECT Gender,sum(age) FROM students GROUP BY Gender;
3、以ClassID分組，顯示其平均年齡大于25的班級；
SELECT ClassID,avg(age) FROM students GROUP BY ClassID Having
avg(age) > 25;
4、以Gender分組，顯示各組中年齡大于25的學員的年齡之和；
SELECT Gender,sum(age) FROM students WHERE age > 25 GROUP
BY Gender;
———————————————————————————
多表查詢:
練習：導入hellodb.sql，完成以下題目：
1、顯示前5位同學的姓名、課程及成績；
SELECT name,Course,scores FROM student,courses,scores WHERE
student.StuID = scores.StuID and scores.CourseID = courses.CourseID
2、顯示其成績高于80的同學的名稱及課程；
SELECT name,course,score FROM student,course,scores WHERE student.StuID = scores.StuID and courses.CourseID = scores.CourseID and Score > 80;
3、求前8位同學每位同學自己兩門課的平均成績，并按降序排列；
SELECT name,avg(Score) (SELECT name,course,score FROM student,course,scores WHERE student.StuID = scores.StuID and courses.CourseID = scores.CourseID) GROUP BY NAME ORDER BY avg(Score) DESC LIMIT 8;
4、顯示每門課程課程名稱及學習了這門課的同學的個數；
SELECT course,count(StuID) FROM (SELECT course,count(StuID) FROM scores GROUP BY CourseID) as a,courses WHERE m.courseID = courses.CourseID;
思考：
1、如何顯示其年齡大于平均年齡的同學的名字？
SELECT name,age FROM students WHERE age > (SELECT avg(age) FROM student);
2、如何顯示其學習的課程為第1、2，4或第7門課的同學的名字？
SELECT name FROM students as a,course as b WHERE a.CourseID in (1,2,4,7) and b.CourseID = a.CourseID;
3、如何顯示其成員數最少為3個的班級的同學中年齡大于同班同學平均年齡的同學？
SELECT name,age FROM (SELECT class,classID FROM students as s,classes as c WHERE s.ClassID GROUP BY CALSS having count(stuid) >= 3) as d,students as e,WHRER d.ClassID = e.ClassID and age > (SELECT avg(age) FROM students )
4、統計各班級中年齡大于全校同學平均年齡的同學。
SELECT name,age FROM students as a,classes as b WHERE a.classID = b.ClassID and age > (SELECT avg(age) FROM students);
============================================================
原創文章，作者：ldt195175108，如若轉載，請注明出處：http://www.www58058.com/59974
