一、count(*)、count(1)、count(字段名）的区别
select count(*) from t_md_inst --153797 --包含字段为null 的记录
select count(1) from t_md_inst ---153797 --包含字段为null 的记录
select count(class_id) from t_md_inst --19883 --不包含class_id是null的记录
select count(distinct class_id) from t_md_inst --29 ---不显示class_id重复的记录
二、group by + having + order by
select quote_class, quoteed_type
from t_meta_quote
group by quote_class, quoteed_type
having quote_class != 'DDIndex'
order by quoteed_type desc, quote_class asc;
三、||连接符：连接字段和字符串
四、distinct两个字段的去重
五、伪列、别名、is null、like
六、例子
select a.pct_free,a.pct_free*2+1,a.* from user_tables a where max_extents is not null and table_name like '%_T_M%';----like
select a.* from user_tables a where initial_extent =65536 and num_rows !=5-----and
select * from t_md_inst WHERE INST_CODE IN ('T_DB_UAT_INFO','T_DQS_KNO_CATS')---in
select distinct max_trans from user_tables a----distinct
select COUNT(*) from t_md_inst----count(*)
select a.version_date,a.start_time,a.* from t_md_inst a order by a.version_date,a.start_time ---order by 字段1，字段2 升序
select a.version_date,a.start_time,a.* from t_md_inst a order by a.version_date,a.start_time desc----order by 字段1，字段2 desc 降序
---创建表
create table cm_cs
(id varchar(5) primary key not null,
code varchar(50),
name varchar(500),
parent_id int ,
creat_time date)
create table cs as select * from t_md_etl_job_inst ---复制 b表的数据给cs表，未复制表属性
create table cs1 as select * from t_md_etl_job_inst where 1=2---只复制 b表的结构给cs表，未复制值
---给表赋值
insert nto t_md_etl_job_inst (job_name,job_id) values ('cc','cc');---INSERT INTO 表名(列名1，列名2……) VALUES (值1，值2……)
insert into t_md_etl_job_inst values （值1，值2）----所有列都要给到值
---修改与删除数据
select * from 表名 where inst_code='TD_TNM' for update ----修改表的记录
update 表名set inst_name='lwh',inst_code='TD_TNM' where inst_id='';
select rowid,a.* from 表名
delete from 表名 where name='百度' and country='CN'; -