Mysql
01 使用CMD連接資料庫
-- 在控制臺連接資料庫（需將位置切換到mysql所在地址）
mysql -u root -p
password:12345
-- 修改mysql賬戶密碼及權限，安裝配置完后慎用
update mysql.user set authentication_string = password('12345') where user = 'root' and Host = 'localhost';-- 修改用戶密碼
flush privileges; -- 重繪權限
--------------------------------------------------
-- 所有陳述句需要使用;作為結尾
show databases; -- 查看所有資料庫
use test01; -- 切換資料庫 use+資料庫名
show tables; --查看資料庫中所有的表
describe `user`; --顯示某張表的資訊
create database test01; -- 創建一個資料庫
exit; -- 退出連接
-- 單行注釋
-- 下面是多行注釋
/*
hello
*/
- 資料庫xxx語言 CRUD 增刪改查
- DDL 定義
- DML 操作
- DQL 查詢
- DCL 控制
02 操作資料庫
操作資料庫>操作資料庫中的表>操作資料庫中表的資料
mysql關鍵字不區分大小寫！！
2.1 操作資料庫
- 創建資料庫
CREATE DATABASE test01; -- 創建資料庫01
-- 但問題是如果test01存在，系統會報錯
-- 所以一般會加入一個判斷條件
CREATE DATABASE IF NOT EXISTS test01;
- 洗掉資料庫
DROP DATABASE IF EXISTS test01;
- 使用資料庫
-- tab鍵上方有一個``，如果資料庫的表名或者欄位名是一個特殊字符，就需要添加
USE `test01`;
- 查詢資料庫
SHOW DATABASES; -- 查看所有資料庫
2.2 資料庫列資料型別
數字
2.3 資料庫欄位屬性（重要）
-
unsigned：
- 無符號的整數
- 宣告了該列不能填充負數
-
zerofill：
- 0填充的
- 不足位數在前面用0來填充，例如int(3),5-->005
-
自增
- 通常理解為自增，自動在上一條記錄的基礎上+1（默認）
- 通常用來設計唯一的主鍵~index，必須是整數型別
- 可以自定義自增步長
- Navicat中Mysql自增設定在選項中
- 非空 NULL not NULL
- 假如設定為not null ，如果不給他賦值就會報錯！
- NULL，如果不填寫值，默認就是NULL！
- 默認：
- 設定默認的值
- 例如 sex，默認值為男性，如果沒有特別指定該列的值，則會有默認的值！
- 需要注意，在Navicat中，默認值設定，自增，無符號以及填充零都在下方
- 拓展
/*每一個表都必須存在以下五個欄位，未來做專案都有用，表示一個記錄存在的意義
id 主鍵
is_delete 偽洗掉
gmt_create 創建時間
gmt_update 更新時間
*/
2.4 創建資料庫表（重點）
-- 目標：創建一個school資料庫
-- 創建學生表（列，欄位） 使用SQL陳述句創建
-- 學號int 登陸密碼varchar(20) 姓名 性別varchar(2) 出生日期(datetime) 家庭住址 email
-- 注意點，使用英文()，表的名稱和欄位盡可能使用``括起來
-- AUTO_INCREMENT 自增
-- 字串使用單引號括起來；
-- 所有的陳述句后面加,(英文的),最后一個不用加
-- PRIMARY KEY 主鍵，一般一個表只有一個唯一的主鍵
CREATE TABLE IF NOT EXISTS `studentinformation`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '學號',
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT'姓名',
`pwd` VARCHAR(20) NOT NULL DEFAULT '12345' COMMENT'密碼',
`sex` VARCHAR(2) NOT NULL DEFAULT'男' COMMENT'性別',
`birthday` DATETIME DEFAULT NULL COMMENT'出生日期',
`address` VARCHAR(100) DEFAULT NULL COMMENT'家庭地址',
`email` VARCHAR(50) DEFAULT NULL COMMENT'電子郵箱',
PRIMARY KEY(`id`)
)ENGINE = INNODB DEFAULT CHARSET=utf8
格式
CREATE TABLE [IF NOT EXISTS] `表名`(
`欄位名` 列型別 [屬性] [索引][注釋],
`欄位名` 列型別 [屬性] [索引][注釋],
·······
`欄位名` 列型別 [屬性] [索引][注釋]
)[表型別][字符集設定][注釋]
2.5 資料表的型別
-- 關于資料庫引擎
/*
INNODB 默認使用~
MYISAM 早些年使用
*/
| MYISAM | INNODB | |
|---|---|---|
| 事務支持 | 不支持 | 支持 |
| 資料行鎖定（同步） | 不支持 | 支持 |
| 外鍵約束 | 不支持 | 支持 |
| 全文索引 | 支持 | 不支持 |
| 表空間大小 | 較小 | 較大，約為前者2倍 |
常規使用操作：
- MYISAM 節約空間，速度較快
- INNODB 安全性高，事務的處理，多表多用戶操作
在物理空間的位置
所有的資料庫檔案都存在data目錄下
所以資料庫的本質還是檔案的存盤
Mysql引擎在物理檔案上的區別
- InnoDB在資料庫表中只有一個*.frm檔案，以及上級目錄下的ibdata1檔案
- MYISAM對應檔案
- *.frm 表結構的定義檔案
- *.MYD 資料檔案（data）
- *.MYI 索引檔案（index）
- 這里有必要提到的是，MySQL8開始洗掉了原來的frm檔案，并采用 Serialized Dictionary Information (SDI), 是MySQL8.0重新設計資料詞典后引入的新產物,并開始已經統一使用InnoDB存盤引擎來存盤表的元資料資訊，SDI資訊源記錄保存在ibd檔案中，
如何可以查看表結構資訊，官方提供了一個工具叫做ibd2sdi，在安裝目錄下可以找到，可以離線的將ibd檔案中的冗余存盤的sdi資訊提取出來，并以json的格式輸出到終端，可參考一篇CSDN博客，https://blog.csdn.net/philipslu/article/details/102499234
設定資料庫表的字符集編碼
CHARSET = utf8
也可以采取在資料庫安裝目錄下新建一個ini檔案的方式進行默認設定，不如不單獨設定，系統會默認為一個latin字符集，我的ini檔案格式如下：
#設定3306埠
port=3306
#設定mysql的安裝目錄 ----------是你的檔案路徑-------------
basedir=E:\WorkappPackage\Mysql\mysql-8.0.32-winx64
#設定mysql資料庫的資料的存放目錄 ---------是你的檔案路徑data檔案夾自行創建
#datadir=E:\WorkappPackage\Mysql\mysql-8.0.32-winx64\data
#允許最大連接數
max_connections=200
#允許連接失敗的次數，
max_connect_errors=10
#服務端使用的字符集默認為utf8mb4
character-set-server=utf8mb4
#創建新表時將使用的默認存盤引擎
default-storage-engine=INNODB
#默認使用“mysql_native_password”插件認證
#mysql_native_password
default_authentication_plugin=mysql_native_password
[mysql]
#設定mysql客戶端默認字符集
default-character-set=utf8mb4
[client]
#設定mysql客戶端連接服務端時默認使用的埠
port=3306
default-character-set=utf8mb4
- 前面有關于檔案路徑以及data保存路徑的設定需要根據實際情況進行調整，后面的埠設定為系統默認的3306，其他相關內容可參考文章：https://blog.csdn.net/weixin_42820850/article/details/129108669
2.6 修改洗掉表
修改
-- 修改表名：ALTER TABLE 舊表名 RENAME AS 新表名
ALTER TABLE test01 Rename AS test02
-- 增加表的欄位：ALTER TABLE 表名 ADD 欄位名 列屬性
ALTER TABLE test02 ADD age INT(10)
-- 修改表的欄位（重命名，修改約束）
-- ALTER TABLE 表名 MODIFY 欄位名 列屬性[]
ALTER TABLE test02 MODIFY age VARCHAR(10)
-- ALTER TABLE 表名 CHANGE 舊名字 新名字 列屬性[]
ALTER TABLE test01 CHANGE age age1 INT(1) -- 欄位重命名
-- 洗掉表的欄位
ALTER TABLE test01 DROP age1
- 可以觀察到，所有對于表的修改都是以ALTER開頭的
洗掉
-- 洗掉表(加入判斷條件，如果存在再洗掉)
DROP TABLE IF EXISTS test01
- 需要注意，所有的創建和洗掉盡量加上判斷，以免出現報錯
03 MySQL資料管理
3.1 資料庫級別外鍵（了解）
方式一：在創建表的時候，增加外鍵陳述句和關聯陳述句等約束（比較復雜）
CREATE TABLE IF NOT EXISTS `studentinformation`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '學號',
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT'姓名',
`pwd` VARCHAR(20) NOT NULL DEFAULT '12345' COMMENT'密碼',
`sex` VARCHAR(2) NOT NULL DEFAULT'男' COMMENT'性別',
`birthday` DATETIME DEFAULT NULL COMMENT'出生日期',
`gradeid` INT(3) NOT NULL COMMENT'年級id',
`address` VARCHAR(100) DEFAULT NULL COMMENT'家庭地址',
`email` VARCHAR(50) DEFAULT NULL COMMENT'電子郵箱',
PRIMARY KEY(`id`),
KEY `FK_gradeid`(`gradeid`), -- 外鍵名稱設定
CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`) -- 關聯約束設定
)ENGINE = INNODB DEFAULT CHARSET=utf8
CREATE TABLE IF NOT EXISTS `grade`(
`gradeid` INT(3) NOT NULL COMMENT'年級id',
`gradename` VARCHAR(10) NOT NULL COMMENT'年級名稱',
PRIMARY KEY(`gradeid`)
)ENGINE = INNODB DEFAULT CHARSET=utf8
- 運行結果如下
- 可以看到此時由于外鍵關聯關系的存在，原表在洗掉以及對某些欄位內容進行修改時都收到了約束
- 洗掉時需要先洗掉參考的表，然后再回來洗掉被參考的表
方法二：正如上面所示，可以在外鍵部分手動添加，這里不做展開，與上面運行結果類似即可
方法三：
CREATE TABLE IF NOT EXISTS `grade`(
`gradeid` INT(3) NOT NULL COMMENT'年級id',
`gradename` VARCHAR(10) NOT NULL COMMENT'年級名稱',
PRIMARY KEY(`gradeid`)
)ENGINE = INNODB DEFAULT CHARSET=utf8
-- 外部創建外鍵約束
CREATE TABLE IF NOT EXISTS `studentinformation`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '學號',
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT'姓名',
`pwd` VARCHAR(20) NOT NULL DEFAULT '12345' COMMENT'密碼',
`sex` VARCHAR(2) NOT NULL DEFAULT'男' COMMENT'性別',
`birthday` DATETIME DEFAULT NULL COMMENT'出生日期',
`gradeid` INT(3) NOT NULL COMMENT'年級id',
`address` VARCHAR(100) DEFAULT NULL COMMENT'家庭地址',
`email` VARCHAR(50) DEFAULT NULL COMMENT'電子郵箱',
PRIMARY KEY(`id`)
)ENGINE = INNODB DEFAULT CHARSET=utf8
-- 創建表的時候沒有外鍵關系
-- 外部添加外鍵約束
ALTER TABLE `studentinformation`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)
-- 結構
ALTER TABLE 表
ADD CONSTRAINT 約束名 FOREIGN KEY(`作為外鍵的列`)
- 但需要注意的是，以上的操作均為物理外鍵，是在資料庫級別進行操作的外鍵，一般不推薦使用，（避免資料庫過多造成困擾）
最佳實踐
- 資料庫就是單純的表，只用來存資料，只有行（資料）和列（欄位）
- 如果想使用多張表的關聯操作，即使用外鍵（在程式中實作）
參考文章：https://www.cnblogs.com/rjzheng/p/9907304.html
3.2 DML語言（需要全部記住）
資料庫意義：資料存盤、資料管理
DML語言：資料操作語言
- INSERT
- UPDATE
- DELETE
3.3 添加INSERT
格式
-- 插入陳述句（添加）
-- INSERT INTO 表名 (`欄位名1`,`欄位名2`,`欄位名3`) VALUES('第一條資料欄位1資料','第一條資料欄位2資料','第一條資料欄位3資料'),('第二條資料欄位1資料','第二條資料欄位2資料','第二條資料欄位3資料')····
-- 例：
INSERT INTO `grade`(`gradename`)VALUES('大一')
-- 需注意的是，跟在表名后面的括號內容可以選擇表中的部分欄位，但是需要觀察表結構，那些非空且沒有默認值的或者沒設定自增的欄位必須包括在內，否則會報錯，還需注意，選定了括號內容后，VALUES后面的括號內的結構需與前面保持一致，如果想一次性添加多條資料，需要在括號中間用“,”隔開，
-- 錯誤示例：
INSERT INTO `grade` VALUES('大二')
-- 此處在表名`grade`后面沒有括號，因此Mysql就會默認格式為全部欄位，依照上面所述，資料庫會對VALUES后面資料進行匹配，因此會報錯，
-- 正確示例：
INSERT INTO `grade` VALUES(2,'大二')
-- 插入多條資料
INSERT INTO `grade`(`gradename`)
VALUES ('大三'),('大四')
- 再次強調，必須注意標點符號的中英文格式！
注意事項：
- 欄位和欄位之間用英文逗號隔開
- 欄位是可以省略的，但是后面括號內的值需要一一對應，不能少
- 可以同時插入多條資料，VALUES后面的值，需要使用隔開即可
3.4 修改UPDATE
3.5 洗掉DELET
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/549369.html
標籤：其他