1. 判断是否是空值 is not null 和 is null
select * from XXX where column_name is not null;
select * from XXX where column_1 is not null and column_2 is not null;
2. [Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'information_schema.PROFILING.SEQ' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
原因:由 sql_mode 引起的。
SHOW VARIABLES LIKE '%sql_mode%'; 或者 select version(), @@sql_mode; 都能看到sql_mode的value值是什么,可以见到它的值是:
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
也就是存在:ONLY_FULL_GROUP_BY
解决方法:
执行:SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
3. 通过查询结果,创建新表
create table table_new select * from table_old where column_1='金庸';
4. 随机取出10条数据: order by rand()
select * from table where condition order by rand() limit 10;
参考:
# 欢迎交流