關(guān)系型數(shù)據(jù)庫(kù)之Mysql查詢及數(shù)據(jù)庫(kù)管理(二)

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

開發(fā)DBA:數(shù)據(jù)庫(kù)設(shè)計(jì)(E-R關(guān)系圖)、SQL開發(fā)、內(nèi)置函數(shù)、存儲(chǔ)過(guò)程(存儲(chǔ)過(guò)程和存儲(chǔ)函數(shù))、觸發(fā)器、事件調(diào)查器(even scheduler)

管理DBA:安裝、升級(jí)、備份、恢復(fù)、用戶管理、權(quán)限管理、監(jiān)控、分析、基準(zhǔn)測(cè)試、語(yǔ)句優(yōu)化(SQL語(yǔ)句編寫必備)、數(shù)據(jù)字典、按需要配置服務(wù)器(服務(wù)器變量（必須掌握）:MyISAM,InnoDB,緩存，日志)

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

SQL語(yǔ)言組成部分:

(1)DDL：數(shù)據(jù)定義語(yǔ)言

(2)DML：數(shù)據(jù)操作語(yǔ)言

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

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

(4)視圖定義:虛表或臨時(shí)表，存儲(chǔ)下來(lái)是SELECT語(yǔ)句

(5)事務(wù)控制

(6)嵌入式SQL和動(dòng)態(tài)SQL

(DCL)控制語(yǔ)言

數(shù)據(jù)類型的作用:

(1)存儲(chǔ)的值類型(比如:類型為字段 則無(wú)法存儲(chǔ)數(shù)據(jù))

(2)占據(jù)的存儲(chǔ)空間(char(40))

(3)定長(zhǎng)、變長(zhǎng)(char,varchar)

(4)如何被索引及排序

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

數(shù)據(jù)字典：系統(tǒng)編目 (system catalog) ---如花名冊(cè)一樣
保存數(shù)據(jù)庫(kù)服務(wù)器上的元數(shù)據(jù)

元數(shù)據(jù)：
       關(guān)系的名字
       每個(gè)關(guān)系的各字段的名字
       各字段的數(shù)據(jù)類型和長(zhǎng)度（）
       約束
       每個(gè)關(guān)系上的視圖的名字及視圖的定義
       授權(quán)用戶的名字
       用戶的授權(quán)和賬戶信息

統(tǒng)計(jì)類的數(shù)據(jù)：
       每個(gè)關(guān)系字段的個(gè)數(shù)；
       每個(gè)關(guān)系中的行數(shù)；
       每個(gè)關(guān)系的存儲(chǔ)方法；

保存元數(shù)據(jù)的數(shù)據(jù)庫(kù)：
    information_schema
    mysql
    performance_shcema

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

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

數(shù)據(jù)類型列表:

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

示例:查詢字符集設(shè)置及服務(wù)器字符變量      

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

collation '規(guī)則'  ：排序規(guī)則

示例:查詢排序規(guī)則

  mysql> show collation;

auto_increment   : 自動(dòng)增長(zhǎng)
             前提：非空，且唯一:支持索引，非負(fù)值             
            UNSIGNED：無(wú)符號(hào)
            null
            not null
            default

浮點(diǎn)型常用修飾符
      notnull
        null
        default
       unsigned

日期時(shí)間型的修飾符
         notnuall
            null
           default

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

MySQL SQL_MODE: SQL模式
                    TRADITIONAL, STRICT_TRANS_TABLES, or STRICT_ALL_TABLES
                    
設(shè)定服務(wù)器變量的值：（僅用于支持動(dòng)態(tài)的變量）
支持修改的服務(wù)器變量：
     動(dòng)態(tài)變量：可以Mysql運(yùn)行時(shí)修改
     靜態(tài)變量：  與配置文件中修改其值，并重啟后方能生效；         

服務(wù)器變量從其生效范圍來(lái)講，有兩類：
       全局變量：服務(wù)器級(jí)別，修改之后僅對(duì)新建立的會(huì)話有效； global
       回話變量：會(huì)話級(jí)別，僅對(duì)當(dāng)前回話有效；                        session
              會(huì)話建立時(shí)，從全局繼承各變量；

查看服務(wù)器變量 ：

 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';

示例如下:

修改變量
前提：默認(rèn)僅管理員有權(quán)限修改全局變量    
mysql> SET {GLOBAL|SESSION} VARIABLE_NAME='VALUE';
 
注意：無(wú)論是全局還是會(huì)話級(jí)別的動(dòng)態(tài)變量修改，在重啟mysqld后都會(huì)失效；想永久有效，可定義在配置文件中的相應(yīng)段中[mysqld]；

MySQL中字符大小寫：
       1、SQL關(guān)鍵字及函數(shù)名不區(qū)分字符大小寫；
       2、數(shù)據(jù)庫(kù)、表及視圖名稱的大小區(qū)分與否取決于低層OS、FS
       3、存儲(chǔ)過(guò)程、存儲(chǔ)函數(shù)及時(shí)間調(diào)度器的名字不區(qū)分大小寫，但觸發(fā)器區(qū)分大小寫
       4、表別名區(qū)分大小寫；
       5、對(duì)字段中的數(shù)據(jù)，如果字段類型為Binary類型，則區(qū)分大小寫，非binary不區(qū)分大小寫；
                        
更改數(shù)據(jù)庫(kù)名沒有更好的辦法,只能備份好，然后新建；

數(shù)據(jù)庫(kù)操作：

創(chuàng)建數(shù)據(jù)庫(kù):

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

示例如下:

刪除數(shù)據(jù)庫(kù)

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

示例如下:

修改數(shù)據(jù)庫(kù)

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

示例如下:

數(shù)據(jù)庫(kù)表管理：   

表創(chuàng)建：第一種方式              
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表，每表有三個(gè)文件，都位于數(shù)據(jù)庫(kù)目錄中；
             tb_name.frm  表結(jié)構(gòu)定義
             tb_name.MYD 數(shù)據(jù)文件
             tb_name.MYI   索引文件

 InnoDB表 ，有兩種存儲(chǔ)方式
           1、默認(rèn)：每表有一個(gè)獨(dú)立文件和一個(gè)共享的文件
             tb_name.frm :表結(jié)構(gòu)的定義，位于數(shù)據(jù)庫(kù)目錄中；
            ibdata#:共享的表空間文件，默認(rèn)位于數(shù)據(jù)目錄（datadir指向的目錄）
           2、獨(dú)立的表空間文件
               tb_name.frm  每表有一個(gè)表結(jié)構(gòu)文件
               tb_name.ibd   一個(gè)獨(dú)有的表空間文件
配置啟用InnoDB引擎獨(dú)立表空間:

#vim /etc/my.cnf
innodb_file_per_table = on

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

表創(chuàng)建：第二種方式（復(fù)制表數(shù)據(jù)不復(fù)制表結(jié)構(gòu)）
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [(create_definition,...)]
    [table_options]
    select_statement

示例如下:

表創(chuàng)建：第三種方式（復(fù)制表結(jié)構(gòu)）
    CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    { LIKE old_tbl_name | (LIKE old_tbl_name) }

示例如下:

表刪除：
mysql> drop table table_name; 刪除表名為xxx的表
表中的數(shù)據(jù)刪除:

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

如 DELETE FROM Test1 WHERE Name='samlee'
清空表數(shù)據(jù):

mysql> truncate table_name

truncate是一個(gè)能夠快速清空資料表內(nèi)所有資料的SQL語(yǔ)法。并且能針對(duì)具有自動(dòng)遞增值的字段。

表修改：

語(yǔ)法格式:alter table tab1_name

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

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

示例如下:

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下在表中特定的字段上創(chuàng)建索引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

修改表的存儲(chǔ)引擎 

格式如下:

>alter table table_name engine=MyISAM

示例如下:

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

擴(kuò)展Tips:

1.指定排序標(biāo)準(zhǔn)的字段：

ORDER BY col_name [, col_name] ...

2.轉(zhuǎn)換字符集及排序規(guī)則：

CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]

3.表選項(xiàng)修改：

[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}]

階段測(cè)試:

新建如下表（包括結(jié)構(gòu)和內(nèi)容）：
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
---------------------------------------------------------------------
##創(chuàng)建上表并插入數(shù)據(jù)
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 子句[布爾條件表達(dá)式];

布爾條件表達(dá)式操作符：

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

通過(guò)案例理解MySQL查詢:

創(chuàng)建案例環(huán)境:

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: 支持的通配符  
%: 任意長(zhǎng)度的任意字符 （模糊查找，性能比較低）
_ : 任意單個(gè)字符

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

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;

組合條件測(cè)試：

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數(shù)值從小到大排序

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

(DESC 降序顯示)

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

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

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

mysql> SELECT SUM(Age) FROM students;

11.計(jì)算學(xué)生年齡的最大數(shù)值

mysql> SELECT MAX(Age) FROM students;

12.統(tǒng)計(jì)表的記錄數(shù)

mysql> SELECT COUNT(Age) FROM students;

13.計(jì)算學(xué)生年齡的最小數(shù)值

mysql> SELECT MIN(Age) FROM students;

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

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

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

15.以下操作在students表上執(zhí)行
以ClassID分組，顯示每班的同學(xué)的人數(shù)；

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的班級(jí)；

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

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

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

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

mysql> SELECT * FROM students LIMIT 2,3;

mysql> SELECT * FROM students LIMIT 4;

擴(kuò)展Tips:

(1)導(dǎo)出數(shù)據(jù)庫(kù)文件:

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

(2)導(dǎo)入數(shù)據(jù)庫(kù)文件

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

SELECT語(yǔ)句的執(zhí)行流程：

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

SELECT語(yǔ)句:

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

MySQL多表查詢和子查詢

導(dǎo)入hellodb.sql 以下操作在students表上執(zhí)行；

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

聯(lián)結(jié)查詢：事先將兩張或多張表join，根據(jù)join的結(jié)果進(jìn)行查詢；

(1)cross join:交叉聯(lián)結(jié)

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

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

(2)自然連接(僅能夠在兩者相等的情況下才能建立聯(lián)結(jié))--內(nèi)連接

等值連接

條件比較

(3)外連接

左外連接:只保留出現(xiàn)在左外連接運(yùn)算之前(左邊)的關(guān)系中的元組--以左邊的元素為準(zhǔn)，右邊沒有則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;

右外連接：只保留出現(xiàn)在右外連接運(yùn)算之后（右邊）的關(guān)系中的元組；
 #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、用于比較表達(dá)式中的子查詢
            子查詢的返回值只能有一個(gè)
   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);

以上為關(guān)系型數(shù)據(jù)庫(kù)之Mysql查詢及數(shù)據(jù)庫(kù)管理(二)所有內(nèi)容