1.数据库的基本知识
1.数据库理解:持久化保存数据的程序或者软件
2.java中用于保存数据的技术
1.变量
2.对象
3.数组
4.集合
5.IO流操作
总结:1、2、3、4将数据保存内存中,易失性,无法持久化保存。5可以实现持久保存,数据的操作不灵活,数据库既可以持久化保存数据,还能灵活的操作
3.数据库的特点:
1.持久化操作
2.保存数据有组织,有结构
3.表的形式保存
4.数据库提供了结构化语言(sql语句)方便操作
4.数据库分类:
1.关系型数据库
1.MySQL
2.Oracle
3.SQLServer
2.非关系型数据库 数据查询速度快
1.Redis
2.mongodb
3.memcached
5.数据库常见名词
1.DBA数据库管理员
2.DBMS数据库管理系统(软件)
3.DB database 数据库
4.SQL 结构化查询语言
6.启动或者关闭mysql服务
1.net start mysql; 开启MySQL服务
2.net stop mysql; 关闭mysql服务
3.mysql【-localhost】-u 用户名 -p密码:连接mysql服务
7.数据库系统结构:
1.一个数据库系统 对应多个数据库
2.一个数据库对应多个数据库表
3.一个数据库表对应多条数据,多个字段(列的标题)
8.数据库的命令(SQL)
1.SQL命令语法:
1.不区分大小写
2.每一个命令结束使用分号 英文状态
2.SQL的分类:
1.DDL:数据库定义语言 数据库的创建和删除查看,以及数据库的修改和查看
2.DML:数据库操作语言 数据库表数据库的增删改
3.DQL:数据库查询语言 数据库表中数据的查询
4.DCL:数据库控制语言 指定用户权限
5.DTL:事务
9.数据库中常见的数据类型:
数据类型作用:限制数据库数据
1.整型:
tinyint/smallint /int(11)/bigint
2.浮点型:
float(n,m)/double(n,m)/decimal(n,m)
n:代表整数部分和小数部分的总长度
m:小数位的长度
例如:float(5,3):-99.999~99.999
3.字符型:
char(n)/varchar(n)/text
char(n):无论存储内容所占多少空间,只要小于n,默认再内存中占有n个大小的内存空间
varchar(n):默认再内存中占有的空间为存储内容所在的空间。
4.日期型:
datetime/date/time/timestamp 时间戳
datetime:日期+时间 8个字节 1900-01-01~9999-12-31;
date:只有日期
time:只有时间
timestamp:4个字节 1970-01-01~2038-12-31;
10.mysql数据中常见的约束:
1.作用:用于额外限定表中的字段,保证数据的完整性(数据的准确可靠)
2.约束:
1.not null:该字段不为空
2.unique:唯一 该字段不可重复,可以为哦那个,并且一个表中可以有多个
3.primary key主键:该字段不可重复,不能为空,一个表只能有一个,一般每一个表指定一个
4.default 默认:该字段如果不手动插入值 会有一个默认
5.foreign key 外键:限定两个表之间的关系 从表中的某列是主表中的某一列
2.DDL:数据库定义语言
1.数据库的增删改:
1.关键字:show create drop创建库,删除库 查看库
1.查看数据库系统中所有库
show databases;
2.创建一个库:
create database 数据库名;
3.删除库:
drop database 数据库名;
4.查看创建数据库命令
show create database 数据库名;
5.选中库
use 数据库名;
2.表的增删查:
1.查看数据库中的表:
show tables;
2.创建一个表:
create table [if not exists] 表名(字段名1 类型【约束】,字段名2 类型 【约束】....)
3.添加外键约束:
constraint fk_e_d foregin key(did) references department(d_id)
4.查看创建表的结构:
desc 表名;
5.删除表:
drop table [if exists] 表名;
6.查看创建表语句
show create table 表名;
2.1表的改:
7.1修改表名:
alter table 表名 rename to 新表名;
7.2修改字段名:
alter table 表名 change column 字段名 新字段名 类型;
7.3修改字段类型:
alter table 表名 modify column 字段名 新类型;
7.4修改约束:(先删除再添加)
万能公式:alter table 表名 modify column 字段 类型 约束
注意:不能操作 主键 外键 唯一
7.4.1主键操作:
1.添加主键约束:
alter table person modify column id int primary key;
2.删除主键约束:
1.alter table person drop primary key;
2.alter table person modify column id int null;
7.4.2非空操作:
1.添加非空:
alter table person modify column name varchar(16) not null;
2.删除非空:
alter table person modify column name varchar(16);
7.4.3唯一操作:
1.添加唯一:
alter table person modify column card varchar(20) unique;
2.删除唯一:
1.alter table person modify column card varchar(20);
2.alter table person drop index card;
7.4.4 默认操作:
1.添加默认:
alter table person modify column sex char(2) default '0';
2.删除默认:
alter table person modify column sex char(2);
7.4.5外键操作:
1.添加外键:
alter table person add constraint fk_p_j foreign key(xx) references job(id);
2.删除外键:
1.alter table person drop foreign key fk_p_j;
2.alter table person drop index fk_p_j;
7.4.6添加字段(列):
alter table 表名 add column 字段名 类型 【约束】;
7.4.7删除字段
alter table 表名 drop column 字段名;
3.DML 数据库操作语言 的增删改
1.插入:
1.单行插入:
insert into 表名(字段名1,字段名2,......)values(值1,值2,.....);
2.多行插入:
insert into 表名(字段名1,字段名2,.....)values(值1,值2,......);
注意:
1.表名后面的字段和values后面的值一一对应
2.如果插入的每个字段都有数据,字段名可以省略
3.如果整型和浮点型 不加引号 字符型和日期类型 需要加引号
4.数据满足该字段对应类型和约束
5.字段名顺序可以调换
6.字段名为默认约束,可以省略不写
7.字段可以为空,也可以省略不写。
插入时value和values的区别:
插入单条数据时:values效率较快,插入多条数据时:value效率较快。
2.修改:update
update 表名 set 字段名1 = 新值1,字段名2=新值2,...
3.删除:delete/truncate
1.delete from 表名【where 条件】
2.truncate table 表名;
4.truncate和delete得区别(面试题):
1.delete后可以加where,truncate不可以
2.truncate效率高
3.delete删除返回受影响行数,truncate不返回受影响行数
4.delete删除自增字段得时候,如果删除再插入,则自增值从当前断点处开始,truncate可以删除自增痕迹,再次插入从1开始
5.delete可以实现回滚,truncate不能实现回滚
4.DQL数据库查询语言
1.基础查询:
语法:select 查询列表 from 表名;
特点:查询列表可以是常量、表达式、函数、字段名或者组合
2.条件查询:
语法:select 查询列表 from 表名 where 条件;
特点:
条件形式1:条件表达式 > 、< 、>=、<=、<>、=
条件形式2:逻辑表达式 and or not
条件形式3:模糊查询 like between...and in
3.排序查询:
语法:select 查询列表 from 表名 where 条件 order by 排序列表 asc| desc;
特点:排序列表可以是:函数、表达式、一个字段名、多个字段或者别名
4.1常见的函数:
1.单行函数
作用::传递一个参数 返回一个结果
1.字符串函数:
1.length :获取长度
2.replace:替换字符串
3.lower:转小写
4.upper:转大写
5.substr:截取字符
6.lpad:左填充
7.rpad:右填充
8.trim:去除两侧空格
9.instr:获取字符第一次出现的索引
10.concat:拼接字符串
2.数学函数:
1.ceil():向上取整
2.floor():向下取整
3.rand():随机数
4.abs():绝对值
5.mod():取余 公式:a%b = a-(int)(a/b)*b
6.round():四舍五入
7.truncate():截取小数位
3.日期函数:
1.now():当前日期+时间
2.date_format:格式日期 返回字符串 %Y年 %m月 %d日 %H时 %i分%s秒;
3.str_to_date(‘3-7 2019’,‘%m-%d %Y’)将字符串转换成日期
4.curdate:当前日期
5.curtime:当前时间
4.流程控制:
1.if(条件,‘条件成立取值’,‘条件不成立取值’);
2.类似多重分支:
case when 条件1 then 表达式1或值1
when 条件2 then 表达式2 或 值2 ...........
【else 表达式n或值n】
end
4.2分组函数:
1.作用:传递一组参数,返回一个结果
常见的分组函数:
1.sum(分组字段):求改字段的所有值的和
2.avg(分组字段):求该字段的所有值得平均值
3.max(字段):求最大值
4.min(字段):求最小值
5.count(字段):求非空字段得个数
特点:
1.分组函数可以搭配where条件
2.函数中可以是字段或者表达式
3.分组函数忽略null值
4.分组函数支持得类型
sum、avg支持数值类型 不支持字符类型
max、min、count、支持所有类型
5.分组查询
1.语法:
1.select 分组字段、分组函数
2.from表名
3.where 分组前筛选
4.group by分组字段
5.having 分组后筛选
6.order by 排序列表 desc|asc;
执行顺序:2-3-4-5-1-6
2.特点:
1.分组查询只能查询分组字段和分组函数
2.分组后筛选 用having 对分组后的结果集做操作group by之后
3.分组前筛选用where 对原始表做操作 group by 之前
6.分页查询:
语法:
1.select 分组字段 ,分组函数
2.from 表名
3.where 分组前筛选
4.group by 分组字段
5.having 分组后筛选
6.order by 排序字段 desc|asc
7.limit 偏移量,查询的个数
执行顺序:2-3-4-5-1-6-7
特点:
1.关键字顺序不可变,limit写在最后
2.偏移量:前面偏移多少个,如果偏移量为0可以省略
7.连接查询(多表查询):
7.1.理解:
1.查询字段或者条件来自多个表,这个时候需要将多个表连接起来查询称为连接查询。
7.2注意:
多表连接的时候,添加连接条件,否则产生笛卡尔乘积
7.3sql92语法:(只有内连接)
1.内连接:
1.等值连接:
语法:
1.select 查询列表
2.from 表1 别名1,表2 别名2......
3.where 连接条件
4.and 分组前筛选
5.group by 分组字段
6.having 分组后筛选
7.order by 排序字段 desc|asc
8.limit 偏移量,查询的个数
2.非等值连接
语法:连接条件和等值连接略有不同
3.自连接
语法:连接条件和等值连接略有不同
7.4 sql99语法:
1.内连接
1.等值连接:
语法:
1.select 查询列表
2.from 表1 别名1
3.【inner】join 表名 别名2
4.on 连接条件
5.where 分组前筛选
6.group by 分组字段
7.having 分组后筛选
8.order by 排序字段 desc|asc
9.limit 偏移量 ,查询个数
2.非等值连接:
语法:
连接条件略有不同
3.自连接:
语法:
连接条件和等值连接略有不同
2.外连接:
1.内连接和外连接的区别:
1.内连接查询两个表有关联的数据,没有null值
2.外连接查询主表中的所有数据
3.副表中有连接的关联起来
4.没有连接的显示null
5.left左侧的表为主表,右侧表为副表
6.right右边的表为主表,左边的表为副表
2.语法:
1.select 查询列表
2.from 表1 列名1
3.left|right【outer】 join 表2 别名2
4.on 连接条件
5.where 分组前筛选
6.group by 分组字段
7.having 分组后筛选
8.order by 排序字段 desc|asc
9.limit 偏移量,查询个数
3.联合查询:
将外连接和右外连接 通过 union连接起来
7.5 子查询:
1.理解:
1.一个查询语句中嵌套另一个完整的查询语句,被嵌套在里面的查询语句,称为子查询或内查询
2.外边的查询称为外查询或主查询。
2.子查询的位置:
select 后面
from后面
where 后面------常用
3.特点:
1.子查询使用括号包裹
2.子查询优先于主查询执行,一般主查询将子查询的结果作为条件
3.子查询根据返回结果的个数:
单行子查询:结合>、<、>=、<=、=、<>
多行子查询:结合 in、not in、any、all
5.DCL数据库控制语言:
1.查看当前数据库系统的用户
select *from mysql.user
2.创建用户:
create user ‘用户名’@'连接权限' identified by‘密码’;
3.给用户分配权限:
grant all /select/insert on 库(*所有).表(*所有) to ‘用户名’ @‘连接权限’
4.取消权限:
revoke all/select /insert on 库(*所有).表(*所有) from '用户名'@'连接权限';
5.删除用户:
drop user '用户名'@'连接权限';
6.查看用户权限:
show grants for ‘用户名’@'连接权限';
6.DTL事务
1.理解:
1.完成某一个功能的整个过程称为事务。
2.只要出现一丁点错误整个事务失败
2.实现事务:
1.查看当前事务是否自动提交:on自动提交 off手动提交
show variables like 'autocommit';
2.关闭自动提交
set autocommit = 0;
3开启事务
start transaction;
4.设置事务命令
sql命令
5.提交事务或者回滚事务
commit:成功提交
rollback:失败回滚
3.事务的特性:(ACID)
1.原子性:整个事务不可分割
2.一致性:事务前后的数据的完整性保持一致
3.隔离性:事务之间互不干扰,存在隔离性
4.持久性:一个事务被提交 数据持久化保存
4.隔离性的隔离级别:
1.读未提交 read uncommitted
引发问题:脏读
解决:提升隔离级别
2.读已提交 read committed
引发问题:不可重复度
解决:提升隔离级别
3.可重复读 repeatable read
引发问题:幻读,数据插入的过程读不到事务
解决:提升隔离级别。
4.串行化 serialiazble
注意:事务隔离级别越高,效率越低。尽量使隔离级别最低