我在北大青鸟学习的时候随便整理的资料 SQL SERVER 2005
--基础:
select * from area
select * from cgfpmxb
/*可以包含任意表达式*/
/*指定别名可以直接写 也可以 as aa */
/*将两个字段合并成一个字段的方法 用||*/
/*可以在语句中加一个字符串 用单引号括起来*/
select itemid || itemname || amount || 'hello'
taxamount,amount*taxratio/10.00 "aa"
from cgfpmxb
/*null的使用*/
select * from djzhb
where ca is not null
/*在计算表达式的时候 只要表达式中有null 整个结果都是NULL*/
select amount , amount+null from cgfpmxb
/*为防止表达式因出现空值而 影响运算 可以为空值指定一个值,
当在计算表达式的时候,如果遇到空值,就会用指定的数值替代*/
select amount,amount+nvl(null,0) from cgfpmxb
/*nvl在日期中的处理,很重要*/
/*如何将字符日期,转换成日期格式*/
select nvl(checkdate, to_date('1900-01-01','yyyy-mm-dd'))
from djzhb
where(ca is null)
/*获取系统时间函数*/
select sysdate
--可以将查询结果重复记录消灭掉,只要我们选取的两列相同,就被视作重复
select distinct distcode ,distname from djzhb
/*获取前两行数据*/
/*rownum式系统函数,可以为表产生一个id列,从而在where中指定id来判断行数*/
select * from
(select rownum id, djzhb. * from djzhb)
where id<3
/*排序*/
/* 升序:asc 降序:desc*/
/*可以在排序指定别名*/
select distcode code,distname from djzhb
order by distcode asc
select distcode code,distname ,ca from djzhb
order by ca desc
/*可以指定多列来排序,*/
/* distinct可以去掉重复,你选几列就按照几列来判断是否相同,只有选出的列的字段值都相同才被视为相同*/
select distinct distcode code,distname ,ca from djzhb
order by distcode,distname desc
-- 一般对一列使用distinct,,在MSSQL中直接就会按照一列进行排序
select distinct distcode from djzhb
order by distcode
/*where用法*/
select distinct distcode,distname,ordertype
from djzhb
where distcode='63345'
/*where处理日期*/
select distcode , distname,ordertype, uploaddate
from djzhb
where uplorddate= to_date('2006-09-25','yyyy-mm-dd')
/* where可以使用<> <= >= 等符号*/
select * from area
where di >=4
/* 可以使用between和in*/
select * from area
where id between 1 and 4
/* 离散的数字 和SQL一样*/
select * from area
where id in (1,4,5)
/*模糊查询*/
-- %表示任意字符
select * from province
where name_c like '%海' /*表示查找最后一个字式海的 */
/* _表示单个字符*/
select * from province
where name_c like '北_'
/* 转意字符,必须自己定义
如果字段中含有 %这个符号 ,要寻找 含有这个符号的那行,就必须使用转意符号,*/
select * from area
where name_en like '%\%' escape '\'
/*可以指定任意的转义符号*/
select * from area
where name_en like '%+%' escape '+'/* 指定 +为转意符号,表示查询以%结束的字段 */
-- 可以使用 and 或 or 来 连接 多个查询条件
select * from area
where id< 4 or name_cn like '%华'
/*函数*/
/* 1*/
/*字符串函数:*/
-- lower
-- upper
-- initcap
-- concat
-- substr
-- length
-- nvl
/*转换成
小写
大写
substr取出某字段值,从第一个字符开始,连续取出10个字符
取出字符串的长度
判断其是否包含GUIDE 如果包含就返回第一次找到的在字段中的起始位置
将两个字符串合并成一个新的字符串*/
select item_number,
lower(name_e),
initcap(name_e),
substr(name_e,1,5),
length(name_e),
instr(name_e,'GUIDE')
concat(name_e,name_c)
from item_master_tb
/*
2*/
/*数值型函数
round 小数点后取两位,最后一位根据四舍五入来确定大小(如果式负数 会影响到整数部分)
trunc 取小数点后指定的位数,把小数点后面的直接截掉,不会四舍五入*/
/*dual 是假表*/
select round(43.345,2),
round(43.345,1),
round(43.345,-1),
trunc(45.546,2),
trunc(45.3455,-1)
from dual
/*3*/
/* 时间函数
sysdate获取系统时间*/
select sysdate from dual
select sysdate+600 from dual /*默认加的式天数*/
/* 连个时间的相减
表示加六个月
获取下个星期的第一天式几号
当前月的最后一天,很重要,可以在编程的时候用到
按照月份四舍五入 只显示到月份
按照天 四舍五入 只显示到天 时间就被舍弃了
按照年 四舍五入 只显示年
year表示对年四舍五入
dd表示明天*/
select months_between( to_date('2010-01-01','yyyy-mm-dd'),sysdate),
add_months(sysdate,6),
next_day(sysdate,1),
last_day(sysdate),
last_day(to_date('2008-02-11','yyyy-mm-dd')),
round(sysdate,'MONTH'),
round(sysdate,'day'),
round(sysdate,'year'),
round(sysdate,'dd'),
from dual
/* 4
时间的转换
YYYY年 英文表示
YEAR 数字表示
MM 月 英文表示
MONTH月 英文
DY星期 中文
DD星期 英文
DAY日期
MI 分钟
SS 秒
HH24 24小时时间表示
HH默认的小时表示*/
select to_char(sysdate,'YEAR-MONTH-DAY'),
tO_char(sysdate,'YEAR-MONTH-DY'),
tO_char(sysdate,'YEAR-MONTH-ddspth'),
to_char(sysdate,'yyyy-mm-dd HH24:MI:SS')
from dual
/*
5
将数字转换成字符串
第二个参数 转换的格式
9表示0-9的任意一个数字,超过就无法显示,
0表示占位,如果不够用0补齐
1显示1234
2第二个数字的位数超过格式规定的位数,所以无法显示
3少于规定的格式两个 ,所以只显示12
4 0表示占位
5 转换小数,要在小数点前后定义格式
6 L表示转换成 人民币
$表示美元
*/
select to_char(1234,'9999'),
to_char(1245677,'9999'),
to_char(12,'9999'),
to_char(234,'0000'),
to_char(23.234,'99.0000'),
to_char(334455.33,'L999,999,999.00')
from dual
/* 6
to_date函数
将字符串转换成日期
前面已经使用*/
select to_date('10 9月 1992','dd Month yyyy')
from dual
/* 7
to_number函数
将字符串转换成数字格式, 字符串最好式数字*/
select to_number('123') from dual
/*
8
函数可以嵌套*/
select to_char(
next_day(
add_months(sysdate,6)
,1)
,'DAY,MONTH DDTH,YYYY'
from dual
/* 多表查询*/
select * from city
/* 外连接*/
select c.city_no,p.name_c pname,c.name_c cname
from city c inner join province p
on c.province_no=p.province_no
selcet * from item_master_tb
/* 内连接*/
select a.item_number,a.description,d.department_name
from item_master_tb a inner join dept_master d
on a.franchise=d.department_code
select count(*) from item_master_tb
where itemgroup6 is null
select count(*)
from item_master_tb
group by group_code
having count(*)>1
where itemgroup6 is null
/*左连接*/
/*数据要区分大小写的*/
/* 和sql语言一样的*/
select a.item_number,a.description,g.description
from item_master_tb a
left join itemgroup_master g
on a.itemgroup6 =g.group_code
where g.group_type='S6'
t
/*另一种写法:*/
/* 要能看懂
+放在右边是左连接 放在左边是右连接*/
select a.item_number,a.description,g.description
from item_master_tb a , itemgroup_master g
where a.itemgroup6=g.group_code(+)
and g.group_type= 'S6'
select *from itemgroup_master
select s7.group_code, s7.description,s6.description
from itemgroup_master s7 inner join itemgroup_master s6
on s7.parent_code=s6.group_code
where s7.parent_code is not null
/* 分组查询
重要
select
group by
having
order by
count(*)计算所有的记录的行数
count(ca) 计算某个字段的行数
只有出现在group by 后面的才能出现在select,oreder by中,这是规定
having 和 order by 是可选的
having 用于进一步过滤条件,就是对分组好之后 再进一步筛选
分组查询经常用到的函数:*/
avg sum min max count()
/* count()函数*/
select count(*) total ,count(ca)
from djzhb
select * from item_master_tb
/*
一般都是先分组再计算平均 或 总值 */
select franchise ,
avg(dist_price),
max(dist_price),
min(dist_price),
count(item_number)
from item_master_tb
group by franchise
having avg(dist_price)>1000
order by franchise
/* 子查询
超级重要
/*
先执行嵌套在里面的 里面的先把数据进行第一步筛选 外层 以 里层为 基础 进行 查询*/
单行数据 *//*直接用 '='*/
select item_number,
last_updated_time
from item_master_tb
where last_updated_time
=
(select max(last_updated_time) from item_master_tb)
/* 多行数据 要用 in
也就说说 嵌套在里面的查询会返回多条记录 这种情况下要用in */
select item_number, franchise from item_master_tb
where franchise in (
select deparment_code from dept_master
where department_type=0)
create user wangshukui
identified by "wangshukui"
profile DEFAULT;
grant connect to wangshukui with admin option;
grant dba to wangshukui with admin option;
grant resource to wangshukui with admin option;
grant unlimited tablespace to wangshukui with admin option;
select * from customers
select * from sales
/*连接三个表*/
select s.productid,s.customerid,s.saleprice,p.productname,c.firstname,c.lastname,s.saledate
from sales s
inner join customers c
on c.customerid=s.customerid
inner join products p
on p.productid=s.productid
where s.saledate >=
to_date('2005-10-01','yyyy-mm-dd')
and
s.saledate <= to_date('2005-10-30','yyyy-mm-dd')
select sales s
select s.customerid
from sales s
group by s.customerid
having count (customerid)>=2
/*
必须要学好 uml 与 设计模式 重点中的重点
把自己心里想说的话 用工具来表达
做成功的软件开发
充分调动思维
数据库设计概念
创建表*/
/* 删除表*/
drop table S_dept
/* 创建*/
create table S_dept
(
/*定义主键*/
id number(7) constraint s_dept_pk primary key, /*定义约束,式针对列的*/
/*not null 约束*/
name varchar2(25) constraint s_dept_name_nn not null,
region_id number(7),
/* 下面的约束式表级的,不是在列上,是定义好之后 在作用到表的列上,可以作用多个列*/
constraint s_dept_name_region_id_uk unique (name,region_id) /*定义表级的约束*/
);
create table s_emp
(
id number(7) primary key,
last_name varchar(25) not null,
first_name varchar(25),
/* 两个约束 not null 也是约束*/
userid varchar2(8) constraint s_emp_userid_nn not null constraint s_emp_userid_uk unique,
/*默认约束,会直接使用系统默认的时间*/
start_date date default sysdate,
comments varchar2(25),
manager_id number(7),
title varchar2(25),
/*引用约束*/
dept_id number(7) constraint s_emp_dept_id_fk references S_dept(id),
salary number(11,2),
/*check约束*/
commission number(4,2) constraint s_emp_commission_ck check(commmision in(10,12.5,15,20))
);
/*拷贝另一个用户的表*/
/*只能拷贝数据,表的主键信息没有*/
create table distributor_mastor as select * from interface.distributor_master
select* from distributor_mastor
/* 在已有的表上面添加约束*/
add constraint distributor_masterpk primary key (dist_id)
/* oracle中的约束:
NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
check*/
/* default不是约束 修改一个表的列的默认值的方法:*/
alter table s_emp
/*modify 是修改列的*/
modify start_date default sysdate
/*
第九章 数据字典
了解
系统表
User tables*/
select distinct object_type
from user_objects
select * from user_objects
/* 查询系统中的所有对象名字*/
select object_name from user_objects
select object_name from user_objects
where object_type='TABLE'
/* 用户表*/
Select * from user_tables
select constraint_name,constraint_type,search_condition,
r_constrinte_name from user_constraints where table_name='s_emp'
/*
操纵数据
insert
update
delete
commit
savepoint
rellback*/
/*
建表
只有批处理的时候需要分号,一般处理可以不用分号*/
create table lesson_test
(
std_id int not null,
msg_id int not null,
msg_text varchar2(200) not null,
updatetime date default sysdate,
primary key (std_id,msg_id)
)
/* 插入值:*/
insert into lesson_test
values(48,1,'my first message',sysdate)
select * from lesson_test
/* 指定null 可以直接写出来*/
insert into lesson_test values(48,2,'myu',null)
/* 向表中插入部分段的数据
没有指定则用默认值来代替*/
insert into lesson_test(std_id, msg_id ,msg_text ) values(48,3,'myu')
insert into lesson_test values(48,2,'myu',null,sysdate)
/* 插入日期,通过转换 转换成日期*/
insert into lesson_test values(48,4,'dfdf', to_date('2008-10-10','yyyy-mm-dd'))
select * from lesson_test
/* 把一个表中的数据全部提取出来赋给另一个表,
第二个表必须要和第一个表的字段的类型一样*/
create table lesson_test_history
(
std_id int not null,
msg_id int not null,
msg_text varchar2(200) not null,
updatetime date default sysdate,
primary key (std_id,msg_id)
)
/* 复制语句:*/
insert into lesson_test_history
select * from lesson_test
select * from lesson_test_history
/*
修改数据
update
set*/
/* ||相当于 c sharp 中的 + 运算符*/
update lesson_test
set msg_text=msg_text|| 'Modified'
where std_id=48 and msg_id=4
select * from lesson_test
/* 删除数据
删除表中的相关数据*/
delete from
/* 可以通过where指定要输出的行*/
delete from lesson_test
where updatetime< to_date('2008-05-01','yyyy-mm-dd')
/*
事务
先删除表,此时数据为空*/
delete from lesson_test
/* 对这个表设置一个回滚点*/
savepoint A;
/* 插入一条记录*/
insert into lesson_test(std_id,msg_id,msg_text,updatetime)
values(48,6,'dddddd',to_date('2008-04-04','yyyy-mm-dd'))
select * from lesson_test
/* 回滚到A点 A点是没有数据的*/
rollback to A
/* 查看发现回滚到A的时候表的信息*/
select * from lesson_test
/*再次回滚,会把上面一条语句的信息 回滚 上面是 delete 语句 */
rollback
/* 查询后发现 以前的数据又找回来了 */
select * from lesson_test
/* commit
会把数据提交给表 真正的修改表,执行查询之后 只有点击commit 才能真正的改变表中的数据
因为所有的数据 都会 利用日志 来 保存我们的任何操作,只有 点击上面的commit 才真正写入到表中
我们删除表中数据的时候 其实是在虚拟表中操作的 ,不会影响真正的表,只有点击上面的commit按钮
才会写入到表中*/
insert into lesson_test(std_id,msg_id,msg_text,updatetime)
values(48,5,'dddddd',to_date('2008-04-04','yyyy-mm-dd'))
commit
/* 是提交上面的操作 数据提交后 前面定义的 回滚点都会被删除*/
select * from lesson_test order by msg_id
/* 修改表*/
/* 前面是怎么创建表的 现在是对已经存在的表 修改
修改列 约束
增加列 约束
删除列 约束
删除表*/
alter table /*表*/
add modify drop /*列*/
enabled
/* 添加列*/
select * from lesson_test
alter table lesson_test
add comments varchar(255) null;/*添加列*/
/* 删除列*/
drop column comments
/* 修改列
只能向大的方向修改 例如 原来列的字段式100个字符串 类型 ,只能向大的方向修改 例如200*/
alter table lesson_test
modify msg_text varchar2(200)
select * from lesson_test
/*
主键 外键*/
drop table lesson_test
create table lesson_test
(
std_id int not null,
msg_id int not null,
msg_text varchar2(200) not null,
updatetime date default sysdate,
primary key (std_id)
)
create table lesson_test_type
(
msg_type int not null,
msg_typename varchar(200),
primary key (msg_type)
)
insert into lesson_test_type
values(2,'dfdff')
select * from lesson_test_type
/* 添加外键*/
alter table lesson_test
add constraint s_lesson_text_id_fk
foreign key (msg_type)
references lesson_test_type (msg_type)
/*
禁用约束*/
alter table lesson_test
disable constraint s_lesson_text_id_fk
/* 启用约束*/
alter table lesson_test
enable constraint s_lesson_text_id_fk
/* 删除表,会级联 把一切的约束 以及与表有关的都级联删除*/
drop table lesson_test
cascade constraints
/* 修改表名*/
rename lesson_test to lessonabc
select* from lessonabc
delete table lessonabc
rollback
/* 不会真正删除,回滚会回到原数据,式删除临时表*/
truncate table lessonabc
/* 真正删除数据,删除的式实际表中数据*/
/* sequences
专门用于产生一个不重复的数字序列 用于使得列唯一 */
create table dept(
id int not null,
name varchar2(200),
primary key (id))
insert into dept values(wangshukui_test_sql.nextval,'test')
select * from dept
/* 建立sequence的语句:*/
-- Create sequence
create sequence WANGSHUKUI_TEST_SQL
minvalue 1
maxvalue 9999999
start with 201
increment by 1
cache 200
cycle;
--练习:自己创建一个sequence
create sequence sq_first1
increment by 1
start with 6534
maxvalue 34356667777888
cache 200
cycle;
insert into dept values(sq_first1.nextval,'test')
select * from dept
/*视图:
好处:
可以防止 直接访问 数据库, 可以保护数据 只让员工访问基础级别的数据 把其封装在试图中
*/
/*简单试图:如果涉及的表 没有 函数 只有一张表 等复杂的内容
复杂试图 : 多表 还有函数 等 多种信息*/
/*创建视图*/
create or replace view vw_lwsson_test
as
select std_id,msg_id,msg_text
from lessonabc
with read only --表示创建只读视图
select * from vw_lwsson_test
select * from lessonabc
insert into lessonabc values(1,2,'dff',sysdate)
--修改视图
update vw_lwsson_test
set msg_text=msg_text || 'mod by view'
where std_id=48 and msg_id=1
select * from vw_lwsson_test
--复杂视图
--有多个表
create or replace view vw_lesson_test2
as
select rownum no,l.std_id,l.msg_id,l.msg_text,t.msg_typename
from lesson_test_history l,lesson_test_type t
where l.msg_id= t.msg_type
--指定别名 :
--也可以看做一个视图
--如果其他用户的某个表经常使用 可以给其指定一个简单的名称 关键字 synonym
create synonym province
for interface.province
select * from province
select * from lessonabc
--当然也可以给当前用户的某个表在起一个名字
--查询这两个名字都可以得到这个表的信息
create synonym lesson
for lessonabc
select * from lesson
select * from lessonabc
/*
嘿嘿
获取别的用户的表*/
select * from liushuai.lesson_test_type
/* 索引
就是数据结构里面的B+ 树
index
通过索引查询数据 可以缩短时间
有些索引 系统自动创建*/
automatically
unique
non-unique
single column
concatenated
/* 为某个表的一列创建索引*/
create index temp_lesson_test
on lesson_test_type(msg_type)
/* 删除索引*/
drop index temp_lesson_test
/* 建立索引的原则 :
一般为哪些列建立索引:
哪些列需要建立索引
如果一列经常出现在where条件里面,
如果一列的取值范围很大
如果一列的大部分数据为NULL
因为:索引会把有数据的值 集中一起, 这样就会先查询有数据的
如果两个或者多个列 经常组合出现在 where条件中 可以将其组合 为其建立索引
如果表的数据很庞大*/
/* 什么情况下不用建立索引:
如果 表很小
如果一列不经常使用
如果每次查询 返回的行数 少于总行数的2-4% 也不用建立索引
如果一个表需要经常更新 也不用建立索引 */
