快捷導(dǎo)航

探討MySQL 約束下的查詢功能

更新時(shí)間：2024年08月19日 12:14:25 作者：robin_suli

這篇文章主要介紹了探討MySQL 約束下的查詢功能,本文通過(guò)實(shí)例代碼給大家介紹的非常詳細(xì),感興趣的朋友跟隨小編一起看看吧

一. 數(shù)據(jù)庫(kù)約束：

1.約束類型匯總：

約束類型	說(shuō)明
NULL約束	使用NOT NULL指定列不為空
UNIQUE唯一約束	指定列為唯一的、不重復(fù)的
DEFAULT默認(rèn)值約束	指定列為空時(shí)的默認(rèn)值
主鍵約束（primary key）	NOT NULL 和 UNIQUE 的結(jié)合
外鍵約束	關(guān)聯(lián)其他表的主鍵或唯一鍵語(yǔ)法：foreign key (列) references 主表(列）
CHECK約束	保證列中的值符合指定的條件

1.1 NULL約束：

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

DROP TABLE IF EXISTS student;
CREATE TABLE student (
   id INT NOT NULL,
   sn INT,
   name VARCHAR(50),
   qq_mail VARCHAR(50)
);

1.2 DEFAULT：默認(rèn)值約束：

mysql> CREATE TABLE student (
    ->    id INT NOT NULL,
    ->    name VARCHAR(20) DEFAULT '默認(rèn)為無(wú)名氏');

1.3 PRIMARY KEY：主鍵約束：

CREATE TABLE student2 (
    -> id bigint primary key auto_increment,
    -> name VARCHAR(20) DEFAULT '默認(rèn)為無(wú)名氏');

這里還使用了auto_increment，在插入時(shí)不指定，可以讓id字段自增。

注意這里一個(gè)表不可以有多個(gè)主鍵，都是可以有復(fù)合主鍵

如下：

1.4 FOREIGN KEY：外鍵約束：

外鍵用于關(guān)聯(lián)其他表的主鍵或唯一鍵

語(yǔ)法：

foreign key (本表要關(guān)聯(lián)的字段) references 主表(列)

例子：創(chuàng)建班級(jí)表classes，id為主鍵；

創(chuàng)建學(xué)生表student，一個(gè)學(xué)生對(duì)應(yīng)一個(gè)班級(jí)，一個(gè)班級(jí)對(duì)應(yīng)多個(gè)學(xué)生。使用id為主鍵， classes_id為外鍵，關(guān)聯(lián)班級(jí)表id。

 create table student(
    -> id INT PRIMARY KEY auto_increment,
    -> class_id int,
    -> foreign key (class_id) references class (id)
    -> );
create table class(
    -> id INT PRIMARY KEY auto_increment,
    -> name varchar(50)
    -> );

注意：這里的class_id的類型，要和主表class的id類型一致這里都是（int）

不然會(huì)報(bào)類型不兼容錯(cuò)誤導(dǎo)致定義失敗

二. 表的設(shè)計(jì)：

1.設(shè)計(jì)表的時(shí)候要遵循三大范式。

第一范式：表里的字段不可以再進(jìn)行拆分

第二范式：再滿足第一范式的基礎(chǔ)上，不存在非關(guān)鍵字段對(duì)任意候選鍵的部分函數(shù)依賴

（簡(jiǎn)單來(lái)說(shuō)就是非主鍵字段對(duì)任意主鍵，外鍵，唯一鍵的部分函數(shù)依賴）

小結(jié)：一個(gè)表沒(méi)有復(fù)合主鍵就天然滿足第二范式

第三范式：再滿足第二范式的基礎(chǔ)上，不存在非關(guān)鍵字段對(duì)任意候選鍵的傳遞依賴

第三范式可以解決數(shù)據(jù)冗余，更新異常，插入異常，刪除異常等問(wèn)題

2.設(shè)計(jì)時(shí)表之間的三大關(guān)系：

一. 一對(duì)一：比如用戶和賬號(hào)信息，可以建立在一個(gè)表中

二. 一對(duì)多：一個(gè)班級(jí)有多個(gè)學(xué)生設(shè)計(jì)如下：

三. 多對(duì)多：一個(gè)學(xué)生可以選多門課，一門課可以被多個(gè)學(xué)生選

學(xué)生表和課程表是多對(duì)多的關(guān)系，這里通過(guò)課程表(關(guān)系表)關(guān)聯(lián)

三. 聚合查詢:

1.常見的統(tǒng)計(jì)總數(shù)、計(jì)算平局值等操作，可以使用聚合函數(shù)來(lái)實(shí)現(xiàn)，常見的聚合函數(shù)有:

函數(shù)	說(shuō)明
COUNT([DISTINCT] expr)	返回查詢到的數(shù)據(jù)的數(shù)量
SUM([DISTINCT] expr)	返回查詢到的數(shù)據(jù)的總和，不是數(shù)字沒(méi)有意義
AVG([DISTINCT] expr)	返回查詢到的數(shù)據(jù)的平均值，不是數(shù)字沒(méi)有意義
MAX([DISTINCT] expr)	返回查詢到的數(shù)據(jù)的最大值，不是數(shù)字沒(méi)有意義
MIN([DISTINCT] expr)	返回查詢到的數(shù)據(jù)的最小值，不是數(shù)字沒(méi)有意義

例子：

1.1COUNT：統(tǒng)計(jì)行數(shù)

統(tǒng)計(jì)班里有多少同學(xué)：

1.2 SUM：

統(tǒng)計(jì)語(yǔ)文成績(jī)總分：

1.3 AVG：

統(tǒng)計(jì)語(yǔ)文平均分：

1.4 MAX:

語(yǔ)文最高分：

1.5 MIN

語(yǔ)文最低分：

2.GROUP BY子句：

2.1 SELECT 指定的字段必須是“分組依據(jù)字段”，其他字段若想出現(xiàn)在SELECT 中則必須包含在聚合函數(shù)中。

2.2：語(yǔ)法：

select 需要分組的列, sum(column2), .. from table group by 需要分組的列

2.3：例子：

mysql> create table emp(
    ->  id int primary key auto_increment,
    ->  name varchar(20) not null,
    ->  role varchar(20) not null,
    ->  salary numeric(11,2)
    -> );
Query OK, 0 rows affected (0.07 sec)
mysql> insert into emp(name, role, salary) values
    -> ('馬云','服務(wù)員', 1000.20),
    -> ('馬化騰','游戲陪玩', 2000.99),
    -> ('孫悟空','游戲角色', 999.11),
    -> ('豬無(wú)能','游戲角色', 333.5),
    -> ('沙和尚','游戲角色', 700.33),
    -> ('隔壁老王','董事長(zhǎng)', 12000.66);
Query OK, 6 rows affected (0.04 sec)

查詢每個(gè)角色的最高工資：