存储数据用文件存储就可以,为什么还需要数据库?
- 文件的安全性问题
- 文件不利于数据查询和管理
- 文件不利于存储海量数据
- 文件在程序中控制不方便
数据库可以分为关系型数据库和非关系型数据库两类
- 关系型数据库:Oracle,MySQL,SQL Server
- 非关系型数据库:redis,memcached
安装和使用
连接命令:mysql -u[用户名] -p[密码] -h[IP] -P[端口号]
- SQL使用(基础+进阶)基础使用
- 索引(加速MySQL执行)用好
- 事务保证MySQL稳定性
一、MySQL基础使用
1. 数据库的操作
- 显示当前数据库:show databases
- 创建数据库:create database db_name character set utf8mb4
- 使用数据库:use db_name
- 删除数据库:drop database db_name
2. MySQL数据类型
- 整数:int
- 字符串:varchar
- 双精度:decimal
- 文本:text
- 超长文本:longtext
- 时间:DateTime
3.操作表
- 查询所有表:show tables;
- 创建表:create table if not exists table_name (
id int not null primary key,
name varchar(10) comment ‘姓名’ ); - 删除表结构和数据:drop table table_name;
- 删除数据:delete from table_name;(不加where时,删除整表数据,但是不会删除表结构)
- 添加单行信息:insert into table_name(字段) values(…);
- 添加多行信息:insert into table_name(字段) values(…),(…);
4.查询
-
全列查询:select * from 表名
-
查看表结构:desc 表名
-
自定义列查询:select 列名 from 表名
-
表达式查询:select 数学成绩+10 from 表名
-
别名查询:select username [as] uname from 表名
-
聚合查询:sum/count/max/min/avg
-
去重查询:distinct
注意事项: 1. distinct a,b 是支持组合去重查询的; 2. select name,distinct(math) from 表名 -> 不行(要将distinct放在前面) select distinct math,name from 表名 (组合去重查询,去重的是两个字段都相同的对象,即针对的是所有的字段)
-
排序:order by 列名 desc(降序)/asc(升序),默认是升序
select * from ni order by age; 使用表达式及别名排序: 1. select name,chinese+english+math from exam_result order by chinese+english+math; 2. select name,chinese+english+math total from exam_result order by total; NULL 数据排序,视为比任何值都小
-
条件查询:where
!= is not null between x and y (查询的结果包含x和y的值) like:模糊查询 %: 查询任意字符 where name like '王%'; _: 匹配单个字符 where 后面不能加聚合函数!!
-
分页查询:limit(数据量太大的时候,一次性查询需要的时间多,所以需要分页查询展示)
select id,name,math from exam limit n;(从0开始,筛选n条结果) select id,name,math from exam limit s,n; (从S开始,筛选N条结果) select id,name,math from exam limit n offset s; (从S开始,筛选N条结果)
-
分组查询: group by
查询每个角色的最高工资,最低工资,平均工资 select role,max(salary),min(salary),avg(salary) from emp group by role; (先根据进行分组,然后找每个分组里面的最高工资,最低工资,平均工资) group by 分组以后,如果还需要对分组结果再次进行条件过滤时,不能使用where语句,而需要使用having,因为where后面不能使用聚合函数 select role,max(salary),min(salary),avg(salary) from emp group by role having avg(salary) < 1500;
5.修改
update 表名 set 字段 = 值 where name = ’ ';
6.列的操作
删除列
ALTER TABLE 【表名】 DROP 【列名】
添加列
ALTER TABLE 【表名】 ADD 【列名】 【类型】
alter table table1 add transactor varchar(10) not Null;
重命名列
ALTER TABLE 【表名】 CHANGE 【列名】【新名】
二、表的约束
-
非空约束: not null
-
唯一约束:unique (NULL可以重复)
-
主键约束:primary key (一个表中只能有一个主键,主键不能为空并且是独一无二的,搭配auto_increment 自增长来使用)
-
默认值约束: default
-
外键约束:foreign key
foreign key(字段名) references 主表(列) 先创建主表,插入主表 drop table if exists classes; create table classes( id int primary key auto_increment, name varchar(20), `desc` varchar(100) ); 然后创建子表,插入子表 drop table if exists student; create table student( id int primary key auto_increment, sn int unique, name varchar(20) default 'unkown', qq_mail varchar(20), class_id int, -- 外键 foreign key (class_id) referencen classes(id) );
-
组合索引
创建索引后查询速度迅速提升!! 创建主键约束,唯一约束和外键约束的时候会自动创建对应列的索引 创建普通索引:create index 索引名称 on 表名(表里面的哪个字段) 创建组合索引:create index 索引名称 on 表名(表里面的哪些字段需要创建索引) 删除索引:drop index 索引名称 on 表名 查询表的所有约束(索引就是约束):show index from 表名 创建唯一索引:create unique index 索引名 on 表名(列名); 唯一索引也可以创建组合的(即在列名里面写两个以上的列名) alter也可以创建索引,但是不需要掌握
索引的注意事项
- 索引的创建是非常耗时的,数据量越大,索引的创建时间也就越长,所以生产场景慎用。
- 索引的适用场景是读取比较多的情况,如果新增或者删除操作比较多的时候不建议使用
- 注意有些情况会导致索引不生效,比如列计算就会导致索引不生效,此时查询就会很慢
索引什么情况下不生效(面试题,重要!!!!)
-
列计算时
-
使用like查询(模糊查询)有可能不生效
索引不生效:like '%xxx%' 索引生效:like 'x%'
-
尽量避免使用or查询,有可能导致查询不生效
-
尽量不要使用is not in 查询语句,也会导致索引不生效
-
!= 、<>导致索引不生效,尽量不要使用
-
对于组合索引来说一i的那个要遵循最左匹配原则
最左匹配原则 index(a,b,c) 走索引: where a = '' where a = '' and b = '' where a = '' and b = '' and c = '' 不走索引: where b = '' where b = '' and a = ''
MySQL索引底层实现结构:B+树(B+树详解链接)
在进行mysql查询的时候尽量使用主键进行查询(性能高)
面试题:普通索引和主键索引
答:主键索引它的非叶子节点存储的所有的数据,当我们根据主键查询响应节点信息之后就可以拿到这个整个数据;对于普通索引来说,当我们根据索引得到节点信息之后,只能得到这个节点的主键信息,然后再根据这个主键继续查找
表的关系
- 一对一:user和身份证
- 一对多:一个班级多个学生
- 多对多:学生和选课(必须有中间表)
多表查询(联合查询):
-
内联查询
select a.*,b.* from a inner join b on a.id = b.aid; select a.*,b.* from a, b where a.id = b.id; (inner join 可以用, 代替,on可以用where代替) 阿里巴巴开发手册规定:禁止三张表以上的关联查询
-
外联查询
a)左表查询 left join b)右表查询 right join
-
子查询
单行子查询 select * from student where classid = (select classid from student where username = 'java'); 多行子查询 select * from student where classid in (select classid from student where name = '语文' or name = '英文'); select * from student where classid not in (select classid from student where name = '语文' or name = '英文');
-
自连接
-
合并查询
union:该操作符用于取得两个结果集的并集,当使用该操作符的时候,会自动去掉结果集当中的重复行 select * from course where id < 3 union select * from course where name = '英文'; (当然,上面这句还可以用or来实现) union all:该操作符用于取得两个结果集的并集,使用该操作符的时候,不会去掉结果集当中的重复行
三、MySQL 事务4大特性(ACID):
- 原子性 Atomic:要么全部成功,要么全部失败(补偿机制)
- 持久性 Consistenay:事务执行的结果一定要永久的保存下来
- 一致性 Isolation:事务执行之前和执行之后,数据必须保证是正确的
- 隔离性 Durability:多个事务在执行的时候要相互隔离
1. MySQL事务执行的时候的三个问题
- 脏读:事务A在执行的时候,读取到了正在执行事务的B的数据,事务B进行了回滚数据
- 不可重复读:事务A使用同一个查询条件读取到的内容不一致,因为在这个给过程中事务B修改了数据
- 幻读:事务A使用相同的查询条件,两次查询的结果不同,因为事务B在这个过程中进行了添加或者删除
经典面试题:不可重复读和幻读的区别?
答:二者的侧重点不同,不可重复读侧重的是修改,而幻读的侧重点是添加或者删除。
MySQL的隔离级别
脏读 | 不可重复读 | 幻读 | |
---|---|---|---|
读未提交(什么问题也解决不了) | √(代表存在上面的问题) | √ | √ |
读已提交 | × | √ | √ |
可重入读(默认的隔离级别) | × | × | √ |
串行化(性能比较低) | × | × | × |
可重入读中的幻读如何解决:MVCC,gap(间隙锁)
2. MySQL事务的使用
(1)开启事务:start transaction;
(2)执行多条sql语句
(3)回滚或提交:rollback(全部失败)/commit(全部成功);
三、Java的JDBC编程(通过java语言操作数据库)
MysqlDataSource dataSource = new MysqlDataSource();
dataSource.setURL("jdbc:mysql://127.0.0.1:3306/blog_system?characterEncoding=utf-8&useSSL=true"); //设置连接的服务器的地址
dataSource.setUser("****"); //用户名
dataSource.setPassword("******"); //密码
Connection connection = dataSource.getConnect(); //获取连接
String sql = "select * from articleinfo where uid = ?";
PreparedStatement statement = connection.prepareStatement(sql); //执行sql语句
statement.setInt(1,uid);
ResultSet resultSet = statement.executeQuery(); //查询到的结果存储在resultSet里面,
//statementUpdate()方法返回的时一个整数,指示受影响的行数,通常用于update,insert,delete语句
while (resultSet.next()) {
//操作的内容
}
return list;
面试题:PreparedStatement和Statement 的区别
PreparedStatement 可以参数化查询,性能高,SQL预编译,阻止常见的SQL注入攻击,占位符不能使用多值,占位符下标从1开始。