Mariadb

結構化數據–>關系型數據庫 范式：Entry（每一行來描述一個整體）

半結構化數據–>YAML,XML,JSON 非結構化數據–>日志文件 NoSQL

關系型數據庫：事務能力 ACID測試（原子性，一致性，隔離性，持久性）

MariaDB or MySQL: 層次模型–>網狀模型–>（Codd）關系模型

DBMS（數據管理系統） --> 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： 5.1–>5.5–>5.6–>5.7–>8.0

Mariadb:5.5.x-->10.x

特性：
    插件式存儲引擎
    單進程多線程

安裝Mysql：
        OS Vendor：rpm 
        MySQL：
            source code：cmake
            binary package：
                i686, x86_64; 
                glibc VERSION
            prepackage：rpm, deb
                os, arch, 

        服務端程序：
            mysqld, mysqld_safe, mysqld_multi
        客戶端程序：
            mysql, mysqldump, mysqlbinlog, mysqldmin, ...
        非客戶端類程序：
            myisamchk, myisampack, ...                  

配置文件：
    讀取多處的多個配置文件，而且會以指定的次序的進行；

        # my_print_defaults

            Default options are read from the following files in the given order:
            /etc/mysql/my.cnf  /etc/my.cnf ~/.my.cnf 

            不同的配置文件中出現同一參數且擁有不同值時，后讀取將為最終生效值； 

            修改默認讀取的配置文件（mysqld_safe命令）：
                 --defaults-file=file_name

            于讀取的默認配置文件之外再加載一個文件：    
                --defaults-extra-file=path

        配置文件格式：ini風格的配置文件，能夠為mysql的各種應用程序提供配置信息：
            [mysqld]
            [mysqld_safe]
            [mysqld_multi]
            [server]
            [mysql]
            [mysqldump]
            [client]
            ...

                PARAMETER = VALUE 

                PARAMETER：
                    innodb_file_per_table
                    innodb-file-per-table 

        程序文件：
            服務端程序：mysqld_safe, mysqld_multi
            客戶端程序：mysql, mysqldump, mysqladmin
            工具程序：myisampack, ...

        mysql --> mysql protocol --> mysqld 
            mysql：交互式CLI工具；

            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：查詢結果縱向顯示；

                命令：
                    客戶端命令：于客戶端執行；
                    服務端命令：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.   

                mysql命令的使用幫助：
                    # man mysql
                    # mysql  --help  --verbose

            sql腳本運行：
                mysql [options] [DATABASE] < /PATH/FROM/SOME_SQL_SCRIPT

        mysqld服務器程序：工作特性的定義方式
            命令行選項
            配置文件參數

            服務器參數/變量：設定MySQL的運行特性；
                mysql> SHOW GLOBA|[SESSION] VARIABLES [LIKE clause];
            like后面字符串要用引號引起來

            SHOW GLOBAL VARIABLES;
            查看全局的服務器變量，定義服務器的工作特性的

            只想查看Threads開頭的狀態變量

            狀態（統計）參數/變量：保存MySQL運行中的統計數據或狀態數據；
                mysql> SHOW GLOBA|[SESSION] STATUS [LIKE clause];

            顯示單個變量設定值的方法：
                mysql> SELECT @@[global.|session.]system_var_name


                    %：匹配任意長度的任意字符；
                    _：匹配任意單個字符；

            變量/參數級別：
                全局：為所有會話設定默認； 
                會話：跟單個會話相關；會話建立會從全局繼承；

            服務器變量的調整方式：
                運行時修改：
                    global：僅對修改后新建立的會話有效；
                    session：僅對當前會話有效，且立即生效；
                啟動前通過配置文件修改：
                    重啟后生效；

            運行時修改變量值操作方法：
                mysql> HELP SET

                SET [GLOBAL | SESSION] system_var_name = expr
                SET [@@global. | @@session. | @@]system_var_name = expr

把autocommit的值改為0

方法一

方法二

改全局級別不影響當前會話，但是對新建立的回話全局級別時生效的

        安裝完成后的安全初始化：
            mysql_secure_installation

        運行前常修改的參數：
            innodb_file_per_table=ON
            skip_name_resolve=ON
            ...


    SQL：ANSI SQL 
        SQL-86, SQL-89, SQL-92, SQL-99, SQL-03, ...

    MySQL的數據類型：
        字符型
        數值型
        日期時間型
        內建類型

        字符型：
            CHAR(#)， BINARY：定長型；CHAR不區分字符大小寫，而BINARY區分；
            VARCHAR(#)， VARBINARY：變長型
            TEXT：TINYTEXT，TEXT，MEDIUMTEXT，LONGTEXT
            BLOB：TINYBLOB，BLOB，MEDIUMBLOB， LONGBLOB
        數值型：
            浮點型：近似
                FLOAT
                DOUBLE
                REAL
                BIT
            整型：精確
                INTEGER：TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT
                DECIMAL
        日期時間型：
            日期：DATE
            時間：TIME
            日期j時間：DATETIME
            時間戳：TIMESTAMP
            年份：YEAR(2), YEAR(4)
        內建：
            ENUM：枚舉
                ENUM('Sun','Mon','Tue','Wed')
            SET：集合

        類型修飾符：
            字符型：NOT NULL，NULL，DEFALUT ‘STRING’，CHARACET SET ‘CHARSET’，COLLATION ‘collocation'
            整型：NOT NULL， NULL， DEFALUT value, AUTO_INCREMENT, UNSIGNED
            日期時間型：NOT NULL， NULL， DEFAULT 

    SQL MODE：定義mysqld對約束等違反時的響應行為等設定；
        常用的MODE：
            TRADITIONAL
            STRICT_TRANS_TABLES
            STRICT_ALL_TABLES
不應該默認使用空模式

        修改方式：
            mysql> SET GLOBAL sql_mode='MODE';
            mysql> SET @@global.sql_mode='MODE';

CREATE TABLE tbl1 (id tinyint unsigned,name CHAR(5)) 創建一個表，定義一個字段為id，另一個字段是name，只能有五個字符

MariaDB [testdb]> CREATE TABLE tbl3 (id tinyint unsigned,name char(6));

MariaDB [testdb]> INSERT INTO tbl3 (id) VALUES (16),(256);

超過的字符被修剪了，違反了規則后，正常插入沒問題，但會有錯誤。插入時會發生警告。一旦違反約束規則的話，應該拒絕數據插入。

這時候定義一種功能

默認mysql定義在空模式下，TRADITIONAL,嚴格遵守規則，不然無法定義

這時候就會提示插入數據錯誤；

    SQL：DDL，DML
        DDL：
            mysql> HELP Data Definition

            CREATE, ALTER, DROP
                DATABASE, TABLE
                INDEX, VIEW, USER
                FUNCTION, FUNCTION UDF, PROCEDURE, TABLESPACE, TRIGGER, SERVER 

        DML:
            mysql> HELP Data Manipulation

            INSERT/REPLACE, DELETE, SELECT, UPDATE

        數據庫：
            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

        表：
            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]

        索引：
            創建：
                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：分析查詢語句的執行路徑；

        視圖：VIEW
            虛表：存儲下來的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] ...

    DML：
        INSERT/REPLACE，DELETE，UPDATE，SELECT

        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] ... ]

        DELETE：

            DELETE  FROM tbl_name [WHERE where_condition] [ORDER BY ...] [LIMIT row_count]      

            注意：一定要有限制條件，否則將清空整個表；
                限制條件：
                    [WHERE where_condition]
                     [ORDER BY ...] [LIMIT row_count]

        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變量可阻止不帶條件更新操作；

    SELECT：

        Query Cache：緩存查詢的執行結果；
            key：查詢語句的hash值； 
            value：查詢語句的執行結果；

            SQL語句的編寫方式：
                SELECT name FROM tbl2;
                select name from tbl2;

        查詢執行路徑：
            請求-->查詢緩存
            請求-->查詢緩存-->解析器-->預處理器-->優化器-->查詢執行引擎-->存儲引擎-->緩存-->響應


        SELECT語句的執行流程：
            FROM  --> WHERE --> Group By --> Having --> Order BY --> SELECT --> Limit 

            先找到執行的表，用where做條件過濾，就能拿到子集，對這個子集要拿到分組。做聚合計算（最大值，平均值）。把聚合都的結果指定過濾條件用having，having用來描述group by的結果如何被過濾的。order by排序，select執行，limit顯示。

挑選行

既能挑選字段，又能挑選行；

添加一個列，性別

往新加的列上賦值

顯示F性別的平均值，和M性別的平均值

聚合后的結果指明過濾條件；

        單表查詢：

            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, ...

            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：對分組聚合后的結果進行條件過濾；

            ORDER BY：根據指定的字段把查詢的結果進行排序；
                升序：ASC
                降序：DESC 

            LIMIT：對輸出結果進行數量限制
                [LIMIT {[offset,] row_count | row_count OFFSET offset}]





        練習：導入hellodb.sql生成數據庫
            (1) 在students表中，查詢年齡大于25歲，且為男性的同學的名字和年齡；

            (2) 以ClassID為分組依據，顯示每組的平均年齡；

            (3) 顯示第2題中平均年齡大于30的分組及平均年齡；

            (4) 顯示以L開頭的名字的同學的信息；

            (5) 顯示TeacherID非空的同學的相關信息；

            (6) 以年齡排序后，顯示年齡最大的前10位同學的信息；

            (7) 查詢年齡大于等于20歲，小于等于25歲的同學的信息；用三種方法；

        練習：導入hellodb.sql，以下操作在students表上執行
            1、以ClassID分組，顯示每班的同學的人數；

            2、以Gender分組，顯示其年齡之和；

            3、以ClassID分組，顯示其平均年齡大于25的班級；

            4、以Gender分組，顯示各組中年齡大于25的學員的年齡之和；

        多表查詢：
            連接操作：
                交叉連接：笛卡爾乘積；
                內連接：
                    等值連接：讓表之間的字段以等值的方式建立連接；

                    不等值連接：
                    自然連接
                    自連接
                外連接：
                    左外連接：
                        FROM tb1 LEFT JOIN tb2 ON tb1.col = tb2.col 
                    右外連接：
                        FROM tb1 RIGHT JOIN tb2 ON tb1.col = tb2.col 

        子查詢：在查詢中嵌套查詢；

            用于WHERE子句中的子查詢；
                (1) 用于比較表達式中的子查詢：子查詢僅能返回單個值； 
                (2) 用于IN中的子查詢：子查詢可以返回一個列表值； 
                (3) 用于EXISTS中的子查詢：
            用于FROM子句中的子查詢；
                SELECT tb_alias.col1, ... FROM (SELECT clause) AS tb_alias WHERE clause; 

        聯合查詢：將多個查詢語句的執行結果相合并；
            UNION 
                SELECT clause UNION SELECT cluase；


        練習：導入hellodb.sql，完成以下題目：
            1、顯示前5位同學的姓名、課程及成績；

            2、顯示其成績高于80的同學的名稱及課程；

            3、求前8位同學每位同學自己兩門課的平均成績，并按降序排列；

            4、顯示每門課程課程名稱及學習了這門課的同學的個數；