MySQL數(shù)據(jù)庫(kù)學(xué)習(xí)筆記整理

數(shù)據(jù)庫(kù)對(duì)于后端開發(fā)來說是必不可少所要用到的枣购，而MySQL數(shù)據(jù)庫(kù)是其中的主流之一泼差，在中小型公司中使用的較為廣泛躯概，作為關(guān)系型數(shù)據(jù)庫(kù)可以當(dāng)成入門級(jí)別進(jìn)行學(xué)習(xí)標(biāo)準(zhǔn)SQL語(yǔ)法年栓，基礎(chǔ)功能學(xué)習(xí)還是相對(duì)簡(jiǎn)單流纹。當(dāng)然糜烹，對(duì)于移動(dòng)端開發(fā)來說，可能MySQL數(shù)據(jù)庫(kù)的學(xué)習(xí)并不是必備的漱凝。但是Android同樣有內(nèi)置SQLite輕量級(jí)數(shù)據(jù)庫(kù)疮蹦，同樣是標(biāo)準(zhǔn)SQL語(yǔ)法，Android的同學(xué)掌握一下也是有好處的茸炒。

數(shù)據(jù)庫(kù)基本概念

數(shù)據(jù)庫(kù)基本概念.png

SQL

SQL概念

SQL的概念.png

SQL語(yǔ)法

SQL通用語(yǔ)法.png

SQL分類

SQL分類.png

數(shù)據(jù)庫(kù)相關(guān)操作

數(shù)據(jù)庫(kù)操作

數(shù)據(jù)庫(kù)操作CRUD.png

表相關(guān)操作

創(chuàng)建表

表操作C(創(chuàng)建).png

操作表

表操作RUD.png

增刪改表的數(shù)據(jù)

增刪改表的數(shù)據(jù).png

查詢語(yǔ)句

查詢語(yǔ)法

查詢語(yǔ)法.png

基礎(chǔ)查詢

基礎(chǔ)查詢.png

條件查詢

條件查詢.png

排序查詢

排序查詢.png

聚合函數(shù)

聚合函數(shù).png

分組查詢

分組查詢.png

分頁(yè)查詢

分頁(yè)查詢.png

約束

約束概念以及類型

約束概念以及類型.png

非空約束

非空約束.png

唯一約束

唯一約束.png

主鍵約束

主鍵約束.png

自動(dòng)增長(zhǎng)

自動(dòng)增長(zhǎng).png

外鍵約束

外鍵約束.png

數(shù)據(jù)庫(kù)設(shè)計(jì)

多表之間的關(guān)系

關(guān)系概述

多表之間的關(guān)系.png

一對(duì)一關(guān)系圖示

一對(duì)一關(guān)系圖示.png

一對(duì)多關(guān)系圖示

一對(duì)多關(guān)系圖示.png

多對(duì)多關(guān)系圖示

多對(duì)多關(guān)系圖示.png

數(shù)據(jù)庫(kù)設(shè)計(jì)范式

范式概述

范式概述.png

三大范式分類說明

三大范式分類說明.png

三大范式解析案列

原始數(shù)據(jù)

原始不符合三大范式的數(shù)據(jù)表.png

解析成符合第一范式

解析成符合第一范式.png

解析成符合第二范式

解析成符合第二范式.png

解析成符合第三范式

解析成符合第三范式.png

數(shù)據(jù)庫(kù)備份和還原

數(shù)據(jù)庫(kù)備份和還原.png

多表查詢

內(nèi)連接查詢

內(nèi)連接查詢.png

顯示內(nèi)連接

顯示內(nèi)連接.png

外連接查詢

外連接查詢.png

子查詢概念

子查詢的概念.png

子查詢的不同情況

子查詢的不同情況.png

事務(wù)相關(guān)

事務(wù)的介紹

事務(wù)的基本介紹.png

事務(wù)的四大特征

事務(wù)的四大特征.png

事務(wù)的隔離級(jí)別

事務(wù)的隔離級(jí)別.png

MySQL數(shù)據(jù)庫(kù)事務(wù)默認(rèn)自動(dòng)提交

MySQL數(shù)據(jù)庫(kù)事務(wù)默認(rèn)自動(dòng)提交.png

DCL管理用戶

用戶相關(guān)

DCL管理用戶.png

權(quán)限管理

DCL查詢用戶.png
DCL權(quán)限管理.png

數(shù)據(jù)庫(kù)基本概念

數(shù)據(jù)庫(kù)基本概念.png

SQL

SQL概念

SQL的概念.png

SQL語(yǔ)法

SQL通用語(yǔ)法.png

SQL分類

SQL分類.png

數(shù)據(jù)庫(kù)相關(guān)操作

數(shù)據(jù)庫(kù)操作

數(shù)據(jù)庫(kù)操作CRUD.png

表相關(guān)操作

創(chuàng)建表

表操作C(創(chuàng)建).png

操作表

表操作RUD.png

增刪改表的數(shù)據(jù)

增刪改表的數(shù)據(jù).png

查詢語(yǔ)句

查詢語(yǔ)法

查詢語(yǔ)法.png

基礎(chǔ)查詢

基礎(chǔ)查詢.png

條件查詢

條件查詢.png

排序查詢

排序查詢.png

聚合函數(shù)

聚合函數(shù).png

分組查詢

分組查詢.png

分頁(yè)查詢

分頁(yè)查詢.png

約束

約束概念以及類型

約束概念以及類型.png

非空約束

非空約束.png

唯一約束

唯一約束.png

主鍵約束

主鍵約束.png

自動(dòng)增長(zhǎng)

自動(dòng)增長(zhǎng).png

外鍵約束

外鍵約束.png

數(shù)據(jù)庫(kù)設(shè)計(jì)

多表之間的關(guān)系

關(guān)系概述

多表之間的關(guān)系.png

一對(duì)一關(guān)系圖示

一對(duì)一關(guān)系圖示.png

一對(duì)多關(guān)系圖示

一對(duì)多關(guān)系圖示.png

多對(duì)多關(guān)系圖示

多對(duì)多關(guān)系圖示.png

數(shù)據(jù)庫(kù)設(shè)計(jì)范式

范式概述

范式概述.png

三大范式分類說明

三大范式分類說明.png

三大范式解析案列

原始數(shù)據(jù)

原始不符合三大范式的數(shù)據(jù)表.png

解析成符合第一范式

解析成符合第一范式.png

解析成符合第二范式

解析成符合第二范式.png

解析成符合第三范式

解析成符合第三范式.png

數(shù)據(jù)庫(kù)備份和還原

數(shù)據(jù)庫(kù)備份和還原.png

多表查詢

內(nèi)連接查詢

內(nèi)連接查詢.png

顯示內(nèi)連接

顯示內(nèi)連接.png

外連接查詢

外連接查詢.png

子查詢概念

子查詢的概念.png

子查詢的不同情況

子查詢的不同情況.png

事務(wù)相關(guān)

事務(wù)的介紹

事務(wù)的基本介紹.png

事務(wù)的四大特征

事務(wù)的四大特征.png

事務(wù)的隔離級(jí)別

事務(wù)的隔離級(jí)別.png

MySQL數(shù)據(jù)庫(kù)事務(wù)默認(rèn)自動(dòng)提交

MySQL數(shù)據(jù)庫(kù)事務(wù)默認(rèn)自動(dòng)提交.png

DCL管理用戶

用戶相關(guān)

DCL管理用戶.png

權(quán)限管理

DCL查詢用戶.png
DCL權(quán)限管理.png

數(shù)據(jù)庫(kù)的基本概念

  1. 數(shù)據(jù)庫(kù)的英文單詞： DataBase 簡(jiǎn)稱 ： DB

  2. 什么數(shù)據(jù)庫(kù)愕乎？

    • 用于存儲(chǔ)和管理數(shù)據(jù)的倉(cāng)庫(kù)。

  3. 數(shù)據(jù)庫(kù)的特點(diǎn)：

    1. 持久化存儲(chǔ)數(shù)據(jù)的壁公。其實(shí)數(shù)據(jù)庫(kù)就是一個(gè)文件系統(tǒng)
    2. 方便存儲(chǔ)和管理數(shù)據(jù)
    3. 使用了統(tǒng)一的方式操作數(shù)據(jù)庫(kù) -- SQL
  1. 常見的數(shù)據(jù)庫(kù)軟件

MySQL數(shù)據(jù)庫(kù)軟件

  1. 安裝

  2. 卸載

    1. 去mysql的安裝目錄找到my.ini文件
      • 復(fù)制 datadir="C:/ProgramData/MySQL/MySQL Server 5.5/Data/"
    2. 卸載MySQL
    3. 刪除C:/ProgramData目錄下的MySQL文件夾感论。

  3. 配置

    • MySQL服務(wù)啟動(dòng)

      1. 手動(dòng)。
      2. cmd--> services.msc 打開服務(wù)的窗口
      3. 使用管理員打開cmd
        • net start mysql : 啟動(dòng)mysql的服務(wù)
        • net stop mysql:關(guān)閉mysql服務(wù)

    • MySQL登錄

      1. mysql -uroot -p密碼
      2. mysql -hip -uroot -p連接目標(biāo)的密碼
      3. mysql --host=ip --user=root --password=連接目標(biāo)的密碼

    • MySQL退出

      1. exit
      2. quit

    • MySQL目錄結(jié)構(gòu)

      1. MySQL安裝目錄：basedir="D:/develop/MySQL/"
        • 配置文件 my.ini
      2. MySQL數(shù)據(jù)目錄：datadir="C:/ProgramData/MySQL/MySQL Server 5.5/Data/"
        • 幾個(gè)概念
          • 數(shù)據(jù)庫(kù)：文件夾
          • 表：文件
          • 數(shù)據(jù)：數(shù)據(jù)

SQL

1.什么是SQL紊册？

Structured Query Language：結(jié)構(gòu)化查詢語(yǔ)言
其實(shí)就是定義了操作所有關(guān)系型數(shù)據(jù)庫(kù)的規(guī)則比肄。每一種數(shù)據(jù)庫(kù)操作的方式存在不一樣的地方，稱為“方言”囊陡。

2.SQL通用語(yǔ)法

  1. SQL 語(yǔ)句可以單行或多行書寫芳绩，以分號(hào)結(jié)尾。
  2. 可使用空格和縮進(jìn)來增強(qiáng)語(yǔ)句的可讀性撞反。
  3. MySQL 數(shù)據(jù)庫(kù)的 SQL 語(yǔ)句不區(qū)分大小寫妥色，關(guān)鍵字建議使用大寫。
  4. 3 種注釋
    • 單行注釋: -- 注釋內(nèi)容 或 # 注釋內(nèi)容(mysql 特有)
    • 多行注釋: /* 注釋 */

3.SQL分類

  1. DDL(Data Definition Language)數(shù)據(jù)定義語(yǔ)言

    用來定義數(shù)據(jù)庫(kù)對(duì)象：數(shù)據(jù)庫(kù)遏片，表嘹害，列等。關(guān)鍵字：create, drop,alter 等

  2. DML(Data Manipulation Language)數(shù)據(jù)操作語(yǔ)言

    用來對(duì)數(shù)據(jù)庫(kù)中表的數(shù)據(jù)進(jìn)行增刪改吮便。關(guān)鍵字：insert, delete, update 等

  3. DQL(Data Query Language)數(shù)據(jù)查詢語(yǔ)言

    用來查詢數(shù)據(jù)庫(kù)中表的記錄(數(shù)據(jù))笔呀。關(guān)鍵字：select, where 等

  4. DCL(Data Control Language)數(shù)據(jù)控制語(yǔ)言(了解)

    用來定義數(shù)據(jù)庫(kù)的訪問權(quán)限和安全級(jí)別，及創(chuàng)建用戶线衫。關(guān)鍵字：GRANT凿可， REVOKE 等

DDL:操作數(shù)據(jù)庫(kù)、表

  1. 操作數(shù)據(jù)庫(kù)：CRUD
    1. C(Create):創(chuàng)建

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

        • create database 數(shù)據(jù)庫(kù)名稱;

      • 創(chuàng)建數(shù)據(jù)庫(kù)，判斷不存在枯跑，再創(chuàng)建：

        • create database if not exists 數(shù)據(jù)庫(kù)名稱;

      • 創(chuàng)建數(shù)據(jù)庫(kù)惨驶，并指定字符集

        • create database 數(shù)據(jù)庫(kù)名稱 character set 字符集名;

      • 練習(xí)： 創(chuàng)建db4數(shù)據(jù)庫(kù)，判斷是否存在敛助，并制定字符集為gbk

        • create database if not exists db4 character set gbk;
    2. R(Retrieve)：查詢
      • 查詢所有數(shù)據(jù)庫(kù)的名稱:
        • show databases;
      • 查詢某個(gè)數(shù)據(jù)庫(kù)的字符集:查詢某個(gè)數(shù)據(jù)庫(kù)的創(chuàng)建語(yǔ)句
        • show create database 數(shù)據(jù)庫(kù)名稱;
    3. U(Update):修改
      • 修改數(shù)據(jù)庫(kù)的字符集
        • alter database 數(shù)據(jù)庫(kù)名稱 character set 字符集名稱;
    4. D(Delete):刪除
      • 刪除數(shù)據(jù)庫(kù)
        • drop database 數(shù)據(jù)庫(kù)名稱;
      • 判斷數(shù)據(jù)庫(kù)存在粗卜，存在再刪除
        • drop database if exists 數(shù)據(jù)庫(kù)名稱;
    5. 使用數(shù)據(jù)庫(kù)
      • 查詢當(dāng)前正在使用的數(shù)據(jù)庫(kù)名稱
        • select database();
      • 使用數(shù)據(jù)庫(kù)
        • use 數(shù)據(jù)庫(kù)名稱;
  1. 操作表

    1. C(Create):創(chuàng)建

      1. 語(yǔ)法：
        create table 表名(
        列名1 數(shù)據(jù)類型1,
        列名2 數(shù)據(jù)類型2,
        ....
        列名n 數(shù)據(jù)類型n
        );
        • 注意：最后一列，不需要加逗號(hào)（,）
        • 數(shù)據(jù)庫(kù)類型：

          1. int：整數(shù)類型

            • age int,

          2. double:小數(shù)類型

            • score double(5,2)

          3. date:日期纳击，只包含年月日续扔，yyyy-MM-dd

          4. datetime:日期，包含年月日時(shí)分秒 yyyy-MM-dd HH:mm:ss

          5. timestamp:時(shí)間錯(cuò)類型 包含年月日時(shí)分秒 yyyy-MM-dd HH:mm:ss

            • 如果將來不給這個(gè)字段賦值焕数，或賦值為null纱昧，則默認(rèn)使用當(dāng)前的系統(tǒng)時(shí)間，來自動(dòng)賦值

          6. varchar：字符串

            • name varchar(20):姓名最大20個(gè)字符
            • zhangsan 8個(gè)字符 張三 2個(gè)字符

      • 創(chuàng)建表

        create table student(
  id int,
  name varchar(32),
  age int ,
  score double(4,1),
  birthday date,
  insert_time timestamp
);

      • 復(fù)制表：

        • create table 表名 like 被復(fù)制的表名;

    2. R(Retrieve)：查詢

      • 查詢某個(gè)數(shù)據(jù)庫(kù)中所有的表名稱
        • show tables;
      • 查詢表結(jié)構(gòu)
        • desc 表名;

    3. U(Update):修改

      1. 修改表名
        alter table 表名 rename to 新的表名;
      2. 修改表的字符集
        alter table 表名 character set 字符集名稱;
      3. 添加一列
        alter table 表名 add 列名 數(shù)據(jù)類型;
      4. 修改列名稱 類型
        alter table 表名 change 列名 新列別 新數(shù)據(jù)類型;
        alter table 表名 modify 列名 新數(shù)據(jù)類型;
      5. 刪除列
        alter table 表名 drop 列名;

    4. D(Delete):刪除

      • drop table 表名;
      • drop table if exists 表名 ;
  • 客戶端圖形化工具：SQLYog

DML：增刪改表中數(shù)據(jù)

  1. 添加數(shù)據(jù)：
    • 語(yǔ)法：
      • insert into 表名(列名1,列名2,...列名n) values(值1,值2,...值n);
    • 注意：
      1. 列名和值要一一對(duì)應(yīng)堡赔。
      2. 如果表名后识脆，不定義列名，則默認(rèn)給所有列添加值
        insert into 表名 values(值1,值2,...值n);
      3. 除了數(shù)字類型善已，其他類型需要使用引號(hào)(單雙都可以)引起來
  2. 刪除數(shù)據(jù)：
    • 語(yǔ)法：
      • delete from 表名 [where 條件]
    • 注意：
      1. 如果不加條件灼捂，則刪除表中所有記錄。
      2. 如果要?jiǎng)h除所有記錄
        1. delete from 表名; -- 不推薦使用换团。有多少條記錄就會(huì)執(zhí)行多少次刪除操作
        2. TRUNCATE TABLE 表名; -- 推薦使用悉稠，效率更高 先刪除表，然后再創(chuàng)建一張一樣的表艘包。
  3. 修改數(shù)據(jù)：

    • 語(yǔ)法：

      • update 表名 set 列名1 = 值1, 列名2 = 值2,... [where 條件];

    • 注意：

      1. 如果不加任何條件的猛，則會(huì)將表中所有記錄全部修改。

DQL：查詢表中的記錄

  • select * from 表名;
  1. 語(yǔ)法：
    select
        字段列表
    from
        表名列表
    where
        條件列表
    group by
        分組字段
    having
        分組之后的條件
    order by
        排序
    limit
        分頁(yè)限定
  1. 基礎(chǔ)查詢
    1. 多個(gè)字段的查詢
      select 字段名1辑甜，字段名2... from 表名衰絮；
      • 注意：
        • 如果查詢所有字段，則可以使用*來替代字段列表磷醋。
    2. 去除重復(fù)：
      • distinct
    3. 計(jì)算列
      • 一般可以使用四則運(yùn)算計(jì)算一些列的值。（一般只會(huì)進(jìn)行數(shù)值型的計(jì)算）
      • ifnull(表達(dá)式1,表達(dá)式2)：null參與的運(yùn)算胡诗，計(jì)算結(jié)果都為null
        • 表達(dá)式1：哪個(gè)字段需要判斷是否為null
        • 如果該字段為null后的替換值邓线。
    4. 起別名：
      • as：as也可以省略
  1. 條件查詢
    1. where子句后跟條件
    2. 運(yùn)算符
      * > 、< 煌恢、<= 骇陈、>= 、= 瑰抵、<>
      • BETWEEN...AND
      • IN( 集合)
      • LIKE：模糊查詢
        • 占位符：
          • _:單個(gè)任意字符
          • %：多個(gè)任意字符
      • IS NULL
      • and 或 &&
      • or 或 ||
        • not 或 !
##

    -- 查詢年齡大于20歲

    SELECT * FROM student WHERE age > 20;
    
    SELECT * FROM student WHERE age >= 20;
    
    -- 查詢年齡等于20歲
    SELECT * FROM student WHERE age = 20;
    
    -- 查詢年齡不等于20歲
    SELECT * FROM student WHERE age != 20;
    SELECT * FROM student WHERE age <> 20;
    
    -- 查詢年齡大于等于20 小于等于30
    
    SELECT * FROM student WHERE age >= 20 &&  age <=30;
    SELECT * FROM student WHERE age >= 20 AND  age <=30;
    SELECT * FROM student WHERE age BETWEEN 20 AND 30;
    
    -- 查詢年齡22歲你雌，18歲，25歲的信息
    SELECT * FROM student WHERE age = 22 OR age = 18 OR age = 25
    SELECT * FROM student WHERE age IN (22,18,25);
    
    -- 查詢英語(yǔ)成績(jī)?yōu)閚ull
    SELECT * FROM student WHERE english = NULL; -- 不對(duì)的。null值不能使用 = （!=） 判斷
    
    SELECT * FROM student WHERE english IS NULL;
    
    -- 查詢英語(yǔ)成績(jī)不為null
    SELECT * FROM student WHERE english  IS NOT NULL;



    -- 查詢姓馬的有哪些婿崭？ like
    SELECT * FROM student WHERE NAME LIKE '馬%';
    -- 查詢姓名第二個(gè)字是化的人
    
    SELECT * FROM student WHERE NAME LIKE "_化%";
    
    -- 查詢姓名是3個(gè)字的人
    SELECT * FROM student WHERE NAME LIKE '___';
    
    
    -- 查詢姓名中包含德的人
    SELECT * FROM student WHERE NAME LIKE '%德%';

DQL:查詢語(yǔ)句

  1. 排序查詢

    • 語(yǔ)法：order by 子句

      • order by 排序字段1 排序方式1 拨拓， 排序字段2 排序方式2...

    • 排序方式：

      • ASC：升序，默認(rèn)的氓栈。
      • DESC：降序渣磷。

    • 注意：

      • 如果有多個(gè)排序條件，則當(dāng)前邊的條件值一樣時(shí)授瘦，才會(huì)判斷第二條件醋界。

  1. 聚合函數(shù)：將一列數(shù)據(jù)作為一個(gè)整體，進(jìn)行縱向的計(jì)算提完。

    1. count：計(jì)算個(gè)數(shù)
      1. 一般選擇非空的列：主鍵
      2. count(*)
    2. max：計(jì)算最大值
    3. min：計(jì)算最小值
    4. sum：計(jì)算和
    5. avg：計(jì)算平均值
    • 注意：聚合函數(shù)的計(jì)算形纺，排除null值。
      解決方案：
      1. 選擇不包含非空的列進(jìn)行計(jì)算
      2. IFNULL函數(shù)

  2. 分組查詢:

    1. 語(yǔ)法：group by 分組字段徒欣；
    2. 注意：
      1. 分組之后查詢的字段：分組字段挡篓、聚合函數(shù)
      2. where 和 having 的區(qū)別？

        1. where 在分組之前進(jìn)行限定帚称，如果不滿足條件官研，則不參與分組。having在分組之后進(jìn)行限定闯睹，如果不滿足結(jié)果戏羽，則不會(huì)被查詢出來

        2. where 后不可以跟聚合函數(shù)，having可以進(jìn)行聚合函數(shù)的判斷楼吃。

           -- 按照性別分組始花。分別查詢男、女同學(xué)的平均分
 
 SELECT sex , AVG(math) FROM student GROUP BY sex;
 
 -- 按照性別分組孩锡。分別查詢男酷宵、女同學(xué)的平均分,人數(shù)
 
 SELECT sex , AVG(math),COUNT(id) FROM student GROUP BY sex;
 
 --  按照性別分組。分別查詢男躬窜、女同學(xué)的平均分,人數(shù) 要求：分?jǐn)?shù)低于70分的人浇垦，不參與分組
 SELECT sex , AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex;
 
 --  按照性別分組。分別查詢男荣挨、女同學(xué)的平均分,人數(shù) 要求：分?jǐn)?shù)低于70分的人男韧，不參與分組,分組之后。人數(shù)要大于2個(gè)人
 SELECT sex , AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex HAVING COUNT(id) > 2;
 
 SELECT sex , AVG(math),COUNT(id) 人數(shù) FROM student WHERE math > 70 GROUP BY sex HAVING 人數(shù) > 2;

  3. 分頁(yè)查詢

    1. 語(yǔ)法：limit 開始的索引,每頁(yè)查詢的條數(shù);

    2. 公式：開始的索引 = （當(dāng)前的頁(yè)碼 - 1） * 每頁(yè)顯示的條數(shù)

       -- 每頁(yè)顯示3條記錄 

 SELECT * FROM student LIMIT 0,3; -- 第1頁(yè)
 
 SELECT * FROM student LIMIT 3,3; -- 第2頁(yè)
 
 SELECT * FROM student LIMIT 6,3; -- 第3頁(yè)

    3. limit 是一個(gè)MySQL"方言"

約束

  • 概念： 對(duì)表中的數(shù)據(jù)進(jìn)行限定默垄，保證數(shù)據(jù)的正確性此虑、有效性和完整性。

  • 分類：

    1. 主鍵約束：primary key
    2. 非空約束：not null
    3. 唯一約束：unique
    4. 外鍵約束：foreign key

  • 非空約束：not null口锭，值不能為null

    1. 創(chuàng)建表時(shí)添加約束

       CREATE TABLE stu(
   id INT,
   NAME VARCHAR(20) NOT NULL -- name為非空
 );

    2. 創(chuàng)建表完后朦前，添加非空約束

       ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;

    3. 刪除name的非空約束

       ALTER TABLE stu MODIFY NAME VARCHAR(20);

  • 唯一約束：unique，值不能重復(fù)

    1. 創(chuàng)建表時(shí)，添加唯一約束

       CREATE TABLE stu(
     id INT,
     phone_number VARCHAR(20) UNIQUE -- 添加了唯一約束
 
 );
      • 注意mysql中韭寸，唯一約束限定的列的值可以有多個(gè)null

    2. 刪除唯一約束

       ALTER TABLE stu DROP INDEX phone_number;

    3. 在創(chuàng)建表后春哨，添加唯一約束

       ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE;

  • 主鍵約束：primary key。

    1. 注意：

      1. 含義：非空且唯一
      2. 一張表只能有一個(gè)字段為主鍵
      3. 主鍵就是表中記錄的唯一標(biāo)識(shí)

    2. 在創(chuàng)建表時(shí)棒仍，添加主鍵約束

       create table stu(
     id int primary key,-- 給id添加主鍵約束
     name varchar(20)
 );

    3. 刪除主鍵
      -- 錯(cuò)誤 alter table stu modify id int ;
      ALTER TABLE stu DROP PRIMARY KEY;

    4. 創(chuàng)建完表后悲靴，添加主鍵
      ALTER TABLE stu MODIFY id INT PRIMARY KEY;

    5. 自動(dòng)增長(zhǎng)：

      1. 概念：如果某一列是數(shù)值類型的，使用 auto_increment 可以來完成值得自動(dòng)增長(zhǎng)

      2. 在創(chuàng)建表時(shí)莫其，添加主鍵約束癞尚，并且完成主鍵自增長(zhǎng)

         create table stu(
     id int primary key auto_increment,-- 給id添加主鍵約束
     name varchar(20)
 );

      3. 刪除自動(dòng)增長(zhǎng)

         ALTER TABLE stu MODIFY id INT;

      4. 添加自動(dòng)增長(zhǎng)

         ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;

  • 外鍵約束：foreign key,讓表于表產(chǎn)生關(guān)系，從而保證數(shù)據(jù)的正確性乱陡。

    1. 在創(chuàng)建表時(shí)浇揩，可以添加外鍵

      • 語(yǔ)法：

          create table 表名(
      ....
      外鍵列
      constraint 外鍵名稱 foreign key (外鍵列名稱) references 主表名稱(主表列名稱)
  );

    2. 刪除外鍵

       ALTER TABLE 表名 DROP FOREIGN KEY 外鍵名稱;

    3. 創(chuàng)建表之后，添加外鍵

       ALTER TABLE 表名 ADD CONSTRAINT 外鍵名稱 FOREIGN KEY (外鍵字段名稱) REFERENCES 主表名稱(主表列名稱);

    4. 級(jí)聯(lián)操作

      1. 添加級(jí)聯(lián)操作

         ALTER TABLE 表名 ADD CONSTRAINT 外鍵名稱 
     FOREIGN KEY (外鍵字段名稱) REFERENCES 主表名稱(主表列名稱) ON UPDATE CASCADE ON DELETE CASCADE  ;

      2. 分類：

        1. 級(jí)聯(lián)更新：ON UPDATE CASCADE
        2. 級(jí)聯(lián)刪除：ON DELETE CASCADE

數(shù)據(jù)庫(kù)的設(shè)計(jì)

  1. 多表之間的關(guān)系

    1. 分類：

      1. 一對(duì)一(了解)：
        • 如：人和身份證
        • 分析：一個(gè)人只有一個(gè)身份證憨颠，一個(gè)身份證只能對(duì)應(yīng)一個(gè)人
      2. 一對(duì)多(多對(duì)一)：
        • 如：部門和員工
        • 分析：一個(gè)部門有多個(gè)員工胳徽，一個(gè)員工只能對(duì)應(yīng)一個(gè)部門
      3. 多對(duì)多：
        • 如：學(xué)生和課程
        • 分析：一個(gè)學(xué)生可以選擇很多門課程，一個(gè)課程也可以被很多學(xué)生選擇

    2. 實(shí)現(xiàn)關(guān)系：

      1. 一對(duì)多(多對(duì)一)：
        • 如：部門和員工
        • 實(shí)現(xiàn)方式：在多的一方建立外鍵爽彤，指向一的一方的主鍵养盗。
      2. 多對(duì)多：
        • 如：學(xué)生和課程
        • 實(shí)現(xiàn)方式：多對(duì)多關(guān)系實(shí)現(xiàn)需要借助第三張中間表。中間表至少包含兩個(gè)字段适篙，這兩個(gè)字段作為第三張表的外鍵往核，分別指向兩張表的主鍵
      3. 一對(duì)一(了解)：
        • 如：人和身份證
        • 實(shí)現(xiàn)方式：一對(duì)一關(guān)系實(shí)現(xiàn)，可以在任意一方添加唯一外鍵指向另一方的主鍵嚷节。

    3. 案例

       -- 創(chuàng)建旅游線路分類表 tab_category
 -- cid 旅游線路分類主鍵聂儒，自動(dòng)增長(zhǎng)
 -- cname 旅游線路分類名稱非空，唯一硫痰，字符串 100
 CREATE TABLE tab_category (
     cid INT PRIMARY KEY AUTO_INCREMENT,
     cname VARCHAR(100) NOT NULL UNIQUE
 );
 
 -- 創(chuàng)建旅游線路表 tab_route
 /*
 rid 旅游線路主鍵衩婚，自動(dòng)增長(zhǎng)
 rname 旅游線路名稱非空，唯一效斑，字符串 100
 price 價(jià)格
 rdate 上架時(shí)間非春，日期類型
 cid 外鍵，所屬分類
 */
 CREATE TABLE tab_route(
     rid INT PRIMARY KEY AUTO_INCREMENT,
     rname VARCHAR(100) NOT NULL UNIQUE,
     price DOUBLE,
     rdate DATE,
     cid INT,
     FOREIGN KEY (cid) REFERENCES tab_category(cid)
 );
 
 /*創(chuàng)建用戶表 tab_user
 uid 用戶主鍵鳍悠，自增長(zhǎng)
 username 用戶名長(zhǎng)度 100税娜，唯一，非空
 password 密碼長(zhǎng)度 30藏研，非空
 name 真實(shí)姓名長(zhǎng)度 100
 birthday 生日
 sex 性別，定長(zhǎng)字符串 1
 telephone 手機(jī)號(hào)概行，字符串 11
 email 郵箱蠢挡，字符串長(zhǎng)度 100
 */
 CREATE TABLE tab_user (
     uid INT PRIMARY KEY AUTO_INCREMENT,
     username VARCHAR(100) UNIQUE NOT NULL,
     PASSWORD VARCHAR(30) NOT NULL,
     NAME VARCHAR(100),
     birthday DATE,
     sex CHAR(1) DEFAULT '男',
     telephone VARCHAR(11),
     email VARCHAR(100)
 );
 
 /*
 創(chuàng)建收藏表 tab_favorite
 rid 旅游線路 id，外鍵
 date 收藏時(shí)間
 uid 用戶 id，外鍵
 rid 和 uid 不能重復(fù)业踏，設(shè)置復(fù)合主鍵禽炬，同一個(gè)用戶不能收藏同一個(gè)線路兩次
 */
 CREATE TABLE tab_favorite (
     rid INT, -- 線路id
     DATE DATETIME,
     uid INT, -- 用戶id
     -- 創(chuàng)建復(fù)合主鍵
     PRIMARY KEY(rid,uid), -- 聯(lián)合主鍵
     FOREIGN KEY (rid) REFERENCES tab_route(rid),
     FOREIGN KEY(uid) REFERENCES tab_user(uid)
 );

  2. 數(shù)據(jù)庫(kù)設(shè)計(jì)的范式

    • 概念：設(shè)計(jì)數(shù)據(jù)庫(kù)時(shí)，需要遵循的一些規(guī)范勤家。要遵循后邊的范式要求腹尖，必須先遵循前邊的所有范式要求

      設(shè)計(jì)關(guān)系數(shù)據(jù)庫(kù)時(shí)，遵從不同的規(guī)范要求伐脖，設(shè)計(jì)出合理的關(guān)系型數(shù)據(jù)庫(kù)热幔，這些不同的規(guī)范要求被稱為不同的范式，各種范式呈遞次規(guī)范讼庇，越高的范式數(shù)據(jù)庫(kù)冗余越小绎巨。
      目前關(guān)系數(shù)據(jù)庫(kù)有六種范式：第一范式（1NF）、第二范式（2NF）蠕啄、第三范式（3NF）场勤、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF歼跟，又稱完美范式）和媳。

    • 分類：

      1. 第一范式（1NF）：每一列都是不可分割的原子數(shù)據(jù)項(xiàng)

      2. 第二范式（2NF）：在1NF的基礎(chǔ)上，非碼屬性必須完全依賴于碼（在1NF基礎(chǔ)上消除非主屬性對(duì)主碼的部分函數(shù)依賴）

        • 幾個(gè)概念：
          1. 函數(shù)依賴：A-->B,如果通過A屬性(屬性組)的值哈街，可以確定唯一B屬性的值留瞳。則稱B依賴于A
            例如：學(xué)號(hào)-->姓名。 （學(xué)號(hào)叹卷，課程名稱） --> 分?jǐn)?shù)
          2. 完全函數(shù)依賴：A-->B撼港， 如果A是一個(gè)屬性組，則B屬性值得確定需要依賴于A屬性組中所有的屬性值骤竹。
            例如：（學(xué)號(hào)帝牡，課程名稱） --> 分?jǐn)?shù)
          3. 部分函數(shù)依賴：A-->B， 如果A是一個(gè)屬性組蒙揣，則B屬性值得確定只需要依賴于A屬性組中某一些值即可靶溜。
            例如：（學(xué)號(hào)，課程名稱） -- > 姓名
          4. 傳遞函數(shù)依賴：A-->B, B -- >C . 如果通過A屬性(屬性組)的值懒震，可以確定唯一B屬性的值罩息，在通過B屬性（屬性組）的值可以確定唯一C屬性的值，則稱 C 傳遞函數(shù)依賴于A
            例如：學(xué)號(hào)-->系名个扰，系名-->系主任
          5. 碼：如果在一張表中瓷炮，一個(gè)屬性或?qū)傩越M，被其他所有屬性所完全依賴递宅，則稱這個(gè)屬性(屬性組)為該表的碼
            例如：該表中碼為：（學(xué)號(hào)娘香，課程名稱）
            • 主屬性：碼屬性組中的所有屬性
            • 非主屬性：除過碼屬性組的屬性

      3. 第三范式（3NF）：在2NF基礎(chǔ)上苍狰，任何非主屬性不依賴于其它非主屬性（在2NF基礎(chǔ)上消除傳遞依賴）

數(shù)據(jù)庫(kù)的備份和還原

  1. 命令行：
    • 語(yǔ)法：
      • 備份： mysqldump -u用戶名 -p密碼 數(shù)據(jù)庫(kù)名稱 > 保存的路徑
      • 還原：
        1. 登錄數(shù)據(jù)庫(kù)
        2. 創(chuàng)建數(shù)據(jù)庫(kù)
        3. 使用數(shù)據(jù)庫(kù)
        4. 執(zhí)行文件。source 文件路徑
  2. 圖形化工具：

多表查詢：

  • 查詢語(yǔ)法：

      select
      列名列表
  from
      表名列表
  where....

  • 準(zhǔn)備sql

      # 創(chuàng)建部門表
  CREATE TABLE dept(
      id INT PRIMARY KEY AUTO_INCREMENT,
      NAME VARCHAR(20)
  );
  INSERT INTO dept (NAME) VALUES ('開發(fā)部'),('市場(chǎng)部'),('財(cái)務(wù)部');
  # 創(chuàng)建員工表
  CREATE TABLE emp (
      id INT PRIMARY KEY AUTO_INCREMENT,
      NAME VARCHAR(10),
      gender CHAR(1), -- 性別
      salary DOUBLE, -- 工資
      join_date DATE, -- 入職日期
      dept_id INT,
      FOREIGN KEY (dept_id) REFERENCES dept(id) -- 外鍵烘绽，關(guān)聯(lián)部門表(部門表的主鍵)
  );
  INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('孫悟空','男',7200,'2013-02-24',1);
  INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('豬八戒','男',3600,'2010-12-02',2);
  INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('唐僧','男',9000,'2008-08-08',2);
  INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('白骨精','女',5000,'2015-10-07',3);
  INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('蜘蛛精','女',4500,'2011-03-14',1);

  • 笛卡爾積：

    • 有兩個(gè)集合A,B .取這兩個(gè)集合的所有組成情況淋昭。
    • 要完成多表查詢，需要消除無用的數(shù)據(jù)

  • 多表查詢的分類：

    1. 內(nèi)連接查詢：

      1. 隱式內(nèi)連接：使用where條件消除無用數(shù)據(jù)

         * 例子：
 -- 查詢所有員工信息和對(duì)應(yīng)的部門信息

 SELECT * FROM emp,dept WHERE emp.`dept_id` = dept.`id`;
 
 -- 查詢員工表的名稱安接，性別翔忽。部門表的名稱
 SELECT emp.name,emp.gender,dept.name FROM emp,dept WHERE emp.`dept_id` = dept.`id`;
 
 SELECT 
     t1.name, -- 員工表的姓名
     t1.gender,-- 員工表的性別
     t2.name -- 部門表的名稱
 FROM
     emp t1,
     dept t2
 WHERE 
     t1.`dept_id` = t2.`id`;

      2. 顯式內(nèi)連接：

         * 語(yǔ)法： select 字段列表 from 表名1 [inner] join 表名2 on 條件
 * 例如：
     * SELECT * FROM emp INNER JOIN dept ON emp.`dept_id` = dept.`id`;   
     * SELECT * FROM emp JOIN dept ON emp.`dept_id` = dept.`id`;

      3. 內(nèi)連接查詢：

        1. 從哪些表中查詢數(shù)據(jù)
        2. 條件是什么
        3. 查詢哪些字段
    2. 外鏈接查詢：
      1. 左外連接：
        • 語(yǔ)法：select 字段列表 from 表1 left [outer] join 表2 on 條件；
        • 查詢的是左表所有數(shù)據(jù)以及其交集部分盏檐。
        • 例子：
          -- 查詢所有員工信息歇式，如果員工有部門，則查詢部門名稱糯笙，沒有部門贬丛，則不顯示部門名稱
          SELECT t1.*,t2.name FROM emp t1 LEFT JOIN dept t2 ON t1.dept_id = t2.id;
      2. 右外連接：
        • 語(yǔ)法：select 字段列表 from 表1 right [outer] join 表2 on 條件；
        • 查詢的是右表所有數(shù)據(jù)以及其交集部分给涕。
        • 例子：
          SELECT * FROM dept t2 RIGHT JOIN emp t1 ON t1.dept_id = t2.id;
    3. 子查詢：

      • 概念：查詢中嵌套查詢豺憔，稱嵌套查詢?yōu)樽硬樵儭?/p>

        -- 查詢工資最高的員工信息

        -- 1 查詢最高的工資是多少 9000
        SELECT MAX(salary) FROM emp;

        -- 2 查詢員工信息，并且工資等于9000的
        SELECT * FROM emp WHERE emp.salary = 9000;

        -- 一條sql就完成這個(gè)操作够庙。子查詢
        SELECT * FROM emp WHERE emp.salary = (SELECT MAX(salary) FROM emp);

      • 子查詢不同情況

        1. 子查詢的結(jié)果是單行單列的：

          • 子查詢可以作為條件恭应，使用運(yùn)算符去判斷。 運(yùn)算符： > >= < <= =

              -- 查詢員工工資小于平均工資的人
  SELECT * FROM emp WHERE emp.salary < (SELECT AVG(salary) FROM emp);

        2. 子查詢的結(jié)果是多行單列的：

          • 子查詢可以作為條件耘眨，使用運(yùn)算符in來判斷

              -- 查詢'財(cái)務(wù)部'和'市場(chǎng)部'所有的員工信息
  SELECT id FROM dept WHERE NAME = '財(cái)務(wù)部' OR NAME = '市場(chǎng)部';
  SELECT * FROM emp WHERE dept_id = 3 OR dept_id = 2;
  -- 子查詢
  SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME = '財(cái)務(wù)部' OR NAME = '市場(chǎng)部');

        3. 子查詢的結(jié)果是多行多列的：

          • 子查詢可以作為一張?zhí)摂M表參與查詢

              -- 查詢員工入職日期是2011-11-11日之后的員工信息和部門信息
  -- 子查詢
  SELECT * FROM dept t1 ,(SELECT * FROM emp WHERE emp.`join_date` > '2011-11-11') t2
  WHERE t1.id = t2.dept_id;
  
  -- 普通內(nèi)連接
  SELECT * FROM emp t1,dept t2 WHERE t1.`dept_id` = t2.`id` AND t1.`join_date` >  '2011-11-11'

    • 多表查詢練習(xí)

        -- 部門表
  CREATE TABLE dept (
    id INT PRIMARY KEY PRIMARY KEY, -- 部門id
    dname VARCHAR(50), -- 部門名稱
    loc VARCHAR(50) -- 部門所在地
  );
  
  -- 添加4個(gè)部門
  INSERT INTO dept(id,dname,loc) VALUES 
  (10,'教研部','北京'),
  (20,'學(xué)工部','上海'),
  (30,'銷售部','廣州'),
  (40,'財(cái)務(wù)部','深圳');
  
  
  
  -- 職務(wù)表昼榛，職務(wù)名稱，職務(wù)描述
  CREATE TABLE job (
    id INT PRIMARY KEY,
    jname VARCHAR(20),
    description VARCHAR(50)
  );
  
  -- 添加4個(gè)職務(wù)
  INSERT INTO job (id, jname, description) VALUES
  (1, '董事長(zhǎng)', '管理整個(gè)公司剔难，接單'),
  (2, '經(jīng)理', '管理部門員工'),
  (3, '銷售員', '向客人推銷產(chǎn)品'),
  (4, '文員', '使用辦公軟件');
  
  
  
  -- 員工表
  CREATE TABLE emp (
    id INT PRIMARY KEY, -- 員工id
    ename VARCHAR(50), -- 員工姓名
    job_id INT, -- 職務(wù)id
    mgr INT , -- 上級(jí)領(lǐng)導(dǎo)
    joindate DATE, -- 入職日期
    salary DECIMAL(7,2), -- 工資
    bonus DECIMAL(7,2), -- 獎(jiǎng)金
    dept_id INT, -- 所在部門編號(hào)
    CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id),
    CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id)
  );
  
  -- 添加員工
  INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES 
  (1001,'孫悟空',4,1004,'2000-12-17','8000.00',NULL,20),
  (1002,'盧俊義',3,1006,'2001-02-20','16000.00','3000.00',30),
  (1003,'林沖',3,1006,'2001-02-22','12500.00','5000.00',30),
  (1004,'唐僧',2,1009,'2001-04-02','29750.00',NULL,20),
  (1005,'李逵',4,1006,'2001-09-28','12500.00','14000.00',30),
  (1006,'宋江',2,1009,'2001-05-01','28500.00',NULL,30),
  (1007,'劉備',2,1009,'2001-09-01','24500.00',NULL,10),
  (1008,'豬八戒',4,1004,'2007-04-19','30000.00',NULL,20),
  (1009,'羅貫中',1,NULL,'2001-11-17','50000.00',NULL,10),
  (1010,'吳用',3,1006,'2001-09-08','15000.00','0.00',30),
  (1011,'沙僧',4,1004,'2007-05-23','11000.00',NULL,20),
  (1012,'李逵',4,1006,'2001-12-03','9500.00',NULL,30),
  (1013,'小白龍',4,1004,'2001-12-03','30000.00',NULL,20),
  (1014,'關(guān)羽',4,1007,'2002-01-23','13000.00',NULL,10);
  
  
  
  -- 工資等級(jí)表
  CREATE TABLE salarygrade (
    grade INT PRIMARY KEY,   -- 級(jí)別
    losalary INT,  -- 最低工資
    hisalary INT -- 最高工資
  );
  
  -- 添加5個(gè)工資等級(jí)
  INSERT INTO salarygrade(grade,losalary,hisalary) VALUES 
  (1,7000,12000),
  (2,12010,14000),
  (3,14010,20000),
  (4,20010,30000),
  (5,30010,99990);
  
  -- 需求：
  
  -- 1.查詢所有員工信息胆屿。查詢員工編號(hào)，員工姓名偶宫，工資非迹，職務(wù)名稱，職務(wù)描述
  /*
      分析：
          1.員工編號(hào)纯趋，員工姓名憎兽，工資，需要查詢emp表  職務(wù)名稱吵冒，職務(wù)描述 需要查詢job表
          2.查詢條件 emp.job_id = job.id
  
  */
  SELECT 
      t1.`id`, -- 員工編號(hào)
      t1.`ename`, -- 員工姓名
      t1.`salary`,-- 工資
      t2.`jname`, -- 職務(wù)名稱
      t2.`description` -- 職務(wù)描述
  FROM 
      emp t1, job t2
  WHERE 
      t1.`job_id` = t2.`id`;
  
  
  
  -- 2.查詢員工編號(hào)纯命，員工姓名，工資痹栖，職務(wù)名稱亿汞，職務(wù)描述，部門名稱揪阿，部門位置
  /*
      分析：
          1. 員工編號(hào)留夜，員工姓名匙铡，工資 emp  職務(wù)名稱图甜，職務(wù)描述 job  部門名稱碍粥，部門位置 dept
          2. 條件： emp.job_id = job.id and emp.dept_id = dept.id
  */
  
  SELECT 
      t1.`id`, -- 員工編號(hào)
      t1.`ename`, -- 員工姓名
      t1.`salary`,-- 工資
      t2.`jname`, -- 職務(wù)名稱
      t2.`description`, -- 職務(wù)描述
      t3.`dname`, -- 部門名稱
      t3.`loc` -- 部門位置
  FROM 
      emp t1, job t2,dept t3
  WHERE 
      t1.`job_id` = t2.`id` AND t1.`dept_id` = t3.`id`;
     
  -- 3.查詢員工姓名，工資黑毅，工資等級(jí)
  /*
      分析：
          1.員工姓名嚼摩，工資 emp  工資等級(jí) salarygrade
          2.條件 emp.salary >= salarygrade.losalary and emp.salary <= salarygrade.hisalary
              emp.salary BETWEEN salarygrade.losalary and salarygrade.hisalary
  */
  SELECT 
      t1.ename ,
      t1.`salary`,
      t2.*
  FROM emp t1, salarygrade t2
  WHERE t1.`salary` BETWEEN t2.`losalary` AND t2.`hisalary`;
  
  
  
  -- 4.查詢員工姓名，工資矿瘦，職務(wù)名稱枕面，職務(wù)描述，部門名稱缚去，部門位置潮秘，工資等級(jí)
  /*
      分析：
          1. 員工姓名，工資 emp 易结， 職務(wù)名稱枕荞，職務(wù)描述 job 部門名稱，部門位置搞动，dept  工資等級(jí) salarygrade
          2. 條件： emp.job_id = job.id and emp.dept_id = dept.id and emp.salary BETWEEN salarygrade.losalary and salarygrade.hisalary
              
  */
  SELECT 
      t1.`ename`,
      t1.`salary`,
      t2.`jname`,
      t2.`description`,
      t3.`dname`,
      t3.`loc`,
      t4.`grade`
  FROM 
      emp t1,job t2,dept t3,salarygrade t4
  WHERE 
      t1.`job_id` = t2.`id` 
      AND t1.`dept_id` = t3.`id`
      AND t1.`salary` BETWEEN t4.`losalary` AND t4.`hisalary`;
  
  
  
  -- 5.查詢出部門編號(hào)躏精、部門名稱、部門位置鹦肿、部門人數(shù)
  
  /*
      分析：
          1.部門編號(hào)矗烛、部門名稱、部門位置 dept 表箩溃。 部門人數(shù) emp表
          2.使用分組查詢瞭吃。按照emp.dept_id完成分組，查詢count(id)
          3.使用子查詢將第2步的查詢結(jié)果和dept表進(jìn)行關(guān)聯(lián)查詢
          
  */
  SELECT 
      t1.`id`,t1.`dname`,t1.`loc` , t2.total
  FROM 
      dept t1,
      (SELECT
          dept_id,COUNT(id) total
      FROM 
          emp
      GROUP BY dept_id) t2
  WHERE t1.`id` = t2.dept_id;
  
  
  -- 6.查詢所有員工的姓名及其直接上級(jí)的姓名,沒有領(lǐng)導(dǎo)的員工也需要查詢
  
  /*
      分析：
          1.姓名 emp涣旨， 直接上級(jí)的姓名 emp
              * emp表的id 和 mgr 是自關(guān)聯(lián)
          2.條件 emp.id = emp.mgr
          3.查詢左表的所有數(shù)據(jù)歪架，和 交集數(shù)據(jù)
              * 使用左外連接查詢
      
  */
  /*
  select
      t1.ename,
      t1.mgr,
      t2.`id`,
      t2.ename
  from emp t1, emp t2
  where t1.mgr = t2.`id`;
  
  */
  
  SELECT 
      t1.ename,
      t1.mgr,
      t2.`id`,
      t2.`ename`
  FROM emp t1
  LEFT JOIN emp t2
  ON t1.`mgr` = t2.`id`;

事務(wù)

  1. 事務(wù)的基本介紹

    1. 概念：

      • 如果一個(gè)包含多個(gè)步驟的業(yè)務(wù)操作，被事務(wù)管理开泽，那么這些操作要么同時(shí)成功牡拇，要么同時(shí)失敗。

    2. 操作：

      1. 開啟事務(wù)： start transaction;
      2. 回滾：rollback;
      3. 提交：commit;

    3. 例子：

       CREATE TABLE account (
     id INT PRIMARY KEY AUTO_INCREMENT,
     NAME VARCHAR(10),
     balance DOUBLE
 );
 -- 添加數(shù)據(jù)
 INSERT INTO account (NAME, balance) VALUES ('zhangsan', 1000), ('lisi', 1000);
 
 
 SELECT * FROM account;
 UPDATE account SET balance = 1000;
 -- 張三給李四轉(zhuǎn)賬 500 元
 
 -- 0. 開啟事務(wù)
 START TRANSACTION;
 -- 1. 張三賬戶 -500
 
 UPDATE account SET balance = balance - 500 WHERE NAME = 'zhangsan';
 -- 2. 李四賬戶 +500
 -- 出錯(cuò)了...
 UPDATE account SET balance = balance + 500 WHERE NAME = 'lisi';
 
 -- 發(fā)現(xiàn)執(zhí)行沒有問題穆律，提交事務(wù)
 COMMIT;
 
 -- 發(fā)現(xiàn)出問題了惠呼，回滾事務(wù)
 ROLLBACK;

    4. MySQL數(shù)據(jù)庫(kù)中事務(wù)默認(rèn)自動(dòng)提交

      • 事務(wù)提交的兩種方式：
        • 自動(dòng)提交：
          • mysql就是自動(dòng)提交的
          • 一條DML(增刪改)語(yǔ)句會(huì)自動(dòng)提交一次事務(wù)。
        • 手動(dòng)提交：
          • Oracle 數(shù)據(jù)庫(kù)默認(rèn)是手動(dòng)提交事務(wù)
          • 需要先開啟事務(wù)峦耘，再提交
      • 修改事務(wù)的默認(rèn)提交方式：
        • 查看事務(wù)的默認(rèn)提交方式：SELECT @@autocommit; -- 1 代表自動(dòng)提交 0 代表手動(dòng)提交
        • 修改默認(rèn)提交方式： set @@autocommit = 0;
  1. 事務(wù)的四大特征：
    1. 原子性：是不可分割的最小操作單位剔蹋，要么同時(shí)成功，要么同時(shí)失敗辅髓。
    2. 持久性：當(dāng)事務(wù)提交或回滾后泣崩，數(shù)據(jù)庫(kù)會(huì)持久化的保存數(shù)據(jù)少梁。
    3. 隔離性：多個(gè)事務(wù)之間。相互獨(dú)立矫付。
    4. 一致性：事務(wù)操作前后凯沪，數(shù)據(jù)總量不變
  2. 事務(wù)的隔離級(jí)別（了解）

    • 概念：多個(gè)事務(wù)之間隔離的，相互獨(dú)立的买优。但是如果多個(gè)事務(wù)操作同一批數(shù)據(jù)妨马，則會(huì)引發(fā)一些問題，設(shè)置不同的隔離級(jí)別就可以解決這些問題杀赢。

    • 存在問題：

      1. 臟讀：一個(gè)事務(wù)烘跺，讀取到另一個(gè)事務(wù)中沒有提交的數(shù)據(jù)
      2. 不可重復(fù)讀(虛讀)：在同一個(gè)事務(wù)中，兩次讀取到的數(shù)據(jù)不一樣脂崔。
      3. 幻讀：一個(gè)事務(wù)操作(DML)數(shù)據(jù)表中所有記錄滤淳，另一個(gè)事務(wù)添加了一條數(shù)據(jù)，則第一個(gè)事務(wù)查詢不到自己的修改砌左。

    • 隔離級(jí)別：

      1. read uncommitted：讀未提交
        • 產(chǎn)生的問題：臟讀脖咐、不可重復(fù)讀、幻讀
      2. read committed：讀已提交 （Oracle）
        • 產(chǎn)生的問題：不可重復(fù)讀绊困、幻讀
      3. repeatable read：可重復(fù)讀 （MySQL默認(rèn)）
        • 產(chǎn)生的問題：幻讀
      4. serializable：串行化
        • 可以解決所有的問題
      • 注意：隔離級(jí)別從小到大安全性越來越高文搂，但是效率越來越低
      • 數(shù)據(jù)庫(kù)查詢隔離級(jí)別：
        • select @@tx_isolation;
      • 數(shù)據(jù)庫(kù)設(shè)置隔離級(jí)別：
        • set global transaction isolation level 級(jí)別字符串;

    • 演示：

        set global transaction isolation level read uncommitted;
  start transaction;
  -- 轉(zhuǎn)賬操作
  update account set balance = balance - 500 where id = 1;
  update account set balance = balance + 500 where id = 2;

DCL：

  • SQL分類：

    1. DDL：操作數(shù)據(jù)庫(kù)和表
    2. DML：增刪改表中數(shù)據(jù)
    3. DQL：查詢表中數(shù)據(jù)
    4. DCL：管理用戶，授權(quán)

  • DBA：數(shù)據(jù)庫(kù)管理員

  • DCL：管理用戶秤朗，授權(quán)

    1. 管理用戶

      1. 添加用戶：

        • 語(yǔ)法：CREATE USER '用戶名'@'主機(jī)名' IDENTIFIED BY '密碼';

      2. 刪除用戶：

        • 語(yǔ)法：DROP USER '用戶名'@'主機(jī)名';

      3. 修改用戶密碼：

         UPDATE USER SET PASSWORD = PASSWORD('新密碼') WHERE USER = '用戶名';
 UPDATE USER SET PASSWORD = PASSWORD('abc') WHERE USER = 'lisi';
 
 SET PASSWORD FOR '用戶名'@'主機(jī)名' = PASSWORD('新密碼');
 SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123');
        • mysql中忘記了root用戶的密碼煤蹭？

          1. cmd -- > net stop mysql 停止mysql服務(wù)

            • 需要管理員運(yùn)行該cmd

          2. 使用無驗(yàn)證方式啟動(dòng)mysql服務(wù)： mysqld --skip-grant-tables

          3. 打開新的cmd窗口,直接輸入mysql命令憋活，敲回車愿待。就可以登錄成功

          4. use mysql;

          5. update user set password = password('你的新密碼') where user = 'root';

          6. 關(guān)閉兩個(gè)窗口

          7. 打開任務(wù)管理器，手動(dòng)結(jié)束mysqld.exe 的進(jìn)程

          8. 啟動(dòng)mysql服務(wù)

          9. 使用新密碼登錄蛙吏。

      4. 查詢用戶：
        -- 1. 切換到mysql數(shù)據(jù)庫(kù)
        USE myql;
        -- 2. 查詢user表
        SELECT * FROM USER;

        • 通配符： % 表示可以在任意主機(jī)使用用戶登錄數(shù)據(jù)庫(kù)

    2. 權(quán)限管理：

      1. 查詢權(quán)限：

         -- 查詢權(quán)限
 SHOW GRANTS FOR '用戶名'@'主機(jī)名';
 SHOW GRANTS FOR 'lisi'@'%';

      2. 授予權(quán)限：

         -- 授予權(quán)限
 grant 權(quán)限列表 on 數(shù)據(jù)庫(kù)名.表名 to '用戶名'@'主機(jī)名';
     -- 給張三用戶授予所有權(quán)限作谭，在任意數(shù)據(jù)庫(kù)任意表上
     GRANT ALL ON *.* TO 'zhangsan'@'localhost';

      3. 撤銷權(quán)限：

         -- 撤銷權(quán)限：
 revoke 權(quán)限列表 on 數(shù)據(jù)庫(kù)名.表名 from '用戶名'@'主機(jī)名';
 REVOKE UPDATE ON db3.`account` FROM 'lisi'@'%';
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
  • 人面猴
    序言：七十年代末稽物，一起剝皮案震驚了整個(gè)濱河市，隨后出現(xiàn)的幾起案子折欠，更是在濱河造成了極大的恐慌贝或，老刑警劉巖，帶你破解...
    沈念sama閱讀 211,743評(píng)論 6贊 492
  • 死咒
    序言：濱河連續(xù)發(fā)生了三起死亡事件锐秦，死亡現(xiàn)場(chǎng)離奇詭異咪奖，居然都是意外死亡，警方通過查閱死者的電腦和手機(jī)酱床，發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 90,296評(píng)論 3贊 385
  • 救了他兩次的神仙讓他今天三更去死
    文/潘曉璐 我一進(jìn)店門羊赵，熙熙樓的掌柜王于貴愁眉苦臉地迎上來，“玉大人扇谣，你說我怎么就攤上這事昧捷∠姓眩” “怎么了？”我有些...
    開封第一講書人閱讀 157,285評(píng)論 0贊 348
  • 道士緝兇錄：失蹤的賣姜人
    文/不壞的土叔 我叫張陵靡挥，是天一觀的道長(zhǎng)序矩。 經(jīng)常有香客問我，道長(zhǎng)芹血，這世上最難降的妖魔是什么贮泞？ 我笑而不...
    開封第一講書人閱讀 56,485評(píng)論 1贊 283
  • ?港島之戀（遺憾婚禮）
    正文 為了忘掉前任，我火速辦了婚禮幔烛，結(jié)果婚禮上，老公的妹妹穿的比我還像新娘囊蓝。我一直安慰自己饿悬，他們只是感情好，可當(dāng)我...
    茶點(diǎn)故事閱讀 65,581評(píng)論 6贊 386
  • 惡毒庶女頂嫁案：這布局不是一般人想出來的
    文/花漫 我一把揭開白布聚霜。 她就那樣靜靜地躺著狡恬，像睡著了一般。 火紅的嫁衣襯著肌膚如雪蝎宇。 梳的紋絲不亂的頭發(fā)上弟劲，一...
    開封第一講書人閱讀 49,821評(píng)論 1贊 290
  • 城市分裂傳說
    那天，我揣著相機(jī)與錄音姥芥，去河邊找鬼兔乞。 笑死，一個(gè)胖子當(dāng)著我的面吹牛凉唐，可吹牛的內(nèi)容都是我干的庸追。 我是一名探鬼主播，決...
    沈念sama閱讀 38,960評(píng)論 3贊 408
  • 雙鴛鴦連環(huán)套：你想象不到人心有多黑
    文/蒼蘭香墨 我猛地睜開眼台囱，長(zhǎng)吁一口氣：“原來是場(chǎng)噩夢(mèng)啊……” “哼淡溯！你這毒婦竟也來了？” 一聲冷哼從身側(cè)響起簿训，我...
    開封第一講書人閱讀 37,719評(píng)論 0贊 266
  • 萬(wàn)榮殺人案實(shí)錄
    序言：老撾萬(wàn)榮一對(duì)情侶失蹤咱娶，失蹤者是張志新（化名）和其女友劉穎，沒想到半個(gè)月后强品，有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體膘侮，經(jīng)...
    沈念sama閱讀 44,186評(píng)論 1贊 303
  • ?護(hù)林員之死
    正文 獨(dú)居荒郊野嶺守林人離奇死亡，尸身上長(zhǎng)有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點(diǎn)故事閱讀 36,516評(píng)論 2贊 327
  • ?白月光啟示錄
    正文 我和宋清朗相戀三年择懂，在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了喻喳。 大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片。...
    茶點(diǎn)故事閱讀 38,650評(píng)論 1贊 340
  • 活死人
    序言：一個(gè)原本活蹦亂跳的男人離奇死亡困曙，死狀恐怖表伦，靈堂內(nèi)的尸體忽然破棺而出谦去，到底是詐尸還是另有隱情，我是刑警寧澤蹦哼，帶...
    沈念sama閱讀 34,329評(píng)論 4贊 330
  • ?日本核電站爆炸內(nèi)幕
    正文 年R本政府宣布鳄哭，位于F島的核電站，受9級(jí)特大地震影響纲熏，放射性物質(zhì)發(fā)生泄漏妆丘。R本人自食惡果不足惜，卻給世界環(huán)境...
    茶點(diǎn)故事閱讀 39,936評(píng)論 3贊 313
  • 男人毒藥：我在死后第九天來索命
    文/蒙蒙 一局劲、第九天 我趴在偏房一處隱蔽的房頂上張望勺拣。 院中可真熱鬧，春花似錦鱼填、人聲如沸药有。這莊子的主人今日做“春日...
    開封第一講書人閱讀 30,757評(píng)論 0贊 21
  • 一樁弒父案苹丸，背后竟有這般陰謀
    文/蒼蘭香墨 我抬頭看了看天上的太陽(yáng)愤惰。三九已至，卻和暖如春赘理，著一層夾襖步出監(jiān)牢的瞬間宦言，已是汗流浹背。 一陣腳步聲響...
    開封第一講書人閱讀 31,991評(píng)論 1贊 266
  • 情欲美人皮
    我被黑心中介騙來泰國(guó)打工商模， 沒想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留奠旺，地道東北人。 一個(gè)月前我還...
    沈念sama閱讀 46,370評(píng)論 2贊 360
  • 代替公主和親
    正文 我出身青樓阻桅，卻偏偏與公主長(zhǎng)得像凉倚，于是被迫代替她去往敵國(guó)和親。 傳聞我的和親對(duì)象是個(gè)殘疾皇子嫂沉，可洞房花燭夜當(dāng)晚...
    茶點(diǎn)故事閱讀 43,527評(píng)論 2贊 349

推薦閱讀更多精彩內(nèi)容

  • 1. 了解SQL 1.1 數(shù)據(jù)庫(kù)基礎(chǔ) ? 學(xué)習(xí)到目前這個(gè)階段稽寒，我們就需要以某種方式與數(shù)據(jù)庫(kù)打交道。在深入學(xué)習(xí)MyS...
    鋒享前端閱讀 1,047評(píng)論 0贊 1
  • 5.多表查詢 多表查詢 目的：從多張表獲取數(shù)據(jù) 前提：進(jìn)行連接的多張表中有共同的列 等連接 通過兩個(gè)表具有相同意義...
    喬震閱讀 1,212評(píng)論 0贊 0
  • 一趟章、SQL速成 結(jié)構(gòu)查詢語(yǔ)言(SQL)是用于查詢關(guān)系數(shù)據(jù)庫(kù)的標(biāo)準(zhǔn)語(yǔ)言杏糙，它包括若干關(guān)鍵字和一致的語(yǔ)法，便于數(shù)據(jù)庫(kù)元件...
    shadow雨軒閱讀 509評(píng)論 0贊 3
  • 5.DML DML 語(yǔ)句 語(yǔ)句操作語(yǔ)言 INSERT UPDATE DELETE MERGE INSERT 方法:...
    喬震閱讀 931評(píng)論 0贊 0
  • 壹 農(nóng)村的教育 自從看到方華的兩兒子的學(xué)習(xí)方式以后蚓土，陽(yáng)光開始質(zhì)疑農(nóng)村的教育宏侍。陽(yáng)光的兒子，張眾尋7歲蜀漆，二年級(jí)的學(xué)生谅河。...
    小玩具媽媽閱讀 1,076評(píng)論 18贊 17