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

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

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

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

1.1 約束類型

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

1.2 NULL約束

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

-- 重新設置學生表結構
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列為唯一的、不重復的：

-- 重新設置學生表結構
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：默認值約束

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

-- 重新設置學生表結構
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列為主鍵：

-- 重新設置學生表結構
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ù)對應字段不給值時，使用最大值+1 。

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

1.6 FOREIGN KEY：外鍵約束

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

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

案例：

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

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

創(chuàng)建學生表student，一個學生對應一個班級，一個班級對應多個學生。使用id為主鍵，classes_id為外鍵，關聯(lián)班級表id：

-- 重新設置學生表結構
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使用時不報錯，但忽略該約束：

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

2. 表的設計

表的設計三大范式：一對一、一對多、多對多。

2.1 一對一

2.2 一對多

2.3 多對多

3. 新增

插入查詢結果，語法：

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

案例：創(chuàng)建一張用戶表，設計有 name 姓名、 email 郵箱、 sex 性別、 mobile 手機號字段。需要把已有的學生數(shù)據(jù)復制進來，可以復制的字段為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 ' 手機號 '
);
-- 將學生表中的所有數(shù)據(jù)復制到用戶表
insert into test_user(name, email) select name, qq_mail from student;

4. 查詢

4.1 聚合查詢

4.1.1 聚合函數(shù)

常見的統(tǒng)計總數(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)計班級共有多少同學
SELECT COUNT (*) FROM student;
SELECT COUNT ( 0 ) FROM student;
-- 統(tǒng)計班級收集的 qq_mail 有多少個， qq_mail 為 NULL 的數(shù)據(jù)不會計入結果
SELECT COUNT (qq_mail) FROM student;

SUM

-- 統(tǒng)計數(shù)學成績總分
SELECT SUM (math) FROM exam_result;
-- 不及格 < 60 的總分，沒有結果，返回 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ù)學最低分
SELECT MIN (math) FROM exam_result WHERE math > 70 ;

4.1.2 GROUP BY子句

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

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

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

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

4.1.3 HAVING

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

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

4.2 聯(lián)合查詢

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

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

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

insert into classes(name, `desc`) values
( ' 計算機系 2019 級 1 班 ' , ' 學習了計算機原理、 C 和 Java 語言、數(shù)據(jù)結構和算法 ' ),
( ' 中文系 2019 級 3 班 ' , ' 學習了中國傳統(tǒng)文學 ' ),
( ' 自動化 2019 級 5 班 ' , ' 學習了機械自動化 ' );
insert into student(sn, name, qq_mail, classes_id) values
( '09982' , ' 黑旋風李逵 ' , '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' , ' 老外學中文 ' , 'foreigner@qq.com' , 2 );
insert into course(name) values
( 'Java' ),( ' 中國傳統(tǒng)文化 ' ),( ' 計算機原理 ' ),( ' 語文 ' ),( ' 高階數(shù)學 ' ),( ' 英文 ' );
insert into score(score, student_id, course_id) values
-- 黑旋風李逵
( 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 內連接

語法：

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

案例：

（ 1 ）查詢 “ 許仙 ” 同學的成績

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 ）查詢所有同學的總成績，及同學的個人信息：

-- 成績表對學生表是多對 1 關系，查詢總成績是根據(jù)成績表的同學 id 來進行分組的
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 ）查詢所有同學的成績，及同學的個人信息：

-- 查詢出來的都是有成績的同學， “ 老外學中文 ” 同學 沒有顯示
select * from student stu join score sco on stu .id =sco .student_id ;
-- 學生表、成績表、課程表 3 張表關聯(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)合查詢，左側的表完全顯示我們就說是左外連接；右側的表完全顯示我們就說是右外連接。

語法：

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

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

-- “ 老外學中文 ” 同學 沒有考試成績，也顯示出來了
select * from student stu left join score sco on stu .id =sco .student_id ;
-- 對應的右外連接為：
select * from score sco right join student stu on stu .id =sco .student_id ;
-- 學生表、成績表、課程表 3 張表關聯(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 自連接

自連接是指在同一張表連接自身進行查詢。

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

-- 先查詢 “ 計算機原理 ” 和 “Java” 課程的 id
select id,name from course where name= 'Java' or name= ' 計算機原理 ' ;
-- 再查詢成績表中， “ 計算機原理 ” 成績比 “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 語句來進行自連接查詢
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è)” 同學的同班同學：

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

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

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

1. [NOT] IN關鍵字：

-- 使用 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關鍵字：

-- 使用 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 合并查詢

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

union

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

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

 select * from course where id<3

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

union all

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

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

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

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

總結

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

您可能感興趣的文章:

淺析MySQL內存的使用說明（全局緩存+線程緩存）
本篇文章是對MySQL內存的使用說明（全局緩存+線程緩存）進行了詳細的分析介紹，需要的朋友參考下
2013-06-06
查看MySQL中已經創(chuàng)建的存儲過程及其定義
在MySQL中,查看已創(chuàng)建存儲過程的方法包括使用SHOW CREATE PROCEDURE命令查看存儲過程定義,查詢INFORMATION_SCHEMA.Routines表或mysql.proc表獲取存儲過程信息,使用source命令執(zhí)行存儲過程創(chuàng)建腳本,或查看存儲過程的文檔注釋,這些方法有助于了解和管理數(shù)據(jù)庫中的存儲過程
2024-11-11
詳細聊聊MySQL中auto_increment有什么作用
auto_increment是用于主鍵自動增長的,從1開始增長,下面這篇文章主要給大家介紹了關于MySQL中auto_increment有什么作用的相關資料,文中通過實例代碼介紹的非常詳細,需要的朋友可以參考下
2022-06-06
mysql無法成功啟動服務的解決方法(通俗易懂!)
Mysql是我們使用數(shù)據(jù)庫時需要用到的服務,但是在使用過程中常常遇到服務無法啟動的問題,下面這篇文章主要給大家介紹了關于mysql無法成功啟動服務的解決方法,需要的朋友可以參考下
2023-02-02
MySQL ALTER語法的運用方法
我們今天主要向大家介紹的是MySQL ALTER語法的實際運用，如果你對這一技術，心存好奇的話，以下的文章將會揭開它的神秘面紗。
2010-11-11
MySQL慢日志實踐小結
這篇文章主要介紹了MySQL慢日志實踐小結，小編覺得挺不錯的，現(xiàn)在分享給大家，也給大家做個參考。一起跟隨小編過來看看吧
2019-01-01
MySQL配置文件my.cnf優(yōu)化詳解(mysql5.5)
這篇文章主要介紹了MySQL配置文件my.cnf優(yōu)化詳解,需要的朋友可以參考下
2014-12-12
如何修改mysql的隔離級別
MySQL的隔離級別是指數(shù)據(jù)庫事務的隔離程度,用于控制并發(fā)事務之間的相互影響,本文就詳細的介紹一下如何修改mysql的隔離級別,感興趣的可以了解一下
2023-08-08
干掉一堆mysql數(shù)據(jù)庫，僅需這樣一個shell腳本（推薦）
這篇文章主要介紹了干掉一堆mysql數(shù)據(jù)庫，僅需這樣一個shell腳本，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧
2019-04-04
MySQL下使用Inplace和Online方式創(chuàng)建索引的教程
這篇文章主要介紹了MySQL下使用Inplace和Online方式創(chuàng)建索引的教程,針對InnoDB為存儲引擎的情況,需要的朋友可以參考下
2015-11-11