sql基礎書寫規則和種類

種類

1. DDL：數據定義語言（針對數據庫和表的操作）
   1. create
   2. drop
   3. alter–只針對表
2. DML：數據操作語言（針對于表內部）
   1. select
   2. insert
   3. update
   4. delete
3. DCL：數據控制語言（針對用戶和數據進行的更改）
   1. commit
   2. rollback
   3. grant
   4. revoke

書寫規則

1. 以分號（;）結尾

2. 且不區分大小寫，但為了規范化，須用大小寫

3. 單詞用空格分隔

   一個;叫做一行，這一條用于查詢錯誤

注釋規則

1. 行注釋：'-- '，兩個’-‘加個空格’ ’

   eg：代碼–注釋內容

2. 多行注釋：/* 內容 */

啟動

1. 直接在網址輸入http://localhost:8080 或 http://localhost:8080/phpMyAdmin/

   要求用的是phpMyAdmin的mysql圖形化管理工具

2. 打開“MySQL Command Line Client”

3. cmd中輸入mysql -uroot -p

exit、quit用于退出mysql

啟動|停止mysql服務器：net start|stop mysql

• mysql圖形化管理工具
  • A、[phpMyAdmin](javascript:void(0))
  • B、[SQLyog](javascript:void(0))
  • C、[MySQL Workbench](javascript:void(0))
  • D、[Navicat](javascript:void(0))

DDL

(create,drop alter)

數據類型

• Mysql 的數據類型一般分為以下 5 種，分別是整數類型、浮點數類型和定點數類型、日期和時間類型、字符串類型、二進制類型等，整數類型和浮點數類型可以統稱為數值數據類 型

• 整型類型：tinyint,smallint,mediumint,int(integhr),bigint

• 浮點和定點數類型：float,double,decimal(m,d),dec

• 日期時間型：year,time,date,datetime,timestamp

  date數據的形式會是’2022-04-23’的形式

• 字符串類型：char(),varchar()----var指vary

  tinytext,text,mediumtext,longtext,enum,set

  沒有var類型

  oracle里是varchar2

  text適合存儲文章內容或評論

• 二進制類型：bit(),binary(),varbinary(),varbinary(),tinyblob(),blob(),mediumblob(),longblob()

• 布爾類型：

  boolean

  實際是tinyint（1），用0表示false，1表示true

數據庫的操作

use

• 進入某個表中：use <表名>;

  use lib;
  

  與oracle不同 的是，要先進入表中才能查看訪問表

show

• 顯示數據庫或表：show <表名、數據庫名(兩者都要復數形式)>;

  show tables/databases;
  

  oralce不能這樣做，查看所有表是select * from cat|tab;

create

• 創建數據庫

  1. 命名規則：

     1. 必須以英文字母開頭
     2. 只能使用英文字母，數字，下劃線（_)
     3. 不能重復名稱

  2. 語法：

     ? CREATE { DATABASE | SCHEMA} [ IF NOT EXISTS ] db_name

     [ [ DEFAULT ] CHARACTER SET [ = ] charset_name ]

     [ [ DEFAULT ] COLLATE [ = ] collation_name ];

     create database if not exists xk
     default character set=utf-8
     default collate = utf8_general_ci;
     

drop

• 刪除數據庫：drop database [if exists] <數據庫名>;

  drop database if exists lib;
  

表的操作

use

• 進入某個表中：use <表名>

  use lib;
  

show

• 顯示表或數據庫

  1. show <表名、數據庫名(兩者都要復數形式)>

     show tables|databases;
     

  2. show create table <表名> [\g或\g];

     • \g和\g：改變展示形式

     show create table book;
     show create table book \g;
     show create table book \g;
     

  3. describe/desc <表名>

     describe book;
     desc book;
     

create

創建表

1. 命名規則：

   • 必須以英文字母開頭
   • 只能使用英文字母，數字，下劃線（_)
   • 不能重復名稱
   • 創建表必須要給表設置結構

2. 語法項：

   create table [if not exists] 表名(

   列名1 數據類型1 [是否為空] [完整性約束條件1] [索引]，

   列名2 數據類型2 [是否為空] [完整性約束條件2] [索引]，

   列名3 數據類型3 [是否為空] [完整性約束條件3] [索引]

   [key] [key的名字] (key的標識符) – 若要指定的話

   [index]

   [constraint] (選填key名) foreign key 本表中要設置外鍵的列

   references 父表名(父表的主鍵)

   [on delete {restrict | cascade | set null | no action}]

   [on update {restrict | cascade | set null | no action}]

   )[表引擎 表字符集];

   創建表的所有的集合，情況

   use lib;
   create table book_bk (
   --  列名1 數據類型1 [not null] [default] [primary key] [unique] [auto_increment]，...
   --  [primary key (bookno),]	                   主鍵約束
   --  [unique (bookno),]                         唯一約束
   --  [check(gender in ('男','女')),]             檢查約束
   --  [unique/fulltext] 
   --  [index id_name(字符1(短索引的大小),字符2...  ,]           創建普通、唯組合、全文、短索引
   --  [key fk_book_bookclass (classno),]    定義表的關鍵字/*	constraint 隨便名字 unique (bookno),     創建唯一約束constraint fk_book_bookclass foreign key (classno) references bookclass (classno)       定義外鍵[on delete/update cascade/no action/set null/default/set default/restrict]   實現部分觸發器的功能
   */
   )engine=innodb default charset=utf8 auto_increment=500;	           -- 定義表的存儲引擎和字符集
   

復制表

• create table [if not exists] 新表名

• [ like 參照表名 ] | [as (select 語句)];

  其中創建視圖也是用的as

1. 使用 like 關鍵字創建一個與已有的表相同結構的新表，列名、數據類型、空指定和索引也將復制，但是表的內容不會復制，因此創建的新表是一個空表。

2. 使用 as 關鍵字可以復制表的內容，但索引和完整性約束是不會復制的。

   create table book_bk1 like book;
   create table book_bk2 as select * from book;
   

drop

• 刪除表：刪除表分兩種情況

  1. 刪除沒有被關聯的普通表

     • 直接使用 drop table 語句可以刪除一個或多個數據表

     • 語法格式如下： drop table [if exists] 表名1 [ ,表名 2, 表名 3 …]

       drop table if exists book_bk;
       

     • 注意： 1用戶必須擁有執行 drop table 命令的權限，否則數據表不會被刪除。2表被刪除時，用戶在該表上的權限不會自動刪除。

  2. 刪除與其他表關聯的表。

     1. 先刪除與它關聯的子表，再刪除父表；但是這樣會同時刪除兩個表中的數據。

        • 不需要保留表的數據，則先刪除 book 表，然后再刪除 bookclass 表，在命令行窗口 中輸入如下命令。

          drop table book; drop table bookclass;
          

     2. 將關聯表的外鍵約束取消，再刪除父表；適用于需要保留子表的數據，只刪除父表 的情況。

        • 需要保留子表的數據，則先刪除 book 表中的外鍵，然后再刪除 bookclass 表。請注 意，在 Mysql 中刪除外鍵其實是兩個操作，一是刪除外鍵，二是刪除該外鍵所建的索引。

        alter table book drop foreign key fk_book_bookclass; -- 刪外鍵
        alter table book drop index fk_book_bookclass; -- 刪索引
        drop table bookclass;
        

alter

在 Mysql 中可以使用 alter table 語句來改變原有表的結構，例如增加或刪減列、更改原有列類型、重新命名列或表等。

• 語法格式如下：

  alter table <表名> [修改選項] 
  修改選項的語法格式如下：
  {add {[column] <列名> <類型> [after column|first]  |  [constraint con_name] [foreign key() references col_name() [on delete cascade|set null]] [primary key]...}
  change [column] <舊列名> <新列名> <新列類型> [約束]-- 必須指定數據類型
  -- 相當于換一個新的字段
  alter [column] <列名> { set default <默認值> | drop default } 
  modify [column] <列名> <類型> 
  drop [column] <列名> 
  rename to|as <新表名> 
  character set <字符集名> 
  auto_increment [=n]} 
  engine={ innodb | myisam | memoery }
  

• 例子：

  注：字段指的是表的列

  alter table book rename booknew;-- 修改表名
  -- 也可以直接
  rename table book to booknew;alter table reader character set gb2312;-- 修改表字符集-- 1.開頭增加新字段
  alter table reader add email varchar(100) first;-- 2.中間增加新字段
  alter table reader add email varchar(100) after phonenumber;-- 3.末尾增加新字段
  alter table reader add email varchar(100);-- 修改字段名,約束（要求必須指定新字段的數據類型)
  alter table reader change phonenumber cellphone varchar(12);
  alter table book_bk change column price price float not null;  -- column可以省略-- 修改字段數據類型）
  alter table reader modify readername varchar(20);-- 刪除字段
  alter table reader drop email;-- 修改表的存儲引擎（必須先將相關外鍵約束刪除）
  alter table book drop foreign key fk_book_bookclass;
  alter table book drop index fk_book_bookclass;
  alter table borrow drop foreign key fk_borrow_book;
  alter table borrow drop index fk_borrow_book;
  alter table book engine=innodb;
  alter table book charset=utf8;
  

Mysql的存儲引擎

有：memory、myIsam、InnoDb、Mysql

知道myisam和innodb就好了

其中myisam不支持外鍵

InnoDb才支持外鍵和事務和全文檢索

DML

（select,insert,update,delete）

select

select語句

• 語法格式（書寫順序）：

select [all | distinct] 輸出列表達式, ...   /*加distinct去重*/
/*select 聚合函數*/
[from <表名 1> [ , <表名 2>] …]/*as改變列名，可以放from或select里*/                                          /*from 子句*/
[where 條件]                                  /*where 子句*/
[group by {列名/表達式/列編號}[asc/desc]     /*group by 子句*/
[having 條件]                               /*having 子句*/
[order by {列名/表達式/列編號} desc] , ...] /*order by 子句*/
[limit {[偏移量,] 行數|行數 offset 偏移量}] /*limit 子句*/
select…是語句必須的，方括號里的子句是根據應用需要。

• select語句的執行順序：

  from – where – group by – having – select – order by

like（謂詞）

• ‘_’：表示單個字符

• ‘%’：表示多個或零個字符

  where readerno like '%01''_005'
  

where

• 比較運算：<、>、**不等于’<>'**等

• 模式匹配（like）：like

• 范圍比較：

  • between xxx and xxx
  • in (xxx)
  • 加not

• 空值比較：is [not] null

• 多種查詢條件：and，or，in

  注：and的優先級高于or

聚合函數：

• 有sum，min，max，count，average等

• sum和average不能用于字符串類型的數據，而max/min可以

• 注意區分參數的不同：

  count(*):包含null

  count(列名):不包含null

  只有count(*)這樣的聚合函數包含null

關于null

• null與任何數進行計算都是null

  eg：null / 0 = null

  ? null + 3 = null

• 不能=null的操作，只能is null

• 對含有null的列排序時，null會在最底部

group by

• group by語句中不能使用as別名
• 結果無序
• 聚合鍵的條件寫在where里,若設置的條件是聚合函數時，只能使用having
• 若聚合鍵包含null，則結果也有null
• group by時，select里必須要有括號里的列

exist謂詞

• 用于判斷一個子查詢是否返回了任何結果。如果子查詢返回了至少一行數據，exist返回true；如果子查詢沒有返回任何數據，exist返回false。

• 位于where子句中，通常帶個子查詢且查詢的是*

• 注意：使用的時候是exists 三單形式，加了s

  select *
  from book
  where exists (select * from bookclass where bookclass.classno=book.classno);-- 顯示從未借過書的讀者信息，要求顯示讀者號和姓名。
  select readerno, readername
  from reader
  where not exists (select readerno from borrow where borrow.readerno=reader.readerno);
  

• 在where子句中，我們使用exist來判斷子查詢是否返回了任何數據，如果返回了，則文章會被包含在結果集中。

• 總之，exist是Mysql中用于判斷子查詢是否返回數據的關鍵字，可以用于過濾數據、查詢是否存在等場景。

多表查詢（聯結）

全連接（笛卡爾積）

• 一般沒用

  select *
  from reader,school;
  

內連接

• 使用條件情況：兩個表都包含相同的一列

• 連接方式1：在where 從句中進行連接條件

  主流

  select readername,schoolname
  from reader,school
  where reader.schoolno=school.schoolno;
  -- 或
  select a.readername,b.schoolname
  from reader a,school b
  where a.schoolno=b.schoolno;
  

• 連接方式2：在from 從句中進行join連接某表，條件設置為on

  • 注：on必須在from 和 where 之間
  • from 某表 [inner] join 零一表 on 連接條件

  原始連接伐，和外連接相似，要記住

  select readername,schoolname
  from reader 
  inner join school -- inner可以省略
  on reader.schoolno=school.schoolno
  where gender='男';
  -- 或連接兩個表的情況，此時，把中間的表作為from的表
  select readername,schoolname,borrowno
  from reader 
  join school
  on reader.schoolno=school.schoolno
  join borrow
  on reader.readerno=borrow.readerno
  where gender='男';
  

自連接

• 利用as，將一根表與它自身進行連接，成為自連接

  select a.bookname, a.author, a.publishname
  from book as a, book as b  -- 來自兩個一樣的表，但是給它命兩個名
  where a.author=b.author and a.publishname<>b.publishname;
  

外連接

• 使用外連接里有主表和非主表，有左連接和右連接

  • 主表里一般會有非主表里沒有的東西，也就是說，主表大于非主表，或非主表包含于主表
  • 使用左連接時，主表放左側
  • 使用右連接時，主表放右側
  • 使用外連接時，如有內連接，先內連接后外連接

  **外連接就是將非主表與主表相同的數據正常顯示，然后再將主表的數據除掉非主表的數據加上去，這些數據全是null。**左外連接將左邊的表格作為基礎表格，并將右邊的表格中沒有匹配到的數據也包含在結果中；右外連接則是將右邊的表格作為基礎表格，并將左邊的表格中沒有匹配到的數據也包含在結果中。

  左外連接：顯示所有左表的記錄和匹配的右表記錄，未匹配的右表記錄顯示為null。右外連接：顯示所有右表的記錄和匹配的左表記錄，未匹配的左表記錄顯示為null。

  select bookname,readerno
  from book left outer join borrow  
  -- 此時book表有borrow表中沒有的數據，也就是book表中有沒有借過書的數據，這些數據顯示為null
  on book.bookno=borrow.bookno;
  或
  select bookname,readerno
  from borrow right outer join book
  on book.bookno=borrow.bookno;-- 和
  select reader.readerno,borrow.bookno
  from borrow right outer join reader on  
  -- 此時reader表有borrow表中沒有的數據，也就是reader表中有沒有借過書的讀者，這些數據顯示為null
  reader.readerno=borrow.readerno;-- 還可以，一個外連接，一個內連接
  select book.bookno,reader.readerno,bookname  
  -- select中要設置主表.   不要讓非主表的.   不然可能會有數據沒有的
  from borrow 
  inner join reader on
  reader.readerno=borrow.readerno
  right outer join book on  -- outer也可以省略
  book.bookno=borrow.bookno
  order by bookno;
  

union

(并集，表的加法，其結果是第二個表的內容直接加到第一個表下面)

• 自動去重

• 列數必須相同且列的類型要一致

• order by只能使用一次

  select bookname
  from book
  union
  select readername
  from reader
  

• 不去重：union 后加個 all

  select bookname
  from book
  union all
  select readername
  from reader
  

子查詢

子查詢的返回結果：

• 一個表
• 一個值
• 一列數據

普通子查詢：

• 指的是返回多行數據的查詢

• 把where的條件換成一個select查詢語句

select bookname, price
from book
where price > (select avg(price) from book);-- 這里用了子查詢

• 注意：此種情況下不能用=和<>，只能用in，否則會報錯：

  subquery returns more than 1 row
  子查詢返回多于一行
  

標量子查詢

• 指的是只能返回一行一列的數據
• 如何做到？
  • 用聚合函數，這樣返回的結果只有一行
• 有什么用？
  • 在此等此種情況下它的返回值可以用=或者<>

10. 流程控制函數

1. if 判斷，流程控制，語法結構如下：

   注：這個是寫在select的列里，對其中一個列進行相應的判斷

   if(expr,v1,v2)-- 當expr為真是返回v1的值，否則返回v2。
   select readername 姓名,if(gender='女',0,1) 性別 from reader limit 10;  -- 在第三列性別中進行判斷，把他的值邊為后面的結果ifnull(字符v1,v2);
   -- 其中：如果 字符v1 不為 null，則 ifnull 函數返回 字符v1; 否則返回 v2 的結果。
   

2. case 搜索判斷語句，語法如下：

   Mysql不允許在 then 或 else 子句中使用空的命令。

   case和if都會寫在設置條件的列上

   casewhen <條件1> then <命令>else <命令>
   end case;
   -------------------------------------
   select bookno 書號,bookname 書名,case when number=0 then '零本'when number=1 then '孤本'when number>1 and number<6 then '少量' else '大量'end as 數量
   from book limit 10;  
   

insert

• 語法

列名 1~n 和值 1~n 的順序要一一對應。

注：value要用三單values

insert可以不帶into，但delete要帶from

insert into <表名> (列名 1, 列名 2,…, 列名 n) values (值 1, 值 2,…,值 n);

• 實例

-- 插入多行數據
insert school_bk1 (schoolno, schoolname)
values
('s001', '人工智能學院'),
('s002', '電子通信學院'),
('s003', '機電工程學院'),
('s004', '計算機學院');-- insert語句中不指定具體的字段名
insert into school
values ('s017','師范學院');-- 為指定列插入數據
insert into school(schoolno, schoolname)
values ('s017','師范學院');-- 插入null
insert into school(schoolno, schoolname)
values ('s017',null);  -- 要求schoolname不能設置not null-- 通過顯示方式插入默認值
insert into school(schoolno, schoolname)
values ('s017',default);-- 該默認值來自表結構的設置-- 通過隱形方式插入默認值
insert into school(schoolno)
values ('s017'); -- 省略要使用默認值的行和列-- 從其他表中復制數據
insert into school(schoolno, schoolname)
select *
from school
where schoolno='001';

update

update就是先delete后insert

• 語法

update <表名> set 字段 1=值 1 [,字段 2=值 2… ] [where 子句 ]
[order by 子句]
[limit 子句]

• 實例

-- 按本表中的條件修改表中數據
update school
set schoolname='化生學院'
where schoolno='s007';--  按內連接修改表中數據
update reader, school
set effectdate='2019-04-28 00:00:00'
where reader.schoolno=school.schoolno
and schoolname='人工智能學院';
或
update reader
set losteffectdate='2022-04-27 00:00:00'
where schoolno=(select schoolno from school where schoolname='人工智能學院');-- 使用null進行更新
update school
set schoolname=null  -- 不能not null
where schoolno='s007';-- 多列更新
update book
set bookname='化生學院',onshelf='否'
where bookno='b001';

delete

• 語法

delete from <表名> [where 子句] [order by 子句]
[limit子句]
truncate [table] table_name;

from不能省去

• drop和delete區別

drop table 語句可以將表完全刪除
delete 語句會留下表（容器），而刪除表中的全部數據

delete 語句的刪除對象并不是表或者列，而是記錄（行）

• 實例

 -- 按本表中的條件刪除表中數據
delete from school
where schoolname='人工智能學院';
-- 注：如果刪除表的內容涉及和其他表關聯，那必須要先刪除子表才能刪除父表-- 按其他表中條件刪除表中數據
刪除讀者表中屬于“人工智能學院”的讀者信息。
-- 1.須先刪除子表
delete borrow
from reader,school,borrow
where reader.schoolno=school.schoolno
and reader.readerno=borrow.readerno 
and schoolname='人工智能學院';
-- 1.須先刪除子表
delete from borrow
where readerno in  -- 用in因為這不是標量子查詢，返回的是多行結果
(select readerno from school ,reader
where schoolname='人工智能學院'
and school.schoolno=reader.schoolno);
-- 1.須先刪除子表
delete from borrow
where readerno in 
(select readerno 
from reader
where schoolno = (select schoolno from school where schoolname='人工智能學院')
);-- 2.再刪除父表
delete reader
from reader, school
where reader.schoolno=school.schoolno
and schoolname='人工智能學院';

注意區分DDL和DML的語法規則

1. DDL（show,create,drop alter)

   基本結構都是

   (DDL的語句) table/database <表名/數據庫名> -- [若是alter，才有這之后的內容]
   -- 注：中間就是table和database，不是名字
   

2. DML(select,insert,updatedelete)

   基本結構就是

   (DML語句) <表名> [相應的操作]
   insert (into) <表名> (列名1,2) values (值1，值2),(值3,值4)
   update <表名> set 列名='' [where條件]
   delete from <表名> [where條件]
   

數據建模

概念

數據建模概念

• 數據建模是指根據用戶的實體模型需求，設計出概念模型，此階段為邏輯結構設計階段。 e-r 模型表示的概念結構模型獨立于任何一種數據模型，并獨立于任何一個具體的數據庫管理系統。 概念結構設計階段設計的 e-r 模型將轉換為關系數據模型二維表結構。
• 數據建模后，就 可以在某一具體的數據庫管理系統上，設計出數據庫以及表的結構和建立真實可用的數據庫和表。

數據庫設計過程：需求收集和分析、概念設計、邏輯設計、物理設計

關系模型概述

• 關系模型是目前數據庫系統普遍采用的數據模型，也是應用最廣泛的數據模型。關系模型通過二維表表示實體以及實體之間的聯系。二維表并不一定是關系模型，只有具備下列特 點的二維表才是關系模型：
• （1）表格中的每一列都是不可再分的基本數據項；
• （2）列和列之間的名字不同，每一列的數據類型相同或者兼容
• （3）行和行的順序無關，列和列的順序也無關
• （4）關系中不能存在完全相同的兩行

二維表來表示實體間的聯系

實體模型

• 實體是現實世界中存在的并可相互區別的事物或概念。實體模型對應的是“現實世界”
• 實體可以是具體的人、事和物，也可以是抽象的概念或聯系，其中的一個具體事物稱為 實例。這些事物的集合稱為事物類，是由具有相似類型的事物，具有共同的性質。如圖書館 管理系統中的“書籍”是一個事務類；“讀者”也是一個事務類。除此之外，還有業務活動， 例如書籍和讀者之間發生“借閱”現象
• 一個事物可以擁有多種特性來形容自己。如“書籍”，具有書名、作者、出版社等共同 的性質；“讀者”，具有姓名、性別、電話等共同的性質
• 此階段是分析項目涉及的業務活動和數據的使用情況，弄清所用數據的種類、范圍、數 量以及在業務活動中的存儲情況，確定用戶對數據庫系統的使用要求和各種約束條件等

概念模型

• 概念模型是現實世界在人們頭腦中的反映，是對客觀事物及其聯系的一種抽象描述，從 而產生概念模型。概念模型對應的是“信息世界”，是按用戶的觀點對數據庫建模

• 概念模型是現實世界到數據世界必然經過的中間層次，使用相關信息化概念和技術對現 實世界進行抽象；抽象的結果可立即轉化為相應的數據模型，在具體的計算機系統上進行實施部署。

  所謂概念模型，就是現實世界到機器世界的一個中間層次，即信息世界

• 在概念模型下，有如下術語：

  • 實體（entity）：是現實世界中的一個對象或概念，如圖書館管理系統中的書籍和讀者 等。

  但是姓名不能稱為實體？？

  • 屬性（attribute）：是進一步描述實體的某個具體性質，例如書籍的書名、作者等。如 果某個屬性或者屬性組合的值能唯一地標識出實體集中一行，則可以選擇該屬性或者屬性組 合作為實體標識符。
  • 聯系（relationship）：表示實體之間存在的相互作用，例如表示書籍實體與讀者實體 之間讀者借閱的聯系。
  • 具有相同屬性的實體的集合稱為實體集。在同一實體集中，每個實體的屬性及其值域是 相同的，但可能取不同的值。

數據模型

• 數據模型是數據特征的抽象，描述了系統的靜態特征、動態行為和約束條件，為數據庫 系統的信息表示與操作提供了一個抽象的框架。
• 數據模型對應的是“數據世界”。數據模型，按計算機系統的觀點對數據建模。常見的 數據模型種類有網狀模型、層次模型、關系模型和面向對象數據模型。
• 數據模型的文件、記錄和數據項，是數據庫系統真實保存數據的方式。分別對應著概念 模型的實體集、實體和屬性。
• 數據模型三要素：數據結構、數據操作和完整性約束。
• 數據結構：用于描述系統的靜態特征。
• 數據操作：指對數據庫中數據允許執行的操作的集合，用語言描述系統的動態特征。數 據模型必須定義操作的確切含義、操作符號、操作規則以及實現操作的方法。
• 完整性約束：是完整性規則的集合，指給定的數據模型中數據及其聯系所具有的制約和 依存規則

關系模型

• 關系模型是應用最廣的數據模型，是用一組關系來描述數據庫。通俗地說，關系就是一 張表格，用“二維表”來表示數據之間的聯系。

• 關系數據庫是將數據表示為表的集合，通過建立簡單表之間的聯系來定義結構的一種數 據庫。

• 關系模型術語：

  • 關系（relation），數據庫可以由多個表組成，表與表之間可以以不同的方式相互關聯。

    在關系模型中，關系指的是矩形，即實體

  • 元組（tuple），表中的行稱為元組。一行是一個元組，對應存儲文件中的一個記錄值。

  • 屬性（attribute），表中的列稱為屬性，每一列有一個屬性名。

  • 域（domain），表中的垂直列，也稱為屬性。屬性的取值范圍，即不同元組對同一屬性 的取值所限定的范圍,稱為域的規格。

  • 分量：元組的某個屬性值。在一個關系數據庫中，它是一個操作原子，即關系數據庫在 做任何操作的時候，屬性是“不可分的”。

  • 碼：表中可以唯一確定一個元組的某個屬性（或者屬性組），如果這樣的碼有不止一個， 都叫候選碼，從候選碼中挑一個出來做主屬性，它就叫主碼。

  • 全碼：如果一個碼包含了所有的屬性，這個碼就是全碼。

  • 主屬性：一個屬性只要在任何一個候選碼中出現過，這個屬性就是主屬性。

  • 非主屬性：與上面相反，沒有在任何候選碼中出現過，這個屬性就是非主屬性。

  • 外碼：一個屬性（或屬性組），它不是碼，但是它別的表的碼，它就是外碼。

  • 關系模式（relation schema）：對關系的描述，記為 r(a1，a2，…， an)，是由關系 名和一組屬性構成。

• 關系數據庫術語：

  • 表（table），是一種按行與列排列的相關信息的邏輯組，類似于工作單表。
  • **字段（field），數據庫表中的每一列稱作一個字段。**表是由其包含的各種字段定義的， 每個字段描述了它所含有的數據。創建一個數據庫時，須為每個字段分配一個數據類型、最 大長度和其它屬性。字段可包含各種字符、數字甚至圖形。
  • 記錄（record），各個有關的信息存放在表的行，被稱為記錄。一般來說，數據庫表創 建時任意兩個記錄都不能相同。
  • 數據庫（database），多個相關聯的數據表的集合

數據字典

? 數據字典（data dictionary）是描述數據的信息集合，是對系統中使用的所有數據元素的定義的集合。數據字典有利于將定義的數據結構等信息在程序員和其他需要參考的人之間共享。通過數據建模產生E-R圖，對每個數據對象給出描述性名字，并對關系也進行描述，然后描述數據的類型（例如文本還是圖像，或者是二進制數值），列出所有可能預先定義的數值，并提供簡單的文字性描述。這樣的集合被組織成特定的形式作為，就叫做數據字典。

? 數據字典通常包括：

• （l）數據項；
• （2）數據結構；
• （3）數據流；
• （4）數據存儲；
• （5）處理過程五個部分。

其中數據項是數據的最小組成單位，若干個數據項可以組成一個數據結構。數據字典通過對數據項和數據結構的定義來描述數據流和數據存儲的邏輯內容。

實體聯系（e-r）圖

• （1）矩形：表示實體，矩形內要標注實體的名稱；

  關系模型中，關系就是這里的矩形（實體）

• （2）菱形：表示實體之間的聯系，菱形內標注聯系的名稱；

• （3）橢圓：表示實體和聯系所具有的屬性，橢圓內標注屬性的名稱。如果屬性較多， 也可以將實體與其相應的屬性另外單獨用列表表示。

• （4）連線：用來連接實體與實體所具有的屬性、聯系與聯系所具有的屬性，以及實體 與聯系。連線上要標出聯系類型。

依賴關系

函數依賴

指的是一個屬性或屬性組決定了另一個屬性或屬性組的取值，這個決定關系就是函數依賴。例如，如果關系模式r（a，b，c）中a→b，那么b的值由a的值 。

函數傳遞依賴

指在一個關系模式中，如果存在非主屬性與主屬性之間的依賴，就稱為函數傳遞依賴。簡單來說，如果關系模式r（a，b，c）中a→b，b→c， ，就存在函數傳遞依賴。

范式定義

第一范式(1nf)

第一范式是指在關系模型中，數據庫表的每一列都是不可分割的原子數據項。簡而言之，第一范式就是無重復的原子域。

第二范式(2nf)

• 本質：一山不容二虎，只允許存在一個主鍵

• 候選鍵：半個主鍵

• 候選鍵 >= 主鍵

• 候選鍵是指能夠唯一標識表中每條記錄的一個或多個屬性組合，每個表可以有多個候選鍵，但最終只能選擇一個作為主鍵。

• 第二范式在滿足第一范式的基礎上，要求關系中的每個非主屬性完全依賴于候選鍵，即每個非主屬性都必須完全依賴于候選鍵，而不能依賴于候選鍵的局部子集，此時第二范式中** **。

  /* 例如
  "訂單”關系，包含:1訂單號、訂單日期、2客戶編號、客戶名稱、客戶地址、客戶電話、3產品編號、產品名稱、產品單價、產品數量等屬性 */
  -- 其中訂單號是主鍵，客戶編號和產品編號是候選鍵
  從2看客戶編號 能決定 （客戶名稱、客戶地址、客戶電話）
  -- 這就是每個非主屬性完全依賴于候選鍵
  從3看產品編號 能決定 （產品名稱、產品單價、產品數量）
  -- 這就是內部的內部不能存在函數傳遞依賴
  從1看訂單號 能決定 客戶編號和產品編號
  而這兩個又能決定其他的關系
  -- 這就是主鍵能存在函數傳遞依賴，而除去主鍵的候選鍵不能存在函數傳遞依賴
  

  讀者（讀者編號，讀者姓名，讀者性別，聯系電話，學院編號，學院名稱，生效日期，失效日期，違規情況，累借次數）
  -- 也是屬于第二范式
  因為有讀者編號--學員編號--學院名稱 這個函數傳遞依賴書籍（圖書編號，分類編號，圖書分類，圖書名稱，作者，出版社，出版時間，簡介，是否在架，剩余冊數，單價）
  -- 也是屬于第二范式
  

第三范式(3nf)

第三范式進一步要求在滿足第二范式的基礎上，消除非主屬性對于其它非主屬性的傳遞依賴關系，即一個非主屬性不能依賴于另一個非主屬性（候選鍵），連主鍵都沒有函數傳遞依賴

讀者（讀者編號，讀者姓名，讀者性別，聯系電話，學院編號，生效日期，失效日期，違規情況，累借次數）
學院（學院編號，學院名稱）
書籍（圖書編號，分類編號，圖書名稱，作者，出版社，出版時間，簡介，是否在架，剩余冊數，單價）
圖書分類（分類編號，圖書分類）
-- 就是把主鍵存在函數依賴關系的候選鍵給提出來，和上面好好對比下

總結

• 第一范式（1nf）要求所有屬性都是原子屬性，即屬性的不可分割性；
• 第二范式（2nf）要求屬性完全依賴于主關鍵字，即主關鍵字和非主關鍵字之間不存在部分依賴關系；
• 第三范式（3nf）要求任何一個非主屬性都依賴于主關鍵字，而不是依賴于其他非主屬性。
• 第二范式和第三范式的區別：
  • 第二范式中在主鍵上能存在函數傳遞依賴，而在非主鍵的候選鍵中不存在函數傳遞依賴
  • 第三范式中全體關系都不存在函數傳遞依賴關系，即每個非主屬性只能通過主屬性獲得，且大多數不存在候選鍵

反范式化

? 所謂反范式化，是一種對范式化設計的數據庫的性能優化策略，通過在表中增加冗余或重復的數據來提供數據庫的讀取性能。

? 沒有冗余的數據庫不一定是最好的數據庫，有時為了提高查詢效率，就必須降低范式標準，適當保留冗余數據。比較典型的操作是在一個表中增加另一個表的冗余字段，通過減少兩個表查詢時的關聯，從而提高查詢效率。還有一種常用方式是增加派生字段，該字段的值通過從其他表中的數據計算生成，其作用是在查詢時減少計算量，從而加快查詢速度，特別是把數據量大的表中的數據計算后放到數據量小的表中以供查詢，對提升性能的作用更加明顯。常見的反規范化技術還包括重新組表（如果許多用戶需要查看兩個表連接出來的結果數據，則把這兩個表重新組成一個表來減少連接從而提高性能）、分割表（包括水平分割、垂直分割）等。

? 反范式化設計本質上就是用空間來換取時間，把數據冗余在多個表中，當查詢時可以減少或者是避免表之間的關聯或計算。RDBMS模型設計過程中，常常使用范式約束關系模型，但在NOSQL模型中則通常大量采用反范式。

• 1）反范式化的優點

  • l 可以避免關聯查詢

  • l 可以設計更有效的索引

  • l 可以提高查詢速度

• 2）反范式的缺點

存在數據冗余和數據維護異常

將 e-r 圖轉換為關系模型

一對一

1：1

（1:1）聯系的 e-r 圖轉換：

• 方法一：各實體的之間的聯系單獨成立一個關系

  將參與聯系的各表/實體集中的主鍵拿出來成立一個關系模式，其中它的主鍵可以是參與聯系的任何一方的主鍵

  • bj（班級編號，院系，專業名，人數）
  • bz（學號，姓名）
  • sy（學號，班級編號）或（學號，班級編號）

• 方法二：聯系不單獨構成關系模式

  不建立新的關系模式，而直接將一方的主鍵直接加入另一方實體集

  • bj（班級編號，院系，專業名，人數）
  • bz（學號，姓名，班級編號）
  • 或者
  • bj（班級編號，院系，專業名，人數，學號）
  • bz（學號，姓名）

一對多

1：n

（1:n）聯系的 e-r 圖轉換：

• 方法一：各實體的之間的聯系單獨成立一個關系

  將參與聯系的各表/實體集中的主鍵拿出來成立一個關系模式，其中它的主鍵只能是n方的主鍵

  • bj（班級編號，院系，專業名，人數）
  • xs (學號，姓名，專業名，性別，出生時間，總學分，備注) – n方
  • sy（學號，班級編號）

• 方法二：聯系不單獨構成關系模式

  不建立新的關系模式，而直接將1主鍵直接加入n中，且主碼是n方

  • bj（班級編號，院系，專業名，人數）
  • xs (學號，姓名，專業名，性別，出生時間，總學分，備注，班級編號)

  如：宿舍和學生的關系

多對多

n：m

（m:n）聯系的 e-r 圖轉換：

• 必須單獨對應一個關系模式， 主碼由各實體集的主碼屬性共同組成。
  • xs(學號，姓名，專業名，性別，出生時間，總學分，備注)，
  • kc（課程號，課程名稱，類別，開課學期，學時，學分），
  • xs_kc (學號，課程號，成績)。

總結就是：將 e-r 圖轉換為關系模型----> 關系規范化

數據完整性

數據完整性是指數據庫中的數據在邏輯上的：

• 一致性
• 正確性
• 有效性
• 相容性

概念

概念及分類

在Mysql中，約束是指對表中數據的一種約束，使用約束來限定表中的數據范圍是很有必要的。

數據完整性分類	數據完整性實現方式
實體完整性	主鍵約束primary key
域（列）完整性	唯一約束unique 默認值約束default 非空約束NULL
參照完整性	外鍵約束references
用戶自定義完整性	檢查約束check

注：主鍵約束和唯一約束的區別：相似的地方，就是它們都能夠確保列的唯一性。不同的地方，唯一約束可以為空，并且唯一約束在一個表中可以有多個

以上6種約束中，一個數據表中只能有一個主鍵約束，其它約束可以有多個。

• 1、實體完整性

  實體完整性是指關系中的主屬性值不能為Null且不能有相同值，即定義表中的所有行具有唯一標識，一般用主鍵。

• 2、域完整性

  域完整性是對數據表中字段屬性的約束，通常指數據的有效性，它包括字段的值域、字段的類型及字段的有效規則等約束，它是由確定關系結構時所定義的字段的屬性決定的。通過限制數據類型、缺省值、自定義規則、是否可以為空等，域完整性可以確保不會輸入無效值。

• 3、參照完整性

  參照完整性是對建立關聯關系的數據表間數據參照引用的約束，也就是對外鍵的約束。準確地說，參照完整性是指關系中的外鍵必須是另一個關系的主鍵有效值，或者是NULL。參考完整性維護表間數據的有效性、完整性，通常通過建立外鍵聯系另一表的主鍵實現。

主鍵約束

（實體完整性）

主鍵本質上是一個排序，即索引。

• 使用主鍵應注意以下幾點：

  • 每個表只能定義一個主鍵。

  • 主鍵值必須唯一標識表中的每一行，且不能為null，即表中不可能存在有相同主鍵值的兩行數據。這是唯一性原則。

  • 一個字段名只能在聯合主鍵字段表中出現一次。

  • 聯合主鍵不能包含不必要的多余字段。當把聯合主鍵的某一字段刪除后，如果剩下的字段構成的主鍵仍然滿足唯一性原則，那么這個聯合主鍵是不正確的。這是最小化原則。

• 創表設置

-- 1.
create table school_bk (...primary key (schoolno,schoolname)
) 
-- 2.
create table school_bk (schoolno char(4) not null primary key;
)

• 添加/刪除表

alter table bookclass_bk add primary key(classno);alter table bookclass_bk drop primary key;

1. 自增長主鍵

在Mysql中，當主鍵定義為自增長后，這個主鍵的值就不再需要用戶輸入數據了，而由數據庫系統根據定義自動賦值。每增加一條記錄，主鍵會自動以相同的步長進行增長。

• auto_increment關鍵字，可以在添加一條記錄時，自動為該行添加數值，而且是自動增長的，一般被作為主鍵。通過給字段添加 auto_increment 屬性來實現主鍵自增長。語法格式如下：

  字段名 數據類型 auto_increment;
  

• 默認情況下，auto_increment 的初始值是1，每新增一條記錄，字段值自動加1。

• 一個表中只能有一個字段使用auto_increment 約束，且該字段必須有唯一索引，以避免序號重復（即為主鍵或主鍵的一部分）。

• auto_increment約束的字段必須具備 not null 屬性。

• auto_increment約束的字段只能是整數類型（tinyint、smallint、int、bigint 等）。

• auto_increment約束字段的最大值受該字段的數據類型約束，如果達到上限，auto_increment就會失效。

create table borrow (
borrowno int(11) not null auto_increment
)auto_increment=500;  -- 設置了自增長的初始值為500

外鍵約束

（參照完整性）

外鍵約束定義了表之間的關系，能夠保證數據的完整性和一致性

在關系數據庫中，外鍵是：在一個關系中的一個或一組屬性是另一個關系的主碼

外鍵約束也可以通過觸發器來做

• 概念

  • 外鍵約束用于建立主鍵所在的表（主表）和外鍵所在的表（從表）之間的數據聯系。可以將表中主鍵值的一列或多列添加到另一個表中創建兩個表之間的連接。

  • 注意：從表的外鍵列名必須是主表的主鍵列名，且在刪除主表時必須刪除外鍵約束或者先刪除從表的數據（記錄），再刪除主表的數據（記錄）。

  • 一個表可以有一個或多個外鍵，外鍵可以為空值，若不為空值，則每一個外鍵的值必須等于主表中主鍵的某個值。

• 創表設置

  • [constraint <外鍵名>] foreign key 字段名 [，字段名2，…]

  • references <主表名> 主鍵列1 [，主鍵列2，…]

  • [on delete {restrict | cascade | set null | no action}]

  • [on update {restrict | cascade | set null | no action}]

“on delete”和“on update”可以為每個外鍵定義指定動作。指定動作包含兩部分， 第一部分指定這個指定動作由什么觸發，是update或是delfte語句；第二部分指定接下來采取什么行動，例如restrict、cascade、set null、no action和set default。

• restrict：當要刪除或更新父表中被參照列上在外鍵出現的時候，拒絕對父表的刪除或更新操作。

  若啥都沒指定，則默認項時restrict

• cascade：從父表刪除或更新行時，自動刪除或更新子表中匹配的行。

  當設置了cascade時，默認的restrict也就沒了，也就不需要先把子表刪了在動父表了

• set null：當從父表刪除或更新時，設置子表中與之對于的外鍵列為null。如果外鍵列沒有指定not null限定，這就是合法的。

• no action：意味著不采取動作，即如果有一個相關的外鍵值在被參考的表里，刪除或更新父表中該值將不被允許，應用和restrict一樣。

• set default：作用和set null一樣，只不過set default是指定子表中的外鍵列為默認值。如果沒有指定動作，兩個參照動作就會默認地使用restrict命令。

Mysql參照完整性約束目前只可以用在那些使用innodb存儲引擎創建的表中，對于其他類型的表。Mysql服務器能夠解析create table語句中的foreign key語法，但不能使用或保存它。

注意：從表的外鍵關聯的必須是主表的主鍵，且主鍵和外鍵的數據類型必須一致。例如，兩者都是int類型，或者都是char類型。如果不滿足這樣的要求，在創建從表時，就會出現錯誤。

create table reader_bk (
...
key fk_reader_bk_school (schoolno), 
constraint fk_reader_bk_school
foreign key (schoolno) 
references school (schoolno)
on delete cascade
on update cascade
)engine=innodb default charset=utf8;

• 添加和刪除

-- 添加
alter table <數據表名> add constraint <外鍵名>
foreign key(<列名>) references <主表名> (<列名>);
-- eg
alter table borrow add constraint fk_book_bookclass 
foreign key (bookno) references book(bookno) 
on update cascade;-- 刪除
alter table <表名> drop foreign key <外鍵約束名>;
-- eg
alter table book drop foreign key fk_book_bookclass;
alter table book drop index fk_book_bookclass;

唯一約束

（域完整性）

創建唯一約束會自動創建唯一索引

• 概念

  • unique約束，是對列的限制。因為有時一張表需要多個列唯一，而一張表又只能由一個主鍵，所以可以設置唯一約束來實現，一張表可以設置多個唯一約束。

  • Mysql唯一約束（unique key）是指所有記錄中字段的值不能重復出現。

  • 唯一約束與主鍵約束相似的是它們都可以確保列的唯一性。不同的是，唯一約束在一個表中可設多個列，并且設置唯一約束的列允許有空值，但是只能有一個空值。而主鍵約束在一個表中只能有一個，且不允許有空值。

針對列而言的

• 創表設置

-- 語法格式如下：
<字段名> <數據類型> unique;
-- eg
create table school_bk (
...
schoolname varchar(20) unique not null,
)

• 添加和刪除

-- 添加
alter table <數據表名> add constraint <唯一約束名> unique(列名);
-- eg
alter table reader_bk add constraint uni_phone unique(phonenumber);-- 刪除
alter table <表名> drop index <唯一約束名>;
-- eg
alter table reader_bk drop index uni_phone;

檢查約束

（用戶自定義完整性）

檢查約束是用來檢查數據表中，字段值是否有效的一個手段。例如，圖書表中的價格字段是沒有負數的，一般應該大于“0”。在設置字段的檢查約束時要根據實際情況進行設置，這樣能夠減少無效數據的輸入；在讀者表中，性別字段，可以設置檢查約束為“男”或“女”，輸入其他的字符為無效。

-- 創建
check <表達式>
-- eg
create table reader (
...
check(gender in (‘男’,‘女’)), -- 或check(gender='男‘ or gender='女')),
)-- 添加
alter table <數據表名> add constraint <檢查約束名> check(<檢查約束>)
-- eg
alter table reader add constraint ck_gender check(gender in ('男','女'));-- 刪除
alter table <數據表名> drop constraint <檢查約束名>;
-- eg
alter table reader drop constraint ck_gender;

默認值約束

（域完整性）

-- 創建
publishname varchar(20) default null,
onshelf char(2) default '是',-- 添加
alter table book change column onshelf char(2) default '是';-- 刪除
alter table book change column onshelf onshelf char(2) default null;

非空約束

（域完整性）

-- 創建
publishname varchar(20) not null-- 修改
alter table book_bk change column price price float not null;-- 刪除
alter table book_bk change column price price float null;

索引

概念

含義及特點：

• 本質：排序
• 在關系數據庫中，索引是為了加速對表中數據行的檢索而對數據庫表中一列或多列的值進行排序的一種存儲結構，它是某個表中一列或若干列值的集合和相應的指向表中物理標識這些值的數據頁的邏輯指針清單。
• 索引是針對表而建立的，它是由數據頁面以外的索引頁面組成的，每個索引頁面中的行都會含有邏輯指針，以便加速檢索物理數據。

索引的優點：

• 大大加快數據的檢索速度;

• 創建唯一性索引，保證數據庫表中每一行數據的唯一性;

• 加速表和表之間的連接;

• 在使用分組和排序子句進行數據檢索時，可以顯著減少查詢中分組和排序的時間。

索引的缺點：

• 索引需要占物理空間。

• 每當對表中的數據進行增加、刪除和修改的時候，索引也要動態的維護，降低了數據的維護速度。

  頻繁更新的字段不適合創建索引

索引的分類：

• 普通索引：是最基本的索引，它沒有任何限制。

• 唯一索引：與普通索引類似，不同的就是：索引列的值必須唯一，但允許有空值。如果是組合索引，則列值的組合必須唯一。

• 主鍵索引：是一種特殊的唯一索引，一個表只能有一個主鍵，不允許有空值。一般是在建表的時候同時創建主鍵索引。

• 組合索引：指多個字段上創建的索引，只有在查詢條件中使用了創建索引時的第一個字段，索引才會被使用。使用組合索引時遵循最左前綴集合，因此組合索引的字段先后順序十分關鍵，直接決定了sql在查詢中是否能使用到該索引。

• 全文索引：在前面的學習中，通過數值比較、范圍過濾等方法就可以完成絕大多數的文本查詢，但是，如果希望通過關鍵字的匹配來進行查詢過濾，那么就需要基于相似度的查詢，而不是原來的精確數值比較。

  全文索引對某些類型的字段，創建不了

• 空間索引：使用spatial參數，Mysql在7之后的版本支持了空間索引，主要支持opengis幾何數據模型，初學者一般用不到。

各索引的實現

區分索引和約束：

• 索引是帶有index 的要刪除創建都得有它
• 約束是帶有constraint 的，一般創立、添加時都有它

-- ------------------- 創表時創立
-- 普通索引
index id_schoolname(schoolname)
-- 唯一性索引
unique index id_schoolname(schoolname)
-- 組合索引
index id_book(bookname,author,publishname)
-- 全文索引
fulltext index id_introduction(introduction)
-- 短索引 (特征：里面有括號規定大小)
index id_introduction(introduction(10))-- ------------------- 已存在的表上添加、刪除索引
create [unique | fulltext | spatial] index [索引名] on 表名 (字段名1 [(長度)] [asc | desc])；
-- 刪除索引
drop index id_schoolname on school_bk;
-- 普通索引
create index id_schoolname on school_bk(schoolname);-- 在某列上創建索引
-- 唯一性索引
create unique index id_schoolname on school_bk(schoolname);
-- 組合索引
create index id_book on book_bk(bookname,author,publishname);
-- 對于確認組合索引的使用效果
1. explain select * from book_bk where publishname=’aaa’ and author=’bbb’;  -- 上述查詢沒有使用到id_book的索引，僅僅使用了條件查詢。
2. explain select * from book_bk where bookname=’aaa’ and author=’bbb’;  -- 上述查詢正確使用了id_book的索引，可見只有在查詢條件中使用了創建索引時的第一個字段，索引才會被使用。
3.explain select * from book_bk where bookname=’aaa’ and publishname=’bbb’;  -- 上述查詢也使用了id_book的索引，但只用到了查詢條件中的第一個字段，而查詢條件中的第二個字段是使用了索引下推功能，可見查詢條件中字段的先后順序十分關鍵，直接決定了sql在查詢中是否能使用到該索引。-- 全文索引
create fulltext index id_introduction on book_bk(introduction);
-- 短索引
create index id_introduction on book_bk(introduction（10）);-- ---------------------修改表結構的方式添加索引
alter table 表名 add [unique | fulltext] [index] [索引名] 列名[(長度)] [ asc | desc])；
-- eg
alter table school_bk add index ...  -- 同上，這里不在敘述drop index id_xxx;

視圖

概念

視圖是什么？

從sql的角度來看視圖就是一張表

視圖來自哪里？

視圖是一個虛擬表，是從數據庫中一個或多個表中導出來的表，其內容由查詢定義。所以一旦表數據變化，視圖數據也會變

視圖和表有什么不同？

是否保存了實際數據

視圖的優點

1. 無需保存數據，可以節省存儲設備的容量
2. 頻繁使用可以調用，大大提高效率
3. 簡單性、安全性、邏輯數據獨立性

表中存儲的是實際數據，而視圖中保存的是從表中取出數據所使用的select語句

應該將經常使用的select語句做成視圖

實現

• 語法格式如下：

create [or replace] view視圖名[(屬性清單)]

as

select語句

[with [cascaded | local] check option];

oracle里沒有cascaded和local，但又with read only只讀

語法說明如下。

• 視圖名：表示要創建的視圖名稱。
• 屬性清單：可選項，指定視圖中各個屬性的名詞，默認情況下與select語句中的查詢的屬性相同。
• select語句：表示一個單表或者多表的完整查詢語句，將查詢記錄導入視圖中。
• with check option：可選項，表示更新視圖數據時要保證在該視圖的權限范圍之內，即檢查插入或修改的數據是否符合 where 設置的條件。

-- 創建單表的視圖
create or replace view v_book_language(書名,作者,類別)
--  v_book_language可以不進行改名，即直接v_book_language as...
as select bookname,author,classno from book
where classno='c005' 
with check option;  -- 默認會是cascade
select * from v_book_language;update v_book_language set 作者='楊思琦' where 書名='新課標小學語文閱讀叢書論語';  -- 同時原表也會更新相關數據insert into v_book_language 
values ('Mysql的前世今生','張健','c006'); -- 沒有插入原表的主鍵-- 創建多表時的情況？
在where里加內外連接就好了
-- 但是注意：多表創建視圖時，無法對該視圖進行修改

由于with check option選項的限制，新增的數據如果不在v_book_language的范圍內，那么插入數據不成功。

-- 修改視圖
alter view v_book_yuyan(書編號,書名,作者,出版社,圖書類別) as 
... -- 和create一樣-- 只能刪除視圖的定義，不會刪除數據。可以一次刪除多個視圖
drop view if exists v_book_yuyan;

數據庫編程

基礎知識

用戶變量

用戶可以使用 declare 關鍵字來定義變量，定義后可以為變量賦值。這些變量的作用范圍是 begin…end 程序段中。

-- 定義
declare var_name[,...] type [default value]
-- 不能同時定義多個變量;type 參數用來指定變量的類型;default value 子句將變量默認值設置為 value，沒有使用 default 子句時，默認值為 null
declare v_num int default 10;
declare v_no char(10);-- 賦值1
set var_name = 賦值表達式[,var_name = 賦值表達式]...
set v_num=20; -- 若在進程之外賦值，則要加@,即 set @v_num=20-- 賦值2  select..into 語句為變量賦值
select col_name [...] into var_name[,...] from table_name where
select readername,phonenumber into v_name,v_phone from reader where readerno='r001'；
-- 返回結果只能唯一行。否則報錯

函數

函數分類

• 數字函數
• 字符串函數
• 日期函數
• 轉換函數
• 聚合函數
• 流程控制函數

數字函數

abs(數值)　-- 絕對值函數
mod(被除數, 除數)　-- 求余
sqrt（數)  -- 求二次方根，負數為null
round(對象數值, 保留小數的位數)　-- 四舍五入
ceil(數)和ceiling(數)  -- 返回不小于參數的最小整數
-- eg:
round(5.4, 4) -- 5.4
round(3.4, 3) -- 3.5
select ceil(-4.5),ceiling(4.5);
返回結果為-4和5。----------------------------------------------------------
-- rand	生成一個0~1之間的隨機數，傳入整數參數是，用來產生重復序列；
select rand(),rand()*10,rand()*100;
-- floor	向下取整，返回值轉化為一個bigint；
select floor(8),floor(8.66),floor(-8),floor(-8.66);-- 產生一個1-100和1000的隨機數
select floor(rand()*100)+1, floor(rand()*1000)+1;

注：絕大多數函數對于null都返回null

字符串函數

• ||–拼接函數

  但：Mysql無法使用，所以只能：

  字符串1 + 字符串2
  

  len(字符串)-- 字符串長度函數
  lower(字符串)-- 小寫轉換函數
  upper(字符串)-- 大寫轉換函數
  replace(對象字符串, 替換前的字符串, 替換后的字符串)-- 字符串替換substring(對象字符串 [from] 截取的起始位置 [for] [個數])-- 字符串的截取
  substring('database',3) -- 結果為abaseselect concat('data','base') -- 合并字符串trim('   database   ')	-- 刪除字符串左右兩側的空格；reverse	-- 字符串反轉（逆序）函數，返回與原始字符串順序相反的字符串;
  例如：select reverse('database');strcmp('abc','ab')-- 兩字符串的比較
  >0 -- a大
  =0 -- 同大
  <0 -- b大
  

流程控制函數

1. if 判斷，流程控制，語法結構如下：

   注：這個是寫在select的列里，對其中一個列進行相應的判斷

   if(expr,v1,v2)-- 當expr為真是返回v1的值，否則返回v2。
   select readername 姓名,if(gender='女',0,1) 性別 from reader limit 10;  -- 在第三列性別中進行判斷，把他的值邊為后面的結果ifnull(字符v1,v2);
   -- 其中：如果 字符v1 不為 null，則 ifnull 函數返回 字符v1; 否則返回 v2 的結果。

2. case 搜索判斷語句，語法如下：

   Mysql不允許在 then 或 else 子句中使用空的命令。

   case和if都會寫在設置條件的列上

   casewhen <條件1> then <命令>when <條件2> then <命令>...else <命令>
   end case;
   -------------------------------------
   select bookno 書號,bookname 書名,case when number=0 then '零本'when number=1 then '孤本'when number>1 and number<6 then '少量' else '大量'end as 數量
   from book limit 10;  
   

流程控制語句

有：if語句、case語句、loop語句、leave語句、iterate語句、repeat語句和while語句等。

if

• if( )判斷函數不同于這里的if語句。

其語法的基本形式如下：

if search_condition then statement_list
[elseif search_condition then statement_list]...
-- 此時elseif不能隔開，否則有多少個if就要多少個end if
[else statement_list]
end if;

mysql的是elseif連在一起，oracle是elsif連在一起，注意區別

if age<20 then set @count1=@count1+1;@count1=@count1+2；  -- 這里若是多條語句，則每一條用分號分隔，這是屬于then內部的elseif age>=@count1=@count1+120 and age<=40 then set @count2=@count2+1;else set @count3=@count3+1;  -- else同理
end if;

case

相對于c的switch

case 語句的基本形式如下：

casewhen search_condition then statement_list[when search_condition then statement_list] ...[else statement_list]
end case;

case
when age<20 then set @count1=@count1+1;when age>=20 and age<=40 then set @count2=@count2+1;else set @count3=@count3+1;
end case;

loop leave

loop相當于c里的while(true)

leave相當于c里的break

loop語句可以使某些特定的語句重復執行。loop只實現了一個簡單的循環，并不進行條件判斷。必須使用leave語句等才能停止循環，跳出循環過程。loop 語句的基本形式如下：

[label:]loop  -- label參數表示這個循環的名字，可以省略statement_listleave label  -- 退出則要指定循環的名字
end loop [label]

add_num:loopset @count=@count+1;if @count=100 thenleave add_num;
end loop add_num;

iterate

相當于c的continue

iterate label  -- 若要使用iterate則，loop中的label參數不能省略
iterate add_num;

repeat

相當于c的do-while – 先做后判斷條件

repeat語句是有條件控制的循環語句，基本語法形式如下：

[label:] repeatstatement_listuntil search_condition  -- 真則跳出循環
end repeat [label]repeatset @count=@count+1;until @count=100
end repeat;

while

[label:] while search_condition dostatement list
end while [label]while @count<100 doset @count=@count+1;
end while;

存儲過程

存儲過程是數據庫中的一個重要功能，存儲過程可以用來轉換數據、數據遷移、制作報表，它類似于編程語言，一次執行成功，就可以隨時被調用，完成指定的功能操作。

-- 創建
create procedure <過程名> ( [過程參數[,…] ] ) <過程體>  -- 創建存儲過程和存儲函數不用加頓號，因為create以下的內容都是屬于它本身的，所以不能加頓號。而在存儲內部，即begin和end的內部，每條大語句都要加頓號
[過程參數[,…] ] 格式如下：
[ in | out | inout ] <參數名> <類型>
begin
end;-- 調用
call p_name();-- ----------------------------------eg
delimiter $$
create procedure pr_good(inout v_no char(10),out v_prize char(30),v_name char(30),v_schoolname char(40))
begindeclare v_sum int;select count(*),readername,schoolname into v_sum,v_name,v_schoolname from borrow,reader,school where reader.readerno=v_no and borrow.readerno=reader.readerno and reader.schoolno=school.schoolno;if v_sum > 25 thenset v_prize='讀者之星';elseif v_sum between 15 and 25 thenset v_prize='希望之星';elseif v_sum<5 thenset v_prize='加油';elseset v_prize='好讀者';end if;select v_no,v_name,v_schoolname,v_prize;
end$$
delimiter ;-- 查看
show procedure status like '存儲過程名' [\g];
show create procedure '存儲過程名' [\g];-- 調用
set @v_no='r066';
call pr_good(@v_no,@v_prize,@v_name,@v_schoolname);
-- 不能直接call pr_good('r066',@v_prize,@v_name);必須帶@它本身-- 刪除
drop procedure [ if exists ] pr_good

mysql里存儲過程的調用是用call，oralce里是可以匿名塊調用或exec調用

存儲函數

存儲函數可以通過return語句返回函數值，主要用于計算并返回一個值。而存儲過程沒有直接返回值，主要用于執行操作。

-- 創建
create function fu_name(param1,param2,…)
returns datatype
beginreturn x  -- 必須要有返回值
end;
-- 存儲函數與存儲過程創建時最大的區別就是存儲函數一定要有返回值，并指定返回類型
-- eg
delimiter $$
create function fu_query(v_name varchar(10))
returns int(4)
beginreturn (select count(*) from book where bookname like concat('%',v_name,'%'));
end$$
delimiter ;-- 查看、刪除同上-- 調用
select 存儲函數名([參數1], [參數2]…)select fu_query('數據庫');
-- 注意區別存儲過程，存儲過程是call，而存儲函數是select查詢

mysql的調用存儲函數不需要from dual，mysql也沒有這個偽表

觸發器

重要的是觸發了“因”，然后執行“果”，不需要進行調用

create trigger <觸發器名> 
< before | after >  -- 觸發之前還是之后
<insert | update | delete > -- 因，就是要進行表的操作，DML
on <表名> 
for each row   -- each row針對每行變更都進行操作
begin  -- 果
end;

• before和after的區別（注意區分）

  before和after，觸發器被觸發的時刻，表示觸發器是在激活它的語句之前或之后觸發。若希望驗證新數據是否滿足條件（一般有條件判斷，而且只對一個表），則使用 before 選項；若希望在激活觸發器的語句執行之后完成幾個或更多的改變（果里涉及到幾個表），則通常使用 after 選項。

• 在觸發器主體（事件）中可以引用行變量

  1. 在監視事件上執行insert操作后會有一個新行，如果在觸發事件中需要用到這個新行的變量，可以用new關鍵字表示。

  2. 在監視事件上執行delete操作后會有一個舊行，如果在觸發事件中需要用到這個舊行的變量，可以用old關鍵字表示。

  3. 在監視事件上執行update操作后原紀錄是舊行，新記錄是新行，可以使用new和old關鍵字來分別操作。

     因為update其實是先delete后insert

• 當使用delete的注意事項

  當觸發器涉及到對觸發器自身的表更新操作時，只能使用before類型觸發器，不可使用after 類型觸發器。另外，如果更新的是同一行數據，須直接更新新行的變量，即使用new關鍵字。

delimiter $$
create trigger tr_borrow_returndata before update on borrow for each row
beginif new.returndate is not null thenset new.re_turn='是';
end if;
end$$
delimiter ;
update borrow set returndate='2017-06-07'了 where borrowno=10;
-- 1.其中，會在update之前進行判斷，而這時因為還沒有update，而只有new關鍵字提前執行了update，即有更新后的數據：'2017-06-07'了。就算說，new既代表了更新后的數據
-- 2.因此時是對僅一個表進行的update操作，所以僅能使用before

• 例子

  -- 查看
  show triggers \g;
  select * from information_schema.triggers where trigger_name='觸發器名' \g;
  -- 刪除
  drop trigger <名字>;--  eg
  delimiter $$
  create trigger tr_borrow_return after update on borrow for each row
  beginif new.re_turn='是' then
  update book set number=number+1 where bookno=new.bookno;
  end if;
  end$$
  delimiter ; 
  -- -----------------------------------------
  delimiter $$
  create trigger tr_borrow_new after insert on borrow for each row
  beginupdate book set number=number-1 where bookno=new.bookno;
  end$$
  delimiter ; 
  ----------------------------------------------
  delimiter $$
  create trigger tr_book_onshelf before update on book for each row
  beginif new.number<=1 then set new.onshelf='否';else set new.onshelf='是';
  end if;
  end$$
  delimiter ;
  

游標

游標是一種能從包括多條數據記錄的結果集中每次提取一條記錄的機制。

盡管游標能遍歷結果中的所有行，但一次只指向一行。

游標的作用就是用于對查詢數據庫所返回的記錄進行遍歷，以便進行相應的操作。

游標創建可以使用五步法：

/*1.聲明一個游標
使用declare關鍵字來聲明游標，并定義相應的select語句。其語法的基本形式如下： */
declare cursor_name cursor for select_statement;
-- 還要設置一個變量來判斷游標的查詢結果是否結束了
declare done int default false; -- 0也行
declare continue handler for not found set done=true;  -- 0對應就是1/*2.打開定義的游標
聲明游標之后，要想從游標中提取數據，必須首先打開游標。其語法格式如下：  */
open cursor_name;
-- 需要注意的是，打開一個游標時，游標并不指向第一條記錄，而是指向第一條記錄的前邊。/*3.獲得下一行數據
游標順利打開后，可以使用fetch...into語句來讀取數據，其語法形式如下：*/
fetch cursor_name into var_name [,var_name]...
/*上述語句中，將游標cursor_name中select語句的執行結果保存到變量參數var_name 中。當第一次使用游標時，此時游標指向結果集的第一條記錄。
Mysql的游標是只讀的，只能順序地從開始往后讀取結果集，不能從后往前，也不能直接跳到中間的記錄。*//*4.需要執行的語句(增刪改查)
這里視具體需求而定；*//*5.關閉游標
游標使用完畢后，要及時使用close關鍵字關閉游標，其語法格式如下：*/
close cursor_name;
-- 使用聲明過的游標不需要再次聲明，用open語句打開它就可以了。

注：mysql里游標的定義用的是for介詞，而oracle里是is

mysql里declare定義變量每個變量都要用declare且都要加分號；

例子

delimiter $$
create procedure pr_lovebook()
begindeclare v_bookno char(4);declare v_sum int(4);declare done int default false;  -- 步驟一：游標所需的變量declare cu_lovebook cursor for   -- 步驟二：定義游標1select bookno,count(*) from borrow group by bookno order by count(*) desc limit 5;declare continue handler for not found set done=true;                                            -- 2設置何時結束open cu_lovebook;                -- 步驟三：打開游標read_loop:loopfetch cu_lovebook into v_bookno,v_sum;                                           -- 步驟四：用循環獲取游標（含退出）if done then leave read_loop;end if;select bookno,bookname,author,onshelf,v_sum from book where bookno= v_bookno;end loop;close cu_lovebook;            -- 步驟五：關閉游標end$$
delimiter ;call pr_lovebook;

用戶權限備份

數據庫用戶管理

用戶會話變量以@開頭

可以用grant語句為指定數據庫添加用戶

修改服務器密碼：set password

查看用戶

-- 查看user表的用戶信息
use Mysql;
select host,user,authentication_string from user;

說明：
1.host表示允許訪問的主機，localhost為本機，%則表示一組主機,%就代表了該用戶允許遠程連接；
2.authentication_string表示密碼，但是是經過加密過的，不能直接看出來。

– 創建好用戶的登錄方式：在cmd中輸入：
Mysql -u user_name -p
Mysql -utest2 -p

新建用戶

• 方法1.create

create user 'user_name'@'host_name' [identified by ['password']];create user 'test'@'%' identified by 'liujiejie';

– @表示要定義主機名稱，中間不能有空格，此時主機名為%
– 定義密碼是 identified by 密碼為liujiejie（密碼要有8位）
– test是用戶名稱

• 方法2.insert

insert into mysql.user(host,user,authentication_string) values('host_name','user_name',password('your_password'));
flush privileges;insert into Mysql.user(host,user,authentication_string) values('%','test2',password('liujiejie'));
flush privileges;  -- 必須更新有才有效

必須擁有 mysql.user 表的 INSERT 權限

– 必須要password，否則可以創建，但你沒法登陸

• 方法3.grant

grant priv_type on database.table to user[identified by [password] 'password']grant select on *.* to 'test3'@'%' identified by 'liujiejie';   -- （select是權限的類型）（此時既創建了用戶又賦予了權限）

該命令可以創建多個用戶，其中priv_type參數表示新用戶的權限(select,insert,delete,etc.)
databse.table參數表示新用戶的權限范圍(在哪個表里有效)
user由用戶名和主機構成

用戶的修改與刪除

• 修改用戶名

rename user 'username'@'host' to 'new_username'@'host';
-- 如
rename user 'test2'@'%' to 'test02'@'%', 'test3'@'%' to 'test03'@'%';

• 刪除用戶

-- 刪除用戶
drop user user_name[,user_name2...];
drop user 'test02'@'%', 'test03'@'%'; -- 需包含用戶名和主機名

• 修改用戶密碼

-- 修改用戶密碼1：root來修改密碼,必須在cmd中操作
mysqladmin -u user_name -p password new_password;
Mysqladmin -u root -p password liujiejie
Mysqladmin -u user1 -p password liujiejie-- 2.update
update user set authentication_string = password('new_pd') where user='user_name';
update user set authentication_string = password('liujiejie') where user = 'test';-- 3.set   登錄哪個用戶就是為哪個用戶改密碼
set password = password('new_pd');
set password = password('liujiejie');-- 登錄的是管理員賬戶，為用戶改密碼
set password for 'u_name'@'h_name'=password('new_pd');
set password for 'test'@'%'=password('liujiejie');-- 4.grant,和創建時一樣
grant select on *.* to 'test'@'%' identified by 'liujiejie';
flush privileges;

用戶權限管理

查看用戶權限

主要的權限表有以下幾個：user（用戶全局權限表）,db,host,table_priv,columns_priv和procs_priv

-- 查看user表的各權限
desc user;-- 查看用戶權限
show grants for 'u_name'@'h_name';
show grants for 'test1'@'%';
show grants for 'root'@'localhost';

授予權限

• Mysql的權限級別分為

  **全局級別（*.*）**的管理權限：作用于整個Mysql實例級別。

  **數據庫級別（lib.*)**的權限：作用于某個指定的數據庫上或者所有的數據庫上。

  3**.數據庫對象級別(lib.book)的權限**：作用于指定的數據庫對象上（表、視圖等）或者所有的數據庫對象上。

  這三種級別的權限分別存儲在Mysql庫的user,db,tables_priv,columns_priv和procs_priv這幾個系統表中，待Mysql實例啟動后就加載到內存中。

• 賦予權限語法

grant privilege[,...] on privilege_level 
to u_name@h_name [identified by password][with [grant_option | resource_option]];
flush privilege;

用戶不存在，則grant會創建

privilege_level是授予的權限，主要有：

權限	范圍	作用
all	服務器	所有權限
select	表、列
insert
update
delete
create	數據庫、表、索引
drop
grant option	數據庫、表、存儲過程	授權
references	數據庫、表	外鍵約束的父表
index	表	創建刪除索引
alter
show databases	服務器	查看數據庫名稱
execute	存儲過程	執行
create view	視圖	創建
show view		查看
create user	服務器	創建用戶

• 全局層級***.***

  grant select on *.* to user1@localhost;
  

• 數據庫層級lib.*

  grant insert on lib.* to user1@localhost;
  

• 表層級lib.book

  grant update on lib.book to user1@localhost;
  

• 列層級level(column1[,…])

  grant update(borrowdate,returndate) on lib.borrow to 'user2'@'%';
  

• 子程序層級

  delimiter $$
  create procedure pr()
  beginselect 1;
  end$$
  delimiter ;
  grant execute on procedure lib.pr to 'user'@'%';
  

權限的轉移與限制

• 權限的轉移

  -- 它將允許此用戶授予其他用戶或從其他用戶刪除被擁有的權限。
  grant insert on lib.* to user1@localhost with grant option;  -- 先設置允許授權
  -- 在登錄user1，將其擁有的新增數據的權限傳遞給 user2。
  grant insert on lib.* to user2@localhost;
  

• 權限的限制

  grant privilege[,...] on privilege_level 
  to u_name@h_name [identified by password][with [grant_option | resource_option]];
  flush privilege;
  

  可選的with resource_option子句可以來分配Mysql數據庫服務器的資源。

  1. max_user_connections：全局變量，一個用戶可以在同一時間連接Mysql實例的數量，此參數無法對每個用戶區別對待。
  2. max_queries_per_hour：一個用戶在一個小時內可以執行查詢的次數（基本包含所有語句）。queries不止select，凡是返回值帶select的都算
  3. max_updates_per_hour：一個用戶在一個小時內可以執行修改的次數（僅包含修改數據庫或表的語句）。
  4. max_connections_per_hour：一個用戶在一個小時內可以連接Mysql的時間。

  grant insert on lib.* to 'user1'@'localhost' with max_queries_per_hour 10;-- 對只允許輸入多少次密碼問題，屬于queries，因為沒輸入此密碼都要去查詢是否相同
  

回收與更新權限

• 回收權限

  -- 回收權限，收回權限就是取消已賦予用戶的某些權限
  revoke [all] privilege,[privilege],.. on privilege_level from user;
  -- 如
  revoke insert on lib.* from user2@'%';
  show grants for 'user2'@'%';
  

• 更新權限的方式

  -- cmd終端
  mysqladmin flush -privileges
  mysqladmin reload
  -- mysql命令行
  flush privileges;
  

數據庫備份和還原

數據庫備份

• 數據庫的備份概念

? Mysqldump命令可以將數據庫中的數據備份成一個文本文件（sql）。執行Mysqldump命令時，它先查出需要備份的表的結構，再在文本文件中生成一個create語句。然后，將表中的所有記錄轉換成一條insert語句。然后通過這些語句，就能夠創建表并插入數據。

• Mysqldump基本語法：

  Mysqldump [options] db_name [tbl_name ...] > backup.sqlMysqldump [options] --databases db_name ... > backup.sqlMysqldump [options] --all-databases > backup.sql
  

  都是在終端cmd進行

  • 第一種方式是備份特定數據庫下的某些表，其中表的指定是可選的，若不指定表，則備份該數據庫下的所有表。
  • 第二種方式是備份某些數據庫，應該至少指定一個數據庫。
  • 第三種方式是備份所有數據庫

  options 中選項：

  • –host=host_name（h host_name）：指定要導出的目標數據庫所在的主機，默認是 localhost
  • –user=user_name（-u user_name）：指定鏈接目標數據庫的數據庫用戶名
  • –password[=password]（-p[password]）：指定鏈接目標數據庫的數據庫密碼
  • –port=port_num（-P port_num）：指定鏈接目標數據庫的端口
  • –all-databases（-A）：參數代表導出所有數據庫里的所有的表

-- 在cmd中方式1.備份某數據庫或某數據庫中的某表
Mysqldump -uroot -p lib book> d:\lib_20230619.sql                                          -- （官方寫法，但是因版本問題，報錯，所有要采用下面的那種方式👇）
Mysqldump --no-defaults -uroot -p12345678 lib book> d:\lib_20230619.sql-- 方式2.備份某些數據庫，最少一個
Mysqldump -u root -p --databases lib xk > d:\lib_xk_20230619.sqlMysqldump –-no-defaults -uroot -p --databases lib xk > d:\lib_xk_20230619.sql-- 方式3.備份所有數據庫
Mysqldump -uroot -p --all-databases > d:\all_20230619.sqlMysqldump –no-defaults -uroot -p --all-databases > d:\all_20230619.sql

數據庫還原

• 語法

  -- 方式1，cmd里
  mysql -u root -p [dbname] < backup.sql-- 方式2，在mysql里
  source backup.sql;
  

• eg

  -- 1.cmd中
  Mysql -uroot -p12345678 lib < d:\lib_20230619.sql-- 2.Mysql中
  drop database if exists lib;
  create database lib;
  use lib;
  source d:\\lib_20230619.sql;  -- 因為\是轉義字符
  -- 或
  source d:/lib_20230619.sql;  
  

備份與還原數據

數據的備份

-- 1.
select ... into outfile '目標文件' [option];-- 2.
select ... into outfile '目標文件' fields terminated by ',' optionally enclosed by '""' lines terminated by '\n';    -- 要求字符用””包含，字段之間用，隔開，每行結束使用\n換行符

“OPTION”參數為可選參數選項，其可能的取值有：

• fields terminated by ‘字符串’：設置字符串為字段之間的分隔符，可以為單個或多 個字符。默認值是“\t”。
• fields enclosed by ‘字符’：設置字符來括住字段的值，只能為單個字符。默認情況 下不使用任何符號。
• fields optionally enclosed by ‘字符’：設置字符來括住 CHAR、VARCHAR 和 TEXT 等 字符型字段。默認情況下不使用任何符號。
• fields ESCAPED by ‘字符’：設置轉義字符，只能為單個字符。默認值為“\”。
• LINES STARTING by ‘字符串’：設置每行數據開頭的字符，可以為單個或多個字符。默 認情況下不使用任何字符。
• LINES terminated by ‘字符串’：設置每行數據結尾的字符，可以為單個或多個字符。 默認值是“\n”。
• fields 和 LINES 兩個子句都是自選的，但是如果兩個子句都被指定了，fields 必須位 于 LINES 的前面。

-- 1.
select * from school into outfile 'd:/lib_school_2023-06-19.txt';-- 2.
select * from book into outfile 'd:/lib_book_2023-06-19.txt'
fields terminated by ',' optionally enclosed by '"' 
lines terminated by '\n';    -- 要求字符用””包含，字段之間用，隔開，每行結束使用\n換行符

數據的還原

create table tb_name like tb_name2 load data infile "/path/file" into table tb_name;create table school_bk like school;
load data infile 'd:/lib_school_20210101.txt' into table school_bk;create table book_bk like book;
load data infile 'd:/lib_book_20210101.txt' into table book_bk
fields terminated by ',' optionally enclosed by '"' 
lines terminated by '\n';   -- 注意數據字符用””標注，字段之間用，隔開，每行結束使用\n換行符