數據庫基礎+增刪查改初階

一。數據庫操作

1.概念：

一個mysql服務器上有很多的表，把有關系的表放在一起就構成了一個數據集合，此時稱為“數據庫”，一個mysql1服務器上可以有多個這樣的數據庫

2.創建數據庫：

create database 數據名；

注：sql關鍵字大小寫都可以

數據庫在創建時不能重復，因此要在創建時加上一個修飾

create database if not exists 數據名

咱們一般在寫數據庫的時候需要中文，但是mysql默認是拉丁文，不支持中文，所以在創建數據庫的時候，要手動指定編碼為中文編碼（GBK或UTF8）因此可以寫成：

create database 數據庫名 charset utf8;

此處的utf8是不完全的，有寫表情是無法識別的，因此如果沒有特殊要求，建議寫成utf8mb4

3.查看數據庫：

列出mysql服務器中都有哪些數據庫

show databases；

注：以sql開頭的語句，都要在寫完一句話的時候加上分號，當然mysql客戶端允許輸入sql的時候換行

4.選中數據庫

use 數據庫名

5.刪除數據庫：

drop database 數據名

刪除不僅僅是刪除了數據庫，而且刪除了數據庫中所有的表和表中的數據

6.補充：

數據庫可分為線上數據庫和線下數據庫，線上數據庫存儲的是用戶真實的信息，線下數據庫是開發測試的一些假的數據

mysql服務器必須保證24小時不間斷運行，如果掛了，就會造成極大的影響，因此有了報警系統，會自動的監視著mysql服務器的運行狀態，一旦mysql服務器停止工作，報警系統會第一時間把消息以電話，短信，郵箱等形式傳遞給相關負責人

二。數據表操作

注：針對數據表中的操作，前提是選中數據庫

1.創建表：

create table 表名（列名 類型，列名 類型...）；

注意：一定是類型在列名的后面

例子：

create table student(id int, name archer(10));

2.查看所有表：

show tables;

3.查看指定表的結構：

desc 表名

4.刪除表：

drop table 表名

補充：有時候刪表比刪庫更嚴重，因為刪庫程序會在第一時間報錯，而刪表，程序不會在第一時間報錯，可能會以這個錯誤的數據運行很長時間

三。增刪改差（CRUD）

1.新增：

（1）法一：直接插入

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

此處的值必須與創建表時的數據類型一一對應

（2）法二：指定列插入

insert into 表名（列名，列名...）values （值,值....);

此處放入的值就要與列名所對應

（3）法三：一次插入多行：

Insert into 表名 values(值，值...) , (值，值...) ....

注意：一次插入多行的速度是要大于一行一行插入的

2.查詢：

（1）全列查詢：

select*from 表名

注意：select*是一個危險的操作，mysql是一個“客戶端—服務器”結構程序，如果當前這個表的數據非常多，就會產生幾個問題：1.在查詢的時候會讀取整個硬盤，會把整個硬盤的IO都給跑滿了，此時在想訪問硬盤就會非常的慢。2.操作網絡，可能把網絡的帶寬跑滿，此時其他網絡訪問服務器就會變得很慢

但是目前初學階段，是可以使用這樣的操作的，當以后進入公司企業工作的生產環境中，這種操作一定要慎重使用

補充：

1.辦公環境：入職公司，公司會分配一個電腦給員工

2.開發環境：有的開發環境是辦公環境，也有的是開發環境，是需要專門的服務器的

3.測試環境：測試工程師針對程序測試的時候，搭配的環境

4.生產環境：一組服務器，這個服務器外面的用戶可以直接訪問的服務器，如果生產環境的服務器出現問題，用戶會得到反饋

第1，2，3都是線下環境，第4個叫做線上環境

（2） 指定列查詢：

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

（3）查詢字段為表達式：

例子：

select name，point+10 from 列名;

這種操作不會修改數據庫當中的原始數據，只會暫時的進行更改

（4）別名：

格式：

select 表達式 as 別名 from 表名；

例子：

select name，english+chinese+math as sum from student;

這里的sum其實就是english+chinese+math的別名。

（5）去重：

distinct去修飾某個列，或多個列

值相同的行只會保留一個

select distinct 列名 from 表名;

這里的列名可以是多個，但是最好根據需求來確定寫什么列，因為如果寫了多個列，那么必須保證這些列的元素都相同才可以去重

（6）查詢進行排序：

規則：1.要針對哪個列進行比較 2. 排序時是升序還是降序

select 列名 from 表名 order by 列名 asc/desc

asc表示升序，desc表示降序

如果不寫desc或者asc則自動默認為升序排序

這里的排序是臨時數據，他不會影響原有數據在數據庫的存儲順序

注意：如果排序的時候遇到了null，則視為null比任何數據都要小，升序時排在最上面，降序時排在最下面

同樣，可以對多個字段進行排序，排序優先級隨書寫順序

例子1：

查詢同學的各門成績，一次按數學降序，英語升序，語文升序的方式顯示

select name, math,english,chinese from exam order by math desc, english,chinese;

例子2:

select*from exam order by math desc, chinese desc;

先以數學成績排序，當遇到數學成績相同的，再按照語文成績進行排序

注意：null參與各種運算的結果還是null

（7）條件查詢：

select 列名 from 表名 where 條件;

在執行條件查詢的時候會遍歷這個表的每一行記錄，把每一行的數據分別代入這個條件中，如果成立就會把結果放入結果集合中，如果不成立就pass

注意：

(1).where 條件可以用于表達式，但是不能使用別名

(2).and的優先級大于or，在同時使用時，要用小括號（）包裹優先執行的部分

無論有幾個列，都可以使用上述方法進查詢

基本查詢：

(1).查詢英語成績小于60分的同學

select name,english from exam where english<60;

(2).查詢英語成績高于語文成績的同學：

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

(3).查詢語數外總分低于200的同學：

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

and與or：

查詢語文大于80分，英語大于100分的同學

select name chinese,english from exam where chinese>80 and english>100;

范圍查詢：

(1)between…and…

查詢語文成績在80到90分的同學的語文成績：

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

(2)in

查詢數據時58或59或60或61的同學的數學成績

select name, math from exam where math in(58,59,60,61);

模糊查詢：LIKE

select name from exam where name LIKE '孫%'；

注：%表示任意多個字符

select name from exam where name LIKE'孫_‘；

注：_表示任意一個字符

（8）分頁查詢：LIMIT

limit可以限制這次查詢最多查詢出幾個結果，適用條件就是有的時候數據非常的多，一次展示出來會影響效率，不方便用戶觀看

select *from 表名 limit 數字;

這個數字就是在限制打印的個數，這段代碼的意思就是只打印這個表的數字之前的數據

select *from 表名 limit 數字1 offset 數字2;

這串代碼表示，只會打印數字1到數字2之間到數據

例子：

select *from exam limit 3 offset 3；

這里只會打印表中的4，5，6行數據

注意：limit一般放在一句話的最后

3.修改：

update 表名 set 列名=值 where 條件;

修改是可以直接修改多個值的

update 表名 set 列名=值，列名=值，......where 條件;

例子：將總成績倒數前三的3位同學的數學成績減30分

update exam set math=math-30 order by chinese+math+English limit 3;

4.刪除：

delete from 表名 where 條件 /order by / limit;

例子：

delete from student where name=’xiao ming‘；

這樣就刪除了student這張表中小明這一行的所有數據

刪表：

delete from 表名;

注意：此處的delete from 和drop table不同。

delete from 是刪除表中的所有數據，但是不刪表

drop table是即刪除表中數據，又把表刪除

四。數據庫約束

1.NULL約束

指某個列不能存放null值

create table student(id int not null, name varchar(10),email int);

2.UNIQUE約束

被修飾的列是不能重復的

create table student(id int unique, name varchar(10),email int);

3.default約束：

規定沒有給列賦值時的默認值

create table student(id int, name varchar(10) default ‘unknown’,email int);

這里就是對name設置了默認值，如果當插入數據的時候，沒有插入這一列，則默認設為unknown

4.primary key主鍵：

確保某列有唯一的標識，簡單點來說就是一行記錄的身份標識

用法其實就是not null和unique的結合

一個表中只能有一個主鍵

create table student(id int primary key, name varchar(10) ,email int);

對應整數主鍵，通常搭配auto_increment來使用，插入數據對應字段不給值時，使用最大值+1

create table student(id int primary key auto_increment, name varchar(10) );

例子：

insert into student values(null,'zhangsan');

假設這張表中沒有插入過數據，則默認插入這條數據中的null為1；

如果再次插入上面一串代碼，則id 就變為了1+1=2，第一個1是目前的最大值，第二個1是指auto_increment的特性（最大值+1）

寫做空，其實是交給數據庫服務器自動分配

自動分配也存在局限性，如果當mysql服務器是沒有問題的，如果是有多個服務器，這是自動分配就不行了

5.foreign key 外鍵:

作用：兩個表之間的關聯關系

例子：

class（classid，name ）100。    TT1114101      TT1964102      TT2103
student（id ，  name，  classid）1    zhangsan   1002    lisi       1013    wangwu。   1024    zhaoliu    200

這里的student表中的zhaoliu的classid是不合法的，因為classid是根據class表中的classid來定義的，如果class表中沒有，則不合法，所以叫這里的class表為父表，student為子表

寫法：

create table student(id int primary key,name archer(10),classId int, foreignn key(classId) references class(classId));           

foreign key要加在最后的部分，foreign key 后的第一個括號要寫被約束的列，后面references 再加上約束別人的表（約束列）

注意：如果被外鍵修飾，并且已經插入數據，則不能直接刪除父表，應先刪除子表再刪除父表

6.擴展：

有一個生活中常見的場景，就是在逛某寶或某多多的時候經常會發現一個商品賣完了，但是在上面外鍵的部分講到了，如果要刪除被外鍵修飾的列的數據時，需要先把所有已經插入的數據全部刪除，才可以刪除被外鍵修飾的列，但是為什么這些購物網站可以成功的把商品下架，并且對已購買的買家依然發貨？

其實這其中就使用了一個很巧妙的方法：邏輯刪除

先給商品表加上一個單獨的列，表示是否在線（不在線，就相當于下架了）

商品表（goodsid，name，price…，isOK）

這里的isOK就是我們新建的單獨的列，如果值為1，表示商品在線，如果值為0，則表示下架，如果商品需要下架只需要把isOK從1改為0

其實電腦上刪除文件也是使用了邏輯刪除的方法，如果只是將文件放入垃圾箱，并且清理回收站是無法做到完全刪除的，硬盤中的數據完全刪除是需要時間的，需要后續有文件把這塊標價無效的空間重復利用，才會真正的消失，當然有一種非常快的方法可以把硬盤上的數據完全刪除，那就是使用物理刪除，就是通過把硬盤砸了等物理方法銷毀數據