1.把查询出的字段组合到一起:
表user,字段如下:
username 名字, usertype 职位
有记录 username="张三",usertype="经理"
现在想查出aaa="张三经理"
select concat(username,usertype) aaa from user ;
2.把查出的数据多出一个自定义字段
表user,字段如下:
username 名字, usertype 职位
有记录 username="张三",usertype="经理"
现在想查出 aaa="张三经理",sex="男"
select concat(username,usertype) aaa,'男' as sex from user
3.把查出来的数据放到一个新表里
表user,字段如下:
username 名字, usertype 职位
有记录 username="张三",usertype="经理"
现在想把"张三经理"和"男" 放到另外一张表 userother里
insert into useroher select concat(username,usertype) aaa,'男' as sex from user ;
4.清空表,而不是删除记录
TRUNCATE user;
5.查询数据库里有多少张表
SELECT COUNT(*) TABLES, table_schema FROM information_schema.TABLES WHERE table_schema = 'community2' GROUP BY table_schema;
6.查询数据库里某一天的数据
SELECT * FROM activity WHERE DATE_FORMAT(intercalate_starttime,'%Y-%m-%d')='2017-10-11'
activity是表名,intercalate_starttime是列名,这里是datetime类型的
7.查询部门以及部门的人数(对null值默认处理)
CREATE TABLE `project` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(30) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8
CREATE TABLE `user` (
`userid` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(10) DEFAULT NULL,
`id` int(11) DEFAULT NULL,
PRIMARY KEY (`userid`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8
SELECT a.name,IFNULL(b.counts,0) counts FROM project a LEFT JOIN (SELECT id,COUNT(*) counts FROM USER GROUP BY id) b ON a.id=b.id ;
8.获取sql里的当前时间
SYSDATE()
9.如果不存在就插入 dual是临时表
INSERT INTO user_role
SELECT 9,9,FALSE,NULL,NULL,NULL,NULL FROM DUAL
WHERE NOT EXISTS (SELECT * FROM user_role WHERE userid=9 AND roleid =9 AND is_delete =FALSE)
10 格式化时间
SELECT DATE_FORMAT(SYSDATE(),'%Y-%m') FROM DUAL;
11.按天
to_day(time_columen)
to_day(now())今天
12.if(ex,ex1,ex2)
前面是表达式.ex1,如果ture,走ex1,如果false走ex2
13.FIND_IN_SET
FIND_IN_SET(str,strlist)是判断在前个字符串是否在后个字符串以逗号分隔的里面
14.查询的某列结果集变成以逗号隔开的字符串,GROUP_CONCAT
SELECT GROUP_CONCAT(name) FROM aaa
15.根据查询进行修改
UPDATE `USER_ACCOUNT` cua,(SELECT `USER_ID`,`RETURN_MONEY` FROM USER_PRODUCT ) cup
SET cua.USDT_ACCOUNT = cua.USDT_ACCOUNT + cup.RETURN_MONEY
WHERE cua.USER_ID = cup.USER_ID
16根据查询进行插入
INSERT INTO USER_ACCOUNT_LOG
SELECT NULL,NOW(),NOW(),'N',0,t.USER_ID,t.USDT_ACCOUNT,t.RETURN_MONEY,t.RETURN_MONEY+t.USDT_ACCOUNT,"xxx","xxx1",NULL
FROM (SELECT ccup.`USER_ID`,ccup.`RETURN_MONEY`,ccua.USDT_ACCOUNT FROM USER_PRODUCT ccup INNER JOIN `USER_ACCOUNT` ccua ON ccup.USER_ID = ccua.USER_ID ) t