mysql 常用sql语句_Mysql常用SQL语句整理

一、查询操作

1.1 查询单表语句

select * from Table;

1.2 查询多表关联语句

select T.id,T2.name FROM Table T left join (select * from Table2 where id>10) T2 on T.fid =T2.id;

1.3 查询表100~200条

select * from am_agent_source  LIMIT 100,100                      //从100  到100的长度

1.4 查询表的重复记录(根据agent_name字段)

select agent_name,count(*) as count from am_agent_source group by agent_name having count>1

1.5 查询一张表存在的数据而另一张表中没有的数据

select * from am_agent_source t where t.agent_id not in (select distinct t1.agent_id from am_agent_source_name t1)

二、插入操作

2.1 插入表数据(查询满足条件的结果导入到表中)

insert into temp_333(afterlevel,num) select T.agent_name,T.contact_num from am_agent_source T where T.is_del=0;

2.2 备份表

CREATE TABLE temp_3333 LIKE temp_333;

INSERT INTO temp_3333 SELECT * FROM temp_333;

三、更新操作

3.1 更新单表数据

update temp_111 set username='张三' where userid=1740;

3.2 更新表数据(根据字段更新另一张表)

update temp_111 t1,temp_3333 t set t1.num=t.num where t1.afterlevel=t.afterlevel

四、删除操作

4.1 删除表的重复记录(保留最大一条)

delete from temp_333 where  id not in (select * from (select  max(id) from temp_333 group by num) t)

4.2 清空表(保留该表，删除表所有记录)

truncate table temp_333;

4.3 删除记录(删除部分记录)

delete from temp_333 T where T.isdel=1;

4.4 直接删除表

drop table temp_333;

五、日期查询

5.1 常用时间格式

select curtime();　　　　　　　　　 　　　　　　　 　　　　 //10:03:54

select date('2008-09-10 07:15:30'); 　　　　　　　　　　　　 //2008-09-10

select DATE_FORMAT(CURDATE()-1,'%Y-%m-%d');               // 昨天 2008-09-09

select time('2008-09-10 07:15:30'); 　　　　　　　　　　　　 // 07:15:30.123456

select year('2008-09-10 07:15:30');　　　　　　　　　　　　 // 2008

select quarter('2008-09-10 07:15:30'); 　　　　　　　　　　　// 3

select month('2008-09-10 07:15:30'); 　　　　　　　　　　　 // 9

select week('2008-09-10 07:15:30'); 　　　　　　　　　　　　// 36

select day('2008-09-10 07:15:30'); 　　　　　　　　　　　　　// 10

select hour('2008-09-10 07:15:30'); 　　　　　　　　　　　　 // 7

select minute('2008-09-10 07:15:30');　　　　　　　　　　　 // 15

select second('2008-09-10 07:15:30'); 　　　　　　　　　　　 // 30

select week('2018-11-29 10:10:56'); 　　　　　　　　　　　　 // 47 一年中第几个星期

select dayofweek('2018-11-29 10:10:56'); 　　　　　　　　   // 5 一周中第几天

select dayofmonth('2018-11-29 10:10:56'); 　　　　　　　　 // 29 一月中第几天

select dayofyear('2018-11-29 10:10:56'); 　　　　　　　　　 //333 一年中第几天

select last_day('2018-11-29 10:10:56');　　　　　　 　　　　 //2018-11-30 本月最后一天

select date_add('2018-11-29 10:15:53', interval 1 day);　　　　　　 //2018-11-30 10:15:53

select date_add('2018-11-29 10:15:53', interval 1 hour);　　　　　　 //2018-11-29 11:15:53

select date_add('2018-11-29 10:15:53', interval 1 minute);　　　　　 //2018-11-29 10:16:53

select date_add('2018-11-29 10:15:53', interval 1 week);　　　　　　//2018-12-06 10:15:53

select date_add('2018-11-29 10:15:53', interval 1 month);　　　　 //2018-12-29 10:15:5

select date_add('2018-11-29 10:15:53', interval 1 quarter);　　　　 //2019-02-28 10:15:53

select date_add('2018-11-29 10:15:53', interval 1 year);　　　　　　 //2019-11-29 10:15:53

select datediff('2019-11-29 10:15:53', '2019-08-29 10:15:53'); 　　　 //92 两个日期相差天数 前面减后面

select date_format('2019-11-29 10:15:53', '%Y-%m-%d %H:%i:%s'); //2019-11-29 10:15:53 时间格式化

select unix_timestamp();　　　　　　　　　　　　　　　　　　　　 //1543458439　　 当前时间戳

SELECT UNIX_TIMESTAMP('2019-11-29 10:15:53')　　　　　　 //1574993753 时间格式转时间戳

SELECT FROM_UNIXTIME(1574993753)　　　　　　　　　　　　 //2019-11-29 10:15:53 时间戳转时间格式

六、常用函数

6.1 处理Null值：IFNULL(空值，代替值)

select   IFNULL(afterlevel,'10')          --用10替代null

6.2 绝对值：abs()

select ABS(-32); 　　　　　　　　 　　　　　　　　　　　　 // 32

6.3 取整函数(大)：ceiling()

select CEILING(1.23); 　　　　　 　　 　　　　　　　　　　  // 2

select CEILING(-1.23);　　　　　 　　　　　　　　　　　　   // -1

6.4 取整函数(小)：floor()

select FLOOR(1.23); 　　　　　　　　  　　　　　　　　　　 // 1

select FLOOR(-1.23);　　　　　　   　   　　　　　　　　　　// -2

6.5 四舍五入：round()

select ROUND(1.58); 　　　　　　　　 　　　　　　　　　　 // 2

select ROUND(-1.58);　　　　　　　　 　　　　　　　　　　// -2

select ROUND(12.335343,2);　　　　　　　　　　　　　　   //12.34

6.6 字符串拼接

select CONCAT('My', 'aaa', 'QL');     　　 　　　　　　　　　   // MyaaaQL

6.7 字符串长度

select LENGTH('text'); 　　　　　　　　  　　　　　　　　　　// 4

6.8 取字符串前几个字符

select LEFT('foobarbar', 5); 　　　　　　　　　　　　　　　　 // fooba

6.8 字符串第几个位置之后字符

select SUBSTRING('Quadratically',5);   　　　　　　　　　　　// ratically

七、字符函数

7.1 返回字符串左侧x个字符

SELECT LEFT('abc',2);　　　　　　　　　　　　　　　　　　  // ab

7.2 返回字符串右侧x个字符

SELECT RIGHT('abc',2);　　　　　　　　　　　　　　　　　    // bc

7.3 截取字符串   x位子开始y个字符

SELECT SUBSTRING('abcd',3,2);　　　　　　　　　　　　　　 // cd

7.4 替换字符串  将字符串x位置开始y个字符替换成form字符

SELECT INSERT('abcd',3,2,'FF');　　　　　　　　　　　　　　  // abFF

7.5 替换字符串  将字符串中包含form的字符替换成target

SELECT REPLACE('abc','a','A');　　　　　　　　　　　　　　    // Abc

7.6 根据特定字符串截取

select FullName,SUBSTRING_INDEX(FullName,'.',1) from i_deptmanage;　   // abc.def       -> abc

7.7 截取最后一个特定字符之后的字符串

select SUBSTRING_INDEX('/AAA/BBB/CCC/1.JPG','/',-1);      //1.JPG

7.8 返回特定字符之前字符串

select substr('aaa#01',1,POSITION('#' in 'aaa#01')-1) ;     //aaa

7.9 字符串转数字

select convert('67',SIGNED);      //转数字67

7.10 数字转字符串

select convert(67,CHAR);      //转字符串‘67’