SQL

DQL:数据查询语言
DML:数据操纵语言
DDL:数据定义语言
DCL:数据控制语言




describe / desc s_emp(描述一张表的结构)

char()   定长字符串
varchar()  变长字符串
varchar2()  变长字符串   Oracle特有

date:年月日时分秒  世纪  毫秒  星期  季度
    时间戳(1970年1月1日至今)

select last_name,salary,commission_pct
from s_emp;

select last_name,start_date,salary
from s_emp;

col last_name for a20(调节宽度)

/  (sqlplus缓冲区中的sql命令)(执行上一条sql语句)

select * from s_dept;

select 10+20
from dual;

所有员工每年的工资是多少
select last_name,salary*12
from s_emp;

select last_name,(salary+500)*12+1000
from s_emp;

select last_name,(salary+500)*12+1000 as "total"
from s_emp;

select first_name||last_name,salary,start_date
from s_emp;

col first_name||" "||last_name for a30

查询员工信息 员工每个月工资(考虑提成比例)
select last_name,salary*(1+NVL(commission_pct,0)/100)
from s_emp;

查询公司的所有部门
select DISTINCT name,region_id   //去重
from s_dept;

select last_name,title
from s_emp;







--------------★排序，筛选行--------------
select last_name,salary,title
from s_emp
order by salary(asc升序   desc降序);

order by 可以添加的子项
1.列名(不论在select子句中是否出现过)
2.别名
3.表达式
4.select子项的编号(从1开始)

NULL值最大

select last_name,commission_pct
from s_emp
order by 2;


select  筛选列      2
form    查整张表    1
where   筛选行      3  
order by  排序      4

select last_name,title,dept_id
from s_emp
where dept_id=42;

select first_name,last_name,title
from s_emp
where last_name = 'Magee';

查询工资是1500-2000之间的员工信息
select last_name,salary
from s_emp
where salary between 1500 and 2000;

查询39 41 43 号  部门的员工信息
select last_name,title,
from s_emp
where dept_id in (39,41,43);

查询名字以_开头的员工信息
select last_name
from s_emp
where last_name like '\_%' escape '\';

查询没有提成比例的员工信息
select last_name,title,commission_pct
from s_emp
where commission_pct is null;

查询41 43号部门工资在1000-1200员工信息
select last_name,salary,dept_id
from s_emp
where (dept_id = 41 or dept_id = 43) and (salary between 1000 and 1200);









--------------★单行函数--------------
select lower(last_name),upper(first_name),initcap('hello world')
from s_emp;

select concat(concat(first_name,' '),last_name)
from s_emp;

substr(last_name,start,length)
查询所有员工last_name后两个字母
select substr(last_name,length(last_name)-1,2)
from s_emp;

查询名字中包含tom(不区分三个字母大小写)的员工信息
select last_name,salary
from s_emp
where upper(last_name) like '%TOM%';

select round(45.932,-2),
round(45.932,-1),
round(55.932,-2),
trunc(45.932,0),
trunc(45.932,-1)
from dual;
dual ---> 哑表  dummy

select sysdate
from dual;

select systimestamp
from dual;

select rownum,rowid,last_name
from s_emp;

select months_between('01-9月-95','11-1月-94')
from dual;

to_date('01-9月-95','DD-MON-YY')
select ROUND(to_date('15-9月-95','DD-MON-YY'),'MONTH')
from dual;

select to_char(sysdate,'YYYY,MM,D,DD,DDD,YEAR,MONTH,ddsp,ddspth,DAY,DY,HH24:MI:SS AM')
from dual;

select　to_char(salary,'L999,999.999')
from s_emp;

select to_number('1234567890')
from dual;

select to_date('01-9月-17')
from dual;







--------------★多表查询--------------
1.连接查询　　　要查询的数据来自于多张表
1) 等值连接和不等值连接(内连接,自然连接)
    内连接只适用于查询外键没有null值列的数据
2) 外连接(左,右,全)
3) 自连接

查询一下员工信息以及所在部门的名称  
select last_name,salary,name
from s_emp,s_dept
where s_emp.dept_id = s_dept.id;

select s_emp.last_name,s_emp.salary,s_dept.name
from s_emp,s_dept
where s_emp.dept_id = s_dept.id;

select e.last_name,e.salary,d.name
from s_emp e,s_dept d
where e.dept_id = d.id;

查询员工信息，所在部门名称，部门所在区域名称
　　s_emp         s_dept        s_region
select e.last_name,d.name,r.name
from s_emp e,s_dept d,s_region r
where e.dept_id = d.id and d.region_id = r.id;

查询所有员工信息和部门名称(即便某些员工没有部门)
插入部门员工
insert into s_emp(id,last_name)
values(99,'ligoudan');
左外连接--->把左表中的所有数据都查出来，不论是否建立连接。
    join关键字左边的表就是左表
select e.last_name,d.name
from s_emp e left outer join s_dept d
on e.dept_id = d.id;

查询所有部门员工信息，即便某些部门没有员工
右外连接
select e.last_name,d.name
from s_emp e right outer join s_dept d
on e.dept_id = d.id;

全外连接
select e.last_name,d.name
from s_emp e full outer join s_dept d
on e.dept_id = d.id;

简化写法　　Oracle特有
左外
select e.last_name,e.salary,d.name
from s_emp e,s_dept d
where e.dept_id = d.id(+);
右外
select e.last_name,e.salary,d.name
from s_emp e,s_dept d
where e.dept_id(+) = d.id;

自连接
查询员工信息以及每个员工的经理的名字
select e.id,e.last_name name,m.last_name manager
from s_emp e,s_emp m
where e.manager_id = m.id;




--------------★集合操作符--------------
union(并集)     union all(并,交集出现两次)     minus(补集)    intersect(交集)

select e.last_name,e.salary,d.name
from s_emp e,s_dept d
where e.dept_id = d.id(+)
union
select e.last_name,e.salary,d.name
from s_emp e,s_dept d
where e.dept_id(+) = d.id;

select e.last_name,e.salary,d.name
from s_emp e,s_dept d
where e.dept_id = d.id(+)
union all
select e.last_name,e.salary,d.name
from s_emp e,s_dept d
where e.dept_id(+) = d.id;

select rownum,last_name
from s_emp
where rownum <= 20
minus
select rownum,last_name
from s_emp
where rownum <= 10;

select e.last_name,e.salary,d.name
from s_emp e,s_dept d
where e.dept_id = d.id(+)
intersect
select e.last_name,e.salary,d.name
from s_emp e,s_dept d
where e.dept_id(+) = d.id;




--------------★多行函数--------------                                               select       5
select avg(salary),min(salary),stddev(salary)                       from         1
from s_emp;                                                                                where        2
                                                                                                       group by     3
select min(last_name),max(last_name)                               having       4
from s_emp;                                                                               order by     6

select count(*)
from s_emp;

查询每个部门的平均工资
select dept_id,avg(salary)
from s_emp
group by dept_id;

查询平均工资>1100的部门id
select dept_id,avg(salary)
from s_emp
group by dept_id
having avg(salary) > 1100;




--------------★子查询--------------
查询和Smith在同一个部门的员工信息
select dept_id
from s_emp
where last_name = 'Smith'
X
select last_name,dept_id
from s_emp
where dept_id in (select dept_id
         from s_emp
         where last_name = 'Smith');

查询11-20（分页查询）
select rownum r,last_name
from s_emp
      ↓
select r,last_name
from (select rownum r,last_name
      from s_emp)
where r between 11 and 20;






--------------★替换变量(Oracle特有)--------------      一般是放在脚本里用
select last_name,dept_id
from s_emp
where dept_id = &var;

select last_name,dept_id
from s_emp
where last_name = &var;           where last_name = '&var';


替换变量指定默认值:
define var = '''Smith'''
指定默认值之后怎么再次接收别的值
accept var prompt '请输入名字:'






--------------★DDL--DML--------------







E-R图转化成表的思维步骤

1 每一个实体 要转化成一张表


2 实体与实体之间的关系 用表怎么表示
   转化成表之后用 外键来表示关系
   1:1
    外键加到任何一张表上
   1:n
        外键加到多的一方
   n:n
    构建一张桥表，桥表中 两列外键
    分别指向两张表

3 实体中的属性，转化成表中的列
 要考虑属性数量够不够



*范式
1.所有的属性都是单一的值，行和列交叉的地方只有唯一的一个值
2.所有的列必须要依赖与实体中的主键，每一列只能通过主键列去唯一标识
3.表中的非主键列不能依赖于其他非主键列




--------------★创建表-----------------
主键约束  primary key
    既可以写成列级约束，也可以写成表级约束
drop table customer;
create table customer(
    id number(7),
    name varchar2(25),
    phone varchar(20),
    emp_id number(7),
    primary key(id)
);
外键约束  foreign key... references...
    既可以写成列级约束，也可以写成表级约束
drop table customer;
create table customer(
    id number(7),
    name varchar2(25),
    phone varchar(20),
    emp_id number(7),
    primary key(id),
    foreign key(emp_id) references s_emp(id)
);
非空约束  not null
    只能写成列级约束
唯一约束  unique
    既可以写成列级约束，也可以写成表级约束
drop table customer;
create table customer(
    id number(7),
    name varchar2(25),
    phone varchar(20),
    emp_id number(7),
    primary key(id),
    foreign key(emp_id) references s_emp(id),
    unique(phone)
);
检查型约束  check
    既可以写成列级约束，也可以写成表级约束

删除表的语句
drop table customer;
创建表的语句
create table customer(
    id number(7) constraint cus_id_pk primary key,
    name varchar2(25) constraint cus_name_nn not null,
    phone varchar(20) constraint cus_phone_uq unique,
    emp_id number(7) constraint cus_emp_fk references s_emp(id)
);
insert into customer
values(1,'jack','2353645364',10);

联合主键
多个列组合起来成为主键
多个列的组合唯一，每个列都是非空
create table t_s_tab(
    t_id number(7),
    s_id number(7),
    primary key(t_id,s_id)
);

默认值
create table stu(
    id number(7),
    name varchar2(25) default '李狗蛋'
);
insert into stu(id)
values(10);

创建表可以使用子查询
原表中只有 not null约束会复制到新表中
create table jd1710_emp
as
select id,last_name,salary
from s_emp;







--------------★数据字典表---Oracle特有--------------
查询root用户，创建的所有表(表名)
数据字典表，规模庞大
dictionary
1.通过模糊查询(从需求中提取关键字)，从dictionary找到 相关的若干张系统表
    select table_name
    from dictionary
    where upper(table_name) like 'USER%TABLE%';
2.使用desc命令  查看这些系统表有哪些列
    desc user_tables
3.选取适合的列进行查看
    select table_name,num_rows
    from user_tables;

查询root用户，创建的所有视图
    select table_name
    from dictionary
    where upper(table_name) like 'USER%VIEW%';






-----------------★操作数据------------------
insert into jd1710_emp
values(30,'Jack',2000);    

insert into jd1710_emp(id,last_name)
values(11,'Tom');    

create table jd1710_customer(
    id number(7) primary key,
    name varchar2(20)
);
create table jd1710_order(
    id number(7) primary key,
    name varchar2(20),
    customer_id number(7) references jd1710_customer(id)
);
insert into jd1710_customer
values(1,'Jack');
insert into jd1710_customer
values(2,'Tom');
insert into jd1710_order
values(1,'食品订单',2);

插入数据可以写子查询
jd1710_emp

insert into jd1710_emp(id,last_name,salary)
select id,last_name,salary
from s_emp
where salary > 1200;


更新数据
urdate jd1710_emp
set last_name = 'Jack';

删除数据
customer    order
delete from jd1710_customer
where id = 1; 不能删除   id = 1被引用
1.级联删除  在删除命令后添加关键字
delete from jd1710_customer
where id = 1 cascade;
2.创建含有外键表的时候，在外键后面就指定级联删除
drop table jd1710_order;
create table jd1710_order(
    id number(7) primary key,
    name varchar2(20),
    customer_id number(7) references jd1710_customer(id) on delete cascade
);
insert into jd1710_order
values(1,'食品订单',1);

事务控制
事务：一组sql命令的集合
    代表业务逻辑中的一个行为，这个行为涉及到的sql命令会有多条
一个事务中可以包含多条DML,SELECT
一个事务中只能包含一条DDL,DCL
怎么开启事务:只要是执行DML或者执行select

事务的特性:ACID
A:Atomicity 原子性    即一个事务下的n个复合操作，要么全部成功，要么全部失败
C:Consistency 一致性  即要求事务做完后，要求满足数据库的一些完整性约束，如：记录的主键约束。
    从业务层理解是：A帐户转钱给B帐户100元钱，这时数据库事务就必须保证A帐户钱减100,B帐户加100,且最终a,b帐户余额也是正确的
I:Isolation 隔离性   两个事务之间不能发生信息的交换
D:Durability 持久性   事务完成后，事务中所操作的数据会被持久保存

commit
事务的提交
会话：一次新的连接，连接到服务端
提交事务，只有事务提交以后，事务中操作的数据才能持久化保存，别的会话才能看到

1.oracle的sqlplus事务需要手动提交
2.mysql的客户端会自动提交事务
3.jdbc会自动提交事务

哪些行为能触发事务提交
1.DDL DCL会触发
2.正常退出sqlplus  exit

rollback
事务的回滚(事务结束)，在事务没提交之前都可以使用rollback让数据回归到事务开启前的状态
rollback to 不会让事务结束
savepoint
保存点，记录某些sql语句执行的结果可以在回退的时候，不恢复原始状态
当事务结束时，该事务内声明的





--------------------★修改表结构以及约束------------------------
添加新列
alter table jd1710_emp
add(
    first_name varchar2(25) unique
);
删除列  考虑外键
alter table jd1710_emp
drop column first_name;
修改列  只能修改列的数据类型，和添加删除not null约束，如果原列中有null值不能添加not null约束

添加约束
alter table jd1710_emp
add constraint emp_salary_uq unique(salary);
删除约束



删除表
drop table ...
修改表名
rename ... to ...
修改列名
alter table ...
rename column salary to money

清空表空间
truncat table ...;
DDL
delete from ...;
DML
delete不会立刻删除数据，而是把要删除的数据添加一个标记，
select语句不会显示出带有删除标记的数据，从而得到删除的效果





---------------------序列-->用于生成主键------------------------------
使用方式类似于Random
  new.Random().nextInt(10);
1.创建序列
2.通过nextval关键字取得序列中的下一个值
create sequence emp_sequence start with 1 increment by 1;






---------------------★视图--------------------------
视图是表的一种映像
create view 视图名
as
子查询

简单视图
   创建视图使用子查询，只来自一张表
   仅用了where和order by
 查
 增删改
 1.如果对视图进行操作，会影响原表
     update v_emp
     set last_name = 'hello world'
     where id = 5;
 2.如果对原表进行操作，会影响视图
    update s_emp
    set last_name = 'Tom'
    where id = 1;

create view v_emp
as
select id,last_name,salary
from s_emp
where salary > 1500;
复杂视图
   创建视图使用的子查询包含了group by或者有连接查询
 create view v_emp_dept
 as
 select e.id id,e.last_name name,e.salary salary,d.name dept_name
 from s_emp e,s_dept d
 where e.dept_id = d.id;
查
增删改---->不允许

修改视图
or replace
create or replace view v_emp
as
select id,last_name,title
from s_emp;

设置视图为只读(在简单视图后加)
with read only

with check option 检查不允许更改建立视图所用的条件列
create or replace view v_emp
as
select id,last_name,title
from s_emp
where last_name = 'Jack';

删除视图
drop view 视图名





-----------------------★创建索引------------------------------
索引是添加在列上的一种数据库对象
目的是提高针对于此列的查询效率