關系型數據庫之Mysql查詢及數據庫管理(二)

    在關系型數據庫之Mysql編譯安裝及數據庫基礎(一)我們大致了解的數據庫的基本應用了，下面我們來聊聊MySQL的家常吧，在實際生產工作中我需要了解自己再數據庫領域應該選擇哪條道？這是們走向數據庫光明之路的前提，關于數據庫發展方向有開發DBA和管理DBA，它們分別需要哪些技能呢:

開發DBA:數據庫設計(E-R關系圖)、SQL開發、內置函數、存儲過程(存儲過程和存儲函數)、觸發器、事件調查器(even scheduler)

管理DBA:安裝、升級、備份、恢復、用戶管理、權限管理、監控、分析、基準測試、語句優化(SQL語句編寫必備)、數據字典、按需要配置服務器(服務器變量（必須掌握）:MyISAM,InnoDB,緩存，日志)

下面我們一步一步深入走向MySQL的世界:

SQL語言組成部分:

(1)DDL：數據定義語言

(2)DML：數據操作語言

(3)完整性定義語言:DDL的部分功能

  如:(約束)主鍵、外鍵、唯一鍵、條件、非空、事務

(4)視圖定義:虛表或臨時表，存儲下來是SELECT語句

(5)事務控制

(6)嵌入式SQL和動態SQL

(DCL)控制語言

數據類型的作用:

(1)存儲的值類型(比如:類型為字段 則無法存儲數據)

(2)占據的存儲空間(char(40))

(3)定長、變長(char,varchar)

(4)如何被索引及排序

(5)是否能夠被索引；(比如text,只能定位左邊一部分)

數據字典：系統編目 (system catalog) ---如花名冊一樣
保存數據庫服務器上的元數據

元數據：
       關系的名字
       每個關系的各字段的名字
       各字段的數據類型和長度（）
       約束
       每個關系上的視圖的名字及視圖的定義
       授權用戶的名字
       用戶的授權和賬戶信息

統計類的數據：
       每個關系字段的個數；
       每個關系中的行數；
       每個關系的存儲方法；

保存元數據的數據庫：
    information_schema
    mysql
    performance_shcema

數據類型：
    
字符型      
       char     ------固定的空間
       varchar  -------變化的空間，不區分大小寫
       tinytext  
       text  （作為對象存儲，不會直接存儲在表中，而是存放了指向其他表的指針）
       mediumtext
       longtext
       binary              ------------固定的空間
       varbinary            -------------變化的空間
       tinyblob
       blob （二進制大對象）
       mediumblob
       longblob

數值型     
       精確數值型  
                 tinyint
                 smallint
                 mediumint
                 int
                 bigint
                 decimal
             1 整型
             2 十進制數據：decimal（定點數，精確表示）
       近似數值型  (float,double)
              單精度浮點型
              雙精度浮點型              
              日期時間型
                        date
                        time
                        datetime
                        timestamp
                        year
                          
布爾型 （mysql 實際上沒有） 0和1
內建類型
       enum 枚舉
       set  集合

數據類型列表:

字符型常用的屬性修飾符：
not_null :非空約束
null       ：允許為空
default 'string' : 默認值，不使用于text類型
character set ‘字符集’     

示例:查詢字符集設置及服務器字符變量      

  mysql> show character set ;
  mysql> show variables like '%char%';

collation '規則'  ：排序規則

示例:查詢排序規則

  mysql> show collation;

auto_increment   : 自動增長
             前提：非空，且唯一:支持索引，非負值             
            UNSIGNED：無符號
            null
            not null
            default

浮點型常用修飾符
      notnull
        null
        default
       unsigned

日期時間型的修飾符
         notnuall
            null
           default

ENUM和SET 的修飾符 (enum枚舉，set 集合 --不適合排序)
            not null
             null
            default ''

MySQL SQL_MODE: SQL模式
                    TRADITIONAL, STRICT_TRANS_TABLES, or STRICT_ALL_TABLES
                    
設定服務器變量的值：（僅用于支持動態的變量）
支持修改的服務器變量：
     動態變量：可以Mysql運行時修改
     靜態變量：  與配置文件中修改其值，并重啟后方能生效；         

服務器變量從其生效范圍來講，有兩類：
       全局變量：服務器級別，修改之后僅對新建立的會話有效； global
       回話變量：會話級別，僅對當前回話有效；                        session
              會話建立時，從全局繼承各變量；

查看服務器變量 ：

 mysql> show {global|session} variables  like/where子句;

示例如下:

mysql> select @@{global|session}.variables_name;

示例如下:

mysql> select * from information_schema.global_variables WHERE VARIABLE_NAME='SOME_VARIABLE_NAME';
mysql> select * from information_schema.session_variables WHERE VARIABLE_NAME='SOME_VARIABLE_NAME';

示例如下:

修改變量
前提：默認僅管理員有權限修改全局變量    
mysql> SET {GLOBAL|SESSION} VARIABLE_NAME='VALUE';
 
注意：無論是全局還是會話級別的動態變量修改，在重啟mysqld后都會失效；想永久有效，可定義在配置文件中的相應段中[mysqld]；

MySQL中字符大小寫：
       1、SQL關鍵字及函數名不區分字符大小寫；
       2、數據庫、表及視圖名稱的大小區分與否取決于低層OS、FS
       3、存儲過程、存儲函數及時間調度器的名字不區分大小寫，但觸發器區分大小寫
       4、表別名區分大小寫；
       5、對字段中的數據，如果字段類型為Binary類型，則區分大小寫，非binary不區分大小寫；
                        
更改數據庫名沒有更好的辦法,只能備份好，然后新建；

數據庫操作：

創建數據庫:

mysql> CREATE {DATABASE|SCHEMA} [IF NOT EXISTS] db_name [DEFAULT] [CHARACTER SET=''] [DEFAULT] [COLLATE='']

示例如下:

刪除數據庫

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

示例如下:

修改數據庫

ALTER {DATABASE|SCHEMA} db_name [DEFAULT] [CHARACTER SET=''] [DEFAULT] [COLLATE='']

示例如下:

數據庫表管理：   

表創建：第一種方式              
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(create_definition,...)
[table_options]
[partition_options]

(create_definition,...)：
字段的定義：字段名、類型和類型修飾符
鍵、約束或索引：
PRIMARY KEY, UNIQUE KEY, FOREIGN KEY, CHECK
{INDEX|KEY}

[table_options]
    ENGINE [=] engine_name

查看mysql支持的引擎:

mysql> SHOW ENGINES;

    AUTO_INCREMENT [=] value
    [DEFAULT] CHARACTER SET [=] charset_name
    [DEFAULT] COLLATE [=] collation_name
    COMMENT [=] 'string'
    DELAY_KEY_WRITE [=] {0 | 1}
    ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
    TABLESPACE tablespace_name [STORAGE {DISK|MEMORY|DEFAULT}]

表分為兩種：
 MyISAM表，每表有三個文件，都位于數據庫目錄中；
             tb_name.frm  表結構定義
             tb_name.MYD 數據文件
             tb_name.MYI   索引文件

 InnoDB表 ，有兩種存儲方式
           1、默認：每表有一個獨立文件和一個共享的文件
             tb_name.frm :表結構的定義，位于數據庫目錄中；
            ibdata#:共享的表空間文件，默認位于數據目錄（datadir指向的目錄）
           2、獨立的表空間文件
               tb_name.frm  每表有一個表結構文件
               tb_name.ibd   一個獨有的表空間文件
配置啟用InnoDB引擎獨立表空間:

#vim /etc/my.cnf
innodb_file_per_table = on

示例如下:查看特定表的信息--如查看使用的存儲引擎

表創建：第二種方式（復制表數據不復制表結構）
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [(create_definition,...)]
    [table_options]
    select_statement

示例如下:

表創建：第三種方式（復制表結構）
    CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    { LIKE old_tbl_name | (LIKE old_tbl_name) }

示例如下:

表刪除：
mysql> drop table table_name; 刪除表名為xxx的表
表中的數據刪除:

mysql> delete from table_name where 字段名='values'

如 DELETE FROM Test1 WHERE Name='samlee'
清空表數據:

mysql> truncate table_name

truncate是一個能夠快速清空資料表內所有資料的SQL語法。并且能針對具有自動遞增值的字段。

表修改：

語法格式:alter table tab1_name

   修改字段定義：alter
   添加新字段（add）

  (after)可定義字段排序規則

示例如下:

mysql> use mydb;
mysql> CREATE TABLE Test5(ID int(10) UNSIGNED auto_increment NOT NULL,PRIMARY KEY(ID));
mysql> ALTER TABLE Test5 ADD Age TINYINT UNSIGNED NOT NULL;
mysql> ALTER TABLE Test5 ADD Gender ENUM('M','F') NOT NULL DEFAULT 'M' AFTER ID;
mysql> ALTER TABLE Test5 ADD Name char(10) NOT NULL ;

刪除字段(DROP)

mysql> ALTER TABLE Test5 DROP Age;

示例如下:

修改字段-->

修改字段名稱(change)

mysql> ALTER TABLE Test5 CHANGE Name StuName CHAR(30) NOT NULL;

示例如下:

修改字段類型及屬性(modify)

mysql> ALTER TABLE Test5 MODIFY Gender ENUM('M','F') NOT NULL AFTER ID;

修改約束、鍵或索引
alter下在表中特定的字段上創建索引index(add)

mysql> ALTER TABLE Test5 ADD INDEX (StuName);

查看某表中的索引index

mysql> SHOW INDEXES FROM Test5\G;

刪除表中特定字段index (drop)

mysql> ALTER TABLE Test5 DROP INDEX StuName;

修改表名(rename)

格式如下:

>RENAME TABLE  old_tablename TO new_tablename

修改表的存儲引擎 

格式如下:

>alter table table_name engine=MyISAM

示例如下:

mysql> ALTER TABLE Test5 engine=MyISAM;
mysql> SHOW TABLE STATUS LIKE 'Test5'\G;

擴展Tips:

1.指定排序標準的字段：

ORDER BY col_name [, col_name] ...

2.轉換字符集及排序規則：

CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]

3.表選項修改：

[table_options]
    ENGINE [=] engine_name
    mysql> SHOW ENGINES;
    AUTO_INCREMENT [=] value
    [DEFAULT] CHARACTER SET [=] charset_name
    [DEFAULT] COLLATE [=] collation_name
    COMMENT [=] 'string'
    DELAY_KEY_WRITE [=] {0 | 1}
    ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
    TABLESPACE tablespace_name [STORAGE {DISK|MEMORY|DEFAULT}]

階段測試:

新建如下表（包括結構和內容）：
ID    Name          Age     Gender     Course
1     Ling Huchong   24      Male       Hamogong
2     Huang Rong    19      Female     Chilian Shenzhang
3     Lu Wushaung   18      Female     Jiuyang Shenggong
4     Zhu Ziliu     52      Male       Pixie Jianfa
5     Chen Jialuo   22      Male       Xianglong Shiba Zhang
6      Ou Yangfeng   70      Male       Shenxiang Bannuo Gong
---------------------------------------------------------------------
##創建上表并插入數據
mysql> CREATE TABLE student(ID smallint not null primary key,Name varchar(40) not null,Age tinyint unsigned not null,Gender ENUM('F','M') not null default 'M',Course varchar(40) not null);
mysql> INSERT INTO student(ID,Name,Age,Gender,Course) value (1,'Ling Huchong',24,'M','Hamogong');
mysql> INSERT INTO student(ID,Name,Age,Gender,Course) value (2,'Huang Rong',19,'F','Chilian Shenzhang');
mysql> INSERT INTO student(ID,Name,Age,Gender,Course) value (3,'Lu Wushuang',18,'F','Jiuyang Shenggong');
mysql> INSERT INTO student(ID,Name,Age,Gender,Course) value (4,'Zhu Ziliu',52,'M','Pixie Jianfa');
mysql> INSERT INTO student(ID,Name,Age,Gender,Course) value (5,'Chen Jialuo',22,'M','Xianglong Shiba Zhang');
mysql> INSERT INTO student(ID,Name,Age,Gender,Course) value (6,'Ou Yangfeng',52,'M','Shenxiang Bannuo Gong');

(1)新增字段：Class 字段定義自行選擇；放置于Name字段后;

mysql> alter table student add Class tinyint not null after Name;

(2)將ID字段名稱修改為SID;

mysql> ALTER TABLE student change ID SID smallint not null;

(3)將SID字段放置最后;

mysql> ALTER TABLE student MODIFY SID smallint not null AFTER Class;

MySQL的查詢操作

select 查詢選擇和投影操作查詢:

投影：挑選要顯示的字段

投影：SELECT 字段1, 字段2, ... FROM tb_name;    #選定顯示指定字段
    SELECT * FROM tb_name;                    #顯示所有字段

選擇：挑選符合條件的行

選擇：SELECT 字段1, ... FROM tb_name WHERE 子句[布爾條件表達式];

布爾條件表達式操作符：

*#: =   等于
*#: <=>  abc = bde ,abc =NULL
*#: <>  不等于
*#: <   小于
*#: <=  小于等于
*#: >   大于
*#: >=  大于等于

通過案例理解MySQL查詢:

創建案例環境:

mysql> CREATE TABLE students (SID INT UNSIGNED AUTO_INCREMENT NOT NULL UNIQUE KEY,Name CHAR(30) NOT NULL, Age TINYINT UNSIGNED NOT NULL,Gender ENUM('F','M') NOT NULL,Tutor CHAR(30),ClassID TINYINT UNSIGNED);
mysql> INSERT INTO students VALUES (1,'Guo Jing',27,'M','Hong qigong',2),(2,'Yang Guo',28,'M','Ou Yangfeng',3),(3,'Qiao feng',21,'M','Ling Huchong',3);
mysql> INSERT INTO students VALUES (4,'Xue Baochai',19,'F','Rong Sir',1),(5,'Xia Yuhe',37,'F','Shi Qian',2),(6,'Wu Yong',51,'M','Lin Daiyu',1);
mysql> INSERT INTO students VALUES (7,'Tom',11,'M','Jerry',1),(8,'Tomy','13','M',NULL,4);

1. 查詢students表Tutor為空的記錄
   

mysql> SELECT * FROM students WHERE Tutor IS NULL;

2.查詢students表Tutor不為空的記錄

mysql> SELECT * FROM students WHERE Tutor IS NOT NULL;

模糊查詢:

LIKE: 支持的通配符  
%: 任意長度的任意字符 （模糊查找，性能比較低）
_ : 任意單個字符

RLIKE，REGEXP :支持使用正則表達式  (模糊查找，性能比較低)

3.查詢姓名以X開頭的記錄

mysql> SELECT * FROM students WHERE Name LIKE 'X%';

或使用RLIKE

mysql> SELECT * FROM students WHERE Name RLIKE '^X.*';

4.查詢students表年齡為25、26、27、28的記錄(使用IN: 判斷指定字段是否在給定的列表中):

mysql> SELECT *  FROM students WHERE Age IN (25,26,27,28);

5.查詢年齡為25至40的記錄

mysql> SELECT *  FROM students WHERE Age BETWEEN 25 AND 40;

組合條件測試：

NOT, !  :否定
AND, && ：并且
OR, ||  ：或者

6.查詢SID為"5" 性別為"F"的記錄

mysql> SELECT *  FROM students WHERE SID = '5' AND Gender = 'F';

7.查詢Age為'28' 或 Gender為'F'的記錄

mysql> SELECT *  FROM students WHERE Age = '28' OR Gender = 'F';

查詢排序:

ORDER BY
    (ASC 升序顯示)

8.查詢Age為'28' 或 Gender為'F'的記錄,按Age數值從小到大排序

mysql> SELECT *  FROM students WHERE Age = '28' OR Gender = 'F' ORDER BY Age;

(DESC 降序顯示)

9.查詢Age為'28' 或 Gender為'F'的記錄,按Age數值從大到小排序

mysql> SELECT *  FROM students WHERE Age = '28' OR Gender = 'F' ORDER BY Age DESC;

聚合函數  sum（），avg（），max（），min( ) ...
                總和         平均值      最大值   最小值
10.計算學生所有學生的平均年齡

mysql> SELECT SUM(Age) FROM students;

11.計算學生年齡的最大數值

mysql> SELECT MAX(Age) FROM students;

12.統計表的記錄數

mysql> SELECT COUNT(Age) FROM students;

13.計算學生年齡的最小數值

mysql> SELECT MIN(Age) FROM students;

14.查詢年齡大于30的，所有人的平均年齡之和

mysql> SELECT SUM(Age) FROM students WHERE Age > 30;

group by  -->分組
having     --> 聚合

15.以下操作在students表上執行
以ClassID分組，顯示每班的同學的人數；

mysql> SELECT ClassID,COUNT(Age) FROM students GROUP BY ClassID;

16.以Gender分組，顯示其年齡之和；

mysql> SELECT Gender,SUM(Age) FROM students GROUP BY Gender;

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

mysql> SELECT ClassID,AVG(Age) FROM students GROUP BY ClassID HAVING AVG(age) > 25;

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

mysql> SELECT Gender,SUM(Age) FROM  students WHERE Age > 25 GROUP BY Gender;

19.使用LIMIT顯示指定行(有效處理大數據過濾查詢操作，減少系統性能消耗)

mysql> SELECT * FROM students LIMIT 2,3;

mysql> SELECT * FROM students LIMIT 4;

擴展Tips:

(1)導出數據庫文件:

# mysqldump -uroot -hlocalhost -predhat mydb > mydb.sql

(2)導入數據庫文件

# mysql -uroot -hlocalhost -prehdat < mydb.sql

SELECT語句的執行流程：

FROM clause --> WHERE clause --> GROUP BY --> HAVING clause --> ORDER BY ... --> SELECT --> LIMIT

SELECT語句:

DISTINCT：指定的結果相同的只顯示一次；
SQL_CACHE：緩存于查詢緩存中；
SQL_NO_CACHE：不緩存查詢結果；

MySQL多表查詢和子查詢

導入hellodb.sql 以下操作在students表上執行；

#mysql -uroot -hlocalhost -predhat < hellodb.sql

聯結查詢：事先將兩張或多張表join，根據join的結果進行查詢；

(1)cross join:交叉聯結

#第一張表行數*第二張表行數=總行數(很少用)

mysql> SELECT students.Name,classes.Class FROM students,classes WHERE students.ClassID = classes.ClassID;

(2)自然連接(僅能夠在兩者相等的情況下才能建立聯結)--內連接

等值連接

條件比較

(3)外連接

左外連接:只保留出現在左外連接運算之前(左邊)的關系中的元組--以左邊的元素為準，右邊沒有則null

#left_tb LEFT JOIN right_tb ON 連接條件

mysql> SELECT s.Name,c.Class FROM students AS s LEFT JOIN classes AS c ON s.ClassID = c.ClassID;

右外連接：只保留出現在右外連接運算之后（右邊）的關系中的元組；
 #left_tb RIGHT JOIN right_tb ON 連接條件

mysql> SELECT s.Name,c.Class FROM students AS s RIGHT JOIN classes AS c ON s.ClassID = c.ClassID;

(4)自連接

mysql> SELECT t.Name,s.Name FROM students AS s,students AS t WHERE s.StuID = t.TeacherID;

Tips：

AS字段別名:

mysql> SELECT Name AS SamleeName FROM students;

子查詢：在查詢中嵌套的查詢
用于where中的子查詢
   1、用于比較表達式中的子查詢
            子查詢的返回值只能有一個
   2、用于exists中的子查詢
             判斷是否存在；
   3、用于IN中的子查詢；
              判斷存在于指定列表中
用于from子句的子查詢

(1)子查詢（1）用在where中

mysql> SELECT s.Name,s.Age,s.Gender FROM (SELECT * FROM students WHERE Gender='M') AS s WHERE s.Age > 25;

(2)子查詢（2） 用在from中

mysql> SELECT Name,Age FROM students WHERE Age > (SELECT AVG(Age) FROM students);

以上為關系型數據庫之Mysql查詢及數據庫管理(二)所有內容