数据库语句增删改查，常用方法，正则表达式详解

数据库常用数据类型

decimal    #精准数据类型
timestamp  #UTC（世界标准时间）进行存储 查询时 客户端当前时区进行返回
tinyint   #tinyint(2) 插入1时他会显示01  tinyint(4) 插入1时他会显示0001  status type等 

常用SQL

 SELECT app.AppID,Context FROM feedback  LEFT JOIN app ON app.AppID=feedback.AppID  
 WHERE feedback.AppID=@appID AND  feedback.UserID=@userID 
 SELECT count(*) as count from `user` where UserName=@userName;
 SELECT DISTINCT 列名称 FROM 表名称    #DISTINCT 去掉重复值
 SELECT  DATE_FORMAT(CreateTime,'%Y-%m-%d') as date 
 # 分页 返回从第0 到5 条数据 
 SELECT SectionName,ID,book.BookEName FROM section LEFT JOIN book ON book.BookID=section.BookID  WHERE book.BookEName=@bookEName ORDER BY section.ID ASC 
 LIMIT 0, 5 
 
 FOUND_ROWS()
 select FOUND_ROWS() as dataCount # 函数返回的是上一条最近一条SELECT语句
 #上一条SELECT 语句查询结果为空，则返回 0
 
select ROW_COUNT()  #函数返回上一条SQL语句，对表数据进行修改操作后影响的记录数
  
GROUP_CONCAT()  #表达式结合体的连结值  
select GROUP_CONCAT(DISTINCT expr ORDER BY col_name DESC) from .....

SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name WHERE id > 100 LIMIT 10;
SELECT FOUND_ROWS();
# 第二个SELECT将返回第一条SELECT如果没有LIMIT时返回的行数。

#合并两个或多个 SELECT 语句的结果集 SELECT语句必须拥有相同数量的列  
#相似的数据类型 列的顺序必须相同 
UNION #选取不同的值。
UNION ALL  #允许重复的值

update test_tb set address=replace(address,'东','西') where id=2 
#将address字段里的 “东” 替换为 “西” 

UPDATE feedback SET ReplyContext=@replyContext WHERE ID=@ID

UPDATE notice SET `Enable` = CASE `Enable` WHEN 0 THEN 1 WHEN 1 THEN 0 END WHERE ID=@ID;

left join #左连接 左表为主
right join  #右连接 右表为主  
join = inner join # 内连接 交集
full outer join #左右表所有行 并集

DELETE FROM feedback WHERE ID=@ID

INSERT INTO testFav( AppEName)  VALUES( @appEName) 
ON DUPLICATE KEY UPDATE IsFav=CASE IsFav WHEN 0 THEN 1 WHEN 1 THEN 0 END,CreateTime=Now()

INSERT INTO userApp(UserID,AppEName) values(@userID,@appEName) on DUPLICATE key  UPDATE AppEName=@appEName

INSERT INTO videouser(VideoID,CreateTime,Duration) values(@videoID,now(),@duration)   
 on DUPLICATE key UPDATE  Duration=IF(Duration<@duration,@duration,Duration) 

#on DUPLICATE key 判断重复数据 有则更新，无则插入   数据表必须有唯一主键、唯一索引

#表要求：有PrimaryKey，或者unique索引  
#结果：表id都会自增：
replace into names(name, age) values("小明", 23);
#已存在记录，删除原来的记录，添加新的记录  不存在替换，添加新的记录

insert ignore into names(name, age) values("大壮", 25);
#插入已存在的记录，则忽略 ，id会自增，不会报错 ；不存在，添加新的记录
-- 用select @@identity得到上一次插入记录时自动产生的ID 

DESC  #降序排列
SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC
ASC #升序

DATE_SUB(date,INTERVAL expr type)

#从日期减去指定的时间间隔。`
UPDATE userVip SET EndTime=DATE_SUB(NOW(),INTERVAL 1 day)  WHERE UserID=@userID  
 INTERVAL -31 DAY  #表示未来31天
 INTERVAL  31 DAY  #表示过去31天

DATEDIFF(大日期，小日期)

SELECT DATEDIFF('2021-12-29','2008-12-30') AS DiffDate  #年间隔

存储过程

CREATE  PROCEDURE 存储过程名([[IN |OUT |INOUT ] 参数名 数据类形...])

mysql> delimiter $$　　#将语句的结束符号从分号;临时改为两个$$(可以是自定义)
mysql> CREATE PROCEDURE delete_matches(IN p_playerno INTEGER)
    -> BEGIN
    -> 　　DELETE FROM MATCHES
    ->    WHERE playerno = p_playerno;
    -> END$$
Query OK, 0 rows affected (0.01 sec)
 
mysql> delimiter;

#调用
mysql> call delete_matches(57);

存储过程的查询

SHOW PROCEDURE STATUS WHERE db='数据库名' 
SHOW CREATE PROCEDURE 数据库.存储过程名;

修改/删除存储过程

ALTER PROCEDURE
DROP  PROCEDURE

变量定义

DECLARE l_int int unsigned default 4000000;  
DECLARE l_numeric number(8,2) DEFAULT 9.95;  #有效位数是8，精确到小数点后两位，并四舍五入
DECLARE l_date date DEFAULT '1999-12-31';  
DECLARE l_datetime datetime DEFAULT '1999-12-31 23:59:59';  
DECLARE l_varchar varchar(255) DEFAULT 'This will not be padded';

常用regex

var phoneReg = /^\w{11}$/;    /^1\d{10}$/  匹配电话号码  /^1[345789]\d{9}$/
/\d{1,3}/   匹配1000以内的正整数
phoneReg.test(phone);
 //i（不区分大小写）  /g 全局搜索
/\【(.+?)\】/g    /\[ [^\] ]+ \]/g    
/\n|\t|\r/g   //匹配 换行符,制表符,回车符
.    // 除换行符以外的所有字符。
^    // 字符串开头。
$    // 字符串结尾。
\    //将下一个字符标记为或特殊字符
\d,\w,\s // 匹配数字、字符、空格。
\D,\W,\S // 匹配非数字、非字符、非空格。
[abc] // 匹配 a、b 或 c 中的一个字母。
[a-z] // 匹配 a 到 z 中的一个字母。
[^abc] // 匹配除了 a、b 或 c 中的其他字母。
aa|bb // 匹配 aa 或 bb。
?  //0 次或 1 次匹配。
*  //匹配 0 次或多次。
+  //匹配 1 次或多次。
{n} // 匹配 n次。
{n,} // 匹配 n次以上。
{m,n} // 最少 m 次，最多 n 次匹配。
(expr) // 捕获 expr 子模式,以 \1 使用它。
(?:expr) // 忽略捕获的子模式。
(?=expr) // 正向预查模式 expr。
(?!expr) // 负向预查模式 expr。