資料庫學習筆記 - MySQL基礎知識

本該是你發表於2021-11-16

資料庫筆記MySql

一、資料庫基礎知識

1.1 Whats's 資料庫

資料庫（Database，DB）：將大量資料儲存起來，通過計算機加工而成的可以進行高效訪問的資料集合。如：大型-銀行儲存的資訊，小型-電話簿。
資料庫管理系統（Batabase Management System，DBMS）：用來管理資料庫的計算機系統。
關係型資料庫（Relational Database，RDB）：目前應用最廣泛的資料庫。
結構化查詢語言（Structured Query Language，SQL）：專門用於操作 RDB。
常見的 5 種關係型資料庫管理系統（Relational Database Management System，RDBMS）：

①Oracle Database：甲骨文公司

②SQL Server：微軟公司

　 ③DB2：IBM 公司

　 ④PostgreSQL：開源

　 ⑤MySQL：開源

1.2 資料庫結構

伺服器：用於接收並處理其它程式發出的請求的程式（軟體），或者是安裝此類程式的裝置（計算機）。
客戶端：向伺服器發出請求的程式（軟體），或者是安裝此類程式的裝置（計算機）。

表（table）：類似 Excel，由行和列組成的二維表。
欄位：表的列（垂直方向）。
記錄：表的行（水平方向）。【注意】關聯式資料庫必須以行為單位進行資料讀寫。
單元格：行列交匯處。【注意】與 Excel 不同，一個單元格只能輸入一個資料。

二、SQL基礎知識

SQL(Structured Query Language)是結構化查詢語言的簡稱，它是一種資料庫查詢和程式涉及語言，同時也是目前使用最廣泛的關係型資料庫操作語言。

SQL 語句：用關鍵字、表名和列名等組合而成的一條語句。
3 種 SQL 語句種類：

（1）DDL（Data Definition Language，資料定義語言）：建立、刪除或修改資料庫以及資料庫中的表等物件。

　　　　　　①CREATE：建立資料庫和表等物件

　　　　　　②DROP：刪除資料庫和表等物件

　　　　　　③ALTER：修改資料庫和表等物件

（2）DML（Data Manipulation Language，資料操作語言）：查詢或修改表中的記錄。

　　　　　　①SELECT：查詢表中的資料

　　　　　　②INSERT：向表中插入資料

　　　　　　③UPDATE：修改表中的資料

　　　　　　④DELETE：刪除表中的資料

（3）DCL（Data Control Language，資料控制語言）：確認或取消對資料庫中的資料變更的執行操作，以及對使用者的運算元據庫中的物件許可權進行設定。

　　　　　　①COMMIT：確認對資料庫中的資料進行的變更

　　　　　　②ROLLBACK：取消對資料庫中的資料進行的變更

　　　　　　③GRANT：賦予使用者的操作許可權

　　　　　　④REVOKE：取消使用者的操作許可權

三、建立資料庫與表

1. 建立資料庫

語法：CREATE DATABASE <資料庫名稱>

-- 建立資料庫
CREATE DATABASE student;
-- 檢視建庫語句
show create database student;

-- 刪除資料庫
drop database student;

2. 建立資料表

語法：
CREATE TABLE <表名>
(
<列名1> <資料型別> <約束>,
<列名1> <資料型別> <約束>,
...
<表約束1>,
<表約束2>,
...
)
# 建立一張用來儲存學生資訊的表
# 欄位包含學號、姓名、性別、年齡、入學日期、班級等資訊
create table student（
	num int ，
	name varchar(5),
	sex char(1),
	age int,
	entersate date,
	class varchar(10),
)

3.命名規則

①只能使用半形英文字母、數字、下劃線（_）作為資料庫、表和列的名稱；

②規範要求命名以半形英文字元開頭；

③名稱不能重複。

4.資料型別的指定：宣告該列的資料型別，是一種約束。

5.資料型別介紹

①INTEGER：整型，意味不能儲存小數；

②CHAR：字串型，如 CHAR（10）和 CHAR（100）中的括號表名該列可以儲存的字串的最大長度。它是“定長字串”，如 CHAR(8) 表示在列中插入 'abc' 時會自動儲存成 'abc '（後面5個半形空格）的形式；

③VARCHAR：字串型，類似 CHAR，不同的是它是 “可變長字串”，如 VARCHAR(8) 在插入 'abc'，儲存的就是字串 'abc'；

④DATE：日期型別；

　... ...

6.約束

①非空約束：NULL 是空白（無記錄）的意思的關鍵字，NOT NULL 表示必須輸入的約束。

②主鍵約束：主鍵是可以確定一行資料的列，一般通過它取特定行的資料，它是唯一的，不允許重複。

7.新增表記錄

INSERT INTO `student`(`num`,`name`,`sex`,`age`,`enterdate`,`class`) VALUES (1,'Emily','女',20,'2022-9-7','Python班級1');

8.修改和刪除記錄

# 更新表記錄
update student set enterdate='2021-9-7' where num=1;
# 刪除記錄
delete from student where num=1;
# 刪除全部記錄
delete from student;

9.更新和刪除表

刪除表(被刪除的表無法恢復)

語法：DROP TABLE <表名>
DROP TABLE student;
更新表
新增列

語法：ALTER TABLE <表名> ADD <列名> <型別>;
ALTER TABLE student ADD E-mail VARCHAR(100); -- 在 student 中新增名為 E-mail 型別為 VARCHAR(100) 的列
刪除列

語法：ALTER TABLE <表名> DROP COLUMN <列名>;
ALTER TABLE student DROP COLUMN E-mail; -- 刪除 student 表中名為 E-mail 的列
修改欄位名

語法：ALTER TABLE <表名>change <老欄位><新欄位>資料型別（修改後的長度）

四、查詢資料庫表

1.查詢指定列：select 關鍵字

語法：
select <列名>, ..... -- 希望查詢的列的名稱

from <表名> -- 指定選取資料的表
-- 從 student中取 3 列
SELECT num, name, sex　　-- 列的順序可以任意指定，逗號（“,”）分隔，查詢結果的順序和 SELECT 子句中的順序相同
FROM student;

2.查詢表的所有列：星號（*）

語法：

select * -- 星號（*）代表所有列

from <表名>

3.為列設定別名：AS 關鍵字

SELECT num AS "學號", name AS "姓名"FROM student;    -- 不用 AS 關鍵字也可以

4.從結果中刪除重複行：DISTINCT

（1）SELECT DISTINCT class FROM student;　　

（2）DISTINCT 對 NULL 型別的處理：存在多條 NULL 值的行時，會結合為一條 NULL 資料。

5.篩選記錄：WHERE

WHERE 子句中可以指定“某一列的值和這個字串相等”或者“某一列的值大於這個數字”等條件，找出只符合該條件的記錄。

語法：

select <列名>,

from <表名>

where <條件表示式>
-- where子句 + 關係運算子 
select * from emp where deptno = 10;
-- where子句 + 邏輯運算子：and
select * from emp where sal >1500 and sal < 3000;
-- where子句 + 邏輯運算子：or
select * from emp where deptno = 10 or deptno = 20;
-- 模糊查詢-- 查詢名字中帶A的員工
select * from emp where ename like '%A%';
-- 任意一個字元
select * from emp where ename like '_A%';
-- 關於null的判斷
select * from emp where comm is null;
select * from emp where comm is not null;

五、資料更新

1.插入資料

INSERT 語句的基本語法

--語法：--INSERT INTO <表名>(列1, 列2, ...) VALUES (值1, 值2, ...)

2.刪除資料

2 種刪除資料的方法：

（1）DROP TABLE 語句：刪除表

（2）DELETE 語句：刪除表的資料

--語法--DELETE FROM <表名>;

指定刪除物件的 DELETE 語句

--語法--DELETE FROM <表名>--WHERE <條件>;

3.更新資料

UPDATE 子句的基本語法

--語法--UPDATE <表名>--    SET <列名> = <表示式>

指定條件的 UPDATE 語句

--語法--UPDATE <表名>--    SET <列名> = <表示式>--WHERE <條件>;

使用 NULL 進行更新

--示例UPDATE dbo.Shohin    SET torokubi = NULLWHERE shohin_id = '008';

多列更新

--示例UPDATE dbo.Shohin    SET hanbai_tanka = hanbai_tanka * 10,　　　　--逗號分割        shiire_tanka = shiire_tanka / 2WHERE shohin_bunrui = '廚房用具'

六、多表查詢

1、笛卡爾積

概念：表示兩個表中的每一行任意組合的結果

例如：

有兩個表，左表有m條資料記錄，x個欄位，右表有n條資料記錄，y個欄位，則執行交叉連線後將返回m*n條資料記錄，x+y個欄位

2、多表查詢

先確定資料要用到哪些表。

將多個表先通過笛卡爾積變成一個表。

然後去除不符合邏輯的資料（根據兩個表的關係去掉）。

最後當做是一個虛擬表一樣來加上條件即可。

3、自然連線（natural join）

自然連線是一種特殊的等值連線，他要求兩個關係表中進行比較的必須是相同的屬性列，無須新增連線條件，並且在結果中消除重複的屬性

SQL語句
Select …… from 表1 natural join 表2;

4、內連線（innner join）

內連線基本與自然連線相同，不同之處在於自然連線要求是同名屬性列的比較，而內連線則不要求兩屬性列名相同，可以使用using或on來指定某兩列欄位相同的連線條件

SQL語句
Select …… from 表1 inner join 表 2 on 表1.A=表2.E;Select …… from 表1 inner join 表 2 using(A);    --using缺點：關聯的欄位，必須是同名的

5、外連線

外連線有三種方式：左連線、右連線、全連線

外連線除了顯示匹配的資料之外，還可以顯示不匹配的資料

自然連線時某一些屬性不同則會導致這些元組會被捨棄，那麼如何去儲存這些被丟失的資料呢？外連線就解決了相應的問題。

5.1 左連線（left outer join）

左連線是在兩表進行自然連線，只把左邊那個表要捨棄的保留在結果集中，右表對應的列上顯示NULL

SQL語句
Select …… from 表1 left outer join 表2 on 表1.C=表2.C;Select …… from 表1 left join 表2 on 表1.C=表2.C;   --outer可以省略不寫

5.2 右連線（right outer join）

右連線是在兩表進行自然連線，只把右邊那個表要捨棄的保留在結果集中，左表對應的列上顯示NULL

SQL語句
Select …… from 表1 right outer join 表2 on 表1.C=表2.C;Select …… from 表1 right join 表2 on 表1.C=表2.C;   --outer可以省略不寫

5.3、全連線（full join）

全連線是在兩表進行自然連線，把左表和右表要捨棄的都保留在結果集中，相對應的列上顯示NULL

SQL語句
Select …… from 表1 full outer join 表2 on 表1.C=表2.C;Select …… from 表1 full join 表2 on 表1.C=表2.C;   --outer可以省略不寫
注意：這個語法在mysql中不支援，在oracle中支援

要解決上述問題可以使用左連線與右連線的並集
Select …… from 表1 left  join 表2 on 表1.C=表2.Cunion -- 並集Select …… from 表1 right join 表2 on 表1.C=表2.C;
注意：mysql中對集合的操作支援比較弱，只支援並集操作，交集、差集不支援(oracle中支援)

6、練習

準備表和資料

CREATE TABLE dept (deptno int NOT NULL,dname varchar(14),loc varchar(13),PRIMARY KEY (deptno))ENGINE = InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;CREATE TABLE emp (empno int NOT NULL,ename varchar(10),job varchar(9),mgr int,hiredate date,sal double(7,2),comm double(7,2),deptno int,PRIMARY KEY (empno),CONSTRAINT fk_deptno FOREIGN KEY (deptno) REFERENCES dept (deptno))ENGINE = InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;CREATE TABLE salgrade (grade int NOT NULL, losal double(7,2),  hisal double(7,2),PRIMARY KEY (grade))ENGINE = InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; INSERT INTO DEPT VALUES ('10', '教研部', '北京');INSERT INTO DEPT VALUES ('20', '學工部', '上海');INSERT INTO DEPT VALUES ('30', '銷售部', '深圳');INSERT INTO DEPT VALUES ('40', '財務部', '廣州');INSERT INTO DEPT VALUES ('50', '董事會', '成都');INSERT INTO EMP VALUES (1001,'SMITH','文員',1013,'2021-12-17',800,NULL,20); INSERT INTO EMP VALUES (1002,'ALLEN','銷售員',1006,'2021-02-20',1600,300,30);INSERT INTO EMP VALUES (1003,'WARD','銷售員',1006,'2021-02-22',1250,500,30); INSERT INTO EMP VALUES (1004,'JONES','經理',1009,'2021-04-02',2975,NULL,20); INSERT INTO EMP VALUES (1005,'MARTIN','銷售員',1006,'2021-09-28',1250,1400,30); INSERT INTO EMP VALUES (1006,'BLAKE','經理',1009,'2021-05-01',2850,NULL,30); INSERT INTO EMP VALUES (1007,'CLARK','經理',1009,'2021-06-09',2450,NULL,10); INSERT INTO EMP VALUES (1008,'SCOTT','分析師',1004,'2021-07-13',3000,NULL,20); INSERT INTO EMP VALUES (1009,'KING','董事長',NULL,'2021-11-07',5000,NULL,10); INSERT INTO EMP VALUES (1010,'TURNER','銷售員',1006,'2021-09-08',1500,0,30); INSERT INTO EMP VALUES (1011,'ADAMS','文員',1008,'2021-07-13',1100,NULL,20); INSERT INTO EMP VALUES (1012,'JAMES','文員',1008,'2021-12-03',950,NULL,30); INSERT INTO EMP VALUES (1013,'FORD','分析師',1004,'2021-12-03',3000,NULL,20); INSERT INTO EMP VALUES (1014,'MILLER','文員',1008,'2021-01-23',1300,NULL,10);INSERT INTO EMP VALUES (1015,'Emily','保潔員',1001,'2021-01-23',1300,NULL,10);INSERT INTO SALGRADE VALUES (1,700,1200); INSERT INTO SALGRADE VALUES (2,1201,1400); INSERT INTO SALGRADE VALUES (3,1401,2000); INSERT INTO SALGRADE VALUES (4,2001,3000); INSERT INTO SALGRADE VALUES (5,3001,9999);

查出至少有一個員工的部門。顯示部門編號、部門名稱、部門位置、部門人數

SELECT d.deptno,d.dname,d.loc,e1.`count(*)`FROM dept d INNER JOIN (SELECT deptno,COUNT(*) FROM emp GROUP BY deptno) e1ON d.deptno=e1.deptno;

列出所有員工的姓名以及直接上級

SELECT e1.ename,e2.ename 上級 FROM emp e1 LEFT JOIN emp e2 ON e1.mgr=e2.empno;

列出工資比 ALLEN 高的所有員工

SELECT * FROM emp WHERE sal >(SELECT sal FROM emp WHERE ename = 'ALLEN');

列出受僱日期早於直接上級的所有員工的編號、姓名、部門名稱

SELECT e1.empno, e1.ename, d.dname from emp e1 left join emp e2 on e1.mgr = e2.empnoleft join dept d on e1.empno = d.empno where e1.hiredate < e2.hiredata;SELECT e1.empno, e1.ename, d.dname FROM emp e1, emp e2, dept d WHERE e1.mgr=e2.empno AND e1.hiredate<e2.hiredate AND e1.deptno=d.deptno;

列出部門名稱和這些部門的員工資訊，同時列出那些沒有員工的部門

SELECT * from emp e RIGHT JOIN dept d on e.deptno = d.deptno;

mysql資料庫學習基礎知識整理
2020-12-12
MySql資料庫
基礎知識學習筆記
2020-09-28
筆記
RxJava 學習筆記 -- 基礎知識
2018-09-06
RxJava筆記
MySQL 資料庫基礎知識點複習
2021-04-30
MySql資料庫
MySQL 基礎知識梳理學習（一）—-系統資料庫
2019-01-05
MySql資料庫
linux基礎知識學習筆記
2013-08-23
Linux筆記
【編測編學】MySQL資料庫基礎知識
2020-12-28
MySql資料庫
MySQL資料庫基礎筆記
2024-03-17
MySql資料庫筆記
GO 學習筆記《1. 基礎知識》
2021-08-30
Go筆記
虛擬化學習筆記-基礎知識
2018-02-28
筆記
Java基礎知識學習筆記總結
2017-04-21
Java筆記
R語言學習筆記：基礎知識
2016-05-02
R語言筆記
Node基礎知識點--學習筆記（一）
2015-04-18
筆記
PHP學習筆記（1）–基礎知識篇
2011-11-11
PHP筆記
【編測編學】MySQL資料庫基礎知識2
2020-12-29
MySql資料庫
學習資料庫的基礎知識的書籍
2020-02-26
資料庫
matlab學習筆記之基礎知識（一）
2016-07-07
Matlab筆記
MySQL資料庫學習筆記
2020-12-10
MySql資料庫筆記
mysql資料庫sql語句基礎知識
2019-01-07
MySql資料庫
MySQL?資料庫的基礎知識撩磅
2022-03-01
MySql資料庫
MySQL資料庫基礎知識及優化
2021-01-21
MySql資料庫優化
資料庫基礎知識
2024-04-03
資料庫
資料庫MySQL需要學習基本知識
2021-03-30
資料庫MySql
大資料學習之路——MySQL基礎（一）——MySQL的基礎知識與常見操作
2021-05-29
大資料MySql
Python學習筆記—day1—基礎知識
2019-02-27
Python筆記
資料庫mysql學習筆記記錄
2021-09-09
資料庫MySql筆記
MySQL學習筆記【基礎篇】
2020-08-17
MySql筆記
基礎知識梳理~資料庫
2019-12-14
資料庫
資料庫基礎重要知識
2018-09-14
資料庫
GEO資料庫基礎知識
2018-12-21
資料庫
MySQL資料庫基礎學習筆記（整理自蘇勇老師的MySQL基礎課程視訊）
2015-05-06
MySql資料庫筆記
Android NDK學習筆記1-基礎知識篇
2016-09-23
Android筆記
python之前端HTML/CSS基礎知識學習筆記
2017-06-06
Python前端HTMLCSS筆記
python之前端HTML/CSS基礎知識學習筆記
2017-11-12
Python前端HTMLCSS筆記
Python學習筆記020——資料庫知識概述
2018-01-28
Python筆記資料庫
Java學習筆記--資料庫初識
2017-06-01
Java筆記資料庫
mysql小記--基礎知識
2016-02-17
MySql
oracle學習筆記零碎（五）--基礎知識複習（2）
2015-03-16
Oracle筆記