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’

 

最大连接数和超时设置

show variables like "max_connections";       -- 查看当前连接数
set global max_connections = 1000;            --设置最大连接数1000
show global VARIABLES like 'wait_timeout';   --查看超时
set global interactive_timeout=1000;              --持续多久关闭
set global wait_timeout=1000;                 --超时时间

 

查看数据库连接

SHOW STATUS LIKE 'Threads%';
select * from information_schema.processlist ORDER BY DB;

 

新增用户和权限

CREATE USER 'root2'@'%' IDENTIFIED BY 'root2';

GRANT ALL PRIVILEGES ON database_name.* TO 'root2'@'%';

grant all privileges on *.* to 'lisi'@'%';

FLUSH PRIVILEGES;

 

 

starrocks数据库

CREATE USER ods@'%' IDENTIFIED BY 'ods@123';     -- 创建用户

GRANT SELECT ON *.* TO 'lizhichun'@'%'; --查询权限
GRANT ALL ON *.* TO 'ods'@'%' ;           -- 赋权限
SET PROPERTY FOR 'ods' 'max_user_connections' = '1000';    -- 设置最大连接数
SHOW PROPERTY FOR 'ods';

SHOW PROC '/frontends'    --查看集群