📄 ch21.htm
字号:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<TITLE>Teach Yourself SQL in 21 Days, Second Edition -- Ch 21 -- Common SQL Mistakes/Errors and Resolutions</TITLE>
</HEAD>
<BODY TEXT="#000000" BGCOLOR="#FFFFFF">
<CENTER>
<H1><IMG SRC="sams.gif" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/sams.gif" WIDTH="171" HEIGHT="66" ALIGN="BOTTOM" BORDER="0"><BR>
<FONT COLOR="#000077">Teach Yourself SQL in 21 Days, Second Edition</FONT></H1>
</CENTER>
<CENTER>
<P><A HREF="ch20.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/ch20.htm"><IMG SRC="previous.gif" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/previous.gif" WIDTH="128" HEIGHT="28"
ALIGN="BOTTOM" ALT="Previous chapter" BORDER="0"></A><A HREF="wk3rev.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/wk3rev.htm"><IMG
SRC="next.gif" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/next.gif" WIDTH="128" HEIGHT="28" ALIGN="BOTTOM" ALT="Next chapter"
BORDER="0"></A><A HREF="index-1.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/index-1.htm"><IMG SRC="contents.gif" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/contents.gif" WIDTH="128"
HEIGHT="28" ALIGN="BOTTOM" ALT="Contents" BORDER="0"></A>
<HR>
</CENTER>
<CENTER>
<H1><FONT COLOR="#000077">- Day 21 -<BR>
<BR>
Common SQL Mistakes/Errors and Resolutions</FONT></H1>
</CENTER>
<H2><FONT COLOR="#000077">Objectives</FONT></H2>
<P>Welcome to Day 21. By the end of today, you will have become familiar with the
following:
<UL>
<LI>Several typical errors and their resolutions
<P>
<LI>Common logical shortcomings of SQL users
<P>
<LI>Ways to prevent daily setbacks caused by errors
</UL>
<H2><FONT COLOR="#000077">Introduction</FONT></H2>
<P>Today you will see various common errors that everyone--from novice to pro--makes
when using SQL. You will never be able to avoid all errors and/or mistakes, but being
familiar with a wide range of errors will help you resolve them in as short a time
as possible.
<BLOCKQUOTE>
<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>We used Personal Oracle7 for our
examples. Your particular implementation will be very similar in the type of error,
but could differ in the numbering or naming of the error. We ran our SQL statements
using SQL*PLUS and set <TT>ECHO</TT> and <TT>FEEDBACK</TT> to <TT>on</TT> to see
the statement.
<HR>
</BLOCKQUOTE>
<P>Keep in mind that some mistakes will actually yield error messages, whereas others
may just be inadequacies in logic that will inevitably cause more significant errors
or problems down the road. With a strict sense of attention to detail, you can avoid
most problems, although you will always find yourself stumbling upon errors.
<H2><FONT COLOR="#000077">Common Errors</FONT></H2>
<P>This section describes many common errors that you will receive while executing
all types of SQL statements. Most are simple and make you want to kick yourself on
the hind side, whereas other seemingly obvious errors are misleading.
<H3><FONT COLOR="#000077">Table or View Does Not Exist</FONT></H3>
<P>When you receive an error stating that the table you are trying to access does
not exist, it seems obvious; for example:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>@tables.sql</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL> spool tables.lst
SQL> set echo on
SQL> set feedback on
SQL> set pagesize 1000
SQL> select owner|| '.' || table_name
2 from sys.dba_table
3 where owner = 'SYSTEM'
4 order by table_name
5 /
from sys.dba_table
*
ERROR at line 2:
ORA-00942: table or view does not exist
SQL> spool off
SQL>
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>Notice the asterisk below the word <TT>table</TT>. The correct table name is <TT>sys.dba_tables</TT>.
An s was omitted from the table name.</P>
<P>But what if you know the table exists and you still receive this error? Sometimes
when you receive this error, the table does in fact exist, but there may be a security
problem--that is, the table exists, but you do not have access to it. This error
can also be the database server's way of saying nicely, "You don't have permission
to access this table!"
<BLOCKQUOTE>
<P>
<HR>
<FONT COLOR="#000077"><B>TIP:</B></FONT><B> </B>Before you allow panic to set in,
immediately verify whether or not the table exists using a DBA account, if available,
or the schema account. You will often find that the table does exist and that the
user lacks the appropriate privileges to access it.
<HR>
</BLOCKQUOTE>
<H3><FONT COLOR="#000077">Invalid Username or Password</FONT></H3>
<H5><FONT COLOR="#000000">INPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF"> SQL*Plus: Release 3.2.3.0.0 - on Sat May 10 11:15:35 1997
Copyright (c) Oracle Corporation 1979, 1994. All rights reserved.
Enter user-name: <B>rplew</B>
Enter password:</FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">ERROR: ORA-01017: invalid username/password; logon denied
Enter user-name:
</FONT></PRE>
<P>This error was caused either by entering the incorrect username or the incorrect
password. Try again. If unsuccessful, have your password reset. If you are sure that
you typed in the correct username and password, then make sure that you are attempting
to connect to the correct database if you have access to more than one database.
<H3><FONT COLOR="#000077">FROM Keyword Not Specified</FONT></H3>
<H5><FONT COLOR="#000000">INPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>@tblspc.sql</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL> spool tblspc.lst
SQL> set echo on
SQL> set feedback on
SQL> set pagesize 1000
SQL> select substr(tablespace_name,1,15) a,
2 substrfile_name, 1,45) c, bytes
3 from sys.dba_data_files
4 order by tablespace_name;
substrfile_name, 1,45) c, bytes
*
ERROR at line 2:
ORA-00923: FROM keyword not found where expected
SQL> spool off
SQL>
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>This error can be misleading. The keyword <TT>FROM</TT> is there, but you are
missing a left parenthesis between <TT>substr</TT> and <TT>file_name</TT> on line
2. This error can also be caused by a missing comma between column names in the <TT>SELECT</TT>
statement. If a column in the <TT>SELECT</TT> statement is not followed by a comma,
the query processor automatically looks for the <TT>FROM</TT> keyword. The previous
statement has been corrected as follows:</P>
<PRE><FONT COLOR="#0066FF">SQL> <B>select substr(tablespace_name,1,15) a,</B>
2 <B> substr(file_name,1,45) c, bytes</B>
3 <B> from sys.dba_data_files</B>
4 <B>order by tablespace_name;</B>
</FONT></PRE>
<H3><FONT COLOR="#000077">Group Function Is Not Allowed Here</FONT></H3>
<H5><FONT COLOR="#000000">INPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>select count(last_name), first_name, phone_number</B>
2 <B>from employee_tbl</B>
3 <B>group by count(last_name), first_name, phone_number</B>
4 <B> /</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF"> group by count(last_name), first_name, phone_number
*
ERROR at line 3:
ORA-00934: group function is not allowed here
SQL>
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>As with any group function, <TT>COUNT</TT> may not be used in the <TT>GROUP BY</TT>
clause. You can list only column and nongroup functions, such as <TT>SUBSTR</TT>,
in the <TT>GROUP BY</TT> clause.
<BLOCKQUOTE>
<P>
<HR>
<FONT COLOR="#000077"><B>TIP:</B></FONT><TT><B> </B>COUNT</TT> is a function that
is being performed on groups in the query.
<HR>
</BLOCKQUOTE>
<P>The previous statement has been corrected using the proper syntax:</P>
<PRE><FONT COLOR="#0066FF">SQL> <B>select count(last_name), first_name, phone_number</B>
2 <B>from employee_tbl</B>
3 <B>group by last_name, first_name, phone_number;</B>
</FONT></PRE>
<H3><FONT COLOR="#000077">Invalid Column Name</FONT></H3>
<H5><FONT COLOR="#000000">INPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>@tables.sql</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL> spool tables.lst
SQL> set echo on
SQL> set feedback on
SQL> set pagesize 1000
SQL> select owner|| '.' || tablename
2 from sys.dba_tables
3 where owner = 'SYSTEM'
4 order by table_name
5 /
select owner|| '.' || tablename
*
ERROR at line 1:
ORA-00904: invalid column name
SQL> spool off
SQL>
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>In line 1 the column <TT>tablename</TT> is incorrect. The correct column name
is <TT>table_name</TT>. The underscore was omitted. To see the correct columns, use
the <TT>DESCRIBE</TT> command. This error can also occur when trying to qualify a
column in the <TT>SELECT</TT> statement by the wrong table name.
<H3><FONT COLOR="#000077">Missing Keyword</FONT></H3>
<H5><FONT COLOR="#000000">INPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL> create view emp_view
2 select * from employee_tbl
3 /</FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF"> select * from employee_tbl
*
ERROR at line 2:
ORA-00905: missing keyword
SQL>
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>Here the syntax is incorrect. This error occurs when you omit a mandatory word
with any given command syntax. If you are using an optional part of the command,
that option may require a certain keyword. The missing keyword in this example is
<TT>as</TT>. The statement should look like this:</P>
<PRE><FONT COLOR="#0066FF">SQL> <B>create view emp_view as</B>
2 <B>select * from employee_tbl</B>
3 <B>/</B>
</FONT></PRE>
<H3><FONT COLOR="#000077">Missing Left Parenthesis</FONT></H3>
<H5><FONT COLOR="#000000">INPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>@insert.sql</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL> insert into people_tbl values
2 '303785523', 'SMITH', 'JOHN', 'JAY', 'MALE', '10-JAN-50')
3 /
'303785523', 'SMITH', 'JOHN', 'JAY', 'MALE', '10-JAN-50')
*
ERROR at line 2:
ORA-00906: missing left parenthesis
SQL>
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>On line 2 a parenthesis does not appear before the Social Security number. The
correct syntax should look like this:</P>
<PRE><FONT COLOR="#0066FF">SQL> <B>insert into people_tbl values</B>
2 <B>('303785523', 'SMITH', 'JOHN', 'JAY', 'MALE', '10-JAN-50')</B>
3 <B>/</B>
</FONT></PRE>
<H3><FONT COLOR="#000077">Missing Right Parenthesis</FONT></H3>
<H5><FONT COLOR="#000000">INPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>@tblspc.sql</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL> spool tblspc.lst
SQL> set echo on
SQL> set feedback on
SQL> set pagesize 1000
SQL> select substr(tablespace_name,1,15 a,
2 substr(file_name, 1,45) c, bytes
3 from sys.dba_data_files
4 order by tablespace_name;
select substr(tablespace_name,1,15 a,
*
ERROR at line 1:
ORA-00907: missing right parenthesis
SQL> spool off
SQL>
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>On line 1 the right parenthesis is missing from the <TT>substr</TT>. The correct
syntax looks like this:</P>
<PRE><FONT COLOR="#0066FF"> SQL> <B>select substr(tablespace_name,1,15) a,</B>
2 <B> substr(file_name,1,45) c, bytes</B>
3 <B> from sys.dba_data_files</B>
4 <B>order by tablespace_name;</B>
</FONT></PRE>
<H3><FONT COLOR="#000077">Missing Comma</FONT></H3>
<H5><FONT COLOR="#000000">INPUT:</FONT></H5>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -