1. RDBMS关系型数据库RDBMS:建立在关系模型的基础上由多张相互连接的二维表组成的数据库。使用表储存数据格式统一便于维护用SQL语言操作标准统一使用方便注释--单行注释 # 单行注释mysql特有 /多行注释/2. sql的分类DDLdata definition language 数据定义语言用来定义数据库对象DML(data manipulation language)数据操作语言用来对数据库表中的数据进行增删改DQL(data query language)数据查询语言 用来查询数据库中的表的记录DCL(data controllanguage)数据控制语言用来创建数据库用户控制数据库访问的权限2.1.DDL 数据定义语言数据库操作查询 show databases;创建 create database if not exists bookstore;删除 drop database if exists 数据库名;使用 use 数据库名;表操作添加字段 alter 表名 add 字段名 类型长度修改数据类型 alter table 表名 modify 字段名 新数据类型长度修改字段名和数据类型 alter table 表名 change 旧字段名 新字段名 类型修改表名 alter table 表名 rename to 新表名删除字段 alter table 表 drop 字段名 truncate(删内容) table 表名这些ALTER TABLE语法在实际开发中很少手敲通常用可视化工具搞定或者交由 DBA 执行。但作为 SQL 基础无疑是重要的尤其是DROP连表带结构都删掉、TRUNCATE清数据表结构还在、DELETE按行删数据可加where条件支持回滚表结构还在的区别一定要分清。#建表语句可参考 CREATE TABLE IF NOT EXISTS user ( id INT PRIMARY KEY AUTO_INCREMENT COMMENT 主键ID, username VARCHAR(50) NOT NULL COMMENT 用户名 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT用户表;2.2.DML 数据库操作语言增加数据INSERT INTO 表名 (列名1, 列名2) VALUES (值1, 值2), (值3, 值4);修改UPDATE 表名 SET 字段名1值1, 字段名2值2 WHERE 条件;删除delete from 表名表数据全部删掉避坑指南1.对于修改数据和删除数据来说where条件就是生命线。如update user set name 张三’若无where,则全部语句都将变成张三同理dalete from user;运行后全表的数据立刻清空2.在实际的开发中很少用delete把数据从硬盘删除因为无法修复。通常用逻辑删除is_deleted字段比如 0 代表正常1 代表已删除用UPDATE去修改这个字段的状态。3.DML 和 DDL 最大的区别是DML增删改操作支持事务回滚。如果误删数据只要没有commit就可以通过rollback回滚撤销操作从而恢复数据4.对于主键冲突id已存在此时用insert会报错。通常会采用以下操作INSERT IGNORE INTO ... 忽略错误INSERT INTO ... ON DUPLICATE KEY UPDATE ... 有则更新无则插入REPLACE INTO .. 替换 其中第二条最常用2.3.DQL数据查询语言基本查询 去重复记录 select distinct 字段列表 from 表名条件查询 where name like ‘_ _’ %模糊匹配的区别。%代表任意个字符_代表单个字符select *from ... where... group by ... having ... order by ... limit...聚合查询 count,max,min,avg,sum SELECT COUNT(*), AVG(salary) FROM 表名;分组查询 group by 用having过滤排序查询 order byasc升序 desc降序分页查询 select 字段 from 表名 limit 起始索引查询记录数mysql方言起始索引 (当前页码 - 1) * 每页条数书写顺序SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT ...2.4.DCL数据控制语言-- 1.1. 创建用户 (注意%代表任意主机localhost代表本机) --CREATE USER 用户名主机名 IDENTIFIED BY 密码; CREATE USER zhangsan% IDENTIFIED BY 123456; -- 1.2. 修改用户密码 ALTER USER 用户名主机名 IDENTIFIED WITH mysql_native_password BY 新密码; -- 1.3. 删除用户 DROP USER 用户名主机名; -- 2.1. 授予权限 (GRANT) -- 语法GRANT 权限列表 ON 数据库名.表名 TO 用户名主机名; -- 示例给 zhangsan 授予 bookstore 库所有表的 查询 和 插入 权限 GRANT SELECT, INSERT ON bookstore.* TO zhangsan%; -- 示例授予所有权限 (ALL) GRANT ALL ON *.* TO zhangsan%; -- 允许 zhangsan 把自己的权限再授权给别人相当于封了个小管理员 GRANT ALL ON *.* TO zhangsan% WITH GRANT OPTION; -- 2. 撤销权限 (REVOKE) -- 语法REVOKE 权限列表 ON 数据库名.表名 FROM 用户名主机名; -- 示例撤销 zhangsan 对 bookstore 库的插入权限 REVOKE INSERT ON bookstore.* FROM zhangsan%; -- 撤销所有权限 REVOKE ALL ON *.* FROM zhangsan%;3. 常用函数3.1.字符串函数CONCAT(s1, s2, ...)字符串拼接有null结果就是nullSUBSTRING(str, start, length)截取字符串如手机后四位UPPER(str)/LOWER(str)转大写 / 转小写TRIM(str)去除字符串首尾空格(常用来清洗用户输入的数据)如把姓和名拼起来SELECT CONCAT(first_name, , last_name) AS full_name FROM employees;3.2.数值函数ROUND(x, y)四舍五入。x是数值y是保留的小数位数CEIL(x)向上取整天花板。(比如分页计算总页数CEIL(总条数 / 每页条数))FLOOR(x)向下取整地板MOD(x, y)取余数(也就是x % y常用来判断奇偶数)ABS(x)绝对值3.3.日期函数NOW()返回当前系统的日期和时间YYYY-MM-DD HH:MM:SSCURDATE()返回当前日期YYYY-MM-DDDATEDIFF(date1, date2)计算两个日期相差的天数。(例如计算用户注册了多少天)DATE_FORMAT(date, format)格式化日期如把日期转成2023-10-01或2023年10月。如查询最近 7 天注册的用户SELECT * FROM users WHERE create_time DATE_SUB(NOW(), INTERVAL 7 DAY);3.4.流程函数IF(value, t, f)如果 value 为真返回 t否则返回 fIFNULL(value1, value2)如果 value1 不为空返回 value1否则返回 value2。(常用于处理空值比如计算奖金时IFNULL(bonus, 0))CASE WHEN[条件] THEN [结果]... ELSE [默认结果] END最强流程控制类似于 Java 里的switch-case或if-else。-- 将数据库里的状态码转成中文 SELECT name,CASE WHEN status 1 THEN 启用 WHEN status 0 THEN 禁用 ELSE 未知 END AS status_desc FROM users;4.约束NOT NULL非空约束DEFAULT默认约束UNIQUE唯一约束(允许null)CHECK检查约束保证满足某一条件PRIMARY KEY主键约束不允许nullFOREIG KEY外键约束CREATE TABLE student ( id INT PRIMARY KEY AUTO_INCREMENT, -- 主键约束自带非空唯一 自增 name VARCHAR(20) NOT NULL, -- 非空约束 gender CHAR(1) DEFAULT 男, -- 默认约束 phone VARCHAR(11) UNIQUE, -- 唯一约束 age INT CHECK (age 0 AND age 150), -- 检查约束 class_id INT, FOREIGN KEY (class_id) REFERENCES class(id) -- 外键约束 );笔者提示刚开始学的时候一定会先学外键保证数据一致性但实际开发大多禁止外键原因如下a.性能每次插入、更新数据时数据库都要去检查外键约束在高并发下极其影响性能。b.死锁外键容易导致表与表之间产生级联锁定引发死锁。c.分库分表在大数据量下数据库要拆分外键根本无法跨库生效。5. 多表查询在实际业务中数据往往分散在多个表中多表查询无疑是重要的。在讲连接之前先理清数据库设计的三大关系一对一用户表与用户详情表。一对多部门与员工最常见通常在多的一方添加外键。多对多学生与课程必须建立中间表存放两张表的主键。5.1.笛卡尔积交叉连接当两张表连接时未指定连接条件就会产生笛卡尔积结果条数 A表条数 × B表条数SELECT * FROM 表A, 表B;5.2 多表查询a.内连接 inner join 查询两表交集部分隐式内连接使用WHERE过滤SELECT*FROM 表A a,表B b WHEREa.id b.a_id;显式内连接使用INNER JOIN.ONSELECT*FROM 表A a INNER JOIN 表B b ON a.id b.a_id;b.外连接OUTER JOIN查询某一张表的全部数据以及两张表交集的数据。|左外连接 (LEFT JOIN)查询左表所有数据以两表交集部分。若右表无对应数据显示NULL。右外连接 (RIGHT JOIN)查询右表所有数据以及两表交集部分。SELECT * FROM 表A a LEFT JOIN 表B b ON a.id b.a_id;c. 自连接 (Self Join)当前表与自身进行连接查询。常用于处理同一张表内的层级关系如员工表与主管表注意自连接必须给表起别名否则数据库无法区分是哪张表。·查询员工及其直属主管的名字员工和主管都在 emp 表里SELECT e.name AS 员工, m.name AS 主管FROM emp eLEFT JOIN emp m ON e.manager_id m.id;d.联合查询 (UNION)把多次查询的结果合并起来形成一个新的查询结果集。要求多次查询的列数必须相同数据类型必须兼容。 union自动去重 union all 不去重将结果拼接示例查询薪资 5000 或者 年龄 50 的员工SELECT * FROM emp WHERE salary 5000 UNION ALLSELECT * FROM emp WHERE age 50;5.3子查询 (嵌套查询)a.标量子查询结果为单个值常配合,,,使用。SELECT * FROM emp WHERE salary (SELECT AVG(salary) FROM emp);b.列子查询结果为一列常配合IN,NOT IN使用。select * from emp where dept_id all(select salary from emp where dept_id (select id from dept where name “财务部”))c.行子查询结果为一行通常用于同时匹配多个字段。如查询薪资和主管ID都与某个特定员工相同的其他员工SELECT * FROM emp WHERE (salary, managerid) (SELECT salary, managerid FROM emp WHERE name 张三);d.表子查询多行多列。常放在FROM后面作为派生表或者配合IN使用。SELECT * FROM emp WHERE (job, salary) IN (SELECT job, salary FROM emp WHERE dept_id IN (1, 2));6. 事务简介把所有操作作为一个整体一起向系统提交或撤销操作同时成功或同时失效6.1事务操作select autocommit; set autocommit 0; --设置为手动提交 --1.查询张三账户余额 select * from account where name zhangsan; --2.将张三账户余额-1000 update account set money -1000 where name zhangsan; --3.将李四账户余额加1000 update account set money 1000 where name lisi; submit; --提交事务 --回滚事务 rollback; select autocommit; set autocommit 1; --设置为自动提交 start transction; --1.查询张三账户余额 select * from account where name zhangsan; --2.将张三账户余额-1000 update account set money -1000 where name zhangsan; --3.将李四账户余额加1000 update account set money 1000 where name lisi; commit; --提交事务 --回滚事务 rollback6.2 事务的四大特性 (ACID)原子性 (Atomicity)事务是不可分割的最小操作单元要么全部成功要么全部失败一致性 (Consistency)事务完成时必须使所有的数据保持状态一致比如转账前后A 和 B 的总金额不变。隔离性 (Isolation)数据库系统提供的隔离机制让事务在不受外部并发操作影响的独立环境下运行。持久性 (Durability)事务一旦提交或回滚它对数据库中数据的改变就是永久的哪怕数据库宕机重启后数据依然存在因为已经写入了磁盘。6.3 并发事务引发的问题当多个事务并发同时执行时如果没有做好隔离会引发以下问题脏读(Dirty Read)一个事务读到了另一个事务还没有提交的数据。如果那个事务回滚了读到的就是脏数据。不可重复读(Non-repeatable Read)一个事务先后读取同一条记录但两次读取的数据不同。因为中间另一个事务修改并提交了这条数据。幻读(Phantom Read)一个事务按照条件查询数据时没有对应的数据行但在插入数据时发现这行数据已经存在或者再次查询时凭空多出了一些数据像产生了幻觉。(注不可重复读针对单行数据的 UPDATE幻读针对多行范围的 INSERT/DELETE)。6.4 事务隔离级别为了解决并发事务带来的脏读、不可重复读和幻读问题数据库提供了四种隔离级别。隔离级别越高数据越安全但并发性能越低。由低到高依次为读未提交(Read uncommitted)最低级别。一个事务可以读到另一个事务未提交的数据。无法解决任何并发问题脏读、不可重复读、幻读都可能发生。读已提交(Read committed)只能读到已经提交的数据。能解决“脏读”但无法解决“不可重复读”和“幻读”。(这是 Oracle 数据库的默认级别)。可重复读(Repeatable Read)保证在一个事务内多次读取同一条记录的结果是一致的。能解决“脏读”和“不可重复读”。(这是 MySQL 数据库的默认级别)。串行化(Serializable)最高级别。强制事务串行执行加锁排队完全隔离。能解决所有并发问题但并发性能极差通常只用于对数据一致性要求极高的极端场景。文章到这里就结束了。以上就是关于 SQL 数据库核心知识体系的全部梳理。从基础的增删改查到复杂的连接查询再到保证数据安全的事务控制掌握这些你已经具备了扎实的 SQL 功底。建议大家多在自己电脑上建几张表把文中的 SQL 语句亲手跑一遍遇到报错才是真正成长的开始。祝大家在面试和工作中都能游刃有余我们一起加油啊
