目录
单行函数
常见字符函数
1.length 获取参数值的字节个数
2.concat 拼接字符串
3.upper lower 输出参数为大写或者小写
4.substr/substring 截取字符串(Mysql中索引从1开始)
5.instr 返回子串在总串第一次出现的索引
6.trim 去前后空格 或者去除前后指定的字符
7.lpad 用指定的字符实现左填充指定长度
8.rpad 用指定的字符实现右填充指定长度
9.replace 替换字符串
常见数学函数
1.round 四舍五入(可选择保留位数)
2.ceil 向上取整 返回>=该参数的最小整数
3.floor 向下取整 返回<=该参数的最大整数
4.truncate 截断小数点后位数
5.mod 取余 mod(a,b) : a-a/b*b
常见日期函数
1.now 返回当前系统日期+时间
2.curdate 返回当前系统日期,不包含时间
3.curtime 返回当前时间,不包含日期
4.year month monthname day hour minute second 获取指定的年 月 日 时 分 秒
5.str_to_date 将日期格式的字符转换成指定格式的日期
6.date_format 将日期转换成字符
其他函数
1.version 查看版本
2.database 查看数据库
3.user 查看用户
流程控制函数
1.if 类似if else函数的效果 select if(10<5,'大','小')
2.case 使用一: 类似switch case 的效果
case 要判断的字段或表达式
when 常量1 then 要显示的值1或者语句1
when 常量2 then 要显示的值2或者语句2
...
else 要显示的值n或者语句n
end;
case的使用二: 类似多重if
case
when 条件1 then 要显示的值1或者语句1
when 条件2 then 要显示的值2或者语句2
...
else 要显示的值n或者语句n
end;
分组函数
常见: sum 求和 ,avg 平均值 ,max 最大值,min 最小值,count 计算个数
特点: 1.sum,avg一般处理数值型 max,min,count可以处理任何型
2.以上分组函数都忽略null值
和分组函数一同查询的字段要求是group by后的字段
分组查询筛选分为两类
数据源 位置 关键字
1.分组前筛选 原始表 group by子句前面 where
2.分组后筛选 分组后的结果集 group by子句后面 having
分组函数作条件肯定是放在having子句中的
能在分组前作筛选的,优先考虑使用分组前筛选(性能更好)
group by支持单个或者多个字段分组,也支持函数分组
连接查询(多表查询)
等值连接
1.多表等值连接的部分为多表的交集部分
2.n表等值连接,则至少需要n-1个连接条件
3.一般需要为表起别名
非等值连接
使用where条件即可,十分简单
自连接
给表起别名可以避免歧义
Sql99语法
语法:
select 查询列表
from 表1 别名 【连接类型】
join 表2 别名
on 连接条件
【where 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序列表】
分类:
内连接:inner
外连接:
左外:left 【outer】
右外:right 【outer】
全外:full 【outer】
交叉连接:cross
内连接
语法:
select 查询列表
from 表1 别名
inner join 表2 别名
on 连接条件;
得到两表的交集部分
外连接
应用场景:一个表有,另外一个表没有的记录
特点:1.外连接的查询结果为主表中的所有记录,如果从表中有和它匹配的,则显示匹配的值,如果从表中没有和它匹配的,则显示null。外连接的查询结果=内连接结果+主表有而从表中没有的记录
2.左外连接,left join 左表的是主表
右外连接,right jon右边的是主表
3.右外和左外交换两个表的顺序,可以实现同样的效果
全外连接(mysql不支持)
全外连接=内连接的结果+表1中有但表2中没有的+表2中有但表1中没有的
交叉连接
使用sql99语法实现笛卡尔乘积
连接使用示意图
子查询
含义:出现在其他语句重点select语句。称为子查询或者内查询
外部的查询语句,称为主查询或外查询
按子查询的位置分:
select后面:
仅仅支持标量子查询
from后面
支持表子查询
where或having后面
标量子查询(单行)√
列子查询(多行)√
行子查询
exists后面(相关子查询)
表子查询
按结果集的行列数不同:
标量子查询(结果集只有一行一列)
列子查询(结果集只有一列多行)
行子查询(结果集有一行多列)
表子查询(结果集一般为多行多列)
在where或having后面特点:
1.子查询放在小括号内
2.子查询一般放在条件的右侧
3.标量子查询,一般搭配单行操作符使用> ,< ,>=, <=, =,<>
列子查询,一般搭配多行操作符使用 in,any/some,all
4.子查询的执行先于主查询,主查询的条件用到了子查询的结果
分页查询
语法:在最末尾加上 limit offset,size
offset:要显示条目的起始索引(从0开始)
size:要显示的条目个数
offset*n*size:页数
size:一页多少条数据
联合查询
union 联合 合并:将多条查询语句的结果合并成一个结果
语法:
查询语句1
union
查询语句2
union
...;
应用场景:要查询的结果来自多个表,且多个表没有直接的连接关系,但查询的信息一致时
特点:
1.要求多条查询语句的查询列数是一致的
2.要求多条查询语句的查询的每一列的类型和顺序最好一样
3.union默认是去重的 如果使用union all 可以不去重
DDL语言(库管理)
库管理
库的创建
create database 【if not exists】 库名;
库的修改
修改库名:rename database 现在库名 to 新库名 (有安全隐患,会丢失数据,现已不用)
更改的库的字符集:alter database 库名 character set 字符集
库的删除
drop database 【if exists】 库名
表管理
表的创建
create table 表名(
列名 列的类型 【(长度) 约束】,
列名 列的类型 【(长度) 约束】,
列名 列的类型 【(长度) 约束】,
...
)
表的修改
1.修改列名
2.修改列的类型或者约束
3.添加新列
4.删除列
5.修改表名
表的删除
drop table 表名
TCL(事务控制语言)
事务的特性:ACID
原子性:一个事务不可再分割,要么都执行,要么都不执行
一致性:一个事务的执行会使数据从一个一致状态到另外一个一致状态
隔离性:一个事务的执行不受其他事务的影响
持久性:一个事务一旦提交,则会永久改变数据库的数据
隐式事务:事务没有明显的开启和结束的标记,比如inset,delete,update语句
显式事务:事务具有明显的开启和结束的标记
前提:必须先设置自动提交功能禁用 set autocommit=0;
步骤1:开启事务
set autocommit=0;
start transaction; 可选的
步骤2:编写事务中的sql语句(select insert delete update)
语句1
【savepoint 回滚点名】 //设置回滚点
语句2
...
步骤3:结束事务
commit 提交事务
rollback 回滚事务 / rollback to 回滚点
事务的隔离级别:
脏读 不可重复读 幻读
read uncommitted √ √ √
read committed × √ √
repeatable read × × √
serializable × × ×
mysql中默认第三个隔离级别 repeatable read
oracle中默认第二个隔离级别 read committed
查看隔离级别
select @@tx_isolation
设置隔离级别
set session|global transaction isolation level 隔离级别;
删除的回滚:delete支持回滚 truncate不支持回滚
视图
定义:视图的动态生成的,视图中保存的是sql逻辑,而不是具体的数据。可将频繁使用或较为复杂的sql语句存储在视图中,方便使用;
语法:
create view 视图名
as
sql语句;
视图的修改:
方式一:
create or replace view 视图名
as 查询语句;
方式二:
alter view 视图名
as 查询语句;
视图的删除:
语法:drop view 视图名,视图名,视图名.....;
视图的查看:
desc 视图名;
show create view 视图名;
视图的更新:
可以在视图中插入或者删除,会影响到原始表,不是佷安全,部分视图无法更新。
具备以下特点的视图不允许更新:
1.包含以下关键字的sql语句:分组函数,distinct,group by,having,union,union all
2.常量视图:搜索的值是一个常量
变量
略过(需回头查看相关知识)
存储过程
创建语法:
create procedure 存储过程名(参数列表)
begin
存储过程体(一组合法的sql语句)
end
参数列表包含三部分
参数模式 参数名 参数类型 如下例子:
in stuname varchar(20)
参数模式:
in:该参数可以作为输入,也就是该参数需要调用方传入值
out:该参数可以作为输出,该参数可以作为输出值
inout:该参数既可以作为输入也可以作为输出,也就是该参数既需要传入值,又可以返回值
如果存储过程体仅仅只有一条sql语句,begin end 可以省略
存储过程体的每条sql语句的结尾要求必须加分号
存储过程的结尾可以使用delimiter重新设置
语法: delimiter 结束标记
调用语法: call 存储过程名(实参列表);
存储过程的删除:
drop procedure 存储过程名
存储过程的查看:
show create procedure 存储过程名
函数
存储过程:可以有0个或者多个返回,适合做批量插入、批量更新
函数:有且仅有1个返回,适合做处理数据后返回一个结果
创建语法:
create function 函数名(参数列表) returns 返回类型
begin
函数体
end
参数列表包含两部分:参数名 参数类型
需要使用delimiter语句设置结束标记
调用语法:
select 函数名(参数列表)
查看函数: show create function 函数名
删除函数: drop function 函数名
流程控制结构
顺序结构:程序从上往下依次执行
分支结构:程序从两条或者多条路径中选择一条去执行
循环结构:程序在满足一定条件的基础上,重复执行一段代码
分支结构
1.if函数(实现简单的双分支)
if(表达式1,表达式2,表达式3)
如果表示式1成立 返回表示式2,否则返回表达式3
2.case结构
case 使用一: 类似switch case 的效果
case 要判断的字段或表达式
when 常量1 then 要显示的值1或者语句1
when 常量2 then 要显示的值2或者语句2
...
else 要显示的值n或者语句n
end;
case的使用二: 类似多重if
case
when 条件1 then 要显示的值1或者语句1
when 条件2 then 要显示的值2或者语句2
...
else 要显示的值n或者语句n
end;
3.if结构
if 条件1 then 语句1;
elseif 条件2 then 语句2;
...
end if;
应用在begin end 中
循环结构
分类:while loop repeat
循环控制:iterate类似于continue,继续结束本次循环,继续下一次循环。
leave类似于break,跳出,结束当前所在的循环
1.while
【标签:】while 循环条件 do 循环体;
end while 【标签】;
2.loop
【标签:】loop 循环体;
end loop 【标签】;
可以用来模拟简单的死循环
3.repeat
【标签:】repeat 循环体;
until 结束循环的条件 end repeat 【标签】;