前言：转后端不久，重新将学校里学的东西捡起来，本文简单对常用sql作一下整理。开发还在继续，本文也会持续更新。。。
1.查询时间，并格式化为“年-月-日”
select date_format(time, '%Y-%m-%d') as day from table_name;
2.日期时间转化为时间戳
select unix_timestamp(date) as day from table_name;
3.时间戳转化为日期时间
select from_unixtime(1488211200, '%Y-%m-%d') as day from table_name;
4.一个sql返回多个总数
select count(*) total, count(case when column = value1 then column1 end) column_value1_num, count(case when column = value2 then column end) column_value2_num from table_name;
5.创建表，注意命名关键字要用``包住而不是''
create table `t_company` (`company_id` int(11) not null, `company_name` varchar(40) null, `company_age` int(11) null, primary key (`company_id`)) charset=utf8 comment='公司表';
6.插入数据
insert into t_company values (125, '上海云贝', 6); //插入全字段
insert into t_company (company_id, company_name) values (126, '上海贝贝'); //插入部分字段
7.内连接
update table_name_1 inner join table_name_2 on table_name_1.id = table_name_2.uid inner join table_name_3 on table_name_3.id = table_name_1.tid set *** = *** where ***;
8.替换某个字段里面的内容
update t_company set company_name = replace(company_name, '上海', '北京') where company_name like '%上海%';
9.查询某字段包含某字符串
select * from t_company where locate ('北京', company_name);
10.查询某字段的哪几位，注意这里索引从1开始
select substring(company_name, 2, 2) from t_company; //获取company_name的第2位以后的两位
11.查询重复的记录
select company_id from t_user_company_rel group by company_id having count(company_id) > 1;
12.删除表中重复的记录（留id最小的或者最大的）
delete from t_user_company_rel where company_id in (select company_id from t_user_company_rel group by company_id having count(company_id) > 1) and id not in (select min(id) from t_user_company_id group by company_id having count(company_id) > 1
13.修改表字段名
alter table t_company change column companny_id company_id varchar(40);
14.分页查询
select * from t_user limit 3,5;
15.计算某一列值的总和
select sum(column_name) from table_name;
注：如果有错误，请各位指出，也欢迎大家前来拍砖，灰常感谢！
网友评论