mysql查詢的數據換行：mysql多行合併成一行數據

建庫建表

drop database if exists jbxx;
create database jbxx;
use jbxx;
create table grjbxx(
ryid int auto_increment not null primary key,
sfzh varchar(50) not null,
hbh varchar(50) not null,
xm varchar(50) not null,
xb varchar(4) not null,
mz varchar(50) not null,
csrq date not null,
hj varchar(50) not null,
zz varchar(50) not null,
lxdh varchar(20) not null,
gmt_create datetime not null default current_timestamp
);
insert into grjbxx(sfzh,hbh,xm,xb,mz,csrq,hj,zz,lxdh) values('37032219000101001x','3703220105','張三','男','漢族','1900-02-05','山東高青','高青縣*小區*號樓*單元*戶號','1390000000');
select * from grjbxx;

查詢xm的分組統計情況

SELECT xm,count(*) from grjbxx group by xm；

只顯示xm重複行的統計情況

SELECT xm,count(*) from grjbxx group by xm having count(*)>1;

顯示xm重複的所有記錄信息

SELECT * from grjbxx where xm in(SELECT xm from grjbxx group by xm having count(xm)>1);

顯示sfzh重複的所有記錄信息

SELECT * from grjbxx where sfzh in(SELECT sfzh from grjbxx group by sfzh having count(sfzh)>1);

刪除表中多餘的sfzh重複記錄，重複記錄是根據單個欄位（sfzh）來判斷，只留有ryid最大的記錄

delete from grjbxx
where sfzh in(SELECT sfzh from grjbxx group by sfzh having count(sfzh)>1)
and ryid not in(select max(ryid) from grjbxx group by sfzh having count(sfzh)>1);

資料庫修改

use mytest;　　　　　　　　--打開mytest資料庫
SELECT * from stu;　　　　--查詢STU表全部記錄
ALTER TABLE stu MODIFY gmt_modified datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP; --修改gmt_modified欄位非空，更新時自動更新
ALTER TABLE stu MODIFY gmt_modified datetime NOT NULL;　　　　　　　　　　　　　　　　　　--修改gmt_modified欄位非空
ALTER TABLE stu change gmt_create gmt_create datetime NOT NULL DEFAULT CURRENT_TIMESTAMP; --修改gmt_create是datetime類型，非空，默認值自動時間
desc stu;　　　　　　　　--顯示stu表結構
UPDATE stu set gmt_modified='2018-08-01 01:59:01'; 　　--更新所有的gmt_modified的值
truncate stu;　　　　--清空stu表，僅保留數據結構

合併多行數據到一行，hbh相同的合併到一行

SELECT hbh,count(xm) as 人數 ,GROUP_CONCAT(xm) as 成員 from grjbxx GROUP BY hbh;

查詢前5條記錄

select * from grjbxx LIMIT 5;

統計多個數據，利用虛擬表dual顯示

SELECT
(SELECT count(DISTINCT sfzh) from grjbxx) 總人數, 
(SELECT count(DISTINCT sfzh) from sb where sblb='01') 社保 
from DUAL;

查詢date欄位日期範圍內的記錄，表示 1980-01-01 00:00:00 –1999-12-31 00:00:00，兩個邊界都包括，這段時間範圍的記錄，

select * from grjbxx WHERE csrq BETWEEN ‘1980-01-01’ and ‘1999-12-31’;

查詢一段時間內的記錄

select * from grjbxx WHERE gmt_create >=’2019-01-23 00:00:00′ and gmt_create<= ‘2019-01-23 14:53:06’;

日期轉換

SELECT DATE_FORMAT(20140614162458,'%Y-%m-%d %H:%i:%s');
SELECT DATE_FORMAT('2014-02-24','%Y-%m-%d %H:%i:%s');
SELECT DATE_FORMAT('2014-02-24','%Y-%m-%d')
SELECT str_to_date('2014-02-24','%Y-%m-%d')
SELECT DATE_FORMAT(gmt_create,'%Y-%m-%d') from grjbxx;
ORCAL 日期轉換
select to_date('2004-05-07 13:23:44','yyyy-mm-dd hh24:mi:ss') from dual

使用substr函數查詢，SUBSTR(str,pos,len)表示：從pos開始的位置，截取len個字元(空白也算字元)。

SELECT * from grjbxx where substr(sfzh,1,6)=’370322′

REPLACE(str,oldstring,newstring)替換函數有三個參數，它將string中的oldstring替換為newstring字元串。

UPDATE grjbxx SET hbh=REPLACE(hbh,’2′,’a’)

刪除自增長主鍵，從1開始

1、取消自增長

ALTER TABLE grjbxx MODIFY ryid int;

2、取消主鍵

ALTER TABLE grjbxx DROP PRIMARY key;

3、更新所有id為0

UPDATE grjbxx set ryid=0;

4、設置主鍵自增長