MySQL基礎(chǔ)知識(二)

• 一個數(shù)據(jù)庫服務(wù)器上，可以把很多有業(yè)務(wù)聯(lián)系上的表放到一起，構(gòu)成了一個邏輯上的“數(shù)據(jù)集合”，稱為數(shù)據(jù)庫。

• 數(shù)據(jù)庫操作:

  • 創(chuàng)建數(shù)據(jù)庫

    create database 數(shù)據(jù)庫名字 charset utf8;
    

  • 查看數(shù)據(jù)庫

    show databases;
    

  • 選中指定的數(shù)據(jù)庫

    use 數(shù)據(jù)庫名;
    

  • 刪除數(shù)據(jù)庫

    drop database 數(shù)據(jù)庫名;
    

那么下面我們繼續(xù)來聊，MySQL的基礎(chǔ)知識。

01 表操作

1.1 創(chuàng)建表

create table 表名(列名 類型，列名 類型...)；

聊聊類型：

整數(shù)： int long
小數(shù)   double decimal
字符串  varchar
時間日期 datetime

MySQL其實也提供了無符號版本的數(shù)據(jù)庫類型(unsigned)，但是在官方文檔上明確寫了不建議使用，在未來也會刪除。

因為兩個無符號類型相減，可能會產(chǎn)生溢出的情況。

1.2 查看所有表

show tables；

這里要注意，如果直接show tables;,是會報錯的。

正確的操作應(yīng)該是：

這里由于沒有在Test1中存任何東西,所以是empty。

1.3 查看指定表的結(jié)構(gòu)

desc 表名;

1.4 刪除表

drop table 表名；

刪除表的同時，也會把表里的數(shù)據(jù)一起刪掉。

刪除表切記一定要慎重！其嚴重性可能比刪除數(shù)據(jù)庫還要嚴重。

假設(shè)數(shù)據(jù)庫有100個表，刪庫，是把100個都刪了，刪表，只是刪掉一個。
如果是刪庫操作，你的程序一定是第一時間就報錯了趕緊進行處理.（把之前備份的數(shù)據(jù)恢復(fù)回去）
如果是刪表操作，你的程序就不一定第一時間報錯了發(fā)現(xiàn)時間就會更晚.
程序就以錯誤的狀態(tài)在生產(chǎn)環(huán)境又運行了很長時間（都是帶傷運行的）=>以錯誤的數(shù)據(jù)，運行過程。

練習

• 有一個商店的數(shù)據(jù)，記錄客戶及購物情況，有以下三個表組成：
  • 商品goods(商品編號goods_id,商品名goods_name,單價unitprice,商品類別category,供應(yīng)商provider)
  • 客戶customer(客戶號customer_id,姓名name,住址address,郵箱email,性別sex,身份證card_id)
  • 購買purchase(購買訂單號order_id,客戶號customer_id,商品號goods_id,購買數(shù)量nums)

最后展示效果：

mysql> create database
    -> if not exists
    -> Test
    -> charset utf8mb4;
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> use Test;
Database changed

mysql> create table
    -> if not exists
    -> goods
    -> (
    -> goods_id int comment '商品編號',
    -> goodes_name varchar(32) comment '商品名稱',
    -> unitprice int comment '單價，單位分',
    -> category varchar(12) comment '商品分類',
    -> provider varchar(64) comment '供應(yīng)商名稱'
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> create table if not exists customer
    -> (
    -> customer_id int comment '客戶編號',
    -> name varchar(32) comment '客戶姓名',
    -> address varchar(256) comment '客戶地址',
    -> sex bit comment '性別',
    -> card_id varchar(18) comment '身份證'
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> create table if not exists purchase
    -> (
    -> oeder_id int comment '訂單號',
    -> customer_id int comment '客戶編號',
    -> goods_id int comment '商品編號',
    -> nums int comment '購買數(shù)量'
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| customer       |
| goods          |
| purchase       |
+----------------+
3 rows in set (0.00 sec)

02 CURD

CURD是數(shù)據(jù)庫非?；A(chǔ)的部分，也是后端開發(fā)日常工作中，最主要做的一項工作。

CRUD 即增加(Create)、查詢(Retrieve)、更新(Update)、刪除(Delete)四個單詞的首字母縮寫。

2.1 新增

insert into 表名 values(值，值...);
-- 此處的值，要與列相匹配（列的個數(shù)和類型）

mysql> create table student (id int, name varchar(20));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into student values(1,'zhangsan');
Query OK, 1 row affected (0.01 sec)

• 實際上插入中文也是ok的。

2.1.1 指定列插入

insert into 表名 (列名,列名...) values (值,值)

• 若此處只插入name，id 這一列就會被填充為默認值。(此處的默認值就是null)
• 其實也可以一次插入多行記錄

insert into 表名 values (值，值...),(值，值...)...;

這里的提示，就是反饋效果，客戶端給服務(wù)器發(fā)起插入請求，服務(wù)器要返回這次插入是否成功。

一次插入多行記錄，相比于一次插入一行，分多次插入，要快不少

MySQL是一個客戶端服務(wù)器結(jié)構(gòu)的程序。

我們可以通過

select * from student;

來查看我們插入的數(shù)據(jù)。

2.1.2 datetime類型插入

datetime插入，可以是用一個固定格式的字符串，來表示時間日期。

我們可以查看效果

如果我們想填寫的時間日期，就是當前時刻，sql其實提供了一個現(xiàn)成的函數(shù)，now().

insert into student values(2, '李四', now());

2.2 查詢

sql中的增刪改實際上都非常簡單，但是查詢就可以玩出各種花樣。

下面就展開一個例子來詳細聊聊~

• 先構(gòu)造一個表

-- 創(chuàng)建考試成績表
DROP TABLE IF EXISTS exam_result;
CREATE TABLE exam_result (
 id INT,
 name VARCHAR(20),
 chinese DECIMAL(3,1),
 math DECIMAL(3,1),
 english DECIMAL(3,1)
);
-- 插入測試數(shù)據(jù)
INSERT INTO exam_result (id,name, chinese, math, english) VALUES
 (1,'唐三藏', 67, 98, 56),
 (2,'孫悟空', 87.5, 78, 77),
 (3,'豬悟能', 88, 98.5, 90),
 (4,'曹孟德', 82, 84, 67),
 (5,'劉玄德', 55.5, 85, 45),
 (6,'孫權(quán)', 70, 73, 78.5),
 (7,'宋公明', 75, 65, 30);

2.2.1 全列查詢

select * from 表名;
--  * 表示通配符，可以指代所有的列

把表中的所有行和列都展示出來。

select * from exam_result;

以上的數(shù)據(jù)是查詢出來之后，服務(wù)器通過網(wǎng)絡(luò)把這些數(shù)據(jù)返回給客戶端的，并且在客戶端以表格形式打印出來。

??????注意：select * 其實是一個危險操作??????

MySQL是一個“客戶端-服務(wù)器”結(jié)構(gòu)的程序，客戶端這里進行的操作，就都會通過請求發(fā)送給服務(wù)器，服務(wù)器查詢的結(jié)果也就會通過響應(yīng)返回給客戶端

如果數(shù)據(jù)庫當前這個表中的數(shù)據(jù)特別多，就可能會產(chǎn)生問題！

1. 讀取硬盤。 把硬盤的 IO 跑滿了，此時程序其他部分想訪問硬盤，就會非常慢
2. 操作網(wǎng)絡(luò)。 也可能把網(wǎng)卡的帶寬跑滿，此時其他客戶端想通過網(wǎng)絡(luò)訪問服務(wù)器，就會非常慢

就好像高速堵車~

2.2.2 指定列查詢

一個表的列數(shù)，可能是非常多的，某個場景下的操作，只需要關(guān)注其中的幾個列。

select 列名, 列名... from 表名;

select name, chinese from exam_result;

2.2.3 查詢字段為表達式

一邊查詢,一邊進行計算…在查詢的時候，寫作由列名構(gòu)成的表達式，把這一列中的所有行都帶入到表達式中，參與運算~

下面舉個例子：

想查詢所有人的語文成績都 -10 分。

select name, chinese - 10 from exam_result;

注意：這里的操作不會修改數(shù)據(jù)庫服務(wù)器上的原始數(shù)據(jù)，只是在最終響應(yīng)里的“臨時結(jié)果”中做了計算~

再次強調(diào)，MySQL是客戶端-服務(wù)器結(jié)構(gòu)的數(shù)據(jù)，進行查詢時，是將服務(wù)器這里的數(shù)據(jù)讀出來，返回給客戶端，并且以臨時表的形式進行展示~

不信的話，我們可以將現(xiàn)在展示一下：

再舉一個例子：計算所有人的總成績

select name, chinese + math + english from exam_result;

這也表明，SQL在查詢的時候，可以進行一些簡單的統(tǒng)計操作

表達式查詢，是 列 和 列 之間的運算，把每一行都帶入到這樣的運算中，而不是 行和行 之間的。

2.2.4 別名查詢

查詢的時候給 列 / 表達式 指定別名。（給表也能指定別名）

select 表達式 as 別名 from 表名；
-- 別名通俗理解就是外號
-- as可以省略（不建議）

查詢結(jié)果的臨時表中，列名就是剛才的別名了。

2.2.5 去重

distinct修飾某個列/多個列

值相同的行，只會保留一個

那么此時我們可以進行以下操作：

select distinct math from exam_result;

他就會篩選掉重復(fù)的數(shù)據(jù)~

但是我們這樣寫的話

select distinct name, math from exam_result;

因為98的分數(shù)對應(yīng)的name不一致，就不會去重咯。

2.2.6 查詢時排序

把行進行排序~

明確排序規(guī)則~：

• 針對哪個列作為比較規(guī)則
• 排序時是升序還是降序

select 列名 from 表名 order by 列名 asc/desc;
-- order by 列名 ：指定某個列進行排序
-- asc ：升序
-- desc： 降序
-- 默認asc

再次強調(diào)：

mysql是一個客戶端服務(wù)器結(jié)構(gòu)的程序，把請求發(fā)給服務(wù)器之后，服務(wù)器進行查詢數(shù)據(jù)，并且把查詢到的結(jié)果進行排序之后，再組織成響應(yīng)數(shù)據(jù)返回給客戶端.
排序仍然是針對臨時數(shù)據(jù)來展開的，此處的排序，不影響原有數(shù)據(jù)在mysql服務(wù)器上存儲的順序~

當前只是在我們自己的機器上進行了一些簡單的操作看起來順序好像沒變，但是要是進行一些復(fù)雜的操作，那就不一定了~

如果不加order by代碼就不應(yīng)該依賴以上的順序~~尤其是在代碼中，就不能依賴上述的順序來展開一些邏輯，因為數(shù)據(jù)庫是沒有承諾這個數(shù)據(jù)是有順序的~

order by指定的列，如果你select 的時候沒有把這一列查出來，也是不影響排序的~

order by還可以根據(jù)表達式進行排序

order by 指定多個列來排序~order by后面可以寫多個列 使用,來分隔開

2.2.7 ??條件查詢 WHERE

會指定具體的條件，按照條件針對數(shù)據(jù)進行篩選

select 列名 from 表名 where 條件；
-- 這里的條件：遍歷這個表的每一行記錄，把每一行的數(shù)據(jù)分別帶入到條件中
--           如果條件成立，這個記錄就會被放入結(jié)果集合中；若不成立你就會被pass 

• 比較運算符：

注意：

1. sql中是沒有==

2. 模糊匹配：通過一些特殊符號，描述出規(guī)則/特征后續(xù)哪些值，符合上述特征

   • 舉個例子就是你要找對象，那么你的要求是

     1. 可愛
     2. 身材好??????

     此時雖然你還不知道你要追哪個，但是你已經(jīng)有一定標準去篩選，那么這也就是模糊匹配~

• 邏輯運算符（與或非）

1. WHERE條件可以使用表達式，但不能使用別名。（下面會提到）

2. AND的優(yōu)先級高于OR，在同時使用時，需要使用小括號()包裹優(yōu)先執(zhí)行的部分

相關(guān)案例：

• 查詢英語成績不及格的同學及其成績

select name, english from exam_result where english < 60;

無論有幾個列，都可以使用上述運算符來描述條件

• 查詢語文成績優(yōu)于英語成績的同學

select name, chinese, english from exam_result where chinese > english;

條件查詢搭配表達式

• 查詢總分低于200的同學

select name, chinese + math + english from exam_result where chinese + math + english < 200;

這里關(guān)于別名的使用見下圖：

這里我們注意理解，select條件查詢執(zhí)行的順序

1. 遍歷表中的每個記錄

2. 把當前記錄的值，帶入條件，根據(jù)條件進行篩選

3. 如果這個記錄條件成立，就要保留，進行列上表達式的計算

   • 這里也就說明where是第二步執(zhí)行的，而別名是第三步定義，也就是說執(zhí)行where的時候，total還處在未定義的狀態(tài)~

   • 但是在理論上說，別名也是可以做到的~

     在實現(xiàn)sql解析引擎的時候，是完全可以做到的，把這里的別名預(yù)先定義好，然后再執(zhí)行 1 2 3，保證執(zhí)行到where的時候也能訪問到別名~

   • 但是！mysql當前并沒有這樣執(zhí)行??

4. 如果有order by，會在所有的行都被獲取到之后（表達式也算完了）

   再針對所有的結(jié)果進行排序~~

• 查詢語文，英語成績均大于80的同學

select name, chinese, english from exam_result where chinese > 80 and english > 80;

• 查詢語文成績大于80，或者英語成績大于80的同學

select name, chinese, english from exam_result where chinese > 80 or english > 80;

• 觀察and和or的優(yōu)先級

由此可見：sql中and的運算符優(yōu)先級更高~

• 查詢語文成績在[80，90]的同學及其成績

select name, chinese from exam_result where chinese between 80 and 90;

select name, chinese from exam_result where chinese >= 80 and chinese <= 90;

• 查詢數(shù)學成績是58或者59或者98或者99的同學及其成績

select name, math from exam_result where math in (58,59,98,99);

select name, math from exam_result where math = 58 or math = 58 or math = 98 or math = 99;

模糊查詢：LIKE

通配符，就是一些特殊的字符，能夠表示特定的含義~

類似于撲克牌中的 癩子 _{也就是特殊牌，可以用來代替任意的點數(shù)或者花色}

% ： 代指任意個任意字符
_ ： 代指一個任意字符

• 查詢姓孫的同學的成績

mysql> select * from exam_result where name like '孫%';
+------+--------+---------+------+---------+
| id   | name   | chinese | math | english |
+------+--------+---------+------+---------+
|    2 | 孫悟空 |    87.5 | 78.0 |    77.0 |
|    6 | 孫權(quán)   |    70.0 | 73.0 |    78.5 |
+------+--------+---------+------+---------+
2 rows in set (0.01 sec)
--------------------------------------------------------------
mysql> select * from exam_result where name like '孫_';
+------+------+---------+------+---------+
| id   | name | chinese | math | english |
+------+------+---------+------+---------+
|    6 | 孫權(quán) |    70.0 | 73.0 |    78.5 |
+------+------+---------+------+---------+
1 row in set (0.00 sec)
---------------------------------------------------------------
mysql> select * from exam_result where name like '孫__';
+------+--------+---------+------+---------+
| id   | name   | chinese | math | english |
+------+--------+---------+------+---------+
|    2 | 孫悟空 |    87.5 | 78.0 |    77.0 |
+------+--------+---------+------+---------+
1 row in set (0.00 sec)

• 孫%：查詢以孫開頭的內(nèi)容

• %孫：查詢以孫結(jié)尾的內(nèi)容

• %孫%：查詢包含孫的

NULL查詢

select * from exam_result where chinese <=> null;
-- 可以針對兩個列比較

select * from exam_result where chinese is null;
-- 只能針對一個列

2.2.8 分頁查詢 LIMIT

select 列名 from 表名 limit N offset M；
N 表示這次查詢最多查出幾個記錄
M 表示這次查詢的這個N記錄，是從第幾個下標開始算

使用select * 這種方式查詢是比較危險的~

需要保證一次查詢不要查出來的東西太多。那么LIMIT就是個好東西~

limit，可以限制查詢這次查詢最多可以查出來多少結(jié)果~

因為在有些時候，數(shù)據(jù)會非常多，一次性顯示出來的話，會影響效率，也不方便用戶查看，那么用limit就可以很好解決這個問題~

select * from exam_result limit 3;

 select * from exam_result limit 3 offset 3;
 -- limit 3  ：這次查詢，查出幾個記錄
 -- offset 3 ：偏移量，也就是一個從0開始的下標

2.3 修改

update 表名 set 列名 = 值 where 條件；
-- where 條件：限制這次操作具體要修改哪些數(shù)據(jù)

講講set這個詞，它在計算機中有兩種典型的含義~

1. 設(shè)置 getter/setter
2. 集合 TreeSet/HashSet

所以說，在計算機中，一個術(shù)語往往有多種含義，必須必須結(jié)合上下文來理解這個含義是什么意思~

• 將孫悟空的數(shù)學成績變更成80分

update exam_result set math = 80 where name = '孫悟空';

實際上使用update，可以一次性修改多個列

set 列 = 值，列 = 值....;

• 將曹孟德的數(shù)學 -> 60分，語文 -> 70分

update exam_result set math = 60, chinese = 70 where name = '曹孟德';

• 將總成績?yōu)榈箶?shù)前三的數(shù)學成績+30分

-- 首先先看看倒數(shù)前三是哪幾位
mysql> select * from exam_result order by chinese + math + english limit 3;

+------+--------+---------+------+---------+
| id   | name   | chinese | math | english |
+------+--------+---------+------+---------+
|    7 | 宋公明 |    75.0 | 65.0 |    30.0 |
|    5 | 劉玄德 |    55.5 | 85.0 |    45.0 |
|    4 | 曹孟德 |    70.0 | 60.0 |    67.0 |
+------+--------+---------+------+---------+
3 rows in set (0.01 sec)

-- 這里注意觀察，id 5 劉玄德的數(shù)學成績?nèi)羰?30 = 115.0，就會不符合前面說的decimal（3，1）  不信我們可以看看以下操作

mysql> update exam_result set math = math + 30 order by chinese + math + english limit 3;

ERROR 1264 (22003): Out of range value for column 'math' at row 2

-- Out of range value for column 是吧~這里說超出范圍咯
-- 其次還有注意點就是 math = math + 30， 是不可以寫成 math += 30
-- 那么我們進行math-30來看看效果吧
mysql> update exam_result set math = math - 30 order by chinese + math + english limit 3; 
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from exam_result order by chinese + math + english limit 3;
+------+--------+---------+------+---------+
| id   | name   | chinese | math | english |
+------+--------+---------+------+---------+
|    7 | 宋公明 |    75.0 | 35.0 |    30.0 |
|    5 | 劉玄德 |    55.5 | 55.0 |    45.0 |
|    4 | 曹孟德 |    70.0 | 30.0 |    67.0 |
+------+--------+---------+------+---------+
3 rows in set (0.00 sec)

• 將所有同學的語文成績更改為原來的兩倍

這里的將所有，也就是update后面不寫任何條件，就是針對所有行進行修改

mysql> update exam_result set chinese = chinese * 2;
ERROR 1264 (22003): Out of range value for column 'chinese' at row 1
-- 哈哈這里又超范圍了，那我們換成/2

mysql> update exam_result set chinese = chinese / 2;
Query OK, 7 rows affected, 2 warnings (0.01 sec)
Rows matched: 7  Changed: 7  Warnings: 2

-- 這里我們可以看看warnings是什么

mysql> show warnings;
+-------+------+----------------------------------------------+
| Level | Code | Message                                      |
+-------+------+----------------------------------------------+
| Note  | 1265 | Data truncated for column 'chinese' at row 2 |
| Note  | 1265 | Data truncated for column 'chinese' at row 5 |
+-------+------+----------------------------------------------+

-- truncated 截斷 這里說的是第 2 5 行的數(shù)據(jù)發(fā)生截斷
-- 2 孫悟空 43.8 因為孫悟空原成績是 87.5 ，/2之后是 43.75，但是43.75會超出decimal（3，1）這樣的范圍，于是就進行了截斷~

mysql> select * from exam_result;
+------+--------+---------+------+---------+
| id   | name   | chinese | math | english |
+------+--------+---------+------+---------+
|    1 | 唐三藏 |    33.5 | 98.0 |    56.0 |
|    2 | 孫悟空 |    43.8 | 80.0 |    77.0 |
|    3 | 豬悟能 |    44.0 | 98.5 |    90.0 |
|    4 | 曹孟德 |    35.0 | 30.0 |    67.0 |
|    5 | 劉玄德 |    27.8 | 55.0 |    45.0 |
|    6 | 孫權(quán)   |    35.0 | 73.0 |    78.5 |
|    7 | 宋公明 |    37.5 | 35.0 |    30.0 |
+------+--------+---------+------+---------+
7 rows in set (0.00 sec)

2.4 刪除

delete from 表名 where 條件 / order by / limit；
-- where 條件 / order by / limit 會把符合條件的行，從表中刪除

• 刪除孫悟空的成績

delete from exam_result where name = '孫悟空';

• 刪除整張表數(shù)據(jù)

delete from exam_result;

這和drop table還不太一樣，drop table是刪除了表，也刪除了表里的記錄

而delete只是刪除了表里的記錄，但是表還在，只不過表變成一張空表~

所以我們要知道，delete和update都是很危險的操作！

一旦delete,的條件沒有設(shè)置好，就可能把不該刪除的給刪掉了~

這兩者修改 / 刪除持久生效的，都會影響到 數(shù)據(jù)庫服務(wù)器 硬盤中的數(shù)據(jù)~

sql中步步都是很危險的啊????????

那么~數(shù)據(jù)庫既然這么危險，該怎么備份呢？

實際上有很多種方式：

1. 數(shù)據(jù)庫最終都是存儲在硬盤上，以文件的形式體現(xiàn)（文件都是二進制），可以將這里的文件cv到別的機器上面（全量備份）。

2. mysqldump工具，mysql自帶的一個程序，會把你mysql中的數(shù)據(jù)導(dǎo)出成一系列insert語句~然后再將這些insert語句，放到另一個mysql中執(zhí)行

3. mysql還有一個binlog功能（將mysql中的各種操作，都通過日志記錄下來）

   借助binlog（讓另一個數(shù)據(jù)庫按照binlog的內(nèi)容執(zhí)行就可以得到一樣的數(shù)據(jù)）

   （增量備份/實時備份）

   ---

   至此，就將mysql的基礎(chǔ)操作知識介紹的差不多了，接下來還會持續(xù)更新mysql，敬請期待~文章來源地址http://www.zghlxwxcb.cn/news/detail-689383.html

到了這里，關(guān)于【MySQL】基礎(chǔ)知識（二）的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！