SQL解决方案

最新推荐文章于 2024-08-23 17:37:15 发布

m0_38045653

最新推荐文章于 2024-08-23 17:37:15 发布

阅读量155

点赞数

文章标签： mysql

本文链接：https://blog.csdn.net/m0_38045653/article/details/105142232

版权

#10 CAPTER

SQL解决方案

1 字符串

合并字符串

 SELECT CONCAT (TITLE ,'_',SUBTITLE) AS TITLE_SUBTITLE
    FROM TITLES
   WHERE TITLE IS NOT NULL AND TITLE <> '' AND SUBTITLE IS NOT NULL AND SUBTITLE <> ''

#截取字符串
#正序取

	SELECT TITLE ,SUBSTRING(TITLE ,1,3)  # SUBSTRING(CLOUMN ,START_INDEX,LENGTH)  START_INDEX从1开始 不能从0开始
	   FROM TITLES
	  WHERE TITLE IS NOT NULL AND TITLE <> '';

#倒序取

  SELECT TITLE ,SUBSTRING(TITLE ,-3,3)  # SUBSTRING(CLOUMN ,START_INDEX,LENGTH)  # START_INDEX从1开始 不能从0开始
   FROM TITLES
  WHERE TITLE IS NOT NULL AND TITLE <> ''

#与Oracle SUBSTR的区别

SUBSTR(COLUMN ,START_INDEX ,LENGTH) START_INDEX 0，1没区别

#与java substring 区别

SUBSTRING(COLUMN ,START_INDEX ,END_INDEX)

#与js substr区别

 SUBSTR(START_INDEX ,LENGTH)       SUBSTRING(START_INDEX,END_INDEX)

#确定字符的长度

SELECT LENGTH('中华') FROM DUAL;  #字节数 utf8 中文 6字节
SELECT CHAR_LENGTH('中华') FROM DUAL; #字符数 2个字符
SELECT LENGTH('AA') FROM DUAL; #  2
SELECT CHAR_LENGTH('AA') FROM DUAL;  # 2

#类似nvl

set @tst = null; 
SELECT IFNULL(@TST,'源为空') FROM DUAL;#当tst = '' 时，这个变量并不是null

#类似nvl2

set @tst = null;
SELECT IF(@tst IS NULL ,'T','F') FROM DUAL;

将字符串输短到一个给定的长度

SELECT IF(CHAR_LENGTH(TITLE) >30 
           ,CONCAT( SUBSTR(TITLE,1,20) ,'...',RIGHT(TITLE,5)) 
           ,TITLE) AS TITLE
        FROM TITLES

#替换字符串

SELECT REPLACE(TITLE,'.','\'') FROM TITLES ;    
#INSTR
SELECT * FROM TITLES WHERE INSTR(TITLE,'.')>0 ORDER BY ID DESC

LIKE 当有特殊字符 % _ 前面需要加\

SELECT 'MYSQL' LIKE '%sql'  #查询一个布尔值   1 能匹配 0 不能匹配  
SELECT 'MYSQL' LIKE BINARY '%sql'
#like 函数  oracle 区分大小写 但是mysql默认不区分大小写
SELECT TITLE FROM TITLES WHERE TITLE LIKE '%sql%'
SELECT TITLE FROM TITLES WHERE TITLE LIKE BINARY '%SQL%'
SELECT TITLE FROM TITLES WHERE TITLE LIKE '\%'

正则表达式 REGEXP 默认不区分大小写 BINARY 大写

SELECT 'ABCD' REGEXP '^abcd$'  
SELECT 'ABCD' REGEXP BINARY '^ABCd$';
SELECT 'ABCK' REGEXP 'AB'
SELECT 'ABCK' REGEXP BINARY '[ABc]{3}'
SELECT 'ABCK' REGEXP '[ABC]{3}'
#大写
SELECT 'A' = 'a';
SELECT 'a' = BINARY 'A';

#日期和时间可采用三种方式

	SELECT COUNT(1) AS CNT
	  FROM TITLES  
	 WHERE TS BETWEEN '2004-11-19 00:20:20' AND '2019-11-20 20:20:20';
	 
	SELECT COUNT(1) AS CNT
	   FROM TITLES
	  WHERE TS BETWEEN '2004/11/19 00:20:20' AND '2019-11-20 20:20:20';

	SELECT COUNT(1) AS CNT 
	   FROM TITLES 
	  WHERE TS BETWEEN '20041119002020' AND '20191120202020';

#按年查询 YEAR()函数

	SELECT YEAR(TS) AS DATA_YEAR
	     ,COUNT(1) AS CNT 
	    FROM TITLES 
	   GROUP BY YEAR(TS)
	   ORDER BY YEAR(TS) DESC

#按月份查询 MONTH()函数

	   SELECT MONTH(TS) DATA_MONTH
	      ,COUNT(1) AS CNT
	    FROM TITLES
	   GROUP BY MONTH(TS)
	   ORDER BY MONTH(TS) DESC   
	   
	   
	   
	   SELECT CONCAT(YEAR(TS),MONTH(TS)) DATA_MONTH
	      ,COUNT(1) AS CNT
	    FROM TITLES
	   GROUP BY DATA_MONTH
	   ORDER BY DATA_MONTH DESC

 DATE_FORMAT() 		函数    DATE_FORMAT(TS ,'%y-%m')  yyyy-mm %m不能是大写否则会变英文月份

	  SELECT COUNT(1) AS CNT  
	       ,DATE_FORMAT(TS ,'%y-%m') AS DATA_MONTH   
	      FROM TITLES
	     GROUP BY DATA_MONTH
	     ORDER BY DATA_MONTH DESC
	# yyyy-MM-dd HH24:MI:SS 
	  SELECT DATE_FORMAT(NOW() ,'%Y-%m-%d %H:%i:%s') FROM DUAL;
	  SELECT DATE_FORMAT(NOW() ,'%Y-%m-%d') FROM DUAL;

#与时期、时间相关的计算函数

# ADDDATE() 增加时间
  SELECT ADDDATE('2019-01-04 22:00:00',INTERVAL '00:22:22' HOUR_SECOND) FROM DUAL;
  SELECT ADDDATE('2019-01-04', INTERVAL '22:22:22' HOUR_SECOND) FROM DUAL;
  SELECT ADDDATE('2019-01-04 22:00:00',INTERVAL 1 DAY) FROM DUAL;
  SELECT ADDDATE('2019-01-04 22:00:00',INTERVAL 1 MONTH) FROM DUAL;
  SELECT ADDDATE('2019-01-04 22:00:00',INTERVAL 1 YEAR) FROM DUAL;
  #SUBDATE() 减去时间
  SELECT SUBDATE('2019-01-04 22:22:22',INTERVAL '00:22:22' HOUR_SECOND) FROM DUAL;
  SELECT SUBDATE('2019-01-04 22:00:00',INTERVAL 1 DAY) FROM DUAL;
  SELECT SUBDATE('2019-01-04 22:00:00',INTERVAL 1 MONTH) FROM DUAL;
  SELECT SUBDATE('2019-01-04 22:00:00',INTERVAL 1 YEAR) FROM DUAL;
  #ADDTIME() 增加时间
  SELECT ADDTIME('2019-01-04 22:00:00','00:22:22') FROM DUAL;
  SELECT ADDTIME('2019-01-04 22:00','00:22:22') FROM DUAL;
  SELECT ADDTIME('2019-01-04 22','00:22:22') FROM DUAL;
  #SUBTIME()  减去时间
  SELECT SUBTIME('2019-01-04 22:22:22','00:22:22') FROM DUAL;
  SELECT SUBTIME('2019-01-04 22:22','00:22:22') FROM DUAL;
  SELECT SUBTIME('2019-01-04 22','00:22:22') FROM DUAL;
  SELECT SUBTIME('2019-01-04','00:22:22') FROM DUAL;#不能用
  #DATEDIFF 相隔多少日 不理会时分秒，指的是脚踏多少日期 而不是相隔24小时算1天 忽略其中时分秒部分
  SELECT DATEDIFF('2019-01-04','2019-01-01') FROM DUAL;
  SELECT DATEDIFF('2019-01-04 000000','2019-01-01 235959') FROM DUAL;#不能用 必须要有:
  SELECT DATEDIFF('2019-01-04 00:00:00','2019-01-03 23:59:59') FROM DUAL; # 1
  #TIMEDIFF 隔多少时分秒 精确到时分秒 
  SELECT TIMEDIFF('2019-01-04 00:00:00','2019-01-03 23:59:59') FROM DUAL; # 00:00:01
  SELECT TIMEDIFF('2019-01-09 00:00:00','2019-01-03 23:59:59') FROM DUAL; # 120:00:01
  #HOUR MINUTE SECOND
  SELECT HOUR(TIMEDIFF('2019-01-09 00:00:00','2019-01-03 23:59:59')) FROM DUAL;
  #一个月的最后一天
  SELECT LAST_DAY(NOW()) FROM DUAL;
  SELECT LAST_DAY('2019-04-05') FROM DUAL;
  #当前日期 CURDATE() 
  SELECT DAY(CURDATE()) FROM DUAL;#当天减月初第一天的天数
  #月初第一天
  SELECT ADDDATE(CURDATE() ,INTERVAL -DAY(CURDATE()) + 1 DAY ) FROM DUAL;
  SELECT ADDDATE('2019-04-05', INTERVAL - DAY('2019-04-05') + 1 DAY) FROM DUAL;
  #时间戳 UNIX_TIMESTAMP()转成Long值    FROM_UNIXTIME()转成时分秒
  SELECT NOW() ,UNIX_TIMESTAMP(NOW()) FROM DUAL;
  SELECT FROM_UNIXTIME(1574323314) FROM DUAL;

#变量与条件表达式 IF CASE

IF函数

SELECT IF(CHAR_LENGTH(TITLE) >30 
           ,CONCAT( SUBSTR(TITLE,1,20) ,'...',RIGHT(TITLE,5)) 
           ,TITLE) AS TITLE
        FROM TITLES;

#正常的CASE WHEN

SELECT CASE 
	       WHEN CHAR_LENGTH(TITLE)>30
	       THEN
	          CONCAT( SUBSTR(TITLE,1,200),'...',RIGHT(TITLE,5) )  
	       ELSE TITLE
       END AS TITLE
      FROM TITLES;

DECODE()函数的实现

SELECT CASE TITLE
         WHEN 'CSS-Praxis' THEN 'YES CSS-Praxis'
         ELSE 'NO CSS-Praxis'
      END
   FROM TITLES;

#自定义排序

#ORACLE中的写法
 SELECT * 
    FROM TABLE_NAME 
   ORDER BY DECODE(COLUMN1,'XXX',1,'YYY',2,'ZZZ',3,LENGTH(COLUMN1)) DESC

#MYSQL的写法

 SELECT * 
   FROM TITLES
  WHERE SUBTITLE IS NOT NULL 
  ORDER BY CASE AUTHORS 
              WHEN 'DuBois Paul' THEN 1
              WHEN 'Kofler Michael' THEN 2
              ELSE LENGTH(AUTHORS) 
           END ;

#在数据表中复制数据
#1、利用操作创建新数据表只复制数据，并默认数据表类型为MYISAM 表结构定义时的各个字段的默认值自增属性都没有

	CREATE TABLE TITLES_BAK SELECT * FROM TITLES;
	SHOW CREATE TABLE TITLES;
	SHOW CREATE TABLE TITLES_BAK;

#2、最好是先创建TITLES相同的表，再将数据插入数据

    SHOW TABLE TITLES;
	#再创建TITLES_BAK
	INSERT INTO TITLES_BAK SELECT * FROM TITLES;
	#这样就能完整备份这个表了

#备份不同数据库的表
#1、完全备份含表结构

    SHOW CREATE TABLE DM.TITLES; 
	USE HWDW;
	#再创建HWDW.TITLES
	INSERT INTO HWDW.TITLES SELECT * FROM DM.TITLES;

#1.1 当备份表结构后，之前某个时间点曾备份过，现在又想备份，最容易出现问题的是AUTO_INCREMENT 字段 pk字段会报唯一约束异常

	#方法1
	  INSERT INGORE INTO HWDW.TITLES SELECT * FROM DM.TITLES;  #pk字段重复的记录不参与复制
	#方法2
	  REPLACE HWDW.TITLES SELECT * FROM DM.TITLES; #PK字段重复的记录会被覆盖。

#2、只是备份数据不备份表结构
CREATE TABLE HWDW.TITLES SELECT * FROM DM.TITLES;

统计报表

 SELECT TITLE,LANGNAME,CATNAME
   FROM TITLES,CATEGORIES ,LANGUAGES
  WHERE TITLES.CATID = CATEGORIES.CATID
    AND TITLES.LANGID = LANGUAGES.LANGID;

#1、此查询有个明显的弊端   语言SUM固定4个，但有可能语言可能有多个

	SELECT CATNAME
	     ,SUM(CASE WHEN TITLES.LANGID=1 THEN 1 ELSE 0 END) AS COUNT_ENGLISH
	     ,SUM(IF(TITLES.LANGID =2,1,0))  AS COUNT_DEUTSCH
	     ,SUM(CASE WHEN TITLES.LANGID = 3 THEN 1 ELSE 0 END) AS COUNT_SWENSK
	     ,SUM(CASE WHEN TITLES.LANGID = 4 THEN 1 ELSE 0 END)   AS COUNT_NORSK
	     ,COUNT(1) AS COUNT_ALL
	   FROM TITLES,CATEGORIES,LANGUAGES
	  WHERE TITLES.CATID = CATEGORIES.CATID
	    AND TITLES.LANGID = LANGUAGES.LANGID
	    GROUP BY CATNAME WITH ROLLUP
	   ORDER BY CATNAME

#2、故需要查出有多少种语言，再拼接SUM ,再拼接成完整的查询

	SELECT C.LANGNAME
	       ,COUNT(1) AS COUNT_LANGUAGE
	       ,A.LANGID
	     FROM TITLES A,CATEGORIES B,LANGUAGES C
	    WHERE A.CATID = B.CATID
	      AND A.LANGID = C.LANGID
	     GROUP BY A.LANGID

#月度查询统计报表

 SELECT * FROM VOTELANGUAGE;
 SELECT DATE_FORMAT(TS,'%Y-%m') AS DATA_MONTH
      ,SUM(CASE WHEN CHOICE = 1 THEN 1 ELSE 0 END) AS COUNT_C
      ,SUM(CASE WHEN CHOICE = 2 THEN 1 ELSE 0 END) AS COUNT_JAVA
      ,SUM(IF(CHOICE=3,1,0)) AS COUNT_PERL
      ,SUM(IF(CHOICE=4,1,0)) AS COUNT_PHP
      ,SUM(IF(CHOICE=5,1,0)) AS COUNT_VB
      ,SUM(IF(CHOICE=6,1,0)) AS COUNT_OTHER
      ,COUNT(1) AS COUNT_THIS_MONTH
     FROM VOTELANGUAGE
    GROUP BY DATA_MONTH
    ORDER BY DATA_MONTH DESC

#子查询
#语法变体：

SELECT * FROM TABLE_NAME1  WHERE COL = (SELECT COL FROM TABLE_NAME WHERE ID =1); #子查询为单行单列
SELECT * FROM TABLE_NAME1 WHERE COL (NOT) IN (SELECT COL FROM TABLE_NAME2) #子查询为离散列表

SELECT ROW(VALUE1,VALUE2,...) =(SELECT COL1,COL2 ,...) #两端都是离散值
SELECT ... WHERE COL = (NOT) EXISTS (SELECT...)   ;
SELECT .. FROM (SELECT ..) AS NAME WHERE ...

#示例

SELECT * 
   FROM TITLES 
  WHERE PUBLID IN (SELECT PUBLID 
                       FROM PUBLISHERS 
                     WHERE PUBLNAME LIKE BINARY 'O%')

SELECT *　
   FROM TITLES 
  WHERE WHERE EXISTS 
   (SELECT * FROM PUBLISHERS 
     WHERE TITLES.PUBLID = PUBLISHERS.PUBLID 
       AND PUBLNAME LIKE BINARY 'O%')

#当子查询结果为非空时对应的记录才会被考虑也就是只有与publishers表关联得上的记录才会被考虑

#以上语名相当于

SELECT A.*
  FROM TITLES A,PUBLISHERS B
 WHERE A.PUBLID = B.`publID`
   AND PUBLNAME LIKE BINARY 'O%'

#测试数据表TITLES是否收录有一本或多本符合 TITLE = ‘Linux’ subtitle=‘Installation,Konfiguration,Anwendung’ 条件的图书查询结果为1表示有这样的书

SELECT ROW('Linux','Installation,Konfiguration,Anwendung') = ANY (SELECT TITLE,SUBTITLE FROM TITLES)  #没区分大小写 1
SELECT ROW(BINARY 'LINUX','Installation,Konfiguration,Anwendung') = ANY (SELECT TITLE,SUBTITLE FROM TITLES);# 0
SELECT ROW('LINUX','Installation,Konfiguration,Anwendung') = ANY (SELECT TITLE,SUBTITLE FROM TITLES);# 1

#子查询

SELECT * FROM ( SELECT A.TITLEID
                       ,A.TITLE
                       ,COUNT(B.AUTHID) AS AUTH_COUNT
                    FROM TITLES A,REL_TITLE_AUTHOR B
                   WHERE A.TITLEID = B.TITLEID
                   GROUP BY B.TITLEID
                   HAVING AUTH_COUNT >1) TEMP
            ORDER BY AUTH_COUNT DESC

m0_38045653

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
SQL解决方案

#10 CAPTERSQL解决方案1 字符串合并字符串 SELECT CONCAT (TITLE ,'_',SUBTITLE) AS TITLE_SUBTITLE FROM TITLES WHERE TITLE IS NOT NULL AND TITLE <> '' AND SUBTITLE IS NOT NULL AND SUBTITLE <> ''...
复制链接

扫一扫