文章内容是学习SQl基础知识的过程中，记录的学习笔记。每条语句都在oracle或access2003上执行过。
select first_name,
last_name
from l_employees;
select employee_id, last_name, credit_limit from l_employees
where credit_limit > 20.00
order by last_name;
select employee_id as employee_number, phone_number as extension, last_name, 'excellect worker' as evaluation, 10 as rating from l_employees;
oracle:
select employee_id as "employee number", phone_number as extension, last_name, 'excellect worker' as evaluation, 10 as rating from l_employees;
access:
select employee_id as [employee number], phone_number as extension, last_name, 'excellect worker' as evaluation, 10 as rating from l_employees;
select employee_id, first_name, last_name, manager_id from l_employees where manager_id=203 or manager_id is null;
select employee_id, first_name, last_name, manager_id from l_employees where not(manager_id=203);
select employee_id, first_name, last_name, manager_id from l_employees where manager_id between 202 and 203;
select employee_id, first_name, last_name, manager_id from l_employees where manager_id in(202,203);
oracle:
select employee_id, first_name, last_name, hire_date from l_employees where hire_date between '01-JUN-1998' and '01-JUL-2003';
access:
select employee_id, first_name, last_name, hire_date from l_employees where hire_date between #01-JUN-1998# and #01-JUL-2003#;
oracle:
select employee_id, first_name, last_name from l_employees where last_name like '%n%';
access:
select employee_id, first_name, last_name from l_employees where last_name like '*n*';
select last_name, hire_date as start_date, phone_number, employee_id, dept_code from l_employees where dept_code='sal' order by last_name;
select last_name, hire_date as start_date, phone_number, employee_id, dept_code as department_code from l_employees where hire_date between '01-Jan-2003' and '31-Dec-2004' order by hire_date desc;
select last_name, hire_date as start_date, phone_number, employee_id, manager_id from l_employees where manager_id is not null order by manager_id desc;
select distinct dept_code, credit_limit from l_employees where not(manager_id is null) order by dept_code, credit_limit desc;
select employee_id, last_name, evaluation, rating, eval_date, next_eval from l_employees, sec0310_constants order by employee_id;
select employee_id, first_name, last_name from l_employees where last_name between 'H' and 'Q';
oracle:
create table sales_staff as select employee_id, first_name, last_name, dept_code from l_employees where dept_code='sal';
access:
select employee_id, first_name, last_name, dept_code into sales_staff from l_employees where dept_code='sal';
oracle:
create view sales_staff_views as select employee_id, first_name, last_name, dept_code from l_employees where dept_code='sal' order by employee_id;
access:
insert into l_foods values ('arr', 'ap', 11, 'apple pie', 1.50, null);
insert into l_foods (product_code, description, supplier_id, price) values ('bp', 'blueberry pie', 'arr', 1.60);
insert into l_foods (supplier_id, product_code, menu_item, description) select 'arr', product_code, menu_item, description from l_foods where supplier_id = 'asp';
update l_foods set price = price + 0.10, price_increase = price_increase + 0.10 where supplier_id in ('jbr', 'frv');
delete from l_foods where supplier_id in ('cbc','jbr');
oracle:
create table excise401 as select employee_id, first_name, last_name, dept_code as department_code from l_employees where dept_code in ('sal', 'mkt');
access:
select employee_id, first_name, last_name, dept_code as department_code into excise401 from l_employees where dept_code in ('sal', 'mkt');
oracle:
create table excise401b as select menu_item, description as food, price from l_foods where price < 5.00;
access:
select menu_item, description as food, price into excise401b from l_foods where price < 5.00;
oracle:
create or replace view ex0402a as
select employee_id, first_name, last_name, dept_code as department_code
from l_employees
where dept_code in ('sal', 'mkt');
access:
select employee_id, first_name, last_name, dept_code as department_code
from l_employees
where dept_code in ('sal', 'mkt');
create or replace view shipping_dept as select employee_id, first_name, last_name, dept_code, credit_limit from l_employees where dept_code = 'shp';
insert into shipping_dept values (211, 'Joan', 'Patrick', 'shp', 25);
update shipping_dept set credit_limit=35 where first_name='Martha' and last_name='Woods';
delete from shipping_dept where employee_id = 208;
oracle:
select table_name from user_tables;
select view_name from user_views
select view_name, text from user_views where view_name='shipping_dept';
select table_name, column_name, column_id from user_tab_columns where table_name = 'l_employees' order by column_id;
select table_name, constraint_type, constraint_name from user_constraints where table_name= 'l_foods';
select * from user_cons_columns where table_name='l_foods';
oracle:
create table l_foods_2 (menu_item number(2), supplier_id varchar2(3), product_code varchar2(2), description varchar2(20), price number(4,2), price_increase number(4,2));
insert into l_foods_2 select menu_item, supplier_id, product_code, description, price, price_increase from l_foods;
drop table l_employees cascade constraints;
create table l_employees
(employee_id number(3) -- comment
,first_name varchar2(10)
,last_name varchar2(10)
,dept_code varchar2(3)
,hire_date date
,credit_limit number(4,2)
,phone_number varchar2(4)
,manager_id number(3)) storage (initial 2k next 2k pctincrease 0) tablespace users;
ALTER TABLE L_EMPLOYEES ADD CONSTRAINT PK_l_EMPLOYEES PRIMARY KEY (EMPLOYEE_ID) USING INDEX STORAGE (INITIAL 2K NEXT 2K PCTINCREASE 0);
insert into l_employees values
(201, 'Susan', 'Brown', 'Exe', '01-Jun-1998', 30, '3484', null );
insert into l_employees values
(202, 'Jim', 'Kern', 'Sal', '16-Aug-1999', 25, '8722', 201);
insert into l_employees values
(203, 'Martha', 'Woods', 'Shp', '02-Feb-2004', 25, '7591', 201);
insert into l_employees values
(204, 'Ellen', 'Owens', 'Sal', '01-Jul-2003', 15, '6830', 202);
insert into l_employees values
(205, 'Henry', 'Perkins', 'Sal', '01-Mar-2000', 25, '5286', 202);
insert into l_employees values
(206, 'Carol', 'Rose', 'Act', null, null, null, null);
insert into l_employees values
(207, 'Dan', 'Smith', 'Shp', '01-Dec-2004', 25, '2259', 203);
insert into l_employees values
(208, 'Fred', 'Campbell', 'Shp', '01-Apr-2003', 25, '1752', 203);
insert into l_employees values
(209, 'Paula', 'Jacobs', 'Mkt', '17-Mar-1999', 15, '3357', 201);
insert into l_employees values
(210, 'Nancy', 'Hoffman', 'Sal', '16-Feb-2004', 25, '2974', 203);
commit;
ANALYZE TABLE L_EMPLOYEES COMPUTE STATISTICS;
关于主键
alter table l_foods_2 add constraint pk_l_foods_2 primary key (supplier_id,product_code);
alter table l_foods_2 drop constraints pk_l_foods_2;
alter table l_foods_2 add constraint pk_l_foods_2 primary key (menu_item);
向表中添加一个新列
alter table l_foods_2 add date_introduced date;
扩展列的长度
oracle:
alter table l_foods_2 modify description varchar2(25);
alter table l_foods_2 modify price number(7,2);
access:
alter table l_foods_2 alter column description varchar(25);
从表中删除一个列
alter table l_foods_2 drop column price_increase;
利用create,select语句实现对表的增新列、删除列、重命名列、更改数据、更改数据类型、对列重排序、删除一个主键的功能
drop table phone_list;
create table phone_list as
select last_name, first_name, phone_number as ext, ' ' as notes
from l_employees
where employee_id between 203 and 206;
update phone_list set ext = '9408' where last_name = 'woods';
显示已经格式化的日期
oracle
column formatted_date format a20;
select employee_id,
first_name,
hire_date,
to_char(hire_date, 'mm-dd-yyyy hh:mi am')
as formatted_date
from l_employees
order by employee_id;
Access
select employee_id,
first_name,
hire_date,
format(hire_date, 'mm-dd-yyyy hh:nn am/pm') as formatted_date
from l_employees
order by employee_id;
输入已经格式化的日期
oracle
insert into l_lunches
values (25,
to_date(' 12-05-2005 11:30 am', 'mm-dd-yyyy hh:mi am'),
202, sysdate);
Access
insert into l_lunches
values (25, #dec 5 2005 11:30 am#,202, now());
删除序列
drop sequence seq_lunch_id;
创建序列
create sequence seq_lunch_id
start with 23
increment by 1;
insert into l_lunches
values (seq_lunch_id.nextval, '07-Dec-2005', 202, sysdate);
insert into l_lunches
values (seq_lunch_id.nextval, '07-dec-2005', 204, sysdate);
取得某个序列的当前值
select seq_lunch_id.currval from dual; -- dual在oracle中是用来打印值的虚表
Access中的序列,被看成是一个数据类型
GUI级别上生成序列的Access方法叫做autonumber
JET引擎级别上生成序列的Access方法叫做counter
Access SQL
alter table sec0708_suppliers2 add column new_num2 counter;
创建索引
create index ix_l_employees_name
on l_employees (last_name, first_name);
数据词典
user_tab_columns中记录了表中数据类型的详细信息
select column_id,
column_name,
data_type,
data_length,
data_precision,
data_scale,
nullable
from user_tab_columns
where table_name = 'l_employees'
order by column_id;
user_sequences中记录了序列信息
select * from user_sequences;
user_indexs,user_ind_columns 这两个字典表中记录了索引信息
oracle
select index_name,
table_name,
uniqueness,
tablespace_name,
status
from user_indexes
where table_name = 'l_employees';
select *
from user_ind_columns
where table_name = 'l_employees';
user_objects中记录了所有数据库对象的信息
select object_name,
object_type,
created,
last_ddl_time,
status
from user_objects;
dictionary表中记录了所有数据词典表信息
在sqlplus中格式化列的格式
column comments format a40 word_wrap;
select *
from dictionary
where table_name like '%seq%';
dict_columns表中记录了词典表中列的信息及说明
select * from dict_columns
where table_name = 'all_sequences';
练习
oracle
drop table my_days;
create table my_days
(my_seq_id number,
my_event varchar2(25),
my_date date);
alter table my_days
add constraint pk_my_days
primary key (my_seq_id);
drop sequence seq_my_days;
create sequence seq_my_days
start with 1
increment by 1;
insert into my_days
values (seq_my_days.nextval,
'birth date', '16-jan-1971');
insert into my_days
values (seq_my_days.nextval,
'college graduation', '24-jun-1993');
insert into my_days
values (seq_my_days.nextval,
'wedding', '14-feb-1994');
commit;
select my_seq_id,
my_event,
to_char(my_date, 'day month dd, yyyy') as my_date
from my_days
order by my_seq_id;
oracle
create table gq_701a (datetime date);
insert into gq_701a values (to_date('12-dec-2015', 'dd-mm-yyyy'));
column datetime format a80;
select to_char(datetime, 'month dd, yyyy') as month_of_year, to_char(datetime, 'ww') as weekday, to_char(datetime, 'ddd') as day_of_year,
to_char(datetime, 'q') as quarter, to_char(datetime, 'd') as week_of_year from gq_701a;
insert into gq_701a values (to_date('14-oct-2015 14:05', 'dd-mon-yyyy hh24:mi'));
select to_char(datetime, 'dd-mon-yyyy hh24:mi') as date_time from gq_701a;
commit;
Access
create table gq_701a (date_time datetime);
insert into gq_701a values (#12-dec-2015#);
select format (date_time, 'mmmm dd, yyyy') as month_of_year, format (date_time, 'dddd') as weekday, format (datet_ime, 'y') as day_of_year,
format (date_time, 'q') as quarter, format (date_time, 'ww') as week_of_year from gq_701a;
参照完整性
oracle
alter table sec0802_clients add constraints fk_state_code foreign key (state_code) references sec0802_states (state_code);
insert into sec0802_clients
values (700, 'gail hauser', 'ny');
update sec0802_clients
set state_code = 'ma'
where client_id = 200;
update sec0802_clients
set state_code = null
where state_code = 'ca';
delete from sec0802_states
where state_code = 'ca';
在查找表中更改编码
insert into sec0802_states
values ('zz', 'california', 'sacramento');
update sec0802_clients
set state_code = 'zz'
where state_code = 'ca';
delete from sec0802_states
where state_code = 'ca';
删除和更改查找表的三个选项
restrict 默认值
set null 外键列的所有匹配值被自动更新为null
cascade deletes
cascade updates
alter table sec0811_clients3
add constraint fk_state_code3
foreign key (state_code)
references sec0811_states3(state_code)
on delete set null;
alter table sec0812_clients4
add constraint fk_state_code4
foreign key (state_code)
references sec0812_states4(state_code)
on delete cascade;
Oracle, Access 都支持restrict更新；Oracle不支持级联更新，Access要在GUI中才支持级联更新。
Access
update sec0813_states5
set state_code = 'zz'
where state_code = 'ca';
通常表的主键列就是它的有效值表，但这不是完全的情况
alter table sec0814_clients6
add constraints fk_state_capital6
foreign key (state_capital)
references sec0814_states6(state_capital);
多个列作为主键时的参照完整性
alter table l_lunch_items
add constraints fk_li_food
foreign key (supplier_id, product_code)
references l_foods(supplier_id, product_code);
在同一个表中使用参照完整性的例子，查找表和数据表为一个表
alter table l_employees
add constraints fk_manager_id
foreign key (manager_id)
references l_employees(employee_id);
check约束条件 Oracle 将检验所有数据
检验规则 Access GUI 将不检验原有数据
Oracle
alter table l_foods
add constraints foods_mas_price
check (price < 10.00 );
unique约束条件
Oracle的两种方法
alter table l_employees
add constraints unique_emp_phone_num
unique (phone_number);
create unique index ix_phone
on l_employees (phone_number);
Access通过GUI实现unique
not null 约束条件
Oracle
alter table l_lunches
add constraints lunches_emp_id_not_null
check (employee_id is not null);
primary key 约束条件
约束条件常通过create table 语句实现
Oracle
create table l_employees3
(employee_id number(3) primary key
,first_name varchar2(10) not null
,last_name varchar2(10)
,dept_code varchar2(3)
,hire_date date
,credit_limit number(4,2) check (credit_limit < 50)
,phone_number varchar2(4) unique
,manager_id number(3) references l_employees3(employee_id)
);
方法2（写全约束名）
create table l_employees4
(employee_id number(3) constraint pk_employees
primary key
,first_name varchar2(10) constraint nn_f_name
check
(first_name is not null)
,last_name varchar2(10) constraint nn_l_name
check
(last_name is not null)
,dept_code varchar2(3)
,hire_date date
,credit_limit number(4,2) constraint max_limit
check
(credit_limit < 50)
,phone_number varchar2(4) constraint unique_phone
unique
,manager_id number(3) constraint fk_manager
references
l_employees4(employee_id)
on delete set null
);
Access SQL: Method 1 - without naming the constraints
Do not run this code. It is meant for you to look at.
create table l_employees
(employee_id integer primary key
,first_name varchar(10) not null
,last_name varchar(10)
,dept_code varchar(3)
,hire_date datetime
,credit_limit money
,phone_number varchar(4) unique
,manager_id integer references
l_employees(employee_id)
);
Access SQL: Method 2 - giving names to the constraints
Do not run this code. It is meant for you to look at.
create table l_employees
(employee_id integer constraint pk_employees
primary key
,first_name varchar(10) constraint nn_f_name
not null
,last_name varchar(10) constraint nn_l_name
not null
,dept_code varchar(3)
,hire_date datetime
,credit_limit money
,phone_number varchar(4) constraint unique_phone
unique
,manager_id integer constraint fk_manager
references
l_employees(employee_id)
);
Oracle
drop table valid_lunch_dates cascade constraints;
create table valid_lunch_dates as
select distinct lunch_date
from l_lunches;
insert into valid_lunch_dates
values ('15-dec-2005');
alter table valid_lunch_dates
add constraints pk_valid_lunch_dates
primary key (lunch_date);
alter table l_lunches
add constraints fk_lunch_date
foreign key (lunch_date)
references valid_lunch_dates (lunch_date);
Access
select distinct lunch_date
into valid_lunch_dates
from l_lunches;
insert into valid_lunch_dates
values (#15-dec-2005#);
alter table valid_lunch_dates
add constraints pk_valid_lunch_dates
primary key (lunch_date);
alter table l_lunches
add constraint fk_lunch_date
foreign key (lunch_date)
references valid_lunch_dates(lunch_date);
Oracle
drop table valid_phone_numbers cascade constraints;
create table valid_phone_numbers as
select distinct phone_number
from l_employees;
insert into valid_phone_numbers
values ('8888');
alter table valid_phone_numbers
add constraints pk_valid_phone_numbers
primary key (phone_number);
alter table l_employees
add constraints fk_l_employees
foreign key (phone_number)
references valid_phone_numbers (phone_number)
on delete cascade;
alter table l_lunch_items
add constraints ck_quantity
check (quantity in (1, 2));
alter table l_foods
add constraints ck_price_increase
check (price_increase < (price/4) );
alter table l_departments
add constraints ck_department_name
check (department_name is not null);
当在select * 后面添加额外的列时，必须在星号前添加该表的名字和一个句点
create table foods_2 as
select l_foods.*, price + price_increase as new_price
from l_foods;
视图
create or replace view step_1 as
select menu_item,
description,
price + price_increase as new_price
from l_foods;
select menu_item,
description,
new_price
from step_1
where new_price > 2.00
order by new_price;
null与其它数字相加，结果都为null
测试行函数
需要为此创建一个二元表，二元表被用作一个空的容器，它提供一个表的结构但是没有提供内容。
select 3 * 4 from dual;
Oracle
select n,
mod(n, 3)
from sec0908_test_numbers
order by n;
Access
select n,
n mod 3
from sec0908_test_numbers
order by n;
合并字符串
Oracle
select employee_id,
first_name || ' ' || last_name as full_name
from l_employees;
Access
select employee_id,
first_name & ' ' & last_name as full_name
from l_employees;
分离字符串
Oracle
create or replace view step_1 as
select full_name,
instr(full_name, ' ') as position_of_space
from sec0911;
select full_name,
substr(full_name, 1, position_of_space - 1) as first_name,
substr(full_name, position_of_space + 1) as last_name
from step_1;
Access
select full_name,
instr(full_name, ' ') as position_of_space
from sec0911;
select full_name,
mid(full_name, 1, position_of_space - 1) as first_name,
mid(full_name, position_of_space + 1) as last_name
from step_1;
Oracle
select employee_id,
first_name,
last_name,
'(415)643-' || phone_number as phone_number2
from l_employees;
Access
select employee_id,
first_name,
last_name,
'(415)643-' & phone_number as phone_number2
from l_employees;
Oracle
select first_name,
last_name,
hire_date,
floor((to_date('01-jan-2005') - hire_date) / 30) as months_with_the_company
from l_employees;
Access
select first_name,
last_name,
hire_date,
int((#01-jan-2005# - hire_date) / 30) as months_with_the_company
from l_employees;
删除时间
Oracle
select lunch_id,
lunch_date,
employee_id,
to_char(trunc(date_entered),
'dd-mon-yyyy hh:mi am') as date_entered
from l_lunches;
Access
select lunch_id,
lunch_date,
employee_id,
format(datevalue(date_entered),
'dd-mmm-yyyy hh:nn am/pm') as date_entered2
from l_lunches;
向日期添加天数，添加月数
Oracle
select to_date('07-mar-2011') + 2 as date_of from dual;
select add_months(to_date('07-mar-2011'), 2) as months_of from dual;
Access
select #07-mar-2011# +2 as date_of from dual;
select Dateadd('m', 2, #07-mar-2011#) as months_of from dual;
Oracle
select employee_id,
substr(first_name, 1, 1) || '. ' || last_name as employee_name
from l_employees
order by employee_id;
drop table ex0904a;
create table ex0904a
(bg_date date,
end_date date);
insert into ex0904a
values ('21-dec-2020', '21-mar-2021');
commit;
select * from ex0904a;
select end_date - bg_date as number_of_days
from ex0904a;
时间函数
Oracle
select user,
to_char(sysdate, 'day month dd, yyyy hh:mi am') as date_time
from dual;
Access
select CurrentUser() as user,
Now() as date_time
from dual;
替换null的函数
Oracle:nvl
select pkey,
nvl(num_col,0) as num_col2,
nvl(text_col, 'zilch') as text_col2,
nvl(date_col, '01-jan-1900') as date_col2
from sec1003;
select pkey,
nvl(to_char(num_col),'no number') as num_col2,
nvl(text_col, 'no text') as text_col2,
nvl(to_char(date_col), 'no date') as date_col2
from sec1003;
Access
select pkey,
nz(num_col, 0) as num_col2,
nz(text_col, 'zilch') as text_col2,
nz(date_col, #01-jan-1900#) as date_col2
from sec1003;
select pkey,
nz(num_col, 'no number') as num_col2,
nz(text_col, 'no text') as text_col2,
nz(date_col, 'no date') as date_col2
from sec1003;
Oracle
select pkey,
text_1,
text_2,
text_1 + text_2 as text_add,
text_1 - text_2 as text_subtract,
text_1 * text_2 as text_multiply,
text_1 / text_2 as text_divide
from sec1004;
Access Access在相加时需要指明数据类型,否则默认为文本的串联
select pkey,
text_1,
text_2,
Cint(text_1) + Cint(text_2) as text_add,
text_1 - text_2 as text_subtract,
text_1 * text_2 as text_multiply,
text_1 / text_2 as text_divide
from sec1004;
简单的数字模式
Oracle
create or replace view sec1007 as
select n,
3*n as multiple_of_3
from numbers_0_to_99;
select multiple_of_3 from sec1007
where multiple_of_3 between 50 and 250
order by multiple_of_3;
复杂的数字模式
select n as prime_number
from numbers_0_to_99
where n > 10
and not (mod(n, 2) = 0)
and not (mod(n, 3) = 0)
and not (mod(n, 5) = 0)
and not (mod(n, 7) = 0)
order by n;
列出一个星期的所有星期数
Oracle
drop table sec1010_constrants;
create table sec1010_constrants
(begin_date date);
insert into sec1010_constrants
values('06-mar-2006');
commit;
create or replace view sec1010 as
select begin_date + digit as days
from numbers_0_to_9,
sec1010_constrants;
select days,
to_char(days, 'dy') as abbreviated_day,
to_char(days, 'day') as full_day
from sec1010
order by days;
创建一个工作日历
drop table sec1011_boundaries;
create table sec1011_boundaries
(begin_date date,
end_date date);
insert into sec1011_boundaries
values ('01-Mar-2015', '01-Jun-2015');
commit;
drop table sec1011_calendar;
create table sec1011_calendar
as select n,
begin_date + n as date_1
from numbers_0_to_99,
sec1011_boundaries
where begin_date + n < end_date;
delete from sec1011_calendar
where to_char(date_1, 'dy') = 'SUN';
update sec1011_calendar
set date_1 = null
where to_char(date_1, 'dy') = 'Sat';
commit;
set null ' ' ;
column day_of_the_week format a15;
select to_char(date_1, 'day') as day_of_the_week,
to_char(date_1, 'mm/dd/yyyy') as work_day
from sec1011_calendar
order by n;
select sysdate - to_date('19-Nov-1979') from dual;
create view birthday2 as
select n, to_char(weekends, 'dy') as week_day,
to_char(weekends, 'month dd,yyyy') as full_day
from birthday_weekends
where to_char(weekends, 'dy') = 'Sat' or to_char(weekends, 'dy') = 'Sun';
create or replace view birthday_b as
select n, to_char(weekends, 'dy') as weekend_day, to_char(weekends, 'month dd, yyyy')
as full_day
from birthday_a
where to_char(weekends, 'dy') = 'Sat' or to_char(weekends, 'dy') = 'Sun'
or to_char(weekends, 'dy') is null
order by n;
列函数
除了count(*)计算null外，其它列函数都忽略null
max, min, count(*), count(column), count(distinct column)
sum, avg, stddev, var[iance]
select min(credit_limit),
max(credit_limit),
max(first_name),
max(last_name),
max(hire_date)
from l_employees;
select min(credit_limit),
max(credit_limit),
max(first_name),
max(last_name),
max(hire_date)
from l_employees
where employee_id between 202 and 206;
子查询
select employee_id,
first_name,
last_name,
credit_limit
from l_employees
where credit_limit = (select min(credit_limit) from l_employees)
order by employee_id;
count(column), count(distinct column)应用于值为null的列，会得到零的结果；而其它列函数应用于值为null的列，结果仍为null
select count(col_2) as count_col,
count(*) as count_rows,
max(col_2) as max,
min(col_2) as min,
sum(col_2) as sum,
avg(col_2) as avg
from sec1107;
多个列中相异值的数目
select count(distinct (manager_id || '*' || credit_limit))
from l_employees;
select sum(credit_limit),
avg(credit_limit)
from l_employees;
行函数中值与null相加结果是null；列函数中值与null相加，将会忽略null
select sum(col_2) + sum(col_3) as columns_added_first,
sum(col_2 + col_3) as rows_added_first
from sec1111;
select sum(col_2) + sum(col_3) as columns_added_first,
sum(nvl(col_2, 0) + nvl(col_3, 0)) as rows_added_first
from sec1111;
select sum((price * quantity)
+ nvl(tax, 0.07 * price * quantity)
+ nvl(shipping, 0.12 * price * quantity)) as total_invoice
from sec1112;
select count(*) as number_of_null
from l_employees
where manager_id is null;
select count(distinct trunc(date_entered))
from l_lunches;
Think and grow rich
select description, price
from l_foods
where price = (select min(price) from l_foods);
select count (distinct (supplier_id || product_code)) as rows_of_items
from l_lunch_items;
select sum(price) as sum_of_price,
sum(price_increase) as price_increase,
sum(price) + sum(price_increase) as sum_column_first,
sum(nvl(price, 0) + nvl(price_increase, 0)) as sum_rows_first
from l_foods;
select manager_id,
count(employee_id) as number_of_employees,
min(credit_limit) as minimum_credit,
max(credit_limit) as maximum_credit
from l_employees
where not (employee_id = 202)
group by manager_id
order by manager_id;
select manager_id,dept_code,
count(employee_id) as number_of_employees,
min(credit_limit) as minimum_credit,
max(credit_limit) as maximum_credit
from l_employees
where not (employee_id = 202)
group by manager_id, dept_code
order by manager_id, dept_code;
select col_2, col_3,
count(*),
count(col_4),
count(col_5)
from sec1204
group by col_2,
col_3
order by col_2,
col_3;
由函数或group by产生的数据称为总结数据；直接从表里取到的数据称为详细数据；二者不能出现在一个select语句中。因为他们的数据
行数就不一致。
select employee_id,
count(lunch_id) as number_of_lunches
from l_lunches
group by employee_id
order by employee_id;
为显示详细数据就要把它放到group by中去
select employee_id,
first_name,
last_name,
count(lunch_id) as number_of_lunches
from sec1210
group by employee_id, first_name, last_name
order by employee_id;
having子句 --它可以对由分组,总结产生的结果表,进行删除某些组
select supplier_id,
product_code,
sum(quantity) as total_servings
from l_lunch_items
group by supplier_id,
product_code
having sum(quantity) >= 10
order by supplier_id, product_code;
having子句与where子句的区别：having只能操作分组，总结后的结果表；having可以在它的条件中使用列函数，而where不可以。
已分组总结的3个约束条件
1、在group by子句中不能出现函数；2、在select子句中，distinct只能出现一次；3、不能将一个列函数应用于另一个列函数上。
select supplier_id,
count(product_code) as numbers_of_items
from l_foods
group by supplier_id
order by supplier_id;
select dept_code,
count(employee_id) as numbers_of_employees
from l_employees
group by dept_code
order by dept_code;
select dept_code, credit_limit,
count(employee_id) as numbers_of_employees
from l_employees
group by dept_code, credit_limit
order by dept_code, credit_limit;
create or replace view exc122a as
select lunch_id, to_char(date_entered, 'dd-mon-yyyy') as lunch_date_entered
from l_lunches;
select lunch_date_entered, count(lunch_id) as lunch_times
from exc122a
group by lunch_date_entered
order by lunch_date_entered;
create or replace view exc122c as
select employee_id, to_char(hire_date, 'yyyy') as hire_date2
from l_employees;
select hire_date2 as hire_date, count(employee_id) as employee_by_year
from exc122c
group by hire_date2
order by hire_date;
select n,
count(*) as number_of_occurences
from ex1203a
where n < 200
group by n
having count(*) > 1
order by n desc;
select first_name, last_name, state,
count(*) as number_of_people
from ex1203b
group by first_name, last_name, state
having state in ('CA', 'OR', 'WA')
order by first_name, last_name;
(内)连接 这是组合两个表最常见的方式 ，被连接的表可以是一对一的关系；一对多的关系；多对多的关系。未匹配连接条件的项会被从结果表中删除。
select a.fruit,
a.f_num,
b.c_num,
b.color
from sec1303_fruits a,
sec1303_colors b
where a.f_num = b.c_num
order by a.fruit;
select a.fruit,
a.f_num,
b.c_num,
b.color
from sec1304_fruits a,
sec1304_colors b
where a.f_num = b.c_num
order by a.fruit;
select a.fruit,
a.f_num,
b.c_num,
b.color
from sec1305_fruits a,
sec1305_colors b
where a.f_num = b.c_num
order by a.fruit;
使用新语法编写连接条件－－在from子句中编写连接条件 Oracle8i不支持；Oracle9i和Acces支持。
select a.fruit,
a.f_num,
b.c_num,
b.color
from sec1306_fruits a
inner join sec1306_colors b
on a.f_num = b.c_num
order by a.fruit;
联合使用连接
select a.employee_id,
a.first_name,
a.last_name,
count(*) as number_of_lunches
from l_employees a,
l_lunches b
where a.employee_id = b.employee_id
and not (a.employee_id = 208)
group by a.employee_id, a.first_name, a.last_name
having count(*) > 1
order by a.employee_id;
select a.employee_id,
a.first_name,
a.last_name,
b.lunch_date,
d.description,
c.quantity
from l_employees a,
l_lunches b,
l_lunch_items c,
l_foods d
where a.employee_id = b.employee_id
and b.lunch_id = c.lunch_id
and c.supplier_id = d.supplier_id
and c.product_code = d.product_code
and a.dept_code = 'shp'
order by a.employee_id,
b.lunch_date;
select a.employee_id,
a.first_name,
a.last_name,
b.lunch_date,
d.description,
c.quantity
from l_employees a,
l_lunches b,
l_lunch_items c,
l_foods d
where a.employee_id = b.employee_id
and b.lunch_id = c.lunch_id
and c.supplier_id = d.supplier_id
and c.product_code = d.product_code
order by a.employee_id,
b.lunch_date;
Oracle
select b.supplier_name,
a.description,
a.price + nvl(a.price_increase, 0.10) as total_price
from l_foods a,
l_suppliers b
where a.supplier_id = b.supplier_id
order by a.price + nvl(a.price_increase, 0.10);
Access
select b.supplier_name,
a.description,
a.price +nz(a.price_increase, 0.10) as total_pric,
a.price,
a.price_increase
from l_foods a,
l_suppliers b
where a.supplier_id = b.supplier_id
order by a.price + nz(a.price_increase, 0.10);
select b.description,
sum(a.quantity) as quantity_of_food
from l_lunch_items a,
l_foods b
where a.supplier_id = b.supplier_id,
and a.product_code = b.product_code
group by a.supplier_id, a.product_code, b.description
order by b.description;
Oracle
select b.description,
c.lunch_date,
sum(a.quantity) as quantity_of_food
from l_lunch_items a,
l_foods b,
l_lunches c
where a.supplier_id = b.supplier_id
and a.product_code = b.product_code
and a.lunch_id = c.lunch_id
group by a.supplier_id, a.product_code, b.description, c.lunch_date
having to_char( c.lunch_date , 'dd-Mon-yyyy') = '16-Nov-2005'
order by b.description;
Access
select b.description,
c.lunch_date,
sum(a.quantity) as quantity_of_food
from l_lunch_items a,
l_foods b,
l_lunches c
where a.supplier_id = b.supplier_id
and a.product_code = b.product_code
and a.lunch_id = c.lunch_id
group by a.supplier_id, a.product_code, b.description, c.lunch_date
having format( c.lunch_date , 'dd-mmm-yyyy') = '16-Nov-2005'
order by b.description;
select a.n
from ex1303a a,
ex1303b b
where a.n = b.n
order by a.n;
select a.n
from ex1303a a,
ex1303b b,
ex1303c c
where a.n = b.n
and b.n = c.n
order by a.n;
外连接：左外连接，右外连接，全外连接
左外连接
Oracle SQL
select a.*,
b.*
from twos a,
threes b
where a.number_2 = b.number_3 (+)
order by a.number_2;
Oracle9i & Access
select a.*,
b.*
from twos a
left outer join threes b
on a.number_2 = b.number_3
order by a.number_2;
右外连接
Oracle SQL
select a.*,
b.*
from twos a,
threes b
where a.number_2 (+) = b.number_3
order by a.number_2;
Oracle9i & Access
select a.*,
b.*
from twos a
right outer join threes b
on a.number_2 = b.number_3
order by a.number_2;
全外连接
Oracle
select a.*,
b.*
from twos a,
threes b
where a.number_2 = b.number_3 (+)
union
select c.*,
d.*
from twos c,
threes d
where c.number_2 (+) = d.number_3;
Oracle9i
select a.*,
b.*
from twos a
full outer join threes b
on a.number_2 = b.number_3;
Oracle9i & Access
select a.*,
b.*
from twos a
left outer join threes b
on a.number_2 = b.number_3
union
select c.*,
d.*
from twos c
right outer join threes d
on c.number_2 = d.number_3;
带有匹配列的两个表的union的例子
select a.number_1,
a.word_1 as text_1,
a.date_1
from sec1407_first a
union
select b.number_2,
b.word_2,
b.date_2
from sec1407_second b
order by number_1;
外连接的应用--零计数
select a.employee_id,
a.first_name,
a.last_name,
count(b.lunch_id) as number_of_lunches
from l_employees a,
l_lunches b
where a.employee_id = b.employee_id (+)
group by a.employee_id, a.first_name, a.last_name
order by a.employee_id;
按排序顺序全外连接
Oracle
select a.*,
b.*,
nvl(a.number_2, b.number_3) as sort_order
from twos a,
threes b
where a.number_2 = b.number_3 (+)
union
select c.*,
d.*,
nvl(c.number_2, d.number_3) as sort_order
from twos c,
threes d
where c.number_2 (+) = d.number_3
order by sort_order;
Access
select a.*,
b.*,
nz(a.number_2, b.number_3) * 1 as sort_order
from twos a
left outer join threes b
on a.number_2 = b.number_3
union
select c.*,
d.*,
nz(c.number_2, d.number_3) * 1 as sort_order
from twos c
right outer join threes d
on c.number_2 = d.number_3
order by sort_order;
Oracle
select a.n,
b.n,
count(b.n) as times_occur
from numbers_1_to_1000 a,
ex1203a b
where a.n = b.n (+)
group by a.n, b.n
having not (count(b.n) = 1)
order by a.n;
Oracle9i & Access
select a.n,
b.n,
count(b.n) as times_occur
from numbers_1_to_1000 a
left outer join ex1203a b
on a.n = b.n
group by a.n, b.n
having not (count(b.n) = 1)
order by a.n;
使用两列或多列进行比较
Oracle
select a.first_col,
a.second_col,
b.first_col,
b.second_col
from sec1412a a,
sec1412b b
where a.first_col = b.first_col (+),
and a.second_col = b.second_col (+)
and b.first_col is null
order by a.first_col, a.second_col;
Oracle9i & Access
select a.first_col,
a.second_col,
b.first_col,
b.second_col
from sec1412a a
left outer join sec1412b b
on a.first_col = b.first_col
and a.second_col = b.second_col
where b.first_col is null
order by a.first_col, a.second_col;
Oracle
select a.supplier_id,
a.product_code,
a.description
from l_foods a,
l_lunch_items b
where a.supplier_id = b.supplier_id (+)
and a.product_code = b.product_code (+)
and b.supplier_id is null
order by a.supplier_id, a.product_code;
Oracle9i & Access
select a.supplier_id,
a.product_code,
a.description
from l_foods a
left outer join l_lunch_items b
on a.supplier_id = b.supplier_id
and a.product_code = b.product_code
where b.supplier_id is null
order by a.supplier_id, a.product_code;
Access
select a.employee_id,
a.first_name,
a.last_name
from l_employees a
left outer join l_lunches b
on a.employee_id = b.employee_id
where b.employee_id is null
order by a.employee_id;
Access
select a.supplier_id,
a.supplier_name
from l_suppliers a
left outer join l_foods b
on a.supplier_id = b.supplier_id
where b.supplier_id is null
order by a.supplier_id;
Access
select a.supplier_id,
a.supplier_name,
count(b.product_code) as number_of_foods
from l_suppliers a,
l_foods b
where a.supplier_id = b.supplier_id
group by a.supplier_id, supplier_name
order by a.supplier_id;
select a.supplier_id,
a.supplier_name,
count(b.product_code) as number_of_foods
from l_suppliers a
left outer join l_foods b
on a.supplier_id = b.supplier_id
group by a.supplier_id, supplier_name
order by a.supplier_id;
Access
select a.dept_code,
a.department_name,
count(b.employee_id) as number_of_employees
from l_departments a
left outer join l_employees b
on a.dept_code = b.dept_code
group by a.dept_code, a.department_name
order by a.dept_code;
Access
select a.description,
a.supplier_id,
a.product_code,
sum(nz(b.quantity, 0)) as quantity_of_foods
from l_foods a
left outer join l_lunch_items b
on a.supplier_id = b.supplier_id
and a.product_code = b.product_code
group by a.supplier_id, a.product_code, a.description
order by a.description;
Access
select a.*,
b.*,
nz(a.number_1, b.number_2) as sort_number
from sec1407_first a
left outer join sec1407_second b
on a.number_1 = b.number_2
union
select c.*,
d.*,
nz(c.number_1, d.number_2) as sort_number
from sec1407_first c
right outer join sec1407_second d
on c.number_1 = d.number_2
order by sort_number;
Union的使用
Oracle & Access
select a.last_name,
a.first_name,
count(b.lunch_id) as number_of_lunches
from l_employees a,
l_lunches b
where a.employee_id = b.employee_id
group by a.last_name,
a.first_name
having count(b.lunch_id) < 5
union
select 'patrick',
'john',
0
from dual
order by last_name;
Union中的order by 子句--使用第一个子句的列名，列别名、union中列的位置编号
select number_1,
word_1,
date_1
from sec1407_first
union
select * from sec1407_second
order by word_1;
select number_1,
word_1,
date_1
from sec1407_first
union
select * from sec1407_second
order by 2;
创建一个包含union的表或视图
Oracle
create or replace view sec1505a as --不能使用order by，与union同时出现会导致问题
select *
from sec1407_first
union
select *
from sec1407_second;
在Access中先通过保存查询来创建视图，通过视创建表
union中的自动数据类型转换
select number_n7 as number_col,
text_t7 as text_column
from sec1506_first
union
select number_n2,
text_t2
from sec1506_second;
不同数据类型表的union
Oracle
create or replace view sec1507_union as
select to_char(number_n7) as first_column,
text_t7 as second_column
from sec1506_first
union
select text_t2,
to_char(number_n2)
from sec1506_second;
Access
select format(number_n7) as first_column,
text_t7 as second_column
from sec1506_first
union
select text_t2,
format(number_n2)
from sec1506_second;
有不同列数的两个表的union--通过补齐一个表的列数来变通实现
Oracle8i
select a.number_col,
a.text_col,
a.date_col
from sec1508_first a
union
select b.number_col,
b.text_col,
to_date(null)
from sec1508_second b;
Oracle9i & Access
select a.number_col,
a.text_col,
a.date_col
from sec1508_first a
union
select b.number_col,
b.text_col,
null
from sec1508_second b;
union的应用--确定两个表是否相同。两个有主键的表或是没有重复行的表，它们行数与它们进行union后的结果表行数相同，表明这两个表相同
union的应用之一-- 在union中使用文字列来确定数据的来源（来自表一还是表二）
select number_1,
word_1,
date_1,
'from the first table' as source_of_the_data
from sec1407_first
union all
select number_2,
word_2,
date_2,
'from the second table'
from sec1407_second
order by number_1;
使用union来标记异常情况、警告和错误的附加消息
select description,
price,
'expensive items' as message
from l_foods
where price > 2.00
union all
select description,
price,
' '
from l_foods
where not(price > 2.00)
or price is null
order by description;
union应用之一--将数据从一个列中分到两个不同的列中
Oracle8i
select item,
to_number(null) as debits,
cost as credits
from sec1512_finances
where cost > 0
union all
select item,
cost,
to_number(null)
from sec1512_finances
where cost < 0 or cost is null
order by item;
Oracle9i & Access
select item,
null as debits,
cost as credits
from sec1512_finances
where cost > 0
union all
select item,
cost,
null
from sec1512_finances
where cost < 0 or cost is null
order by item;
union应用之一--将两个函数应用到数据的不同部分
select menu_item,
description,
price,
price * 1.05 as new_price
from l_foods
where price > 2.00
union all
select menu_item,
description,
price,
price * 1.1
from l_foods
where price <= 2.00
order by menu_item;
使用union连接很多表
select 'a' as letters
from dual
union
select 'b'
from dual
union
select 'c'
from dual;
在oracle中设置交集--intersect子句
select number_1,
word_1,
date_1
from sec1407_first
intersect
select number_2,
word_2,
date_2
from sec1407_second
order by number_1;
在oracle中设置差集--minus子句
select number_1,
word_1,
date_1
from sec1407_first
minus
select number_2,
word_2,
date_2
from sec1407_second
order by number_1;
select item,
price,
date_purchased,
'from_sue' as person
from ex1501_sue
union all
select null,
cost,
null,
'from_bob'
from ex1501_bob;
select item,
price,
date_purchased,
person,
'higher than $50' as message
from ex151c
where price < -50
union
select item,
price,
date_purchased,
person,
null
from ex151c
where price >= -50
order by price;
select item,
price,
date_purchased,
person,
message,
price as credits,
' ' as debits
from ex151d
where price > 0
union
select item,
price,
date_purchased,
person,
message,
' ',
price
from ex151d
where price <=0
order by date_purchased;
create or replace view ex152b as
select *
from l_lunch_items
union
select *
from ex1502_lunch_items;
select a.*,
b.*
from l_lunch_items a
left outer join ex1502_lunch_items b
on a.lunch_id = b.lunch_id
and a.item_number = b.item_number
and a.supplier_id = b.supplier_id
and a.product_code = b.product_code
and a.quantity = b.quantity
where b.lunch_id is null
union
select c.*,
d.*
from l_lunch_items c
right outer join ex1502_lunch_items d
on c.lunch_id = d.lunch_id
and c.item_number = d.item_number
and c.supplier_id = d.supplier_id
and c.product_code = d.product_code
and c.quantity = d.quantity
where c.lunch_id is null
order by a.lunch_id;
交叉连接
select a.*,
b.*
from sec1601_first a,
sec1601_second b;
数据库一次只能处理表的一行数据，自连接则突破了这一个限制！！！
Oracle
select emp.employee_id,
emp.last_name,
emp.phone_number,
boss.last_name as manager_name,
boss.phone_number as manager_phone
from l_employees emp,
l_employees boss
where emp.manager_id = boss.employee_id (+)
order by emp.employee_id;
生成从0-999的数字----使用一个自连接、一个交叉连接实现
Oracle
drop table numbers_0_to_9;
create table numbers_0_to_9
(digit number(1));
Access
drop table numbers_0_to_9;
create table numbers_0_to_9
(digit smallint);
Oracle & Access
insert into numbers_0_to_9 values (0);
insert into numbers_0_to_9 values (1);
insert into numbers_0_to_9 values (2);
insert into numbers_0_to_9 values (3);
insert into numbers_0_to_9 values (4);
insert into numbers_0_to_9 values (5);
insert into numbers_0_to_9 values (6);
insert into numbers_0_to_9 values (7);
insert into numbers_0_to_9 values (8);
insert into numbers_0_to_9 values (9);
Oracle
drop table numbers_0_to_999;
create table numbers_0_to_999 as
select ((a.digit * 100 ) + (b.digit * 10 ) + c.digit) as n
from numbers_0_to_9 a,
numbers_0_to_9 b,
numbers_0_to_9 c;
在Oracle和Access中添加行编号的方法
Oracle
1.从原select 语句中创建一个新的视图；2.使用rownum添加一个行编号列。
Access
1.从原select 语句中创建一个新的表；2.使用alter table命令添加一个新的列。
Oracle
create or replace view sec1611 as
select price,
description
from l_foods
where price > 1.75
order by price,
description;
select rownum as line_number,
a.*
from sec1611 a
order by rownum;
Access
select price,
description
into sec1611
from l_foods
where price > 1.75
order by price,
description;
alter table sec1611
add column line_number counter;
select emp.employee_id,
emp.first_name,
emp.hire_date,
boss.employee_id,
boss.first_name,
boss.hire_date
from l_employees emp,
l_employees boss
where emp.manager_id = boss.employee_id and
boss.hire_date > emp.hire_date;
create or replace view ex162c as
select a.description,
b.lunch_id
from l_foods a,
l_lunch_items b
where a.supplier_id = b.supplier_id
and a.product_code = b.product_code
and a.description in ('dessert', 'coffee');
select a.lunch_id
from ex162c a,
ex162c b
where a.lunch_id = b.lunch_id
and a.description = 'coffee'
and b.description = 'dessert';
if-then-else和子查询
Oracle中的decode函数：DECODE(tested_value,
if_1, then_1,
if_2, then_2,
...
default_value)
Access中的iif函数： iif(true_of_false_expression, true_value, false_value)
子查询
select a.description,
a.price
from l_foods a
where a.price < (select avg(b.price)
from l_foods b)
order by a.description;
select number_2,
word_2
from twos
where number_2 in (select number_3
from threes);
update l_employees a
set a.manager_id = (select b.new_manager
from sec1816_changes b
where a.employee_id = b.emp_id)
where a.employee_id in (select c.emp_id
from sec1816_changes c);
select *
from sec1407_first
where (number_1 & '*' & word_1 &'*' & date_1)
not in (select (number_2 & '*' & word_2 & '*' & date_2)
from sec1407_second);