0%

发表于 更新于 分类于 阅读次数：
本文字数： 192 阅读时长 ≈ 1 分钟

#web开发/czh_blog/数据库
2017年8月31日 上午9:51

关于聚合函数注意事项

原则：select中的每一项，单独查询的话结果条数相等
技巧：当没有group by 使用聚合函数最有可能出问题



查询student表中的男生
一共四个男生，对应四条数据



查询student表中的男生人数
男生人数是4，就一条数据



当同时查询男生人数，和男生时
就会造成1对4 表就不知道怎么表示了

修改为下图也可以

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

#web开发/czh_blog/数据库
2017年8月31日 下午2:35

索引

  1. 主键索引，唯一索引（unique）
  2. 索引： index 建立索引是为了提高查询速度 但是对于频繁改动的字段添加索引会降低性能
  3. 在创建表或者改变表内字段约束时， primary key 和 unique 约束会自动为该字段创建对应索引
  4. 索引的用途： 在查询时将索引字段用于条件判断可以提升速度（性能）
  5. 慎用！！
  6. 索引的创建 与 删除 
    1
    2
    3
    create index stu_s_name on stu(s_name)

    drop index stu_s_name

事务在不同数据库中的不同

  1. MYSQL和Windows sqlSEVER 默认开启自动提交
  2. 在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。
  3. 因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。
  4. 当使用begin时，就不会自动提交了
  5. ORACL中需要手动的commit进行提交，不会默认进行提交
  6. 参考MySQL 事务 | 菜鸟教程
    1. 在没有commit之前，是可以回滚的
    2. 但是commit之后就不可以回滚了，同时，前面设置的回滚点也会失效

注：

  1. 在一个查询窗口中，没有commit的insert操作是不写入磁盘的。这时的insert的操作只是影响内存的数据
  2. 在PL/SQL中（软件），如果一个sql insert执行完没有commit，在当前查询窗口中，查询select是可以出表的变化的。
  3. 但是，但如果换一个查询窗口，或者去表中去看，其实是没有插入的，这就是因为oracle不是自动commit事务，需要手动提交，
  4. 你在当前查询窗口中可以查询出表的变化，是因为在当前窗口中是从当前查询表对应的内存去查询，并不是去硬盘上去查询

sequence 序列

  1. mysql SQLServer 由表自身维护主键
  2. Oracle 由对象维护主键 需要有SQL进行配合（这里的对象指的是sequence对象）
  3. 不一定保证连续
  4. 每行调用nextval后都会自增
  5. 同一行多次调用后只会自增一次
  6. 想使用currval 必须先调用一次 nextval 否则序列不会自动初始化
1
2
3
4
5
6
7
8
9
10
11
 create sequence seq_stu
 minvalue 15 
 start with 15 
 increment by 1;
   
insert into school values(seq_stu.nextval,'a','a');

commit;

select seq_stu.nextval from dual
select seq_stu.currval from dual

创建索引
自动创建索引

case
exists
sequence

用户 表空间 版本号

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

#web开发/czh_blog/数据库
2017年8月31日 下午2:35

索引

  1. 主键索引，唯一索引（unique）
  2. 索引： index 建立索引是为了提高查询速度 但是对于频繁改动的字段添加索引会降低性能
  3. 在创建表或者改变表内字段约束时， primary key 和 unique 约束会自动为该字段创建对应索引
  4. 索引的用途： 在查询时将索引字段用于条件判断可以提升速度（性能）
  5. 慎用！！
  6. 索引的创建 与 删除 
    1
    2
    3
    create index stu_s_name on stu(s_name)

    drop index stu_s_name

事务在不同数据库中的不同

  1. MYSQL和Windows sqlSEVER 默认开启自动提交
  2. 在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。
  3. 因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。
  4. 当使用begin时，就不会自动提交了
  5. ORACL中需要手动的commit进行提交，不会默认进行提交
  6. 参考MySQL 事务 | 菜鸟教程
    1. 在没有commit之前，是可以回滚的
    2. 但是commit之后就不可以回滚了，同时，前面设置的回滚点也会失效

注：

  1. 在一个查询窗口中，没有commit的insert操作是不写入磁盘的。这时的insert的操作只是影响内存的数据
  2. 在PL/SQL中（软件），如果一个sql insert执行完没有commit，在当前查询窗口中，查询select是可以出表的变化的。
  3. 但是，但如果换一个查询窗口，或者去表中去看，其实是没有插入的，这就是因为oracle不是自动commit事务，需要手动提交，
  4. 你在当前查询窗口中可以查询出表的变化，是因为在当前窗口中是从当前查询表对应的内存去查询，并不是去硬盘上去查询

sequence 序列

  1. mysql SQLServer 由表自身维护主键
  2. Oracle 由对象维护主键 需要有SQL进行配合（这里的对象指的是sequence对象）
  3. 不一定保证连续
  4. 每行调用nextval后都会自增
  5. 同一行多次调用后只会自增一次
  6. 想使用currval 必须先调用一次 nextval 否则序列不会自动初始化
1
2
3
4
5
6
7
8
9
10
11
 create sequence seq_stu
 minvalue 15 
 start with 15 
 increment by 1;
   
insert into school values(seq_stu.nextval,'a','a');

commit;

select seq_stu.nextval from dual
select seq_stu.currval from dual

创建索引
自动创建索引

case
exists
sequence

用户 表空间 版本号

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

2017年8月30日 上午8:34

我参考过一些文章
ORACLE配置tnsnames.ora文件详解 - kobe269的专栏
关于“ORA-12518: TNS: 监听程序无法分发客户机连接”的解决
plsql 安装后database下拉没有东西 - qitongce的博客 - CSDN博客
pl/sql登录时database下拉框没有任何内容_百度经验
卸载plsql后重新安装遇到问题小结 - 到底意难平的专栏 - CSDN博客
tnsping用法 - evilcry2012的专栏 - CSDN博客

关于配置关键问题理解

  1. listener.ora tnsnames.ora sqlnet.ora
    1. listener.ora是用来配置服务端监听客户端申请操作的
    2. 因此，告诉服务器的监听要监听哪个GLOBAL_DBNAME，SID_NAME
    3. 通俗的就是说，只有规定的人才可以连接我的数据库，其他没有定义的人是不可以连接我的数据库的
    4. 而且，也规定了这个人可以连接哪个数据库。这不是连接的时候定的，而是我服务端一开始配置就写死的，你作为使用者是不能更改的
    5. 其中的GLOBAL_DBNAME 对应着的是客户端连接时填写的SERVER_NAME
    6. 其中的SID_NAME对应着客户端连接时填写的SID
  2. tnsnames.ora
    1. 这个文件是给客户端用的
    2. 客户端连接服务器数据库过程：我作为用户，要和哪个监听对应，那么我就填所选监听的名字（SERVER_NAME或SID），这个监听的位置（ip）然后，就会去判断这个位置上的这个名字的监听在不在，如果在，那么就连接上这个监听对应的数据库
  3. sqlnet.ora
    1. 这个东西我不知道是啥，网上说是自动生成的，也是一个配置文件
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# listener.ora Network Configuration File: C:\Users\All Users\Desktop\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ORCL)
      (SID_NAME = ORCL)
    )
  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.103)(PORT = 1521))
  )

ADR_BASE_LISTENER = C:\app\oracle\product\11.2.0\dbhome_2\log
1
2
3
4
5
6
7
8
9
10
11
12
# tnsnames.ora Network Configuration File: C:\Users\All Users\Desktop\tnsnames.ora
# Generated by Oracle configuration tools.

ORICLE_SERVER_NAME =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.103)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ORCL)
    )
  )
1
2
3
4
5
6
7
8
9
10
11
12
# sqlnet.ora Network Configuration File: C:\Users\All Users\Desktop\sqlnet.ora
# Generated by Oracle configuration tools.

# This file is actually generated by netca. But if customers choose to 
# install "Software Only", this file wont exist and without the native 
# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

ADR_BASE = C:\app\oracle\product\11.2.0\dbhome_2\log

当服务的网络ip发生更改

  1. 有三个文件需要更改
  2. 更改的内容是服务器的ip
  3. 重新配置lisenter.ora,本地PL\SQL使用的nsnames.ora，以及外部客户端软件navicat
  4. 否则，会出现监听服务不随系统启动，并且，当我们手动启动的时候出现下面的报错，然后又自动关闭了

解决问题过程中的一些截图

测试istener.ora tnsnames.ora sqlnet.ora 这三个文件的作用的过程


当我主机中个客户端navicat在删除服务命名时，同样也可以连接。
说明

  1. 当我用navicat连接服务器数据库时，nsnames.ora 文件会根据你在navicat的配置自动生成
  2. 用net manager配置的nsnames.ora （服务命名）其实也是给客户端程序用的，例如，PL\SQL中配置环境变量
    1. 配置参考pl/sql登录时database下拉框没有任何内容_百度经验
    2. 你配置的服务名就会出现在PL\SQL登陆界面的下拉框中

解决PL\SQL安装问题

第二个问题

我的环境是win7 64位虚拟机，当我安装PL\SQL时出现了一个错是关于路径中不能使用括号，因为默认的路径是(x86)，这个(x86)就是括号，这里一定要更改默认路径才行 （ps:他的warning是英文的，我就是没有仔细看，而且也不认识这个括号的单词）

第一个问题

提示没有msvcr100这个文件，这个去百度上下一个，放到路径中规定的路径就可以解决。（网上有一个教程是安装一个window官方的软件，我这里是不行）

解决PL\SQL下拉框问题

[assets/6413C249-774A-4A4A-8E2D-FD199350E91F.png]
我不知道这个lisener_ORCL是从哪来的
[assets/7DB6B554-0CC8-4BB3-B05C-0498EA4959DC.png]
但出现这个问题是因为自己的的nsnames.ora 文件没有设置好，可以在net manager中设置
[assets/98B3AAAC-FB50-428C-92D1-52DBFEEDAE15.png]
我这里起的是local_server_name。
这个名字要出现在PL\SQL中的下框中，来说明这次登陆使用的是哪个nsnames.ora
[assets/4ECA1415-F659-4D58-9221-E667037E3860.png]
这里一定要调试通，才可以说明问题解决了


这里说明成功登陆了

  1. tnsping这个命令是用来判断你的服务名配置是否成功
  2. 我理解就是模拟了一次使用nsnames.ora文件进行访问服务器监听的过程，和在软件中的操作一样，只不过在软件中是界面的形式
  3. 可以发现最后是“ok”，说明我的这个nsnames.ora是没有问题的
  4. 当发现问题时，重新去net manager中配置服务名
  5. tnsping用法 - evilcry2012的专栏 - CSDN博客

发表于 更新于 分类于 阅读次数：
本文字数： 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

发表于 更新于 分类于 阅读次数：
本文字数： 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

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

#web开发/czh_blog/数据库
2017年8月30日 下午4:37

面试的重点在画星号的题
sql基础练习题.txt
维信科技_内部Oracle测试题试卷_田璟霞.doc

下面两个知识点之间的关系

SQL嵌套是sql分析步骤中，针对整理型情况下的总结
一般的限制型，用的比较少

注意

写SQL时函数的使用可以帮助我们实现一些很难实现的功能
例如
1. min
2. max

e.g.：scott账户下的功能功能测试（上面的题2中有）
我这里就没有使用min函数来解决-取得部门最低的人的工资 ，我想的是自己写复杂的sql来实现，最后也没有成功

1
2
3
4
5
-- 13.	显示部门内最低工资比20部门最低工资要高的部门编号以及部门内最低工资。++++++++
	select * from (
	select e.deptno,min(e.sal) min_sal from emp e GROUP BY e.deptno 
	)a
	where a.min_sal > (SELECT min(sal) from emp where deptno = 20 group by deptno)

分析sql的步骤

  1. Sql分成两种：限制性 + 整理型
  2. 第一步:涉及到几张表，把它们连起来
    1. 两种方法
      1. from
      2. join
    2. 连接有哪些？
      1. 固定的：外键连接
      2. 额外的：看自己有啥要求，或者要过滤那些情况
      3. 关键是要明白自己的需求
      4. 然后转换成里逻辑表达式
    3. 参考： SQL表连接
  3. 第二步：在第一步组成的表的基础上，有哪些限制
    1. 限制，指的就是句子中的修饰部分
    2. 简单的sql，文中的限制能很快的转换成一下五种情况
      2. <
      3. =
      4. in
      5. not in
      6. max
      7. min
      8. count
      9. sum
    3. 难的sql，就需要自己将中文转述成一种能用数学表达式表示的形式
  4. 有一些题他的目的不在于限制，而是在于整理 如这里的16、17、18
    1. 当侧重点在于整理时，需要重点考虑SQL嵌套
    2. 需要你利用相关子查询的知识去增加一些列去解决
    3. 其实，这里select伪列的作用就是去针当前表每一条信息去做统计
    4. 后续
      1. 有些题还需要对统计出来的select伪列做统计
      2. 这时只能在当前表的外面做查询限制，不能写在当前表中，因为：sql的执行顺序，伪列的产生在最后执行，位于where这些限制的之后执行
    5. 参考 SQL相关子查询的补充

什么时候会产生SQL嵌套（子查询+相关子查询）

  1. 当前限制条件需要单独查询一次时，用在条件判断中作为条件的值传入
  2. 要对表中的信息进行初步过滤时，用作临时视图存在
  3. 统计新信息时，用作创建伪列存在
  4. 查询伪列时，要对伪列中的信息进行限制

group by 相关：

来源：SQL中Group By的使用 - Rain Man - 博客园
Having和where的区别：

区分子查询+相关子查询

子查询：嵌套在其他查询中的查询称之。
子查询又称内部，而包含子查询的语句称之外部查询（又称主查询）。
所有的子查询可以分为两类，即相关子查询和非相关子查询

  1. 非相关子查询是独立于外部查询的子查询，子查询总共执行一次，执行完毕后将值传递给外部查询。
  2. 相关子查询的执行依赖于外部查询的数据，外部查询执行一行，子查询就执行一次。
    故非相关子查询比相关子查询效率高

伪列概念：

  1. 伪列：数据库没有实际保存的列。
  2. 普通的列，是实际有存储的，在物理存储上就有这个数据。伪列就没有，是算出来的。可以认为是一种特殊的函数。
  3. 如rowid，rowscn，rownum等等

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

#web开发/czh_blog/数据库
2017年8月30日 下午4:37

面试的重点在画星号的题
sql基础练习题.txt
维信科技_内部Oracle测试题试卷_田璟霞.doc

下面两个知识点之间的关系

SQL嵌套是sql分析步骤中，针对整理型情况下的总结
一般的限制型，用的比较少

注意

写SQL时函数的使用可以帮助我们实现一些很难实现的功能
例如
1. min
2. max

e.g.：scott账户下的功能功能测试（上面的题2中有）
我这里就没有使用min函数来解决-取得部门最低的人的工资 ，我想的是自己写复杂的sql来实现，最后也没有成功

1
2
3
4
5
-- 13.	显示部门内最低工资比20部门最低工资要高的部门编号以及部门内最低工资。++++++++
	select * from (
	select e.deptno,min(e.sal) min_sal from emp e GROUP BY e.deptno 
	)a
	where a.min_sal > (SELECT min(sal) from emp where deptno = 20 group by deptno)

分析sql的步骤

  1. Sql分成两种：限制性 + 整理型
  2. 第一步:涉及到几张表，把它们连起来
    1. 两种方法
      1. from
      2. join
    2. 连接有哪些？
      1. 固定的：外键连接
      2. 额外的：看自己有啥要求，或者要过滤那些情况
      3. 关键是要明白自己的需求
      4. 然后转换成里逻辑表达式
    3. 参考： SQL表连接
  3. 第二步：在第一步组成的表的基础上，有哪些限制
    1. 限制，指的就是句子中的修饰部分
    2. 简单的sql，文中的限制能很快的转换成一下五种情况
      2. <
      3. =
      4. in
      5. not in
      6. max
      7. min
      8. count
      9. sum
    3. 难的sql，就需要自己将中文转述成一种能用数学表达式表示的形式
  4. 有一些题他的目的不在于限制，而是在于整理 如这里的16、17、18
    1. 当侧重点在于整理时，需要重点考虑SQL嵌套
    2. 需要你利用相关子查询的知识去增加一些列去解决
    3. 其实，这里select伪列的作用就是去针当前表每一条信息去做统计
    4. 后续
      1. 有些题还需要对统计出来的select伪列做统计
      2. 这时只能在当前表的外面做查询限制，不能写在当前表中，因为：sql的执行顺序，伪列的产生在最后执行，位于where这些限制的之后执行
    5. 参考 SQL相关子查询的补充

什么时候会产生SQL嵌套（子查询+相关子查询）

  1. 当前限制条件需要单独查询一次时，用在条件判断中作为条件的值传入
  2. 要对表中的信息进行初步过滤时，用作临时视图存在
  3. 统计新信息时，用作创建伪列存在
  4. 查询伪列时，要对伪列中的信息进行限制

group by 相关：

来源：SQL中Group By的使用 - Rain Man - 博客园
Having和where的区别：

区分子查询+相关子查询

子查询：嵌套在其他查询中的查询称之。
子查询又称内部，而包含子查询的语句称之外部查询（又称主查询）。
所有的子查询可以分为两类，即相关子查询和非相关子查询

  1. 非相关子查询是独立于外部查询的子查询，子查询总共执行一次，执行完毕后将值传递给外部查询。
  2. 相关子查询的执行依赖于外部查询的数据，外部查询执行一行，子查询就执行一次。
    故非相关子查询比相关子查询效率高

伪列概念：

  1. 伪列：数据库没有实际保存的列。
  2. 普通的列，是实际有存储的，在物理存储上就有这个数据。伪列就没有，是算出来的。可以认为是一种特殊的函数。
  3. 如rowid，rowscn，rownum等等