|
189
|
1 SQL.No_Connection> connect testschema/testschema@orcl
|
|
|
2 testschema@orcl> CREATE TABLE play (
|
|
|
3 > title VARCHAR2(40) CONSTRAINT xpk_play PRIMARY KEY,
|
|
|
4 > author VARCHAR2(40));
|
|
|
5
|
|
|
6 Executed
|
|
|
7
|
|
|
8 testschema@orcl> INSERT INTO play VALUES ('Twelfth Night', 'Shakespeare');
|
|
|
9
|
|
|
10 Executed (1 rows)
|
|
|
11
|
|
|
12 testschema@orcl> INSERT INTO play VALUES ('The Tempest', 'Shakespeare');
|
|
|
13
|
|
|
14 Executed (1 rows)
|
|
|
15
|
|
|
16 testschema@orcl> INSERT INTO play VALUES ('Agamemnon', 'Aeschylus');
|
|
|
17
|
|
|
18 Executed (1 rows)
|
|
|
19
|
|
|
20 testschema@orcl> commit;
|
|
|
21
|
|
|
22 Executed
|
|
|
23
|
|
|
24 testschema@orcl> select
|
|
|
25 > *
|
|
|
26 > from
|
|
|
27 > play;
|
|
|
28
|
|
|
29 TITLE AUTHOR
|
|
|
30 ------------- -----------
|
|
|
31 Twelfth Night Shakespeare
|
|
|
32 The Tempest Shakespeare
|
|
|
33 Agamemnon Aeschylus
|
|
|
34
|
|
|
35 3 rows selected.
|
|
|
36
|
|
|
37 testschema@orcl> ls
|
|
|
38
|
|
|
39 NAME
|
|
|
40 --------------
|
|
|
41 INDEX/XPK_PLAY
|
|
|
42 TABLE/PLAY
|
|
|
43
|
|
|
44 2 rows selected.
|
|
|
45
|
|
|
46 testschema@orcl> ls table
|
|
|
47
|
|
|
48 NAME
|
|
|
49 ----------
|
|
|
50 TABLE/PLAY
|
|
|
51
|
|
|
52 1 row selected.
|
|
|
53
|
|
|
54 testschema@orcl> desc play
|
|
|
55 TABLE TESTSCHEMA.PLAY
|
|
|
56
|
|
|
57 COLUMN_NAME Null? DATA_TYPE
|
|
|
58 ----------- -------- ------------
|
|
|
59 TITLE NOT NULL VARCHAR2(40)
|
|
|
60 AUTHOR NULL VARCHAR2(40)
|
|
|
61
|
|
|
62 2 rows selected.
|
|
|
63
|
|
|
64 testschema@orcl> COMMENT ON COLUMN play.author IS 'Primary author (if multiple)';
|
|
|
65
|
|
|
66 Executed
|
|
|
67
|
|
|
68 testschema@orcl> COMMENT ON TABLE play IS 'I like plays.';
|
|
|
69
|
|
|
70 Executed
|
|
|
71
|
|
|
72 testschema@orcl> comments play
|
|
|
73 TABLE TESTSCHEMA.PLAY: I like plays.
|
|
|
74
|
|
|
75 COLUMN_NAME COMMENTS
|
|
|
76 ----------- ----------------------------
|
|
|
77 TITLE None
|
|
|
78 AUTHOR Primary author (if multiple)
|
|
|
79
|
|
|
80 2 rows selected.
|
|
|
81
|
|
|
82 testschema@orcl> cat play
|
|
|
83
|
|
|
84 TITLE AUTHOR
|
|
|
85 ------------- -----------
|
|
|
86 Twelfth Night Shakespeare
|
|
|
87 The Tempest Shakespeare
|
|
|
88 Agamemnon Aeschylus
|
|
|
89
|
|
|
90 3 rows selected.
|
|
|
91
|
|
|
92 testschema@orcl> help terminators
|
|
|
93 ; standard Oracle format
|
|
|
94 \c CSV (with headings)
|
|
|
95 \C CSV (no headings)
|
|
|
96 \g list
|
|
|
97 \G aligned list
|
|
|
98 \h HTML table
|
|
|
99 \i INSERT statements
|
|
|
100 \s CSV (with headings)
|
|
|
101 \S CSV (no headings)
|
|
|
102 \t transposed
|
|
|
103 \x XML
|
|
|
104 \l line plot, with markers
|
|
|
105 \L scatter plot (no lines)
|
|
|
106 \b bar graph
|
|
|
107 \p pie chart
|
|
|
108 testschema@orcl> select * from play where author='Shakespeare'\c
|
|
|
109
|
|
|
110 TITLE,AUTHOR
|
|
|
111 "Twelfth Night","Shakespeare"
|
|
|
112 "The Tempest","Shakespeare"
|
|
|
113
|
|
|
114 2 rows selected.
|
|
|
115
|
|
|
116 testschema@orcl> select * from play where author='Shakespeare'\g
|
|
|
117
|
|
|
118
|
|
|
119
|
|
|
120 **** Row: 1
|
|
|
121 TITLE: Twelfth Night
|
|
|
122 AUTHOR: Shakespeare
|
|
|
123
|
|
|
124 **** Row: 2
|
|
|
125 TITLE: The Tempest
|
|
|
126 AUTHOR: Shakespeare
|
|
|
127
|
|
|
128
|
|
|
129 2 rows selected.
|
|
|
130
|
|
|
131 testschema@orcl> select * from play where author='Shakespeare'\h
|
|
|
132
|
|
|
133 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
|
|
|
134 <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
|
|
|
135 <head>
|
|
|
136 <title>play</title>
|
|
|
137 <meta http-equiv="content-type" content="text/html;charset=utf-8"/>
|
|
|
138 </head>
|
|
|
139 <body>
|
|
|
140 <table id="play" summary="Result set from query on table play">
|
|
|
141 <tr>
|
|
|
142 <th id="header_title">
|
|
|
143 title
|
|
|
144 </th><th id="header_author">
|
|
|
145 author
|
|
|
146 </th>
|
|
|
147 </tr>
|
|
|
148 <tr>
|
|
|
149 <td headers="header_title">
|
|
|
150 Twelfth Night
|
|
|
151 </td><td headers="header_author">
|
|
|
152 Shakespeare
|
|
|
153 </td>
|
|
|
154 </tr><tr>
|
|
|
155 <td headers="header_title">
|
|
|
156 The Tempest
|
|
|
157 </td><td headers="header_author">
|
|
|
158 Shakespeare
|
|
|
159 </td>
|
|
|
160 </tr>
|
|
|
161 </table>
|
|
|
162 </body>
|
|
|
163 </html>
|
|
|
164
|
|
|
165 2 rows selected.
|
|
|
166
|
|
|
167 testschema@orcl> select * from play\i
|
|
|
168
|
|
|
169
|
|
|
170 INSERT INTO play (TITLE, AUTHOR) VALUES ('Twelfth Night', 'Shakespeare');
|
|
|
171 INSERT INTO play (TITLE, AUTHOR) VALUES ('The Tempest', 'Shakespeare');
|
|
|
172 INSERT INTO play (TITLE, AUTHOR) VALUES ('Agamemnon', 'Aeschylus');
|
|
|
173
|
|
|
174 3 rows selected.
|
|
|
175
|
|
|
176 testschema@orcl> select * from play where author='Shakespeare'\t
|
|
|
177
|
|
|
178
|
|
|
179 COLUMN NAME ROW N.1 ROW N.2
|
|
|
180 ----------- ------------- -----------
|
|
|
181 TITLE Twelfth Night The Tempest
|
|
|
182 AUTHOR Shakespeare Shakespeare
|
|
|
183
|
|
|
184 2 rows selected.
|
|
|
185
|
|
|
186 testschema@orcl> select * from play where author='Shakespeare'\x
|
|
|
187
|
|
|
188
|
|
|
189 <xml>
|
|
|
190 <play_resultset>
|
|
|
191 <play>
|
|
|
192 <title>Twelfth Night</title>
|
|
|
193 <author>Shakespeare</author>
|
|
|
194 </play>
|
|
|
195 <play>
|
|
|
196 <title>The Tempest</title>
|
|
|
197 <author>Shakespeare</author>
|
|
|
198 </play>
|
|
|
199 </play_resultset>
|
|
|
200 </xml>
|
|
|
201
|
|
|
202 2 rows selected.
|
|
|
203
|
|
|
204 testschema@orcl> set
|
|
|
205 autobind: False
|
|
|
206 commit_on_exit: True
|
|
|
207 echo: False
|
|
|
208 maxfetch: 1000
|
|
|
209 maxtselctrows: 10
|
|
|
210 timeout: 30
|
|
|
211 testschema@orcl> print
|
|
|
212 testschema@orcl> set autobind on
|
|
|
213 autobind - was: False
|
|
|
214 now: True
|
|
|
215 testschema@orcl> select * from play where author like 'A%';
|
|
|
216
|
|
|
217 TITLE AUTHOR
|
|
|
218 --------- ---------
|
|
|
219 Agamemnon Aeschylus
|
|
|
220
|
|
|
221 1 row selected.
|
|
|
222
|
|
|
223 testschema@orcl> print
|
|
|
224 :1 = Agamemnon
|
|
|
225 :2 = Aeschylus
|
|
|
226 :title = Agamemnon
|
|
|
227 :author = Aeschylus
|
|
|
228 testschema@orcl> select * from play where title = :1;
|
|
|
229
|
|
|
230 TITLE AUTHOR
|
|
|
231 --------- ---------
|
|
|
232 Agamemnon Aeschylus
|
|
|
233
|
|
|
234 1 row selected.
|
|
|
235
|
|
|
236 testschema@orcl> select * from play where author = :author;
|
|
|
237
|
|
|
238 TITLE AUTHOR
|
|
|
239 --------- ---------
|
|
|
240 Agamemnon Aeschylus
|
|
|
241
|
|
|
242 1 row selected.
|
|
|
243
|
|
|
244 testschema@orcl> help grep
|
|
|
245 grep PATTERN TABLE - search for term in any of TABLE's fields
|
|
|
246 Usage: grep [options] arg
|
|
|
247
|
|
|
248 Options:
|
|
|
249 -h, --help show this help message and exit
|
|
|
250 -i, --ignore-case Case-insensitive search
|
|
|
251
|
|
|
252 testschema@orcl> grep -i EM play
|
|
|
253 play
|
|
|
254
|
|
|
255 TITLE AUTHOR
|
|
|
256 ----------- -----------
|
|
|
257 The Tempest Shakespeare
|
|
|
258 Agamemnon Aeschylus
|
|
|
259
|
|
|
260 2 rows selected.
|
|
|
261
|
|
201
|
262 testschema@orcl> -- \d command inspired by postgresql's psql
|
|
|
263 testschema@orcl> \dt
|
|
|
264
|
|
|
265 TABLE_NAME TYPE
|
|
|
266 ---------- -----
|
|
|
267 PLAY TABLE
|
|
|
268
|
|
|
269 1 row selected.
|
|
|
270
|
|
|
271 testschema@orcl> \di
|
|
|
272
|
|
|
273 INDEX_NAME INDEX_TYPE
|
|
|
274 ---------- ----------
|
|
|
275 XPK_PLAY NORMAL
|
|
|
276
|
|
|
277 1 row selected.
|
|
|
278
|
|
|
279 testschema@orcl> CREATE OR REPLACE VIEW review
|
|
|
280 > AS
|
|
|
281 > SELECT title, author, 'A masterpiece!' review
|
|
|
282 > FROM play;
|
|
|
283
|
|
|
284 Executed
|
|
|
285
|
|
|
286 testschema@orcl> \dv
|
|
|
287
|
|
|
288 VIEW_NAME TYPE
|
|
|
289 --------- ----
|
|
|
290 REVIEW VIEW
|
|
|
291
|
|
|
292 1 row selected.
|
|
|
293
|
|
|
294 testschema@orcl> cat review
|
|
|
295
|
|
|
296 TITLE AUTHOR REVIEW
|
|
|
297 ------------- ----------- --------------
|
|
|
298 Twelfth Night Shakespeare A masterpiece!
|
|
|
299 The Tempest Shakespeare A masterpiece!
|
|
|
300 Agamemnon Aeschylus A masterpiece!
|
|
|
301
|
|
|
302 3 rows selected.
|
|
|
303
|
|
|
304 testschema@orcl> drop view review;
|
|
|
305
|
|
|
306 Executed
|
|
|
307
|
|
189
|
308 testschema@orcl> drop table play;
|
|
|
309
|
|
|
310 Executed
|
|
210
|
311
|
|
|
312 testschema@orcl> foo
|
|
|
313
|
|
|
314 bar |
