mysql 数据库知识

order by 字段    将查到的list集合按指定字段升序排序

order by 字段 DESC   将查到的list集合按指定字段降序排序

GROUP BY 语句用于结合合计函数,根据一个或多个列对结果集进行分组。

关键词 DISTINCT 用于返回唯一不同的值。

SELECT DISTINCT 列名称 FROM 表名称
select id, distinct name from A;   --会提示错误,因为distinct必须放在开头

----------------------------------------------------------------------------------------------------------------------------------
mysql判断字段非空以及非空字符串(mybatis中使用) 单单只是判空可以直接用IFNULL(expr1,expr2) 函数
写法一:if(表达式一,值一,值二) 判空用到了isnull函数 mysql工具上可以运行,但是mybatis中不支持用字符串长度判空字符串的写法

SELECT m.memo AS VALUE, DATE_FORMAT(m.createDate, '%Y-%m-%d %H:%i:%S') AS createDate,
IF(ISNULL((SELECT memo FROM t_user_friend WHERE userid=f.friendid AND friendid=f.userid AND STATUS=1)) ||
LENGTH(TRIM((SELECT memo FROM t_user_friend WHERE userid=f.friendid AND friendid=f.userid AND STATUS=1)))<1,
(SELECT userName FROM t_shop_user WHERE id=f.userid AND STATUS=1),
(SELECT memo FROM t_user_friend WHERE userid=f.friendid AND friendid=f.userid AND STATUS=1)) AS username
,(SELECT path FROM t_shop_user WHERE id=f.userid) AS photourl
FROM t_user_friend_message m
LEFT JOIN t_user_friend f ON f.id=m.parentid
LEFT JOIN t_shop_user u ON f.userid=u.id
WHERE f.friendid=#{id,jdbcType=INTEGER} AND m.status=1
AND DATE_FORMAT(m.createDate, '%Y-%m-%d')=CURRENT_DATE
ORDER BY m.createDate DESC

写法二:格式同上   更改了判空字符串的写法,支持mybatis

SELECT m.memo AS VALUE, DATE_FORMAT(m.createDate, '%Y-%m-%d %H:%i:%S') AS createDate,
IF(ISNULL((SELECT memo FROM t_user_friend WHERE userid=f.friendid AND friendid=f.userid AND STATUS=1)) ||
(SELECT memo FROM t_user_friend WHERE userid=f.friendid AND friendid=f.userid AND STATUS=1)='',
(SELECT userName FROM t_shop_user WHERE id=f.userid AND STATUS=1),
(SELECT memo FROM t_user_friend WHERE userid=f.friendid AND friendid=f.userid AND STATUS=1)) AS username
,(SELECT path FROM t_shop_user WHERE id=f.userid) AS photourl
FROM t_user_friend_message m
LEFT JOIN t_user_friend f ON f.id=m.parentid
LEFT JOIN t_shop_user u ON f.userid=u.id
WHERE f.friendid=#{id,jdbcType=INTEGER} AND m.status=1
AND DATE_FORMAT(m.createDate, '%Y-%m-%d')=CURRENT_DATE
ORDER BY m.createDate DESC

写法三:  判空使用not exists

语法: EXISTS subquery
参数: subquery 是一个受限的 SELECT 语句 (不允许有 COMPUTE 子句和 INTO 关键字)。
结果类型: Boolean 如果子查询包含行,则返回 TRUE ,否则返回 FLASE 。

区别:exists (sql 返回结果集为真)  
           not exists (sql 不返回结果集为真)

SELECT m.memo AS VALUE, DATE_FORMAT(m.createDate, '%Y-%m-%d %H:%i:%S') AS createDate,(SELECT memo FROM t_user_friend WHERE userid=f.friendid AND friendid=f.userid AND STATUS=1) AS aaa,
IF(NOT EXISTS((SELECT memo FROM t_user_friend WHERE userid=f.friendid AND friendid=f.userid AND STATUS=1)) ||
(SELECT memo FROM t_user_friend WHERE userid=f.friendid AND friendid=f.userid AND STATUS=1)='',
(SELECT userName FROM t_shop_user WHERE id=f.userid AND STATUS=1),
(SELECT memo FROM t_user_friend WHERE userid=f.friendid AND friendid=f.userid AND STATUS=1)) AS username
,(SELECT path FROM t_shop_user WHERE id=f.userid) AS photourl
FROM t_user_friend_message m
LEFT JOIN t_user_friend f ON f.id=m.parentid
LEFT JOIN t_shop_user u ON f.userid=u.id
WHERE f.friendid=1 AND m.status=1
AND DATE_FORMAT(m.createDate, '%Y-%m-%d')=CURRENT_DATE
ORDER BY m.createDate DESC

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

sql调优

①:创建必要的索引

在经常需要进行检索的字段上创建索引,比如要按照姓名进行检索,那么就应该在姓名字段上创建索引,如果

经常要按照员工部门和员工岗位级别进行检索,那么就应该在员工部门和员工岗位级别这两个字段上创建索引。创

建索引给检索带来的性能提升往往是巨大的,因此在发现检索速度过慢的时候应该首先想到的就是创建索引。

②:使用预编译查询

程序中通常是根据用户的输入来动态执行SQL,这时应该尽量使用参数化SQL,这样不仅可以避免SQL注入漏洞

攻击,最重要数据库会对这些参数化SQL进行预编译,这样第一次执行的时候DBMS会为这个SQL语句进行查询优化

并且执行预编译,这样以后再执行这个SQL的时候就直接使用预编译的结果,这样可以大大提高执行的速度。

③:调整Where字句中的连接顺序

DBMS一般采用自下而上的顺序解析where字句,根据这个原理表连接最好写在其他where条件之前,那些可以

过滤掉最大数量记录。

④:尽量将多条SQL语句压缩到一句SQL中

每次执行SQL的时候都要建立网络连接、进行权限校验、进行SQL语句的查询优化、发送执行结果,这个过程

是非常耗时的,因此应该尽量避免过多的执行SQL语句,能够压缩到一句SQL执行的语句就不要用多条来执行。

⑤:用where字句替换HAVING字句

避免使用HAVING字句,因为HAVING只会在检索出所有记录之后才对结果集进行过滤,而where则是在聚合前

刷选记录,如果能通过where字句限制记录的数目,那就能减少这方面的开销。HAVING中的条件一般用于聚合函数

的过滤,除此之外,应该将条件写在where字句中。

⑥:使用表的别名

当在SQL语句中连接多个表时,请使用表的别名并把别名前缀于每个列名上。这样就可以减少解析的时间并减

少哪些友列名歧义引起的语法错误。

⑦:在in和exists中通常情况下使用EXISTS,因为in不走索引。

⑧:避免在索引上使用计算

在where字句中,如果索引列是计算或者函数的一部分,DBMS的优化器将不会使用索引而使用全表查询,函数

属于计算的一种

效率低:select * from person where salary*12>25000(salary是索引列)

效率高:select * from person where salary>25000/12(salary是索引列)

⑨:用union all替换union

当SQL语句需要union两个查询结果集合时,即使检索结果中不会有重复的记录,如果使用union这两个结果集

同样会尝试进行合并,然后在输出最终结果前进行排序,因此如果可以判断检索结果中不会有重复的记录时候,应

该用union all,这样效率就会因此得到提高。

⑩:避免SQL中出现隐式类型转换

当某一张表中的索引字段在作为where条件的时候,如果进行了隐式类型转换,则此索引字段将会不被识别,因

为隐式类型转换也属于计算,所以此时DBMS会使用全表扫面。

最后需要注意的是:防止检索范围过宽

如果DBMS优化器认为检索范围过宽,那么将放弃索引查找而使用全表扫描。下面几种可能造成检索范围过宽的

情况。

a、使用is not null或者不等于判断,可能造成优化器假设匹配的记录数太多。

b、使用like运算符的时候,“a%”将会使用索引,而“a%c”和“%a”则会使用全表扫描,因此“a%c”和“%a”不能被有

效的评估匹配的数量。

上一篇:WARN bzip2.Bzip2Factory: Failed to load/initialize native-bzip2 library system-native, will use pure-Java version


下一篇:css属性简写集合