MySQL約束和表的復(fù)雜查詢操作大全
SQL查詢中各個(gè)關(guān)鍵字的執(zhí)行先后順序：
from > on> join > where > group by > with > having > select > distinct > order by > limit
一. 數(shù)據(jù)庫約束
約束是關(guān)系型數(shù)據(jù)庫的一個(gè)重要功能, 添加到庫中的數(shù)據(jù)需要保證其的正確性; 約束, 就是讓數(shù)據(jù)庫幫助程序員更好的檢查數(shù)據(jù)是否正確.
1. 數(shù)據(jù)庫常用約束
- not null - 指示某列不能存儲(chǔ) NULL 值.
- unique - 保證某列的每行必須有唯一的值.
- default - 規(guī)定沒有給列賦值時(shí)的默認(rèn)值.
- primary key - not null 和 unique 的結(jié)合。確保某列（或兩個(gè)列多個(gè)列的結(jié)合）有唯一標(biāo)識(shí)，有助于更容易更快速地找到表中的一個(gè)特定的記錄.
- foreign key - 保證一個(gè)表中的數(shù)據(jù)匹配另一個(gè)表中的值的參照完整性.
- check - 保證列中的值符合指定的條件; 對(duì)于MySQL數(shù)據(jù)庫，對(duì)check子句進(jìn)行分析，但是忽略check子句(MySQL5中不支持check).
2. not null 約束
在創(chuàng)建表的時(shí)候使用, 對(duì)某一列使用該約束, 則該列的值不能為null.
語法:
create table 表名 (變量 類型 not null, ...);
示例:
-- 創(chuàng)建student表并對(duì)id字段使用not null約束
mysql> create table student (id int not null, name varchar(20));
Query OK, 0 rows affected (0.01 sec)
-- id默認(rèn)值為null,但此時(shí)id不允許為null,所以插入數(shù)據(jù)時(shí)id要有具體的值
mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
-- id值為null,插入失敗
mysql> insert into student values (null, '張三');
ERROR 1048 (23000): Column 'id' cannot be null
-- 不指定id列,其默認(rèn)值為null,插入失敗
mysql> insert into student (name) values ('張三');
ERROR 1364 (HY000): Field 'id' doesn't have a default value
-- id不為null, 插入成功
mysql> insert into student values (1, '張三');
Query OK, 1 row affected (0.00 sec)注意:
not null 約束可以同時(shí)對(duì)多個(gè)列使用.
-- 如果存在student表則刪除 mysql> drop table if exists student; Query OK, 0 rows affected (0.01 sec) -- id和name字段都使用not null約束 mysql> create table student (id int not null, name varchar(20) not null); Query OK, 0 rows affected (0.02 sec) -- 查看表結(jié)構(gòu) mysql> desc student; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | | NULL | | | name | varchar(20) | NO | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec)
3. unique約束
在創(chuàng)建表的時(shí)候使用, 對(duì)某一列使用該約束, 則該列的值不能重復(fù).
語法:
create table 表名 (變量 類型 unique, ...);
示例:
mysql> drop table if exists student; Query OK, 0 rows affected (0.01 sec) -- 創(chuàng)建student表,字段id和name都使用約束unique mysql> create table student (id int unique, name varchar(20) unique); Query OK, 0 rows affected (0.02 sec) -- 查看表結(jié)構(gòu) mysql> desc student; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | UNI | NULL | | | name | varchar(20) | YES | UNI | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) -- 第一次插入數(shù)據(jù),無重復(fù)數(shù)據(jù),成功插入 mysql> insert into student values (1, '張三'); Query OK, 1 row affected (0.00 sec) -- 只要id/name字段中有重復(fù)的數(shù)據(jù),插入失敗 mysql> insert into student values (1, '李四'); ERROR 1062 (23000): Duplicate entry '1' for key 'id' mysql> insert into student values (2, '張三'); ERROR 1062 (23000): Duplicate entry '張三' for key 'name'
4. default設(shè)置默認(rèn)值
在創(chuàng)建表的時(shí)候使用, 可以設(shè)置列的默認(rèn)值.
語法:
create table 表名 (變量 類型 default 默認(rèn)值, ...);
示例:
mysql> drop table if exists student; Query OK, 0 rows affected (0.01 sec) -- 設(shè)置字段name的默認(rèn)值為'匿名' mysql> create table student (id int, name varchar(20) default '匿名'); Query OK, 0 rows affected (0.02 sec) -- 查看表結(jié)構(gòu) mysql> desc student; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | 匿名 | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) -- 只指定id列插入 mysql> insert into student (id) values (1); Query OK, 1 row affected (0.00 sec) -- name用默認(rèn)值填充 mysql> select * from student; +------+--------+ | id | name | +------+--------+ | 1 | 匿名 | +------+--------+ 1 row in set (0.00 sec)
5. primary key約束
primary key是主鍵約束, 是一條記錄的身份標(biāo)識(shí), 相當(dāng)于not null和 unique結(jié)合的效果, 在創(chuàng)建表時(shí)使用, 對(duì)`某一列使用該約束, 則該列的值必須是唯一的且不能是null.
實(shí)際開發(fā)中, 大部分的表, 一般都會(huì)帶有一個(gè)主鍵, 主鍵往往是一個(gè)整數(shù)表示的id.
語法:
create table 表名 (變量 類型 primary key, ...);
示例:
mysql> drop table if exists student; Query OK, 0 rows affected (0.00 sec) -- 設(shè)置id字段為主建 mysql> create table student (id int primary key, name varchar(20)); Query OK, 0 rows affected (0.01 sec) -- 查看表結(jié)構(gòu) mysql> desc student; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.01 sec) -- id不重復(fù),插入成功 mysql> insert into student values (1, '張三'); Query OK, 1 row affected (0.00 sec) -- id不重復(fù),插入成功 mysql> insert into student values (2, '李四'); Query OK, 1 row affected (0.00 sec) -- id重復(fù),插入失敗 mysql> insert into student values (1, '王五'); ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY' -- id為null,插入失敗 mysql> insert into student values (null, '趙六'); ERROR 1048 (23000): Column 'id' cannot be null -- 查看插入結(jié)果 mysql> select * from student; +----+--------+ | id | name | +----+--------+ | 1 | 張三 | | 2 | 李四 | +----+--------+ 2 rows in set (0.00 sec)
上面主鍵描述的列的值是我們自己手動(dòng)去添加, mysql中還支持自增主鍵, 使用自增主鍵描述列可以不去手動(dòng)設(shè)置值, 在插入記錄時(shí), 它會(huì)自動(dòng)從1開始自增(未設(shè)置初始值的情況下).
當(dāng)然, 使用了自增主鍵我們也可以去手動(dòng)添加值, 但當(dāng)我們手動(dòng)設(shè)置一個(gè)之后, 后面插入記錄再讓它自增, 此時(shí)的值會(huì)基于我們?cè)O(shè)置的那個(gè)值開始自增.
語法:
create table (變量 類型 primary key auto_increment, ...);
示例:
要注意的是下面插入記錄時(shí)id列的null并不是將id設(shè)置為null, 而是將id的賦值交給數(shù)據(jù)庫來使用自增主鍵
mysql> drop table if exists student;
Query OK, 0 rows affected (0.00 sec)
mysql> create table student (
-> id int primary key auto_increment,
-> name varchar(20)
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> insert into student values
-> (null, '張三'), (null, '李四'), (null, '王五'),
-> (100, '趙六'), (null, '趙錢'), (null, '孫李');
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> select * from student;
+-----+--------+
| id | name |
+-----+--------+
| 1 | 張三 |
| 2 | 李四 |
| 3 | 王五 |
| 100 | 趙六 |
| 101 | 趙錢 |
| 102 | 孫李 |
+-----+--------+
6 rows in set (0.00 sec)在mysql中,一個(gè)表中只能有一個(gè)主鍵, 雖然主鍵不能有多個(gè), 但mysql允許把多個(gè)列放到一起共同作為一個(gè)主鍵(聯(lián)合主鍵).
語法:
primary key (列名, 列名, ...);
示例:
mysql> drop table if exists student;
Query OK, 0 rows affected (0.01 sec)
mysql> create table student (
-> id int,
-> name varchar(20),
-> primary key (id, name)
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | NO | PRI | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)6. 分庫分表下的自增主鍵
學(xué)習(xí)階段的mysql的數(shù)據(jù)量較小, 所有的數(shù)據(jù)都在一個(gè)mysql服務(wù)器上, 此時(shí)每個(gè)表中的自增主鍵都可以很到的工作, 考慮下面的場(chǎng)景:
如果mysql的數(shù)據(jù)量很大，一臺(tái)主機(jī)放不下, 就需要進(jìn)行分庫分表, 使用多個(gè)主機(jī)來進(jìn)行存儲(chǔ), 本質(zhì)上就是,把一張大表分成兩個(gè)/多個(gè)小的表, 每個(gè)數(shù)據(jù)庫服務(wù)器, 分別只存一部分?jǐn)?shù)據(jù); 此時(shí)的增主鍵如何保證在多個(gè)主機(jī)上不重復(fù)呢?
在這個(gè)場(chǎng)景下, 如果再新插入一個(gè)數(shù)據(jù), 這個(gè)數(shù)據(jù)就會(huì)落在三個(gè)服務(wù)器之一, 新的這個(gè)數(shù)據(jù)的主鍵id，如何分配才能保證不重復(fù)呢?
這里涉及到一個(gè)"分布式系統(tǒng)中唯一id生成算法", 實(shí)現(xiàn)公式如下:
實(shí)現(xiàn)公式=時(shí)間戳＋主機(jī)編號(hào)＋隨機(jī)因子
時(shí)間戳保證在不同時(shí)間下的id不同, 再和主機(jī)編號(hào)組合保證如果再同一時(shí)間有不同的數(shù)據(jù)分散到不同主機(jī)上的id不同, 最后再和一個(gè)隨機(jī)因子組合保證多個(gè)數(shù)據(jù)到同一個(gè)主機(jī)時(shí)id不同, 結(jié)合這三個(gè)部分,就可以得到一個(gè)全局唯一的id.
7. foreign key約束
foreign key是外鍵約束, 用來約束兩張表之間的關(guān)系(相互約束), 在創(chuàng)建表時(shí)使用, 使用該約束要求表中某個(gè)記錄必須在另外一個(gè)表里存在.
例如一張學(xué)生表的字段有學(xué)號(hào), 姓名, 班級(jí)號(hào), 還有一張表是班級(jí)表, 字段有班級(jí)號(hào)和班級(jí)名, 該學(xué)生表中的學(xué)生所在班級(jí)都能在班級(jí)表里面找到, 此時(shí)就可以對(duì)學(xué)生表的班級(jí)號(hào)使用外鍵約束, 讓學(xué)生表與班級(jí)表聯(lián)系起來; 我們發(fā)現(xiàn)學(xué)生表中的數(shù)據(jù)要依賴于班級(jí)表的數(shù)據(jù), 班級(jí)表的數(shù)據(jù)對(duì)學(xué)生表產(chǎn)生約束力(父親對(duì)孩子有約束力), 此處起到約束作用的班級(jí)表就叫做"父表" (parent)，被約束的這個(gè)表就叫做“子表" (child).
語法:
-- 父表已經(jīng)創(chuàng)建的前提下 create table 表名 ( 變量 類型,... foreign key (子表中的一個(gè)變量) references 父表名 (父表中的一個(gè)變量) );
示例:
-- class為父表
mysql> create table class(
-> class_id int primary key auto_increment,
-> class_name varchar(20)
-> );
Query OK, 0 rows affected (0.02 sec)
-- 查看class表結(jié)構(gòu)
mysql> desc class;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| class_id | int(11) | NO | PRI | NULL | auto_increment |
| class_name | varchar(20) | YES | | NULL | |
+------------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
-- 給class表插入數(shù)據(jù)
mysql> insert into class values (null, '1班'), (null, '2班'), (null, '三班');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
-- 查看插入結(jié)果
mysql> select * from class;
+----------+------------+
| class_id | class_name |
+----------+------------+
| 1 | 1班 |
| 2 | 2班 |
| 3 | 三班 |
+----------+------------+
3 rows in set (0.00 sec)
mysql> drop table if exists student;
Query OK, 0 rows affected (0.00 sec)
-- student為子表, 字段class_id添加外鍵約束
mysql> create table student (
-> id int primary key auto_increment,
-> name varchar(20),
-> class_id int,
-> foreign key (class_id) references class(class_id)
-> );
Query OK, 0 rows affected (0.02 sec)
-- 查看student表結(jié)構(gòu)
mysql> desc student;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| class_id | int(11) | YES | MUL | NULL | |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
-- 給student表插入數(shù)據(jù)
mysql> insert into student (name, class_id) values
-> ('張三', 3), ('李四', 1), ('王五', 2), ('趙六', 1);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
-- 查看插入結(jié)果
mysql> select * from student;
+----+--------+----------+
| id | name | class_id |
+----+--------+----------+
| 1 | 張三 | 3 |
| 2 | 李四 | 1 |
| 3 | 王五 | 2 |
| 4 | 趙六 | 1 |
+----+--------+----------+
4 rows in set (0.00 sec)
-- 插入班級(jí)表中不存在的班級(jí),插入失敗
mysql> insert into student (name, 2) values ('孫李', 4);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '2) values ('孫李', 4)' at line 1
使用外鍵約束的數(shù)據(jù)表, 依賴于另一個(gè)表, 這個(gè)被依賴的表稱為父表, 被約束的表被稱為子表, 當(dāng)在子表插入記錄時(shí)，必須在父表中存在某一對(duì)應(yīng)關(guān)系才能插入.
外鍵約束不僅約束著子表,同時(shí)也約束著父表, 我們嘗試修改或者刪除班級(jí)表中class_id的值, 且學(xué)生表中存在班級(jí)為此id的學(xué)生, 此時(shí)是不能成功修改或刪除的.
mysql> update class set class_id = 5 where class_id = 1; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`java_rong`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `class` (`class_id`)) mysql> delete from class where class_id = 2; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`java_rong`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `class` (`class_id`)) mysql>
要?jiǎng)h除/修改的父表中的記錄, 前提是子表中沒有記錄與父表中的記錄相關(guān)聯(lián).
mysql> insert into class values(4, '四班'); Query OK, 1 row affected (0.01 sec) mysql> delete from class where class_id = 4; Query OK, 1 row affected (0.00 sec)
當(dāng)子表中插入的記錄和父表建立聯(lián)系后, 此時(shí)就不能直接刪除父表了, 要?jiǎng)h除父表要先刪除子表.
-- 直接刪除class表,刪除失敗 mysql> drop table class; ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails -- 先刪除子表student,刪除成功 mysql> drop table student; Query OK, 0 rows affected (0.00 sec) -- 刪除子表student后再刪除父表class,刪除成功 mysql> drop table class; Query OK, 0 rows affected (0.00 sec)
實(shí)際開發(fā)中, 很多時(shí)候刪除表并不會(huì)真的將數(shù)據(jù)都清除, 而是采用邏輯刪除, 也就是再表中來設(shè)置一個(gè)屬性開關(guān), 通過這個(gè)屬性來看是否可以使用表中的數(shù)據(jù), 這樣做就可以避免觸發(fā)外鍵約束的限制了.
8. 主鍵,外鍵,uniqe運(yùn)用原理
當(dāng)字段被主鍵/unique約束時(shí), 是如何保證記錄不重復(fù)的?
當(dāng)字段被外鍵約束時(shí), 如何知道子表中待插入的數(shù)據(jù)在父表中是否存在?
其實(shí)不難想到, 不管是使用上面的哪個(gè)約束, 先要有查詢過程, 查詢滿足條件后才會(huì)完成插入操作.
但要注意這里的查詢不會(huì)是遍歷查詢, 那樣的話時(shí)間開銷就太大了, mysql中通過索引來完成查詢, 索引能加快查詢效率(mysql專欄中寫有介紹索引的博客可以參考).
對(duì)于外鍵約束, 子表插入數(shù)據(jù)前在父表會(huì)有查詢操作, 被依賴的這一列必須要要有索, 如果使用primary key或者unique約束該列, 則該列會(huì)自動(dòng)創(chuàng)建索引.
所以上面的學(xué)生表與班級(jí)表的示例, 雖然我們沒有主動(dòng)對(duì)父表中的class_id列創(chuàng)建索引, 但是該列使用了主鍵約束, 就會(huì)自動(dòng)創(chuàng)建索引.
二. 表的設(shè)計(jì)
1. 一對(duì)一關(guān)系
以學(xué)校中的教務(wù)系統(tǒng)為例, 學(xué)生這個(gè)實(shí)體有學(xué)號(hào),姓名等屬性, 登錄教務(wù)系統(tǒng)的賬號(hào)也是一個(gè)實(shí)體, 有用戶名,密碼等屬性; 顯而易見的, 一個(gè)學(xué)生只能有一個(gè)賬號(hào), 像這樣就是一對(duì)一的關(guān)系.
那么最重要的就是要在數(shù)據(jù)庫中表示這種關(guān)系.
方案一
可以將這兩個(gè)實(shí)體信息在一個(gè)表中描述, 那么一條記錄就對(duì)應(yīng)一個(gè)關(guān)系.
方案二
分別有學(xué)生表和用戶表兩張表相互關(guān)聯(lián), 學(xué)生表中添加一列來與用戶表關(guān)聯(lián)或者用戶表中添加一列來與學(xué)生表關(guān)聯(lián)
2. 一對(duì)多關(guān)系
例如學(xué)生與班級(jí)之間的關(guān)系就是一對(duì)多的關(guān)系, 一個(gè)學(xué)生只能在一個(gè)班級(jí)中, 而一個(gè)班級(jí)可以有多名學(xué)生.
用如下方式在數(shù)據(jù)庫中表示一對(duì)多的關(guān)系.
方案一
在班級(jí)表中添加一列, 用來存放一個(gè)班里面所有學(xué)生的記錄, 但這種想法在mysql中是實(shí)現(xiàn)不了的, 因?yàn)閙ysql中沒有類似于數(shù)組的類型.
方案二
在學(xué)生表中添加一列, 存放學(xué)生所在的班級(jí).
3. 多對(duì)多關(guān)系
學(xué)生與課程之間多對(duì)多的關(guān)系, 一個(gè)學(xué)生可以學(xué)習(xí)多門課程, 一門課程中有多名學(xué)生學(xué)習(xí).
只有一種方案來表示這種關(guān)系, 如下:
建立一個(gè)關(guān)聯(lián)表, 來關(guān)聯(lián)學(xué)生表和課程表, 這個(gè)關(guān)聯(lián)表中至少有兩列, 一列用來存放學(xué)生學(xué)號(hào)與學(xué)生表關(guān)聯(lián), 另一列存放課程號(hào)與課程表關(guān)聯(lián), 這樣兩表就可以通過這個(gè)關(guān)聯(lián)表來實(shí)現(xiàn)多對(duì)多的關(guān)系.
補(bǔ)充: 建表時(shí)可以使用合適的約束使得數(shù)據(jù)間關(guān)系的描述更準(zhǔn)確.
三. 表的復(fù)雜查詢操作
1. 將一個(gè)表中的數(shù)據(jù)插入到另一個(gè)表中
將表a中的數(shù)據(jù)插入到表b, 實(shí)質(zhì)上是先查詢a表中的數(shù)據(jù)生成臨時(shí)表, 再將臨時(shí)表中的數(shù)據(jù)插入表中, 要注意的是查詢出來的記錄(臨時(shí)表)的列需要和表b相對(duì)應(yīng)才能插入成功.
語法:
insert into b select select 與表b列相對(duì)應(yīng)的列 from a;
示例:
下面有a,b,c三個(gè)表, 其中a,b兩表的列是對(duì)應(yīng)的, c和a的不對(duì)應(yīng), 將a表中的數(shù)據(jù)插入到b表和c表中.
mysql> create table a (id int, name varchar(20));
Query OK, 0 rows affected (0.05 sec)
mysql> insert into a values
-> (1,'喜羊羊'), (2, '美羊羊'), (3, '懶洋洋');
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from a;
+------+-----------+
| id | name |
+------+-----------+
| 1 | 喜羊羊 |
| 2 | 美羊羊 |
| 3 | 懶洋洋 |
+------+-----------+
3 rows in set (0.00 sec)
mysql> create table b (id int, name varchar(20));
Query OK, 0 rows affected (0.03 sec)
-- a表和b表的列相對(duì)應(yīng),不需要調(diào)整
mysql> insert into b select * from a;
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from b;
+------+-----------+
| id | name |
+------+-----------+
| 1 | 喜羊羊 |
| 2 | 美羊羊 |
| 3 | 懶洋洋 |
+------+-----------+
3 rows in set (0.00 sec)
mysql> create table c (name varchar(20), id int);
Query OK, 0 rows affected (0.03 sec)
-- a和c的列不對(duì)應(yīng),需要調(diào)整
mysql> insert into c select name, id from a;
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from c;
+-----------+------+
| name | id |
+-----------+------+
| 喜羊羊 | 1 |
| 美羊羊 | 2 |
| 懶洋洋 | 3 |
+-----------+------+
3 rows in set (0.00 sec)
2. 聚合查詢
2.1 聚合函數(shù)
| 函數(shù) | 說明 |
|---|---|
| count(列名或表達(dá)式) | 返回查詢到的數(shù)據(jù)的個(gè)數(shù) |
| sum(列名或表達(dá)式) | 返回查詢到的數(shù)據(jù)的和 |
| avg(列名或表達(dá)式) | 返回查詢到的數(shù)據(jù)的平均值 |
| max(列名或表達(dá)式) | 返回查詢到的數(shù)據(jù)的最大值 |
| min（列名或表達(dá)式) | 返回查詢到的數(shù)據(jù)的最小值 |
注意: 代碼中的函數(shù)名和( )之間不能有空格.
上面的聚合函數(shù)在使用時(shí)可以在列名或表達(dá)式前加上關(guān)鍵字distinct先讓查詢到的數(shù)據(jù)去重, 然后再進(jìn)行計(jì)算.
這些聚合函數(shù)是針對(duì)一個(gè)或多個(gè)列的行來進(jìn)行運(yùn)算的, 其中sum,avg,max,min這幾個(gè)聚合函數(shù)只能針對(duì)數(shù)值類型進(jìn)行計(jì)算, 不能是字符串和日期類型.
下面給出這些聚合函數(shù)的一些使用示例, 首先創(chuàng)建表并插入數(shù)據(jù), 如下:
-- 創(chuàng)建考試成績(jī)表
mysql> create table exam_result (
-> id int,
-> name varchar(20), -- 姓名
-> chinese decimal(4, 1), -- 語文成績(jī)
-> math decimal(4, 1), -- 數(shù)學(xué)成績(jī)
-> english decimal(4, 1) -- 英語成績(jī)
-> );
Query OK, 0 rows affected (0.02 sec)
-- 插入數(shù)據(jù)
mysql> insert into exam_result values
-> (1, '喜羊羊', 67, 98, 56),
-> (2, '懶羊羊', 87.5, 78, 77),
-> (3, '美羊羊', 88, 98.5, 90),
-> (4, '沸羊羊', 82, 84, 67),
-> (5, '暖羊羊', 55.5, 85, 45),
-> (6, '黑大帥', 70, 73, 78.5),
-> (7, '瀟灑哥', null, 75, 65),
-> (8, null, null, 75, 65);
Query OK, 8 rows affected (0.01 sec)
Records: 8 Duplicates: 0 Warnings: 0
-- 插入結(jié)果
mysql> select * from exam_result;
+------+-----------+---------+------+---------+
| id | name | chinese | math | english |
+------+-----------+---------+------+---------+
| 1 | 喜羊羊 | 67.0 | 98.0 | 56.0 |
| 2 | 懶羊羊 | 87.5 | 78.0 | 77.0 |
| 3 | 美羊羊 | 88.0 | 98.5 | 90.0 |
| 4 | 沸羊羊 | 82.0 | 84.0 | 67.0 |
| 5 | 暖羊羊 | 55.5 | 85.0 | 45.0 |
| 6 | 黑大帥 | 70.0 | 73.0 | 78.5 |
| 7 | 瀟灑哥 | NULL | 75.0 | 65.0 |
| 8 | NULL | NULL | 75.0 | 65.0 |
+------+-----------+---------+------+---------+
8 rows in set (0.00 sec)count函數(shù)
使用count函數(shù)可以計(jì)算數(shù)據(jù)表中有多少行, 統(tǒng)計(jì)全表行數(shù)可以直接使用*來匹配所有的行或者使用一個(gè)常量表達(dá)式.
-- 表中有8行數(shù)據(jù) mysql> select count(*) from exam_result; +----------+ | count(*) | +----------+ | 8 | +----------+ 1 row in set (0.00 sec) -- 函數(shù)參數(shù)給一個(gè)常量表達(dá)式也行 mysql> select count(1) from exam_result; +----------+ | count(1) | +----------+ | 8 | +----------+ 1 row in set (0.00 sec) -- 也可以起一個(gè)別名 mysql> select count(*) as 全表行數(shù) from exam_result; +--------------+ | 全表行數(shù) | +--------------+ | 8 | +--------------+ 1 row in set (0.00 sec)
還有一個(gè)需要注意的點(diǎn), 聚合函數(shù)在計(jì)算時(shí)不會(huì)將null計(jì)入在內(nèi), 比如統(tǒng)計(jì)班級(jí)中的有多個(gè)有效的語文成績(jī).
-- chinese 為 NULL 的數(shù)據(jù)不會(huì)計(jì)入結(jié)果 mysql> select count(chinese) from exam_result; +----------------+ | count(chinese) | +----------------+ | 6 | +----------------+ 1 row in set (0.00 sec)
sum函數(shù)
-- 統(tǒng)計(jì)所有同學(xué)語文成績(jī)的總和 mysql> select sum(chinese) from exam_result; +--------------+ | sum(chinese) | +--------------+ | 450.0 | +--------------+ 1 row in set (0.00 sec) -- 統(tǒng)計(jì)英語成績(jī)不及格同學(xué)(<60)成績(jī)的總和 mysql> select sum(english) from exam_result where english < 60; +--------------+ | sum(english) | +--------------+ | 101.0 | +--------------+ 1 row in set (0.00 sec) -- 如果沒有匹配的記錄, 返回null mysql> select sum(english) from exam_result where english < 10; +--------------+ | sum(english) | +--------------+ | NULL | +--------------+ 1 row in set (0.00 sec)
avg函數(shù)
-- 統(tǒng)計(jì)所有同學(xué)總分的平均分 mysql> select avg(chinese + math + english) as 平均總分 from exam_result; +--------------+ | 平均總分 | +--------------+ | 230.00000 | +--------------+ 1 row in set (0.01 sec)
max函數(shù)
-- 統(tǒng)計(jì)英語最高分 mysql> select max(english) from exam_result; +--------------+ | max(english) | +--------------+ | 90.0 | +--------------+ 1 row in set (0.00 sec)
min函數(shù)
-- 統(tǒng)計(jì) > 70 分以上的數(shù)學(xué)最低分 mysql> select min(math) from exam_result where math > 70; +-----------+ | min(math) | +-----------+ | 73.0 | +-----------+ 1 row in set (0.00 sec)
最后要注意聚合函數(shù)是不能嵌套使用的.
mysql> select count(count(math)) from exam_result; ERROR 1111 (HY000): Invalid use of group function
2.2 分組查詢
sql中分組操作通過group by關(guān)鍵字實(shí)現(xiàn), 一般和聚合函數(shù)結(jié)合使用, 通過指定分組條件實(shí)現(xiàn)分組查詢.
語法:
select 列, ... from 表名 (條件篩選) group by 分組條件,即列名;
其中, 上面的條件篩選可以使用where, order by, limit等來實(shí)現(xiàn), 條件篩選不是必寫項(xiàng).
示例:
創(chuàng)建一個(gè)不同職位的薪水表
-- 創(chuàng)建員工表
mysql> create table emp (
-> id int primary key auto_increment,
-> name varchar(20) not null,
-> role varchar(20) not null,
-> salary decimal(20, 2)
-> );
Query OK, 0 rows affected (0.03 sec)
-- 添加記錄
mysql> insert into emp values
-> (null, "馬云", "老板", 100000000),
-> (null, "馬化騰", "老板", 120000000),
-> (null, "張三", "開發(fā)", 10000),
-> (null, "李四", "開發(fā)", 11000),
-> (null, "王五", "開發(fā)", 11000),
-> (null, "趙六", "測(cè)試", 8000),
-> (null, "孫李", "測(cè)試", 9000);
Query OK, 7 rows affected (0.00 sec)
Records: 7 Duplicates: 0 Warnings: 0
-- 查看表中數(shù)據(jù)
mysql> select * from emp;
+----+-----------+--------+--------------+
| id | name | role | salary |
+----+-----------+--------+--------------+
| 1 | 馬云 | 老板 | 100000000.00 |
| 2 | 馬化騰 | 老板 | 120000000.00 |
| 3 | 張三 | 開發(fā) | 10000.00 |
| 4 | 李四 | 開發(fā) | 11000.00 |
| 5 | 王五 | 開發(fā) | 11000.00 |
| 6 | 趙六 | 測(cè)試 | 8000.00 |
| 7 | 孫李 | 測(cè)試 | 9000.00 |
+----+-----------+--------+--------------+
7 rows in set (0.00 sec)查詢每種崗位員工薪水的平均值, 最高值, 最低值.
mysql> select role, avg(salary), max(salary), min(salary) from emp group by role;
+--------+------------------+--------------+--------------+
| role | avg(salary) | max(salary) | min(salary) |
+--------+------------------+--------------+--------------+
| 開發(fā) | 10666.666667 | 11000.00 | 10000.00 |
| 測(cè)試 | 8500.000000 | 9000.00 | 8000.00 |
| 老板 | 110000000.000000 | 120000000.00 | 100000000.00 |
+--------+------------------+--------------+--------------+
3 rows in set (0.03 sec)
-- 也可以起一個(gè)別名~~
mysql> select role, avg(salary) as 平均薪水,
-> max(salary) as 最高薪水, min(salary) as 最低薪水 from emp group by role;
+--------+------------------+--------------+--------------+
| role | 平均薪水 | 最高薪水 | 最低薪水 |
+--------+------------------+--------------+--------------+
| 開發(fā) | 10666.666667 | 11000.00 | 10000.00 |
| 測(cè)試 | 8500.000000 | 9000.00 | 8000.00 |
| 老板 | 110000000.000000 | 120000000.00 | 100000000.00 |
+--------+------------------+--------------+--------------+
3 rows in set (0.00 sec)2.3 having
在上面分組查詢的基礎(chǔ)上, 分組查詢也可以添加指定條件, 這里的條件分有下面兩種情況:
- 分組之前指定條件, 也就是先篩選再分組, 使用
where關(guān)鍵字. - 分組之后指定條件, 也就是先分組再篩選, 使用
group by關(guān)鍵字.
上面的兩種指定條件的查詢方式可以同時(shí)都使用, 也可以只使用其中一種.
語法:
關(guān)于where和group by語法上有一點(diǎn)要注意區(qū)分, where語句緊跟在表名后, 而having跟在group by后.
-- having語法 select 聚合函數(shù)(或者列), ... from 表名 group by 列 having 條件; -- where語法 select 聚合函數(shù)(或者列), ... from 表名 where 條件 group by 列;
示例:
老板的共資太高, 不能代表大眾的平均薪資, 查詢結(jié)果不顯示老板的平均薪資.
-- 使用where示例 mysql> select role, avg(salary) as 平均薪水 from emp where role != '老板' group by role; +--------+--------------+ | role | 平均薪水 | +--------+--------------+ | 開發(fā) | 10666.666667 | | 測(cè)試 | 8500.000000 | +--------+--------------+ 2 rows in set (0.02 sec) -- 使用having示例 mysql> select role, avg(salary) as 平均薪水 from emp group by role having role != '老板'; +--------+--------------+ | role | 平均薪水 | +--------+--------------+ | 開發(fā) | 10666.666667 | | 測(cè)試 | 8500.000000 | +--------+--------------+ 2 rows in set (0.00 sec)
3. 多表查詢(聯(lián)合查詢)
3.1 笛卡爾積
笛卡爾乘積是指在數(shù)學(xué)中, 兩個(gè)集合X和Y的笛卡尓積(Cartesian product), 又稱直積, 表示為X×Y, 第一個(gè)對(duì)象是X的成員而第二個(gè)對(duì)象是Y的所有可能有序?qū)Φ钠渲幸粋€(gè)成員.
假設(shè)集合A={a, b}, 集合B={0, 1, 2}, 則兩個(gè)集合的笛卡爾積為
{(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}.
聯(lián)合查詢也叫多表查詢, 是基于笛卡爾積來實(shí)現(xiàn)的, 多表查詢先基于笛卡爾積將多個(gè)表合并, 然后對(duì)合并后的表去篩選有效的記錄.
將多個(gè)表進(jìn)行笛卡爾積的語法:
select 列, ... from 表名1, 表名2, ...;
示例:
將下面的student和class兩個(gè)表進(jìn)行笛卡爾積.
-- class表,有兩條記錄代表2個(gè)班級(jí)
mysql> create table class (class_id int, name varchar(20));
Query OK, 0 rows affected (0.02 sec)
mysql> insert into class values (1, '軟件1班'), (2, '軟件2班');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from class;
+----------+------------+
| class_id | name |
+----------+------------+
| 1 | 軟件1班 |
| 2 | 軟件2班 |
+----------+------------+
2 rows in set (0.00 sec)
-- student表,有四條記錄代表4個(gè)班級(jí)
mysql> create table student(id int, name varchar(20), class_id int);
Query OK, 0 rows affected (0.02 sec)
mysql> insert into student values
-> (1, '張三', 1), (2, '李四', 1), (3, '王五', 2), (4, '趙六', 2);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from student;
+------+--------+----------+
| id | name | class_id |
+------+--------+----------+
| 1 | 張三 | 1 |
| 2 | 李四 | 1 |
| 3 | 王五 | 2 |
| 4 | 趙六 | 2 |
+------+--------+----------+
4 rows in set (0.00 sec)
-- 兩個(gè)表進(jìn)行笛卡爾積,得到 4*2=8 條記錄
mysql> select * from student, class;
+------+--------+----------+----------+------------+
| id | name | class_id | class_id | name |
+------+--------+----------+----------+------------+
| 1 | 張三 | 1 | 1 | 軟件1班 |
| 1 | 張三 | 1 | 2 | 軟件2班 |
| 2 | 李四 | 1 | 1 | 軟件1班 |
| 2 | 李四 | 1 | 2 | 軟件2班 |
| 3 | 王五 | 2 | 1 | 軟件1班 |
| 3 | 王五 | 2 | 2 | 軟件2班 |
| 4 | 趙六 | 2 | 1 | 軟件1班 |
| 4 | 趙六 | 2 | 2 | 軟件2班 |
+------+--------+----------+----------+------------+
8 rows in set (0.00 sec)
對(duì)n條記錄的表A和m條記錄的表B進(jìn)行笛卡爾積，一共會(huì)產(chǎn)生n*m條記錄, 當(dāng)兩張表的數(shù)據(jù)量很大的時(shí)候, 這個(gè)操作就非常危險(xiǎn)了, 所以多表查詢?cè)趯?shí)際工作中要慎重使用.
上面得到兩表的笛卡爾積的結(jié)果后, 觀察表中數(shù)據(jù), 只有兩表的class_id相等的記錄才是有效數(shù)據(jù), 所以我們還需要再通過一些限制條件來篩選出有效的數(shù)據(jù).
上面這兩張表中存在同名情況, 可以使用表名.列名的形式來訪問區(qū)分對(duì)應(yīng)表中的列.
-- 篩選出有效數(shù)據(jù)
mysql> select id, student.name,student.class_id, class.name
-> from student, class where student.class_id = class.class_id;
+------+--------+----------+------------+
| id | name | class_id | name |
+------+--------+----------+------------+
| 1 | 張三 | 1 | 軟件1班 |
| 2 | 李四 | 1 | 軟件1班 |
| 3 | 王五 | 2 | 軟件2班 |
| 4 | 趙六 | 2 | 軟件2班 |
+------+--------+----------+------------+
4 rows in set (0.00 sec)下面再創(chuàng)建幾個(gè)表來演示之后的內(nèi)容
drop table if exists classes;
drop table if exists student;
drop table if exists course;
drop table if exists score;
create table classes (id int primary key auto_increment, name varchar(20), `desc` varchar(100));
create table student (id int primary key auto_increment, sn varchar(20), name varchar(20), qq_mail varchar(20) ,
classes_id int);
create table course(id int primary key auto_increment, name varchar(20));
create table score(score decimal(3, 1), student_id int, course_id int);
insert into classes(name, `desc`) values
('計(jì)算機(jī)系2019級(jí)1班', '學(xué)習(xí)了計(jì)算機(jī)原理、C和Java語言、數(shù)據(jù)結(jié)構(gòu)和算法'),
('中文系2019級(jí)3班','學(xué)習(xí)了中國(guó)傳統(tǒng)文學(xué)'),
('自動(dòng)化2019級(jí)5班','學(xué)習(xí)了機(jī)械自動(dòng)化');
insert into student(sn, name, qq_mail, classes_id) values
('09982','黑旋風(fēng)李逵','xuanfeng@qq.com',1),
('00835','菩提老祖',null,1),
('00391','白素貞',null,1),
('00031','許仙','xuxian@qq.com',1),
('00054','不想畢業(yè)',null,1),
('51234','好好說話','say@qq.com',2),
('83223','tellme',null,2),
('09527','老外學(xué)中文','foreigner@qq.com',2);
insert into course(name) values
('Java'),('中國(guó)傳統(tǒng)文化'),('計(jì)算機(jī)原理'),('語文'),('高階數(shù)學(xué)'),('英文');
insert into score(score, student_id, course_id) values
-- 黑旋風(fēng)李逵
(70.5, 1, 1),(98.5, 1, 3),(33, 1, 5),(98, 1, 6),
-- 菩提老祖
(60, 2, 1),(59.5, 2, 5),
-- 白素貞
(33, 3, 1),(68, 3, 3),(99, 3, 5),
-- 許仙
(67, 4, 1),(23, 4, 3),(56, 4, 5),(72, 4, 6),
-- 不想畢業(yè)
(81, 5, 1),(37, 5, 5),
-- 好好說話
(56, 6, 2),(43, 6, 4),(79, 6, 6),
-- tellme
(80, 7, 2),(92, 7, 6),
一共有四張表, classes為班級(jí)表, student為學(xué)生表, course表為課程表, score為成績(jī)表, 其中學(xué)生與班級(jí)的關(guān)系是一對(duì)多，學(xué)生與課程之間的關(guān)系是多對(duì)多.
drop table if exists classes;
drop table if exists student;
drop table if exists course;
drop table if exists score;
create table classes (
id int primary key auto_increment,
name varchar(20),
`desc` varchar(100)
);
create table student (
id int primary key auto_increment,
sn varchar(20), name varchar(20),
qq_mail varchar(20),
classes_id int
);
create table course (
id int primary key auto_increment,
name varchar(20)
);
create table score (
score decimal(3, 1),
student_id int,
course_id int
);
insert into classes(name, `desc`) values
('計(jì)算機(jī)系2019級(jí)1班', '學(xué)習(xí)了計(jì)算機(jī)原理、C和Java語言、數(shù)據(jù)結(jié)構(gòu)和算法'),
('中文系2019級(jí)3班','學(xué)習(xí)了中國(guó)傳統(tǒng)文學(xué)'),
('自動(dòng)化2019級(jí)5班','學(xué)習(xí)了機(jī)械自動(dòng)化');
insert into student(sn, name, qq_mail, classes_id) values
('09982','黑旋風(fēng)李逵','xuanfeng@qq.com',1),
('00835','菩提老祖',null,1),
('00391','白素貞',null,1),
('00031','許仙','xuxian@qq.com',1),
('00054','不想畢業(yè)',null,1),
('51234','好好說話','say@qq.com',2),
('83223','tellme',null,2),
('09527','老外學(xué)中文','foreigner@qq.com',2);
insert into course(name) values
('Java'),('中國(guó)傳統(tǒng)文化'),('計(jì)算機(jī)原理'),
('語文'),('高階數(shù)學(xué)'),('英文');
insert into score(score, student_id, course_id) values
-- 黑旋風(fēng)李逵
(70.5, 1, 1),(98.5, 1, 3),(33, 1, 5),(98, 1, 6),
-- 菩提老祖
(60, 2, 1),(59.5, 2, 5),
-- 白素貞
(33, 3, 1),(68, 3, 3),(99, 3, 5),
-- 許仙
(67, 4, 1),(23, 4, 3),(56, 4, 5),(72, 4, 6),
-- 不想畢業(yè)
(81, 5, 1),(37, 5, 5),
-- 好好說話
(56, 6, 2),(43, 6, 4),(79, 6, 6),
-- tellme
(80, 7, 2),(92, 7, 6);
mysql> select * from classes;
+----+-------------------------+-------------------------------------------------------------------+
| id | name | desc |
+----+-------------------------+-------------------------------------------------------------------+
| 1 | 計(jì)算機(jī)系2019級(jí)1班 | 學(xué)習(xí)了計(jì)算機(jī)原理、C和Java語言、數(shù)據(jù)結(jié)構(gòu)和算法 |
| 2 | 中文系2019級(jí)3班 | 學(xué)習(xí)了中國(guó)傳統(tǒng)文學(xué) |
| 3 | 自動(dòng)化2019級(jí)5班 | 學(xué)習(xí)了機(jī)械自動(dòng)化 |
+----+-------------------------+-------------------------------------------------------------------+
3 rows in set (0.00 sec)
mysql> select * from student;
+----+-------+-----------------+------------------+------------+
| id | sn | name | qq_mail | classes_id |
+----+-------+-----------------+------------------+------------+
| 1 | 09982 | 黑旋風(fēng)李逵 | xuanfeng@qq.com | 1 |
| 2 | 00835 | 菩提老祖 | NULL | 1 |
| 3 | 00391 | 白素貞 | NULL | 1 |
| 4 | 00031 | 許仙 | xuxian@qq.com | 1 |
| 5 | 00054 | 不想畢業(yè) | NULL | 1 |
| 6 | 51234 | 好好說話 | say@qq.com | 2 |
| 7 | 83223 | tellme | NULL | 2 |
| 8 | 09527 | 老外學(xué)中文 | foreigner@qq.com | 2 |
+----+-------+-----------------+------------------+------------+
8 rows in set (0.00 sec)
mysql> select * from course;
+----+--------------------+
| id | name |
+----+--------------------+
| 1 | Java |
| 2 | 中國(guó)傳統(tǒng)文化 |
| 3 | 計(jì)算機(jī)原理 |
| 4 | 語文 |
| 5 | 高階數(shù)學(xué) |
| 6 | 英文 |
+----+--------------------+
6 rows in set (0.00 sec)
mysql> select * from score;
+-------+------------+-----------+
| score | student_id | course_id |
+-------+------------+-----------+
| 70.5 | 1 | 1 |
| 98.5 | 1 | 3 |
| 33.0 | 1 | 5 |
| 98.0 | 1 | 6 |
| 60.0 | 2 | 1 |
| 59.5 | 2 | 5 |
| 33.0 | 3 | 1 |
| 68.0 | 3 | 3 |
| 99.0 | 3 | 5 |
| 67.0 | 4 | 1 |
| 23.0 | 4 | 3 |
| 56.0 | 4 | 5 |
| 72.0 | 4 | 6 |
| 81.0 | 5 | 1 |
| 37.0 | 5 | 5 |
| 56.0 | 6 | 2 |
| 43.0 | 6 | 4 |
| 79.0 | 6 | 6 |
| 80.0 | 7 | 2 |
| 92.0 | 7 | 6 |
+-------+------------+-----------+
20 rows in set (0.00 sec)
下面的內(nèi)容就是根據(jù)這幾張表來示范多表查詢的, 多表查詢進(jìn)行笛卡爾積再篩選出有效數(shù)據(jù)其實(shí)是在將多個(gè)表進(jìn)行連接的的過程.
常用的連接方式有: 內(nèi)連接和外連接(外連接分為左外連接和右外連接), 如果多表之間的記錄數(shù)據(jù)均有對(duì)應(yīng), 內(nèi)外連接的查詢結(jié)果是沒有區(qū)別的; 而如果多表之間的記錄數(shù)據(jù)有存在不對(duì)應(yīng)的情況, 那么內(nèi)外連接就有一定的區(qū)別了, 內(nèi)鏈接只會(huì)查詢顯示多表對(duì)應(yīng)的記錄, 左外連接會(huì)把左表的記錄都顯示出來, 右表中不對(duì)應(yīng)的地方用null填充, 而右外連接就會(huì)把右表的記錄都顯示出來, 左表中不對(duì)應(yīng)的地方用null填充.
3.2 內(nèi)連接
語法:
其中inner可以省略
select 字段 from 表1, 表2, ... where 條件; select 字段 from 表1 inner join 表2 on 條件 join 表3 on 條件...;
類連接查詢類似于交集, 如下圖:
示例:
查詢?cè)S仙同學(xué)的成績(jī).
這里來逐步分析出正確的sql語句.
1.首先成績(jī)的獲取是從分?jǐn)?shù)表中獲取, 還需要獲取許仙的個(gè)人信息, 所以需要對(duì)學(xué)生表和分?jǐn)?shù)表進(jìn)行笛卡爾積.
select * from student, score;
2.然后加入連接條件篩選出有效的數(shù)據(jù);.
select * from student, score where student.id = score.student_id; select * from student, score where student.id = score.student_id;
3.再根據(jù)需求加入必要的限制條件.
select * from student, score where student.id = score.student_id;
4.最后再把不必要的列去掉, 對(duì)查詢的列進(jìn)行精簡(jiǎn), 只保留要輸出的列得到最終結(jié)果.
mysql> select student.name, score.score from student, score
-> where student.id = score.student_id and student.name = '許仙';
+--------+-------+
| name | score |
+--------+-------+
| 許仙 | 67.0 |
| 許仙 | 23.0 |
| 許仙 | 56.0 |
| 許仙 | 72.0 |
+--------+-------+
4 rows in set (0.01 sec)也可以使用join on關(guān)鍵字實(shí)現(xiàn).
select student.name, score,score from student join score on student.id = score.student_id and name = '許仙';
查詢所有同學(xué)的總成績(jī)及個(gè)人信息.
1.需要先將學(xué)生表和分?jǐn)?shù)表進(jìn)行笛卡爾積
select * from student, score;
2.加上連接條件,篩選出有效數(shù)據(jù)
select * from student, score where student.id = score.student_id;
3.將所有記錄以姓名分組, 再使用sum()函數(shù)計(jì)算總分完成聚合查詢.
mysql> select name, sum(score.score) as 總分 from student, score
-> where student.id = score.student_id group by student.name;
+-----------------+--------+
| name | 總分 |
+-----------------+--------+
| tellme | 172.0 |
| 不想畢業(yè) | 118.0 |
| 好好說話 | 178.0 |
| 白素貞 | 200.0 |
| 菩提老祖 | 119.5 |
| 許仙 | 218.0 |
| 黑旋風(fēng)李逵 | 300.0 |
+-----------------+--------+
7 rows in set (0.01 sec)也可以使用join on實(shí)現(xiàn)
select name, sum(score.score) as 總分 from student join score on student.id = score.student_id group by student.name;
查看所有同學(xué)的各科成績(jī)及個(gè)人信息.
1.先對(duì)學(xué)生表, 課程表, 成績(jī)表進(jìn)行笛卡爾積.
select * from student, course, score;
2。加入連接條件, 三張表需要兩個(gè)連接條件.
select * from student, course, score where student.id = score.student_id and course.id = score.course_id;
3.最后根據(jù)精簡(jiǎn)要顯示的列完成查詢.
mysql> select student.name as 學(xué)生姓名, course.name as 課程名稱, score.score from student, course, score
-> where student.id = score.student_id and course.id = score.course_id;
+-----------------+--------------------+-------+
| 學(xué)生姓名 | 課程名稱 | score |
+-----------------+--------------------+-------+
| 黑旋風(fēng)李逵 | Java | 70.5 |
| 黑旋風(fēng)李逵 | 計(jì)算機(jī)原理 | 98.5 |
| 黑旋風(fēng)李逵 | 高階數(shù)學(xué) | 33.0 |
| 黑旋風(fēng)李逵 | 英文 | 98.0 |
| 菩提老祖 | Java | 60.0 |
| 菩提老祖 | 高階數(shù)學(xué) | 59.5 |
| 白素貞 | Java | 33.0 |
| 白素貞 | 計(jì)算機(jī)原理 | 68.0 |
| 白素貞 | 高階數(shù)學(xué) | 99.0 |
| 許仙 | Java | 67.0 |
| 許仙 | 計(jì)算機(jī)原理 | 23.0 |
| 許仙 | 高階數(shù)學(xué) | 56.0 |
| 許仙 | 英文 | 72.0 |
| 不想畢業(yè) | Java | 81.0 |
| 不想畢業(yè) | 高階數(shù)學(xué) | 37.0 |
| 好好說話 | 中國(guó)傳統(tǒng)文化 | 56.0 |
| 好好說話 | 語文 | 43.0 |
| 好好說話 | 英文 | 79.0 |
| tellme | 中國(guó)傳統(tǒng)文化 | 80.0 |
| tellme | 英文 | 92.0 |
+-----------------+--------------------+-------+
20 rows in set (0.00 sec)也可以使用join on實(shí)現(xiàn)
select student.name as 學(xué)生姓名, course.name as 課程名稱, score.score from student join score on student.id = score.student_id join course on course.id = score.course_id;
3.3 外連接
外連接分為左外連接，右外連接, 左外連接是以左表為主, 右外連接以右表為主.
外連接是通過join on關(guān)鍵字來實(shí)現(xiàn).
語法:
-- 左外連接 select 字段 from 表A left join 表B on 條件 ...; -- 右外連接 select 字段 from 表A right join 表B on 條件 ...;
示例:
-- 建表A和B mysql> create table A(id int, name varchar(20)); Query OK, 0 rows affected (0.02 sec) mysql> insert into A values (1, "張三"), (2, "李四"), (4, "王五"); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> create table B(A_id int, score decimal(4,1)); Query OK, 0 rows affected (0.02 sec) mysql> insert into B values (1, 66.6), (2, 88.8), (3, 99.9); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from A; +------+--------+ | id | name | +------+--------+ | 1 | 張三 | | 2 | 李四 | | 4 | 王五 | +------+--------+ 3 rows in set (0.00 sec) mysql> select * from B; +------+-------+ | A_id | score | +------+-------+ | 1 | 66.6 | | 2 | 88.8 | | 3 | 99.9 | +------+-------+ 3 rows in set (0.00 sec)
使用左外連接多表查詢:
mysql> select * from A left join B on A.id=B.A_id; +------+--------+------+-------+ | id | name | A_id | score | +------+--------+------+-------+ | 1 | 張三 | 1 | 66.6 | | 2 | 李四 | 2 | 88.8 | | 4 | 王五 | NULL | NULL | +------+--------+------+-------+ 3 rows in set (0.00 sec)
使用右外連接多表查詢:
mysql> select * from A right join B on A.id=B.A_id; +------+--------+------+-------+ | id | name | A_id | score | +------+--------+------+-------+ | 1 | 張三 | 1 | 66.6 | | 2 | 李四 | 2 | 88.8 | | NULL | NULL | 3 | 99.9 | +------+--------+------+-------+ 3 rows in set (0.00 sec)
使用內(nèi)連接多表查詢:
mysql> select * from A join B on A.id=B.A_id; +------+--------+------+-------+ | id | name | A_id | score | +------+--------+------+-------+ | 1 | 張三 | 1 | 66.6 | | 2 | 李四 | 2 | 88.8 | +------+--------+------+-------+ 2 rows in set (0.00 sec)
3.4 自連接
自連接也是多表查詢的一種, 上面介紹的是多張不同的表連接在一起的查詢, 而自連接是 是指在同一張表連接自身進(jìn)行查詢, 也就是說自連接是多張相同的表進(jìn)行笛卡爾積, 自連接的主要使用場(chǎng)景是記錄分布在不同的行上不方便進(jìn)行比較查詢, 自連接就可以將不同行的數(shù)據(jù)轉(zhuǎn)化在同一行的不同列上以方便數(shù)據(jù)的比較查詢.
語法:
select 字段 from 表A, 表A,... where 條件;
示例:
還是使用上面3.1中的表數(shù)據(jù), 查詢 所有“計(jì)算機(jī)原理”成績(jī)比“Java”成績(jī)高的成績(jī)信息.
首先查詢 詢“計(jì)算機(jī)原理”和“Java”課程的id.
mysql> select id, name from course where name = 'Java' or name = '計(jì)算機(jī)原理'; +----+-----------------+ | id | name | +----+-----------------+ | 1 | Java | | 3 | 計(jì)算機(jī)原理 | +----+-----------------+ 2 rows in set (0.02 sec)
將兩個(gè)相同成績(jī)表進(jìn)行笛卡爾積, 兩張相同的表存在列同名情況, 使用表名.列名來指定是哪一個(gè)表的列, 表中有效的記錄要滿足下面的條件:
- 兩表學(xué)生id相同.
- 使左邊的表保留課程
id為3的數(shù)據(jù), 右邊的表保留課程id為1的數(shù)據(jù), 左邊的成績(jī)是計(jì)算機(jī)原理, 右邊的成績(jī)是Java. - 左邊的分?jǐn)?shù)要大于右邊的分?jǐn)?shù).
加上這些限制條件就可以完成要求的查詢.
mysql> select * from score as s1, score as s2 where
-> s1.student_id=s2.student_id and s1.course_id=3
-> and s2.course_id=1 and s1.score>s2.score;
+-------+------------+-----------+-------+------------+-----------+
| score | student_id | course_id | score | student_id | course_id |
+-------+------------+-----------+-------+------------+-----------+
| 98.5 | 1 | 3 | 70.5 | 1 | 1 |
| 68.0 | 3 | 3 | 33.0 | 3 | 1 |
+-------+------------+-----------+-------+------------+-----------+
2 rows in set (0.02 sec)指定列顯示, 只保留滿足條件的學(xué)生id.
mysql> select s1.student_id from score as s1, score as s2 where
-> s1.student_id=s2.student_id and s1.course_id=3
-> and s2.course_id=1 and s1.score>s2.score;
+------------+
| student_id |
+------------+
| 1 |
| 3 |
+------------+
2 rows in set (0.00 sec)3.5 子查詢
子查詢是指嵌入在其他sql語句中的select語句，也叫嵌套查詢, 將多步查詢轉(zhuǎn)化為一步查詢, 但實(shí)際上并不建議使用子查詢, 因?yàn)椴还軐懯裁创a代碼, 要么追求的是可讀性,可維護(hù)性來提高開發(fā)效率, 要么是追求程序跑的快來提升運(yùn)行效率; 而子查詢哪個(gè)都實(shí)現(xiàn)不了, 當(dāng)嵌套了很多層查詢, 這sql代碼可能就看不懂了, 容易出錯(cuò), 而且維護(hù)困難.
單行子查詢 : 返回一行記錄的子查詢
查詢與“不想畢業(yè)” 同學(xué)的同班同學(xué)
先演示逐步查詢的過程.
-- 先查詢不想畢業(yè)同學(xué)的班級(jí)id mysql> select classes_id, name from student where name = '不想畢業(yè)'; +------------+--------------+ | classes_id | name | +------------+--------------+ | 1 | 不想畢業(yè) | +------------+--------------+ 1 row in set (0.00 sec) -- 然后查詢和不想畢業(yè)班級(jí)id相同的同學(xué) mysql> select name from student where classes_id = 1 and name != '不想畢業(yè)'; +-----------------+ | name | +-----------------+ | 黑旋風(fēng)李逵 | | 菩提老祖 | | 白素貞 | | 許仙 | +-----------------+ 4 rows in set (0.00 sec)
子查詢就是相當(dāng)于把上面兩個(gè)過程合并了.
mysql> select name from student where classes_id =
-> (select classes_id from student where name='不想畢業(yè)') and name != '不想畢業(yè)';
+-----------------+
| name |
+-----------------+
| 黑旋風(fēng)李逵 |
| 菩提老祖 |
| 白素貞 |
| 許仙 |
+-----------------+
4 rows in set (0.00 sec)多行子查詢 : 返回多行記錄的子查詢.
查詢“語文”或“英文”課程的成績(jī)信息
逐步查詢過程.
-- 先根據(jù)課程名查詢出課程id mysql> select id, name from course where name = '語文' or name = '英文'; +----+--------+ | id | name | +----+--------+ | 4 | 語文 | | 6 | 英文 | +----+--------+ 2 rows in set (0.00 sec) -- 然后根據(jù)課程id查詢課程成績(jī) mysql> select * from score where course_id = 4 or course_id = 6; +-------+------------+-----------+ | score | student_id | course_id | +-------+------------+-----------+ | 98.0 | 1 | 6 | | 72.0 | 4 | 6 | | 43.0 | 6 | 4 | | 79.0 | 6 | 6 | | 92.0 | 7 | 6 | +-------+------------+-----------+ 5 rows in set (0.00 sec)
使用子查詢, 由于返回的是多條記錄, 所以不能再使用等號(hào), 這里使用in操作符.
mysql> select * from score where course_id in
-> (select id from course where name='語文' or name='英文');
+-------+------------+-----------+
| score | student_id | course_id |
+-------+------------+-----------+
| 98.0 | 1 | 6 |
| 72.0 | 4 | 6 |
| 43.0 | 6 | 4 |
| 79.0 | 6 | 6 |
| 92.0 | 7 | 6 |
+-------+------------+-----------+
5 rows in set (0.03 sec)還可以使用[not]exists來實(shí)現(xiàn)多行子查詢, 這個(gè)用到的地方也不多, 就不在這里展開介紹了.
3.6 合并查詢
合并查詢相當(dāng)于得到的是兩個(gè)表中數(shù)據(jù)的并集, 使用union關(guān)鍵字來來實(shí)現(xiàn).
語法:
-- 去重合并查詢 select 字段 from 表1 where 條件 union select 字段 from 表2 where 條件; -- 不去重合并查詢 select 字段 from 表1 where 條件 union all select 字段 from 表2 where 條件;
對(duì)于上面語法中的all, 帶all的不會(huì)對(duì)結(jié)果去重，而不帶all的會(huì)對(duì)結(jié)果去重.
示例:
查詢id小于3，或者名字為“英文”的課程
mysql> select * from course where id<3 union
-> select * from course where name='英文';
+----+--------------------+
| id | name |
+----+--------------------+
| 1 | Java |
| 2 | 中國(guó)傳統(tǒng)文化 |
| 6 | 英文 |
+----+--------------------+
3 rows in set (0.01 sec)
-- 也可以使用or來實(shí)現(xiàn)
mysql> select * from course where id<3 or name='英文';
+----+--------------------+
| id | name |
+----+--------------------+
| 1 | Java |
| 2 | 中國(guó)傳統(tǒng)文化 |
| 6 | 英文 |
+----+--------------------+
3 rows in set (0.01 sec)查詢id小于3，或者名字為“Java”的課程(演示不去重效果)
-- 結(jié)果集中出現(xiàn)重復(fù)數(shù)據(jù)Java
mysql> select * from course where id<3
-> union all select * from course where name='Java';
+----+--------------------+
| id | name |
+----+--------------------+
| 1 | Java |
| 2 | 中國(guó)傳統(tǒng)文化 |
| 1 | Java |
+----+--------------------+
3 rows in set (0.00 sec)這里要注意一下or和union的區(qū)別, or只能針對(duì)同一張表下得到并集, 而union能夠得到不同表的并集; 也就是說合并查詢不僅能夠查詢單表中兩個(gè)結(jié)果的并集, 也能查詢多表中兩個(gè)結(jié)果的并集, 而or只能實(shí)現(xiàn)單表查詢并集.
到此這篇關(guān)于MySQL約束和表的復(fù)雜查詢操作的文章就介紹到這了,更多相關(guān)mysql約束和表的復(fù)雜查詢內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
實(shí)現(xiàn)mysql級(jí)聯(lián)復(fù)制的方法示例
這篇文章主要介紹了實(shí)現(xiàn)mysql級(jí)聯(lián)復(fù)制的方法示例，文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2019-05-05
MySQL優(yōu)化全攻略-相關(guān)數(shù)據(jù)庫命令
MySQL優(yōu)化全攻略-相關(guān)數(shù)據(jù)庫命令...2006-11-11
Windows10下mysql 5.7.17 安裝配置方法圖文教程
這篇文章主要為大家詳細(xì)介紹了Windows10下mysql5.7.17安裝配置方法圖文教程，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2017-02-02
這篇文章主要介紹了MySQL半同步復(fù)制原理配置與介紹詳解，小編覺得挺不錯(cuò)的，現(xiàn)在分享給大家，也給大家做個(gè)參考。一起跟隨小編過來看看吧2019-01-01
binlog 就是binary log,二進(jìn)制日志文件,這個(gè)文件記錄了MySQL所有的DML操作,通過binlog日志我們可以做數(shù)據(jù)恢復(fù),增量備份,主主復(fù)制和主從復(fù)制等等,本文給大家介紹了MySQL 5.7開啟并查看biglog的詳細(xì)教程,需要的朋友可以參考下2024-03-03
關(guān)于django連接mysql數(shù)據(jù)庫并進(jìn)行數(shù)據(jù)庫的創(chuàng)建的問題
這篇文章主要介紹了django連接mysql數(shù)據(jù)庫并進(jìn)行數(shù)據(jù)庫的創(chuàng)建,本文給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2022-06-06
MySQL 8.0的關(guān)系數(shù)據(jù)庫新特性詳解
廣受歡迎的開源數(shù)據(jù)庫MySQL 8中,包括了眾多新特性，下面這篇文章主要給大家介紹了關(guān)于MySQL 8.0的關(guān)系數(shù)據(jù)庫新特性的相關(guān)資料，文中通過示例代碼介紹的非常詳細(xì)，需要的朋友可以參考借鑒，下面來一起看看吧。2018-03-03
MySQL日期時(shí)間類型與字符串互相轉(zhuǎn)換的方法
這篇文章主要介紹了MySQL日期時(shí)間類型與字符串互相轉(zhuǎn)換的方法,文中通過代碼示例和圖文結(jié)合的方式給大家講解的非常詳細(xì),對(duì)大家的學(xué)習(xí)或工作有一定的幫助,需要的朋友可以參考下2024-07-07
mysql5.6忘記root密碼怎么辦，用什么方法可以重置root密碼呢？下面通過本文給大家介紹Mysql5.6 忘記root密碼的解決辦法，需要的朋友參考下2016-01-01
MySQL count(1)、count(*)、count(字段)的區(qū)別
COUNT在數(shù)據(jù)庫行數(shù)統(tǒng)計(jì)中被廣泛使用，那么你知道MySQL count(1)、count(*)、count(字段)的區(qū)別嗎，本文就想的介紹一下，感興趣的可以了解一下2021-12-12