MySQL基礎合集

  • 2022 年 4 月 21 日
  • 筆記

 我的小站

1、MySQL的優勢

  • 運行速度快
  • 使用成本低
  • 可移植性強
  • 適用用戶廣

2、MySQL的運行機制

一個SQL語句，如select * from tablename ，從支援介面進來後，進入連接池後做許可權、驗證等環節，然後判斷是否有快取，有則直接放回結果，否則進入SQL介面，在查詢之前查詢優化器進行優化，最後進行解析，查詢。並通過存儲引擎與文件交互。

3、MySQL的配置

**埠號：**3306 **默認字符集：**utf8 將bin目錄寫入 環境變數 root密碼設置

**註：**utf8:一種Unicode編碼，解決國際上字元不統一的多位元組編碼，通用性強 my.ini：記錄MySQL配置資訊，可修改配置內容，如埠號，字符集等

4、命令行連接MySQL

首先檢查是否啟動服務 屬性窗口 DOS命令：

​ 啟動：net start mysql57

​ 停止：net stop mysql57

57代表是5.7的版本，以下的不需接57

註：如果修改了配置文件，必須重啟MySQL服務才能生效 命令行連接MySQL語法：mysql –h伺服器主機地址 –u 用戶名 –p密碼

l –h伺服器主機地址（如果是本機可以省略這名句） 密碼可省略，按回車再輸入

5、系統資料庫和用戶資料庫

系統資料庫： **information_schema:**主要存儲系統中的一些資料庫對象資訊，如用戶表資訊、欄位資訊、許可權資訊、字符集資訊和分區資訊等。 **performance_schema:**主要存儲資料庫伺服器性能參數

**mysql:**主要存儲系統的用戶許可權資訊 **test:**MySQL資料庫管理系統自動創建的測試資料庫，任何用戶都可以使用 用戶資料庫： 用戶資料庫是用戶根據實際需求創建的資料庫。本章後面的講解主要針對用戶資料庫。

6、創建資料庫

​ set names utf8; 語法： create database 資料庫名 **示例：**create database myschool;

7、查看資料庫列表

語法：show databases;

8、選擇資料庫

語法：use 資料庫名; 註：使用數據前必須先選擇該資料庫！

9、刪除資料庫

語法：drop database 資料庫名；

10、MySQL數據類型

11、創建表

語法：CREATE TABLE [IF NOT EXISTS] 表名 ( 欄位1 數據類型 [欄位屬性|約束][索引][注釋], …… 欄位n 數據類型 [欄位屬性|約束][索引][注釋]
)[表類型][表字符集][注釋];

例：

   CREATE TABLE `student`
  (
    `studentNo` INT(4)  PRIMARY KEY auto_increment not null,
        ` name` CHAR(10),
     ……
  );

12、欄位的約束及屬性

名稱 關鍵字 說明
非空約束 NOT NULL 欄位不允許為空 默認約束 DEFAULT 賦予某欄位默認值 唯一約束 UNIQUE KEY(UK) 設置欄位的值是唯一的 允許為空，但只能有一個空值 主鍵約束 PRIMARY KEY(PK) 設置該欄位為表的主鍵 可唯一標識該表記錄 外鍵約束 FOREIGN KEY(FK) 用於在兩表之間建立關係 自動增長 AUTO_INCREMENT 設置該列為自增欄位，默認每條自增1，通常用於設置主鍵

主鍵：

 CREATE TABLE student（
      `studentNo` INT(4)  PRIMARY KEY,
      ……）;

注釋：

 CREATE TABLE test (
    `id` int(11) UNSIGNED COMMENT 『編號』
)COMMENT='測試表』 ;

設置字符集編碼

 CREATE TABLE [IF NOT EXISTS] 表名（
     #省略程式碼
）CHARSET = 字符集名;

創建學生表示例：

CREATE TABLE `student`(
    `studentNo` INT(4) NOT NULL COMMENT '學號' PRIMARY KEY,
	`loginPwd` VARCHAR(20) NOT NULL COMMENT '密碼',  
	`studentName` VARCHAR(50) NOT NULL COMMENT '學生姓名',
	`sex` CHAR(2) DEFAULT '男' NOT NULL  COMMENT '性別', 
	`gradeId` INT(4)  UNSIGNED COMMENT '年級編號', 
	`phone` VARCHAR(50)  COMMENT '聯繫電話',
	`address` VARCHAR(255)  DEFAULT '地址不詳'COMMENT '地址',
    `bornDate` DATETIME  COMMENT '出生時間',
	`email` VARCHAR(50) COMMENT'郵件帳號',
	 `identityCard` VARCHAR(18)  UNIQUE KEY COMMENT '身份證號'
) COMMENT='學生表';

13、查看錶

查看錶是否存在

查看錶定義

註：避免DOS窗口亂碼，可執行SET NAMES gbk;

14、刪除表

**語法：**DROP TABLE [IF EXISTS] 表名; 示例：

註：在刪除表之前，先使用IF EXISTS語句驗證表是否存在

15、MySQL的存儲引擎

1）存儲引擎的類型 MyISAM、InnoDB 、Memory、CSV等9種 2）MyISAM與InnoDB類型主要區別

經驗： 適用場合 使用MyISAM: 不需事務，空間小，以查詢訪問為主 使用InnoDB: 多刪除、更新操作，安全性高，事務處理及並發控制 3）查看當前默認存儲引擎 SHOW VARIABLES LIKE 『storage_engine%』; 4）修改存儲引擎

修改my.ini配置文件 default-storage-engine= InnoDB

5）設置表的存儲引擎 語法：CREATE TABLE 表名( #省略程式碼 )ENGINE=存儲引擎;

16、數據表的存儲位置

1）MyISAM類型表文件 *.frm：表結構定義文件 *.MYD：數據文件 *.MYI：索引文件 2）InnoDB類型表文件 *.frm：表結構定義文件 ibdata1文件 注意：存儲位置 因作業系統而異，可查my.ini

datadir=”C:/ProgramData/MySQL/MySQL Server 5.5/Data/” innodb_data_home_dir=”D:/MySQL Datafiles/”

17、MySQL系統幫助

HELP 查詢內容; HELP contents; HELP Data Types; HELP INT;

補充：mysql 數值類型 中帶符號的/無帶符號的 區別

帶符號和無符號,顧名思義,就是是否有正負之分: 比如8為二進位,如果帶符號,需要用1位表示符號(1表示負數,0表示正),剩下7位表示數據. 那麼表示範圍是-127—127(包括-0和+0). 如果不帶符號,8位全部表示數據, 那麼表示範圍是 0–256

補充： mysql 有沒有nvarchar nchar

​ 有，但是，mysql採用utf-8編碼,而傳統的資料庫採用unicode,一個漢字要用兩個unicode的char,而在mysql中由於使用了utf-8,所以無論漢字還是字母,都是一個長度的char,所以就不用分nvarhcar和varchar了,一律作varchar

SQL高級（一）

1、修改表

1）#修改表名 ALTER TABLE 舊錶名 RENAME 新表名; 2）#添加欄位 ALTER TABLE 表名 ADD 欄位名 數據類型 [屬性]; 3）#修改欄位 ALTER TABLE 表名 CHANGE 原欄位名 新欄位名 數據類型 [屬性]; 4）#刪除欄位 ALTER TABLE 表名 DROP 欄位名;

2、添加主鍵

語法： ALTER TABLE 表名 ADD CONSTRAINT 主鍵名 PRIMARY KEY 表名（主鍵欄位）;

**例：**設置grade表中gradeId欄位為主鍵

 alter table `grade` add constraint `pk_grade` primary key `grade`(`gradeId`);

3、添加外鍵

語法： ALTER TABLE 表名 ADD CONSTRAINT 外鍵名 FOREIGN KEY（外鍵欄位） REFERENCES 關聯表名（關聯欄位）;

**例：**將student表的gradeId欄位和grade表的gradeId欄位建立外鍵關聯

alter table `student`  add  constraint fk_student_grade foreign key(`gradeId`) 
references `grade` (`gradeId`);

4、DML語句–插入單條數據記錄

**語法：**INSERT INTO 表名 [(欄位名列表)] VALUES (值列表);

注意： 欄位名是可選的，如省略則依次插入所有欄位 多個列表和多個值之間使用逗號分隔 值列表和欄位名列表一一對應 如插入的是表中部分數據，欄位名列表必填 示例：

INSERT INTO `student`(`loginPwd`,`studentName`,`gradeId`,`phone`,`bornDate`)
VALUES('123','黃小平',1,'13956799999','1996-5-8');

5、DML語句–插入多條數據記錄

**語法：**INSERT INTO 新表（欄位名列表）VALUES(值列表1),(值列表2),……,(值列表n); 示例：

 INSERT INTO `subject`(`subjectName`,`classHour`,`gradeID`)
VALUES('Logic Java',220,1),('HTML',160,1),('Java OOP',230,2);

經驗： 為避免表結構發生變化引發的錯誤，建議插入數據時寫明具體欄位名！

6、DML語句——將查詢結果插入新表

CREATE TABLE `phoneList`(
    SELECT `studentName`,`phone`
    FROM `student`);

註：如新表已存在，將會報錯！

7、DML語句——數據更新、刪除

1）更新數據記錄 update 表名 set 欄位1=值1,欄位2=值2,…,欄位n=值n [where 條件]; 2)刪除數據記錄

DELETE FROM 表名 [WHERE條件];
 TRUNCATE TABLE 表名;
TRUNCATE語句刪除後將重置自增列，表結構及其欄位、約束、索引保持不變，執行速度比DELETE語句快

8、DQL語句

語法：

SELECT <欄位名列表> FROM <表名或視圖> [WHERE <查詢條件>] [GROUP BY <分組的欄位名>] [HAVING <條件>] [ORDER BY <排序的欄位名> [ASC 或 DESC]] 示例：

SELECT `studentNo`,`studentName`,`phone`,`address`,`bornDate` 
FROM `student`
WHERE `gradeId` = 1
ORDER BY studentNo;

9、LIMIT子句

MySQL查詢語句中使用LIMIT子句限制結果集 語法： SELECT <欄位名列表> FROM <表名或視圖> [WHERE <查詢條件>] [GROUP BY <分組的欄位名>] [ORDER BY <排序的列名> [ASC 或 DESC]] [LIMIT [位置偏移量, ]行數];

示例：

SELECT `studentNo`,`studentName`,`phone`,`address`,`bornDate` 
FROM `student`
WHERE `gradeId` = 1
ORDER BY studentNo
 LIMIT 4,4; #(從第5條開始顯示4條)

註：使用LIMIT子句時，注意第1條記錄的位置是0！

10、常用函數——聚合函數

**AVG()**返回某欄位的平均值

COUNT() 返回某欄位的行數

**MAX()**返回某欄位的最大值

MIN() 返回某欄位的最小值

SUM() 返回某欄位的和

11、常用函數——字元串函數

CONCAT(str1,str1…strn) 字元串連接 SELECT CONCAT(‘My’,’S’,’QL’); 返回：MySQL INSERT(str,pos,len,newstr) 字元串替換 SELECT INSERT( ‘這是SQL Server資料庫’,3,10,’MySQL’); 返回：這是MySQL資料庫 LOWER(str) 將字元串轉為小寫 SELECT LOWER(‘MySQL’); 返回：mysql UPPER(str) 將字元串轉為大寫 SELECT UPPER(‘MySQL’); 返回：MYSQL SUBSTRING(str,num,len) 字元串截取 SELECT SUBSTRING( ‘JavaMySQLOracle’,5,5); 返回：MySQL

12、常用函數——時間日期函數

CURDATE() 獲取當前日期 SELECT CURDATE();返回：2016-08-08 CURTIME() 獲取當前時間 SELECT CURTIME(); 返回：19:19:26 NOW() 獲取當前日期和時間 SELECT NOW();返回：2016-08-08 19:19:26 WEEK(date) 返回日期date為一年中的第幾周 SELECT WEEK(NOW()); YEAR(date) 返回日期date的年份 SELECT YEAR(NOW()); HOUR(time) 返回時間time的小時值 SELECT HOUR(NOW()); DATEDIFF(date1,date2) 返回日期參數date1和date2之間相隔的天數 SELECT DATEDIFF(NOW(),’2008-8-8′); 返回：2881

13、常用函數——數學函數

CEIL(x)返回大於或等於數值x的最小整數 SELECT CEIL(2.3) 返回：3 FLOOR(x) 返回小於或等於數值x的最大整數 SELECT FLOOR(2.3) 返回：2 RAND() 返回0~1間的隨機數 SELECT RAND() 返回：0.5525468583708134

14、子查詢

1）子查詢是一個嵌套在 SELECT、INSERT、UPDATE 或 DELETE 語句或其他子查詢中的查詢 2）子查詢在WHERE語句中的一般用法 **語法：**SELECT … FROM 表1 WHERE 欄位1 比較運算符(子查詢) **註：**將子查詢和比較運算符聯合使用，必須保證子查詢返回的值不能多於一個 示例：

SELECT `studentNo`,`studentName`,`sex`,`bornDate`,`address`
 FROM `student` 
 WHERE `bornDate` > (SELECT `bornDate` FROM `student` WHERE `studentName`='李斯文');

15、使用子查詢替換表連接

1）實現方法一：採用表連接

SELECT `studentName` FROM `student` stu 
 INNER JOIN `result` r ON stu.studentNO = r.studentNo     	
 INNER JOIN `subject` sub ON r.subjectNo = sub.subjectNo 	
WHERE `studentResult` = 60 AND `subjectName` = 'Logic Java';

2）實現方法二：採用子查詢

SELECT `studentName` FROM `student` WHERE `studentNo` = (
SELECT `studentNo` FROM `result` 
INNER JOIN `Subject` ON result.subjectNo= subject.subjectNo 
 WHERE `studentResult`=60 AND `subjectName`='Logic Java'
);

子查詢比較靈活、方便，常作為增刪改查的篩選條件，適合於操縱一個表的數據 表連接更適合於查看多表的數據

16、IN子查詢

SELECT `studentName` FROM `student` 
WHERE `studentNo` IN(
    SELECT `studentNo` FROM `result` 
    WHERE `subjectNo` =  (
        SELECT `subjectNo` FROM `subject`
        WHERE `subjectName`='Logic Java'
    )AND `studentResult` = 60  
);

常用IN替換等於（＝）的子查詢 IN後面的子查詢可以返回多條記錄

註：非原創！

VirMach 便宜 VPS

QNews

QNews