MySql基础篇
1、数据模型概述
2、SQL
2.1、SQL通用语法
-
SQL语句可以单行或者多行书写，以分号结尾
-
SQL可以使用空格/缩进来增强语句的可读性
-
MySQL数据库的SQL语句不区分大小写，关键字建议使用大写
-
注释：
单行注释：-- 注释内容 或者 #注释内容 多行注释：/*注释内容*/
2.2、SQL分类
2.2.1、DDL
-
定义：数据定义语言，用来对数据库对象进行定义（数据库、表、字段）
-
DDL数据库操作：
1、查询
查询所用数据库
SHOW DATAVASES;查询当前数据库：
SELECT DATABASE();2、创建
CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集][COLLATE 排序规则];3、删除
DROP DATABASE [IF EXISTS] 数据库名;4、使用
USE 数据库名 -
DDL表操作
1、查询
查询当前数据库中所用表
SHOW TABLES;查询表结构
DESC 表名查询指定表的建表语言
SHOW CREATE TABLE 表名2、创建
CREATE TABLE 表名{字段1 字段1类型[COMMENT 字段1注释],字段2 字段2类型[COMMENT 字段2注释],字段3 字段3类型[COMMENT 字段3注释],字段4 字段4类型[COMMENT 字段4注释],...字段n 字段n类型[COMMENT 字段n注释] }[COMMENT 表注释];3、修改
添加字段
ALTER TABLE 表名 ADD 字段名 类型(长度)[COMMENT 注释][约束];修改数据类型
ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);修改字段名和字段类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度)[COMMENT 注释][约束];修改表名
ALTER TABLE 表名 RENAME TO 新表名;4、删除
删除字段
ALTER TABLE 表名 DROP 字段名;删除表
DROP TABLE [IF EXISTS] 表名;删除指定表并重新创建该表
TRUNCATE TABLE 表名; -
数据类型
1、数值类型
2、字符串类型
3、日期时间类型
2.2.2、DML
-
定义：数据操作语言，用来对数据库表中的数据进行增删改
-
添加数据（INSERT）
1、给指定字段添加数据
INSERT INTO 表名(字段1, 字段2,...) VALUES(值1, 值2,...);2、给全部字段添加数据
INSERT INTO 表名 VALUES(值1, 值2,...);3、批量添加数据
INSERT INTO 表名(字段1, 字段2,...) VALUES(值1, 值2,...), (值1, 值2,...),...; INSERT INTO 表名 VALUES(值1, 值2,...), (值1, 值2,...),...;注意：
插入数据时，指定的字段顺序要与值的顺序一一对相应
字符串和日期型数据应该包含在引号中
插入的数据大小应该在字段的规定范围内
-
修改数据（UPDATE）
UPDATE 表名 SET 字段1 = 值1, 字段2 = 值2,...[WHERE 条件];注意：
修改语句的条件可以有也可以没有，如果没有条件，则会修改整张表的所有数据。
-
删除数据（DELETE）
DELETE FROM 表名 [WHERE 条件];注意：
DELETE语句的条件可以有也可以没有，如果没有条件，则会删除整张表的所有数据
DELETE语句不能删除某个字段的值（可使用UPDATE）
2.2.3、DQL
-
定义：数据查询语言，用来对数据库中表中的数据进行查询操作
-
语法：
SELECT 字段列表 FROM表名列表 WHERE条件列表 GROUP BY分组字段列表 HAVING分组后条件列表 ORDER BY排序字段列表 LIMIT分页参数 -
基本查询
1、查询多个字段
SELECT 字段1, 字段2, 字段3,... FROM 表名;SELECT * FROM 表名;2、设置别名
SELECT 字段1[AS 别名1], 字段2[AS 别名2],...FROM 表名;3、去除重复记录
SELECT DISTINCT 字段列表 FROM 表名; -
条件查询（WHERE）
1、语法：
SELECT 字段列表 FROM 表名 WHERE 条件列表;2、条件
比较运算符 功能 > 大于 >= 大于等于 < 小于 <= 小于等于 = 等于 <> 或者 != 不等于 BETWEEN…AND… 在某个范围之内（包含最小值，也包含最大值） IN(…) 在in之后的列表的值，多选一 LIKE 占位符 模糊匹配（_匹配单个字符，%匹配任意字符） IS NULL 时NULL 逻辑运算符 功能 AND 或者 && 并且（多个条件同时成立） OR 或者 || 或者（多个条件任意一个成立） NOT 或者 ！ 非，不是 -
聚合查询（count、max、min、avg、sum）
1、常见聚合函数
函数 功能 count 统计数量 max 最大值 min 最小值 avg 平均值 sum 求和 2、语法
SELECT 聚合函数(字段列表) FROM 表名; -
分组查询（GROUP BY）
1、语法
SELECT 字段列表 FROM 表名 [WHERE 条件列表] GROUP BY 分组字段名 [HAVING 分组后过滤条件];2、where与having区别
执行的时机不同：where是分组前进行过滤，不满足where条件，不参与分组，而having是分组之后对结果进行过滤。
判断条件不同：where不能对聚合函数进行判断，而having可以。
注意：分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段没有意义。
-
排序查询（ORDER BY）
1、语法
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1, 字段2 排序方式2;2、排序方式
ASC：升序排序（默认值）
DESC：降序排序
注意：如果是多个字段进行排序，当地一个字段相同时，才会根据第二个字段进行排序
-
分页查询（LIMIT）
1、语法
SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数;注意：起始索引从零开始，起始索引 =（查询页码 - 1）* 每页显示记录数；分页查询是数据库的方言，不同的数据库有不同的实现，MYSQL中的是LIMIT；如果查询的是第一页数据，其实索引可以省略。
-
执行顺序
1、FROM
2、WHERE
3、GROUP BY, HAVING
4、SELECT
5、ORDER BY
6、LIMIT
2.2.4、DCL（不作为重点掌握）
-
定义：数据控制语言，用来创建数据库用户，控制数据库的访问权
-
DCL-管理用户
1、查询用户
USE mysql; SELECT * FROM user;2、创建用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码'; #如果想让创建的用户在任意主机上都能访问到该数据库则将”主机名“改为”%“3、修改用密码
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';4、删除用户
DROP USER '用户名'@'主机名'; -
DCL-权限控制
MYSQL中常用的几种权限
权限 说明 ALL、ALL PRIVILEGES 所有权限 SELECT 查询数据 INSERT 插入数据 UPDATE 修改数据 DELETE 删除数据 ALTER 修改表 DROP 删除数据库、表、视图 CREATE 创建数据库、表 1、查询权限
SHOW GRANTS FOR '用户名'@'主机名';2、授予权限
GRANTS 权限列表 ON 数据库名.表名 TO '用户名'@'表名';3、撤销权限
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';注意：多个权限之间，使用都好分隔，授权时数据库名和表名可以使用*进行通配，代表所有
3、函数
3.1、概念
函数是指一段可以被另一段程序直接调用的程序或者代码
3.2、常用函数
3.2.1、字符串函数
使用示例
SELECT 函数(参数);select concat('Hello', ' World!');select lower('HELLO');select upper('hello');select lpad('1', 5, '0');select rpad('1', 5, '-');select trim(' Hello ');select substring('Hello World!', 1, 5);
测试示例：假如当前有一个表（sm_student），现在要将表中学生的学号补齐为十位，不满十位的用前导零补充
update sm_student set student_sno = lpad(student_sno, 10, '0');
3.2.2、数值函数
测试示例：通过数据库函数，生成一个四位数的随机验证码
--用rand生成一个0-1之间的随机数，然后乘上10000进行四舍五入，如果此时还不足四位则在验证码后补’3‘
select rpad(round(rand() * 10000), 4, '3');
3.2.3、日期函数
使用示例
select curdate();select curtime();select now()#当前日期属于那一年
select year(now());select month(now());select date(now());#当前日期向后推80天是什么时候
select date_add(now(), interval 80 day);#运算时是第一个时间减去第二个时间
select datediff('2021-9-11', '2024-6-6');
3.2.4、流程函数
使用示例
select if(true, '正确', '错误') as '判断';select ifnull(null, '为空') as '是否为空';#查询学生姓名和专业（计算机/大数据/软件工程--->计算机系，其他--->其他系）
selectstudent_name,(case student_major when '计算机' then '计算机系' when '大数据' then '计算机系' when '软件工程' then '计算机系'else '其他系' end) as '学生系别'
from sm_student;
4、约束
4.1、概述
-
概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据
-
目的：保证数据库中的数据正确，有效性和完整性
-
分类
约束是作用于表中字段的，可以在创建表/修改表的时候添加约束。
4.2、约束演示
根据需求完成表结构的创建
create table user(id int primary key auto_increment comment '主键',name varchar(10) not null unique comment '姓名',age int check ( age > 0 and age <= 120 ) comment '年龄',status char(1) default '1' comment '状态',gender char(1) comment '性别'
) comment '用户表';
4.3、外键约束
4.3.1、概念
外键用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性
4.3.2、语法
添加外键
CREATE TABLE 表名(字段名 数据类型,...[CONSTRAINT] [外键名称] FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名)
);ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名);
删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
4.3.3、外键删除更新行为
- 常见删除/更新行为
以CASECADE 为例子，格式为
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名) REFERENCES 主表(主表列明) ON UPDATE CASCADE ON DELETE CASCADE;
5、多表查询
5.1、多表关系
5.1.1、概述
项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，各个表结构之间也存在着各种联系，基本上分为三种：
- 一对多（多对一）
案例：部门与员工的关系
关系：一个部门对应多个员工，一个员工对应一个部门
实现：在多的一方建立外键，指向一的一方的主键
create table emp (id int auto_increment primary key comment '编号',employee_id varchar(10) comment '员工工号',employee_name varchar(10) comment '员工姓名',gender char(1) comment '性别',age tinyint unsigned comment '年龄',entrytime date comment '入职时间'
) comment '员工信息表';insert into emp values (null, '19985642', '糖锅', '男', 20, '2010-6-8'),(null, '19985696', '糖解', '女', 19, '2014-7-8'),(null, '19925564', '糖魅', '女', 27, '2011-12-3'),(null, '12385642', '糖地', '男', 24, '2020-5-8'),(null, '19753642', '糖仁', '男', 51, '2010-6-8');create table department(id int auto_increment comment '编号',name varchar(50) primary key comment '主键部门名称',emp_id int not null comment '员工id',constraint fk_emp_id foreign key (emp_id) references emp (id)
) comment '部门信息表'; insert into department values (1, '产品研发部', 1),(1, '产品研发部', 2),(1, '产品研发部', 3),(2, '产品销售部', 4),(2, '产品销售部', 5);
- 多对多
案例：学生与课程的关系
关系：一个学生可以选修多门课程，一门课程可供多名学生选修
实现：建立第三张中间表，中间表至少含有两个外键，分别关联两方主键
create table student(id int auto_increment primary key comment '主键id',name varchar(20) comment '姓名',num varchar(20) comment '学号'
) comment '学生表';insert into student values (null, '糖锅', '20211103196'),(null, '糖解', '20211106145'),(null, '糖魅', '20211106125'),(null, '糖地', '20251109147');create table course(id int auto_increment primary key comment '主键id',name varchar(20) comment '课程名字'
) comment '课程表';insert into course values (null, 'java'),(null, 'html'),(null, 'css'),(null, 'javascript');create table student_course(id int auto_increment primary key comment '主键',student_id int not null comment '学生id',course_id int not null comment '课程id',constraint fk_student_id foreign key (student_id) references student (id),constraint fk_course_id foreign key (course_id) references course (id)
) comment '学生课程中间表';insert into student_course values (null, 1, 1),(null, 1, 2),(null, 1, 3),(null, 2, 2),(null, 2, 3),(null, 2, 4);
- 一对一
案例：用户与用户详情的关系
关系：一对一关系，多用于单表拆分，将一张表的基础字段放在另一张表中，其他详情字段放在另一张表中，以提升操作效率
实现：在任意一方加入外键，关联另外一方主键，并且设置外键为唯一的（UNIQUE）
create table user(id int auto_increment primary key comment '主键id',name varchar(20) comment '姓名',age int comment '年龄',gender char(1) comment '性别（1：男, 2：女）',phone char(11) comment '手机号'
) comment '用户基本信息表';insert into user values (null '糖锅', 20, '1', '12011011934'),(null '糖解', 19, '2', '12011564514'),(null '糖魅', 27, '2', '19631011934'),(null '糖仁', 32, '1', '19018451934');create table user_edu(id int auto_increment primary key comment '主键id',degree varchar(20) comment '学历',major varchar(50) comment '专业',primaryschool varchar(50) comment '小学',middleschool varchar(50) comment '中学',university varchar(50) comment '大学',userid int unique comment '用户id',constraint fk_userid foreign key (userid) references user (id)
) comment '用户教育信息表';insert into user_edu values (null, '本科', 'rap', '小学1', '中学1', '大学1', 1),(null, '本科', 'rap', '小学2', '中学2', '大学2', 2),(null, '本科', 'rap', '小学3', '中学3', '大学3', 3),(null, '本科', 'rap', '小学4', '中学4', '大学4', 4);
5.2、多表查询概述
5.2.1、概述
指从多张表中查询数据
5.2.2、笛卡尔积
笛卡尔积是指在数学中，两个集合A集合和B集合的所有组合情况。再多表查询时，需要消除无效的笛卡尔积。
示例：查询每一个员工的姓名，以及关联的部门的名称
select emp.employee_name, department.name from emp, department where emp.id = department.emp_id;
查询结果：
5.3、内连接
5.3.1、概述
相当于查询A、B两个集合交集部分的数据
5.3.2、语法
隐式内连接
SELECT 字段列表 FROM 表1, 表2,... WHERE 条件...;
参考5.2.2中的示例
显式内连接
SELECT 字段列表 FROM 表1, [INNER] JOIN 表2 ON 连接条件...;
示例：查询每一个员工的姓名，以及关联的部门的名称（显式内连接实现）
select e.employee_name, d.name from emp e inner join department d on e.id = d.emp_id
查询结果与5.2.2一致
5.4、外连接
5.4.1、概述
左外连接：查询左表所有的数据，以及两张表交集的数据
右外连接：查询右表所有的数据，以及两张表交集的数据
5.4.2、语法
左外连接
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件...;
示例：查询emp表中所有数据，和对应的部门信息
select e.*, d.name from emp e left outer join department d on e.id = d.emp_id
查询结果：
右外连接
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件...;
示例：查询dept表中所有数据，和对应的员工信息
select e.*, d.* from emp e right outer join department d on e.id = d.emp_id;
查询结果：
5.5、自连接
5.5.1、概述
当前表于自身的连接查询，自连接必须使用别名
5.5.2、语法
SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件...;
示例1：查询员工及其所属领导的名字（需要在emp表中添加字段managerid依次赋值为null、1、1、2、3）
select a.employee_name 员工, b.employee_name 领导 from emp a, emp b where a.managerid = b.id;
查询结果：
示例2：查询所有员工emp及其领导的名字，如果没有领导，也需要查询出来
select a.employee_name 员工, b.employee_name 直属领导 from emp a left join emp b on a.managerid = b.id;
查询结果：
5.6、联合查询-union、union all
5.6.1、概述
对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。
5.6.2、语法
SELECT 字段列表 FROM 表A ...
UNION [ALL]
SELECT 字段列表 FROM 表B ...;
对于联合查询的多张表的列数必须相同，字段类型也需要保持一致。
union all会将全部的数据合并到一起，union会对合并的数据去重
5.7、子查询
5.7.1、概述
SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询。
5.7.2、语法
SELECT * FROM t1 WHERE columnl = (SELECT columnl FROM t2);
子查询外部的语句可以是INSERT / UPDATE / DELETE / SELECT 任意一个
5.7.3、分类
标量子查询（子查询结果为单个值）
常用操作符：=、>、 >=、<、<=、<>
列子查询（子查询结果为一列）
常用操作符：IN、NOT IN、ANY、SOME、ALL
行子查询（子查询结果为一行）
常用操作符：=、<>、IN、NOT IN
表子查询（子查询结果又多行多列）
示例：
添加薪资等级表
create table salgrade(grade int,losal int,hisal int
) comment '薪资等级表';insert into salgrade values (1, 0, 5000),(2, 5001, 10000),(3, 10001, 20000),(4, 20001, 40000),(5, 40001, 80000);
emp表添加薪资字段
alter table emp add salaty double comment '员工薪水';
1、查询拥有员工的部门id，部门名称
select distinct d.id, d.name from emp e, department d where e.id = d.id;
查询结果：
distinct去重
2、查询年龄大于25的员工的，及其部门的名称
select e.*, d.name from emp e left outer join department d on e.id = d.emp_id where e.age > 25;
查询结果：
3、查询所有员工工资等级
select e.employee_name, s.grade from emp e, salgrade s where e.salarty >= s.losal and e.salaty <= s.hisal;
查询结果：
4、查询研发部所有员工的信息，工资等级
-- 涉及的表：emp、department、salgrade
-- 连接的条件：emp.salaty between salgrade.losal and salgrade.hisal emp.id = department.emp_id
-- 查询条件：department.name like '%研发部'
select e.*, s.grade
from emp e,department d,salgrade s
where e.id = d.emp_idand (e.salaty between s.losal and s.hisal)and d.name like '%研发部';
查询结果：
5、查询研发部员工的平均工资
-- 涉及的表：emp、department
-- 连接条件：emp.id = department.emp_id
-- 查询条件：department.name like '%研发部'
select avg(e.salaty)
from emp e,department d
where e.id = d.emp_idand d.name like '%研发部';
6、查询工资比糖魅高的员工信息
select *
from emp e
where salaty > (select salaty from emp where emp.employee_name = '糖魅');
7、查询薪资比平均工资高的员工
select *
from emp
where salaty > (select avg(salaty) from emp);
8、查询所有部门的信息，并统计所有的部门的员工数
select distinct d1.id, d1.name, (select count(*) from department d2 where d2.id = d1.id) '人数'
from department d1;
9、查询所有学生选课情况，展示处学生姓名、学号、课程名称
select s.name, s.num, c.name
from student s,course c,student_course sc
where s.id = sc.student_idand c.id = sc.course_id;
6、事务
6.1、概述
事务是一组操作集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或者撤销操作请求，即这些操作要么同时成功，要么同时失败。
6.2、操作
准备一张表
create table account(id int auto_increment primary key comment '主键id',name varchar(20) comment '姓名',money double comment '余额'
) comment '账户表';insert into account (id, name, money)
values (null, '糖锅', 2000),(null, '糖魅', 2000);
演示转账操作（糖锅给糖魅转账1000）
1、查询账户余额
select * from account where name = '糖锅';
2、将糖锅账户余额减少1000
update account set money = money - 1000 where name = '糖锅';
3、将糖魅账户余额加上1000
update account set money = money + 1000 where name = '糖魅';
若此时上述三个语句变为
select * from account where name = '糖锅';update account set money = money - 1000 where name = '糖锅';
程序抛出异常...
update account set money = money + 1000 where name = '糖魅';
当我们执行这些语句时，程序报错了，并且我们的数据表中的数据出现了一些问题，糖锅的余额减少了1000但是糖魅账户中的余额没有发生变化。
如何解决上述问题呢？先不着急，我们先来介绍一下事务操作
- 查看/设置事务提交方式
-- 查看事务提交方式（1：自动提交；2：手动提交）
SELECT @@autocommit;-- 设置事务提交方式为手动提交
SET @@autocommit = 0;
- 提交事务
COMMIT;
- 回滚事务
ROLLBACK;
使用示例：
set @@autocommit = 0; --设置手动提交select * from account where name = '糖锅';update account set money = money - 1000 where name = '糖锅';update account set money = money + 1000 where name = '糖魅';-- 提交事务,若没有执行提交，则表中数据不发生变化
commit;
此时我们再执行上述出错语句
select * from account where name = '糖锅';update account set money = money - 1000 where name = '糖锅';
抛出异常...
update account set money = money + 1000 where name = '糖魅';-- 回滚事务
rollback;
SQL语句中出现异常，使用回滚事务，此时数据表中的数据没有发生变化
当让我们还可以通过手动的开启事务提交事务方式来控制事务
-- 开启事务
START TRANSACTION 或 BEGIN-- 提交事务
COMMIT;-- 回滚事务
ROLLBACK;
6.3、事务的四大特性
6.3.1、原子性
事务是不可分割的最小操作单元，要么全部成功，要么全部失败
6.3.2、一致性
事务完成时，必须保证所有的数据保持一致状态
6.3.3、隔离性
数据库系统提供的隔离机制，保证事务在不受外部并发操作影响独立环境下运行
6.3.4、持久性
事务一旦提交或回滚，它对数据库中的数据的改变就是永久的
6.4、并发事务问题
6.5、事务的隔离级别
6.5.1、隔离级别
我们可以通过SQL语句查看事务的隔离级别
SELECT @@TRANSACTION_ISOLATION;
这只事务隔离级别
SET [SESSION GLOBAL] TRANSACTION ISOATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }
+ 提交事务~~~MYSQL
COMMIT;
- 回滚事务
ROLLBACK;
使用示例：
set @@autocommit = 0; --设置手动提交select * from account where name = '糖锅';update account set money = money - 1000 where name = '糖锅';update account set money = money + 1000 where name = '糖魅';-- 提交事务,若没有执行提交，则表中数据不发生变化
commit;
此时我们再执行上述出错语句
select * from account where name = '糖锅';update account set money = money - 1000 where name = '糖锅';
抛出异常...
update account set money = money + 1000 where name = '糖魅';-- 回滚事务
rollback;
SQL语句中出现异常，使用回滚事务，此时数据表中的数据没有发生变化
当让我们还可以通过手动的开启事务提交事务方式来控制事务
-- 开启事务
START TRANSACTION 或 BEGIN-- 提交事务
COMMIT;-- 回滚事务
ROLLBACK;
6.3、事务的四大特性
6.3.1、原子性
事务是不可分割的最小操作单元，要么全部成功，要么全部失败
6.3.2、一致性
事务完成时，必须保证所有的数据保持一致状态
6.3.3、隔离性
数据库系统提供的隔离机制，保证事务在不受外部并发操作影响独立环境下运行
6.3.4、持久性
事务一旦提交或回滚，它对数据库中的数据的改变就是永久的
6.4、并发事务问题
[外链图片转存中…(img-0eIbUsSy-1718363774696)]
6.5、事务的隔离级别
6.5.1、隔离级别
[外链图片转存中…(img-a9cZUP05-1718363774697)]
我们可以通过SQL语句查看事务的隔离级别
SELECT @@TRANSACTION_ISOLATION;
这只事务隔离级别
SET [SESSION GLOBAL] TRANSACTION ISOATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }
之后还会有后续补充，现在就先到这吧~~
