--建表
create table if not exists `runoob_tbl`(
`runoob_id` int unsigned auto_increment,
`runoob_title` varchar(100) not null,
`runoob_author` varchar(40) not null,
`submission_date` date,
primary key ( `runoob_id` )
)engine=innodb default charset=utf8;
--插入数据
insert into runoob_tbl (runoob_title, runoob_author, submission_date) values ("学习 php", "菜鸟教程", now());
insert into runoob_tbl (runoob_title, runoob_author, submission_date) values ("学习 mysql", "菜鸟教程", now());
insert into runoob_tbl (runoob_title, runoob_author, submission_date) values ("java 教程", "runoob.com", '2016-05-06');
insert into runoob_tbl (runoob_title, runoob_author, submission_date) values ("学习 python", "runoob.com", '2016-05-06');
--查询全部
select * from runoob_tbl
--条件查询where
select * from runoob_tbl where runoob_author = "菜鸟教程"
--多重条件查询
select * from runoob_tbl where runoob_author = "菜鸟教程" and runoob_id = 1
--更新数据库字段
update runoob_tbl set runoob_title="学习 c++" where runoob_id = 3
--更新数据库表部分字段
update runoob_tbl set runoob_title=replace(runoob_title,'c++','python') where runoob_id = 3
--删除数据 delete from 表名称 where 删除条件;
delete from runoob_tbl where runoob_id = 3
--mysql like 子句
select* from runoob_tbl where runoob_author like('%com')
like 匹配/模糊匹配,会与 % 和 _ 结合使用。
'%a' //以a结尾的数据 'a%' //以a开头的数据 '%a%' //含有a的数据 '_a_' //三位且中间字母是a的 '_a' //两位且结尾字母是a的 'a_' //两位且开头字母是a的
查询以 java 字段开头的信息。
select * from position where name like 'java%';
查询包含 java 字段的信息。
select * from position where name like '%java%';
查询以 java 字段结尾的信息。
select * from position where name like '%java';
在 where like 的条件查询中,sql 提供了四种匹配方式。
--mysql 排序
--在命令提示符中使用 order by 子句
select * from runoob_tbl order by submission_date asc
select * from runoob_tbl order by submission_date desc
--mysql group by 语句
--创建新表
drop table if exists `employee_tbl`;
create table `employee_tbl` (
`id` int(11) not null,
`name` char(10) not null default '',
`date` datetime not null,
`singin` tinyint(4) not null default '0' comment '登录次数',
primary key (`id`)
) engine=innodb default charset=utf8;
--插入数据
insert into `employee_tbl` 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');
--group by 语法
--接下来我们使用 group by 语句 将数据表按名字进行分组,并统计每个人有多少条记录:
select name, count(*) from employee_tbl group by name
--使用 with rollup
--with rollup 可以实现在分组统计数据基础上再进行相同的统计(sum,avg,count…)。
select name, sum(singin) as singin_count from employee_tbl group by name with rollup;
select coalesce(name, '总数'), sum(singin) as singin_count from employee_tbl group by name with rollup;
如对本文有疑问, 点击进行留言回复!!
一步步教你用Prometheus搭建实时监控系统系列(一)——上帝之火,普罗米修斯的崛起
网友评论