LOADING...

加载过慢请开启缓存(浏览器默认开启)

loading

数据库

2023/10/8 后端

数据库基础内容

数据库系统(DBMS)

  • 关系型数据库系统(RDBMS)是指使用了关系模型的数据库
  • 关系模型中，数据是分类存放的，数据之间可以有联系
  • 淘宝网背后是3000多个数据库并发的集群
  • DB2电信金融领域 Oracle数据库集群 MySQL开源灵活 SQL Server教育领域免费
  • NoSQL数据库[Redis]指的是数据分类存放，但是数据之间没有关联关系的数据库系统
    主流NoSQL数据库 => Redis(内存 双十一秒杀) MemCache MongoDB(新闻) Neo4J
    NoSQL数据库只是关系型数据库的补充

MySQL衍生版

Oracle Percona(Linux系统) MariaDB

重设root密码 (D:/temp.txt)

  • 创建一个Txt文件，定义修改密码的SQL语句

    ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';

  • Windows PowerSheell(管理员) 窗口打开
    停止服务

    net stop mysql180

mysqld --defaults-file="D:\MySQL\MySQL Server 8.0\my.ini" --init-file=="D:/temp.txt" --console
ctrl+C取消

    启动服务

    net start mysql180

MySQL配置文件

  • 在my.ini文件中，我们可以设置各种MySQL的配置，例如字符集、端口号、目录地址等等

    my.init{客户端配置信息：[client]… [mysql]… 数据库配置信息：[mysqld]…}

[client]

# pipe=

# socket=MYSQL 端口号

port=3306

# 错误时主板没有轰鸣声
[mysql]
no-beep

# server_type=3
[mysqld]
#端口号
port=3306


# basedir="D:/MySQL/MySQL Server 8.0/"
# Path to the database root
datadir=D:/MySQL/MySQL Server 8.0\Data

# with an account. 密码认证插件
authentication_policy=mysql_native_password

#默认存储引擎
default-storage-engine=INNODB

# database servers. 开启严格模式
sql-mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

# General and Slow logging. 用文件记录日志
log-output=FILE
# 关闭日志输出
general-log=0
# 日志文件名称
general_log_file="PLUMINARY.log"
# 开启慢查询日志
slow-query-log=1
#慢查询日志文件名称
slow_query_log_file="PLUMINARY-slow.log"
#大于多少秒的执行SQL被记录在慢查询日志
long_query_time=10

# Error Logging.错误日志名称
log-error="PLUMINARY.err"

# from every other ID in use by any other source or replica. 集群会用到数据库ID
server-id=1

# 把表名转换成小写
lower_case_table_names=1

# 导入导出数据的目录地址
secure-file-priv="D:/MySQL/MySQL Server 8.0/Uploads"

#最大连接数
max_connections=151

定义逻辑库、数据表

  • SQL是用于访问和处理数据的标准的计算机语言
SQL语句的注释
# 这是一段注释文字
/* 这是另一段注释文字 */

创建逻辑库

CREATE DATABASE 逻辑库名称; #创建
SHOW DATABASES; #展现逻辑库
DROP DATABASE 逻辑库名称; #删除

创建数据表

CREATE TABLE 数据表(
  列名1 数据类型[约束] [COMMENT 注释],
  列名2 数据类型[约束] [COMMENT 注释],
  ......
)[COMMENT = 注释];


CREATA TABLE student(
  id INT UNSIGNED PRIMARY KEY, #主键约束 不可重复
  name VARCHAR(20) NOT NULL, #varchar 字符串 最大不超过20个字符 NOT NULL必填,不允许没有数据
  sex CHAR(1) NOT NULL, #char 字符
  birthday DATE NOT NULL, 
  tel CHAR(11) NOT NULL,
  remark VARCHAR(200)  #备注不超过200字符串
);

INSERT INTO student VALUES(1,"李强","男","1995-05-15","13312345678",NULL);

SHOW tables; #展现数据表名称
DESC student; #数据表结构具体情况
SHOW CREATE TABLE student; #查询当时的sql语句
DROP TABLE student; #删除数据表

数据定义语言：数据类型

数字

类型 大小 说明
TINYINT 1字节 小整数
SMALLINT 2字节 普通整数
MEDIUMIINT 3字节 普通整数
INT 4字节 较大整数
BIGINT 8字节 大整数
FLOAT 4字节 单精度浮点数
DOUBLE 8字节 双精度浮点数
DECIMAL[精确钱] ——– DECIMAL(10,2)
  • 十进制的浮点数无法在计算机中用二进制精确表达 比如0.2
   num FLOAT(20,10) #位数20 小数点后精确10位  在num中输入0.2的时候 变成了0.200000000030
=> num DECIMAL(20,10)

数据类型：字符串

类型 大小 说明
CHAR 1-255字符 固定长度字符串
VARCHAR 1-65535字符 不固定长度字符串
TEXT 1-65535字符 不确定长度字符串[后不加括号]
MEDIUMETEXT 1-1千6百万字符 不确定长度字符串[后不加括号]
LONGTEXT 1-42亿字符 不确定长度字符串[后不加括号]

数据类型：日期类型(年月日中间横线分割 要加引号)

类型 大小 说明
DATE 3字节 日期
TIME 3字节 时间
YEAR 1字节 年份
DATETIME 8字节 日期时间[电影开始时间]
TIMESTAMP 4字节 时间戳

修改表结构

添加字段
ALTER TABLE 表名称
ADD 列1 数据类型 [约束] [COMMENT 注释],
ADD 列2 数据类型 [约束] [COMMENT 注释],
......;

ADD address VARCHAR(200) NOT NULL;
修改字段名称
ALTER TABLE 表名称
CHANGE 列1 新列名1 数据类型 [约束] [COMMENT 注释],
CHANGE 列2 新列名2 数据类型 [约束] [COMMENT 注释],
......;

修改字段
ALTER TABLE student
MODIFY home_tel VARCHAR(20) NOT NULL;
CHANGE address home_address VARCHAR(200) NOT NULL; #改变字段名
删除字段
ALTER TABLE 表名称
DROP 列1，
DROP 列2，
......;

数据库的范式

第一范式：原子性

  • 第一范式是数据库的基本要求，不满足组这一点就不是关系数据库
  • 数据库的每一列都是不可分割的基本数据项，同一列中不能有多个值，也不能存在重复的属性
不符合第一范式
学号 姓名 班级
1000 刘娜 高三年级1班
符合第一范式
学号 姓名 年纪 班级
1000 刘娜 高三 1班

第二范式：唯一性

  • 数据表中的每条记录必须是唯一的。为了实现区分，通常要为表加上一列用来存储唯一标识，这个唯一属性列被称为主键列
无法区分重复的数据
学号 考试成绩 日期
230 58 2018-07-15
230 58 2018-07-15
数据具有唯一性
流水号 学号 考试成绩 日期
201807152687 230 58 2018-07-15
201807152694 230 58 2018-07-15

第三范式：关联性

  • 每列都与主键有直接关系，不存在传递依赖
违反第三范式
爸爸 儿子 女儿 女儿的玩具 女儿的衣服
陈华 陈浩 陈婷婷 海绵宝宝 校服

拿爸爸作为主键 儿子和女儿字段都是依赖于爸爸字段 但是后面的字段违反了第三范式 女儿的玩具和女儿的衣服是依赖于女儿这个字段的并不依赖于爸爸这个字段
数据表关系都非常松散 在检索的时候非常慢 因为爸爸主键字段查询非常快 但是查女儿的玩具和女儿的衣服找不到一样的主键

遵守第三范式
爸爸 儿子 女儿
陈华 陈浩 陈婷婷
女儿 女儿的玩具 女儿的衣服
陈婷婷 海绵宝宝 校服
  • 依照第三范式，数据可以拆分保存到不同的数据表，彼此保持关联
编号 部门 电话
10 财务部 1001
20 技术部 1002
30 销售部 1003
编号 姓名 性别 部门 入职日期
1 陈浩 男 10 2018-05-10
2 李婷婷 女 30 2018-03-22

部门字段也是依赖于编号的 两张表没有违反第三范式

字段约束

  • MySQL中的字段约束共有四种：
约束名称 关键字 描述
主键约束 PRIMARY KEY 字段值唯一，且不能为NULL
非空约束 NOT NULL 字段值不能为NULL
唯一约束 UNIQUE 字段值唯一，且可以为NULL
外键约束 FOREIGN KEY 保持关联数据的逻辑性

主键约束

  • 主键约束要求字段的值在全表必须唯一，而且不能为NULL值
  • 建议主键一定要使用数字类型，因为数字的检索速度会非常快
  • 如果主键是数字类型，还可也设置自动增长
CREATE TABLE t_teacher(
    id INT PRIMARY KEY AUTO_INCREMENT, #自带索引功能 自带排序
    ......
);

非空约束

  • 非空约束要求字段的值不能为NULL值
  • NULL值以为没有值，而不是 “” 空字符串
CREATE TABLE t_teacher(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(200) NOT NULL,
    married BOOLEAN NOT NULL DEFAULT FALSE #若不写则是默认值false
);

唯一约束

  • 唯一约束要求字段值如果不为NULL，那么在全表必须唯一
CREATE TABLE t_tracher(
    ......
    tel CHAR(11) NOT NULL UNIQUE
);

t_  tb_  一般是真实的表  
v_ vw_   一般是视图虚拟表的意思
总结代码
CREATE TABLE t_teacher(
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(200) NOT NULL,
    tel CHAR(11) NOT NULL UNIQUE, #唯一约束 UNIQUE 字段值唯一，且可以为NULL
    married BOOLEAN NOT NULL DEFAULT FALSE
);
-------------------------------------------------------------------------
CREATE TABLE student(
    id INT UNSIGNED PRIMARY KEY,
    name VARCHAR(20) NOT NULL,
    sex CHAR(1) NOT NULL,
    birthday DATE NOT NULL,
    tel CHAR(11) NOT NULL,
    remark VARCHAR(200)
);
INSERT INTO student VALUES (1,"李强","男","1995-05-15","13312345678",NULL);
DESC student; 
SHOW CREATE TABLE student;

ALTER TABLE student
ADD address VARCHAR(200) NOT NULL, #添加字段信息
ADD home_tel CHAR(11) NOT NULL;

ALTER TABLE student
MODIFY home_tel VARCHAR(20) NOT NULL; #改字段类型信息

ALTER TABLE student
CHANGE address home_address VARCHAR(200) NOT NULL; #改变字段名

ALTER TABLE student
DROP address; #删除字段

外键约束

  • 外键约束用来保证关联数据的逻辑关系
  • 外键约束的定义是写在子表上的
编号 部门 电话
10 财务部 1001
20 技术部 1002
30 销售部 1003
编号 姓名 性别 部门 入职日期
1 陈浩 男 10 2018-05-10
2 李婷婷 女 30 2018-03-22 
CREATE TABLE t_dept(
    deptno INT UNSIGNED PRIMARY KEY,
    dname VARCHAR(20) NOT NULL UNIQUE,
    tel CHAR(4) UNIQUE #写了电话必须唯一
);

CREATE TABLE t_emp(
    empno INT UNSIGNED PRIMARY KEY,
    ename VARCHAR(20) NOT NULL,
    sex EUNM("男","女") NOT NULL, #枚举 选择其中一个
    deptno INT UNSIGNED,
    hiredate DATE NOT NULL,
    FOREIGN KEY (deptno) REFERENCES t_dept(deptno) #和父表进行外键约束关联
);

不允许删除第一个编号 需要先删除第二个表的记录 再删除第一个表记录 逻辑关系有保证

外键约束的闭环问题 【因此开发中放弃外键约束】

  • 如果形成外键闭环，我们将无法删除任何一张表的记录

数据排序的好处

  • 一旦数据排序后，查找的速度就会翻倍，现实世界跟程序世界都是如此

如何创建索引

CREATE TABLE 表名称(
    ......,
    INDEX [索引名称] (字段),
    ......
);

#数据库对这个索引字段排序生成二叉树 每个字段都是有索引名称的
CREATE TABLE t_message(
    id INT UNSIGNED PRIMARY KEY,
    content VARCHAR(200) NOT NULL,
    type ENUM("公告","通报","个人通知") NOT NULL,
    create_time TIMESTAMP NOT NULL,
    INDEX idx_type (type)
); #利用二叉树的二分查找去查找索引字段就会非常快

如何添加与删除索引

CREATE INDEX 索引名称 ON 表名(字段); #添加索引
ALTER TABLE 表名称 ADD INDEX [索引名](字段); #添加索引
SHOW INDEX FORM 表名; #展示索引表  PRIMARY KEY AUTO_INCREMENT, #自带索引功能 自带排序
DROP INDEX 索引名称 ON 表名; #删除索引

索引的使用原则

  • 数据量很大，而且经常被查询的数据表可以设置索引 (日志表很少查询 无需设置 因为数据库要维护二叉树)
  • 索引只添加在经常被用作检索条件的字段上面
  • 不要在大字段上创建索引

(以上均为DDL语句)

DML语句

数据操作语句：普通查询

记录查询
  • 最基本的查询语句是由SELECT 和 FROM关键字组成
USE demo;
SELECT * FROM t_emp; #FROM是从员工表查数据 *是在结果集里显示员工表所有字段
SELECT empno,ename,sal FROM t_emp; #不查询所有 单独查询想要的
  • SELECT语句屏蔽了物理层的操作，用户不必关心数据的真实存储，交给数据库高效查找数据
  • 通常情况下 ，SELECT子句中使用了表达式，那么这列的名字就默认为表达式，因此需要一种对列明重命名的机制 [起别名：只是对查询的结果集字段改名称]
SELECT
  empno,
  sal*12 AS "income"
FROM t_emp;
  • 上述sql 查询语句的子句执行顺序

① 词法分析与优化：读取SQL语句
② FROM：选择数据来源
③ SELECT：选择输出内容

数据操作语言：数据分页

  • 朋友圈只会加载少量部分信息，不用一次性加载全部朋友圈，那样只会浪费CPU时间、内存和网络带宽
  • 如果结果集的记录很多，则可以使用LIMIT关键字限定结果集数量
SELECT ... FROM ... LIMIT 起始位置,偏移量; 
SELECT empno,ename FROM t_emp LIMIT 0,20; #从0往后取20条数据
数据分页简写
  • 如果LIMIT子句只有一个参数，它表示的是偏移量，起始值默认为0
SELECT empno,ename FROM t_emp LIMIT 10;
SELECT empno,ename FROM t_emp LIMIT 0,10;
FROM → SELECT → LIMIT

结果集排序

  • 如果没有设置，查询语句不会对结果集进行排序。也就是说，如果想让结果集按照某种顺序排序，就必须使用ORDER BY子句 【默认升序】
SELECT ... FROM ... ORDER BY 列名 [ASC|DESC];
SELECT ename,sal FROM t_emp ORDER BY sal;

排序关键字

  • ASC代表升序(默认)，DESC代表降序
  • 如果排序列是数字类型，数据库就按照数字大小排序，如果是日期类型就按照日期大小排序，如果是字符串就暗战字符集序号排序。
SELECT ename,sal FROM t_emp ORDER BY hiredate DESC;
排序字段内容相同的情况
  • 如果两条数据排序字段内容相同 sal 都是3000
    默认情况下是按照主键升序
多个排序字段
  • 使用ORDER BY规定首要排序顺序条件和次要排序条件。数据库会先按照要排序条件排序，如果遇到首要排序内容相同的记录，那么就会启动次要排序条件接着排序
SELECT ename,sal,hiredate 
FROM t_emp 
ORDER BY hiredate DESC,sal ASC;
#先按照首要排序hiredate降序 再启动次要排序sal降序

SELECT ename,sal,hiredate
FROM t_emp
ORDER BY sal DESC 
LIMIT 0,5  #工资排在前五位进行降序
排序+分页
  • ODER BY 子句书写的时候放在LIMIT子句的前面
    FROM → SELECT → ORDER BY → LIMIT

结果集中的重复数据

  • 假如我们要查询员工表有多种职业，写出来的sql语句

    SELECT job FROM t_emp; #结果集内可能会出现重复记录

  • 如果去除重复的数据，可以使用 DISTINCT 关键字来实现

    SELECT DISTINCT 字段 FROM ...;
SELECT DISTINCT job FROM t_emp;
注意事项

  • 使用DISTINCT的SELECT子句中只能查询一列数据，如果查询多列，去除重复记录就会失效

    SELECT DISTINCT job,ename FROM t_emp; #有job相同但是ename不相同 不能查询

  • DISTINCT关键字只能再SELECT子句中使用一次 [必须放在第一个字段前面]

数据操作语言：条件查询(一)

  • 满足某一种或几种条件的记录。这类条件要用WHERE子句来实现数据的筛选

    SELECT ... FROM ... WHERE 条件 [AND|OR] 条件 ...;

SELECT empno,ename,sal FROM t_emp
WHERE deptno=10 AND sal>=2000;

SELECT empno,ename,sal
FROM t_emp
WHERE(deptno=10 OR deptno=20) AND sal>=2000;
四类运算符

  • WHERE语句中的条件运算会用到以下四种运算符

    序号 运算符
    1 数学运算符
    2 比较运算符
    3 逻辑运算符
    4 按位运算符
算数运算符 [+加 -减 *乘 /除 %模]

NULL值与任何数字加减乘除都是NULL值 如果想要运算 必须加入 IFNULL(null,0); 意思是遇到NULL值就用0来计算 10+IFNULL(null,0) = 10
DATEDIFF(入职日期-现在的日期)/365

从t_emp表中找出 号位是10 和 总工资≥15000 的并且 计算工龄超过20年的人
SELECT empno,ename,sal,hiredate
FROM t_emp
WHERE deptno=10 AND (sal+IFNULL(NULL,0))*12>=15000
AND DATEDIFF(NOW(),hiredate)/365>=20;
比较运算符 [>大于 >=大于等于 <小于 <=小于等于 =等于 !=不等于 IN包含deptno IN(10,30,40)]
查询10 20 30部门里面在1980年以前入职的员工而且不能是SALESMAN职位
SELECT
  empno,ename,sal,deptno,hiredate
FROM t_emp;
WHERE deptno IN(10,20,30) AND job!="SALESMAN"
AND hiredate<"1985-01-01";
+续比较运算符
序号 表达式 意义 例子
8 IS NULL 为空 comm IS NULL
9 IS NOT NULL 不为空 comm IS NOT NULL
10 BETWEEN AND 范围 sal BETWEEN 2000 AND 3000
11 LIKE 模糊查询 ename LIKE “A%”
12 REGEXP 正则表达式 ename REGEXP “[a-zA-Z]{4}”

__代表前方一个未知 %代表前方N个未知

SELECT 
ename,comm,sal
FROM t_emp WHERE comm IS NULL
AND sal BETWEEN 2000 AND 3000;
AND ename LIKE "_LAKE";

SELECT 
ename,comm,sal
FROM t_emp WHERE comm IS NULL
AND sal BETWEEN 2000 AND 3000;
AND ename REGEXP "^[\\u4e00-\\u9fa5]{2,4}$"; #正则表达汉字范围寻找两到四个中文字符
按位运算符 [&位与 |位或 ~位取反 ^位异或 <<左移 >>右移]

<< 左移 10<<1 把10转换成二进制位 在最右面补上一个0 整体向左移动了一个单位
》》左移 10<<1 把10转换成二进制位 在最右面抹去一个0 整体向右移动了一个单位

二进制按位运算

  • 二进制运算的实质是将参与运算的两个操作数，按对应的二进制数逐位进行逻辑运算
    SELECT 3 & 7; 0011 & 0111 = 0011 = 3

数据操作语言：条件查询(二) [AND与 OR或 NOT非 XOR异或]

查询10和20之外部门的信息
SELECT
  ename,deptno,sal
FROM t_emp
WHERE NOT deptno IN(10,20) XOR sal>=2000;

WHERE子句的注意事项

  • WHERE子句中，条件执行的顺序是从左到右的。所以我们应该把索引条件，或者筛选掉记录最多的条件写在最左侧
    SELECT empno,ename FROM t_emp
WHERE ename = "FORD" AND sal >= 2000;

SELECT empno,ename FROM t_emp
WHERE deptno = 10 AND sal >= 2000;

各种子句的执行排序

FROM → WHERE → SELECT → ORDER BY → LIMIT

先表 再查出符合条件的记录 才能从中挑选出符合的字段 先排序后限制

数据库高级内容

数据操作语言：聚合函数

  • 聚合函数在数据的查询分析中，应用十分广泛。聚合函数可以对数据求和、求最大值和最小值、求平均值等等

  • 求公司员工的平均月收入是多少？

    底薪+佣金(不是null)
SELECT AVG(sal+IFNULL(comm,0)) FROM t_emp;
SUM函数

  • SUM函数用于求和，只能用于数字类型，字符类型的统计结果为0，日期类型统计结果是毫秒数相加

    SELECT SUM(ename) FROM t_emp

SELECT SUM(sal) FROM t_emp
WHERE deptno IN (10,20);
MAX函数

  • MAX函数用于获得非空值的最大值

    SELECT MAX(comm) FORM t_emp;

①查询10和20部门中,月收入最高的员工？
SELECT 
MAX(sal+IFNULL(comm,0)) FROM t_temp
FROM t_emp
WHERE deptno IN (10,20);

②查询员工名字最长的是几个字符？
SELECT MAX(LENGTH(ename)) FROM t_emp;

SELECT
SUM(sal),MAX(sal+IFNULL(comm,0)) 
FROM t_emp
WHERE deptno IN(10,20);
MIN函数

  • MIN函数用于获得非空值的最小值

    SELECT MIN(empno) FROM t_emp;
AVG函数

  • AVG函数用于获得非空值的平均值，非数字数据统计结果为0

    SELECT AVG(sal+IFNULL(comm,0)) FROM t_emp;
COUNT函数

  • COUNT(*) [找所有]用于获得包含空值的记录数，COUNT(列名)用于获得包含非空值的记录数

    SELECT COUNT(*) FROM t_emp; #统计所有   15 
SELECT COUNT(comm) FROM t_emp; #统计数量是非空   5

  • 查询10和20部门中，底薪超过2000元并且工龄超过15年的员工人数

    SELECT COUNT(*) 
FROM t_emp
WHERE deptno IN(10,20) 
AND sal>=2000
AND DATEDIFF(NOW(),hiredate)/365>=15;

  • 查询1985年以后入职的员工，底薪超过公司平均底薪的员工数量？

    ×××××××××错误示范×××××××××
SELECT COUNT(*) FROM t_emp
WHERE hiredate>="1985-01-01"
AND sal>AVG(sal); #AVG无法运行 聚合函数不能出现在WHERE里面

数据操作语言：分组查询

为什么要分组？
  • 默认情况下汇总函数是对全表范围内的数据做统计
  • GROUP BY子句的作用是通过一定的规则将一个数据集划分成若干个小的区域，然后针对每个小区域分别进行数据汇总处理
SELECT deptno,ROUND(AVG(sal))
FROM t_emp
GROUP BY deptno; #分组来计算AVG

逐级分组

  • 数据库支持多列分组条件，执行的时候逐级分组
  • 查询每个部门里，每种职位的人员数量和平均底薪
#按照部门和工作分组 ↓这种职位人数 ↓这种职位底薪平均值
SELECT deptno,job,COUNT(*),AVG(sal)
FROM t_emp 
GROUP BY deptno,job
ORDER BY deptno; #按照deptno去排序

对SELECT子句的要求

  • 查询语句中如果含有GROUP BY子句，那么SELECT子句中的内容就必须要遵守规定：SELECT子句中可以包括聚合函数，或者GROUP BY子句的分组列，其余内容均不可以出现在SELECT子句中
#正确示范
SELECT deptno,COUNT(*),AVG(sal)
FROM t_emp GROUP BY deptno;

#错误示范
SELECT deptno,COUNT(*),AVG(sal),sal
FROM t_emp GROUP BY deptno;

对分组结果集再次做汇总计算

SELECT 
deptno,COUNT(*),AVG(sal),MAX(sal),MIN(sal)
FROM t_emp 
GROUP BY deptno WITH ROLLUP; #WITH ROLLUP对汇总函数再次进行汇总运算

GROUP_CONCAT函数

  • GROUP_CONCAT函数可以把分组查询中的某个字段拼接成一个字符串

  • 查询每个部门内底薪超过2000元的人数和员工姓名

    SELECT deptno,GROUP_CONCAT(ename),COUNT(*)
FROM t_emp 
WHERE sal>=2000
GROUP BY deptno;
    deptno COUNT(*) GROUP_CONCAT(ename)
    10 2 CLARK,KING
    20 3 JONES,SCOTT,FORD
    30 1 BLAKE

各种子句的执行顺序

FROM → WHERE → GROUP BY → SELECT → ORDER BY → LIMIT

WHERE符合的留下来交给GROUP BY去分组之后调用SELECT中的聚合函数计算 ORDER BY子句对结果排序交给LIMIT子句来挑选返回哪些数据

分组查询遇到的困难？

  • 查询部门平均底薪超过2000元的部门编号

    #错误演示[因为WHERE语句出现了聚合函数]
SELECT deptno FROM t_emp
WHERE AVG(sal)>=2000
GROUP BY deptno;

  • HAVING语句是紧紧跟着GROUP BY语句的 HAVING子句可以写聚合函数作为判断条件

    SELECT deptno
FROM t_emp
GROUP BY deptno HAVING AVG(sal)>=2000;

HAVING子句的用途

  • 查询每个部门中，1982年以后入职的员工${普通条件可以写在WHERE里}$超过2个人${COUNT(*)>=2}$的部门编号
    不能拿聚合函数和某一个字段做判断

    SELECT deptno 
FROM t_emp
WHERE hiredate>="1982-01-01"
GROUP BY deptno HAVING COUNT(*)>=2;
ORDER BY deptno ASC;

SELECT deptno 
FROM t_emp
WHERE hiredate>="1982-01-01"
GROUP BY deptno HAVING COUNT(*)>=2 AND AVG(sal)>=2000;

HAVING子句的特殊用法 [作用类似于WHERE]

  • 按照数字1分组，MySQL会根据SELECT子句中的列进行分组，HAVING子句也可以正常使用
    能用WHERE就不要先用HAVING 它的作用是給聚合函数做判断

    #不推荐写法
SELECT deptno,COUNT(*) 
FROM t_emp
GROUP BY 1 HAVING deptno IN (10,20);

#推荐写法
SELECT deptno,COUNT(*) 
FROM t_emp
WHERE deptno IN(10,20)
GROUP BY 1;

数据操作语言：表链接查询(一)

从多张表中提取数据

  • 从多张表中提取数据，必须指定关联的条件。如果不定义关联条件就会出现无条件链接，两张表的数据会交叉连接，产生笛卡尔积

  • 规定了链接条件的表链接语句，就不会出现笛卡尔积 [On条件]

    SELECT e.empno,e.ename,d.dname
FROM t_emp e JOIN t_dept d #給表起别名
ON e.deptno=d.deptno; #员工部门编号等于部门的部门编号

表链接的分类

  • 表链接分为两种：内链接和外连接
  • 内链接是结果集中只保留符合连接条件的记录
  • 外连接是不管符不符合链接条件，记录都要保留在结果集中
内链接
SELECT ... FROM 表1
[INNER] JOIN 表2 ON 条件
[INNER] JOIN 表3 ON 条件
...
内连接的多种语法形式
SELECT ... FROM 表1 JOIN 表2 ON 连接条件;
SELECT ... FROM 表1 JOIN 表2 WHERE 连接条件
SELECT ... FROM 表1,表2 WHERE 连接条件;

内连接练习1

  • 查询每个员工的工号、姓名、部门名称、底薪、职位、工资等级？且保证工资符合范围
SELECT e.empno,e.ename,d.dname,e.sal,e.job,s.grade
FROM t_emp e JOIN t_dept d ON e.deptno=d.deptno
JOIN t_salgrade s ON e.sal BETWEEN s.losal AND s.hisal;
  • 内连接的数据表不一定必须有同名字段，只要字段之间符合逻辑关系就可以

内连接练习2

  • 查询与SCOTT相同部门的员工都有谁？

  • 相同的数据表也可以做表连接

    SELECT deptno
FROM t_emp
WHERE ename="SCOTT"; 

#子查询的结果变成了条件  SCOTT本人不算
SELECT deptno
FROM t_emp
WHERE deptno=(SELECT dptno FROM t_emp WHERE ename="SCOTT");
AND ename!="SCOTT";

#改造快速一些  ON后是筛选条件 和 WHERE作用差不多
SELECT e2.ename
FROM t_emp e1 JOIN t_emp e2 ON e1.deptno=e2.deptno
WHERE e1.ename="SCOTT" AND e2.ename!="SCOTT";

数据操作语言：表链接查询(二)

内连接查询练习1
  • 查询底薪超过公司平均底薪的员工信息？
    把聚合函数查询的结果做成一张表 再进行表连接 【分部描述】
#错误展示
SELECT e2.empno,e2.ename,e2.sal  #因为ON可以换成WHERE 然而WHERE后面有聚合函数 所以会报错
FORM t_emp e1 JOIN t_emp e2 ON e2.sal>=AVG(e1.sal);

#把聚合函数查询的结果做成一张表 再进行表连接 【分部描述】
SELECT AVG(sal)
FROM t_emp;

SELECT e.empno,e.ename,e.sal
FROM t_emp e JOIN(SELECT AVG(sal) avg FROM t_emp) t #avg别名
ON e.sal>=t.avg;
  • 查询RESEARCH部门的人数、最高底薪、最低底薪、平均底薪、平均工龄？
SELECT COUNT(*),MAX(e.sal),MIN(e.sal),AVG(e.sal),AVG(DATEDIFF(NOW(),e.hiredate)/365)
FROM t_emp e JOIN t_dept d ON e.deptno=d.deptno
WHERE d.dname="RESEARCH";

SELECT FLOOR(28.9); #变成28 向上取整
SELECT CEIL(1.1);   #变成1  向上取整
内连接查询练习2

  • 查询每种职业的最高工资、最低工资、平均工资、最高工资等级和最低工资等级？

    SELECT 
e.job,MAX(e.sal+IFNULL(e,comm,0)),
MIN(e.sal+IFNULL(e,comm,0)),AVG(e.sal+IFNULL(e,comm,0)),
MAX(s.grade),MIN(s.grade)
FROM t_emp e JOIN t_salgrade s
ON (e.sal+IFNULL(e,comm,0)) BETWEEN s.losal AND s.hisal
GROUP BY e.job;

  • 查询每个底薪超过部门平均底薪的员工信息 [用表连接 而不是子查询]

    SELECT e.empno,e.ename,e.sal
FROM t_emp e JOIN
(SELECT deptno,AVG(sal) AS avg FROM t_emp GROUP BY dptno) t #别名t
ON e.deptno=t.deptno AND e.sal>=t.avg;

数据操作语言：表链接查询(三)

为什么要使用外连接
  • 如果说陈浩是一名临时人员，没有固定的部门编制(NULL)，那么我们想查询每名员工和他的部门名称，用内连接就会遗漏掉陈浩，所以要引用外连接的语法才能解决这个问题
外连接简介

  • 外连接与内连接的区别在于，除了符合条件的记录之外，结果集中还会保留不符合条件的记录

    SELECT e.empno,e.ename,d.dname
FROM t_emp e  #因为陈浩部门编制是NULL 不能直接JOIN 只能LEFT JOIN
LEFT JOIN t_dept d ON e.deptno=d.deptno;
    7902 FORD RESEARCH
    7934 MILLER ACCOUNTING
    8000 陈浩 (NULL)

左连接和右连接

  • 左外连接就是保留左表所有的记录，与右表做连接。如果右表有符合条件的记录就与左表连接。如果右表没有符合条件的记录，就用NULL与左表连接。右外连接也是如此。

    SELECT e.empno,e.ename,d.dname
FORM t_dept d RIGHT JOIN t_emp e #要保留所有的t_emp e
ON e.deptno=d.deptno;

外连接练习1

  • 查询每个部门的名称和部门的人数？ [40有部门没员工 要保存空值]
    左外连接把部门表写在左侧 右外连接把部门表写在右侧

    SELECT d.dname,COUNT(*)
FROM t_dept d LEFT JOIN t_emp e #保留左表的所有记录 右表有空
ON d.deptno=e.deptno
GROUP BY d.deptno; #因为保留了左表所有记录 所以分组按照部门号分
#最终有一条t_dept与t_emp中的NULL做连接 所以 COUNT算上此记录
#若要将右表的NULL值忽略掉 要在COUNT(d.deptno)这样写

SELECT d.dname,COUNT(d.deptno)
FROM t_dept d LEFT JOIN t_emp e #保留左表的所有记录
ON d.deptno=e.deptno
GROUP BY d.deptno;
    dname COUNT(d.deptno)
    ACCOUNTING 3
    RESEARCH 5
    SALES 6
    OPERATIONS 0

  • 查询每个部门的名称和部门的人数？如果没有部门的员工，部门名称用NULL代替

  • UNION关键字可以将多个查询语句的结果集进行合并

    (查询语句) UNION (查询语句) UNION (查询语句)...
（SELECT d.name,COUNT(e.deptno)
FROM t_dept d LEFT JOIN t_emp e
ON d.deptno=e.deptno
GROUP BY d.deptno
）UNION
(SELECT d.dname,COUNT(*)
FROM t_dept d RIGHT JOIN t_emp e
ON d.deptno=e.deptno
GROUP BY d.deptno
);

数据库操作语言：表连接查询(四)

  • 查询每名员工的编号、姓名、部门、月薪、工资等级、工龄、上司编号、上司姓名、上司部门？
    [不知道：员工信息 员工的上司信息 两个不知道信息是不能使用子查询的 员工表 部门表 公司等级表]

    #陈浩要保存下来 用外连接
SELECT
e.empno,e.ename,d.dname,
e.sal+IFNULL(e.comm,0),s.grade,
FLOOR(DATEDIFF(NOW(),e.hiredate)/365),
t.empno AS mgrno,t.ename AS mname,t.dname AS mdname #定义上司的数据
FROM t_emp e 
LEFT JOIN t_dept d ON e.deptno=d.deptno #结果集所有记录保存下来跟工资等级表做连接
LEFT JOIN t_salgrade s ON e.sal BETWEEN s.losal AND s.hisal
LEFT JOIN
(SELECT e1.empno,e1.ename,d1.dname
FROM t_emp e1 JOIN t_dept d1
ON e1.deptno=d1.deptno
)t ON e.mgr=t.empno;

外连接的注意事项

  • 内连接值保留符合条件的记录，所以查询条件写在ON子句和WHERE子句中的效果是相同的。但是外连接里，条件写在WHERE子句里，不符合条件的记录是会被过滤掉的，而不是保留下来的。

数据操作语言：子查询(一)

WHERE中的子查询是需要反复查询的 不推荐使用，但是把它所得的结果集作为一张表跟其他表做连接是推荐的

  • 子查询是一种查询中嵌套查询的语句

  • 查询底薪超过公司平均底薪的员工信息

    SELECT empno,ename,sal
FORM t_emp
WHERE sal>=(SELECT AVG(sal) FROM t_emp); #不推荐使用 最好用表连接
子查询的分类
  • 子查询可以写在三个地方：WHERE子句、FROM子句、SELECT子句，但是只有FROM子句子查询是最可取的
WHERE子查询

  • 这种子查询最简单，最容易理解，但是确实效率很低的子查询

  • 查询底薪超过公司平均底薪的员工信息

    SELECT empno,ename,sal
FORM t_emp #↓↓↓↓ 比较每条记录都要重写执行子查询 ↓↓↓↓
WHERE sal>=(SELECT AVG(sal) FROM t_emp); #不推荐使用 最好用表连接
FROM子查询

  • 这种子查询只会执行一次，所以查询效率很高

  • 查询底薪超过公司平均底薪的员工信息

    SELECT
e.empno,e.ename,e.sal,t.avg
FROM t_emp e JOIN
(SELECT deptno,AVG(sal) AS avg
FORM t_emp 
GROUP BY deptno) t #按照部门编号去分组 起别名
ON e.deptno=t.deptno 
AND e.sal>=t.avg;
SELECT子查询

  • 这种子查询每输出一条记录的时候都要执行一次，查询效率很低

    SELECT
e.empno,
e.ename,
(SELECT dname FROM t_dept WHERE deptno=e.deptno)
FROM t_emp e;

数据操作语言：子查询(二)

单行子查询和多行子查询 [结果集可以作为新表连接]

  • 单行子查询的结果集只有一条记录，多行子查询结果集有多行记录

  • 多行子查询只能出现在WHERE子句和FROM子句中

  • 如何用子查询查找FORD和MARTIN两个人的同事

    SELECT ename #排除那俩人之外
FROM t_emp 
WHERE
deptno IN    # deptno = 不行因为后面返回了两条记录
(SELECT deptno FROM t_emp WHERE ename IN("FORD","MARTIN")); #返回两条记录
AND ename NOT IN("FORD","MARTIN");

WHERE子句中的多行子查询

  • WHERE子句中，可以使用IN、ALL、ANY、EXISTS关键字来处理多行表达式结果集的条件判断

  • 调查”FORD”和”MARTIN”底薪都高的员工信息

    SELECT ename 
FROM t_emp 
WHERE sal > ALL #ALL是sal里的数比结果集里的所有值都大  ANY则是比任何一个人大
(SELECT sal FROM t_emp #结果集里返回了多条记录
WHERE ename IN("FORD","MARTIN"))
AND ename NOT IN("FORD","MARTIN"); #不包含这俩人

EXISTS关键字

  • EXISTS关键字是把原来在子查询之外的条件判断，写到了子查询的里面
    EXISTS用上之后WHERE就不写任何语句了

    SELECT ... FROM 表名 WHERE [NOT] EXISTS(子查询);

  • 查询工资等级是3级或者4级的员工信息

    #排斥此方法 效率低下
SELECT
FROM t_emp
WHERE EXISTS(
SELECT * FROM t_salgrade
WHERE sal BETWEEN losal AND hisal 
AND grade IN(3,4)
);

MySQL对数据的基本操作

数据操作语言：INSERT语句

  • INSERT语句可以向数据表写入记录，可以是一条记录，也可以是多条记录

    INSERT INTO 表名(字段1,字段2,......) #添加字段可以快速写入 
VALUES(值1,值2,......);

INSERT INTO 表名(字段1,字段2,......) 
VALUES(值1,值2,......),(值1,值2,......); #多条记录

INSERT INTO t_dept(deptno,dname,loc)
VALUES(520,"研发部","河北"),(250,"销售部","江西");

#向技术部添加一条员工记录 
#[技术部编号不知道 子查询技术部的编号 结果 写到VALUES子句里面通过INSERT插入员工表里面]
INSERT INTO t_emp
(empno,ename,job,mgr,hiredate,sal,comm,deptno)
VALUES(8001,"潘春尧","SALESMAN",8000,"2023-10-15",2000,NULL,
(SELECT deptno FROM t_dept WHERE dname="技术部")); #子查询单个结果返回

INSERT语法方言

  • MySQL的INSERT语句有一种方言语法

    INSERT INTO 表名 SET 字段1=值1,字段2=值2,......; #只适合Mysql数据库

(INSERT) INTO t_emp
SET empno=8002,ename="JACK",job="SLAESMAN",mgr=8000,
hiredate="1985-01-01",sal=2500,comm=NULL,deptno=50;

DELETE FROM t_emp WHERE empno=8002;

IGNORE关键字

  • IGNORE关键字会让INSERT只插入数据库不存在的记录

    INSERT [IGNORE] INTO 表名...;
INSERT IGNORE INTO t_dept(deptno,dname,loc)
VALUES(40,"技术部","北京"); #40编号是主键被占用了 和已经现存的记录有冲突

#运用方言写
INSERT IGNORE INTO t_dept
SET deptno=40,dname="技术部",loc="北京";

#多重数据忽略不正确的数据
INSERT IGNORE INTO t_dept(deptno,dname,loc)
VALUES(40,"A","北京"),(80,"B","上海");

#VALUES(40,"A","北京"),(80,"B","上海")
#> Affected rows: 1
#> 时间: 0.002s

数据操作语言：UPDATE语句(一)

  • UPDATE语句用于修改表的记录
    UPDATE中的NIGNORE直接忽略冲突的语句

    UPDATE [IGNORE] 表名
SET 字段1=值1，字段2=值2,......
[WHERE 条件1...] #有条件的修改记录
[ORDER BY...] #对数据先排序 后修改员工编号＋1 
[LIMIT...]; #取分页数据

#把每个员工的编号和上司的编号+1，用ORDER BY子句完成
UPDATE t_emp 
SET empno=empno+1,mgr=mgr+1
ORDER BY empno DESC;

#把用收入前三名的员工底薪减100元，用LIMIT子句完成
UPDATE t_emp
SET sal=sal-100 
ORDER BY sal+IFNULL(comm,0) DESC #降序 
LIMIT 3; #取前三条记录

UPDATE语句的表连接(一)

  • 因为相关子查询效率非常低，所以我们可以利用表连接的方式来改造UPDATE语句

    UPDATE 表1 JOIN 表2 ON 条件
SET 字段1=值1,字段2=值2,......;

  • 表连接的UPDATE语句可以修改多张表的记录[进化]

    UPDATE 表1,表2
SET 字段1=值1,字段2=值2,......
WHERE 连接条件;

#把ALLEN调往RESEARCH部门，职务调整为ANALYST
[员工表+部门表]
UPDATE t_emp e JOIN t_dept d
SET e.deptno=d.deptno
WHERE e.ename="ALLEN" AND d.dname="RESEARCH"
#//在t_emp表中找到ALLEN + 在t_dept表中找到RESEARCH 
#//然后把d.deptno赋值給e.deptno

UPDATE t_emp e JOIN t_dept d
SET e.deptno=d.deptno,e.job="ANALYST",d.loc="北京"
WHERE e.ename="ALLEN" AND d.dname="RESEARCH"

  • 把底薪低于公司平均底薪的员工，底薪增加150元
    [运用表连接的方法比WHERE语句的效率高]

    UPDATE t_emp e JOIN
(SELECT AVG(sal) AS avg FROM t_emp) t
ON e.sal<t.avg
SET e.sal=e.sal+150;

UPDATE语句的表连接(二)

  • UPDATE语句的表连接既可以是内连接，又可以是外连接

    UPDATE 表1[LEFT|RIGHT] JOIN 表2 ON 条件 
SET 字段1=值1,字段2=值2,...;

  • 把没有部门的员工，或者SALES部门低于2000元底薪的员工，都调往20部门
    [把所有员工保留下来 但是陈浩没有部门 用内连接的话会把陈浩忽略掉 要用左外连接把左表所有数据保留下来再去跟部门表作连接]

    #把没有部门的员工，或者SALES部门低于2000元底薪的员工，都调往20部门
UPDATE t_emp e LEFT JOIN t_dept d ON e.deptno=d.deptno #连接条件 不符合的也保留
SET e.deptno=202
WHERE e.deptno IS NULL OR (d.dname="SALES" AND e.sal<2000);

数据操作语言： DELETE语句(一)

  • DELETE语句用于删除记录
    DELETE [IGNORE] FROM 表名
[WHERE 条件1,条件2,...] #按照条件删除记录
[ORDER BY...] #排序后删除
[LIMIT...]; #分页 工资降序排序 排在前五名的删掉
#顺序依次向下 最后一个是DELETE
练习1

  • 删除10部门中，工龄超过20年的员工记录

    DELETE FROM t_emp
WHERE deptno=10 AND DATEDIFF(NOW(),hiredate)/365>=20;

  • 删除20部门中工资最高的员工记录

    DELETE FROM t_emp
WHERE deptno=20
ORDER BY sal+IFNULL(comm,0) DESC
LIMIT 1;

DELETE语句的表连接(一)

  • 因为相关子查询效率非常低，所有我们可以利用表连接的方法来改造DELETE语句

    DELETE 表1,...FROM 表1 JOIN 表2 ON 条件 #删除哪张表记录的操作
[WHERE 条件1,条件2,...]
[ORDER BY...]
[LIMIT...];

#删除SALES部门和该部门的全部员工记录
#无需外连接 因为没有部门需要不删除的
DELETE e,d
FROM t_emp e JOIN t_dept d NO e.deptno=d.deptno #部门名称
WHERE d.dname="SALES";

  • 删除SALES部门和该部门的全部员工记录[表连接]

    DELETE e
FROM t_emp e JOIN
(SELECT deptno,AVG(sal) AS sal FROM t_emp GOURP BY deptno)
ON e.deptno=t.deptno AND e.sal<t.sal

  • 删除员工KING和他的直接下属的员工记录，用表连接实现

    DELETE e
FROM t_emp e JOIN
(SELECT empno FROM t_emp WHERE ename="KING") t
ON e.mgr=t.empno OR e.empno=t.empno; #KING的下属 OR KING这个人

DELETE语句的表连接(二)

  • DELETE语句的表连接既可以是内连接，又可以是外连接

    DELETE 表1,... FROM 表1 [LEFT|RIGHT] JOIN 表2 ON 条件...;

  • 删除SALES部门的员工，以及没有部门的员工 [左外 因为陈浩没部门要保留 外连接(不可用内连接)]

    DELETE e
FROM t_emp e LEFT JOIN t_dept d ON e.deptno=d.deptno
WHERE d.dname="SALES" OR e.deptno IS NULL

快速删除数据表全部记录

  • DELETE语句实在事务机制下删除记录，删除记录之前，先把将要删除的记录保存到日志文件里面，然后删除记录

  • TRUNCATE语句再事务机制之外删除记录，速度大于DELETE语句

    TRUNCATE TABLE 表名;

MySql的内置函数

数据库函数分类：数字、字符、日期、条件函数

函数 功能 用力
ABS 绝对值 ABS(-100)
ROUND 四舍五入 ROUND(4.62)
FOLLR 强制舍位到最近的整数 FLOOR(9,9) = 9
CEIL 强制仅为最近的整数 CEIL(3.2) = 4
POWER 幂函数 POWER(2,3)
LOG 对数函数 LOG(7,3)
LN 对数函数 LN(1,0)
函数 功能 用例
SQRT 开平方 SQRT(9)
PI 圆周率 P10
SIN 三角函数 sin(1)
TAN 三角函数 TAN(1)
COT 三角函数 COT(1)
COS 三角函数 COS(2)
DADIANS 角度来换成角度 DADIANS(20)
DEGREES 弧度转换角度 DEGRESS(1)

获取系统的时间函数

  • NOW()函数能获得系统日期和时间，yyyy-MMMM-dd hh:mm:ss
  • CURDATE()函数能获得当前系统日期，yyyy=MM=ddd
  • CURTIME()函数能获得当前时间系统信息，hh:mm:ss

日期格式化函数(一)

  • **DATE_FORMAT()**函数用于格式化日期，返回用户想要的日期格式

    DATE_FORMAT(r日期,表达式)

SELECT ename,DATE_FORMAT(hiredate,"%Y") AS "year"
FROM t_emp;

日期格式化函数(二)

占位符 作用 占位符 作用
%Y 年份 %m 月份
%d 日期 %w 星期(数字)
%W 星期(名称) %j 本年第几天
%U 本年第几周 %H 小时(24)
%h 小时(12) %i 分钟
%s 秒 %r 时间(12)
%T 时间(24)

  • 利用日期函数，查询明年你的生日是星期几？

    SELECT DATE_FORMAT("2019-6-20","%w"); #数字
SELECT DATE_FORMAT("2019-6-20","%W"); #英文星期

  • 利用日期函数，查询1981年上半年入职的员工多少人？[聚合函数 全表范围不用分组GROUP BY]

    SELECT COUNT(*) FROM t_emp
WHERE DATE_FORMAT(hiredate,"%Y")=1981
AND DATE_FORMAT(hiredate,"%m")<=6 #上半年

日期计算的注意事项

  • MySQL数据库里面，两个日期不能直接加减，日期也不能与数字加减
日期偏移计算

  • DATE_ADD()函数可以实现日期的偏移计算，而且时间单位很灵活

    DATE_ADD(日期,INTERVAL 偏移量 时间单位)

SELECT DATE_ADD(NOW(),INTERVAL 15 DAY); #15天之后
SELECT DATE_ADD(NOW(),INTERVAL -300 MINUTE); #300分钟之前  2023-10-16 04:48:48
SELECT DATE_FORMAT(
DATE_ADD(DATE_ADD(NOW(),INTERVAL -6 MONTH),INTERVAL -3 DAY),"%Y/%m/%d"); #2023/04/13
计算日期之间相隔的天数

  • **DATEDIFF()**函数用来计算两个日期之间相差的天数

    DATEDIFF(日期,日期)

字符函数(一)

函数 功能 用例
LOWER 转换小写字符 LOWER(ename)
UPPER 转换大写字符 UPPER(ename)
LENGTH 字符数量 LENGTH(ename)
CONCAT 连接字符串 CONCAT(sal, “$”)
INSTR 字符出现的位置 INSTR(ename, “A”)
INSERT 插入/替换字符 INSERT(“你好”,1,0,”先生”)
替换1个字符 0表示不替换—候补
REPLACE 替换字符 REPLACE(“你好先生”,“先生”,”女士”)
先生换成女士		 
SELECT
  LOWER(ename),UPPER(ename),LENGTH(ename),CONCAT(sal,"$"),INSTR(ename,"A")
FROM t_emp;

字符函数(二)

函数 功能 用例
SUBSTR 截取字符串 SUBSTR(“你好世界”,3,4)
3开始位置 4结束位置
SUBSTRING 截取字符串 SUBSTRING(“你好世界”,3,2)
3个字符开始往后截取2个位置
LPAD 左侧填充字符 LPAD(“Hello”,10,”“)
潘* 10最终字符串一共为10个字符
电话隐私保护加※号
RPAD 右侧填充字符 RPAD(“Hello”,10,”*”)
TRIM 去除首位空格 TRIM(“ 你好先生 “) 
SELECT LPAD(SUBSTRING("15027597319",8,4),11,"*");
SELECT RPAD(SUBSTRING("潘春尧",1,1),LENGTH("潘春尧")/3,"*"); 
#LENGTH是算英文字符 再除以3就是汉字的字符个数

条件函数

  • SQL语句中可以利用条件函数来实现变成语言里的条件判断

    IFNULL(表达式,值)
IF(表达式,值1,值2)

  • 中秋节公司发放礼品，SALES部门发放礼品A，其余部门发放礼品B，打印每名员工获得的礼品
    [按部门名称作表连接 部门表和员工表 内连接没有部门的没有礼品]

    SELECT 
  e.empno,e.ename,d.dname,
  IF(d.dname="SALES","礼品A","礼品B")
FROM t_emp e JOIN t_tept d ON e,deptno=d.deptno;

条件语句

  • 复杂的条件判断可以用条件语句来实现，比IF语句功能更强大

    CASE 
  WHEN 表达式 THEN 值1
  WHEN 表达式 THEN 值2
  ......
  ELSE 值N
END

  • 公司年庆决定组织员工集体旅游，每个部门旅游目的地是不同的。SALES部门去P1地点，ACCOUNTING部门去P2地点，RESEARCH部门去P3地点，查询每名员工的旅行地点。

    SELECT 
  e.empno,e.ename,
  CASE 
    WHEN d.dname="SALES" THEN "p1"
    WHEN d.dname="ACCOUNTING" THEN "p2"
    WHEN d.dname="RESEARCH" THEN "P3"
FROM t_emp e JOIN t_dept d ON e.deptno=d.deptno;
课堂练习

  • 某公司决定为员工调整基本工资，具体调整方案如下：

    序号 条件 涨幅
    1 SALES部门中工龄超过20年 10%
    2 SALES部门中工龄不满20年 5%
    3 ACCOUNTING部门 +300元
    4 RESEARCH部门里低于部门平均底薪 +200元
    5 没有部门的员工 +100元 
    [员工表连接部门表]
UPDATE t_emp e LEFT JOIN t_dept d ON e.deptno=d.deptno
LEFT JOIN (SELECT deptno,AVG(sal) AS avg FROM t_temp GOURP BY deptno) t
ON e.deptno=t.deptno
SET sal=(
    CASE 
      WHEN d,dname="SALES" AND DATEDIFF(NOW(),e.hiredate)/365>=20 
      THEN e.sal*1.1
      WHEN d,dname="ALES" AND DATEDIFF(NOW(),e.hiredate)/365<20 
      THEN e.sal*1.50
      WHEN d,dname="ACCOUNTING" THEN e.sal+300
      WHEN d,dname="RESEARCH" THEN e.sal<t.avg THEN e.sal+200
      WHEN e.deotno IS NULL THEN e.sal+100
      ELSE e.sal
    END
);

事务机制(一) 进入企业市场的第一步

避免写入直接操作数据文件
  • 如果数据的写入直接操作数据文件是非常危险的事情
利用日志来实现间接写入
  • MySQL总共有5种日志，其中只有redo日志和undo日志与事务有关
    [数据库 拷贝数据给 undo日志 记录修改 redo日志 与数据库同步数据]
事务机制(Transaction)
  • RDBMS = SQL语句 + 事务(ACID)
  • 事务是一个或者多个SQL语句组成的整体，要么全部执行成功，要么全都执行失败
事务案例
  • 把10部门中MANGER员工调往20部门，其他岗位的员工调往30部门，然后删除10部门
    事务：开启事务 [把要修改的数据拷贝到undo日志[可恢复]内,做的修改会被记录到redo日志[同步]里面] UPDATE语句  DELETE语句  提交事务

管理事务

  • 默认情况下，MySQL执行每条SQL语句都会自动开启和提交事务

  • 为了让多条SQL语句纳入到一个事务之下，可以手动管理事务

    START TRANSACTION;
SQL语句
[COMMIT|ROLLBACK];

START TRANSACTION;
DELETE FROM t_emp
DELETE FROM t_dept;
SELECT * FROM t_emp;
SELECT * FROM t_dept;
#这些删除修改只是在redo日志文件中进行的修改并未提交 
#虽然SELECT查不到了 但是点开左列的表中数据仍未删除
#只要不提交事务 redo日志就不会和数据库做同步
COMMIT; #把结果提交到日志里面 就会同步了
ROLLBACK; #做标记 回滚 一起失败

事务的ACID属性

原子性 一致性 隔离性 持久性

事务的原子性

  • 一个事务中的所有操作要么全部完成，要么全部失败。事务执行后，不允许停留在中间某个状态

事务的一致性

  • 不管任何给定的时间、并发事务由多少，事务必须保证运行结果的一致性
    [阻止事务之间互相读取临时数据] [A給B 10元 事务没有提交后且回滚了 C给A20元 此时A应该有30元 ]

隔离性

  • 隔离性要求事务不受其他并发事务的影响，如同在给定的时间内，该事务是数据库唯一运行的事务
  • 默认情况下A事务，只能看到日志中该事务的相关数据 [A,B事务可以看undo和redo日志]

持久性

  • 事务一旦提交，结果便是永久性的。即便发生宕机，仍然可以依靠事务日志完成数据的持久化

事务机制(二)

事务的四个隔离级别(可设置相互读取)

序号 隔离级别 功能
1 read uncommitted 读取未提交数据
2 read committed 读取已提交数据
3 repeatable read 重复读取
4 serializable 序列化

业务案例1 [购票系统]

A事务 B事务
车次 车厢 坐席 状态
G8047 1 1A 未售出
G8047 1 1B 未售出

A事务看到G8047 1A坐席未售出 用UPDATE把状态修改成已售出 因为没有提交 所以只修改在了undo日志里 真实的数据没有发生改变。此时B事务启动了看到了1A坐席还有票 于是更新且提交了 于是数据已经发生了改变。

A事务
车次 车厢 坐席 状态
G8047 1 1A 已售出
G8047 1 1B 未售出
所以在这个案例中我们需要B事务去读取A事务的状态 发现在A事务的临时数据里购买了坐席 那么B事务就可以去购买其他的坐席

修改事务隔离级别

  • READ UNCOMMITTED 代表可以读取其他事务未提交的数据

    # ↓设置当前绘画的事务级别↓ [并非全局]
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

    *无标题-查询1
START TRANSACTION;
UPDATE t_emp SET sal=1;
-----------------------
*无标题-查询2
START TRANSACTION;
SELECT empno,enam,sal FROM t_emp;
#发现员工的sal并未变成1块钱

#解决问题：增加事务隔离级别  一个事务读到了另一个事务的数据
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
SELECT empno,enam,sal FROM t_emp;
COMMIT;

业务案例2 [银行转账]

Scott账户有5000元余额 B事务开始执行支出100元 此时A事务执行转账1000元到该用户 如果AB都正常commit 最终账户余额是5900元 但是如果B事务是一个错误的消费被回滚[退款操作] 最终账户应该是6000元。如果允许A事务去读取B事务的临时数据 按照4900+1000=5900元 若A回滚就凭空少了100元

修改事务隔离级别

  • READ COMMITTED 代表只能读取其他事务提交的数据

    # ↓设置当前绘画的事务级别↓ [并非全局]
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

业务案例3 [电商案例]

A事务 下单购买 商品(价格350元) B事务要对 该商品涨价

修改事务隔离级别【READ为默认级别】

  • REPEATABLE READ 代表事务在执行中反复读取数据，得到的结果是一致性的，不会受其他事务影响

    #就算事务A提交了数据 也不影响事务B的查询原始数据
SET SESSION TRANSACTION ISOLATION LEVEL REPEATALE READ;

START TRANSACTION; 
UPDATE t_emp SET sal=1; 
COMMIT;
-----------------------
SET SESSION TRANSACTION ISOLATION LEVEL REPEATALE READ;
START TRANSACTION; 
SELECT empno,ename,sal FROM t_emp; 
COMMIT;

事务的序列化

  • 由于事务并发执行所带来的各种问题，前三种隔离级别只适用于在某些业务场景中，但是序列化的隔离性，让事务逐一执行，就不会产生上述问题了。

    SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

START TRANSACTION; ①
UPDATE t_emp SET sal=1; ②
COMMIT; ⑤
-----------------------
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION; ③
SELECT empno,ename,sal FROM t_emp; ④ #此时会等待 只需要执行上方COMMIT
COMMIT;

数据导入与导出

数据导出与备份的区别

  • 数据导出，导出的纯粹是业务数据
  • 数据备份，备份的是数据文件、日志文件、索引文件灯

全量备份 → 增量备份1 → 增量备份2

数据导出的分类

数据库可以导出SQL文档(数据不多) 也可以 导出文本文档(数据巨大)

导出SQL文件 [或者手动右键库 存储SQL文件]

  • mysqldump用来把业务数据导出成SQL文件，其中也包括了表结构

    #导出命令行 不写no-date就导出即包含 表结构 又包含 数据
mysqldump -uroot -p [no-date] 逻辑库 > 路径
# D:/MySQL/demo.sql

导入SQL文件

  • source命令用于导入SQL文件，包括创建数据表，写入记录等
    sql > USE demo;
sql > SOURCE backup.sql;
导出纯粹的业务数据

若数据巨大 先导出表结构 再导出数据库文档(.txt) 之后再导入 就会快很多

  • t_emp 右键 存储sql文件 仅结构 再次右键 导出向导… txt文档 (跳过词法分析与语法优化)

    "empno"    "ename"    "job"    "mgr"    "hiredate"    "sal"    "comm"    "deptno"
"7369"    "SMITH"    "CLERK"    "7902"    "17/12/1980"    "800"        "20"
"7499"    "ALLEN"    "SALESMAN"    "7698"    "20/2/1981"    "1600"    "300"    "30"
"7521"    "WARD"    "SALESMAN"    "7698"    "22/2/1981"    "1250"    "500"    "30"
"7566"    "JONES"    "MANAGER"    "7839"    "2/4/1981"    "2975"        "20"
"7654"    "MARTIN"    "SALESMAN"    "7698"    "28/9/1981"    "1250"    "1400"    "30"
"7698"    "BLAKE"    "MANAGER"    "7839"    "1/5/1981"    "2850"        "30"
"7782"    "CLARK"    "MANAGER"    "7839"    "9/6/1981"    "2450"        "10"
"7788"    "SCOTT"    "ANALYST"    "7566"    "9/12/1982"    "3000"        "20"
"7839"    "KING"    "PRESIDENT"        "17/11/1981"    "5000"        "10"
"7844"    "TURNER"    "SALESMAN"    "7698"    "8/9/1981"    "1500"    "0"    "30"
"7876"    "ADAMS"    "CLERK"    "7788"    "12/1/1983"    "1100"        "20"
"7900"    "JAMES"    "CLERK"    "7698"    "3/12/1981"    "950"        "30"
"7902"    "FORD"    "ANALYST"    "7566"    "3/12/1981"    "3000"        "20"
"7934"    "MILLER"    "CLERK"    "7782"    "23/1/1982"    "1300"        "10"

  • 导入回来 先删除t_emp 在dmeo右键 运行文件 导入刚刚右键存储的t_emp的结构(仅结构) 此时员工表结构就回来了 导入向导… 下一步 找到导出的txt文档 第一个数据行1(第一行有效数据) 在导入向导中根据源字段匹配目标字段 (txt+结构=数据表)

© 2018 - 2025 P-luminary @Asuna
Based on the Hexo Engine