(http://www.9cbs.com.cn/Database/187.htm)
Miles Tsai (Net-Bull@126.com) Songzy@mailcity.com 2000.2 1, SQL Speed Structure Query Language (SQL) is a standard language used to query relational databases, which includes several keywords and consistent syntax for easy database components. (Such as the establishment and manipulation of tables, indexes, fields, etc.). Here are some important SQL quick references, and the SQL syntax and increased features on standard SQL, please query the mysql manual. 1. Creating a table is one of the most basic elements of the database, and the tables can be independent of each other and can be associated with each other. The basic syntax of the creation table is as follows: create Table Table_name (Column_name Datattype {Identity | Null | NOTATYPE}, ...) Where the parameter table_name and column_name must meet the Identifier requirements in the user database, the parameter datatype is a standard SQL Type or type provided by the user database. Users should use the Non-Null clause to enter data. Create Table also has some other options, such as creating a temporary table and using the SELECT clause from another table to form a new table. Also, the creation table is the primary key or index of the identifier that can be set with Primary Key, Key, Index. Pay attention to writing: List of full field list in a pair of parentheses. The field name is spaced apart with a comma. Add a space after the comma between fields. There is no comma after the last field name. All SQL statements are ended in semicolons; Example: MySQL> CREATE TABLE TEST (blob_col blob, index (blob_col (10))); 2. Create an index index for queries for the database. The general database has a variety of indexing schemes, each of which is fine at a particular query class. Index can accelerate the query process of the database. The basic syntax for creating an index is as follows: CREATE INDEX INDEX_NAME ON TABLE_NAME (Col_name [(Length)], ...) An example: mysql> create index part_of_name on Customer (Name (10)); 3. Changing the table structure In the process of use of the database, sometimes you need to change its table structure, including changing the field name, and even changing relationships between different database fields. You can implement the above changed command is ALTER, and its basic syntax is as follows: ALTER TABLE TABLE_NAME ALTER_SPEC [, alter_spec ...] Example: MySQL> ALTER TABLE T1 Change A B Integer; 4. Deleting a data object Many databases are dynamically used, sometimes you may need to delete a table or index. Most database objects can delete the following command: Drop Object_name MySQL> Drop Table TB1; 5. Executing a query query is the most use of the SQL command. Querying the database requires factors such as structures, indexes, and field types. Most databases contain an Optimizer to convert the user's query statements into optional forms to improve query efficiency. It is worth noting that mysql does not support the nested WHERE clauses of SQL92 standards, ie it only supports a WHERE clause.
The basic syntax is as follows: SELECT [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [HIGH_PRIORITY] [DISTINCT | DISTINCTROW | ALL] select_expression, ... [INTO {OUTFILE | DUMPFILE} 'file_name' export_options] [FROM table_references [WHERE where_definition] [ Group by col_name, ...] [haVing where_definition] [Order by {unsigned_integer | col_name | formula} [ASC | DESC], ...] [Limit [offset,] rows] [procedure procedure_name]]] Where WHERE clause is defined Select a standard place, where_definition can have different formats, but all follow the form: Field Name Operation Expression field name action field name Under the first form, the standard compares the value of the field with the expression; in the second Under the form, compare the values of the two fields. According to the compared data type, the operations in Search_Condition may choose the following: = Check if you are equal! = Check if inequality> (or> =) Check if the left value is greater than (or greater than or equal) right value <(or <=) check if the left value is less than (or less than or equal) right value [NOT] BETWEEN Check whether the left side value is In a certain range [not] in checking the left side of a particular set of members [NOT] Like Checks the left side of the substring is [not] null check if the left is null value here, you can use wildcard _ representature any one Characters,% represents any string. Use keyword
Their basic syntax is as follows: Data Add: INSERT [INTO] Table_Name [(Column (s))] VALUES (EXPRESSION (S)): mysql> Insert Into TBL_NAME (COL1, COL2) VALUES (15, col1 * 2); Data Deletion: Delete from Table_name Where Search_Condition Data Change: Update Table_name Set Column1 = Expression1, Column2 = Expression2, ... Where Search_Condition 7. Database Switching When multiple databases exist, you can define the database you want to use with the following command: Use data_name 8. The statistical function SQL has some statistical functions, which is very helpful for generating a data table. Here, several commonly used statistics: SUM (Exepression) computational expression and AVG (Exepression) calculated the average count (Exepression) of the expression to make a simple count count (*) statistics record number MAX (Exepression) Ask the maximum MIN (Exepression) to seek minimum where Exepression is any valid SQL expression, which can be one or more records, or a combination of other SQL functions.
Second, MySQL uses guidance 1. Establish a new database using MySQL Under the shell: $> mysqladmin create database01 database "database" database "created. 2. Launch MySQL Under Shell: $> mysql Welcome To the MySQL Monitor. Commands End with; or g. Your MySQL Connection ID IS 22 TO Server Version: 3.21. 29a-gamma-debug type 'Help' for Help. 3. Replace Database Mysql> Use Database01 Database Changed. 4. Create Table MySQL> Create Table Table01 (Field01 Integer, Field02 Char (10)); Query OK, 0 ROWS Affected (0.00 sec) 5. List lists Mysql> show tables; tables in database01 table01 table02 6. List of fields in the table mysql> show columns from table01; field type null key default extra field01 int (11) Yes Field02 Char (10) Yes 7. Data fill in the inserted data mysql> Insert Into Table01 (Field01, Field02) Values (1, 'First'); Query OK, 1 ROW Affected (0.00 sec) 8. Increase in field ... A field mysql> ALTER TABLE TABLE01 Add Column Field03 Char (20); Query Ok, L Row Affected (0.04 Sec) Records: 1 Duplicates: 0 Warnings: 0 ... Take multiple fields Mysql> Alter Table Table01 Add Column Field04 Date, Add Column Field05 Time; Query Ok, L Row Affected (0.04 SEC) Records: 1 Duplicates: 0 Warnings: 0 Note: Each column must be restarted by "Add Column". Is it running? let us see. MySQL> Select * from table01; field01 field02 field03 field04 field05 1 First Null Null Null 9. Multi-Bank Command Enter the mysql command line interface allows the statement as a row input, or it can be expanded to multi-line input. There is no difference between the two between the two. Use multiple lines of input, you can decompose SQL to decompose steps, making it easier to understand. In multi-line mode, the comment is added to the previous row until you use the semicolon ";" to end this SQL statement. Once the semicolon is typed and press the Enter key, this statement is executed.
The following example is the two input methods of the same strict SQL statement: single line input mysql> create table table33 (Field01 Integer, Field02 Char (30)); Multi-line input mysql> Create Table Table33 -> (Field01 -> Integer, -> Field02 -> Char (30)); Note You cannot disconnect words, such as: correct mysql> create table table33 -> (field01-> integer, -> field02 -> char (30)); error mysql> Create Table Table33 -> (Field01 INTE -> GER, -> Field02 -> Char (30)); When inserting or changing data, you cannot expand a string of the field to multiple rows, otherwise the hard drive will be stored in the data. : Standard operation mysql> INSERT INTO TABLE33 (Field02) -> VALUES -> ('WHO THOUGHT OF FOO?'); Hard Enter Save to Data Mysql> Insert Into Table33 (Field02) -> VALUES -> ('Who Thought -> of foo? '); the results are as follows: mysql> select * from table33; field01 field02 null who thought of foo? Null Who Thought of foo? 10. Table data embedded mysql> INSERT INTO TABLE01 (Field01, Field02, Field03, Field04, Field05) Values -> (2, 'Second', 'Another', '1999-10-23', '10: 30: 30:) Query OK, 1 ROW Affected (0.00 sec) Standard date format is "YYYY-MM-DD". The standard time format is "HH: mm: SS". The requirements given in quotation marks are the above standard date and time format. Dates can also be "YYYYMMDD" form, time can also be entered in "hhmmss", but its value does not need to be quoted. Number values do not need to be quoted. This storage is independent of data type, which has a formatted column to include (for example: text, date, time, integer, etc.). MySQL has a very useful command buffer. It saves the SQL statement you have now typed to use it. For the same command, you don't have to repeat your input over again. Next, let's see an example of this. Add another data to press the up arrow key on the two keyboard by using the command buffer (and any date and time format). Enter. Enter a new value in parentheses and end with a semicolon. (3, 'a think', 'more', 19991024, 103004); Enter.
