1 SQL Language 1.1 Two SQL Language 1.1.1 DDL Table 7-1 Some DDL Statement List SQL Command Features ALTER TABLE SUST ALTER TABLE adds table columns, redefines the table columns, and changes the performance of stored allocation Analyze Collect Database objects. Statistical value and send it to the price-based optimizer ALTER TABLE Add Constraint Add Constrained CREATE TABLE Creative Table Create INDEX Creating Index DROP INDEX Delete Index DROP TABLE Delete Table GRANT to grant permissions or roles to users or other roles Truncate delete All rows of REVOKEs from users or database roles 1.1.2 DML Table 7-2 Section DML statement list SQL Command Function INSERT Add Data Row to Table DELETE Delete Data Row Update Change Table Data Select SELECT From Table or View Sino-retrieval data line CommT Work put the current transaction to permanently (write disk) ROLLBACK All Changes since the last commit, 1.2 SQL * Plus Getting Started into the DOS window, enter the command SQLPLUS Polly / Gone 1.2.1 Create a statement Create Table Customer (Last_Name VARCHAR2 (30) Not null, State_CD VARCHAR (2), SALES NUMBER TABLESPACE CUSTSPACE - TABLESPACE clause puts the table in the specified database table space. Storage (Initial 25K Next 25k Minelts 1); - Storage clause is used to define tablespaces
Create Table State (state_cd varchar (2) Not null, state_name varcha2 (30));
Create Table X (Col Varchar2 (30));
CREATE TABLE Y (COL VARCHAR2 (30)); Table 7-3 Some Data Type Data Type Description CHAR (SIZE) Store Penios Character Data, the longest 2 0 0 characters nchar (size) similar to the char type, but it The maximum length is determined by the character set used by the database (such as American english) determines VARCHAR2 (size) to store the variable length character data, the maximum length is 4000 characters nvarchar (size) Similar to VARCHAR2 VARCHAR is currently equivalent to CHAR Number (L, D) storage value Type data, L represents the total number of points, and D represents the maximum number of digits (4, 2) of the decimal point is 99.99. BLOB binary large object, its maximum length is 4GB RAW (RAW) binary data, the maximum length is 2000 bytes Date stored date, from January 1, 4712 BC to December 9999 On the 31st long storage, the variable length character data is stored, and the maximum 2GB (gigabytes) has a semicolon (;) in each DDL statement, indicating that the SQL statement is completed, and can begin execution. 1.2.2 Description Statement Describe Customer - Gets the information about Table Customer in SQL * Plus, only the describe command can be short-handed as DESC.
1.2.3 Insert Statement INSERT INTO Customer Values ('Teplow', 'Ma', 23445.67); Insert Into Customer Values ('Abbev', 'GA', 6969.96);
Oracle Database Response 1 ROW CREATED.
Or: Insert INTO State (State_Name, State_CD) Values ('Mass Aching "; 1.2.4 Query Statement Using SELECT Statement Operating Data Dictionary View Select * from user_table where table_name =' Customer '; 1.2. 5 WHERE sub-sentence Table 7-5 Common comparison operator operator action Sample = equal select * from state where state_cd = 'ma';! = Not equal Select * from State where state_cd! = 'Ma'; ^ = same! = Select * from state where state_cd ^ = 'ma'; <> with! = select * from state where state_cd <> ma ';
1.2.6 Order By Sentences Design: Select * from Customer ORDER BY LAST_NAME DESC; Ascending: Select * from Customer ORDER BY LAST_NAME; Oracle is arranged in ascending order 1.3 numerical data SQL in ascending order in the Order By clause In the statement, if the specific data sheet cannot be specified for this statement, you can use the DUAL table. Table 7-7 Numerical Common Function Function Return Value Sample Display CEIL (N) Large Integer SELECT CEIL (10.6) from Dual; 11 FLOOR (N) is less than or equal to the maximum integer of the value N SELECT FLECT FLOOR (10.6) From dual; 1 0 mod (m, n) m divided by the remainder of N, if n = 0, return M SELECT MOD (7, 5) from Dual; 2 Power (M, N) M, N times SELECT POWER (3, 2) from Dual; 9 Round (n, m) Insert N round, reserved a decimal point, M-bit SELECT ROM DUAL; 14.57 SIGN (N) n = 0 returns 0; n> 0 Returns 1 N <0 Returns -1 SELECT SIGN (12) from Dual; 1 SQRT (N) N square root SELECT SQRT (25) from Dual; 5 SQL * Plus Help utility stores in Oracle Documentation. SQL> HELP FUNCTION 1.4 Character Data Table 7-8 Common Manager Function Function Return Value Sample Display INITCAP (Char) Replace the first character of each string into uppercase Select Initcap ('Mr.Teplow') from Dual; M r. Te plow Lower (char) Whole string is replaced with lowercase Select Lower ('mr.frank townson') from Dual; Mr.Terry Beaker Replace (CHAR, STR1, STR2) string All STR1 is replaced with STR2 SELECT REPLACE ('Scott', 's', 'boy') from Dual; Boycott Soundex (char) String voice representation (Last_name) = SOUNDEX ('SMYTHE'); Substr (CHAR, M, N) Remove N characters from M characters Substr ('AbcDef', 2, 1) from Dual; CD Length (char) Ask the length of the string Select Length ('Anderson') from Dual; 8 Use the two vertical line "||" to represent the operator. SELECT 'ABC' || 'DEF' from Dual;
If the last_name value is john, select 'dear' || Last_name || ':' from customer; will return text "dearjohn:" 1.5 Date data 1.5.1 Date type function Table 7-9 Common Date type function function Return value Example Sysdate Current Date and Time Select Sysdate from Dual; 28-Feb-02 On February 28,2002 Last_day This month SELECT LAST_DAY (SYSDATE) from DUAL; 31-Mar-02 On March 12,2002 Add_MontHS (D, N The current date d, push N month SELECT add_months (sysdate, 2) from dual; 1 8 - M AY-02 On March 18, 2002 MONTHS_BETWEEN (F, S) Date F and S between Monthly Month SELECT MONTHS_BETWEEN (Sysdate, '12 - mar, -02 ') from Dual; 13 in April 2003 Next_day (D, day) DAY Date Select next_day (sysdate,' monday ') from Dual; 03-Jan-02 on December30 , 2001 1.5.2 Date Data Table 7-10 Common Date Data Format Format Return Value Sample Display Y or YY or YYY Year of the last, two or three select to_char (sysdate, 'yyy') From Dual; 002 Represents Syear or Year Syear to add a negative of the year before the year before the year, SESDAT TO_CHAR (SYSDATE, 'SYEAR') from Dual; -1112 indicates that the Q quall of the Q quarter, from January to March 1 to March Select TO_CHAR (Sysdate, 'Q') from Dual; 2 Represents the second quarter 1 MM Southam SELECT TO_CHAR (SYSDATE, 'MM') from Dual; 12 means that the Roman in December RM represents Select to_Char (sysdate, ' RM ') from Dual; IV means that in April Month, the month name named Select to_Char (sysda) TE, 'MONTH') from Dual; MAY followed 6 spaces Representation in May WW for the next few SELECT TO_CHAR (Sysdate, 'WW') from Dual; 24 means that on June 13, 2002 is the 24th week of W this month It is a few weeks. Select to_char (sysdate, 'w') from dual; on October 1, 2002, the first week, DDD, the last year, January 1 is 001, February 1 is 032 Select to_Char (Sysdate, 'DDD' ) from dual; 363 February 2, 2002, 363 days DD on the day of the day SELECT TO_CHAR (SYSDATE, 'DD') from dual; 04 October 4 for the fourth day D Week SELECT TO_CHAR (SYSDATE, 'D') from Dual; 5 March 2002 Abbreviation Select TO_CHAR (SYSDATE, 'DY') from Monday DY Week, March 24, March 24, 2002, Sunday HH Or HH12 12 Entering Hours Select TO_CHAR (Sysdate, 'HH') from DUAL;
02 midnight 2 o'clock 8 points into 02 hh24 24 hours select to_char (sysdate, 'hh24') from dual; 14 in the afternoon of 14 mi minutes (0 ~ 59) Select to_char (sysdate, 'mi') From dual; 17 pm 17 points (0 ~ 59) SELECT TO_CHAR (SYSDATE, 'SS') from dual; 22 11:36 3 points 22 seconds Prompt Notice Do not use MM format for minutes (minutes should use Mi ). MM is a format for the month, and it can work in minutes, but the result is wrong. 1.5.3 Date Operation Oracle Special Talents Monthly and Year 's Issues. Such as the statement select to_char (sysdate 14) from Dual; will be added to the original value 23-DEC-2002 for 1 4 days to draw 06-Jan-03. 1.6 Table Ram Data Type Conversion Users often use the case where you need to convert a data table column into another type (such as a numerical conversion to date type, character type conversion to a numeric model). O R A C L e provides three main conversion functions. 1. TO_CHAR converts any type of data into a character type. Statement select to_char (8897) from DUAL; returns a data type of character string 8897. 2. TO_NUMBER converts a set of legal digital strings (such as character data 8897) to the value. Statement Select TO_NUMBER ('8897') from DUAL; returns a data including numeric value 8897. 3. TO_DATE converts the string data of the appropriate format into date data, statement select to_date ('12 -Dec-02 ') from dual; statement select to_date (' 20021227 ',' yyyymmdd ') from Dual; will return date 27 -Dec-02 1.7 Update, delete, and alter 1.7.1 Update statement u Update After following one or more tables to modify, this is essential. U set with one or more tables to modify, this is essential. U WHERE follows up to update the qualified condition, which is optional. For example, if the user is ready to change all the sales values in the Customer table to 0, just perform the UPDATE CUSTOMER SET SALES = 0; Oracle will modify all data lines and return to the number of rows modified. If you just want to change Sales, Sales for the client's customer's customer's sales, then available SQL statement Update Customer Set Sales = 0 where state_cd = 'ma'; can see the functionality of the update command is important. 1.7.2 Delete Statement u Keyword Delete from following the table name of deleting data from it, it is essential. u Key words where the condition is deleted, is optional. If the user wants to delete all records of the Customer table, use S Q L statements delete from customer ;. If the user is ready to delete the State_CD for CA's client record, use SQL statement delete from customer where state_cd = 'ca'; 1.7.3 Alter statement u statement alter table customer add (saler_date date); will successfully put the list Sale_Date to the table Customer. In most cases, users use ALTER to add a table to the table. Statement Alter Table X Modify (Col1 Date); used to change a data type with an existing table column.
(When executing the alter command, if you want to change the data type of the column containing the data, the Oracle8i will have a strict restriction on the operation performed by the alter command) U Alter Table X Modify (Col1 Date, Col5 Number (3, 1)) The role is the same as two independent Alter statements. u Oracle8i's new content: If you want to delete a new Sale_Date column in the Customer table, you can use the following statement: ALTER TABLE CUSTOMER DROP Column Sale_Date; this will immediately remove this column from the table, recover the space used by the Sale_DATE column. 1.8 Connection Two Table 1.9 Format The output utilizes S Q L * P L u s command show all, users know the current settings in the display format. 1.9.1 The size of the line is set to 80, and the command set linesize 80 can be entered. You can set the page length 3 0 line with the command set pagesize 30. 1.9.2 Ttitle 'Database Techologies | CUSTOMER Report' Sets the text "Database Technology" in Oracle "Database Technology" is the first header line, and then place the text "Customer Report" in the second line. 1.9.3 Footer BTITLE '--- Sample.SQL ---' Let SQL * Plus put text "--- sample.sql ---" Place the center of each page. Users can also use the Left or Right keyword to place the text in the btitle command in the corresponding position. 1.9.4 Write the SQL * Plus results 1. SPOOL C: /REPORT/out.lis. In most operating systems, if the user does not specify a suffix, the system will automatically increase the suffix ".lst" after the file name. For example, the command Spool Report automatically generates a file called Report.lst. 2. To stop spooling, you can use the SPOOL OFF or SPOOL OUT to turn off the output file and print the output. 1.9.5 Format Output Table Ran 1. Column Last_name Format A8 Wrap Heading 'Last | Name' tells SQL * Plus only 8 characters in the Last_name table column. 8 represents the display width of Last_Name, and A means that each location can only be character. Where W R A P section illustrates if last_name is more than 8 characters, excess characters are displayed in the corresponding position of the next row. The Heading section tells SQL * Plus to print "Last Name" on the header of the report. 2. Column State_CD Format A8 Heading 'State | Code' Let SQL * Plus display state_cd in 8 characters, and put a column header "State Code" on top of the State_CD table column. 3. Column Sales Format 999, 999, 999, 999.00 Heading 'Sales' Make S Q L * P L u s to display 1 2 digits, two decimals, separators with comma. 4. Col Sales Format 9999999 formatted the Sales table, because the Sales table column is formatted with 99999999, so there is no decimal in the display result.
