检索数据

深碍√TFBOYSˉ_ 2022-05-11 08:16 278阅读 0赞

# 检索数据 #

建表,插入数据：

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子句操作符：

<table> 
 <thead> 
  <tr> 
   <th>运算符</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>=</td> 
   <td>等于</td> 
  </tr> 
  <tr> 
   <td>!=</td> 
   <td>不等于，某些数据库系统也写作 &lt;&gt;</td> 
  </tr> 
  <tr> 
   <td>&gt;</td> 
   <td>大于</td> 
  </tr> 
  <tr> 
   <td>&lt;</td> 
   <td>小于</td> 
  </tr> 
  <tr> 
   <td>&gt;=</td> 
   <td>大于或等于</td> 
  </tr> 
  <tr> 
   <td>&lt;=</td> 
   <td>小于或等于</td> 
  </tr> 
  <tr> 
   <td>BETWEEN … AND …</td> 
   <td>介于某个范围之内，例：WHERE age BETWEEN 20 AND 30</td> 
  </tr> 
  <tr> 
   <td>NOT BETWEEN …AND …</td> 
   <td>不在某个范围之内</td> 
  </tr> 
  <tr> 
   <td>IN(项1,项2,…)</td> 
   <td>在指定项内，例：WHERE city IN(‘beijing’,‘shanghai’)</td> 
  </tr> 
  <tr> 
   <td>NOT IN(项1,项2,…)</td> 
   <td>不在指定项内</td> 
  </tr> 
  <tr> 
   <td>LIKE</td> 
   <td>搜索匹配，常与模式匹配符配合使用</td> 
  </tr> 
  <tr> 
   <td>NOT LIKE</td> 
   <td>LIKE的反义</td> 
  </tr> 
  <tr> 
   <td>IS NULL</td> 
   <td>空值判断符</td> 
  </tr> 
  <tr> 
   <td>IS NOT NULL</td> 
   <td>非空判断符</td> 
  </tr> 
  <tr> 
   <td>NOT、AND、OR</td> 
   <td>逻辑运算符，分别表示否、并且、或，用于多个逻辑连接。优先级：NOT &gt; AND &gt; OR</td> 
  </tr> 
  <tr> 
   <td>%</td> 
   <td>模式匹配符，表示任意字串，例：WHERE username LIKE ‘%user’</td> 
  </tr> 
 </tbody> 
</table>

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