Mysql学习

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