1.where name = 'zhangsan' or name = '李四' and price >= 10
有优先级的 and优先级大于or
正解:where (name = 'zhangsan' or name = '李四') and price >= 10
2.order name desc,price asc
多条件排序
3.like模糊查询区分大小写 like BINARY 'Z%'
[]多个
_单个字符
[^]不包含多个
4. not between and
not
5. LPAD(day(CURRENT_DATE()),4,0) 自动补零
date_format(CURRENT_DATE(),'%Y-%m')
select AVG(DISTINCT id) from t_user 去掉重复值然后取平均
6.
select id, `status`, count(*) as number from t_user
where id >= 2
GROUP BY id
having number >=1
order by status desc
7.union all包括重复项 union去掉重复项
8.insert into user_(username_) (select username_ from user_)
insert into user_(username_) values(),()
9.复合主键(两个字段必须有一个不同)
联合主键(两个字段拼接起来不同)
10. select ROUND(2.509,2)
11. select FIND_IN_SET('3','1,2,6,8')
12.存储函数
BEGIN
DECLARE sTemp VARCHAR(400);
DECLARE sTempChd VARCHAR(400);
SET sTempChd = '';
SET sTemp = cast(areaId as char);
WHILE sTempChd is not NULL DO
SET sTemp = CONCAT(sTemp,',',sTempChd);
SELECT group_concat(child_id) INTO sTempChd FROM group_c where FIND_IN_SET(parent_id,sTemp)>0;
END WHILE;
return sTemp;
END
有优先级的 and优先级大于or
正解:where (name = 'zhangsan' or name = '李四') and price >= 10
2.order name desc,price asc
多条件排序
3.like模糊查询区分大小写 like BINARY 'Z%'
[]多个
_单个字符
[^]不包含多个
4. not between and
not
5. LPAD(day(CURRENT_DATE()),4,0) 自动补零
date_format(CURRENT_DATE(),'%Y-%m')
select AVG(DISTINCT id) from t_user 去掉重复值然后取平均
6.
select id, `status`, count(*) as number from t_user
where id >= 2
GROUP BY id
having number >=1
order by status desc
7.union all包括重复项 union去掉重复项
8.insert into user_(username_) (select username_ from user_)
insert into user_(username_) values(),()
9.复合主键(两个字段必须有一个不同)
联合主键(两个字段拼接起来不同)
10. select ROUND(2.509,2)
11. select FIND_IN_SET('3','1,2,6,8')
12.存储函数
BEGIN
DECLARE sTemp VARCHAR(400);
DECLARE sTempChd VARCHAR(400);
SET sTempChd = '';
SET sTemp = cast(areaId as char);
WHILE sTempChd is not NULL DO
SET sTemp = CONCAT(sTemp,',',sTempChd);
SELECT group_concat(child_id) INTO sTempChd FROM group_c where FIND_IN_SET(parent_id,sTemp)>0;
END WHILE;
return sTemp;
END