SQL Syntax Reference

Reference | grammar | Grammar reference I. DATA definition DDL (data definition Language) Data definition language refers to the format and form of data defined by the language, he is the first time each database to be established when to face, all the data are the form of the relationship between the table, what is the field of the primary key, The relationship between tables and tables, and so on, is what must be planned at the beginning.
1. Build table: CREATE TABLE table_name (COLUMN1 datatype [NOT NULL] [NOT NULL primary key], COLUMN2 datatype [NOT NULL], ...) Description: DataType--is the format of the data, as detailed in the table. Nut null--Can you allow the data to be empty (no data has been filled in yet). Primary key--is the primary key for this table.
2. Change table ALTER TABLE table_name ADD COLUMN COLUMN_NAME datatype description: Add a field (not remove the syntax for a field.) ALTER TABLE TABLE_NAME ADD PRIMARY KEY (column_name) Description: Change the definition of a table to set a field as the primary key. ALTER TABLE table_name DROP PRIMARY KEY (column_name) Description: Deletes the definition of the primary key. 3. Indexing CREATE INDEX index_name on table_name (column_name) Description: Index The field of a table to increase the speed of the query.
4, delete drop table_name drop index_name
Second, the data form datatypes smallint 16-bit integer. Interger a 32-bit integer. Decimal (P,S) p exact value and s size decimal integer, the exact value p means that all have several number (digits) size values, S is the number of several digits after the decimal point. If not specifically specified, the system is set to p=5; S=0. A float 32-bit real number. A double 64-bit real number. char (n) n length of string, n cannot exceed 254. varchar (n) length is not fixed and its maximum length is n of the string, n can not exceed 4000. Graphic (n) is the same as char (n), but its unit is two characters Double-bytes and N cannot exceed 127. This form is designed to support two-character-length fonts, such as Chinese characters. Vargraphic (N) a two-character string with a variable length and a maximum length of n that cannot exceed 2000. Date contains the year, month, and date. Time contains hours, minutes, and seconds. Timestamp contains years, months, days, hours, minutes, seconds, and 1 per thousand seconds.
Iii. Data Manipulation DML (data manipulation language) data is defined, and then the data is manipulated. The operation of the data is nothing more than the addition of data (insert), query data (query), change data (update), delete data (delete) four modes, the following describes their syntax:
1, add data: INSERT INTO table_name (COLUMN1,COLUMN2,...) VALUES (value1,value2, ...) Description:
<1. If the column system is not specified, the data is filled in the order of the fields in the table.
< 2. The data form of the field and the data to be filled must match.
< 3.table_name can also be a landscape view_name. INSERT INTO table_name (column1,column2,...) Select Columnx,columny,... from another_table Note: You can also go through a subquery (subquery) to fill in the other forms of information.
2, Query information: Basic Query Select COLUMN1,COLUMNS2,... from table_name Description: TABLE_NAME Specific field data are listed in the SELECT * FROM table_name where column 1 = xxx [and Column2 > YYY] [or Column3 <> zzz] Description:
<1.´*´ indicates that all of the fields are listed.
After the <2.where is the conditional type, the qualified data are listed. Select COLUMN1,COLUMN2 from table_name orders by COLUMN2 [desc] Description: ORDER BY is specified to be sorted by a field, [desc] is from large to small, and if not indicated, it is a small to large array query Combined query means that the source of the query is not only a single form, but a combination of more than one table can get results. SELECT * from Table1,table2 where Table1.colum1=table2.column1 description:
< 1. Check the data in the two tables with the same column1 values.
< 2. Of course, two tables are compared to each other in a field with the same data form.
<3. A complex query may have many of the forms it uses. Consolidated query: SELECT COUNT (*) from table_name WHERE column_name = XXX Description: There are several strokes of the data that meet the criteria. Select SUM (column1) from table_name Description:
< 1. Calculates the sum, the selected field must be a number shape that can be counted.
< 2. Besides, AVG () is an integrated query that calculates the maximum minimum value of the average, Max (), Min (). Select Column1,avg (COLUMN2) from table_name GROUP BY COLUMN1 have avg (column2) > XXX Description:
<1.group by: The average value of a column1 for a set of Column2 must be used with the keyword of an integrated query such as AVG, SUM, and so on.
< 2.having: must be used with group by as a consolidation restriction. Complex Query select * from table_name1 where exists (SELECT * from table_name2 where conditions) Description:
The conditions of < 1.where can be another query.
< 2.exists here means existence or not. SELECT * from Table_name1 where column1 in (select Column1 from table_name2 where conditions) Description:
< 1. In the back face is a collection that indicates that the COLUMN1 exists within the set.
< 2. The data form of select must conform to Column1. Other queries select * from table_name1 where column1 Like´x%´ description: Like must echo the ´x%´ of the rear to denote a string that begins with X. SELECT * from Table_name1 where column1 in (´xxx´,´yyy´,..) Description: In the back face is a collection that indicates that the COLUMN1 exists within the set. SELECT * from table_name1 where column1 between xx and yy description: between represents column1 value between XX and yy.
3, change information: Update table_name SET Column1=´xxx´where conditoins Description: 1. Change a field to set its value to ´xxx´. 2.conditions is the condition you want to meet, and if there is no where, the entire table's field will all be changed. 4. Delete data: Delete from table_name where conditions Description: deletes eligible data. Note: There are different expressions for different databases if there is a comparison of dates after the Where condition. Specifically as follows:
(1) In the case of an Access database: where mydate> #2000 -01-01#
(2) If it is an Oracle database: where Mydate>cast (´2000-01-01´as date) or: where Mydate>to_date (´2000-01-01´,´yyyy-mm-dd´) Written in Delphi: Thedate=´2000-01-01´; Query1.sql.add (´select * FROM ABC where mydate>cast (´+´´´´+thedate+´´´´+´as date) ´); --Recommended for use in Oracle if the comparison date time type is: where Mydatetime>to_date (´2000-01-01 10:00:01´,´yyyy-mm-dd Hh24:mi:ss´)