01oracle学习笔记

一、简单语句：

 1、创建表：

                create table employee(

                                        id  NUMBER(4), primary key(设置主键)

                                        name VARCHAR2(20),default  'M',

                                        birth DATE,

                                        salary   NUMBER(10) NOT NULL,(必须有值)

                                       deptno  NUMBER(2))；

            其中default为设置指定字段的默认值，若没有设置默认值，无论什么类型都默认为NULL，

          若后面写了NOT NULL，则在你插入数据的时候就不能不写值了，否则会报错。

           1=1，1=2的使用，在SQL语句组合时用的较多

　　  “where 1=1” 是表示选择全部 “where 1=2”全部不选，

2、 删除表：

                        sql="delete  from m_message where mId=?";

      

3、更新表：

                       sql="update m_message set mgood=?where mId=?";   

                        update   employee  set   salary = 100   where  name = 'll '

                      

                             更新 10号员工的工作和工资使其与250号员工 相同。

                        update  employee  set    job_id  = (select  job_id  from    employees  where  employee_id = 250),

                                                                     salary  = (select  salary  from  employees  where  employee_id = 250

                                                                     where employee_id    = 10;

4、插入表：

                            sql="insert into m_message(mId,mcontent,mauthor,mtitle,mpubDate,mgood,heading) values(?,?,?,?,sysdate,?,0)";

                       insert into  employee(id,name,gender,birth,salary)values(1,'gg','n',  to_date('2017-10-25','YYYY-MM-DD'),90)；

                      从已有表拷贝：insert into emp1 select * from emp where sal< 1000;

 

5、查询表：

                             sql= "Select * from employees where department_id = 9"；

                      ①查询那几个值：select * from employees where department_id in(90, 50)

                      ②查询相似的值：select first_name from employees where first_name like 'S_';

6、排序：

                      select  *  from employees orderby hire_date desc；（asc升序）

                      select * from employees order byhire_date desc, salary desc

                      sql="select * from (select t.*,rownum  num  from m_message t) A where A.num>? and A.num<=?";(分页用的)

7、更改表：

                     在表增加salary：  alter table emp add(salary date default sysdate);

                     在表增加一列       alter table  message  add column headimg

     

                      删除某一字段：    alter  table  employee drop(hiredate);

                      修改某你字段：      alter  table  employeemodify(job varchar2(40) default 'clerk');

                          添加主键：         alter table tabname add primary key(col)
 
　　                   删除主键：         alter table tabname drop primary key(col)

                          创建索引：         create [unique] index idxname on tabname(col….)

　　                    删除索引：         drop index idxname

8、函数：

                            select max(salary) fromemployees;

                     select sum(salary) fromemployees;

                     select avg(salary) fromemployees;    

                     select count(salary) fromemployees;（count（*）所有）

显示每个部门的平均工资和最高工资：

Select avg(sal),max(sal),deptno from emp group by deptno;

9、子查询：

                     select  name,salary  from   employees  where  salary > (

                                                                                                             select  salary  from   employees

                                                                                                               where   name = 'A' )

10、分页：  

              

                             select * from(  select  rownum  rn, employee_id, salary

                                                           from(   select  employee_id, salary , last_name  from  employees  order by salary desc)

                                                                               )where rn <=50 and rn >40

                    蓝色部分被看做一张新表。

 

                   分页在oracle中需要三步：

                                    1、确定查询数据，并且排序（第一次子查询）

                                    2、对数据进行编号（第二次子查询）

                                     3、根据行号取范围（最外层查询）

                                           根据页数page，每页条目数pageSize

                                           计算范围的公式：start：（page-1）*pageSize+1 end：page*pageSize

11、主键： 

                        每张表的第一个字段应当是主键，主键应当是与该表要保存的数据无关的内容，  

                        主键字段在每条记录中的值都不能一样，并且不能为null，这样该值可以唯一标识该表的一条记录。

  

                       主键生成的两种方式：

                       数字：使用序列生成即可：

                                  Create sequence emp_seq

                                  Start  with 100

                                  Increment  by 10

 

表示生成一个以100开头的依次递增10的序列。序列支持两个伪列

12、索引：

               目的是提高数据库的查询效率。X

 

                     例子代码：

                                   Create index idx_emp_ename on emp（ename）;

                                   如果经常在order by字句中使用job和sal作为排序依据，可以建立复合索引：

                                   Create index idx_emp_ename on emp（job, sal）;

 

                                     创建基于函数的索引：

                                   Create index idx_emp_ename on emp（UPPER(ename)）;

 

                                    有时候我们需要定期的重建索引，以提高索引的空间和利用率。

                            alter index  idx_emp_ename  rebuild；

 

                           删除索引：

                                   Drop index index_emo_ename;

                    合理使用索引提升查询效率：

                             1. 为经常出现在where字句中的列创建索引 

                             2. 为经常出现在order by。Distinct后面的字段建立索引，如果建立的复合索引，

                                 索引的字段顺序要和这些关键字后面的字段顺序一致

                             3. 为经常作为表的连接条件的列上创建索引

                             4. 不要在经常做DML操作的表上建立索引

                             5. 不要在小表上建立索引

                             6. 限制表上的索引数目，索引并不是越多越好。

                             7. 删除很少被使用的。不合理的索引。

13、约束：

           

                            1、 在创建表时，我们可以设置字段的值不能为空，也就称为非空约束

                                  Create table e（  Id varchar2（10） not null     ）

                                                                

                             当设置非空约束后，我们执行更新和插入操作时，就不能插入空值或更新为空值了。 

                      2、唯一约束   也就是不允许插入相同的值。

                                       Create table e（  Id varchar2（10） unique ）

                                                                   

                                     在建表之后添加唯一性约束：

                              Alter table employees Add constraint employees_name_uk unique（name）；

                      3、主键约束  意思是：非空且唯一。 

                                    Create table e（ Id varchar2（10）primary key）

 

                                   在创建表之后添加主键约束：

                               Alter table employees  Add constraint employees_eid_pk PRIMARY KEY(eid)