MySQL 增删改查（進階）

    上一篇我們介紹了MySQL的比較基礎的增删改查，這裏介紹增删查改的一些進階用法，說是進階用法，實際上，最主要的進階部分是查詢部分，新增主要是把查詢的結果在作為值插入；而查詢的部分包含聚合查詢、聯合查詢（多錶查詢）；不涉及修改和删除的進階。

MySQL 增删查改（基礎）_Naion的博客-CSDN博客https://blog.csdn.net/Naion/article/details/125809324?spm=1001.2014.3001.5501這裏繼續使用idea遠程連接數據庫來進行說明。idea如何遠程連接請點擊鏈接。

數據庫約束

    如圖所示，現在我的想法是：能不能當沒有給id賦值的時候，給他一個默認的值？或者要求只能插入一個張三。要實現這些功能，需要用到數據庫的約束。

約束類型

（1）NOT NULL - 指示某列不能存儲 NULL 值，當插入的值為null的時候，報錯；

（2）UNIQUE - 保證某列的每行必須有唯一的值，不能出現重複的值；

（3）DEFAULT - 規定沒有給列賦值時的默認值；

（4）PRIMARY KEY - NOT NULL 和 UNIQUE 的結合，確保某列（或兩個列多個列的結合）有唯一標識，有助於更容易更快速地找到錶中的一個特定的記錄。

（5）FOREIGN KEY - 保證一個錶中的數據匹配另一個錶中的值的參照完整性。

（6）CHECK - 保證列中的值符合指定的條件。但是MySQL不支持check語句。

比如：

not null約束

unique約束

default默認值約束

primary key主鍵約束

    主鍵是not null 和 unique 的結合，對於整數類型的主鍵，經常和auto_increment使用，這樣就可以只插入name，id就會自動給你計算，每插入一個，數值加1。

foreign key 外鍵約束

foreign key (字段名) references 主錶(列)

    用於關聯其他錶的主鍵或唯一鍵。關聯不是主鍵或唯一鍵就會報錯。比如，學生有自己的班級，張三和李四是1班的，王五是2班的。

create table class
(
    class_id int primary key auto_increment,
    class_name varchar(20) unique
);
insert into class(class_name)
values ('mysql1班'),
       ('mysql2班');

create table student
(
    id int primary key auto_increment,
    name varchar(20) not null,
    class_id int, # 班級id
    foreign key (class_id) references class(class_id)
);
insert into student(name, class_id)
values ('張三', 1),
       ('李四', 1),
       ('王五', 2);
select *
from student;

 如果我插入了三班，就會報錯。

新增

把查詢的結果作為值插入。

INSERT INTO  table_name [(column [, column ...])] SELECT ...

比如，創建了student2錶，把student的內容拷貝到student2錶中。

聚合查詢

    基礎的查詢和條件查詢是針對列和列之間的查詢。要針對行和行之間進行插入，就要使用聚合查詢。

聚合函數

比如查詢student錶的人數，使用count()函數。

group by子句

select column1, sum(column2), .. from table group by column1,column3;

    使用group by子句對指定的列進行分組，注意的是使用group by進行分組查詢時，select指定的字段必須是“分組依據字段”，其他字段若想出現在select中則必須包含在聚合函數中。比如查詢兩個班分別有多少人。

    當然也可以在group by子句之前使用where語句，達到先篩選後分組的效果，不允許where語句放在group by子句後面，否則使用having語句。

having

    where放在group by之前，是先篩選在分組；group by之後要進行篩選，不允許使用where，而是使用having。where子語句作用於沒有經過分組的錶和視圖，having語句作用於經過分組的組。where語句不能包含聚合函數，而having裏面經常使用聚合函數。

比如分組後查詢人數大於1的班級。

當然可以同時使用where 和 having 在group by子句中。

聯合查詢（多錶查詢）

    在實際的開發中，需要使用多張錶進行聯合查詢，比如，聯合查詢學生對應的班級，這裏不是查詢對應的id，而是每一個學生對應的班級名。多錶查詢就是對多張錶數據取笛卡爾積。

    笛卡爾積，又叫cross join，是SQL中兩錶連接的一種方式。假如A錶中的數據為m行，B錶中的數據有n行，那麼A和B做笛卡爾積，結果為m*n行。

    通常我們都要在實際SQL中避免直接使用笛卡爾積，因為它會使“數據爆炸”，尤其是數據量很大的時候。但是巧妙使用，反而快速幫助我們解决問題。

內連接

select * from A,B;

select * from A [inner] join B on 連接條件 and 其他條件;

聯合查詢學生對應的班級，先進行笛卡爾積。

    我們可以看見查詢的結果是非常多的，這個時候，需要確定連接條件，過濾無效信息。觀察就可以知道，student錶中的class_id一定是和class_id中的值一一對應的關系，在student錶中，1班當然對應class錶中的1班，加上這個條件。

現在看起來這個錶的數據就好多了。我們需要的是學生對應的班級，所以在篩選一下列。

上面的步驟可以一氣呵成，但是剛開始的時候，就需要慢慢來。

使用帶join的語句。

select class.class_name, student.name
from class
    join java_104.student
where class.class_id = student.class_id;
# on class.class_id = student.class_id; # 或者使用on來連接

兩個語法都能達到目的，join還有特殊的用法。

外連接

    外連接分為左外連接和右外連接。如果聯合查詢，左側的錶完全顯示我們就說是左外連接；右側的錶完全顯示我們就說是右外連接。

-- 左外連接，錶1完全顯示

select 字段名  from 錶名1 left join 錶名2 on 連接條件;

-- 右外連接，錶2完全顯示

select 字段 from 錶名1 right join 錶名2 on 連接條件;

現在在class錶中新增班級，student錶中新增學生，但是沒有班級。

然後看看左外連接和右外連接的區別，以及和內連接的區別。

insert into class(class_name)
values ('mysql3班');

insert into student(name)
values ('王麻子');

select class.class_name, student.name
from class
         left join student
                   on class.class_id = student.class_id;
select class.class_name, student.name
from class
         right join student
                   on class.class_id = student.class_id;

他們的結果可以使用圖來清晰的錶示。

自連接

    自己連接自己進行查詢。這種查詢是一種奇技淫巧，用來把行轉換成列進行查詢。查詢的過程中需要對錶起別名，否則報錯。比如在同一張錶中進行比較就需要用到這種查詢。這裏只寫用法。

子查詢

子查詢是嵌入在其他sql語句中的select語句，也是叫嵌套查詢，俗稱套娃。

    單行子查詢只返回一行記錄，多行子查詢使用 in 關鍵字返貨多條查詢。比如，查詢學生id大於2的學生 id 和姓名，這裏不使用條件查詢。

合並查詢

    在實際應用中，為了合並多個select的執行結果，可以使用集合操作符 union，union all。使用UNION 和UNION ALL時，前後查詢的結果集中，字段需要一致。該操作符用於取得兩個結果集的並集。當使用該操作符時，會自動去掉結果集中的重複行。

union 查詢的結果會自動去掉結果集中的重複行，但是union all不會。

查詢 id 小於3或者學生姓名為"張三"的結果。結果和使用or條件查詢結果一致。