SQL语句总结

说明,本文章以数据库名为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';