数据库知识点

数据库

数据库解决的问题：持久化存储，优化读写，保证数据的有效性

关系型数据库：基于E-R模型，使用SQL语言进行操作 有mysql oracle sqlserver DB2

分类：文档型 sqlite应用于智能端 服务型：大型的网站 访问量大

三大范式：1，列不可拆分 2，唯一标识

索引

1.0 索引是一种数据结构，索引目的是提高查询效率：排好序的快速查找数据结构。一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上

优势：可以提高数据检索的效率，降低数据库的IO成本，通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗

劣势：1，实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。

2，虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行insert,update,delete.

因为更新表时，mysql不仅要保存数据，还要保存一下索引文件，每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。

3，分类：单值索引：即一个索引只包含单个列，一个表可以有多个单列索引

唯一索引：索引列的值必须唯一，但允许有控制

复合索引：一个索引包含多个列
DDL 数据定义语言

DML 数据管理语言

DQL 数据查询语言

DCL 数据控制语言

id 主键

version 乐观锁

is_delete 伪删除

gmt_create 创建时间

gmt_update 修改时间

创建表：

--创建表
CREATE TABLE IF NOT EXISTS `student`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(30)NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` VARCHAR(20) NOT NULL DEFAULT '123456'COMMENT '密码',
`sex` VARCHAR(2) NOT NULL DEFAULT '女'COMMENT '性别',
`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
`email` VARCHAR(30) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

数据库引擎：

INNODB：安全性高，事务的处理，多表多用户操作

MYISAM：节约空间，速度较快

	MYISAM	INNODB
事务支持	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文索引	支持	不支持
表空间大小	较小	较大约为myisam2倍

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xage0BRy-1597879755714)(C:\Users\pc\AppData\Roaming\Typora\typora-user-images\image-20200818205537942.png)]

–修改表名

ALTER TABLE teacher RENAME AS teacher1

–添加表的字段

ALTER TABLE teacher1 ADD age INT(10)

–修改字段的约束

ALTER TABLE teacher1 MODIFY age VARCHAR(100)

–修改表的字段名（重命名）

ALTER TABLE teacher1 CHANGE age age1 INT(11)

–删除表中的字段名

ALTER TABLE teacher1 DROP age1

外键：

--方式一；
CREATE TABLE IF NOT EXISTS `grade`(
	`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
	`gradename`VARCHAR(50) NOT NULL COMMENT '年级名称',
	PRIMARY KEY(`gradeid`)
	)ENGINE=INNODB DEFAULT CHARSET=utf8
	
-- 学生表的字段 gradeid 要去引用年级表的gradeid
-- 定义外键key
-- 给这个外键添加约束（执行引用） references 引用	
CREATE TABLE IF NOT EXISTS `student` (
  `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
  `sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
  `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
  `gradeid` INT(10) NOT NULL  COMMENT '年级id',
  `address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
  `email` VARCHAR(30) DEFAULT NULL COMMENT '邮箱',
  PRIMARY KEY (`id`),
  KEY `FK_gradeid` (`gradeid`),
  CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`) 
) ENGINE=INNODB DEFAULT CHARSET=utf8

-- 创建表之后添加外键 方式二
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`);

DML 数据库操作语言

insert

update

delete

--清空数据
truncate table `student`
delete from `student` 
两者区别：truncate 清空表，自增会归零
        delete 清空表，不会影响自增
        相同点：都可以删除数据，不会影响表结构
InnoDB 自增列会从1开始（存在内存中，断电即失）
MyISAM 继续从上一个自增量开始（存在文件中，不会丢失）

DQL 数据查询语言

drop database if exists db10;
create database if not exists db10 charset=utf8;
use db10;
drop table if exists emp;
create table emp(
id int primary key auto_increment,
name varchar(50) not null,
gender varchar(30) not null,
birthday date not null,
score double,
foreign key (dept_id) references dept(id),
on delete cascade
);
insert into emp values(null,‘张三’,‘男’,‘1988-08-09’,99.5);
基础查询:
select * from emp;
where子句查询
select * from emp where id=4;
select name,sal from emp where sal >3000;
模糊查询
select name from emp where name like ‘%涛%’; 包含涛的员工
select name from emp where name like ‘刘%’; 以刘字开头的
select name from emp where name like ‘%涛’;以涛字结尾的
select name from emp where name like ‘%刘_’; 以刘字开头 姓名为两个字
select name from emp where name like ‘%刘_ _’;以刘字为开头 姓名为三个字的
多行函数查询
select max(sal) from emp;
select sum(sal) from emp ;
select count(*) from emp;
分组查询
select * from emp group by dept; 按照部门分组
排序查询 默认asc 升序
select name,sal from emp oder by sal desc;
分页查询
select * from emp limit 0,3;
其他函数

select * from emp where month(birthday)=month(now()); 本月过生日的员工

select name,birthday from emp
where year(birthday)>=1993 and year(birthday)<=1995;

concat 拼接字符串
select name,concat(sal,’(元)’) from emp;

select name,concat_ws(’/’,sal,‘元’) from emp;

主键约束: id int primary key
主键 设置自增 id int primary key auto_increment
非空约束 password varchar(50) not null
唯一约束 username varchar(50) unique not null
外键约束:通知数据库两张表存在相对应的关系 帮忙维护

foreign key (dept_id) references dept(id)
级联删除 on delete cascade
级联更新 on update cascade

表关系 一对多 在多的地方加一列 保存1的主键
一对一 双方都可以添加列保存对方主键
多对多 需要再创建一张表 分别保存两张表的主键
创建第三张表
create table stu_tea(
stu_id int,
tea_id int,
primary key(stu_id,tea_id), 设置联合主键
foreign key(stu_id) references stu(stu_id), 添加外键
foreign key(tea_id) references tea(tea_id) 添加外键
);
多表查询 适合在同一个数据库
内连接
select name from dept d inner join emp e
on e.dept_id = d.id;

左外连接
select name from dept d left join emp e
on e.dept_id =d.id;

右外连接
select name from dept d right join emp e
on e.dept_id =d.id;

全连接union
select name from dept d left join emp e
on e.dept_id =d.id
union
select name from dept d right join emp e
on e.dept_id =d.id;

1，用外连接查询所有员工和对应的部门
2.求出大数据部门最高的薪资
3，求出比大数据部门最高薪资还高的员工信息
select e.name,e.sal,d.name from dept d right join emp e
on e.dept_id =d.id
where sal>(select max(sal) from emp where dept id=30);

===========
关联查询两张表
select d.name,e.name from dept d,emp e
where e.dept_id=d.id
and d.name=“培优部”；

自查询 列出所有员工以及直接上级 显示员工姓名
上级编号 上级姓名
emp e1 员工表 emp e2 上级表
select e1.name,e2.id,e2.name from emp e1,emp e2
where e1.topid=e2.id;

select job ,min(sal) from emp group by job
having min(sal)>1500;

1）业务层 发起两次单表查询，然后进行统一封装
2）数据层 嵌套查询
先查询到角色自身信息的id 再基于id再次角色关系数据查询，把查询结果存储到
这个类型对象的属性上面
3）数据层 多表关联查询

把表看成是一本书，大纲看成索引，目的查询性能提高
表的三大范式

1,字段可再分 不符合第一范s式
2,确保表中的每列和主键相关,依赖主键值 第二范式
3,确保每列和主键直接相关,而不是部分相关 第三范式
允许适当冗余,违反第三范式,但是,提高查询性能,牺牲了空间 赢得了查询时间
前提冗余字段很少更改 更新需要更新两个表
不设置外键 是用户大的情况下 强耦合性 不可拆分 物理上的关系 拿走用户表不能拿走

pagehelper
Dao层
基于条件查询用户以及用户对应的部门信息
List findPageObjects(String username)
Mapper.xml
<select id=“findPageObjects”
resultMap=“SysUserDept”
实现类
设置分页参数
int pageSize=3;
Page page = PageHelper.stratPage(pageCurrent,pageSize);
查询当前页记录
(int)page.getTotal() ,page.getPages()

标签 标签