MySQL一些函数

MySQL一些内容

1.对于表的查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
-- 查询表信息
SELECT
  table_name tableName,
  ENGINE,
  table_comment tableComment,
  create_time createTime 
FROM
  information_schema.TABLES 
WHERE
  table_schema = ( SELECT DATABASE ( ) ) 
  AND table_name = 'bm_user';
  
-- 查询列信息
SELECT
  column_name columnName,
  data_type dataType,
  column_comment columnComment,
  column_key columnKey,
  extra 
FROM
  information_schema.COLUMNS 
WHERE
  table_name = 'bm_user' 
  AND table_schema = ( SELECT DATABASE ( ) ) 
ORDER BY
  ordinal_position;
  
  
-- 查询这个库下边所有的表信息
SELECT
  table_name tableName,
  ENGINE,
  table_comment tableComment,
  create_time createTime 
FROM
  information_schema.TABLES 
WHERE
  table_schema = ( SELECT DATABASE ( ) );

获取日期

1
2
3
4
5
6
7
SELECT 
NOW() "当前时间",
CURDATE() "当天日期",
CURTIME() "当前时间",
DATE_SUB(CURDATE(),INTERVAL -1 DAY) "后一天日期",
DATE_SUB(CURDATE(),INTERVAL 1 DAY) "前一天日期"

MySQL表里没有id，想要一个顺序排列的id

1
SELECT TABLENAME as tablename , TAGNAME AS tagName, @l:=@l+1 AS id FROM modelconfig, (SELECT @l:=-1) AS temp WHERE TABLENAME = 'DT_LIMSOut';