本学习笔记对应：动力节点MySQL数据库入门教程，B站视频，菜鸟教程
目录
概述
mysql大小写不敏感
MySQL 是一个关系型数据库管理系统，由瑞典 MySQL AB 公司开发，目前属于 Oracle 公司。
MySQL特点
*MySQL 是开源的，目前隶属于 Oracle 旗下产品。
*MySQL 支持大型的数据库。可以处理拥有上千万条记录的大型数据库。
*MySQL 使用标准的 SQL 数据语言形式。
*MySQL 可以运行于多个系统上，并且支持多种语言。这些编程语言包括 C、C++、Python、Java、Perl、PHP、Eiffel、Ruby 和 Tcl 等。
*MySQL 支持大型数据库，支持 5000 万条记录的数据仓库，32 位系统表文件最大可支持 4GB，64 位系统支持最大的表文件为8TB。
*MySQL 是可以定制的，采用了 GPL 协议，你可以修改源码来开发自己的 MySQL 系统。
术语
数据查询语言(DQL-Data Query Language)
代表关键字:select
数据操纵语言(DML-Data Manipulation Language)
代表关键字:insert,delete,update
数据定义语言(DDL-Data Definition Language)
代表关键字:create ,drop,alter,
事务控制语言(TCL-Transactional Control Language)
代表关键字:commit ,rollback;
数据控制语言(DCL-Data Control Language)
代表关键字:grant,revoke.
字段：表的列
记录：表的行
常用命令
- show databases;
查看所有数据库 - create database 数据库名称;
创建数据库 - use 数据库名称;
使用数据库 - show tables;
查看当前数据库中的所有表 - desc 表名;
查看表的结构 - show create table 表名;
查看表的创建语句 - \c
终止正在编写的语句 - exit
退出mysql
单表查询
sql执行顺序
select 字段1 as 重命名1,字段2 as 重命名2 -----------5
…
from 表名1 as 表别名1,表名2 as 表别名2--------------1
…
where 条件1,条件2 ------------------------------------------2
…
group by 字段1,字段2 --------------------------------------3
…
having 条件1,条件2 -----------------------------------------4
…
order by 字段1 asc,字段2 desc -------------------------6
…
注意：
- distinct只能出现在所有字段的最前面
- select *全表查询
- as可省略
- 当字段为空值是条件需用is null或is not null
- 模糊查询like，%表示匹配任意字符，_表示匹配一个字符
- 分组函数(count、sum、avg、max、min)自动忽略空值。
- count(*)表示所有记录的个数，（包括空值）,
count(字段名)表示该字段不为空的记录的个数，
count（distinct 字段名）表示在字段不重复且不为空的记录的个数 - 在SQL语句中若有group by 语句，那么在select语句后面只能跟分组函数或参与分组的字段。
连接查询
内连接
内连接：假设A和B表进行连接，使用内连接的话，凡是A表和B表能够匹配上的记录查询出来，这就是内连接。AB两张表没有主副之分，两张表是平等的（忽略了null）。可分为等值连接、非等值连接、自连接。
语法：
select...
from 表1
inner join 表2//inner可省略
on 连接条件
where...
外连接
外连接：假设A和B表进行连接，使用外连接的话，AB两张表中有一张表是主表，一张表是副表，主要查询主表中的数据，捎带着查询副表，当副表中的数据没有和主表中的数据匹配上，副表自动模拟出NULL与之匹配。分为左连接（左边的表为主表）和右连接。
左连接：左连接以左面的表为准和右边的表比较，和左表相等的不相等都会显示出来，右表符合条件的显示,不符合条件的不显示
语法：
select ...
from 表1
left outer join 表2
on 连接条件
where...
分页查询
MySQL提供关键字limit可进行分页查询
语法：
limit startIndex, length
//startIndex表示起始位置，从0开始，0表示第一条数据。
//length表示取几个
limit在sql语句的末尾，是执行的最后一个环节
表及表中数据的操作
create建表
- 建空表：
create table t_student(
no bigint,
name varchar(255),
sex char(1),
classno varchar(255),
birth char(10) );
- 拷贝建表：
create table 表名 as select语句;
drop删表
drop table 表名; // 这个通用。
drop table if exists 表名; // oracle不支持这种写法。
insert向表中插入数据
- 直接插入
insert into t_student
(no,name,sex,classno,birth)
values
(1,'zhangsan','1','gaosan1ban');
- 拷贝插入
insert into dept1 select * from dept;
update修改表中的数据
update dept1
set loc = 'SHANGHAI', dname = 'RENSHIBU'
where deptno = 10
delete删除数据
delete from 表名 where 条件;
//注意：没有条件全部删除。
约束
非空约束(not null)：约束的字段不能为NULL
唯一约束(unique)：约束的字段不能重复
主键约束(primary key)：约束的字段既不能为NULL，也不能重复（简称PK）
外键约束(foreign key)：…（简称FK）
检查约束(check)：注意Oracle数据库有check约束，但是mysql没有，目前mysql不支持该约束。
非空约束
drop table if exists t_user;
create table t_user(
id int,
username varchar(255) not null,
password varchar(255)
);
唯一性约束
//case1列级约束
drop table if exists t_user;
create table t_user(
id int,
username varchar(255) unique // 列级约束
);
//case2表级约束
drop table if exists t_user;
create table t_user(
id int,
usercode varchar(255),
username varchar(255),
unique(usercode,username) // 多个字段联合起来添加1个约束unique 【表级约束】
);
注意：满足唯一性约束的字段值可以为NULL
主键约束
//case1列级约束
drop table if exists t_user;
create table t_user(
id int primary key auto_increment, // 列级约束,主键自增，从1开始
//oracel中自增为序列（sequence）对象
username varchar(255),
email varchar(255)
);
//case2表级约束
drop table if exists t_user;
create table t_user(
id int,
username varchar(255),
primary key(id)
//复合主键：primary key（id，username）
);
主键分类：
按主键字段数量可分为单一主键和复合主键（多个字段组合形成主键，不推荐使用）；
按主键性质可分为自然主键（自然数）和业务主键（与业务挂钩，如身份证号，不推荐使用）
注意：一张表只能有一个主键
外键约束
drop table if exists t_student;
drop table if exists t_class;
create table t_class(
cno int,
cname varchar(255),
primary key(cno)
);
create table t_student(
sno int,
sname varchar(255),
classno int,
primary key(sno),
foreign key(classno) references t_class(cno)//外键约束
);
注意：
外键值可以为NULL；
被引用的字段不一定满足主键约束，但必须满足unique约束
navicat软件在设置外键时删除属性和更新属性有四个取值：
CASCADE：删除或更新父表中的记录时，首先检查该记录中的字段是否有外键来源字段，如果有，则删除或更新外键在子表中的对应记录。
NO ACTION、RESTRICT（默认）：只能先删除或更新子表中的记录，否则会报错。
SET NULL：删除或更新父表中的记录时，首先检查该记录中的字段是否有外键来源字段，如果有，则将外键在子表中的对应记录设为NULL。
存储引擎（表的存储方式）
show engines \g
MySQL常见的存储引擎：
InnoDB(默认存储引擎):
优点：支持事务、行级锁、外键等。这种存储引擎数据的安全得到保障。
表的结构存储在xxx.frm文件中；
数据存储在tablespace这样的表空间中（逻辑概念），无法被压缩，无法转换成只读；
在MySQL数据库崩溃之后提供自动恢复机制；
支持级联删除和级联更新；
MyISAM（最常用）：
MyISAM这种存储引擎不支持事务。
MyISAM采用三个文件组织一张表：
xxx.frm（存储格式的文件）
xxx.MYD（存储表中数据的文件）
xxx.MYI（存储表中索引的文件）
优点：可被压缩，节省存储空间。并且可以转换为只读表，提高检索效率。
缺点：不支持事务。
MEMORY：
优点：查询速度最快。
缺点：不支持事务。数据容易丢失。因为所有数据和索引都是存储在内存当中的。
事务
事务的定义
一个事务是一个完整的业务逻辑单元，不可再分。
比如：银行账户转账，从A账户向B账户转账10000.需要执行两条update语句：
update t_act set balance = balance - 10000 where actno = ‘act-001’;
update t_act set balance = balance + 10000 where actno = ‘act-002’;
以上两条DML语句必须同时成功，或者同时失败，不允许出现一条成功，一条失败。
事务只与DML语句相关（insert、delete、update）
一个事务通常需要多条DML语句才能完成
事务的四大特性
- 原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
- 一致性：在事务开始之前与结束之后，数据库都保持一致状态。
- 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
- 持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。
关于事务的隔离性：
第一级别：读未提交（read uncommitted）
对方事务还没有提交，我们当前事务可以读取到对方未提交的数据。读未提交存在脏读（Dirty Read）现象：表示读到了脏的数据。
第二级别：读已提交（read committed）
对方事务提交之后的数据我方可以读取到。
这种隔离级别解决了: 脏读现象没有了。
读已提交存在的问题是：不可重复读。
第三级别：可重复读（repeatable read）
这种隔离级别解决了：不可重复读问题。
这种隔离级别存在的问题是：读取到的数据是幻象。
第四级别：序列化读/串行化读（serializable）
解决了所有问题。
效率低。需要事务排队。
oracle数据库默认的隔离级别是：读已提交。
mysql数据库默认的隔离级别是：可重复读。
语法
//MySQL默认每执行完一条DML语句就提交事务
START TRANSACTION //开启一个事务；
...
COMMIT //提交事务；
ROLLBACK //回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
SAVEPOINT identifier，//创建一个保存点，一个事务中可以有多个 SAVEPOINT；
RELEASE SAVEPOINT identifier //删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
ROLLBACK TO identifier //把事务回滚到标记点；
SET TRANSACTION //用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。
索引
概述
数据库检索方式有两种：全表扫描和根据索引检索，索引检索效率高
索引虽然可以提高检索效率，但是不能随意的添加索引，因为索引也是数据库当中的对象，也需要数据库不断的维护。是有维护成本的。比如，表中的数据经常被修改，这样就不适合添加索引，因为数据一旦修改，索引需要重新排序，进行维护。
什么情况下要给字段建索引
* 数据量庞大。（根据客户的需求，根据线上的环境）
* 该字段很少的DML操作。（因为字段进行修改操作，索引也需要维护）
* 该字段经常出现在where子句中。（经常根据哪个字段查询）
注意：
主键和具有unique约束的字段自动会添加索引。
根据主键查询效率较高。尽量根据主键检索。
模糊查询时索引失效
语法
//创建索引对象：
create index 索引名称 on 表名(字段名);
//删除索引对象：
drop index 索引名称 on 表名;
索引实现原理
通过B+Tree缩小扫描范围，底层索引进行了排序，分区，索引会携带数据在表中的“物理地址”，最终通过索引检索到数据之后，获取到关联的物理地址，通过物理地址定位表中的数据，效率是最高的。
select ename from emp where ename = ‘SMITH’;
通过索引转换为：
select ename from emp where 物理地址 = 0x3;
数据库设计三范式
概念
*第一范式：任何一张表都应该有主键，并且每一个字段原子性不可再分。
*第二范式：建立在第一范式的基础之上，所有非主键字段完全依赖主键，不能产生部分依赖。
*第三范式：建立在第二范式的基础之上，所有非主键字段直接依赖主键，不能产生传递依赖。
采用三范式设计的表不会产生数据冗余，但实际开发中可能会牺牲空间来换性能
应用—表的设计
1. 多对多关系
方案：三张表，关系表两个外键。
实例：
t_student学生表
sno(pk) sname
-------------------
1 张三
2 李四
3 王五
t_teacher 讲师表
tno(pk) tname
---------------------
1 王老师
2 张老师
3 李老师
t_student_teacher_relation 学生讲师关系表
id(pk) sno(fk) tno(fk)
----------------------------------
1 1 3
2 1 1
3 2 2
4 2 3
5 3 1
6 3 3
2.一对多关系
方案：两张表，多的加外键
示例：
班级t_class
cno(pk) cname
--------------------------
1 班级1
2 班级2
学生t_student
sno(pk) sname classno(fk)
---------------------------------------------
101 张1 1
102 张2 1
103 张3 2
104 张4 2
105 张5 2
3.一对一关系
方案1：一张表
在实际开发中可能出现一张表数据量太大，不便于管理的情况，这是可以将一张表拆为几张表
方案2：主键共享（用的较少）
示例：
t_user_login 用户登录表
id(pk) username password
--------------------------------------
1 zs 123
2 ls 456
t_user_detail 用户详细信息表
id(pk+fk) realname tel ....
------------------------------------------------
1 张三 1111111111
2 李四 1111415621
方案3：外键唯一（用的较多）
t_user_login 用户登录表
id(pk) username password
--------------------------------------
1 zs 123
2 ls 456
t_user_detail 用户详细信息表
id(pk) realname tel userid(fk+unique)....
-----------------------------------------------------------
1 张三 1111111111 2
2 李四 1111415621 1
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
