sql语句的简单用法
来源:互联网 发布:淘宝卖家需要的软件 编辑:程序博客网 时间:2024/06/08 01:59
感觉麻烦就没有将表建进来,需要有基础的朋友替换相应的表和字段,谢谢
--mysql分页查询 5是查询在数据存储第5条数据20是从第五条开始的20条数据,因此显示的是6至26之间的数据
select * from tm_acl_dict where 1=1 limit 5,20
--修改字段长度
alter table tm_zm_score modify column CREDIT_UP_DATE varchar(32);
--移动表数据,从s2移动到tm_zm_watch_list_detail表,前提是两表的字段一致
insert into tm_zm_watch_list_detail
select * from s2 ;
--统计两张表的数据个数
select * from (select count(*) as c from tm_zm_watch_list_detail) as t0,
(select count(*) from s2) as s0;
--删除表
drop table s2;
--先去重一张表再右连接一张表,最后排序输出
select * from (
select distinct id_no from tm_zm_watch_list_detail) t0
right join cust on t0.id_no = cust.id_no
order by t0.id_no;
--先去重再计数
select count(distinct id_no) from cust;
//查询日期等于某天的
select * from tm_cis_req_prd_his twhere t.req_date = '20170522'
select * from tm_zm_score t
where t.zm_score='666'
//大于芝麻分666的
select zm_score as score from tm_zm_score
where zm_score>666
//求和
select sum(zm_score) as score from tm_zm_score
//求平均值
select avg(zm_score) as score from tm_zm_score
//求最大值
select max(zm_score+0) as score from tm_zm_score
//求最小值
select min(zm_score+0)as score from tm_zm_score
//求数据条数
select count(zm_score) from tm_zm_score
//数字字符串排序
select * from tm_zm_score order by zm_score+0 desc
//between and用法
select * from tm_zm_score where zm_score between 6 and 66 order by zm_score+0
//in 用法
select * from tm_zm_score where zm_score in (66)
//查询某个时间段所有大于0的数据并按芝麻分降序排列
select * from tm_zm_score where zm_score>0 and create_date between '2017-04-30 00:00:00' and '2017-05-24 00:00:00' order by zm_score desc
//增加一列
alter table tm_zm_score add bb varchar(11)
//删除一列
alter table tm_zm_score drop aa
//模糊查询
select * from tm_zm_score where zm_score like '%6%'
//插入数据
insert into tm_zm_score(cust_id,org) value('01','01');
//删除数据
delete from tm_zm_score where cust_id='02'
//更新数据
update tm_zm_score set cust_id='02' where cust_id='01'
//左连接表查询
select * from tm_zm_watch_list left join tm_zm_watch_list_detail on tm_zm_watch_list.cust_id=tm_zm_watch_list_detail.cust_id
//右连接查询
select * from tm_zm_watch_list right join tm_zm_watch_list_detail on tm_zm_watch_list.cust_id=tm_zm_watch_list_detail.cust_id
//内连接
select * from tm_zm_watch_list inner join tm_zm_watch_list_detail on tm_zm_watch_list.cust_id=tm_zm_watch_list_detail.cust_id
//索引创建
create index q on tm_acl_dict(id)
//删除索引
drop index q on tm_acl_dict
select * from tm_acl_dict where id=1000
select zm_score as score from tm_zm_score where zm_score=66
//group by 分组查询
select type_name,count(type_name) from tm_acl_dict group by type_name
select count(type_name) from tm_acl_dict t where t.type_name='省'
select count(type_name) from tm_acl_dict where type_name='省'
//case 用法
select type_name as '地区',
case type when 'state'
then code else 0 end as'code'
from tm_acl_dict
//改变主键前要先删掉原先的主键
alter table tm_td_post_loan_monitoring_data_person_info drop PRIMARY KEY
alter table tm_td_post_loan_monitoring_data_person_info add primary key(id_number)
- sql语句的简单用法
- sql语句的用法
- sql语句中"case when"的简单用法示例
- 记录下, sql server下面语句的一些简单用法
- 简单的SQL语句
- 简单的SQL语句
- 简单的SQL语句
- 简单的sql语句。
- sql简单的语句
- 简单的sql语句
- 简单的SQL语句
- SQL语句基本的用法
- SQL语句基本的用法
- sql语句like的用法
- 一些SQL语句的用法
- sql语句的基本用法
- SQL语句Union的用法
- SQL语句Union的用法
- UVA 10361 Automatic Poetry
- CSS绘制三角形
- redis实现web页面缓存
- 算法分析与设计课程12——486. Predict the Winner
- 用tarjan缩点重建图
- sql语句的简单用法
- 算法课第十五周作业 | Median of Two Sorted Arrays
- 系统学习前端之CSS 基础
- rhel7中,targetcli使用两位数的分区后缀名字(比如sda10)而导致的问题
- java单链表的翻转
- 解决mybatis generator生成实体类和数据库字段名不一致
- [UIViewController _loadViewFromNibNamed:bundle:] loaded the nib but the view outlet was not set
- android studio 中 Error:(12, 23) 错误: 程序包org.apache.http不存在
- GO语言method