MySQL-1
-
- 一、MySQL介紹
- 二、MySQL與Navicat 安裝
- 三、建立表
- 四、SQL語言
-
- 建立表
- DDL建立
- DML增删改
- DQL查
- 五、函數
- 六、連接配接
- 七、統計函數
- 八、子查詢
-
- 子查詢定義
- 子查詢的位置
- 相關子查詢
- 增強版修改和删除
- 九、其他
-
- 導入導出資料庫
- union 和union all 差別
- 限制
- 視圖
- 索引
一、MySQL介紹
MySQL 是時下最流行的關系型資料庫管理系統，很多企業都使用MySQL，優點自行百度。
由于MySQL是資料庫管理系統，是以我們在學習的時候需要安裝一個伺服器端(MySQL)和一個用戶端(Navicat)。
二、MySQL與Navicat 安裝
安裝的版本如下，教程就不做記錄了，需要安裝包可留言。
三、建立表
MySQL裡面可以建立很多的資料庫，例如，建立一個企業OA系統的資料庫
資料庫裡可以有很多張表(table)，資料是存儲在表裡，如：
| 部門編号 | 部門名稱 | 部門位址 | 備注 |
|---|---|---|---|
| 1 | 開發部 | 大連高新園區 | 無 |
| 2 | 人事部 | 大連高新園區 | 無 |
表裡的列也叫做字段，一行資料也叫做一條記錄
- 在左側空白處右鍵，建立資料庫，自定義資料庫名，字元集與排序規則可為空，字元集預設為UTF-8；
Java全棧大資料學習筆記NO.14（MySQL-1） - 右鍵點選表-建立表，這時就在建立字段了，每個字段代表該列存放的資訊；
Java全棧大資料學習筆記NO.14（MySQL-1） - 名就：字段名
- 類型按需要選擇，這裡介紹一下字元串類型varchar和char
- char類型是固定長度字元串，按照長度來配置設定空間
- varchar類型是可變長度字元串，根據存儲資料的長度來配置設定空間，但是不能超多指定的最大值
- 不是null選擇之後在建立記錄的時候就必須要設定值
- 鑰匙：主鍵，任何一張表，都建議設一個主鍵，主鍵是唯一辨別表裡的每一條記錄，我們可以指定某個列作為主鍵，該列的值不允許為null也不允許重複。如果表裡沒有哪些合适的列作為主鍵，可以加一個自動增長辨別列作為主鍵。
四、SQL語言
任何資料庫操作，都可以通過sql來實作，按sql語句功能的不同，可将sql進行分類
- DDL語言，實作建立，修改或删除資料庫對象，叫做資料庫定義語言
- DML語言，資料操作語言，做表裡的資料做增删改的功能
- DQL語言，資料查詢語言，對表裡的資料做查詢操作的功能
- DCL語言，資料控制語言，控制資料庫使用者權限的語言
- TCL語言，事務控制語言。
sql語言有兩個版本，92版和99版，99是92的更新
在左側資料庫中右鍵點選查詢-建立查詢，在打開的查詢編輯器中編寫程式，注意儲存
建立表
在代碼介紹之前，先建立4個表，以便下面代碼實作
表名：dept（部門編号，部門名稱，部門所在地）
表名：emp（員工編号，員工名字，員工職務，所屬上級編号，入職日期，工資，獎金，所在部門編号）
表名：job_grades（職務等級，最低工資，最高工資）
表名：jobs（職務編号，職務名稱，最低工資，最高工資）
DDL建立
是針對資料庫對象的建立、修改或者删除，關鍵字對應的是 create、alter、drop，這部分隻需能看懂即可。
-- 建立表 dept
DROP TABLE IF EXISTS `dept`; -- 如果表dept存在就删除該表
CREATE TABLE `dept` ( -- 建立表dept的字段
`deptno` int(2) NOT NULL COMMENT '部門編号',
`dname` varchar(14) DEFAULT NULL COMMENT '部門名稱',
`loc` varchar(13) DEFAULT NULL COMMENT '部門位址',
PRIMARY KEY (`deptno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 删除表dept中的 deptno 字段
ALTER TABLE dept DROP deptno
-- 在表 dept 後面追加 daa 字段
ALTER TABLE dept ADD (`daa` varchar(10) DEFAULT NULL COMMENT '部門xx');
DML增删改
在此之前首先聲明，MySQL中的語言不區分大小寫，可以将程式選中之後右鍵單獨執行選中程式，添加注釋的方法為ctrl+/
-
添加資料
文法：
INSERT INTO 表名(列名清單)VALUES(值清單) 可選取部分字段進行指派，其他為空
INSERT INTO 表名 VALUES(值清單) 必須對全部字段進行指派
INSERT INTO 表名 VALUES(值清單),VALUES(值清單),~~~ 指派多個資料
INSERT INTO dept (id,dname,loc) VALUES(2,'綜合部','沈陽'); INSERT INTO dept VALUES(3,'财務部','大連'); INSERT INTO dept VALUES(4,'業務部','大連'),(5,'人力部','大連'); -
删除資料
文法：
delete from 表名 删除整張表的資料
delete from 表名 where 條件 按條件删除
DELETE FROM dept; DELETE FROM dept WHERE loc = '大連' or id = 4; -
修改資料
文法：
update 表名 set 列名 = 新值，列名 = 新值… 修改整張表裡所有記錄相關字段的值
update 表名 set 列名 = 新值，列名 = 新值… where 條件 修改符合條件的所有記錄的相關字段的值
UPDATE dept SET dname = '滕泰' WHERE loc = '大連'; UPDATE dept SET dname = '财務部',loc = '軟體園' WHERE id = 3;
DQL查
- 查詢資料：select 列名清單（或者是衍生列） from 表名
-- 查詢全部列 SELECT * from emp; -- 1 查詢每個員工的編号，姓名，工資，新工資 = 原始工資上浮25% -- FROM emp as e 代表在衍生列中可用 e 代表 emp SELECT e.empno,e.ename,e.sal*1.25 as newsal FROM emp as e; -
帶條件的查詢語句文法：select 列名清單（或者是衍生列） from 表名 where 條件
關系運算符：=，!=(<>),>,>=,<,<=
邏輯運算符：and 與，or 或，not 非
算術運算符：+，-，*，/
-- 2 查詢在7900号部門員工的資訊 SELECT * FROM emp as e WHERE e.empno=7900; -- 3 查詢1985年以前入職的員工 SELECT * FROM emp WHERE hiredate < '1985-1-1'; -- 4 查詢每個員工姓名，工資，獎金，實發工資（工資+獎金） SELECT e.ename,e.sal,e.comm,(e.sal+e.comm) as newsal FROM emp as e; - null值與任何資料做數學運算結果一定為null，null值與任何資料做比較，結果一定為false
- 去掉查詢結果集合裡的重複資料，用distinct關鍵字
- 特殊的關系運算符
- 空值驗證使用 is 或者是 is not-
-- 5 查詢獎金為空的員工的資訊/查詢沒有部門的員工 SELECT * FROM emp WHERE comm IS NULL; SELECT * FROM emp WHERE deptno IS NULL; -- 6 查詢獎金不為空的員工的資訊 SELECT * FROM emp WHERE comm IS NOT NULL; - Between and 查詢在某個範圍内，隻能做數字類型和日期類型的範圍判斷
-- 7 查詢工資在3000到5000之間的員工資訊（兩種方法） SELECT * FROM emp WHERE sal>=3000 AND sal<=5000; SELECT * FROM emp WHERE sal BETWEEN 3000 AND 5000; -- 8 查詢在85年到90年之間入職的員工資訊（兩種方法） SELECT * FROM emp WHERE hiredate>='1985-1-1' AND hiredate<='1990-12-31'; SELECT * FROM emp WHERE hiredate BETWEEN '1985-1-1' AND '1990-12-31'; -
In(值清單)：是邏輯或，判斷的字段的值隻要等于（）裡的其中一個資料就ok
Not in (值清單)：是邏輯與，判斷的字段的值不能等于（）裡任何一個資料
-- 9 查詢（7369，7521，7654）以外的所有員工的資訊 SELECT * FROM emp WHERE empno=7369 OR empno=7521 OR empno=7654; SELECT * FROM emp WHERE empno not in (7369,7521,7654); -- 10 查詢獎金不為300和500的員工的資訊 SELECT * FROM emp WHERE comm is null or comm not in (300,500); -
模糊查詢，like，隻有字元串類型的字段可以做模糊查詢
如果使用like做模糊查詢，必須和通配符一起使用才能達到效果
%:代表任意長度的字元串
_:代表一個長度的任意字元
通過轉移字元 \ 可将通配符轉化成普通字元-- 11 查詢ename是以S開頭的所有員工資訊 %：通配符，代表任意長度的字元串 SELECT * FROM emp WHERE ename like 's%'; -- 12 查詢ename是以s結尾的所有員工的資訊 _：代表一個長度的任意字元 SELECT * FROM emp WHERE ename like '%s'; -- 13 查詢ename是以M開頭，并且第三個字母是R的員工資訊 SELECT * FROM emp WHERE ename like 'M_R%'; -- 14 查詢ename中包含AL的員工資訊 SELECT * FROM emp WHERE ename like '%AL%'; -- 15 查詢工資超過5000，并且ename是以s結尾的員工 SELECT * FROM emp WHERE sal>2000 and ename like '%s';-- 16 查詢名字中含有 _ % 的員工 SELECT * FROM emp WHERE ename like '%\_%';
- 空值驗證使用 is 或者是 is not-
-
對查詢結果進行排序：order by 排序列 asc(升序)/desc降序
Order by後面可以寫：1）列名2）列的索引3）列的表名 4）表達式
排序也可以按多列來排序，按order by 後面的排序列的順序，進行主次排序
-- 17 查詢員工資訊，按入職日期降序排列 升序ASC 降序DESC SELECT * FROM emp ORDER BY hiredate DESC; -- 18 查詢員工資訊，按工資升序，按編号降序 SELECT empno,ename,sal,hiredate as date FROM emp ORDER BY sal ASC,empno DESC; -
限制查詢傳回條數：limit
LIMIT n 查詢的是前n條資料
LIMIT n,m 查詢的是從第n+1條開始，傳回m條資料
-- 19 查詢員工表的前5條資料 SELECT * FROM emp LIMIT 5; -- 20 查詢員工表第 6,7 條資料 SELECT * FROM emp LIMIT 5,2;
五、函數
和java裡的方法差不多，可以實作某些特定的功能，傳遞參數，有傳回值
數學函數：
- 向上取整，dual是一個虛拟庫，可作練習用
- 向下取整
- 生産0-1之間的随機小數
- 四舍五入，第二個參數保留精度，也可以不設定精度，預設取整
- 截斷數字，第二個參數是保留精度
字元串函數：
- 将多個字元串拼成一個字元串
- 擷取字元串字元的個數
- LOWER 将字元串轉換為小寫，UPPER 将字元串轉換為大寫
-
傳回第一個字元串在第二個字元串中的位置，如果沒有則傳回0
LEFT 在字元串左側截取指定個數字元，RIGHT 在字元串右側截取指定個數字元
- 從字元串的指定位置截取指定長度的字元串
- 替換字元串，第一個參數是原字元串，第二個參數是被替換的子串，第三個參數是替換後的新子串
日期函數：
- 擷取目前系統時間
- 分别擷取日期的年、月、日、時、分、秒部分
SELECT YEAR(NOW()),MONTH(NOW()),DAY(NOW()),HOUR(NOW()), MINUTE(NOW()),SECOND(NOW()) from dual; - 将日期格式化成指定格式的字元串，此處大小寫有差別（Y%和y%）
- 兩個日期相減，傳回天數
- 在日期的指定部分加值（正負均可）
SELECT DATE_ADD('2021-10-20',INTERVAL -1 YEAR) from dual; SELECT DATE_ADD('2021-10-20',INTERVAL 1 MONTH) from dual; SELECT DATE_ADD('2021-10-20',INTERVAL 1 DAY) from dual; - 擷取兩個日期的指定時間差
通用函數：
- 空值替換處理，把空值替換成指定值
六、連接配接
MySQL是關系型資料庫，各個表之間不是獨立存在，它們之間可能會存在主外鍵關系，比如
部門表 ：
| 部門編号 | 部門名稱 |
|---|---|
| 10 | 開發部 |
| 20 | 綜合部 |
員工表：
| 員工編号 | 部門編号 |
|---|---|
| 7639 | 10 |
| 7896 | 30 |
當一個表的某個列的資料來源于另外一張表，那麼該列可以設定成外鍵。一旦兩張表建立了主外鍵關系，那麼如果在外鍵表（從表）插入的值在主鍵表（主表）裡不存在，系統要報主外鍵錯誤！
如果要删除主表的資料，如果該資料在從表裡有關聯資料，需要先删從表資料，再删主表資料。
多表連接配接查詢分為内連接配接和外連接配接
-
内連接配接查詢
内連接配接的原理，當兩表按關聯條件内連接配接查詢時，符合關聯條件的資料，出現在結果集裡，沒有關聯上的資料全部過濾掉
-- 1. 查詢員工編号，姓名，所在部門名稱（emp和dept）92文法内連接配接查詢語句 SELECT e.empno,e.ename,d.dname FROM emp e,dept d WHERE e.deptno = d.deptno; -- 2 查詢員工編号，姓名，部門編号，部門名稱，職務編号，職務名稱（三表聯查） SELECT e.empno,e.ename,e.deptno,d.dname,j.job_id,job_title FROM emp e,dept d,jobs j WHERE e.deptno=d.deptno and e.job=j.job_id;--99文法：select 多表的列名清單 from table1 INNER JOIN table2 on 關聯條件 -- INNER JOIN table3 on 關聯條件 -- JOIN table4 on 關聯條件(INNER可省略) -- WHERE 其他條件-- 3 查詢員工編号，姓名，所在部門編号，部門的名稱 SELECT e.empno,e.ename,e.deptno,d.dname FROM emp e INNER JOIN dept d on e.deptno=d.deptno; -- 4 查詢部門編号是10的員工編号，姓名，部門編号，部門名稱，職務編号，職務名稱（三表聯查） SELECT e.empno,e.ename,d.deptno,d.dname,e.job,j.job_title FROM emp e JOIN dept d on e.deptno=d.deptno JOIN jobs j on e.job=j.job_id WHERE e.deptno=10; -
外連接配接查詢
原理：符合條件的資料出現在結果集裡，但是外連接配接可以指定其中某一張表中不符合關聯條件的資料也出現在查詢結果集裡。
被指定的這張表暫時認為是主表，左外連接配接（left join）主表放在左側，右外連接配接（right join）就将主表放在右側。
-- 5 查詢員工編号，姓名，部門編号，部門名稱（外連接配接，沒有部門的員工也傳回） SELECT e.empno,e.ename,e.deptno,d.dname FROM emp e LEFT JOIN dept d on e.deptno=d.deptno; SELECT e.empno,e.ename,e.deptno,d.dname FROM dept d RIGHT JOIN emp e on e.deptno=d.deptno; -- 6 查詢員工編号，姓名，部門編号，部門名稱（外連接配接，沒有員工的部門也傳回） SELECT e.empno,e.ename,d.deptno,d.dname FROM dept d LEFT JOIN emp e on e.deptno=d.deptno;
七、統計函數
多行函數也叫做聚合函數也叫做統計函數，分組函數
max、min、avg、sum預設是統計括号裡指定的列的非空資料的最大值、最小值、平均值還有和
count（列名）：統計該列的值有幾條資料（不算空值），*就是所有資料的條數
group by：按該列内的值分組
having：在select之後執行條件，where先執行條件再進行select
-- 1 統計函數
SELECT MAX(sal) 最大值,MIN(sal) 最小值,AVG(sal) 平均值,SUM(sal),COUNT(*) 計數
FROM emp WHERE deptno=10;
-- 2 統計每個部門的平均工資
SELECT AVG(sal),deptno FROM emp GROUP BY deptno;
-- 3 統計每個部門不同職務的平均工資
SELECT AVG(sal),deptno,job FROM emp GROUP BY deptno,job;
-- 4 統計每個部門不同職務的平均工資
SELECT AVG(sal),deptno,job FROM emp GROUP BY deptno,job HAVING AVG(sal)<3000;
-- 5 統計不同職務的個數
SELECT COUNT(*),job from emp GROUP BY job;
-- 6 統計每個部門的平均工資，部門名稱，職務名稱
SELECT AVG(sal),d.dname,e.job
FROM emp e
JOIN dept d on e.deptno=d.deptno
JOIN jobs j on e.job=j.job_id
GROUP BY d.dname,e.job;
程式執行的先後順序：
八、子查詢
子查詢定義
在一個查詢語句裡，嵌套另外一個完整的查詢語句，并且用括号括起來，那麼該查詢語句是子查詢，外面的查詢語句叫主查詢或者是父查詢。
子查詢的位置
任何表達式存在的位置都可以是子查詢
- where後嵌套子查詢：執行順序是子查詢先執行，執行後主查詢執行。
-- 1 誰的工資比BLAKE高？ SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename='BLAKE'); - select後的衍生列可以是子查詢：執行順序為父查詢查詢一條資料，子查詢執行一次，父查詢查到多少條資料，子查詢執行多少次。
-- 2 查詢員工編号，姓名，工資，員工表的工資總和 SELECT empno,ename,sal,(SELECT SUM(sal) FROM emp) total FROM emp; - from 後的表名可以是子查詢：将子查詢的結果當成一張表來使用，進行二次查詢資料，執行順序，先執行一次子查詢，再執行父查詢。
-- 3 查詢每個部門的平均工資，部門編号，經理編号，經理姓名 SELECT temp.avg,temp.deptno,e.empno,e.ename FROM (SELECT AVG(sal) avg,deptno FROM emp GROUP BY deptno) temp JOIN emp e on temp.deptno=e.deptno WHERE e.job='MANAGER';
相關子查詢
子查詢内使用了父查詢的資料，執行順序是父查詢先查出一條資料，然後子查詢使用父查詢查到的那條資料裡的某個字段進行查詢，子查詢查詢之後的結果被父查詢再次使用，有可能做衍生列也有可能作為篩選條件。
-- 4 查詢員工編号，姓名，部門編号，工資，本部門工資的總和(相關子查詢)
SELECT e.empno,e.ename,e.deptno,e.sal,(SELECT SUM(sal) FROM emp m WHERE m.deptno=e.deptno) FROM emp e;
-- 5 查詢所有工資超過本部門平均工資的員工的資訊
SELECT * FROM emp e WHERE sal>(SELECT AVG(sal) FROM emp WHERE deptno=e.deptno);
增強版修改和删除
MySQL中不支援以子查詢為條件更新或删除資料，但是可以通過符合連接配接條件的方式更新或删除資料。
-- 6 修改工資最高的員工的工資為一萬
UPDATE emp JOIN (select max(sal) maxsal from emp)temp
on emp.sal=temp.maxsal SET emp.sal=10000;
-- 檢視該員工資訊
SELECT * FROM emp HAVING emp.sal=(SELECT MAX(sal) FROM emp);
-- 7 部門的所有員工工資降薪10% 并且該部門的名稱改為後勤部
UPDATE emp e JOIN dept d on e.deptno=d.deptno
SET e.sal=e.sal*0.9,d.dname='後勤部'
WHERE e.deptno=10;
-- 檢視修改後的資訊
SELECT * FROM dept WHERE deptno=10;
SELECT * FROM emp WHERE deptno=10;
以連接配接條件做删除。注意，删除不能同時删除多表的資料
-- 8 開除工資最高的員工
DELETE emp FROM emp JOIN (SELECT MAX(sal) maxsal FROM emp)temp
on temp.maxsal=emp.sal;
九、其他
導入導出資料庫
-
導出
在對應資料庫上右鍵選擇轉儲SQL檔案，結構和資料即可得到一個.sql的檔案
Java全棧大資料學習筆記NO.14（MySQL-1） -
導入
将.sql檔案直接拖拽到查詢中，選擇複制到目前位置，所有的表都以程式方式存在，點選運作後各種表就會出現在目前資料庫中。
Java全棧大資料學習筆記NO.14（MySQL-1）
union 和union all 差別
相同點： union和union all 都能實作合并查詢結果集
不同點： union可以去掉兩個結果集裡的重複資料、結果按預設規則排序,
union all不去重、不排序
-- 兩表合并
SELECT empno,ename from emp union all select deptno,dname from dept;
限制
資料庫裡有5種限制，目的是限制插入表裡的資料的準确性。
- 主鍵限制 primary key
- 外鍵限制 foreign key
- 非空限制 not null 與 預設值 default
- 唯一鍵限制，設定唯一限制的列，裡面的值不允許重複，但是允許有一個null值 unique
- 檢查限制也叫check限制，為某一列寫插入資料時的檢查條件，符合條件，資料插入成功，否則失敗
Java全棧大資料學習筆記NO.14（MySQL-1）
視圖
視圖就是儲存起來的查詢語句，作為資料庫的對象使用，它是一張虛表，視圖裡的查詢語句如果有衍生列，一定要起别名。
-- 每個部門的平均工資，工資的最大值，工資總和，部門編号，部門名稱，部門位址
CREATE OR REPLACE VIEW v_deptinfo
AS
SELECT d.deptno,d.dname,d.loc,AVG(sal) avgsal,
COUNT(e.deptno) num,MAX(sal) maxsal,SUM(sal) sumsal
FROM dept d LEFT JOIN emp e on e.deptno=d.deptno
GROUP BY d.deptno;
-- 直接調用視圖,視圖是一張虛表，可以進行查詢或者與其他表進行連接配接
SELECT * FROM v_deptinfo WHERE num>=2;
-- 删除視圖
DROP VIEW v_deptinfo;
簡單的單表視圖是可以進行修改或者删除操作的，但是實際操作的是真實的表。如果是複雜的視圖，比如多表查詢，子查詢的，分組操作，沒有辦法進行修改或删除。
索引
索引的作用是加快查詢速度,但是以dml操作的速度為代價。
什麼樣的列适合建索引：
- 表的資料量大
- 該列頻繁做條件搜尋
- 該列的值重複率低
- 該列存儲資料需要的空間較小（數值類型優于字元類型）
Create index 索引名 on table(列名)
Alter index 索引名 on table(列名)
Drop index 索引名
索引的類型：主鍵索引、外鍵索引、唯一鍵索引、普通索引、全文索引