sql基础学习

1、SELECT * FROM EMP;  --最简单的sql语句  SELECT * INTO 
2、Select * from tab;  --查看当前用户所有表和视图
3、Sql>help index  --显示SQLPLUS命令的帮助
4、Sql>show all  --显示SQLPLUS命令的帮助
5、Sql>show user --显示SQLPLUS命令的帮助
6、SPOOL /u01/app/oracle append  --显示SQLPLUS命令的帮助，append是追加
SELECT * FROM EMP;
SPOOL OFF
7、SQL> clear screen  --清屏
8、Select sal as salary,hiredate “上班日期” ,sal*12 total_salary from emp;  --用空格和as给列加别名
9、Select distinct deptno from emp;  --distinct去掉重复的列
10、SELECT *|{[DISTINCT] column|expression [alias],...}  --where,order by语法，默认是升序asc，降序要指定desc
FROM table
[WHERE condition(s)]
   [order by column|expression| alias ];
11、Select ename,sal from emp Where sal between 1000 and 3000;  --between...and...用法
12、Select deptno,ename,sal from emp Where deptno in(10,20);  --in用法
13、select ename,deptno from emp where ename like 'J%';  --like用法，_通配一个，仅匹配一个字符,% 通配没有或多个字符
14、Select ename from emp where ename like ‘%s_%’ escape ‘s ’;  --escape转义字符
15、Select ename,comm from emp where comm is null;  --null值用法，null不等null也不等于0
16、Select ename,deptno,sal From emp Where deptno=30 and sal>1200;  --and用法
17、Select ename,deptno,sal From emp Where deptno=30 or sal>1200;  --or用法
18、Select ename,deptno,sal From emp Where ename not like 'T%';  --not用法


优先级
1、算术运算
2、连接运算
3、关系运算
4、 IS [NOT] NULL, LIKE, [NOT] IN
5、Between
6、not
7、and
8、or
括号强制优先级


操作字符的函数
lower,upper,initcap,concat,length,lengthb,lengthc,substr,instr,trim(leading,trailing,both),lpad,rpad,replace
1、Select lower(ename)，upper(ename)，initcap(ename) from emp;
2、select ename,job,concat(ename,job) from emp;
3、select length('张三') from dual;--按照字
4、select lengthb('张三') from dual;--按字节
5、select lengthc('张三') from dual;--unicode的长度
6、select ename,substr(ename,1,1) "first",substr(ename,-1) "last"from emp;
7、select ename,instr(ename,'A') "A在第几位" from emp;
8、select trim(leading 'a' from 'aaaaabababaaaaa') from dual;
9、select trim(trailing 'a' from 'aaaaabababaaaaa') from dual;
10、select trim(trailing 'a' from 'aaaaabababaaaaa') from dual;
11、select trim(trailing 'a' from 'aaaaabababaaaaa') from dual;
12、SELECT REPLACE('JACK and JUE','J','BL') FROM DUAL;
操作数字的函数
round,trunc,ceil,abs,mod
1、SELECT ROUND(45.923,2), ROUND(45.923,0), ROUND(45.923,-1) FROM DUAL;
2、SELECT TRUNC(45.923,2), TRUNC(45.923), TRUNC(45.923,-2) FROM DUAL;
3、select ceil(45.001) from dual;取整，上进位，和trunc全部去掉正好相反
4、select abs(-23.00) from dual;取绝对值
5、select mod(sal,2000) from emp;取余数
操作日期的函数
sysdate,between_months,add_months,next_day,last_day
col value for a20  --格式化列长度
col value clear  --清除格式化
1、select sysdate from dual;  --查看当前时间
2、alter session set NLS_DATE_FORMAT='yyyy/mm/dd:hh24:mi:ss';  --设定当前session时间格式
3、select months_between(sysdate,hiredate) ,sysdate,hiredate from emp;  --取两个日期的月间隔
4、select add_months(hiredate,6) ,hiredate from emp;  --六个月过后是哪天。
5、select next_day(hiredate,'friday') ,hiredate from emp;  --当前的日期算起，下一个星期五是哪一天，这句话你可能运行失败，因为日期和客户端的字符集设置有关系，如果你是英文的客户端，就的用Friday来表达，日期是格式和字符集敏感的。如果你是中文的客户端，就的用‘星期五’来表达。
6、select last_day(hiredate) ,hiredate from emp;  --该日期的月底是哪一天。
7、select ename,to_char(hiredate,'fmyyyy/mm/dd') from emp;  --FM消除前置的零和空格。
其他格式：year,month,mon,day,dy,am,ddsp,ddspth
8、select to_char(hiredate,'fmyyyy "年" mm "月"') from emp;  --格式内加入字符串请双引。
9、select sysdate,to_char(sysdate,'sssss') ss from dual;  --当前距离零点的秒数.
10、select to_char(sysdate,'yyyy year mm month mon dd day dy ddsp ddspth') from dual; 
11、select ename,to_char(sal,'9999.000') salary from emp;
select ename,to_char(sal,'$00099999000.00' ) salary from emp;
select ename,to_char(sal,'l99,999.000') salary from emp;
select ename,to_char(sal,'9G999D99') salary from emp;
--9是代表有多少宽度，如果不足会显示成######,0代表强制显示0，但不会改变你的结果。G是千分符，D是小数点。
12、select to_char(321,'xxxxx') from dual;  --xxxxx的位数要足够多
如果你想将十六进制的数转换为十进制的数请使用to_number函数
13、select to_number('abc32','xxxxxxxx') from dual;
14、SELECT TO_DATE( 'January 15, 1989, 11:00 A.M.', 'Month dd, YYYY, HH:MI A.M.','NLS_DATE_LANGUAGE = American') FROM DUAL;
15、select TO_NUMBER('100.00', '9G999D99') from dual;
null值操作
NVL (expr1, expr2)  --如果expr1为非空，就返回expr1, 如果expr1为空返回expr2，两个表达式的数据类型一定要相同。
NVL2 (expr1, expr2, expr3)  --如果expr1为非空，就返回expr2, 如果expr1为空返回expr3
NULLIF (expr1, expr2)  --如果expr1和 expr2相同就返回空，否则返回expr1
COALESCE (expr1, expr2, ..., exprn)  --返回括号内第一个非空的值。
1、select ename,comm,nvl(comm,-1) from emp;
2、select sal+comm ,sal+nvl(comm,0),nvl2(comm,'工资加奖金','纯工资') "收入类别" from emp;
3、select ename,nullif(ename,'KING') from emp;
4、select COALESCE(comm,sal,100) "奖金" from emp;
Case语句
SELECT ename, job, sal,
CASE job WHEN 'CLERK' THEN 1.10*sal
WHEN 'SALESMAN' THEN 1.15*sal
WHEN 'ANALYST' THEN 1.20*sal
ELSE sal END "REVISED_SALARY"
FROM emp;
decode语句
DECODE(col|expression, search1, result1
[, search2, result2,...,]
[, default])


SELECT ename, job, sal,
decode(job ,'CLERK' , 1.10*sal
,'SALESMAN' , 1.15*sal
,'ANALYST' , 1.20*sal
, sal ) "REVISED_SALARY"
FROM emp;
组函数avg,sum,max,min,count
select sum(sal),min(sal),max(sal),avg(sal),count(sal) from emp;
group by  --分组排序
select deptno,sum(sal) from emp group by deptno;
select job,avg(sal) from emp group by job having avg(sal)>2000;  --Having是在结果中再次筛选。Having一定得出现在group by 子句得后面。不能独立存在。
INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL JOIN连接表用法
INNER JOIN  --连接表
SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2 
ON table_name1.column_name=table_name2.column_name
LEFT JOIN  --左连接
SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2 
ON table_name1.column_name=table_name2.column_name
RIGHT JOIN  --右连接
SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2 
ON table_name1.column_name=table_name2.column_name
FULL JOIN  --FULL JOIN全连接
SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2 
ON table_name1.column_name=table_name2.column_name
JOIN: 如果表中有至少一个匹配，则返回行
LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行
RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行
FULL JOIN: 只要其中一个表中存在匹配，就返回行
UNION  --连接两个SQL语句结果集 UNION ALL允许重复
SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2
约束
UNIQUE  --约束唯一标识数据库表中的每条记录。
CREATE TABLE Persons
(
Id_P int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
ALTER TABLE Persons ADD CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName)
ALTER TABLE Persons DROP CONSTRAINT uc_PersonID
PRIMARY KEY  --约束均为列或列集合提供了唯一性的保证。
CREATE TABLE Persons
(
Id_P int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
ALTER TABLE Persons ADD CONSTRAINT pk_PersonID PRIMARY KEY (Id_P,LastName)
ALTER TABLE Persons DROP CONSTRAINT pk_PersonID
FOREIGN KEY  --外键约束
CREATE TABLE Orders
(
Id_O int NOT NULL PRIMARY KEY,
OrderNo int NOT NULL,
Id_P int FOREIGN KEY REFERENCES Persons(Id_P)
)
ALTER TABLE Orders ADD CONSTRAINT fk_PerOrdersFOREIGN KEY (Id_P)REFERENCES Persons(Id_P)
ALTER TABLE Orders DROP CONSTRAINT fk_PerOrders
CHECK  --CHECK 约束用于限制列中的值的范围。
CREATE TABLE Persons
(
Id_P int NOT NULL CHECK (Id_P>0),
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
CREATE TABLE Persons  --命名CHECK或为多列添加CHECK
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT chk_Person CHECK (Id_P>0 AND City='Sandnes')
)
ALTER TABLE Persons ADD CHECK (Id_P>0)
ALTER TABLE Persons ADD CONSTRAINT chk_Person CHECK (Id_P>0 AND City='Sandnes')  ----命名CHECK或为多列添加CHECK
ALTER TABLE Persons DROP CONSTRAINT chk_Person
DEFAULT --DEFAULT 约束用于向列中插入默认值。
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes'
)
ALTER TABLE Persons ALTER COLUMN City SET DEFAULT 'SANDNES'
ALTER TABLE Persons ALTER COLUMN City DROP DEFAULT