sql操作資料庫（2）->DQL、資料庫備份和還原

sql操作資料庫（2）—>DQL、資料庫備份和還原

2021 年 1 月 4 日
筆記

查詢

查詢表中的所有的行和列的數據

select * from 表名;

select * from student;
查詢指定列的數據：如果有多個列，中間用逗號隔開。

select 列名1，列名2，列名3，…… from 表名

select age,name from student;
查詢時指定列的別名
- 使用關鍵字 as
- 使用別名的好處：顯示的時候指定新的名字，並不修改表的原有結構。
- 對指定列進行別名操作： select 列名1 as 新名字，列名2 as 新名字，….. from 表名;
- 對錶和列同時進行別名操作： select 列名1 as 新名字，列名2 as 新名字，….. from 表名 as 新表名;
  
  如：對學生表進行操作：查詢學生表的姓名和年齡;
```
select 
    s.age as 年齡,
    s.name as 姓名
from 
     student as s;     
-- as關鍵字可以省略不寫。     
```
去除重複查詢
- 使用關鍵字 distinct 去掉重複的記錄
- 語法格式： select distinct 欄位名 from 表名;
  
  例如：
```
-- 查詢本班同學都來自於哪些地方?
select distinct address from student;
```
查詢的結果值可以參與數學運算
- 查詢出來的列和其他列可以進行數學運算
- 語法： select 列名1+、-、/、* 列名2,….. from 表名;
- 注意：能夠參與數學運算的是前提是列的數據類型是數值型. 整數和小數
  
  例如：
```
-- 查詢本班同學的數學成績和歷史成績的和
select s.math+s.history as 綜合  from student as s;
-- as 可以省略
```

條件查詢

查詢的時候，有時候只想獲取符合條件的結果值，並不是獲取表中的所有記錄
語法： select 欄位列表 from 表名 where 條件表達式;
結果值：符合條件的記錄就會被返回，如果條件不符合就不返回(過濾掉)

運算符：

>、 < 、 >= 、<= 、!=、=、<>(不等於)，沒有”==”	在sql中，不等於有兩種表示方式，!= 、<>
between….. and	表示的是一個區間範圍如 between A and B [A,B]
in(具體的取值)	裡面放的是一個個數值，如果有多個，中間用逗號隔開
like	模糊查詢，根據關鍵字查詢，一般搭配”%”
is null	查詢某一列為null的值，注意在sql 不能這樣表達「欄位名= null」
is not null	查詢某一列不為null的值

操作：

-- 比價運算符的操作
-- 查詢數學成績50分以上的同學
select * from student as s where s.math <> 100;
-- BETWEEN AND
-- 查詢數學成績在70-100分之間的
select * from student as s where s.math BETWEEN 70 and 100;
-- in(某幾個散列值)
-- 查詢歷史成績為80分和100分的同學
select * from student as s where s.history in(80,100);
-- 模糊查詢  like 搭配使用 %
-- 查詢學生地址中含有"陽"的同學
select * from student as s where s.address like  '%陽%';
-- 查詢本班同學中含有小的同學資訊
SELECT * from student as s where s.username like '%劉%';
-- 查詢生日為空的同學有哪些
select * from student as s where s.birthday is not null;

備註：在模糊查詢時，通配符有兩種表示方式：% _

% 匹配任意多個字元
_ 匹配單個字元

兩者都需要注意書寫的位置：

一般我們使用【%】作為模糊查詢的通配符

邏輯運算符與、或、非

與：&& —> and
或： || —> or
非： !

在sql建議使用單詞來表示邏輯運算 and or

操作：


-- 邏輯運算符  與或非

-- 查詢歷史成績為80分和100分的同學  or
select * from student as s where s.history = 80 or s.history = 100;

-- 查詢本班同學成績在70分以上的    and
SELECT * from student as s where  s.math > 70 and s.history > 70;

-- 取非  ！
SELECT * from student as s where  ! (s.math > 70 and s.history > 70);

排序
- 單列排序
  
  通過 order by語句來實現排序，只是將查詢出來的結果值進行排序，並不影響查詢的結果，不進行條件過濾，影響的是顯示的方式(從大到小還是從小到大)
  
  升序：asc 默認就是升序
  
  降序：desc
  
  單列排序：根據表中的某個欄位、某列進行排序
  
  語法： select 欄位列表 from 表名 where 條件 order by 欄位名 asc、desc;
  
  如：
```
--對查詢出來的同學資訊根據歷史成績降序排序
select * from student as s order by s.history desc; 
```
- 組合排序
  - 同時對表中的多個欄位進行排序，如果前面的欄位值相同，再根據後面的欄位再次排序
  - 語法： select 欄位列表 from 表名 where 條件 order by 欄位1 desc|asc，欄位名2 desc|asc;
  - 操作：
```
-- 根據數學成績進行升序排序，當數學成績相同再根據歷史成績降序排序
select * from student as s order by s.math asc,s.history desc; 
```
聚合函數
- 常用的聚合函數
- 求一個最大值 max
- 求一個最小值 min
- 求一下平均數 avg
- 求一下總和 sum
- 統計查詢結果的記錄數 count
使用count統計記錄數的時候，如果count中填寫的是表中的某個欄位，如果某條件中該欄位為null，則該記錄會被過濾掉。

ifnull(表達式1，表達式2)，如果前面的欄位為null，後面的表達式2可以替換掉前面的欄位為null的值。可以統計帶null的值
分組查詢 group by
```
select
   欄位列表
from
   表名列表
where
    分組之前的條件
group by
   分組欄位
having 
    分組之後的條件
limit
   分頁限定條件
```
概念：使用group by 對查詢的結果資訊進行分組，相同的數據分成一組。

語法： select 欄位列表 from 表名 group by 分組欄位 having 分組之後的條件

例如：

將查詢出來的結果值內容，再按照性別進行分組，分組2組。

-- 將查詢出來的結果值內容，再按照性別進行分組，分組2組。文綜和是多少
-- 年齡大於18歲的
/*
	1.我想要什麼樣的結果值
    2. sql中需要有哪些條件
    3. 在sql都需要涉及到幾張表
 
*/
SELECT
    s.gender,sum(s.history+s.geo+s.political) as 文綜和   -- 查詢出來需要展示的結果值
from
    student as s
WHERE 
     s.age > 18
GROUP BY
    s.gender

-- 統計成年人男女各多少個？
SELECT
   s.gender, count(s.gender) as 個數
FROM
   student as s
WHERE 
   s.age >=18
GROUP BY
   s.gender

having和where的區別

where語句：將查詢結果分組前的符合條件返回數據，不符合條件的過濾掉，即先過濾再分組。where後面不能使用聚合函數。
having語句：在分組之後過濾數據，即先分組再過濾。having後面可以使用聚合函數
分頁查詢
- 使用關鍵字 limit,它是mysql中的方言操作
- 作用：對查詢的結果值進行分頁展示，每次顯示多少條記錄
- 語法： select 欄位列表 from 表名 where 條件 group by 分組欄位 having 分組之後條件 order by 升降序 limit 分頁限定條件
- 分頁限定條件 —–> 起始值展示的記錄數 limit offset，length
- 操作：
```
-- 查詢學生表中的數據，從第一條開始，每頁顯示2條，第一次只能看到前兩條
SELECT
   s.username as 姓名,s.age as 年齡,s.gender as 性別
FROM
   student as s
LIMIT
   0,2

-- 查詢學生表中的數據，從第4條，展示3條
SELECT
   *
FROM
  student
LIMIT
  3,3
-- 分頁  轉換分頁查詢的起始值和頁碼數，每頁展示的個數確定的
-- 0,15  第一頁
-- 15,15 第二頁
-- 30,15 第三頁

--  查詢第28頁的數據 15條記錄
-- 頁碼數已知的 pageNum  起始值是未知的 beginNum ，記錄數為num
-- totalNum 總記錄數  根據記錄數進行分頁
--  beginNum = (pageNum - 1) * num
LIMIT 
   (pageNum - 1) * num;   
-- 如果你查詢的記錄值是從第一條開始的，這個0可以省略
SELECT
  * 
FROM
  student as s
LIMIT
  5;
-- 如果你查詢的是最後幾條，有幾條顯示幾條，不會報分頁錯誤
-- 查詢從第三條開始，查詢5條
SELECT
  * 
FROM
  student as s
LIMIT
  2,5;
```
  備註:
  
  如果查詢從第一條開始，起始值可以省略，如果查詢最後幾條，有幾條顯示幾條，不會報分頁錯誤。
資料庫備份和還原
- 圖形化介面工具 Navicat
  1. 備份
    
    選中資料庫右鍵 —-> 轉儲sql文件—-> 結構和數據 —>指定磁碟中sql文件存儲的位置。
  2. 還原
    
    先通過Navicat創建一個資料庫(需要和sql文件中的資料庫保持同名) —>右鍵運行sql文件—–>瀏覽磁碟中存儲的sql文件，點擊運行，最後——>刷新。
- dos指令操作
  1. 備份
    
    在不登錄的情況下使用 mysqldump -u登錄名 -p登錄密碼備份資料庫名 > 存儲磁碟的位置(絕對路徑)
  2. 還原
登錄mysql，首先創建同名的資料庫，然後使用當前創建的資料庫 use 創建資料庫名

然後 source 磁碟中sql文件存儲的位置

備份：

還原：

資料庫表的約束

概述

對表中的數據進行限定，保證數據的有效性、完整性和正確性。一個表一旦添加了約束，那麼不正確的數據將無法添加進來，所以一般約束在創建表設定欄位的時候添加上
主鍵約束
- 主鍵：一般是用來唯一標識資料庫表中的某一條記錄，不能為空
- 通常情況下，一般會給表添加一個id欄位，用作唯一標識，設置為主鍵，主鍵一般是提供給資料庫操作使用的(查詢，修改，刪除等等)，主鍵不能重複，也不能為空。
  
  比如：一個人的身份證號、一個人手機號，一個學生的學號，一個員工工號
- 語法：使用關鍵字 primary key
- 操作：
```
-- 查詢
SELECT * FROM user;
-- 非法數據插入  唯一 不重複
INSERT INTO user values(7,'0008','123123','小花','12312312313','女',19);

-- 非法數據插入   null值插入  非空
INSERT INTO user values(null,'0008','123123','小花','12312312313','女',19);
-- 刪除主鍵  sql語句
alter table user drop PRIMARY key;
-- 添加主鍵
alter table user add PRIMARY KEY(id);
```
- 主鍵自增
  
  如果希望在添加表記錄時，不設定表中主鍵值，系統會自動給主鍵添加自增值
  - 語法： auto_increment 表示自動自增
```
-- 非法數據插入   null值插入
INSERT INTO user values(null,'0010','123123','小周','12312312313','男',25);
```
  - 修改自增的起始值為100
    
    alter table user auto_increment = 起始值;
唯一約束
- 概念：欄位值唯一，不允許重複
- 關鍵字： unique
- 語法：欄位名欄位數據類型 unique
- 操作：
```
-- 插入數據
insert into role values(null,'CEO');
insert into role values(null,'manager');
insert into role values(null,'CTO');
-- 查詢角色表
SELECT * from role;
insert into role values(null,null);
```
  備註：如果插入是null，表明是沒有數據，不存在數據重複問題，可以重複插入。

非空約束

概念：記錄中的某個欄位不能為null

語法：欄位名欄位數據類型 not null

-- 創建一張部門表
create table department (
    id int PRIMARY KEY auto_increment, -- id 主鍵自增
    departmentname VARCHAR(10) not null -- 部門不能為空
   
)  

insert into department values(null,'總裁辦');
insert into department values(null,'財務部');
insert into department values(null,'人事部');
insert into department values(null,'研發部');
insert into department values(null,'銷售部');

-- 查詢該表數據
SELECT * from department;

insert into department values(null,null);

默認值：

概念：當沒有給欄位賦值，系統會賦上一個指定的默認值
語法：欄位名欄位數據類型 default 默認值

操作:

-- 創建一張表 employee 員工表
create table employee (
    id int PRIMARY key auto_increment,
    username VARCHAR(10) not null,
    gender VARCHAR(1) DEFAULT '男',
    age int 
)
insert into employee(id,username,age) values(null,'小孫',20);
-- 查詢員工表
SELECT * from employee;
insert into employee(id,username,gender,age) values(null,'小麗','女',20);

備註：

如果給表中的某個欄位既添加了非空約束又添加了唯一約束，那麼該欄位是不是主鍵呢?

一般情況下，一張表中只能有一個主鍵。

外鍵約束

sql操作資料庫（2）—>DQL、資料庫備份和還原

查詢

資料庫備份和還原

資料庫表的約束

VirMach 便宜 VPS

QNews

sql操作資料庫（2）—>DQL、資料庫備份和還原

查詢

資料庫備份和還原

資料庫表的約束

分享此文：

Related Posts

java中的垃圾處理機制

Python在Finance上的應用3：處理股票數據基礎

Spark學習進度-Spark環境搭建&Spark shell

AMD RDNA3顯示卡架構大變：Zen3這一套全學會了

VirMach 便宜 VPS

QNews

熱門搜尋