MySQL數(shù)據(jù)庫表的增刪改查操作(進(jìn)階)

更新時間：2023年11月25日 09:15:48 作者：H-yajing

這篇文章主要給大家介紹了關(guān)于MySQL數(shù)據(jù)庫表的增刪改查操作,文章記錄常用的SQL查詢語句,包括數(shù)據(jù)庫、表和數(shù)據(jù)的增刪改查,逐漸擴(kuò)展到其他比較復(fù)雜的查詢語句,需要的朋友可以參考下

1. 數(shù)據(jù)庫約束

1.1 約束類型

NOT NULL - 指示某列不能存儲 NULL 值。
UNIQUE - 保證某列的每行必須有唯一的值。
DEFAULT - 規(guī)定沒有給列賦值時的默認(rèn)值。
PRIMARY KEY - NOT NULL 和 UNIQUE 的結(jié)合。確保某列（或兩個列多個列的結(jié)合）有唯一標(biāo)識，有助于更容易更快速地找到表中的一個特定的記錄。
FOREIGN KEY - 保證一個表中的數(shù)據(jù)匹配另一個表中的值的參照完整性。
CHECK - 保證列中的值符合指定的條件。對于MySQL數(shù)據(jù)庫，對CHECK子句進(jìn)行分析，但是忽略CHECK子句

1.2 NULL約束

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

-- 重新設(shè)置學(xué)生表結(jié)構(gòu)
DROP TABLE IF EXISTS student;
CREATE TABLE student (
    id INT NOT NULL ,
    sn INT ,
    name VARCHAR ( 20 ),
    qq_mail VARCHAR ( 20 )
);

1.3 UNIQUE：唯一約束

指定sn列為唯一的、不重復(fù)的：

-- 重新設(shè)置學(xué)生表結(jié)構(gòu)
DROP TABLE IF EXISTS student;
CREATE TABLE student (
    id INT NOT NULL ,
    sn INT UNIQUE,
    name VARCHAR ( 20 ),
    qq_mail VARCHAR ( 20 )
);

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

該約束是：規(guī)定沒有給列賦值時的默認(rèn)值。如：指定插入數(shù)據(jù)時，name列為空，默認(rèn)值unkown：

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

1.5 PRIMARY KEY：主鍵約束

指定id列為主鍵：

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

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

-- 主鍵是 NOT NULL 和 UNIQUE 的結(jié)合，可以不用 NOT NULL
id INT PRIMARY KEY auto_increment,

1.6 FOREIGN KEY：外鍵約束

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

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

案例：

創(chuàng)建班級表classes，id為主鍵：

-- 創(chuàng)建班級表，有使用 MySQL 關(guān)鍵字作為字段時，需要使用 `` 來標(biāo)識
DROP TABLE IF EXISTS classes;
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為主鍵，classes_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)
);

1.7 CHECK約束（了解）

MySQL使用時不報(bào)錯，但忽略該約束：

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

2. 表的設(shè)計(jì)

表的設(shè)計(jì)三大范式：一對一、一對多、多對多。

2.1 一對一

2.2 一對多

2.3 多對多

3. 新增

插入查詢結(jié)果，語法：

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

案例：創(chuàng)建一張用戶表，設(shè)計(jì)有 name 姓名、 email 郵箱、 sex 性別、 mobile 手機(jī)號字段。需要把已有的學(xué)生數(shù)據(jù)復(fù)制進(jìn)來，可以復(fù)制的字段為name 、 qq_mail。

-- 創(chuàng)建用戶表
DROP TABLE IF EXISTS test_user;
CREATE TABLE test_user (
     id INT primary key auto_increment,
     name VARCHAR ( 20 ) comment ' 姓名 ' ,
     age INT comment ' 年齡 ' ,
     email VARCHAR ( 20 ) comment ' 郵箱 ' ,
    sex varchar ( 1 ) comment ' 性別 ' ,
    mobile varchar ( 20 ) comment ' 手機(jī)號 '
);
-- 將學(xué)生表中的所有數(shù)據(jù)復(fù)制到用戶表
insert into test_user(name, email) select name, qq_mail from student;

4. 查詢

4.1 聚合查詢

4.1.1 聚合函數(shù)

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

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

舉例：

COUNT

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

SUM

-- 統(tǒng)計(jì)數(shù)學(xué)成績總分
SELECT SUM (math) FROM exam_result;
-- 不及格 < 60 的總分，沒有結(jié)果，返回 NULL
SELECT SUM (math) FROM exam_result WHERE math < 60 ;

AVG

-- 統(tǒng)計(jì)平均總分
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 ;

4.1.2 GROUP BY子句

SELECT 中使用 GROUP BY 子句可以對指定列進(jìn)行分組查詢。需要滿足：使用 GROUP BY 進(jìn)行分組查詢時，SELECT 指定的字段必須是 “ 分組依據(jù)字段 ” ，其他字段若想出現(xiàn)在 SELECT 中則必須包含在聚合函數(shù)中。

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

案例：準(zhǔn)備測試表及數(shù)據(jù)：職員表，有id（主鍵）、name（姓名）、role（角色）、salary（薪水）查詢每個角色的最高工資、最低工資和平均工資（對角色進(jìn)行分組）

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

4.1.3 HAVING

GROUP BY 子句進(jìn)行分組以后，需要對分組結(jié)果再進(jìn)行條件過濾時，不能使用 WHERE 語句，而需要用 HAVING。顯示平均工資低于1500的角色和它的平均工資

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

4.2 聯(lián)合查詢

實(shí)際開發(fā)中往往數(shù)據(jù)來自不同的表，所以需要多表聯(lián)合查詢。多表查詢是對多張表的數(shù)據(jù)取笛卡爾積：

注意：關(guān)聯(lián)查詢可以對關(guān)聯(lián)表使用別名。

初始化測試數(shù)據(jù)：

insert into classes(name, `desc`) values
( ' 計(jì)算機(jī)系 2019 級 1 班 ' , ' 學(xué)習(xí)了計(jì)算機(jī)原理、 C 和 Java 語言、數(shù)據(jù)結(jié)構(gòu)和算法 ' ),
( ' 中文系 2019 級 3 班 ' , ' 學(xué)習(xí)了中國傳統(tǒng)文學(xué) ' ),
( ' 自動化 2019 級 5 班 ' , ' 學(xué)習(xí)了機(jī)械自動化 ' );
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' ),( ' 中國傳統(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 );

4.2.1 內(nèi)連接

語法：

select 字段 from 表 1 別名 1 [inner] join 表 2 別名 2 on 連接條件 and 其他條件 ;
select 字段 from 表 1 別名 1, 表 2 別名 2 where 連接條件 and 其他條件;

案例：

（ 1 ）查詢 “ 許仙 ” 同學(xué)的成績

select sco .score from student stu inner join score sco on stu .id =sco .student_id and stu .name = ' 許仙 ' ;
-- 或者
select sco .score from student stu, score sco where stu .id =sco .student_id and stu .name = ' 許仙 ' ;

（ 2 ）查詢所有同學(xué)的總成績，及同學(xué)的個人信息：

-- 成績表對學(xué)生表是多對 1 關(guān)系，查詢總成績是根據(jù)成績表的同學(xué) id 來進(jìn)行分組的
SELECT
  stu .sn ,
  stu .NAME ,
  stu .qq_mail ,
  sum( sco .score )
FROM
student stu
JOIN score sco ON stu .id = sco .student_id
GROUP BY
sco .student_id ;

（ 3 ）查詢所有同學(xué)的成績，及同學(xué)的個人信息：

-- 查詢出來的都是有成績的同學(xué)， “ 老外學(xué)中文 ” 同學(xué) 沒有顯示
select * from student stu join score sco on stu .id =sco .student_id ;
-- 學(xué)生表、成績表、課程表 3 張表關(guān)聯(lián)查詢
SELECT
    stu .id ,
    stu .sn ,
    stu .NAME ,
    stu .qq_mail ,
    sco .score ,
    sco .course_id ,
    cou .NAME
FROM
student stu
JOIN score sco ON stu .id = sco .student_id
JOIN course cou ON sco .course_id = cou .id
ORDER BY
 stu.id;

4.2.2 外連接

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

語法：

-- 左外連接，表 1 完全顯示
select 字段名   from 表名 1 left join 表名 2 on 連接條件 ;
-- 右外連接，表 2 完全顯示
select 字段名   from 表名 1 right join 表名 2 on 連接條件 ;

案例：查詢所有同學(xué)的成績，及同學(xué)的個人信息，如果該同學(xué)沒有成績，也需要顯示

-- “ 老外學(xué)中文 ” 同學(xué) 沒有考試成績，也顯示出來了
select * from student stu left join score sco on stu .id =sco .student_id ;
-- 對應(yīng)的右外連接為：
select * from score sco right join student stu on stu .id =sco .student_id ;
-- 學(xué)生表、成績表、課程表 3 張表關(guān)聯(lián)查詢
SELECT
   stu .id ,
   stu .sn ,
   stu .NAME ,
   stu .qq_mail ,
   sco .score ,
   sco .course_id ,
   cou .NAME
FROM
student stu
LEFT JOIN score sco ON stu .id = sco .student_id
LEFT JOIN course cou ON sco .course_id = cou .id
ORDER BY
stu .id

4.2.3 自連接

自連接是指在同一張表連接自身進(jìn)行查詢。

案例：顯示所有 “ 計(jì)算機(jī)原理 ” 成績比 “Java” 成績高的成績信息

-- 先查詢 “ 計(jì)算機(jī)原理 ” 和 “Java” 課程的 id
select id,name from course where name= 'Java' or name= ' 計(jì)算機(jī)原理 ' ;
-- 再查詢成績表中， “ 計(jì)算機(jī)原理 ” 成績比 “Java” 成績 好的信息
SELECT
   s1.*
FROM
   score s1,
   score s2
WHERE
s1 .student_id = s2 .student_id
AND s1 .score < s2 .score
AND s1 .course_id = 1
AND s2 .course_id = 3 ;
-- 也可以使用 join on 語句來進(jìn)行自連接查詢
SELECT
   s1.*
FROM
score s1
JOIN score s2 ON s1 .student_id = s2 .student_id
AND s1 .score < s2 .score
AND s1 .course_id = 1
AND s2 .course_id = 3 ;

4.2.4 子查詢

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

單行子查詢：返回一行記錄的子查詢

查詢與“不想畢業(yè)” 同學(xué)的同班同學(xué)：

select * from student where classes_id=( select classes_id from student where
name= ' 不想畢業(yè) ' );

多行子查詢：返回多行記錄的子查詢

查詢“語文”或“英文”課程的成績信息

1. [NOT] IN關(guān)鍵字：

-- 使用 IN
select * from score where course_id in ( select id from course where name= ' 語文 ' or name= ' 英文 ' );
-- 使用 NOT IN
select * from score where course_id not in ( select id from course where name!= ' 語文 ' and name!= ' 英文 ' );

2. [NOT] EXISTS關(guān)鍵字：

-- 使用 EXISTS
select * from score sco where exists ( select sco .id from course cou where (name= ' 語文 ' or name= ' 英文 ' ) and cou .id = sco .course_id );
-- 使用 NOT EXISTS
select * from score sco where not exists ( select sco .id from course cou where (name!= ' 語文 ' and name!= ' 英文 ' ) and cou .id = sco .course_id );

4.2.5 合并查詢

在實(shí)際應(yīng)用中，為了合并多個 select 的執(zhí)行結(jié)果，可以使用集合操作符 union ， union all 。使用 UNION 和UNION ALL 時，前后查詢的結(jié)果集中，字段需要一致。

union

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

案例：查詢id小于3，或者名字為“英文”的課程：

 select * from course where id<3

union
select * from course where name= ' 英文 ' ;
-- 或者使用 or 來實(shí)現(xiàn)
select * from course where id< 3 or name= ' 英文';

union all

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

案例：查詢 id 小于 3 ，或者名字為 “Java” 的課程：

 -- 可以看到結(jié)果集中出現(xiàn)重復(fù)數(shù)據(jù)Java

select * from course where id< 3
union all
select * from course where name= ' 英文';

總結(jié)

到此這篇關(guān)于MySQL數(shù)據(jù)庫表的增刪改查操作的文章就介紹到這了,更多相關(guān)MySQL表增刪改查內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章:

亚洲乱码中文字幕综合,中国熟女仑乱hd,亚洲精品乱拍国产一区二区三区,一本大道卡一卡二卡三乱码全集资源,又粗又黄又硬又爽的免费视频