MySQL鞏固學習記錄（一）

余月七發表於2021-04-07
MySql

mysql下載安裝

一、採用圖形化介面安裝

（初期只安裝server服務端就可以了，別的不多贅述）

二、採用壓縮版安裝

1、將檔案解壓縮到自己想要的路徑

2、 新增環境變數，即mysql的bin目錄

3、 編輯“my.ini”檔案，注意要替換路徑位置

[mysqld]
basedir=D:\Program Files\mysql-5.7\
datadir=D:\Program Files\mysql-5.7\data\
port=3306
skip-grant-tables

4、進入bin目錄下，以管理員身份開啟cmd，輸入“mysqld -install”安裝mysql

5、再輸入“mysqld --initialize-insecure --user=mysql”初始化資料檔案

6、然後通過命令進入mysql介面輸入命令更改root使用者密碼

update mysql.user set authentication_string=password('123456') where user='root'and Host = 'localhost';

7、重新整理許可權

flush privileges;

8、修改 my.ini 檔案刪除最後一句 skip-grant-tables

9、 重啟 mysql 即可正常使用

net stop mysql
net start mysql

幾個基本的資料庫操作命令

update user set password=password('123456')where user='root'; 修改密碼
flush privileges; 重新整理資料庫
show databases; 顯示所有資料庫
use dbname；開啟某個資料庫
show tables; 顯示資料庫mysql中所有的表
describe user; 顯示錶mysql資料庫中user表的列資訊
create database name; 建立資料庫
use databasename; 選擇資料庫
exit; 退出Mysql
? 命令關鍵詞 : 尋求幫助
-- 表示註釋

命令列運算元據庫

說明 : 反引號用於區別 MySQL 保留字與普通字元而引入的 (鍵盤 esc 下面的鍵).

建立資料庫 : create database [if not exists] 資料庫名;

刪除資料庫 : drop database [if exists] 資料庫名;

檢視資料庫 : show databases;

使用資料庫 : use 資料庫名;

資料庫欄位屬性

UnSigned

  • 無符號的

  • 宣告該資料列不允許負數 .

ZEROFILL

  • 0 填充的

  • 不足位數的用 0 來填充 , 如 int(3),5 則為 005

Auto_InCrement

  • 自動增長的 , 每新增一條資料 , 自動在上一個記錄數上加 1(預設)

  • 通常用於設定主鍵 , 且為整數型別

  • 可定義起始值和步長

    • 當前表設定步長 (AUTO_INCREMENT=100) : 隻影響當前表
    • SET @@auto_increment_increment=5 ; 影響所有使用自增的表 (全域性)

NULL 和 NOT NULL

  • 預設為 NULL , 即沒有插入該列的數值

  • 如果設定為 NOT NULL , 則該列必須有值

DEFAULT

  • 預設的

  • 用於設定預設值

  • 例如, 性別欄位, 預設為 "男" , 否則為 “女” ; 若無指定該列的值 , 則預設值為 "男" 的值

資料庫表型別

  • 適用 MyISAM : 節約空間及相應速度

  • 適用 InnoDB : 安全性 , 事務處理及多使用者運算元據表

修改資料庫

修改欄位 :

  • ALTER TABLE 表名 MODIFY 欄位名 列型別 [屬性]

  • ALTER TABLE 表名 CHANGE 舊欄位名 新欄位名 列屬性 [屬性]

刪除欄位 : ALTER TABLE 表名 DROP 欄位名

刪除資料表

語法：DROP TABLE [IF EXISTS] 表名

  • IF EXISTS 為可選 , 判斷是否存在該資料表

  • 如刪除不存在的資料表會丟擲錯誤

1. 可用反引號（`）為識別符號（庫名、表名、欄位名、索引、別名）包裹，以避免與關鍵字重名！中文也可以作為識別符號！

2. 每個庫目錄存在一個儲存當前資料庫的選項檔案db.opt。

3. 註釋：
  單行註釋 # 註釋內容
  多行註釋 /* 註釋內容 */
  單行註釋 -- 註釋內容       (標準SQL註釋風格，要求雙破折號後加一空格符（空格、TAB、換行等）)
   
4. 模式萬用字元：
  _   任意單個字元
  %   任意多個字元，甚至包括零字元
  單引號需要進行轉義 \'
   
5. CMD命令列內的語句結束符可以為 ";", "\G", "\g"，僅影響顯示結果。其他地方還是用分號結束。delimiter 可修改當前對話的語句結束符。

6. SQL對大小寫不敏感 （關鍵字）

7. 清除已有語句：\c

外來鍵

外來鍵概念 FOREIGN KEY

如果公共關鍵字在一個關係中是主關鍵字，那麼這個公共關鍵字被稱為另一個關係的外來鍵。由此可見，外來鍵表示了兩個關係之間的相關聯絡。以另一個關係的外來鍵作主關鍵字的表被稱為主表，具有此外來鍵的表被稱為主表的從表。

在實際操作中，將一個表的值放入第二個表來表示關聯，所使用的值是第一個表的主鍵值 (在必要時可包括複合主鍵值)。此時，第二個表中儲存這些值的屬性稱為外來鍵 (foreign key)。

外來鍵作用

保持資料一致性，完整性，主要目的是控制儲存在外來鍵表中的資料, 約束。使兩張表形成關聯，外來鍵只能引用外表中的列的值或使用空值。

注意 : 刪除具有主外來鍵關係的表時 , 要先刪子表 , 後刪主表

DML（資料庫管理語言）

一、新增資料

### INSERT 命令

語法：

INSERT INTO 表名[(欄位1,欄位2,欄位3,...)] VALUES('值1','值2','值3')

注意 :

  • 欄位或值之間用英文逗號隔開 .

  • ’ 欄位 1, 欄位 2…’ 該部分可省略 , 但新增的值務必與表結構, 資料列, 順序相對應, 且數量一致 .

  • 可同時插入多條資料 , values 後用英文逗號隔開 .

-- 使用語句如何增加語句?
-- 語法 : INSERT INTO 表名[(欄位1,欄位2,欄位3,...)] VALUES('值1','值2','值3')
INSERT INTO grade(gradename) VALUES ('大一');

-- 主鍵自增,那能否省略呢?
INSERT INTO grade VALUES ('大二');

-- 查詢:INSERT INTO grade VALUE ('大二')錯誤程式碼：1136
Column count doesn`t match value count at row 1

-- 結論:'欄位1,欄位2...'該部分可省略 , 但新增的值務必與表結構,資料列,順序相對應,且數量一致.

-- 一次插入多條資料
INSERT INTO grade(gradename) VALUES ('大三'),('大四');

二、修改資料

update 命令

語法：

UPDATE 表名 SET column_name=value [,column_name2=value2,...] [WHEREcondition];

注意 :

  • column_name 為要更改的資料列

  • value 為修改後的資料 , 可以為變數 , 具體指 , 表示式或者巢狀的 SELECT 結果

  • condition 為篩選條件 , 如不指定則修改該表的所有列資料

三、刪除資料

DELETE 命令

語法：

DELETE FROM 表名 [WHERE condition];

注意：condition 為篩選條件 , 如不指定則刪除該表的所有列資料

-- 刪除最後一個資料
DELETE FROM grade WHERE gradeid = 5

TRUNCATE 命令

作用：用於完全清空表資料 , 但表結構 , 索引 , 約束等不變 ;

語法：

TRUNCATE [TABLE] table_name;

-- 清空年級表
TRUNCATE grade

注意：區別於 DELETE 命令

  • 相同 : 都能刪除資料 , 不刪除表結構 , 但 TRUNCATE 速度更快

  • 不同 :

    • 使用 TRUNCATE TABLE 重新設定 AUTO_INCREMENT 計數器
    • 使用 TRUNCATE TABLE 不會對事務有影響
-- 建立一個測試表
CREATE TABLE `test` (
`id` INT(4) NOT NULL AUTO_INCREMENT,
`coll` VARCHAR(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

-- 插入幾個測試資料
INSERT INTO test(coll) VALUES('row1'),('row2'),('row3');

-- 刪除表資料(不帶where條件的delete)
DELETE FROM test;
-- 結論:如不指定Where則刪除該表的所有列資料,自增當前值依然從原來基礎上進行,會記錄日誌.

-- 刪除表資料(truncate)
TRUNCATE TABLE test;
-- 結論:truncate刪除資料,自增當前值會恢復到初始值重新開始;不會記錄日誌.

-- 同樣使用DELETE清空不同引擎的資料庫表資料.重啟資料庫服務後
-- InnoDB : 自增列從初始值重新開始 (因為是儲存在記憶體中,斷電即失)
-- MyISAM : 自增列依然從上一個自增資料基礎上開始 (存在檔案中,不會丟失)`

DQL(資料庫查詢語言)

SELECT 語法

SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias]
  [left | right | inner join table_name2]  -- 聯合查詢
  [WHERE ...]  -- 指定結果需滿足的條件
  [GROUP BY ...]  -- 指定結果按照哪幾個欄位來分組
  [HAVING]  -- 過濾分組的記錄必須滿足的次要條件
  [ORDER BY ...]  -- 指定查詢記錄按一個或多個條件排序
  [LIMIT {[offset,]row_count | row_countOFFSET offset}];
   -- 指定查詢的記錄從哪條至哪條

注意 : [] 括號代表可選的 , {} 括號代表必選得

指定查詢欄位

-- 查詢表中所有的資料列結果 , 採用 **" \* "** 符號; 但是效率低，不推薦 .

-- 查詢所有學生資訊
SELECT * FROM student;

-- 查詢指定列(學號 , 姓名)
SELECT studentno,studentname FROM student;

AS 子句作為別名

作用：

  • 可給資料列取一個新別名

  • 可給表取一個新別名

  • 可把經計算或總結的結果用另一個新名稱來代替

-- 這裡是為列取別名(當然as關鍵詞可以省略)
SELECT studentno AS 學號,studentname AS 姓名 FROM student;

-- 使用as也可以為表取別名
SELECT studentno AS 學號,studentname AS 姓名 FROM student AS s;

-- 使用as,為查詢結果取一個新名字
-- CONCAT()函式拼接字串
SELECT CONCAT('姓名:',studentname) AS 新姓名 FROM student;

DISTINCT 關鍵字的使用

作用 : 去掉 SELECT 查詢返回的記錄結果中重複的記錄 (返回所有列的值都相同) , 只返回一條

-- # 檢視哪些同學參加了考試(學號) 去除重複項
SELECT * FROM result; -- 檢視考試成績
SELECT studentno FROM result; -- 檢視哪些同學參加了考試
SELECT DISTINCT studentno FROM result; -- 瞭解:DISTINCT 去除重複項 , (預設是ALL)

使用表示式的列

資料庫中的表示式 : 一般由文字值 , 列值 , NULL , 函式和操作符等組成

應用場景 :

  • SELECT 語句返回結果列中使用

  • SELECT 語句中的 ORDER BY , HAVING 等子句中使用

  • DML 語句中的 where 條件語句中使用表示式

    -- selcet查詢中可以使用表示式
SELECT @@auto_increment_increment; -- 查詢自增步長
SELECT VERSION(); -- 查詢版本號
SELECT 100*3-1 AS 計算結果; -- 表示式

-- 學員考試成績集體提分一分檢視
SELECT studentno,StudentResult+1 AS '提分後' FROM result;

  • 避免 SQL 返回結果中包含 ’ . ’ , ’ * ’ 和括號等干擾開發語言程式.

where條件語句

-- 滿足條件的查詢(where)
SELECT Studentno,StudentResult FROM result;

-- 查詢考試成績在95-100之間的
SELECT Studentno,StudentResult
FROM result
WHERE StudentResult>=95 AND StudentResult<=100;

-- AND也可以寫成 &&
SELECT Studentno,StudentResult
FROM result
WHERE StudentResult>=95 && StudentResult<=100;

-- 模糊查詢(對應的詞:精確查詢)
SELECT Studentno,StudentResult
FROM result
WHERE StudentResult BETWEEN 95 AND 100;

-- 除了1000號同學,要其他同學的成績
SELECT studentno,studentresult
FROM result
WHERE studentno!=1000;

-- 使用NOT
SELECT studentno,studentresult
FROM result
WHERE NOT studentno=1000;

注意：

  • 數值資料型別的記錄之間才能進行算術運算 ;

  • 相同資料型別的資料之間才能進行比較 ;

四、連線查詢

/*
連線查詢
   如需要多張資料表的資料進行查詢,則可通過連線運算子實現多個查詢
內連線 inner join
   查詢兩個表中的結果集中的交集
外連線 outer join
   左外連線 left join
       (以左表作為基準,右邊表來一一匹配,匹配不上的,返回左表的記錄,右表以NULL填充)
   右外連線 right join
       (以右表作為基準,左邊表來一一匹配,匹配不上的,返回右表的記錄,左表以NULL填充)
       
等值連線和非等值連線

自連線
*/

-- 查詢參加了考試的同學資訊(學號,學生姓名,科目編號,分數)
SELECT * FROM student;
SELECT * FROM result;

/*思路:
(1):分析需求,確定查詢的列來源於兩個類,student result,連線查詢
(2):確定使用哪種連線查詢?(內連線)
*/
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno

-- 右連線(也可實現)
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
RIGHT JOIN result r
ON r.studentno = s.studentno

-- 等值連線
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s , result r
WHERE r.studentno = s.studentno

-- 左連線 (查詢了所有同學,不考試的也會查出來)
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
LEFT JOIN result r
ON r.studentno = s.studentno

-- 查一下缺考的同學(左連線應用場景)
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
LEFT JOIN result r
ON r.studentno = s.studentno
WHERE StudentResult IS NULL

-- 思考題:查詢參加了考試的同學資訊(學號,學生姓名,科目名,分數)
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON sub.subjectno = r.subjectno

自連線

/*
自連線
   資料表與自身進行連線

需求:從一個包含欄目ID , 欄目名稱和父欄目ID的表中
    查詢父欄目名稱和其他子欄目名稱
*/

-- 建立一個表
CREATE TABLE `category` (
`categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主題id',
`pid` INT(10) NOT NULL COMMENT '父id',
`categoryName` VARCHAR(50) NOT NULL COMMENT '主題名字',
PRIMARY KEY (`categoryid`)
) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8

-- 插入資料
INSERT INTO `category` (`categoryid`, `pid`, `categoryName`)
VALUES('2','1','資訊科技'),
('3','1','軟體開發'),
('4','3','資料庫'),
('5','1','美術設計'),
('6','3','web開發'),
('7','5','ps技術'),
('8','2','辦公資訊');

-- 編寫SQL語句,將欄目的父子關係呈現出來 (父欄目名稱,子欄目名稱)
-- 核心思想:把一張表看成兩張一模一樣的表,然後將這兩張表連線查詢(自連線)
SELECT a.categoryName AS '父欄目',b.categoryName AS '子欄目'
FROM category AS a,category AS b
WHERE a.`categoryid`=b.`pid`

-- 思考題:查詢參加了考試的同學資訊(學號,學生姓名,科目名,分數)
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON sub.subjectno = r.subjectno

-- 查詢學員及所屬的年級(學號,學生姓名,年級名)
SELECT studentno AS 學號,studentname AS 學生姓名,gradename AS 年級名稱
FROM student s
INNER JOIN grade g
ON s.`GradeId` = g.`GradeID`

-- 查詢科目及所屬的年級(科目名稱,年級名稱)
SELECT subjectname AS 科目名稱,gradename AS 年級名稱
FROM SUBJECT sub
INNER JOIN grade g
ON sub.gradeid = g.gradeid

-- 查詢 資料庫結構-1 的所有考試結果(學號 學生姓名 科目名稱 成績)
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='資料庫結構-1'

排序和分頁

/*============== 排序 ================
語法 : ORDER BY
   ORDER BY 語句用於根據指定的列對結果集進行排序。
   ORDER BY 語句預設按照ASC升序對記錄進行排序。
   如果您希望按照降序對記錄進行排序，可以使用 DESC 關鍵字。
   
*/

-- 查詢 資料庫結構-1 的所有考試結果(學號 學生姓名 科目名稱 成績)
-- 按成績降序排序
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='資料庫結構-1'
ORDER BY StudentResult DESC

/*============== 分頁 ================
語法 : SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset
好處 : (使用者體驗,網路傳輸,查詢壓力)

推導:
   第一頁 : limit 0,5
   第二頁 : limit 5,5
   第三頁 : limit 10,5
   ......
   第N頁 : limit (pageNo-1)*pageSzie,pageSzie
   [pageNo:頁碼,pageSize:單頁面顯示條數]
   
*/

-- 每頁顯示5條資料
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='資料庫結構-1'
ORDER BY StudentResult DESC , studentno
LIMIT 0,5

-- 查詢 JAVA第一學年 課程成績前10名並且分數大於80的學生資訊(學號,姓名,課程名,分數)
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='JAVA第一學年'
ORDER BY StudentResult DESC
LIMIT 0,10

子查詢

/*============== 子查詢 ================
什麼是子查詢?
   在查詢語句中的WHERE條件子句中,又巢狀了另一個查詢語句
   巢狀查詢可由多個子查詢組成,求解的方式是由裡及外;
   子查詢返回的結果一般都是集合,故而建議使用IN關鍵字;
*/

-- 查詢 資料庫結構-1 的所有考試結果(學號,科目編號,成績),並且成績降序排列
-- 方法一:使用連線查詢
SELECT studentno,r.subjectno,StudentResult
FROM result r
INNER JOIN `subject` sub
ON r.`SubjectNo`=sub.`SubjectNo`
WHERE subjectname = '資料庫結構-1'
ORDER BY studentresult DESC;

-- 方法二:使用子查詢(執行順序:由裡及外)
SELECT studentno,subjectno,StudentResult
FROM result
WHERE subjectno=(
   SELECT subjectno FROM `subject`
   WHERE subjectname = '資料庫結構-1'
)
ORDER BY studentresult DESC;

-- 查詢課程為 高等數學-2 且分數不小於80分的學生的學號和姓名
-- 方法一:使用連線查詢
SELECT s.studentno,studentname
FROM student s
INNER JOIN result r
ON s.`StudentNo` = r.`StudentNo`
INNER JOIN `subject` sub
ON sub.`SubjectNo` = r.`SubjectNo`
WHERE subjectname = '高等數學-2' AND StudentResult>=80

-- 方法二:使用連線查詢+子查詢
-- 分數不小於80分的學生的學號和姓名
SELECT r.studentno,studentname FROM student s
INNER JOIN result r ON s.`StudentNo`=r.`StudentNo`
WHERE StudentResult>=80

-- 在上面SQL基礎上,新增需求:課程為 高等數學-2
SELECT r.studentno,studentname FROM student s
INNER JOIN result r ON s.`StudentNo`=r.`StudentNo`
WHERE StudentResult>=80 AND subjectno=(
   SELECT subjectno FROM `subject`
   WHERE subjectname = '高等數學-2'
)

-- 方法三:使用子查詢
-- 分步寫簡單sql語句,然後將其巢狀起來
SELECT studentno,studentname FROM student WHERE studentno IN(
   SELECT studentno FROM result WHERE StudentResult>=80 AND subjectno=(
       SELECT subjectno FROM `subject` WHERE subjectname = '高等數學-2'
  )
)

/*
練習題目:
   查 C語言-1 的前5名學生的成績資訊(學號,姓名,分數)
   使用子查詢,查詢郭靖同學所在的年級名稱
*/

相關文章