＜＜＜学习提示：点击以下或者右侧目录即可快速跳转对应知识点＞＞＞
目录
1.3 修改表（修改字段类型、字段名称、表名；删除字段、表）
五 事务transaction：数据库不可分割的最小操作单元。
文中99%的SQL语句是所有关系型数据库都通用的，其中部分语句只适用于MySQL数据库（因为当时学习做该笔记时使用的是MySQL数据库） ！感谢理解！如果您使用的是Oracle数据库，您可以点击以下链接快速跳转学习~
https://blog.csdn.net/weixin_45806267/article/details/138319184?sharetype=blogdetail&sharerId=138319184&sharerefer=PC&sharesource=weixin_45806267&spm=1011.2480.3001.8118从下图↓↓↓开始正式步入本文正题~
- 我们可以通过MySQL客户端连接数据库管理系统DBMS，然后通过DBMS操作数据库。
- 可以使用SQL语句，通过数据库管理系统操作数据库，以及操作数据库中的表结构及数据。
- 一个数据库服务器中可以创建多个数据库，一个数据库中也可以包含多张表，而一张表中又可以包含多行记录。
一、什么是SQL？
SQL全称 Structured Query Language，结构化查询语言。
操作关系型数据库的编程语言，定义了一套操作关系型数据库
统一标准 。
二、SQL通用语法
在学习具体的SQL语句之前，先来了解一下SQL语言的通用语法。
- SQL语句可以单行或多行书写，以分号结尾。
- SQL语句可以使用空格/缩进来增强语句的可读性。
- MySQL数据库的SQL语句不区分大小写，关键字建议使用大写。
- 注释：
- 单行注释：-- 注释内容 或 # 注释内容
- 多行注释：/* 注释内容 */
三、SQL分类
SQL语句，根据其功能，主要分为四类：DDL、DML、DQL、DCL。
1 DDL：数据定义语言
Data Definition Language，数据定义语言，用来定义数据库对象(数据库，表，字段) 。
注意: 从本节开始所有语法中的[...] 内为可选参数，可以省略。
注意: 从本节开始所有语法中的[...] 内为可选参数，可以省略。
注意: 从本节开始所有语法中的[...] 内为可选参数，可以省略。
1.1 数据库操作
1.查询所有数据库
show datebases;
2.查询当前数据库
show database();
3.创建数据库
create database [ if not exists ] 数据库名
[ default charset 字符集 ]
[ collate 排序规则 ] ;
★可以通过if not exists 参数来解决这个问题，数据库不存在, 则创建该数据库，如果存在，则不创建。
4.删除数据库
drop database [if exists] 数据库名称;
★如果删除一个不存在的数据库，将会报错。
★此时，可以加上参数 if exists ，如果数据库存在，再执行删除，否则不执行删除。
5.切换数据库
use 数据库名;
★我们要操作某一个数据库下的表时，就需要通过该指令，切换到对应的数据库下，否则是不能操作的。1.2 表操作
1.2.1 一些与表相关的基本查询操作
1.查询当前数据库所有表
show tables;
★比如,我们可以切换到sys这个系统数据库,并查看系统数据库中的所有表结构。
2.查看指定表结构
desc 表名;
★通过这条指令，我们可以查看到指定表的字段，字段的类型、是否可以为NULL，是否存在默认值等信息。
3.查询指定表的建表语句
show create table 表名;
★过这条指令，主要是用来查看建表语句的，而有部分参数我们在创建表的时候，并未指定也会查询到，因为这部分是数据库的默认值，如：存储引擎、字符集等。1.2.2 MySQL数据类型
1.2.3 创建表
- comment：注释
- 注意: [...] 内为可选参数，最后一个字段后面没有逗号。
- 注意：约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束。
- 某字段的多个约束之间用空格隔开，注释放在最后。
***1.MySQL创建表SQL：
CREATE TABLE 表名称(
字段1 字段1类型（长度） 约束 [COMMENT 字段1注释 ],
字段2 字段2类型（长度） 约束 [COMMENT 字段2注释 ],
字段3 字段3类型（长度） 约束 [COMMENT 字段3注释 ],
......
字段n 字段n类型（长度） [COMMENT 字段n注释 ]
) [ COMMENT 表注释 ];
★comment：注释
★注意: [...] 内为可选参数，最后一个字段后面没有逗号。
★注意：约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束。
★某字段的多个约束之间用空格隔开，注释放在最后。
实例：
CREATE TABLE tb_user(
id int AUTO_INCREMENT PRIMARY KEY COMMENT 'ID唯一标识',--AUTO_INCREMENT：自动增长
name varchar(10) NOT NULL UNIQUE COMMENT '姓名' ,
age int check (age > 0 && age <= 120) COMMENT '年龄' ,
status char(1) default '1' COMMENT '状态',
gender char(1) COMMENT '性别'
);
***2.添加外键约束
CREATE TABLE 表名(
字段名 数据类型,
...
[CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名) );
***3.修改外键
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名) ;
实例：为emp表的dept_id字段添加外键约束,关联dept表的主键id。
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);
***4.删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
案例：删除emp表的外键fk_emp_dept_id。
alter table emp drop foreign key fk_emp_dept_id;
***5.Oracle创建表：（属性=字段，属性和字段一个意思）
create table 表名称(
属性1 数据类型(长度) primary key,--主键= not null unique
属性2 数据类型(长度) check(限制条件),--例：CHECK (Sex='男' OR Sex='女')
...
字段n 数据类型(长度) references 被参照的表名（被参照的属性名）--外键来自哪个表哪个属性
on delete cascade--表示删除被参照关系的元组时，同时删除参照关系的元组
on delete set null--表示删除被参照关系的元组时，参照关系的元组取null值
);
★6.补充知识：
char 与 varchar 都可以描述字符串，char是定长字符串，指定长度多长，就占用多少个字符，和
字段值的长度无关 。而varchar是变长字符串，指定的长度为最大占用长度。
如：
1). 用户名 username ------> 长度不定, 最长不会超过50
username varchar(50)
2). 性别 gender ---------> 存储值, 不是男,就是女
gender char(1)
3). 手机号 phone --------> 固定长度为11
phone char(11)1.2.4 constraint：约束
- 概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据。
- 目的：保证数据库中数据的正确、有效性和完整性。
1.2.4.1 字段约束
1.2.4.2 foreign key：外键约束
- 外键约束：用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。
- 注意：在数据库层面，必须建立外键关联， 才能保证数据的一致性和完整性的。
如果不在员工表中添加外键：我们可以做一个测试，删除id为1的部门信息。
结果，我们看到删除成功，而删除成功之后，部门表不存在id为1的部门，而在emp表中还有很多的员工，关联的为id为1的部门，此时就出现了数据的不完整性。 而要想解决这个问题就得通过数据库的外键约束。
alter table
emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);
添加了外键约束之后，我们再到
dept
表
(
父表
)
删除
id
为
1
的记录，然后看一下会发生什么现象。 此时 将会报错，不能删除或更新父表记录，因为存在外键约束。
1.添加外键约束
CREATE TABLE 表名(
字段名 数据类型,
...
[CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名) );
2.修改外键
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名) ;
实例：
为emp表的dept_id字段添加外键约束,关联dept表的主键id。
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);
3.删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
案例：删除emp表的外键fk_emp_dept_id。
alter table emp drop foreign key fk_emp_dept_id;
1.2.4.3 添加了外键之后的删除/更新行为
添加了外键之后，再删除父表数据时产生的约束行为，我们就称为删除/更新行为。具体的删除/更新行 为有以下几种：
-
NO ACTION：当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则 不允许删除/更新。 (与 RESTRICT 一致) 默认行为。
- RESTRICT：当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。 (与 NO ACTION 一致) 默认行为。
- CASCADE：当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有，则也删除/更新外键在子表中的记录。
- SET NULL：当在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null（这就要求该外键允许取null）。
-
SET DEFAULT：父表有变更时， 子表将外键列设置成一个默认的值 (Innodb不支持)。
具体语法：
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES
主表名 (主表字段名) ON UPDATE CASCADE ON DELETE CASCADE;
例：
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references
dept(id) on update cascade on delete cascade ;
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references
dept(id) on update set null on delete set null ;1.3 修改表（修改字段类型、字段名称、表名；删除字段、表）
1.添加字段
ALTER TABLE 表名 ADD 字段名 类型 (长度) [ COMMENT 注释 ] [ 约束 ];
案例:
为emp表增加一个新的字段”昵称”为nickname，类型为varchar(20)。
ALTER TABLE emp ADD nickname varchar(20) COMMENT '昵称';
2.修改字段数据类型
ALTER TABLE 表名 MODIFY 字段名 新数据类型 (长度);
3.修改字段名和字段类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型 (长度) [ COMMENT 注释 ] [ 约束 ];
案例:
将emp表的nickname字段修改为username，类型为varchar(30)
ALTER TABLE emp CHANGE nickname username varchar(30) COMMENT '昵称';
4.删除字段
ALTER TABLE 表名 DROP 字段名;
案例:
将emp表的字段username删除
ALTER TABLE emp DROP username;
5.修改表名
ALTER TABLE 表名 RENAME TO 新表名;
案例:
将emp表的表名修改为 employee
ALTER TABLE emp RENAME TO employee;
6.删除表
DROP TABLE [ IF EXISTS ] 表名;
★可选项 IF EXISTS 代表，只有表名存在时才会删除该表，表名不存在，则不执行删除操作(如果不
加该参数项，删除一张不存在的表，执行将会报错)。
7.删除表
TRUNCATE TABLE 表名;
★注意: 在删除表的时候，表中的全部数据也都会被删除。2 DML：数据操作语言（增、删、改）
DML英文全称是Data Manipulation Language(数据操作语言)，用来对数据库中表的数据记录进
行增、删、改操作。
2.1 增insert into：添加插入数据
插入数据的具体语法为：
1.给全部字段添加数据
insert into 表名 values（值1，值2，值3...）;
★没有指名要插入的列，所以values后需要把所有列的值都写进去。
2.给指定字段添加数据
insert into 表名（列名1，列名2...） values (列名1的值,列名2的值...);
★指名了要插入的列，values跟对应指名列的值，未指名的字段默认为空。
3.批量添加数据（全部字段）
INSERT INTO 表名 VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...) ;
4.批量添加数据（指定字段）
INSERT INTO 表名 (字段名1, 字段名2, ...)
VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...) ;
注意事项
★批量增加字段时，每两组值之间用英文状态的逗号,分割。
★插入数据时，指定的字段顺序需要与值的顺序必须是对应的。
★字符串和日期型数据应该包含在引号中。
★插入的数据大小，应该在字段的规定范围内。2.2 删delete from：删除数据
删除数据的具体语法为：
delete from 表名;
★删除表中所有内容=清除内容，不删除表的关系模式结构存储结构等
delete from 表名 where ...;
★删除符合where后条件的行
truncate table 表名;
--等价于不带where的delete语句，
优点是truncate一次性删除数据，效率比delete更高，可以释放空间，设置成初始大小；
缺点是truncate是一个DDL语句，不能被撤销rollback
drop table 表名;
★删除所有内容，包括表的关系模式结构等
★注意事项:
• DELETE 语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据。
• DELETE 语句不能删除某一个字段的值(可以使用UPDATE，将该字段值置为NULL即可)。2.3 改update：修改更新数据
修改数据的具体语法为:
UPDATE 表名 SET 字段名1 = 新值1 , 字段名2 = 新值2 , .... [ WHERE 条件 ] ;
update更新实例:
--例1：项目名称不变，收费项目类别“诊查费”要改为“诊察费”
update T001
set mc = '诊察费'
where dl = '0111'
and bm = 05
and mc = '诊查费';
update medi000
set fylb1mc = '诊察费', fylb2mc = '诊察费', fylb3mc = '诊察费'
where fylb1 = '05'
and fylb2 = '05'
and fylb1mc = '诊查费'
and fylb2mc = '诊查费';
注意事项:
★修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据。3 DQL：数据查询语言
DQL英文全称是Data Query Language(数据查询语言)，数据查询语言，用来查询数据库中表的记
录。在讲解语法之前，我们应该先搞清楚
MySQL数据库中DQL语句在执行时的执行顺序，也就是先执行那一部分，后执行那一部分。如下图：
3.1 查select：简单查询
MySQL中，DQL 查询语句，语法结构如下：
MySQL中DQL语法结构：
SELECT 字段列表
FROM 表名列表
WHERE 条件列表
GROUP BY 分组字段列表
HAVING 分组后条件列表
ORDER BY 排序字段列表
LIMIT 分页参数;
★注意：最后的limit分页参数只适用于MySQL，其余通用。如果是limit 后跟一个数字，就代表查询前几条数据。例如 limit 50 就代表取前50条数据。
拆分后的情况：
1.查询所有字段
SELECT * FROM 表名 ;
★注意 : * 号代表查询所有字段，在实际开发中尽量少用（不直观、影响效率）。
2.查询多个字段
SELECT 字段1, 字段2, 字段3 ... FROM 表名 ;
3.字段设置别名
SELECT 字段1 [ AS 别名1 ] , 字段2 [ AS 别名2 ] ... FROM 表名;
SELECT 字段1 [ 别名1 ] , 字段2 [ 别名2 ] ... FROM 表名;
★注意 : 别名带不带as都可以。
4.去除重复记录
SELECT DISTINCT 字段列表 FROM 表名;
5.条件查询
SELECT 字段列表 FROM 表名 WHERE 条件列表;
6.聚合函数：将一列数据作为一个整体，进行纵向计算 。
SELECT 聚合函数(字段列表) FROM 表名;
★注意 : NULL值是不参与所有聚合函数运算的。
7.分组函数
SELECT 字段列表 FROM 表名 [ WHERE 条件 ] GROUP BY 分组字段名 [ HAVING 分组后过滤条件 ];
★where与having区别：
★执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。
★判断条件不同：where不能对聚合函数进行判断，而having可以。
★注意事项:
• 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。
• 执行顺序: where > 聚合函数 > having 。
• 支持多字段分组, 具体语法为 : group by columnA,columnB
8.排序查询
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1 , 字段2 排序方式2 ;
★注意事项：
• 如果是升序, 可以不指定排序方式ASC。如果是降序，需要指定desc
• 如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序 ;
***9.分页查询（limit分页参数只适用于MySQL，其余通用。）
SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数 ;
★注意事项:
• 起始索引从0开始，起始索引 = （查询页码 - 1）* 每页显示记录数。
• 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。
• 如果查询的是第一页数据，起始索引可以省略，直接简写为 limit 10。
案例:
***MySQL特有分页参数limit：限制。
★语法：limit 起始索引，查询记录数;
★起始索引=（页码-1）*每一页展示的记录数
实例1：第一页，每页10条
SELECT * FROM emp limit 0,10;
起始索引0=（1-1）*10
实例2：查询第二页员工数据，每页展示10条。
SELECT * FROM emp limit 10,10;
起始索引10=（2-1）*10
以此类推：
第三页
SELECT * FROM emp limit 20,10;
第四页
SELECT * FROM emp limit 30,10;
★★★补充知识：
1.Oracle中实现分页方法一：利用三层嵌套。
rownum结合order by 使用规则：
★第一层子查询先做order by
★第二层子查询带出rownum
★第三层查询再来选择排序后要显示的行号结果
实例：
select * from
(select rownum r,t.* from
(select * from z_detail order by lrrq desc) --第一层子查询先做order by
t) --第二层子查询带出rownum
where r>10 and r<=20;--第三层查询再来选择排序后要显示的行号结果
2.Oracle中实现分页方法二：利用minus实现分页查询。
select rownum,t.* from z_doctors_advice t where rownum<=20
mius
select rownum,t.* from z_doctors_advice t where rownum<=10;
3.Oracle中实现分页方法三：利用row_number()函数实现分页查询。
实例：
select tt.* from
(select row_number() over(order by lrrq asc) 行号,t.*
from z_main t) tt
where 行号>10 and 行号<=20;3.2 查select：多表连接查询
注意：多表连接查询中，如果连接n张表，where后至少要有n-1个连接条件。
注意：多表连接查询中，如果连接n张表，where后至少要有n-1个连接条件。
注意：多表连接查询中，如果连接n张表，where后至少要有n-1个连接条件。
3.2.1 多表之间的关系
项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结 构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种：
-
一对多(多对一)： 实现： 在 多的一方建立外键 ，指向一的一方的主键。
-
多对多： 实现 : 建立第三张中间表 ，中间表至少包含两个外键，分别关联两方主键。
-
一对一： 实现 : 在任意一方加入外键 ，关联另外一方的主键，并且设置外键为唯一的 (UNIQUE)。
3.2.2 连接查询的分类
- inner join(等值连接或者叫内连接)：相当于查询A、B交集部分数据。
- left ioin (左连接)：查询左表所有数据，以及两张表交集部分数据
- right join (右连接)：查询右表所有数据，以及两张表交集部分数据。
- full join (全外连接)：返回左右表中所有的记录和左右表中交集部分的记录。
-
自连接：顾名思义，就是自己连接自己。当前表与自身的连接查询， 自连接必须使用表别名。对于自连接查询，可以是内连接查询，也可以是外连接查询。
3.2.3 连接查询的语法及案例
★SQL99标准查询：
SELECT
查询列表
FROM 表1 别名1
【连接类型】 JOIN 表2 别名2 ON 连接条件
【where 分组前筛选条件】
【group BY 分组列表】
【having 分组后筛选条件】
【order BY 排序列表】;
***1.等值连接（内连接）
★语句格式： ...from A (inner) join B on 条件; --(inner) 可以省略
显示内连接：select * from m_main A inner join z_main B ON A.DAH=B.DAH;
隐式内连接：select * from m_main A, z_main B where A.DAH=B.DAH;
2.左外连接
★语句格式：...from 主表A left (outer) join 副表B on 条件; --(outer)可以省略
select * from m_main A left outer join z_main B ON A.DAH=B.DAH;
3.右外连接
★语句格式：...from 副表A right (outer) join 主表B on 条件;--(outer)可以省略
select * from m_main A right outer join z_main B ON A.DAH=B.DAH;
4.全外连接
★语句格式：...from A full (outer) join B on 条件;--(outer)可以省略
select * from m_main A full outer join z_main B ON A.DAH=B.DAH;
***5.自连接：自连接查询，顾名思义，就是自己连接自己，也就是把一张表连接查询多次。
SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ... ;
自连接实例1：A. 查询员工 及其 所属领导的名字
select a.name , b.name from emp a , emp b where a.managerid = b.id;
自连接实例2：查询所有员工 emp 及其领导的名字 emp , 如果员工没有领导, 也需要查询出来。
select a.name '员工', b.name '领导' from emp a left join emp b on a.managerid =
b.id;
***注意：在自连接查询中，必须要为表起别名，要不然我们不清楚所指定的条件、返回的字段，到底
是哪一张表的字段。
3.3 嵌套查询，又称子查询
SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询。
子查询具体语法：
SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 );
注意：子查询外部的语句可以是INSERT / UPDATE / DELETE / SELECT 的任何一个。3.3.1 子查询分类
根据子查询结果不同，分为：
- 标量子查询（子查询结果为单个值）
- 列子查询(子查询结果为一列)
- 行子查询(子查询结果为一行)
- 表子查询(子查询结果为多行多列)
根据子查询位置不同，分为：
-
WHERE 之后
-
FROM之后
-
SELECT之后
3.3.2 子查询的语法及案例
1.标量子查询
子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询称为标量子查询。
常用的操作符：= <> > >= < <=
案例：查询 "销售部" 的所有员工信息。
select * from emp where dept_id = (select id from dept where name = '销售部');
2.列子查询
子查询返回的结果是一列（可以是多行），这种子查询称为列子查询。
常用的操作符：IN 、NOT IN 、 ANY 、SOME 、 ALL
案例：查询 "销售部" 和 "市场部" 的所有员工信息。
select * from emp where dept_id in (select id from dept where name = '销售部' or
name = '市场部');
3.行子查询
子查询返回的结果是一行（可以是多列），这种子查询称为行子查询。
常用的操作符：= 、<> 、IN 、NOT IN
案例: 查询与 "张无忌" 的薪资及直属领导相同的员工信息。
select * from emp where (salary,managerid) = (select salary, managerid from emp
where name = '张无忌');
4.表子查询
子查询返回的结果是多行多列，这种子查询称为表子查询。
常用的操作符：IN
案例:查询与 "鹿杖客" , "宋远桥" 的职位和薪资相同的员工信息。
select * from emp where (job,salary) in ( select job, salary from emp where name =
'鹿杖客' or name = '宋远桥' );4 DCL：数据控制语言
DCL英文全称是
Data Control Language(数据控制语言)，用来管理数据库用户、控制数据库的访
问权限。
★该部分全部语法注意事项:
• 在MySQL中需要通过用户名@主机名的方式，来唯一标识一个用户。
• 主机名可以使用%通配。
• 这类SQL开发人员操作的比较少，主要是DBA（ Database Administrator 数据库管理员）使用。
MySQL中DCL语法结构：
1.查询用户
select * from mysql.user;
★其中 Host代表当前用户访问的主机, 如果为localhost, 仅代表只能够在当前本机访问，是不可以远程访问的。User代表的是访问该数据库的用户名。在MySQL中需要通过Host和User来唯一标识一个用户。
2.创建用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
3.修改用户密码
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码' ;
4.删除用户
DROP USER '用户名'@'主机名';
5.查询权限
SHOW GRANTS FOR '用户名'@'主机名' ;
6.授予权限
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
7.撤销权限
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';
★6,7注意事项：
• 多个权限之间，使用逗号分隔
• 授权时， 数据库名和表名可以使用 * 进行通配，代表所有。
实例
-- 创建用户 itcast , 只能够在当前主机localhost访问, 密码123456;
create user 'itcast'@'localhost' identified by '123456';
-- 创建用户 heima , 可以在任意主机访问该数据库, 密码123456 ;
create user 'heima'@'%' identified by '123456';
-- 修改用户 heima 的访问密码为 1234 ;
alter user 'heima'@'%' identified with mysql_native_password by '1234';
-- 删除itcast@localhost用户
drop user 'itcast'@'localhost';
-- 查询权限
show grants for 'heima'@'%';
-- 授予权限
grant all on itcast.* to 'heima'@'%';
-- 撤销权限
revoke all on itcast.* from 'heima'@'%';
四 MySQL常用函数
函数 ：是指一段可以直接被另一段程序调用的程序或代码。
也就意味着，这一段程序或代码在MySQL中已经给我们提供了，我们要做的就是在合适的业务场景调用对应的函数完成对应的业务需求即可。
1 常用字符串函数
***1.CONCAT(S1,S2,...Sn): 字符串拼接，将S1，S2，... Sn拼接成一个字符串
select concat('Hello' , ' MySQL');
2.LOWER(str): 将字符串str全部转为小写
select lower('Hello');
3.UPPER(str): 将字符串str全部转为大写
select upper('Hello');
***4.LPAD(str,n,pad): 左填充，用字符串pad对str的左边进行填充，达到n个字符串长度
select lpad('01', 5, '-');
--将01的左边填充-填充至总长等于5：输出结果为---01
***5.RPAD(str,n,pad)：右填充，用字符串pad对str的右边进行填充，达到n个字符串长度
select rpad('01', 5, '-');--输出结果为01---
6.TRIM(str)：去除首尾空格。去掉字符串头部和尾部的空格
select trim(' Hello MySQL ');
***7.SUBSTRING(str,start,len)：截取子字符串。返回从字符串str从start位置起的len个长度的字符串
select substring('Hello MySQL',1,5);
案例:
由于业务需求变更，企业员工的工号，统一为5位数，目前不足5位数的全部在前面补0。比如： 1号员工的工号应该为00001。
update emp set workno = lpad(workno, 5, '0');2 常用数值函数
1.CEIL(x)：向上取整
select ceil(1.1);
select ceil(1.9);
2.FLOOR(x)：向下取整
select floor(1.9);
select floor(1.2);
3.MOD(x,y)：返回x/y的模（余数）
select mod(7,4);
***4.RAND()：获取随机数。返回0~1内的随机数
select rand();
***5.ROUND(x,y)：四舍五入。求参数x的四舍五入的值，保留y位小数
select round(2.345,2);
案例：
通过数据库的函数，生成一个六位数的随机验证码。
思路： 获取随机数可以通过rand()函数，但是获取出来的随机数是在0-1之间的，所以可以在其基础
上乘以1000000，然后舍弃小数部分，如果长度不足6位，补0。
select lpad(round(rand()*1000000 , 0), 6, '0');3 常用日期函数
1.CURDATE()：返回当前日期
select curdate();
2.CURTIME()：返回当前时间
select curtime();
***3.NOW()：返回当前日期和时间
select now();
***4.DATEDIFF(date1,date2)：获取两个日期相差的天数。
返回起始时间date1 和 结束时间date2之间的天数
select datediff('2021-10-01', '2021-12-01');
5.YEAR(date)：获取指定date的年份
select YEAR(now());
select MONTH(now());
select DAY(now());
7.MONTH(date)：获取指定date的月份
select YEAR(now());
select MONTH(now());
select DAY(now());
8.DAY(date)：获取指定date的日期
select YEAR(now());
select MONTH(now());
select DAY(now());
9.DATE_ADD(date, INTERVAL expr type)：返回一个日期/时间值加上一个时间间隔expr后的时间值
select date_add(now(), INTERVAL 70 YEAR );
案例：
查询所有员工的入职天数，并根据入职天数倒序排序。
思路： 入职天数，就是通过当前日期 - 入职日期，所以需要使用datediff函数来完成。
select name,
datediff(curdate(), entrydate) as 'entrydays'
from emp order by
entrydays desc;4 常用流程函数
流程函数也是很常用的一类函数，可以在SQL语句中实现条件筛选，从而提高语句的效率。
***1.IF(value , t , f)：如果value为true，则返回t，否则返回f
select if(false, 'Ok', 'Error');
***2.IFNULL(value1 , value2)：如果value1不为空，返回value1，如果为null则返回value2
select ifnull('Ok','Default');
select ifnull('','Default');
select ifnull(null,'Default');
***3.CASE [ 某字段 ] WHEN [ val1 ] THEN [res1] .WHEN [ val2] THEN [res2].. ELSE [ default ] END
如果某字段的值等于val1，返回res1，... 否则返回default默认值
3的实际案例：
需求: 查询emp表的员工姓名和工作地址 (北京/上海 ----> 一线城市 , 其他 ----> 二线城市)
select name 姓名,
( case workaddress when '北京' then '一线城市'
when '上海' then '一线城市'
else '二线城市' end ) as '工作地址'
from emp;
***4.CASE WHEN [ 条件 ] THEN [res1] ...ELSE [ default ] END
如果条件为true，返回res1，... 否则返回default默认值
4的实际案例：
第一步：数据准备语句
create table score(
id int comment 'ID',
name varchar(20) comment '姓名',
math int comment '数学',
english int comment '英语',
chinese int comment '语文'
) comment '学员成绩表';
insert into score(id, name, math, english, chinese) VALUES (1, 'Tom', 67, 88, 95
),(2, 'Rose' , 23, 66, 90),(3, 'Jack', 56, 98, 76);
第二步：查询需求如下：
统计班级各个学员的成绩，展示的规则如下:
>= 85，展示优秀
>= 60，展示及格
否则，展示不及格
第三步：实现需求语句
select
id 学号,
name 姓名,
(case when math >= 85 then '优秀' when math >=60 then '及格' else '不及格' end ) '数学',
(case when english >= 85 then '优秀' when english >=60 then '及格' else '不及格' end ) '英语',
(case when chinese >= 85 then '优秀' when chinese >=60 then '及格' else '不及格'end ) '语文'
from score;五 事务transaction：数据库不可分割的最小操作单元。
1.事务简介
- 事务：是一组操作的集合，它是不可分割的最小操作单元，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。
- 每一条SQL语句都是一个事务。
-
注意： 默认MySQL的事务是 自动提交 的，也就是说，当执行完一条DML语句时， MySQL会立即隐式的提交事务。
-
事务举例： 张三给李四转账10000块钱。张三银行账户的钱减少10000，而李四银行账户的钱要增加10000。 这一组操作就必须在一个事务的范围内，要么都成功，要么都失败。
2 事务ACID四大特性
- 原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
- 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。
- 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行.
- 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。
***事务控制方式一： 1.查看/设置事务提交方式 SELECT @@autocommit ; --查询结果为1代表是自动提交。 SET @@autocommit = 0 ; --修改为0代表需要手动提交。 2.提交事务 COMMIT; 3.回滚事务 ROLLBACK; 注意：上述的这种方式，我们是修改了事务的自动提交行为, 把默认的自动提交修改为了手动提 交, 此时我们执行的DML语句都不会提交, 需要手动的执行commit进行提交。 ***事务控制方式二： 1.每次执行操作前先开启事务 start transaction; --或 begin; --每一次执行相关操作时，先打开这个事务开关。打开之后我们需要手动提交或撤销事务，这种方式也避免了MySQL的自动提交。 2.提交事务 COMMIT; 3.回滚事务 ROLLBACK; 注意：推荐使用方式一，但是核对无误后要记得提交。 方式一可以有效避免每次操作前忘记开启事务，对MySQL误操作，却被自动提交的情况。
3 并发事务问题
- 脏读：一个事务读到另外一个事务还没有提交的数据。
- 丢失修改：两个事务同时修改同一行数据，最后提交的事务会覆盖之前事务的修改，导致第一个事务修改的内容丢失。
- 不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。
-
幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据 已经存在，好像出现了 "幻影"。
4 事务隔离级别
为了解决并发事务所引发的问题，在数据库中引入了事务隔离级别。主要有以下几种：
注意：事务隔离级别越高，数据越安全，但是性能越低。
1.查看事务隔离级别
SELECT @@TRANSACTION_ISOLATION;
2.设置事务隔离级别
SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED |
READ COMMITTED | REPEATABLE READ | SERIALIZABLE }
注意：事务隔离级别越高，数据越安全，但是性能越低。
扫一扫
1708
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
