sql语句类型

 

• DDL 数据定义语言(create)
• TPL 事务处理语言(commit)
• DCL 数据控制语言()
• DML 数据操作语言(select,insert,update,delete)

.Join从句

• 内连接 INNER

  • 选取两张表交集

• 左外连接 LEFT OUTER

  • SELECT * FROM TABLEA A LEFT JOIN TABLEB B ON A.KEY=B.KEY 
    查询A表中的数据(包含AB交集)
  • SELECT * FROM TABLEB B LEFT JOIN TABLEB B ON A.KEY = B.KEY WHERE B.KEY IS NULL 
    查询仅存在与A表中的数据(排除AB交集)，优化NOT IN

• 右外连接 RIGHT OUTER

  • 与LEFT JOIN对应，以B表为核心来查询

• 全外连接 FULL OUTER

  • 左右连接的合集，查询所有AB表的数据
  • 排除AB表公共部分
  • 注意，MYSQL中不支持FULL JOIN，需要利用UNION ALL连接LEFT，RIGHT连接

• 交叉连接 CROSS

  • 又称笛卡尔连接或叉乘
  • SELECT A.CLUMN,B.CLUMN FROM TABLEA A CROSS JOIN TABLEB B

sql中经常用到的方法

• 时间格式的转换

               DATE_FORMAT(from_unixtime(" + queryName + "/1000),'%Y-%m-%d') "

• 时间戳毫秒级格式的转换

             DATE_FORMAT(from_unixtime(CREATE_TIME/1000),'%Y-%m-%d %H:%i:%s')

函数：FROM_UNIXTIME

作用：将MYSQL中以INT(11)存储的时间以"YYYY-MM-DD"格式来显示。

DATE_FORMAT() 函数用于以不同的格式显示日期/时间数据。

 

时间转字符串

select date_format(now(), '%Y-%m-%d');  
  
#结果：2016-01-05  

 

时间转时间戳

select unix_timestamp(now());  
  
#结果：1452001082  

字符串转时间

select str_to_date('2016-01-02', '%Y-%m-%d %H');  
  
#结果：2016-01-02 00:00:00  

字符串转时间戳

select unix_timestamp('2016-01-02');  
  
#结果：1451664000 

时间戳转时间

select from_unixtime(1451997924);  
  
#结果：2016-01-05 20:45:24  

时间戳转字符串

select from_unixtime(1451997924,'%Y-%d');  
  
//结果：2016-01-05 20:45:24

 

 

 

• 修改多个business_id的sipping_id(根据business_id当等于20改为4，等于21改为9)

UPDATE or_order  SET sipping_id = 
CASE business_id 
     WHEN 20 THEN 4  
     WHEN 21 THEN 9  
     WHEN 22 THEN 8  
     WHEN 23 THEN 10 

 END  WHERE business_id IN (20,21,22,23)

•  sql 截取字符串

 

1、LOCATE(substr , str )：返回子串 substr 在字符串 str 中第一次出现的位置，如果字符substr在字符串str中不存在，则返回0；

 

2、POSITION(substr  IN str )：返回子串 substr 在字符串 str 中第一次出现的位置，如果字符substr在字符串str中不存在，与LOCATE函数作用相同；

 

3、LEFT(str, length)：从左边开始截取str，length是截取的长度；

 

4、RIGHT(str, length)：从右边开始截取str，length是截取的长度；

 

5、SUBSTRING_INDEX(str  ,substr  ,n)：返回字符substr在str中第n次出现位置之前的字符串;

 

6、SUBSTRING(str  ,n ,m)：返回字符串str从第n个字符截取到第m个字符；

 

7、REPLACE(str, n, m)：将字符串str中的n字符替换成m字符；

 

8、LENGTH(str)：计算字符串str的长度

9、根据特定的字符截断某个字符串取出中间需要的内容

 

      SUBSTRING_INDEX(SUBSTRING_INDEX(ship_addr,'-', 2),'-',- 1)   

      例： 陕西省-西安市-未央区先锋花园北区    结果：西安市

合并两张表

需要成这样的

此时这两张表之间并没有相关联的字段，我们经常用的left join，right join， full join 都是需要相关联的字段的，所以我们就找一个相同关联的字段---ROWNUM,唯一且能关联，再使用全连接full join 就能解决了问题了，SQL语句如下：

SELECT A.*, B.* FROM (SELECT S.*, ROWNUM RN FROM STUDENT S) A FULL JOIN (SELECT C.*, ROWNUM RN FROM COURSE C) B ON A.RN = B.RN;

可以看出通过rownum 和full join 就能轻松的实现良两张表的横向连接展示了。

如果不想显示RN这一列的话，就不要用*就OK了，需要什么字段显示什么字段：

SELECT A.ID, A.NAME, A.CLASS, B.ID, B.C_NAME
FROM 
  (SELECT S.*, ROWNUM RN FROM STUDENT S) A
  FULL JOIN 
  (SELECT C.*, ROWNUM RN FROM COURSE C) B
ON A.RN = B.RN;

 

sql行转列，逗号隔开

 select GROUP_CONCAT(`name`) from es_building where id in (1,2)     结果 name:  a,b