sql常用语句和函数（SQL common statements and functions）

sql常用语句和函数（SQL common statements and functions） sql常用语句和函数(SQL common statements and functions) 1., word string length LENGTH You can use the LENGTH function to find the length of a string. LENGTH returns a value. This value is equal to the number of characters in the parameter. Example: using the LENGTH function SQL > select, Last_Name, length (Last_Name), from, customer, order, by, LastName; 2. use the SUBSTR function to extract substrings from strings Syntax: The syntax of the SUBSTR function is as follows: SUBSTR (string, string, charcter, number, of, charcters) Variables are defined as follows: String is a character column or string expression String charcter is the starting position of substrings Number of charcters is the number of returned characters C Example: explains how to use the SUBSTR function to obtain the first four characters of the teacher's last name SQL > select, last_Name, substr (Last_Name, 1, 4) from, instector, order, by, Last_Name Example: use the LENGTH function in the SUBSTR function (take the next three characters) 5Qt. > select, last_Name, substr (Last_Name, Length (Last_Name) - 2, 3) from, instector, order, by, Last_Name 3. find patterns in strings Example: using the LIKE operator SQL > column, description, format, a40, word_wrapped SQL > column, title, format, A35 SQL > select, Title, Description, from, Course, where, Description, like,'%thory%',, or, Description, like,'%theories%'; 4. replacing a part of a string Frequently encountered data manipulation tasks are to convert data from one schema to another in a specific column. If you want to change course in the Course table, will explain the words in seminar words. Then you can use discussion instead of Oracle to provide the REPLACE function, the function of the string of a column can be a string instead of. Syntax: The syntax of the REPLACE function is as follows: REPLACE (string, existion_string, [replacement_string]) Variables are defined as follows: String is a character expression C Existion_string is an existing string. Replacement_string is an optional string to replace. Example: using the REPLACE function In the Course table shows how to use REPLACE to change the name of the course (title): first use the query to display the current name of the course, the UPDATE statement uses the REPLACE function to change SEMINAR to DISCUSSION, another query shows the UPDATE statement effect. SQL > update, Course, Title = replace (Title,'SEMINAR', DISCUSSION', set); Note: if you do not specify what string to use in the REPLACE function, the original string in the column will be deleted. 5. delete the string space If a string column contains leading or trailing spaces, querying a column at a specified value is likely to return the error result. LTRIM and RTRIM. 6.LPAD left fill function Use LPAD to fill the string left. Syntax: LPAD (string, N, pad_string); Variables are defined as follows: String is the value of the string to be left padding, direct value, or character column. N returns the string length for LPAD. Pad_string is a string that is left filled to string. SQL > select lpad (my_col, 20) from test_trim; The parameters using the LENGTH function as LPAD function: SQL > select lpad (my_col, length (my_col) + 8,'You, say') from test_trim; 7. change the string size Oracle provides three functions that change string case size: INITCAP converts the first character of each word to uppercase LOWER converts all characters to lowercase UPPER converts all characters to human 8. converts strings using the DECODE function (value conversion function) grammar The syntax for DECODE is: DECODE (expression, value1, returned_value1,..., valueN, returned_valueN, [default_returned_value] Variables are defined as follows: Dexpression is a valid Oracle expression. ValueN the value that expression might take for this purpose: Returned_valueN is the return value of DECODE when expression is equal to valueN. Default_returned_value is optional. It is the return value of DECODE when expression is not equal to any valueN. SQL > select, Schedult_id, Day, decode (Day, 1,'sun', 2,'mon', 3,'tue', 4,'wed', 5,'sat', 7,'thu', 6,'fri', D, c) From, Schedult_Type_Details, order, by, Schedult_id, Day; The 9. conversion string is a ASCII value SQL > select, Last_Name, SCAII (Last_Name), from, Instructor, order, by, Last_Name; 10. current date and time: SYSDATE The 11. conversion date is a string Syntax: The format of the T0_CHAR function is as follows: TO_CHAR (date_value, format) SQL > select, Last_Name, First_Name, to_char (Hire_Date, MONTH, DD, YYYY'), H_DATE, from, Employee, order, by, Hire_Date; Embed the to_char function in the SUBSTR function SQL > select, Last_Name, First_Name, substr (to_char (Hire_date,'MON), 1, 1) the_fist_letter_of_the_month, from, Employee; The 12. conversion string is the date Syntax: To_date (string_value, date_format); SQL > select, SYSDATE, to_date ('07-04-1976','MM-DD-YYYY'), from, dual; 13. date and time Use the time format in the to_char function: SQL>select, Employee_id, (to_char (time, clocked_in,'HH:MM:SS'), Time_Clocked_In, from, Time_Clock; 14. calculate the difference between the two dates SQL>select sysdate + 7 from dual; 15. in Insert\Update and other operations, the number will automatically be converted to string. Numeric fields can be converted into strings using to_char. 16. format numeric fields Select to_char (Additional_Fees,'9999.99') from Course; Currency symbol: Select (to_char Additional_Fees, from Course '$9999.99'); Scientific notation: Select to_char (Additional_Fees,'9.9999EEEE') from Course; 17. converts strings into numbers To_number is the inverse operation of to_char. Update, Security_Price, set, Last_Qtr_EPS = to_number ('$2.81','$999.99'); 18. internal statistics function AVG (value): calculates the average value, and its parameters come from several lines in which it works. STDDEV (value): returns several lines of its function as the standard deviation of the parameter. VARIANCE (value): returns several rows that it functions as arguments. 19., four, five into the function and the interception function Four, five, ROUND (value, [scale]), ROUND (101.8) = 102, Round (123.37, 1) = 123.4 Intercept TRUNC (value, [scale]), Trunc (123.33) = 123, Trunc (123.567, 2) = 123.56 Find the largest integer FLOOR (value) Floor (128.3) =128 Floor (129.8) = 129, in addition to the fractional part that cannot be intercepted, Floor is almost equivalent to the Trunc function. Find the smallest integer CELL (value) Cell (128.3) =129 Cell (129.8) = 130 20. seek maximum or minimum MAX and MIN. 21. replace NULL NVL (column, value). Select NVL (Additional, 0) from Course; 22. internal aggregation function COUNT function: Find different rows: DISTINCT Grouping: GROUP, BY:, select, Department_id, count (*), from, curse, group, by, Department_id; Group, by and having:, select, Department_id, count (*), from, Course, by, Department_id, having, group, count (*) = 4 - which departments have 4 courses?. AVG and group, by:, select, Department_id, AVG (Additional_Fees), from, Course, group, by, Department_id; 23.EXISTS Select, Last_Name, Fist_Name, from, Instructor, I, where, exists (select * from, Class, C, where, I.Instruetor_id = C.Instructor_id); The set operation of the 24.SELECT statement INTERSECE (intersection) UNION (and): select, Epicenter_Latitude, Epicenter_Longtitude, from, UNSD_Event, UNION, select, Location_Lat, Location_Lon, Richter_Number, from,, RIND_Event, order, by,..; MINUS (bad): select, Last_Name, First_Name, from, Instructor, minus, select, Last_Name, First_Name, from, Hoover_Instructor; 25. create a table with child queries Create, table, Anthor, as, select * from, instructor, where 1 = 2; 26. create a simple view Create view Stueent_No_Personal As Select, Student_ID, Last_Name, First_Name, from, Student; Structure of 27.PL/SQL block Explanation section Executive part Exception handling section Cases: DECLARE Max_records, CONSTANT, int: = 100; I int: = 1; BEGIN FOR, I, in, 1..Max_records, LOOP INSERT, INTO, test_table (record_number, current_date), values (I, sysdate); END LOOP; COMMIT; END; / 28. declare a variable with%TYPE Example: to illustrate a variable used to store the name of a warehouse technician, Tech_Name Dept_Esimate.Techican%TYPE; 29. declare a variable with%ROWTYPE Example: store a row of a Depot_Estimate table with a composite variable. Depot_Est_Row Depot_Esimate%ROWTYPE; The elements of Depot_Est_Row can be used in the following ways: Depot_Est_Row.Techniciam: ='yujj'; 30. some common control structures IF statement: If MOD (I, 5) = 0 then Rec_number: = 5; Elseif MOD (I, 7) = 0 then Rec_number: = 7; Else Rec_number: = i; End if; Simple LOOP/EXIT statement: LOOP EXIT WHEN...; ENDLOOP; WHILE_LOOP statement: WHILE I <100 loop> I: = I + 1; END LOOP; FOR_LOOP statement: For, I, in, 1..Max, loop I: = I + 1; Dbms_output.put_line ('j:'||to_char (J)); End loop; GOTO statement: 去more_processing; „ < > „ 结束; 空语句: 如果(mod，10),0，那么 i:= i + 1; 其他的 无效的; 最后如果; 赋值语句: PL/SQL中使用:=进行赋值。 31。在PL / SQL SQL语句中使用 PL / SQL和选择语句: 进行设置 声明 average_body_temp病人。body_temp deg_f分型; 开始 dbms_output.enable; 选择AVG(body_temp_deg_f)从病人到average_body_temp; dbms_putput。put_line(“平均体温是华氏度:“| | to_char (average_body_temp。'999.99 ')); 结束; 子块的应用: 一个匿名块，它包含另一个子块 声明 max_i恒int:= 100; I = 1; rec_number int; 开始 我在1 max_i环.. 如果mod(i，5),0 rec_number:= 5; 其他的 rec_number:=我; 最后如果; 插入test_table(record_number，current_date)值(record_number， SYSDATE); 这里是一个子块; 声明 max_j恒int:= 20; j = 1; 开始 J在1 max_j环..; rec_number:= rec_number * J; 插入test_table(record_number，current_date)值(record_number， SYSDATE); 结束循环; 结束; 结束循环; 一个过程的示例: 声明 new_patient_id patient_id %型病人; high_fever恒房:= 42; 程序record_patient_temp_deg_c(patient_id VARCHAR2， body_temp_deg_c房)是 temp_deg_f房; 开始 temp_deg_f:=(9 / 5)* body_temp_deg_c + 32; 插入病人(patient_id，body_temp_deg_f)值(patient_id， tempdeg_f); 承诺; 结束; 开始 new_patient_id:=“gg9999”; record_patient_temp_deg_c(new_patient_id，high_fever); 结束; / ——一个过程内声明的变量在过程体外不能被使用。 一个函数的示例: 功能max_additional_fees(dept_id在VARCHAR2) 返回VARCHAR2是 additional_fees课程。额外费用分型; 开始 „ 回报的东西; 结束; 过程和函数的参数类型:/ /出 32。关于dbms_output包的调用:先输入进行设置; 33。系统视图user_source，保存有过程、函数、包等。它有以下四 列: 名称包括过程、函数、包或包体的名字 TYPE grant source code is a process, function package, or package TEXT contains a row in the source code LINE contains the number of rows contained in the source code in TEXT Examples: select, line, text, from, user_source, where, name ='DROP_CLASS', order, by, line; 34.SELECT and storage functions: In SELECT, you can use the existing storage function Create, or, replac, function, DegF_add10 (Deg_F, IN, number) Return number is Deg_C number; Begin Deg_c: = deg_f + 10; Return deg_c; End DegF_add10; Select, body_temp, DegF_add10 (body_temp), from, patient; 35. additional PL/SQL data types Boolean, binary_integer, natural, positive,%type,%rowtype, pl/sql tables, or array user-defined records 36.ORACLE predefined exceptions DUP_VAL_ON_INDEX: occurs when the SQL statement sets up duplicate data in a column with a unique index. INVALID_NUMBER: occurs when the SQL statement specifies an invalid number. NO_DATTE_FOUND: occurs when the SELECT statement does not return any rows. TOO_MANY_ROWS: occurs when a SELECT statement retrieves multiple rows of data in a PL/SQL environment. In order to retrieve any number of rows from a query, cursors can be used to view the cursor as a window for the query to return results. This exception occurs when a SELECT statement returns multiple data rows in pl/sql. Most of the VALUE_ERROR: is related to truncation and conversion errors. Assign a string of more than 5 to a VARCHAR2 (5) field. 37.SQLCODE and SQLERRM SQLCODE includes the error state of the current execution of the Oracle pl/sql statement. If the SQL statement is not error, then SQLCODE is 0; SQLERRM includes error information associated with the SQLCODE. If the SQL statement succeeds, the SQLCODE is 0 and the SQLERRM is a string: ORA_0000:normal, successful, completion. 38. steps to use cursors 1. specify the (declare) cursor, give the cursor a name, and relate it to the syntax of the SELECT statement. 2. open (open) cursors, Oracle, RDBMS, perform cursor related queries, and determine the defined rows (active sets). 3. draw data rows with cursors: return the value of each row to the PL/SQL subroutine environment and return a row at a time. 4. closes the (close) cursor and frees the associated resources. 39. illustrates cursors CURSOR cursor_name [[Parameter1 parameter1_datetype[: = default1], . [parameterN parameterN_datetype[: = derfaultN])] IS select_stmt; 40. turn on the cursor Open cursor_name; Explanation: if there is no argument type defined when the cursor is defined, the variable or direct value must be specified for the relevant parameter when the OPEN cursor is applied. Such as open tempcur (20,30); 41. take trips from the cursor Loop Fetch, tmpcur, into, field1, field2; Exit when tmpcur%notfound; Dbms_output.put_line (field1); End loop; 42. close the cursor Close tmpcur; 43. the number of rows returned from the cursor Dbms_output.put_line (tmpcur%rowcount); 44. in the Oracle database, the line number of the query results is represented by a pseudo column ROWNUM (starting at 1). You can use it for database paging!!! For example, select * from, employee, where, rownum<10> 从中选择*( 选择my_table。*，行号为my_rownum从(由生日员工顺序选择名字， 生日 my_table rownum < 120 >的地方) 在my_rownum > = 100) 例:给多 表 关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf 查询的结果加上序列号 行号为“serialno”、“pname”、“compname”从 “fundcompaccount”、“fundpersonaccount B 在“compaccount”=“compaccount”; ——以上已经打印20040511 Oracle选定前20条数据 SELECT * FROM”fundduty“rownum < 20; Oracle选定21至40条数据 SELECT * FROM”fundduty“rownum <= 40 减 SELECT * FROM”fundduty“rownum <= 21