MySQL基础知识一、SQL语句分类1.1 客户登录操作1.2 SQL语句四大分类1.2.1 DDLData Definition Language数据定义语言1.2.2 DMLData Manipulation Language数据操作语言1.2.3 DCLData Control Language数据控制语言1.2.4 DQLData Query Language数据查询语言1.3 常用字段类型选型规范二、DDL 数据定义语言2.1 数据库操作2.2 数据表操作2.2.1 创建表2.2.2 查看操作2.2.3 删除表2.2.4 清空表 TRUNCATE2.2.5 修改表 ALTER TABLE三、DML 数据操作语言3.1 插入 INSERT3.2 更新 UPDATEWHERE常用运算符3.3 删除 DELETE四、DCL 数据控制语言4.1 创建用户4.2 授权 GRANT4.3 回收权限 REVOKE4.4 查看用户权限4.5 删除用户五、DQL 数据查询语言5.0 测试表初始化5.1 基础查询5.1.1 字段控制5.1.2 条件 WHERE5.1.3 模糊匹配 LIKE5.2 排序 ORDER BY5.3 聚合函数纵向统计忽略NULL5.4 分组查询 GROUP BY / HAVING5.5 LIMIT 分页限制六、字段约束6.1 主键 PRIMARY KEY6.2 非空 NOT NULL6.3 唯一 UNIQUE6.4 外键 FOREIGN KEYInnoDB引擎支持七、多表查询7.1 合并结果集 UNION / UNION ALL7.2 连接查询 JOIN7.2.1 内连接 INNER JOIN只返回两边匹配到的数据7.2.2 外连接7.3 子查询查询嵌套SELECT7.3.1 WHERE后作为条件7.3.2 FROM后作为临时表多行多列子查询7.3.3 SELECT后标量子查询仅支持单行单列一、SQL语句分类1.1 客户登录操作登录服务器mysql -uroot -p123 -hlocalhost-u指定登录用户名 -p指定登录密码密码紧跟p无空格也可只写-p回车后交互式输入密码更安全 -h指定数据库服务IP/主机名本地可省略-hlocalhost -P大写P指定端口默认3306退出服务器exit/quit/\q1.2 SQL语句四大分类1.2.1 DDLData Definition Language数据定义语言作用创建、删除、修改数据库、表、字段、索引等库表结构关键字CREATE、DROP、ALTER、TRUNCATE1.2.2 DMLData Manipulation Language数据操作语言作用增删改表中的行数据记录关键字INSERT、UPDATE、DELETE1.2.3 DCLData Control Language数据控制语言作用管理用户、分配/回收权限、控制数据库访问安全关键字CREATE USER、DROP USER、GRANT、REVOKE、FLUSH PRIVILEGES1.2.4 DQLData Query Language数据查询语言作用查询表数据业务最常用关键字SELECT1.3 常用字段类型选型规范唯一ID、32位UUIDCHAR(32)定长查询更快名称、地址、简介、短文本VARCHAR(50/100/200)变长节省空间长文本、文章详情、富文本TEXT布尔开关状态0关闭/1开启TINYINT(1)普通数字、次数、数量INT雪花ID、超大自增编号BIGINT金额、价格精确小数DECIMAL(总长度,小数位数)例DECIMAL(10,2)生日、仅年月日DATE创建时间、下单完整时间年月日时分秒DATETIME修改自动更新时间戳TIMESTAMP受时区影响范围更小二、DDL 数据定义语言2.1 数据库操作查看所有数据库SHOWDATABASES;-- 等价SHOWSCHEMAS;切换指定数据库USE数据库名;创建数据库推荐utf8mb4完整支持emojiCREATEDATABASE[IFNOTEXISTS]bookstoreDEFAULTCHARACTERSETutf8mb4DEFAULTCOLLATEutf8mb4_unicode_ci;IF NOT EXISTS不存在才创建避免库已存在报错删除数据库DROPDATABASE[IFEXISTS]bookstore;修改数据库字符集ALTERDATABASEbookstoreCHARACTERSETutf8mb4;2.2 数据表操作2.2.1 创建表CREATETABLE[IFNOTEXISTS]表名(字段名 类型[约束],字段名 类型[约束],...);2.2.2 查看操作SHOWTABLES;-- 查看当前库所有表SHOWCREATETABLE表名;-- 查看建表完整语句含引擎、字符集、注释DESC表名;-- 简易查看表结构DESCRIBE表名;-- 等价DESC2.2.3 删除表DROPTABLE[IFEXISTS]表名;2.2.4 清空表 TRUNCATETRUNCATETABLE表名;特性清空全部数据自增主键重置从1开始表结构、索引、约束、字符集、引擎全部保留不记录日志速度远快于DELETE无法事务回滚外键关联场景下需先解除外键依赖才能执行2.2.5 修改表 ALTER TABLE添加字段支持批量ALTERTABLE表名ADD(字段1类型 约束,字段2类型 约束);-- 单个字段ALTERTABLE表名ADDageINT;修改字段类型/约束不改字段名MODIFYALTERTABLE表名MODIFYageTINYINTNOTNULL;注意字段已有数据时类型缩小可能丢失数据、报错修改字段名类型CHANGE功能包含MODIFYALTERTABLE表名 CHANGE old_name new_nameVARCHAR(50);删除字段ALTERTABLE表名DROPCOLUMNage;-- 简写ALTERTABLE表名DROPage;修改表名ALTERTABLE旧表名RENAMETO新表名;-- 简写ALTERTABLE旧表名RENAME新表名;三、DML 数据操作语言3.1 插入 INSERT指定字段插入推荐扩展性强INSERTINTO表名(列1,列2,...)VALUES(值1,值2,...);未指定的字段若无默认值会填充NULL值顺序、数量必须和字段一一对应。全字段插入需匹配建表字段全部顺序INSERTINTO表名VALUES(值1,值2,值3);批量插入高效INSERTINTO表名(name,age)VALUES(张三,18),(李四,20);3.2 更新 UPDATEUPDATE表名SET列1值1,列2值2[WHERE条件];⚠️ 重要不加WHERE会全表更新线上禁止WHERE常用运算符、!、、、、、区间BETWEEN A AND B闭区间包含两端多值匹配IN(值1,值2)空值判断IS NULL/IS NOT NULL逻辑AND、OR、NOT示例UPDATEpersonSETgender男,ageage1WHEREsid1;WHEREageBETWEEN18AND80;WHEREnameIN(张三,李四);WHEREphoneISNULL;3.3 删除 DELETEDELETEFROM表名[WHERE条件];⚠️ 不加WHERE清空整张表可回滚事务内自增主键不会重置。四、DCL 数据控制语言4.1 创建用户-- 仅localhost本地登录CREATEUSERuser01localhostIDENTIFIEDBY123456;-- 任意IP远程登录 %代表通配CREATEUSERuser01%IDENTIFIEDBY123456;4.2 授权 GRANT-- 指定库指定权限GRANTSELECT,INSERT,UPDATE,DELETE,CREATE,ALTER,DROPONbookstore.*TOuser01%;-- 某库全部权限GRANTALLPRIVILEGESONbookstore.*TOuser01%;-- 所有库所有权限超级权限GRANTALLON*.*TOroot%;授权后刷新权限生效FLUSHPRIVILEGES;4.3 回收权限 REVOKEREVOKECREATE,ALTER,DROPONbookstore.*FROMuser01localhost;4.4 查看用户权限SHOWGRANTSFORuser01localhost;4.5 删除用户DROPUSERIFEXISTSuser01%;五、DQL 数据查询语言5.0 测试表初始化DROPDATABASEIFEXISTSexam;CREATEDATABASEexamDEFAULTCHARSETutf8mb4;USEexam;-- 部门表CREATETABLEdept(deptnoINTPRIMARYKEYCOMMENT部门编号,dnameVARCHAR(50)COMMENT部门名称,locVARCHAR(50)COMMENT部门地点)ENGINEInnoDBDEFAULTCHARSETutf8mb4;-- 雇员表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))ENGINEInnoDBDEFAULTCHARSETutf8mb4;5.1 基础查询5.1.1 字段控制查询全部列SELECT*FROMemp;查询指定列推荐减少IOSELECTempno,ename,salFROMemp;结果去重 DISTINCTSELECTDISTINCTjobFROMemp;注GROUP BY分组是聚合用途不要用来单纯去重MySQL独有写法不通用。列运算、函数、别名数值运算SELECTsal*1.2ASnew_salFROMemp;字符串拼接CONCAT()SELECTCONCAT(底薪,sal)ASsalaryFROMemp;NULL值替换IFNULL(字段,默认值)-- 奖金为NULL按0计算SELECTIFNULL(comm,0)salAStotalFROMemp;别名AS可省略SELECTename nameFROMemp;5.1.2 条件 WHERESELECTempno,ename,salFROMempWHEREsal3000ANDcommISNOTNULL;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 聚合函数纵向统计忽略NULLCOUNT()、MAX()、MIN()、SUM()、AVG()COUNT(*)-- 统计所有行数包含NULLCOUNT(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 / HAVINGGROUP BY按字段分组聚合统计每组数据-- 每个部门人数SELECTdeptno,COUNT(*)emp_countFROMempGROUPBYdeptno;-- 每个岗位最高薪资SELECTjob,MAX(sal)max_salFROMempGROUPBYjob;HAVING分组后过滤结果WHERE过滤原始行HAVING过滤分组后聚合结果-- 只查询人数大于3的部门SELECTdeptno,COUNT(*)cntFROMempGROUPBYdeptnoHAVINGcnt3;区分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特性唯一 非空一张表只能一个主键可被外键关联建表时指定单列主键CREATETABLEstu(sidINTPRIMARYKEY,snameVARCHAR(20));末尾统一声明主键适合复合主键CREATETABLEstu(sidINT,snameVARCHAR(20),PRIMARYKEY(sid));后期添加/删除主键ALTERTABLEstuADDPRIMARYKEY(sid);ALTERTABLEstuDROPPRIMARYKEY;自增主键 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字段值全局唯一允许存一个NULLNULL不参与唯一性校验CREATETABLEstu(sidINTPRIMARYKEYAUTO_INCREMENT,phoneVARCHAR(11)UNIQUE);6.4 外键 FOREIGN KEYInnoDB引擎支持规则外键字段类型必须和引用主键完全一致外键允许重复、允许NULL一张表可多个外键语法CONSTRAINT 约束名 FOREIGN KEY(外键字段) REFERENCES 主表(主键)建表时创建外键CREATETABLEemp(empnoINTPRIMARYKEY,deptnoINT,CONSTRAINTfk_emp_deptFOREIGNKEY(deptno)REFERENCESdept(deptno));建表后添加外键原文ALERT笔误修正为ALTERALTERTABLEempADDCONSTRAINTfk_emp_deptFOREIGNKEY(deptno)REFERENCESdept(deptno);删除外键必须填约束名ALTERTABLEempDROPFOREIGNKEYfk_emp_dept;七、多表查询7.1 合并结果集 UNION / UNION ALL要求多张查询结果列数量相同、对应列类型兼容UNION自动去重性能差UNION ALL直接拼接不去重性能更高推荐-- 合并两个部门员工不去重SELECT*FROMempWHEREdeptno10UNIONALLSELECT*FROMempWHEREdeptno20;7.2 连接查询 JOIN7.2.1 内连接 INNER JOIN只返回两边匹配到的数据隐式内连接逗号写法方言SELECT*FROMdept d,emp eWHEREd.deptnoe.deptno;标准INNER JOIN推荐可读性高SELECT*FROMdept dINNERJOINemp eONd.deptnoe.deptno;7.2.2 外连接左外连接 LEFT JOIN左表全部保留右表无匹配补NULLSELECT*FROMdept dLEFTJOINemp eONd.deptnoe.deptno;右外连接 RIGHT JOIN右表全部保留左表无匹配补NULLSELECT*FROMdept dRIGHTJOINemp eONd.deptnoe.deptno;MySQL不支持FULL OUTER JOIN全外连接需求可通过LEFT JOIN UNION RIGHT JOIN实现。7.3 子查询查询嵌套SELECT7.3.1 WHERE后作为条件单行单列 !-- 查询工资高于平均工资的员工SELECT*FROMempWHEREsal(SELECTAVG(sal)FROMemp);多行单列IN / ANY / ALL| 表达式 | 说明 ||--------|------||IN/ ANY| 匹配子查询集合中任意一个值 || ANY| 大于集合最小值 || ANY| 小于集合最大值 || ALL| 大于集合全部值大于最大值 || ALL| 小于集合全部值小于最小值 ||NOT IN/! ALL| 不在集合内 |⚠️ 重点子查询结果包含NULL时NOT IN查询结果为空极易踩坑。单行多列括号包裹多字段匹配SELECT*FROMempWHERE(deptno,job)IN(SELECTdeptno,jobFROMempWHEREempno1001);7.3.2 FROM后作为临时表多行多列子查询-- 先分组统计再外层过滤SELECTt.deptno,t.cntFROM(SELECTdeptno,COUNT(*)cntFROMempGROUPBYdeptno)tWHEREt.cnt2;7.3.3 SELECT后标量子查询仅支持单行单列-- 查询部门信息部门下员工总数SELECTd.*,(SELECTCOUNT(*)FROMemp eWHEREe.deptnod.deptno)emp_countFROMdept d;