MySQL期末突擊複習資料

概述

启动MySQL服务：

　　services.msl命令可以打开服务面板，然后通过鼠标点击完成开启服务。

　　以管理員身份運行CMD:net stop mysql（关闭服务），net start mysql（开启服务）

登陆和退出(实测全部失败)：

　　mysql -uroot -plove

　　mysql –uroot –p

　　mysql -h+IP -uroot -plove

　　mysql –host=ip –user=root –password=password

通用语法：

1. 可以以单行或多行书写，以分号结束
2. 使用空格或者tab提高可读性
3. MySQL不区分大小写，但建议关键字使用大写
4. 注釋：

　　　　a) 單行注釋：--+空格+注釋内容

　　　　b) 單行注釋（MySQL特有）：#+注釋内容

　　　　c) 多行注釋：/* 注釋内容*/

什麽是SQL:Structured Query Language:結構化查詢語言,定義了所有關係型數據庫的操作規則

分類：

　　DDL：操作數據庫和表

　　DML：增刪改表中的數據

　　DQL：查詢表中的數據

　　DCL：授權和安全訪問操作

DDL：操作數據庫和表

操作數據庫：

C(create):創建

　　創建數據庫：CREATE DATABASE DBname;

　　創建之前先查看是否已經存在：CREATE DATABASE IF NOT EXISTS DBname;

　　手動設置字符集：CREATE DATABASE DBname CHARACTER SET gbk;

R(Retrieve):查詢

　　查詢所有數據庫的名稱：SHOW DATABASE;

　　查詢某個數據庫的的字符集：SHOW CREATE DATABASE DBname;

U(Update):修改

　　修改數據庫字符集：ALTER DATABASE DBname CHARACTER SET jbk;

D(Delete):刪除

　　直接刪除：DROP DATABASE DBname;

　　首先判斷是否存在：DROP DATABASE IF EXISTS DBname;

使用數據庫：

　　查詢正在使用的DB：SELECT DATABASE();

　　切換使用的DB：USE DBname;

操作表：

C(create)：創建

　　CREATE TABLE table_name(

　　列名1 數據類型,

　　列名2 數據類型,

　　……

　　列名n 數據類型

　　);

類型:

　　int: 整形

　　double: 小數類型

　　date: 只包含年月日的日期格式yyyy-mm-dd

　　datetime: yyyy-mm-dd HH:mm:ss

　　timestamp: 時間戳,不賦值則自動為當前系統時間

　　varchar(n): 字符串類型,n表示了字符串最大的長度—一個漢字也是算一個字符

R(Retrieve)：查詢

　　查詢所有表名稱：SHOW TABLES;

　　查詢表結構：DESC table_name;

U(Update)：修改

　　修改表名: ALTER table_name RENAME TO new_table_name;

　　修改表的字符集: ALTER TABLE table_name CHARACTER SET GBK;

　　添加一列: ALTER TABLE table_name ADD 列名 類型;

　　修改列的名稱,類型:

　　修改表名和數據類型:ALTER TABLE table_name CHANGE 列名 新列名 數據類型;

　　只修改數據類型:ALTER TABLE 表名 MODIFY 列名 新數據類型;

　　刪除列: ALTER TABLE 表名 DROP 列名;

D(Delete)：刪除

　　刪除一個表: DROP TABLE table_name;

　　刪除之前先判斷: DROP TABLE IF EXISTS table_name;

DML: 修改表中數據

添加數據

　　基本語法: INSERT INTO 表名(列名1, 列名2, 列名2…) VALUES(值1, 值2, 值3…);

注意事項:

1. 列名和值需要一一對應
2. 表名后的括號内不添加列名默認給所有列添加值
3. 出來數字類型,其他的類型需要使用引號引起來

刪除數據

　　基本語法:DELETE FROM [WHERE 條件];

　　注意事項:不加條件則會刪除表中的所有數據

　　刪除表,並創建一個與之相同的空表:TRUNCATE TABLE table_neme;這條指令比刪除表中的所有數據效率高

修改數據

　　基本語法:UPDATE table_name set 列名1=值1, 列名2=值2,…[WHERE 條件]

　　注意:如果不加任何條件則會將改變所有記錄

DQL:查詢表數據

完整語法:

　　SELECT

　　　　字段列表

　　FROM

　　　　表名列表

　　WHERE

　　　　條件列表

　　GROUP BY

　　　　分組列表

　　HAVING

　　　　分組之後的條件

　　ORDER

　　　　排序

　　LIMIT

　　　　分頁限定

基礎查詢:

多個字段的查詢:

　　將想要查詢的字段名寫在SELECT之後,字段名之間使用逗號隔開

　　查詢結果會按照字段列表排序

　　查詢所有字段可以使用*,但是慎用

去除重複:

　　在字段名之前使用DISTINCT

　　去重只會去除完全相同的行,只有部分相同的不會改變

計算列:

　　將對應的計算式卸載字段列表即可

　　使用IFNULL(expr1,expr2)可以在expr1的值為NULL的時候使用expr2代替

起別名

　　AS可以使用于給查詢的字段起別名,類似於python中的導包

條件查詢

1. 在WHERE子句后面跟條件
2. 運算符:

　　　　a) < , > , < = , >= , <> , =

　　　　b) BETWEEN a AND b:在ab之間

　　　　c) IN:在某個集合當中

　　　　d) IS NULL:等號只能使用NULL判斷,不能使用=或者<>判斷

　　　　e) AND OR NOT:你懂的,不用講同python

　　　　f) LIKE:模糊查詢:

正則:

　　_:單個任意字符

　　%多個任意字符

排序查詢

　　語法:ORDER BY 排序字段1 排序方式1,排序字段2 方式2…

　　排序方式:ASC(升序),DESC(降序).默認情況為升序

　　注意:只有儅第一排序條件相同的時候第二排序方式才能起作用,更多的條件同理

聚合函數

　　含義:將一列數據作爲一個整體,然後進行縱向的計算

　　常見函數:

　　　　COUNT:計算個數

　　　　MAX:計算最大值

　　　　MIN:計算最小值

　　　　SUM:求和

　　　　AVG:平均值

注意事項:聚合函數會自動派出NULL值的計算

解決方案:選擇不包含非空列進行計算或者使用IFBULL函數替換NULL值

分組查詢

　　語法:GROUP BY分組字段

　　注意:分組之後查詢字段列表只能是分組字段或者聚合函數,雖然可以填其他的,但是沒有任何實際意義.

　　WHERE和HAVING的區別:

　　　　WHERE在分組之前進行限定,如果不滿足條件則不參與分組

　　　　HAVING在分組之後進行限定,如果不滿足條件則不被查詢

　　　　WHERE後面不能使用聚合函數,HAVING后可以使用聚合函數進行判斷

分頁查詢

　　語法:LIMIT 開始的索引,每頁查詢的條數

　　公式:開始的索引 = (當前的頁碼 - 1)*每頁條數

　　注意:LIMIT是MySQL的方言,其他數據庫尤其自己的分頁方式

約束:

　　概念:對表中的數據進行限定,保證數據的正確性,有效性和完整性.

　　分類:

　　主鍵:PRIMARY KEY

1. 1. 表示非空且唯一
   2. 一張表只能有一個主鍵字段
   3. 創建表的時候添加主鍵id INT PRIMARY KEY,
   4. 在創建之後使用ALTER TABLE table_name DROP PRIMARY KEY刪除主鍵
   5. 創建之後使用使用MODIFY添加主鍵
   6. 使用關鍵字AUTO_INCREMENT自動增長,常配合PRIMARY KEY使用,并且其增長只與上一條數據有關

　　非空:NOT NULL

1. 1. 表示指不能沒有
   2. 在創建表的時候添加約束
   3. 在創建完成之後使用ALTER TABLE table_name MODIFY 列名 +約束可以添加或者刪除NOT NULL約束

　　唯一:UNIQUE

1. 1. 表示值不能重複
   2. 創建表的時候與NOT NULL使用方式類似
   3. 在創建完成之後使用ALTER TABLE tanle_name DROP INDEX 列名刪除列的UNIQUE約束,不能使用與NOT NULL相同的方式刪除
   4. 在創建表完成之後可以使用ALTER TABLE table_name MODIFY 列名 +約束的方式來添加約束
   5. NULL和NULL是不相等的

　　外鍵:FOREIGN KEY

1. 1. 在創建表的時候添加外鍵,外鍵列:CONSTRAINT 外鍵名稱 FOREIGN KEY (外鍵列名稱) REFERENCES 主表名稱(主表列名稱
   2. 刪除外鍵:ALTER TABLE table_name DROP FOREIGN KEY 外鍵名
   3. 在創建之後添加外鍵)ALTER TABLE table_nema ADD CONSTRAINT 外鍵名稱 FOREIGN KEY (外鍵列名稱) REFERENCES 主表名稱(主表列名稱
   4. 從表中外鍵值可以為NULL,但是不可以是主表中不存在的值
   5. 級聯操作:級聯更新和級聯刪除在添加的外鍵后加上:ON UPDATE CASCADE ON DELETE CASCADE

數據庫的設計

多表之間的關係

　　a) 分類:

　　　　i. 一對一:一個人只能有一個身份証

　　　　ii.一對多或者多對一,一個老師有多個學生,一個學生只有一個老師

　　　　iii.多對多:一個學生有多門課程,一門課程有多個學生學習

　　b)實現:

　　　　i. 一對多:在身爲多的一方建立外鍵,指向一的一方的主鍵

　　　　ii. 多對多:需要藉助第三張中間表,中間表至少包含兩個字段,分別作爲外鍵指向兩個表的主鍵

　　　　iii. 一對一:在任意一方添加唯一外鍵指向另一方的主鍵,一般情況下一對一的關係直接合成一張表就可以了

數據庫設計的範式

　　c) 第一范式(1NF):每一列都是不可分割的原子數据

　　d) 第二范式(2NF):非碼屬性必須完全依賴于候選碼

　　e) 第三範式(3NF):任何非主屬性不依賴於其他非主屬性

　　f) 巴斯-科德範式(BCNF)

　　g) 第四範式(4NF)

　　h) 第五範式(5NF,又稱完美範式)

数据库的备份和还原

命令行的方式:

　　备份:语法:mysqldump -u用户名 -p密码  需要备份的数据库名称 >保存路径

　　还原:

1. 1. 登陆数据库
   2. 创建数据库
   3. 使用数据库
   4. 执行文件 soure 文件路径

　　图形化界面:

　　　　这个你不用知道,因为如果你在看这个文件说明你上面使用命令行还原的方式你都未必知道

多表查询

　　語法:在單表查詢的查詢列表中添加多個查詢列表就可以了,使用逗號隔開

　　查詢結果為笛卡爾積:

　　分類:

1. 1. 内連接查詢

　　　　　　　　a)隱式内鏈接:使用WHERE消除無用數據

　　　　　　　　b)顯式内鏈接:語法:SELECT 字段 FROM 表名1 [INNRT] JOIN 表名2 ON 條件

　　　　　　2. 外連接查詢

　　　　　　　　a)左外連接:

                                        i.語法:SELECT *FROM table_1 LEFT [OUTER] JOIN table_2 條件

                                        ii.注意:查詢結果為左表的所有數據和右表的交集

　　　　　　　　b)右外連接

                                       i. 語法:SELECT *FROM table_1 RIGHT [OUTER] JOIN table_2 條件

                                       ii.注意:查詢結果為右表的所有數據和右表的交集

　　　　　　3.子查詢:

　　　　　　　　a)概念:查詢中嵌套查詢,稱嵌入的查詢語句為子查詢

　　　　　　　　b)語法:看後面的不同看法就知道了

　　　　　　　　c)不同情況:

                                        i.子查詢結果是單行單列:子查詢可以作爲條件,使用運算符判斷

                                        ii.子查詢結果為單列多行:使用IN關鍵字來將結果判斷

                                        iii.子查詢結果為多行多列:作爲虛擬表放在FROM后進行查詢,但是這種情況和使用普通内鏈接查詢是一樣的

事务

　　基本介紹;

　　概念:包含多個步驟的業務操作被事務管理,要麽同時成功,要麽同時失敗.

　　格式:

　　　　開啓事務:START TRANSACTION

　　　　提交事務:COMMIT

　　　　回滾事務:ROLLBACK

　　注意:

1. 1. MySQL會自動提交事務如果沒有手動開啓事務的話且每執行一條DML都會提交一次,但是如果手動開啓了事務,卻沒有手動提交,系統不會默認提交,而是會回滾.
   2. 查看:使用命令SELECT @@AOTUCOMMIT,結果爲1表示自動提交,如果爲0表示不自動提交.可以使用SET @@AOTOCOMMIT=0來修改其值
   3. Oracle數據庫是手動提交數據的

　　四大特徵:

1. 1. 原子性:不可分割,要麽同時成功,要麽同時失敗
   2. 持久性:事務一旦提交或者回滾,數據庫會持久化的保存數據
   3. 隔離性:多個事務之間相互獨立
   4. 一致性:事務操作前後,數據總量不變

　　隔離級別(瞭解):

　　　　概念:多個事務之間是相互獨立的,但是如果多個事務操作同一批數據則會引發一些問題,可以通過設置隔離級別解決問題

　　　　會產生的問題:

1. 1. 脹讀:一個事務讀取到另一個事務中沒有提交的數據
   2. 不可重複讀:在同一個事務中兩次讀取到的數據不一樣
   3. 幻讀:一個事務操作數據表中的記錄,另一個事務添加一條數據,則第一個事務查詢不到自己的記錄

　　　　隔離級別:

1. 1. 讀未提交
   2. 讀已提交
   3. 可重複讀
   4. 串行化

DCL

　　管理用戶:

　　查詢用戶:

1. 1. 切換到mysql數據庫
   2. 查詢user表

　　添加用戶:

1. 1. CREATE ‘user_name’@’主機名’ IDENTIFIED 密碼

　　修改用戶密碼:

1. 1. UPDATE USER SET PASSWORD = PASSWORD(‘新密碼’)WHERE USER=’用戶名’;
   2. SET PASSWORD FOR ‘user’@’主機名’=PASSWORD(‘新密碼‘);

　　刪除用戶:

1. 1. DROP USER ‘user_name’@’主機名’

　　管理員忘記密碼:

1. 1. 由管理員停止MySQL服務
   2. 使用無驗證方式啓動MySQL
   3. 在新窗口中可以直接進入MySQL並修改ROOT密碼
   4. 結束MySQL的進程
   5. 重啓MySQL服務
   6. 使用新密碼登陸

　　授權:

　　　　權限查詢:SHOW GROUND FOR ‘用戶名’@’主機名’;

　　　　權限授予:GRANT 權限列表(SELECT DELECT UPDATE) ON 數據庫.表名 TO ‘用戶名’@’主機名;

　　　　撤銷權限:REWORK 權限列表 ‘ON 數據庫 ‘用戶名’@’主機名’;