【版權申明】 http://blog.csdn.net/javazejian/article/details/61614366
出自【zejian的博客】
關聯文章:
MySQL的初次見面禮基礎實戰篇
MySQL的進階實戰篇
本篇將以最簡單的方式呈現并演繹MySQL數據庫的必知必會的知識點，通過本篇博文您將會對mysql從起點到終點的較為全面的認識，關于mysql的知識，將分兩篇來記錄，即MySQL的基礎實戰篇和MySQL的進階實戰篇，以下是本篇的主要知識點。
基礎實戰篇
- 環境準備
- 數據庫與表的創建以及SQL增刪改查
- 數據庫和表的創建與刪除
- 表的創建與刪除
- 外鍵的約束
- 數據類型
- 數字數據類型
- 日期和時間類型
- 字符串類型
- 表的增刪改查操作
- 增刪改
- 變化多端的查詢
- 過濾條件 - WHERE
- NULL 操作符
- LIKE 操作符
- BETWEEN 操作符
- IN 操作符
- 多條件組合AND 和 OR 操作符
- 對查詢結果排序 ORDER BY
- 獲取指定行數的數據-LIMIT
- 數據分組-GROUP BY 與 過濾分組 - HAVING
- 靈活使用計算字段
- 可能需要知道的常用的數據處理函數
- mysql中的常用聚合函數
- 表關聯的那點事兒
- 內關聯
- 外關聯
- 左外連接
- 右外連接
- 復雜關聯查詢
- 子查詢
- 基本子查詢
- 多返回值的子查詢
- 相關子查詢
- 組合查詢
- 表維護與改造
- 表的改造
- 表的復制
基礎實戰篇
環境準備
本篇不會講解如何去安裝mysql，也沒有這樣的必要，網上這方面的資料隨處可見，因此這里的環境搭建主要的我們可能在后面會使用到的數據庫和表結構，本篇將采用電商網站最常見的四張表,如下：
+-------------------+
| Tables_in_webshop |
+-------------------+
| items |
| orderdetail |
| orders |
| user |
+-------------------+
商品表結構items(簡化版)：
+------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(32) | NO | | NULL | | | price | float(10,1) | NO | | NULL | | | detail | text | YES | | NULL | | | pic | varchar(64) | YES | | NULL | | | createtime | datetime | NO | | NULL | | +------------+-------------+------+-----+---------+----------------+
訂單詳情表結構orderdetail(簡化版):
+-----------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+---------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | orders_id | int(11) | NO | MUL | NULL | | | items_id | int(11) | NO | MUL | NULL | | | items_num | int(11) | YES | | NULL | | +-----------+---------+------+-----+---------+----------------+
訂單表結構orders(簡化版)：
+------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | user_id | int(11) | NO | MUL | NULL | | | number | varchar(32) | NO | | NULL | | | createtime | datetime | NO | | NULL | | | note | varchar(100) | YES | | NULL | | +------------+--------------+------+-----+---------+----------------+
用戶表結構user(簡化版):
+----------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | username | varchar(32) | NO | | NULL | | | birthday | date | YES | | NULL | | | sex | char(1) | YES | | NULL | | | address | varchar(256) | YES | | NULL | | +----------+--------------+------+-----+---------+----------------+
它們間的關系如下圖，即一個訂單詳情orderdetail可以有多個商品items，但只能屬于一個訂單orders，而一個訂單orders也只能屬于一個user用戶，一個user用戶可以擁有多個orders訂單。
這里我們使用mysql提供的命令行窗口監視器對mysql數據庫進行各項操作。
數據庫與表的創建以及SQL增刪改查
數據庫和表的創建與刪除
通過以下命令鏈接上mysql監聽器：
mysql -u root -p ********(輸入密碼)
我們可以通過以下語句來創建數據庫和刪除數據庫并查看當前有哪些數據庫(大小不敏感)
#創建數據庫 CREATE DATABASE 數據庫名稱 #刪除數據庫 DROP DATABASE 數據庫名稱 #查看當前所有數據庫 SHOW DATABASES
先查看當前有哪些數據庫：
mysql> show databases; <-----------查看當前數據庫 +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test | +--------------------+ 5 rows in set (0.00 sec)
其中information_schema、performance_schema、mysql、sys 都是mysql自動創建的數據庫，如下給出這幾庫的簡單信息：
-
information_schema數據庫又稱為信息架構，數據表保存了MySQL服務器所有數據庫的信息。如數據庫名，數據庫的表，表欄的數據類型與訪問權限等。
-
performance_schema數據庫主要用于收集數據庫服務器性能參數，以便優化mysql數據庫性能。
-
mysql數據庫是存儲著已MySQL運行相關的基本信息等數據管理的數據庫。
-
sys 數據庫是mysql5.7增加的，通過這個庫可以快速的了解系統的元數據信息
這個庫可以方便DBA發現數據庫的很多信息，提供解決性能瓶頸的信息。
而test數據庫則是一個測試數據庫可有可無。ok，就此打住，現在通過創建和刪除一個名為debug的數據庫來演示數據庫的創建語句。
mysql> create database debug; <------創建數據庫 Query OK, 1 row affected (0.01 sec) <------代表執行成功 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | debug |<------已被創建的數據庫 | mysql | | performance_schema | | sys | | test | +--------------------+ 6 rows in set (0.00 sec)
使用DROP DATABASE語句刪除數據庫：
mysql> drop database debug ; <------刪除數據庫 Query OK, 0 rows affected (0.02 sec) mysql> show databases; <------重新查看數據庫 +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test | +--------------------+ 5 rows in set (0.00 sec)
表的創建與刪除
表的操作語句如下：
#創建表 CREATE TABLE 表名 (字段名1 數據類型 [其他可選項], 字段名2 數據類型 [其他可選項], ...... ) #刪除表 DROP TABLE 表名 #顯示表的數據結構 DESC 表名 #查看數據庫中所有的表 SHOW TABLES
創建表的最基本的3點是：
表的名稱 表字段名稱 每個字段的數據類型
現在利用上述的SQL操作語言，先創建一個名稱webshop的數據庫，并使用USE 關鍵字選擇該數據庫，然后創建前面 items 、orderdetail、orders、user 四張表，ok，先創建數據庫，操作如下：
mysql> create database webshop; <------創建webshop數據庫 Query OK, 1 row affected (0.00 sec) mysql> use webshop <------使用use關鍵字選擇webshop數據庫 Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed
創建 items 表，語句如下:
CREATE TABLE items ( id INT(11) NOT NULL AUTO_INCREMENT, item_name VARCHAR(32) NOT NULL COMMENT '商品名稱', price FLOAT(10,1) NOT NULL COMMENT '商品價格', detail TEXT COMMENT '商品詳情', pic VARCHAR(64) DEFAULT NULL COMMENT '圖片', createtime DATETIME NOT NULL COMMENT '生成日期', PRIMARY KEY (id) )ENGINE = INNODB DEFAULT CHARSET =UTF8 COMMENT '商品表'
其中id是唯一主鍵，使用關鍵字 PRIMARY KEY 進行指定，并且不能為空，因此使用 NOT NULL 標識非空，而 AUTO_INCREMENT 選項代表該id為自動增長從1開始。在其他列中如name中還使用到了 COMMENT 來標識name的含義。每個列中使用到諸如int(11)、varchar(32)、float(10,1)、text、datetime 等數據類型對每個字段的數據存儲類型進行標明（關于數據類型后面會說明）。在表創建的結尾，使用 ENGINE=InnoDB 來說明該items表在mysql數據庫中使用的引擎為InnoDB（mysql數據庫中提供多種數據庫引擎供選擇，而InnoDB是具備事務功能的引擎，后面還能見到它，這里暫且打住），通過 CHARSET=utf8 指定該表的字符集，到此創建表的語句就完成了。接著創建其他3張表：
#user表創建語句 CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(32) NOT NULL COMMENT '用戶名稱', `birthday` date DEFAULT NULL COMMENT '生日', `sex` char(1) DEFAULT NULL COMMENT '性別', `address` varchar(256) DEFAULT NULL COMMENT '地址', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=37 DEFAULT CHARSET=utf8; #訂單表orders創建語句 CREATE TABLE `orders` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL COMMENT '下單用戶id', `number` varchar(32) NOT NULL COMMENT '訂單號', `createtime` datetime NOT NULL COMMENT '創建訂單時間', `note` varchar(100) DEFAULT NULL COMMENT '備注', PRIMARY KEY (`id`), CONSTRAINT `FK_orders_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8; #訂單詳情表orderdetail創建語句 CREATE TABLE `orderdetail` ( `id` int(11) NOT NULL AUTO_INCREMENT, `orders_id` int(11) NOT NULL COMMENT '訂單id', `items_id` int(11) NOT NULL COMMENT '商品id', `items_num` int(11) DEFAULT NULL COMMENT '商品購買數量', PRIMARY KEY (`id`), <--------創建外鍵約束-----------> CONSTRAINT `FK_orderdetail_1` FOREIGN KEY (`orders_id`) REFERENCES `orders` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `FK_orderdetail_2` FOREIGN KEY (`items_id`) REFERENCES `items` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
外鍵的約束
在訂單詳情表orderdetail表的創建語句中使用到如下語句：
CONSTRAINT `FK_orderdetail_1` FOREIGN KEY (`orders_id`) REFERENCES `orders` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `FK_orderdetail_2` FOREIGN KEY (`items_id`) REFERENCES `items` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
其中orders_id 和 items_id 分別的訂單表orders和商品表items的主鍵，像這種屬于其他表主鍵又存在于orderdetail表中的字段，稱之為orderdetail的外鍵字段，使用外鍵的好處是可以使得兩張表存在關聯，保證數據的一致性和實現一些級聯操作；如每次購物時必須存在相對應的items_id商品數據才能創建訂單詳情的數據，因為沒有商品也沒有所謂的訂單詳情了，而每次可能會購買多種商品，而每種商品也將生成不同訂單詳情，而客戶的購買行為屬一次購買，因此訂單詳情匯聚成一個整體的訂單（orders_id），也就是說一個訂單詳情只能屬于一個訂單，而一個訂單可以擁有多個訂單詳情。在MySQL中，InnoDB引擎類型的表支持了外鍵約束，而外鍵的使用條件如下：
1.兩個表必須使用InnoDB引擎
2.外鍵列必須建立了索引(關于索引后面分析，主鍵創建時會自動創建索引)，MySQL 4.1.2以后的版本在建立外鍵時會自動創建索引
3.外鍵關系的兩個表的列必須是數據類型相似，也就是可以相互轉換類型的列，比如int和tinyint可以，但int和char則不可以；
外鍵的定義語法：
[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, …) REFERENCES tbl_name (index_col_name, …) [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}] [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
實例對照：
CONSTRAINT `FK_orderdetail_1` FOREIGN KEY (`orders_id`) REFERENCES `orders` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION <-----默認行為，可以不寫
注意該語法可以在 CREATE TABLE 和 ALTER TABLE 時使用，CONSTRAINT symbol，指明了約束標識符，在SQL排錯時可能有不錯的表現，如果不指明CONSTRAINT symbol，MYSQL會自動生成一個名字。兩表間的更新刪除時數據的同步可以使用ON DELETE、ON UPDATE 來表明相互間刪除和更新事件觸發后的影響，可設參數以下參數，假設主表是orders，從表是orderdetail。
-
RESTRICT、NO ACTION(默認行為)
刪除：從表數據記錄不存在時，主表數據記錄才可以刪除，如當從表orderdetail的數據被刪除后主表的orders的數據才能被刪除，否則無法刪除。刪除從表數據，主表數據不變
更新：從表記錄數據不存在時，主表數據才可以更新。當更新從表數據，主表數據不變 -
CASCADE（級聯）
刪除：刪除主表數據時自動刪除從表數據。刪除從表數據，主表數據不變
更新：更新主表數據時自動更新從表數據。更新從表數據，主表數據不變
-
SET NULL
刪除：刪除主表數據時自動更新從表對于數據值為NULL。刪除從表數據，主表數據不變
更新：更新主表數據時自動更新從表數據值為NULL。更新從表數據數據，主表不變
到此，4張表都創建完成，我們使用show tables 語句來查看數據庫中的表：
mysql> show tables; +-------------------+ | Tables_in_webshop | +-------------------+ | items | | orderdetail | | orders | | orders-dely | | user | +-------------------+ 5 rows in set (0.01 sec)
其中orders-dely表是多余的，使用drop table 表名 語句將其刪除：
mysql> drop table `orders-dely` ; <-------刪除orders-dely表 Query OK, 0 rows affected (0.01 sec) mysql> show tables; <------再次查看數據庫中的表 +-------------------+ | Tables_in_webshop | +-------------------+ | items | | orderdetail | | orders | | user | +-------------------+ 4 rows in set (0.00 sec)
數據類型
MySQL使用所有標準的ANSI SQL數字數據類型，下面將列出常見的數據類型及其說明
數字數據類型
-
TINYINT - 一個非常小的整數，可以帶符號。如果是有符號，它允許的范圍是從-128到127。如果是無符號，允許的范圍是從0到255，占1個字節。。
-
SMALLINT - 一個小的整數，可以帶符號。如果有符號，允許范圍為-32768至32767。如果無符號，允許的范圍是從0到65535，占2個字節。
-
MEDIUMINT - 一個中等大小的整數，可以帶符號。如果有符號，允許范圍為-8388608至8388607。 如果無符號，允許的范圍是從0到16777215，占3個字節。。
-
INT - 正常大小的整數，可以帶符號。如果是有符號的，它允許的范圍是從-2147483648到2147483647。如果是無符號，允許的范圍是從0到4294967295，占4個字節。
-
BIGINT - 一個大的整數，可以帶符號。如果有符號，允許范圍為-9223372036854775808到9223372036854775807。如果無符號，允許的范圍是從0到18446744073709551615. 占8個字節。
-
FLOAT(M,D) - 不能使用無符號的浮點數字。可以定義顯示長度(M)和小數位數(D)。這不是必需的，并且默認為10,2。其中2是小數的位數，10是數字(包括小數)的總數。占4個字節。
-
DOUBLE(M,D) - 不能使用無符號的雙精度浮點數。可以定義顯示長度(M)和小數位數(D)。 這不是必需的，默認為16,4，其中4是小數的位數，占8個字節。
-
DECIMAL(M,D) - 非壓縮浮點數不能是無符號的。在解包小數，每個小數對應于一個字節。定義顯示長度(M)和小數(D)的數量是必需的。 NUMERIC是DECIMAL的同義詞。
日期和時間類型
-
DATE - 以YYYY-MM-DD格式的日期，在1000-01-01和9999-12-31之間。 例如，1999年10月30日將被存儲為1999-10-30。
-
DATETIME - 日期和時間組合以YYYY-MM-DD HH:MM:SS格式，在1000-01-01 00:00:00 到9999-12-31 23:59:59之間。例如，1999年10月30日下午3:30，會被存儲為1999-10-30 15:30:00。
-
TIMESTAMP - 1970年1月1日午夜之間的時間戳，到2037的某個時候。這看起來像前面的DATETIME格式，無需只是數字之間的連字符; 1973年12月30日下午3點30分將被存儲為19731230153000(YYYYMMDDHHMMSS)。
-
TIME - 存儲時間在HH:MM:SS格式。
-
YEAR(M) - 以2位或4位數字格式來存儲年份。如果長度指定為2(例如YEAR(2))，年份就可以為1970至2069(70〜69)。如果長度指定為4，年份范圍是1901-2155，默認長度為4。
字符串類型
-
CHAR(M) - 固定長度的字符串是以長度為1到255之間個字符長度(例如：CHAR(5))，存儲右空格填充到指定的長度。 限定長度不是必需的，它會默認為1。
-
VARCHAR(M) - 可變長度的字符串是以長度為1到255之間字符數(高版本的MySQL超過255); 例如： VARCHAR(25). 創建VARCHAR類型字段時，必須定義長度。
-
BLOB 或 TEXT - 字段的最大長度是65535個字符。 BLOB是“二進制大對象”，并用來存儲大的二進制數據，如圖像或其他類型的文件。定義為TEXT文本字段還持有大量的數據; 兩者之間的區別是，排序和比較上存儲的數據，BLOB大小寫敏感，而TEXT字段不區分大小寫。不用指定BLOB或TEXT的長度。
-
TINYBLOB 或 TINYTEXT - BLOB或TEXT列用255個字符的最大長度。不指定TINYBLOB或TINYTEXT的長度。
-
MEDIUMBLOB 或 MEDIUMTEXT - BLOB或TEXT列具有16777215字符的最大長度。不指定MEDIUMBLOB或MEDIUMTEXT的長度。
-
LONGBLOB 或 LONGTEXT - BLOB或TEXT列具有4294967295字符的最大長度。不指定LONGBLOB或LONGTEXT的長度。
-
ENUM - 枚舉，這是一個奇特的術語列表。當定義一個ENUM，要創建它的值的列表，這些是必須用于選擇的項(也可以是NULL)。例如，如果想要字段包含“A”或“B”或“C”，那么可以定義為ENUM為 ENUM(“A”，“B”，“C”)也只有這些值(或NULL)才能用來填充這個字段。
表的增刪改查操作
增刪改
有上述的表結構就可以對表進行增刪改查的操作，其語句法結構如下：
#insert 插入操作 INSERT INTO 表名(列名1,列名2,...) VALUES (數據1,數據2...); #update 更新操作 UPDATE 表名 SET 列名1=值1,列名2=值2,...WHERE 條件表達式； #delete 刪除操作 DELETE FROM 表名 WHERE 條件表達式 #select 查詢操作 SELECT 列名1，列名2，... FROM 表名 [條件表達式]
接著使用上述語句對user進行增刪改查的操作，首先查詢一下user表有哪些用戶數據，通過select 語句進行查詢：
mysql> select * from user; <---- select查詢語句 +----+-----------+------------+------+--------------------+ | id | username | birthday | sex | address | +----+-----------+------------+------+--------------------+ | 1 | 王五 | NULL | 2 | NULL | | 2 | 張曹宇 | 1990-08-05 | 1 | 廣東省汕頭市 | | 10 | 張三 | 1999-06-06 | 1 | 北京市朝陽區 | | 16 | 任在明 | 1996-12-01 | 1 | 廣東省廣州市 | | 22 | 陳小明 | 1995-05-10 | 1 | 廣東省深圳市 | | 24 | 任傳海 | 1992-03-08 | 1 | 海南三亞 | +----+-----------+------------+------+--------------------+ 9 rows in set (0.00 sec)
其中 * 號代表查詢出該表的所有字段，當然也向下面那樣一個個字段列舉出來：
select id, username, birthday, sex, address from user;
現在通過下面語句向user表插入一條數據：
insert into user (id, username, birthday, sex, address) values('3','新數據','1909-12-12','1','常年在外'); #當確定插入表中所有列時可以省略列名稱 insert into user values('3','新數據','1909-12-12','1','常年在外');
結果如下：
#插入新數據 mysql> insert into user (id, username, birthday, sex, address) values('3','新數據','1909-12-12',1,'常年在外'); Query OK, 1 row affected (0.00 sec) mysql> select * from user; +----+-----------+------------+------+--------------------+ | id | username | birthday | sex | address | +----+-----------+------------+------+--------------------+ | 1 | 王五 | NULL | 2 | NULL | | 2 | 張曹宇 | 1990-08-05 | 1 | 廣東省汕頭市 | | 3 | 新數據 | 1909-12-12 | 1 | 常年在外 <--------新插入的數據 | 10 | 張三 | 1999-06-06 | 1 | 北京市朝陽區 | | 16 | 任在明 | 1996-12-01 | 1 | 廣東省廣州市 | | 22 | 陳小明 | 1995-05-10 | 1 | 廣東省深圳市 | | 24 | 任傳海 | 1992-03-08 | 1 | 海南三亞 | +----+-----------+------------+------+--------------------+ 10 rows in set (0.00 sec)
使用更新操作，更新ID為3的記錄：
update user set username='大王讓我來巡山',address='北京朝陽' where id=3;
執行過程如下：
mysql> update user set username='大王讓我來巡山',address='北京朝陽' where id=3; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from user; +----+-----------------------+------------+------+--------------------+ | id | username | birthday | sex | address | +----+-----------------------+------------+------+--------------------+ | 1 | 王五 | NULL | 2 | NULL | | 2 | 張曹宇 | 1990-08-05 | 1 | 廣東省汕頭市 | | 3 | 大王讓我來巡山 | 1909-12-12 | 1 | 北京朝陽 | | 10 | 張三 | 1999-06-06 | 1 | 北京市朝陽區 | | 16 | 任在明 | 1996-12-01 | 1 | 廣東省廣州市 | | 22 | 陳小明 | 1995-05-10 | 1 | 廣東省深圳市 | | 24 | 任傳海 | 1992-03-08 | 1 | 海南三亞 | +----+-----------------------+------------+------+--------------------+ 10 rows in set (0.01 sec)
顯然id為3的數據已更新，接著使用delete語句刪除id為3和33的記錄，執行如下：
mysql> delete from user where id = 3 and id=33; Query OK, 0 rows affected (0.00 sec) <-----刪除成功 mysql> select * from user; +----+-----------------------+------------+------+--------------------+ | id | username | birthday | sex | address | +----+-----------------------+------------+------+--------------------+ | 1 | 王五 | NULL | 2 | NULL | | 2 | 張曹宇 | 1990-08-05 | 1 | 廣東省汕頭市 | | 3 | 大王讓我來巡山 | 1909-12-12 | 1 | 北京朝陽 | | 10 | 張三 | 1999-06-06 | 1 | 北京市朝陽區 | | 16 | 任在明 | 1996-12-01 | 1 | 廣東省廣州市 | | 22 | 陳小明 | 1995-05-10 | 1 | 廣東省深圳市 | | 24 | 任傳海 | 1992-03-08 | 1 | 海南三亞 | +----+-----------------------+------------+------+--------------------+ 10 rows in set (0.00 sec)
變化多端的查詢
對于增刪改都比較簡單，這里我們主要來細說一下查詢，因為查詢可以更加不同條件組合來獲取不同的查詢結果，這點還是比較有意思的。
過濾條件 - WHERE
先來看看條件查詢，前面我們更新和刪除時都使用到了條件語句，使用where子句指明要刪除記錄的id是哪個，也就是指明刪除的符合具體條件的行數據，同樣的條件也適合select語句，通過where子句查詢，可以過濾不是期望的數據，下面通過一個例子來演示，查詢id大于15的所有數據，執行語句如下：
mysql> select * from user where id > 15 ; +----+-----------+------------+------+--------------------+ | id | username | birthday | sex | address | +----+-----------+------------+------+--------------------+ | 16 | 任在明 | 1996-12-01 | 1 | 廣東省廣州市 | | 22 | 陳小明 | 1995-05-10 | 1 | 廣東省深圳市 | | 24 | 任傳海 | 1992-03-08 | 1 | 海南三亞 | +----+-----------+------------+------+--------------------+ 6 rows in set (0.00 sec)
其中 > 屬于比較運算符 ，如上面id大于15的所有數據將會被檢索出來，而id小于15的就被過濾掉了，當然在where子句中還可使用其他運算符，如下
| 運算符 | 描述 | 演示 |
|---|---|---|
= |
相等 | id = 15 |
> |
大于 | id > 15 |
< |
小于 | id < 15 |
>= |
大于等于 | id >= 15 |
<= |
小于等于 | id <= 15 |
<> |
不相等 | id <> 15 |
IS [NOT] NULL |
為NULL(不為NULL) | address is NULL |
[NOT] LIKE |
模糊查詢，指向模糊查詢目標 | address LIKE '北京%' |
[NOT] BETWEEN |
(不包含)包含在指定范圍內 | id BETWEEN 1 AND 15 |
[NOT] IN |
包含在指定范圍值內 | id in (1,2,15,20) |
NULL 操作符
NULL條件有點需要注意，在使用NULL條件檢索時不能使用=號，必須使用 is 指明查詢條件的值為空，當然如果是not null 那就是非空數據了，如：
#查詢生日為空的用戶 mysql> select * from user where birthday is NULL; +----+----------+----------+------+---------+ | id | username | birthday | sex | address | +----+----------+----------+------+---------+ | 1 | 王五 | NULL | 2 | NULL | +----+----------+----------+------+---------+ 1 row in set (0.00 sec)
LIKE 操作符
關鍵字like主要用于模糊查詢，如下查詢名稱為‘任’開頭的用戶：
mysql> select username from user where username like '任%'; +-----------+ | username | +-----------+ | 任在明 | | 任傳海 | +-----------+ 2 rows in set (0.00 sec)
其中% 稱為外卡符號，代表0個以上的字符。如上述的【任%】代表以任開頭的所有字符串。如果是 【%任】則代表以任結尾的所有字符串，而【%任%】代表所有含有任字的字符串。當然除了% 是外卡符號，還有‘_’下劃線也是外卡符號，，代表一個字符，也就是說條件為任_ 時 只有【任良】是符合條件而【任其阿】則是不符合條件。同理加上NOT 則取反的意思。
BETWEEN 操作符
BETWEEN 是讓我們可以運用一個范圍 (range) 內抓出數據庫中的值。執行語句如下：
#查詢id在1到15之間的用戶(包括1和15) mysql> select * from user where id between 1 and 15 ; +----+-----------------------+------------+------+--------------------+ | id | username | birthday | sex | address | +----+-----------------------+------------+------+--------------------+ | 1 | 王五 | NULL | 2 | NULL | | 2 | 張曹宇 | 1990-08-05 | 1 | 廣東省汕頭市 | | 3 | 大王讓我來巡山 | 1909-12-12 | 1 | 北京朝陽 | | 10 | 張三 | 1999-06-06 | 1 | 北京市朝陽區 | +----+-----------------------+------------+------+--------------------+ 4 rows in set (0.00 sec)
IN 操作符
表示某一組指明的數據，在括弧內可以有一或多個值，而不同值之間由逗點分開。值可以是數目或是文字。如下語句：
#查詢id為1，2，3的用戶 mysql> select * from user where id in (1,2,3); +----+-----------------------+------------+------+--------------------+ | id | username | birthday | sex | address | +----+-----------------------+------------+------+--------------------+ | 1 | 王五 | NULL | 2 | NULL | | 2 | 張曹宇 | 1990-08-05 | 1 | 廣東省汕頭市 | | 3 | 大王讓我來巡山 | 1909-12-12 | 1 | 北京朝陽 | +----+-----------------------+------------+------+--------------------+ 4 rows in set (0.00 sec)
多條件組合AND 和 OR 操作符
當需要在查詢中使用多個條件組合時，可以使用AND 或者 OR ，其中指明兩個條件必須成立，而OR則需要一個條件成立即可，如下語句使用AND進行條件組合查詢：
#查詢生日為空并且sex=1的用戶 mysql> select * from user where birthday is null and sex = 1; +----+----------+----------+------+---------+ | id | username | birthday | sex | address | +----+----------+----------+------+---------+ | 1 | 王五 | NULL | 1 | NULL | +----+----------+----------+------+---------+ 1 row in set (0.00 sec)
使用OR進行條件組合查詢的結果如下：
#查詢生日為空或者sex=1的用戶 mysql> select * from user where birthday is null or sex = 1; +----+-----------------------+------------+------+--------------------+ | id | username | birthday | sex | address | +----+-----------------------+------------+------+--------------------+ | 1 | 王五 | NULL | 1 | NULL | | 2 | 張曹宇 | 1990-08-05 | 1 | 廣東省汕頭市 | | 3 | 大王讓我來巡山 | 1909-12-12 | 1 | 北京朝陽 | | 10 | 張三 | 1999-06-06 | 1 | 北京市朝陽區 | | 16 | 任在明 | 1996-12-01 | 1 | 廣東省廣州市 | | 22 | 陳小明 | 1995-05-10 | 1 | 廣東省深圳市 | | 24 | 任傳海 | 1992-03-08 | 1 | 海南三亞 | +----+-----------------------+------------+------+--------------------+ 7 rows in set (0.00 sec)
由此看出AND和OR的區別確實很明顯，而且在where條件語句中可包含任意數目的AND和OR操作符，因此我們在語句查詢時對于AND和OR的使用以及計算順序要特別注意，否則將會得到非預期的查詢結果。
對查詢結果排序 ORDER BY
有時我們希望查詢出的數據按照一定的規律排序，此時ORDER BY就是很好的幫手了，如想讓查詢出來的數據按生日排序：
#默認按升序，ASC 升序（可省略） ， DESC 降序 mysql> select username,birthday from user where id > 15 order by birthday ASC; +-----------+------------+ | username | birthday | +-----------+------------+ | 任傳海 | 1992-03-08 | | 陳小明 | 1995-05-10 | | 任在明 | 1996-12-01 | +-----------+------------+ 6 rows in set (0.00 sec)
如果有多個排序條件則用逗號隔開，第一個條件的優先級總是高于第二個條件，如下：
#先按id排序再按生日排序 mysql> select id ,username,birthday from user where id > 15 order by id ,birthday; +----+-----------+------------+ | id | username | birthday | +----+-----------+------------+ | 16 | 任在明 | 1996-12-01 | | 22 | 陳小明 | 1995-05-10 | | 24 | 任傳海 | 1992-03-08 | +----+-----------+------------+ 6 rows in set (0.00 sec)
獲取指定行數的數據-LIMIT
通過LIMIT可以獲取到指定行數的記錄。比如想獲取前3條數據
#獲取前3條數據 mysql> select * from user limit 0 , 3; +----+-----------------------+------------+------+--------------------+ | id | username | birthday | sex | address | +----+-----------------------+------------+------+--------------------+ | 1 | 王五 | NULL | 1 | NULL | | 2 | 張曹宇 | 1990-08-05 | 1 | 廣東省汕頭市 | | 3 | 大王讓我來巡山 | 1909-12-12 | 1 | 北京朝陽 | +----+-----------------------+------------+------+--------------------+
其中limit 0 ，3 ，第一個參數代表從第0個(也就是第一行數據)開始獲取，第二個參數3，表示獲取的條數。如下從第1個數據開始獲取，結果就不一樣了。
mysql> select * from user limit 1 , 3; +----+-----------------------+------------+------+--------------------+ | id | username | birthday | sex | address | +----+-----------------------+------------+------+--------------------+ | 2 | 張曹宇 | 1990-08-05 | 1 | 廣東省汕頭市 | | 3 | 大王讓我來巡山 | 1909-12-12 | 1 | 北京朝陽 | | 10 | 張三 | 1999-06-06 | 1 | 北京市朝陽區 | +----+-----------------------+------------+------+--------------------+
數據分組-GROUP BY 與 過濾分組 - HAVING
有時候可能需要依據某個字段進行查詢結果分組，這時GROUP BY就顯得很有用了，比如在user 表中我們依據sex字段進行分組,統計兩種性別分別有多少人，先查看沒有分組的數據：
mysql> select * from user ; +----+-----------------------+------------+------+--------------------+ | id | username | birthday | sex | address | +----+-----------------------+------------+------+--------------------+ | 1 | 王五 | NULL | 1 | NULL | | 2 | 張曹宇 | 1990-08-05 | 1 | 廣東省汕頭市 | | 3 | 大王讓我來巡山 | 1909-12-12 | 1 | 北京朝陽 | | 10 | 張三 | 1999-06-06 | 0 | 北京市朝陽區 | | 16 | 任在明 | 1996-12-01 | 1 | 廣東省廣州市 | | 22 | 陳小明 | 1995-05-10 | 0 | 廣東省深圳市 | | 24 | 任傳海 | 1992-03-08 | 1 | 海南三亞 | +----+-----------------------+------------+------+--------------------+ 7 rows in set (0.00 sec)
分組數據如下，其count()是統計函數，可以統計某個字段的數量,執行結果如下：
#根據sex統計分組數量 mysql> select sex ,count(id) from user group by sex; +------+-----------+ | sex | count(id) | +------+-----------+ | 0 | 2 | | 1 | 5 | +------+-----------+ 2 rows in set (0.01 sec)
有點要注意的是如果列中具有NULL值，則NULL將作為一個分組返回，如果列中有多個NULL值，它們將分為一組，GROUP BY 子句必須出現在WHERE子句之后，ORDER BY 語句之前。如下添加兩條sex為null的數據。
insert into user values(11,'aaa',NULL,NULL,NULL); insert into user values(12,'bbb',NULL,NULL,NULL); mysql> select sex ,count(id) from user group by sex; +------+-----------+ | sex | count(id) | +------+-----------+ | NULL | 2 |<------ 以NULL進行分組 | 0 | 2 | | 1 | 5 | +------+-----------+ 3 rows in set (0.00 sec)
除了能使用group by 分組數據外，我們還可以對分組的數據進行過濾，從而指定包括哪些分組，排除哪些分組，比如根據sex字段進行user查詢分組時想排除sex字段值為null的數據，此時需要指明一個條件進行過濾，可能我們已想到where 子語句，遺憾的是where并不能對數據進行分組過濾，因為where更多地是進行行數據的過濾而不是分組數據的過濾，實際上where并沒有分組的概念。幸運的是，mysql提供另外的子語句having，having與where有點類似，只不過where是進行行數據過濾，而having是進行組數據過濾,其演示過程如下：
#使用 having 排除sex為null的分組 mysql> select sex ,count(id) from user group by sex having sex is not null; +------+-----------+ | sex | count(id) | +------+-----------+ | 0 | 2 | | 1 | 5 | +------+-----------+ 2 rows in set (0.01 sec)
記住，where是進行行數據的過濾，也就是說在分組前where已過濾了數據，而having是進行分組數據的過濾，即在分組后才進行數據過濾，接著來看一個where 和 having 結合使用的例子，比如還是根據sex進行分組查詢并排除sex為null的分組，同時希望排除id小于10的數據，其sql編寫并執行如下：
#先查看所有數據 mysql> select id ,username, sex from user; +----+-----------------------+------+ | id | username | sex | +----+-----------------------+------+ | 1 | 王五 | 1 | | 2 | 張曹宇 | 1 | | 3 | 大王讓我來巡山 | 1 | | 10 | 張三 | 0 | | 11 | aaa | NULL | | 12 | bbb | NULL | | 16 | 任在明 | 1 | | 22 | 陳小明 | 0 | | 24 | 任傳海 | 1 | +----+-----------------------+------+ 9 rows in set (0.00 sec) #同時使用where 和 having 進行數據過濾 mysql> select sex ,count(id) from user where id > 10 group by sex having sex is not null; +------+-----------+ | sex | count(id) | +------+-----------+ | 0 | 1 | | 1 | 2 | +------+-----------+ 2 rows in set (0.00 sec)
靈活使用計算字段
所謂的計算字段就通過組合計算出來的字段，下面通過一個簡單實例來說明，如我們想查詢出所有用戶的名稱和生日，并通過 name (birthday) 的格式顯示 ，sql語句如下：
#為讓排版漂亮些，更新一下數據 mysql> update user set username = '李達康' where id =3 ; mysql> update user set username = '張書記' where id = 10; #計算字段(拼接字段) mysql> select Concat(username,'(',birthday,')') from user where birthday is not null; +-----------------------------------+ | Concat(username,'(',birthday,')') | <-----------字段的名稱使用AS操作符創建別名 +-----------------------------------+ | 張曹宇(1990-08-05) | | 李達康(1909-12-12) | | 張書記(1999-06-06) | | 任在明(1996-12-01) | | 陳小明(1995-05-10) | | 任傳海(1992-03-08) | +-----------------------------------+
在sql語句中使用了concat函數拼接字符串，concat 函數可以把多個字符串拼接成一個長字符串，其使用方式如下，返回結果為連接參數產生的字符串。注意如有任何一個參數為NULL ，則返回值為 NULL。
CONCAT(str1,str2,…)
這樣就完成了計算字段的拼接，但是我們發現字段名稱比較長也不直觀，這里可以使用mysql提供的AS操作符創建別名，如下：
mysql> select Concat(username,'(',birthday,')') AS name_birthday from user where birthday is not null; +-----------------------+ | name_birthday | +-----------------------+ | 張曹宇(1990-08-05) | | 李達康(1909-12-12) | | 張書記(1999-06-06) | | 任在明(1996-12-01) | | 陳小明(1995-05-10) | | 任傳海(1992-03-08) | +-----------------------+
除了上述的計算字段，還可執行算術計算的字段如下，items表的數據，查詢出來后按8折價格計算
mysql> select name , price ,price * 0.8 AS discount_price from items; +------------------+---------+----------------+ | name | price | discount_price | +------------------+---------+----------------+ | MacBook Air | 8298.9 | 6639.1 | | MacBook Pro | 10982.0 | 8785.6 | | 背包 | 200.0 | 160.0 | | 臺式機 | 3000.0 | 2400.0 | | Java EE 7 | 78.0 | 62.4 | | Java編程思想 | 83.0 | 66.4 | +------------------+---------+----------------+
price 字段為items表的原字段，而 discount_price 則是計算出來后的折扣價格，這種字段也稱計算字段。這里我們反復使用了AS操作符創建別名，實際上該操作符也可使用表名稱上為表創建別名，當表名很長時，利用AS創建別名不失為一種好辦法。
可能需要知道的常用的數據處理函數
如果需要進一步了解每個函數具體用法，可以訪問-這篇文章
- 文本處理函數
| 函數 | 描述 |
|---|---|
| Concat(str1,str2,…) | 連接字符串 |
| Left(str,len) | 從字符串的左端開始取得len長的子串 |
| Right(str,len) | 從字符串的右端開始取得len長的子串 |
| Length(str) | 返回串str的長度 |
| Lower(str) | str轉為小寫 |
| Upper(str) | str轉為大寫 |
| Trim(str) | 去掉字符串str兩邊的空格 |
| SubString(str,pos[,len]) | 取得字符串str中從pos位置開始的len長的子串 |
| Replace（t1，t2，t3） | 把t1字符串中的t2換為t3 |
| SubString(t，x，y) | 返回t中始于x的長為y的字符串 |
- 數值處理函數
| 函數 | 描述 |
|---|---|
| Abs(num) | 返回一個數的絕對值 |
| Mod(x,y) | 求x/y余數 |
| Pow(x，n) | x的n次方 |
| Rand() | 返回一個隨機數 |
| Round(n1,n2) | 返回數n1，它被四舍五入為n2位小數 |
| Sqrt(num) | 返回一個數的平方根 |
| Ceiling(num) | 基于num的值的下一個最大的整數 |
| Floor(num) | 返回num的整數值 |
| Format（n1,n2） | 返回格式化為一個數的n1，這個數帶有n2位小數，并且每3位之間插入一個逗號 |
- 日期函數
| 函數 | 描述 |
|---|---|
| Now | 當前時間(2017-04-08 17:06:45) |
| CurDate() | 當前日期（2017-04-08） |
| CurTime() | 當前時間(17:06:45) |
| DateDiff(d1,d2) | 計算兩個日期差值 |
| AddDate() | 添加一個日期(天、周等) |
| AddTime() | 添加一個時間（時、分等） |
| date_format(d1,format) | 格式化日期 |
| Date() | 返回日期時間的日期部分 |
| Month() | 返回一個日期的月份部分 |
| Year() | 返回一個日期的年份部分 |
| Day() | 返回日期的天數部分 |
| DayOfWeek() | 對于一個日期返回對于星期幾 |
| Hour() | 返回一個時間的小時部分 |
| Minute() | 返回一個時間的分鐘部分 |
| Second() | 返回一個時間的秒部分 |
mysql中的常用聚合函數
有些情況下我們可能只是需要查詢結果的匯總數據而不是把每行每列檢索出來，如確定表中的行數，獲取表中每個字段的總和等，此時使用mysql提供的聚合函數就可很容易獲得預期結果，一般常用的聚合函數如下：
| 函數 | 描述 |
|---|---|
| AVG(列名) | 平均值 |
| COUNT(列名) | 總數量 |
| MAX(列名) | 最大值 |
| MIN(列名) | 最小值 |
| SUM(列名) | 合計值 |
-
AVG()函數
AVG函數是用來查找各種記錄的一個字段的平均值，下面通過一個查詢案例來說明其作用，現在通過sql查詢items商品表中的商品平均價格，其語句及其執行過程如下：
#此sql返回的avg_price字段就是商品表中商品的平均價格 mysql> select AVG(price) AS avg_price from items; +------------+ | avg_price | +------------+ | 3773.65007 | +------------+ 1 row in set (0.00 sec)
-
注意，在使用AVG()函數求平均值時，計算的字段是必須提供的，而且對于NULL值，AVG()函數將會忽略值為NULL的行。
-
COUNT()函數
COUNT()主要用于行數計算，可利用該函數來確定表中的行數或者符合特定條件的數目。現在通過COUNT()函數查詢user的總數量，其sql如下：
mysql> select COUNT(*) AS count from user; +-------+ | count | +-------+ | 9 | +-------+ 1 row in set (0.00 sec) mysql> select COUNT(birthday) AS count from user; +-------+ | count | +-------+ | 6 | +-------+ 1 row in set (0.00 sec) mysql> select COUNT(id) AS count from user; +-------+ | count | +-------+ | 9 | +-------+ 1 row in set (0.00 sec)
-
從上述sql可以看出COUNT()函數存在兩種使用方式：
-
使用COUNT(*)對表中的行數進行統計，不管列中包含的是NULL值還是非NULL值，都可正常統計出數量（大多數情況下建議使用該方式）。
-
使用COUNT(column)對特定列中的具有值的行進行統計，忽略NULL值，上述通過birthday字段和id字段查詢出來的總數不一樣，就是因為birthday字段存在NULL值，而id字段不存在NULL值。
-
-
MAX()函數 和 MIN()函數
MAX()函數返回最大值，而MIN()函數返回最小值，查詢商品表items中價格最大的商品和價格最低的商品，執行過程如下：
mysql> select MAX(price) AS max_price ,MIN(price) AS min_price from items; +-----------+-----------+ | max_price | min_price | +-----------+-----------+ | 10982.0 | 78.0 | +-----------+-----------+ 1 row in set (0.00 sec)
SUM()函數
SUM()函數主要用來返回指定列值的和，如查詢訂單詳情orderdetail表中的商品總數量
#訂單詳情的數據 mysql> select * from orderdetail; +----+-----------+----------+-----------+ | id | orders_id | items_id | items_num | +----+-----------+----------+-----------+ | 1 | 3 | 1 | 1 | | 2 | 3 | 2 | 3 | | 3 | 4 | 3 | 4 | | 4 | 4 | 2 | 3 | +----+-----------+----------+-----------+ #查詢訂單詳情orderdetail表中的商品總數量 mysql> select SUM(items_num) AS items_sum_count from orderdetail; +-----------------+ | items_sum_count | +-----------------+ | 11 | +-----------------+
表關聯的那點事兒
在前面的sql語句中，我們對表操作始終只有一張并沒有涉及到多張表的關聯，但在實際開發中，單表查詢并不能很好滿足預期的需求，而通過多表關聯查詢則能實現預期的需求，這也就是為什么需要表關聯的原因了，在前面的表創建中，orderdetail表中同時擁有關聯商品表items的item_id字段和訂單表order的order_id字段，這兩個字段稱為orderdetail表的外鍵，由此它們之間也就產生了關聯關系，如下圖：
在前面的sql語句中，我們對表操作始終只有一張并沒有涉及到多張表的關聯，但在實際開發中，單表查詢并不能很好滿足預期的需求，而通過多表關聯查詢則能實現預期的需求，這也就是為什么需要表關聯的原因了，在前面的表創建中，orderdetail表中同時擁有關聯商品表items的item_id字段和訂單表order的order_id字段，這兩個字段稱為orderdetail表的外鍵，由此它們之間也就產生了關聯關系，如下圖：
那為什么需要產生關聯關系呢？這里我們以訂單詳情orderdetail和商品表items為例子，分析不把商品信息直接都放到訂單詳情表中的原因？事實上分表存儲商品信息和訂單詳情表是有如下理由
-
對于每個訂單來說，相同的商品的信息都是一樣的，因此如果把商品信息直接存放到訂單表中，對于不同訂單的相同商品的信息的存儲將是重復的，這屬于既浪費時間又浪費存儲空間，完全沒有必要。
-
如果此時商品信息發生變化，對于商品表來說只需改變一次即可，但如果直接把商品信息存儲到訂單表中，那么重復的商品信息都將要修改，維護成本得不償失呢。
所有分表存儲商品信息和訂單詳情信息是個不錯的選擇。雖然分解數據為多個表能更有效地存儲，更方便地處理數據，但這些好處是有一定的代價的，那就是在查詢預期數據時需要多表關聯查詢，也稱為多表聯結，這樣的查詢效率顯然不會有單表查詢的效率高，不過相對于上述存在的問題，犧牲點效率還是很值得的。下面的語句將演示如何進行聯結查詢。
#查看訂單詳情表的信息 mysql> mysql> * from nt from orderdetail; +----+-----------+----------+-----------+ | id | orders_id | items_id | items_num | +----+-----------+----------+-----------+ | 1 | 3 | 1 | 1 | | 2 | 3 | 2 | 3 | | 3 | 4 | 3 | 4 | | 4 | 4 | 2 | 3 | +----+-----------+----------+-----------+ 4 rows in set (0.03 sec) #查看商品表的信息 mysql> select * from items; +----+------------------+---------+--------------------+------+---------------------+ | id | name | price | detail | pic | createtime | +----+------------------+---------+--------------------+------+---------------------+ | 1 | MacBook Air | 8298.9 | MAC Air | NULL | 2016-09-03 13:22:53 | | 2 | MacBook Pro | 10982.0 | MAC Pro | NULL | 2016-02-09 13:22:57 | | 3 | 背包 | 200.0 | 名牌背包 | NULL | 2016-02-06 13:23:02 | | 4 | 臺式機 | 3000.0 | 聯想 | NULL | 2016-02-03 13:22:53 | | 5 | Java EE 7 | 78.0 | 機器學習領域 | NULL | 2016-12-01 18:29:33 | | 6 | Java編程思想 | 83.0 | Java EE 7平臺 | NULL | 2016-12-05 13:21:33 | +----+------------------+---------+--------------------+------+---------------------+
內關聯
現在通過關聯orderdetail和items表查詢出每個訂單詳情的商品名稱，商品價格，購買數量，購買總價格，sql語句如下：
#關聯查詢 mysql> select tm.name , tm.price ,od.items_num, (tm.price * od.items_num) AS sum_price from orderdetail AS od inner join items AS tm on od.items_id = tm.id; +-------------+---------+-----------+-----------+ | name | price | items_num | sum_price | +-------------+---------+-----------+-----------+ | MacBook Air | 8298.9 | 1 | 8298.9 | | MacBook Pro | 10982.0 | 3 | 32946.0 | | 背包 | 200.0 | 4 | 800.0 | | MacBook Pro | 10982.0 | 3 | 32946.0 | +-------------+---------+-----------+-----------+
文章列表
留言列表
