Mysql
多表关系
- 一对一(人对应身份证)
- 一对多(一个研发部对有若干个成员)
- 多对一(多个开发者都是对应一个研发部门)
- 多对多(需要借助第三张中间表,至少需要两个外键,分别是指向原来那两张表的主键)
动态sql
for循环首先考虑:在当前td判断
<td>
<c:choose>
<c:when test="${empty bzDeceased.documentsNo}">
</c:when>
<c:otherwise>
<input id="idNo" name="input_id" class="unclaimedId" type="checkbox"
value="${bzDeceased.id}_${bzDeceased.documentsNo}"
style="display: inline-block; width:20px;height: 20px;">
</c:otherwise>
</c:choose>
</td>
外键约束
从表某一列的值受到主表的约束,主表主键id(外键约束
-
主键不能为空值,但允许外键中出现空值
-
主键类型的外键类型一致
-
在创建表的时候设置外键约束,通过foreign key关键字来指定外键
-
Navicat选到表右键选择逆向表到模型,可以查看表与表之间的关系
-
有外键约束(必须先给主表添加数据,再到从表
-
主表的数据有从表依赖的主键不能删除,不被依赖可以删除,从表数据可以任意删除
-
删除外键约束:alter table<表名> drop foreign key 《外键约束名》
多表联合查询
- 交叉连接查询
- 内连接
- 外连接
- 子查询
- 表自连
-
外键约束对多表查询并无影响
-
primary key == 主键 等价于 唯一 (UNIQUE) 且 非空 (NOT NULL)
-
SELECT * FROM students WHERE NOT class_id = 2;
-
优先级:not > and > or
-
SELECT * FROM students WHERE (score < 80 OR score > 90) AND gender = ‘M’;
-
倒序:SELECT id, name, gender, score FROM students ORDER BY score desc;(有相同数据的话,直接在后面加,字段)
-
写order by子句的时候应该用$而不是#
-
1.order by:排序 SELECT * FROM student ORDER BY SId LIMIT 5 OFFSET 0; - OFFSET超过了查询的最大数量并不会报错,而是得到一个空的结果集。 - 使用LIMIT <M> OFFSET <N>分页时,随着N越来越大,查询效率也会越来越低。(M代表每页条数,N代表从第几条数据开始查询《默认是0》)
-
聚合函数
1.count(*):计算数量,有多少条数据 SELECT count(SId) as '数量' FROM student -- 使用聚合查询并设置WHERE条件: SELECT COUNT(*) boys FROM students WHERE gender = 'M'; 2. ground by:分组 SELECT class_id, COUNT(*) num FROM students GROUP BY class_id; 3. 多表查询,返回的数据是student,sc两个表的乘积,当每个表有10条数据的时候,返回的是100条数据,又称卡迪尔查询 SELECT * from student,sc
-
`` 是 MySQL 的转义符,避免和 mysql 的本身的关键字冲突,只要你不在列名、表名中使用 mysql 的保留字或中文,就不需要转义。
所有的数据库都有类似的设置,不过mysql用的是`而已。通常用来说明其中的内容是数据库名、表名、字段名,不是关键字。
MySQL数据库引擎
MYISAM和Innodb
- InnoDB是MySQL的默认引擎,使用的是可重复读级别的隔离,B+树是InnoDB的默认索引类型,并且支持事务和行锁,以及外键约束
- innodb:支持事务、支持外键,myisum不支持事务、不支持外键
- myisam适合查询以及插入为主的应用,innodb审核频繁修改以及涉及到安全的应用
sql优化
- 索引并不是越多越好,索引固然可以提高相应的 select 的效率,但同时也降低了 insert 及 update 的效率,
- 因为 insert 或 update 时有可能会重建索引,所以怎样建索引需要慎重考虑,视具体情况而定。
- 一个表的索引数最好不要超过6个,若太多则应考虑一些不常使用到的列上建的索引是否有必要。
- exists 代替 in
- 连续查询的可以用between and 代替 in 和 not in
- 少使用select * 会查出所有字段浪费资源 查询对应字段即可
- 考虑在 where 及 order by 涉及的列上建立索引,避免全表示扫描
- 导致引擎放弃使用索引而进行全表扫描
- LIKE双百分号无法使用到索引
关系型数据库与非关系型数据库区别
关系型数据库(Mysql和Oracle)
1.表和表、表和字段、数据和数据存在着关系
优点:
1.数据之间有关系,进行数据的增删改查的时候是非常方便的
2.关系型数据库是有事务操作的,保证数据的完整性和一致性。
缺点:
1.因为数据和数据是有关系的,底层是运行了大量的算法,大量算法会降低系统的效率,会降低性能
2.面对海量数据的增删改查的时候会显的无能为力
3.海量数据对数据进行维护变得非常的无力
常见应用:
适合处理一般量级的数据(银行转账和钱)
非关系数据库的(redis和MangDB)
为了处理海量数据,非关系数据库设计之初就是为了替代关系型数据库的关系
优点:
1.海量数据的增删改查是可以的
2.海量数据的维护和处理非常轻松
缺点:
1.数据和数据没有关系,他们之间就是单独存在的
2.非关系数据库没有关系,没有强大的事务关系,没有保证数据的完整性和安全性
study总结
- sql条件判断增加
<if test="task != null">
and (
td.object_name like CONCAT('%',#{task},'%')
or td.spot_name like CONCAT('%',#{task},'%')
or s.sample_code like CONCAT('%', #{task},'%')
)
</if>
SELECT
COUNT( CASE WHEN transport_type = '3' AND service_type != '5' THEN id END ) AS '自送数',
count( CASE WHEN transport_type != '3' AND service_type != '5' THEN id END ) AS '遗体接运数',
count( CASE WHEN create_by THEN id END ) AS '接听电话数'
FROM
bz_deceased
WHERE
TO_DAYS( create_date ) = TO_DAYS('2021-08-23')
- case when then else多个条件
CASE sex
WHEN '1' THEN '男'
WHEN '2' THEN '女'
ELSE '其他' END
实战:
<when test="bzServiceItem.id eq 'b6d7226d133b71e18f49d852b80fa140'">
sum(case when (serv.id in ('0994ed0a98e34f779bb584afb524f4db','642e96845b18496eae4d775e5a491173','8ad1eb642b1e40c2bf01d7a2178d88d7')) then ABS(serv.actual_pay)*rela.prod_count else 0 end) as 'doorForehead',
sum(case when (serv.id in ('900df8f6de52482b81983af15e65fa23','932402ed22fa43bcbb300773f00e1602','e0fe40dd277c40eea4dc798e424ddbf1')) then ABS(serv.actual_pay)*rela.prod_count else 0 end) as 'doorInsidehead',
</when>
模糊查询
<if test="telephoneName != null and telephoneName != ''">
AND a.telephone_name like
<if test="dbName == 'oracle'">'%'||#{telephoneName}||'%'</if>
<if test="dbName == 'mssql'">'%'+#{telephoneName}+'%'</if>
<if test="dbName == 'mysql'">concat('%',#{telephoneName},'%')</if>
</if>
mybatis遍历数组id
关于 mybatis 中 in 写法,<foreach collection="xxx" item="xxx" index="index" open="(" separator="," close=")"> 参数详解
使用 mybatis 时,如果要使用到 in 写法,要使用 foreach ,里面几个参数,看了很多地方,都说的不清不楚,自己最后各种测试,这里详细说下:
(1)collection = “” ,这个参数是 dao 层(mapper)接口方法里面传过来的集合参数,如果dao 层传的参数只有一个,这里写关键字 list(如果是数组,写 array)
例子:
dao 层:User getInfo(List<Integer> user_ids)
collection = "list"
【如果有多个参数,并且使用了 @Param 注解(import org.apache.ibatis.annotations.Param),则这里要写注解里面的参数!
例子: dao 层 :User getInfo(@Param("user_ids")List<Integer> user_ids,@Param("xxx")String xxx) collection = "user_ids" 】
(2)item = “” ,集合里面的单个值,给下面 #{ } 用
(3)index = "" ,这个是遍历的下标,举个简单的例子立刻明白,就是平时 for 循环,我们定义的 i 一样
例子: for(int i = 0 ;i < 10 ; i ++){ }
因此这个参数随便定义都可以,有就行
(4)open separator close 这3个比较好理解,就是 ( , , ,) 这样子啦,拼接括号,中间逗号隔开的意思
左连接(left join on)
- 注意左联表on的条件,每次根据不同id查出的数据是不一样的
--根据bz_consumelist_project_relation_id消费清单查就是唯一的,一开始根据死者id联表查出了多条数据
SELECT b.id AS "bzRentVehicleId"
FROM bz_negotiation_consumelist a
LEFT JOIN bz_consumelist_project_relation c ON c.consumelist_id = a.id
LEFT JOIN bz_rent_vehicle b ON b.bz_consumelist_project_relation_id = c.id
WHERE a.id = '381d76cc8cac4543ac30c10cf53e8cbd'
AND a.del_flag = 0
AND b.del_flag = 0
AND c.del_flag = 0
update
UPDATE bz_deceased
SET audit_data_status = NULL
WHERE
id = '06f93dc936c046daa0c2c5c4bd7e615b' //begin_charge_time=#{beginChargeTime},
set null值不用加“”直接写NULL就好
mysql日期函数
- 格式化时间:
xml日期转化格式:and (DATE_FORMAT(bd.clearance_time, '%Y-%m-%d') >= DATE_FORMAT(#{clearanceTime}, '%Y-%m-%d'))
- TO_DAYS 函数 :返回一个天数! 啊哈?什么天数? 从年份0开始的天数
比如:
mysql> SELECT TO_DAYS(‘1997-10-07′);
结果 729669就是从0年开始 到1997年10月7号之间的天数
- DATEDIFF(p1,p2) 函数,计算两个日期的是时间差
(DATE_FORMAT(temp.bz_ysgl_date, '%Y-%m-%d') ,DATE_FORMAT(NOW(), '%Y-%m-%d')) as "aaa",
常用标签
- jstl c:choose>、<c:when>和<c:otherwise>标签
<c:choose>、<c:when>和<c:otherwise>在一起连用,可以实现Java语言中的if-else语句的功能。例如以下代码根据username请求参数的值来打印不同的结果:
<c:choose>
<c:when test="${empty param.username}">
Nnknown user.
</c:when>
<c:when test="${param.username=='Tom'}">
${param.username} is manager.
</c:when>
<c:otherwise>
${param.username} is employee.
</c:otherwise>
</c:choose>
以上标签等价于以下Java程序片段:
<% String username=request.getParameter("username");
if(username==null){
//对应第一个<c:when>标签的主体
out.print("Nnknown user.");
}else if(username.equals("Tom")){
//对应第二个<c:when>标签的主体
out.print(username+" is manager.");
}else{
//对应<c:otherwise>标签的主体
out.print(username+" is employee.");
}
%>
<c:choose>、<c:when>和<c:otherwise>标签的使用必须符合以下语法规则:
<c:when>和<c:otherwise>不能单独使用,它们必须位于<c:choose>父标签中。
在<c:choose>标签中可以包含一个或多个<c:when>标签。
在<c:choose>标签中可以不包含<c:otherwise>标签。
在<c:choose>标签中如果同时包含<c:when>和<c:otherwise>标签,那么<c:otherwise>必须位于<c:when>标签之后。
- sql语句case when什么意思?
case when 就是case when
判断 case when 当满足条件 then
( case when (ba.register_date is not null ) then ba.register_date else bd.mis_transport_time end ) as
transportDate
总结学习
<if test="jobId gt 0"> AND job_id = #{jobId} </if>
- java mybatis XML文件中不允许出现">“、”<"之类的符号。需要转义 "="是可以正常使用的
- eq
- sql量小的时候直接在dao上注解写即可
@Select("select * from bz_group_consumelist_relation where consumelist_id = #{necId} and type = '2'")
List<BzListGroup> getListGroup(String necId);
@Update("update bz_group_consumelist_relation set type = '' where group_id = #{id} ")
void updateGroupListRealation(@Param("id") String id);
@Update("update bz_list_group set group_type = #{groupType},grouping_status = #{groupingStatus},settlement_date = #{settlementDate},update_date = #{updateDate},update_by = #{updateBy.id} where id = #{id}")
void updateLisgGroupSettlement(BzListGroup bzListGroup);
- 排序放最后,排序的时候需要and来连接
排序放最后:SELECT
buta.serial_number AS "serialNumber",
buta.actual_name AS "actualName",
buta.mailing_address AS "mailingAddress",
buta.home_address "homeAddress",
buta.unit_name,
buta.service_type AS "serviceType"
FROM
bz_deceased_unclaimed bdu
LEFT JOIN bz_unclaimed_tel_address buta ON bdu.id = buta.deceased_id
WHERE
bdu.del_flag = '0'
AND buta.del_flag = '0'
AND buta.serial_number LIKE concat( '%','2021G34394', '%' )
ORDER BY
buta.serial_number
- sql查询,不包括其中这几个值
SELECT `value`,label FROM sys_dict WHERE type = "bz_unclaimed" AND `value` NOT IN(1,2,3) order by value