目录
数据库简介
操作系统中数据存放的载体
Windows、Linux、MacOS都是基于文件的操作系统
AVI\DOC\JPG\TXT等
为什么要使用数据库管理数据?
不利于提取数据
而对于excel,不利于关联信息查找
什么是数据库系统?
数据库系统(DBMS)时指一个能为用户提供信息服务的系统。它实现了有组织的、动态的存储大量相关数据的功能,提供了数据处理和信息资源共享的便利手段。
什么是关系型数据库系统
关系型数据库系统(RDBMS)是指使用了关系模型的数据库系统。
关系模型中,数据是分类存放的,数据之间可以有联系
关系型数据库的应用
- 教育系统 2.商业系统 3.医疗系统
数据库的大规模应用
有大规模数据库集群
主流关系型数据库
DB2 Oracle MySQL SQL Server(微软的)
什么是NoSQL数据库系统
NoSQL数据库是指数据分类存放,但是数据之间没有关联关系的数据库系统。
redis 单线程的
主流NoSql数据库
redis(内存保存数据)、MemCache(内存保存数据)、MongoDB(硬盘保存 低价值)、Neo4J(硬盘保存 复杂的人际关系等)
NoSQL数据库的应用场景
电商抢购、新闻
MySQL数据库
MySQL是应用最广、普及度最高的开源关系型数据库
MySQL由瑞典MySQL AB公司开发,目前属于Oracle旗下产品
用户管理
重设root密码 :
- 创建一个TXT文件,定义修改密码的SQL语句
- 写入 ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘密码’;
- 管理员身份打开PowerShell
- 执行net stop mysql80
- mysqld --defaults-file=”mysql文件路径地址\my.ini” --init-file=”写入的TXT文件的路径地址/TXT文件名.txt”--console
MySQL配置文件
在my.ini文件中,我们可以设置各种MySQL的参数配置,例如字符集、端口号、目录地址等等
my.ini [client]、[mysql] 都是客户端配置信息 [mysqld]数据库配置信息
数据库表的相关操作
管理逻辑库、数据表
什么是SQL语言
SQL是用于访问和处理数据的标准的计算机语言
SQL语言分类
DML 添加、修改、删除、查询(select)
DCL 用户、权限、事务
DDL 逻辑库、数据表、视图、索引
SQL语句注意事项
SQL语句不区分大小写,但是字符串区分大小写
例: SELECT “HelloWorld”;
SQL语句必须以分号结尾
SQL语句中的空白和换行没有限制,但是不能破坏语法
SQL语句的注释
SQL语句的注释有两种 2是多行注释
创建逻辑库
mysql > CREATE DATABASE 逻辑库名称; 创建逻辑库
mysql> SHOW DATABASES; 显示逻辑库
mysql> DROP DATABASE逻辑库名称; 删除逻辑库
创建数据表
CREATE TABLE 数据表(
列明1 数据类型[约束] [COMMENT 注释],
列明2 数据类型[约束][COMMENT 注释],
...
)[COMMENT = 注释];
例如:
CREATE TABLE student( #表名
id INT UNSIGNED PRIMARY KEY, #整数,不能为负数, 主键
name VARCHAR(20) NOT NULL, #字符串最大不超过20 不允许没有数据
sex CHAR(1) NOT NULL, #字符串只能为1
birthday DATE NOT NULL,
tel CHAR(11) NOT NULL,
remark VARCHAR(200)
);
效果:
INSERT INTO student VALUES(1,”李强”,”男”,”1991-1-1”,”11111111111”,NULL)
#向student表格中插入一行
其他代码:
SHOW tables;
DESC student; #看student表
SHOW CREATE TABLE student; #看创建student表的sql语句
DROP TABLE student; #删除数据表
了解常用的数据类型和约束
数据类型
数据类型:数字
类型 | 大小 | 说明 |
TINYINT | 1字节 | 小整数 |
SMALLINT | 2字节 | 普通整数 |
MEDIUMINT | 3字节 | 普通整数 |
INT | 4字节 | 较大整数 |
BIGINT | 8字节 | 大整数 |
FLOAT | 4字节 | 单精度浮点数 |
DOUBLE | 8字节 | 双精度浮点数 |
DECIMAL | ----- | DECIMAL(10,2)整数加小数10 |
不精确的浮点数
十进制的浮点数无法在计算机中用二进制精确表达
小数位不能精确表示 如何用二进制小数表示十进制0.2?
1/8+1/16+1/128+.....
所以可以用DECIMAL类型保存精确小数,因为DECIMAL是把每个数当做1字符
数据类型:字符串
类型 | 大小 | 说明 |
CHAR | 1-255字符 | 固定长度字符串 |
VARCHAR | 1-65535字符 | 不固定长度字符串 |
TEXT | 1-65535字符 | 不确定长度字符串 |
MEDIUMTEXT | 1-1千6百万字符 | 不确定长度字符串 |
LONGTEXT | 1-42亿字符 | 不确定长度字符串 |
数据类型:日期类型
类型 | 大小 | 说明 |
DATE | 3字节 | 日期 |
TIME | 3字节 | 时间 |
YEAR | 1字节 | 年份 |
DATETIME | 8字节 | 日期时间 |
TIMESTAMP | 4字节 | 时间戳 |
修改数据表的结构
添加字段(第一行的)
ALTER TABLE 表名称
ADD 列1 数据类型[约束][COMMENT 注释],
ADD列2 数据类型[约束][COMMENT 注释],
....;
修改字段类型和约束
ALTER TABLE 表明称
MODIFY 列1 数据类型[约束][COMMENT 注释],
MODIFY 列2 数据类型[约束][COMMENT 注释],
....;
修改字段名称
ALTER TABLE 表明称
CHANGE 列1 新列名1 数据类型[约束][COMMENT 注释],
CHANGE 列2 新列明2 数据类型[约束][COMMENT 注释],
...;
删除字段
ALTER TABLE 表名称
DROP 列1,
DROP 列2,
...;
字段约束
数据库的范式
构建数据库必须遵循一定的规则,这种规则就是范式
目前关系数据库有六种范式,一般情况下,只要满足第三范式即可
第一范式: 原子性
数据表的每一列都是不可再分的基本数据项,同一列中不能有多个值,也不能存在重复的属性。
学号 | 姓名 | 班级 |
1 | 李四 | 高三年级1班 |
这个就不符合第一范式, 每一列都要不可再分,班级可再分为年级和班级
符合第一范式 原子性的是:
学号 | 姓名 | 年级 | 班级 |
1 | 李四 | 高三 | 1班 |
数据表中的每条记录必须是唯一的,为了实现区分,通常要为表上加上一个列来存储唯一标识,这个唯一属性列被称作主键列
学号 | 考试成绩 | 日期 |
230 | 58 | 2018-07-15 |
230 | 58 | 2018-07-15 |
这个就无法区分重复的数据
而唯一性是,加入一个主键区分不同的数据
流水号 | 学号 | 考试成绩 | 日期 |
201807152687 | 230 | 58 | 2018-07-15 |
201807152695 | 230 | 58 | 2018-07-15 |
每列都与主键有直接关系,不存在传递依赖
爸爸(key) | 儿子 | 女儿 | 女儿的玩具 | 女儿的衣服 |
陈啊 | 陈额 | 陈哦 | 海绵宝宝 | 校服 |
这个表格违反第三范式,关联性, 因为爸爸是主键,而女儿的玩具和女儿的衣服都跟爸爸没有关系,所有违反关联性
可以改为:
爸爸(key) | 儿子 | 女儿 |
陈啊 | 陈额 | 陈哦 |
女儿(key) | 女儿的玩具 | 女儿的衣服 |
陈哦 | 海绵宝宝 | 校服 |
依照第三范式,数据可以拆分保存到不同的数据表,彼此保持关联
字段约束
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(
married BOOLEN NOT NULL DEFAULT FALSE
);
外键约束
外键约束用来保证关联数据的逻辑关系
外键约束的定义是写在子表上的
CREATE TABLE t_dept( #父表 部门表
deptno INT UNSIGNED PRIMARY KEY,
dname VARCHAR(30) NOT NULL UNIQUE,
tel CHAR(4) UNIQUE
);
CREATE TABLE t_emp( #子表,员工表
empno INT UNSIGNED PRIMARY KEY,
ename VARCHAR(20) NOT NULL,
sex ENUM(“男”,”女”) NOT NULL,
deptno INT UNSIGNED,
hiredate DATE NOT NULL,
FOREIGN KEY (deptno) REFERENCES t_dept(deptno)
外键约束的闭环问题(所以一般不用外键)
如果形成外键闭环,我们将无法删除任何一张表的记录
索引运行机制和使用原则
数据排序的好处
一旦数据排序之后,查找的速度就会翻倍,现实世界跟程序世界都是如此
如何创建索引
CREATE TABLE 表名称(
...,
INDEX [索引名称](字段), #数据库就会排序
...
);
如何添加与删除索引
CREATE INDEX 索引名称 ON 表名(字段); #添加索引
ALTER TABLE 表名称 ADD INDEX [索引名称](字段); #添加索引
SHOW INDEX FROM 表名; #查看索引
DROP INDEX 索引名称 ON 表名;
索引的使用原则
数据量很大,而且经常被查询的数据表可以设置索引
索引只添加在经常被用作索引条件的字段上面
不要在大字段上创建索引
数据库的基本查询
数据的简单查询
记录查询
最基本的查询语句是由SELECT和FROM关键字组成的
SELECT *FROM t_emp;
SELECT empno,ename,sal FROM t_emp;
SELECT语句屏蔽了物理层的操作,用户不必关心数据的真实存储,交由数据库高效的查找数据
使用列别名
通常情况下,SELECT子句中使用了表达式,那么这列的名字就默认为表达式,因此需要一种对列名命名的机制
SELECT
empno,
sal*12 AS “income”
FORM t_emp;
查询语句中的字句执行顺序
SELECT
empno,
sal*12 AS “income”
FORM t_emp;
数据的高级查询
数据分页
比如我们查看朋友圈,只会加载少量部分信息,不用一次性加载全部朋友圈,那样只会浪费CPU时间、内存和网络带宽
如果结果集的记录很多,则可以使用LIMIT关键字限定结果集数量
SELECT ...FROM ... LIMIT 起始位置,偏移量; #是偏移量!
例:
SELECT empno,ename FROM t_emp LIMIT 0,20; #查询前20条数据, 从0开始的
结果集排序
如果没有设置,查询语句不会对结果集进行排序。也就是说,如果想让结果集按照某种顺序排序,就必须使用ORDER BY语句
SELECT ... FROM ... ORDER BY 列名[ASC|DESC]; #什么都不写 就是升序
SELECT ename,sal FROM t_emp ORDER BY sal;
如果排序列是数字类型,大小排序,如果是日期类型,就按照日期大小排序,如果是字符串就按照字符集序号排序
排序字段内容相同的情况
默认情况下,如果两条数据排序字段内容相同,那么排序会是什么样子? 默认按照主键顺序排序
多个排序字段
我们可以使用ORDER BY规定首要排序条件和次要排序条件。数据库会先按照首要条件排序,如果遇到首要排序内容相同的记录,那么就会启用次要顺序接着排序。
SELECT .. FROM .. ORDER BY .. ASC, ... DESC;
排序+分页 执行顺序
FROM -->SELECT -->ORDER BY -->LIMIT02
去除结果集中的重复记录
如果我们需要去除重复的数据,可以使用DISTINCT关键字来实现
SELECT DISTINCT job FROM t_emp;
注意事项
使用DISTINCT的SELECT子句中只能查询一列数据,如果查询多列,去除重复记录就会失效。
DISTINCT关键字只能在SELECT子句中使用一次
条件查询
很多时候,用户感兴趣的并不是逻辑表的全部信息,而是能满足某一种或者几种条件的记录,这类条件要用WHERE子句来实现数据的筛选
SELECT ... FROM ... WHERE 条件 [AND|OR] 条件
例:
SELECT empno, ename ,sal FROM t_emp
WHERE deptno =10 AND sal>2000;
四类运算符
WHERE语句中的条件运算会用到以下四种运算符
序号 | 运算符 |
1 | 数学运算符 |
2 | 比较运算符 |
3 | 逻辑运算符 |
4 | 按位运算符 |
算数运算符
序号 | 表达式 | 意义 | 例子 |
1 | + | 加法 | 1 + 2 + 3 |
2 | - | 减法 | 1 - 2 - 3 |
3 | * | 乘法 | 5 * 35 |
4 | / | 除法 | 231 / 2 |
5 | % | 求模 | 10 % 3 |
比较运算符
序号 | 表达式 | 意义 | 例子 |
1 | > | 大于 | age>18 |
2 | >= | 大于等于 | age >= 18 |
3 | < | 小于 | age<18 |
4 | <= | 小于等于 | age<=18 |
5 | = | 等于 | deptno=10 |
6 | != | 不等于 | deptno != 10 |
7 | IN | 包含 | deptno IN(10,30,40) |
8 | IS NULL | 为空 | comm IS NULL |
9 | IS NOT NULL | 不为空 | comm IS NOT NULL |
10 | BETWEEN AND | 范围 | sal BETWEEN 20 AND 30 |
11 | LIKE | 模糊查询 | ename LIKE “A%” 以A开头 |
12 | REGEXP | 正则表达式 | ename REGEXP “[A-Za-z]{4} |
逻辑运算符
序号 | 表达式 | 意义 | 例子 |
1 | AND | 与关系 | age>18 AND sex =”男” |
2 | OR | 或关系 | empno =8000 OR deptno =20 |
3 | NOT | 非关系 | NOT deptno = 20 |
4 | XOR | 异或 | age>18 XOR sex=”男” |
异或: 只要两端表达式一个为true 一个为false 则结果为true
二级制按位运算
二进制位运算的实质是将参与运算的两个操作数,按对应的二进制数逐位进行逻辑运算
例: SELECT 2&7 结果为0011
按位运算符
序号 | 表达式 | 意义 | 例子 |
1 | & | 位与关系 | 3&7 |
2 | | | 位或关系 | 3|7 |
3 | ~ | 位取反 | ~10 |
4 | ^ | 位异或 | 3^7 |
5 | << | 左移 | 10<<1 |
6 | >> | 右移 | 10>>1 最右侧移除,左侧补0 |
WHERE子句的注意事项
WHERE子句中,条件执行的顺序是从左向右的。所以我们应该把索引条件,或者筛选掉记录最多的条件写在最左侧。
各种子句的执行顺序
FROM-->WHERE-->SELECT-->ORDER BY-->LIMIT
数据库的高级查询
数据统计分析操作
聚合函数
什么是聚合函数
聚合函数在数据的查询中,应用十分广泛。聚合函数可以对数据求和、求最大值和最小值、求平均值等等。
例:求公司员工的平均收入:
SELECT AVG(sal + IFNULL(comm,0) FROM t_emp;
SUN函数
SUN函数用于求和,只能用于数字类型,字符类型的统计结果为0,日期类型的统计结果是毫秒数相加。
例:
SELECT SUM(sal)
FROM t_emp
WHERE deptno IN(10,20);
MAX函数
MAX函数用于获得非空值得最大值
SELECT MAX(sal) FROM t_emp;
例: 查询部门10和20中,月收入最高的员工
SELECT MAX(sal + IFNULL(comm,0))
FROM t_emp
WHERE deptno IN(10,20);
MIN函数
MIN函数用于获得非空值得最小值
与MAX相似
AVG函数
AVG函数用于获得非空值的平均值,非数字数据统计结果为0。
SELECT AVG(sal + IFNULL(comm,0)) AS avg
FROM t_emp;
COUNT函数
COUNT(*)用于获得包含空值的记录数,COUNT(列名)用于获得包含非空值得记录数。
SELECT COUNT(*) FROM t_emp;
SELECT COUNT(comm) FROM t_emp;
注意:聚合函数不能出现在WHERE语句中
分组查询
为什么要分组
默认情况下汇总函数是对全表范围内的数据做统计
GROUP BY 子句的作用是通过一定的规则将一个数据集划分为若干个小的区域,然后针对每个小区域分别进行数据汇总处理
SELECT deptno, AVG(sal)
FROM t_emp
GROUP BY deptno
逐级分组
数据库支持多列分组条件,执行时逐级分组。
查找每个部门里,每种职位的人员数量和平均底薪。
SELECT deptno,job,COUNT(*),AVG(sal)
FROM t_emp GROUP BY deptno,job
ORDER BY deptno;
对SELECT子句的要求
查询语句中如果含有GROUP BY子句,那么SELECT子句中的内容就必须遵守规定:SELECT子句中可以包含聚合函数,或者GROUP BY子句的分组列,其余内容均不可以出现在SELECT字句中。
对分组结果集再次做汇总计算
SELECT deptno,COUNT(*),AVG(sal),MAX(sal),MIN(sal)
FROM t_emp GROUP BY deptno WITH ROLLUP; #再次汇总,
结果
GROUP _ CONCAT函数
GROUP_CONCAT函数可以把分组查询中的某个字段拼接成一个字符串
例子: 查询每个部门内底薪超过2000元的人数和员工姓名:
SELECT deptno, COUNT(*),GROUP_CONCAT(ename)
FROM t_emp
WHERE sal>=2000 GROUP BY deptno;
各种子句的执行顺序
FROM-->WHERE-->GROUP BY-->SELECT-->OREDER BY -->LIMIT
HAVING子句
分组查询遇到的困难?
查询部门平均底薪超过两千元的部门编号
HAVING子句不能独立存在,依赖于GROUP BY子句
SELECT deptno
FROM t_emp
GROUP BY deptno HAVING AVG(sal)>=2000;
HAVING 中可以加限制条件 聚合函数例如AVG、MIN等
HAVING子句的用途
跟WHERE子句差不多,但是WHERE优先级比GROUP BY高,而HAVING子句优先级比GROUP BY低。
查询每个部门中,1982年以后入职的员工超过2个部门编号:
SELECT deptno,COUNT(*)
FROM t_emp
WHERE hiredate>="1982-01-01"
GROUP BY deptno HAVING COUNT(*)>=2;
HAVING子句的特殊用法
按照数字1分组,MySQL会依据SELECT子句中的列进行分组,HAVING子句也可以正常使用
如:SELECT deptno,COUNT(*) FROM t_emp
GROUP BY 1; #意思是用SELECT语句中的第一个字段进行分组,也就是deptno分组
多表连接查询
从多张表中提取数据
从多张表中提取数据,必须指定关联的条件。如果不定义关联条件就会出现无条件连接,两张表的数据会交叉连接,产生笛卡尔积。
SELECT empno, ename, dname
FROM t_emp JOIN t_dept; #会产生笛卡尔积
规定了连接条件的表连接语句,就不会出现笛卡尔积
SELECT empno, ename, 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 连接条件;
内连接数据表不一定必须要有同名字段,只要字段之间符合逻辑关系就可以:
例:查询每个员工工号、姓名、部门名称、底薪、职位、工资等级?
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
#查询与SCOOT相同部门的员工都有谁 用到了子查询 但是效率很低
SELECT ename
FROM t_emp
WHERE deptno=(SELECT deptno FROM t_emp WHERE ename="SCOTT") AND ename != "SCOOT" ;
#表连接可以连接两个相同的表
SELECT e2.ename
FROM t_emp e1 JOIN t_emp e2 ON e1.deptno=e2.deptno
WHERE e1.ename="SCOTT" AND e2.ename!="SCOTT";
#查询底薪超过公司平均底薪的员工信息 把查询的结果集也当做一张表
SELECT e1.ename,e1.sal
FROM t_emp e1 JOIN
(SELECT AVG(sal) avg FROM t_emp) e2 ON e1.sal>=e2.avg ;
#查询RESEARCH部门的人数、最高底薪、最低底薪、平均底薪、平均工龄
SELECT COUNT(*) ,MAX(e.sal),MIN(e.sal),AVG(e.sal),AVG(DATEDIFF(NOW(),e.hiredate)/365) AS "平均工龄"
FROM t_emp e JOIN t_dept d ON e.deptno=d.deptno
WHERE d.dname="RESEARCH"
#查询每种职业的最高工资、最低工资、平均工资、最高工资等级和最低工资等级
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 job;
#查询每个底薪超过部门平均底薪的员工信息
SELECT e1.empno,e1.ename,e1.job,e1.deptno,e1.sal
FROM t_emp e1 JOIN (SELECT AVG(sal) avg,deptno FROM t_emp GROUP BY deptno) e2 ON e1.sal>=e2.avg AND e1.deptno=e2.deptno;
外连接
为什么使用外连接
如果说陈浩是一名临时人员,没有固定的部门编制,那么我们想查询每名员工和他的部门名称,用内连接就会漏掉陈浩,所以要引入外连接的语法才能解决这个问题
外连接简介
外连接与内连接的区别在于,除了符合条件的记录外,结果集中还会保留不符合条件的记录
SELECT...
FROM表1
LEFT JOIN 表2 ON条件 #LEFT JOIN左外连接(保留左表所有记录) JOIN内连接
左连接和右连接
左连接就是保留左表所有的记录,与右表左连接。如果右表有符合条件的记录就与左表连接。
如果右表没有符合条件的记录,就用NULL与左表连接。右外连接也是如此
例题:
#查询每个部门的名称和部门的人数
SELECT d.dname,COUNT(e.ename)
FROM t_dept d LEFT JOIN t_emp e ON d.deptno=e.deptno
GROUP BY d.dname;
UNION关键字可以将多个查询语句的结果集进行合并 并集
(SELECT d.dname,COUNT(e.ename)
FROM t_dept d LEFT JOIN t_emp e ON d.deptno=e.deptno
GROUP BY d.dname) UNION
(SELECT d.dname,COUNT(e.ename)
FROM t_dept d RIGHT JOIN t_emp e ON e.deptno=d.deptno
GROUP BY d.dname);
#查询每名员工的编号、姓名、部门、月薪、工资等级、工龄、上司编号、上司姓名、上司部门
SELECT e.empno,e.ename,d.dname,(e.sal+IFNULL(e.comm,0)),s.grade,FLOOR(DATEDIFF(NOW(),e.hiredate)/365),e.mgr,t.`上司姓名`,t.`上司部门`
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的优先级大于外连接内连接
子查询(查询效率低下)
子查询简介
子查询是一种查询中嵌套查询的语句
查询底薪超过公司平均底薪的员工的信息
子查询可以写在三个地方,WHERE子句、FROM子句、SELECT子句,但是只有FROM子句查询时最可取的。
WHERE子查询 查询很多次
这种子查询最简单,最容易理解,但是效率很低
在WHERE中操作
#查询底薪超过公司平均底薪的员工的信息
SELECT empno,ename
FROM t_emp
WHERE sal>=(SELECT AVG(sal) FROM t_emp);
FROM子查询 会查询一次
这种子查询只会执行一次,所以效率很高
SELECT e.empno,ename
FROM t_emp e,(SELECT AVG(sal) avg FROM t_emp) t
WHERE sal>=t.avg;
SELECT子查询
这种子查询每输出一条记录的时候都要执行一次,查询效率非常低
SELECT e.empno,e.ename,
(SELECT dname FROM t_dept WHERE deptno = e.deptno) FROM t_emp e;
单行子查询和多行子查询
单行子查询的结果只有一条记录,多行子查询结果集有多行记录
多行子查询只能出现在WHERE子句和FROM字句中
WHERE子句中的多行子查询
WHERE子句中,可以使用IN ,ALL,ANY,EXISTS关键字来处理多行表达式结果集的条件判断
ALL 比结果任意都高
#比FORD和MARTIN底薪都高的员工信息
SELECT ename,sal
FROM t_emp
WHERE sal>=ALL
(SELECT sal
FROM t_emp
WHERE ename IN("FORD","MARTIN"));
ANY是比其中之一高就行
SELECT ename,sal
FROM t_emp
WHERE sal>=ANY
(SELECT sal
FROM t_emp
WHERE ename IN("FORD","MARTIN"))AND ename NOT IN("FORD","MARTIN");
EXISTS关键字
EXISTS关键字是把原来在子查询之外的条件判断,写到了子查询的里面 判断WHERE后面是否为空
#查询员工登记是3级或4级的员工信息
SELECT ename
FROM t_emp
WHERE EXISTS(SELECT*
FROM t_salgrade
WHERE sal BETWEEN losal AND hisal
AND grade IN (3,4)
);
MySQL对数据库的基本操作
数据添加
INSERT语句
INSERT语句可以向数据表中写入记录,可以是一条记录,也可以是多条记录。
VALUES(值1,值2...);
VALUES(值1,值2,..),(值1,值2..);
例子:
#向技术部添加一条员工记录
INSERT INTO t_emp
(empno,ename,job,mgr,hiredate,sal,comm,deptno)
VALUES(8001,"李四","SALEMAN",8000,"1999-12-24",3000,NULL,(SELECT deptno FROM t_dept WHERE dname="技术部"));
INSERT方言语句
MySQL的INSERT语句还有一种方言语句
INSERT INTO 表名 SET 字段1=值1,字段2=值2....;
IGNORE关键字
IGNORE关键字会让INSERT只插入数据库中不存在的记录
INSERT[INGORE] INTO 表名...;
例子:
INSERT IGNORE INTO t_dept(deptno,dname,loc)
VALUES(40,”技术部”,”北京”);
数据修改
UPDATE语句
UPDATE语句用于修改表的记录
UPDATE [IGNORE] 表名
SET 字段1=值1,字段2=值2,...
[WHERE 条件1....]
[ORDER BY...]
[LIMIT...]; # 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;
#把10部门中,工龄超过20年的员工,底薪加200元
UPDATE t_emp
SET sal=sal+200
WHERE deptno=10 AND DATEDIFF(NOW(),hiredate)/365>=20;
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,e.job="ANALYST",d.loc="北京"
WHERE e.ename="ALLEN" AND d.dname="RESEARCH";
UPDATE语句的表连接既可以是内连接,也可以是外连接
UPDATE 表1 [LEFT|RIGHT] JOIN 表2 ON 条件 SET 字段1=值1,字段2=值2..;
数据删除
DELECT语句
DELECT 语句用于删除记录,语法如下:
DELECT [IGNORE] FROM 表名
[WHERE 条件1,条件2,...]
[ORDER BY...]
[LIMIT...];
练习:
#删除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;
DELECT表连接
因为相关子查询效率非常低,所以我们可以利用表连接的方式来改造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 ON e.deptno=d.deptno
WHERE d.dname="SALES";
#删除每个低于部门平均底薪的员工记录
DELETE e
FROM t_emp e JOIN(SELECT deptno,AVG(sal) AS avg FROM t_emp GROUP BY deptno) t
ON e.deptno=t.deptno
WHERE e.sal<t.avg;
#删除员工KING 和他的直接下属的员工记录,用表连接实现
DELETE e1,e2
FROM t_emp e1 JOIN t_emp e2 ON e2.mgr=e1.empno
WHERE e1.ename="KING";
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基本函数的使用
MySQL的函数
像编程语言利用函数封装业务功能一样,数据库也把一些复杂的功能封装到函数里,供使用者调用。
数字函数
函数 | 功能 | 用例 |
ABS | 绝对值 | ABS(-100) |
ROUND | 四舍五入 | ROUND(4.62 |
FLOOR | 强制舍位到最近的整数 | FLOOR(9.9) |
CEIL | 强制进位到最近的整数 | CEIL(3.2) |
POWER | 幂函数 | POWER(2,3) |
LOG | 对数函数 | LOG(7,3) |
LN | 对数函数 | IN(10) |
函数 | 功能 | 用例 |
SQRT | 开平方 | SQRT(9) |
PI | 圆周率 | PI() |
SIN | 三角函数 | SIN(1) #1弧度 |
COS | 三角函数 | COS(1) |
TAN | 三角函数 | TAN(1) |
COT | 三角函数 | COT(1) |
RADIANS | 角度转换弧度 | RADIANS(30) |
DEGREES | 弧度转换角度 | DEGREES(1) |
字符函数
函数 | 功能 | 用例 |
LOWER | 转换小写字符 | LOWER(ENAME) |
UPPER | 转换大写字符 | UPPER(ename) |
LENGTH | 字符数量 | LENGTH(ename) |
CONCAT | 连接字符串 | CONCAT(sal,”$”) |
INSTR | 字符出现的位置 | SELECT INSTR("ename","a"); |
INSERT | 插入/替换字符 | INSERT(“你好”,1,0,”先生”)#1第一个字符串 0 是插入. |
REPLACE | 替换字符 | REPLACE(“你好先生”,”先生”,”女士”) |
函数 | 功能 | 用例 |
SUBSTR | 截取字符串 | SUBSTR(“你好世界”,3,4) |
SUBSTRING | 截取字符串 | SUBSTRING(“你好世界”,3,2) |
LPAD | 左侧填充字符 | LPAD(“hello”,10,”*”) #一共填充10个字符 |
RPAD | 右侧填充字符 | RPAD(“hello”,10,”*”) |
TRIM | 去尾首尾空格 | TRIM(“你好先生”) |
日期函数
获取系统时间的函数
NOW()函数能获得系统日期和时间,格式yyyy-MM-dd hh:mm:ss
CURDATE()函数能获得当前系统日期,格式yyyy-MM-dd
CURTIME()函数能获得当前系统日趋,格式hh:mm:ss
日期格式化函数
DATE_FORMAT()函数用于格式化日期,返回用户想要的日期格式。
DATE_FORMAT(日期,表达式)
占位符 | 作用 | 占位符 | 作用 |
%Y | 年份 | %m | 月份 |
%d | 日期 | %w | 星期(数字) |
%W | 星期(名称) | %j | 本年第几天 |
%U | 本年第几周 | %H | 小时(24) |
%h | 小时(12) | %i | 分钟 |
%s | 秒 | %r | 时间(12) |
%T | 时间(24) |
练习:
#利用日期函数,查询1981年上半年入职的员工有多少人
SELECT COUNT(*)
FROM t_emp
WHERE DATEDIFF("1981-07-01",hiredate) BETWEEN 1 AND 183;
SELECT COUNT(*)
FROM t_emp
WHERE DATE_FORMAT(hiredate,"%Y") =1981 AND
DATE_FORMAT(hiredate,"%m")<=6; #格式可以%y/%m/%d
日期函数的注意事项
MySQL数据库里面,两个日期不能直接加减,日期也不能与数字加减
日期偏移计算
DATE_ADD()函数可以实现日期的偏移计算,而且时间单位很灵活
DATE)ADD(日期,INTERVAL 偏移量 时间单位)
例:
SELECT DATE_ADD(NOW(),INTERVAL 10 DAY); #DAY YEAR MONTH MINUTE等
#6个月03天之前
SELECT DATE_FORMAT(
DATE_ADD(DATE_ADD(NOW(),INTERVAL -6 MONTH),INTERVAL -3 DAY),"%Y/%m/%d");
计算日期之间相隔的天数
DATEDIFF()函数用来计算两个日期之间相差多少天
DATEDIFF(日期,日期)
条件函数
SQL语句中可以利用条件函数来实现编程语言例的条件判断
IFNULL(表达式,值)
IF(表达式,值1,值2)
练习:
#SALES部门发放礼品A,其余部门发放礼品B,打印每名员工获得的礼品
SELECT e.empno,e.ename,d.dname,
IF(d.dname="SALES","礼品A","礼品B") AS "获得礼品"
FROM t_emp e JOIN t_dept 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,d.dname,
CASE
WHEN d.dname="SALES" THEN "p1"
WHEN d.dname="ACCOUNTING" THEN "P2"
WHEN d.dname="RESEARCH" THEN "P3"
END AS "旅游目的地"
FROM t_emp e JOIN t_dept d ON e.deptno=d.deptno;
#修改表中的sal
UPDATE t_emp e LEFT JOIN t_dept d ON e.deptno=d.deptno
LEFT JOIN (SELECT deptno,AVG(sal) avg FROM t_emp GROUP 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="SALES" AND DATEDIFF(NOW(),e.hiredate)/365<=20 THEN e.sal*1.05
WHEN d.dname="ACCOUNTING" THEN e.sal+300
WHEN d.dname="RESEARCH" AND e.sal<t.avg
THEN e.sal+200
WHEN E.deptno IS NULL THEN e.sal+100
ELSE e.sal
END
);
MySQL的综合应用
数据库事务机制
避免写入直接操作数据文件
如果数据的写入直接操作数据文件是非常危险的事情
利用日志来实现间接写入
MySQL总共有5种日志,其中只有redo日期和undo日志与事务有关
undo和redo日志
MySQL拷贝数据到undo日志中,增删改查的操作记录在redo操作里面,最后redo日志同步到数据库文件中
事务机制(Transaction)
RDBMS = SQL语句 + 事务(ACID)
事务是一个或多个SQL语句组成的整体,要么全部执行成功,要么全部执行失败
事务案例
把10部门中MANGER员工调往20部门,其他岗位的员工调往30部门,然后删除10部门
事务: 1.开启事务 2.UPDATE语句 3.DELETE语句 4.提交事务
管理事务
默认情况下,MySQL执行每条SQL语句都会自动开启和提交事务
为了让多条SQL语句纳入到一个事务之下,可以手动管理事务
START TRANSACTION;
SQL语句
[COMMIT |ROLLBACK]; #提交|回滚 提交之后数据才发生变化 不然一直在查改的是redo文件
事务的ACID属性
原子性、一致性、隔离性、持久性
事务的原子性
一个事务中的所有操作要么全部成功,要么全部失败。事务执行后,不允许停留在中间某个状态。
事务的一致性
不管在任何给定的时间、并发事务有多少,事务必须保证运行结果的一致性。
事务的隔离性
隔离性要求事务不受其他并发事务的影响,如同在给定的时间内,该事务是数据库唯一运行的事务。
默认情况下A事务,只能看到日志中该事务的相关数据
事务的持久性
事务一旦提交,结果便是永久性的。即使发生宕机,断电,仍然可以依靠事务日志完成数据的持久化。
事务的四个隔离级别
序号 | 隔离级别 | 功能 |
1 | read uncommitted | 读取未提交数据 |
2 | read committed | 读取已提交数据 |
3 | repeatable read | 重复读取 |
3 | serializable | 序列化 |
修改事务隔离级别
read uncommitted 代表可以读取其他事务未提交的数据 (抢票机制)
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
在银行转账情况下,隔离级别应该是读取已提交数据
read committed 代表只能读取其他事务提交的数据
SET SESSION TRANSACTION ISOLACTION LEVEL READ COMMITTED;
可重复读取情况是在买东西后商品涨价的情况,在事务执行过程中,不会受其他事务的影响。
repeatable read (默认级别)
SET SESSION TRANSACTION ISOLACTION LEVEL REPEATABLE READ;
要先存到undo日志中 select
事务的序列化(并发性会下降)
由于事务并发执行所带来的的各种问题,前三种隔离级别只适用于在某些业务场景中,但是序列化的隔离性,让事务逐一执行,就不会产生上述问题了。
SET SESSION TRANSCATION ISOLACTION LEVEL SERIALIZABLE;
SQL文件的导出与导入
数据导出,导出的纯粹是业务数据
数据备份,备份的是数据文件、日志文件、索引文件等等
全量备份->增量备份1->增量备份2
数据导出的分类
SQL文档、文本文档 一般数据比较少 SQL文档 数据多的话 导出文本文档
导出SQL文件
mysqldump用来把业务数据导出成SQL文件,其中也包括了表结构
mysqldump -uroot -p [no-data] 逻辑库 > 路径 #导出表结构和文件
导入SQL文件
source命令用于导入SQL文件,包括创建数据库表,写入记录等
USE demo;
SOURCE backup.sql;
TXT文档的导入与导出
在客户端比较简单。