MySQL课程知识点记录

数据库基础知识

常用数据库有哪些？
MySQL，oracle，DB2，SQLServer，OceanBase

SQL语言

SQL语言是非过程性语言
DDL数据库定义语言
用来定义数据库对象：数据库，表，列
关键字：creat ，drop，alter,truncate,show等

DML数据库操作语言
用来对表中数据进行更新，增加和删除记录
如updata，insert，delete，不包含查询

DCL数据库控制语言
用来设置或更改数据库用户或角色权限，
如grant，revoke，begain transaction等

DDL

create database 数据库名；
create database if not exists；
create database 数据库名 character set 编码格式；
show databases；查看所有的数据库
show create database 数据库名称；查看某个数据库
alter database 数据库名 character set 编码格式；修改数据库编码格式
drop database 数据库名；删除数据库
select database();查看正在使用的数据库
use 数据库名；切换数据库
create table 数数据库名(字段名 字段类型，...);
show tables;查看所有表
desc 表名；
show create table 表名；查看创建表的SQL信息
create table 表名 like 表名；快速创建相同的表
drop table 表名；删除表
alter table 表名 add 字段名 类型；增加字段
alter table 表名 modify 字段名 新的类型；修改表中的字段类型
alter table 表名 change 旧字段名 新字段名 类型；修改字段名
alter table 表名 drop 字段名；
rename table 表名 to 新表名；
alter table 表名 character set 编码格式；

DML

insert into 表名（字段名1，字段名2，...）values (字段值1，字段值2，...)；
insert into 表名 values（）；
update 表名 set 字段名=新的字段名，字段名=新的值，...where 条件；
delete from 表名 where 条件；
truncate table 表名；

DQL

select 字段名1，字段名2，... from 表名 ；
select * from 表名；
select 字段名1 as 别名，字段名2 as别名，... from 表名；
select distinct 字段名 from 表名；
select * from 表名 where 条件 limit offset，row_count；

蠕虫复制

inset into 表名1 select * from 表名2；

in ，between and关键字

select * from 表名 where 字段名 in（值1，值2，值3）；
selec * from 表名 where 字段名 between 值1 and 值2；

模糊查询like

SELECT * FROM 表名 WHERE 字段名 LIKE '通配符字符串';

%: 表示零到多个字符(任意多个字符)

_: 表示一个字符

排序

select * from 表名 order by 字段名 asc/desc；

• 五大聚合函数
  count
  sum
  max
  min
  avg

分组查询

select  字段名，... from 表名 group by 字段1，字段2，...  having  条件；

约束

主键约束 primary key 非空唯一
唯一约束 unique 唯一
非空约束 not null 非空
默认值约束 default 默认的值 指定字段默认值
外键约束 foreign key 用于多表之间的关系约束

##外键的级联

on update cascade
on delete cascade

• 多对多，创建中间表
• 一对多，多的表外键维护
• 一对一，外键唯一任意维护

三大范式

第一范式

表中所有字段都满足原子性

第二范式

在满足第一范式的条件下，字段完全依赖于主键

第三范式

在满足第二范式的条件下，不存在传递依赖

反三范式

为了减少多表的关联查询，可以增加冗余字段

多表查询

表连接查询

• 内连接

select * from 表名1 inner join 表2 on 条件；
select * from 表名1，表名2 where 条件；

• 外连接
  左外连接

select * from 表名 left outer join 表2  on 条件；

右外连接

select * from 表名 right outer join 表2  on 条件；

子查询

MySQL常用函数

字符串函数

char_length(s)
concat（s1,s2,s3,…）
Lower（s）
UPPER(S)
substr（s，start ，length）
trim（s）

数字函数

rand（）
round（）
truncate（）
least（1,2，3,4，…）
greatest(1,2,3,4,…)

日期函数

now（）
curdate（）
curtime（）
year（d）
month（d）
day（d）

高级函数

current_user()
IFNULL(v1,v2)

事务

手动提交事务

start transaction；开启事务
commit；提交事务
rollback；回滚事务

自动提交事务

MySQL的每一条DML （增删改）语句都是一个单独的事务

设置回滚点

savepoint 名字；
rollback to 名字；

事务的原理

1.客户端连接服务器,mysql服务会为当前会话创建一个临时的事务日志文件;
2.当我们没有手动开启事务时,所有的自操作直接刷入到数据库中;
3.当我们手动开启事务的时候(start transaction),我们的操作会先写入临时事务文件中,然后当用户使用commit指令提交时,会将临时事务日志文件中的数据刷入到数据库,同时清空临时事务日志文件;
4.当事务没有提交前,会话异常关闭 或者事务正常提交后,这个临时日志文件中的数据会被清空;

事务的四大特性（ACID）

• 原子性
  事务包装的一组sql业务逻辑是不可分割的，要么都成功，要么都失败
• 一致性
  事务前后的状态保持一致，如转账前后两个用户的总金额是不变的
• 隔离性
  多用户并发访问数据库时，每个用户的事务之间相互隔离
  如转账 A-C,B-C,D-C互不影响
• 持久性
  事物一旦提交，数据就持久保存到数据库，哪怕之后发生数据库异常，重启之后数据不丢失

MySQL性能

分析-执行次数比较多的语句

1.执行次数比较多的语句分类
查询密集型
修改密集型
2.查看当前数据库
show global status like ‘Inodb——rows%’

索引

MySQL索引分类

• 主键索引
• 唯一索引
• 普通索引
• 组合索引
• 全文索引
• hash索引
• 空间索引

创建索引

-- 创建普通索引
create index 索引名 on 表名(字段);

-- 创建唯一索引
create unique index 索引名 on 表名(字段);

-- 创建普通组合索引
create index 索引名 on 表名(字段1,字段2,..);

-- 创建唯一组合索引
create unique index 索引名 on 表名(字段1,字段2,..);

说明：

1.如果在同一张表中创建多个索引，要保证索引名是不能重复的

2.上述创建索引的方式比较麻烦，还需要指定索引名

3.采用上述方式不能添加主键索引

创建表时指定

-- 创建学生表
CREATE TABLE student3(
 id INT PRIMARY KEY AUTO_INCREMENT, -- 主键索引
 name VARCHAR(32),
 telephone VARCHAR(11) UNIQUE, -- 唯一索引
 sex VARCHAR(5),
 birthday DATE,
 INDEX(name) -- 普通索引
);

查看索引

show index from 表名；

删除索引

alter table 表名 drop index 索引名；

索引的优缺点

• 优点
  提高检索效率，降低IO成本

• 缺点
  会占用磁盘和内存开销

索引创建yuanze

1.字段的辨识度越高越好，最好大于70%；
2.where条件中关联的字段使用频率高科创建索引
3.表关联查询join的字段适合添加索引
4.order by 字段也可添加索引
总之,索引并不是越多越好,索引也有磁盘和内存的开销,同时如果索引所在字段存在大量的增删改操作,不建议创建索引;

视图

1.视图（View）是一种虚拟存在的表，行和列的数据来源于定义视图的查询中使用的表，并且是在使用视图时动态生成的。

 2.视图和普通表一样使用，但是视图并不存储数据。通俗的讲，视图就是一条SELECT语句执行后返回的结果集。

create view 视图名 as 查询语句；

视图的好处

1.提高代码的复用性
2.不占用磁盘空间
3.在一定程度上保护数据安全

修改视图

create or replace view 视图名 as 查询语句；


alter view 视图名 as 查询语句；

# 触发器
~~~sql
-- 语法
create trigger trigger_name
before/after insert/update/delete
on tbl_name
for each row   -- 行级触发器
	trigger_stmt ;
说明：
	1）before/after位置称为触发时机，一个触发器只能选择一个
	2）insert/update/delete位置称为触发事件，一个触发器只能选择一个
	3）for each row称为行级触发器，触发器绑定实质是表中的所有行，因此当每一行发生指定改变的时候，就会触发触发器。

存储过程和存储函数

1.存储过程和函数类似于java中的方法，将一段代码封装起来，然后使用的时候直接调用即可。

2.mysql中的存储过程和函数是 一组预先编译并存储在数据库中的SQL 语句的集合，我们可以通过调用存储过程和函数来执行一组SQL语句的集合。

3.好处:提高代码的重用性,简化操作,减少编译次数并且减少了和数据库服务器的连接次数，提高了效率.

-- 格式
CREATE procedure 存储过程名(参数列表)
begin
	-- 存储过程体(一组合法的sql语句集合)
end

case结构

CASE
    WHEN 条件表达式1 THEN 语句1;
    [WHEN 条件表达式2 THEN 语句2;] ...
    [ELSE 语句3]
END CASE;

mysql存储过程三种循环

while

while 循环条件 do

	sql语句

end while;
-- 当满足循环条件时,继续运行,否则退出循环;

repeat

-- 如果不满足until后面的循环条件则执行循环体代码，直到满足循环条件就结束循环体代码
repeat
	-- 循环体
    sql语句
    until  循环条件 -- 注意：循环条件后面不能加分号，不满足语法规则
end repeat;
说明:与while do正好相反,满足条件就退出

loop

-- 语法格式
-- c表示给循环体取一个名称,名称随便定义(sql关键字除外)
c:loop
  sql语句
  if 条件判断 then -- 条件判断
     leave c; -- 满足条件则退出当前循环
     end if;
end loop c;   

游标

delimiter $
create procedure pro_test10()
begin
   declare id int;
   declare name varchar(10);
   declare age int;
   declare salary int;  
  declare flag int default 1;-- flag=1表示表中有数据,当变成0时,表示数据读取完毕
  declare emp_result cursor for select * from emp;
  -- 声明异常句柄处理器not found
  declare exit handler for not found set flag=0;
  -- 打开游标 开启游标必须在句柄处理器的后边开启
  open emp_result;
  while flag=1 do
        fetch emp_result into id,name,age,salary;
        select concat(id,name,age,salary);
  end while;
  -- 关闭游标
  close emp_result;    
end$

call pro_test10();

存储函数

-- 创建存储函数
create function 存储函数名(参数名 参数类型)
returns 返回值的数据类型
begin
...
return xx;
end$

-- 调用存储函数
select 存储函数名(实参)$

存储引擎

1.存储引擎的选择:
innodb:对事务要求比较高,或者需要一些外键支持的业务场景,可以使用,同时业务中修改操作如果比较频繁,也可使用innodb;
myisam:适合以du为主的应用,同时对事务要求不是太高的场景;

2.在日常开发中,如果没有特殊的要求,那么一般会优先使用innodb存储引擎;

锁

myisam
读锁会阻塞写，但是不会阻塞读。而写锁，则既会阻塞读，又会阻塞写。
innodb
行锁模式
S锁，读锁，共享锁
X锁，写锁，排他锁
:innodb行级锁是基于事务的,然后对其他同一行的写操作是阻塞的.
行锁的特点:对同一行的写操作阻塞,但是对于读操作可不阻塞;
间隙锁
在批量写操作时,对于间隙的数据,如果存在数据的插入操作,那么这个插入的事务会被阻塞,这种现象叫间隙锁;