MySQL基礎語句【學習筆記】

標籤：des   io   os   ar   使用   for   strong   sp   檔案   

放在這裡，以備後查。

 

       
       1. 資料庫， 資料庫伺服器， 資料庫語言

                 資料庫，是持久性資料的集合，供給定企業的應用程式系統使用，並且由一個資料庫管理系統來管理；

                 資料庫伺服器，又稱資料庫管理系統，用來管理資料庫（高效地儲存、查詢、更新資料庫，並維護資料庫的完整性狀態）；
 
                 資料庫語言，是應用程式用來向資料庫伺服器發送命令並從中取出所需要資料的特定語言。

 

       2.  系統變數

 

                 查詢系統變數： SELECT @@系統變數名 ， eg. SELECT @@DATADIR
                 顯示所有系統變數： SHOW VARIABLES ， SHOW VARIABLES LIKE ‘pattern‘ 
                 設定系統變數： SET @@GLOBAL(LOCAL,SESSION).varname=值 ； 
           

                 (1) 資料庫目錄： DATADIR  ;    (2) 日誌： LOG_WARNINGS
                 (3) 最大使用者串連數： MAX_USER_CONNECTIONS ;    (4) 版本： VERSION  

          3.   系統資料庫和表
             

                  (1)  MYSQL.USER : 存放資料庫使用者登陸資訊的表。
                  (2)  INFORMATION_SCHEMA : 存放目錄資料的資料庫

       4.   查看資料庫詳細資料：   HELP SHOW ；SHOW WARNINGS ； SHOW STATUS ；
       
       5.  mysql 基本語句：

       (1) 建立和查看使用者、許可權、資料庫      
 
            建立使用者： CREATE USER ‘username‘@‘hostname‘ IDENTIFIED BY ‘password‘

            建立資料庫： CREATE DATABASE database_name ；

 

               授予許可權：GRANT 【操作許可權，比如，select, update, delete, all】 ON database_name.table_name to ‘username‘@‘host‘
                查看許可權： SHOW GRANTS ； 查看資料庫建立定義：SHOW CREATE DATABASE database_name ；   

               

                顯示目前使用者所擁有的所有資料庫： show databases ;    選擇要使用的資料庫： USE database_name ; 
                顯示當前資料中的所有表： show tables ;   

       (2) 建立表和表操作

           建立表： CREATE TABLE table_name ( 屬性名稱1 屬性類型1 約束1，...  屬性名稱n 屬性類型n 約束n, PRIMARY KEY(屬性名稱i,j,...,p)) ENGINE=引擎名 DEFAULT CHARACTER SET 字元集名 COLLATE 校對名

           A. 約束： 是否允許為空白值(NOT NULL, 預設允許空值)；是否有預設值(DEFAULT 預設值)
           B. 主索引值： 若不想指定，則用 NULL 代替， 由系統自動產生相應值進行填充
           C. 多個列作為主鍵： 使用逗號隔開的多個列名的列表
           D. 引擎類型：InnoDB(可靠交易處理)， MyISAM(效能高，支援全文檢索搜尋，但不支援交易處理), MEMORY(速度特別快)   
           E. 指明所使用字元集；可以對單個列進行指定。

           查看錶建立定義： SHOW CREATE TABLE 表名； 查看錶的欄位定義： desc table_name ; 

           重新命名表：  RENAME TABLE 舊錶名 TO 新表名

           更改表結構： 

           ALTER TABLE 表名    (ADD 列名 列名類型) [添加列]   |    (DROP COLUMN 列名) [刪除已有列]    |
           (ADD CONSTRAINT 約束名 FOREIGN KEY (外鍵名) REFERENCES 外鍵所在表名 (外鍵所在表的相應主鍵名))[添加外鍵]

           查看錶建立定義： SHOW CREATE TABLE 表名； 查看錶的欄位定義： desc table_name ;

           插入資料：  INSERT INTO table_name values (資料1, 資料2，... 資料n) ； 插入資料必須與對應屬性名稱的屬性類型匹配

           刪除資料：  DELETE FROM table_name WHERE CONDITIONS

           更新資料：  UPDATE table_name SET 屬性名稱=新值 WHERE CONDITIONS 

           建立索引：  ADD （UNIQUE） INDEX 索引名 ON 表名（屬性名稱）

           建立視圖：  CREATE VIEW 視圖名（屬性名稱i, ... 屬性名稱j） AS   SELECT 查詢語句       

       (3) 刪除操作       

             刪除資料庫： DROP DATABASE database_name ; 
 
             刪除視圖： DROP VIEW 視圖名

             刪除表：   DROP TABLE 表名

             刪除索引： DROP INDEX 索引名



   6.  查詢與過濾資料
      

            表定義[見 mysql 必知必會]：
            a. 客戶表 customers: cust_id(PK), cust_name, cust_email, others
            b. 供貨商表： vendors: vend_id(PK), vend_name, vend_country, others 
            c. 產品表 products: prod_id(PK), vend_id(FK), prod_name, prod_price, prod_desc
            d. 客戶訂單表 orders: order_num(PK), order_date, cust_id(FK)
            e. 訂單資訊表 orderitems: (order_num, order_item)(PK), prod_id(FK), quantity, item_price

 

 

(1) 基本查詢與過濾： 
 
       SELECT   [DISTINCT]   [OP1]      FROM   [OP2]

                   WHERE   [COND_CLAUSE] 

                   ORDER BY [  OP3]   <DESC>

                   LIMIT offset, lineNum

       ---> OP1: 單個列名或列名運算式，或多個用逗號隔開的列名或列名運算式。
       ---> OP2：一個或多個表名，用逗號隔開 ；
       ---> OP3: 一個或多個列名，用逗號隔開； 
       ---> ORDER BY: 對檢索結果排序，按照指定列名順序依次排序；預設升序排列；DESC 指明降序排列；
       ---> LIMIT offset, lineNum : 從第 offset 行 [下標從零數起] 開始的 lineNum 行； 若行數不足 lineNum, 則檢索能夠得到的最大行數。
       ---> COND_CLAUSE: 由一個或多個條件子句組成。
            -----> 條件子句結構為 ‘表名.列名 操作符 值或值集‘ 
            -----> 空值查詢： IS NULL；
            -----> 集合操作符：IN (值的集合) ； 表示僅在括弧中給定的值集中取值；
            -----> 通配操作符：LIKE [BINARY] ‘text‘ ; text 為萬用字元文本。 
                             萬用字元： % 任意多個任一字元 ； _ 任意單個字元   
            -----> Regex：REGEXP [BINARY] ‘regexText‘ ; regexText 為Regex文本  
                        Regex： . 匹配任意單個字元 ； OR | 或 ；範圍匹配 [0-9], [a-zA-Z] ;

                                                   //c , // 轉義符，比如匹配字元點號 //. ;
                         * 零個或多個 ； + 一個或多個 ； {n} 恰好 n 個 ；{n,} N>=n ; {n,m}  n<=N<=m
                         ^ 文本開始 ； $ 文本末尾 
            -----> 可以使用邏輯操作符 AND, OR 將多個條件子句串連起來，形成多重過濾條件。

                     AND 優先順序高於 OR ； 為確保正確的次序，盡量多使用括弧來表明優先順序;

                     NOT 可對條件字句的結果取反（F->T, T->F）。   
               ---> 關鍵字： BINARY 搜尋區分大小寫 ； DISTINCT 去除重複行
    
       (2) 產生新欄位： 上述查詢語句中，[OP1] 還可以是任何合法的列名運算式：
               

                A. 由多個列名及字串拼接而成的欄位。 例如 SELECT Concat(列名1, ‘ *** ‘, 列名2) FROM 表名.
                B. 列名的四則運算。 例如 SELECT (prod_price * quantity) FROM items;             

                C. 列名的函數。 例如， SELECT Concat(YEAR(order_date), ‘/‘, Month(order_date)) FROM orders;                   
                D. AS alias_name： 可以用來給新產生的欄位命名。 
       
      (3) 分組資料： 可以在WHERE 字句後加入分組子句 ； WHERE 子句在分組前過濾資料， HAVING 子句在分組後過濾資料。
          SELECT [OP1]   FROM [OP2]   [WHERE 子句]   GROUP BY   一個或多個列名   HAVING 條件子句

      (4) 子查詢： 子查詢可以用來替代任何有值或值集的地方，尤其是與 in 連用。例如
          SELECT cust_name FROM customers

                   WHERE cust_id   IN 

                   ( SELECT cust_id FROM orders WHERE Date(order_date) BETWEEN ‘2005-09-01‘ AND ‘2005-09-30‘) ;

      (5) 表連接： 

 

          A. 基本的表連接： 主要是使用笛卡爾乘積和 WHERE 子句來進行。 例如，找出所有的產品名稱及供貨商名稱：
              

               SELECT prod_name , vend_name FROM products, vendors Where products.vend_id = vendors.vend_id;  或者
               SELECT prod_name , vend_name FROM products INNER JOIN vendors on products.vend_id = vendors.vend_id; 
          
          B. 多個表連接： 方法不變，為了避免出錯，可以先在文字檔把語句寫好，再複製粘貼過去以檢驗。

               例如，找出客戶所下訂單的資訊：

               SELECT cust_name, orders.order_num, prod_name, vend_name, quantity, prod_price,   item_price*quantity AS order_price
               FROM customers, vendors, orders, products, orderitems 
                     WHERE    products.prod_id = orderitems.prod_id
                          AND    orderitems.order_num = orders.order_num
                          AND    customers.cust_id = orders.cust_id
                          AND    products.vend_id = vendors.vend_id;
   
                技巧： 分三步進行： a. 在 SELECT 中在多個表中選擇想要顯示的列名或構造列名運算式； 
                                               b. 在 FROM 中列出所有涉及到的表名； 
                                               c. 根據表中的外鍵及WHERE相等子句條件建立連接。

 

           C. 自連接： 在單個 SELECT中 多次引用和連接同一張表，需要使用表別名來消除歧義性。
 
                例如，查詢生產產品ID為 ‘DTNTR‘ 的供應商生產的其它產品：         

                   SELECT p1.vend_id, p1.prod_id FROM products AS p1, products AS p2
                   WHERE p1.vend_id = p2.vend_id AND p2.prod_id = ‘DTNTR‘

          D. 外連接：在自然連接的基礎上附加沒有被關聯的行（請自行查閱相關資料庫理論書籍） 

                   SQL： SELECT ... FROM 表名1 LEFT[RIGHT] OUTER JOIN 表名2 ON ...
                       
       (6) 組合查詢： 將多個 SELECT 查詢組合成單個查詢結果
          
                   ----> SQL ： SELECT ... UNION [ALL] SELECT ... [ORDER BY DESC]
                   ----> UNION ALL: 包含多個查詢結果中重複出現的行；預設是不包含重複行的；
                   ----> ORDER BY: 必須在最後一個 SELECT 語句之後，對整個組合查詢的結果進行排序。

  
   7.  資料庫維護：

       (1) 執行SQL檔案： /. <filename> 或者 source <filename> ; mysql -u username -p database_name < xxx.sql

       (2) 備份和恢複資料表： select * into outfile ‘/var/lib/mysql/user.bak‘ from tblname;

                                                  load data infile ‘/var/lib/mysql/user.bak‘ replace into table tblname ;

      (3) 備份和恢複資料庫或表：                                                 

 

                                     mysqldump -uxtools -h127.0.0.1 -pxtool dbname tablename > /tmp/tbl.bak.sql

MySQL基礎語句【學習筆記】