SQL之基础

一、SQL基本操作

1.1、 数据类型

• 字符型： varchar2 （最常用的 长度可变的字符串）char（定长） nchar nvarchar2(unicode字符集变长字符型数据) long（使用比较少）

• 数字型：

  • number(p,s)p表示精度，s表示保留小数最大精度38位
  • float 二进制计算精度，最大126转化成十进制的时候需要乘0.30103

• 日期 date、timestamp 和data的区别就是date精确到秒，但是timestamp精确到小数秒，还能显示上午和下午

• 其他的数据类型：

  • blob最多4G（二进制）
  • clob最多4G(字符数据)
  • bfile 大小和操作系统有关系 二进制数据放在数据库意外的操作系统文件中

1.2、数据定义语言

• create 创建表

  CREATE TABLE table_name {
     column_name datatype [null|not null],--设置是否可以为空
     column_name datatype [null|not null],
     ...
     [constraint]-- 设置约束，详细的见约束条件章节
  }
  

• Alter 修改表字段

      ALTER TABLE table_name ADD column_name | MODIY column_name | DROP column
      
      --`DROP column`  删除列，在处理这部分的时候，常常要加上`CASCASDE CONSTRAINTS `删除与该列有关系的约束条件
  

  DROP column 删除列，在处理这部分的时候，常常要加上CASCASDE CONSTRAINTS删除与该列有关系的约束条件

• Drop 删除表

  DROP TABLE table_name;
  

1.3、约束条件的使用

1. 主键约束

   • 创建表格的时候使用primary key

       CREATE TABLE usertable {
           name_id varchar2,
           name varchar2 ,
           value varchar2,
           primary key <name_id>
       }
     

   • 使用ALTER TABLE

       ALTER TABLE table_name ADD CONSTRAINTS constraint_name PRIMARY KEY (column_name);
     
       ALTER TABLE usertable ADD CONSTRAINTS pk_nameid     PRIMARY KEY (name_id);
       --这里的pk_nameid是主键的名称，用来删除的
     

   • 删除主键

     ALTER TABLE table_name DROP CONSTRAINT pk_nameid ;
     

2. 外键约束

   • 创建表的时候添加

     CONSTRAINT constraint_name FOREIGN KEY (column_name) REFERENCE table_name (column_name) 
     ON DELETE CASCADE
     

     • constraint_name：外键约束的名字
     • ON DELETE CASCADE设置级联删除，当主键字段被删除的时候，外键对应的字段也同时被删除

   • 修改数据库表的时候添加外键约束

     ADD CONSTRAINT constraint_name FOREIGN KEY (column_name) REFERENCE table_name (column_name)
     ON DELETE CASCADE;
     

   • 删除外键

         ALTER TABLE table_name DROP CONSTRAINT constraint_name
     

3. CHECK约束
   检查约束，能够规定每一个列能够输入的值，以保证数据的正确性

   • 创建表的时候添加

         CONSTRAINT constraint_name CHECK(condition);
         
         CREATE TABLE usertable {
             name_id varchar2,
             name varchar2 ,
             age number(2),
             
         }
     
         CONSTRAINT chk_age CHECK(age>=18 and age<66) ;
     

   • 修改表的时候添加check约束

         ADD CONSTRAINT constraint_name CHECK (condition);
     

   • 删除CHECK约束

         ALTER TABLE usertable DROP CONSTRAINT chk_age;
     

4. UNIQUE 唯一约束

   • 创建表的时候添加，和上边一样

         CONSTRAINT constraint_name UNIQUE(column_name);
         
         CREATE TABLE usertable {
             name_id varchar2,
             name varchar2 ,
             age number(2),
             
         }
     
         CONSTRAINT unq_naemid UNIQUE(name_id) ;
     

   • 修改表的时候添加check约束

         ADD CONSTRAINT constraint_name UNIQUE (column_name);
     

   • 删除UNIQUE约束

         ALTER TABLE usertable DROP CONSTRAINT chk_age;
     

5. NOT NULL约束

   • ALTER TABLE table_name MODIFY column NOT NULL;

1.4、 数据操纵语言DML和数据查询语言DQL

• 添加数据用INSERT

  • 基本的添加数据的方法

    INSERT INTO     table_name(column_name1,column_name2...) VALUES(data1,data2...)
    

  • 其他表里添加数据的方法

    INSERT INTO table_name1 (column_name1,column_name2,column_name3..)
    SELECT column_name1,column_name2,column_name3 ... FROM table_name2
    

• UPDATE
  UPDATE table_name SET column_name1 = data1,column_name2 = data2 ... [WHERE condition]

• DELETE
  DELETE FROM table_name [where condition]

• SELECT
  select column_name1,column_name2 ... from table_name WHERE [CONDITION];

• 其他的语句

  • TRUNCATE语句: 和delete一样都是用来删除表中数据的，但是TRUNCATE是没有条件的，直接删除 TRUNCATE TABLE table_name
  • MERGE:和update语句的功能比较类似，使用MERGE的同事能够进行添加和修改

        MERGE [INTO] table_name1 USING table_name2 ON (condition) WHERE  MATCHED THEN merge_update_clause WHERE NOT MATCHED THEN merge_insert_caluse;
    

    • table_name1 要修改或者是添加的表
    • table_name2 参考的更新表
    • condition 表关联关系
    • merge_update_clause：如果和表2的条件匹配就执行更新操作的sql语句
    • merge_insert_caluse： 如果条件不匹配，就执行增加操作的sql

    -- 加入有两张表user /userInfo
    select * from user;
    -- name age id
    -- AA   12  3
    -- BB   12  2
    -- CC   12  1
    select * from userInfo;
    -- name  id
    -- baipu   3
    
    --MERGE修改
    MERGE INTO user u USING userInfo i ON u.id = i.id  WHERE MATCHED  THEN UPDATE user set u.name = i.id;
    --查询结果
    select * from user ;
    -- name age id
    -- baipu 12  3
    -- BB   12  2
    -- CC   12  1
    

---

二、SELECT

2.1 基本语句

1. 语法

       SELECT [DISTINCT|ALL] select_list FROM table_list 
       [WHERE where_clause]
       [group by group_by_caluse]
       [HAVING coding]
       [ORDER BY  order_by_calcuse] 
   

   • [DISTINCT|ALL]:全部显示还是去重
   • where_clause 查询where条件部分
   • group_by_caluse: GROUP BY 子句部分
   • HAVING coding HAVING子句部分
   • order_by_clause排序

2. 查询指定的字段
   select id,user,name from user_table

3. 查询所有的字段
   select * from user_table;

4. 使用别名替代表中的字段,查出来的字段显示的是汉字
   select id AS 用户编号,user AS 用户 ,name AS 用户姓名 from user_table

5. 使用表达式操作查询的字段
   查出来的字段根据表达式来展示
   select id ,name,age,address,price || '*'||1.23||'='||price*1.23 as new_price from user_table

   • || 是连接操作符
   • *表示乘号，算最后的结果

6. 函数操作查询字段
   select SUBSTR(name,1,5) as 截取后的名字 FROM user_table;

7. 去重复
   SELECT distinct(name) from production

2.2 查询结果排序

• 语法

  ORDER BY {expr | position | c_alias}[ASC|DESC][NULLS FIRST| NULLS LAST]
  
  [,{expr | position | c_alias}[ASC|DESC][NULLS FIRST| NULLS LAST]]
  

  • expr表达式
  • position:表中列的位置
  • c_alias别名
  • ASC升序、DESC降序
    -[NULLS FIRST| NULLS LAST]对空字符串的处理
• 字段的位置作为排序字段
  select price ,name,quantity from producInfo ORDER BY 3 desc表示按照quantity排序
• c_alias别名排序
  select price 价格 ,name,quantity from producInfo ORDER BY 价格 desc
  表示按照price排序
• 表达式
  select price ,name,quantity from producInfo ORDER BY quantity*price desc
  表示按照price和quantity的乘积排序
• 多个字段
  select price ,name,quantity from producInfo ORDER BY quantity,price desc
  优先按照quantity排序，如果一样就按照price排序

2.3 WHERE部分处理

可以使用关系操作符和比较操作符
关系类的：<,>,<=,>=,=,!=,<>
比较类的：IS NULL ,LIKE BETWEEN...AND IN
逻辑类的： AND OR NOT

SELECT * FROM PRODUCT WHERE 
-- != 和<>用法一样
(
   category <> '010030002'
--使用函数
   AND 
   substr(name,1,3)!= '数据库'
)
-- %表示多个字符 _表示单个字符 NOT表示对结果取反
OR
(
   productName NOT LIKE '%数据库_'
   AND 
   price IN ('1','2','3')
   AND
   qualitity IS NULL
)

2.4 GROUP BY HAVING子句

• group by
  • 基本语法：

        GROUP BY 
        {
            expr|{ROLLUP|CUBE}({expr[,expr]...})
        }
    

    • expr列名
    • ROLLUP|CUBE子句扩展
  • group by 不允许出现在where子句中，但是允许出现在where子句后面
  • gourp by 出现的时候，查询字段智能存在分组函数或者是gourp by 子句中的字段
• having
  一把来说需要和group by 的子句一起使用

  select * from priduct group by category having avg(productprice) >2000;
  

2.5 子查询

• 子查询返回一行的时候

      select * from productinfo where category =
      (
          select categroyid from categoryinfo where categoryname = "U盘"
      );
  

• 子查询返回多行的时候

  -- IN
   select * from productinfo where category IN
      (
          select categroyid from categoryinfo where categoryname = "U盘" or categoryname = "电脑"
      );
  -- ANY
   select * from productinfo where price <
       any (
          select productionPrice from categoryinfo where categoryname = "U盘" or categoryname = "电脑"
      );
  --SOME  some和any的用法差不多，一般来说any都是用在非=的时候上边的例子中用的就是> some用在=的时候选出多种数据来
  select * from productinfo where price =
       some (
          select productionPrice from categoryinfo where categoryname = "U盘" or categoryname = "电脑"
      );
  -- ALL必须小于所有的情况下
  select * from productinfo where price <
       ALL(
          select productionPrice from categoryinfo where categoryname = "U盘" or categoryname = "电脑"
      );
  

2.6 连接查询

• 最简单的查询 select * from a,b查询结果为a表和b表的笛卡尔乘积，没卵用
• 内连接
  把两个表或者是多个表进行连接，只能查出匹配的记录，不匹配的记录无法查出来
  sql select * from productinfo p,categoryinfo c where p.category = c.categoryid; -- INNER　JOIN select * from productinfo p INNER JOIN categoryinfo c ON p.category = c.categoryid;
• 自连接
  把自身的一个引用作为另一个表达式来处理

      select * from productioninfo p,prductioninfo pr 
      where p.id != pr.id
      and p.quantity = pr.quantity
      and p.rowid < pr.rowid
      
      --为了避免重复，直接调用p.rowid 和pr.rowid比较，取比较小的那条 
  

• 外连接
  • left join左外连接

    select * from productinfo p left join categoryinfo c on p.category = c.categoryid;
    -- 上边的sql将会勒出productinfo表的所有记录，然后和categoryinfo中的数据进行匹配
    

  • right join右连接

    select * from productinfo p 
    right join categorinfo c on c.category = p.categoryid;
    -- 将会展示categoryinfo中所有的数据
    

  • FULL JOIN全外连接

     select * from productinfo p 
    FULL join categorinfo c on c.category = p.categoryid;
    -- 将会展示categoryinfo和productinfo中所有的数据
    

  • （+）的使用
    简化版本的左连接和右连接

    select * from productinfo p,categorinfo c where c.category(+) =  p.categoryid;
    
       -- +在非主表的一方
    

    • 只能在where语句中，不能同outer join一起使用
    • 不能用于全外连接
    • 如果有多个条件，每个条件都要使用
    • 不建议使用