数据库总结

SQL语法

SQL，结构化查询语言

1. 增

   -- 不指定列名 插入的数据需要与列一一对应
   insert into 表名 values (value1,value2,value3);
   -- 指定列名  以及插入的值
   insert into 表名 (column1,column2,column3) values (value1,value2,value3);
   

2. 删

   delete from 表名 where some_column=some_value;
   -- 注意如果不加条件，则删除表中的所有数据
   drop 表名;
   -- 删除表，并释放空间,删除表的结构
   truncate 表名;
   -- 删除表的内容,释放空间,但是表的结构还在
   

   • delete 是DML,数据操纵语言,涉及事务,可以进行回滚操作
   • drop 和 truncate 是DDL,数据定义语言,删除后不能回滚,但是效率要高,drop效率最高
   • truncate删除后表的索引清零,delete则不会

3. 改

   update 表名 set column1=value1,column2=value2,column3=value3 where some_column=some_value;
   

4. 查

   -- 查询表的全部 
   select * from table_name ;
   -- 查询某个字段
   select column_name from table_name ;
   -- 查询结果去除掉重复值
   select distinct column_name,column_name from table_name;
   

5. 聚合函数

   select  聚合函数1,聚合函数2,聚合函数3 from table_name where some_column=some_value group by some_condition having condition order by condition;
   

   • avg 取平均值
   • min 取最小值
   • max 取最大值
   • sum 求和
   • count 统计
   • group by 分组
   • having 分组后过滤
   • order by 对结果进行排序

6. 连接查询

   -- 隐式内连接
   select column_name 
   from table1 t1,table2 t2
   where t1.column_name=t2.column_name;	
   -- 显示内连接
   select column_name
   from table1 t1
   inner join table2 t2
   where t1.column_name=t2.column_name;
   -- 左外连接
   select column_name
   from table1 t1
   left outer join table2 t2
   where t1.column_name=t2.column_name;
   -- 右外连接
   select column_name
   from table1 t1
   right outer join table2 t2
   where t1.column_name=t2.column_name;
   

   • 注意: 左连接与右连接的左右是指以哪一张表作为基准表,他们都是外连接,外连接就像是给非基准表添加了一行全为空值的万能行,用来与基准表中找不到匹配的行进行匹配.
   • 左外连接,即将左表的内容全部显示,右表有匹配的全部显示,没有匹配的显示空值
   • 右外连接,即将右表的内容全部显示,左表有匹配的全部显示,没有匹配的显示空值
   • inner join < =min(left join , right join)
   • full join > =max(left join , right join )

事务

事务是指逻辑上的一组操作,组成这个操作的各个单元,要么全部成功,要么全部失败

事务的特性

• 原子性: 事务是一个不可分割的工作单位,事务中的操作要么全部成功,要么全部失败
• 一致性: 事务必须使数据从一个一致性状态转换到另外一个一致性状态
• 隔离性: 事务在执行的过程中对数据的修改,在事务提交之前对其他事务不可见
• 持久性: 事务一旦被提交,它对数据库中数据的改变就是永久性的,即使记下来数据库发生故障也不会对其有任何影响

事务的隔离级别

不考虑隔离级别,可能会引发如下问题:

• 赃读: 一个事务读取到了另外一个事务未提交的数据
• 不可重复读 : 一个事务内读取表中的某一行数据,多次读取结果不同,不可重复读是重新读取前一次已提交的事务
• 虚读: 是一个事务内读取到了别的事务插入的数据,导致前后读取不一致
• 更新丢失: 多个事务对同一数据进行操作,后提交的事务覆盖了前面提交的事务
  • mysql的隔离级别
    |隔离级别| 赃读 | 不可重复读 | 虚读
    |:–?:–?:–?:–?
    | 读未提交 (read uncommitted)| √ |√|√
    | 读已提交(read committed) | × |√|√
    | 可重复读(repeatable read) | × |×|√
    | 可串行化( serializable ) | × |×|×
• oracle的隔离级别

  隔离级别	赃读	不可重复读	虚读
  读已提交(read committed)	×	√	√
  可串行化(serializable)	×	×	×
  只读(read-only)	×	×	×

只读事务只能看到事务执行前就已经提交的数据,且事务中不能执行Insert,update,delete

• 丢失更新的解决方法
  • 悲观锁
    总是假设最坏的情况,每次拿数据的时候都认为别人会修改,所以每次拿数据的时候都会上锁,传统的关系型数据库用到了很多这种锁机制,例如行锁,表锁,Java中的synchronized和ReentrantLock等独占锁就是悲观锁思想的体现
  • 乐观锁
    总是假设最好的情况,每次去拿数据的时候都认为别人不会修改,所以不会上锁,但是在更新的时候会判断一下此期间别人有没有去更新这个数据
    • 版本号机制
      一般在数据表中添加一个数据版本号的字段,用来标识数据被修改的次数. 在提交更新时,获取数据时的版本号需要与当前数据库的版本号相同才更新,否则重试更新操作,直到更新完成.
    • CAS算法
      即compare and swap 比较与交换,是一种无锁算法,即不使用锁的情况下实现多线程之间的变量同步,