一、SQL基本操作
1.1、 数据类型
字符型：
varchar2（最常用的 长度可变的字符串）char（定长）ncharnvarchar2(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 keyCREATE 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子句部分 -
HAVINGcoding 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一起使用
- 不能用于全外连接
- 如果有多个条件，每个条件都要使用
- 不建议使用
- left join左外连接
