常用MySQL相关操作整理

增删改查

增：

语句：
INSERT INTO 表名(字段，字段…) VALUES(值，值…)

新增：

<insert id="save" parameterType="com.yss.test.pojo.SavePO">
        insert into t_base_test
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="is_delete !=null ">is_delete,</if>
            <if test="create_time !=null ">create_time,</if>
            <if test="create_user !=null ">create_user,</if>
            <if test="update_time !=null ">update_time,</if>
            <if test="update_user !=null ">update_user,</if>
        </trim>
        <trim prefix="VALUES(" suffix=")" suffixOverrides=",">
            <if test="is_delete !=null ">#{is_delete},</if>
            <if test="create_time !=null ">#{create_time},</if>
            <if test="create_user !=null ">#{create_user},</if>
            <if test="update_time !=null ">#{update_time},</if>
            <if test="update_user !=null ">#{update_user},</if>
        </trim>
    </insert>

说明：

		trim：用于拼接sql或去除sql中的关键字或字符等
		prefix：拼接sql的前缀
		suffix：拼接sql的后缀
		suffixOverrides：去除sql语句最后面的字符或关键字
		prefixOverrides：去除sql语句前面的字符或关键字

删：

• 语句：
  DELETE FROM 表名 WHERE 条件
  TRUNCATE TABLE 表名
  DROP TABLE 表名

区别：

• delete ： 可以按照条件进行删除数据，可删除一条数据，保存表结构
• truncate ： 删除表的所有数据，保存表结构
• drop ： 删除表的数据和结构，不保存表结构
• 执行速度：drop > truncate > delete

改：

语句：
UPDATE 表名 SET 字段 = 值，字段 = 值… WHERE 条件

修改：

<update id="update" parameterType="com.yss.UpdatePO">
        UPDATE t_base_test
        <trim prefix="set" suffixOverrides=",">
            <if test="name !=null and name !=''"> name=#{name}, </if>
            <if test="is_delete !=null "> is_delete=#{is_delete}, </if>
            <if test="update_time !=null "> update_time=#{update_time}, </if>
            <if test="update_user !=null "> update_user=#{update_user}, </if>
        </trim>
        WHERE code=#{code}
    </update>

查：

语句：
SELECT 字段名,字段名… FROM 表名 WHERE 条件

	注意：
	 可以使用SELECT * FROM 表名来查询此表的所有数据，但是会对表中的所有列进行查询，没有
	针对性容易造成浪费资源

常用函数：

字符串函数：

函数	说明
CONCAT(str1,str2…)	拼接字符串
LENGTH(str)	返回字符串的长度
LEFT(str,length)	返回字符串str从左边第一个到第length个，包括第length个
RIGHT(str,length)	返回字符串str从最后向前第length个，包括第length个
SUBSTRING(str,len1,len2)	截取str从len1到len2，包含len2
SUBSTRING(str,len1)	截取str从len1开始到最后，包含len1
TRIM(str)	去除str两边的空格

时间函数：

函数	说明
DATE_FORMAT(data,format)	时间转换成字符串，data一般是数据库中timestamp类型的字段，format是返回的格式如果不使用此函数，查询数据库会返回一个时间戳
STR_TO_DATE(str,format)	字符串转换成时间
NOW()	返回当前的日期和时间

流程控制函数：

函数	说明
IF(判断条件，result1,result2)	根据判断条件进行判断，若为true则返回result1，为false返回result2
IFNULL(ex1,ex2	ex1若为空，则返回ex2，否则返回ex1

case:
	CASE 要判断的字段 
	  WHEN 条件1 THEN 满足条件1要显示的内容
	  WHEN 条件2 THEN 满足条件2要显示的内容
	  ...
	  ELSE 除了以上条件以外所显示的值
	  END 此字段的别名 

聚合函数：

函数	说明
AVG(字段)	求平均值
SUM(字段)	求和
MIN/MAX(字段)	求最大或最小
COUNT(*)/COUNT(1)	求数据条数

其他：

函数	说明
GROUP BY 字段 HAVING 条件	过滤分组，按照字段分组并且按照条件判断
ORDER BY 字段 DESC	按照字段排序，默认ASC(升序)；DESC(降序)
表n1 CROSS JOIN 表n2	笛卡儿积，若n1的条数为n，n2的条数为n2，则查询出的结果是n1 * n2
INNER JOIN	求两张表的交集
LEFT JOIN	求两个表的交集并加上左侧表的剩余数据
RIGHT JOIN	求两个表的交集并加上右侧表的剩余数据
OUTER JOIN	求两张表的并集

数据库优化：

1. 优化sql语句
   - 不使用select *
   - 使用between and 代替in
   - 使用join代替子查询，速度更快
   - 用in代替or
   - where后避免使用表达式和函数操作
   - 分页时使用limit
   - 一条复杂的查询可以拆分成多条进行查询
   - 多次查询，查询20w条数据可以查询四次每次5w
   - 使用exists：外表数据少则使用exists，内表数据少则使用in
   - 尽可能使用not null，不要对字段进行null判断，会导致引擎放弃使用索引而进行全表扫描
   - 避免where子句使用 != 和<>
2. 使用union代替临时表，union是查询不包括重复行，union all 查询所有数据，包括重复的
3. 使用索引
4. 选择合适的字段属性,varchar代替char等，节省空间
5. 使用外键
6. 存储引擎选择

MySQL和Oracle比较

区别：

相同点：

都是关系型数据库

不同点：

• 隔离级别：
  mysql是可重复读，oracle是读写提交
• 提交事务：
  mysql自动提交，oracle手动使用commit提交
• 总体区别：
  mysql免费的轻量级数据库
  oracle收费的重量级数据库
• 事务支持：
  mysql默认是不支持事务的，设置引擎比如innodb才支持事务，oracle完全支持事务
• 自动增长处理：
  mysql主键是自动增长的，在创建一个mysql表时不需要配置主键自增；oracle不支持自动增长，所以在新增表时需要配置一个序号类完成自动增长
• 一些方法区分

1. 分页方式：
   mysql时limit；oracle是rownum

mysql：
select * from test limit 1，10
oracle:
select rownum,id from t_test t where rownum<=3 and  rownum>=2

2. mysql中是int,varchar,oracle是number和varchar2
3. mysql去空用trim(),oracle有trim(),ltrim(),rtrim()

问题：
计算时间差(小时)

SELECT HOUR(TIMEDIFF(NOW(),'2019-11-22 12:40:00'))