02. sql note.txt

在达内培训java笔记

外链接分左链接和右链接:
    会使用一方表中的所有记录去和另一格表中的记录按条件匹配，空值也会匹配，这个表中的所有记录都会显示，
    数据库会模拟出记录去和那些不匹配的记录匹配。
左链接  加号在右面 
    如：有 TABLE1   TABLE2
        1的一条记录在2里面没有匹配上，那么1里面的记录保留
        2的一条记录在1里面没有匹配上 ，那么2丢弃
右链接正好相反
    --例题：哪些人是领导。
    select distinct b.id,b.last_name manager
    from s_emp a,s_emp b
    where a.manager_id=b.id(+);
左右顺序有区别，这是另外新建一个表，要显示的是第二个表格的内容。
+放在没有匹配行的表一侧，令表格能完整显示出来。

标准写法：内连接用INNER，左连接用LEFT，右连接用RIGHT。
    select distinct b.id,b.last_name manager
    from s_emp a LEFT join s_emp b
    ON a.manager_id=b.id;


十、组函数：
    分组允许将数据分为多个逻辑组，以便能对每个组进行聚集计算。
Group：分组
Group by：分组。(默认按升序对所分的组排序；想要降序要用 order by)可以包括任意数目的列。
    如果嵌入了分组，数据将在最后规定的分组上进行汇总。
    GROUP BY 子句中列出的每个列都必须是检索列或有效的表达式，但不能是聚集函数。
    *如果在SELECT 中使用表达式，则必须在GROUP BY子句中指定相同的表达式，不能使用别名。
    除聚合计算语句外，SELECT语句中的每个列都必须在GROUP BY子句中给出。
    如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列中有多行NULL，它们将分为一组。

Having：过滤。分组之后，不能再用where，要用having 选择过滤。Having不能单独存在，必须跟在group by后面。
    WHERE在数据分组前进行过滤，HAVING在数据分组后过滤。
    可以在SQL中同时使用 WHERE和HAVING，先执行WHERE，再执行HAVING。


  聚合函数：
AVG：平均值    (忽略值为NULL的行，但不能用 AVG(*))
COUNT：计数    (Count(列)不计算空值；但 COUNT(*)表示统计表中所有行数，包含空值)
MAX：最大值    (忽略列值为 NULL 的行。但有些DBMS还允许返回文本列中的最大值，
               在作用于文本数据时，如果数据按照相应的列排序，则 MAX()返回最后一行。)
MIN：最小值    (忽略值为 NULL 的行。不能用 MIN(*)。一般是找出数值或者日期值的最小值。
               但有些DBMS还允许返回文本列中的最小值，这时返回文本最前一行)
SUM：求和      (忽略值为 NULL 的值。SUM 不能作用于字符串类型，而 MAX()，MIN()函数能。也不能 SUM(*))


子查询：查询语句的嵌套
    可以用于任意select 语句里面，但子查询不能出现 order by。
    子查询总是从内向外处理。作为子查询的SELECT 语句只能查询单个列，企图检索多个列，将会错误。
    如：找出工资最低的人select min(last_name),min(salary) from s_emp;
       或者用子查询select last_name,salary from s_emp where salary=(select min(salary) from s_emp);

E-R图：属性: E(Entity) -R(Relationship)
        * (Mandatory marked 强制的)   强制的非空属性
        o (Optional marked 可选的)    可选属性(可以有值也可以没有)
        #* (Primary marked )         表示此属性唯一且非空  

约束：针对表中的字段进行定义的。
PK：primary key (主键约束，PK=UK+NN)保证实体的完整性，保证记录的唯一
    主键约束，唯一且非空，并且每一个表中只能有一个主键，有两个字段联合作为主键，
    只有两个字段放在一起唯一标识记录，叫做联合主键(Composite Primary Key)。
FK：foreign key (外建约束)保证引用的完整性,外键约束,外键的取值是受另外一张表中的主键或唯一值的约束,不能够取其他值,
    只能够引用主键会唯一键的值，被引用的表，叫做parent table(父表)，引用方的表叫做child table(子表);
    child table(子表)，要想创建子表，就要先创建父表，后创建子表，记录的插入也是如此，先父表后子表，
    删除记录，要先删除子表记录，后删除父表记录，
    要修改记录，如果要修改父表的记录要保证没有被子表引用。要删表时，要先删子表，后删除父表。
U：unique key(唯一键 UK)，值为唯一，不能重复。
    在有唯一性约束的列，可以有多个空值，因为空值不相等。
NN：NOT NULL，不能为空。
    index(索引)是数据库特有的一类对象，实际应用中一定要考虑索引，view(示图)

    数量关系:  一对一关系
              多对一关系
              一对多关系
              多对多关系

范式：
	好处：降低数据冗余；减少完整性问题；标识实体，关系和表
    第一范式(First normal form：1Nf)，每一个属性说一件事情。所有的属性都必须是单值，也就是属性只表示单一的意义。
                   (记录可以重复，会有大量冗余，没有任何限制)
    第二范式(2N范式)，最少有一个属性要求唯一且非空PK，其他跟他有关联(记录不可重复，但是数据可能会出现冗余)。
    第三范式(3N范式)，非主属性只能依赖于主属性,不能依赖于其他非主属性。(解决数据冗余问题,不能存在推理能得出的数据)
                   一般情况会做到第三范式。


创建表： Create Table 表名
        (字段名1 类型(数据长度)(default ...) 约束条件,
        字段名2  类型(数据长度) 约束条件 );
建表的名称：
    必须字母开头；最多30字符；只能使用“A～Z、a～z、0～9、_、$、#”；
    同一目录下不能有同名的表；表名不能跟关键字、特殊含意字符同样。
    如：create table number_1 (n1 number(2,4), n2 number(3,-1), n3 number);
       create table t_sd0808(id number(12) primary key,name varchar(30) not null);
    MySQL的： create table student (oid int primary key, ACTNO varchar(20) not null unique,
       BALANCE double); --MySQL的number类型分小类了，Oracle只有number，且MySQL的数值型不用定大小
    Oracle的： create table t_ad (oid number(15) primary key, 
       ACTNO varchar(20) not null unique,BALANCE number(20));

INSERT：插入(或添加)行到数据库表中的关键字。
    插入方式有以下几种：插入完整的行；插入行的一部分；插入某些查询的结果。
    对于INSERT操作，可能需要客户机/服务器的DBMS中的特定的安全权限。
     插入行(方式一)   INSERT INTO products VALUES(2008,’TV’,222.22,’US’);
    依赖于表中定义的顺序，不提倡使用。有空值时需要自己补上。
     插入行(方式二)   INSERT INTO products(id,name,price,vend_name) VALUES(2008,’TV’,222.22,’US’);
    依赖于逻辑顺序，会自动补上空值，提倡使用。

    插入检索出的数据：可以插入多条行到数据库表中
        INSERT INTO products(*,*,*,*)
        SELECT *,*,*,*
        FROM products_copy;
    如果这个表为空，则没有行被插入，不会产生错误，因为操作是合法的。
    可以使用WHERE加以行过滤。

复制表：   将一个表的内容复制到一个全新的表(在运行中创建，开始可以不存在)
    CREATE TABLE 新表名  AS
    SELECT *
    FROM 表名;

    INSERT INTO 与 CREATE TABLE AS SELECT 不同，前者是导入数据，而后者是导入表。
    任何SELECT选项和子句都可以使用，包括WHERE和GROUP BY。
    可利用联接从多个表插入数据。不管从多少个表中检索数据，数据都只能插入到单个表中。

更新数据 UPDATE 语句
            需要提供以下信息：要更新的表；列名和新值；确定要更新的哪些行的过滤条件。
    UPDATE 表名
    SET    vend_name = ‘HP’,
           prod_name = ‘NEWCOMPUTER’
    WHERE  vend_name = ‘IBM’;
    --UPDATE 语句中可以使用子查询，使得能用SELECT语句检索出的数据更新列数据。也可以将一个列值更新为 NULL。

删除数据 DELETE 语句
    DELETE
    FROM products
    WHERE prod_name = ‘COMPUTER’;
    全行删除，不要省略WHERE，注意安全。
    DELETE不需要列名或通配符。删除整行而不是删除列。DELETE是删除表的内容而不是删除表。
    如果想从表中删除所有内容，可以使用TRUNCATE TABLE语句(清空表格)，它更快。

数字字典表：
Sequence:排列。存储物理地址

Index:索引。依附于表，为提高检索速度。

View:视图。看到表的一部分数据。
    限制数据访问。简化查询。数据独立性。本质上是一个sql查询语句。
    Create[or Relace][Force|noForce]  View  视图名
         [(alias[,alias]…)]    别名列表
        As subquery
    [With Check Option [Constraint ……]]
    [With Read Only]
    注意：有些DBMS不允许分组或排序视图，不能有 Order by 语句。可以有 Select 语句。
    删除视图：    DROP VIEW 视图名

Rownum:纬列。内存里排序的前N个。
    在where语句中，可以用＝1，和<=N 或 <N；但不能用＝N 或 >N。
    因为这是内存读取，没有1就丢弃再新建1。只能从1开始。需要从中间开始时，需二重子rownum语句需取别名。
经典应用： Top-n Analysis  (求前N名或最后N名)
          Select [查询列表], Rownum
          From (Select  [查询列表(要对应)]
                   From 表
                   Order by  Top-N_字段)
          Where Rownum <= N

分页显示：
    --取工资第5～10名的员工(二重子rownum语句，取别名)
    select  rn,id,last_name,salary 
    From ( select  id,last_name,salary,Rownum rn
           From (Select id,last_name,salary 
                     from s_emp
                     order by salary desc)
    where rownum <= 10)
    where rn between 5 and 10;


Union：合并表
    Select …   Union   Select…    把两个Select语句的表合并。
    要求两表的字段数目和类型按顺序对应。合并后的表，自动过滤重复的行。
Intersect：交。    同上例，把两个Select表相交。
Minus：减。        把相交的内容减去。
not exists        除运算。


添加字段(列)：
    Alter Table 表名
    Add (column dataype [Default expr][Not Null]
         [,column datatype]…);
    添加有非空限制的字段时，要加Default语句
    字段名字不可以直接改名，需要添加新字段，再复制旧字段后删除旧字段。
      添加约束：    Alter Table 表名
                  Add [CONSTRAINT constraint] type (column);
    添加非空约束时，要用Modify语句。
    查看约束名时，可以违反约束再看出错提示；或者查看约束字典desc user_constraints

减少字段：
    Alter Table 表名
    Drop (column [,column]…);
      删除约束：    Alter Table 表名
                  Drop CONSTRAINT  column;
      或：   Alter Table 表名
            Drop  Primary Key  Cascade;

暂时关闭约束，并非删除：
    Alter Table 表名
    Disable CONSTRAINT  column  Cascade;
打开刚才关闭的约束：
    Alter Table 表名
    Enable  CONSTRAINTcolumn;

修改字段：
    Alter Table 表名
    Modify  (column dataype [Default expr][Not Null]
             [,column datatype]…);
修改字段的类型、大小、约束、非空限制、空值转换。

删除表：
会删除表的所有数据，所有索引也会删除，约束条件也删除，不可以roll back恢复。
    Drop Table 表名 [Cascade Constraints];
    加 [Cascade Constraints] 把子表的约束条件也删除；但只加 [Cascade]会把子表也删除。

改表名：
Rename 原表名 To 新表名;

清空表格：
    TRUNCATE TABLE 表名;
    相比Delete,Truncate Table清空很快，但不可恢复。清空后释放内存。
    Delete 删除后可以roll back。清空后不释放内存。