MySQL數(shù)據(jù)庫(kù)表的增刪改查操作(進(jìn)階)
1. 數(shù)據(jù)庫(kù)約束
1.1 約束類(lèi)型
- NOT NULL - 指示某列不能存儲(chǔ) NULL 值。
- UNIQUE - 保證某列的每行必須有唯一的值。
- DEFAULT - 規(guī)定沒(méi)有給列賦值時(shí)的默認(rèn)值。
- PRIMARY KEY - NOT NULL 和 UNIQUE 的結(jié)合。確保某列（或兩個(gè)列多個(gè)列的結(jié)合）有唯一標(biāo)識(shí)，有助于更容易更快速地找到表中的一個(gè)特定的記錄。
- FOREIGN KEY - 保證一個(gè)表中的數(shù)據(jù)匹配另一個(gè)表中的值的參照完整性。
- CHECK - 保證列中的值符合指定的條件。對(duì)于MySQL數(shù)據(jù)庫(kù)，對(duì)CHECK子句進(jìn)行分析，但是忽略CHECK子句
1.2 NULL約束
創(chuàng)建表時(shí)，可以指定某列不為空：
-- 重新設(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ī)定沒(méi)有給列賦值時(shí)的默認(rèn)值。如：指定插入數(shù)據(jù)時(shí)，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 )
);對(duì)于整數(shù)類(lèi)型的主鍵，常配搭自增長(zhǎng) auto_increment 來(lái)使用。插入數(shù)據(jù)對(duì)應(yīng)字段不給值時(shí)，使用最大值+1 。
-- 主鍵是 NOT NULL 和 UNIQUE 的結(jié)合，可以不用 NOT NULL id INT PRIMARY KEY auto_increment,
1.6 FOREIGN KEY：外鍵約束
外鍵用于關(guān)聯(lián)其他表的主鍵或唯一鍵，語(yǔ)法：
foreign key (字段名) references 主表(列)
案例：
- 創(chuàng)建班級(jí)表classes，id為主鍵：
-- 創(chuàng)建班級(jí)表，有使用 MySQL 關(guān)鍵字作為字段時(shí)，需要使用 `` 來(lái)標(biāo)識(shí)
DROP TABLE IF EXISTS classes;
CREATE TABLE classes (
id INT PRIMARY KEY auto_increment,
name VARCHAR ( 20 ),
`desc` VARCHAR ( 100 )
); - 創(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：
-- 重新設(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使用時(shí)不報(bào)錯(cuò)，但忽略該約束：
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ì)三大范式：一對(duì)一、一對(duì)多、多對(duì)多。
2.1 一對(duì)一
2.2 一對(duì)多
2.3 多對(duì)多
3. 新增
插入查詢(xún)結(jié)果，語(yǔ)法：
INSERT INTO table_name [(column [, column ...])] SELECT ...
案例：創(chuàng)建一張用戶表，設(shè)計(jì)有 name 姓名、 email 郵箱、 sex 性別、 mobile 手機(jī)號(hào)字段。需要把已有的學(xué)生數(shù)據(jù)復(fù)制進(jìn)來(lái)，可以復(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ī)號(hào) '
);
-- 將學(xué)生表中的所有數(shù)據(jù)復(fù)制到用戶表
insert into test_user(name, email) select name, qq_mail from student;4. 查詢(xún)
4.1 聚合查詢(xún)
4.1.1 聚合函數(shù)
常見(jiàn)的統(tǒng)計(jì)總數(shù)、計(jì)算平均值等操作，可以使用聚合函數(shù)來(lái)實(shí)現(xiàn)，常見(jiàn)的聚合函數(shù)有：
| 函數(shù) | 說(shuō)明 |
| COUNT([DISTINCT] expr) | 返回查詢(xún)到的數(shù)據(jù)的數(shù)量 |
| SUM([DISTINCT] expr) | 返回查詢(xún)到的數(shù)據(jù)的總和，不是數(shù)字沒(méi)有意義 |
| AVG([DISTINCT] expr) | 返回查詢(xún)到的數(shù)據(jù)的平均值，不是數(shù)字沒(méi)有意義 |
| MAX([DISTINCT] expr) | 返回查詢(xún)到的數(shù)據(jù)的最大值，不是數(shù)字沒(méi)有意義 |
| MIN([DISTINCT] expr) | 返回查詢(xún)到的數(shù)據(jù)的最小值，不是數(shù)字沒(méi)有意義 |
舉例：
COUNT
-- 統(tǒng)計(jì)班級(jí)共有多少同學(xué) SELECT COUNT (*) FROM student; SELECT COUNT ( 0 ) FROM student; -- 統(tǒng)計(jì)班級(jí)收集的 qq_mail 有多少個(gè)， qq_mail 為 NULL 的數(shù)據(jù)不會(huì)計(jì)入結(jié)果 SELECT COUNT (qq_mail) FROM student;
SUM
-- 統(tǒng)計(jì)數(shù)學(xué)成績(jī)總分 SELECT SUM (math) FROM exam_result; -- 不及格 < 60 的總分，沒(méi)有結(jié)果，返回 NULL SELECT SUM (math) FROM exam_result WHERE math < 60 ;
AVG
-- 統(tǒng)計(jì)平均總分 SELECT AVG (chinese + math + english) 平均總分 FROM exam_result;
MAX
-- 返回英語(yǔ)最高分 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 子句可以對(duì)指定列進(jìn)行分組查詢(xún)。需要滿足：使用 GROUP BY 進(jìn)行分組查詢(xún)時(shí)，SELECT 指定的字段必須是 “ 分組依據(jù)字段 ” ，其他字段若想出現(xiàn)在 SELECT 中則必須包含在聚合函數(shù)中。
select column1, sum(column2), .. from table group by column1,column3;
案例： 準(zhǔn)備測(cè)試表及數(shù)據(jù)：職員表，有id（主鍵）、name（姓名）、role（角色）、salary（薪水） 查詢(xún)每個(gè)角色的最高工資、最低工資和平均工資（對(duì)角色進(jìn)行分組）
select role,max(salary),min(salary),avg(salary) from emp group by role;
4.1.3 HAVING
GROUP BY 子句進(jìn)行分組以后，需要對(duì)分組結(jié)果再進(jìn)行條件過(guò)濾時(shí)，不能使用 WHERE 語(yǔ)句，而需要用 HAVING。 顯示平均工資低于1500的角色和它的平均工資
select role,max(salary),min(salary),avg(salary) from emp group by role having avg(salary)< 1500 ;
4.2 聯(lián)合查詢(xún)
實(shí)際開(kāi)發(fā)中往往數(shù)據(jù)來(lái)自不同的表，所以需要多表聯(lián)合查詢(xún)。多表查詢(xún)是對(duì)多張表的數(shù)據(jù)取笛卡爾積：
注意：關(guān)聯(lián)查詢(xún)可以對(duì)關(guān)聯(lián)表使用別名。
初始化測(cè)試數(shù)據(jù)：
insert into classes(name, `desc`) values ( ' 計(jì)算機(jī)系 2019 級(jí) 1 班 ' , ' 學(xué)習(xí)了計(jì)算機(jī)原理、 C 和 Java 語(yǔ)言、數(shù)據(jù)結(jié)構(gòu)和算法 ' ), ( ' 中文系 2019 級(jí) 3 班 ' , ' 學(xué)習(xí)了中國(guó)傳統(tǒng)文學(xué) ' ), ( ' 自動(dòng)化 2019 級(jí) 5 班 ' , ' 學(xué)習(xí)了機(jī)械自動(dòng)化 ' ); 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' , ' 好好說(shuō)話 ' , 'say@qq.com' , 2 ), ( '83223' , 'tellme' , null , 2 ), ( '09527' , ' 老外學(xué)中文 ' , 'foreigner@qq.com' , 2 ); insert into course(name) values ( 'Java' ),( ' 中國(guó)傳統(tǒng)文化 ' ),( ' 計(jì)算機(jī)原理 ' ),( ' 語(yǔ)文 ' ),( ' 高階數(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 ), -- 好好說(shuō)話 ( 56 , 6 , 2 ),( 43 , 6 , 4 ),( 79 , 6 , 6 ), -- tellme ( 80 , 7 , 2 ),( 92 , 7 , 6 );
4.2.1 內(nèi)連接
語(yǔ)法：
select 字段 from 表 1 別名 1 [inner] join 表 2 別名 2 on 連接條件 and 其他條件 ;
select 字段 from 表 1 別名 1, 表 2 別名 2 where 連接條件 and 其他條件;
案例：
（ 1 ）查詢(xún) “ 許仙 ” 同學(xué)的 成績(jī)
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 ）查詢(xún)所有同學(xué)的總成績(jī)，及同學(xué)的個(gè)人信息：
-- 成績(jī)表對(duì)學(xué)生表是多對(duì) 1 關(guān)系，查詢(xún)總成績(jī)是根據(jù)成績(jī)表的同學(xué) id 來(lái)進(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 ）查詢(xún)所有同學(xué)的成績(jī)，及同學(xué)的個(gè)人信息：
-- 查詢(xún)出來(lái)的都是有成績(jī)的同學(xué)， “ 老外學(xué)中文 ” 同學(xué) 沒(méi)有顯示
select * from student stu join score sco on stu .id =sco .student_id ;
-- 學(xué)生表、成績(jī)表、課程表 3 張表關(guān)聯(lián)查詢(xú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)合查詢(xún)，左側(cè)的表完全顯示我們就說(shuō)是左外連接；右側(cè)的表完全顯示我們就說(shuō)是右外連接。
語(yǔ)法：
-- 左外連接，表 1 完全顯示 select 字段名 from 表名 1 left join 表名 2 on 連接條件 ; -- 右外連接，表 2 完全顯示 select 字段名 from 表名 1 right join 表名 2 on 連接條件 ;
案例：查詢(xún)所有同學(xué)的成績(jī)，及同學(xué)的個(gè)人信息，如果該同學(xué)沒(méi)有成績(jī)，也需要顯示
-- “ 老外學(xué)中文 ” 同學(xué) 沒(méi)有考試成績(jī)，也顯示出來(lái)了 select * from student stu left join score sco on stu .id =sco .student_id ; -- 對(duì)應(yīng)的右外連接為： select * from score sco right join student stu on stu .id =sco .student_id ; -- 學(xué)生表、成績(jī)表、課程表 3 張表關(guān)聯(lián)查詢(xú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)行查詢(xún)。
案例： 顯示所有 “ 計(jì)算機(jī)原理 ” 成績(jī)比 “Java” 成績(jī)高的成績(jī)信息
-- 先查詢(xún) “ 計(jì)算機(jī)原理 ” 和 “Java” 課程的 id select id,name from course where name= 'Java' or name= ' 計(jì)算機(jī)原理 ' ; -- 再查詢(xún)成績(jī)表中， “ 計(jì)算機(jī)原理 ” 成績(jī)比 “Java” 成績(jī) 好的信息 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 語(yǔ)句來(lái)進(jìn)行自連接查詢(xú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 子查詢(xún)
子查詢(xún)是指嵌入在其他sql語(yǔ)句中的select語(yǔ)句，也叫嵌套查詢(xún)。
單行子查詢(xún)：返回一行記錄的子查詢(xún)
查詢(xún)與“不想畢業(yè)” 同學(xué)的同班同學(xué)：
select * from student where classes_id=( select classes_id from student where name= ' 不想畢業(yè) ' );
多行子查詢(xún)：返回多行記錄的子查詢(xún)
查詢(xún)“語(yǔ)文”或“英文”課程的成績(jī)信息
1. [NOT] IN關(guān)鍵字 ：
-- 使用 IN select * from score where course_id in ( select id from course where name= ' 語(yǔ)文 ' or name= ' 英文 ' ); -- 使用 NOT IN select * from score where course_id not in ( select id from course where name!= ' 語(yǔ)文 ' and name!= ' 英文 ' );
2. [NOT] EXISTS關(guān)鍵字：
-- 使用 EXISTS select * from score sco where exists ( select sco .id from course cou where (name= ' 語(yǔ)文 ' 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!= ' 語(yǔ)文 ' and name!= ' 英文 ' ) and cou .id = sco .course_id );
4.2.5 合并查詢(xún)
在實(shí)際應(yīng)用中，為了合并多個(gè) select 的執(zhí)行結(jié)果，可以使用集合操作符 union ， union all 。使用 UNION 和UNION ALL 時(shí)，前后查詢(xún)的結(jié)果集中，字段需要一致。
- union
該操作符用于取得兩個(gè)結(jié)果集的并集。當(dāng)使用該操作符時(shí)，會(huì)自動(dòng)去掉結(jié)果集中的重復(fù)行。
案例：查詢(xún)id小于3，或者名字為“英文”的課程：
select * from course where id<3 union select * from course where name= ' 英文 ' ; -- 或者使用 or 來(lái)實(shí)現(xiàn) select * from course where id< 3 or name= ' 英文';
- union all
該操作符用于取得兩個(gè)結(jié)果集的并集。當(dāng)使用該操作符時(shí)，不會(huì)去掉結(jié)果集中的重復(fù)行。
案例：查詢(xún) 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ù)庫(kù)表的增刪改查操作的文章就介紹到這了,更多相關(guān)MySQL表增刪改查內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
淺析MySQL內(nèi)存的使用說(shuō)明（全局緩存+線程緩存）
本篇文章是對(duì)MySQL內(nèi)存的使用說(shuō)明（全局緩存+線程緩存）進(jìn)行了詳細(xì)的分析介紹，需要的朋友參考下2013-06-06
查看MySQL中已經(jīng)創(chuàng)建的存儲(chǔ)過(guò)程及其定義
在MySQL中,查看已創(chuàng)建存儲(chǔ)過(guò)程的方法包括使用SHOW CREATE PROCEDURE命令查看存儲(chǔ)過(guò)程定義,查詢(xún)INFORMATION_SCHEMA.Routines表或mysql.proc表獲取存儲(chǔ)過(guò)程信息,使用source命令執(zhí)行存儲(chǔ)過(guò)程創(chuàng)建腳本,或查看存儲(chǔ)過(guò)程的文檔注釋,這些方法有助于了解和管理數(shù)據(jù)庫(kù)中的存儲(chǔ)過(guò)程2024-11-11
詳細(xì)聊聊MySQL中auto_increment有什么作用
auto_increment是用于主鍵自動(dòng)增長(zhǎng)的,從1開(kāi)始增長(zhǎng),下面這篇文章主要給大家介紹了關(guān)于MySQL中auto_increment有什么作用的相關(guān)資料,文中通過(guò)實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下2022-06-06
mysql無(wú)法成功啟動(dòng)服務(wù)的解決方法(通俗易懂!)
Mysql是我們使用數(shù)據(jù)庫(kù)時(shí)需要用到的服務(wù),但是在使用過(guò)程中常常遇到服務(wù)無(wú)法啟動(dòng)的問(wèn)題,下面這篇文章主要給大家介紹了關(guān)于mysql無(wú)法成功啟動(dòng)服務(wù)的解決方法,需要的朋友可以參考下2023-02-02
我們今天主要向大家介紹的是MySQL ALTER語(yǔ)法的實(shí)際運(yùn)用，如果你對(duì)這一技術(shù)，心存好奇的話，以下的文章將會(huì)揭開(kāi)它的神秘面紗。2010-11-11
這篇文章主要介紹了MySQL慢日志實(shí)踐小結(jié)，小編覺(jué)得挺不錯(cuò)的，現(xiàn)在分享給大家，也給大家做個(gè)參考。一起跟隨小編過(guò)來(lái)看看吧2019-01-01
MySQL配置文件my.cnf優(yōu)化詳解(mysql5.5)
這篇文章主要介紹了MySQL配置文件my.cnf優(yōu)化詳解,需要的朋友可以參考下2014-12-12
MySQL的隔離級(jí)別是指數(shù)據(jù)庫(kù)事務(wù)的隔離程度,用于控制并發(fā)事務(wù)之間的相互影響,本文就詳細(xì)的介紹一下如何修改mysql的隔離級(jí)別,感興趣的可以了解一下2023-08-08
干掉一堆mysql數(shù)據(jù)庫(kù)，僅需這樣一個(gè)shell腳本（推薦）
這篇文章主要介紹了干掉一堆mysql數(shù)據(jù)庫(kù)，僅需這樣一個(gè)shell腳本，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2019-04-04
MySQL下使用Inplace和Online方式創(chuàng)建索引的教程
這篇文章主要介紹了MySQL下使用Inplace和Online方式創(chuàng)建索引的教程,針對(duì)InnoDB為存儲(chǔ)引擎的情況,需要的朋友可以參考下2015-11-11