- -- 很少支持
- 段落注释
SELECT
检索数据
| 语法 | 作用 | 例子 | 释义 |
|---|---|---|---|
| select | 查找列，并返回行 | select prod_name from products； #可使用,分隔列名来查找多个列。 |
查找prod_name列，并返回其下的所有行，在products表中。 |
| * | 通配符 | select * from products； | 查找所有列并返回所有行，在products表中。 |
| distinct | 返回不重复的值 | select distinct vend_id from products； #不可配合通配符使用除非所有列完全相同 |
查找vend_id列并返回其下所有行中不重复的值，在products表中。 |
| limit | 限制 | select prod_name from products limit 5,5; | 查找prod_name列并返回其下第5行起5行的值，在products表中。 |
排序检索数据
| 语法 | 作用 | 例子 | 释义 |
|---|---|---|---|
| order by | 排序 | select prod_id,prod_price,prod_name from Products order by 2; #默认升序（ASC）排列 #指定按多个列排列时：仅当指定的第一列中有重复元素时，才对其（存在重复值的）按指定的下一列进行排序。 |
即按照查找的第二个列进行排序，也可指定列名（prod_price） |
| desc | 降序 | select prod_id,prod_price,prod_name from Products order by 2 DESC,3 desc; |
即按照查找的第二个列进行降序排序，desc仅对其前的列有效； |
过滤数据
| 语法 | 作用 | 例子 | 释义 |
|---|---|---|---|
| where | 在客户端过滤数据 | select * from Products where prod_price >= 5.99 order by prod_price desc; #同排序操作一同使用时，不得位于排序操作之前#支持<>=!=等操作，其中<>操作等同于!= 例： select * from Products where prod_id <>"fc" order by prod_price desc; #过滤字符串不区分大小写 |
1.查找所有列，在Products表中，并返回prod_price >=5.99的所有行 2.查找所有列，在Products表中，并返回除prod_id = "fc"之外的所有行 |
| between | 值的范围过滤 | select prod_name,prod_price from Products where prod_price between 4 and 10 |
查找prod_name,prod_price两列在Products表中，并返回prod_price值为4-10范围内的的的所有行 |
| is | 可用来检查null（空值） | select prod_name,prod_price from Products where prod_price is null |
返回所有没有价格的商品 |
| and,or | 逻辑操作符 and且 or与，这里是短路的 |
select * from Products where vend_vend_id ="1001" and prod_price <=4; #and的优先级比or要高，and，or共同使用时为避免错误应用（）明确分组， #也可使用in代替or，例： select prod_name,prod_price from products where vend_id in("1001","1002") order by prod_name 等同于： select prod_name,prod_price from products where vend_id = "1001" or vend_id = "1002" order by prod_name |
返回所有vend_vend_id ="1001" 且 prod_price <=4;的行 |
| not | 否定其后的条件 | select prod_name,prod_price from products where not vend_id in("1001","1002") order by prod_name |
可与in连用，返回vend_id=1001 vend_id=1002外的所有行 |
通配符搜索
| 语法 | 作用 | 例子 |
|---|---|---|
| % | 匹配0、1或多个字符包含空格。不会匹配到null | select prod_name from products where prod_name like "f%%" |
| _ | 匹配单个字符，包含空格 | select prod_name from products where prod_name like "fuse_" |
| rtrim（）ltrim（） | 去除右边、左边空格 |
创建计算字段
select prod_id ,quantity,item_price,quantity*item_price as expanded_price
from orderitems
where order_num = 20008;
#如上创建了一个expanded_price字段（quantity*item_price的结果的别名），其仅在此时有效而不会存放到表中。
使用函数
select vend_name, upper(vend_name) as vend_name_upcase
from vendors
#将vend_name列下的所有行以大写形式返回
select avg(prod_price) as avg_peice from products where vend_id ="1001"
#返回平均值
select count(*) as num_cust from customers
#返回长度（数目），也可对列表中特定值进行计数
分组
select vend_id,count(*) as num_prods from products #对vend_id每行进行计数
group by vend_id;#按照vend_id排序并分组
select cust_id,count(*) as orders
from orders
group by cust_id
having count(*)>=2#过滤分组中>=2的，having支持where的所有操作
select order_num,count(*) as items
from orderitems group by order_num
having count(*) >=3
order by items,order_num desc#对分组依照选定的列进行排序
子句查询
select cust_name,cust_contact from customers where cust_id =(select cust_id
from orders
where order_num = (select order_num from orderitems where prod_id = "jp2000"));
#由内而外，哈哈
等效于：
select order_num
from orderitems
where prod_id = "jp2000"；
select cust_id
from orders
where order_num =20006
select cust_name,cust_contact from customers where cust_id =10003
联结（返回不在同一个表中的行）
select vend_name, prod_name,prod_price
from vendors,products
where vendors.vend_id=products.vend_id;#此处过滤联结条件。
#如没有联结条件过滤，将检索出“笛卡尔积”：表1行数*表2行数
select vend_name, prod_name,prod_price
from vendors inner join products
on vendors.vend_id=products.vend_id
自联结（比子查询更快）
select cust_id, cust_name, cust_contact
from customers
where cust_name = (select cust_name from customers where cust_contact ="jim jones");
select c1.cust_id, c1.cust_name,c1.cust_contact
from customers as c1,customers as c2#不以别名进行会引发错误
where c1.cust_name=c2.cust_name and c2.cust_contact="jim jones"#联结cust_name与c2.cust_name ，并过滤cust_contact="jim jones"的行
组合查询
select cust_name,cust_contact,cust_email,cust_state
from customers
where cust_state in("il","in","mi")
union #组合上下select多个select之间需要多个union分隔，union默认排除重复，union all则不排除
select cust_name,cust_contact,cust_email,cust_state#union中每个查询必须包含相同的列、表达式、或聚集函数
from customers
where cust_name ="wascals"
order by cust_name;#不能分别对每条union指定不同的排序
INSERT
依赖于次序的插入
insert into customers
values("1000000006","toy land","123 any street","new york","ny", "11111","usa",null, null);
#依赖于次序的插入，必须为每一列提供一个值，如某列无添加则应写上null
提供列名的插入
insert into customers(cust_id,cust_contact,cust_email,cust_name,cust_address,cust_city,cust_state,cust_zip)
#必须为提供了列名的列给出一个值
values(null, null,"1000000006","toy land","123 any street","new york","ny", "11111");
从另一个表插入
insert into 表名（列名）
select 列名
from 表名
where 过滤
复制一个表
select *
into custcopy
from customers;
create table custcopy as
select *
from customers;
UPDATE
更新单个列
update customers
set cust_email = "kim@@thetoystore.com"
where cust_id = "100000000005"#如不指定，将更新customers表cust_email列下的所有行
更新多个列
update customers
set cust_email = "kim@@thetoystore.com",cust_contact="sam roberts"
where cust_id = "100000000006"
DELLETE
delete from customers
where cust_id = "1000000006"#删除此行，不过滤则删除所有行
#update删除列
#truncate删除表
添加删除列&&表
添加表
create table orderitems
(order_num integer not null,
order_item integer not null,
prod_id char(10) not null,
quantity integer not null default 1,#设置quantity列下的行默认值为1
item_price decimal(8,2) not null);#not null即不允许填入null，默认可填入null，只有为 not null的列方可为主键及唯一标识
alter table vendors
add vend_phone char (20);
alter table vendors
drop column vend_phone;#此操作不可逆
drop table custcopy;#此操作不可逆
视图
create view#创建视图
drop view 视图名#删除视图
create view productcustomers as
select cust_name , cust_contact,prod_id
from customers,orders,orderitems
where customers.cust_id=orders.cust_id
and orderitems.order_num=orders.order_num;
select *
from productcustomers;
create view customeremaillist as
select cust_id, cust_name,cust_email
from customers
where cust_email is not null;#返回查询中所有cust_email不为空的，并将其添加到视图中
create view orderitemsexpanded as
select order_num,prod_id,quantity,item_price,quantity*item_price,quantity*item_price as
expanded_price
from orderitems
事务管理
start transaction ;
-- 标识事务处理块,块中内容未执行完则整体撤销
savepoint delete1;#标识
rollback to delete1;返回标识delete1
![[学习笔记] Oracle基础增删改查用法](/upload/202205/01/4e5dk5lwrfw.jpg)
