sql语句
1:去重（把seq字段去重）
select distinct(seq) from 表名
2:分页：查询的是 start条往后的limit条数据，不包含start条
select * from user where removetime is null limit #{start}, #{limit}
start：开始条数
limit：每页的条数
limit (pageNo-1)*pageSize , pageSize
3：查询书库里的所有的表名
show tables
4：查询某张表的表结构
DESC user 例 ： DESC ${tablename} tablename是表的名称
5：查询某张表的主键，自增到几了，返回的值是下一条数据的主键值
select AUTO_INCREMENT from INFORMATION_SCHEMA.TABLES where TABLE_NAME='day_business'
6: MYSQL 大于号，小于号
< < 小于号
> > 大于号
& & 和
' ’ 单引号
" " 双引号
sum(case when time > '1937-07-06' and time <= '1945-09-02' then 1 else 0 end ) as time,
7: 把一个表的字段更新到另一个表的字段中
例子 : 将B表的filde filde1 两个字段值跟新到A表中 根据A和B表的 条件 a.pid =b.id
Update A a ,B b
set a.filde=b.filde
, a.filde1=b.filde1
where a.pid =b.id
8:添加唯一索引
alter table User add unique key no_account(NAME,USERNAME);
9:去重 获取表 某个字段最大值的一列
CREATE TABLE a (
`id` varchar(64) NOT NULL COMMENT '',
`xm` varchar(64) NOT NULL COMMENT '姓名',
`xmz` varchar(64) NOT NULL COMMENT '项目组',
`nl` varchar(64) NOT NULL COMMENT '年龄',
`xmzlist` varchar(64) NOT NULL COMMENT '以前呆过的项目组'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='';
INSERT INTO `a` VALUES ('1', '张三', '1', '20', '1,3,4');
INSERT INTO `a` VALUES ('2', '张三', '3', '20', '1,3,4');
INSERT INTO `a` VALUES ('3', '张三', '4', '20', '1,3,4');
INSERT INTO `a` VALUES ('4', '李四', '3', '20', '3');
INSERT INTO `a` VALUES ('5', '王五', '2', '20', '2,6');
INSERT INTO `a` VALUES ('6', '王五', '6', '20', '2,6');
INSERT INTO `a` VALUES ('7', '冰河', '7', '20', '7,3');
INSERT INTO `a` VALUES ('8', '冰河', '3', '20', '7,3');
select * from a j where not exists (select 1 from a j1 where j.xm=j1.xm and j.xmz<j1.xmz)
select t1.* from (
select * from a where 1=1 order by xm DESC, xmz desc limit 99999999
) t1 group by xm
10: MySQL help_topic行转列 表里字段 根据某个字段分割 转成多行 用上面的表
SELECT
substring_index( substring_index( a.xmzlist, ',', b.help_topic_id + 1 ), ',',- 1 ) AS xmzlist
FROM
a a
JOIN mysql.help_topic b
ON b.help_topic_id < (length( a.xmzlist ) - length( REPLACE ( a.xmzlist, ',', '' ) ) + 1 )
select xmz,count(id)zs,
sum(case when xmz="1" or xmz like "%1%" then 1 else 0 end ) as '1组数量',
sum(case when xmz="3" or xmz like "%1%" then 1 else 0 end ) as '3组数量',
sum(case when xmz="4" or xmz like "%1%" then 1 else 0 end ) as '4组数量',
sum(case when xmz="7" or xmz like "%1%" then 1 else 0 end ) as '7组数量'
from a group by xmz
11: MySQL 查询某张表的表结构
SELECT
"User" 表英文名,
"用户表" 表中文名,
COLUMN_NAME 字段英文名,
COLUMN_COMMENT 字段中文名,
COLUMN_TYPE 字段类型,
"" 字段要求格式,
"" 字典值说明,
"" 主外键关系,
CHARACTER_MAXIMUM_LENGTH 长度,
IS_NULLABLE 是否为空,
COLUMN_DEFAULT 默认值,
COLUMN_COMMENT 备注
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE table_schema = 'kkkk' AND table_name = 'User' -- 改成自己的数据库 和 表名称
