mysql数据库动态查询_数据库@MySQL常用SQL查询

查询表结构语句

select

column_name 列名,

column_type 数据类型,

data_type 字段类型,

character_maximum_length 长度,

is_nullable 是否为空,

column_default 默认值,

column_comment 备注

from

information_schema.columns

where

table_schema = 'plcs_web' -- 数据库名称

and table_name = 'wsxd_case' -- 表名

结果：

实现一行变多行

实现多行变一行

根据自身状态更新

根据自身状态删除

触发器

存储过程基础

存储过程循环(传入字符串数组)

-- 建表语句

CREATE TABLE test (

id int(11) NOT NULL AUTO_INCREMENT,

letter varchar(255) DEFAULT NULL,

number varchar(255) DEFAULT NULL,

constant varchar(255) DEFAULT NULL,

other varchar(255) DEFAULT NULL,

PRIMARY KEY (id)

) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

-- 插入数据

insert into test

(letter, number, constant, other)

values

('a', '1', '甲', '金'),

('b', '2', '乙', '木'),

('c', '3', '丙', '水'),

('d', '4', '丁', '火'),

('a', '2', '甲', '土'),

('b', '3', '丙', '金'),

('c', '4', '乙', '木'),

('d', '4', '丁', '水'),

('e', '1', '丙', '火'),

('e', '3', '甲', '金');

select * from test;

-- 根据letter分组，再根据number倒序排序，取每个letter组的第2位

-- 查询语句

select *

from test a

order by a.letter, number desc

-- 看出结果

idletternumberconstantother

1a1甲金

2b2乙木

3c3丙水

8d4丁水

9e1丙火

;

-- 是用变量的方式，可以写出语句

select

a.id, a.letter, a.number, a.constant, a.other

from

(

select

if(@letter != a.letter, @rownum := 1, @rownum := @rownum + 1) as var_rownum,

@letter := a.letter as var_letter,

a.id,

a.letter,

a.number,

a.constant,

a.other

from test a

order by a.letter, a.number desc

) a

where a.var_rownum = 2;

select *

from test a

left join (

select

a.id, a.letter, a.number, a.constant, a.other

from

(

select

if(@letter != a.letter, @rownum := 1, @rownum := @rownum + 1) as var_rownum,

@letter := a.letter as var_letter,

a.id,

a.letter,

a.number,

a.constant,

a.other

from test a

order by a.letter, a.number desc

) a

where a.var_rownum = 2

) b

on a.id = b.id