1.数据查询语言DQL
1.1. 常见函数
- 类似 Java 的方法
1.1.1单行函数
-
length(‘A’):显示字符串长度
-
concat(‘A’,‘B’):拼接两个字符串
-
subst():按照要求截取字符串
#截取从指定索引处后面所有字符
SELECT SUBSTR(‘李莫愁爱上了陆展元’, 7) out_ put; .
#截取从指定索引处指定字符长度的字符
SELECT SUBSTR(‘李莫愁爱上了陆展元’ ,1,3) out put; -
upper();lower()
把指定字符串更改为大写/小写 -
instr() 返回子串第一次出现的索引,如果找不到返回0
SELECT INSTR(‘杨不殷六侠悔爱上了殷六侠’, ‘殷八侠’) AS out_ _put; -
trim() 去掉中文字符串前后的 非中文字符串(若两个中文之间有非中文则不会被删除)
#去掉中文前后所有非中文
SELECT TRIM( ‘aaaa张翠山aaaaaaaa’) AS out_ put;
#去掉指定的非中文
SELECT TRIM(‘aa’ FROM ‘aaaaa张aaa翠aa山aaaaaaa’) AS out _put; -
lpad用指定的字符实现左填充指定长度(若规定长度小于原字符串长度则会截掉后面多余的)
#SELECT LPAD(‘殷素素’,2,’*’) AS out_ put; . -
rpad用指定的字符实现右填充指定长度(同上)
#SELECT LPAD(‘殷素素’,2,’) AS out_ _put; -
replace替换(表达式1原字符串;表达式2需要替换的字符串;表达式3替换成该字符串)
SELECT REPLACE(‘张无忌爱上了周芷若’,‘周芷若’,‘赵敏’) AS out_ put;
1.1.2 数学函数
-
round 四舍五入
#默认保留一位小数
SELECT ROUND(-1.55) ;
#指定保留几位小数
SELECT ROUND(1.567,2) ; -
ceil向上取整,返回>=该参数的最小整数
SELECT CEIL(-1.02); -
floor 向下取整,返回<=该参数的最大整数
SELECT FLOOR(79.99); -
runcate截断
#第二个参数指定保留几位小数
SELECT TRUNCATE (1.69999,1) ; -
mod取佘
#MySql 取余原理: mod(a,b) : a-a/bb
101 mod(-10,-3):-10- (-10)/(-3) (-3) =-1
SELECT 10%3 —> SELECT MоD(10,-3);
1.1.3 日期函数
-
now返回当前系统日期+时间
SELECTNOW(); -
curdate返回当前系统日期,不包含时间
SELECT CURDATE() ; -
curtime 返回当前时间,不包含日期
SELECT CURTIME () ; -
可以获取指定的部分,年、月、日、小时、分钟、秒
SELECT YEAR(NOW()) 年:
#显示月对应的数字
SELECT MONTH (NOW()) 月;
#显示月对应的英文
SELECT MONTHNAME (NOW()) 月 ;
#其他的同理 -
str_to_date:将日期格式的 字符 转换成指定格式的日期
STR_ TO _DATE(‘9-13-1999’,’%m-%d-%Y’) –> 1999-09-13 -
date_ format:将日期转换成字符
DATE_ FORMAT(‘2018/6/6’,’%Y年%m月 %d ) –> 2018年06月06日
1.1.4 流程控制函数
-
if函数: if else 的效果
SELECT IF(10>5,‘大’,‘小’) -
case when then
#类似于java 中的 case switch
case要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2;
…
else 要显示的值n或语句n;
end#类似于java中的 if else if
case
when 条件1 then要显示的值1或语句1
when 条件2 then要显示的值2或语句2
…
else 要显示的值n或语句n
end
1.1.5分组函数
sum求和、avg平均值、max最大值、min 最小值、count 计算个数
- sum、 avg般用于处理数值型
max、min、count可以处理任何类型
-
以上分组函数都忽略null值
-
可以和distinct搭配实现去重的运算
-
count函数的单独介绍
一般使用count(*) 用作统计行数
-
和分组函数- -同查询的字段要求是group by后的字段
2. MySql 查询
1.2.1 关键字(函数),及其作用
-
distinct —— 去重
在需要去重的列名前加上 distinct 即可。
例: select distinct bookClass from book -
desc —— 显示表结构 / 降序
desc + 表名 -> 显示表的结构
排序时在 加 desc 表降序查询 -
ifnull(’字段名‘,为空时自动填充的值)—— 判断字段值是否为null,若为 null ,则自动填充为第二个参数设的值。
例: select ifnull(’money‘,0) from salary -
escape —— 转义模糊查询中的特殊字符
例: select ·········where name like ‘- $ -A’ escape ’ $ '
(表示把 $ 看成单纯的字符,不具任何意义)
1.2.2 条件查询
- 按条件表达式
< , >, = , != , <> , <= , >= - 模糊查询
- like:
- 包含某字符串
- like(%a%)
- %:匹配任意多个字符,
- -: 匹配单个字符
- between ()and ()
- 包含 临界值
- 顺序不能颠倒
- in:
- select ·········where age in(‘20’,‘30’,‘40’)
(表示是否属于 括号中的几个值;在 in 中不能使用【-、%】)
- select ·········where age in(‘20’,‘30’,‘40’)
- is null / is not null
- 判断是否为 null
1.2.3 分组查询
-
基本格式
SELECT * FROM table WHERE id = 1 GROUP BY class ORDER BY age -
分组查询中的筛选条件分为两类
数据源 | 位置 | 关键字 | |
---|---|---|---|
分组前筛选 | 原始表 | group by子句的前面 | where |
分组后筛选 | 分组后的结果集 | group by子句的后面 | having |
①分组函数做条件肯定是放在having子句中
②能用分组前筛选的,就优先考虑使用分组前筛选
1.2.4 分页查询
-
应用场景: 当要显示的数据过多,一页放不下
-
语法:
select 查询列表
from 表
【join 【type】表2】
【on 链接条件】
【where 筛选条件】
【group by 分组字段】
【having 分组后的筛选条件】
【order by 排序字段】
limit offset , size;
(offset:要显示条目的起始索引,从0 开始
size:要显示的条目数) -
例:
select * from employee limit 0,5;
1.2.5 联合查询
-
union 联合: 将多条查询语句的结果,合并成一个结果
-
使用: 查询的结果来自多个表,且表之间没有很明显的联系
-
语法:
查询语句1
union
查询语句2
。。。 -
特点:
- 要求多条查询语句的查询列数要一致
- 要求每列的含义、顺序要一致
- union 会自动去重; 若不去重 则用 union all
2. 数据操作语言DML
2.1. 插入
-
关键词:insert
-
语法:
1. 方法一
insert into 表名(列名1,列名2,。。。)value(值1,值2,。。。);
2. 方法二
insert into 表名 set 列名1=值1,列名2=值2,。。。 -
要求:
1. 插入的值的类型要与列的类型一致或兼容
2. 不为空的列必须插入值;可以为空的列,直接写null或者列名都不写
3. 列的顺序可以和原表不一致,但是插入的列名和值要一一对应
4. 列数和值的个数一致
5. 可以省略列名,就要写出所有列,且要和原表的顺序一致 -
两种方式区别:
1. 方式一支持插入多行,方式二不支持
2. 方式一支持子查询(把子查询的结果集,对应插入),方式二不支持
2.2. 修改
- 关键词: update
- 语法:
1. 修改单表
update 表名
set 列1=值1,列2=值2,。。。
where 筛选条件;
2. 修改多表
update 表1 别名
type join 表2 别名
on 链接条件
set 列1=值1,列2=值2,。。。
where 筛选条件;
2.3. 删除
- 关键词: 方式1: delete;方式2: truncate
- 语法:
方式一:
delete from 表名 where 筛选条件
方式二:(删除整张表)
truncate table 表名
3. 数据定义语言DDL
3.1. 库的管理
3.1.1 库的创建
- 语法:
create database 【if not exists】 库名; - 注意:若库已存在,则报错
3.1.2 库的修改
- 更改库的字符集
alter database 库名 character set 字符集
3.1.2 库的删除
drop database if exists 库名
3.2. 表的管理
3.2.1 表的创建
- 语法:
creat table 【if not】 exists 表名(
列名1 列的类型【(长度)约束】,
列名2 列的类型【(长度)约束】,
列名3 列的类型【(长度)约束】,
列名4 列的类型【(长度)约束】
)
3.2.2 表的修改
- 修改列名
alter table 表名 change column 旧列名 新列名 【类型】 - 修改列的类型或约束
alter table 表名 modify column 列名 类型 - 添加列
alter table 表名 add column 列名 类型 - 删除列
alter table 表名 drop column 列名 - 修改表名
alter table 表名 rename to 新表名
3.2.3 表的删除
drop table 【if exists】 表名
3.2.4 表的复制
- 只复制表的 结构
create table 新表名 like 被复制的表名 - 只复制表的 部分结构(让所有数据都不满足筛选条件即可)
create table 新表名 select 列名1,列名2,。。。from 被复制的表名 where 1=2 - 复制表的 结构+数据
create table 新表名 select * from 被复制的表名 - 复制表的 结构+部分数据
create table 新表名 select * from 被复制的表名 where 筛选条件
4. 事务控制语言TCL
- 事务:一个或一组 sql 语句组成的一个执行单元,这个执行单元要么全部执行,要么全部不执行。
- 例: 在一组语句执行过程中,某一步出现异常,则回滚,所有的语句都不执行。
4.1 事务的创建
4.1.1 隐式事务:事务没有明显的开始和结束的标记
- 例: insert、update、delete 语句
4.1.2显示事务: 事务具有明显的开始和结束的标记(必须利用JDBC)
- 关闭自动提交(只对当前事务有效;执行该步骤默认开启事务,所以下一步可写可不写):
set autocommit = 0; - 开始事务:
start transaction;(可选) - 编写 sql 语句(select、insert、update、delete)
- 结束事务
commit;(提交事务)
rollback;(回滚)
- 补充:保存点的使用(savepoint——rollback)
- 例:
set autocommit = 0;
start transaction;
delete from student where id = 10;
savepoint a;(设置保存点,取名为 a)
delete from student where id = 20;
rollback to a;(回滚到 名为a 的保存点处)
- 例:
4.1.3 MySql 隔离级别
5. 存储过程
5.1 变量
5.1.1 实例变量
- 全局变量(对全局有效,但不能跨重启,重启后恢复)
- 查看所有【全局变量 | 会话变量】(属于系统变量)的值
show 【global | session】variables; - 查看 满足条件的部分【全局变量 | 会话变量】(属于系统变量)的值
select【global | session】variables like ‘%char%’; - 查看 指定的某一个 【全局变量 | 会话变量】(属于系统变量)的值
select @@【global | session】。变量名;(应为英文的点,这里用句号显眼一点) - 为某一个系统变量复制
set global | session 。系统变量名 = 值;
- 局部变量
- 查看所有局部【会话】变量的值
show 【session】 variables; - 查看 满足条件的部分局部【会话】变量的值
select【session】variables like ‘%char%’; - 查看 指定的某一个 局部【会话】变量的值
select @@【 session】。变量名;(应为英文的点,这里用句号显眼一点) - 为某一个局部【会话】变量赋值
set 【session】 。系统变量名 = 值;
5.1.2 自定义变量
- 用户变量(针对当前会话有效,可以放在任何地方)
- 声明并初始化(三种方式)
set @ 用户变量名 = 值;
set @ 用户变量名: = 值;
select @ 用户变量名: = 值; - 赋值或更新(四种方式)
set @ 用户变量名 = 值;
set @ 用户变量名: = 值;
select @ 用户变量名: = 值;
select 字段 into 变量名 from 表名; - 使用
select @用户变量名;
- 局部变量(只能在定义它的 begin - end 中且 只能在第一句话)
- 声明并初始化(三种方式)
declare 变量名 类型;
declare 变量名 类型 default 默认值; - 赋值或更新(四种方式)
set 局部变量名 = 值;
set 局部变量名: = 值;
select @ 局部变量名: = 值;
select 字段 into 局部变量名 from 表名; - 使用
select 局部变量名;
5.2 存储过程和函数
- 类似于 java 中的方法
5.2.1 存储过程
- 含义: 一组预先编译号的 sql 语句的集合,理解成批处理语句
- 特点:
1. 提高了代码的重用性
2. 简化操作
3. 减少了编译次数并且减少了和数据库的连接次数,提高效率
创建语法
create procedure 存储过程名(参数列表)
begin
存储过程体
end
*注:*
1. 参数列表包括:参数模式 参数名 参数类型
例: in stuname varchar;
- 参数模式:
- in:该参数可以作为输入(需要调用方传入值)(不写默认为 in)
- out:该参数可以作为输出(作为返回值)
- inout:结合前两个
2. 如果存储过程体中只有一句话,begin end 可以省略
3. 存储过程中的每条 sql 语句都必须加 分号 结尾
存储过程的结尾可以使用 delimiter 重新设置
语法: delimiter 结束标记
调用语法
call 存储过程名(实参列表)
- 使用实例:
delimiter $
create procedure demo()
begin
insert into admin(username,password)
values(‘jack’,‘000’);
end $
call demo $
(结束标记设置为 $ ,则后面都要以该字符结尾)
案例:
-
带一个 in 参数
-
带一个 in 参数、一个out 参数
-
带两个 out参数
-
带有 inout 参数
存储过程删除
- 语法: drop procedure 存储过程名;
(一次只能删一个)
查看存储过程结构
- 语法: show create procedure 存储过程名;
5.2.2 函数
- 含义: 一组预先编译号的 sql 语句的集合,理解成批处理语句
- 特点:
1. 提高了代码的重用性
2. 简化操作
3. 减少了编译次数并且减少了和数据库的连接次数,提高效率 - 与存储过程的区别
1. 存储过程可以有 0 个,也可以有多个返回,适用于批量增、删、改操作
2. 函数有且仅有一个返回,是用于查询一个值,或完成操作后返回一个结果
函数创建语法
create function 函数名(参数名 参数类型)returns 返回类型
begin
函数体
end
- 注: 在函数体的最后一句要写 return 要返回的值;
存储过程的结尾可以使用 delimiter 重新设置
函数调用语法
select 函数名(参数列表)
(执行函数体中的操作,并输出返回值)
案例
- 无参数、有返回(需要定义变量作为返回值)
- 有参数、有返回
查看函数的定义
show create function 函数名;
删除函数
drop procedure 函数名;
6. 流程控制结构
6.1 顺序结构
6.2 分支结构
- if 函数(应用于任何地方)
- 语法:if(表达式1,表达式2,表达式3)
- 若 表达式1 成立,则执行 表达式2 ,否则执行 表达式3
- case结构(应用于多个等值判断,或者区间判断)
-
#类似于java 中的 case switch
case要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2;
…
else 要显示的值n或语句n;
end 【case】; -
#类似于java中的 if else if
case
when 条件1 then要显示的值1或语句1
when 条件2 then要显示的值2或语句2
…
else 要显示的值n或语句n
end 【case】; -
注: 可以放在 begin - end 之间,也可以放在外面
- if 结构(不是第一点 if 函数)
if 条件1 then 语句1;
elseif 条件2 语句2;
。。。
【else 语句n】
end if;
- 注: 只能放在 begin - end 之间
6.3 循环结构
- 循环控制 :
- iteration 类似于 java 中的 continue
- leave 类似于 java 中的 break
- while
- 语法:
【标签:】while 循环条件 do
循环体
end while【标签】; - 注:【标签:】类似于给循环起名字
- loop
- 语法:
【标签:】loop
循环体
end loop 【标签】; - 注:用来表示简单的死循环,没有循环条件
- repeat
【标签:】repeat
循环体
until 结束循环的条件
end repeat 【标签】
7. 数据库的原则
7.1 SQL 的 ACID
- A(Atomicity) 原子性
- C(Consistency)一致性
- I(Isolation)隔离性
- D(Durability)持久性
7.2 NoSQL 的 CAP
- C( Consistency)一致性
- A(Availability)可用性
- P(Partition Tolerance)分区容错性