MySQL基础介绍

一、SQL语句分类

1.1 客户登录操作

• 登录服务器：mysql -uroot -p123 -hlocalhost

-u：指定登录用户名 -p：指定登录密码（密码紧跟p无空格，也可只写-p回车后交互式输入密码更安全） -h：指定数据库服务IP/主机名，本地可省略-hlocalhost -P：大写P指定端口，默认3306

• 退出服务器：exit/quit/\q

1.2 SQL语句四大分类

1.2.1 DDL（Data Definition Language）数据定义语言

作用：创建、删除、修改数据库、表、字段、索引等库表结构
关键字：CREATE、DROP、ALTER、TRUNCATE

1.2.2 DML（Data Manipulation Language）数据操作语言

作用：增删改表中的行数据记录
关键字：INSERT、UPDATE、DELETE

1.2.3 DCL（Data Control Language）数据控制语言

作用：管理用户、分配/回收权限、控制数据库访问安全
关键字：CREATE USER、DROP USER、GRANT、REVOKE、FLUSH PRIVILEGES

1.2.4 DQL（Data Query Language）数据查询语言

作用：查询表数据，业务最常用
关键字：SELECT

1.3 常用字段类型选型规范

1. 唯一ID、32位UUID：CHAR(32)（定长，查询更快）
2. 名称、地址、简介、短文本：VARCHAR(50/100/200)（变长，节省空间）
3. 长文本、文章详情、富文本：TEXT
4. 布尔开关状态（0关闭/1开启）：TINYINT(1)
5. 普通数字、次数、数量：INT
6. 雪花ID、超大自增编号：BIGINT
7. 金额、价格（精确小数）：DECIMAL(总长度,小数位数)，例DECIMAL(10,2)
8. 生日、仅年月日：DATE
9. 创建时间、下单完整时间（年月日时分秒）：DATETIME
10. 修改自动更新时间戳：TIMESTAMP（受时区影响，范围更小）

二、DDL 数据定义语言

2.1 数据库操作

1. 查看所有数据库

SHOWDATABASES;-- 等价SHOWSCHEMAS;

2. 切换指定数据库

USE数据库名;

3. 创建数据库（推荐utf8mb4完整支持emoji）

CREATEDATABASE[IFNOTEXISTS]bookstoreDEFAULTCHARACTERSETutf8mb4DEFAULTCOLLATEutf8mb4_unicode_ci;

• IF NOT EXISTS：不存在才创建，避免库已存在报错

4. 删除数据库

DROPDATABASE[IFEXISTS]bookstore;

5. 修改数据库字符集

ALTERDATABASEbookstoreCHARACTERSETutf8mb4;

2.2 数据表操作

2.2.1 创建表

CREATETABLE[IFNOTEXISTS]表名(字段名 类型[约束],字段名 类型[约束],...);

2.2.2 查看操作

SHOWTABLES;-- 查看当前库所有表SHOWCREATETABLE表名;-- 查看建表完整语句（含引擎、字符集、注释）DESC表名;-- 简易查看表结构DESCRIBE表名;-- 等价DESC

2.2.3 删除表

DROPTABLE[IFEXISTS]表名;

2.2.4 清空表 TRUNCATE

TRUNCATETABLE表名;

特性：

1. 清空全部数据，自增主键重置从1开始
2. 表结构、索引、约束、字符集、引擎全部保留
3. 不记录日志，速度远快于DELETE
4. 无法事务回滚
5. 外键关联场景下需先解除外键依赖才能执行

2.2.5 修改表 ALTER TABLE

1. 添加字段（支持批量）

ALTERTABLE表名ADD(字段1类型 约束,字段2类型 约束);-- 单个字段ALTERTABLE表名ADDageINT;

2. 修改字段类型/约束（不改字段名）MODIFY

ALTERTABLE表名MODIFYageTINYINTNOTNULL;

注意：字段已有数据时，类型缩小可能丢失数据、报错

3. 修改字段名+类型CHANGE（功能包含MODIFY）

ALTERTABLE表名 CHANGE old_name new_nameVARCHAR(50);

4. 删除字段

ALTERTABLE表名DROPCOLUMNage;-- 简写ALTERTABLE表名DROPage;

5. 修改表名

ALTERTABLE旧表名RENAMETO新表名;-- 简写ALTERTABLE旧表名RENAME新表名;

三、DML 数据操作语言

3.1 插入 INSERT

1. 指定字段插入（推荐，扩展性强）

INSERTINTO表名(列1,列2,...)VALUES(值1,值2,...);

未指定的字段若无默认值会填充NULL，值顺序、数量必须和字段一一对应。

2. 全字段插入（需匹配建表字段全部顺序）

INSERTINTO表名VALUES(值1,值2,值3);

3. 批量插入（高效）

INSERTINTO表名(name,age)VALUES('张三',18),('李四',20);

3.2 更新 UPDATE

UPDATE表名SET列1=值1,列2=值2[WHERE条件];

⚠️ 重要：不加WHERE会全表更新，线上禁止！

WHERE常用运算符

=、!=、<>、>、<、>=、<=
区间：BETWEEN A AND B（闭区间，包含两端）
多值匹配：IN(值1,值2)
空值判断：IS NULL/IS NOT NULL
逻辑：AND、OR、NOT

示例：

UPDATEpersonSETgender='男',age=age+1WHEREsid=1;WHEREageBETWEEN18AND80;WHEREnameIN('张三','李四');WHEREphoneISNULL;

3.3 删除 DELETE

DELETEFROM表名[WHERE条件];

⚠️ 不加WHERE清空整张表，可回滚（事务内），自增主键不会重置。

四、DCL 数据控制语言

4.1 创建用户

-- 仅localhost本地登录CREATEUSERuser01@'localhost'IDENTIFIEDBY'123456';-- 任意IP远程登录 %代表通配CREATEUSERuser01@'%'IDENTIFIEDBY'123456';

4.2 授权 GRANT

-- 指定库指定权限GRANTSELECT,INSERT,UPDATE,DELETE,CREATE,ALTER,DROPONbookstore.*TOuser01@'%';-- 某库全部权限GRANTALLPRIVILEGESONbookstore.*TOuser01@'%';-- 所有库所有权限（超级权限）GRANTALLON*.*TOroot@'%';

授权后刷新权限生效：

FLUSHPRIVILEGES;

4.3 回收权限 REVOKE

REVOKECREATE,ALTER,DROPONbookstore.*FROMuser01@'localhost';

4.4 查看用户权限

SHOWGRANTSFORuser01@'localhost';

4.5 删除用户

DROPUSERIFEXISTSuser01@'%';

五、DQL 数据查询语言

5.0 测试表初始化

DROPDATABASEIFEXISTSexam;CREATEDATABASEexamDEFAULTCHARSETutf8mb4;USEexam;-- 部门表CREATETABLEdept(deptnoINTPRIMARYKEYCOMMENT'部门编号',dnameVARCHAR(50)COMMENT'部门名称',locVARCHAR(50)COMMENT'部门地点')ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;-- 雇员表CREATETABLEemp(empnoINTPRIMARYKEYCOMMENT'员工编号',enameVARCHAR(50)COMMENT'员工姓名',jobVARCHAR(50)COMMENT'岗位',mgrINTCOMMENT'直属领导编号',hiredateDATECOMMENT'入职日期',salDECIMAL(7,2)COMMENT'基本工资',commDECIMAL(7,2)COMMENT'奖金',deptnoINTCOMMENT'所属部门编号',-- 自关联：领导也是员工，关联本表empnoCONSTRAINTfk_emp_mgrFOREIGNKEY(mgr)REFERENCESemp(empno),-- 关联部门表CONSTRAINTfk_emp_deptFOREIGNKEY(deptno)REFERENCESdept(deptno))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;

5.1 基础查询

5.1.1 字段控制

1. 查询全部列

SELECT*FROMemp;

2. 查询指定列（推荐，减少IO）

SELECTempno,ename,salFROMemp;

3. 结果去重 DISTINCT

SELECTDISTINCTjobFROMemp;

注：GROUP BY分组是聚合用途，不要用来单纯去重，MySQL独有写法不通用。

4. 列运算、函数、别名

• 数值运算

SELECTsal*1.2ASnew_salFROMemp;

• 字符串拼接CONCAT()

SELECTCONCAT('底薪：',sal)ASsalaryFROMemp;

• NULL值替换IFNULL(字段,默认值)

-- 奖金为NULL按0计算SELECTIFNULL(comm,0)+salAStotalFROMemp;

• 别名AS可省略

SELECTename nameFROMemp;

5.1.2 条件 WHERE

SELECTempno,ename,salFROMempWHEREsal>3000ANDcommISNOTNULL;SELECT*FROMempWHEREsalBETWEEN2000AND5000;SELECT*FROMempWHEREjobIN('经理','程序员');

5.1.3 模糊匹配 LIKE

• _：匹配单个任意字符

-- 姓张，名字两个字SELECT*FROMempWHEREenameLIKE'张_';-- 名字三个字SELECT*FROMempWHEREenameLIKE'___';

• %：匹配0~N个任意字符

-- 所有姓张员工SELECT*FROMempWHEREenameLIKE'张%';-- 姓名包含"阿"SELECT*FROMempWHEREenameLIKE'%阿%';

注意：LIKE '%'无法匹配NULL字段；前置%无法走索引，大数据避免。

5.2 排序 ORDER BY

-- 工资升序 ASC默认可省略SELECT*FROMempORDERBYsalASC;SELECT*FROMempORDERBYsal;-- 奖金降序 DESC不可省略SELECT*FROMempORDERBYcommDESC;-- 多字段排序：工资升序，工资相同则奖金降序SELECT*FROMempORDERBYsalASC,commDESC;

5.3 聚合函数（纵向统计，忽略NULL）

COUNT()、MAX()、MIN()、SUM()、AVG()

COUNT(*)-- 统计所有行数（包含NULL）COUNT(comm)-- 统计comm不为NULL的行数MAX(sal)-- 最高工资MIN(sal)-- 最低工资SUM(sal)-- 工资总和AVG(sal)-- 平均工资

示例：

SELECTCOUNT(*),MAX(sal),MIN(sal),SUM(sal),AVG(sal)FROMemp;

5.4 分组查询 GROUP BY / HAVING

1. GROUP BY：按字段分组，聚合统计每组数据

-- 每个部门人数SELECTdeptno,COUNT(*)emp_countFROMempGROUPBYdeptno;-- 每个岗位最高薪资SELECTjob,MAX(sal)max_salFROMempGROUPBYjob;

2. HAVING：分组后过滤结果（WHERE过滤原始行，HAVING过滤分组后聚合结果）

-- 只查询人数大于3的部门SELECTdeptno,COUNT(*)cntFROMempGROUPBYdeptnoHAVINGcnt>3;

区分：

• WHERE：原始表数据过滤，不能用聚合函数
• HAVING：分组后过滤，只能使用聚合函数/分组字段

5.5 LIMIT 分页限制

语法：LIMIT 偏移量, 查询条数
偏移量从0开始：LIMIT 0,10取前10条
分页计算公式：

起始偏移量 = (当前页码 - 1) * 每页条数

示例：每页10条，查询第3页

-- (3-1)*10 = 20，从第21条开始取10条SELECT*FROMempLIMIT20,10;

六、字段约束

6.1 主键 PRIMARY KEY

特性：唯一 + 非空，一张表只能一个主键，可被外键关联

1. 建表时指定单列主键

CREATETABLEstu(sidINTPRIMARYKEY,snameVARCHAR(20));

2. 末尾统一声明主键（适合复合主键）

CREATETABLEstu(sidINT,snameVARCHAR(20),PRIMARYKEY(sid));

3. 后期添加/删除主键

ALTERTABLEstuADDPRIMARYKEY(sid);ALTERTABLEstuDROPPRIMARYKEY;

4. 自增主键 AUTO_INCREMENT（仅INT/BIGINT主键可用）

CREATETABLEstu(sidINTPRIMARYKEYAUTO_INCREMENT,snameVARCHAR(20)NOTNULL);-- 修改字段开启自增ALTERTABLEstu CHANGE sid sidINTAUTO_INCREMENT;-- 移除自增ALTERTABLEstu CHANGE sid sidINT;

6.2 非空 NOT NULL

字段不允许存入NULL，插入/更新必须传值

CREATETABLEstu(sidINTPRIMARYKEYAUTO_INCREMENT,snameVARCHAR(20)NOTNULL);

6.3 唯一 UNIQUE

字段值全局唯一，允许存一个NULL（NULL不参与唯一性校验）

CREATETABLEstu(sidINTPRIMARYKEYAUTO_INCREMENT,phoneVARCHAR(11)UNIQUE);

6.4 外键 FOREIGN KEY（InnoDB引擎支持）

规则：

1. 外键字段类型必须和引用主键完全一致

2. 外键允许重复、允许NULL

3. 一张表可多个外键
   语法：CONSTRAINT 约束名 FOREIGN KEY(外键字段) REFERENCES 主表(主键)

4. 建表时创建外键

CREATETABLEemp(empnoINTPRIMARYKEY,deptnoINT,CONSTRAINTfk_emp_deptFOREIGNKEY(deptno)REFERENCESdept(deptno));

2. 建表后添加外键（原文ALERT笔误修正为ALTER）

ALTERTABLEempADDCONSTRAINTfk_emp_deptFOREIGNKEY(deptno)REFERENCESdept(deptno);

3. 删除外键（必须填约束名）

ALTERTABLEempDROPFOREIGNKEYfk_emp_dept;

七、多表查询

7.1 合并结果集 UNION / UNION ALL

要求：多张查询结果列数量相同、对应列类型兼容

• UNION：自动去重，性能差
• UNION ALL：直接拼接，不去重，性能更高（推荐）

-- 合并两个部门员工，不去重SELECT*FROMempWHEREdeptno=10UNIONALLSELECT*FROMempWHEREdeptno=20;

7.2 连接查询 JOIN

7.2.1 内连接 INNER JOIN（只返回两边匹配到的数据）

1. 隐式内连接（逗号写法，方言）

SELECT*FROMdept d,emp eWHEREd.deptno=e.deptno;

2. 标准INNER JOIN（推荐可读性高）

SELECT*FROMdept dINNERJOINemp eONd.deptno=e.deptno;

7.2.2 外连接

1. 左外连接 LEFT JOIN（左表全部保留，右表无匹配补NULL）

SELECT*FROMdept dLEFTJOINemp eONd.deptno=e.deptno;

2. 右外连接 RIGHT JOIN（右表全部保留，左表无匹配补NULL）

SELECT*FROMdept dRIGHTJOINemp eONd.deptno=e.deptno;

MySQL不支持FULL OUTER JOIN全外连接；需求可通过LEFT JOIN UNION RIGHT JOIN实现。

7.3 子查询（查询嵌套SELECT）

7.3.1 WHERE后作为条件

1. 单行单列（= > < >= <= !=）

-- 查询工资高于平均工资的员工SELECT*FROMempWHEREsal>(SELECTAVG(sal)FROMemp);

2. 多行单列（IN / ANY / ALL）
   | 表达式 | 说明 |
   |--------|------|
   |IN/= ANY| 匹配子查询集合中任意一个值 |
   |> ANY| 大于集合最小值 |
   |< ANY| 小于集合最大值 |
   |> ALL| 大于集合全部值（大于最大值） |
   |< ALL| 小于集合全部值（小于最小值） |
   |NOT IN/!= ALL| 不在集合内 |
   ⚠️ 重点：子查询结果包含NULL时，NOT IN查询结果为空，极易踩坑。

3. 单行多列，括号包裹多字段匹配

SELECT*FROMempWHERE(deptno,job)IN(SELECTdeptno,jobFROMempWHEREempno=1001);

7.3.2 FROM后作为临时表（多行多列子查询）

-- 先分组统计，再外层过滤SELECTt.deptno,t.cntFROM(SELECTdeptno,COUNT(*)cntFROMempGROUPBYdeptno)tWHEREt.cnt>2;

7.3.3 SELECT后标量子查询（仅支持单行单列）

-- 查询部门信息+部门下员工总数SELECTd.*,(SELECTCOUNT(*)FROMemp eWHEREe.deptno=d.deptno)emp_countFROMdept d;