mysql学习笔记

学习mysql课程视频— 基础篇

https://www.bilibili.com/video/BV1fK4y1s7Mk?p=144

1 数据库的优势
可将数据持久化到硬盘
可存储大量数据
可方便检索
2 常见数据库产品
Oracle
DB2
SQL Server
Mysql
3 常见的概念
数据库：类似于仓库，保存了数据
数据库管理系统：数据库是通过DBMs创建和操作的容器。
SQL:结构化查询语言，与数据库通信的语言

一 面试需知
第一部分 基础部分
一DQL数据查询语言select基础查询
1 起别名
去重
连接两个字符串：concat(a,b)
2 条件查询
2.1 条件运算符的使用 > < = != >= <=

2.2 逻辑运算符的使用 && || ! and or —用于连接条件表达式
2.3 模糊查询 like、某个字段包含某个字符‘%a%’
between … and …、
in、
is null
2.4 常见函数
select 函数名（实参列表） from 表
2.4.1 字符函数

select  char_length('hello,郭襄'); #获取字符长度
select substr(‘张三丰爱上郭襄’，1，3) #截取子串，起始位置，以及所要截至长度
#lpad/rpad 左填充，右填充 ；固定列宽用。

2.4.2 日期函数

select now() #查看当前日期+时间
select curdate() #查看当前日期
select curtime() #查看当前时间
select datediff(‘1996-7-17’，‘1998-10-24’) #查看日期之差
#select  date_formate('1996-12-14 01:14:45',fmt)#按照字符串fmt格式化日期'1996-12-14 01:14:45'
select  date_formate('1996-12-14 01:14:45',‘%Y-%m-%d’)

2.4.3 流程控制函数

select if(100>7,'好','坏')
case ... when ... then ... else ... end
case   when ... then ... else ... end

3 排序查询
order by
3.1
3.2
3.3
3.4
3.5

二
1 基本的SQL语句
显示所有的数据库文件show databases;
创建数据库create database test;
使用某个数据库use test;
查看数据表show tables;
删除库drop database test;

2 标识列
2.1 类似于约束，可以用于限定某个字段，不用手动插入，可以自动增长。
系统提供默认的序列值，从1 开始。


注意：(1) 标识列必须与一个key搭配(例如主键、唯一、外键…)；
(2) 一个表至多有1个标识列；
(3) 标识列的类型只能是数值型；
(4) 设置步长语句：

2.2 修改表时设置标识列

2.3 删除自增长列

3 TCL语言-事务控制语言
3.1
事务：一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行；要么全部不执行。

即在一个执行单元中，每个SQL语句相互依赖。
注：mysql中的存储引擎
不是所有的存储引擎都支持事务。
3.2 事务具备的属性—ACID

3.3 事务的创建
—针对一个事务
隐式事务：事务没有明显的开启和结束的标记(自动开启、自动结束)。比如：insert、update、delete语句

变量“autocommit”为自动提交的意思，该功能开启。
显式事务：事务具有明显的开启和结束的标记，前提为必须先设置自动提交功能为禁用。

步骤1 开启事务

步骤2
编写事务中的sql语句(select\insert\update\delete)—增删改查
—create\alter\drop属于DDL语言，没有事务之说。
也可以设置回滚点 savepoint a;
步骤3 结束事务
commit;提交事务
rollback;回滚事务

3.4 事务并发问题的介绍
对于同时运行的多个事务，当这些事务访问数据库中相同数据时，如果没有采取必要的隔离机制，就会导致各种并发问题.并发问题有：脏读、不可重复读、幻读。
脏读：一个事务读取了其他事务还没有提交的数据；
不可重复读：一个事务多次读取，结果不一样；
幻读：一个事务读取了其他事务还没有提交的数据，只是读到的是 其他事务 插入的数据；
即数据库系统必须具备隔离并发运行各个事务的能力，使它们不会相互影响，避免各种并发问题。故设置隔离级别来解决并发问题。

通过命令行形式来运行mysql,演示隔离级别。
第一步，查看当前的默认隔离级别— select @@tx_isolation;
第二步，设置隔离级别— set session transaction isolation level read uncommitted;
第三步，查看当前的默认隔离级别— select @@tx_isolation;

案例1：有一个数据表 account，字段为姓名username、余额balance；
1号事务或命令行窗口
(1)设置隔离级别— set session transaction isolation level read uncommitted;
(2)开启事务，—set autocommit=0
(3)修改，—update account set username="john" where id=25;
注意：现在事务开启，但还没有提交；
2号事务或命令行窗口
(1)设置隔离级别— set session transaction isolation level read uncommitted;
(2)开启事务，—set autocommit=0
(3)查看表信息，select * from account

发现表信息已改。但是1号命令行并没有提交事务。所以现在读到的数据是“脏数据”，

若1号命令行现在输入回滚：rollback;
则在二号命令行查看时，会是：

这种现象成为“不可重复读”。

综上所述，在 read uncommitted 隔离级别下，会出现 脏读、不可重复读、幻读 现象。
案例2
现在将 隔离级别设置为 read committed
有一个数据表 account，字段为姓名username、余额balance；)
1号事务或命令行窗口
(1)设置隔离级别— set session transaction isolation level read committed;
(2)开启事务，—set autocommit=0
(3)修改，—update account set username="张飞" where id=25;
注意：现在事务开启，但还没有提交；
2号事务或命令行窗口
(1)设置隔离级别— set session transaction isolation level read committed;
(2)开启事务，—set autocommit=0
(3)查看表信息，select * from account;

即它避免了脏读。但是没有避免 不可重复读、幻读 。
(4)提交事务：commit;

案例3
现在将 隔离级别设置为repeatable read
有一个数据表 account，字段为姓名username、余额balance；)
1号事务或命令行窗口
(1)设置隔离级别— set session transaction isolation level repeatable read;
(2)开启事务，—set autocommit=0

(3)修改，—update account set username="刘备" where id=25;
注意：现在事务开启，但还没有提交；
2号事务或命令行窗口
(1)设置隔离级别— set session transaction isolation level repeatable read ;
(2)开启事务，—set autocommit=0
(3)查看表信息，select * from account;

1号命令行
(4)提交事务 commit;
2号命令行
在1号命令行提交事务后，在2号命令行查看数据表：
即在2号命令号没有提交事务时，1号命令行提交事务前或后，2号事务查看到的表信息一样。避免了不可重复读。
在2号命令行
(4)提交事务 commit后，查看数据表：

案例4
幻读是针对插入数据。
现在将 隔离级别设置为serializable
有一个数据表 account，字段为姓名username、余额balance；)
1号事务或命令行窗口
(1)设置隔离级别— set session transaction isolation level serializable
(2)开启事务，—set autocommit=0
查看数据表信息：

(3)修改，—update account set username="www"
注意：现在事务开启，但还没有提交；
2号事务或命令行窗口
(1)设置隔离级别— set session transaction isolation level serializable ;
(2)开启事务，—set autocommit=0
(3)插入数据：

insert into account values(null,'关羽',2000)

回车，但是没反应。一直在等待…
等到1号命令行提交事务
(4)提交事务 commit
2号命令行提交事务 commit。

总结：事务的隔离机制read uncommitted\read committed\repeatable read\serializable;mysql默认repeatable read；oracle默认read committed。
3.5 回滚点的演示
命令：savepoint 节点名 #只搭配rollback使用。
第一步，打开事务

set commit=0;
start transaction;

第二步，

delete from account where id=25;
savepoint  a;#设置保存点a
delete from account where id=28;

第三步，

rollback  to a;#回滚到保存点
select * from account;

注：25号删掉，28号没有删掉。

4 视图
4.1 视图的介绍
视图是一个虚拟的表。和普通的表使用一样。行和列的数据来自定义视图的 查询中使用的表，并且是在使用视图时动态生成的，只保存了sql逻辑结构，不保存查询结果。
应用场景：(1)多个地方用到同样的查询结果;
(2)该查询结果使用的sql语句较复杂。

等价于—


4.2 视图的创建
语法： create view 视图名
as
查询语句；

4.3 视图的修改
(1) create or replace view 视图名
as
查询语句；
(2) alter view 视图名
as
查询语句；

4.4 视图的删除
语法：drop view 视图名，视图名，…;
4.5 视图结构的查询
desc 视图名

4.6 视图的更新
视图的更新,指视图内容的更新。
只有在某些情况下才可以插入、删除数据。

5.1视图和表的对比
创建语法不一样: create view\create table

5.2 delete 与truncate在事务中的区别

案例1：通过事务来删除该表

set autocommit=0;
start  transaction;
delete from account;
rollback;#没有提交事务，执行回滚；

select  * from  account;#执行事务后，查看表

即，表还存在，没有被删除。delete 成功回滚。

案例2：通过事务来删除该表

set autocommit=0;
start  transaction;
truncate table account;
rollback;#没有提交事务，执行回滚；

select  * from  account;#执行事务后，查看表

即truncate在事务执行过程中，实际删除，不支持回滚。

总结：
a. 创建表：
6 约束
6.1 约束类型
not null 非空
unique 唯一
default 默认
check 检查，mysql不支持
primary key 主键
foreign key 外键

6.2 异同点

外键：用于限定两个表的关系；从表、主表
插入数据时，先插入主表；
删除数据时，先删除从表；
设置外键语句：
表已有时，添加外键
alter table 表名 add constraint 外键名 foreign key(从表的外键字段) references 主表名(主表的字段)
案例：主表 major ；从表 stuinfo
major:专业编号、专业名
stuinfo ：学号、姓名、性别、座位、年龄、专业编号
删除major表的3号专业：
设计主表、从表，怎么设计？
方法1：级联删除
删除主表的3号专业记录；
再把学员信息表中引用该专业 3号的信息记录删除
命令为，在设置外键时追加一个on 子句；
alter table 表名 add constraint 外键名 foreign key(从表的外键字段) references 主表名(主表的字段) on delete cascade;
delete from major where id=3;
效果为：删除了主表的3号专业记录；学员信息表中引用该专业 3号的信息记录也被删除。

方法2：级联置空
删除主表的3号专业记录；
再把学员信息表中引用该专业 3号的信息记录的专业编号字段 设置为空值。

命令为，在设置外键时追加一个on 子句；
alter table 表名 add constraint 外键名 foreign key(从表的外键字段) references 主表名(主表的字段) on delete set null;
delete from major where id=3;
效果为：删除了主表的3号专业记录；学员信息表中引用该专业 3号的信息记录的专业编号字段 设置为了空值。

6.3 创建表时添加约束
列级约束：非空、主键、唯一、默认；不支持外键；
表级约束：外键【constraint 约束名 foreign key(字段名) references 主表(被引用列)】；不支持非空与默认；

6.4 修改表时添加约束

7 变量
变量分为系统变量和自定义变量；系统变量包括全局变量、会话变量；自定义变量包括用户变量、局部变量。下面从声明、赋值、调用来进行详细的说明。

7.1 系统变量：(变量由系统提供)
全局变量,服务器每次启动将为所有的全局变量赋初始值。
会话变量，仅针对于当前会话有效。
a. 查看系统变量：

 show global variables#查看全局变量；

查看会话变量:

 show session variables#查看会话变量；

b. 查看满足条件的部分系统变量：
案例：查看字符集

 show global【session】 variables  like  ‘%char%’

c. 查看指定的某个系统变量的值：

  select   @@【global[session].】系统变量名

 案例：当前的默认隔离级别
           select @@tx_isolation;

d. 为某个系统变量赋值
法一： set global[session] 系统变量名=值；
法二： set @@global[session].系统变量名=值；
show tables#查看所有表

7.2 自定义变量：变量是用户自定义的，不是系统的。
用户变量、局部变量； 使用步骤：声明、赋值、使用。
注意：用户变量，针对当前会话有效；局部变量，仅仅在定义它的begin end 中有效，应用在begin end 中的第一句话！！！

a 用户变量，针对当前会话有效；
声明并初始化：
法一：set @用户变量名=值；
法二：set @用户变量名：=值；
法三：select @用户变量名:=值；
赋值(更新用户变量的值)
法一：set @用户变量名=值；
set @用户变量名：=值；
select @用户变量名:=值；
法二： select 字段 into 变量名 from 表； #可以用在从表中返回字段时。

使用(查看用户变量值)
select @用户变量名；

b.查看局部变量，仅仅在定义它的begin end 中有效；应用在begin end 中的第一句话！！！

声明[一定要在begin end 中的第一行声明]并初始化：

declare  变量名  类型；
declare  变量名  类型  default  值；

赋值(更新用户变量的值)
法一：

set  局部变量名=值；
set  局部变量名：=值；
select  @局部变量名:=值；

法二：select 字段 into 局部变量名 from 表；
使用(查看用户变量值)

 select   局部变量名；

注意：用户变量、局部变量在声明、赋值、使用时的区别。

7.3 用户变量、局部变量对比
作用域不同
定义和使用的位置不同
语法不同

案例：声明两个变量并赋初始值，求和，并打印。

8 存储过程
8.1 定义
类似与java中的方法，是 一组预先编译好的sql语句的集合理解成批处理语句。
好处：提高代码的重用性；
简化操作；
减少了编译次数，并且减少了和数据库服务器的连接次数；
8.2 语法
a. 创建语法

create  procedure  存储过程名(参数列表)
begin
       存储过程体(一组合法的SQL语句 )
end

注意(1)参数列表包括3部分：参数模式 参数名 参数类型
参数模式：in【该参数可以作为输入】、out【该参数可以作为返回值】、inout【该参数既可以作为输入，又可以作为输出】.
(2)存储过程体中的每条SQL语句的结尾要求必须加分号；
存储过程的结尾可以使用 delimiter 重新设置。
delimiter 结束标记
案例：delimiter $

b. 调用语法

call   存储过程名(实参列表);

c. 案例—存储过程的建立与调用
案例1. 空参列表
插入到admin表中5条记录

delimiter  $
 create procedure  mypro1()
 begin
     insert  into admin(username,password) 
     values('joh1',0000),('joh2',2000)('joh3',3000)('joh4',4000)('joh5',5000);
 end
 call mypro1()  $  #调用
 select * from admin  $ #查看

案例2.1 in模式参数的存储过程，实现：根据女生名查看对应的男生信息；

create procedure  mypro2(in  beautyname varchar(20))
begin
   select  bo.*
   from  boys bo right  join beauty b
   on bo.id=b.boyfriend_id
   where  b.name=beautyname
end   $
call mypro2('小昭')  $  #调用 
select * from admin  $  #查看

案例2.2 用户是否登录成功。

create procedure  mypro3(in  username varchar(20), in  password varchar(20))
begin
   declare result   varchar(20)  default  ' ';#声明并初始化
   
   select  count(*)   into result   #赋值
   from  admin
   where admin.username=username and admin.password =password ; 
  
   select if (result>0,‘成功’,‘失败’)  ；#使用
end   $
call mypro3('张飞'，‘000000’)  $   #调用
select * from admin  $  #查看  

知识点：两个in模式、变量的声明、初始化、赋值；if函数的使用；

案例3.1 out模式参数的存储过程{带返回值}，实现：根据女生名查看对应的男生信息并返回；
注意：使用out模式时，调用存 储过程，会自动返回值。

create procedure  mypro4(in  beautyname varchar(20)，out  boyname varchar(20) )
begin
   select  bo.boyname into    boyname     #赋值局部变量的一种方式
   from  boys bo inner  join beauty b
   on bo.id=b.boyfriend_id
   where  b.name=beautyname;

end   $

set @bname $   #定义一个用户变量，可以在begin-end外边起作用
call mypro4('小昭'，@bname)  $  #调用 
select @bname  $  #查看变量

案例4. inout模式参数的存储过程，实现：传入a,b;翻倍并返回。

create procedure  mypro5(inout  a int，inout  b  int )
begin
   set a=a*2;
   set b=b*2;
end   $
调用 
set @a=10$   #定义一个用户变量，可以在begin-end外边起作用
set @b=20$
call mypro5(@a，@b)  $
select @a ,@b $   #查看变量

d 存储过程的删除

drop procedure 存储过程名

注意：一次只能删除一个存储过程；
无法修改存储过程里的存储体。
e 查看存储过程的信息

show create  procedure 存储过程名

会出现：存储过程名，语法格式等等

9函数
9.1 介绍
与存储过程类似。区别在于，存储过程可以有0个或多个返回；函数有且仅有一个返回。
存储过程适合做批量插入、更新；函数适合做数据处理后返回一个结果。
与内置函数联系一下。
9.2 语法

a. 创建语法

create  function  函数名(参数名，参数类型)  returns 返回类型
begin
       函数体(一组合法的SQL语句 )
end

注意(1)参数列表包括2部分： 参数名 参数类型；
(2)函数体肯定会有return语句，如果没有会报错。
函数的结尾可以使用 delimiter 重新设置。
delimiter 结束标记
案例：delimiter $

b. 调用语法

select 函数名(实参列表);

9.3 案例
案例1. 无参数有返回值；返回公司的员工个数.
#函数定义

delimiter  $
create  function  myfun1()  returns int
begin
       declare c int default 0;  #定义变量
       select count(*)  into c
       from employees;
       return c;
end $
select myfun1()$

案例2. 有参数有返回值；根据员工名返回工资。

create  function  myfun2(empname varchar(20))  returns double
begin
       declare c int default 0;  #定义变量
       
        select salary into c   #赋值
        from employees
        where  employees.empname=empname;
        
       return c;
end $

select myfun2(‘king’)$

9.4 函数的查看与删除
函数的查看

show create  function 函数名；

或

show create  function 函数名$

函数的删除

drop funcyion 函数名

10 流程控制结构
顺序结构、分支结构、循环结构
10.1分支结构–if函数
if函数可以简单实现双分支
语法 :select if(表达式1，表达式2，表达式3)
执行顺序：如果表达式1成立，则if函数返回表达式2的值，否则返回表达式3的值。
应用在任何地方。

10.2分支结构–case 结构
可以搭配select使用，也可以单独使用（在存储过程中、函数中）
情况1：用于等值判断（类似于switch）
语法： case 变量/表达式/字段
when 要判断的值 then 返回的值1 或语句；
when 要判断的值 then 返回的值2 或语句；
…
else 要返回的值n 或语句；
end case;

情况2：用于区间判断（类似于多重if）
语法： case
when 要判断的条件1 then 返回的值1 或语句；
when 要判断的条件2 then 返回的值2 或语句；
…
else 要返回的值n 或语句；
end case;
特点：(1)可以作为表达式，嵌套在其他语句(例如：select)中使用；可以放在begin-end外边、里皆可以；then 后边为值，无需分号；
也可以作为独立语句，必须放在begin-end里使用。then 后边为语句，须加分号。
(2)
案例：建立一个存储过程，根据传入的成绩，显示等级。

delimiter  $  #定义结束标记
#将case结构作为一个独立语句，故只能在begin-end中。
create procedure  test-case(in  score int)
begin
      case 
      when  score>=90   then  select  'A';
      when  score>=80   then  select  'B';
      when  score>=60   then  select  'C';
      else   select  'D';
      end  case;
end $

call   test-case(90)$

10.3分支结构–if结构
实现多重分支
语法：
if 条件1 then 语句1；
elseif 条件2 then 语句2；
…
else 语句 n;
end if;
只能应用在begin-end中。

案例2 因为只有一个返回值，可以创建函数来实现。

create  function  test-case(score int)  returns  char
begin
      if   score>=90   then  return  'A';
      elseif   score>=80   then  return   'B';
      elseif  score>=60   then   return   'C';
      else   return  'D';
      end  if;
end $

call   test-case(90)$

10.4 循环结构
分类：while、loop、repeat；必须放在begin-end 中，所以依托于存储过程或者函数。
循环控制：iterate（类似于continue）;leave(类似于break)
(1)语法
while语法:
while 循环条件 do
循环体；
end while；

如果要加入循环控制，最好给循环起个名字。
标签： while 循环条件 do
循环体；
end while 标签；

loop语法：
标签： loop
循环体；
end loop 标签；

repeat 语法:
标签： repeat
循环体；
until 结束循环的条件
end repeat 标签；

案例1:批量插入

create   procedure  my1(in insertcount  int)
begin
       declare i int default 1;
       [a:] while i<=insertcount  do
             insert into admin  values(concat('roce',i),'666') ;
             set i=i+1；
       end while [a]；
#a:给起的标签
end  $
call   my1(100)$
select  * from admin $

案例2 批量插入，次数大于20停止。

create   procedure  my2(in insertcount  int)
begin
       declare i int default 1;
       a: while i<=insertcount  do
                     insert into admin  values(concat('roce',i),'666') ;
                     if  i>=20   then  leave a;
                     end   if;
                     set i=i+1；
       end while a；
       #a:给起的标签
end  $
call   my2(100)$
select  * from admin $

案例3：批量插入，只插入偶数。

create   procedure  my3(in insertcount  int)
begin
       declare i int default 0;
       **a:** while i<=insertcount  do
                   set i=i+1；
                   
                       if mod(i,2)!=0  then iterate **a**;
                       end   if;
                   
                      insert into admin  values(concat('roce',i),'666') ;                           
        end while **a**；  
end  $
call   my3(100)$
select  * from admin $

补充知识点：
1 关键字exists
什么时候用exists，什么时候用in？
主表为employees，从表为dept_emp，在主表和从表都对关联的列emp_no建立索引的前提下：
当主表比从表大时，IN查询的效率较高；
当从表比主表大时，EXISTS查询的效率较高；
原因如下：
in是先执行子查询，得到一个结果集，将结果集代入外层谓词条件执行主查询，子查询只需要执行一次
exists是先从主查询中取得一条数据，再代入到子查询中，执行一次子查询，判断子查询是否能返回结果，主查询有多少条数据，子查询就要执行多少次

2 英文字符排序
3 排序问题
案例1：请，输出通过的题目的排名，通过题目个数相同的，排名相同，此时按照id升序排列。

非等值连接
select  p1.id,p1.number,count(distinct p2.number) as t_rank
from passing_number as p1, passing_number as p2
where p1.number<= p2.number
group by p1.id
order by  t_rank asc, p1.id asc

案例2：流程控制函数搭配select使用
实例sql–69
流程控制函数的使用
案例3：partition by 语法学习
partition by 学习案例

笔记：partition by 与group by不同之处在于前者返回的是分组里的每一条数据，并且可以对分组数据进行排序操作。后者只能返回聚合之后的组的数据统计值的记录。

实例4

第二部分 高级篇（见数据库原理）
mysql是一种数据库管理系统DBMS。
常见的数据库关系系统：mysql\Oracle\DB2\SQLServer
sql:结构化查询语言 :DQL数据查询语言select基础查询、条件查询、排序查询、常见函数【sum、avg\densk_rank() over() 】、分组函数、
分组查询、连接查询、子查询、分页查询、union联合查询
DML数据操作语言 插入数据、修改数据、删除数据
DDL数据定义语言：数据表、库的建立、删除、修改
TCL事务控制语言：事务和事务处理

1 mysql服务启动
方式一 计算机管理 -服务MySQL5.5
方式二 命令行 win+R cmd
停止服务 net stop MySQL5.5
启动服务 net start MySQL5.5
2 mysql登录
或者 mysql -u root -p 密码 lbl@822823@wj
mysql退出：exit 或者ctrl+c

3 mysql环境变量配置

4 mysql常见命令

show databases;#查看当前所有的数据库
use information;#打开指定的库
show tables; #查看当前库的所有表
show tables from 库名；#查看某个库的所有表
desc 表名； #查看表结构
select version  通过命令行查看版本

5 mysql语言规范
不区分大小；建议关键字大写
每条命令用分号结尾；
可以注释 单行注释： #
多行注释：/* */
写性能高效的sql语言
一 mysql架构分析
1 mysql简介
关系型数据库管理系统
1.1MySQL 服务器的优化
2 mysql的Linux版的安装
3 mysql配置文件
4 mysql 逻辑架构介绍
5 mysql 存储引擎

二 索引优化分析
1 性能下降SQL慢
执行时间长
等待时间长
2 常见通用的JOIN查询
3 索引简介
4 性能分析
5 索引优化

三 查询截取分析
1 查询优化
2 慢查询日志
3 批量数据脚本
4 show profile
5 全局查询日志

四 mysql锁机制

五 主从复制