2023/07/10：夏令营week1记录:mysql笔记

MySQL课堂笔记

0.标题暂定

关于语句大小写

MySQL 在 windows 下是不区分大小写的，将 script 文件导入 MySQL 后表名也会自动转化为小写，结果再 想要将数据库导出放到 linux 服
务器中使用时就出错了。因为在 linux 下表名区分大小写而找不到表，查了很多都是说在 linux 下更改 MySQL 的设置使其也不区分大小写，但是
有没有办法反过来让 windows 下大小写敏感呢。其实方法是一样的，相应的更改 windows 中 MySQL 的设置就行了。

具体操作：
在 MySQL 的配置文件 my.ini 中增加一行：
lower_case_table_names = 0
其中 0：区分大小写，1：不区分大小写
MySQL 在 Linux 下数据库名、表名、列名、别名大小写规则是这样的：
1、数据库名与表名是严格区分大小写的；
2、表的别名是严格区分大小写的；
3、列名与列的别名在所有的情况下均是忽略大小写的；
4、变量名也是严格区分大小写的； MySQL 在 Windows 下都不区分大小写

使用source + 地址显示“Failed to open file ‘…’, error: 2”的可能原因

1.未先创建并”use”数据库；
2.在windows中，路径不能包含空格，中文字符，需使用“/”连接路径，且最好选择绝对路径；不可使用“.”以及“<”和“>”
3.该语句结尾不需分号
原文https://sebhastian.com/mysql-failed-to-open-file-error-2/
https://philip.yurchuk.com/software/mysql-failed-to-open-file-error-2-and-22-on-windows/

1.查看MYSQL版本

$ mysql --version
$ mysql -V

2.创建及使用

$ create database/table [name]
$ use [name]

3.查询使用的数据库

$ select database()

4.查询数据库版本

$ select version()

5.终止输入语句

键入 \c

6.退出mysql

\q QUIT\EXIT
也可CTRL+C

7.导入数据库

（创建并使用数据库后）

$ source [文件路径]（无需;）

8.查看

$ 表的结构 desc [table name]
$ 数据库 show databases
$ 查看表创建语句 show create table [table name]

9.简单查询

$ select [column name],[column name] from [table name]
$ select * from [table name]

10.别名

可以采用 as 关键字重命名表字段，其实 as 也可以省略，如：

$ select empno "员工编号", ename "员工姓名", sal*12 （as）"年薪" from emp

11.条件查询

条件查询需要用到 where 语句，where 必须放到 from 语句表的后面
支持如下运算符
运算符 说明
= 等于
<>或!= 不等于
< 小于
<= 小于等于
> 大于
>= 大于等于
between …and …. 两个值之间,等同于 >= and <=
is null 为 null（is not null 不为空）
and 并且
or 或者
in 包含，相当于多个 or（not in 不在这个范围中）如:select * from emp where job in (‘manager’,’salesman’);
not not 可以取非，主要用在 is 或 in 中
like like称为模糊查询，支持%或下划线匹配
%匹配任意个字符
下划线，一个下划线只匹配一个字符
例如：select * from emp where ename like ‘_A%’;

12.排序

排序采用 order by 子句，order by 后面跟上排序字段，排序字段可以放多个，多个采用逗号间隔，order by 默认采用升
序，如果存在 where 子句那么 order by 必须放到 where 语句的后面

$ 升序：order by [column name] asc
$ 降序：order by [column name] desc

13.数据处理函数/单行处理函数

Lower 转换小写
upper 转换大写 如：upper(‘manager’)
substr 取子串（substr(被截取的字符串//[table name],起始下标,截取的长度)）
length 取长度 如：length([table name])
trim 去空格
str_to_date 将字符串转换成日期
如：select * from emp where HIREDATE=’1981-02-20’；
（第二种方法，将字符串转换成 date 类型）
select * from emp where HIREDATE=str_to_date(‘1981-02-20’,’%Y-%m-%d’);
select * from emp where HIREDATE=str_to_date(‘02-20-1981’,’%m-%d-%Y’);
date_format 格式化日期 如：select empno, ename, date_format(hiredate, ‘%Y-%m-%d %H:%i:%s’) as hiredate from emp
format 设置千分位 如：select empno, ename, Format(sal, 0) from emp;
round 四舍五入 round(123.123)
rand() 生成随机数
Ifnull 可以将 null 转换成一个具体值
如：ifnull(comm,0)
当comm为null替换为0

14.日期格式

now() 获得当前时间

格式的说明

%Y：代表 4 位的年份
%y：代表 2 位的年份
%m：代表月, 格式为(01……12)
%c：代表月, 格式为(1……12)
%H：代表小时,格式为(00……23)
%h：代表小时,格式为(01……12)
%i：代表分钟, 格式为(00……59)
%r：代表 时间,格式为 12 小时(hh:mm:ss [AP]M)
%T：代表 时间,格式为 24 小时(hh:mm:ss)
%S：代表 秒,格式为(00……59)
%s：代表 秒,格式为(00……59)

15.case … when … then …..else …end

例如：
如果 job 为 MANAGERG 薪水上涨 10%,如果 job 为 SALESMAN 工资上涨 50%
select empno, ename, job, sal, case job when ‘MANAGER’ then sal1.1 when ‘SALESMAN’ then sal1.5 end as newsal from emp;

16.分组函数/聚合函数/多行处理函数

未进行分组时整张表默认为一组
count 取得记录数 例如:count([table name]或*) *为一整行一个数据，这里相当于统计行数
sum 求和 注意：当例如select sum(sal+comm) from emp中comm中出现null,需替换null
avg 取平均
max 取最大的数
min 取最小的数

注意：

1.分组函数自动忽略空值(null)，不需要手动的加 where 条件排除空值。
select count(*) from emp where xxx; 符合条件的所有记录总数。(一行数据，只要不全为null则count++，当然不存在全为null的数据，有一位不是null则数据有效)
select count(comm) from emp; comm 这个字段中不为空的元素总数。
2.分组函数不能直接使用在 where 关键字后面。
错误示范： mysql> select ename,sal from emp where sal > avg(sal);
原因见分组查询
3.所有分组函数可组合使用

17.分组查询（重点）

什么是分组

在实际的应用中，可能有这样的需求，需要先进行分组，然后对每一组的数据进行操作。

关键字执行顺序

1. from
2. where
3. group by
4. select
5. order by

注意事项

1.分组函数不能在where后：因为分组函数在使用的时候必须先分组之后才能使用，where执行的时候，还没有分组。所以where后面不能出现分组函数。
2.select sum(sal) from emp; 这个没有分组，为啥sum()函数可以用呢？ 因为select在group by之后执行。
3.在一条select语句当中，如果有group by语句的话，select后面只能跟：参加分组的字段，以及分组函数。其它的一律不能跟。
4.可多个字段联合分组

函数

1.group by

例：select job,deptno,sum(sal) from emp group by job,deptno;

2.having

对分组数据再进行过滤需要使用 having 子句
优先考虑where过滤
例：select job, avg(sal) from emp group by job having avg(sal) >2000;
顺序：根据条件查询数据 分组 采用 having 过滤，取得正确的数据

18.distinct

把查询结果去除重复记录
distinct只能出现在所有字段的最前方:
select ename,distinct job from emp为错误形式
允许该形式：
count(distinct job)

19.连接查询

也可以叫跨表查询，需要关联多个表进行查询

连接分类

可分为：
内连接： （无主次，平等）
等值连接 例：select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno;
非等值连接 例：select e.ename, e.sal, s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal; // 条件不是一个等量关系，称为非等值连接。
自连接（技巧：视为多张表）例：select a.ename as ‘员工名’, b.ename as ‘领导名’ from emp a join emp b on a.mgr = b.empno; //员工的领导编号 = 领导的员工编号
外连接： （可在join前加outer,如right outer join ）
左外连接（左连接）
右外连接（右连接）right代表什么：表示将join关键字右边的这张表看成主表，主要是为了将
这张表的数据全部查询出来，捎带着关联查询左边的表。
在外连接当中，两张表连接，产生了主次关系。
全连接

SQL92与SQLl99

SQL92语法：

select 
    e.ename,d.dname
 from
     emp e, dept d
where
    e.deptno = d.deptno;
sql92的缺点：结构不清晰，表的连接条件，和后期进一步筛选的条件，都放到了where后面。

SQL99语法：

     select 
         ...
     from
        a
    (inner) join    //inner可以省略（带着inner可读性更好！！！一眼就能看出来是内连接）
        b
     on
        a和b的连接条件
    where
        筛选条件
sql99优点：表连接的条件是独立的，连接之后，如果还需要进一步筛选，再往后继续添加where

20.多张表的连接查询

语法：
select
…
from
a
join
b
on
a和b的连接条件
join
c
on
a和c的连接条件
right join
d
on
a和d的连接条件
b,c,d并列
一条SQL中内连接和外连接可以混合。都可以出现

21.子查询

子查询就是嵌套的 select 语句，可以理解为子查询是一张表

where子查询

格式：
select
..(select).
from
..(select).
where
..(select).
例：
第一步：查询最低工资是多少
select min(sal) from emp;
结果实例：
+———-+
| min(sal) |
+———-+
| 800.00 |
+———-+
第二步：找出>800的
select ename,sal from emp where sal > 800;
第三步：合并
select ename,sal from emp where sal > (select min(sal) from emp);

from子查询

注意：from后面的子查询，可以将子查询的查询结果当做一张临时表。
例：找出每个岗位的平均工资的薪资等级。
第一步：找出每个岗位的平均工资（按照岗位分组求平均值）
select job,avg(sal) from emp group by job;
+———–+————-+
| job | avgsal |
+———–+————-+ t表
| ANALYST | 3000.000000 |
…
+———–+————-+
第二步：克服心理障碍，把以上的查询结果就当做一张真实存在的表t。
select * from salgrade;
+——-+——-+——-+
| GRADE | LOSAL | HISAL |
+——-+——-+——-+ s表
| 1 | 700 | 1200 |
| 2 | 1201 | 1400 |
…
t表和s表进行表连接，条件：t表avg(sal) between s.losal and s.hisal;
select
t.*, s.grade
from
(select job,avg(sal) as avgsal from emp group by job) t
join
salgrade s
on
t.avgsal between s.losal and s.hisal; // 技巧：avg(sal) as avgsal,否则t.avg(sal)报错

select子查询（非重点）

对于select后面的子查询来说，这个子查询只能一次返回1条结果(就是括号里面那个)
例：
select e.ename,e.deptno,(select d.dname from dept d where e.deptno = d.deptno) as dname from emp e;

22.union

union可以减少匹配的次数。在减少匹配次数的情况下，还可以完成两个结果集的拼接。
例：
select ename,job from emp where job = ‘MANAGER’
union
select ename,job from emp where job = ‘SALESMAN’;

注意事项：

1.union在进行结果集合并的时候，要求两个结果集的列数相同（查询字段对应个数相同）。
2.不同于oracle，mysql允许结果集合并时列和列的数据类型不一致。

23.limit

将查询结果集的一部分取出来。通常使用在分页查询当中。分页的作用是为了提高用户的体验。

使用

完整用法：limit startIndex, length
startIndex是起始下标，length是长度。
起始下标从0开始。
缺省用法：limit 5; 这是取前5
limit 1,2; 从第二条（下标为1）开始取两条数据
注意：mysql当中limit在order by之后执行

分页

每页显示3条记录
第1页：limit 0,3 [0 1 2]
第2页：limit 3,3 [3 4 5]
…
每页显示pageSize条记录
第pageNo页：limit (pageNo - 1) * pageSize , pageSize
public static void main(String[] args){
// 用户提交过来一个页码，以及每页显示的记录条数
int pageNo = 5; //第5页
int pageSize = 10; //每页显示10条

    int startIndex = (pageNo - 1) * pageSize;
    String sql = "select ...limit " + startIndex + ", " + pageSize;
}

记公式：limit (pageNo-1)*pageSize , pageSize

24.语句总结与运行顺序

总：select … from … where … group by … having … order by … limit …
顺序：1.from 2.where 3.group by 4.having 5.select 6.order by 7.limit..

25.建表

格式：

create table tableName(
columnName dataType(length),
………………..
columnName dataType(length)
);

MySQL常用数据类型

类型
Char(长度) 定长字符串，存储空间大小固定，适合作为主键或外键
Varchar(长度) 变长字符串，存储空间等于实际数据空间
double(有效数字位数，小数位) 数值型
Float(有效数字位数，小数位) 数值型
Int(长度) 整型
bigint(长度) 长整型
Date 日期型
BLOB Binary Large OBject（二进制大对象）
CLOB Character Large OBject（字符大对象）

修改

添加修改字段：alter table [table name] add [column name] datatype;
alter table [table name] modify [column name] datatype ;
alter table [table name] drop [column name];
表复制：create table … as …
删除表：drop table if [table name];// 如果这张表存在的话，删除
插入数据：insert into 表名(字段名1,字段名2,字段名3…) values(值1,值2,值3);字段名和值数量数据类型对应，没有给其它字段指定值的话，默认值是NULL。若省略前面的字段名，等于都写上，所以值也要都写上

插入日期注意格式转换

str_to_date(‘字符串日期’, ‘日期格式’)：将字符串varchar类型转换成date类型
date_format(日期类型数据, ‘日期格式’)：将date类型转换成具有一定格式的varchar字符串类型。
命名规范：所有的标识符都是全部小写，单词和单词之间使用下划线进行衔接。
mysql默认的日期格式：’%Y-%m-%d’
date是短日期：只包括年月日信息。默认：%Y-%m-%d
datetime是长日期：包括年月日时分秒信息。默认:%Y-%m-%d %h:%i:%s

26.表的修改与删除

修改：update 表名 set 字段名称 1=需要修改的值 1, 字段名称 2=需要修改的值 2 where …
删除：Delete from 表名 where … 数据在硬盘上的真实存储空间不会被释放，可恢复
truncate table 表名; 不可恢复，快速

27.约束（重点）

目的：创建表的时候，给表中的字段加上一些约束保证表中的数据有效完整

类型：

非空约束：not null
唯一性约束: unique
主键约束: primary key （简称PK）
外键约束：foreign key（简称FK）
检查约束：check（mysql不支持，oracle支持）

非空约束

非空约束not null约束的字段不能为NULL。
例：create table t_vip(
id int,
name varchar(255) not null // not null只有列级约束，没有表级约束！
);

唯一性约束

唯一性约束unique约束的字段不能重复，但是可以为NULL。
例：create table t_vip(
id int,
name varchar(255) unique,
email varchar(255)
);

表级约束

需要给多个字段联合起来添加某一个约束的时候，需要使用表级约束。
例：create table t_vip(
id int,
name varchar(255),
email varchar(255),
unique(name,email)
)
在MySQL中，unique 和not null联合自动变成主键字段。例：name varchar(255) not null unique

列级约束

例：id int primary key

主键约束（primary key，简称PK）

主键字段：该字段上添加了主键约束，这样的字段叫做：主键字段
主键值：主键字段中的每一个值都叫做主键值，一般数字且定长
作用：主键值是每一行记录的唯一标识，相当于身份证号，任何一张表都应该有主键，没有主键，表无效
复合主键：primary key(id,name)多个字段联合起来做约束（不建议使用）
在mysql当中，有一种机制，可以帮助我们自动维护一个主键值
create table t_vip(
id int primary key auto_increment, //auto_increment表示自增，从1开始，以1递增！
name varchar(255)
);

外键约束

外键主要是维护表之间的关系的，主要是为了保证参照完整性，如果表中的某个字段为外键字段，那么该字段的值必须来源于参照的表的字段
存在外键的表就是子表，参照的表就是父表，所以存在一个父子关系，也就是主从关系
删除表的顺序：先删子，再删父；
创建表的顺序：先创建父，再创建子。
删除数据的顺序：先删子，再删父。
插入数据的顺序：先插入父，再插入子。
子表中的外键引用的父表中的某个字段，被引用的这个字段不一定是主键，但至少具有unique约束。
外键值可为null
例：constraint [table name] foreign key([column name]) references [table name]([column name])
更改删除父表数据，子表也会变动

约束的更改

删除：外键：alter table 表名 drop foreign key 外键字段;
主键：alter table 表名 drop primary key;
唯一：alter table 表名 drop index 字段名
添加： alter table 表名 add constraint 约束名 约束类型(字段名) [外键的引用]

28.存储引擎（了解）

存储引擎是MySQL中特有的一个术语，实际上存储引擎是一个表存储/组织数据的方式。不同的存储引擎，表存储数据的方式不同。
在创建表时，可使用 ENGINE 选项为 CREATE TABLE 语句显式指定存储引擎。未指定则默认
常用引擎：MyISAM InnoDB MEMORY

29.事务（重点）

一个事务其实就是一个完整的业务逻辑。是一个最小的工作单元。不可再分。
本质上事务就是批量的DML语句（insert、delete、update 对数据进行更改）同时成功，或者同时失败

事务工作过程

InnoDB存储引擎：提供一组用来记录事务性活动的日志文件
在事务的执行过程中，每一条DML的操作都会记录到“事务性活动的日志文件”中。在事务的执行过程中，我们可以提交事务，也可以回滚事务。
提交事务：清空事务性活动的日志文件，将数据全部彻底持久化到数据库表中。标志着事务的结束。并且是一种全部成功的结束。
回滚事务：将之前所有的DML操作全部撤销，并且清空事务性活动的日志文件。标志着事务的结束。并且是一种全部失败的结束。

事务执行

开始事务：start transaction（关闭DML自动提交，此句开始保持上一次提交点）;
提交事务：commit; 语句
回滚事务：rollback; 语句（回滚永远都是只能回滚到上一次的提交点）
默认每执行一条DML自动提交

事务特性

原子性：说明事务是最小的工作单元。不可再分。
一致性：所有事务要求，在同一个事务当中，所有操作必须同时成功，或者同时失败，以保证数据的一致性。
隔离性：A事务和B事务之间具有一定的隔离。教室A和教室B之间有一道墙，这道墙就是隔离性。A事务在操作一张表的时候，另一个事务B也操作这张表会那样
持久性：事务最终结束的一个保障。事务提交，就相当于将没有保存到硬盘上的数据保存到硬盘上

事务隔离性

4个级别
读未提交：read uncommitted（最低的隔离级别）事务A可以读取到事务B未提交的数据。
读已提交：read committed （数据库隔离级别默认从此开始）事务A只能读取到事务B提交之后的数据。数据真实
可重复读：repeatable read （mysql中默认）事务A开启之后，不管是多久，每一次在事务A中读取到的数据都是一致的。即使事务B将数据已经修改，并且提交了，事务A读取到的数据还是没有发生改变
序列化/串行化：serializable（最高的隔离级别）事务排队，不能并发（我操作你停） synchronized，线程同步（事务同步）将一个事务与其他事务完全地隔离。效率最低

一致性问题

脏读现象：读到脏数据（未提交的数据）
不可重复读：事务A开启之后，不管是多久，每一次在事务A中读取到的数据都是一致的。即使事务B将数据已经修改，并且提交了
幻像读：读到的数据可能不够绝对真实，即使事务B将数据已经修改，并且提交了，事务A读取到的数据还是没有发生改变

隔离级别与一致性问题关系

隔离级别 脏读取 不可重复读 幻像读
读未提交 可能 可能 可能
读已提交 不可能 可能 可能
可重复读 不可能 不可能 对InnoDB不可能
序列化 不可能 不可能 不可能

设置隔离级别

会话级：mysql> SET TRANSACTION ISOLATION …；
或：
mysql> SET SESSION TRANSACTION ISOLATION LEVEL …；
全局级：mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL …；

查看隔离级别

mysql> SELECT @@tx_isolation;或：
mysql> SELECT @@session.tx_isolation;
– 查看全局级的当前隔离级别：
mysql> SELECT @@global.tx_isolation;

30.索引

概念

索引是在数据库表的字段上添加的，是为了提高查询效率存在的一种机制。索引被用来快速找出在一个列上用一特定值的行。一张表的一个字段可以添加一个索引，当然，多个字段联合起来也可以添加索引。索引相当于一本书的目录，是为了缩小扫描范围而存在的一种机制。
MySQL在查询方面主要就是两种方式：第一种方式：全表扫描（无索引时的查找方式，效率较低）；第二种方式：根据索引检索。

原理

在mysql当中索引是一个B-Tree数据结构。
遵循左小又大原则存放。采用中序遍历方式遍历取数据。
底层是一个自平衡的二叉树
注意：1.任何数据库主键自动添加索引，mysql如果有unique约束也会自动创建
2.在任何数据库当中，任何一张表的任何一条记录在硬盘存储上都有一个硬盘的物理存储编号
3.在mysql当中，索引是一个单独的对象，在mysql当中都是一个树的形式存在。（自平衡二叉树：B-Tree）不同存储引擎形式不同

应用条件

1.表中该字段中的数据量庞大
2.经常被检索，经常出现在 where 子句中的字段
3.经常被 DML 操作的字段不建议添加索引（增删改后要重新排序）
4.索引需维护，不随意添加
5.唯一性比较弱的字段上添加索引用处不大。

索引应用

建立：create index 索引名 on 表名(字段名);
alter table 表名 add unique index 索引名 (列名);
删除：DROP INDEX 索引名 ON 表名
ALTER TABLE 表名 DROP INDEX 索引名
查看索引：show index from 表名;
通过扫描次数判断是否使用索引

explain语句

语法：explain select …
用来查看SQL语句执行效果

索引失效

1.模糊匹配当中以“%”开头 如：select * from emp where ename like ‘%T’;
2.使用or的时候其中一边有一个字段没有索引（union不会失效）
3.使用复合索引（两个字段，或者更多的字段联合起来添加一个索引）的时候，没有使用左侧的列查找
4.在where当中索引列参加了运算
5.在where当中索引列使用了函数
…
(没有直接匹配结果则失效？)

索引分类

单一索引：一个字段上添加索引。
复合索引：两个字段或者更多的字段上添加索引。
主键索引：主键上添加索引。
唯一性索引：具有unique约束的字段上添加索引。
…..

31.视图

视图（虚拟表）：站在不同的角度去看待同一份数据，是一种根据查询（也就是 SELECT 表达式）定义的数据库对象，用于获取想要看到和使用的局部数据。用来提高检索效率，隐藏表的实现细节（面向视图检索）

应用

创建：create view 视图名 as select …;（mysql 不支持子查询创建视图）
删除：drop view (if exists) 视图名 ;
修改：alter view 视图名 as select …;

作用

可以面向视图对象进行增删改查（看作一张表），对视图对象的增删改查，会导致原表被操作！（视图的特点：通过对视图的操作，会影响到原表数据。）方便，简化开发，利于维护

32.DBA

重点数据导入导出（数据备份）

导入

登录 MYSQL 数据库管理系统之后执行：source D:\ bjpowernode.sql

导出

导出整个数据库：在 windows 的 dos 命令窗口中执行：mysqldump bjpowernode>D:\bjpowernode.sql -uroot -p123
导出指定库下的指定表：在 windows 的 dos 命令窗口中执行：mysqldump bjpowernode emp> D:\ bjpowernode.sql -uroot –p123

33.数据库设计三范式

第一范式：有主键，具有原子性，字段不可分割
第二范式：完全依赖，没有部分依赖
第三范式：没有传递依赖

表设计总结

一对多：一对多，两张表，多的表加外键
多对多：多对多，三张表，关系表两个外键
一对一：一张表字段太多，太庞大就拆分表，外键唯一