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

??魯阿豔??發表於2021-01-04

查詢

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

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

          備註:

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

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

  • 外來鍵約束

相關文章