My SQL 应用总结

创建表
CREATE TABLE TEXT(
ID INT(10) NOT NULL AUTO_INCREMENT COMMENT'id',
NAME VARCHAR(50) NOT NULL COMMENT'姓名',
AGE INT(10) NULL COMMENT'年龄',
SEX INT(10) NULL COMMENT'性别',
PHONE VARCHAR(11) NULL COMMENT'手机',
EMAIL VARCHAR(50) NULL COMMENT'电子邮箱',
REMARK VARCHAR(50) NULL COMMENT'备注',
PRIMARY KEY(ID)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

ALTER TABLE — 更改表属性
添加字段：
ALTER TABLE TEXT ADD COLUMN BIRTHDAY VARCHAR(20) NULL DEFAULT NULL COMMENT '生日' AFTER SEX (在SEX字段后面添加BIRTHDAY字段)
删除字段：
ALTER TABLE TEXT DROP COLUMN BIRTHDAY
调整字段顺序：
ALTER TABLE TEXT CHANGE BIRTHDAY BIRTHDAY VARCHAR(20) NULL DEFAULT NULL COMMENT '生日' AFTER ID
重命名列
ALTER TABLE TEXT CHANGE BIRTHDAY UP_DIRTHDAY VARCHAR(20) NULL DEFAULT NULL COMMENT '生日'
创建存储过程
DROP PROCEDURE IF EXISTS PROC_GET_DATAS;
DELIMITER //
CREATE DEFINER=`root`@`%` PROCEDURE PROC_GET_DATAS(IN P_ID INT)
LANGUAGE SQL
CONTAINS SQL
COMMENT '根据Id获取信息'
BEGIN
DECLARE V_LOOP INT(11); /*定义循环变量*/
DECLARE V_NAME VARCHAR(50);
/*创建临时表begin*/
DROP TABLE IF EXISTS TEMP_DATAS_TABLE;
CREATE TEMPORARY TABLE TEMP_DATAS_TABLE (
SELECT * FROM TEXT WHERE ID = P_ID;
);
/*创建临时表end*/
/*按id从小到大循环出TEMP_DATAS_TABLE中数据并插入TEXT_B表中bgin*/
SET V_LOOP = (SELECT MIN(ID) FROM TEMP_DATAS_TABLE);/*给V_LOOP赋值*/
WHILE V_LOOP IS NOT NULL DO
SET V_NAME = (SELECT NAME FROM TEMP_DATAS_TABLE WHERE ID = V_LOOP);
INSERT INTO TEXT_B (ID,NAME) VALUES (V_LOOP,V_NAME);
SET V_LOOP = (SELECT MIN(ID) FROM TEMP_DATAS_TABLE WHERE ID > V_LOOP);/*找出下一个最小ID*/
END WHILE;
/*循环end*/
SELECT * FROM TEXT_B;
END//
DELIMITER ;

创建函数
DROP PROCEDURE IF EXISTS FUNC_FORMAT_NUM;

DELIMITER //

CREATE DEFINER=`root`@`localhost` FUNCTION FUNC_FORMAT_NUM(P_NUM DECIMAL(18,6), P_TYPE INT)
RETURNS varchar(50)
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT '设置传入参数保留几位小数'
BEGIN

IF P_TYPE=2 THEN

RETURN ROUND(P_NUM, 2);

END IF;

IF P_TYPE=3 THEN

RETURN ROUND(P_NUM, 3);

END IF;

RETURN P_NUM;

END  //

DELIMITER ;

字段为空时转值 IFNULL(字段,0)
字符串连接函数 CONCAT(值1,值2,...,值n)
获取一个月的最后一天 LAST_DAT(日期) 如：LAST_DAT('2013-04-01')
获取一个日期的下一天 DATE_ADD(日期,INTERVAL 1 DAY)
获取一个日期的下一月 DATE_ADD(日期,INTERVAL 1 MONTH)
获取一个日期的下一年 DATE_ADD(日期,INTERVAL 1 YEAR)
两个日期相减得到相差天数 DATEDIFF(日期1,日期2) 如果结果为正数，日期1大于日期2，相反，结果是负数，日期1小于日期2
                         TIMESTAMPDIFF(DAY,日期1,日期2) 同上
两个日期相减得到相差月数 TIMESTAMPDIFF(MONTH,日期1,日期2) 同上
两个日期相减得到相差年数 TIMESTAMPDIFF(YEAR,日期1,日期2) 同上
格式化日期 DATE_FORMAT(字段,'%Y-%m-%d') 结果：yyyy-MM-dd
获取系统当前时间： NOW();
创建临时变量
SET @变量=(函数，或查询语句) 或 (2)SELECT 字段1, ,...,字段n INTO @变量1 ,...,@变量n
创建临时表
(1)CREATE TEMPORARY TABLE 临时表名称 (定义字段);
(2)CREATE TEMPORARY TABLE 临时表名称 (SQL查询语句);
(3)CREATE TEMPORARY TABLE IF NOT EXISTS 临时表名称 (定义字段);
查询表中有没有某个字段
SELECT COUNT(*) FROM information_schema.`COLUMNS` WHERE TABLE_SCHEMA=所在库 AND TABLE_NAME=表名 AND COLUMN_NAME = 字段名称
执行拼接sql SET @stmt = '拼接的sql';PREPARE sql_stmt FROM @stmt; EXECUTE sql_stmt; DEALLOCATE PREPARE sql_stmt;控制语句(1)判断: IF '条件' THEN 'SQL语句' END IF;(2)循环: WHILE '条件' DO 'SQL语句' END WHILE;
行转列
GROUP_CONCAT(字段) 如：SELECT GROOP_CONCAT(ID) WHERE T_USER ,假设该表中有1,2，3三个USER，那么查询出来的结果就是：1,2,3
GROUP_CONCAT(DISTINCT C.ID separator  ';') WHERE T_USER ,假设该表中有1,2，3三个USER，那么查询出来的结果就是：1;2;3
IN和FIND_IN_SET的区别
MYSQL Split函数
SELECT REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(IFNULL(V_RS_VAL,''),',',P_INDEX)),',',1));
V_RS_VAL：以逗号","拼接的字符串 如：111,222,333,444,555,666,777
P_INDEX：取值索引，以上面为例，假设V_RS_VAL='111,222,333,444,555,666,777' ,当P_INDEX=1,则执行结果为111，当P_INDEX=7,则执行结果为777
MYSQL 某个字符出现的个数,如检查字符串'12.3,12.4,12.5,12.6'中','出现的个数
SELECT CHAR_LENGTH('12.3,12.4,12.5,12.6') - CHAR_LENGTH(REPLACE('12.3,12.4,12.5,12.6', ',', ''));  执行结果3
获取字符串'12.3,12.4,12.5,12.6'中‘2.4,12.5,12.6’
SELECT SUBSTRING_INDEX(IFNULL('12.3,12.4,12.5,12.6',''),',',-(CHAR_LENGTH('12.3,12.4,12.5,12.6') - CHAR_LENGTH(REPLACE('12.3,12.4,12.5,12.6', ',', ''))));

   
   
CREATE TABLE `test` (  
  `id` int(8) NOT NULL auto_increment,  
  `name` varchar(255) NOT NULL,  
  `list` varchar(255) NOT NULL,  
  PRIMARY KEY  (`id`)  
)  
  
  
INSERT INTO `test` VALUES (1, 'name', 'daodao,xiaohu,xiaoqin');  
INSERT INTO `test` VALUES (2, 'name2', 'xiaohu,daodao,xiaoqin');  
INSERT INTO `test` VALUES (3, 'name3', 'xiaoqin,daodao,xiaohu');  
  
  
test1:sql = select * from `test` where 'daodao' IN (`list`);  
得到结果空值.  
test2:sql = select * from `test` where FIND_IN_SET('daodao',`list`);  
得到三条数据。 
-- 查数据库运行状态
SELECT * FROM information_schema.INNODB_TRX;
-- 将锁定的执行断开
kill trx_mysql_thread_id;
-- 设置超时时间
set global innodb_lock_wait_timeout = 60;
-- 查询超时时间
show variables like '%time%';

将1,2,3,4,5,6结果集按数值下标形式去值，但不是从0开始而是1

REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(IFNULL('50,60',''),',',2)),',',1));

游标存储过程

CREATE PROCEDURE `p_test`()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN

DECLARE V_ID VARCHAR(50); -- 自定义变量1  
DECLARE V_NAME VARCHAR(50); -- 自定义变量2  
DECLARE V_DONE INT DEFAULT FALSE; -- 自定义控制游标循环变量,默认false
DECLARE MY_CURSOR CURSOR FOR (SELECT userId, userName FROM base_user);-- 定义游标并输入结果集  
DECLARE CONTINUE HANDLER FOR NOT FOUND SET V_DONE = TRUE; -- 绑定控制变量到游标,游标循环结束自动转true  
OPEN MY_CURSOR;-- 打开游标  
V_LOOP:LOOP -- 开始循环体,myLoop为自定义循环名,结束循环时用到  
FETCH MY_CURSOR INTO V_ID, V_NAME; -- 将游标当前读取行的数据顺序赋予自定义变量12  
IF V_DONE THEN -- 判断是否继续循环  
     LEAVE V_LOOP; -- 结束循环  
END IF;
SELECT V_ID, V_NAME;
-- 自己要做的事情,在 sql 中直接使用自定义变量即可  
    -- UPDATE t_user SET c_name = my_name WHERE id = V_ID and rtrim(ltrim(V_NAME)) = ''; -- 左右去空格  
COMMIT; -- 提交事务  
  END LOOP V_LOOP; -- 结束自定义循环体  
CLOSE MY_CURSOR; -- 关闭游标    

END