数据库笔记 NO.1 ------------2020.03.26

 

***排序与检索数据***
---检索多个列
select prod_id, prod_name, prod_price from Products;
---检索所有列
select * from Products;
---去重 DISTINCT只返回不同的值,因为他是关键字,所以必须直接放在列名的前面
select DISTINCT name from Products;
---限制行数查找:检索前五行数据
select TOP 5 prod_name from Products;
select prod_name from Products where rownum <=5;
--order by 排序:
select prod_name from Products order by pro_name;
--多列排序:先按照money,后按照name
select prod_id, prod_name, prod_price from Products 
order by prod_price, prod_name;
--倒序desc   or   升序asc排序
select prod_id, prod_name, prod_price from Products order by prod_price DESC; 价格降序排序
select prod_id, prod_name, prod_price from Products order by prod_price DESC, prod_name;   只对价格降序排序,名字仍然默认升序排序

***过滤数据***
---使用where子句操作符
等于=
不等于<>   !=
小于<   小于等于<=
大于>   大于等于>=
不小于!<       不大于!>
介于 between       为空is null

---不匹配查询      列出所有产品名不为A的产品
select * from Products where name <> 'A';
---范围值查询      列出产品价格在5到10元之间的数据
select * from prod_price from Products where prod_price BETWEEN 5 AND 10;
---空值查询          列出产品名称为空的数据
select * from Products where name is null;
---and  和  or 操作符     筛选出价格大于10的A或者B  加上()
select name, price from products where (name = 'A' or name = 'B') and price >=10;
---in与or功能相当,但是执行效率更高!
select name, price from products where name IN ('A','B') ;
---not只有一个功能:就是否定其后所跟的任何条件:列出名字不是A的数据
select name, price from products where not name = 'A'; 
等同于select name, price from products where name <> 'A';

***使用通配符:用来匹配值的一部分的特殊字符 LIKE***
---找到fish打头的产品名字
select name from products where name like 'fish%';
---找到任何位置上包含文本fish的数据
select name from products where name like '%fish%';
---找到以F打头Y结尾的数据
select name from products where name like 'f%y';
---方括号[]通配符指定一个字符集合
找到所有名字以J或者M起头的联系人:[JM]匹配[]中任意一个字符,%匹配第一个字符之后的任意数目的字符
select name from products where name like '[JM]%';

***创建计算字段***
拼接字段:将两个值联结到一起,构成单个值     操作符:+ 或者 ||
---将名字和国家拼接在一起
select name + '('+country+')' from products order by name;
select name ||  '('  ||  country  ||  ')'   from products order by name;
去掉空格(trim)     去掉右边空格(rtrim)    去掉左边空格(ltrim)
select name + '('+ RTRIM(country) +')' from products order by name;


***使用别名***
查询结果作为新的一列,别名用AS关键字赋予,指示SQL创建一个包含指定计算结果的名为newtitle的计算字段
select  name as newtitle from products; 

***使用计算字段执行算书计算***
对检索出的数据进行算书运算
---检索订单号为2020的商品数据
select prod_id, prod_quantity, prod_price from Products where ordernum = '2020';
---计算产品总价将total_price作为新的列
select prod_id, prod_quantity, prod_price, quantity*prod_price AS total_price 
from Products where ordernum = '2020';

***使用函数处理数据***
提取字符串的组成部分    substr()
读取当前日期   sysdate
文本处理函数UPPER(将文本转化为大写)    注意:常用的文本处理函数
---select name , UPPER(name) AS newname from products order by name;

8.2日期时间处理函数
----检索2012年的所有订单
select order from product where to_number (to_char(order_date,'YYYY')) = '2012';
*to_char()函数是用来提取日期成分
*to_number()函数是将取出来的成分转换为数值的
*between也可以
select order from product where order_date between to_date('01-01-2012') and to_date('12-31-2012')
其中to_date()函数用来将两个字符串转换为日期

9.1聚集函数
avg() 返回平均值
count() 返回行数
max() 返回最大值
min() 返回最小值
sum() 返回某列之和
9.1.1avg()
----产品的平均价值
select avg(prod_price) as avg_price from product;
*as后面是别名
----返回特定一列的平均值
select avg(price) as avg_price from product where id = '001';
9.1.2count()函数
两种方法:
1.count(*)  计算行数(包含空与非空)
1.count(column) 对特定列中具有值的行数计算,忽略null值
select count(*) from products;
select count(name) from products;
9.1.3max()函数  min()函数
max()返回列中最大的值   min()返回列中最小的值
select max(price) as max_price from products;
9.1.4sum()函数
返回指定列的和
----返回100型产品数量
select sum(quantity) as item from products where num = '100';
sum()也可以合并计算
----返回订单总金额:返回2020型号产品的总金额
select sum(price*num) as total_price from product where order_num = '2020';

9.3组合聚集函数
将函数组合使用,as用来取别名
----返回产品的数量,最小值,最大值和平均值
select count(*) as num , min(price) as price_min , max(price) as price_max ,avg(price) as price_avg from products;

10.1创建分组
----使用group by,统计每个产品的数量
select name , count(*) as num_prods from products group by name;
**group by 包含任意数目的列,可以实现嵌套
group by 在where 之后 order by之前
10.3过滤分组 规定包括哪些分组,排除哪些分组
where是过滤行  having是过滤分组
-----过滤数量是大于2的产品
select id ,count(*) as num from products group by id having count(*)>2;
**即where在数据分组之前进行过滤,having在数据分组之后进行过滤
-----where和having同时存在的情况:返回数量>2且价格>4的产品
select id,count(*) as num from products where price>4 group by having count(*) >2;
***where行级过滤
having组级过滤

11.1使用子查询:子查询即嵌套在其他查询中的查询
----查询订购7系的所有顾客名单
1.查定了7系的订单号
select order_number from order where prod_id = '7系';
2.查询订单号中涉及到的顾客名单
select cust_id form order where order_number = '001';
3.查询顾客的详细情况
select name,phone from customer where cust_id = '001';
合并
select name , phone from customers where cust_id in 
(select cust_id from order where order_number in
(select order_number from order where cust_id = '7系'
));

12联结表(联结join)
---使用表名:可以对列名、计算字段、表名起别名
select cust_name , cust_contact
from customer as C ,order as O ,order_item as OI 
where C.cust_id = O.cust_id
and OI.order_num = O.order_num
and prod_id = '001';
自联结  self join 自然联结 natural join  外联结 outer join

12.1内联结  inner join
12.2外联结  outer join
select T_USER_INFO.IDCARD ,RY_TZ_CZRK.GMSFHM
FROM T_USER_INFO RIGHT OUTER JOIN RY_TZ_CZRK
ON RY_TZ_CZRK.GMSFHM = T_USER_INFO.IDCARD;
----返回所有顾客以及每个顾客所下的订单数量(订单表和顾客表在两张表)
select customers.cust_id , count(orders.order_num)  as num_ord
from customers inner join orders 
on customers

14.1组合查询
----使用union,只要将union放在各条语句之间
select A,B,C from products
where D in ('1','2','3')
union
select E,F,G from products
where H ='I';

15插入数据
----insert插入   空值为null
insert into customers values ('张三','18',NULL,'高三');

最安全的写法是
insert into customers(name,age,address,grade) values('张三','18',null,'高三')

**插入部分行,那么省略的列可能会使用当时建表时候定义的默认值或者本身改列定位为允许null值

15.1复制表select into
---select语句创建一个新表newA,并且把old_A的整个内容复制到新表中
select * into newA from old_A;

16.1更新和删除数据
----更新update:更新客户1008现在有了新的电子邮箱123456@qq.com
update customers                --要更新的表名
set cust_email = '123456@qq.com' --列名和它的新值
where cust_id = '1008';          --确定要更新哪些行的过滤条件,没有where子句,将会更新所有行
----更新多个值
update customers                
set cust_email = '123456@qq.com'  , cust_contact = '183666666'
where cust_id = '1008';  
----删除某个列的值,若表定义允许null
update customers
set cust_email = NULL
where cust_id = '1008';
----删除数据
delete from customers where cust_id = '1008';
----删除所有行数据  直接使用truncate table 清空表

17更新表(结构)
---给customers表增加一个名为phone的列,数据类型为char
alter table customers
add phone char(11);
----删除表drop
drop table customers;

18使用视图:视图是虚拟的表,视图只包含使用时动态检索数据的查询
视图的规则和限制:
1.与表一样,视图必须唯一命名
2.视图没有数目限制
3.创建视图必须有很大的访问权限
4.视图允许嵌套
5.禁止在视图查询中使用order by子句
6.视图不能索引,也不能有关联的触发器或默认值
----创建视图 create view      删除视图 drop view A;
18.1利用视图简化复杂的联结
原查询语句:创建名为product_customer的视图,联结三个表
create view product_customer as 
select cust_name,cust_contact,prod_id
from customers,orders,order_item
where customers.cust_id = order.cust_id
and order_item.order_num = orders.order_num;
----检索订购了001的顾客
select cust_name , cust_contact
from product_customer
where prod_id = '001';
18.2使用视图与计算字段
原语句:
select prod_id,quantity,item_price, quantity*item_price as total_price
from order_item
where order_num = 2020;
----使用视图后
create view order_itemtotal as 
select  order_item,prod_id,quantity,item_price, quantity*item_price as total_price
from order_item

select * from order_itemtotal 
where order_num = 2020;

19.1存储过程:就是为以后使用而保存的一条或者多条SQL语句
19.2执行存储过程  execute 
----将一个新的产品添加到products表中
execute addnewproduct('李四','18','男','高三');
19.3创建存储过程procedure
----Oracle支持in传递值给存储过程,out从存储过程返回值,inout既传递值给存储过程也从存储过程传回值
--下面执行一条简单的select语句,检索具有邮件地址的顾客,然后用检索出的行数设置listcount
create procedure mailinglistcount(listcount out integer)
is
v_rows integer;
begin
     select count(*) into v_rows
     from customers
     where not cust_email is null;
     listcount := v_rows;
end;

--声明一个变量来保存存储过程返回的任何值,然后执行存储过程,再使用select语句返回值
var returnvalue number
exec mailinglistcount(:returnvalue);
select returnvalue;


 

发布了1 篇原创文章 · 获赞 0 · 访问量 31
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 大白 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览