From: chinaunix.net 1. Data definition DDL (Data Definition Language) Data-defined language refers to the language defined in the format and shape of the information. He first faces every time you have to build, and the information is divided. Which formal relationships, what fields in the form, the relationship between the table, and the table, etc., all must be planned at the beginning. 1. Built: Create Table Table_Name (Column1 Datatype [NOT NULL] [Not Null Primary Key], Column2 DataType [Not Null], ...) Description: DataType - is the format of the data, see the table. NUT NULL - You can not allow information to be available (not yet filled in). Primary Key - is the primary key of this table. 2, change the form ALTER TABLE_NAME Add Column Column_name DataType Description: Add a field (no grammar to delete a certain field. Alter Table Table_Name Add Primary Key Description: Change the definition of the episode sets a certain field set Primary key. ALTER TABLE TABLE_NAME DROP Primary Key Description: Delete the primary key. 3, establish an index create index index_name on table_name (column_name) Description: Establish an index for a table to increase the speed of the query. 4 Delete DROP TABLE_NAME DROP INDEX_NAME II, the data form DataTypes Smallint 16-bit integers. Interger 32-bit elements of the integer. Decimal (p, s) p exact value and S size ten-in-one integer, accurate value P is all Several number (DIGITS) large value, S is a few digits after the decimal point. If there is no special designation, the system is set to P = 5; s = 0. The real number of Float 32-bit yuan. Double 64-bit real number CHAR (N) N length string, N cannot exceed 254. VARCHAR (N) is not fixed and its maximum length is a string of N, N cannot exceed 4000. Graphic (n) and char (n), but The unit is two characters Double-Bytes, n cannot exceed 127. This form is to support the fonts of the two character lengths, such as Chinese words. Vargraphic (n) variable length and its maximum length is a double-word element with N. String, N cannot exceed 2000. Date contains years, month, date. Time contains hours, minutes, seconds. TimeStamp contains years, month, day, hour, minute, second, thousandth, information Operating DML (Data Manipulation Language) The following is the data operation. The data is operative to add information (query), query, change the information, delete four Mode, the following introduces their grammar: 1, add information: Insert Into Table_name (Column1, Column2, ...) Values (Value1, Value2, ...) Description: 1. If you do not specify a column system, it will be filled in the order in the field in the form.
2. The data form of the field and the information filled in the field must be consistent. 3.Table_name can also be a landscape view_name. INSERT INTO TABLE_NAME (Column1, Column2, ...) SELECT Columnx, Columnx, Column, ... from another_table description: You can also fill in other forms of tables after a subquery (SubQuery). 2, inquiry information: Basic query Select Column1, Column2, ... from Table_Name Description: Column of Table_name's specific field information SELECT * home_name where column1 = xxx [and color2> yyy] [or column3 <> zzz] Description: 1. '*' is listed in all fields. 2.Where is the conditional pattern, and the eligible information is listed. Select Column1, Column2 from Table_Name ORDER BY Column2 [Desc] Description: ORDER BY is specified to be sorted in a certain field, and [dec] means from big to small arrangement, if there is no indication, it is from a small to large array inquiry combination The query refers to the source of the data is not only a single table, but a combination of more than one table can be obtained. Select * from table1, table2 where table1.colum1 = table2.column1 Description: 1. Query data in both tables in the two tables. 2. Of course, the two forms are compared to each other, and their data must be the same. 3. A complex query that it can be used to use may be many. Integrated query: select count (*) from table_name where colorn_name = xxx Description: Querying the qualified information has a total of several. SELECT SUM (Column1) from table_name Description: 1. Calculate the sum, the selected field must be a number of digital forms. 2. Avg () is the integrated query for calculating average, max () and min () calculate the maximum minimum. Select Column1, AVG (Column2) from Table_name Group By Column1 Having AVG (Column2)> xxx Description: 1.Group By: Use Column1 to calculate the average of the COLUMN2 must be used with the keywords of AVG, SUM and other integrated queries . 2.Having: It must be used as a constraint for integration with Group By. Complency Query Select * from Table_name1 WHERE EXISTS (Select * from table_name2 where conditions) Description: 1.where's conditions can be another Query. 2.Exists herein refers to whether there is. Select * from table_name1 WHERE Column1 in (Select Column1 from Table_Name2 WHERE CONDitions) Description: 1. The later is connected to a collection, indicating that the column1 exists in a collection. 2. SELECT's data must meet Column1. Other Query Select * from table_name1 where column1 like 'x%' Description: LIKE must be and the back 'X%' Echo indicates the string starting with X.
