MySQL 基礎概念與簡單使用
一、數據庫基本概念
1、數據庫定義
數據庫（Database）是存儲在計算機內、有組織、可共享的數據集合，用于高效地管理大量數據。
2、數據庫分類
按數據模型分類：
- 關系型數據庫（如 MySQL、Oracle、SQL Server）
- 數據按表格存儲，表間通過關系鏈接。強調結構規范和一致性，使用SQL語言
- 非關系型數據庫（如 Redis、MongoDB）
- 非關系型數據庫：打破固定表格結構，形式靈活（如文檔、鍵值對等）。為高性能、高擴展性而生
- 層級型數據庫
- 數據像家族樹一樣嚴格按父子層次結構組織，每個節點只能有一個父節點
- 網絡型數據庫
- 是層級數據庫的擴展，允許一個節點有多個父節點，形成像網一樣的復雜結構
按存儲介質分類：
- 磁盤（關系）數據庫：數據持久化存儲在磁盤中（如 MySQL、PostgreSQL）
- 內存（非關系）數據庫：數據存儲在內存中，讀寫速度快（如 Redis）
二、關系型數據庫
1、基本概念
- 核心概念：數據以**表（Table）**的形式組織。每個表由行（Row）和列（Column）組成。
- 表：代表一個實體類型，如“用戶表”、“訂單表”。
- 行：代表一個具體的實體記錄，如“一個用戶的信息”。
- 列：代表實體的一個屬性，如“姓名”、“年齡”。
- 關鍵特性：
- 結構化數據： schema（模式）是預先定義且嚴格的，規定了每列的數據類型（如整數、字符串等）
- SQL：使用結構化查詢語言進行操作和查詢，功能強大且通用
- ACID事務：保證了數據的原子性、一致性、隔離性、持久性，這是金融、交易等系統的基石
- 關系：表與表之間通過主鍵和外鍵建立關聯，避免了數據冗余
- 優點：數據一致性高、支持復雜查詢、技術成熟、生態完善
- 缺點：在處理海量數據和高并發讀寫時，性能可能成為瓶頸（盡管可以通過集群等方式緩解）；schema 不夠靈活，修改結構比較麻煩
2、典型關系型數據庫
- 大型：Oracle、DB2
- 中型：SQL Server、MySQL
- 小型：Access、SQLite
三、SQL 分類
SQL（Structured Query Language）根據其功能主要分為以下五大類：
1、數據定義語言 (DDL - Data Definition Language)
- 職責：用于定義和管理數據庫中的結構，如數據庫、表、索引、視圖等。這些操作通常會自動提交（隱式事務），無法回滾
- 核心指令：
CREATE：創建新的數據庫、表、索引等ALTER：修改現有的數據庫對象結構（例如，添加、刪除或修改列）DROP：刪除整個數據庫、表或索引TRUNCATE：清空表中的所有數據（保留表結構）
2、數據查詢語言 (DQL - Data Query Language)
- 職責：用于從數據庫中查詢/檢索數據，而不對數據本身進行修改。是SQL中最核心和最常用的部分
- 核心指令：
SELECT：從表中選擇數據SHOW（在某些數據庫如MySQL中用于顯示數據庫、表等信息）
- 常用子句/關鍵字：
FROM：指定要查詢的表WHERE：指定過濾條件ORDER BY：對結果集進行排序GROUP BY：將結果集按一列或多列分組HAVING：對分組后的結果進行過濾JOIN：用于連接多個表
3、數據操作語言 (DML - Data Manipulation Language)
- 職責：用于對數據庫表中的**記錄（行）**進行增、刪、改操作
- 核心指令：
INSERT：向表中插入新記錄UPDATE：更新表中已存在的記錄DELETE：刪除表中的記錄
4、數據控制語言 (DCL - Data Control Language)
- 職責：用于控制對數據庫的訪問權限和安全性
- 核心指令：
GRANT：授予用戶或角色訪問數據庫對象的權限REVOKE：撤銷之前授予的權限
5、事務處理語言 (TPL - Transaction Processing Language)
- 職責：用于管理數據庫中的事務，確保數據的完整性和一致性，保證一系列DML操作要么全部成功，要么全部失敗
- 核心指令：
BEGIN TRANSACTION/START TRANSACTION：開始一個事務COMMIT：提交事務，確認所有更改，使其永久化ROLLBACK：回滾事務，撤銷所有未提交的更改
四、MySQL 基本操作
1、對于數據庫
-
創建數據庫
create database yy; -
查看數據庫
show databases; -
進入數據庫
use yy; -
刪除數據庫
drop database yy;
2、對于表
常用約束：
PRIMARY KEY：主鍵UNIQUE KEY：唯一鍵auto_increment：自增約束NOT NULL：非空DEFAULT：默認值CHECK：檢查約束（MySQL 8.0+ 支持）foreign key references：外鍵
-
創建表
create table xx (-> id int primary key auto_increment,-> name varchar(10) not null,-> age int(3)) engine=InnoDB default charset=utf8; -
查看表
show tables; -
查看表的列信息
show columns from xx; -
刪除表
drop table xx;
3、其他查看命令
-
查看支持的所有字符集
show character set; -
查看當前數據庫支持的所有存儲引擎
show engines; -
不進入某數據庫而列出其包含的所有表
show tables from yy; -
查看某表的創建命令
show create table xx; -
查看某表的狀態
show table status like 'xx'\G
五、用戶操作與DCL
MySQL用戶賬號
由兩部分組成，‘USERNAME’@‘HOST’。其中，HOST的值可為：
- IP地址
- 通配符：
%：匹配任意長度的任意字符，常用于設置允許從任何主機登錄_：匹配任意單個字符
創建授權grant
- 權限類型(priv_type)
ALL：所有權限SELECT：讀取內容的權限INSERT：插入內容的權限UPDATE：更新內容的權限DELETE：刪除內容的權限
- 指定要操作的對象
*.*：所有庫的所有表- db_name.*` ：指定庫的所有表
- db_name.table_name` ：指定庫的指定表
- WITH GRANT OPTION：被授權的用戶可將自己的權限副本轉贈給其他用戶，說白點就是將自己的權限完全復制給另一個用戶。不建議使用。
-
創建數據庫用戶
create user 'yanyvhang'@'192.168.100.20' identified by 'wiltjer'; -
使用新創建用戶和密碼登錄
切換到 192.168.100.20 主機進行操作
mysql -uyanyvhang -h192.168.100.10 -pwiltjer -
授權
示例：
授權 yanyvhang 用戶在 192.168.100.20 上遠程登錄讀取所有數據庫
grant select on *.* to 'yanyvhang'@'192.168.100.20' identified by 'wiltjer'; -
重讀授權表
flush privileges; -
查看授權
切換到 192.168.100.20 主機進行操作
show grants; -
撤銷授權
revoke select on *.* from 'yanyvhang'@'192.168.100.20'; -
刪除數據庫用戶
drop user 'yanyvhang'@'192.168.100.20';
六、DML操作
DML操作包括增(INSERT)、刪(DELETE)、改(UPDATE)、查(SELECT)，均屬針對表的操作
INSERT語句
語法
insert [into] table_name [(column_name,...)] {values | value} (value1,...),(...),...
-
一次插入一條記錄
insert into xx (id,name,age) value (1,'tom',20); -
一次插入多條記錄
insert into xx (id,name,age) value (2,'jerry',23),-> (3,'zs',22),-> (4,'li',25),-> (5,'ww',26),-> (6,'zl',20),-> (7,'lq',NULL);
SELECT語句
字段column表示法
-
*：所有字段 -
column1, column2：選擇指定字段 -
as：字段別名，column1 AS alias_name -
當表名很長時用別名代替
條件判斷語句WHERE
>，<，>=，<=，=，!=：操作符BETWEEN value1 AND value2：范圍查詢LIKE：模糊匹配IS NULL：空值IS NOT NULL：非空
條件邏輯操作
AND：與OR：或NOT：非
ORDER BY語句
ORDER BY column_nam：升序（默認）ORDER BY column_name ASC：升序ORDER BY column_name DESC：降序
LIMIT 限制
LIMIT n：取前n個結果LIMIT n,m：跳過m個結果，取后面n個結果
語法
SELECT column1,column2,... FROM table_name [WHERE clause] [ORDER BY 'column_name' [DESC]] [LIMIT [m,]n];
-
選擇所有字段
select * from xx; -
選擇 name 字段
select name from xx; -
按照年齡進行升序排序
select * from xx order by age; -
按照年齡進行降序排序
select * from xx order by age desc; -
按照年齡進行升序排序，取前兩個結果
select * from xx order by age limit 2; -
按照年齡進行升序排序，取前第二、第三個結果
select * from xx order by age limit 1,2; -
查詢年齡大于等于25的所有
select * from xx where age >= 25; -
查詢年齡大于等于25且名字為ww的所有字段
select * from xx where age >= 25 and name = 'ww'; -
查詢年齡在22到25之間的所有字段
select * from xx where age between 22 and 25; -
查詢年齡為空值的所有字段
select * from xx where age is null;
update語句
語法
UPDATE table_name SET column1 = new_value1[,column2 = new_value2,...] [WHERE clause] [ORDER BY 'column_name' [DESC]] [LIMIT [m,]n];
-
將名字為lq的年齡改為28
update xx set age = 28 where name = 'lq';
delete語句
語法
DELETE FROM table_name [WHERE clause] [ORDER BY 'column_name' [DESC]] [LIMIT [m,]n];
-
刪除id為7的記錄
delete from xx where id = 7; -
刪除整張表的內容
delete from xx;
truncate語句
| 特性 | DELETE | TRUNCATE |
|---|---|---|
| 刪除方式 | 逐行刪除，記錄每行刪除日志 | 釋放數據頁方式刪除，只記錄頁釋放 |
| 事務日志 | 在事務日志中記錄每行刪除操作 | 只在事務日志中記錄頁釋放 |
| 恢復可能性 | 可通過回滾事務日志恢復數據 | 刪除后無法恢復數據 |
| 執行速度 | 相對較慢 | 執行速度快 |
| 資源占用 | 占用較多系統和事務日志資源 | 使用較少系統資源 |
| 表結構 | 保留表結構、約束和索引 | 保留表結構、約束和索引 |
| 標識列 | 不重置標識列的計數值 | 重置標識列的計數值為初始值 |
| 外鍵約束 | 可用于有外鍵約束的表 | 不能用于有外鍵約束引用的表 |
| 索引視圖 | 可用于加入了索引視圖的表 | 不能用于加入了索引視圖的表 |
| 觸發器 | 會激活觸發器 | 不會激活觸發器 |
語法
TRUNCATE table_name;
七、示例參考
-
搭建mysql服務
略
-
創建一個以你名字為名的數據庫，并創建一張表student
create database yanyvhang; use yanyvhang create table student (-> id int(11) not null auto_increment,-> name varchar(100) not null,-> age tinyint(4),-> primary key(id));
-
查看下該新建的表有無內容（用select語句）
select * from student;
-
往新建的student表中插入數據（用insert語句）
insert into student (id,name,age) value (1,'tom',20),-> (2,'jerry',23),-> (3,'chenyu',25),-> (4,'sean',28),-> (5,'zhangshan',26),-> (6,'zhangshan',20),-> (7,'lisi',NULL),-> (8,'chenshuo',10),-> (9,'wangwu',3),-> (10,'qiuyi',15),-> (11,'qiuxiaotian',20);
-
修改lisi的年齡為50
update student set age = 50 where name = 'lisi';
-
以age字段降序排序
select * from student order by age desc;
-
查詢student表中年齡最小的3位同學跳過前2位
select * from student order by age limit 2,3;
-
查詢student表中年齡最大的4位同學
select * from student order by age desc limit 4;
-
查詢student表中名字叫zhangshan的記錄
select * from student where name = 'zhangshan';
-
查詢student表中名字叫zhangshan且年齡大于20歲的記錄
select * from student where name = 'zhangshan' and age > 20;
-
查詢student表中年齡在23到30之間的記錄
select * from student where age between 23 and 30;
-
修改wangwu的年齡為100
update student set age = 100 where name = 'wangwu';
-
刪除student中名字叫zhangshan且年齡小于等于20的記錄
delete from student where name = 'zhangshan' and age <= 20;
-
創建表course表，要求如下
id字段，int(3)，主鍵
course_name字段，varchar(100)create table course (-> id int(3) primary key,-> course_name varchar(100)); -
為course表插入數據
insert into course (id,course_name) value (1,'Java'),-> (2,'MySQL'),-> (3,'Python'),-> (4,'Go'),-> (5,'C++');
-
創建student123表，要求如下
id字段 int(3) 主鍵
name varchar(100)
age int(3)
sex varchar(10)
height int(3)
course_id int(3) 外鍵，參照course表中的主鍵create table student123 (-> id int(3) primary key,-> name varchar(100),-> age int(3),-> sex varchar(10),-> height int(3),-> course_id int(3),-> foreign key(course_id) references course(id));
-
為student123表插入數據
insert into student123 (id,name,age,sex,height,course_id) value (1,'Dany',25,'man',160,1),-> (2,'Green',23,'man',158,2),-> (3,'Henry',23,'woman',185,1),-> (4,'Jane',22,'man',162,3),-> (5,'Jim',24,'woman',175,2),-> (6,'John',21,'woman',172,4),-> (7,'Lily',22,'man',165,4),-> (8,'Susan',23,'man',170,5),-> (9,'Thomas',22,'woman',178,5),-> (10,'Tom',23,'woman',165,5);
內存文件映射mmap)
