MySQL約束和表的復雜查詢操作大全
更新時間：2022年11月17日 09:17:45 作者：心榮～
約束是關系型數(shù)據(jù)庫的一個重要功能,?添加到庫中的數(shù)據(jù)需要保證其的正確性;?約束,?就是讓數(shù)據(jù)庫幫助程序員更好的檢查數(shù)據(jù)是否正確.，這篇文章主要介紹了MySQL約束和表的復雜查詢操作,需要的朋友可以參考下
SQL查詢中各個關鍵字的執(zhí)行先后順序：
from > on> join > where > group by > with > having > select > distinct > order by > limit
一. 數(shù)據(jù)庫約束
約束是關系型數(shù)據(jù)庫的一個重要功能, 添加到庫中的數(shù)據(jù)需要保證其的正確性; 約束, 就是讓數(shù)據(jù)庫幫助程序員更好的檢查數(shù)據(jù)是否正確.
1. 數(shù)據(jù)庫常用約束
- not null - 指示某列不能存儲 NULL 值.
- unique - 保證某列的每行必須有唯一的值.
- default - 規(guī)定沒有給列賦值時的默認值.
- primary key - not null 和 unique 的結合。確保某列（或兩個列多個列的結合）有唯一標識，有助于更容易更快速地找到表中的一個特定的記錄.
- foreign key - 保證一個表中的數(shù)據(jù)匹配另一個表中的值的參照完整性.
- check - 保證列中的值符合指定的條件; 對于MySQL數(shù)據(jù)庫，對check子句進行分析，但是忽略check子句(MySQL5中不支持check).
2. not null 約束
在創(chuàng)建表的時候使用, 對某一列使用該約束, 則該列的值不能為null.
語法:
create table 表名 (變量 類型 not null, ...);
示例:
-- 創(chuàng)建student表并對id字段使用not null約束
mysql> create table student (id int not null, name varchar(20));
Query OK, 0 rows affected (0.01 sec)
-- id默認值為null,但此時id不允許為null,所以插入數(shù)據(jù)時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列,其默認值為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 約束可以同時對多個列使用.
-- 如果存在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) -- 查看表結構 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)建表的時候使用, 對某一列使用該約束, 則該列的值不能重復.
語法:
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) -- 查看表結構 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ù),無重復數(shù)據(jù),成功插入 mysql> insert into student values (1, '張三'); Query OK, 1 row affected (0.00 sec) -- 只要id/name字段中有重復的數(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設置默認值
在創(chuàng)建表的時候使用, 可以設置列的默認值.
語法:
create table 表名 (變量 類型 default 默認值, ...);
示例:
mysql> drop table if exists student; Query OK, 0 rows affected (0.01 sec) -- 設置字段name的默認值為'匿名' mysql> create table student (id int, name varchar(20) default '匿名'); Query OK, 0 rows affected (0.02 sec) -- 查看表結構 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用默認值填充 mysql> select * from student; +------+--------+ | id | name | +------+--------+ | 1 | 匿名 | +------+--------+ 1 row in set (0.00 sec)
5. primary key約束
primary key是主鍵約束, 是一條記錄的身份標識, 相當于not null和 unique結合的效果, 在創(chuàng)建表時使用, 對`某一列使用該約束, 則該列的值必須是唯一的且不能是null.
實際開發(fā)中, 大部分的表, 一般都會帶有一個主鍵, 主鍵往往是一個整數(shù)表示的id.
語法:
create table 表名 (變量 類型 primary key, ...);
示例:
mysql> drop table if exists student; Query OK, 0 rows affected (0.00 sec) -- 設置id字段為主建 mysql> create table student (id int primary key, name varchar(20)); Query OK, 0 rows affected (0.01 sec) -- 查看表結構 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不重復,插入成功 mysql> insert into student values (1, '張三'); Query OK, 1 row affected (0.00 sec) -- id不重復,插入成功 mysql> insert into student values (2, '李四'); Query OK, 1 row affected (0.00 sec) -- id重復,插入失敗 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 -- 查看插入結果 mysql> select * from student; +----+--------+ | id | name | +----+--------+ | 1 | 張三 | | 2 | 李四 | +----+--------+ 2 rows in set (0.00 sec)
上面主鍵描述的列的值是我們自己手動去添加, mysql中還支持自增主鍵, 使用自增主鍵描述列可以不去手動設置值, 在插入記錄時, 它會自動從1開始自增(未設置初始值的情況下).
當然, 使用了自增主鍵我們也可以去手動添加值, 但當我們手動設置一個之后, 后面插入記錄再讓它自增, 此時的值會基于我們設置的那個值開始自增.
語法:
create table (變量 類型 primary key auto_increment, ...);
示例:
要注意的是下面插入記錄時id列的null并不是將id設置為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中,一個表中只能有一個主鍵, 雖然主鍵不能有多個, 但mysql允許把多個列放到一起共同作為一個主鍵(聯(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. 分庫分表下的自增主鍵
學習階段的mysql的數(shù)據(jù)量較小, 所有的數(shù)據(jù)都在一個mysql服務器上, 此時每個表中的自增主鍵都可以很到的工作, 考慮下面的場景:
如果mysql的數(shù)據(jù)量很大，一臺主機放不下, 就需要進行分庫分表, 使用多個主機來進行存儲, 本質上就是,把一張大表分成兩個/多個小的表, 每個數(shù)據(jù)庫服務器, 分別只存一部分數(shù)據(jù); 此時的增主鍵如何保證在多個主機上不重復呢?
在這個場景下, 如果再新插入一個數(shù)據(jù), 這個數(shù)據(jù)就會落在三個服務器之一, 新的這個數(shù)據(jù)的主鍵id，如何分配才能保證不重復呢?
這里涉及到一個"分布式系統(tǒng)中唯一id生成算法", 實現(xiàn)公式如下:
實現(xiàn)公式=時間戳＋主機編號＋隨機因子
時間戳保證在不同時間下的id不同, 再和主機編號組合保證如果再同一時間有不同的數(shù)據(jù)分散到不同主機上的id不同, 最后再和一個隨機因子組合保證多個數(shù)據(jù)到同一個主機時id不同, 結合這三個部分,就可以得到一個全局唯一的id.
7. foreign key約束
foreign key是外鍵約束, 用來約束兩張表之間的關系(相互約束), 在創(chuàng)建表時使用, 使用該約束要求表中某個記錄必須在另外一個表里存在.
例如一張學生表的字段有學號, 姓名, 班級號, 還有一張表是班級表, 字段有班級號和班級名, 該學生表中的學生所在班級都能在班級表里面找到, 此時就可以對學生表的班級號使用外鍵約束, 讓學生表與班級表聯(lián)系起來; 我們發(fā)現(xiàn)學生表中的數(shù)據(jù)要依賴于班級表的數(shù)據(jù), 班級表的數(shù)據(jù)對學生表產生約束力(父親對孩子有約束力), 此處起到約束作用的班級表就叫做"父表" (parent)，被約束的這個表就叫做“子表" (child).
語法:
-- 父表已經(jīng)創(chuàng)建的前提下 create table 表名 ( 變量 類型,... foreign key (子表中的一個變量) references 父表名 (父表中的一個變量) );
示例:
-- 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表結構
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
-- 查看插入結果
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表結構
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
-- 查看插入結果
mysql> select * from student;
+----+--------+----------+
| id | name | class_id |
+----+--------+----------+
| 1 | 張三 | 3 |
| 2 | 李四 | 1 |
| 3 | 王五 | 2 |
| 4 | 趙六 | 1 |
+----+--------+----------+
4 rows in set (0.00 sec)
-- 插入班級表中不存在的班級,插入失敗
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ù)表, 依賴于另一個表, 這個被依賴的表稱為父表, 被約束的表被稱為子表, 當在子表插入記錄時，必須在父表中存在某一對應關系才能插入.
外鍵約束不僅約束著子表,同時也約束著父表, 我們嘗試修改或者刪除班級表中class_id的值, 且學生表中存在班級為此id的學生, 此時是不能成功修改或刪除的.
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>
要刪除/修改的父表中的記錄, 前提是子表中沒有記錄與父表中的記錄相關聯(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)
當子表中插入的記錄和父表建立聯(lián)系后, 此時就不能直接刪除父表了, 要刪除父表要先刪除子表.
-- 直接刪除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)
實際開發(fā)中, 很多時候刪除表并不會真的將數(shù)據(jù)都清除, 而是采用邏輯刪除, 也就是再表中來設置一個屬性開關, 通過這個屬性來看是否可以使用表中的數(shù)據(jù), 這樣做就可以避免觸發(fā)外鍵約束的限制了.
8. 主鍵,外鍵,uniqe運用原理
當字段被主鍵/unique約束時, 是如何保證記錄不重復的?
當字段被外鍵約束時, 如何知道子表中待插入的數(shù)據(jù)在父表中是否存在?
其實不難想到, 不管是使用上面的哪個約束, 先要有查詢過程, 查詢滿足條件后才會完成插入操作.
但要注意這里的查詢不會是遍歷查詢, 那樣的話時間開銷就太大了, mysql中通過索引來完成查詢, 索引能加快查詢效率(mysql專欄中寫有介紹索引的博客可以參考).
對于外鍵約束, 子表插入數(shù)據(jù)前在父表會有查詢操作, 被依賴的這一列必須要要有索, 如果使用primary key或者unique約束該列, 則該列會自動創(chuàng)建索引.
所以上面的學生表與班級表的示例, 雖然我們沒有主動對父表中的class_id列創(chuàng)建索引, 但是該列使用了主鍵約束, 就會自動創(chuàng)建索引.
二. 表的設計
1. 一對一關系
以學校中的教務系統(tǒng)為例, 學生這個實體有學號,姓名等屬性, 登錄教務系統(tǒng)的賬號也是一個實體, 有用戶名,密碼等屬性; 顯而易見的, 一個學生只能有一個賬號, 像這樣就是一對一的關系.
那么最重要的就是要在數(shù)據(jù)庫中表示這種關系.
方案一
可以將這兩個實體信息在一個表中描述, 那么一條記錄就對應一個關系.
方案二
分別有學生表和用戶表兩張表相互關聯(lián), 學生表中添加一列來與用戶表關聯(lián)或者用戶表中添加一列來與學生表關聯(lián)
2. 一對多關系
例如學生與班級之間的關系就是一對多的關系, 一個學生只能在一個班級中, 而一個班級可以有多名學生.
用如下方式在數(shù)據(jù)庫中表示一對多的關系.
方案一
在班級表中添加一列, 用來存放一個班里面所有學生的記錄, 但這種想法在mysql中是實現(xiàn)不了的, 因為mysql中沒有類似于數(shù)組的類型.
方案二
在學生表中添加一列, 存放學生所在的班級.
3. 多對多關系
學生與課程之間多對多的關系, 一個學生可以學習多門課程, 一門課程中有多名學生學習.
只有一種方案來表示這種關系, 如下:
建立一個關聯(lián)表, 來關聯(lián)學生表和課程表, 這個關聯(lián)表中至少有兩列, 一列用來存放學生學號與學生表關聯(lián), 另一列存放課程號與課程表關聯(lián), 這樣兩表就可以通過這個關聯(lián)表來實現(xiàn)多對多的關系.
補充: 建表時可以使用合適的約束使得數(shù)據(jù)間關系的描述更準確.
三. 表的復雜查詢操作
1. 將一個表中的數(shù)據(jù)插入到另一個表中
將表a中的數(shù)據(jù)插入到表b, 實質上是先查詢a表中的數(shù)據(jù)生成臨時表, 再將臨時表中的數(shù)據(jù)插入表中, 要注意的是查詢出來的記錄(臨時表)的列需要和表b相對應才能插入成功.
語法:
insert into b select select 與表b列相對應的列 from a;
示例:
下面有a,b,c三個表, 其中a,b兩表的列是對應的, c和a的不對應, 將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表的列相對應,不需要調整
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的列不對應,需要調整
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(列名或表達式) | 返回查詢到的數(shù)據(jù)的個數(shù) |
| sum(列名或表達式) | 返回查詢到的數(shù)據(jù)的和 |
| avg(列名或表達式) | 返回查詢到的數(shù)據(jù)的平均值 |
| max(列名或表達式) | 返回查詢到的數(shù)據(jù)的最大值 |
| min（列名或表達式) | 返回查詢到的數(shù)據(jù)的最小值 |
注意: 代碼中的函數(shù)名和( )之間不能有空格.
上面的聚合函數(shù)在使用時可以在列名或表達式前加上關鍵字distinct先讓查詢到的數(shù)據(jù)去重, 然后再進行計算.
這些聚合函數(shù)是針對一個或多個列的行來進行運算的, 其中sum,avg,max,min這幾個聚合函數(shù)只能針對數(shù)值類型進行計算, 不能是字符串和日期類型.
下面給出這些聚合函數(shù)的一些使用示例, 首先創(chuàng)建表并插入數(shù)據(jù), 如下:
count函數(shù)
使用count函數(shù)可以計算數(shù)據(jù)表中有多少行, 統(tǒng)計全表行數(shù)可以直接使用*來匹配所有的行或者使用一個常量表達式.
-- 表中有8行數(shù)據(jù) mysql> select count(*) from exam_result; +----------+ | count(*) | +----------+ | 8 | +----------+ 1 row in set (0.00 sec) -- 函數(shù)參數(shù)給一個常量表達式也行 mysql> select count(1) from exam_result; +----------+ | count(1) | +----------+ | 8 | +----------+ 1 row in set (0.00 sec) -- 也可以起一個別名 mysql> select count(*) as 全表行數(shù) from exam_result; +--------------+ | 全表行數(shù) | +--------------+ | 8 | +--------------+ 1 row in set (0.00 sec)
還有一個需要注意的點, 聚合函數(shù)在計算時不會將null計入在內, 比如統(tǒng)計班級中的有多個有效的語文成績.
-- chinese 為 NULL 的數(shù)據(jù)不會計入結果 mysql> select count(chinese) from exam_result; +----------------+ | count(chinese) | +----------------+ | 6 | +----------------+ 1 row in set (0.00 sec)
sum函數(shù)
-- 統(tǒng)計所有同學語文成績的總和 mysql> select sum(chinese) from exam_result; +--------------+ | sum(chinese) | +--------------+ | 450.0 | +--------------+ 1 row in set (0.00 sec) -- 統(tǒng)計英語成績不及格同學(<60)成績的總和 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)計所有同學總分的平均分 mysql> select avg(chinese + math + english) as 平均總分 from exam_result; +--------------+ | 平均總分 | +--------------+ | 230.00000 | +--------------+ 1 row in set (0.01 sec)
max函數(shù)
-- 統(tǒng)計英語最高分 mysql> select max(english) from exam_result; +--------------+ | max(english) | +--------------+ | 90.0 | +--------------+ 1 row in set (0.00 sec)
min函數(shù)
-- 統(tǒng)計 > 70 分以上的數(shù)學最低分 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關鍵字實現(xiàn), 一般和聚合函數(shù)結合使用, 通過指定分組條件實現(xiàn)分組查詢.
語法:
select 列, ... from 表名 (條件篩選) group by 分組條件,即列名;
其中, 上面的條件篩選可以使用where, order by, limit等來實現(xiàn), 條件篩選不是必寫項.
示例:
創(chuàng)建一個不同職位的薪水表
-- 創(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, "趙六", "測試", 8000),
-> (null, "孫李", "測試", 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 | 趙六 | 測試 | 8000.00 |
| 7 | 孫李 | 測試 | 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 |
| 測試 | 8500.000000 | 9000.00 | 8000.00 |
| 老板 | 110000000.000000 | 120000000.00 | 100000000.00 |
+--------+------------------+--------------+--------------+
3 rows in set (0.03 sec)
-- 也可以起一個別名~~
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 |
| 測試 | 8500.000000 | 9000.00 | 8000.00 |
| 老板 | 110000000.000000 | 120000000.00 | 100000000.00 |
+--------+------------------+--------------+--------------+
3 rows in set (0.00 sec)2.3 having
在上面分組查詢的基礎上, 分組查詢也可以添加指定條件, 這里的條件分有下面兩種情況:
- 分組之前指定條件, 也就是先篩選再分組, 使用
where關鍵字. - 分組之后指定條件, 也就是先分組再篩選, 使用
group by關鍵字.
上面的兩種指定條件的查詢方式可以同時都使用, 也可以只使用其中一種.
語法:
關于where和group by語法上有一點要注意區(qū)分, where語句緊跟在表名后, 而having跟在group by后.
-- having語法 select 聚合函數(shù)(或者列), ... from 表名 group by 列 having 條件; -- where語法 select 聚合函數(shù)(或者列), ... from 表名 where 條件 group by 列;
示例:
老板的共資太高, 不能代表大眾的平均薪資, 查詢結果不顯示老板的平均薪資.
-- 使用where示例 mysql> select role, avg(salary) as 平均薪水 from emp where role != '老板' group by role; +--------+--------------+ | role | 平均薪水 | +--------+--------------+ | 開發(fā) | 10666.666667 | | 測試 | 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 | | 測試 | 8500.000000 | +--------+--------------+ 2 rows in set (0.00 sec)
3. 多表查詢(聯(lián)合查詢)
3.1 笛卡爾積
笛卡爾乘積是指在數(shù)學中, 兩個集合X和Y的笛卡尓積(Cartesian product), 又稱直積, 表示為X×Y, 第一個對象是X的成員而第二個對象是Y的所有可能有序對的其中一個成員.
假設集合A={a, b}, 集合B={0, 1, 2}, 則兩個集合的笛卡爾積為
{(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}.
聯(lián)合查詢也叫多表查詢, 是基于笛卡爾積來實現(xiàn)的, 多表查詢先基于笛卡爾積將多個表合并, 然后對合并后的表去篩選有效的記錄.
將多個表進行笛卡爾積的語法:
select 列, ... from 表名1, 表名2, ...;
示例:
將下面的student和class兩個表進行笛卡爾積.
-- class表,有兩條記錄代表2個班級
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個班級
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)
-- 兩個表進行笛卡爾積,得到 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)
對n條記錄的表A和m條記錄的表B進行笛卡爾積，一共會產生n*m條記錄, 當兩張表的數(shù)據(jù)量很大的時候, 這個操作就非常危險了, 所以多表查詢在實際工作中要慎重使用.
上面得到兩表的笛卡爾積的結果后, 觀察表中數(shù)據(jù), 只有兩表的class_id相等的記錄才是有效數(shù)據(jù), 所以我們還需要再通過一些限制條件來篩選出有效的數(shù)據(jù).
上面這兩張表中存在同名情況, 可以使用表名.列名的形式來訪問區(qū)分對應表中的列.
-- 篩選出有效數(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)建幾個表來演示之后的內容
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
('計算機系2019級1班', '學習了計算機原理、C和Java語言、數(shù)據(jù)結構和算法'),
('中文系2019級3班','學習了中國傳統(tǒng)文學'),
('自動化2019級5班','學習了機械自動化');
insert into student(sn, name, qq_mail, classes_id) values
('09982','黑旋風李逵','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','老外學中文','foreigner@qq.com',2);
insert into course(name) values
('Java'),('中國傳統(tǒng)文化'),('計算機原理'),('語文'),('高階數(shù)學'),('英文');
insert into score(score, student_id, course_id) values
-- 黑旋風李逵
(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為班級表, student為學生表, course表為課程表, score為成績表, 其中學生與班級的關系是一對多，學生與課程之間的關系是多對多.
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
('計算機系2019級1班', '學習了計算機原理、C和Java語言、數(shù)據(jù)結構和算法'),
('中文系2019級3班','學習了中國傳統(tǒng)文學'),
('自動化2019級5班','學習了機械自動化');
insert into student(sn, name, qq_mail, classes_id) values
('09982','黑旋風李逵','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','老外學中文','foreigner@qq.com',2);
insert into course(name) values
('Java'),('中國傳統(tǒng)文化'),('計算機原理'),
('語文'),('高階數(shù)學'),('英文');
insert into score(score, student_id, course_id) values
-- 黑旋風李逵
(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 | 計算機系2019級1班 | 學習了計算機原理、C和Java語言、數(shù)據(jù)結構和算法 |
| 2 | 中文系2019級3班 | 學習了中國傳統(tǒng)文學 |
| 3 | 自動化2019級5班 | 學習了機械自動化 |
+----+-------------------------+-------------------------------------------------------------------+
3 rows in set (0.00 sec)
mysql> select * from student;
+----+-------+-----------------+------------------+------------+
| id | sn | name | qq_mail | classes_id |
+----+-------+-----------------+------------------+------------+
| 1 | 09982 | 黑旋風李逵 | 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 | 老外學中文 | foreigner@qq.com | 2 |
+----+-------+-----------------+------------------+------------+
8 rows in set (0.00 sec)
mysql> select * from course;
+----+--------------------+
| id | name |
+----+--------------------+
| 1 | Java |
| 2 | 中國傳統(tǒng)文化 |
| 3 | 計算機原理 |
| 4 | 語文 |
| 5 | 高階數(shù)學 |
| 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)
下面的內容就是根據(jù)這幾張表來示范多表查詢的, 多表查詢進行笛卡爾積再篩選出有效數(shù)據(jù)其實是在將多個表進行連接的的過程.
常用的連接方式有: 內連接和外連接(外連接分為左外連接和右外連接), 如果多表之間的記錄數(shù)據(jù)均有對應, 內外連接的查詢結果是沒有區(qū)別的; 而如果多表之間的記錄數(shù)據(jù)有存在不對應的情況, 那么內外連接就有一定的區(qū)別了, 內鏈接只會查詢顯示多表對應的記錄, 左外連接會把左表的記錄都顯示出來, 右表中不對應的地方用null填充, 而右外連接就會把右表的記錄都顯示出來, 左表中不對應的地方用null填充.
3.2 內連接
語法:
其中inner可以省略
select 字段 from 表1, 表2, ... where 條件; select 字段 from 表1 inner join 表2 on 條件 join 表3 on 條件...;
類連接查詢類似于交集, 如下圖:
示例:
查詢許仙同學的成績.
這里來逐步分析出正確的sql語句.
1.首先成績的獲取是從分數(shù)表中獲取, 還需要獲取許仙的個人信息, 所以需要對學生表和分數(shù)表進行笛卡爾積.
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.最后再把不必要的列去掉, 對查詢的列進行精簡, 只保留要輸出的列得到最終結果.
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關鍵字實現(xiàn).
select student.name, score,score from student join score on student.id = score.student_id and name = '許仙';
查詢所有同學的總成績及個人信息.
1.需要先將學生表和分數(shù)表進行笛卡爾積
select * from student, score;
2.加上連接條件,篩選出有效數(shù)據(jù)
select * from student, score where student.id = score.student_id;
3.將所有記錄以姓名分組, 再使用sum()函數(shù)計算總分完成聚合查詢.
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 |
| 黑旋風李逵 | 300.0 |
+-----------------+--------+
7 rows in set (0.01 sec)也可以使用join on實現(xiàn)
select name, sum(score.score) as 總分 from student join score on student.id = score.student_id group by student.name;
查看所有同學的各科成績及個人信息.
1.先對學生表, 課程表, 成績表進行笛卡爾積.
select * from student, course, score;
2。加入連接條件, 三張表需要兩個連接條件.
select * from student, course, score where student.id = score.student_id and course.id = score.course_id;
3.最后根據(jù)精簡要顯示的列完成查詢.
mysql> select student.name as 學生姓名, course.name as 課程名稱, score.score from student, course, score
-> where student.id = score.student_id and course.id = score.course_id;
+-----------------+--------------------+-------+
| 學生姓名 | 課程名稱 | score |
+-----------------+--------------------+-------+
| 黑旋風李逵 | Java | 70.5 |
| 黑旋風李逵 | 計算機原理 | 98.5 |
| 黑旋風李逵 | 高階數(shù)學 | 33.0 |
| 黑旋風李逵 | 英文 | 98.0 |
| 菩提老祖 | Java | 60.0 |
| 菩提老祖 | 高階數(shù)學 | 59.5 |
| 白素貞 | Java | 33.0 |
| 白素貞 | 計算機原理 | 68.0 |
| 白素貞 | 高階數(shù)學 | 99.0 |
| 許仙 | Java | 67.0 |
| 許仙 | 計算機原理 | 23.0 |
| 許仙 | 高階數(shù)學 | 56.0 |
| 許仙 | 英文 | 72.0 |
| 不想畢業(yè) | Java | 81.0 |
| 不想畢業(yè) | 高階數(shù)學 | 37.0 |
| 好好說話 | 中國傳統(tǒng)文化 | 56.0 |
| 好好說話 | 語文 | 43.0 |
| 好好說話 | 英文 | 79.0 |
| tellme | 中國傳統(tǒng)文化 | 80.0 |
| tellme | 英文 | 92.0 |
+-----------------+--------------------+-------+
20 rows in set (0.00 sec)也可以使用join on實現(xiàn)
select student.name as 學生姓名, 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關鍵字來實現(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)
使用內連接多表查詢:
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 自連接
自連接也是多表查詢的一種, 上面介紹的是多張不同的表連接在一起的查詢, 而自連接是 是指在同一張表連接自身進行查詢, 也就是說自連接是多張相同的表進行笛卡爾積, 自連接的主要使用場景是記錄分布在不同的行上不方便進行比較查詢, 自連接就可以將不同行的數(shù)據(jù)轉化在同一行的不同列上以方便數(shù)據(jù)的比較查詢.
語法:
select 字段 from 表A, 表A,... where 條件;
示例:
還是使用上面3.1中的表數(shù)據(jù), 查詢 所有“計算機原理”成績比“Java”成績高的成績信息.
首先查詢 詢“計算機原理”和“Java”課程的id.
mysql> select id, name from course where name = 'Java' or name = '計算機原理'; +----+-----------------+ | id | name | +----+-----------------+ | 1 | Java | | 3 | 計算機原理 | +----+-----------------+ 2 rows in set (0.02 sec)
將兩個相同成績表進行笛卡爾積, 兩張相同的表存在列同名情況, 使用表名.列名來指定是哪一個表的列, 表中有效的記錄要滿足下面的條件:
- 兩表學生id相同.
- 使左邊的表保留課程
id為3的數(shù)據(jù), 右邊的表保留課程id為1的數(shù)據(jù), 左邊的成績是計算機原理, 右邊的成績是Java. - 左邊的分數(shù)要大于右邊的分數(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)指定列顯示, 只保留滿足條件的學生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語句，也叫嵌套查詢, 將多步查詢轉化為一步查詢, 但實際上并不建議使用子查詢, 因為不管寫什么代碼代碼, 要么追求的是可讀性,可維護性來提高開發(fā)效率, 要么是追求程序跑的快來提升運行效率; 而子查詢哪個都實現(xiàn)不了, 當嵌套了很多層查詢, 這sql代碼可能就看不懂了, 容易出錯, 而且維護困難.
單行子查詢 : 返回一行記錄的子查詢
查詢與“不想畢業(yè)” 同學的同班同學
先演示逐步查詢的過程.
-- 先查詢不想畢業(yè)同學的班級id mysql> select classes_id, name from student where name = '不想畢業(yè)'; +------------+--------------+ | classes_id | name | +------------+--------------+ | 1 | 不想畢業(yè) | +------------+--------------+ 1 row in set (0.00 sec) -- 然后查詢和不想畢業(yè)班級id相同的同學 mysql> select name from student where classes_id = 1 and name != '不想畢業(yè)'; +-----------------+ | name | +-----------------+ | 黑旋風李逵 | | 菩提老祖 | | 白素貞 | | 許仙 | +-----------------+ 4 rows in set (0.00 sec)
子查詢就是相當于把上面兩個過程合并了.
mysql> select name from student where classes_id =
-> (select classes_id from student where name='不想畢業(yè)') and name != '不想畢業(yè)';
+-----------------+
| name |
+-----------------+
| 黑旋風李逵 |
| 菩提老祖 |
| 白素貞 |
| 許仙 |
+-----------------+
4 rows in set (0.00 sec)多行子查詢 : 返回多行記錄的子查詢.
查詢“語文”或“英文”課程的成績信息
逐步查詢過程.
-- 先根據(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查詢課程成績 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)
使用子查詢, 由于返回的是多條記錄, 所以不能再使用等號, 這里使用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來實現(xiàn)多行子查詢, 這個用到的地方也不多, 就不在這里展開介紹了.
3.6 合并查詢
合并查詢相當于得到的是兩個表中數(shù)據(jù)的并集, 使用union關鍵字來來實現(xiàn).
語法:
-- 去重合并查詢 select 字段 from 表1 where 條件 union select 字段 from 表2 where 條件; -- 不去重合并查詢 select 字段 from 表1 where 條件 union all select 字段 from 表2 where 條件;
對于上面語法中的all, 帶all的不會對結果去重，而不帶all的會對結果去重.
示例:
查詢id小于3，或者名字為“英文”的課程
mysql> select * from course where id<3 union
-> select * from course where name='英文';
+----+--------------------+
| id | name |
+----+--------------------+
| 1 | Java |
| 2 | 中國傳統(tǒng)文化 |
| 6 | 英文 |
+----+--------------------+
3 rows in set (0.01 sec)
-- 也可以使用or來實現(xiàn)
mysql> select * from course where id<3 or name='英文';
+----+--------------------+
| id | name |
+----+--------------------+
| 1 | Java |
| 2 | 中國傳統(tǒng)文化 |
| 6 | 英文 |
+----+--------------------+
3 rows in set (0.01 sec)查詢id小于3，或者名字為“Java”的課程(演示不去重效果)
-- 結果集中出現(xiàn)重復數(shù)據(jù)Java
mysql> select * from course where id<3
-> union all select * from course where name='Java';
+----+--------------------+
| id | name |
+----+--------------------+
| 1 | Java |
| 2 | 中國傳統(tǒng)文化 |
| 1 | Java |
+----+--------------------+
3 rows in set (0.00 sec)這里要注意一下or和union的區(qū)別, or只能針對同一張表下得到并集, 而union能夠得到不同表的并集; 也就是說合并查詢不僅能夠查詢單表中兩個結果的并集, 也能查詢多表中兩個結果的并集, 而or只能實現(xiàn)單表查詢并集.
到此這篇關于MySQL約束和表的復雜查詢操作的文章就介紹到這了,更多相關mysql約束和表的復雜查詢內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
相關文章
這篇文章主要介紹了實現(xiàn)mysql級聯(lián)復制的方法示例，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2019-05-05
MySQL優(yōu)化全攻略-相關數(shù)據(jù)庫命令
MySQL優(yōu)化全攻略-相關數(shù)據(jù)庫命令...2006-11-11
Windows10下mysql 5.7.17 安裝配置方法圖文教程
這篇文章主要為大家詳細介紹了Windows10下mysql5.7.17安裝配置方法圖文教程，具有一定的參考價值，感興趣的小伙伴們可以參考一下2017-02-02
這篇文章主要介紹了MySQL半同步復制原理配置與介紹詳解，小編覺得挺不錯的，現(xiàn)在分享給大家，也給大家做個參考。一起跟隨小編過來看看吧2019-01-01
binlog 就是binary log,二進制日志文件,這個文件記錄了MySQL所有的DML操作,通過binlog日志我們可以做數(shù)據(jù)恢復,增量備份,主主復制和主從復制等等,本文給大家介紹了MySQL 5.7開啟并查看biglog的詳細教程,需要的朋友可以參考下2024-03-03
關于django連接mysql數(shù)據(jù)庫并進行數(shù)據(jù)庫的創(chuàng)建的問題
這篇文章主要介紹了django連接mysql數(shù)據(jù)庫并進行數(shù)據(jù)庫的創(chuàng)建,本文給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下2022-06-06
廣受歡迎的開源數(shù)據(jù)庫MySQL 8中,包括了眾多新特性，下面這篇文章主要給大家介紹了關于MySQL 8.0的關系數(shù)據(jù)庫新特性的相關資料，文中通過示例代碼介紹的非常詳細，需要的朋友可以參考借鑒，下面來一起看看吧。2018-03-03
這篇文章主要介紹了MySQL日期時間類型與字符串互相轉換的方法,文中通過代碼示例和圖文結合的方式給大家講解的非常詳細,對大家的學習或工作有一定的幫助,需要的朋友可以參考下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)計中被廣泛使用，那么你知道MySQL count(1)、count(*)、count(字段)的區(qū)別嗎，本文就想的介紹一下，感興趣的可以了解一下2021-12-12