食用前说明:
本文章内容来自B站韩顺平老师的课堂笔记,本人只是将其进行自我整理,内容有所删减,韩顺平老的B站课程AV号:BV1fh411y7R8
本文章内容来自韩顺平老师的课堂笔记的 第24章 MySQL基础。重点学习数据库类型,DDL的增删改查,约束,事务
1. SQ、L 基本介绍
1.1 什么是 SQL
Structured Query Language :结构化查询语言
其实就是定义了操作所有关系型数据库的规则。每一种数据库操作的方式存在不一样的地方,称为“方言”。
1.2 SQL 通用语法
-
SQL 语句可以单行或多行书写,以分号结尾。
-
可使用空格和缩进来增强语句的可读性。
-
三种注释:
-
单行注释:
-
--
注释内容 -
#
注释内容(mysql特有)
-
-
多行注释:
-
/*
注释*/
-
-
1.3 SQL 的四种分类
-
DDL (Date Definition Language) 数据定义语言,用来定义数据库对象:数据库,表,列等。
关键字:create ,drop ,alter 等
-
DML (Data Manipulation Language) 数据操作语言,用来对数据库中表的数据进行 增,删,改。
关键字:insert ,delete ,update 等
-
DQL (Data Query Language) 数据查询语言,用来查询数据库中表的数据。
关键字:select ,where 等
-
DCL (Data Control Language) 数据控制语言,用于定义数据库的访问权限和安全级别,及创建用户。
关键字:GRANT ,REVOKE 等
2. DDL : 操作数据库、数据表、数据列
2.1 操作数据库四字诀:CRUD
2.1.1 C (Create) :创建
创建数据库:
create database 数据库名称;
进阶:
判断数据库是否存在,再创建数据库
create database if not exists 数据库名称;
指定字符集,创建数据库
create database 数据库名称 character set 字符集名;
案例演示
-- 创建db4数据库,判断是否存在,并制定字符集为gbk create database if not exists db4 character set gbk;
2.1.2 R (Retrieve) :查询
查询所有数据库名称:
show database;
查询某个数据库的创建语句(数据库的字符集):
show create database 数据库名称;
2.1.3 U (Update) :修改
修改数据库的字符集
alter database 数据库名称 character set 字符集名称;
2.1.4 D (Delete) :删除
删除数据库
drop database 数据库名称;
判断数据库后再删除
drop database if exists 数据库名称;
2.1.5 使用数据库
查询当前正在使用的数据库名称
select database();
使用数据库
use 数据库名称;
2.2 操作表四字诀:CRUD
2.2.1 C (Create) :创建
语法:
create table 表名( 列名1 数据类型1, 列名2 数据类型2, .... 列名n 数据类型n );
注意:最后一列,不需要加逗号
,
案例演示
create table student( id int, name varchar(32), age int , score double(4,1), birthday date, insert_time timestamp );
复制表:
create table 表名 like 被复制的表名;
数据类型 | 描述 |
---|---|
int | 整数类型 |
double | 小数类型 |
date | 日期,只包含年月日,yyyy-MM-dd |
datetime | 日期,包含年月日时分秒,yyyy-MM-dd HH:mm:ss |
timestamp | 1. 时间戳类型,包含年月日时分秒,yyyy-MM-dd 2. 如果将来不给这个字段赋值,或赋值为null,则默认使用当前的系统时间,来自动赋值 |
varchar | 字符串,可指定大小 |
2.2.2 R (Retrieve) :查询
查询某个数据库中所有的表名称
show tables;
查询表结构
desc 表明;
2.2.3 U (Update) :修改
修改表名
alter table 表名 rename to 新的表名;
修改表的字符集
alter table 表名 character set 字符集名称;
添加一列
alter table 表名 add 列名 数据类型;
修改类名称 类型
alter table 表名 change 列名 新列名 新数据类型; alter table 表名 change 列名 新数据类型;
删除列
alter table 表名 drop 列名;
2.2.4 D (Delete) :删除
drop table 表名; drop table if exists 表名;
3. DML : 增删改表中的数据
3.1 添加数据
语法:
insert into 表名(列名1,列名2,...列名n) values (值1,值2,...值n);
注意:
列名和值要一对一对相应
如果表名后,不定义列名,则默认值给所有列添加值
insert into 表名 value(值1,值2,...值n);
除了数字类型,其他类型需要使用引号 (
''
/""
) 引起来
3.2 删除数据
语法:
delete from 表名 [where 条件]
注意:
如果不加条件,则会删除表中所有记录
如果要删除所有记录
delete from 表名; --不推荐使用。有多少条记录就会执行多少次删除操作
TRUNCATE TABLE 表名; --推荐使用。效率更高,先删除表,再重新创建一张一样的表。
3.3 修改数据
语法:
update 表名 set 列名1 = 值1, 列名2 = 值2,...[where 条件];
❗ 注意:如果不添加任何条件,则会将表中所有记录全部修改。
4. DQL : 查询表中的记录
查询所有表
select * from 表名;
4.1 语法关键词
后面跟什么 | |
---|---|
where | 条件列表 |
group by | 分组字段 |
having | 分组之后的条件 |
order by | 表示:排序 ASC:升序,默认 DESC:降序 limit :分页限定 |
❗ 注意:order by
order by 排序字段1 排序方法1, 排序字段2 排序方法2...若有多个排序条件,则当前一个的条件值一样时,才会判断第二个条件,以此类推。
4.2 基础查询
描述 | |
---|---|
distinct | 去除重复 |
as | 起别名(也可以省略as) |
ifnull(表达式1,表达式2) | 如果第一个参数不为空,则返回第一个参数,否则返回第二个参数。 |
-
查询多个字段
-
select 字段名1,字段名2... from 表名;
-
❗ 注意:如果查询所有字段,则可以使用
*
来代替字段列表,但一般公司不允许使用(降低了代码的可读性,增加了维护的成本)
-
4.3 聚合函数 :count
❗ :将一列数据作为一个整体,进行纵向的计算。
-
关键词
-
count :计算个数
-
一般选择非空的列 : 主键
-
count(*)
描述 max 计算最大值 min 计算最小值 sum 计算和 avg 计算平均值 ❗ 注意:集合函数的计算,排除 null 值。
-
解决方案:
-
选择不包含非空的列进行计算
-
IFNULL 函数
-
-
-
4.4 条件查询 :where
-
比较运算符
描述 > 、< 、<= 、>= 、= 、<> 、!= 大于、小于、大于(小于)等于、不等于 BETWEEN...AND... 显示在某一区间的值 IN (集合) 显示在 in 列表中的值,例:in(100,200) LIKE 、NOT LIKE 模糊查询占位符 _
单个任意字符 %
多个任意字符 IS NULL 判断是否为空 -
逻辑运算符
描述 and 或 && 多个条件同时成立 or 或 || 多个条件任一个成立 not 或 ! 不成立
4.4.1 分组查询 :group by
分组之后查询的字段:分组字段、聚合函数
❗ 注意:where 和 having 的区别?
区别 where 1. 在分组之前进行限定,若不满足条件,则不参与分组。
2. 不可以跟聚合函数
having 1. 在分组之后进行限定,若不满足结果,则不会被查询出来。
2. 可以跟聚合函数
4.4.2 分页查询 :limit
每页的查询条数
公式:开始的引索 = (当前页码 - 1)* 每页显示的条数
limit :是 MySQL 的 “方言”,独有。
4.5 约束
4.5.1 约束基本介绍
-
概念:
-
对表中的数据进行限定,保证数据的正确性、有效性和完整性。
-
-
分类
名称 关键字 描述 主键约束 primary key 1. 含义:非空且唯一
2. 一张表只能有一个字段为主键
3. 主键就是表中记录的唯一标识
非空约束 not null 1. 某一列的值不能为 null 唯一约束 unique 1. 某一列的值不能重复唯一约束
2. 可以有 NULL 值,但是只能有一条记录为 null
外键约束 foreign key 让表于表产生关系,从而保证数据的正确性。 -
❗ 注意:
-
主键的自动增长
-
概念:如果某一列是数值类型的,使用 auto_increment 可以来完成值得自动增长
-
在创建表时,添加主键约束,并且完成主键自增长
create table stu( id int primary key auto_increment,-- 给id添加主键约束 name varchar(20) );
-
删除自动增长
ALTER TABLE stu MODIFY id INT;
-
添加自动增长
ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;
-
-
-
4.5.2 级联操作
添加级联操作
语法:
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE ;
5. 数据库的设计
5.1 多表之间的关系
5.1.1 表与表之间的三大分类
一对一 (了解):
如:人和身份证
分析:一个人只有一个身份证,一个身份证只能对应一个人
一对多 (多对一):
如:部门和员工
分析:一个部门有多个员工,一个员工只能对应一个部门
多对多 :
如:学生和课程
分析:一个学生可以选择很多门课程,一个课程也可以被很多学生选择
5.2 数据库设计的范式
5.2.1 范式基本介绍
-
概念:设计数据时,需要遵循的一些规范。要遵循后边的范式要求,必须先遵循前边的所有范式要求
-
设计关系数据库时,遵从不同的规范要求,设计出合理的关系型数据库,这些不同的规范要求被称为不同的范式,各种范式呈递次规范,越高的范式数据库冗余越小。
-
-
目前关系数据库有六种范式:
范式名称 描述 第一范式(1NF) 1. 属于第一范式关系的所有属性都不可再分,即数据项不可分。 第二范式(2NF) 1. 在1NF的基础上 2. 且每一个非主属性完全函数依赖于任何一个候选码(在1NF基础上,消除非主属性对主码的部分函数依赖) 第三范式(3NF) 1. 在2NF基础上 2. 任何非主属性不依赖于其它非主属性 (在2NF基础上,消除传递依赖) 巴斯-科德范式(BCNF) 一个满足BC范式的关系模式有: 1. 所有非主属性对每一个码都是完全函数依赖; 2. 所有主属性对每一个不包含它的码也是完全函数依赖; 3. 没有任何属性完全函数依赖于非码的任何一组属性。 第四范式(4NF) 1. 在BCNF的基础上 2. 当一个表中的非主属性互相独立时(3NF),这些非主属性不应该有多值,若有多值就违反了4NF。 第五范式(5NF,完美范式) 1. 必须满足第四范式; 2. 表必须可以分解为较小的表,除非那些表在逻辑上拥有与原始表相同的主键。 -
判断一个关系是否属于第二范式:
-
找出数据表中的 所有码;
-
找出所有主属性和非主属性;
-
判断所有非主属性对码的部分函数依赖。
-
-
第五范式是在第四范式的基础上做的进一步规范化。
-
第四范式处理的是相互独立的多值情况。
-
而第五范式则处理相互依赖的多值情况。
-
-
-
概念基本表
描述 函数依赖 1. A-->B,如果通过 A 属性(属性组)的值,可以确定唯一 B 属性的值。 则称 B 依赖于 A 例如:学号-->姓名。(学号,课程名称) --> 分数 完全函数依赖 1. A-->B,如果 A 是一个属性组,则 B 属性值得确定需要依赖于 A 属性组中所有的属性值。 例如:(学号,课程名称) --> 分数 部分函数依赖 1. A-->B,如果 A 是一个属性组,则 B 属性值得确定只需要依赖于 A 属性组中某一些值即可。某些值依赖 例如:(学号,课程名称) -- > 姓名 传递函数依赖 1. A --> B,B -- > C 如果通过 A 属性(属性组)的值,可以确定唯一 B 属性的值,在通过 B 属性(属性组)的值可以确定唯一 C 属性的值,则称 C 传递函数依赖于 A 例如:学号-->系名,系名-->系主任 码 1. 如果在一张表中,一个属性或属性组,被其他所有属性所完全依赖,则称这个属性(属性组)为该表的 码 例如:该表中码为:(学号,课程名称) 主属性 1. 码属性组中的所有属性 2. 所有候选码的属性称为主属性。 非主属性 & 非码属性 1. 除过码属性组的属性 2. 不包含在任何候选码中的属性。 规范化 1. 一个低一级的关系模式,通过模式分解可以转化成,若干个高一级范式的关系模式的集合。 这个过程叫做规范化。 候选码 1. 若关系中的某一属性组的值能唯一标识一个元组,其他子集不能,则称该属性组为 候选码。 2. 若一个关系中有多个候选码,则选定其中一个为主码。
6. 多表查询 :select...from...where...
6.1 多表查询的分类
描述 | |
---|---|
隐式内连接查询 | 1. from子句 后面直接写 多个表名 使用where指定连接条件的 2. 使用where条件消除无用数据 |
显示内连接查询 | 使用 inner join ...on 这种方式 |
左外连接查询 | 1. 查询的是左表所有数据以及其交集部分 (以左表为基准,匹配右边表中的数据 若匹配到,就展示匹配到的数据 若匹配不到,左表中的数据正常展示, 右边的展示为 null ) 2. 使用 LEFT [OUTER] JOIN ,OUTER 可以省略 |
右外连接查询 | 1. 查询的是右表所有数据以及其交集部分 (以右表为基准,匹配左表数据 若匹配到,就展示匹配到的数据 若匹配不到,就右表数据正常展示,左边展示为 null ) 2. 使用 RIGHT [OUTER] JOIN |
子查询 | 1. 查询中嵌套查询,称嵌套查询为子查询 |
概括:内连接查询的三方面:
从哪些表中查询数据
条件是什么
查询哪些字段
6.1.1 子查询基本介绍
结果是单行单列
子查询可以作为条件,❗ 使用运算符:
>
>=
<
<=
=
案例
-- 查询员工工资小于平均工资的人 SELECT * FROM emp WHERE emp.salary < (SELECT AVG(salary) FROM emp);
结果是多行单列
子查询可以作为条件,使用运算符 in 来判断
案例
-- 查询'财务部'和'市场部'所有的员工信息 SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部'; SELECT * FROM emp WHERE dept_id = 3 OR dept_id = 2; -- 子查询 SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部');
结果是多行多列
子查询可以作为一张虚拟表参与查询
案例
-- 查询员工入职日期是2011-11-11日之后的员工信息和部门信息 -- 子查询 SELECT * FROM dept t1 ,(SELECT * FROM emp WHERE emp.`join_date` > '2011-11-11') t2 WHERE t1.id = t2.dept_id; -- 普通内连接 SELECT * FROM emp t1,dept t2 WHERE t1.`dept_id` = t2.`id` AND t1.`join_date` > '2011-11-11'
7. 事务
7.1 事务的基本介绍
-
概念:
-
如果一个包含多个步骤的业务操作,被事务管理,那么这些操作要么同时成功,要么同时失败。
失败后要回滚。
-
-
操作:
-
开启事务:start transaction;
-
回滚:rollback;
-
提交:commit;
-
-
案例
CREATE TABLE account ( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(10), balance DOUBLE ); -- 添加数据 INSERT INTO account (NAME, balance) VALUES ('zhangsan', 1000), ('lisi', 1000); SELECT * FROM account; UPDATE account SET balance = 1000; -- 需求:张三给李四转账 500 元 -- 0. 开启事务 START TRANSACTION; -- 1. 张三账户 -500 UPDATE account SET balance = balance - 500 WHERE NAME = 'zhangsan'; -- 2. 李四账户 +500 -- 出错了... UPDATE account SET balance = balance + 500 WHERE NAME = 'lisi'; -- 发现执行没有问题,提交事务 COMMIT; -- 发现出问题了,回滚事务 ROLLBACK;
7.1.1 MySQL数据库中事务的默认自动提交
事务提交的两种方式:
自动提交:
mysql就是自动提交的
一条DML(增删改)语句会自动提交一次事务。
手动提交: * Oracle 数据库默认是手动提交事务 * 需要先开启事务,再提交
修改事务的默认提交方式:
查看事务的默认提交方式:
SELECT @@autocommit; -- 1 代表自动提交 0 代表手动提交
修改默认提交方式:
set @@autocommit = 0;
7.2 事务的四大特征
原子性:是不可分割的最小操作单位,要么同时成功,要么同时失败。
持久性:当事务提交或回滚后,数据库会持久化的保存数据。
隔离性:多个事务之间,相互独立。
一致性:事务操作前后,数据总量不变。
7.3 事务的隔离级别(了解)
设置事务隔离级别,解决 多少个事务操作同一批数据引发的问题。
事务存在的问题:
描述 脏读 一个事务,读取到另一个事务中没有提交的数据的情况。 不可重复读(虚读) 在同一个事务中,两次读取到的数据不一样的情况。 幻读 一个事务操作 (DML) 数据表中所有记录,另一个事务添加了一条数据,则第一个事务查询不到自己的修改。 隔离级别:
名称 事务隔离级别 脏读 不可重复读 幻读 读未提交 read-uncommitted Yes Yes Yes 不可重复读(Oracle) read-committed NO Yes Yes 可重复读(MySQL默认) repeatable-read NO NO Yes 串行化 serializable NO NO NO ❗ 注意:隔离级别从小到大,安全性越来越高,但效率越来越低。
查询数据库隔离级别:
select @@tx_isolation;
设置数据库隔离级别:
set global transaction isolation level 级别字符串;
演示
set global transaction isolation level read uncommitted; start transaction; -- 转账操作 update account set balance = balance - 500 where id = 1; update account set balance = balance + 500 where id = 2;
8. DCL 管理用户,授权 (权限管理)
DBA:数据库管理员
8.1 管理用户
添加用户:
CREATE USER '用户'@'主机名' IDENTIFIED BY '密码';
删除用户:
DROP USER '用户名'@'主机名';
修改用户密码:
UPDATE USER SET PASSWORD = PASSWORD('新密码') WHERE USER = '用户名'; UPDATE USER SET PASSWORD = PASSWORD('abc') WHERE USER = 'lisi'; SET PASSWORD FOR '用户名'@'主机名' = PASSWORD('新密码'); SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123');
mysql 中忘记了 root 用户的密码?
cmd -- > net stop mysql 停止 mysql 服务 * 需要管理员运行该cmd
使用无验证方式启动 mysql 服务: mysqld --skip-grant-tables
打开新的 cmd 窗口,直接输入mysql命令,敲回车。就可以登录成功
use mysql;
update user set password = password('你的新密码') where user = 'root';
关闭两个窗口
打开任务管理器,手动结束 mysqld.exe 的进程
启动mysql服务
使用新密码登录。
查询用户:
-- 1. 切换到mysql数据库 USE myql; -- 2. 查询user表 SELECT * FROM USER;
❗ 通配符:
%
表示可以在任意主机使用用户登录数据库
8.2 权限管理
查询权限:show grants for
-- 查询权限 SHOW GRANTS FOR '用户名'@'主机名'; SHOW GRANTS FOR 'lisi'@'%';
授予权限:grant
-- 授予权限 grant 权限列表 on 数据库名.表名 to '用户名'@'主机名'; -- 给张三用户授予所有权限,在任意数据库任意表上
撤销权限:revoke...on
-- 撤销权限 revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名'; REVOKE UPDATE ON db3.`account` FROM 'lisi'@'%'