mysql查漏补缺2

游标

 

CREATE PROCEDURE curdemo()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE a CHAR(16);
  DECLARE b, c INT;
  DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
  DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;-- --指定游标循环结束时的返回值

  OPEN cur1;
  OPEN cur2;

  read_loop: LOOP
    FETCH cur1 INTO a, b;--etch是获取游标当前指向的数据行，并将指针指向下一行，当游标已经指向最后一行时继续执行会造成游标溢出。
    FETCH cur2 INTO c;
    IF done THEN
      LEAVE read_loop;
    END IF;
    IF b < c THEN
      INSERT INTO test.t3 VALUES (a,b);
    ELSE
      INSERT INTO test.t3 VALUES (a,c);
    END IF;
  END LOOP;

  CLOSE cur1;
  CLOSE cur2;
END;

use one

go

--获取所有的banner表 然后游标 加字段
declare tableNames CURSOR for SELECT  NAME  FROM SYSOBJECTS WHERE TYPE='U' and NAME like '%banner%'

open tableNames

DECLARE @temp nvarchar(50)
FETCH NEXT from tableNames into @temp
DECLARE @SQL NVARCHAR(MAX)
while @@Fetch_Status = 0
BEGIN

--set  @SQL ='alter table ' + @temp +' drop column banner_image_p1'
set  @SQL ='alter table '+ @temp +' add banner_image_p VARCHAR(256)'
--如果没有sp_executesql  则exec 把@sql当成存储过程 报错 找不到这个存储过程
EXEC sp_executesql  @SQL
fetch NEXT from tableNames into @temp

END
CLOSE tableNames
DEALLOCATE tableNames;

参考：https://www.jianshu.com/p/f9dcfc14e0b6

范式

范式越高，数据的冗余度越小。其实没有冗余的数据库设计是可以做到的。但是，没有冗余的数据库未必是最好的数据库

 

第一范式：1NF）：属性不可分
          （1NF是对属性的原子性约束，要求属性具有原子性，不可再分解）

分表

 

编码

常用

select into table1 from table2 where 条件
复制所有列到一张新表

INSERT INTO table2 SELECT * FROM table1 WHERE condition;
复制列从一张表到另一张表

SELECT OrderID, Quantity,
CASE
    WHEN Quantity > 30 THEN "The quantity is greater than 30"
    WHEN Quantity = 30 THEN "The quantity is 30"
    ELSE "The quantity is something else"
END 
FROM OrderDetails;
当第条件满足时 返回一个值

SELECT CONCAT("SQL ", "Tutorial ", "is ", "fun!") AS ConcatenatedString; 
concat是用于字符串拼接

 

分页

select * from table where 1 limit a,b
从第a条开始向后读取b条数据

 

select * from (select Row_number() over (order by id) as row ,* from table) as newTable where row between a and b
 --先给数据标号 读取a和b之间的数据

 

 

mysql中文乱码解决方案：