文章目录
数据库MySQL
DBMS:数据库管理系统
SQL(Structure query language),操作关系型数据库的编程语言
企业开发时,数据库并不在本地,需要指定服务器ip和端口号访问数据库
登陆参数
mysql -u用户名 -p密码 -h要连接的mysql服务器的ip地址(默认127.0.0.1) -P端口号(默认3306)
SQL统一语法:
- 单行或多行,以;结尾
- 使用空格\TAB增强可读性
- 不区分大小写
- 四大类,DDL创建库,DML增删改,DQL查,DCL创建用户\控制访问权限
DDL
show databases;//查询所有数据库
select database();//查询当前数据库
create database if not exist db01;//创建
drop database if exist db01;//删除
//database和schame都指数据库
图形化工具 DataGrip
IDEA已集成DataGrip(数据库管理)的功能
约束关键字
- not null //非空约束
- unique //唯一约束
- primary key //主键约束,非空且唯一,一行数据的唯一标识
- default //默认值
- foreign key //外键约束
表创建
create table tb_user(
id int primary key comment 'ID, 唯一标识',
username varchar(20) not null unique comment '用户名',
name varchar(10) not null comment '姓名',
age int comment '年龄',
gender char(1) default '男' comment '性别'
) comment '用户表';//comment后为字段注释
数据类型
- 数值型 tinyint(1字节) int(4字节) bigint(8字节)
- 字符串型 char(定长字符串) varchar(变长字符串)
- 日期型 date(yyyy-MM-dd) datetime(yyyy-MM-dd HH:MM:SS)
页面需求->基础字段(id,主字段\create_time,此行数据创建时间\update_time,更新时间)+原型字段->sql语句(企业基于UI创建)
show tables' //查询当前库所有表
desc tb_emp;//查询表结构
show create table tb_emp;//查询建表语句
alter table tb_emp add qq int comment 'QQ';添加字段
alter table tb_emp modify qq char(1) comment 'QQ';修改字段类型
alter table tb_emp change QQ char(1) comment 'QQ';修改字段名
alter table tb_emp drop column QQ;//删除字段
rename table tb_emp to tb_boss;//修改表名
DDL的语法功能已经被UI界面集成\替换了,会使用UI界面(IDEA)就可以了
DML
增
insert into tb_emp(username,name,gender,create_time,update_time) values ('wuji','张无忌',1,now(),now());//为指定字段添加值
insert into tb_emp(username,name,gender,create_time,update_time) values ('wuji','张无忌',1,now(),now()),('xiexun','谢逊',1,now(),now());//批量添加数据
改
update tb_emp set name = '张三' , update_time = now() where id=1;//where id=1为条件语句
update tb_emp set entrydate='2010-01-01',update_time = now();
删
delete from tb_emp where id=1;
DQL
基本查询
查
select tb_emp.name,tb_emp.entrydate from tb_emp;//查询指定字段
select id, username, password, name, gender, image, job, entrydate, create_time, update_time from tb_emp//查询所有字段,不建议使用*通配符替代字段,性能低,且易出错
select name as 姓名,entrydate as 入职日期 from tb_emp;//查询时添加别名
select distinct job from tb_emp;//查询时剔除重复信息
条件查询
我记不住的条件运算符
where between '2000-01-01' and '2010-01-01';
where id in (1,2,3);//ID为1,2,3的
where name like '___';//名字是三个字的
where name like '%张%';//姓张的 ,'_'和'&'为占位符
where job is null;//没职位的
聚合函数
select count(id) from tb_emp;//查询id字段有值的数量
select count(*) from tb_emp;//推荐使用*,表中的数据行个数
select min(tb_emp.entrydate) from tb_emp;
select max(tb_emp.entrydate) from tb_emp;
select avg(id) from tb_emp;//平均
select sum(id) from tb_emp;//总和
分组查询
select tb_emp.gender,count(*) from tb_emp group by gender;//分组只能查询分组字段和计数
select job,count(*) from tb_emp where entrydate <='2015-01-01' group by job having count(*)>=2;//语法有逻辑顺序,where先执行再执行group by,所以分组后的条件要写在having之后
面试题
1.where先执行再执行group by,所以分组后的条件要写在having之后
2.where不能对聚合函数判断,而having可以,因为where对聚合函数判断也没意义
排序查询
select * from tb_emp order by entrydate asc;//根据entrydate升序排序
select * from tb_emp order by entrydate desc;//根据entrydate降序排序
select * from tb_emp order by entrydate asc , update_time desc;//根据entrydate升序排序,如果字段值相同,再按update_time 降序排序
分页查询
select * from tb_emp limit 0,5;//第一页,每页5条数据
select * from tb_emp limit 5,5;//第二页
select * from tb_emp limit 10,5;//第三页
//起始索引(i-1)*5,5;i为页码
Apache ECharts 开源网页统计图网站
select *
from tb_emp
where name like '%张%'
and gender = 1
and entrydate between '2000-01-01' and '2015-12-31'
order by update_time desc
limit 0,10;
select if(gender = 1, '男性员工', '女性员工'), count(*)
from tb_emp
group by gender;//返回给前端的数据不能性别是1或2,应该是男女,所以要借助if表达式
select (case job when 1 then '班主任' when 2 then '讲师' when 3 then '学工主管' when 4 then '教研主管' else '未分配职位' end),count(*)
from tb_emp
group by job;//多个类别要借助case表达式
多表设计
一对多
比如员工表(子表)和部门表(父表),一个部门对多个员工,在子表建立外键
不需要掌握外键建立代码,通过UI就可以
一对一
在任意一方加入外键,关联另一方,并设置外键唯一(unique)
多对多
建立第三张中间表,中间表至少含两个外键,分别关联两方主键
物理外键
当前企业开发禁止使用
使增删改效率低,不适用分布式\集群场景,容易死锁
逻辑外键
解决物理外键存在的问题
多表查询
select * from tb_emp,tb_dept;
笛卡尔积,集合A和集合B所有的组合情况
正确的查询方式应该是这样
select * from tb_emp,tb_dept where tb_emp.dept_id =tb_dept.id;
- 连接查询
-
- 内连接,相当于查询A\B交集
-
- 外连接,左外连接(查询左表所有数据,包括两表交集),右外连接(查询右表所有数据)
- 子查询
内连接
显式内连接
select tb_emp.name,tb_dept.name from tb_emp,tb_dept where dept_id=tb_dept.id;
隐式内连接
select tb_emp.name,tb_dept.name from tb_emp inner join tb_dept on dept_id= tb_dept.id;
起别名
select e.name,d.name from tb_emp e, tb_dept d where e.dept_id =d.id;
外连接
左外连接
select e.name,d.name from tb_emp e left join tb_dept d on e.dept_id=d.id;//tb_emp为左表,tb_dept为右表
右外连接
select e.name,d.name from tb_emp e right join tb_dept d on e.dept_id=d.id;//tb_emp为左表,tb_dept为右表
子查询
标量子查询
-- 查询"教研部"所有员工信息
-- a 查询教研部ID - tb_dept
select id from tb_dept where name= '教研部';
-- b 查询此ID下的所有员工信息
select * from tb_emp where dept_id = 2;
-- 合起来就是
select * from tb_emp where dept_id = (select id from tb_dept where name= '教研部');
列子查询
-- 查询"教研部"和"咨询部"的所有员工信息
-- a 查询"教研部"和"咨询部"的id_dept
select id from tb_dept where name = '教研部' or name = '咨询部';
-- b 对应id_dept的所有员工信息
select * from tb_emp where dept_id in (3,2);
-- 合起来是
select * from tb_emp where dept_id in (select id from tb_dept where name = '教研部' or name = '咨询部');
行子查询
-- 查询与"韦一笑"的入职日期及职位都相同的员工信息
-- a.查询"韦一笑"的入职日期及职位
select tb_emp.entrydate,tb_emp.job from tb_emp where name ='韦一笑';
-- b.查询员工信息
select * from tb_emp where (entrydate,job)=('2007-01-01',2);
-- 合起来
select * from tb_emp where (entrydate,job)=(select tb_emp.entrydate,tb_emp.job from tb_emp where name ='韦一笑');
表子查询
-- 查询入职日期是"2006-01-01"之后的员工信息,及部门名称
-- a.查询入职日期是"2006-01-01"之后的员工信息
select * from tb_emp where entrydate >'2006-01-01';
-- b.查询部门信息
select e.*,d.name from tb_emp e,tb_dept d where e.dept_id=d.id and entrydate > '2006-01-01';
-- 合起来
select e.*,d.name from (select * from tb_emp where entrydate >'2006-01-01') e,tb_dept d where e.dept_id=d.id;
事务
start transaction ;//开启事务
delete from tb_emp where id =3;//第一条命令
delete from tb_emp where dept_id=3;//与第一条必须共同执行的命令
//有时可能某条指令写错了,数据库错误编辑,就要回滚,而不commit
commit ;//提交
rollback ;//回滚
四大特性(ACID)
- 原子性
- 一致性
- 隔离性
- 持久性
索引
索引是帮助数据库高效获取数据的数据结构
create index idx_sku_sn on tb_sku(sn);//根据sn这个字段建立索引,比如二叉搜索树
select * from tb_sku where sn = '1000314033'
优点,提高查询效率,提高排序效率
缺点,索引表占用存储空间,索引降低增删改的效率(比如二叉搜索树的特性就是这样)
索引结构
默认采用B+Tree(多路平衡搜索树)
相较于红黑树和二叉搜索树,层数更低,每层的信息更多,搜索的次数更少
create index idx_emp_name on tb_emp(name);
show index from tb_emp;//展示所有的索引信息
drop index idx_emp_name on tb_emp;//删除索引
主键字段,在建表时,会自动创建主键索引
添加唯一约束时,数据库实际上会添加唯一索引
MyBatis
优秀的持久层(dao,三层架构中的数据访问层)框架,用于简化JDBC(java和数据库的桥梁)的开发
alt+insert是快速插入代码
入门
- 准备工作(创建springboot工程\数据库表user\实体类User)
- 引入mybatis相关依赖,配置mybatis(\在resources的application.properties中填写连接信息)
- 编写SQL语句
配置SQL提示,需要引入数据库连接
数据库连接池
数据库连接池
- 容器，负责分配、管理数据库连接
- 可资源复用、提升响应速度
lombok
lombok是一个实用的java类库,能通过注解的形式生成各种方法,简化开发
@Getter,提供get方法
@Setter,提供set方法
@ToString,提供toString方法
@EqualsAndHashCode,重写equals()和hashCode()
@Data,生成以上四个所有
@NoArgsConstructor,无参构造
@AllArgsConstructor,有参构造
基础操作
@Mapper注解使其成为一个能调用SQL的接口
删除
//根据ID删除数据
@Delete("delete from emp where id = #{id}")
public void delete(Integer id);
预编译SQL语句
- 性能更高
- 更安全,防止别有用心之人玩弄SQL语法,用SQL注入攻击
参数占位符
#{},先预编译SQL,再将#{}中的参数填补进去
${},直接拼接参数进行编译,有SQL注入的问题
关于占位符是面试题
新增
@Insert("insert into emp(username, name, gender, image, job, entrydate, dept_id, create_time, update_time)" +
" values (#{username},#{name},#{gender},#{image},#{job},#{entrydate},#{deptId},#{createTime},#{updateTime})")
public void insert(Emp emp);
主键返回
@Options(useGeneratedKeys = true, keyProperty = "id")
//这条注解语句会将主键id赋值给emp实体类
更新
//更新员工
@Update("update emp set username = #{username}, name = #{name}, gender = #{gender}, image = #{image}," +
" job = #{job}, entrydate = #{entrydate}, dept_id = #{deptId},update_time = #{updateTime} where id = #{id}")
public void update(Emp emp);
查询(根据ID)
//方案一: 给字段起别名, 让别名与实体类属性一致
@Select("select id, username, password, name, gender, image, job, entrydate, " +
"dept_id deptId, create_time createTime, update_time updateTime from emp where id = #{id}")
public Emp getById(Integer id);
//方案二: 通过@Results, @Result注解手动映射封装
@Results({
@Result(column = "dept_id", property = "deptId"),
@Result(column = "create_time", property = "createTime"),
@Result(column = "update_time", property = "updateTime")
})
@Select("select * from emp where id = #{id}")
public Emp getById(Integer id);
//方案三: 开启mybatis的驼峰命名自动映射开关 — a_cloumn ------> aColumn
@Select("select * from emp where id = #{id}")
public Emp getById(Integer id);
//只需在application.properties中打开驼峰命名自动映射开关mybatis.configuration.map-underscore-to-camel-case=true
//推荐这种方法,一劳永逸
查询(根据条件)
@Select("select * from emp where name like concat('%',#{name},'%') and gender = #{gender} and " +
"entrydate between #{begin} and #{end} order by update_time desc ")
public List<Emp> list(String name, Short gender, LocalDate begin , LocalDate end);
//不使用concat('%',#{name},'%')只能用'%${name}%',有SQL注入的风险
//不能用'%#{name}%'是因为#不能在通配符内
XMl映射文件
- XML文件放在资源包下,相对路径(com.itheima.mapper)和文件名称与mapper接口一致(‘同包同名’)
- XML中的namespace属性与mapper接口名一致
- XML中的SQL语句的id与Mapper接口中的方法名一致
注解,完成简单的增删改查
XML映射文件,实现复杂的SQL功能
使用MybatisX插件可提高使用XMl映射文件的开发效率
动态SQL
if和where标签
比如条件查询
Mapper接口
//动态条件查询
public List<Emp> list(String name, Short gender, LocalDate begin, LocalDate end);
//这里是idea的错误误报,不用管
XML中
<mapper namespace="com.itheima.mapper.EmpMapper">
<!--resultType: 单条记录封装的类型-->
<select id="list" resultType="com.itheima.pojo.Emp">
<include refid="commonSelect"/>
<where>
<if test="name != null">
name like concat('%', #{name}, '%')
</if>
<if test="gender != null">
and gender = #{gender}
</if>
<if test="begin != null and end != null">
and entrydate between #{begin} and #{end}
</if>
</where>
order by update_time desc
</select>
</mapper>
//就是为了能实现更复杂的查询功能,只用name查可以,只有gender查也行,排列组合都不出错,符合常规逻辑
标签,如果成立则执行
标签,如果标签内无成立语句不生成where,还可自动去除’and’等多余关键字
set标签
Mapper接口
//动态更新员工
public void update2(Emp emp);
XML中
<update id="update2">
update emp
<set>
<if test="username != null">username = #{username},</if>
<if test="name != null">name = #{name},</if>
<if test="gender != null">gender = #{gender},</if>
<if test="image != null">image = #{image},</if>
<if test="job != null">job = #{job},</if>
<if test="entrydate != null">entrydate = #{entrydate},</if>
<if test="deptId != null">dept_id = #{deptId},</if>
<if test="updateTime != null">update_time = #{updateTime}</if>
</set>
where id = #{id}
</update>
set标签,自动去除’,’
foreach标签
Mapper接口
//批量删除员工
public void deleteByIds(List<Integer> ids);
XML中
delete from emp where id in
#{id}
sql和include标签
<sql id="commonSelect">
select id, username, password, name, gender, image, job, entrydate, dept_id, create_time, update_time
from emp
</sql>//定义需重复使用的语句
<select id="list" resultType="com.itheima.pojo.Emp">
<include refid="commonSelect"/>//引入定义的语句
.....
</select>
sql和include标签,是为了防止因数据库内容的修改,需要大量改动XML的提高开发效率的方法
扫一扫
2217
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
