create database 2.2;
use 2.2;
create table 用户明细
select * from 2.1.用户明细 limit 0;
drop table 2.2.用户明细;
这样子写,列名不好看
create table 用户明细
select 用户ID, 注册日期, 身份证号码, 性别, 出生日期, TIMESTAMPDIFF(YEAR, 出生日期, now())
from 2.1.用户明细;
drop table 2.2.用户明细;
使用AS重新定义列名
create table 用户明细
select 用户ID, 注册日期, 身份证号码, 性别, 出生日期, TIMESTAMPDIFF(YEAR, 出生日期, now()) as 年龄
from 2.1.用户明细;
truncate 用户明细;
insert into 用户明细 select * from 2.1.用户明细 where 性别='男';
truncate 用户明细;
insert into 用户明细 select * from 2.1.用户明细 where 性别='男' and 年龄>20 and 年龄<30;
insert into 用户明细 select * from 2.1.用户明细 where 性别='女' and TIMESTAMPDIFF(YEAR, 注册日期, now())>3;
truncate 用户明细;
insert into 用户明细
select * from 2.1.用户明细
where (性别='男' and 年龄>20 and 年龄<30)
or (性别='女' and TIMESTAMPDIFF(YEAR, 注册日期, now())>3);
select * from 2.1.用户明细
where 性别='男' and 年龄>20 and 年龄<30
order by TIMESTAMPDIFF(YEAR, 注册日期, now()) DESC
INTO OUTFILE 'D:\MySQL\2.2\man.txt'
CHARACTER SET 'UTF8'
COLUMNS TERMINATED BY ','
LINES TERMINATED BY '\n' ;
select * from 2.1.用户明细
where 性别='女' and TIMESTAMPDIFF(YEAR, 注册日期, now())>3
order by 年龄 DESC
INTO OUTFILE 'D:\MySQL\2.2\woman.txt'
CHARACTER SET 'UTF8'
COLUMNS TERMINATED BY ','
LINES TERMINATED BY '\n' ;
select * from 2.1.用户明细
where 性别='男' and 年龄>20 and 年龄<30
order by TIMESTAMPDIFF(YEAR, 注册日期, now()) DESC
limit 0, 4264
INTO OUTFILE 'D:\MySQL\2.2\man.txt'
CHARACTER SET 'UTF8'
COLUMNS TERMINATED BY ','
LINES TERMINATED BY '\n' ;
经常用来做找第n大之类的这种比较变态的查询
select distinct 年龄 from 2.1.用户明细 order by 年龄 desc limit 10, 1 into @第十一大年龄;
select @第十一大年龄;
select * from 2.1.用户明细 where 年龄=@第十一大年龄;
