SQL之基础

2019-03-16 本文已影响0人白璞1024

一、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、约束条件的使用

主键约束

创建表格的时候使用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 ;

外键约束

创建表的时候添加
```
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

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;

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;

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 基本语句

语法

    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排序

查询指定的字段
select id,user,name from user_table
查询所有的字段
select * from user_table;
使用别名替代表中的字段,查出来的字段显示的是汉字
select id AS 用户编号,user AS 用户 ,name AS 用户姓名 from user_table
使用表达式操作查询的字段
查出来的字段根据表达式来展示
select id ,name,age,address,price || '*'||1.23||'='||price*1.23 as new_price from user_table
- || 是连接操作符
- *表示乘号，算最后的结果
函数操作查询字段
select SUBSTR(name,1,5) as 截取后的名字 FROM user_table;
去重复
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一起使用
- 不能用于全外连接
- 如果有多个条件，每个条件都要使用
- 不建议使用

SQL之基础

一、SQL基本操作

1.1、数据类型

1.2、数据定义语言

1.3、约束条件的使用

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

二、SELECT

2.1 基本语句

2.2 查询结果排序

2.3 WHERE部分处理

2.4 GROUP BY HAVING子句

2.5 子查询

2.6 连接查询

猜你喜欢

热点阅读

SQL之基础

一、SQL基本操作

1.1、 数据类型

1.2、数据定义语言

1.3、约束条件的使用

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

二、SELECT

2.1 基本语句

2.2 查询结果排序

2.3 WHERE部分处理

2.4 GROUP BY HAVING子句

2.5 子查询

2.6 连接查询

猜你喜欢

热点阅读

1.1、数据类型

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