SQL必知必会

如需要电子书,联系我。

  • oracle只显示前几行

select prod_name from products where rownum <=5 ;

表的一个默认字段

第一课创建表

字段后有空格,数据类型需要空格来补全,空格是有函数可以去掉的,第8课有

select * from Customers ;
select * from OrderItems  ;
select * from Orders ;
select * from Products ;
select * from Vendors ;

第二课rownum

select prod_id,prod_name,prod_price from products ;
select distinct vend_id from products;
select * from products ;
select distinct vend_id,prod_name from products;
select prod_name from products where rownum <=5 ;

第三课order by

order by 子句是一个select语句最后一个子句

order by的列可以是非检索的列

select prod_name from products order by prod_name ;
select prod_name from products order by vend_id ;
select prod_id,prod_price,prod_name from products order by prod_price,prod_name;
select prod_id,prod_price,prod_name from products order by 2,3;
select prod_id,prod_price,prod_name from products order by prod_price desc,prod_name;

第四课where

where子句用的符号还有between\is null

将值与字符串进行比较需要单引号,与数值比较则不需要单引号

between 5 and 10 条码需要两个值,开始值与结束值,包括开始值与结束值

is null条码判断值是否为空

select prod_name,prod_price from products where prod_price = 3.49;
select prod_name,prod_price from products where prod_price < 10 ;
select prod_name,prod_price from products where prod_price <= 10 ;
select vend_id,prod_name from products where vend_id <> 'DLL01';
select prod_price,prod_name from products where prod_price between 5 and 10 ;
select cust_name from customers where cust_email is not null;

第五课高级数据过滤

or操作符只要前面的满足后面的就不执行了

有or又有and,and的优先级更高,大多时候会借助括号解决

not用于否定后面的列

select prod_id,prod_price,prod_name from products where vend_id='DLL01' and prod_price <=4 ;
select prod_id,prod_price,prod_name from products where vend_id='DLL01' or vend_id='BRS01' ;
select prod_id,prod_price,prod_name from products where vend_id='DLL01' or vend_id='BRS01' and prod_price >= 10 ;
select prod_id,prod_price,prod_name from products where (vend_id='DLL01' or vend_id='BRS01') and prod_price >= 10 ;
select prod_name,prod_price from products where vend_id in ('DLL01','BRS01') order by prod_name ;
select prod_name,prod_price from products where vend_id='DLL01' or vend_id='BRS01' order by prod_name ;
select prod_name from products where not vend_id = 'DLL01' order by prod_name;
select prod_name from products where vend_id <> 'DLL01' order by prod_name ;

第六课like与通配符

like是谓词

%不能匹配null

通配符使用起来很慢,应该放在最后

select prod_id,prod_name from products where prod_name like 'Fish%';
select prod_id,prod_name from products where prod_name like '%bean bag%';
select prod_id,prod_name from products where prod_name like 'F%y%';/*后面有空格*/
select prod_id,prod_name from products where prod_name like '__ inch teddy bear%';
/*只有微软件的数据库才能用,可以在中括号里加^号来否定,其他的只能用not了*/
select cust_contact from customers where cust_contact like '[JM]%' order by cust_contact ;
select prod_id,prod_name from products where not prod_name like 'F%';

第七课计算字段

oracle用||拼接两个列

rtrim()函数用于去除右边空格

ltrim()函数用于去除左边空格

trim()函数用于去除两边的空格

计算列,对列进行计算,创建是新的列,可以重命名

select 可以用于运算,但是在这里不行

select rtrim(vend_name) || '(' || rtrim(vend_country) || ')' from vendors order by vend_name ;
select rtrim(vend_name) || '(' || rtrim(vend_country) || ')' as vend_title from vendors order by vend_name ;
select prod_id,quantity,item_price from orderitems where order_num='20008';
select prod_id,quantity,item_price,quantity*item_price as expanded_price from orderitems where order_num='20008';

第八课使用函数处理数据

upper()将小写字母转换成大写字母

lower()将大写字母转换成小写字母

rtrim()除去右边空格

ltrim()除去左边空格

length()字符串的长度

soundex()找出列类似的读音

处理日期oracle没有datepart()函数,只能用其他的了

to_char()函数取日期的部分

to_number()函数将取出的字符串类型日期转换为数字类型

借助between and ,to_date()函数可以将字符串转换成日期类型

还有比较多的数值处理函数

select vend_name, upper(vend_name) as vend_name_upcase from vendors order by vend_name ;
select vend_name, length(rtrim(vend_name)) as vend_name_length from vendors order by vend_name ;
select upper(vend_name) as vend_name_upcase,lower(upper(vend_name)) as vend_name_lower from vendors order by vend_name ;
select cust_contact from customers where soundex(cust_contact) = soundex('John Smith1');
select cust_name, cust_contact from customers where soundex(cust_contact) = soundex('Michael Green');
select order_num "订单号",to_char(order_date,'MM')||'月'||to_char(order_date,'dd')||'日' as "日期" from orders where to_number(to_char(order_date,'YYYY')) = 2012 ;
select order_num from orders where order_date between to_date('2012/01/01','yyyy/MM/dd') and to_date('2012/12/31','yyyy/MM/dd') ;

第九课聚集函数

avg()返回某列的平均值

count()返回某列的行数,参数为*\1则返回所有列,参数为某列则返回某列的非null值的个数

max()返回某列最大值,忽略null

min()返回某列最小值,忽略null

sum()返回某列值之和,忽略null

distinct参数用于指定不同的值,distinct后面应该跟的是列名

select avg(prod_price) avg_price,count(*) num_items,max(prod_price) max_price,min(prod_price) min_price,sum(prod_price) total_price from products ;
select count(*) num_items,count(cust_email) email_items from customers ;
select sum(quantity)  from orderitems where order_num='20005';
select sum(quantity*item_price) as total_price from orderitems where order_num='20005';
select avg(distinct prod_price) as avg_pric from products where vend_id='DLL01';

第十课分组函数,group by\having

分组后使用聚集函数

group by可以对多个列进行分组,select中使用了非计算列,都需要在group by中出现

oracle允许使用位置来指定分组

having与where使用相同,作用不同,where是作用在所有的行,having是作用在分组内

另一种说法,where是在分组前进行过滤,group by是在分组后进行过滤

使用group by时也一定要使用order by才能确认顺序

select vend_id,count(*) as num_prods from products group by vend_id ;
select prod_id,prod_name from products order by 1,2 ;
select * from orders
select cust_id,count(*) as orders from orders group by cust_id having count(*)>=2;
select vend_id,count(*) as num_prods from products where prod_price>=4 group by vend_id having count(*)>=2;
select order_num,count(*) as items from orderitems group by order_num having count(8)>=3 order by items,order_num;

第11课使用子查询

子查询总是从内向外进行处理

计算字段是子查询语句的时候

下面使用的是完全限定列名,由表名与列名组合而成,意思是比较orders表中的cust_id与当前正从customers表中检索的cust_id

子查询常用于in操作符与填充计算列中

购买RGAN01的顾客的信息

select order_num from orderitems where prod_id='RGAN01';
select cust_id from orders where order_num in ('20007','20008') ;
select cust_id from orders where order_num in (select order_num from orderitems where prod_id='RGAN01');
select cust_name,cust_contact from customers where cust_id in ('1000000004','1000000005');
select cust_name,cust_contact from customers where cust_id in (select cust_id from orders where order_num in (select order_num from orderitems where prod_id='RGAN01'));
/*对一个顾客执行订单数目及对全部顾客执行订单数目*/
select count(*) as orders from orders where cust_id='1000000001';
select cust_name,cust_state,(select count(*) from orders where orders.cust_id=customers.cust_id) as orders from customers order by cust_name ;

(重要)第12课联结表join

关系数据库的设计:一类数据一个表

用from联结两表的时候,如果没有where则将第一个表的每一行与第二个表的每一行进行联结(叉联结\笛卡儿积)

等值联结\内联结

select vend_name,prod_name,prod_price from products,vendors where vendors.vend_id=products.vend_id ;
/*内联结,指明联结的类型*/
select vend_name,prod_name,prod_price from vendors inner join products on vendors.vend_id=products.vend_id ;
select prod_name,vend_name,prod_price,quantity from orderitems,products,vendors where products.vend_id=vendors.vend_id and products.prod_id=orderitems.prod_id and orderitems.order_num='20007';
/*只需要用到多个表的数据都可以用联结查询*/
select cust_name,cust_contact from customers,orders,orderitems where customers.cust_id=orders.cust_id and orders.order_num=orderitems.order_num and prod_id='RGAN01';
select cust_name,cust_contact from customers inner join orders on customers.cust_id=orders.cust_id inner join orderitems on orders.order_num=orderitems.order_num where orderitems.prod_id='RGAN01';

第13课创建高级联结

自联结、自然联结inner join、外联结left outer join\right outer join

全外联结full outer join

别名的另一个作用是自联结

可以多个表联结,况且联结时可以使用多种不同的联结

联结时一定要使用正确的联结条件

select rtrim(vend_name)||'('||rtrim(vend_country)||')' as vend_title from vendors order by vend_name ;
select cust_name,cust_contact from customers C ,orders O,orderitems OI where C.cust_id=O.cust_id and O.order_num=OI.order_num and OI.prod_id='RGAN01';
/*自联结与子查询对比,自联结会更快*/
select cust_id,cust_name,cust_contact,cust_email 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,C1.cust_email from customers C1,Customers C2 where C1.cust_name=C2.cust_name and C2.cust_contact='Jim Jones' ;
/*自然联结,至少存在一列出现在两个表中*/
select C.*,O.order_date,O.cust_id,OI.prod_id,OI.quantity,OI.item_price from customers C,orders O,orderitems OI where C.cust_id=O.cust_id and O.order_num = OI.order_num and OI.prod_id='RGAN01';
/*外联结,联结在相关的表中没有对应行的行,外联结与内联结对比。外联结将没有对应的行的数据也进行输出*/
select C.cust_id, O.order_num from customers C inner join orders O on C.cust_id=O.cust_id ;
select C.cust_id, O.order_num from customers C left outer join orders O on C.cust_id=O.cust_id ;
/*全外关联,两个表的没有对应的行的数据也进行关联*/
select customers.cust_id, orders.order_num from customers full outer join orders on customers.cust_id=orders.cust_id ;
/*内联与聚集函数一起用*/
select C.cust_id,count(O.order_num) num_order from customers C inner join orders O on C.cust_id=O.cust_id group by C.cust_id ;
select C.cust_id,count(O.order_num) num_order from customers C left outer join orders O on C.cust_id=O.cust_id group by C.cust_id ;

第14课组合查询union

union all可以返回重复的匹配值

多个union的列要相同,顺序没有要求,有聚集函数也要相同

union的排序只需要在最后一个select语句中使用order by语句*/

对比两种实现方法

select cust_name,cust_contact,cust_email from customers where cust_state in('IL','IN','MI');
select cust_name,cust_contact,cust_email from customers where cust_name='Fun4All';
select cust_name,cust_contact,cust_email from customers where cust_state in('IL','IN','MI')
union all
select cust_name,cust_contact,cust_email from customers where cust_name='Fun4All';
select cust_name,cust_contact,cust_email from customers where cust_state in('IL','IN','MI') or cust_name='Fun4All';
select cust_name,cust_contact,cust_email from customers where cust_state in('IL','IN','MI')
union
select cust_name,cust_contact,cust_email from customers where cust_name='Fun4All' order by cust_name desc;

第15课插入数据insert

插入完整的行

插入行的一部分

插入某些查询的结果

insert select

select into

最简单的insert语句,不安全,因为表结构变化将不能使用,应该指定列名,对比以下语句

指定了全部列名,这样安全性高些,列名与值的内容可以按照任意次序出现,保证前后对应即可

insert into customers values('1000000006','Toy Land','123 Any Street','New York','NY','11111','USA',null,null);
insert into customers(cust_id,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country,cust_contact,cust_email) values('1000000006','Toy Land','123 Any Street','New York','NY','11111','USA',null,null);
/*插入查询结果,selecr后的列名不重要,只会把select后的按位置插入*/
create table custnew as select * from customers ;
select * from custnew for update ;
insert into customers
    (cust_id,
     cust_name,
     cust_address,
     cust_city,
     cust_state,
     cust_zip,
     cust_country,
     cust_contact,
     cust_email)
    select * from custnew;
/*复制表,第一种情况不适用于oracle*/
select * into custcopy from customers ;
create table custnew as select * from customers ;

第16课更新和删除数据update/delete

使用update与delete时一定要注意,不要漏了where语句

若要清除一格的值,可以将其值设置为null

delete 删除一行至所有行,不删除表

如果需要删除所有行有更快的语法truncate table

drop才可以删除表结构

使用update\delete语句前先用select语句选出所有值检查没问题后再更改

update customers set cust_email='kim@thetoystore.com' where cust_id='1000000005' ;
update customers set cust_contact='Sam Roberts',cust_email='sam@toyland.com' where cust_id='1000000006';
update customers set cust_email=null where cust_id='1000000005';
delete from customers where cust_id='1000000012';
truncate table custnew ;/*0.046S*/
delete from custnew2 ;/*0.016S*/
drop table custnew ;

第17课创建和操纵表

列名唯一,数据类型难点,后面的描述复杂

防止意外覆盖表,在创建表之前需要对表进行手动删除

default 1是用于设置默认值

default sysdate用于设置默认时间

更新表(添加列)alter table,有数据时候更新不好,应该在设计时就考虑好

如果表的结构更改过大,可以创建新的表存放旧内容,替换掉

drop table无法恢复

rename to 重命名表名

CREATE TABLE Products
(
  prod_id    char(10)      NOT NULL ,
  vend_id    char(10)      NOT NULL ,
  prod_name  char(255)     NOT NULL ,
  prod_price decimal(8,2)  NOT NULL default 1,
  prod_desc  varchar(1000) NULL 
);
insert into pdm values(to_char(sysdate,'yyyy\MM\dd'));
alter table vendors add vend_phone char(20);
alter table vendors drop column vend_phone ;
drop table custnew ;
create table custnew as select * from customers ;
rename custnew to custcopy ;
select * from custcopy ;

第18课使用视图

一个虚拟表,可以很方便地通过查到数据

视图可能禁止使用order by

create view创建视图

drop view viewname删除视图

创建出来的视图就相当于一个表

视图是处理表后,得到想要的数据的新的表

select cust_name,cust_contact,prod_id from customers,orders,orderitems where customers.cust_id=orders.cust_id and orders.order_num=orderitems.order_num and prod_id='RGAN01';
create view productcustomers as select cust_name,cust_contact,prod_id from customers,orders,orderitems where customers.cust_id=orders.cust_id and orders.order_num=orderitems.order_num;
select cust_name,cust_contact from productcustomers where prod_id='RGAN01';
select rtrim(vend_name)||'('||rtrim(vend_country)||')' as vend_title from vendors order by vend_name ;
create view vendorlocation as select rtrim(vend_name)||'('||rtrim(vend_country)||')' as vend_title from vendors order by vend_name ;
select * from vendorlocation ;
create view customeremaillist as select cust_id,cust_name,cust_email from customers where cust_email is not null ;
select * from customeremaillist ;
create view orderitemsexpanded as select order_num,prod_id,quantity,item_price,quantity*item_price as expanded_proce from orderitems;
select * from orderitemsexpanded where order_num=20008 ;

第19课使用存储过程

最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 151,688评论 1 330
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 64,559评论 1 273
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 101,749评论 0 226
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 42,581评论 0 191
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 50,741评论 3 271
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 39,684评论 1 192
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 31,122评论 2 292
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 29,847评论 0 182
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 33,441评论 0 228
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 29,939评论 2 232
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 31,333评论 1 242
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 27,783评论 2 236
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 32,275评论 3 220
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 25,830评论 0 8
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 26,444评论 0 180
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 34,553评论 2 249
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 34,618评论 2 249

推荐阅读更多精彩内容

  • 第三课: 排序检索数据 distinct关键字:distinct 列名1,列名2,列名3DISTINCT 关键字会...
    VictorBXv阅读 1,432评论 0 8
  • 表 存储在表中的数据是同一种类型的数据或清单。 数据库中的表有为一个名字来标识自己。 表具有一些特性,这些特性定义...
    蛐蛐囍阅读 1,256评论 0 7
  • 注:这一系列的文章是《SQL必知必会》第四版的读书笔记。 7.创建计算字段 什么是计算字段,怎么创建计算字段,以及...
    zuyuxia阅读 312评论 0 0
  • 一、基本概念 数据库、表、列、行、主键、外键、关键字 二、检索 1.检索列 SELECT prod_name(id...
    兀镜晓Jingle阅读 325评论 0 7
  • SQL 必知必会 在mac终端操作sqlite: cd 数据库所在的目录 sqlite3 数据库文件名 //带后缀...
    陈_振阅读 696评论 0 0