SQL statement is a small mind

2010-02-06  来源:本站原创  分类:Database  人气:203 

SELECT DISTINCT statement <br /> in the table may contain duplicate values. This is not a problem, but sometimes you may want to just list different (distinct) values.
Keywords DISTINCT only difference for the return value.
Syntax: SELECT DISTINCT name FROM table name column

<br /> For WHERE clause to conditionally select data from the table can be added to the SELECT statement WHERE clause.
Syntax: SELECT column names FROM table name WHERE column operator value of the following operators can be used in the WHERE clause:
Operator Description
= Equal to
<> Is not equal to
> Greater than
<Less than
> = Greater than or equal
<= Less than or equal
BETWEEN within a certain range
LIKE search for a pattern Note: In some versions of SQL, operator <> can be written as! =

AND and OR operators
WHERE AND and OR sub-statement in the combination of two or more conditions. If the first condition and second conditions are true, then the AND operator displays a record. If the first condition and second condition as long as there is a set up, the OR operator displays a record.

ORDER BY statement
ORDER BY statement is used according to the specified column to sort the result set. ORDER BY statement, the default sort in ascending order on the record. If you want to sort the records in descending, you can use the DESC keyword; ascending order with the ASC.

INSERT INTO statement
INSERT INTO statement is used to insert a new row in the table.
INSERT INTO table name VALUES (value 1, value 2 ,....)
We can also specify the column to insert the data:
INSERT INTO table_name (column 1, column 2 ,...) VALUES (value 1, value 2 ,....)

Update statement
Update statement to modify data in the table.
Syntax: UPDATE table name SET column column name = new value WHERE name = a value such as: UPDATE Person SET Address = 'Zhongshan 23', City = 'Nanjing' WHERE LastName = 'Wilson'

DELETE statement
DELETE statement is used to delete a table row.
Syntax: DELETE FROM table WHERE name = value column name

LIMIT statement
LIMIT statement is used to control the output of the Number of data syntax: SELECT * FROM table name WHERE name = a value of LIMIT column value

LIKE operator
LIKE operator in the WHERE clause for the column specified in the search mode.
Syntax: SELECT * FROM table WHERE name LIKE pattern column name wildcard: "%" means any length of characters, "_" represents a character, [charlist] that character out of any single character, [^ charlist] or [ ! charlist] characters that are not listed in any single character

IN operator
IN operator in the WHERE clause allows us to provision multiple values.
Syntax: SELECT * FROM table name WHERE column name IN (value1, value2 ,...)
If (value1, value2 ,...) is character, then, must be accompanied by single quotation marks

BETWEEN operator <br /> BETWEEN ... AND operator will be selected between the range of data between two values. These values can be numeric, text or date.
SELECT column_name (s) FROM table_name WHERE column_name BETWEEN value1 AND value2
For use outside the scope of the above examples show, please use the NOT operator
SELECT column_name (s) FROM table_name WHERE column_name NOT BETWEEN value1 AND value2

As examples: using table name aliases assume we have two tables are: "Persons" and "Product_Orders". We were their designated alias "p" and "po".
Now we want to list "John Adams" to all orders.
We can use the following SELECT statement:
SELECT po.OrderID, p.LastName, p.FirstName FROM Persons AS p, Product_Orders AS po WHERE p.LastName = 'Adams'AND p.FirstName =' John '

  • Skills: dynamically created SQL statement 2009-06-12

    MATT on his blog, has posted a dynamically created SQL statements Bo-wen. In the text, he referred to himself using the IN operator when a database query problems: According to the set of external incoming IN operator in the number of decisions place

  • ID generation strategies and Save () whether to issue the sql statement of: 2008-08-23

    ID generation strategies and Save () whether to issue the sql statement of: The primary key generation side of road is the uuid, so called after the completion of save, but the entity object into the session management, and will not issue insert stat

  • SQL statement used the classical 2010-02-04

    1 - Note: the word of several high-level query operations 2A: UNION operator 3UNION operator results through a combination of the other two tables (for example, TABLE1 and TABLE2) and eliminate any duplicate rows in the table derives a result table.

  • In the SQL statement optimization process, often used hint (change) 2010-02-26

    In the SQL statement optimization process, often used hint, The following are the common Oracle SQL optimization process in the "HINT" use of 30 1. / * + ALL_ROWS * / Show that the choice of the block cost-based optimization method, and the best

  • Rails detail (12) Sql statement 2010-03-24

    Active Record is how to deal with SQL, we find ways to look at the: conditions parameter, when called like this: find (: all,: conditions => ...), here: conditions parameter determines which records find method returns It is equivalent to where part

  • sql statement to update the same data, different data insert 2010-03-09

    Execute a Sql statement, the same vendor_id, inventory_item_id to update, insert new data in different merge into tscs_ems_upload_storage_t a using (select? as vendor_id, ? As inventory_item_id, ? As description,? As unit, ? As quantity,? As check_da

  • ORACLE SQL statement Categories 2010-03-11

    Oracle SQL statements can be divided into the following categories: Data manipulation language statements [Data manipulation language, DML] From one or more tables or views in the query data (SELECT); for operation [fetch] is scrollable [scrollable]

  • Classic SQL statement 2010-03-21

    Classic SQL statement 1. Copy table (copy the data source table name: A (a, b, c) target table name: B) 2. Copy the table (only copy the structure, the source table name: A (a, b, c) a new table name: B), to insert a record 1,2,3 B 3. There are two t

  • About 1 million records SQL statement optimization, need to remove all the duplicate data 2010-03-22

    Need to get all the fields in Table A, b the same data as the SQL used to query a database of all duplicate records in a table! Such as Table A ---------------------- ID USER PASS ---------------------- 1 test1 a 2 test2 b 3 test3 c 4 test3 cc 5 test

  • sql statement optimization 2010-03-23

    Today, their ancestors left behind in optimizing a sql statement and found two problems. These issues are resolved, SQL statement execution efficiency has been greatly improved. First of all, tell us about the situation. Database is oracle. We use th

  • An SQL statement face questions on the group by 2010-03-24

    An SQL statement face questions on the group by Table contents :2005-05-09 wins 2005-05-09 win 2005-05-09 Negative 2005-05-09 Negative 2005-05-10 win 2005-05-10 Negative 2005-05-10 Negative If you want to generate the following results, how to write

  • oracle and sql server query SQL statement page 2010-03-25

    1.Oracle paging sql statement SELECT * FROM (SELECT A.*, ROWNUM RN FROM ( select sno,sname,sage,ssex from tb_student where 1=1 ) A WHERE ROWNUM <'24') WHERE RN >='0' 2.Sql Server paging sql statement SELECT sno,sname,sage,ssex FROM ( SELECT ROW_NUMB

  • Oracle query and delete duplicate records in SQL statement collection 2010-03-25

    Oracle query and delete duplicate records in SQL statement collection ORACLE query and delete duplicate data, we often encounter problems and need treatment, although often useful in that way a lot. But do not want to sort out, so reposted elsewhere

  • The highest utilization rate of the SQL statement in a few 2010-03-26

    The highest utilization rate of the SQL statement in a few 1. Create tables (including multiple fields) Create table table_name (column_name1 datatype1, column_name2 datatype2 ,...) 2. Delete table (all records) Delete table_name delete "form record&

  • ORACLE SQL statement optimization summarized in 2010-02-23

    ORACLE SQL statement optimization summarized in (1) Select the most efficient sequence table name (only the effective rule-based optimizer): ORACLE parser in accordance with the order processing from right to left in the FROM clause of the table name

  • ORACLE database SQL statement optimization techniques 2010-03-15

    ORACLE database SQL statement optimization techniques: IN operator IN written with the advantages of SQL is easier to write and clear easy to understand, is more suitable for modern software development style. However, the SQL performance with IN is

  • Window platform, MYSQL SQL statement execution time 2010-03-15

    1. The following SQL statement, save for the delete.sql; use qdcomland; delete from cdb_product where 2. Save the following batch file commands del.bat @ Echo off D: \ AppServ \ MySQL \ bin \ mysql-hlocalhost-uroot-proot <delete.sql Note, delete.sql

  • Optimization of SQL statement based on the index of 2010-03-15

    1 Introduction 2 2 Master 2 3 Eighteen 3 The first metacarpal to avoid the operation of the column 3 The second metacarpal to avoid unnecessary type conversions 4 The third metacarpal increased to limit the query 4 Get rid of the fourth metacarpal as

  • Several recent use Oracle sql statement 2010-04-07

    1. For with a hierarchical tree structure of data read For example: table A: id, name, parentid Id = 999 then known, and need to find 1) All subordinate nodes (including itself) select level, id from A start with 2) all higher nodes select level, id

  • Oracle View user default table space usage sql statement 2010-04-07

    View user default tablespace. Sql: select username, default_tablespace from dba_users; View all tables occupied disk space conditions. Sql: select b.file_id document ID number b.tablespace_name table space name, b.bytes/1024/1024 | | 'M' number of by