站長資訊網
本篇文章帶大家詳細了解MySQL表的CURD操作，希望對大家有所幫助！
一、SQL語句
操作關系型數據庫的編程語言，定義了一套操作關系型數據庫的統一標準，簡稱SQL。
– SQL通用語法
1 . SQL語句可以單行或多行書寫，以分號結尾。
2 . SQL語句可以使用空格/縮進來增強語句的可讀性。
3 . MySQL數據庫的SQL語句不區分大小寫，關鍵字建議使用大寫。
– 注釋
- 單行注釋： – 注釋內容 或者使用 # 注釋內容 。
- 多行注釋：/* 注釋內容 */
– SQL語句分類
| 分類 | 說明 |
|---|---|
| DDL（deifnition） | 數據定義語言（用來定義數據庫對象，數據庫，表，字段） |
| DML(manipulation) | 數據操縱語言（對數據庫 表中的是數據進行增刪改） |
| DQL(query) | 數據查詢語言，用來查詢數據庫中表的記錄 |
| DCL(control) | 數據控制語言，用來創建數據庫用戶，控制數據庫的訪問權限 |
二、 基礎表操作
– 創建表
-
同一個數據庫中，不能有兩個表的名字相同，表名和列名不能和SQL的關鍵詞重復。
-
語法：
create table 表名(定義列1, 定義列2, .......); 列 -> 變量名 數據類型
登錄后復制
- 舉例：
mysql> create table if not exists book( -> book_name varchar(32) comment '圖書名稱', -> book_author varchar(32)comment '圖書作者' , -> book_price decimal(12,2) comment '圖書價格', -> book_category varchar(12) comment '圖書分類', -> publish_data timestamp -> )character set utf8mb4; Query OK, 0 rows affected (0.04 sec)
登錄后復制
– 查看庫中的表
-
語法：
show tables;
登錄后復制 -
舉例：
mysql> show tables; +--------------------+ | Tables_in_mytestdb | +--------------------+ | book | +--------------------+ 1 row in set (0.00 sec)
登錄后復制
– 查看表結構
-
語法：
desc 表名；
登錄后復制 -
舉例：
MySQL數據庫中的表結構主要包含以下幾種信息： 字段名稱，字段類型，是否允許為空，索引類型。默認值，擴充信息
– 刪除表
- 語法：
drop table 表名
登錄后復制
- 舉例 :
mysql> desc test1; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | name | varchar(10) | YES | | NULL | | | age | int(11) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.02 sec) mysql> drop table test1; Query OK, 0 rows affected (0.04 sec) mysql> desc test1; ERROR 1146 (42S02): Table 'mytestdb.test1' doesn't exist
登錄后復制
– 重命名表
-
語法：
rename table old_name to new_name;
登錄后復制 -
舉例：
mysql> rename table book to eBook; Query OK, 0 rows affected (0.05 sec) mysql> show tables; +--------------------+ | Tables_in_mytestdb | +--------------------+ | ebook | +--------------------+ 1 row in set (0.00 sec)
登錄后復制
三、MySQL 中的增刪查改操作
CRUD 即增加(Create)、查詢(Retrieve)、更新(Update)、刪除(Delete)四個單詞的首字母縮寫
案例:
-- 創建一張圖書表 mysql> create table if not exists book( -> book_name varchar(32) comment '圖書名稱', -> book_author varchar(32)comment '圖書作者' , -> book_price decimal(12,2) comment '圖書價格', -> book_category varchar(12) comment '圖書分類', -> publish_data timestamp -> )character set utf8mb4;
登錄后復制
– 增加（insert語句）
-
單行插入(全列)
insert into 表名 values(對應列的參數列表); -- 一次插入一行
登錄后復制 -
多行插入(全列)
insert into 表名 values(對應列的實參列表), (對應列的參數列表), (對應列的參數列表); -- 一次插入多行 -- 一次插入多行
登錄后復制 -
指定列插入
- values 后面( )中的內容, 個數和類型要和表名后面( )中指定的結構匹配.
- 未被指定的列會以默認值進行填充.
insert into 表名 (需要插入的列) values(對應列的參數列表); -- 一次插入一行 insert into 表名 (需要插入的列) values(對應列的參數列表), (), ().... -- 一次插入多行
登錄后復制 -
案例
# 單行輸入 mysql> insert into book values('計算機網絡','謝希仁',45,'計算機類','2020-12-25 12:51:00'); Query OK, 1 row affected (0.01 sec) #多行輸入 mysql> insert into book values('計算機組成原理','王峰',45,'硬件類','2020-12-12 12:00:00'), -> ('微機原理','李華',97,'硬件類','2000-12-19 20:00:00'); Query OK, 2 rows affected (0.04 sec) Records: 2 Duplicates: 0 Warnings: 0 #指定列插入 mysql> insert into book(book_name,book_author,publish_data) values ('軟件工程','張三','2020-05-06 12:00:00'); Query OK, 1 row affected (0.02 sec)登錄后復制 -
插入數據后的表如圖所示:
在MySQL當中 , 多條記錄逐次插入的效率是要低于一次把多條紀錄一起插入的 ,原因如下:
- 網絡請求和響應時間開銷 , 每次插入都會有一定的時間開銷.
- 數據庫服務器是把數據保存在硬盤上的 , IO操作時,操作的次數帶來的影響大于數據量.
- 每一次sql操作,內部開啟的事務也會占據一定的開銷.
– 查詢(select語句)
- 全列查詢
語法
select * from 表名 -- * 表示通配符, 可以匹配表中的所有列.
登錄后復制
企業級別的數據庫中慎用, 容易把I/O或者網絡帶寬吃滿，如果有外邊的用戶客戶端要通過寬帶訪問服務器時，服務器就無法做出正確的響應．
示例
select * from book;
登錄后復制
- 指定列查詢
select 列名... from 表名
登錄后復制
- 示例
mysql> select book_name from book; +----------------+ | book_name | +----------------+ | 計算機網絡 | | 計算機組成原理 | | 微機原理 | | 軟件工程 | +----------------+ 4 rows in set (0.01 sec) mysql> select book_author,book_price from book; +-------------+------------+ | book_author | book_price | +-------------+------------+ | 謝希仁 | 45.00 | | 王峰 | 45.00 | | 李華 | 97.00 | | 張三 | NULL | +-------------+------------+ 4 rows in set (0.00 sec)
登錄后復制
- 查詢你字段為表達式
select 字段或表達式, 字段或表達式... from 表名;
登錄后復制
- 示例
-- 查詢圖書漲價10元后所有圖書的名稱作者和價格 mysql> select book_name ,book_author,book_price + 10 from book; +----------------+-------------+-----------------+ | book_name | book_author | book_price + 10 | +----------------+-------------+-----------------+ | 計算機網絡 | 謝希仁 | 55.00 | | 計算機組成原理 | 王峰 | 55.00 | | 微機原理 | 李華 | 107.00 | | 軟件工程 | 張三 | NULL | +----------------+-------------+-----------------+ 4 rows in set (0.00 sec)
登錄后復制
- 將表達式或者字段指定別名查詢
mysql中支持給所查詢的表達式取一個別名 , 使用 as 可以使查詢結果更加直觀 , 代碼的可讀性也會更強.
select 列名或表達式 as 別名, ... from 表名;
登錄后復制
- 示例
-- 將漲價20元后的圖書價格取為別名newprice mysql> select book_name,book_author,book_price + 20 as newprice from book; +----------------+-------------+----------+ | book_name | book_author | newprice | +----------------+-------------+----------+ | 計算機網絡 | 謝希仁 | 65.00 | | 計算機組成原理 | 王峰 | 65.00 | | 微機原理 | 李華 | 117.00 | | 軟件工程 | 張三 | NULL | +----------------+-------------+----------+ 4 rows in set (0.00 sec)
登錄后復制
- 去重查詢
select distinct 列名 from 表名
登錄后復制
- 示例
--book 表中插入一條重復的book_name數據 mysql> insert into book values('計算機網絡','張華',89,'計算機類','2020-11-23 11:00:00'); Query OK, 1 row affected (0.00 sec) mysql> select book_name from book; +----------------+ | book_name | +----------------+ | 計算機網絡 | | 計算機組成原理 | | 微機原理 | | 軟件工程 | | 計算機網絡 | +----------------+ 5 rows in set (0.00 sec) mysql> select distinct book_name from book; +----------------+ | book_name | +----------------+ | 計算機網絡 | | 計算機組成原理 | | 微機原理 | | 軟件工程 | +----------------+ 4 rows in set (0.00 sec)
登錄后復制
查詢結果當中,沒有了重復的book _ name 元素,達到了去重效果.
- 排序查詢
select 列名 from 表名 order by 列名 asc(升序)/desc(降序); # 想要排序的列
登錄后復制
- 示例
# 按照書的價格升序進行排列 mysql> select book_name,book_price from book order by book_price asc; +----------------+------------+ | book_name | book_price | +----------------+------------+ | 軟件工程 | NULL | | 計算機網絡 | 45.00 | | 計算機組成原理 | 45.00 | | 計算機網絡 | 89.00 | | 微機原理 | 97.00 | +----------------+------------+ 5 rows in set (0.00 sec) #按照書的價格降序進行排列 mysql> select book_name,book_price from book order by book_price desc; +----------------+------------+ | book_name | book_price | +----------------+------------+ | 微機原理 | 97.00 | | 計算機網絡 | 89.00 | | 計算機網絡 | 45.00 | | 計算機組成原理 | 45.00 | | 軟件工程 | NULL | +----------------+------------+ 5 rows in set (0.00 sec)
登錄后復制
- 使用排序查詢時 , 升序查詢 asc 可以省略, 即默認為升序排列, null值一定為其中最小的.
- 可以對多個字段進行排序,優先級按照書寫的順序進行.
- 示例
# 查詢按照價格升序 ,年份降序 select name,price,age from book order by price asc,age desc; #查詢按照總成績進行降序 select name,english+math+chinese as total from grade order by total desc;
登錄后復制
- 條件查詢
當我們使用查詢時, 通常具有各種各樣的前提條件 , 此時就需要使用條件查詢來完成.
select 列名.. from 表名..where + 條件
登錄后復制
比較運算符
| 運算符 | 說明 |
|---|---|
| >, >=, <, <= | 大于，大于等于，小于，小于等于 |
| = | 等于，null 不安全，例如 null = null 的結果是 null(false) |
| <=> | 等于，null 安全，例如 null <=> null 的結果是 true(1) |
| !=, <> | 不等于 |
| between a0 and a1 | 范圍匹配，[a0, a1]，如果 a0 <= value <= a1，返回 true(1) |
| in (option, …) | 如果是 option 中的任意一個，返回 true(1) |
| is null | 是 null |
| is not null | 不是 null |
| like | 模糊匹配; % 表示任意多個（包括 0 個）任意字符；_ 表示任意一個字符 |
邏輯運算符
| 運算符 | 說明 |
|---|---|
| and | 多個條件必須為 true , 結果才為true |
| or | 任意一個條件為true 結果才為true |
| not | 條件為true , 結果為false |
注：
WHERE條件可以使用表達式，但不能使用別名。
AND的優先級高于OR，在同時使用時，需要使用小括號()包裹優先執行的部分
- 案例
-- 查詢圖書價格低于50的圖書作者和圖書名稱 mysql> select book_name,book_author from book where book_price < 50; +----------------+-------------+ | book_name | book_author | +----------------+-------------+ | 計算機網絡 | 謝希仁 | | 計算機組成原理 | 王峰 | +----------------+-------------+ 2 rows in set (0.05 sec) -- 查詢圖書價格等于97的圖書作者 mysql> select book_name ,book_author from book where book_price = 97; +-----------+-------------+ | book_name | book_author | +-----------+-------------+ | 微機原理 | 李華 | +-----------+-------------+ 1 row in set (0.00 sec) -- 查詢圖書價格在50 - 100 之間的圖書名稱 mysql> select book_name from book where book_price between 50 and 100; +------------+ | book_name | +------------+ | 微機原理 | | 計算機網絡 | +------------+ 2 rows in set (0.02 sec) -- 查詢圖書價格在此范圍內的圖書名稱 mysql> select book_name from book where book_price in (12,45); +----------------+ | book_name | +----------------+ | 計算機網絡 | | 計算機組成原理 | +----------------+ 2 rows in set (0.00 sec)
登錄后復制
模糊查詢
- % 匹配任意多個（包括 0 個）字符
- _ 匹配嚴格的一個字符
#查詢姓張的作者的書本價格書名. mysql> select book_price,book_name,book_author from book where book_author like '張%'; +------------+------------+-------------+ | book_price | book_name | book_author | +------------+------------+-------------+ | NULL | 軟件工程 | 張三 | | 89.00 | 計算機網絡 | 張華 | +------------+------------+-------------+ 2 rows in set (0.00 sec) # 查詢前綴為'計算機'后綴為七個字的書籍名稱 mysql> select book_name from book where book_name like '計算機____'; +----------------+ | book_name | +----------------+ | 計算機組成原理 | +----------------+ #查詢前綴為'計算機'的書籍名稱并去重 mysql> select distinct book_name from book where book_name like '計算機%'; +----------------+ | book_name | +----------------+ | 計算機網絡 | | 計算機組成原理 | +----------------+ 2 rows in set (0.00 sec)
登錄后復制
- 分頁查詢
分頁查詢即將查詢出的結果 , 按頁進行呈現,并不是一次性展現出來,這種模式就是分頁查詢, mysql當中使用limit來實現分頁查詢.
- limit 子句當中接受一個或者兩個參數 , 這兩個參數的值為0 或者正整數
兩個參數的limit子句的用法
select 元素1,元素2 from 表名 limit offset,count; #offset參數指定要返回的第一行的偏移量。第一行的偏移量為0，而不是1。 #count指定要返回的最大行數。
登錄后復制
示例:
mysql> select book_author from book limit 2, 3; +-------------+ | book_author | +-------------+ | 李華 | | 張三 | | 張華 | +-------------+ 3 rows in set (0.02 sec) #表示獲取列表當中偏移量為2(表示從第3行開始), 最大行數為3的作者名稱
登錄后復制
帶有一個參數的limit子句的用法
select 列名1.列名2 from 表名 limit count; # 表示從結果集的開頭返回的最大行數為count; # 獲取前count行的記錄
登錄后復制
等同于
select 列名1 ,列名2 from 表名 limit 0 , count;# 第一行的偏移量為0
登錄后復制
示例
mysql> select book_price from book limit 5; +------------+ | book_price | +------------+ | 45.00 | | 45.00 | | 97.00 | | NULL | | 89.00 | +------------+ 5 rows in set (0.00 sec) # 獲取表中前五行的圖書價格 , 最大行數為5
登錄后復制
-
limit 結合 order by 語句 和其他條件可以獲取n個最大或者最小值
select book_name,book_price from book order by book_price desc limit 3; #獲取價格前三高的圖書名稱和圖書價格 mysql> select book_price,book_name from book order by book_price desc limit 3; +------------+------------+ | book_price | book_name | +------------+------------+ | 97.00 | 微機原理 | | 89.00 | 計算機網絡 | | 45.00 | 計算機網絡 | +------------+------------+ 3 rows in set (0.01 sec)
登錄后復制 -
使用limit 獲取第n高個最大值
偏移量從
0開始，所以要指定從n – 1 開始，然后取一行記錄
#示例:獲取價格第二高的圖書名稱 mysql> select book_name from book order by book_price desc limit 1,1; +------------+ | book_name | +------------+ | 計算機網絡 | +------------+ 1 row in set (0.00 sec)
登錄后復制
– 修改(update)
MySQL當中使用update關鍵字來對數據進行修改 , 既可以修改單列又可以修改多列.
update 表名 set 列名1 = 值 , 列名2 = 值 ... where 限制條件下修改
登錄后復制
SET子句指定要修改的列和新值。要更新多個列，請使用以逗號分隔的列表。以字面值，表達式或子查詢的形式在每列的賦值中來提供要設置的值。- 第三，使用WHERE子句中的條件指定要更新的行。
WHERE子句是可選的。 如果省略WHERE子句，則UPDATE語句將更新表中的所有行。
示例:
#將書名為'軟件工程'的圖書價格修改為66元 mysql> update book set book_price = 66 where book_name = '軟件工程'; Query OK, 1 row affected (0.05 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select book_price from book where book_name = '軟件工程'; +------------ | book_price | +------------+ | 66.00 | +------------+ 1 row in set (0.00 sec) #將所有的圖書價格修改為原來的二倍 mysql> update book set book_price = 2 * book_price; Query OK, 5 rows affected (0.02 sec) Rows matched: 5 Changed: 5 Warnings: 0 #更新成功 mysql> select book_price from book; +------------+ | book_price | +------------+ | 90.00 | | 90.00 | | 194.00 | | 132.00 | | 178.00 | +------------+ 5 rows in set (0.00 sec)
登錄后復制
– 刪除(delete)
要從表中刪除數據,需要使用delete 語句, delete 語句的 用法如下
delete from 表名 where + 條件
登錄后復制
首先指定需要刪除數據的表,其次使用條件指定where子句中刪除的行記錄, 如果行匹配條件,這些行記錄將會刪除.
WHERE子句是可選的。如果省略WHERE子句，DELETE語句將刪除表中的所有行 , 請注意，一旦刪除數據，它就會永遠消失。因此，在執行DELETE語句之前，應該先備份數據庫，以防萬一要找回刪除過的數據。
示例
#刪除圖書表中圖書單價大于150的圖書記錄 mysql> delete from book where book_price > 150; Query OK, 2 rows affected (0.01 sec) mysql> select book_price from book; +------------+ | book_price | +------------+ | 90.00 | | 90.00 | | 132.00 | +------------+ 3 rows in set (0.00 sec)
登錄后復制
MySQL中delete 語句也可以結合limit語句 和 order by 語句來控制刪除的數量和條件
【
