数据库@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
-- 看出结果
id letter number constant other
1 a 1 甲 金
2 b 2 乙 木
3 c 3 丙 水
8 d 4 丁 水
9 e 1 丙 火
;
-- 是用变量的方式,可以写出语句
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