说明,本文章以数据库名为dbname为例,表名tabname为例,列名，id,username,password create_time(创建时间)
查询某个表今天的数据
select * from tabname where to_days(create_time) = to_days(now());
查询某个表24小时内的数据（不能认为是昨天的数据，不准确）
select * from pcm_tf_stock where to_days(now()) - to_days(create_time) <= 1
查询某个表本周的数据
SELECT * FROM tabname WHERE YEARWEEK( date_format( create_time,'%Y-%m-%d' ) ) = YEARWEEK( now() ) ;
查询某个表本月的数据
SELECT * FROM tabname WHERE DATE_FORMAT( create_time, '%Y%m' ) = DATE_FORMAT( CURDATE( ) ,'%Y%m' )
查询某个表上一月的数据
SELECT * FROM tabname WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( create_time, '%Y%m' ) ) =1
查询某个表今年的数据
SELECT * FROM tabname WHERE YEAR( create_time ) = YEAR(NOW())
查询某个表去年的数据
select * from tabname where year(create_time)=year(date_sub(now(),interval 1 year));
查询某个表昨天的数据
SELECT * FROM pcm_tf_stock WHERE TO_DAYS( NOW( ) ) - TO_DAYS( create_time) = 1
查询某个表本季度的数据
select * from tabname where QUARTER(create_time)=QUARTER(now());
查询某个表上个季度的数据
select * from pcm_tf_stock where QUARTER(create_time)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));
查询距离当前现在6个月的数据
select * from pcm_tf_stock where create_time between date_sub(now(),interval 6 month) and now();
#查询日期最近的十条数据
SELECT * from tabname ORDER BY create_time desc LIMIT 10
#查询日期最近的第十条数据到第二十条数据
SELECT * from tabname ORDER BY create_time desc LIMIT 10,20
#查询日期最后十条数据
SELECT * FROM tabname order by pk_id DESC limit 10;
#查询日期随机十条数据
select * from tabname order by rand() limit 10;
#查询同一时间段但不同日期的数据
select * from tabname where create_time>='2017-10-22' and create_time<='2017-10-27' and DATE_FORMAT(create_time, '%T' )>='08:00:00' and DATE_FORMAT(create_time, '%T' )<='24:00:00';
#查询非空数据
SELECT * FROM tabname WHERE birthday <>''
#查询空数据
select * from tabname where birthday is null;
#查询结果不显示重复记录
SELECT DISTINCT creator FROM tabname
#查询该表有多少条记录
SELECT COUNT(*) FROM tabname
#求和
select sum(field1) as sumvalue from tabname
#平均：
select avg(field1) as avgvalue from tabname
#最大
select max(field1) as maxvalue from tabname
#最小
select min(field1) as minvalue from tabname
#模糊查询
select name as "姓名" from tabname where name like 'zhou%';
#查询某个字段在某个列表的数据
select * from crm_tf_user where code in (10000030,10000022);
#查询某个字段在某个区间的数据
select * from tabname where score between 80 and 100;
#查询某个字段（username）重复的数据
#where字句在聚合前先筛选记录
#having子句在聚合后对组记录进行筛选
select *,count(*) as ct from tabname GROUP BY username HAVING ct > 1
#查询某一个表的字段和数据类型 --只改tabname就行
select column_name,data_type from information_schema.columns
where table_name = 'tabname '
#增加一个列
Alter table tabname add column addColumn type
#去掉列test
alter table tabname drop column test;
#修改列username默认值
alter table tabname alter username set default 'wz';
#去掉username默认值
alter table tabname alter username drop default;
#修改表中某个字段的名字
alter table tabname change username name VARCHAR(200);
#使用旧表创建新表
create table user_new like user_old
#修改表名
alter table user rename user_new;
#删除如果存在的表
drop table if exists student;
#删除主键--其中主键不能为自增。自增删除不了
ALTER TABLE user_old DROP PRIMARY KEY
#添加主键--主键字段里的值不能重复
ALTER TABLE user_old ADD PRIMARY KEY(`username`);
#给表的某个字段创建唯一索引
create unique index mobileIndex on crm_tf_user(mobile);
#删除索引
drop index mobileIndex on crm_tf_user
1.基本sql语法：
创建数据库
--dbname 数据库名
create database dbname;
显示创建数据库的语句 show create database dbname;
删除数据库
drop database dbname;
使用数据库
use dbname;
说明：对数据库进行增删改查之前需先指明使用使用哪个数据库
创建表tabname,并设id为主键
create table tabname(
id int primary key,
username varchar(20) not null,
studentno varchar(20) not null,
password varchar(20) not null
);
外键情况：
create table tabname2(
id int primary key,
create table tabname2(
id int primary key,
studentid int,
course varchar(20) not null,
score int,
foreign key(studentid) references tabname(id)
);
显示所有数据库 :
show databases;
显示某个表创建时的全部信息 :
show create table tabname2;
显示当前数据库中所有表的名称。 show tables或show tables from tabname2;
插入数据（增）
insert into tabname(id,username,studentno,password) values(1,'wz','10001','123');
查询所有数据（查）
select * from tabname;
限制查询范围：
select * from tabname where username = 'wz';
更新数据（改）
update tabname set password = 123456 where id = 1;
删除数据（删）
delete from tabname where username ='wz';
增加列名：
--sex列名，varchar(20)数据类型
alter table tabname add sex varchar(20);
2.其他sql语句
显示mysql字符编码：
show variables like 'character%';
--聚集函数，as后的字段名为自定义查询列名
查询表中的记录数：select count(*) as totalcount from tabname2;
查询某人的总分：select sum(score) as sumvalue from tabname2 username ='wz';
查询某人的平均分：select avg(score) as avgvalue from tabname2 username ='wz';
查询某人最高分：select max(score) as maxvalue from tabname2 username ='wz';
查询某人的最低分：select min(score) as minvalue from tabname2 where username ='wz';
