資料庫（MySQL）

ZhaoAnKang發表於2019-04-17
資料庫MySql

DB、DBS、DBMS之間有什麼關係？

  • DB、DBS、DBMS三者的關係是：DBS（資料庫系統）包括DB（資料庫）和DBMS（資料庫管理系統）。資料庫系統是一個通稱，包括資料庫、資料庫管理系統、資料庫管理人員等的統稱，是最大的範疇。  
  • DBMS和DB的關係：DBMS資料庫管理系統(Database Management System)是一種操縱和管理資料庫的大型軟體，是用於建立、使用和維護資料庫（DB）。它對資料庫進行統一的管理和控制，以保證資料庫的安全性和完整性。使用者通過DBMS訪問資料庫（DB）中的資料。
  •  DBS和DB的關係：資料庫系統DBS（Data Base System，簡稱DBS）是一個實際可執行的儲存、維護和應用系統提供資料的軟體系統，是儲存介質、處理物件和管理系統的集合體。它通常由軟體、資料庫（DB）和資料管理員（DBA）組成。

資料庫（MySQL）

擴充套件資料 

資料庫及資料庫系統組成成分：  

  • 資料庫，即儲存在磁帶、磁碟、光碟或其他外存介質上、按一定結構組織在一起的相關資料的集合。  
  • 資料庫管理系統（DBMS）。它是一組能完成描述、管理、維護資料庫的程式系統。它按照一種公用的和可控制的方法完成插入新資料、修改和檢索原有資料的操作。 
  • 資料庫管理員（DBA），有重新組織資料的能力，能改變資料的儲存結構或資料儲存位置。 
  • 使用者和應用程式，具有可修改性和可擴充性。

參考資料：

百度百科—DBMS

百度百科—DBS

百度百科—資料庫

資料庫的基本概念

  • 資料庫（DataBase，DB）：指長期儲存在計算機的儲存裝置上，按照一定規則組織起來，可以被各種使用者或應用共享的資料集合。儲存、維護和管理資料的集合。
  • 資料庫管理系統（DataBase Management System，DBMS）：指一種操作和管理資料庫的大型軟體，用於建立、使用和維護資料庫，對資料庫進行統一管理和控制，以保證資料庫的安全性和完整性。使用者通過資料庫管理系統訪問資料庫中的資料。
  •  注意：通常情況下，經常會用資料庫來表示他們使用的資料庫軟體，這經常會引起混淆，確切的說，資料庫軟體應該為資料庫管理系統，資料庫是通過資料庫管理系統建立和操作的。

常見資料庫管理系統

  • Oracle：目前比較成功的關係型資料庫管理系統。執行穩定、功能齊全、效能超群、技術領先。主要應用在大型的企業資料庫領域。
  •  MySQL：免費的資料庫系統。被廣泛用於中小型應用系統。體積小、速度快、總體擁有成本低，開放原始碼。2008年被SUN收購，2009年SUN被Oracle收購。
  •  SQL Server：Microsoft的產品。軟體介面友好、易學易用，在操作性和互動性方面獨樹一幟。 
  • PostgreSQL：加州大學伯克利分校以教學目的開發的資料庫系統，支援關係和麵向物件的資料庫，屬於自由資料庫管理系統。
  •  DB2：IBM的產品。

資料庫伺服器、資料庫和表的關係 

  • 所謂安裝資料庫伺服器，只是在機器上裝了一個資料庫管理程式，這個管理程式可以管理多個資料庫，一般開發人員會針對每一個應用建立一個資料庫。 
  • 為儲存應用中實體的資料，一般會在資料庫建立多個表，以儲存程式中實體的資料。 
  • 資料庫伺服器、資料庫和表的關係如圖所示： 

資料庫（MySQL）

如何運算元據庫 

  • 資料庫管理系統可以通過SQL（結構化查詢語言）來定義和運算元據，維護資料的完整性和安全性，以及進行各種資料庫的管理等。 

什麼是SQL 

  • SQL：Structure Query Language。 
  • SQL被美國國家標準局（ANSI）確定為關係型資料庫語言的美國標準，後來被國際化標準組織（ISO）採納為關聯式資料庫語言的國際標準。 
  • SQL優點： 
  1. 不是某個特定資料庫供應商專有的語言。幾乎所有重要的資料庫管理系統都支援SQL。
  2.  簡單易學，該語言的語句都是由描述性很強的英語單片語成，且這些單詞的數目不多。 
  3. 高度非過程化，即用SQL運算元據庫，只需指出“做什麼”，無須指明“怎麼做”，存取路徑的選擇和操作的執行由DBMS自動完成。 

SQL的分類 

  • 它所支援的SQL提供以下功能： 
  1. DDL（Data Definition Language）：資料定義語言，用來定義資料庫物件：庫、表、列等；
  2. DML（Data Manipulation Language）：資料操作語言，用來運算元據庫表中的記錄（資料）；  
  3. DQL（Data Query Language）：資料查詢語言，用來查詢記錄（資料）。 
  4. DCL（Data Control Language）：資料控制語言，用來定義訪問許可權和安全級別；

運算元據表

  • 語法：

create table 表名(
				欄位1 欄位型別,
				欄位2 欄位型別,
				...
				欄位n 欄位型別
			);
複製程式碼

  • 常用資料型別：

    • int：整型 
    •  double：浮點型，例如double(5,2)表示最多5位，其中必須有2位小數即最大值為999.99； 
    •  char：固定長度字串型別； char(10) 'abc ' varchar：可變長度字串型別；varchar(10) 'abc' 
    •  text：字串型別; 
    •  blob：位元組型別；
    • date：日期型別，格式為：yyyy-MM-dd； 
    •  time：時間型別，格式為：hh:mm:ss 
    •  timestamp：時間戳型別 yyyy-MM-dd hh:mm:ss 會自動賦值 
    •  datetime:日期時間型別 yyyy-MM-dd hh:mm:ss
      • 當前資料庫中的所有表 SHOW TABLES;
      •  檢視錶的欄位資訊 DESC employee;
      • 在上面員工表的基本上增加一個image列。 ALTER TABLE employee ADD image blob;
      • 修改job列，使其長度為60。 ALTER TABLE employee MODIFY job varchar(60);
      • 刪除image列,一次只能刪一列。 ALTER TABLE employee DROP image;
      • 表名改為user。 RENAME TABLE employee TO user; 
      • 檢視錶格的建立細節。 SHOW CREATE TABLE user;
      • 修改表的字符集為gbk ALTER TABLE user CHARACTER SET gbk;
      • 列名name修改為username ALTER TABLE user CHANGE name username varchar(100);
      • 刪除表 DROP TABLE user ;

DDL資料定義語言 

  • DDL：Data Definition Language 
  • 作用：用於描述資料庫中要儲存的現實世界實體的語言。即建立資料庫和表的結構。 
  • 常用關鍵字： CREATE ALTER DROP  
建立資料庫

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
    [create_specification [, create_specification] ...]
 
create_specification:
    [DEFAULT] CHARACTER SET charset_name //指定字符集
  | [DEFAULT] COLLATE collation_name  //指定資料庫字符集的比較方式複製程式碼

檢視、刪除資料庫 

檢視當前資料庫伺服器中的所有資料庫：
SHOW DATABASES

顯示資料庫建立語句：
SHOW CREATE DATABASE db_name

資料庫刪除語句：
DROP DATABASE  [IF EXISTS]  db_name 

檢視當前使用的資料庫
select database();

切換資料庫
use mydb2;
複製程式碼

修改、備份、恢復資料庫

ALTER  DATABASE  [IF NOT EXISTS] db_name    
	[alter_specification [, alter_specification] ...] 

alter_specification:    

    [DEFAULT] CHARACTER SET charset_name  
|   [DEFAULT] COLLATE collation_name

檢視伺服器中的資料庫，並把mydb2的字符集修改為utf8;
 alter database mydb2 character set utf8;複製程式碼

建立表(基本語句) 

CREATE TABLE table_name
(
	field1  datatype,
	field2  datatype,
	field3  datatype
)character set 字符集 collate 校對規則
field：指定列名　datatype：指定列型別
複製程式碼

注意：建立表前，要先使用use db語句使用庫。

MySQL常用資料型別 

分類資料型別說明

數值型別BIT(M)
TINYINT [UNSIGNED] [ZEROFILL] 
BOOL，BOOLEAN 
SMALLINT [UNSIGNED] [ZEROFILL] 
INT [UNSIGNED] [ZEROFILL] 
BIGINT [UNSIGNED] [ZEROFILL] 
FLOAT[(M,D)] [UNSIGNED] [ZEROFILL] 
DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL] 位型別。M指定位數，預設值1，範圍1-64
帶符號的範圍是-128到127。無符號0到255。
使用0或1表示真或假
2的16次方
2的32次方
2的64次方
M指定顯示長度，d指定小數位數
表示比float精度更大的小數

文字、二進位制型別CHAR(size) char(20)
VARCHAR(size)  varchar(20)
BLOB   LONGBLOB
TEXT(clob)          LONGTEXT(longclob)固定長度字串
可變長度字串
二進位制資料
大文字時間日期DATE/DATETIME/TimeStamp日期型別(YYYY-MM-DD)  (YYYY-MM-DD HH:MM:SS)，TimeStamp表示時間戳，它可用於自動記錄insert、update操作的時間複製程式碼

修改表 

使用 ALTER TABLE 語句追加, 修改, 或刪除列的語法。

ALTER TABLE table_name
ADD		   (column datatype [DEFAULT expr]
		   [, column datatype]...);
複製程式碼

ALTER TABLE table_name
MODIFY	   column datatype [DEFAULT expr]
		   [, column datatype]...;
複製程式碼

ALTER TABLE table_name
DROP	      (column);
複製程式碼

  • 修改表的名稱：Rename table 表名 to 新表名 
  • 修改表的字符集：alter table student character set utf8;

DML資料操縱語言  

  • DML:Data Manipulation Language 
  • 作用：用於向資料庫表中插入、刪除、修改資料。 
  • 常用關鍵字： INSERT UPDATE DELETE

Insert語句 

  • 使用 INSERT 語句向表中插入資料。

INSERT INTO	table [(column [, column...])]
VALUES		(value [, value...]);
複製程式碼

  • 插入的資料應與欄位的資料型別相同。
  • 資料的大小應在列的規定範圍內，例如：不能將一個長度為80的字串加入到長度為40的列中。 
  • 在values中列出的資料位置必須與被加入的列的排列位置相對應。 
  • 字元和日期型資料應包含在單引號中。 
  • 插入空值，不指定或insert into table value(null) 

mysql中文亂碼 

  mysql有六處使用了字符集，分別為：client 、connection、database、results、server 、system。 

  • client是客戶端使用的字符集。  
  • connection是連線資料庫的字符集設定型別，如果程式沒有指明連線資料庫使用的字符集型別就按照伺服器端預設的字符集設定。 
  •  database是資料庫伺服器中某個庫使用的字符集設定，如果建庫時沒有指明，將使用伺服器安裝時指定的字符集設定。 
  •  results是資料庫給客戶端返回時使用的字符集設定，如果沒有指明，使用伺服器預設的字符集。 
  •  server是伺服器安裝時指定的預設字符集設定。 
  •  system是資料庫系統使用的字符集設定。 

Update語句 

  • 使用 update語句修改表中資料。 

    UPDATE 	tbl_name    
	SET col_name1=expr1 [, col_name2=expr2 ...]    
	[WHERE where_definition]    
複製程式碼

  • UPDATE語法可以用新值更新原有錶行中的各列。 
  • SET子句指示要修改哪些列和要給予哪些值。 
  • WHERE子句指定應更新哪些行。如沒有WHERE子句，則更新所有的行。 

Delete語句 

  • 使用 delete語句刪除表中資料。 

delete from _name       
	[WHERE where_definition]    
複製程式碼

  • 如果不使用where子句，將刪除表中所有資料。 
  • Delete語句不能刪除某一列的值（可使用update） 使用delete語句僅刪除記錄，不刪除表本身。如要刪除表，使用drop table語句。 
  • 同insert和update一樣，從一個表中刪除記錄將引起其它表的參照完整性問題，在修改資料庫資料時，頭腦中應該始終不要忘記這個潛在的問題。 
  • 刪除表中資料也可使用TRUNCATE TABLE 語句，它和delete有所不同，參看mysql文件。 
  1. truncate table 不僅是刪除表裡面的資料，而且還會清空表裡面主鍵的標識。也就是說使用過truncate table 的表在重新寫入資料的時候，識別符號會從0或1重新開始（看你設定的種子號）。
    delete * from就是僅僅能刪除資料，不能清空標識。不過delete * from可以後面加Where truncate table卻不能加Where。。
  2. TRUNCATE TABLE 和不帶 WHERE 的 DELETE 功能是一樣的，都是刪除表中的所有資料，不過 TRUNCATE TABLE 速度更快，佔用的日誌更少，這是因為 TRUNCATE TABLE 直接釋放資料頁並且在事務日誌中也只記錄資料頁的釋放，而 DELETE 是一行一行地刪除，在事務日誌中要記錄每一條記錄的刪除。
那麼可不可以用 TRUNCATE TABLE 代替不帶 WHERE 的 DELETE 呢？在以下情況是不行的：
  • 要保留標識的情況下不能用 TRUNCATE TABLE，因為 TRUNCATE TABLE 會重置標識
  • 需要使用觸發器的情況下不能使用 TRUNCATE TABLE ，它不會激發觸發器。
  • 對於由 FOREIGN KEY 約束引用的表（即主鍵所在的表，不是外來鍵所在的表）不能使用 TRUNCATE TABLE。
  • 對於參與了索引檢視的表不能使用 TRUNCATE TABLE ，注意指索引檢視，並非普通檢視。

Truncate是SQL中的一個刪除資料表內容的語句，用法是： 　　

TRUNCATE TABLE [Table Name]複製程式碼

  • 具有聚集索引的檢視成為索引檢視；必須先為檢視建立聚集索引，然後才能為該檢視定義其他索引，因為在建立其他索引的時候，SQL SERVER， 會重建表是的非聚集索引。

DQL資料查詢語言(簡單的)

  • DQL:Data Query Language 
  • 作用：查詢資料，返回結果集。 
  • 常用關鍵字： SELECT

Select語句(1)

  • 基本select語句 

SELECT [DISTINCT] *|{column1, column2, column3..}FROM	table;
複製程式碼

  • Select 指定查詢哪些列的資料。 
  • column指定列名。 
  • *號代表查詢所有列。 
  • From指定查詢哪張表。 
  • DISTINCT可選，指顯示結果時，是否剔除重複資料 

Select語句(2)

  • 在select語句中可使用表示式對查詢的列進行運算 

SELECT *|{column1｜expression, column2｜expression，..}FROM	table;
複製程式碼

  • 在select語句中可使用as語句 

SELECT column as 別名 from 表名;
複製程式碼

Select語句(3)

  • 在where子句中經常使用的運算子 

比較運算子>   <   <=   >=   =    <>大於、小於、大於(小於)等於、不等於BETWEEN  ...AND...顯示在某一區間的值(含頭含尾)IN(set)顯示在in列表中的值，例：in(100,200)LIKE ‘張pattern’模糊查詢IS NULL判斷是否為空
邏輯運算子and多個條件同時成立or多個條件任一成立not不成立，例：where not(salary>100);複製程式碼

  • Like語句中，% 代表零個或多個任意字元，_ 代表一個字元，例first_name like ‘_a%’; 

Select語句(4) 

  • 使用order by 子句排序查詢結果。 

SELECT column1, column2. column3..
		FROM	table;
		order by column asc|desc
複製程式碼

  • Order by 指定排序的列，排序的列即可是表中的列名，也可以是select 語句後指定的列名。
  •  Asc 升序、Desc 降序 
  • ORDER BY 子句應位於SELECT語句的結尾。
Select語句(5) 
  • 使用group by 子句對列進行分組

SELECT column1, column2. column3.. FROM	table;
		group by column
複製程式碼

  • 使用having 子句過濾 

SELECT column1, column2. column3..
		FROM	table;
		group by column having ...
複製程式碼

  • Having和where均可實現過濾，但在having可以使用合計函式,having通常跟在group by後，它作用於組。

資料完整性 

  • 資料完整性是為了保證插入到資料中的資料是正確的，它防止了使用者可能的輸入錯誤。
  • 資料完整性主要分為以下三類： 
  1. 實體完整性： 規定表的一行（即每一條記錄）在表中是唯一的實體。實體完整性通過表的主鍵來實現。 
  2. 域完整性： 指資料庫表的列（即欄位）必須符合某種特定的資料型別或約束。比如NOT NULL。 
  3. 參照完整性： 保證一個表的外來鍵和另一個表的主鍵對應。 

定義表的約束

  • 定義主鍵約束 
    •  primary key:不允許為空，不允許重複 
      • 刪除主鍵：alter table tablename drop primary key ; 
  • 定義主鍵自動增長 auto_increment 
  • 定義唯一約束 unique 
  • 定義非空約束 not null 
  • 定義外來鍵約束 constraint ordersid_FK foreign key(ordersid) references orders(id),

DQL資料查詢語言(複雜的)

  • 連線查詢： 
    • 交叉連線（cross join）:不帶on子句，返回連線表中所有資料行的笛卡兒積。 
    • 內連線（inner join）：返回連線表中符合連線條件及查詢條件的資料行。 
    • 外連線：分為左外連線（left out join）、右外連線（right outer join）。與內連線不同的是，外連線不僅返回連線表中符合連線條件及查詢條件的資料行，也返回左表（左外連線時）或右表（右外連線時）中僅符合查詢條件但不符合連線條件的資料行。 
  • 子查詢 
  • 聯合查詢 
  • 報表查詢

連線查詢

  • 連線查詢的from子句的連線語法格式為:

from TABLE1 join_type TABLE2  [on (join_condition)] [where (query_condition)]
複製程式碼

  • 其中，TABLE1和TABLE2表示參與連線操作的表，TABLE1為左表，TABLE2為右表。on子句設定連線條件，where子句設定查詢條件，join_type表示連線型別。
交叉連線查詢 
  • 交叉連線查詢CUSTOMER表和ORDERS表 

SELECT * FROM customer CROSS JOIN orders;
SELECT * FROM customer,orders;
複製程式碼

內連線查詢 

  • 顯式內連線：使用inner join關鍵字，在on子句中設定連線條件

SELECT * FROM customer c INNER JOIN orders o ON c.id=o.customer_id; 
複製程式碼

  • 隱式內連線：不包含inner join關鍵字和on關鍵字，在where子句中設定連線條件

SELECT * FROM customer c,orders o WHERE c.id=o.customer_id; 
複製程式碼

右外連線查詢 

  • 使用right outer join關鍵字，在on子句中設定連線條件

    SELECT * FROM customer c RIGHT OUTER JOIN orders o ON c.id=o.customer_id; 
複製程式碼

    • 不僅包含符合c.id=o.customer_id連線條件的資料行，還包含orders右表中的其他資料行，帶查詢條件的右外連線查詢，在where子句中設定查詢條件

      SELECT * FROM customer c RIGHT OUTER JOIN orders o ON c.id=o.customer_id WHERE o.price>250;
複製程式碼

子查詢 

  • 子查詢也叫巢狀查詢，是指在select子句或者where子句中又嵌入select查詢語句,子查詢的語句放在小括號之內。
  • 查詢“趙安康”的所有訂單資訊

    SELECT * FROM orders WHERE customer_id=(SELECT id FROM customer WHERE name LIKE '%趙安康%');
複製程式碼

左外連線查詢 

  • 使用left outer join關鍵字，在on子句中設定連線條件 

   SELECT * FROM customer c LEFT OUTER JOIN orders o ON c.id=o.customer_id; 
複製程式碼

    • 不僅包含符合c.id=o.customer_id連線條件的資料行，還包含customer左表中的其他資料行 
  • 帶查詢條件的左外連線查詢，在where子句中設定查詢條件

    SELECT * FROM customer c LEFT OUTER JOIN orders o ON c.id=o.customer_id WHERE o.price>250;
複製程式碼

聯合查詢 

  • 聯合查詢能夠合併兩條查詢語句的查詢結果，去掉其中的重複資料行，然後返回沒有重複資料行的查詢結果。聯合查詢使用union關鍵字

    SELECT * FROM orders WHERE price>200 UNION SELECT * FROM orders WHERE customer_id=1;
複製程式碼

報表查詢 

  • 報表查詢對資料行進行分組統計，其語法格式為:

    [select …] from … [where…] [ group by … [having… ]] [ order by … ] 
複製程式碼

  • 其中group by 子句指定按照哪些欄位分組，having子句設定分組查詢條件。在報表查詢中可以使用SQL函式。
合計函式－Count
  • Count(列名)返回某一列，行的總數 

Select count(*)|count(列名) from tablename
		[WHERE where_definition]  複製程式碼

合計函式－SUM 

  • Sum函式返回滿足where條件的行的和 

Select sum(列名)｛,sum(列名)…｝ from tablename
		[WHERE where_definition]       
複製程式碼

  • 注意：sum僅對數值起作用，否則會報錯
  • 注意：對多列求和，“，”號不能少。

合計函式－AVG 

  • AVG函式返回滿足where條件的一列的平均值 

Select sum(列名)｛,sum(列名)…｝ from tablename
		[WHERE where_definition]   
複製程式碼

合計函式－MAX/MIN 

  • Max/min函式返回滿足where條件的一列的最大/最小值 

Select max(列名)　from tablename
		[WHERE where_definition]   
複製程式碼

資料的備份與恢復 

  • 資料庫備份： mysqldump -u root -psorry test>test.sql 
  • 資料庫恢復： 
    • 建立資料庫並選擇該資料庫 
    • SOURCE 資料庫檔案 
    • 或者： mysql -u root -psorry test<test.sql 


相關文章