前段時間因為開學 所以到現在才更新 如有不足之處歡迎指出，
一、為什么要學習資料庫
1.1、資料庫的好處
? 1.持久化資料到本地
? 2.可以實作結構化查詢，方便管理
1.2、資料庫的相關概念
? 1、DB：資料庫，保存一組有組織的資料的容器
? 2、DBMS：資料庫管理系統，又稱為資料庫軟體（產品），用于管理DB中的資料
? 3、SQL:結構化查詢語言，用于和DBMS通信的語言
1.3、資料庫存盤資料的特點
? 1、將資料放到表中，表再放到庫中
? 2、一個資料庫中可以有多個表，每個表都有一個的名字，用來標識自己，表名具有唯一性，
? 3、表具有一些特性，這些特性定義了資料在表中如何存盤，類似java中 “類”的設計，
? 4、表由列組成，我們也稱為欄位，所有表都是由一個或多個列組成的，每一列類似java 中的”屬性”
? 5、表中的資料是按行存盤的，每一行類似于java中的“物件”，
常見的資料庫管理系統
mysql、oracle、db2、sqlserver
二、初始MySQL
2.1、MySQL服務的啟動和停止
? 方式一：計算機——右擊管理——服務
? 方式二：通過管理員身份運行
? net start 服務名（啟動服務）
? net stop 服務名（停止服務）
2.2、MySQL服務的登錄和退出
? 方式一：通過mysql自帶的客戶端
? 只限于root用戶
方式二：通過windows自帶的客戶端
登錄：
mysql 【-h主機名 -P埠號 】-u用戶名 -p密碼
1. mysql -uroot -p密碼
2. mysql -hip -uroot -p連接目標的密碼
3. mysql --host=ip --user=root --password=連接目標的密碼
退出：
1.exit或ctrl+C 2. quit
2.3、MySQL的常見命令
1.查看當前所有的資料庫
show databases;
2.打開指定的庫
use 庫名
3.查看當前庫的所有表
show tables;
4.查看其它庫的所有表
show tables from 庫名;
5.創建表
create table 表名(
列名 列型別,
列名 列型別，
，，，
);
6.查看表結構
desc 表名;
7.查看服務器的版本
方式一：登錄到mysql服務端
select version();
方式二：沒有登錄到mysql服務端
mysql --version
或
mysql --V
2.4、MySQL的語法規范
? 1.不區分大小寫,但建議關鍵字大寫，表名、列名小寫
? 2.每條命令最好用分號結尾
? 3.每條命令根據需要，可以進行縮進 或換行
? 4.注釋
? 單行注釋：#注釋文字
? 單行注釋：-- 注釋文字
? 多行注釋：/* 注釋文字 */
?
2.5、SQL的語言分類
? 1) DDL(Data Definition Language)資料定義語言
? 用來定義資料庫物件：資料庫，表，列等，關鍵字：create, drop,alter 等
? 2) DML(Data Manipulation Language)資料操作語言
? 用來對資料庫中表的資料進行增刪改，關鍵字：insert, delete, update 等
? 3) DQL(Data Query Language)資料查詢語言
? 用來查詢資料庫中表的記錄(資料)，關鍵字：select, where 等
? 4) DCL(Data Control Language)資料控制語言(了解)
? 用來定義資料庫的訪問權限和安全級別，及創建用戶，關鍵字：GRANT， REVOKE 等
? TCL（Transaction Control Language）：事務控制語言
? commit、rollback
2.6、SQL的常見命令
show databases； 查看所有的資料庫
use 庫名； 打開指定 的庫
show tables ; 顯示庫中的所有表
show tables from 庫名;顯示指定庫中的所有表
create table 表名(
欄位名 欄位型別,
欄位名 欄位型別
); 創建表
desc 表名; 查看指定表的結構
select * from 表名;顯示表中的所有資料
2.7、卸載
? 1. 去mysql的安裝目錄找到my.ini檔案
? * 復制 datadir=“C:/ProgramData/MySQL/MySQL Server 5.5/Data/”
? 2. 卸載MySQL
? 3. 洗掉C:/ProgramData目錄下的MySQL檔案夾，
三、DQL語言的學習
3.1、基礎查詢
一、語法：
SELECT 查詢串列
【FROM 表名】;
- 語法：
? select
? 欄位串列
? from
? 表名串列
? where
? 條件串列
? group by
? 分組欄位
? having
? 分組之后的條件
? order by
? 排序
? limit
? 分頁限定
類似于Java中 :System.out.println(要列印的東西);
特點：
①通過select查詢完的結果 ，是一個虛擬的表格，不是真實存在
② 要查詢的東西 可以是常量值、可以是運算式、可以是欄位、可以是函式
二、特點
1、查詢串列可以是欄位、常量、運算式、函式，也可以是多個
2、查詢結果是一個虛擬表
三、示例
1、查詢單個欄位
select 欄位名 from 表名;
2、查詢多個欄位
select 欄位名，欄位名 from 表名;
3、查詢所有欄位
select * from 表名
4、查詢常量
select 常量值;
注意：字符型和日期型的常量值必須用單引號引起來，數值型不需要
5、查詢函式
select 函式名(實參串列);
6、查詢運算式
select 100/1234;
7、起別名
①as
②空格
8、去重
select distinct 欄位名 from 表名;
計算列
* 一般可以使用四則運算計算一些列的值，（一般只會進行數值型的計算）
* ifnull(運算式1,運算式2)：null參與的運算，計算結果都為null
* 運算式1：哪個欄位需要判斷是否為null
* 如果該欄位為null后的替換值，
-- 計算math和english分數之和
SELECT NAME,math,english,math+english AS final FROM student;
-- 如果有null參與的運算，計算結果都為null
SELECT NAME,math,english,math+IFNULL(english,0) AS final FROM student;
9、+
作用：做加法運算
select 數值+數值; 直接運算
select 字符+數值;先試圖將字符轉換成數值，如果轉換成功，則繼續運算；否則轉換成0，再做運算
select null+值;結果都為null
10、【補充】concat函式
功能：拼接字符
select concat(字符1，字符2，字符3,...);
11、【補充】ifnull函式
功能：判斷某欄位或運算式是否為null，如果為null 回傳指定的值，否則回傳原本的值
select ifnull(commission_pct,0) from employees;
12、【補充】isnull函式
功能：判斷某欄位或運算式是否為null，如果是，則回傳1，否則回傳0
3.2、條件查詢
? 條件查詢：根據條件過濾原始表的資料，查詢到想要的資料
一、語法：
select 要查詢的欄位|運算式|常量值|函式
? from 表名
? where 條件 ;
分類：
1、條件運算式
示例：salary>10000
2. 運算子
條件運算子： * > 、< 、<= 、>= 、= 、<>
* BETWEEN...AND
* IN( 集合) 在某個范圍之內
* LIKE：模糊查詢
* 占位符：
* _:單個任意字符
* %：多個任意字符
* IS NULL
* and 或 &&
* or 或 ||
* not 或 !
3、邏輯運算式
示例：salary>10000 && salary<20000
邏輯運算子：
and（&&）:兩個條件如果同時成立，結果為true，否則為false
or(||)：兩個條件只要有一個成立，結果為true，否則為false
not(!)：如果條件成立，則not后為false，否則為true
三、模糊查詢
like:一般搭配通配符使用，可以判斷字符型或數值型
通配符：%任意多個字符，_任意單個字符
示例：last_name like 'a%'
between and
in
is null /is not null：用于判斷null值
is null PK <=>
普通型別的數值 null值 可讀性
is null × √ √
<=> √ √ ×
-- 查詢年齡大于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);
-- 查詢英語成績為null
SELECT * FROM student WHERE english = NULL; -- 不對的，null值不能使用 = （!=） 判斷
SELECT * FROM student WHERE english IS NULL;
-- 查詢英語成績不為null
SELECT * FROM student WHERE english IS NOT NULL;
-- 查詢姓馬的有哪些？ like
SELECT * FROM student WHERE NAME LIKE '馬%';
-- 查詢姓名第二個字是化的人
SELECT * FROM student WHERE NAME LIKE "_化%";
-- 查詢姓名是3個字的人
SELECT * FROM student WHERE NAME LIKE '___';
-- 查詢姓名中包含德的人
SELECT * FROM student WHERE NAME LIKE '%德%';
3.3、排序查詢
語法：
select
查詢串列
from
表
where
條件
order by 排序的欄位|運算式|函式|別名 【asc|desc】
* 注意：
* 如果有多個排序條件，則當前邊的條件值一樣時，才會判斷第二條件，
-- 按照數學成績排名，如果數學成績一樣，則按照英語成績排名
SELECT *FROM student ORDER BY math ,english ASC;
二、特點
1、asc ：升序，如果不寫默認升序
desc：降序
2、排序串列 支持 單個欄位、多個欄位、函式、運算式、別名
3、order by的位置一般放在查詢陳述句的最后（除limit陳述句之外）
?
3.4、常見函式
? 一、單行函式
? 1、字符函式
? concat 拼接
? substr 截取子串
? upper 轉換成大寫
? lower 轉換成小寫
? trim 去前后指定的空格和字符
? ltrim 去左邊空格
? rtrim 去右邊空格
? replace 替換
? lpad 左填充
? rpad 右填充
? instr 回傳子串第一次出現的索引
? length 獲取位元組個數
?
2、數學函式
round 四舍五入
rand 亂數
floor 向下取整
ceil 向上取整
mod 取余
truncate 截斷
3、日期函式
now 當前系統日期+時間
curdate 當前系統日期
curtime 當前系統時間
str_to_date 將字符轉換成日期
date_format 將日期轉換成字符
4、流程控制函式
if 處理雙分支
case陳述句 處理多分支
情況1：處理等值判斷
情況2：處理條件判斷
5、其他函式
version版本
database當前庫
user當前連接用戶
二、分組函式
sum 求和
max 最大值
min 最小值
avg 平均值
count 計數
特點：
1、以上五個分組函式都忽略null值，除了count(*)
2、sum和avg一般用于處理數值型
max、min、count可以處理任何資料型別
3、都可以搭配distinct使用，用于統計去重后的結果
4、count的引數可以支持：
欄位、*、常量值，一般放1
-- 使用聚合函式查看總人數
SELECT COUNT(NAME) AS '總人數' FROM student;
建議使用 count(*)
* 注意：聚合函式的計算，排除null值，
解決方案：如下代碼
1. 選擇不包含非空的列進行計算
2. IFNULL函式
SELECT COUNT(english) FROM student；
SELECT COUNT(IFNULL(english,0)) FROM student;
3.5、分組查詢
1、語法：
? select 查詢的欄位，分組函式
? from 表
? group by 分組的欄位
?2、注意：
-
分組之后查詢的欄位：分組欄位、聚合函式
-
where 和 having 的區別？
? 1. where 在分組之前進行限定，如果不滿足條件，則不參與分組，having在分組之后進行限定，如果不滿足結果，則不會被查詢出來
? 2. where 后不可以跟聚合函式，having可以進行聚合函式的判斷，
特點：
1、可以按單個欄位分組
2、和分組函式一同查詢的欄位最好是分組后的欄位
3、分組篩選
針對的表 位置 關鍵字
分組前篩選： 原始表 group by的前面 where
分組后篩選： 分組后的結果集 group by的后面 having
4、可以按多個欄位分組，欄位之間用逗號隔開
5、可以支持排序
6、having后可以支持別名
練習：
-- 按照性別分組，分別查詢男、女同學的平均分
SELECT sex , AVG(math) FROM student GROUP BY sex;
-- 按照性別分組，分別查詢男、女同學的平均分,人數
SELECT sex , AVG(math),COUNT(id) FROM student GROUP BY sex;
-- 按照性別分組，分別查詢男、女同學的平均分,人數 要求：分數低于60分的人，不參與分組
SELECT sex , AVG(math),COUNT(id) FROM student WHERE math > 60 GROUP BY sex;
-- 按照性別分組，分別查詢男、女同學的平均分,人數 要求：分數低于60分的人，不參與分組,分組之后，人數要大于2個人
SELECT sex , AVG(math),COUNT(id) FROM student WHERE math > 60 GROUP BY sex HAVING COUNT(id) > 2;
SELECT sex , AVG(math),COUNT(id) 人數 FROM student WHERE math > 60 GROUP BY sex HAVING 人數 > 2;
3.6、多表連接查詢
笛卡爾乘積：當查詢多個表時，沒有添加有效的連接條件，導致多個表所有行實作完全連接
如何解決：添加有效的連接條件
* 有兩個集合A,B .取這兩個集合的所有組成情況，
* 要完成多表查詢，需要消除無用的資料
3.6.1、傳統模式下的連接 ：等值連接——非等值連接
1、等值連接
語法：
select 查詢串列
from 表1 別名,表2 別名
where 表1.key = 表2.key
【and 篩選條件】
【group by 分組欄位】
【having 分組后的篩選】
【order by 排序欄位】
特點：
① 等值連接的結果 = 多個表的交集
② n表連接，至少需要n-1個連接條件
③ n表連接至少需要n-1個連接條件
④ 等值連接的結果是多表的交集部分
2、非等值連接
語法：
select 查詢串列
from 表1 別名,表2 別名
where 非等值的連接條件
【and 篩選條件】
【group by 分組欄位】
【having 分組后的篩選】
【order by 排序欄位】
3.6.2、多表查詢
1、內連接
語法：
select 查詢串列
from 表1 別名
【inner】 join 表2 別名 on 連接條件
where 篩選條件
group by 分組串列
having 分組后的篩選
order by 排序串列
limit 子句;
特點：
① 表的順序可以調換
② 內連接的結果=多表的交集
③ n表連接至少需要n-1個連接條件
分類：
等值連接
非等值連接
自連接
2、外連接
語法：
select 查詢串列
from 表1 別名
left|right|full【outer】 join 表2 別名 on 連接條件
where 篩選條件
group by 分組串列
having 分組后的篩選
order by 排序串列
limit 子句;
特點：
①查詢的結果=主表中所有的行，如果從表和它匹配的將顯示匹配行，如果從表沒有匹配的則顯示null
②left join 左邊的就是主表，right join 右邊的就是主表
full join 兩邊都是主表
③一般用于查詢除了交集部分的剩余的不匹配的行
練習：
* 準備sql
# 創建部門表
CREATE TABLE dept(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
);
INSERT INTO dept (NAME) VALUES ('開發部'),('市場部'),('財務部');
# 創建員工表
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) -- 外鍵，關聯部門表(部門表的主鍵)
);
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);
1. 內連接查詢：
1. 隱式內連接：使用where條件消除無用資料
* 例子：
-- 查詢所有員工資訊和對應的部門資訊
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. 顯式內連接：
* 語法： 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. 內連接查詢：
1. 從哪些表中查詢資料
2. 條件是什么
3. 查詢哪些欄位
2. 外鏈接查詢：
1. 左外連接：
* 語法：select 欄位串列 from 表1 left [outer] join 表2 on 條件；
* 查詢的是左表所有資料以及其交集部分，
* 例子：
-- 查詢所有員工資訊，如果員工有部門，則查詢部門名稱，沒有部門，則不顯示部門名稱
SELECT t1.*,t2.`name` FROM emp t1 LEFT JOIN dept t2 ON t1.`dept_id` = t2.`id`;
-- 查詢所有員工資訊，如果員工有部門，則查詢部門名稱，沒有部門，則不顯示部門名稱
SELECT t1.*,t2.`name` FROM emp t1 LEFT JOIN dept t2 ON t1.`dept_id` = t2.`id`;
2. 右外連接：
* 語法：select 欄位串列 from 表1 right [outer] join 表2 on 條件；
* 查詢的是右表所有資料以及其交集部分，
* 例子：
SELECT * FROM dept t2 RIGHT JOIN emp t1 ON t1.`dept_id` = t2.`id`;
3、交叉連接
語法：
select 查詢串列
from 表1 別名
cross join 表2 別名;
特點：
類似于笛卡爾乘積
?
3.6.3、自連接
案例：查詢員工名和直接上級的名稱
sql99
SELECT e.last_name,m.last_name
FROM employees e
JOIN employees m ON e.`manager_id`=m.`employee_id`;
sql92
SELECT e.last_name,m.last_name
FROM employees e,employees m
WHERE e.`manager_id`=m.`employee_id`;
3.7、子查詢
含義：
嵌套在其他陳述句內部的select陳述句稱為子查詢或內查詢，
外面的陳述句可以是insert、update、delete、select等，一般select作為外面陳述句較多
外面如果為select陳述句，則此陳述句稱為外查詢或主查詢
特點：
1、子查詢都放在小括號內
2、子查詢可以放在from后面、select后面、where后面、having后面，但一般放在條件的右側
3、子查詢優先于主查詢執行，主查詢使用了子查詢的執行結果
4、子查詢根據查詢結果的行數不同分為以下兩類：
① 單行子查詢
結果集只有一行
一般搭配單行運算子使用：> < = <> >= <=
非法使用子查詢的情況：
a、子查詢的結果為一組值
b、子查詢的結果為空
② 多行子查詢
結果集有多行
一般搭配多行運算子使用：any、all、in、not in
in： 屬于子查詢結果中的任意一個就行
any和all往往可以用其他查詢代替
* 子查詢不同情況
1. 子查詢的結果是單行單列的：
* 子查詢可以作為條件，使用運算子去判斷， 運算子： > >= < <= =
-- 查詢員工工資小于平均工資的人
SELECT * FROM emp WHERE emp.salary < (SELECT AVG(salary) FROM emp);
2. 子查詢的結果是多行單列的：
* 子查詢可以作為條件，使用運算子in來判斷
-- 查詢'財務部'和'市場部'所有的員工資訊
SELECT id FROM dept WHERE NAME = '財務部' OR NAME = '市場部';
SELECT * FROM emp WHERE dept_id = 3 OR dept_id = 2;
-- 子查詢
SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME = '財務部' OR NAME = '市場部');
3. 子查詢的結果是多行多列的：
* 子查詢可以作為一張虛擬表參與查詢
-- 查詢員工入職日期是2011-11-11日之后的員工資訊和部門資訊
-- 子查詢
SELECT * FROM dept t1 ,(SELECT * FROM emp WHERE emp.`join_date` > '2011-11-11') t2
WHERE t1.id = t2.dept_id;
-- 普通內連接
SELECT * FROM emp t1,dept t2 WHERE t1.`dept_id` = t2.`id` AND t1.`join_date` > '2011-11-11'
3.8、分頁查詢
語法：
select 欄位|運算式,...
from 表
【where 條件】
【group by 分組欄位】
【having 條件】
【order by 排序的欄位】
limit 【起始的條目索引，】條目數;
分頁查詢
1.語法：limit 開始的索引,每頁查詢的條數; 起始條目索引從0開始，limit子句放在查詢陳述句的最后
2. 公式：開始的索引 = （當前的頁碼 - 1） * 每頁顯示的條數
-- 每頁顯示3條記錄
SELECT * FROM student LIMIT 0,3; -- 第1頁
SELECT * FROM student LIMIT 3,3; -- 第2頁
SELECT * FROM student LIMIT 6,3; -- 第3頁
3.公式：select * from 表 limit （page-1）*sizePerPage,sizePerPage
假如:
每頁顯示條目數sizePerPage
要顯示的頁數 page
3.9、聯合查詢
引入：
union：合并、聯合，將多次查詢結果合并成一個結果
語法：
select 欄位|常量|運算式|函式 【from 表】 【where 條件】 union 【all】
select 欄位|常量|運算式|函式 【from 表】 【where 條件】 union 【all】
select 欄位|常量|運算式|函式 【from 表】 【where 條件】 union 【all】
.....
select 欄位|常量|運算式|函式 【from 表】 【where 條件】
特點：
1、多條查詢陳述句的查詢的列數必須是一致的
2、多條查詢陳述句的查詢的列的型別幾乎相同
3、union代表去重，union all代表不去重
四、DML語言的學習
4.1、插入
語法：
insert into 表名(欄位名，…)
values(值1，…);
特點：
* 注意：
1. 列名和值要一一對應，
2. 如果表名后，不定義列名，則默認給所有列添加值
insert into 表名 values(值1,值2,...值n);
3. 除了數字型別，其他型別需要使用引號(單雙都可以)引起來
一、方式一
語法：
insert into 表名(欄位名,...) values(值,...);
特點：
1、要求值的型別和欄位的型別要一致或兼容
2、欄位的個數和順序不一定與原始表中的欄位個數和順序一致
但必須保證值和欄位一一對應
3、假如表中有可以為null的欄位，注意可以通過以下兩種方式插入null值
①欄位和值都省略
②欄位寫上，值使用null
4、欄位和值的個數必須一致
5、欄位名可以省略，默認所有列
二、方式二
語法：
insert into 表名 set 欄位=值,欄位=值,...;
兩種方式 的區別：
1.方式一支持一次插入多行，語法如下：
insert into 表名【(欄位名,..)】 values(值，..),(值，...),...;
2.方式一支持子查詢，語法如下：
insert into 表名
查詢陳述句;
4.2、修改
修改單表語法：
update 表名 set 欄位=新值,欄位=新值
【where 條件】
修改多表語法：
update 表1 別名1,表2 別名2
set 欄位=新值，欄位=新值
where 連接條件
and 篩選條件
* 注意：
如果不加任何條件，則會將表中所有記錄全部修改，
4.3、洗掉
方式1：delete陳述句
一、洗掉單表的記錄★
語法：delete from 表名 【where 篩選條件】【limit 條目數】
二、級聯洗掉[補充]
語法：
delete 別名1,別名2 from 表1 別名
inner|left|right join 表2 別名
on 連接條件
【where 篩選條件】
方式2：truncate陳述句
truncate table 表名
兩種方式的區別【面試題】
一般洗掉速度 ：drop>truncate>delete
應用范圍：truncate只能對table 而delete可以是table和view
1.truncate洗掉后，如果再插入，標識列從1開始
delete洗掉后，如果再插入，標識列從斷點開始
2.delete可以添加篩選條件 truncate不可以添加篩選條件
3.truncate效率較高
4.truncate沒有回傳值
delete可以回傳受影響的行數
5.truncate不可以回滾
delete可以回滾
五、DCL語言的學習
DCL：管理用戶，授權
- DBA：資料庫管理員
- DCL：管理用戶，授權
5.1、管理用戶
1. 添加用戶：
* 語法：CREATE USER '用戶名'@'主機名' IDENTIFIED BY '密碼';
2. 洗掉用戶：
* 語法：DROP USER '用戶名'@'主機名';
3. 修改用戶密碼：
UPDATE USER SET PASSWORD = PASSWORD('新密碼') WHERE USER = '用戶名';
UPDATE USER SET PASSWORD = PASSWORD('abc') WHERE USER = 'lisi';
SET PASSWORD FOR '用戶名'@'主機名' = PASSWORD('新密碼');
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123');
* mysql中忘記了root用戶的密碼？
1. cmd -- > net stop mysql 停止mysql服務
* 需要管理員運行該cmd
2. 使用無驗證方式啟動mysql服務： mysqld --skip-grant-tables
3. 打開新的cmd視窗,直接輸入mysql命令，敲回車，就可以登錄成功
4. use mysql;
5. update user set password = password('你的新密碼') where user = 'root';
6. 關閉兩個視窗
7. 打開任務管理器，手動結束mysqld.exe 的行程
8. 啟動mysql服務
9. 使用新密碼登錄，
4. 查詢用戶：
-- 1. 切換到mysql資料庫
USE myql;
-- 2. 查詢user表
SELECT * FROM USER;
* 通配符： % 表示可以在任意主機使用用戶登錄資料庫
5.2 、權限管理
1. 查詢權限：
-- 查詢權限
SHOW GRANTS FOR '用戶名'@'主機名';
SHOW GRANTS FOR 'lisi'@'%';
2. 授予權限：
-- 授予權限
grant 權限串列 on 資料庫名.表名 to '用戶名'@'主機名';
-- 給張三用戶授予所有權限，在任意資料庫任意表上
GRANT ALL ON *.* TO 'wanghui'@'localhost';
3. 撤銷權限：
-- 撤銷權限：
revoke 權限串列 on 資料庫名.表名 from '用戶名'@'主機名';
REVOKE UPDATE ON wh.`account` FROM 'lisi'@'%';
六、DDL語言的學習
6.1、庫和表的管理
庫的管理：
1. 操作資料庫：CRUD
1. C(Create):創建
* 創建資料庫：
* create database 資料庫名稱;
* 創建資料庫，判斷不存在，再創建：
* create database if not exists 資料庫名稱;
* 創建資料庫，并指定字符集
* create database 資料庫名稱 character set 字符集名;
* 練習： 創建wh資料庫，判斷是否存在，并制定字符集為gbk
* create database if not exists wh character set gbk;
2. R(Retrieve)：查詢
* 1.查詢所有資料庫的名稱:
* show databases;
* 2.查詢某個資料庫的字符集:查詢某個資料庫的創建陳述句
* show create database 資料庫名稱;
3. U(Update):修改
* 修改資料庫的字符集
* alter database 資料庫名稱 character set 字符集名稱;
alter database wh character set utf8; //這里我們不能寫utf-8
4. D(Delete):洗掉
* 洗掉資料庫
* drop database 資料庫名稱;
* 判斷資料庫存在，存在再洗掉
* drop database if exists 資料庫名稱;
5. 使用資料庫
* 查詢當前正在使用的資料庫名稱
* select database();
* 使用資料庫
* use 資料庫名稱;
表的管理：
2. 操作表
1. C(Create):創建 重點
1. 語法：
create table 表名(
列名1 資料型別1,
列名2 資料型別2,
....
列名n 資料型別n
);
* 注意：最后一列，不需要加逗號（,）
* 創建表
create table student(
id int,
name varchar(32),
age int ,
score double(4,1),
birthday date,
insert_time timestamp
);
* 復制表：
* create table 表名 like 被復制的表名;
2. R(Retrieve)：查詢
* 查詢某個資料庫中所有的表名稱
* show tables;
* 查詢表結構
* desc 表名;
3. U(Update):修改
1. 修改表名
alter table 表名 rename to 新的表名;
2. 修改表的字符集
alter table 表名 character set 字符集名稱;
3. 添加一列
alter table 表名 add 列名 資料型別;
4. 修改列名稱 型別
alter table 表名 change 列名 新列別 新資料型別;
alter table 表名 modify 列名 新資料型別; 只改型別
5. 洗掉列
alter table 表名 drop 列名;
4. D(Delete):洗掉
* drop table 表名;
* drop table if exists 表名 ;
6.2、常見型別
一、數值型
1、整型
tinyint、smallint、mediumint、int/integer、bigint
1 2 3 4 8
特點：
①都可以設定無符號和有符號，默認有符號，通過unsigned設定無符號
②如果超出了范圍，會報out or range例外，插入臨界值
③長度可以不指定，默認會有一個長度
長度代表顯示的最大寬度，如果不夠則左邊用0填充，但需要搭配zerofill，并且默認變為無符號整型
2、浮點型
定點數：decimal(M,D)
浮點數:
float(M,D) 4
double(M,D) 8
特點：
①M代表整數部位+小數部位的個數，D代表小數部位
②如果超出范圍，則報out or range例外，并且插入臨界值
③M和D都可以省略，但對于定點數，M默認為10，D默認為0
④如果精度要求較高，則優先考慮使用定點數
二、字符型
char、varchar、binary、varbinary、enum、set、text、blob
char：固定長度的字符，寫法為char(M)，最大長度不能超過M，其中M可以省略，默認為1
varchar：可變長度的字符，寫法為varchar(M)，最大長度不能超過M，其中M不可以省略
三、日期型
year年
date日期
time時間
datetime 日期+時間 8
timestamp 日期+時間 4 比較容易受時區、語法模式、版本的影響，更能反映當前時區的真實時間
6.3、常見約束
NOT NULL：非空，該欄位的值必填
UNIQUE：唯一，該欄位的值不可重復
DEFAULT：默認，該欄位的值不用手動插入有默認值
CHECK：檢查，mysql不支持
PRIMARY KEY：主鍵，該欄位的值不可重復并且非空 unique+not null
FOREIGN KEY：外鍵，該欄位的值參考了另外的表的欄位
主鍵和唯一
1、區別：
①、一個表至多有一個主鍵，但可以有多個唯一
②、主鍵不允許為空，唯一可以為空
2、相同點
都具有唯一性
都支持組合鍵，但不推薦
外鍵：
1、用于限制兩個表的關系，從表的欄位值參考了主表的某欄位值
2、外鍵列和主表的被參考列要求型別一致，意義一樣，名稱無要求
3、主表的被參考列要求是一個key（一般就是主鍵）
4、插入資料，先插入主表
洗掉資料，先洗掉從表
可以通過以下兩種方式來洗掉主表的記錄
分類：
1. 級聯更新：ON UPDATE CASCADE
2. 級聯洗掉：ON DELETE CASCADE
#方式一：級聯洗掉
語法：ALTER TABLE 表名 ADD CONSTRAINT 外鍵名稱
OREIGN KEY (外鍵欄位名稱) REFERENCES 主表名稱(主表列名稱)
ON UPDATE CASCADE ON DELETE CASCADE ;
ALTER TABLE stuinfo ADD CONSTRAINT fk_stu FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE CASCADE;
#方式二：級聯置空
ALTER TABLE stuinfo ADD CONSTRAINT fk_stu FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE SET NULL;
二、創建表時添加約束
create table 表名(
欄位名 欄位型別 not null,#非空
欄位名 欄位型別 primary key,#主鍵
欄位名 欄位型別 unique,#唯一
欄位名 欄位型別 default 值,#默認
constraint 約束名 foreign key(欄位名) references 主表（被參考列）
)
注意：
支持型別 可以起約束名
列級約束 除了外鍵 不可以
表級約束 除了非空和默認 可以，但對主鍵無效
列級約束可以在一個欄位上追加多個，中間用空格隔開，沒有順序要求
三、修改表時添加或洗掉約束
1、非空
添加非空
alter table 表名 modify column 欄位名 欄位型別 not null;
洗掉非空
alter table 表名 modify column 欄位名 欄位型別 ;
2、默認
添加默認
alter table 表名 modify column 欄位名 欄位型別 default 值;
洗掉默認
alter table 表名 modify column 欄位名 欄位型別 ;
3、主鍵
添加主鍵
alter table 表名 add【 constraint 約束名】 primary key(欄位名);
洗掉主鍵
alter table 表名 drop primary key;
4、唯一
添加唯一
alter table 表名 add【 constraint 約束名】 unique(欄位名);
洗掉唯一
alter table 表名 drop index 索引名;
5、外鍵
添加外鍵
alter table 表名 add【 constraint 約束名】 foreign key(欄位名) references 主表（被參考列）;
洗掉外鍵
alter table 表名 drop foreign key 約束名;
四、自增長列
1.概念：如果某一列是數值型別的，使用 auto_increment 可以來完成值得自動增長
2、一個表至多有一個自增長列
3、自增長列只能支持數值型
4、自增長列必須為一個key
一.在創建表時，添加主鍵約束，并且完成主鍵自增長
create table stu(
id int primary key auto_increment,-- 給id添加主鍵約束
name varchar(20)
);
二. 洗掉自動增長 alter table 表 modify column 欄位名 欄位型別 約束
ALTER TABLE stu MODIFY id INT;
三. 添加自動增長 alter table 表 modify column 欄位名 欄位型別 約束 auto_increment
ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;
七、TCL語言的學習
7.1、資料庫事務
一、含義
事務：一潭訓多條sql陳述句組成一個執行單位，一組sql陳述句要么都執行要么都不執行
二、特點（ACID）
A 原子性：一個事務是不可再分割的整體，要么都執行要么都不執行
C 一致性：一個事務可以使資料從一個一致狀態切換到另外一個一致的狀態
I 隔離性：一個事務不受其他事務的干擾，多個事務互相隔離的
D 持久性：一個事務一旦提交了，則永久的持久化到本地
相關步驟：
1. 開啟事務： start transaction;
2. 回滾：rollback;
3. 提交：commit;
* 修改事務的默認提交方式：
* 查看事務的默認提交方式：SELECT @@autocommit; -- 1 代表自動提交 0 代表手動提交
* 修改默認提交方式： set @@autocommit = 0;
事務的分類：
隱式事務，沒有明顯的開啟和結束事務的標志
比如
insert、update、delete陳述句本身就是一個事務
顯式事務，具有明顯的開啟和結束事務的標志
①開啟事務
set autocommit=0;
start transaction;#可以省略
②撰寫一組邏輯sql陳述句
注意：sql陳述句支持的是insert、update、delete
設定回滾點：
savepoint 回滾點名;
③結束事務
提交：commit;
回滾：rollback;
回滾到指定的地方：rollback to 回滾點名;
使用到的關鍵字
set autocommit=0;
start transaction;
commit;
rollback;
savepoint 斷點
commit to 斷點
rollback to 斷點
7.2、事務的隔離級別:
事務并發問題如何發生？
當多個事務同時操作同一個資料庫的相同資料時
事務的并發問題有哪些？
臟讀：一個事務讀取到了另外一個事務未提交的資料
不可重復讀：同一個事務中，多次讀取到的資料不一致
幻讀：一個事務讀取資料時，另外一個事務進行更新，導致第一個事務讀取到了沒有更新的資料
如何避免事務的并發問題？
通過設定事務的隔離級別
1、READ UNCOMMITTED 讀未提交 * 產生的問題：臟讀、不可重復讀、幻讀
2、READ COMMITTED 可以避免臟讀 讀已提交 （Oracle） * 產生的問題：不可重復讀、幻讀
3、REPEATABLE READ 可以避免臟讀、不可重復讀和一部分幻讀 可重復讀 （MySQL默認）* 產生的問題：幻讀
4、SERIALIZABLE 可以避免臟讀、不可重復讀和幻讀 串行化 * 可以解決所有的問題
設定隔離級別：
set session|global transaction isolation level 隔離級別名;
查看隔離級別：
select @@tx_isolation;
八、視圖
含義：理解成一張虛擬的表
視圖和表的區別：
使用方式 占用物理空間
視圖 完全相同 不占用，僅僅保存的是sql邏輯
表 完全相同 占用
視圖的好處：
好處：
1、簡化sql陳述句
2、提高了sql的重用性
3、保護基表的資料，提高了安全性
視圖的創建
語法：
CREATE VIEW 視圖名
AS
查詢陳述句;
視圖的增刪改查
1、查看視圖的資料 ★
SELECT * FROM wh;
SELECT * FROM wh WHERE last_name='Partners';
2、插入視圖的資料
INSERT INTO wh(last_name,department_id) VALUES('孫悟空',90);
3、修改視圖的資料
UPDATE wh SET last_name ='齊天大圣' WHERE last_name='孫悟空';
4、洗掉視圖的資料
drop view 視圖1，視圖2,...;
###某些視圖不能更新
包含以下關鍵字的sql陳述句：分組函式、distinct、group by、having、union或者union all
常量視圖
Select中包含子查詢
join
from一個不能更新的視圖
where子句的子查詢參考了from子句中的表
視圖邏輯的更新
#方式一：
CREATE OR REPLACE VIEW wh
AS
SELECT last_name FROM employees
WHERE employee_id>100;
#方式二:
ALTER VIEW wh
AS
SELECT employee_id FROM employees;
SELECT * FROM wh;
視圖的洗掉
DROP VIEW test_v1,test_v2,test_v3;
###視圖結構的查看
desc 視圖名;
show create view 視圖名;
視圖和表的對比
關鍵字 是否占用物理空間 使用
視圖 view 占用較小，只保存sql邏輯 一般用于查詢
表 table 保存實際的資料 增刪改查
九、變數
9.1、系統變數
分類
說明：變數由系統提供的，不用自定義
語法：
①查看系統變數
show 【global|session 】 variables like ''; 如果沒有顯式宣告global還是session，則默認是session
②查看指定的系統變數的值
select @@【global|session】.變數名; 如果沒有顯式宣告global還是session，則默認是session
③為系統變數賦值
方式一：
set 【global|session 】 變數名=值; 如果沒有顯式宣告global還是session，則默認是session
方式二：
set @@global.變數名=值;
set @@變數名=值；
1、全域變數
服務器層面上的，必須擁有super權限才能為系統變數賦值，作用域為整個服務器，也就是針對于所有連接（會話）有效
作用域：針對于所有會話（連接）有效，但不能跨重啟
查看所有全域變數
SHOW GLOBAL VARIABLES;
查看滿足條件的部分系統變數
SHOW GLOBAL VARIABLES LIKE '%char%';
查看指定的系統變數的值
SELECT @@global.autocommit;
為某個系統變數賦值
SET @@global.autocommit=0;
SET GLOBAL autocommit=0;
2、會話變數
服務器為每一個連接的客戶端都提供了系統變數，作用域為當前的連接（會話）
作用域：針對于當前會話（連接）有效
查看所有會話變數
SHOW SESSION VARIABLES;
查看滿足條件的部分會話變數
SHOW SESSION VARIABLES LIKE '%char%';
查看指定的會話變數的值
SELECT @@autocommit;
SELECT @@session.tx_isolation;
為某個會話變數賦值
SET @@session.tx_isolation='read-uncommitted';
SET SESSION tx_isolation='read-committed';
9.2、自定義變數
說明：
1、用戶變數
作用域：針對于當前連接（會話）生效
位置：begin end里面，也可以放在外面
使用：
①宣告并賦值：
set @變數名=值;或
set @變數名:=值;或
select @變數名:=值;
②更新值
方式一：
set @變數名=值;或
set @變數名:=值;或
select @變數名:=值;
方式二：
select xx into @變數名 from 表;
③使用
select @變數名;
2、區域變數
作用域：僅僅在定義它的begin end中有效
位置：只能放在begin end中，而且只能放在第一句
使用：
①宣告
declare 變數名 型別 【default 值】;
②賦值或更新
方式一：
set 變數名=值;或
set 變數名:=值;或
select @變數名:=值;
方式二：
select xx into 變數名 from 表;
③使用
select 變數名;
二者的區別：
作用域 定義位置 語法
用戶變數 當前會話 會話的任何地方 加@符號，不用指定型別
區域變數 定義它的BEGIN END中 BEGIN END的第一句話 一般不用加@,需要指定型別
###分支
一、if函式
語法：if(條件，值1，值2)
特點：可以用在任何位置
二、case陳述句
語法：
情況一：類似于switch
case 運算式
when 值1 then 結果1或陳述句1(如果是陳述句，需要加分號)
when 值2 then 結果2或陳述句2(如果是陳述句，需要加分號)
...
else 結果n或陳述句n(如果是陳述句，需要加分號)
end 【case】（如果是放在begin end中需要加上case，如果放在select后面不需要）
情況二：類似于多重if
case
when 條件1 then 結果1或陳述句1(如果是陳述句，需要加分號)
when 條件2 then 結果2或陳述句2(如果是陳述句，需要加分號)
...
else 結果n或陳述句n(如果是陳述句，需要加分號)
end 【case】（如果是放在begin end中需要加上case，如果放在select后面不需要）
特點：
可以用在任何位置
三、if elseif陳述句
語法：
if 情況1 then 陳述句1;
elseif 情況2 then 陳述句2;
...
else 陳述句n;
end if;
特點：
只能用在begin end中！！！！！！！！！！！！！！！
三者比較：
應用場合
if函式 簡單雙分支
case結構 等值判斷 的多分支
if結構 區間判斷 的多分支
###回圈
語法：
【標簽：】WHILE 回圈條件 DO
回圈體
END WHILE 【標簽】;
特點：
只能放在BEGIN END里面
如果要搭配leave跳轉陳述句，需要使用標簽，否則可以不用標簽
leave類似于java中的break陳述句，跳出所在回圈！！！
十、存盤程序和函式
10.1、存盤程序
含義：一組經過預先編譯的sql陳述句的集合
好處：
1、提高了sql陳述句的重用性，減少了開發程式員的壓力
2、提高了效率
3、減少了傳輸次數
分類：
1、無回傳無參
2、僅僅帶in型別，無回傳有參
3、僅僅帶out型別，有回傳無參
4、既帶in又帶out，有回傳有參
5、帶inout，有回傳有參
注意：in、out、inout都可以在一個存盤程序中帶多個
一 創建存盤程序
語法：
create procedure 存盤程序名(in|out|inout 引數名 引數型別,...)
begin
存盤程序體
end
注意：
1.引數模式：in、out、inout，其中in可以省略
2.存盤程序體的每一條sql陳述句都需要用分號結尾
類似于方法：
修飾符 回傳型別 方法名(引數型別 引數名,...){
方法體;
}
注意
1、需要設定新的結束標記
delimiter 新的結束標記
示例：
delimiter $
CREATE PROCEDURE 存盤程序名(IN|OUT|INOUT 引數名 引數型別,...)
BEGIN
sql陳述句1;
sql陳述句2;
END $
2、存盤程序體中可以有多條sql陳述句，如果僅僅一條sql陳述句，則可以省略begin end
3、引數前面的符號的意思
in:該引數只能作為輸入 （該引數不能做回傳值）
out：該引數只能作為輸出（該引數只能做回傳值）
inout：既能做輸入又能做輸出
二、呼叫
call 存盤程序名(實參串列)
舉例：
呼叫in模式的引數：call sp1（‘值’）;
呼叫out模式的引數：set @name; call sp1(@name);select @name;
呼叫inout模式的引數：set @name=值; call sp1(@name); select @name;
三、查看
show create procedure 存盤程序名;
四、洗掉
drop procedure 存盤程序名;
10.2、函式
###創建函式
學過的函式：LENGTH、SUBSTR、CONCAT等
語法：
一、創建
CREATE FUNCTION 函式名(引數名 引數型別,...) RETURNS 回傳型別
BEGIN
函式體
END
注意：函式體中肯定需要有return陳述句
二、呼叫
select 函式名(實參串列);
三、查看
show create function 函式名;
四、洗掉
drop function 函式名；
###函式和存盤程序的區別
關鍵字 呼叫語法 回傳值 應用場景
函式 FUNCTION SELECT 函式() 只能是一個 一般用于查詢結果為一個值并回傳時，當有回傳值而且僅僅一個
存盤程序 PROCEDURE CALL 存盤程序() 可以有0個或多個 一般用于更新
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/34423.html
標籤：其他