目錄

一.關(guān)系模型的簡要概述

二.數(shù)據(jù)庫的約束?

2.1約束類型

? ? ? ??2.2NULL約束

2.3?UNIQUE：唯一約束

2.4 默認約束

2.5 PRIMARY KEY：主鍵約束

2.6 FOREIGN KEY：外鍵約束

2.7 CHECK約束

三.高效率查詢

3.1高效率查詢的分類

3.2聚合查詢

3.2.1聚合函數(shù)

3.2.2 GROUP BY子句

3.2.3HAVING

3.3.聯(lián)合查詢

3.3.1聯(lián)合查詢的解析

3.3.2內(nèi)連接

?3.3.3外連接

?3.3.4自連接

?3.3.5子查詢

3.3.6合并查詢

四.總結(jié)

??個人主頁：tq02的博客_CSDN博客-C語言,Java,Java數(shù)據(jù)結(jié)構(gòu)領(lǐng)域博主
?? 本文由 tq02 原創(chuàng)，首發(fā)于 CSDN??
???本章講解內(nèi)容：MySQL的數(shù)據(jù)庫的約束、數(shù)據(jù)的高效率查詢

??歡迎各位→點贊?? +?收藏? +?評論??+關(guān)注?

?

---

?注：在學(xué)習本文前，需要學(xué)會數(shù)據(jù)庫的基本操作以及數(shù)據(jù)表中數(shù)據(jù)的增刪查找

???數(shù)據(jù)庫的基本操作 學(xué)習鏈接：http://t.csdn.cn/LS3Dp

???數(shù)據(jù)表的數(shù)據(jù)增刪查找 學(xué)習鏈接：http://t.csdn.cn/78QIz

一.關(guān)系模型的簡要概述

我們先來普及最基礎(chǔ)的知識點-----關(guān)系模型的基本術(shù)語：

關(guān)系：一個關(guān)系對應(yīng)一張表

屬性：表中一列即為一個屬性

元組：表中一行即為一個元組

域：某個屬性的取值范圍

候選碼：關(guān)系中某個屬性組的值能唯一標識一個元組，則該屬性組為候選碼。

主碼：也稱'主鍵'，當一個關(guān)系中有多個候選碼，則可以選擇任意一個為主碼。

外碼：也稱'外鍵'，如果一個數(shù)據(jù)表中的一個屬性是另一個數(shù)據(jù)表的主碼，則為外碼

候選碼的詳細講解?

?例如上圖，學(xué)號的值不會重復(fù)，每一個值可以標識一個元組。而姓名不可以，其中含有重復(fù)的值，也就是說候選碼的條件是 屬性?中沒有重復(fù)的值。但從定義中知道 這個是屬性組，也就是說，一個表的候選碼不唯一，而成績的值也不存在重復(fù)，因此成績也是候選碼。

主碼的詳細講解

就是從候選碼中選擇一個，作為主碼。當然，選擇主碼應(yīng)該要選擇更合適的，例如學(xué)生成績的表，如果再添加一組數(shù)據(jù)，那么成績可能重復(fù)，所以為了安全起見，我們選擇學(xué)號為主碼

外碼的講解

從圖中，我們可以看出，學(xué)生成績表的候選碼：學(xué)號，成績。圖書借閱表的候選碼：圖書編號?

而當我們將學(xué)號作為學(xué)生成績表的主碼時，圖書借閱表中的‘學(xué)號’屬性便是學(xué)生成績表的外碼

?

二.數(shù)據(jù)庫的約束?

2.1約束類型

????????數(shù)據(jù)庫的約束，其實約束的是?數(shù)據(jù)表結(jié)構(gòu)，控制每一列的存儲數(shù)值。

???約束類型：

1. NOT? NULL ：限制列取值不為空。
2. DEFAULT： 給定列的默認值。
3. UNIQUE：限制列的取值不重復(fù)。
4. CHECK：限制列的取值范圍。
5. PRIMARY KEY：指定本列為主碼。
6. FREIGN? KEY ：定義本列為引用其他表的外碼。

2.2NULL約束

創(chuàng)建表時，可以指定某列不為空。

例如：創(chuàng)建一個學(xué)生表，并且指定學(xué)號不為空

CREATE TABLE student (
    id INT NOT NULL,
    sn INT,
    name VARCHAR(20),
    qq_mail VARCHAR(20)
);

2.3?UNIQUE：唯一約束

?創(chuàng)建表時，指定某一列的值不能重復(fù)。

指定 sn 列不可重復(fù)。

CREATE TABLE student (
    id INT NOT NULL,
    sn INT UNIQUE,
    name VARCHAR(20),
    qq_mail VARCHAR(20)
);

2.4 默認約束

創(chuàng)建表時，當輸入的某字段值為空，可賦 默認值。

例如：指定插入數(shù)據(jù)時，name列為空，默認值tq02

CREATE TABLE student (
    id INT NOT NULL,
    sn INT UNIQUE,
    name VARCHAR(20) DEFAULT 'tq02',
    qq_mail VARCHAR(20)
);

2.5 PRIMARY KEY：主鍵約束

創(chuàng)建表時，指定某列為主鍵。

注：對于整數(shù)類型的主鍵，常配搭自增長auto_increment來使用。插入數(shù)據(jù)對應(yīng)字段不給值時，使用最大值+1.

例如：指定 id 列為主鍵

CREATE TABLE student (
? ? id INT NOT NULL PRIMARY KEY,
? ? sn INT UNIQUE,
? ? name VARCHAR(20) DEFAULT 'unkown',
? ? qq_mail VARCHAR(20)
);

2.6 FOREIGN KEY：外鍵約束

創(chuàng)建表時，用于關(guān)聯(lián)其他表的主碼和唯一鍵

語法格式：foreign key (字段名) references 主表(列)

例子：先創(chuàng)建一個班級表，id為主碼。

//創(chuàng)建班級表，有使用MySQL關(guān)鍵字作為字段時，需要使用``來標識
CREATE TABLE classes (
    id INT PRIMARY KEY auto_increment,
    name VARCHAR(20),
    `desc` VARCHAR(100)
);

再創(chuàng)建一個學(xué)生表student，一個學(xué)生對應(yīng)一個班級，一個班級對應(yīng)多個學(xué)生，使用id為主碼，class_id為外鍵，關(guān)聯(lián)班級表id

-- 重新設(shè)置學(xué)生表結(jié)構(gòu)
DROP TABLE IF EXISTS student;
    CREATE TABLE student (
    id INT PRIMARY KEY auto_increment,
    sn INT UNIQUE,
    name VARCHAR(20) DEFAULT 'unkown',
    qq_mail VARCHAR(20),
    classes_id int,

FOREIGN KEY (classes_id) REFERENCES classes(id)
);

2.7 CHECK約束

約束某個列的取值范圍

約束性別，只能為男和女。

create table test_user (
    id int,
    name varchar(20),
    sex varchar(1),
    check (sex ='男' or sex='女')
);

三.高效率查詢

3.1高效率查詢的分類

高效率查詢分為2種，聚合查詢和聯(lián)合查詢

聚合查詢：對數(shù)據(jù)表中的一個字段進行部分或者全部進行統(tǒng)計查詢。

聯(lián)合查詢：合并多個相似的選擇查詢的結(jié)果，等同于將一個表追加到另一個表，從而實現(xiàn)將2個表的查詢組合在一起。

3.2聚合查詢

3.2.1聚合函數(shù)

聚合函數(shù)，用于統(tǒng)計總數(shù)、計算平局值等操作。

常見的聚合函數(shù)：

count使用：

//統(tǒng)計班級收集的 qq_mail 有多少個，qq_mail 為 NULL 的數(shù)據(jù)不會計入結(jié)果
SELECT COUNT(qq_mail) FROM student;

sum使用：

-- 不及格 < 60 的總分，沒有結(jié)果，返回 NULL
SELECT SUM(math) FROM exam_result WHERE math < 60;

avg使用：

-- 統(tǒng)計平均總分
SELECT AVG(chinese + math + english) 平均總分 FROM exam_result;

max使用：
?

-- 返回英語最高分
SELECT MAX(english) FROM exam_result;

min使用：

-- 返回 > 70 分以上的數(shù)學(xué)最低分
SELECT MIN(math) FROM exam_result WHERE math > 70;

3.2.2 GROUP BY子句

????????????????使用group by子句，可以指定列進行分組查詢。

使用規(guī)定：select指定的字段必須是'分組依據(jù)字段'，其他字段若是想出現(xiàn)，則必須包含在聚合函數(shù)中。

語法格式:?select? 字段名1,聚合函數(shù)名(字段名2).....for table group by 字段名1，字段名2；

例子：

• 準備一個測試表，有id（主鍵）,name(姓名)，role(職位)，salary（薪水）

create table emp(
    id int not null primary key auto_increment,
    name varchar(20) not null,
    role varchar(20) not null,
    salary numeric(11,2)
);

insert into emp(name, role, salary) values
('奧特曼','服務(wù)員', 1000.20),
('灰太狼','游戲陪玩', 2000.99),
('孫悟空','洗碗工', 999.11),
('橙留香','收破爛', 333.5),
('熊大','伐木工', 700.33),
('火云飛','董事長', 12000.66);

• 查詢每個職工的最高工資，最低工資，平局工資

select role,max(salary),min(salary),avg(salary) from emp group by role;

看到這里的時候，我們會發(fā)現(xiàn)，原來聚合函數(shù)需要和group by組合一起使用才會有高效率啊。?

3.2.3HAVING

GROUP BY 子句進行分組以后，需要對分組結(jié)果再進行條件過濾時，不能使用 WHERE 語句，而需要用HAVING

例如：顯示平均工資低于1500的員工和他們的平均工資

select role,max(salary),min(salary),avg(salary) from emp group by role
having avg(salary)<1500;

3.3.聯(lián)合查詢

3.3.1聯(lián)合查詢的解析

聯(lián)合聯(lián)合，顧名思義，需要多表進行查詢，而多表查詢：對多張表的數(shù)據(jù)取笛卡爾積。

?聯(lián)合查詢分為了5種：1.內(nèi)連接? ?2.外連接? ?3.自連接? ?4.子連接? ? 5.合并查詢

為了更好的講述聯(lián)合查詢，我們可以先建立幾個數(shù)據(jù)表，并往其中添加數(shù)據(jù)。

1.建立班級表，往其中加入數(shù)據(jù)

create table classes(
    classes_id int not null , 
    descrtion  varchar(20) not null  primary key                  
);

insert into classes(classes_id, descrtion) values
(1, '學(xué)習了Java語言'),
(2,'學(xué)習了中國傳統(tǒng)文學(xué)'),
(2,'學(xué)習了語文');

2.建立學(xué)生表，往其中添加數(shù)據(jù)

//創(chuàng)建學(xué)生表
CREATE TABLE student (
    sid varchar(20) not null  primary key ,
    name VARCHAR(20) comment '姓名',
    age INT comment '年齡',
    sex varchar(1) comment '性別',
    classes_id int comment '班級'
);

insert into student(sid, name, age, sex, classes_id) values
('09982','黑旋風李逵',18,'男',1),
('00835','菩提老祖',19,'女',1),
('00391','白素貞',22,'女',2),
('00031','許仙',25,'男',1),
('09527','孫悟空',300,'男',2);

3.建立課程表，往其中添加數(shù)據(jù)

create table course(
    name varchar(100) comment '課程名',
    course_id int comment '課程號'
);

insert into course(name,course_id) values
('Java',1),('中國傳統(tǒng)文化',2),('語文',4),('高階數(shù)學(xué)',5);

4.建立分數(shù)表，并往其中添加數(shù)據(jù)

create table score(
    grade int comment"分數(shù)",
    sid varchar(20) comment"學(xué)號",
     course_id  int comment "課程號"
);

insert into score(grade,sid,course_id) values(
-- 黑旋風李逵
(70, '09982', 1),
-- 菩提老祖
(60, '00835', 1),
--白素貞
(99, '00391', 2),(88, '00391',4),
-- 許仙
(67, '00031', 1),
-- 孫悟空
(81, '09527', 2),(37,'09527',4)
);

3.3.2內(nèi)連接

內(nèi)連接，也被稱為自然連接，只有兩個表相匹配的行才能在結(jié)果集中出現(xiàn)。返回的結(jié)果集選取了兩個表中所有相匹配的數(shù)據(jù)，舍棄了不匹配的數(shù)據(jù)。

語法格式：

1. select *from 表1 join 表2 [ on過濾條件 ] [ where查詢條件 ] ；
2. select *from 表1，表2 [ where查詢條件 ] ；
3. select *from 表1 inner join 表2 [ on過濾條件 ] [ where查詢條件 ] ；

注，經(jīng)常使用1來進行多表連接

?查詢白素貞的總分時，使用：

select student.name,sum(score.grade) as sum from student join score on? student.sid=score.sid where student.name='白素貞';

?當查詢白素貞的個人所有信息時，我們使用：

?select * from student join score on student.sid=score.sid where student.name='白素貞';

?3.3.3外連接

外連接分為左外連接和右外連接。如果聯(lián)合查詢，左側(cè)的表完全顯示我們就說是左外連接；右側(cè)的表完全顯示我們就說是右外連接。

1.左外連接，表1完全顯示：

? ? ? 語法格式 ：select 字段名 from 表名1 left join 表名2 on 連接條件??[where條件查詢];

?注：表1 查詢結(jié)果是所有數(shù)據(jù)，表2查詢結(jié)果是與表1重合部分的數(shù)據(jù)。

---

2.右外連接，表2完全顯示：

? ? ?語法格式：select 字段 from 表名1 right join 表名2 on 連接條件??[where條件查詢];

注：表2?查詢的是所有數(shù)據(jù)，表 1查詢結(jié)果是與表2重合的部分

聯(lián)表查詢中on和where的區(qū)別：

1. on在內(nèi)連接中可省略，外連接不可省略；
2. on在內(nèi)連接中執(zhí)行效果和外連接執(zhí)行效果不同；
3. ?left join...on查詢不能過濾掉左表中的數(shù)據(jù)，而內(nèi)連接on查詢可以過濾掉全局數(shù)據(jù)。
4. 外連接中on和where不同，on篩選笛卡爾積過濾條件，where篩選具體業(yè)務(wù)

??3.3.4自連接

自連接，顧名思義，在同一張表連接自身。

語法格式：select *from 表名 as t1,表名 as t2 where t1.id=t2.id [, ...]；

例如，查詢分數(shù)表中，某個人一門成績大于另一門的成績

使用：select * from score as t1,score as t2 where t1.sid=t2.sid and t1.grade<t2.grade;

?3.3.5子查詢

子查詢是指嵌入在其他sql語句中的select語句，也叫嵌套查詢

分兩種，一種單行子查詢，另一種多行子查詢

• ?單行子查詢

? ?返回一行記錄的子查詢

例如：查詢與許仙同班的同學(xué)

• ?多行子查詢

返回多行記錄的子查詢

例如：查詢Java和中國傳統(tǒng)文學(xué)的分數(shù)

3.3.6合并查詢

? 在實際操作中，我們需要合并多了select的執(zhí)行結(jié)果，因此我們需要使用集合操作符union，union all。

• union

該操作符用于取得兩個結(jié)果集的并集。當使用該操作符時，會自動去掉結(jié)果集中的重復(fù)行。

例如：查詢學(xué)生表中大于22歲的男性信息

• ?union all

該操作符用于取得兩個結(jié)果集的并集。當使用該操作符時，不會去掉結(jié)果集中的重復(fù)行。

結(jié)：這個很雞肋，幾乎沒有用處，了解就好。?

四.總結(jié)

? ? ? ? ? ? ? ? 我們需要先弄懂關(guān)系模型的各個專業(yè)術(shù)語概念，才能理解到約束條件的作用，接著接觸到查詢當中的聚合和聯(lián)合查詢。

????????聚合查詢，使用的是函數(shù)方法，較為簡單，更容易理解，但是聯(lián)合查詢更為麻煩，但是理解了外連接之后，其他連接方式就更加容易明白。

????????最后，如果本文有幫助到你們，我很開心，希望可以得到大家的贊同，謝謝大家，祝大家在數(shù)據(jù)庫的世界里大顯身手，未來前程似錦?。?！文章來源地址http://www.zghlxwxcb.cn/news/detail-495879.html

到了這里，關(guān)于MySQL數(shù)據(jù)庫：數(shù)據(jù)庫的約束以及數(shù)據(jù)的聚合、聯(lián)合查詢的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！