检索数据
建表,插入数据:
CREATE TABLE `sys_log` (
`id` int(64) NOT NULL COMMENT '编号',
`type` char(1) DEFAULT '1' COMMENT '日志类型',
`title` varchar(255) DEFAULT '' COMMENT '日志标题',
`create_by` varchar(64) DEFAULT NULL COMMENT '创建者',
`create_date` datetime DEFAULT NULL COMMENT '创建时间',
`remote_addr` varchar(255) DEFAULT NULL COMMENT '操作IP地址',
`user_agent` varchar(255) DEFAULT NULL COMMENT '用户代理',
`request_uri` varchar(255) DEFAULT NULL COMMENT '请求URI',
`method` varchar(5) DEFAULT NULL COMMENT '操作方式',
`params` text COMMENT '操作提交的数据',
`exception` text COMMENT '异常信息',
PRIMARY KEY (`id`),
KEY `sys_log_create_by` (`create_by`) USING BTREE,
KEY `sys_log_request_uri` (`request_uri`) USING BTREE,
KEY `sys_log_type` (`type`) USING BTREE,
KEY `sys_log_create_date` (`create_date`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='日志表';
INSERT INTO `test`.`sys_log` (`id`, `type`, `title`, `create_by`, `create_date`, `remote_addr`, `user_agent`, `request_uri`, `method`, `params`, `exception`) VALUES ('1', '1', '我的面板-个人信息-个人信息', '1', '2017-10-18 10:21:51', '0:0:0:0:0:0:0:1', 'Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/58.0.3029.96 Safari/537.36', '/a/sys/user/info', 'GET', 'tabPageId=jerichotabiframe_0', '');
INSERT INTO `test`.`sys_log` (`id`, `type`, `title`, `create_by`, `create_date`, `remote_addr`, `user_agent`, `request_uri`, `method`, `params`, `exception`) VALUES ('2', '1', '文章管理-文章管理-公共留言-查看', '1', '2018-02-01 10:16:35', '172.18.254.14', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:44.0) Gecko/20100101 Firefox/44.0', '/a/cms/guestbook/', 'POST', 'pageNo=1&pageSize=30&type=2&content=a&delFlag=(SELECT (CASE WHEN (9233=4048) THEN 9233 ELSE 9233*(SELECT 9233 FROM INFORMATION_SCHEMA.PLUGINS) ...', '');
INSERT INTO `test`.`sys_log` (`id`, `type`, `title`, `create_by`, `create_date`, `remote_addr`, `user_agent`, `request_uri`, `method`, `params`, `exception`) VALUES ('3', '1', '文章管理-文章管理-文章发布-文章模型-修改', '1', '2018-02-01 10:21:27', '172.18.254.14', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:44.0) Gecko/20100101 Firefox/44.0', '/a/cms/article/draftsSave', 'POST', 'id=17f74834a1f04d54a11fe7fc6377c2a2&category.id=daikuanmaiche&category.name=贷款买车&title=Mr.&weight=0&weightDate=0006-05-21&description=1&image=/userfiles/1/images/photo/2018/01/1.jpg&articleData.content=01/01/1967&articleData.copyfrom=01/01/1967&createDate=0006-05-21 00:00:00&delFlag=2\' UNION ALL SELECT NULL,NULL,NULL,NULL-- fVOA', '');
INSERT INTO `test`.`sys_log` (`id`, `type`, `title`, `create_by`, `create_date`, `remote_addr`, `user_agent`, `request_uri`, `method`, `params`, `exception`) VALUES ('4', '0', '文章管理-文章管理-文章发布-文章模型-修改', '1', '2018-02-01 10:06:28', '172.18.254.14', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:44.0) Gecko/20100101 Firefox/44.0', '/a/cms/article/save', 'POST', 'image=/userfiles/1/images/photo/2018/01/1.jpg&articleData.content=alert(123)&keywords=&category.name=文化&weight=0&description=&source=aa AND 5582=7941-- fdkm&category.id=1c1fc0f69b76404db913b7de7d086d09&title=\"onclick=alert(1)//&delFlag=0&articleData.relation=&hits=0&isShare=0&id=a4c138f660f644a5b555249b15b23923&weightDate=&articleData.copyfrom=&categoryid=1c1fc0f69b76404db913b7de7d086d09&createDate=2018-02-01 08:27:27', '');
INSERT INTO `test`.`sys_log` (`id`, `type`, `title`, `create_by`, `create_date`, `remote_addr`, `user_agent`, `request_uri`, `method`, `params`, `exception`) VALUES ('5', '0', '在线办公-个人办公-请假办理-查看', '1', '2016-12-07 17:58:13', '172.18.254.14', 'Mozilla/5.0 (Windows NT 6.1; WOW64; rv:50.0) Gecko/20100101 Firefox/50.0', '/a/oa/leave/list', 'POST', 'pageNo=1&pageSize=30&ids=-2614%\' UNION ALL SELECT 2598,2598,2598,2598,2598,2598,2598,2598,2598,2598,2598#&createDateStart=&createDateEnd=', '');
INSERT INTO `test`.`sys_log` (`id`, `type`, `title`, `create_by`, `create_date`, `remote_addr`, `user_agent`, `request_uri`, `method`, `params`, `exception`) VALUES ('2', '1', '文章管理-文章管理-公共留言-查看', '1', '2018-02-01 10:16:35', '172.18.254.14', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:44.0) Gecko/20100101 Firefox/44.0', '/a/cms/guestbook/', 'POST', 'pageNo=1&pageSize=30&type=2&content=a&delFlag=(SELECT (CASE WHEN (9233=4048) THEN 9233 ELSE 9233*(SELECT 9233 FROM INFORMATION_SCHEMA.PLUGINS) ...', '');
INSERT INTO `test`.`sys_log` (`id`, `type`, `title`, `create_by`, `create_date`, `remote_addr`, `user_agent`, `request_uri`, `method`, `params`, `exception`) VALUES ('7', '0', '文章管理-文章管理-公共留言-审核', '1', '2018-02-01 10:57:00', '172.18.254.14', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:44.0) Gecko/20100101 Firefox/44.0', '/a/cms/guestbook/save', 'POST', 'id=7383fdd0f3624ffaa53c99986d5fa48a&delFlag=0&reContent=dddd', '');
INSERT INTO `test`.`sys_log` (`id`, `type`, `title`, `create_by`, `create_date`, `remote_addr`, `user_agent`, `request_uri`, `method`, `params`, `exception`) VALUES ('8', '2', '系统设置-机构用户-机构管理-查看', '1', '2017-04-18 10:10:55', '127.0.0.1', 'Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/45.0.2454.101 Safari/537.36', '/hyd/a/sys/office/list', 'GET', 'id=&parentIds=', '');
INSERT INTO `test`.`sys_log` (`id`, `type`, `title`, `create_by`, `create_date`, `remote_addr`, `user_agent`, `request_uri`, `method`, `params`, `exception`) VALUES ('9', '2', '我的面板-个人信息-个人信息', 'cc69cea4037843ed9ccf7f0302d46603', '2018-06-29 15:26:31', '172.18.254.63', 'Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/55.0.2883.87 Safari/537.36', '/a/sys/user/info', 'GET', 'tabPageId=jerichotabiframe_0', '');
INSERT INTO `test`.`sys_log` (`id`, `type`, `title`, `create_by`, `create_date`, `remote_addr`, `user_agent`, `request_uri`, `method`, `params`, `exception`) VALUES ('10', '2', '文章管理-文章管理', '1', '2018-03-07 16:40:12', '172.18.254.63', 'Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/64.0.3282.167 Safari/537.36', '/a/cms/tree', 'GET', '', '');
INSERT INTO `test`.`sys_log` (`id`, `type`, `title`, `create_by`, `create_date`, `remote_addr`, `user_agent`, `request_uri`, `method`, `params`, `exception`) VALUES ('11', '2', '我的面板-个人信息-个人信息', '1', '2017-08-31 18:17:13', '0:0:0:0:0:0:0:1', 'Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/60.0.3112.113 Safari/537.36', '/hyd/a/sys/user/info', 'GET', 'tabPageId=jerichotabiframe_0', '');
INSERT INTO `test`.`sys_log` (`id`, `type`, `title`, `create_by`, `create_date`, `remote_addr`, `user_agent`, `request_uri`, `method`, `params`, `exception`) VALUES ('12', '2', '文章管理-文章管理-文章发布-文章模型', '1', '2017-10-14 23:06:13', '172.18.254.63', 'Mozilla/5.0 (Windows NT 6.1; WOW64; rv:56.0) Gecko/20100101 Firefox/56.0', '/a/cms/article/', 'GET', 'category.id=1c1fc0f69b76404db913b7de7d086d09', '');
1.查询排序
mysql> select * from sys_log order by id;
2.多列排序
SELECT id,type,create_date from sys_log ORDER BY id,type;
只有多个行id相同时,然后对type进行排序,如果id都是唯一的,则不会按照后面的列排序
3.指定排序方向(默认升序ASC,降序是DESC)
mysql> SELECT id,type,create_date from sys_log ORDER BY id DESC,type;
+----+------+---------------------+
| id | type | create_date |
+----+------+---------------------+
| 12 | 2 | 2017-10-14 23:06:13 |
| 11 | 2 | 2017-08-31 18:17:13 |
| 10 | 2 | 2018-03-07 16:40:12 |
| 9 | 2 | 2018-06-29 15:26:31 |
| 8 | 2 | 2017-04-18 10:10:55 |
| 7 | 0 | 2018-02-01 10:57:00 |
| 5 | 0 | 2016-12-07 17:58:13 |
| 4 | 0 | 2018-02-01 10:06:28 |
| 3 | 1 | 2018-02-01 10:21:27 |
| 2 | 0 | 2016-12-05 18:19:13 |
| 2 | 1 | 2018-02-01 10:16:35 |
| 1 | 1 | 2017-10-18 10:21:51 |
+----+------+---------------------+
12 rows in set
mysql> SELECT id,type,create_date from sys_log ORDER BY id ,type DESC;
+----+------+---------------------+
| id | type | create_date |
+----+------+---------------------+
| 1 | 1 | 2017-10-18 10:21:51 |
| 2 | 1 | 2018-02-01 10:16:35 |
| 2 | 0 | 2016-12-05 18:19:13 |
| 3 | 1 | 2018-02-01 10:21:27 |
| 4 | 0 | 2018-02-01 10:06:28 |
| 5 | 0 | 2016-12-07 17:58:13 |
| 7 | 0 | 2018-02-01 10:57:00 |
| 8 | 2 | 2017-04-18 10:10:55 |
| 9 | 2 | 2018-06-29 15:26:31 |
| 10 | 2 | 2018-03-07 16:40:12 |
| 11 | 2 | 2017-08-31 18:17:13 |
| 12 | 2 | 2017-10-14 23:06:13 |
+----+------+---------------------+
12 rows in set
mysql> SELECT id,type,create_date from sys_log ORDER BY id DESC,type DESC;
+----+------+---------------------+
| id | type | create_date |
+----+------+---------------------+
| 12 | 2 | 2017-10-14 23:06:13 |
| 11 | 2 | 2017-08-31 18:17:13 |
| 10 | 2 | 2018-03-07 16:40:12 |
| 9 | 2 | 2018-06-29 15:26:31 |
| 8 | 2 | 2017-04-18 10:10:55 |
| 7 | 0 | 2018-02-01 10:57:00 |
| 5 | 0 | 2016-12-07 17:58:13 |
| 4 | 0 | 2018-02-01 10:06:28 |
| 3 | 1 | 2018-02-01 10:21:27 |
| 2 | 1 | 2018-02-01 10:16:35 |
| 2 | 0 | 2016-12-05 18:19:13 |
| 1 | 1 | 2017-10-18 10:21:51 |
+----+------+---------------------+
12 rows in set
DESC只对他前面的列有效,比如下面的语句, 所以先id降序,如果id相同,则以type降序。
SELECT id,type,create_date from sys_log ORDER BY id DESC,type DESC;
如果我们碰到A和a,那么怎么排序?这个和数据库的设置有关系,可以找DBA来配置。
4.使用order by和limit查询最值。
mysql> SELECT id,type,create_date from sys_log ORDER BY id DESC limit 1
;
+----+------+---------------------+
| id | type | create_date |
+----+------+---------------------+
| 12 | 2 | 2017-10-14 23:06:13 |
+----+------+---------------------+
1 row in set
mysql> SELECT id,type,create_date from sys_log ORDER BY id ASC limit 1;
+----+------+---------------------+
| id | type | create_date |
+----+------+---------------------+
| 1 | 1 | 2017-10-18 10:21:51 |
+----+------+---------------------+
1 row in set
5.过滤数据where
where子句操作符:
运算符 | 说明 |
---|---|
= | 等于 |
!= | 不等于,某些数据库系统也写作 <> |
> | 大于 |
< | 小于 |
>= | 大于或等于 |
<= | 小于或等于 |
BETWEEN … AND … | 介于某个范围之内,例:WHERE age BETWEEN 20 AND 30 |
NOT BETWEEN …AND … | 不在某个范围之内 |
IN(项1,项2,…) | 在指定项内,例:WHERE city IN(‘beijing’,‘shanghai’) |
NOT IN(项1,项2,…) | 不在指定项内 |
LIKE | 搜索匹配,常与模式匹配符配合使用 |
NOT LIKE | LIKE的反义 |
IS NULL | 空值判断符 |
IS NOT NULL | 非空判断符 |
NOT、AND、OR | 逻辑运算符,分别表示否、并且、或,用于多个逻辑连接。优先级:NOT > AND > OR |
% | 模式匹配符,表示任意字串,例:WHERE username LIKE ‘%user’ |
mysql在执行匹配时不区分大小写,例如:
mysql> SELECT id,method from test.sys_log WHERE method = 'get';
+----+--------+
| id | method |
+----+--------+
| 1 | GET |
| 8 | GET |
| 9 | GET |
| 10 | GET |
| 11 | GET |
| 12 | GET |
+----+--------+
6 rows in set
计算次序,先and后or,通常这种情况使用圆括号括起来
mysql> SELECT id,method from test.sys_log WHERE id > 3 or id <10 and method = 'get';
+----+--------+
| id | method |
+----+--------+
| 1 | GET |
| 4 | POST |
| 5 | POST |
| 7 | POST |
| 8 | GET |
| 9 | GET |
| 10 | GET |
| 11 | GET |
| 12 | GET |
+----+--------+
9 rows in set