sql~~白手起家~~

目录

SQL的用途：

 SQL中的字符串类型定义

SQL的数值类型的定义

sql有三种语言

1.DML---数据操纵语言

2.DDL---数据定义语言

3.DCL---数据控制语言

---

SQL的用途：

 SQL中的字符串类型定义

SQL的数值类型的定义

 SQL的日期类型的定义

 SQL的命名

SQL的三种语言类型 

1.DML---数据操纵语言

（1）insert：插入数据

（2）update：更新数据

（3）delete：删除数据

（4）select:查询数据

2.DDL---数据定义语言

（1）grant table：授予用户访问某表的权限

（2）alter table：修改表的类型。。。。

（3）drop table：删除表

（4）create index：创建视图

（5）drop index：删除视图

3.DCL---数据控制语言

（1）grant:授予权限

（2）revoke:撤销权限

（3）commit:提交事务

（4）rollback：回滚

（5）lock:锁定

（6）savepoint:设置保存点

（SQL是不区分大小写的！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！）

1.数据的导入

SQL>@d:/sql/del_date.sql---保存数据的路径
SQL>@d:/hr_cre.sql;
SQL>@d:/hr_popul.sql;

2.查看表的结构

SQL>desc tablename;

3.去除重复的数据

SQL>select distinct department_id
    from employees;
输出有12行数据，若没有distinct，则输出与107条数据

4.模糊查询

SQL>select last_name
    from employees
    where last_name like'%L%';

5.更新数据要commit，否则无法更新数据

6.排序，需要使用order排序，ASC：升序、Desc：降序

SQL>select last_name,department_id,salary
    from employees
    where department_id=60
    order by salary desc;

7.大小写 lower:小写，upper大写，initcap--首字母大写

SQL>select initcap ('Abc ABCD')
    from dual;

----Abc Abcd


SQL>select lower ('Abc ABCD')
    from dual;

----abc abcd

SQL>select upper ('Abc ABCD')
    from dual;

----ABC ABCD

8.截断函函数

SQL>select substr('Helloworld',1,5)
from dual;

－－－hello

9.instr返回所需字符所在的位置

SQL>select instr('Helloworld','w')
    from dual;

----6

10.lpad--左边补位，rpad---右边补位

SQL>select lpad(1111 ,10,'&')
    from dual;

----&&&&&&1111

SQL>select rpad(1111 ,10,'&')
    from dual;

----1111&&&&&&

11.trim移除首位字符

SQL>select trim ('g' from 'gril')
    from dual;

----ril

12.replace取代出现过的字符

SQL>select replace ('xiaozhang','x','t')
    from dual;

----tiaozhang

13.round四舍五入

SQL>select round(144.789,3)
   from dual;

---144.789(3代表保留的小数位的位数)

14.trunc截断

SQL>select trunc (144.789,1)
    from dual;

---144.7(1代表截断后还保留的位数)

15.mod求余

SQL>select mod(10,6)
    from dual;
---4

16.to_char日期的转换

SQL>select hire_date,employee_id
    from employees
    where to_char(hire_date,'yyyy-mm-dd')='1994-06-07';

----1994/6/7 205

17.to_date字符串的转换

select hire_date,employee_id
from employees
where to_date('1994-06-07','yyyy-mm-dd')=hire_date;

--1994/06/07

17.2---to_number的使用--将char和string or varchar2的转换成number类型

sselect to_number('000012134') from dual;
select to_number('88877') from dual;
select to_number('$12345.678', '$999999.999') from dual;
select to_number('19f','xxx') from dual;---16进制的转换
select to_number('f','xx') from dual; 

18. nvl将空值转换成一个已知的值

SQL>select employee_id,last_name,12*salary*(1+nvl(commission_pct,0)) "annual salary"
    from employees;

----有107条数据

19.nvl2(expr1,expr2,expr3):若expr1不为null，返回expr2，若为null，返回expr3

SQL>select last_name,nvl2(commission_pct,commission_pct+0.015,0.01)
    from employees;
---有107条数据

20.nullif(expr1,expr2)若expr1=expr2,返回null，否则返回expr1

SQL>SELECT first_name, LENGTH(first_name) "expr1",
       last_name,  LENGTH(last_name)  "expr2",
       NULLIF(LENGTH(first_name), LENGTH(last_name)) result 
    FROM employees;
----107行数据

21.条件表达式case(语句中没有标点)

部门号分别为10，20，30给它们的工资分别乘以0.1，0.2，0.3
SQL>select employee_id,last_name,department_id,
         case department_id when 10 then salary * 1.1
                            when 20 then salary * 1.2
                            else salary * 1.3 end new_sal
    from employees
    where department_id in(10,20,30);
---9行数据

22.decode函数

SQL>select employee_id,last_name,department_id,
       decode(department_id,10,salary*1.1,
                            20,salary*1.2,
                            30,salary*1.3,
                            salary) new_sal
     from employees
     where department_id in(10,20,30);

23.多表查询---笛卡尔积(所有内容都交叉连接)

SQL>select last_name,department_name
    from employees,departments;

23.等值连接(起别名)

SQL>select e.employee_id,e.department_id,d.department_name
    from employees e,departments d
    where d.department_id=e.department_id;
----106行数据

24.左连接

SQL>select e.last_name,e.department_id,d.department_name
    from employees e,departments d
    where e.department_id=d.department_id(+)

或
SQL>select e.last_name,e.department_id,d.department_name
    from employees e left outer join department d
    where e.department_id=d.department_id;

24.右连接

SQL>select e.last_name,e.department_id,d.department_name
    from employees e,departments d
    where e.department_id（+）=d.department_id;
或
SQL>select e.last_name,e.department_id,d.department_name
    from employees e right outer join department d
    where e.department_id=d.department_id;

25.满外连接

SQL>select e.last_name,e.department_id,d.department_name
    from employees e full outer join department d
    where e.department_id=d.department_id;

26.自然连接

SQL>select employee_id,department_id,department_name
    from employees e natural join departments d;

27..using创建连接---(不用起别名)

SQL>select employee_id,department_id,department_name
    from employees
    join departments using (department_id);

28.on创建连接

SQL>select employee_id,d.department_id,department_name
    from employees e join departments d
    on e.department_id=d.department_id;

29.group by分组函数，作用于一组函数，返回一个数据的值(在where后面)

SQL>select department_id,avg(salary)
    from employees
    group by department_id;

---12条数据

30.having过滤条件，不能在where中使用

SQL>select department_id,avg(salary)
    from employees
    having avg(salary)>7000
    group by department_id;
---6条数据

31.子查询(先执行外层而后执行内层)

SQL>select department_id,min(salary)
    from employees
    group by department_id
    having min(salary)>(select min(salary)
                    from employees
                    where department_id=50);
---11条数据

32.多行子查询

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');

2.表的创建

1.表的创建--白手起家

SQL>create table emp1(
    id number(10),
    name varchar2(20),
    salary number(10,2),   ------总共有10位，其中有两位是小数位，即整数位8位
    hire_date date
    )

2.表的创建--依托于现有的表

SQL>create table tablename
    as
    select employee_id,last_name,hire_date,salary
    from employees;

3.增加列（修改内容）

1增加一个列(修改表用alter)

SQL>alter table tablename
    add (tel_phone varchar2(20));

2.修改一个列(alter....modify)

SQL>alter table tablename
    modify(tel_phone number(11));

 3.重命名一个列（alter.....rename）

SQL>alter table tablename
    rename cloumn tel_phone to tel;

4.删除内容

1.删除一个列(alter....drop)

SQL>alter table tablename
    drop column tel_phone;

2.删除表(drop)

SQL>drop table tablename;

3.清空表truncate（删除表中所有的数据，释放表的空间，执行后不可以rollback，但delete可以)

SQL>truncate table tablename;

39.rename--可以改变表，视图，序列的名称

SQL>rename tabelname to test1;

40.将表设为不可使用的状态

SQL>alter table test1
    set unused test;

41.插入数据insert

SQL>insert into test1
values(1001,'lili',id,name);
-----只能插入一条数据

42.从其他表中拷贝数据

SQL>insert into test1(employee_id,hire_date,last_name,salary)
    select employee_id,hire_date,last_name,salary
    from employees
    where department_id=80;

43.更新数据(update)

SQL>update test1
    set salary=5000
    where employee_id=90;

44.删除数据（delete）

SQL>delete from employees1
    where department_id=(select department_id
                     from departments
                     where department_name =1);
      

45.创建视图view(使用group by为复杂视图)

SQL>create or replace view test1view
    as
    select dapartment_name,avg(salary) avg_sal
    from employees e,department d
    where e.department_id=d.department
    group by department_name;

46.更新视图

SQL>update test1view
    set salary=9000
    where employee_id=100;

47.top-n 分析(rownum只能使用<,<=其余的都不能使用)

SQL>select rownum,employee_id,last_name,salary
    from(
     select employee_id,last_name,salary
     from employees
     order by salary desc)
     where rownum<=10;

48.创建序列sequence

SQL>create sequence test_seq
    increment by 10---增长的数
    start with 10---开始的数
    maxvalue 100----最大数
    cycle-----循环
    cache----缓存

49.创建索引 index

SQL>create index test_index
    on test(employee_id);

50.创建同义词

SQL>create synonym t for test1;

51.赋予访问权限

SQL>grant create synonym to scoot;

52.创建用户

SQL>create user 11；

53.赋予用户访问权限

SQL>grant cretate session
    to 11;

54.分配表空间

SQL>alter user 11 quota unlimited on 11.kongjian;

55.创建表空间

SQL>create tablespace 12;

56.创建角色

SQL>create role my_role;

57.赋予角色权限

SQL>grant create session,create table,create view to my_role;

58.将角色赋予给用户

SQL>grant my_role to 11;

59.将表的某些权限给用户

SQL>grant select,update
    on scott.employees
    to 11;

60.回收对象权限

SQL>revoke select 
    on employee
    from 11;

61.查询权限的分配情况

SQL>select * from user_tab_privs_recd;

62.union--返回结果的并集（去重）

SQL>select employee_id,department_id
    from employees01
    union 
    select employee_id,department_id
    from employees02

63.union all--返回结果的并集（不去重）

SQL>select *
    from employees01
    union all
    select *
    from employees02;

64.intersect结果的交集

SQL>select employee_id,department_id
    from employees01
    intersect 
    select employee_id,department_id
    from employees02
    order by emp_id;

65.minus结果的差集

SQL>select employee_id,department_id
    from employees01
    minus 
    select employee_id,department_id
    from employees02
    order by emp_id;

66.高级子查询(在from中使用子查询)

SQL>select last_name,e1.department_id,salary,e2.avg_sal
    from employees e1,(select department_id,avg(salary) avg_sal from employees group by         
    department_id) e2
    where e1.department_id = e2.department_id
    and e1.salary>e2.avg_sal;

67.exists使用

SQL>select employee_id,last_name,job_id,department_id
    from employees e1
    where exists (select 'A'
              from employees e2
              where e1.employee_id = e2.manager_id)

68.no exists

SQL>select department_id,department_name
    from departments d
    where not exists(
                 select 'C'
                 from employees
                 where department_id = d.department_id
                 )

69.相关更新

SQL>update test1
    set department_name=(select department_name
                     from employees
                     where department_id=test1.department_id);

70.相关删除

SQL>delete from test2
    where department_id in(select department_id
                       from test1
                       where deaprtment_id=test1.department_id);
                       

71.with子句

SQL>with Abel_sal as(select salary
                 from employees
                 where last_name='Abel')
    select employee_id,salary
    from employees
    where salary>(select salary
              from Abel_sal);

---