Sql (笔记)
通配符
- % :百分号表示零个、一个或多个字符
- _ :下划线表示单个字符
1.查询
SELECT * FROM 表名 WHERE 条件
不显示重复数据
SELECT DISTINCT 列名 FROM table
SELECT 表名.字段,表名.字段 FROM 表名1 AS 名1,表名2 AS 名2 WHERE 条件
SELECT * FROM 表名 AS 表 WHERE 表.时间字段>'2022-03-06 00:00:00'
SELECT * FROM 表 AS 名 WHERE to_date(名.字段,'yyyy-MM-dd') BETWEEN to_date('2011-06-01','yyyy-MM-dd') AND to_date('20110705','yyyymmdd') ;
SELECT * FROM _ AS _ WHERE _._LIKE '%__%'
SELECT * FROM table cloumn LEFT JOIN table cloumn ON cloumn.id=cloumn.id
SELECT * FROM table cloumn RIGHT JOIN table cloumn ON cloumn.id=cloumn.id
inner join 交集 显示表1表2内相同数据的行
SELECT * FROM 表1 表1的别名 INNER JOIN 表2 表2的别名 on 表1别名.栏位 = 表2别名.栏位;
union 查询,两个表的字段数量必须相同,结果无重复数据,union all 所有数据
SELECT 语句 1 UNION SELECT 语句 2;
SELECT 语句 1 UNION ALL SELECT 语句 2;
case 类似于 switch case 语句
SELECT CASE ("column")
WHEN "条件1" THEN "结果1"
WHEN "条件2" THEN "结果2"
...
ELSE "结果N"
END
FROM "table";
……order by asc #升序
……order by desc #降序
子句
select a.column from tablea a where a.column in (select * from tableb b)
exist
select * from table a exits (条件或子句)
某个时间范围
sql select * from 表名 where 时间字段 BETWEEN '开始时间' AND '结束时间';
今天
sql select * from 表名 where to_days(时间字段名) = to_days(now());
昨天
sql SELECT * FROM 表名 WHERE TO_DAYS( NOW( ) ) - TO_DAYS( 时间字段名) = 1;
一周(7天)
sql SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(时间字段名);
一月(30天)
sql SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(时间字段名);
本月
sql SELECT * FROM 表名 WHERE DATE_FORMAT( 时间字段名, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' );
上个月
sql SELECT * FROM 表名 WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( 时间字段名, '%Y%m' ) ) =1;
查询本季度数据
sql select * from 表名 where QUARTER(create_date)=QUARTER(now());
查询上季度数据
sql select * from 表名 where QUARTER(create_date)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));
查询本年数据
sql select * from 表名 where YEAR(create_date)=YEAR(NOW());
查询上年数据
sql select * from 表名 where year(create_date)=year(date_sub(now(),interval 1 year));
查询当前这周的数据
SELECT name,submittime FROM 表名 WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now());
查询上周数据
sql SELECT name,submittime FROM 表名 WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now())-1;
查询上个月数据
select name,submittime from 表名 where date_format(submittime,'%Y-%m')=date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y-%m');
select * from 表名 where DATE_FORMAT(pudate,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m') ;
select * from 表名 where WEEKOFYEAR(FROM_UNIXTIME(pudate,'%y-%m-%d')) = WEEKOFYEAR(now());
select * from 表名 where MONTH(FROM_UNIXTIME(pudate,'%y-%m-%d')) = MONTH(now());
MONTH(FROM_UNIXTIME(pudate,'%y-%m-%d')) = MONTH(now());
select * from 表名 where pudate between 上月最后一天 and 下月第一天;
查询当前月份的数据
sql select name,submittime from 表名 where date_format(submittime,'%Y-%m')=date_format(now(),'%Y-%m');
查询距离当前现在6个月的数据
sql select name,submittime from 表名 where submittime between date_sub(now(),interval 6 month) and now()
批量insert
insert into table (column, column2) values("" ,""),("","")
DATEDIFF()查询
select * from table where DATEDIFF(NOW(),COLUMN)>15
函数
- avg() 返回指定列的平均值 count() 返回指定列中非 NULL 值的个数
- min() 返回指定列的最小值
- max() 返回指定列的最大值
- sum(x) 返回指定列的所有值之和
插入
INSERT INTO 表名称 VALUES (value1, value2,....)
索引
索引是数据结构,类似于图书馆的分离标签,书的目录,帮助查询效率。数据即索引。索引更新维护麻烦。
ORACLE:
ORACLE没有提供直接获取年、周的函数,提供了一些函数,需要进行计算
to_char()和to_date基本等价于JAVA中的SimpleDateFormat函数
trunc()函数为指定元素而截去的日期值。
trunc(sysdate,'yyyy') --返回当年第一天。
trunc(sysdate,'mm') --返回当月第一天。
trunc(sysdate,'d') --返回当前星期的第一天。
trunc(sysdate,'dd')--返回当前年月日
trunc()也可以对数字精度进行操作,trunc(number,length),number用于完整取精度的数字,length代表精度长度,默认为0;trunc()取精度时不进行四舍五入
select trunc(123.458,0) from dual --123
select trunc(123.458,1) from dual --123.4
select trunc(123.458,-1) from dual --120
select trunc(123.458,-4) from dual --0
select trunc(123.458,4) from dual --123.4580
add_months(times,months)表示在time时间之上进行月份操作,months为正整数表示之后,正负数表示之前
maven-war-plugin 3.0.0xml
create table t_user(
user_id number not null primary key,
user_name varchar2(30),
credits number,
user_password varchar2(32),
last_visit date,
last_ip varchar2(23)
)
-- Create sequence
create sequence T_USER_SEQ
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
cache 20;
create or replace trigger t_user_tr
before insert on t_user
for each row
begin
select t_user_seq.nextval into :new.user_id from dual;
end t_user_tr;
insert into t_user values(null,'xiaoming',123,'1114',sysdate,'192.168.37.132');
insert into t_user values(null,'xiaoming1',123,'1114',sysdate,'192.168.37.132');
insert into t_user values(null,'xiaoming1',123,'1114',sysdate,'192.168.37.132');
insert into t_user values(null,'xiaoming1',123,'1114',sysdate,'192.168.37.132');
insert into t_user values(null,'xiaoming2',123,'1114',sysdate,'192.168.37.132');
insert into t_user values(null,'xiaoming',123,'1114',sysdate,'192.168.37.132');
insert into t_user values(null,'xiaoming',123,'1114',sysdate,'192.168.37.132');
insert into t_user values(null,'xiaoming',123,'1114',sysdate,'192.168.37.132');
insert into t_user values(null,'xiaoming',123,'1114',sysdate,'192.168.37.132');
insert into t_user values(null,'xiaoming',123,'1114',sysdate,'192.168.37.132');
select * from t_user
orcale 插入当前时间字段的两种方式
insert into test2(id, name, create_time)
values(1, '测试', to_date('2021-10-26 16:47:21', 'yyyy-MM-dd HH24:mi:ss'));
insert into TEXT_car_records values(null,'ÍîA8888',sysdate,null,1);
create table test2(
id number primary key,
name varchar(16),
create_time timestamp default current_timestamp
);
insert into test2(id, name)
values(1, '测试2');