建库建表
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、设置主键自增长
ALTER TABLE grjbxx CHANGE ryid ryid int not NULL PRIMARY KEY auto_increment;
显示百分号%
select concat(round(fs/100*100,2),’%’) from sb;
计算>=60记录数占比
select round(sum(case when fs>=60 then 1 else 0 end)/count(fs),2) from sb;
