0%

ORCALE SQL语法总结

发表于 更新于 分类于 阅读次数：
本文字数： 7.8k 阅读时长 ≈ 7 分钟

#web开发/czh_blog/数据库
2017年8月30日 上午7:00

用sublime打开，手机上bear打不开
常用sql终极版.sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
--修改表结构  为表中添加一个ssex字段  默认追加在表的最尾部
alter table student add (ssex varchar2(2));

alter table student add stel varchar2(11);

alter table student add saddr integer;
--修改表结构  将saddr字段的数据类型改为varchar2
alter table student modify saddr varchar2(255);

alter table student add saddre integer;

--alter table student change column saddre to sidcard varchar2(18);
--修改表结构  删除表中saddre字段
alter table student drop column saddre;

alter table student  add sidcard varchar2(18);
--将student表重命名为stu
rename student to stu 



--常见约束：
--1、主键约束：保证该列数据不能为空，切不能重复  primary key

--2、非空约束 not null    ：保证了该列不能存储null

--3、默认值约束  default   ：如果用户不为此列添加数据  那么数据字典会为行的该列添加默认值

--4、check约束  check   ：  为某一列增加条件约束   如果用户不满足条件，那么操作将被驳回

--5、唯一约束  unique     ：保证该列数据唯一性

--6、外键约束  foreign key  ：存在于两张表中   由其中一张表的某个字段指向另一张表的主键  
--                                从而让两张表产生关系

create table stu(
       s_id number(10) primary key,
       s_name varchar2(255) not null,
       s_sex varchar2(2) check(s_sex='男' or s_sex='女'),
       s_addr varchar2(255) default '山西省太原市',
       s_idcard varchar2(18) unique    
);

create table stu1(
       s_id number(10),
       s_name varchar2(255) not null,
       s_sex varchar2(2),
       s_addr varchar2(255) default '山西省太原市',
       s_idcard varchar2(18) ,
       constraint pri_sid primary key(s_id),
       constraint unique_idcard unique(s_idcard),
       constraint check_sex check(s_sex='男' or s_sex='女')
);



select * from stu;
insert into stu values(3,'rose','女','北京市朝阳区','000001');
insert into stu(s_id,s_name) values(2,'mary');


create table stu (s_id number(10) primary key,
       s_name varchar2(255));
       
create table student(
       s_id number(10),
       s_name varchar2(255),
       constraint primary_sid primary key(s_id)
       );
       
select * from stu;

insert into stu values(1,'mike');

insert into stu values(2,'rose');


--6、外键约束  foreign key  ：存在于两张表中   由其中一张表的某个字段指向另一张表的主键  
--                                从而让两张表产生关系

--禁用约束
   alter table stu  disable constraint stu_clazz_fk
--激活约束
   alter table stu  enable constraint stu_clazz_fk

   alter table stu drop constraint stu_clazz_fk
   
   alter table clazz drop constraint SYS_C009658 cascade

insert into clazz values(1,'软件开发与测试')
insert into stu values(1,'mary',1)
insert into stu values(2,'mary',2)

delete from stu where s_id = 3
delete from clazz where c_id=1 

select * from clazz
select * from stu

create table stu(s_id number(10) primary key
             ,s_name varchar2(255),
             c_id number(20),
             constraint stu_clazz_fk foreign key(c_id) references clazz(c_id) on delete cascade
             );
             
create table stu(s_id number(10)
             ,s_name varchar2(255),
             c_id number(20) references clazz(c_id)
             );

alter table stu add c_id number(20) ;


alter table stu add constraint stu_clazz_fk foreign key  (c_id) references clazz(c_id);

create table clazz(c_id number(10) primary key,c_name varchar2(255));







--常用的DML语句：

--查询表中所有数据
  select * from stu;
  
  select sid,sname from stu;
  
  select s.sid 编号,s.sname 名字 from stu s

--向表中添加一行数据
  insert into stu values(1,'mike','男','13888888888','山西省太原市','00001');
  
  insert into stu(sid,sname) values(2,'rose');
  
  --insert into stu values(3,'john','男','13845678912','北京市朝阳区','00003'),
  --(4,'mary','女','13001234567','北京市石景山区','00004');
  
  --insert into student select * from stu;
  
-- 修改表内数据

   update stu set ssex='女';
   
    update stu set ssex='男'  where sid=1;
    
    update stu set stel='13001234567',saddr='北京市朝阳区'  where sid=2;

--删除表中数据
    
   delete from stu where sid=3;
   --清空表内数据
   delete from stu;

--摧毁表    将要摧毁的表摧毁，但是保留原有字段（将表删除，按照原表的数据格式进行重建）

   truncate table stu;
   


insert into stu values(1,'mike','男','山西省太原市','13888888888',20,'00001',1);
insert into stu values(2,'mary','女','山西省太原市','13000000000',18,'00002',2);
insert into stu values(3,'rose','女','山西省太原市','13111111111',28,'00003',3);
insert into stu values(4,'john','男','山西省太原市','13546354321',30,'00004',3);
insert into stu values(5,'alin','女','山西省太原市','13789312381',16,'00005',2);
insert into stu values(6,'keven','男','山西省太原市','13324432544',25,'00006',1);
insert into stu values(7,'张三','男','山西省太原市','13813128312',40,'00007',1);
insert into stu values(8,'李四','男','山西省太原市','13857123574',53,'00008',1);
insert into stu values(9,'王五','男','山西省太原市','13898411624',30,'00009',3);
insert into stu values(10,'貂蝉','女','山西省太原市','13854101464',19,'00010',2);
insert into stu values(11,'王昭君','女','山西省太原市','13016779414',16,'00011',1);


insert into clazz values(1,'java');
insert into clazz values(2,'UI');
insert into clazz values(3,'测试');



--常用函数
--1、字符函数
--LOWER  UPPER  LENGTH  SUBSTR   INSTR

select * from stu
select * from clazz

SELECT * FROM STU WHERE S_NAME = upper('keven')
select s_name,length(s_name) from stu
select * from stu where length(s_name)=2
select s_name,substr(s_addr,1,3) from stu
select s_name,instr(s_name,'e') from stu
select concat(111,222) from dual
--------------------------------------------
--2、数学函数  ABS CEIL  FLOOR   ROUND TRUNC  MOD

select * from stu
select s_id,s_name,s_sex,s_addr,s_tel,s_age,s_idcard,c_id from stu
select * from clazz

select trunc(3.67,1) from dual
select MOD(10 , 3) from dual


--------------------------------------------
--3、日期函数    sysdate  
select last_day(sysdate+4) from dual

select sysdate+7 from dual 

--4、转换函数  to_number  to_char   to_date   nvl(n1,n2)   nvl2(n1,n2,n3)    null   
--        case      case ... when ... then ... when ... then ... else ...end   
--        decode 

select * from stu where s_addr is not null
select to_number(s_tel) from stu 
select to_char(sysdate,'yyyy-MM-dd') from dual

select replace(to_date('20170829','yyyy/MM/dd'),'/','-') from dual
select s_name,nvl(s_addr,'暂未登记地址') from stu
select s_name,nvl2(s_addr,'已登记地址','未登记地址') from stu 
select s_name,s_age,(case when s_age <18 then '未成年' when s_age <30 and s_age>20 then '青年' else '不合法的年龄' end) 区间  from stu
select s_name,s_age,(case s_age when 18 then '成年' when 30  then '而立' else '不合法的年龄' end) 区间  from stu
select s_name,s_age,decode(s_age ,20,'加冠',30,'而立') from stu

select trim(leading 'H' from 'Hello World') from dual

--5、连接函数   union   union all     这些函数可以将N个不同的视图做纵向拼接。拼接时列数需一致。

select s_id from stu
minus  
select c_id from clazz


select 1,1 from dual
union
select 1,1 from dual
union
select 1,1 from dual


--  6、伪列  rowid   rownum      rownum只能使用< 来做条件判断而不能用> 连接

select s.rowid,s.* from stu s where s.rowid = 'AAARVcAAGAAAADMAAG';

select rownum,s.* from stu s where rownum>5 and rownum <=10

-- 7、连接查询    作用就是将N张表连接在一起做查询处理操作
-- 等值连接   通过,连接N张表  再通过where条件为表和表之间两两建立连接条件
   select * from stu s,clazz c where s.c_id = c.c_id ;
-- 内连接    inner join ... on ..
   select * from stu s inner join clazz c on s.c_id=c.c_id ;
--外连接  左外连接   left [outer] join ... on ...   右外连接  right [outer] join ... on ...
--  以主表为标准查询  满足连接条件内容按连接条件显示，但是主表中数据即使不满足条件依然显示，子表中以null字段补齐
   select * from stu s left join clazz c on s.c_id = c.c_id 
--全连接   full join ... on ...
   select * from stu s full join clazz c on s.c_id = c.c_id

--8、分组函数  聚合函数     group by...分组依据   having 子句为分组后的数据增加约束条件
--   常用组函数  AVG   SUM   COUNT

   select count(1) from stu
   select s.s_sex,s.s_age,count(1) from stu s where s.s_age<>20 
          group by s.s_sex,s.s_age having count(1)>1 
                    

--9、排序函数  order by ...  desc是倒叙排序  asc是升序排序 默认可以省略

   select * from stu s order by s.s_age desc,s.s_id desc

--10、模糊查询  like  %  _

   select * from stu s where s.s_name like '_o%'

   
--11、子查询   一个主select语句中嵌套子select语句   用在条件判断中作为条件的值传入  用作临时视图存在    用作创建伪列存在
--  子查询从外往里一层层写   排错时 从内到外一层层排
           select * from stu
           select * from clazz
           --select s.* from stu s left join clazz c on s.c_id=c.c_id where c.c_name='UI'
           select s.* from stu s where s.c_id=(select c.c_id from clazz c where c.c_name='UI')
           
           select * from stu s where s.c_id in (select c.c_id from clazz c where c.sch_id = 
           (select sch.sch_id from school sch where sch.sch_name='中北大学'))
           
           
           select a.s_name,a.c_name,a.a,a.b from (
                  select s.s_name,s.s_sex,s.s_age,s.c_id a,c.c_id b,c.c_name from stu s,clazz c where s.c_id=c.c_id
           ) a
           
--  视图 view
           
           create or replace view v1 as( select s.s_name,s.s_sex,s.s_age,s.c_id a,c.c_id b,c.c_name from stu s,clazz c where s.c_id=c.c_id)
           
           select * from v1 

          select s1.s_name,s1.s_age,(select count(1) from stu s2 where s2.s_age<s1.s_age)+1 a 
          from stu s1 order by a
           
          --select rownum,a.s_name,a.s_age from stu s left join (select * from stu s1 order by s1.s_age) a on s.s_id=a.s_id
           
--12、去除重复          distinct
       select distinct s.s_age from stu s 
       
       insert into a 
       select distinct * from stu