sql運算元據庫（2）--->DQL、資料庫備份和還原

??魯阿豔??發表於2021-01-04
SQL資料庫

查詢

  • 查詢表中的所有的行和列的資料

    ​ 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);

          備註：

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

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

  • 外來鍵約束

相關文章