MySQL学习记录（2）

模糊查询like
条件中用like，不用等号或者大于小于号来指定特定的字段值。
使用%作为通配符，不是*

select * from mytable_tbl
where content like '%is%';

使用or、and来添加多个逻辑条件

select * from mytable_tbl
where content like 'He%' or Age_2019>30;

以下这种方式，or后边的条件无法起作用，需要写完整

这种方式才可以：

Order by 排序

select * from mytable_tbl order by Age_2019 asc;

Group by 分组
原始数据

set names utf8;
set foreign_key_checks=0;
-- -------------------------------------------
-- table structure for 'employee_tbl'
-- -------------------------------------------
drop table if exists employee_tbl;
create table employee_tbl
(id int not null primary key,
name varchar(10) not null default '',
date datetime not null,
singin tinyint(4) NOT NULL DEFAULT '0' comment '登录次数'
) Engine=InnoDB Default Charset=utf8;
Begin;
Insert into employee_tbl
(id, name, date, singin)
values
('1', '小明', '2016-04-22 15:25:33', '1'),
('2', '小王', '2016-04-20 15:25:47', '3'),
('3', '小丽', '2016-04-19 15:26:02', '2'), 
('4', '小王', '2016-04-07 15:26:14', '4'), 
('5', '小明', '2016-04-11 15:26:40', '4'), 
('6', '小明', '2016-04-04 15:26:54', '2');
commit;
set foreign_key_checks=1;

分组

select name, count(*) as '次数'from employee_tbl
group by name;

with rollup
with rollup可以实现汇总，最后一行, coalesce(‘a’, ‘b’, ‘c’), 如果a不为null，则用a，否则则用b

select coalesce(name, '总数') as 'name' , sum(singin) as '登录次数' from employee_tbl
group by name
with rollup;