mysql基础学习

1.DQL语言

1.单行函数

• 字符函数

#拼接字符
select concat(name,'@') from account;
#返回字串第一次出现的索引，如果找不到返回0
select instr('','');
#去掉两边指定字符，不指定默认去掉两边空格
select trim('a' from 'aaahaaahaaaa');
#用指定字符实现左填充指定长度
select lpad('hhh',10,'*');
#用指定字符实现右填充指定长度
select rpad('hhh',10,'*');

-** 数学函数**

#四舍五入
select round(-1.55);
#小数点后保留指定位数
select round(1.567,2)
#向上取整
select ceil(1.001);
#向下取整
select floor(9.99);
#截断小数点后指定位数
select truncate(1.65,1);
#mod取余
select mod(10,3);

• 日期函数

#返回当前系统日期，不包含时分秒
select curdate();
#返回当前系统时间，不包含年月日
select curtime();
#获取日期指定部分-年
select year(now());
#获取日期指定部分-月
select month(now());
#获取日期指定部分-日
select day(now());
#将字符转换为日期类似于Oracle中的to_date()
select str_to_date('2021-11-01','%Y-%c-%d');
#将日期转换为字符类似于Oracle中的to_char()
select date_format('2021/11/11','%Y年%m月%d日');

• 流程控制函数

#满足条件输出大，不满足条件输出小
select if(10>5,'大','小');
#接受两个参数，如果不是NULL，则返回第一个参数
select ifnull(null,666);
#有变量表达式case结构，可以有多个when then
SELECT 
    CASE 10 > 5
        WHEN TRUE THEN '大'
        ELSE '小'
    END;
#无变量表达式case结构，可以有多个when then
SELECT 
    CASE
        WHEN TRUE THEN '大'
        ELSE '小'
    END;

• 分组函数/聚合函数/组函数

sum()忽略null值
avg()忽略null值
max()忽略null值
min()忽略null值
count()忽略null值，null不计入个数

2.连接查询/多表查询

1.笛卡尔乘积

现象：表1有m行，表2有n行，结果=m*n行
发生原因：没有有效的连接条件
如何避免：添加有效的连接条件

2.分类

1.按年代分类：

• sql92标准：仅仅支持内连接
• sql99标准【推荐】：支持内连接+外连接（左外和右外）+交叉连接

2. 按功能分类

1. 内连接
   1）等值连接
   2）非等值连接
   3）自连接

(inner) join	

2. 外连接
   1）左外连接

left(outer) join

2）右外连接

right(outer) join

3）全外连接

full(outer) join

3. 交叉连接

#实现笛卡尔乘积
cross join

3.子查询/内查询

1.多行子查询

1.多行比较操作符

#等于列表中的任意一个
in/not in
#和子查询返回的某一个值比较，可以用min()替代
any/some
#和子查询返回的所有值比较，可以用max()替代
all

4.分页查询

#offset:要显示条目的起始索引（起始索引从0开始）
#size：要显示的条目个数
select * from account limit 【offset】,size;

5.联合查询

#去重
union
#不去重
union all

2.DML语言

1.插入语句

#支持插入多行
#支持子查询
insert into 表名【(列名)】 values(值),(值);
insert into 表名 set 列名=值;

2.修改语句

#修改单表记录
update 表名 set 列=新值,列=新值;
#修改多表记录
update 表1 别名 inner|left|right| join 表2 别名 on 连接条件 where 筛选条件
set 列=值,列=值;

3.删除语句

#单表删除
delete from 表名;
#多表删除
delete 表1的别名，表2的别名 from 表1 别名 inner|left|right join 表2 别名 on 连接条件
where 筛选条件;
#全表删除
truncate table 表名;

delete与truncate的区别

• truncate效率更高
• delete删除后，再插入数据，自增长列的值从断点开始；而truncate删除后，再插入数据，自增长列的值从1开始
• delete 删除有返回值；truncate删除没有返回值
• truncate删除不能回滚，delete删除可以回滚

3.DDL语言

1.库的管理

1.库的创建

create database 【if not exists】 库名;

2.库的修改

#修改库的字符集
alter database 库名 character set utf8;

3.库的删除

drop database 【if exists】 库表;

2.表的管理

1.修改表

#change修改列名
alter table 表名 add|drop|modify|change column 【列名】 列名【列类型 约束】

2.表的复制

#仅复制表的结构
create table 复制后表名 like 表名
#复制表的结构+数据
create table 复制后表名 select * from 表名
#仅复制部分字段
create table 复制后的表名 select 列名、列名 from 表名 where 1=0

3.标识列/自增长列

1.创建表时设置标识列

auto_increment
#设置步长
set auto_increment_increment = 3;

• 标识列的特点：
  1）一个表中只能有一个标识列
  2）标识列可以不与主键搭配
  3）标识列的类型只能是数值型

2.修改表时设置标识列

3.修改表时删除标识列

4.TCL语言（Transaction Control Language/事务控制语言）

1.不是所有存储引擎都支持事务

2.事务的ACID特性

1）原子性（Atomicity）
2）一致性（Consistency）
3）隔离性（Isolation）
4）持久性（Durability）

3.事务的创建

#1.开启事务
set autocommit = 0;
start transaction;可以不写
#2.编写事务中的一组sql语句（select insert update delete）
#3.结束事务
commit;提交事务
rollback;回滚事务

4.事务的隔离级别

1.常见的并发问题

• 脏读
• 不可重复读
• 幻读：主要针对插入

2.事务的隔离级别

• READ UNCOMMITTED（读未提交）：会导致脏读、不可重复读、幻读
• READ COMMITTED（读已提交）-Oracle默认：可解决脏读，会导致不可重复读、幻读
• REPEATABLE READ（可重复读）-mysql默认：可解决脏读、不可重复读，会导致幻读
• SERIALIZABLE（串行化）：可解决脏读、不可重复读，幻读

3.设置回滚点

• savepoint 只能搭配rollback

#1.开启事务
set autocommit = 0;
start transaction;可以不写
#2.编写事务中的一组sql语句（select insert update delete）
sql语句1
savepoint a;
sql语句2
#3.结束事务
rollback to a;回滚事务--只能回滚sql语句2

4.delete和truncate在事务中的区别

• delete支持事务回滚
• truncate不支持事务回滚

5.视图

1.创建视图

create view 视图名 as 查询语句

2.修改视图

#方式1
create or replace view as 查询语句
#方式2
alter view 视图名 as 查询语句

3.删除视图

drop view 视图名,视图名,...

4.更新视图

• 视图可以禁用更新

#插入
insert into 视图名 values();
#修改
update 视图名 set 列名=值 where 筛选条件;
#删除
delete from 视图名 where 筛选条件;

6.变量

1.系统变量

• 由系统提供，不是用户定义
• 查看所有的系统变量

#查看所有的系统变量
show global|[session] variables;
#查看满足条件的部分系统变量
show global|[session] variables like '%char%';
#查看指定的某个系统变量
select @@global|[session].系统变量名;
#为某个系统变量赋值
#方式1
set global|[session]|系统变量名 = 值;
#方式2
set @@global|[session].系统变量名=值;

1.全局变量

2.会话变量

2.自定义变量

• 变量由用户自定义

1.用户变量

• 作用域：针对于当前会话有效
• 可以应用在任何地方

#声明并初始化
set @用户变量名=值;
set @用户变量名:=值;
select @用户变量名:=值;
#赋值（更新用户变量的值）
#方式1
set @用户变量名=值;
set @用户变量名:=值;
select @用户变量名:=值;
#方式2
select 字段 into @用户变量名 from 表;
#使用
select @用户变量名;

2.局部变量

• 作用域：仅仅哎定义它的begin end中有效
• 应用在begin end中，且只能为第一句话
• 需要限定类型

#声明
declare 变量名 类型;
declare 变量名 类型 default 值;
#赋值（更新用户变量的值）
#方式1
set 局部变量名=值;
set 局部变量名:=值;
select @局部变量名:=值;
#方式2
select 字段 into 局部变量名 from 表;
#使用
select 局部变量名;

7.存储过程和函数

1.存储过程

• 含义：一组预先编译好的sql语句的集合，可以理解成批处理语句

1.创建语法

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

• 注意
  1）参数列表包含三部分：参数模式 参数名 参数类型

#参数模式
in：该参数可以作为输入，也就是该参数需要调用方传值
out：该参数可以作为输出，也就是该参数可以作为返回值
inout：该参数既可以输入又可以作为输出，也就是该参数既需要调用方传值又可以返回值

2）如果存储过程体仅仅只有一句话，begin end可以省略
3）存储过程体中的每条sql语句的结尾必须加分号，存储过程的结尾可以使用delimiter重新设置

delimiter 结束标记

• 示例

#空参
delimiter $
create procedure mypl()
begin
	insert into account(id,name,salary) values(10,'lalaaha',1000);
end $
#创建带in模式参数的存储过程
delimiter $
create procedure myp2(in iid int)
begin
	select * from account where id = iid;
end $
#创建带多个in模式参数的存储过程
delimiter $
create procedure myp3(in iid int,in ssalary float)
begin
	select * from account where id = iid and salary > ssalary;
end $
#创建带out模式参数的存储过程
delimiter $
create procedure myp4(in iid int,out nname varchar(40))
begin
	select name into nname from account where id = iid;
end $
#调用
call myp4(2,@nname);
select @nname;
#创建带多个out模式参数的存储过程
delimiter $
create procedure myp5(in iid int,out nname varchar(40),out ssalary float)
begin
	select name,salary into nname,ssalary from account where id = iid;
end $
#调用
call myp5(2,@nname,@ssalary);
select @nname,@ssalary;
#创建带多个inout模式参数的存储过程
delimiter $
create procedure myp6(inout iid int,inout nname varchar(40))
begin
	select id+1,concat(name,nname) into iid,nname from account where id = 1;
end $
#调用
set @iid=2;
set @nname='haha';
call myp6(@iid,@nname);
select @iid,@nname;

2.调用语法

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

3.删除语法

drop procedure 存储过程名;

4.查看存储过程

show create procedure 存储过程名;

2.函数

• 有且仅有一个返回值

1.创建语法

create function 函数名(函数列表) returns 返回类型
begin
	函数体
end

• 注意：
  1）参数列表包含两部分：参数名 参数类型
  2）函数体：肯定会有return语句，如果没有会报错
  3）当函数体仅仅只有一句话，begin end可以省略
  4）使用delimiter语句设置结束标记

2.调用语法

select 函数名(参数列表);

3.查看函数语法

show create function 函数名;

4.删除函数语法

drop function 函数名;

8.流程控制结构

1.顺序结构

2.分支结构

1.if函数

#如果表达式1成立，则if函数返回表达式2的值；否则返回表达式3的值
if(表达式1,表达式2,表达式3)

2.case结构

3.if结构

• 只能应用在begin end中

if 条件1 then 语句1;
elseif 条件2 then 语句2;
...
else 语句n;
end if; 

3.循环结构

-必须放在begin end中

• 循环

#while循环
[标签:]while 循环条件 do 
循环体;
end while[标签];
#loop循环
[标签:]loop
循环体：
end loop[标签];
#repeat循环
[标签:]repeat
循环体;
until 结束循环的条件
end repeat[标签];

-循环控制

#leave类似于break
leave 标签;
#iterate类似于continue
iterate 标签;