数据库

NA
239 阅读12分钟

Oracle

  1. 全表备份

    创建ddl：create table tableName_bak as (select * from tableName) definition only;

    插入数据：insert into tableName_bak select * from tableName;

  2. 替换函数 replace(原字段，“原字段旧内容“,“原字段新内容“)

  3. 日期和字符转换函数用法 to_date("2022-04-03"，"yyyy-mm-dd") to_char(sysdate，"yyyymmdd")

  4. 截取字符串substr(char，m，n)

  5. 四舍五入round(sal，1)

  6. with temp as 把这个子查询包起来，后边直接用temp. 就可以了

  7. 聚合函数sum，count，max，avg

  8. having by 是分组group by 后的筛选条件

  9. truncate删除比delete快，delete会先copy到回滚表，truncate直接删除

  10. 并集：Union All比Union(去重)快

  11. oracle自增序列

--创建示例表 --
create table Student(
    stuId number(9) not null,
    stuName varchar2(20) not null,
    stuMsg varchar2(50) null
)
  
  -- 创建序列  Student_stuId_Seq 
 create sequence Student_stuId_Seq
 increment by 1
 start with 1
 minvalue 1
 maxvalue 999999999;
  
 --获取序列自增ID Oracle中的一个伪表，利用这个伪表可以设置或查看序列--
 select Student_stuId_Seq.Nextval from dual;

  1. 索引

    1. B树索引(默认)就是平衡树；叶子节点（双向链表）包含索引列和指向表中每个匹配行的ROWID值
    2. 所有叶子节点具有相同的深度，所以不管查询条件怎样，查询速度基本相同
    3. 索引可以极大的提高查询效率，但是增删改的时候也会操作索引，所以不必要的索引反而会让查询更慢
    4. 能够适应精确查询、模糊查询和比较查询
    5. create index index_name on student('name');

  2. 连接

    1. left join :左连接，返回左表中所有的记录以及右表中连接字段相等的记录。
    2. right join :右连接，返回右表中所有的记录以及左表中连接字段相等的记录。
    3. inner join :内连接，又叫等值连接，只返回两个表中连接字段相等的行。
    4. full join :外连接，返回两个表中的行：left join + right join
    5. cross join :结果是笛卡尔积，就是第一个表的行数乘以第二个表的行数。

  3. 小细节

    1. 表名顺序：ORACLE 的解析器按照从右到左的顺序处理FROM子句中的表名，写在最后的表，也叫驱动表，会优先处理，所以多表查询的时候，数据量最少的放到最后
    2. Where语句，把能过滤最多数据的条件写在最后边，条件过滤是自下向上，从右向左扫描的
    3. 用exists，not exists 来替代 in ，not in

MySql

juejin.cn/post/717358…

唯一索引查询数据时，会比普通索引快上一截，但插入数据时就不同了，因为要确保数据不重复，所以插入前会检查一遍表中是否存在相同的数据。但普通索引则不需要考虑这个问题，因此普通索引的数据插入会快一些。 索引正确使用方式：

  • ①查询SQL中尽量不要使用OR关键字，可以使用多SQL或子查询代替。

  • ②模糊查询尽量不要以%开头，如果实在要实现这个功能可以建立全文索引。

  • ③编写SQL时一定要注意字段的数据类型，否则MySQL的隐式转换会导致索引失效。

  • ④一定不要在编写SQL时让索引字段执行计算工作，尽量将计算工作放在客户端中完成。

  • ⑤对于索引字段尽量不要使用计算类函数，一定要使用时请记得将函数计算放在=后面。

  • ⑥多条件的查询SQL一定要使用联合索引中的第一个字段，否则会打破最左匹配原则。

  • ⑦对于需要对比多个字段的查询业务时，可以拆分为连表查询，使用临时表代替。

  • ⑧在SQL中不要使用反范围性的查询条件，大部分反范围性、不等性查询都会让索引失效

  1. MySql不支持Full Join，不支持外连接时用(+)连接-Oracle可以

  2. MySQL 数据库不支持 SELECT ... INTO 语句，但支持 INSERT INTO ... SELECT]

    1. 拷贝表结构及数据
    create table 新表 as select */(column_name) from 旧表
    1. 只对数据进行拷贝
    insert into A(column_name..) select */(column_name) from B

  3. MySql主从复制

    1. Master开启binlog功能，把操作语句记录到binlog日志里
    1. Slave会生成两个线程，一个I/O线程，一个SQL线程
    1. I/O线程去Master上拉取binglog，然后写到本地的relay-log(中继日志)文件中
    1. SQL线程，会读取relay log文件中的日志，解析并执行里面的SQL语句完成复制

  4. 关系型数据库MySql和非关系型数据库Redis的理解

    1. 关系型数据库

      1. 最大的特点就是数据一致；表结构固定；支持SQL，可用于复杂查询，无法满足高并发的读写需求
      2. 索引：B+树，哈希等

    1. 非关系型数据库

      1. 最大的特点就是快，读写都快，主要用来做缓存，如果对数据一致性要求比较高，就不用缓存
      2. 基于键值对，数据没有耦合性，容易扩展，适合高并发的读写
      3. 数据依赖于关系型数据库

  5. 存储引擎

    1. Mysql默认InnoDB，支持事务，MyIsam不支持事务，对于InnoDB每一条SQL都默认封装成事务，自动提交，这样会影响速度，所以可以把多条SQL放到begin和commit之间，组成一个事务。
    1. InnoDB支持外键，而MyIsam不支持，对一个包含外键的InnoDB表转成MyIsam表会失败
    1. InnoDB是聚簇索引，也就是主键索引，每个表只有一个，把数据和索引放到一快，索引结构的叶子节点保存了行数据，查询效率很高，非聚簇索引需要两次查询，先查询到主键，然后通过主键查询到数据，这就是回表操作
    1. MyIsam是非聚簇索引，除主键之外的索引，可以有多个，把数据与索引分开了，索引保存的是数据文件的指针
    1. InnoDB不支持全文检索，MyIsam支持全文检索，查询效率上MyIsam要高
    1. InnoDB默认使用行锁，锁粒度小，支持的并发高
    1. MyIsam使用表锁，锁住的是整个表，锁粒度较大，并发数较低

  6. 判断表达式

-- case when then ...
select 
    case
        when age<20 then '20岁以下'
        when age between 20 and 24 then '20-24岁'
        when age>=25 then '25岁及以上'
        else '其他'
  
    end as age_cut
    from tableName;
-- IF(expr1,expr2,expr3) 三目
drop table if EXISTS tableName;
select IF(age is null,0,age) from tableName;
-- IFNULL 替代 Oracle 中的 NVL
select IFNULL(age,0) from tableName;
  1. 日期函数
-- year(),month(),day()
select year(date) as year,month(date) as month,day(date) as day from tableName;
-- DATE_FORMAT(date,format) 用于以不同的格式显示日期/时间数据
DATE_FORMAT(NOW(),'%Y-%m-%d')
​
-- last_day() 返回指定日期所属月份的最后一天
day(last_day(date)) -- 可以获取date日期所属月份的天数
​
-- DATE_ADD(date,INTERVAL expr type)  向日期添加指定的时间间隔 间隔1天
DATE_ADD(date,INTERVAL 1 day) 
​
-- DATE_SUB(date,INTERVAL expr type)  从日期减去指定的时间间隔 
DATE_SUB(date,INTERVAL 1 day)
​
-- DATEDIFF(date1,date2) 返回两个日期之间的天数 
SELECT DATEDIFF('2008-11-30','2008-11-29') 
​
-- TIMESTAMPDIFF(unit,begin,end) 返回end-begin的结果
select TIMESTAMPDIFF(MINUTE,start_time,end_time) from tableName;
-- unit：year，month，day，minute，week，second
  1. 窗口函数
-- 计算同一结果集中当前行和后续行之间的差异
-- LAG(expr,N,default)  
-- LEAD(expr,N,default])  LEAD(date,1,10) 
-- group by 分组，partition by 分区 
select avg(if(DATEDIFF(date1,date2)=-1,1,0)) AS result from (
select distinct device_id,date AS date1,
LEAD(date) over (PARTITION by device_id ORDER BY date) AS date2
from (select distinct device_id,date from question_practice_detail) AS S1
) AS S2
​
-- 分组排序
-- 语法格式：row_number() over(partition by 分组列 order by 排序列 desc)
 select *,
    row_number() over (partition by university order by gpa) as rn
    from user_profile
  1. 字符串函数
-- 注意：字符串的字符计数是从1开始的，不是0，如果start/count值写为0，将会返回一个空
-开始的位置可以为负数，为负数时是倒数，最后一个字符是-1
​
-- SUBSTR(str,start,length)
-- SUBSTRING(string, start, length) 从字符串中提取子字符串（从任何位置开始）
-- SUBSTRING_INDEX(str,delim,count) 字符串，截取数据依据的字符，字符的位置N(可负可正)
select SUBSTRING_INDEX(PROFILE,',',-1) AS gender,count(*) AS number
from user_submit group by gender
​
select SUBSTR(device_id,1,2) from user_profile;
select SUBSTRING(device_id,1,2) from user_profile;
  1. IN的用法
-- 匹配单个字段
select * from user where name in (?);
-- 匹配多个字段,in左右两边的字段要一致
select * from user where (name1,name2..) in (?)
  1. 联合查询
-- 对于left/right join，不管on后面跟什么条件，左/右表的数据全部查出来，因此要想过滤需把条件放到where后面
-- on条件是在⽣成临时表时使⽤的条件，它不管on中的条件是否为真，都会返回左/右边表中的记录
-- where条件是在临时表⽣成好后，再对临时表进⾏过滤的条件
-- 在多表联接查询时，on⽐where更早起作⽤。系统⾸先根据各个表之间的联接条件，把多个表合成⼀个临时表后，再由where进⾏过滤，然后再计算，计算完后再由having进⾏过滤
-- 两种方式查询出来的结果一样,using()用于两张表的join查询，要求using()指定的列在两个表中均存在
select * from tableA a left join tableB b on a.id=b.id;
select * from tableA a join tableB b using(id);
  1. 插入SQL
-- replace into 跟 insert 功能类似，不同点在于：replace into 首先尝试插入数据到表中， 1. 如果发现表中已经有此行数据（根据主键或者唯一索引判断）则先删除此行数据，然后插入新的数据。 2. 否则，直接插入新数据。要注意的是：插入数据的表必须有主键或者是唯一索引！否则的话，replace into 会直接插入数据，这将导致表中出现重复的数据
-- table不加()指定字段时，values后面要列出全字段，可以多条插入
insert into table(....) values(),(); 
replace into table(....) values(),()
  1. 数据拷贝
-- 拷贝表结构
create table tableName_bak like tableName
-- 拷贝表结构和数据
create table tableName_bak as select */(column_name) from tableName
-- 向现有表中插入数据
insert into tableName_bak select * from tableName
  1. 索引
-- 创建索引
CREATE
  [UNIQUE -- 唯一索引
  | FULLTEXT -- 全文索引
  ] INDEX index_name ON table_name(col1,col2..) -- 不指定唯一或全文时默认普通索引
-- 索引使用
-- 索引使用时满足最左前缀匹配原则，即对于组合索引(col1, col2)，在不考虑引擎优化时，条件必须是col1在前col2在后，或者只使用col1，索引才会生效；
-- 索引不包含有NULL值的列
-- 一个查询只使用一次索引，where中如果使用了索引，order by就不会使用
-- like做字段比较时只有前缀确定时才会使用索引在列上进行运算后不会使用索引，如year(date)不会使用date上的索引
  1. 查询表容量
SELECT table_name, table_rows,( data_length + index_length ) / 1024 / 1024 AS size 
FROM
	information_schema.TABLES 
WHERE
	table_schema = 'mix_saas' 
ORDER BY
	size DESC

Mybatis

  1. Mybatis半ORM框架，专注于SQL本身，灵活性高，支持动态SQL，可以使用 XML 或注解来配置映射，把 Java对象映射成数据库中的记录，避免了几乎所有的 JDBC 代码，手动设置参数和获取结果集，但是需要手动写SQL，SQL依赖于数据库，移植性差

  2. #{}和${}的区别

    1. ${}直接字符串替换，SQL注入风险(在用户输入的字符串里加上SQL语句 1=1)
    2. #{}预处理，会把Sql中的参数替换成?号，然后用PreparedStatement的Set方法来赋值；防止Sql注入

  3. Mapper接口的工作原理

    Mapper接口没有实现类，当调用接口方法的时，接口的命名空间namespace+方法名拼接字符串作为key值，可以 唯一定位一个MapperStatement（每一个crud的标签都会被封装为一个MapperStatement）

  4. 分页

    1. 使用RowBounds对象进行分页，他是针对ResultSet结果集执行的内存分页，非物理分页
    2. 分页插件(pagehelper)原理：使用mybatis提供的插件接口，实现自定义插件，拦截待执行的sql，重写sql

  5. 缓存 PerpetualCache

    1. 一级缓存：基于PerpetualCache的hashMap缓存，作用域session，当session调用flush或close，将被清空，默认开启
    2. 二级缓存：基于PerpetualCache的hashMap缓存，作用域为Mapper(namespace)，可以自定义存储源，如Ehcache
    3. 对于缓存更新机制，当某一个作用域(session或者namespace)进行了增删改操作，默认该作用域下的所有select缓存清空

  6. 延迟加载

    Mybatis仅支持association关联对象和collection关联集合对象的延迟加载，可以通过配置lazyLoadingEnabled来进行配置 association：用于一对一的关联查询 collection：用于一对多的关联查询

  7. 动态SQL

    1. 以标签的形式编写，他是根据表达式的值完成逻辑判断并动态拼接sql的功能，常用的Where，If，Trim
    2. Sql片段，代码复用，include

  8. 工作流程

    1. 读取mybatis配置文件(application.yml)
    2. 加载mapper.xml映射文件
    3. 创建SqlSessionFactory会话工厂
    4. 通过工厂创建SqlSession
    5. 通过session执行数据库操作
    6. session.commit;session.close;factory.close;

  9. 整合Mybatis

    1. 基于xml:导入依赖 mybatis-Spring-boot-starter(包含mybatis和mybatis-Spring等核心包) mapper接口，mapper.xml 。。。。。 yml中配置mybatis(别名，mapper文件路径...) 启动类上添加@MapperScan("com.example.demo.mapper")用来扫描整个包下的mapper
    2. 基于注解 主要提供了 @Select，@Insert，@Update，@Delete 四个注解
    @Select("select * from user where id = #{id}")
@Results({
        @Result(property = "username", column = "user_name"),
        @Result(property = "password", column = "password")
})  // 多个字段的话时使用result来
@ResultMap("BaseResultMap")// 也可以获取xml中配置的BaseResultMap来接收参数，注解和xml相结合
User getUser(Long id)
avatar
文章
阅读
粉丝