CRUD:
CRUD是指在做計算處理時的
增加(Create)、查詢(Retrieve)(重新得到數據)、更新(Update)和刪除(Delete)幾個單詞的首字母簡寫。主要被用在描述軟件系統中數據庫或者持久層的基本操作功能。
限定條件查詢:
1.查什么
2.從哪里查
3.條件是什么
SELECT 查什么
FROM 從哪里查
WHERE 條件
select first_name ,salary from s_emp where salary > 1500;
--order by 子句
--默認升序
select first_name , salary from s_emp ORDER BY salary ;
select first_name , salary from s_emp ORDER BY salary desc ;
ASC 升序
DESC 降序
--排序可以給多個字段
select first_name , salary from s_emp ORDER BY salary , first_name ;
--根據列的順序號來進行排序
select first_name , salary from s_emp ORDER BY 2 ;
where 子句:
select first_name, last_name , salary from s_emp where last_name = 'magee';
Logical comparison operators
= > >=
BETWEEN ... AND...
IN(list)
LIKE
IS NULL
AND
OR
NOT
--查出工資范圍在 1000至1500
//error
select first_name ,salary from s_emp where 1000< salary <1500;
--and用法
select first_name ,salary from s_emp where salary > 1000 and salary < 1500;
--or 用法
--工資低於1000高於1500
select first_name ,salary from s_emp where salary < 1000 or salary > 1500;
--BETWEEN ... AND... [ ]
select first_name ,salary from s_emp where salary between 1100 and 1500;
select first_name ,salary from s_emp where salary > 1100 and salary < 1500;
--IS NULL
select commission_pct , first_name from s_emp where commission_pct is null;
select commission_pct , first_name from s_emp where commission_pct is not null;
--in
--查出41,42,43部門員工
select first_name ,salary from s_emp where dept_id = 41 or dept_id = 42 or dept_id = 43;
select first_name ,salary from s_emp where dept_id in (41,42,43)
--查出非41,42,43部門員工
select first_name ,salary from s_emp where dept_id not in (41,42,43)
--like 模糊查詢
% 0--N
_ 有且僅有一個字符
select first_name ,salary from s_emp where first_name like '%a%';
select first_name ,salary from s_emp where first_name like '_a%';
select table_name from user_tables where table_name like 'S/_%' ESCAPE '/';
--查出41,42部門工資大於 1200
select first_name , salary ,dept_id from s_emp
where salary >1200 and (dept_id = 41 or dept_id = 42);
公式:
SELECT [DISTINCT] {*, column [alias], ...}
FROM table
[WHERE condition(s)]
[ORDER BY {column, expr, alias} [ASC|DESC]];
3.Single Row Functions
字符
LOWER Converts to lowercase
UPPER Converts to uppercase
INITCAP Converts to initial capitalization
CONCAT Concatenates values
SUBSTR Returns substring
LENGTH Returns number of characters
NVL Converts a null value
select LOWER('DHT') from s_emp;
--啞表 dual
select LOWER('DHT') from dual;
select first_name, last_name , salary from s_emp where LOWER(last_name) = 'magee';
select UPPER('lll') from dual;
select INITCAP('ambow xubin') from dual;
select CONCAT('ambow','xubin') from dual;
select SUBSTR('ambowxubin',1,5) from dual;
數字
ROUND 舍入
TRUNC 截取
ROUND (45.923, 2) 45.92
ROUND (45.923, 0) 46
ROUND (45.923, -1) 50
TRUNC (45.929999, 2) 45.92
TRUNC (45.923) 45
TRUNC (45.923, -1) 40
select round(45.924999) from dual;
ceil 向上取整
select ceil(45.924999) from dual;
日期
sysdate
select sysdate from dual;
日-月-年
DD-MON-RR
RR YYYY
MM MON
--對 sysdate + - 整數 單位 天
select sysdate-1 from dual;
MONTHS_BETWEEN
select months_between(sysdate, sysdate+10) from dual;
ADD_MONTHS
select ADD_MONTHS(sysdate,1) from dual;
NEXT_DAY
select NEXT_DAY(sysdate ,'FRIDAY') from dual;
select NEXT_DAY(sysdate ,7) from dual;
LAST_DAY
select LAST_DAY(sysdate-10) from dual;
ROUND
select ROUND(sysdate, 'DD') from dual;
TRUNC
select TRUNC(sysdate, 'DD') from dual;
轉換
to_char
select concat(to_char(9999), to_char('aaa'))from dual;
to_number
select to_number('1234') from dual;
to_date
select to_char(sysdate , 'YYYY-MM-DD') FROM DUAL;
select to_char(sysdate , 'YYYY-MM-DD HH24:MI:SS AM') FROM DUAL;
select to_char(sysdate , 'YEAR-MM-DD HH24:MI:SS AM') FROM DUAL;
'2008-08-08'
select to_date('2008-08-08' ,'YYYY-MM-DD') FROM DUAL;
SELECT SYSDATE FROM DUAL;
HH24:MI:SS AM 15:45:32 PM
MON
YYYY
YY
RR
DD
YEAR
求上個月第一天
select add_months( trunc(sysdate ,'Mon') , -1) from dual;
select last_day(add_months(sysdate, -2 )) +1 from dual;
求下個月最后一天
多行函數
Group
avg
sum
min
max
count
select count(*) from s_emp;
select max(salary), sum(salary) ,avg(salary), min(salary) from s_emp;
group by ... having ....
SELECT column, group_function
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column];
--統計各個部門,平均工資,最低工資,最高工資,工資總和
select dept_id , max(salary), sum(salary) ,avg(salary), min(salary) from s_emp
group by (dept_id );
--統計各個部門,多少員工,平均工資,最低工資,最高工資,工資總和
select dept_id , count(salary) , max(salary), sum(salary) ,avg(salary), min(salary) from s_emp
group by (dept_id );
--統計各個部門,多少員工,平均工資,最低工資,最高工資,工資總和,按部門編號排序
select dept_id , count(salary) , max(salary), sum(salary) ,avg(salary), min(salary) from s_emp
group by (dept_id )
order by dept_id ;
--統計各個部門,多少員工,平均工資,最低工資,最高工資,工資總和,按部門編號排序 除 41 43 部門以外
select dept_id , count(salary) , max(salary), sum(salary) ,avg(salary), min(salary)
from s_emp where dept_id !=41 and dept_id !=43
group by (dept_id )
order by dept_id ;
select dept_id , count(salary) , max(salary), sum(salary) ,avg(salary), min(salary)
from s_emp
group by (dept_id )
having dept_id !=41 and dept_id !=43
order by dept_id ;