8-sql总结-基础使用

1. sql语句

语法特点:
1.操作表的字段的DDL(drop,show,create,alter等)使用table和database定位操作的是表或者数据库
2.操作表数据的dml使用where定位操作数据对象,delate和dql使用from定位表名
create可以创建database和table(ID int)
drop database 数据库名;
create database 数据库名 (character set 字符集名称);
use 数据库名;

#alter为操作表的字段,并且是唯一对字段进行操作的
alter table 表名 add 字段名 字段类型(长度) ;
alter table 表名 drop 字段名 after 另一个字段名;可以把该字段名放在另一个字段名后面,并且alter句式后面都能用
rename table 老表名 to 新表名;
alter table tb_user change name emp_name varchar(30);
alter table tb_employee modify job varchar(60);

#insert和update操作字段值
insert into tb_employee(id,emp_name) values(2,"lisi");
insert into tb_employee values(1,"zhangsan","2021-8-30",'2021-8-30',"产品经理",1.0,"boy.jpg");
update tb_employee set salary = 6000 ;
delete from tb_employee ;//删除是删除所有记录,不包括字段
truncate table tb_employee;	//ddl,删除table,包括字段

select * from tb_exam where name = 'zhangsan';
select name , english from tb_exam;
select t1.name as '姓名' , (t1.chinese + t1.math + t1.english) as '总分' from tb_exam as t1;
select t1.name  '姓名' , (t1.chinese + t1.math + t1.english)  '总分' from tb_exam  t1;

# ③查询购买了几类商品,显示的是总价格大于2000的商品.
select product , sum(price) from tb_order   group by product having sum(price) > 2000;
select * from tb_exam limit 4 , 2;
select name as '姓名' , (math + english + chinese) as '总分' from tb_exam order by (math + english + chinese) desc;                                                                                                           
# 查看表
show tables;
# 查看指定表的表结构
desc 表名;
# 删除表
drop table 表名;
left join 表名 on 条件

2. 函数

聚集函数

• count() : 统计个数
• min:求最小值
• max():求最大值
• avg() : 求平均分
• sum()：求和

分组统计和分页查询

-- 分组统计
select product , sum(price) from tb_order   group by product having sum(price) > 2000;
# pagesize : 每页记录数
-- 分页查询
select * from 表名 limit begin , pagesize;

账户管理

# 查询权限
show grants for '账户'@'主机';

# 添加授权 : insert、delete、update、select
GRANT 权限1 , 权限2 ON 数据库名.表名 TO '账户名'@'主机名';

# 撤销授权 
revoke 权限1, 权限2 on 数据库名.表名 from '账户名'@'主机名';

约束

键约束句式一样

# 建表时添加主键约束
#  Column 'id' cannot be null : 非空
# Duplicate entry '1' for key 'PRIMARY' : 唯一
create table tb_account(
	id int primary key,
    name varchar(30),
    money double 
);

# 删除主键约束
alter table tb_account drop primary key;

# 建表后添加主键约束
alter table tb_account modify id int primary key;

非键约束句式一样

# 删除自增约束
alter table 表名 modify 字段名 字段类型(长度);

# 建表后添加自增约束
alter table 表名 modify 字段名 字段类型(长度) auto_increment;

3. 事务

• 概述

1. 一条或多条 SQL 语句组成一个执行单元，其特点是这个单元要么同时成功要么同时失败，单元中的 每条 SQL 语句都相互依赖，形成一个整体，如果某条SQL语句执行失败或者出现错误，那么整个单元 就会回滚，撤回到事务最初的状态，如果单元中所有的 SQL 语句都执行成功，则事务就顺利执行。

2. 即事务要么同时成功要么同时失败

事务的四大特性(ACID)

1. 原子性: 事务是最小执行单元, 要么一起成功, 要么一起失败, 不可执行事务的一部分; 原子性应该不用解释, 我以后也不至于这么蠢
2. 一致性: 事务必须使数据库从一个一致性状态变换为另一个一致性状态, 说人话就是, 事务结束前是一个一致性状态, 事务结束后是一个一致性状态, 从上一个一致性状态跳转到下一个一致性状态, 不会更新一半(其实是更新过程其他事务不可见)
3. 隔离性: 事务之间是相互隔离的, 取决于隔离级别
4. 持久性: 事务提交或回滚后, 是物理存到数据库服务器的, 对数据的改变时永久性的

事务可能发生的问题:

1. 脏读: 一个事务可以读取到另一个事务未提交的数据

2. 不可重复读: 一个事务读取一数据后, 另一事务修改了该数据并提交, 第一个事务第二次读取该数据, 和第一次读取不一致, 这即是不可重复读

3. 幻读:

   ① 基本定义(来源于网络):当某个事务a范围查询数据时，另一个事务b在该范围内插入了数据，当事务a再次范围查询时，会产生幻行（即多了事务b插入的那行数据）。

   ②幻读是repeatable read隔离级别下的产物,但并不是repeatable read下应该有的产物, 根据repeatable read 的定义, 重复读一定是相同的

   ③幻读产生的原因: 没有加读操作的范围锁, 其他事务b可以在事务a处理过程中在a读取范围内添加新的记录

   ④解决幻读的措施-mvcc机制

   ⑤解决幻读的措施-next-key锁(范围加锁).

   mysql的mvcc实现是通过快照读和写操作实现的, 快照读确保了读取的数据都是同一时间点读取的数据

   mvcc: 版本并发控制

   mysql的mvcc实现:

   ①mysql的insert操作时会插入一条数据, 同时写入版本号

   ②mysql的update操作会插入一条新的数据,同时更新新老数据的版本号

   读操作读取的是快照数据, 幻读的直接反应则是读取不到数据, 但是也插入不了该字段的数据,但是可以更新这个字段数据, 这个是合理的, 因为读取的是历史版本的快照读, 添加和更新则是更新当前最新版本号的数据

事务的隔离级别:

1. 读未提交: 所有事务a能看见其他事务b未提交的结果
2. 读已提交: 一个事务a只能看见其他事务b已提交结果
3. 可重复读: 事务在整个事务期间保持一致的快照读, 其他事务的修改不会影响正在运行的事务
4. 可串行化: 通过强制的事务排序, 使事务的读写操作不再冲突, 具体是通过在每个读的数据行上加共享锁实现的

分布式事务

一致性:

4. JDBC

• 不同的数据库厂商会提供不同的驱动给开发人员操作数据库
• sun公司提供了驱动的jdbc规范
• jdbc: java database connectivity : java数据库连接

jdbc连接数据库步骤:

①加载驱动

②获取连接

③获取SQL执行对象

④获取SQL语句, 执行SQL语句

SQL漏洞:

用户输入内容可能会拼接进SQL语句, 当用户输入利用后台执行的SQL语句进行拼接时, 可能会使用SQL语句来篡改SQL执行语句, 例如在passworld中输入 123’ or 1=1’

java中使用通配符可解决SQL注入漏洞

使用mybatis解决SQL注入方式:

① 模糊查询: ${}作为通配符有SQL注入风险, 使用#{}代替

<select id="queryAll"  resultMap="resultMap">  SELECT * FROM NEWS WHERE ID = ${id}</select>
    正确写法: 
select * from news where tile like concat(‘%’,#{title}, ‘%’)

② in之后多个id查询时使用# 同样会报错， 应该使用标签

Select * from news where id in (#{ids})
正确写法: 
id in<foreach collection="ids" item="item" open="("separatosr="," close=")">#{ids} </foreach>

③ order by 后语句, mybatis-generator 自动生成的SQL中, 也是使用的#{}

所以最好在order by 后使用索引映射

连接池:

用来存储连接, 由于连接是珍贵可回收使用资源, 所以不应该使用完即销毁,而应该反复利用

常见连接池: Druid , c3p0, dbcp