MYSQL 语法基础随记

1 各种JOIN

2 临时表、文件


CREATE TEMPORARY TABLE tmp_table (

name VARCHAR(10) NOT NULL,

value INTEGER NOT NULL

)



CREATE  TEMPORARY  TABLE a (select * FROM sys_user_role);

Select * into outfile 'c://outfile.txt' from test;

3 变量（局部变量、用户变量、会话变量、全局变量）

https://www.cnblogs.com/genialx/p/5932558.html

https://www.cnblogs.com/EasonJim/p/7966918.html

3.1 使用set或select直接赋值，变量名以@开头，可以在一个会话的任何地方声明，作用域是整个会话，称为用户变量。

set @var=1;

3.2 以declare关键字声明的变量，只能在存储过程中使用（begin..end），或者是给存储传参数中，称为存储过程/局部变量，例如：

declare var1 int default 0;

4 查询结果增加序号列

https://www.cnblogs.com/smile-nn/p/7690775.html

4.1.法一：

select  (@i:=@i+1)  i,a.url from  base_api_resources a  ,(select   @i:=0)  t2 order by a.id desc limit 10;

4.2.法二：有序号就不能 select *，一定要指定显示类名

set @rownum=0;
select @rownum:=@rownum+1 as rownum, a.url from base_api_resources a order by a.id desc limit 10

5 时间处理

格式化

select FROM_UNIXTIME(1344887103);  //参数：一般为10位的时间戳（秒级别）
2012-08-14 03:45:03     

select unix_timestamp("2012-08-14 03:45:03") //秒级时间戳
1344887103
  
select unix_timestamp(NOW())* 1000 //粗算毫秒时间戳

SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s') //参数：datetime
2012-08-14 03:45:03 

SELECT DATE_FORMAT(NOW(),'%H')
03

时间差

//datediff函数，返回值是相差的天数，不能定位到小时、分钟和秒
select datediff('2018-03-22 09:00:00', '2018-03-20 07:00:00');

//TIMESTAMPDIFF函数 定位到天数、小时、分钟和秒
select TIMESTAMPDIFF(DAY, '2018-03-20 23:59:00', '2015-03-22 00:00:00');
select TIMESTAMPDIFF(HOUR, '2018-03-20 09:00:00', '2018-03-22 10:00:00');
select TIMESTAMPDIFF(SECOND, '2018-03-20 09:00:00', '2018-03-22 10:00:00');

时间计算

DATE_ADD(date,INTERVAL expr type）给日期添加指定的时间间隔

DATE_SUB(date,INTERVAL expr type）从日期减去指定的时间间隔

type：SECOND MINUTE HOUR DAY WEEK MONTH QUARTER YEAR

select date_sub(now(),interval 1 day); //减一天  返回格式 2018-12-19 23:35:39

6 NULL处理

1.isnull(exper) 判断exper是否为空，是则返回1，否则返回0

2.ifnull(exper1,exper2)判断exper1是否为空，是则用exper2代替

3.nullif(exper1,exper2)如果expr1= expr2 成立，那么返回值为NULL，否则返回值为 expr1。

7 MySQL类型转换

CAST()和CONVERT()函数可用来获取一个类型的值，并产生另一个类型的值。两者具体的语法如下：

1、CAST(value as type) 就是CAST(xxx AS 类型)

2、CONVERT(value, type) 就是CONVERT(xxx,类型)

但是要特别注意,可以转换的数据类型是有限制的。这个类型可以是以下值其中的一个：

二进制，同带binary前缀的效果 : BINARY
字符型，可带参数 : CHAR()
日期 : DATE
时间: TIME
日期时间型 : DATETIME
浮点数 : DECIMAL
整数 : SIGNED （小数丢失，不是四舍五入）
无符号整数 : UNSIGNED

8 动态SQL

推荐都使用？占位符预编译sql，不要直接拼接，防止注入

set @stmt = concat('select * from ',table_name,' limit ?,?');--table_name 表名字符串
prepare s1 from @stmt;
set @s1 = page_begin;
set @s2 = page_end;
execute s1 using @s1,@s2;
deallocate prepare s1;

9 返回影响

SqlServer、mysql 要用变量在执行后立即记录影响行数，因为影响行数只返回上一条语句的，且不立即记录有可能会出现意想不到的失准情况。

MYSQL

found_rows() : select
row_count() : insert update delete

insert into `MyTestTable`(`ID`,`Name`)
  select '1','role1' union all
  select '2','role2' union all
  select '3','role3';
select row_count(); -- 输出3(返回新添加的记录数),[注:如果使用insert into...values只返回1]
 
select * from `MyTestTable`;
select found_rows(); -- 输出3(返回选择的行数)

update `MyTestTable` set `Name`='people';
select row_count(); -- 输出3(返回修改的行数)

delete from `MyTestTable`;
select row_count(); -- 输出3(返回删除的行数)

SqlServer

declare @rcount int  
update task_hz set is_ysh='是',state='分配'  where djbh='' and is_ysh='中' 
//记录受影响的行数
set @rcount=@@rowcount

if  @rcount= 0 //判断上句UPDATE是否有受影响的行
begin
   //没有，表示没有djbh='' and is_ysh='中' 的数据
   update task_hz set is_ysh='中'  where djbh='' and is_ysh='否'

   //记录受影响的行数
   set @rcount=@@rowcount 
end

select @rcount  as rcount

10 WHILE 循环

while do ..... end while

    DECLARE COUNT INT DEFAULT 0;
    DECLARE SUM INT DEFAULT 0;
    WHILE COUNT < in_count DO
        SET SUM = SUM + COUNT;
        SET COUNT = COUNT + 1;
    END WHILE;
    SELECT SUM;

11 TRIM

SELECT TRIM('  bar   ');   --去除空格-> 'bar'
SELECT LTRIM('  barbar');
SELECT RTRIM('  barbar  ');
SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');   --删除指定的首字符 x -> 'barxxx'
SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');     --删除指定的首尾字符 x -> 'bar' 
SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');  --删除指定的尾字符 xyz -> 'barx'

12 OFFSET 显示偏移

第二高的薪水

https://leetcode-cn.com/problems/second-highest-salary/solution/

SELECT DISTINCT
    Salary AS SecondHighestSalary
FROM
    Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 1  #显示1条数据，从[1]开始。默认是从[0]

存储过程（函数）

//推荐用navicat创建

CREATE DEFINER=`root`@`%` PROCEDURE `selectuser`(IN `flag` int,INOUT  getresult int )
BEGIN

if flag=1 THEN

  select getresult ;

  set getresult=10;//INOUT

end IF;
  
END

执行

set @getresult=1;  
call selectuser(1,@getresult) ; //INOUT参数 必须由@变量传入

select @getresult //接收INOUT参数结果 10

数字辅助表

https://blog.csdn.net/jiangnan2014/article/details/18861521

行列互换

https://www.cnblogs.com/ken-jl/p/8570518.html

https://blog.csdn.net/sxdtzhaoxinguo/article/details/55519171

posted @ 2018-12-05 23:52 sw008 阅读(183) 评论(0) 收藏举报

刷新页面返回顶部

sw008

MYSQL 语法基础 随记