sql语言

一、SELECT 语句基本结构
SELECT *|{[DISTINCT] column|expression [alias],...} FROM table;
SELECT 确定哪些列。
FROM 确定哪张表。
SELECT 是一个或多个字段的列表
* 选择所有的列
DISTINCT 关键字表示禁止重复
column|expression 选择指定的字段或表达式
alias 给所选择的列不同的标题
 
 连字符:||
定义列别名:select last_name name , COMMISSION_PCT as comm frmo table;列名+“空格或as”+别名
去重复行:select distinct department_id from employees;
行选择:select last_name,job_id,department_id from employees where department_id =90;  
字符串:select job_id from employees where last_name='King';
日期: select first_name,DEPARTMENT_ID,job_id from employees where HIRE_DATE='24-1月-06';
between:select last_name,salary from employees where salary between 2500 and 3500; between 下限 and 上限
in:select employee_id,last_name,salary from employees where manager_id in(100,101,201);
like: select last_name from employees where last_name like '%a%';    %表示0或多个字符,_表示一个字符;
escape:select last_name from employees where job_id like '%SA\_%'escape'\';
null:select last_name , job_id,commission_pct from employees where commission_pct is null;
 
优先规则:1、算术运算 2、连字操作 3、比较操作 4、is null ,like,in ,5、between 6、not逻辑条件 7、and逻辑条件 8、or逻辑条件
排序:select last_name  from employees order by salary desc;  desc为降序,asc为升序;
select last_name,job_id,hire_date from employees where hire_date between '22-2月-02' and '1-5月-07' order by hire_date asc;
 
大小写处理函数
LOWER:转换大小写混合的字符串为小写字符串。
UPPER:转换大小写混合的字符串为大写字符串。
INITCAP:将每个单词的首字母转换为大写,其他字母为小写。
select ' The job id for '||upper(last_name)||' is '||lower(job_id) as"EMPLOYEE DETALLS" from employees;
 

 

trim默认去掉两侧,leading为去掉头部,trailing为去掉头部

replace函数:用另外一个值替换某个值

SQL> select replace('HelloWorld','W','w') from dual;

手机号隐藏中间四位: select replace('13622329860',substr('13622329860',4,4),'****') from dual;

数字函数:

 

 

SYSDATE 函数

 

 日期函数

 

 months_between:select months_between(sysdate,hire_date) from employees; 两个日期见有多少天

add_months:select add_months(sysdate,5) from employees; 5个月后的日期

next_day:select next_day (sysdate,'星期一') from dual; 当前日期最近的星期一

last_day:select last_day(sysdate) from dual;   当前月最后一天的日期

 
to_char 日期转换

 

 

to_char : select to_char(sysdate,'yyyy')from dual;
select to_char(sysdate,'yyyy"年"mm"月"dd"日"hh"时"mi"分"ss"秒"') from dual;
 
数字格式模板:
FM:代表去掉返回结果中的前后空格。
数字转字符:select last_name,to_char(salary,'FM$999,999,999.00') from employees where last_name='Whalen';
to_number: select to_number('¥34,346.56','L99,999.99') from dual; 
to_date: select to_date('2021-05-18 11:57:28','yyyy-mm-dd hh:mi:ss') from dual;
 
通用函数
NVL:转换一个空值到一个实际的值
nvl(commission,1) 
NVl2(1,2,3):1不为空返回2,1为空返回3;
select nvl2(commission_pct,'$AL+COMM','SAL') from employees;
NULLIF(1,2):比较两个表达式,相等返回空,不相等返回1
select first_name,length(first_name)as "expr1",last_name,length(last_name)as"expr2",nullif(length(first_name),length(last_name))as "result" from employees;
COALESCE(1,2,3,4.....,n):返回列表中第一个非空表达式
 select coalesce(commission_pct,salary,10) from employees;
 
case表达式:IF WHEN THEN END
SQL> select last_name,job_id,salary, CASE job_id WHEN 'IT_PROG' THEN salary*1.1 WHEN 'ST_CLERK' THEN salary*1.15 WHEN 'SA_REP' THEN salary*1.2 END from employees ;
DECODE函数:decode() 
SQL> select last_name,job_id,salary, DECODE(job_id,'IT_PROG',salary*1.1,'ST_CLERK',salary*1.15,'SA_REP',salary*1.2) from employees ;
 

多表连接语法结构:

左外链接(LEFT OUTER JOIN):selecte.last_name,d.department_namefromemployeese left outer join departmentsd one.department_id = d.department_id;
右外链接(RIGHT OUTER JOIN)
全外链接(FULL OUTER JOIN)
自然连接(NATURAL JOIN):selectd.department_id,d.department_name,l.cityfrom departments d natural join locations l;

 

组函数(聚合函数):

组函数的类型
• AVG 平均值
• COUNT 计数
• MAX 最大值
• MIN最小值
• SUM合计
 
创建分组:
GROUP BY:SQL> select avg(salary) from employees group by department_id;
显示在每个部门中付给每个工作岗位的合计薪水的报告:select department_id,job_id,sum(salary) from employees group by department_id,job_id order by department_id asc;
先进行数据查询,在对数据进行分组,然后执行组函数
 
约束分组结果:HAVING子句
HAVING 语句通常与 GROUP BY 语句联合使用,用来过滤由 GROUP BY 语句返回的记录集。
HAVING 语句的存在弥补了 WHERE 关键字不能与聚合函数联合使用的不足
显示那些最高薪水大于$10,000 的部门的部门号和最高薪水: select department_id,max(salary) from employees group by department_id having max(salary)>10000;
嵌套分组函数:SQL> select max(avg(salary)) from employees group by department_id ;
 
子查询:select...from... where...(select)
多行子查询:
in:等于列表中的任何成员 
SQL> selecte.last_name,e.department_id,e.salary fromemployees e where e.salary in(selectmin(em.salary) from employees em group byem.department_id); 
any:比较子查询返回的每个值   <ANY:小于最大值   >ANY:大于最小值
SQL> select employee_id ,last_name, job_id,salary from employees where job_id <> 'IT_PROG' and salary<any(select salary from employees where job_id ='IT_PROG');
all:比较子查询返回的全部值 <ALL:小于最小值  >ALL:大于最大值
SQL> select employee_id ,last_name, job_id,salary from employees where job_id <> 'IT_PROG' and salary<all(select salary from employees where job_id ='IT_PROG');
 
操纵数据DML
向表中插入数据:SQL> insert into departments(department_id,department_name,manager_id) values(280,'Teaching',180);
添加日期:25/5月/2021;to_date('2021-05-25','yyyy-mm-dd');
复制一行数据到另外一个表:insert into emp (**,**,**) select ...frmo
修改数据:update emp set name='xiaoma' ,salary=20000 where id=170;
删除数据:delete from emp where id =304;
 
事务控制语言(TCL)
commit.....事物提交
rollback....事物回滚
savepoint..设置回滚点
 
数据库定义语言(DDL)
创建表:SQL> create table dept(deptno number(2),dname varchar2(14),loc varchar2(13));
修改表信息
1、增加列:SQL> alter table dept add (salary number(8,2));
2、修改列类型:SQL> alter table dept modify(dname varchar2(20));
3、修改默认值:SQL> alter table dept modify(salary number(8,2) default 5000);
4、修改列名:alter tabledeptrename column dname toname;
5、删除列:alter table dept drop column salary;
6、修改表名:SQL> rename job_grades1 to job_grades2;
7、截断表(清空表数据):SQL> truncate table job_grades2;
8、删除表:SQL> drop table job_grades2;
 
非空约束:
1、创建表时:SQL> create table dept80(id number,name varchar2(20) not null,salary number constraint dept80_un not null);
2、修改表时:SQL> alter table dept80 modify location_id not null;
唯一性约束:Unique
主键约束:primary key
外键约束:foreign key    SQL> create teble dept40(id number,d_id number,constraintdept40_fk foreignkey(d_id reference dept60(id)));
check约束:SQL> create table dept10 (id number,salary number(8,2) check (salary>2000));
查看约束信息:SQL> select constraint_name,constraint_type,search_condition from user_constraints where lower(table_name)='dept40';
禁用约束:SQL> alter table dept60 disable constraint 约束名 cascade;  cascade为外键约束时添加
启用约束:SQL> alter table dept60 enable constraint 约束名 ;
 
 
top-n分析
显示薪水最高的3个人:select rownum,last_name,salary from(select last_name,salary from employees order by salary desc) where rownum<=3;
 
创建序列:

 

 SQL> create sequence dept_seq increment by 10 start with 120 maxvalue 9999 nocache nocycle;(无,分割)

 

单行索引:SQL> create index emp_index on employees(last_name);

复合索引:SQL> create index dept_man_loc on departments(manager_id,location_id);

函数索引:SQL> create index dept_upper on departments (upper(department_name));

删除索引:SQL> drop index dept_upper;

同义词:SQL> create synonym em for employees;

删除同义词:SQL> drop  synonym em;

 

导入/导出

exp|imp 用 户 名 / 密 码 @ 连 接 地 址 : 端 口 / 服
务 名 file= 路 径 / 文 件
名 .dmp full=y|tabels(tablename,tablename...)|owner(username1,username2,username3)
exp:导出命令,导出时必写。
imp:导入命令,导入时必写,每次操作,二者只能选择一个执行。
username:导出数据的用户名,必写;
password:导出数据的密码,必写;
@:地址符号,必写;
SERVICENAME:Oracle 的服务名,必写;
1521:端口号,1521 是默认的可以不写,非默认要写;
file="文件名.dmp" : 文件存放路径地址,必写;
full=y :表示全库导出。可以不写,则默认为 no,则只导出用户下的对象;
tables:表示只导出哪些表;
owner:导出该用户下对象;
full|tables|owner:只能使用一种;

 

 

 

 

 
 
 
posted @ 2021-05-14 16:35  马士怡  阅读(128)  评论(0)    收藏  举报