数据库
一，MySQL基础
1，单行函数
1，字符函数
- 长度函数：LENGTH
SELECT LENGTH (last_name) from user ; -- 字节长度
- 字符串拼接：CONCAT
select concat(last_name,'_',first_name) as 姓名 from user
- 大小写转换：UPPER ， LOWER
select concat(UPPER(last_name),'_',LOWER(first_name)) as 姓名 from user
- 字符串截取：substr , substring
-- 索引从1开始，往后截取（字符长度）
select substr('好好学习,天天向上',6) 目标; -- 天天向上
-- 从1开始截取4个长度
select substr('好好学习,天天向上',1,4) 目标; -- 好好学习
- 起始索引位置：instr
-- 第一次出现的起始位置索引，没有则返回 0
select instr('好好学习,天天向上','天') 位置;
- 去空格：trim
-- 去除前后空格，中间不管
select trim(' 好好学习, 天天向上 ') 目标; -- 好好学习, 天天向上
-- 去除前后连续的a字符
select trim('a' from 'aaaaa好好学习, aa天天向上aaaaa') 目标;-- 好好学习, aa天天向上
- 左右填充：lpad，rpad
-- 从左开始按照10个字符长度用day填充
SELECT lpad('好好学习',10,'day')； -- dayday好好学习
SELECT rpad('好好学习',10,'day')； -- 好好学习dayday
- 替换：replace
select replace('aaaaa好好学习, aa天天向上aaaaa','a', '') 目标;
2，数学函数
- 四舍五入：round
-- 默认取整
select round(1.23); -- 1
-- 四舍五入保留小数后2位
select round(1.2356,2); -- 1.24
- 向上取整：ceil
-- 向上取整，返回>=当前值的最小整数
select ceil(1.23) -- 2
- 向下取整：floor
-- 向下取整，返回<=当前值的最大整数
select floor(1.23) -- 1
- 截断：truncate
-- 直接截断，保留小数后2位
select truncate(1.234,2); -- 1.23
- 取余：mod
select mod(10,-3); -- 1
3，日期函数
- 当前时间（包含时分秒）：now()
select now(); --2019-10-22 22:44:09
- 当前日期（不包含时分秒）：curdate()
select curdate(); -- 2019-10-22
- 当前时间（只有时分秒）：curtime()
select curtime(); -- 22:45:41
- 特指年月日：
SELECT YEAR(now());
SELECT MONTH(now());
SELECT DAY(now());
- 日期格式化：str_to_date
SELECT STR_TO_DATE('2019-10-22','%Y-%m-%d');
- 日期转字符：str_to_date
SELECT DATE_FORMAT(now(),'%Y年%m月%d日')；-- 2019年10月22日
日期格式化格式符
| 格式符 | 功能 |
|---|---|
| %Y | 4位年份，如 2019 |
| %y | 2位年份 ，如 19 |
| %m | 月份，如 01，02，，，11，12 |
| %c | 月份，如 1，2，，，11，12 |
| %d | 日，如 01，02，，， |
| %H | 时，24小时制 |
| %h | 时，12小时制 |
| %i | 分钟，如 00，01，，，58，59 |
| %s | 秒，如 00，01，，，58，59 |
4，其他函数
不限于此
-- 当前版本
SELECT VERSION();
-- 当前数据库
SELECT DATABASE();
-- 当前用户
SELECT User();
5，流程控制函数
- 三元运算，IF
select if(1>0,'1大','0大'); -- 1大
- case…when…then
select salary,department
case department
when 'java' then salary*2
when 'python' then salary*3
when 'bigdata' then salary*4
else salary
end as '绩效工资'
where employees;
2，分组函数
- 求和，sum （只支持数值型，忽略为null的行）
select sum(salary) from employees;
- 求平均，avg（只支持数值型，忽略为null的行）
select avg(salary) from employees;
- 最小值，min（支持数值型，字符型，日期型，忽略为null的行）
select min(salary) from employees;
- 最大值，max（支持数值型，字符型，日期型，忽略为null的行）
select max(salary) from employees;
- 数量统计，count （该表中参数行非空的个数统计）
select count(1) from employees;
select count(*) from employees;
-- 以上的区别，count(1) 相当于在列中加一个为1的常量值列，这个列贯穿所有行，可做总行数统计。coun(*) 相当于统计含有字段不为空的所有行，可做总行数统计；
-- 在Myisam引擎下，count(*)效率高（引擎自带次函数），在Innodb引擎下，count(1)和count(*)效率相仿，但高于count(字段)，因为字段需要做判空
特点：
- sum ，avg一般只处理数值类型；
- min，max，count支持所有类型；
- 分组函数都忽略null值；
- 可以和distinct搭配实现去重；
-- 第一个根据薪水去重再求和，第二个直接求和
select sum(distinct salary),sum(salary) from employees;
-- 根据薪水去重再算数量
select count(distinct salary) from employees;
- 和分组函数一同查询的字段需要是group by后的字段；
3，分组查询
- 分组筛选：group by
select 分组函数 ，列（出现在group by 后面的字段）
from 表
where 条件 -- 分组前的筛选条件
group by 分组的列
order by 排序；
-- 如：统计部门平均工资
select avg(salary),department
from employees group by department;
- 分组后的筛选：having （having后可以放原始函数，如：max，min，count等）
-- 根据部门分组筛选出人数大于100的
select count(*),department
from employees group by department
having -- 分组后的筛选条件
count(*)>100;
- 多字段分组：
-- 多字段分组顺序可以不一致
select department,job,work_no
from employees group by department,work_no,job
特点：
- 分组筛选条件分两种：
| 数据源 | 位置 | 关键字 | |
|---|---|---|---|
| 分组前筛选 | 当前表 | group by 前面 | where |
| 分组后筛选 | 分组后的结果集 | group by 后面 | having |
- 优先考虑分组前筛选；
- 分组函数做查询一定属于分组后筛选；
- 支持别名；
4，关联查询
SQL92标准
- 等值连接（取交集）
-- 当关联表中存在相同字段名称时，需要制定到表
select e.name 员工名称,d.name 部门名称 from employees e,department d where e.dep_id=d.id;
- 非等值连接
select e.salary,g.grand
from employees e,grand g
where e.salary between g.lowest and g.highest;
- 自连接
-- 查询员工名称及其对应的领导名称
select e.name as 员工名称,m.name as 领导名称
from employees e,employees m
where e.leander_id=m.id
SQL99标准
- 内连接 inner join … on
select e.name 员工名称,d.name 部门名称
from
employees e inner join department d
on e.dep_id=d.id; -- on 等同于 where
- 外连接 left join … on(左外:左主) ; right join … on(右外:右主)
-- 主表不为null的全部查询出来，从表为空用null填充
select e.name 员工名称,d.name 部门名称
from
employees e left join department d
on e.dep_id=d.id;
- 全外连接 full
-- 查询所有，一方为空时用null填充
select e.name 员工名称,d.name 部门名称
from
employees e full join department d
on e.dep_id=d.id;
- 交叉连接（笛卡尔积） cross
select e.name 员工名称,d.name 部门名称
from
employees e cross join department d
on e.dep_id=d.id;
5，子查询
- select子查询：只能子查询一行一列的
-- 查询每个部门的员工个数
selec d.* ,(
select count(*) from employees e
where e.dep_id = d.id
) from department d;
- from子查询：子查询结果当做表使用
- where/having子查询
- exists子查询：标识查询的结果是否存在，返回1（存在）或0（不存在）
6，分页查询
- limit
-- limit 开始索引，查询条数
select * from employees limit 5;
select * from employees limit 0,5;
7，联合查询
- union：将多条语句的查询结果合并成一个结果
select name,age from employees
union
select user_name,user_age from user;
特点：
- 要求union的多条查询语句字段数量一致，否则报错；
- union的字段顺序最好一致，默认按照第一条语句的字段名称顺序显示；
- union会根据所查字段内容进行去重，如果多条sql中有相同的字段结果则会被去重，如果想避免去重，需使用union all ；
8，六大约束&一大标识
- not null：非空约束，保证字段非空；
- default：默认约束，保证有默认值；
- primary key：主键约束，保证非空且唯一；
- unique：唯一约束，唯一但可以为空；
- check：检查约束，mysql不支持；
- foreign key：外键约束，限定表之间的关系 ；
- auto_increment：自增长，定义列的自增长标识；通过set auto_increment_increment=10，来设置自增步长，alter table table_name auto_increment =10000，设置自增初始值；
9，事务
mysql存储引擎Innodb支持事务；
事务的ACID
- 原子性（Atomicity）：是指事务是一个不可分割的单元，事务中的操作要么全发生，要么都不发生；
- 一致性（Consistency）：事务必须使数据库从一个一致性状态变换到另一个一致性状态；
- 隔离性（Isolation）：是指一个事务的执行不能被其他事务干扰，即一个事务的内部操作及使用的数据对并发的其他操作的事务是隔离的，并发执行的各个事务之间不能相互干扰；
- 持久性（Durability）：是指一个事务一旦被提交，那么他对数据库中数据的改变是永久性的，即使数据库故障都不应该对其有任何影响；
事务的创建
- 隐式事务：事务没有明显的开启和结束的标记，如：insert、update、delete语句的执行；
- 显式事务：事务具有明显的开启和结束的标记，但必须关闭事务的自动提交，手动去触发事务开启、关闭，如：用set autocommit=0关闭事务的自动提交（关闭只对当前事务有效），用commit提交事务，用rollback回滚事务；
常见数据库读错误
- 脏读：对于两个事务T1，T2，T1读取了T2更新但没有提交的数据，之后若T2回滚了，T1读取的内容就是临时且无效的数据（更新时）；
- 不可重复读：对于两个事务T1，T2，T1读取了一字段，然后T2修改了该字段，之后T1再去读取该字段发现值不一样了；
- 幻读：对于两个事务T1，T2，T1从表中读取一些行，然后T2在表中插入了新的行，之后T1再去读取的时候发现行多了（插入时）；
事务的隔离级别
| 隔离级别 | 表述 |
|---|---|
| read uncommited（读未提交数据） | 允许事务读取其他事务没有提交的变更，可能会出现脏读、不可重复读、幻读 |
| read commited（读已提交数据） | 只允许事务读取其他事务已经提交的变更，可以避免脏读，但不能避免不可重复读、幻读 |
| repeatable read（可重复读） | 确保事务可以多次从一个字段中读取相同的值，在本次事务持续期间，禁止其他事务对这个字段进行更改，可以避免脏读、不可重复读，但可能出现幻读 |
| serializable（串行化） | 确保事务可以多次从一个字段中读取相同的值，在本次事务持续期间，禁止其他事务对该整张表的插入、删除、修改，可以避免所有并发问题，但性能低 |
注：
- mysql默认隔离级别为repeatable read（可重复读）；
- oracle默认隔离级别为read commited（读已提交）
- delete支持事务回滚，truncate不支持事务回滚
- repeatable read相当于行级锁，serializable相当于表级锁；
回滚点
set autocommit = 0;
start transaction ;
delete from user where id = 10;
savepoint a;-- 设置名称为a的保存点
delete from user where id = 11;
rollback to a;-- 回滚到保存点a，此时10呗删除，11得以回滚
10，视图
一种虚拟的表，和普通表一样使用，可以将一些复杂的查询封装的一个视图中，当做一个新的临时表，在使用时动态生成虚拟表；
- 创建视图
create view 视图名称V1
as
复杂的sql语句；
-- 使用视图
select * from V1 where xxx
- 修改视图
create or replace view 视图名称V1
as
复杂的sql语句;
------------------------
alter view 视图名称V1
as
复杂的sql语句;
- 删除视图
drop view 视图名V1,视图名V2....
- 视图查看
desc 视图名称V1;
show create view 视图名称V1；
- 视图修改：对视图的增删改都会同步修改原始表，但复杂sql中包含：分组函数、distinct、group by、having、union、union all、子查询、join、from后跟一个不能修改的视图、where语句的子查询引用了from中的表时，或视图为常量视图时，视图不允许更新；
11，变量
系统变量
- 全局变量：
-- 查看全部系统变量
show global variables;
-- 查看指定系统变量
select @@global 系统变量名 ;-- 或模糊查询 show global variables like ‘xxx’
- 会话变量
-- 查看全部会话变量 session可以省略，查询方式同上
show session variables;
- 系统变量赋值
-- 或 set @@ 系统变量名 = 值;
set 系统变量名 = 值;
自定义变量
- 用户变量
-- 初始化/赋值
set @用户变量名 = 值;
set @用户变量名 := 值;
select @用户变量名 := 值;
- 局部变量
-- 只在begin ... end 中有效
declare 变量名 类型 default 值;
-- 初始化/赋值
set 局部变量名 = 值;
set 局部变量名 := 值;
select @局部变量名 := 值;
select 值 into 变量名
-- 使用
select 局部变量名;
12，存储过程&函数
一组预先编译好的sql语句的集合；
存储过程
存储过程创建
delimiter $
create procedure 存储过程名(参数列表)
begin
存储过程体（一组sql语句体）;-- 过程体只有一句时，begin...end 可以省略
end $
-- 参数列表：参数模式 参数名 参数类型，如：in nume varchar(20)
-- 参数模式：in、out、inout，in修饰的为入参，out修饰的可以为出参，inout两者皆可
delimiter 结束标记 -- 如：delimiter $
存储过程调用
call 存储过程名(实参列表) $ -- 注意结束标记，需要统一
函数
函数创建
delimiter $
create function 函数名(参数列表) returns 返回类型
begin
函数体;（一组sql语句体，含return 值）;-- 过程体只有一句时，begin...end 可以省略
return 结果;
end $
-- 参数列表：参数名 参数类型，如：in nume varchar(20)
-- 函数体：必须有返回体，否则会报错
delimiter 结束标记 -- 如：delimiter $
函数调用
select 函数名(实参列表) $ -- 注意结束标记，需要统一
13，流程控制结构
- 顺序结构
-- if函数
select if(表达式1,表达式2,表达式3)
-- 如果表达式1为true，则返回表达式2，否则返回表达式3
-- if结构(只能用在begin...end中)
if 条件1 then 语句1;
elseif 条件2 then 语句2;
...
else 语句n;
end if;
- 分支结构
-- case1
select xxx
case 变量|表达式|字段
when 要判断的值 then 返回值1或语句1;
when 要判断的值 then 返回值2或语句2;
...
else 返回值n或语句n;
end case;
-- case2
select xxx
case
when 条件1 then 返回值1或语句1;
when 条件2 then 返回值2或语句2;
...
else 返回值n或语句n;
end case;
- 循环结构
-- while
名称1: while 循环条件 do
循环体;
end while 名称1;
-- loop
名称2: loop
循环体;
end loop 名称2;
-- repeat
名称3: repeat
循环体;
until 结束条件
end repeat 名称3;
实例
-- 向user 表中插入数据，只插入为偶数时的数据，并且超过100的时候退出
delimiter $
create procedure test(into num int)
begin
declare i int default 0;
a:while i<=num do
set i=i+1;
if mod(i,2)!=0 then iterate a; -- iterate(相当于java中continue)
end if;
if i==100 then leave a; -- leave(相当于java中break)
end if;
insert into user(username,password) values(concat('neei',i),1000+i);
end while a;
end $
14，DQL、DML、DDL、DCL的概念与区别
参考博客 ：DQL、DML、DDL、DCL的概念与区别
244
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
