一、创建数据库后修改数据库语句
1.添加语句
alter table 表名 add 字段名 字段类型 comment '别名‘
2.修改数据类型
alter table 表名 modify 字段名 新数据类型
3.修改字段名和字段类型
alter table 表名 change 旧字段名 新字段名 新字段类型 comment '别名'
4.删除字段
alter table 表明 drop 字段名
5.修改数据库表明
alter table 表名 rename to 新表名
6.删除表
drop table if exists 表名
7.删除表名并重建
truncate table 表名
8.DQL语句执行顺序
FORM>WHERE>GROUP BY >SELECT>HAVING>ORDER BY>LIMIT
二、分页
起始索引从0开始，起始索引 = (查询页码-1)*每页显示记录数
三、函数
1.LPAD（str,n,pad)————左填充，向str左边填充pad，直到长度达到n
2.SUBSTRING(str,start,len)————返回字符串str从start开始的len个长度的字符串
3.MOD(x,y)————返回x/y的模
4.ROUND(x,y)————求参数x的四舍五入值，保留y位小数
5.CURDATE()————返回当前日期
6.CURTIME()————返回当前时间
7.NOW()————返回当前日期和时间
8.YEAR(date)————获取指定date的年份
9.MONTH(date)————获取指定date的月份
10.DAT(dat)————获取指定date的日期
11.DATE_ADD(date,INTERVAL expr typer)————返回一个日期/时间加上时间间隔expr后的事件值 ex:SELECT DATE_ADD(NOW(),INTERVAL 70 YRAR);
流程函数
1.IF(value, t, f)————如果value为true，则返回t，否则返回f
2.CASE WHEN [ val1 ] THEN [ res1 ] ... ELSE [ default ] END——如果val1为true，返回res1，... 否则返回default默认值
3.CASE [ expr ] WHEN [ val1 ] THEN [ res1 ] ... ELSE [ default ] END——如果expr的值等于val1，返回res1，... 否则返回default默认值
四、添加外键
CREATE TABLE 表名(
字段名 字段类型,
...
[CONSTRAINT] [外键名称] FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名)
);
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名);
删除外键：ALTER TABLE 表名 DROP FOREIGN KEY 外键名;
五、事务
四大特性：
原子性(Atomicity)：事务是不可分割的最小操作但愿，要么全部成功，要么全部失败
一致性(Consistency)：事务完成时，必须使所有数据都保持一致状态
隔离性(Isolation)：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行
持久性(Durability)：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的
并发事务
**脏读** 一个事务读到另一个事务还没提交的数据，两个窗口同时开启事务，一个窗口修改数据后，但还没提交，另一个窗口立马可以看到已修改数据
**不可重复读** 一个事务先后读取同一条记录，但两次读取的数据不同，开启两个窗口，一个窗口首先查询一次，发现结果为1000，另一个窗口此时修改并提交，第一个窗口查询结果为2000，两次查询数据不一致
**幻读** 一个事务按照条件查询数据时，没有对应的数据行，但是再插入数据时，又发现这行数据已经存在，开启两个窗口，第一个窗口查询数据没有，准备添加，此时第二个窗口添加此条数据，第一个窗口就添加不进去，并且反复查询仍发现为空。
六、索引
索引虽然在查询数据时会增加速度，但是索引列也是要占用空间的，降低了更新的速度，比如 INSERT、UPDATE、DELETE。
聚集索引选取规则
如果存在主键，主键索引就是聚集索引
如果不存在主键，将使用第一个唯一(UNIQUE)索引作为聚集索引
如果表没有主键或没有合适的唯一索引，则 InnoDB 会自动生成一个 rowid 作为隐藏的聚集索引
创建索引：create [unique\fulltext] index 索引名 on table(字段名)
查看索引:show index form 索引名
删除索引：drop index 索引名 on 表名
最左前缀法则
联合索引中，出现范围查询（<, >），范围查询右侧的列索引失效。可以用>=或者<=来规避索引失效问题。
索引失效
1.在索引列上进行运算操作
2.字符串类型字段使用时，不加引号
3.模糊查询中，如果仅仅是尾部模糊匹配，索引不会是失效；如果是头部模糊匹配，索引失效
4.用 or 分割开的条件，如果 or 其中一个条件的列没有索引，那么涉及的索引都不会被用到
5.如果 MySQL 评估使用索引比全表更慢，则不使用索引
explain 中 extra 字段含义
using index condition：查找使用了索引，但是需要回表查询数据
using where; using index;：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询
前缀索引
create index idx_xxxx on table_name(columnn(n));
七、SQL优化
order by优化
1.Using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区 sort buffer 中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序
2.Using index：通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高。
注：多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）
分页查询优化
通过创建 覆盖索引 能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化。
explain select * from tb_sku t , (select id from tb_sku order by id limit 2000000,10) a where t.id = a.id
count优化
按照效率排序的话，count(字段) < count(主键 id) < count(1) ≈ count()，所以尽量使用 count()。
八、视图
视图只保存了查询的SQL逻辑，不保存查询结果
1.创建视图：create [or replace] view 视图名称 as SELECT语句 [WITH [CASCADED | LOCAL ] CHECH OPTION]
2.查看创建视图语句：SHOW CREATE VIEW 视图名称
3.查看视图数据：SELECT * FROM 视图名称
4.修改视图：Alter view 视图名称 as select语句
5.删除视图：DROP VIEW [IF EXISTS] 视图名称 [,视图名称]
CASCADED：级联，即会检查自己，也会检查其他
LOCAL：只会检查自己
九、存储过程
存储过程是事先经过编译并存储在数据库中的一段 SQL 语句的集合
**创建：**
create procedure 存储过程名称（参数列表）
begin
--SQL语句
end；
**调用：**
call 名称（[参数]）
**查看**
show create procedure 存储过程名称
**删除**
drop procedure [if exists] 存储过程名称
用户自定义变量
设置方式一：
用户自定义变量：SET @var_name :=expr [,@var_name :=wxpr]
设置方式二：
SELECT @var_name := expr [, @var_name := expr] ... ;
SELECT 字段名 INTO @var_name FROM 表名;
使用：
SELECT @var_name ;
局部变量
根据需要定义的在局部生效的变量，访问之前，需要DECLARE声明。可用作存储过程内的局部变量和输入参数，局部变量的范围是在其内声明的BEGIN … END块。
声明：DECLARE 变量名 变量类型 [DEFAULT ... ] ;
赋值：
SET 变量名 := 值 ;
SELECT 字段名 INTO 变量名 FROM 表名 ... ;
参数
创建：
CREATE PROCEDURE 存储过程名称 ([ IN/OUT/INOUT 参数名 参数类型 ])
BEGIN
-- SQL语句
END ;
ex：
创建：
create procedure p4(in score int, out result varchar(10))
调用
call p4(18, @result)
查询结果
select @result
IF
IF 条件1 THEN
.....
ELSEIF 条件2 THEN -- 可选
.....
ELSE -- 可选
.....
END IF;
case
方式一：
CASE case_value
WHEN when_value1 THEN statement_list1
[ WHEN when_value2 THEN statement_list2] ...
[ ELSE statement_list ]
END CASE;
方式二：
CASE
WHEN search_condition1 THEN statement_list1
[WHEN search_condition2 THEN statement_list2] ...
[ELSE statement_list]
END CASE;
while
WHILE 条件 DO
SQL逻辑...
END WHILE;
ex:
create procedure test(in num int)
begin
declare total in default 0;
while num>0 do
set total := total+n
set num := num - 1
end while;
select total;
end;
claa test(100);
repeat
repeat是有条件的循环控制语句, 当满足until声明的条件的时候，则退出循环.
先执行一次逻辑，然后判定UNTIL条件是否满足，如果满足，则退出。如果不满足，则继续下一次循环
REPEAT
SQL逻辑...
UNTIL 条件
END REPEAT;
loop
LOOP 实现简单的循环，如果不在SQL逻辑中增加退出循环的条件，可以用其来实现简单的死循环。
LOOP可以配合一下两个语句使用：
LEAVE ：配合循环使用，退出循环。
ITERATE：必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环。
创建：
[begin_label:] LOOP
SQL逻辑...
END LOOP [end_label];
LEAVE label; -- 退出指定标记的循环体
ITERATE label; -- 直接进入下一次循环
游标
游标（CURSOR）是用来存储查询结果集的数据类型 , 在存储过程和函数中可以使用游标对结果集进行循环的处理。游标的使用包括游标的声明、OPEN、FETCH 和 CLOSE
声明游标：DECLARE 游标名称 CURSOR FOR 查询语句 ;
打开游标：OPEN 游标名称 ;
获取游标记录：FETCH 游标名称 INTO 变量 [, 变量 ] ;
关闭游标：CLOSE 游标名称 ;
注：先声明变量，在声明游标
ex:
-- 逻辑:
-- A. 声明游标, 存储查询结果集
-- B. 准备: 创建表结构
-- C. 开启游标
-- D. 获取游标中的记录
-- E. 插入数据到新表中
-- F. 关闭游标
create procedure p11(in uage int)
begin
declare uname varchar(100);
declare upro varchar(100);
declare u_cursor cursor for select name,profession from tb_user where age <=uage;
drop table if exists tb_user_pro;
create table if not exists tb_user_pro(
id int primary key auto_increment,
name varchar(100),
profession varchar(100)
);
open u_cursor;
while true do
fetch u_cursor into uname,upro;
insert into tb_user_pro values (null, uname, upro);
end while;
close u_cursor;
end;
call p11(30);
条件处理程序
条件处理程序（Handler）可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤
declare exit handler for SQLSTATE '02000' close u_cursor;
-- 声明条件处理程序 ： 当SQL语句执行抛出的状态码为02000时，将关闭游标u_cursor，并退出
十、存储函数
存储函数是有返回值的存储过程，存储函数的参数只能是IN类型的
characteristic说明：
DETERMINISTIC：相同的输入参数总是产生相同的结果
NO SQL ：不包含 SQL 语句。
READS SQL DATA：包含读取数据的语句，但不包含写入数据的语句。
ex:
十一、触发器
触发器是与表有关的数据库对象，指在insert/update/delete之前(BEFORE)或之后(AFTER)，触发并执行触发器中定义的SQL语句集合。
**创建**
create trigger 触发器名字
before/after insert/update/delete on 表名
for each row ---------行级触发器
begin
trigger_stmt;
end;
查看
show triggers;
删除
drop trigger 触发器名字
ex:
十二、SpringBoot+Mybatis简单分页查询
1.添加依赖
<!--Mybatis分页插件-->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>1.4.0</version>
</dependency>
2.配置yml文件
pagehelper:
helperDialect: mysql
reasonable: true
supportMethodsArguments: true
params: count=countSql
<!--supportMethodsArguments:true 自动分页，如果参数中有pageNum，pageSize分页参数，则会自动分页-->
helper-dialect:属性来指定分页插件使用哪种方言。
reasonable：分页合理化参数，默认值为false。当该参数设置为 true 时，pageNum<=0 时会查询第一页， pageNum>pages（超过总数时），会查询最后一页。默认false 时，直接根据参数进行查询。
3.查询数据
//获取第1页，10条内容，默认查询总数count
PageHelper.startPage(1, 10);
List<Country> list = countryMapper.selectAll();
//用PageInfo对结果进行包装
PageInfo page = new PageInfo(list);
return page;
参数：
pageNum：当前页的页码
pageSize：每页显示的条数
size：当前页显示的真实条数
total：总记录数
pages：总页数
prePage：上一页的页码
nextPage：下一页的页码
isFirstPage/isLastPage：是否为第一页/最后一页
hasPreviousPage/hasNextPage：是否存在上一页/下一页
navigatePages：导航分页的页码数
navigatepageNums：导航分页的页码，[1,2,3,4,5]
————————————————————————————————————————————————————————————————————————
第二种写法：
public class PageUtil {
/**
* 开始分页
* @param list
* @param pageNum 页码
* @param pageSize 每页多少条数据
* @return
*/
public static List startPage(List list, Integer pageNum,
Integer pageSize) {
if (list == null) {
return null;
}
if (list.size() == 0) {
return null;
}
Integer count = list.size(); // 记录总数
Integer pageCount = 0; // 页数
if (count % pageSize == 0) {
pageCount = count / pageSize;
} else {
pageCount = count / pageSize + 1;
}
int fromIndex = 0; // 开始索引
int toIndex = 0; // 结束索引
if (!pageNum.equals(pageCount)) {
fromIndex = (pageNum - 1) * pageSize;
toIndex = fromIndex + pageSize;
} else {
fromIndex = (pageNum - 1) * pageSize;
toIndex = count;
}
List pageList = list.subList(fromIndex, toIndex);
return pageList;
}
}
public PageInfo<IpAddrModel> getAllIpOnLineAddrList(IpAddrExample example) {
int pageIndex = 1;
int pageSize = 10;
if(StringUtils.isNotBlank(example.getPageNumber()+"")){
pageIndex = example.getPageNumber()+1;
}
if(StringUtils.isNotBlank(example.getPageSize()+"")){
pageSize = example.getPageSize();
}
List<IpAddrModel> list = ipAddrMapper.getAllIpOnLineAddrList(example);
List<IpAddrModel> pageList = PageUtil.startPage(list,pageIndex,pageSize);
PageInfo<IpAddrModel> pageInfo = new PageInfo<IpAddrModel>();
pageInfo.setPageNum(pageIndex);
pageInfo.setPageSize(pageSize);
pageInfo.setTotal(list.size());
pageInfo.setList(pageList);
return pageInfo;
}
十三：SpringBoot+MaybatisPlus分页查询
1.引入依赖
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.4.3</version>
</dependency>
2.配置中间件
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
@Configuration
public class MPInterceptor {
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor(){
MybatisPlusInterceptor interceptor=new MybatisPlusInterceptor();
interceptor.addInnerInterceptor(new PaginationInnerInterceptor());
return interceptor;
}
}
3.方法实现
@Resource
private StudentMapper studentMapper;
/**
* 分页1
* @param current
* @param size
* @return *** list对象 ***
*/
@Override
public List<Student> selectStudentPage(int current, int size) {
QueryWrapper<Student> queryWrapper = new QueryWrapper<>();
// 判断年龄大于20岁的，这里用的是mybatis-plus的条件构造器
queryWrapper.gt("age",20);
/**
* 加上false就不查询总记录数啦,如果不需要的话可以加上false，这样就会少一条sql语句
* Page<Student> page = new Page<>(current,size,false);
*/
Page<Student> page = new Page<>(current,size);
// queryWrapper 可以为null 不进行条件判断
// 返回list 调用 selectPage
IPage<Student> userIPage = studentMapper.selectPage(page, queryWrapper);
System.out.println("总页数"+userIPage.getPages());
System.out.println("总记录数"+userIPage.getTotal());
//getRecords()分页对象记录列表
List<Student> records = userIPage.getRecords();
return records;
}
MyBatisPlus配置文件
mybatis-plus:
global-config:
db-config:
table-prefix: tbl_ #设置表名通用前缀
id-type: auto #设置主键id字段的生成策略为参照数据库设定的策略，当前数据库设置id生成策略为自增
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl 在控制台打印日志信息
7289
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
