MySQL學習之DDL、DML及DQL基礎總結

推薦學習：

資料庫相關概念

什麼是資料庫

儲存資料的倉庫。它體現我們電腦中，就是一個軟體或者檔案系統。然後把資料都儲存這些特殊的檔案中，並且需要使用固定的語言（SQL語言/語句）去操作檔案中的資料。

• 儲存資料的倉庫，資料是具有組織的進行儲存
• 英文名：DataBase，簡稱 DB

資料庫的優點

資料庫是按照特定的格式將資料儲存在檔案中，通過SQL語句可以方便的對大量資料進行增、刪、改、查操作，資料庫是對大量的資訊進行管理的高效的解決方案。

常見的資料庫

常見的關係型資料庫管理系統

我們開發應用程式的時候，程式中的所有資料，最後都需要儲存到專業軟體中。這些專業的儲存資料的軟體我們稱為資料庫。我們學習資料庫，並不是學習如何去開發一個資料庫軟體，我們學習的是如何使用資料庫以及資料庫中的資料記錄的操作。而資料庫軟體是由第三方公司研發。

• Oracle：它是Oracle公司的大型關係型資料庫。系統可移植性好、使用方便、功能強，適用於各類大、中、小、微機環境。它是一種高效率、安全可靠的。但是它是收費的。
• MYSQL：早期由瑞典一個叫MySQL AB公司開發的，後期被sun公司收購，再後期被Oracle收購。體積小、速度快、總體擁有成本低，尤其是開放原始碼這一特點，一般中小型網站的開發都選擇 MySQL 作為網站資料庫。MySQL6.x版本也開始收費。
• DB2 ：IBM公司的資料庫產品,收費的。常應用在銀行系統中.
• SQLServer：MicroSoft 公司收費的中型的資料庫。C#、.net等語言常使用。
• SyBase：Sybase公司的。 已經淡出歷史舞臺。提供了一個非常專業資料建模的工具PowerDesigner。

常用資料庫：Java開發應用程式主要使用的資料庫：MySQL（5.6）、Oracle、DB2。（原因：開源，免費，功能足夠強大，足以應付web開發）

關係型資料庫

在開發軟體的時候，軟體中的資料之間必然會有一定的關係存在。比如商品和客戶之間的關係，一個客戶是可以買多種商品，而一種商品是可以被多個客戶來購買的。

需要把這些資料儲存在資料庫中，同時也要維護資料之間的關係，這時就可以直接使用上述的那些資料庫。而上述的所有資料庫都屬於關係型資料庫。

關係型資料：設計資料庫的時候，需要使用E-R實體關係圖來描述。

E-R 是兩個單詞的首字母，E表示Entity實體 R表示Relationship 關係。

• 1.資料表中的資料之間必然會有一定的關係存在，比如商品和客戶之間的關係，一個客戶是可以買多種商品，而一種商品是可以被多個客戶來購買的。
• 2.設計資料庫的時候，可以使用ER實體關係圖來描述表之間的關係，E表示Entity 實體 , R表示Relationship 關係
• 3.實體：可以理解成我們Java程式中的一個物件。比如商品，客戶等都是一個實體物件。在E-R圖中使用 矩形(長方形) 表示。
• 4.屬性：實體物件中是含有屬性的，比如商品名、價格等。針對一個實體中的屬性，我們稱為這個實體的資料，在E-R圖中使用橢圓表示。
• 5.關係：實體和實體之間的關係：在E-R圖中使用菱形表示。

需求： 使用E-R圖描述 客戶、商品、訂單之間的關係

MySQL資料庫

SQL簡介

什麼是SQL

Structured Query Language結構化查詢語言。SQL語句不依賴於任何平臺，對所有的資料庫是通用的。學會了SQL語句的使用，可以在任何的資料庫使用,但都有特有內容。SQL語句功能強大、簡單易學、使用方便。

SQL特點

SQL語句是一個非過程性的語言，每一條SQL執行完都會有一個具體的結果出現。多條語句之間沒有影響

SQL作用

SQL語句主要是運算元據庫，資料表，資料表中的資料記錄

SQL通用語法

1. SQL語句可以單行或多行書寫，以分號結尾。
2. 可使用空格和縮排來增強語句的可讀性。
3. MySQL資料庫的SQL語句不區分大小寫，關鍵字開發中一般大寫.
4. 三種註釋
   - 單行註釋: -- 註釋內容
   - 多行註釋: /* 註釋內容 */
   - # 註釋內容：(mysql特有的單行註釋)

SQL分類
SQL是用來存取關聯式資料庫的語言，具有定義、操縱、控制和查詢關係型資料庫的四方面功能。所以針對四方面功能，我們將SQL進行了分類。

1. DDL(Data Definition Language)資料定義語言
   用來定義資料庫物件：資料庫，表，列等。關鍵字：create drop alter truncate(清空資料記錄) show等

2. DML(Data Manipulation Language)資料操作語言★★★

   在資料庫表中更新，增加和刪除記錄。如 update(更新)， insert(插入)， delete(刪除) 不包含查詢

3. DQL(Data Query Language) 資料查詢語言★★★★★
   資料表記錄的查詢。關鍵字select。

4. DCL(Data Control Language)資料控制語言(瞭解)

   是用來設定或更改資料庫使用者或角色許可權的語句，如grant(設定許可權)，revoke(復原許可權)，begin transaction等。這個比較少用到。

MySQL 目錄結構

MySQL 資料庫連線

MySQL是一個需要賬戶名密碼登入的資料庫，登陸後使用，它提供了一個預設的root賬號，使用安裝時設定的密碼即可登入。

啟動資料庫服務：在開啟dos視窗中輸入net start MySQL命令

關閉資料庫服務：在開啟dos視窗中輸入net stop MySQL命令

連線MySQL：登入格式1：mysql -u使用者名稱 -p密碼

mysql -uroot -p123456

登入格式2：mysql[-h連線主機ip地址 -P埠號3306] -u 使用者名稱 -p 密碼

mysql -h 127.0.0.1 -P 3306 -u root -p 123456

如果連線的是本機：可以省略 -h -P 主機IP和埠。這樣就可以登入mysql資料庫了

退出：exit命令

顯示資料庫: show databases;

SQL_DDL_運算元據庫

DDL_建立和檢視資料庫

建立資料庫：

1.直接建立資料庫

create database 資料庫名;

2.判斷資料庫是否存在並建立（如果不存在，則建立）

create database if not exists 資料庫名;

3.建立資料庫並指定字元集（編碼表）

create database 資料庫名 character set 字元集;

說明：字元集就是編碼表名，在mysql中utf8

檢視資料庫：

1.檢視所有資料庫

show databases;

2.檢視某個資料庫的定義資訊

show create database 資料庫名;

3.檢視當前使用的資料庫

select database();

使用和切換資料庫：

use 資料庫名;

DDL_修改和刪除資料庫

修改資料庫：

1.修改資料庫字元集

-- alter 表示修改alter database 資料庫名 default character set 新字元集;

注意：如果修改資料庫指定的編碼表是utf8，記住不能寫utf-8

Java中的常用編碼對應mysql資料庫中的編碼

刪除資料庫：

1.直接刪除

-- drop 刪除資料庫drop database 資料庫名;

2.刪除資料庫時判斷是否存在（如果存在，則刪除）

drop database if exists 資料庫名;

SQL_DDL_運算元據表

DDL_資料庫約束

約束的概念：

• 約束是作用於表中列上的規則，用於限制加入表的資料

• 約束的存在保證了資料庫中資料的正確性、有效性和完整性

約束的分類：

MySQL5.7不支援檢查約束，但寫入語句不會報錯，MySQL8.0版本支援檢查約束

非空約束

非空約束用於保證列中所有資料不能有NULL值

1.建表時新增約束

-- 建立表時新增非空約束create table 表名(
    列名 資料型別 not null,
    ...);

2.建完表之後新增約束

-- 建完表之後新增約束alter table 表名 modify 欄位名 資料型別 not null;

3.刪除約束

alter table 表名 modify 欄位名 資料型別;

唯一約束

唯一約束用於保證列中所有資料各不相同

1.建立表時新增唯一約束

-- 方式1create table 表名(
    欄位名 資料型別 UNIQUE,
    ...);-- 方式2create table 表名(
    欄位名 資料型別,
    ...
    [CONSTRAINT] [約束名稱] UNIQUE(列名));

2.建完表之後新增唯一約束

-- 建完表後新增唯一約束alter table 表名 modify 欄位名 資料型別 UNIQUE;

3.刪除唯一約束

alter table 表名 drop index 欄位名;

主鍵約束

• 主鍵是一行資料的唯一標識，要求非空且唯一
• 一張表只能有一個主鍵

1.建立表時新增主鍵約束

create table 表名(
    欄位名 資料型別 PRIMARY KEY [AUTO_INCREMENT],
    -- [AUTO_INCREMENT] 當不指定值時自動增長
    ...);create table 表名(
    列名 資料型別,
    [CONSTRAINT] [約束名稱] PRIMARY KEY(列名))

2.建完表之後新增主鍵約束

alter table 表名 add PRIMARY KEY(欄位名);

3.刪除主鍵約束

alter table 表名 drop PRIMARY KEY;

預設約束

儲存資料時，未指定值則採用預設值

1.建立表時新增預設約束

create table 表名(
    欄位名 資料型別 default 預設值,
    ...);

2.建完表後新增預設約束

alter table 表名 alter 列名 set DEFAULT 預設值;

3.刪除約束

alter table 表名 alter 列名 drop DEFAULT;

DDL_建立和檢視和表

前提 ：建立資料庫db1並使用這個資料庫

-- 建立資料庫create database db1;-- 使用資料庫use db1;

建立表：

create table 表名(
    欄位名1 欄位型別 約束條件,
    欄位名2 欄位型別 約束條件,
    ...
    欄位名n 欄位型別 約束條件);-- 注意：最後一個欄位不加逗號

建立一個表結構和其他表結構相同的表

create table 表名 like 其他表名;

MySQL中常用的資料型別

案列需求：

設計一張學生表，要求如下：

• 學號，要求唯一主鍵，自增
• 姓名，不能為空，且唯一
• 性別，只有男和女，預設值為null
• 班級，字串型別
• 入學時間，取值為年、月、日
• 數學成績，double型別，預設為60分
• 英語成績，double型別，沒有預設值

create table students(
    id int primary key auto_increment,
    name varchar(10) not null unique,
    sex enum('男','女') default null,
    class varchar(10),
    starttime date,
    math int default 60,
    english int);

檢視表：

1.檢視某個資料庫中所有的表

show tables;

2.檢視表結構

desc 表名;

3.檢視建立表的SQL語句

show create table 表名;-- 根據該語句檢視上面案列的建表sql語句show create table students;

DDL_刪除表和修改表的結構

刪除表：

1.直接刪除

drop table 表名;

2.刪除表時判斷表是否存在（如果存在，則刪除）

drop table if exists 表名;

修改表：

1.修改表名

alter table 舊錶名 rename to 新表名;

2.向表中新增一個欄位（一列）

alter table 表名 add 欄位名 資料型別;

3.修改表中欄位資料型別

alter table 表名 modify 欄位名 新的資料型別;

4.修改表中欄位名（列名）和資料型別

alter table 表名 change 欄位名 新的欄位名 新的資料型別;

5.刪除表中欄位（列）

alter table 表名 drop 欄位名;

SQL_DML_運算元據庫

DML_插入表資料

1.插入全部欄位

-- 全部欄位寫出來insert into 表名(欄位1,欄位2,...) values(值1,值2,...);
-- 插入全部不寫欄位名insert into 表名 values(值1,值2,...);
-- 給案例中的表插入資料insert into students(id,name,sex,class,starttime,math,english) values(1,'張三','男','高三1班','2022-03-02',80,69);
insert into students values(2,'李四','女','高三2班','2022-03-01',70,80);

2.插入部分資料

-- 插入姓名，班級，入學時間，英語成績-- id預設增長，性別預設null，數學預設60
insert into students(name,class,starttime,english) values('王五','高三3班','2022-03-02',78);

說明：插入部分資料的時候，要求列名一定書寫出來。

3.批次插入資料

insert into 表名 values(欄位值1, 欄位值2...),(欄位值1, 欄位值2...),(欄位值1, 欄位值2...);

沒有新增資料的欄位會使用NULL

注意：

• 值與列一一對應。有多少個列，就需要寫多少個值。如果某一個列沒有值，可以使用null，表示插入空。

• 值的資料型別，與列被定義的資料型別要相匹配，並且值的長度，不能夠超過定義的列的長度。

• 字串：插入字元型別的資料，建議寫英文單引號括起來。在mysql中，使用單引號表示字串

• date 時間型別的資料也得使用英文單引號括起來： 如yyyy-MM-dd

DML_更新表資料

1.不帶條件修改資料

update 表名 set 欄位名=新的值,欄位名=新的值,...;
-- 注意：不帶條件的修改是將資料表中的整列都做修改
-- 修改students表中math的值為90update students set math=90;

2.帶條件修改資料

update 表名 set 欄位名=新的值,欄位名=新的值,... where 條件;
-- 修改students表中王五的性別為男，數學成績設定為70update students set sex='男',math=70 where name='王五';

3.關鍵字說明

UPDATE: 表示修改記錄 
SET: 要改哪個欄位WHERE: 設定條件

4.注意

• 不帶條件的更新資料庫記錄：UPDATE 表名 SET 欄位名=新的值；是將整個表中修改的列修改
• 帶條件：UPDATE 表名 SET 欄位名=新的值 WHERE 條件

DML_刪除表記錄

1.不帶條件刪除

DELETE -- 刪除記錄DELETE FROM 表名;表還在，可以操作，只是刪除資料。

2.帶條件刪除

DELETE FROM 表名 WHERE 條件;-- 刪除學生表中的王五的資訊DELETE FROM students WHERE name='王五';

3.truncate刪除表記錄（屬於DDL）

truncate table 表名;

4.truncate和delete區別

• delete是將表中的資料一條一條刪除
• truncate是將整個表摧毀，重新建立一個新的表,新的表結構和原來表結構一模一樣

SQL_DQL_ 簡單查詢資料

準備一張學生表，在這張表上進行查詢操作

DQL_基礎查詢

1.查詢所有資料

select * from 表名;
-- 查詢學生表中所有的資料select * from students;

2.查詢指定列的資料

select 欄位名1,欄位名2,... from 表名;
-- 查詢姓名和班級這兩個欄位select name,class from students;

3.查詢到的欄位設定別名

select 欄位名1 as 別名1,欄位名2 as 別名2 from 表名;
-- 查詢students表中的欄位並設定別名select id as 學號,name as 姓名,sex as 性別,class as 班級 , starttime as 入學時間 from students;

4.查詢到的資料去重

-- DISTINCT  去重複-- 查詢班級欄位結果不出現重複的select DISTINCT class from students;

DQL_條件查詢

1.條件查詢語法

select 欄位名1,欄位名2,... where 條件列表;

2.條件運運算元

3.查詢數學成績大於80並且性別為男的學生

-- 兩個條件同時滿足select * from students where math > 80 and sex='男';

4.查詢英語成績在60-80之間的學生

-- BETWEEN 值1 AND 值2 -- 表示從值1到值2範圍，包頭又包尾
select * from students where english between 60 and 80;
select * from students where english>=60 && english<=80;

5.查詢學號為1或者2或者3的學生

-- in裡面的每個資料都會作為一次條件，只要滿足條件的就會顯示select * from students where id in (1,2,3);

DQL_模糊查詢

LIKE：表示模糊查詢

select * from 表名 where 欄位名 like '通配字元';

MySQL萬用字元有兩個：

• %：表示0個或多個字元（任意字元）
• _：表示一個字元

1.查詢名字中以孫開頭的學生

-- '孫%'表示孫後面有任意個字元select * from students where name like '孫%';

2.查詢名字中以孫開頭的兩個字的學習

-- '孫_'表示孫後面只能有一個字元select * from students where name like '孫_';

DQL_查詢排序

通過ORDER BY子句，可以將查詢出的結果進行排序（排序只是顯示方式，不會影響資料庫中資料的順序）

-- ASC:升序排序(預設)-- DESC:降序排序select 欄位 from 表名 order by 排序欄位 [ASC|DESC];

1.單列排序

-- 查詢學生的數學成績按照升序排序select * from students order by math ASC;

2.組合排序

-- 查詢數學成績升序的基礎上，英語成績降序-- 組合排序就是先按第一個欄位進行排序，如果第一個欄位相同，才按第二個欄位進行排序，依次類推。
select * from students order by math ASC,english DESC;

DQL_ 聚合函數

之前我們做的查詢都是橫向查詢，它們都是根據條件一行一行的進行判斷，而使用聚合函數查詢是縱向查詢，它是對一列的值進行計算，然後返回一個結果值；另外聚合函數會忽略空值，對於null不作為統計。

1.五個聚合函數

2.聚合函數語法

select 聚合函數名(列名) from 表名;

注意：null 值不參與所有聚合函數運算

3.查詢學生總數

select count(id) from students;-- 通常使用select count(*) from students;

4.查詢最高分和最低分

-- 查詢數學最高分和英語最低分select max(math),min(english) from students;

5.求和求平均值

-- 求該表數學總分和平均值select sum(math),avg(math) from students;

6.ifnull()函數

-- ifnull(列名,預設值)函數表示判斷該列是否為空值，如果為null，返回預設值，如果不為空，返回實際值ifnull(math,60); 
-- 如果數學成績為null時，返回60，如果不為null，就返回實際值

DQL_分組查詢

分組： 按照某一列或者某幾列。把相同的資料，進行合併輸出。

1.注意

• 按照某一列進行分組，目的為了統計使用。
• 聚合函數：分組之後進行計算
• 通常select後面的內容是被分組的列，以及聚合函數
• 在sql語句中的where後面不允許新增聚合函數
• 可以使用having條件，表示分組之後的條件，在having後面可以書寫聚合函數

2.查詢各個班級的數學成績總和

-- 查詢每個班的數學成績總和select class,sum(math) from students group by class;

3.having用法

having必須和group by 一起使用，having和where的用法一模一樣，where怎麼使用having就怎麼使用，where不能使用的，having也可以使用，比如說where後面不可以使用聚合函數，但是在having後面是可以使用聚合函數的。

-- 查詢每個班數學總成績大於300分的班級並顯示總成績
select class,sum(math) from students group by class having sum(math)>300;

4.where和having的區別

• having 通常與group by 分組結合使用。 where 和分組無關。

• having 可以書寫聚合函數 (聚合函數出現的位置： having 之後)，例如having中的 聚合函數（count，sum，avg，max，min），是不可以出現where條件中。

• where 是在分組之前進行過濾的，having 是在分組之後進行過濾的。

DQL_分頁查詢

1.應用和概念

比如我們登入京東，淘寶，返回的商品資訊可能有幾萬條，不是一次全部顯示出來。是一頁顯示固定的條數。假設我們一每頁顯示5條記錄的方式來分頁。

-- 起始索引：從0開始，索引是0表示資料表第一行資料select 欄位列表 from 表名 limit 起始索引,查詢條目數;

計算公式：起始索引=（當前頁碼-1）* 每頁顯示的條數

注意：

• 分頁查詢limit是MySQL資料庫的方言
• Oracle分頁查詢使用rownumber
• SQLServer分頁查詢使用top

2.分頁查詢

-- 查詢學生表中資料，每四條資料為一頁
select * from students limit 0,4;
select * from students limit 4,4;
select * from students limit 8,4;
select * from students limit 12,4;
...
-- 注意:最後一行不夠查詢條目數，有多少就顯示多少

3.返回前幾條或者中間某幾行資料

-- 2表示分頁查詢的索引，對應資料表是第3行資料，4表示每頁顯示4條資料
-- 查詢從第三行資料開始查詢之後的四條資料
select * from students limit 2,4;

4.SQL執行順序

SELECT 欄位名(5) FROM 表名(1) WHERE 條件(2) GROUP BY 分組列名(3) HAVING 條件(4) ORDER BY 排序列名(6) LIMIT 跳過行數, 返回行數(7);
執行順序：1234567

順序：1234567

推薦學習：

以上就是MySQL學習之DDL、DML及DQL基礎總結的詳細內容，更多請關注TW511.COM其它相關文章！