Basic SQL knowledge1. database query: Select usageSelect [top (numeric)] field list from data table [Where condition] [order by field] [ASC or DESC]
2. Add data: insert into usageInsert into data table (Field 1, Field 2, Field 3 ,...) Values (value of Field 1, value of Field 2, value of Field 3 ,...)
3. delete data: delete usageDelete from data table [Where condition]
4. Update Data: Update usageUpdate data table set field 1 = field value 1, Field 2 =
[Code = SQL] [/Code]-- Sentence skill-- Data OperationsSelect -- retrieve data rows and columns from database tablesInsert -- add new data rows to the database tableDelete -- delete data rows from a database tableUpdate -- update data in the database table-- Data DefinitionCreate Table -- create a database tableDrop table -- delete a table from a databaseAlter table -- modify the database table structureCreate View -- create a viewDrop View -- delete
Access Permissions
-- Transaction control
COMMIT -- end the current transaction
ROLLBACK -- abort the current transaction
Set transaction -- define the data access features of the current TRANSACTION
-- Programmatic SQL
DECLARE -- set the cursor for the query
Explain -- describe the data access plan for query
OPEN -- OPEN a cursor for retrieving query results
FETCH -- retrieve a row of query results
CLOSE -- CLOSE the cursor
PREPARE -- prepare
Access Permissions
-- Transaction control
Commit -- end the current transaction
Rollback -- abort the current transaction
SET transaction -- define the data access features of the current transaction
-- Programmatic SQL
Declare -- set the cursor for the query
Explain -- describe the data access plan for query
Open -- open a cursor for retrieving query results
Fetch -- retrieve a row of query results
Close -- close the cursor close
Prepare -- prepare
Permissions-- Transaction controlCommit -- end the current transactionRollback -- abort the current transactionSET transaction -- define the data access features of the current transaction-- Programmatic SQLDeclare -- set the cursor for the queryExplain -- describe the data access plan for queryOpen -- open a cursor for retrieving query resultsFetch -- retrieve a row of query resultsClose -- close the cursorPrepare -- prepare SQL statements for dynam
-- Transaction controlCommit -- end the current transactionRollback -- abort the current transactionSet transaction -- define the data access features of the current TRANSACTION-- Programmatic SQLDECLARE -- set the cursor for the queryExplain -- describe the data access plan for queryOPEN -- OPEN a cursor for retrieving query resultsFETCH -- retrieve a row of query resultsCLOSE -- CLOSE the cursorPREPARE -- prepare SQL statements for dynamic execution
,datalengthO:length www.knowsky.com28. Capitalization Conversion Lower,upper
29. Capitalize the first letter of the wordS: I didn't find it.O:select initcap (' ABCD Dsaf df ') value from dual
30. Left fill space (the first parameter of Lpad is a space function)S:select space + ' ABCD ' valueO:select lpad (' ABCD ') value from dual
31. Right complement space (the first parameter of Rpad is a space function)S:select ' ABCD ' +space (+) valueO:select rpad (' ABCD ') value from dual
32. Delete Space
Differences between oracle and SQL (common functions) (3) ORACLE functions
BitsCN.com
Rn S: select patindex ('% d % q %', 'sdsfasdqe ') value O: oracle not found, however, instr can use the fourth parameter to control the number of occurrences. select INSTR ('sdsfasdqe ', 'SD', 1, 2) value from dual returns 6
24. substring S: select substring ('abcd',) value O: select substr ('abcd',) value from dual
25. substring instead of aijklmnef S: select stuff
Comparison of common functions of SQL Server and Oracle, sqlserveroracle
--------- Mathematical functions1. Absolute ValueS: select abs (-1) valueO: select abs (-1) value from dual2. INTEGER (large)S: select ceiling (-1.001) valueO: select ceil (-1.001) value from dual3. Round (small)S: select floor (-1.001) valueO: select floor (-1.001) value from dual4. Round (truncation)S: select cast (-1.002 as int) valueO: select trunc (-1.002) value from dual5.
The ORACLE tutorial is: OraclePL/SQL single-row functions and group functions. A 1 function is a program with zero or multiple parameters and a return value. Oracle has a series of built-in functions in SQL, which can be called SQL or PL/SQL statements. functions are mainly divided into two categories:23 single row Fun
the starting position of the first occurrence of a pattern in a specified expression, and returns 0 syntax PATINDEX ('%pattern% ', expression) if the pattern is not found in all valid text and character data types(one) peplaceThe function function replaces all occurrences of the second given string expression in the first string expression with a third expression, replace (' String_expression1 ', ' string_expression2 ', ' String_expression3 ‘ )(REVERSE)function function returns the inverse synt
not in this range, null is returned. Select char (72)
Returns the Unicode character with the specified integer code.
Nchar (integer_expression) integer_expression is a positive integer between 0 and 65535. If a value out of this range is specified, null is returned. Select nchar (1, 1000)
Returns a unicode string with delimiters. Adding delimiters can make the input string a valid SQL Server separator identifier.
Quotename ('character _ string') char
Vend_name; Use the function to remove space RTrim () trim () LTrim () Select RTrim (vend_name) + ' (' +rtrim (vend_country) + ') ' from Vendersorder by Vend_name; Use aliases Select RTrim (vend_name) + ' (' +rtrim (vend_country) + ') ' as Vned_titilefrom vendersorder by Vend_name; Perform arithmetic calculations (+-*/) Select Pro_id,quantity,pro_price,quantity*pro_price as Expanded_pricefrom productswhere order_num = 2008;Seven. Using Data processing functionsDifferent DBMS functions function d
function | An explanatory function is a program that has 0 or more parameters and has a return value. In SQL, Oracle builds a series of functions that can be called SQL or PL/SQL statements, and functions fall into two main categories:
Single line function
Group functions
This article discusses how to take advantage of a single-line function and use rules.
Si
Java. util. Date and Java. SQL. Date, java. SQL. Time, java. SQL. Timestamp interchange, java. SQL. timestamp
1. SQL time type to util time type
Principle: java. SQL. date, java. SQL. t
Tags: implementation mechanism originally page organizes tables based on free article content index ClustFollowing the book on SQL tuning (SQL tuning or SQL optimization) in the essentials of high-performance SQL tuning and case resolution, we talk today about one of the most important core mechanisms of
the string to replace. Displays only the results, which do not affect the database data .Select REPLACE(Test,'8','two points') fromXueshengxinxi--REPLICATE Copy, can replicate 0-n times, equivalent to cut and then copySelect REPLICATE('qwe',3)--REVERSE FlipSelect REVERSE(name) fromXueshengxinxi--SOUNDEX Returns a four-character code (SOUNDEX) that evaluates the similarity of two stringsSELECT
domain To delete a domain from the database --Data Control Grant--Grant user access Deny--Deny user access REVOKE--Unlock user access --transaction control COMMIT--End current transaction ROLLBACK--Abort the current transaction SET TRANSACTION--Define the current transaction data access characteristics --Programmatic SQL DECLARE--Set cursors for queries Explan--Describe data access plans for queries Open--Retrieving query results opening a
NULL.② do not use wildcard characters excessively. If other operators can achieve the same purpose, other operators should be used.When you do need to use wildcards, try not to use them at the beginning of the search pattern. The wildcard character is placed at the beginning and the search is the slowest.3. Stitching Field +Statement: SELECT Execution Result:Splicing field a query that synthesizes two fields into a field.4. Text Processing functionsThere are many built-in functions in ①
(rtrim ('
-------------
Gao Qian Jing
11. substr (string, start, count)
Substring, starting from start, count
SQL> select substr ('123', 13088888888) from dual;
Substr ('
--------
08888888
12. Replace ('string', 's1', 's2 ')
String: the character or variable to be replaced.
String to be replaced by S1
String to be replaced by S2
SQL> select Replace ('He love you', 'hes', 'I') from dual;
Replace ('heloveyou
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.