1. DDL定义数据库和表结构DDL 全称是Data Definition Language数据定义语言。它不是用来操作具体数据的而是用来操作数据库、表、字段结构的。比如SHOWDATABASES;查看当前 MySQL 里有哪些数据库。CREATEDATABASE数据库名;创建一个数据库。USE数据库名;切换到某个数据库后续操作都在这个库里进行。DROPDATABASE数据库名;删除数据库这个比较危险会把库里的表和数据一起删掉。DDL 操作表表是数据库里真正存储数据的地方。SHOWTABLES;查看当前数据库中有哪些表。CREATETABLE表名(字段1字段类型,字段2字段类型);创建表。例如CREATETABLEuser(idINT,nameVARCHAR(20),ageINT);意思是创建一张user表里面有id、name、age三个字段。DESC表名;查看表结构。SHOWCREATETABLE表名;查看创建这张表时完整的 SQL 语句。ALTERTABLE表名ADD/MODIFY/CHANGE/DROP/RENAMETO...修改表结构比如添加字段、修改字段类型、改字段名、删除字段、改表名。DROPTABLE表名;删除表。可以理解为DDL 管的是“库和表长什么样”。2. DML操作表里的数据DML 全称是Data Manipulation Language数据操作语言。它是用来操作表中具体数据的包括增加、修改、删除。添加数据INSERTINSERTINTO表名(字段1,字段2)VALUES(值1,值2);例如INSERTINTOuser(id,name,age)VALUES(1,Tom,18);意思是往user表里插入一条数据。也可以一次插入多条INSERTINTOuser(id,name,age)VALUES(1,Tom,18),(2,Jack,20);修改数据UPDATEUPDATE表名SET字段1值1,字段2值2WHERE条件;例如UPDATEuserSETage19WHEREid1;意思是把id 1的用户年龄改成 19。这里一定要注意WHERE。如果你不写UPDATEuserSETage19;那就是把整张表所有人的年龄都改成 19。删除数据DELETEDELETEFROM表名WHERE条件;例如DELETEFROMuserWHEREid1;删除id 1的用户。同样WHERE很重要。DELETEFROMuser;会删除整张表的数据但表结构还在。3. DCL用户和权限控制DCL 全称是Data Control Language数据控制语言。它主要用于 MySQL 的用户管理和权限管理。用户管理CREATEUSER用户名主机名IDENTIFIEDBY密码;创建用户。例如CREATEUSERtestlocalhostIDENTIFIEDBY123456;表示创建一个只能从本机登录的用户test。后面的主机名表示这个用户可以从哪里连接 MySQL。常见写法testlocalhost表示只能本机连接。test%表示任意主机都可以连接。修改密码ALTERUSER用户名主机名IDENTIFIEDWITHmysql_native_passwordBY密码;删除用户DROPUSER用户名主机名;权限控制GRANT权限列表ON数据库名.表名TO用户名主机名;给用户授权。例如GRANTSELECT,INSERTONmydb.userTOtestlocalhost;表示给test用户对mydb数据库中的user表授予查询和插入权限。REVOKE权限列表ON数据库名.表名FROM用户名主机名;收回权限。可以理解为DCL 管的是“谁能登录谁能操作哪些库表”。4. MySQL 常用函数函数就是 MySQL 提供的一些工具方法可以直接在 SQL 里用。字符串函数比如CONCAT()拼接字符串。SELECTCONCAT(Hello,MySQL);结果是HelloMySQL LOWER() UPPER()转小写、转大写。TRIM()去除前后空格。SUBSTRING()截取字符串。数值函数CEIL()向上取整。SELECTCEIL(1.2);结果是2。FLOOR()向下取整。SELECTFLOOR(1.8);结果是1。MOD()取余数。SELECTMOD(10,3);结果是1。RAND()生成随机数。ROUND()四舍五入。日期函数CURDATE()获取当前日期。CURTIME()获取当前时间。NOW()获取当前日期和时间。YEAR()MONTH()DAY()获取年、月、日。DATE_ADD()日期增加。DATEDIFF()计算两个日期相差多少天。流程函数类似 Java 里的if else。IF(条件,值1,值2)例如SELECTIF(age18,成年,未成年)FROMuser;还有IFNULL(字段,默认值)如果字段是NULL就用默认值替代。CASEWHEN条件THEN结果ELSE结果END更像多分支判断。5. 约束限制字段的数据规则约束就是给表字段加规则防止错误数据进入数据库。NOT NULL非空约束nameVARCHAR(20)NOTNULL表示name字段不能为空。UNIQUE唯一约束phoneVARCHAR(11)UNIQUE表示手机号不能重复。PRIMARY KEY主键约束idINTPRIMARYKEY主键特点不能为NULL不能重复一张表一般只有一个主键通常会配合自增idINTPRIMARYKEYAUTO_INCREMENT表示id自动递增不需要手动指定。DEFAULT默认约束statusINTDEFAULT1如果插入数据时没有指定status默认值就是1。CHECK检查约束ageINTCHECK(age0ANDage120)表示年龄必须在合理范围内。FOREIGN KEY外键约束外键用于建立两张表之间的关系。比如有两张表学生表student(id, name, class_id)班级表class(id, name)其中student.class_id对应class.id。那么student.class_id就可以作为外键关联class.id。作用是保证数据一致性。比如班级表里没有id 10的班级那么学生表里就不能出现class_id 10的学生。6. 多表关系数据库中表和表之间常见有三种关系。一对多最常见。例如部门和员工。一个部门可以有多个员工但一个员工只属于一个部门。dept部门表 emp员工表通常是在“多”的一方加外键。也就是员工表里加dept_id关联部门表的主键id。多对多例如学生和课程。一个学生可以选多门课一门课也可以被多个学生选择。这时候不能只靠一个外键解决需要一张中间表。student 学生表 course 课程表 student_course 学生课程关系表中间表里一般有两个外键student_id course_id分别关联学生表和课程表。一对一例如用户表和用户详情表。一个用户只有一份详情一份详情也只属于一个用户。实现方式通常是在任意一方加外键并且给这个外键加UNIQUE唯一约束。7. 多表查询多表查询就是从多张表里一起查数据。隐式内连接SELECT...FROM表A,表BWHERE条件;例如SELECTemp.name,dept.nameFROMemp,deptWHEREemp.dept_iddept.id;意思是查询员工对应的部门名称。显式内连接SELECT...FROM表AINNERJOIN表BON条件;例如SELECTemp.name,dept.nameFROMempINNERJOINdeptONemp.dept_iddept.id;内连接查的是两张表中能匹配上的数据。如果员工没有部门或者部门没有员工就不会显示。左外连接SELECT...FROM表ALEFTJOIN表BON条件;左连接的特点保留左表所有数据右表匹配不上就显示 NULL。例如SELECTemp.name,dept.nameFROMempLEFTJOINdeptONemp.dept_iddept.id;即使某个员工没有部门也会被查出来只是部门信息为NULL。右外连接SELECT...FROM表ARIGHTJOIN表BON条件;右连接的特点保留右表所有数据左表匹配不上就显示 NULL。自连接自连接就是一张表自己和自己连接。比如员工表里既有员工也有领导emp(id, name, manager_id)manager_id指向的还是emp.id。查询员工和他的领导SELECTe.name 员工名,m.name 领导名FROMemp e,emp mWHEREe.manager_idm.id;这里e和m是同一张表的两个别名。子查询子查询就是 SQL 里面嵌套 SQL。例如SELECT*FROMempWHEREsalary(SELECTAVG(salary)FROMemp);意思是查询工资高于平均工资的员工。常见子查询有标量子查询返回一个值 列子查询返回一列 行子查询返回一行 表子查询返回一张临时表8. 事务事务是 MySQL 里非常重要的知识点。事务可以理解为一组 SQL 操作要么全部成功要么全部失败。经典例子是转账。A 给 B 转 100 元A 扣100B 加100这两个操作必须一起成功。如果 A 扣钱成功了B 加钱失败了就会出大问题。所以要用事务保证它们是一个整体。事务操作开启事务STARTTRANSACTION;提交事务COMMIT;回滚事务ROLLBACK;例如STARTTRANSACTION;UPDATEaccountSETmoneymoney-100WHEREnameA;UPDATEaccountSETmoneymoney100WHEREnameB;COMMIT;如果中间出错就执行ROLLBACK;9. 事务四大特性 ACID事务有四大特性AAtomicity 原子性 CConsistency 一致性 IIsolation 隔离性 DDurability 持久性原子性 Atomicity事务中的操作要么全部成功要么全部失败。比如转账中扣钱和加钱必须作为一个整体。一致性 Consistency事务执行前后数据都要保持正确状态。比如转账前A 1000 B 1000 总金额 2000转账后A 900 B 1100 总金额 2000总金额没有变化这就是一致性。隔离性 Isolation多个事务并发执行时彼此之间不能随便干扰。比如两个事务同时修改同一条数据数据库要通过锁、MVCC 等机制保证结果正确。持久性 Durability事务一旦提交修改就会永久保存到数据库中。即使数据库崩溃重启后也应该能恢复已经提交的数据。10. 并发事务问题多个事务同时执行时可能出现三个典型问题。脏读一个事务读到了另一个事务还没有提交的数据。例如事务 A 修改了余额但还没提交。事务 B 读到了这个修改后的余额。后来事务 A 回滚了。那事务 B 刚才读到的就是“脏数据”。不可重复读同一个事务中两次读取同一条数据结果不一样。例如事务 A 第一次查余额是 1000。事务 B 修改余额为 800并提交。事务 A 第二次再查发现变成 800。这就是不可重复读。重点是同一行数据被修改了。幻读同一个事务中两次按照相同条件查询结果条数不一样。例如事务 A 查询SELECT*FROMuserWHEREage18;查到 10 条。事务 B 插入了一条age 20的数据并提交。事务 A 再查同样条件发现变成 11 条。这就是幻读。重点是新增或删除导致结果集数量变化。11. 事务隔离级别为了处理这些并发问题MySQL 提供了四种隔离级别。READ UNCOMMITTED读未提交最低隔离级别。一个事务可以读到另一个事务还没提交的数据。可能出现脏读、不可重复读、幻读一般很少用。READ COMMITTED读已提交只能读到别人已经提交的数据。可以解决脏读。但仍然可能出现不可重复读、幻读REPEATABLE READ可重复读MySQL InnoDB 默认隔离级别。可以保证同一个事务中多次读取同一条数据结果一致。可以解决脏读、不可重复读在 MySQL InnoDB 中通过 MVCC 和锁机制也在很大程度上解决了幻读问题。SERIALIZABLE串行化最高隔离级别。事务一个一个排队执行安全性最高但性能最差。可以解决脏读、不可重复读、幻读总结一张图的逻辑你这些内容可以这样记MySQL 基础 ├── DDL操作数据库和表结构 ├── DML操作表中的数据 ├── DCL操作用户和权限 ├── 函数字符串、数值、日期、流程函数 ├── 约束限制字段数据规则 ├── 多表关系一对多、多对多、一对一 ├── 多表查询内连接、外连接、自连接、子查询 └── 事务 ├── 事务操作start transaction / commit / rollback ├── ACID原子性、一致性、隔离性、持久性 ├── 并发问题脏读、不可重复读、幻读 └── 隔离级别读未提交、读已提交、可重复读、串行化你现在可以先重点掌握这几个核心1. DDL建库建表 2. DML增删改 3. SELECT 多表查询 4. 约束尤其是主键和外键 5. 事务和隔离级别其中面试和实际开发最常问的是主键和外键 一对多、多对多关系设计 inner join 和 left join 的区别 事务 ACID 脏读、不可重复读、幻读 MySQL 默认隔离级别这些学明白之后MySQL 基础部分就比较稳了。
【Mysql】基础篇
1. DDL定义数据库和表结构DDL 全称是Data Definition Language数据定义语言。它不是用来操作具体数据的而是用来操作数据库、表、字段结构的。比如SHOWDATABASES;查看当前 MySQL 里有哪些数据库。CREATEDATABASE数据库名;创建一个数据库。USE数据库名;切换到某个数据库后续操作都在这个库里进行。DROPDATABASE数据库名;删除数据库这个比较危险会把库里的表和数据一起删掉。DDL 操作表表是数据库里真正存储数据的地方。SHOWTABLES;查看当前数据库中有哪些表。CREATETABLE表名(字段1字段类型,字段2字段类型);创建表。例如CREATETABLEuser(idINT,nameVARCHAR(20),ageINT);意思是创建一张user表里面有id、name、age三个字段。DESC表名;查看表结构。SHOWCREATETABLE表名;查看创建这张表时完整的 SQL 语句。ALTERTABLE表名ADD/MODIFY/CHANGE/DROP/RENAMETO...修改表结构比如添加字段、修改字段类型、改字段名、删除字段、改表名。DROPTABLE表名;删除表。可以理解为DDL 管的是“库和表长什么样”。2. DML操作表里的数据DML 全称是Data Manipulation Language数据操作语言。它是用来操作表中具体数据的包括增加、修改、删除。添加数据INSERTINSERTINTO表名(字段1,字段2)VALUES(值1,值2);例如INSERTINTOuser(id,name,age)VALUES(1,Tom,18);意思是往user表里插入一条数据。也可以一次插入多条INSERTINTOuser(id,name,age)VALUES(1,Tom,18),(2,Jack,20);修改数据UPDATEUPDATE表名SET字段1值1,字段2值2WHERE条件;例如UPDATEuserSETage19WHEREid1;意思是把id 1的用户年龄改成 19。这里一定要注意WHERE。如果你不写UPDATEuserSETage19;那就是把整张表所有人的年龄都改成 19。删除数据DELETEDELETEFROM表名WHERE条件;例如DELETEFROMuserWHEREid1;删除id 1的用户。同样WHERE很重要。DELETEFROMuser;会删除整张表的数据但表结构还在。3. DCL用户和权限控制DCL 全称是Data Control Language数据控制语言。它主要用于 MySQL 的用户管理和权限管理。用户管理CREATEUSER用户名主机名IDENTIFIEDBY密码;创建用户。例如CREATEUSERtestlocalhostIDENTIFIEDBY123456;表示创建一个只能从本机登录的用户test。后面的主机名表示这个用户可以从哪里连接 MySQL。常见写法testlocalhost表示只能本机连接。test%表示任意主机都可以连接。修改密码ALTERUSER用户名主机名IDENTIFIEDWITHmysql_native_passwordBY密码;删除用户DROPUSER用户名主机名;权限控制GRANT权限列表ON数据库名.表名TO用户名主机名;给用户授权。例如GRANTSELECT,INSERTONmydb.userTOtestlocalhost;表示给test用户对mydb数据库中的user表授予查询和插入权限。REVOKE权限列表ON数据库名.表名FROM用户名主机名;收回权限。可以理解为DCL 管的是“谁能登录谁能操作哪些库表”。4. MySQL 常用函数函数就是 MySQL 提供的一些工具方法可以直接在 SQL 里用。字符串函数比如CONCAT()拼接字符串。SELECTCONCAT(Hello,MySQL);结果是HelloMySQL LOWER() UPPER()转小写、转大写。TRIM()去除前后空格。SUBSTRING()截取字符串。数值函数CEIL()向上取整。SELECTCEIL(1.2);结果是2。FLOOR()向下取整。SELECTFLOOR(1.8);结果是1。MOD()取余数。SELECTMOD(10,3);结果是1。RAND()生成随机数。ROUND()四舍五入。日期函数CURDATE()获取当前日期。CURTIME()获取当前时间。NOW()获取当前日期和时间。YEAR()MONTH()DAY()获取年、月、日。DATE_ADD()日期增加。DATEDIFF()计算两个日期相差多少天。流程函数类似 Java 里的if else。IF(条件,值1,值2)例如SELECTIF(age18,成年,未成年)FROMuser;还有IFNULL(字段,默认值)如果字段是NULL就用默认值替代。CASEWHEN条件THEN结果ELSE结果END更像多分支判断。5. 约束限制字段的数据规则约束就是给表字段加规则防止错误数据进入数据库。NOT NULL非空约束nameVARCHAR(20)NOTNULL表示name字段不能为空。UNIQUE唯一约束phoneVARCHAR(11)UNIQUE表示手机号不能重复。PRIMARY KEY主键约束idINTPRIMARYKEY主键特点不能为NULL不能重复一张表一般只有一个主键通常会配合自增idINTPRIMARYKEYAUTO_INCREMENT表示id自动递增不需要手动指定。DEFAULT默认约束statusINTDEFAULT1如果插入数据时没有指定status默认值就是1。CHECK检查约束ageINTCHECK(age0ANDage120)表示年龄必须在合理范围内。FOREIGN KEY外键约束外键用于建立两张表之间的关系。比如有两张表学生表student(id, name, class_id)班级表class(id, name)其中student.class_id对应class.id。那么student.class_id就可以作为外键关联class.id。作用是保证数据一致性。比如班级表里没有id 10的班级那么学生表里就不能出现class_id 10的学生。6. 多表关系数据库中表和表之间常见有三种关系。一对多最常见。例如部门和员工。一个部门可以有多个员工但一个员工只属于一个部门。dept部门表 emp员工表通常是在“多”的一方加外键。也就是员工表里加dept_id关联部门表的主键id。多对多例如学生和课程。一个学生可以选多门课一门课也可以被多个学生选择。这时候不能只靠一个外键解决需要一张中间表。student 学生表 course 课程表 student_course 学生课程关系表中间表里一般有两个外键student_id course_id分别关联学生表和课程表。一对一例如用户表和用户详情表。一个用户只有一份详情一份详情也只属于一个用户。实现方式通常是在任意一方加外键并且给这个外键加UNIQUE唯一约束。7. 多表查询多表查询就是从多张表里一起查数据。隐式内连接SELECT...FROM表A,表BWHERE条件;例如SELECTemp.name,dept.nameFROMemp,deptWHEREemp.dept_iddept.id;意思是查询员工对应的部门名称。显式内连接SELECT...FROM表AINNERJOIN表BON条件;例如SELECTemp.name,dept.nameFROMempINNERJOINdeptONemp.dept_iddept.id;内连接查的是两张表中能匹配上的数据。如果员工没有部门或者部门没有员工就不会显示。左外连接SELECT...FROM表ALEFTJOIN表BON条件;左连接的特点保留左表所有数据右表匹配不上就显示 NULL。例如SELECTemp.name,dept.nameFROMempLEFTJOINdeptONemp.dept_iddept.id;即使某个员工没有部门也会被查出来只是部门信息为NULL。右外连接SELECT...FROM表ARIGHTJOIN表BON条件;右连接的特点保留右表所有数据左表匹配不上就显示 NULL。自连接自连接就是一张表自己和自己连接。比如员工表里既有员工也有领导emp(id, name, manager_id)manager_id指向的还是emp.id。查询员工和他的领导SELECTe.name 员工名,m.name 领导名FROMemp e,emp mWHEREe.manager_idm.id;这里e和m是同一张表的两个别名。子查询子查询就是 SQL 里面嵌套 SQL。例如SELECT*FROMempWHEREsalary(SELECTAVG(salary)FROMemp);意思是查询工资高于平均工资的员工。常见子查询有标量子查询返回一个值 列子查询返回一列 行子查询返回一行 表子查询返回一张临时表8. 事务事务是 MySQL 里非常重要的知识点。事务可以理解为一组 SQL 操作要么全部成功要么全部失败。经典例子是转账。A 给 B 转 100 元A 扣100B 加100这两个操作必须一起成功。如果 A 扣钱成功了B 加钱失败了就会出大问题。所以要用事务保证它们是一个整体。事务操作开启事务STARTTRANSACTION;提交事务COMMIT;回滚事务ROLLBACK;例如STARTTRANSACTION;UPDATEaccountSETmoneymoney-100WHEREnameA;UPDATEaccountSETmoneymoney100WHEREnameB;COMMIT;如果中间出错就执行ROLLBACK;9. 事务四大特性 ACID事务有四大特性AAtomicity 原子性 CConsistency 一致性 IIsolation 隔离性 DDurability 持久性原子性 Atomicity事务中的操作要么全部成功要么全部失败。比如转账中扣钱和加钱必须作为一个整体。一致性 Consistency事务执行前后数据都要保持正确状态。比如转账前A 1000 B 1000 总金额 2000转账后A 900 B 1100 总金额 2000总金额没有变化这就是一致性。隔离性 Isolation多个事务并发执行时彼此之间不能随便干扰。比如两个事务同时修改同一条数据数据库要通过锁、MVCC 等机制保证结果正确。持久性 Durability事务一旦提交修改就会永久保存到数据库中。即使数据库崩溃重启后也应该能恢复已经提交的数据。10. 并发事务问题多个事务同时执行时可能出现三个典型问题。脏读一个事务读到了另一个事务还没有提交的数据。例如事务 A 修改了余额但还没提交。事务 B 读到了这个修改后的余额。后来事务 A 回滚了。那事务 B 刚才读到的就是“脏数据”。不可重复读同一个事务中两次读取同一条数据结果不一样。例如事务 A 第一次查余额是 1000。事务 B 修改余额为 800并提交。事务 A 第二次再查发现变成 800。这就是不可重复读。重点是同一行数据被修改了。幻读同一个事务中两次按照相同条件查询结果条数不一样。例如事务 A 查询SELECT*FROMuserWHEREage18;查到 10 条。事务 B 插入了一条age 20的数据并提交。事务 A 再查同样条件发现变成 11 条。这就是幻读。重点是新增或删除导致结果集数量变化。11. 事务隔离级别为了处理这些并发问题MySQL 提供了四种隔离级别。READ UNCOMMITTED读未提交最低隔离级别。一个事务可以读到另一个事务还没提交的数据。可能出现脏读、不可重复读、幻读一般很少用。READ COMMITTED读已提交只能读到别人已经提交的数据。可以解决脏读。但仍然可能出现不可重复读、幻读REPEATABLE READ可重复读MySQL InnoDB 默认隔离级别。可以保证同一个事务中多次读取同一条数据结果一致。可以解决脏读、不可重复读在 MySQL InnoDB 中通过 MVCC 和锁机制也在很大程度上解决了幻读问题。SERIALIZABLE串行化最高隔离级别。事务一个一个排队执行安全性最高但性能最差。可以解决脏读、不可重复读、幻读总结一张图的逻辑你这些内容可以这样记MySQL 基础 ├── DDL操作数据库和表结构 ├── DML操作表中的数据 ├── DCL操作用户和权限 ├── 函数字符串、数值、日期、流程函数 ├── 约束限制字段数据规则 ├── 多表关系一对多、多对多、一对一 ├── 多表查询内连接、外连接、自连接、子查询 └── 事务 ├── 事务操作start transaction / commit / rollback ├── ACID原子性、一致性、隔离性、持久性 ├── 并发问题脏读、不可重复读、幻读 └── 隔离级别读未提交、读已提交、可重复读、串行化你现在可以先重点掌握这几个核心1. DDL建库建表 2. DML增删改 3. SELECT 多表查询 4. 约束尤其是主键和外键 5. 事务和隔离级别其中面试和实际开发最常问的是主键和外键 一对多、多对多关系设计 inner join 和 left join 的区别 事务 ACID 脏读、不可重复读、幻读 MySQL 默认隔离级别这些学明白之后MySQL 基础部分就比较稳了。
