SQL syntax
First, SQL crash
Structure Query Language (SQL) is a standard language used to query relational databases, which includes several keywords and consistent syntax to facilitate the establishment and manipulation of database components such as tables, indexes, fields, and so on.
Here are some important SQL quick references, for SQL syntax and added features on standard SQL, please check the MySQL manual.
1. Create a table
A table is one of the most basic elements of a database, and can be independent of each other or associated with a table. The basic syntax for creating a table is as follows:
CREATE TABLE table_name
(column_name data is invalid {identity |null|not null},
...)
where parameter table_name and COLUMN_NAME must meet the requirements of the recognizer (identifier) in the user database, parameter data is not valid as a standard SQL type or a type provided by the user database. Users use non-null clauses to enter data for each field.
CREATE table has other options, such as creating temporary tables and reading some fields from other tables using the SELECT clause to make a new table. Also, in the creation table is available primary key, key, index, and other identifiers to set some fields as the primary key or index.
To note in writing:
The complete list of fields is listed in a pair of parentheses.
Field names are separated by commas.
Add a space after the comma between the field names.
The last field name is not followed by a comma.
All SQL statements are semicolon ";" End.
Cases:
mysql> CREATE TABLE Test (Blob_col blob, index (Blob_col (10)));
2. Create an index
The index is used for queries against the database. The general database is built with multiple indexing schemes, each of which is proficient in a particular query class. Indexes can speed up the query process for a database. The basic syntax for creating an index is as follows:
CREATE INDEX Index_name
On table_name (col_name[(length)],...)
Cases:
Mysql> CREATE INDEX part_of_name on customer (name (10));
3. Change Table structure
In the process of using a database, it is sometimes necessary to change its table structure, including changing the name of the field, or even changing the relationship between different database fields. The command that can achieve this change is alter, whose basic syntax is as follows:
ALTER TABLE table_name ALTER_SPEC [, Alter_spec ...]
Cases:
mysql> ALTER TABLE T1 change a b INTEGER;
4. Delete a Data object
Many databases are used dynamically, and sometimes you may need to delete a table or index. Most database objects can be removed by the following command:
Drop object_name
mysql> DROP TABLE tb1;
5. Execute Query
The query is the SQL command with the most use. Querying a database requires factors such as structure, index, and field type. Most databases contain an optimizer (optimizer) that converts a user's query statement into an optional form to improve query efficiency.
It is noteworthy that MySQL does not support the SQL92 standard nested WHERE clause, which supports only 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 the WHERE clause is the place to define the selection criteria, where_definition can have different formats, but all follow the following form:
Field name Action expression
Field name Action field name
In the first form, the standard compares the value of a field to an expression, and in the second form, compares the values of two fields. Depending on the data type being compared, the actions in Search_condition may choose the following:
= Check for equality
！ = Check whether unequal
> (or >=) check whether the left value is greater than (or greater than or equal to) the right value
< (or <=) check if the left value is less than (or less than equal to) the right
[NOT] between checks whether the left value is within a range
[NOT] in check whether a member of a particular set is left
[NOT] like check whether the left side is the right substring
is [not] null check for null value on left
Here, you can use the wildcard character _ to represent any one character, and% to represent any string. Use keywords <AND>, <OR>, and <NOT> to generate complex words that run checks using multiple standard sets of Boolean expressions.
Cases:
Mysql> Select T1.name, t2.salary from employee as T1, info as t2 where t1.name = T2.name;
Mysql> Select College, Region, seed from tournament
Order by region, seed;
Mysql> Select Col_name from tbl_name WHERE col_name > 0;
6. Modify data in a table
When using a database, you often modify the data in its tables, such as adding new data to the table, deleting the existing data in the table, or making changes to the existing data in the table. Their basic syntax is as follows:
Data add:
Insert [INTO] table_name [(column (s))]
VALUES (expression (s))
Cases:
Mysql> INSERT into Tbl_name (col1,col2) VALUES (15,col1*2);
Data deletion:
Delete from table_name where search_condition
Data changes:
UPDATE table_name
Set Column1=expression1,
Column2=expression2,...
where search_condition
7. Database switching
When multiple databases exist, you can use the following command to define the database that users want to use:
Use database_name
8. Statistical functions
SQL has some statistical functions that are useful for generating data tables. Here are a few common statistical functions:
SUM (exepression) evaluates expressions and
AVG (exepression) calculates the average of an expression
Count (exepression) a simple count of an expression
COUNT (*) Statistics records
Max (exepression) max value
Min (exepression) to find the minimum value
Where exepression is any valid SQL expression, it can be one or more records, or it can be a combination of other SQL functions.
Second, the use of MySQL guide
1. Using MySQL to create a new database
Run under the shell:
$>mysqladmin Create Database01
Database "Database01" created.
2. Start MySQL
Run under the shell:
$>mysql
Welcome to the MySQL Monitor. Commands End With; or G.
Your MySQL Connection ID is to server version:3.21. 29a-gamma-debug
Invalid ' help ' for help.
3. Replacing a database
Mysql>use database01
Database changed.
4. Create a table
Mysql>create table table01 (field01 integer, field02 char (10));
Query OK, 0 rows Affected (0.00 sec)
5. List List of tables
Mysql>show tables;
Tables in Database01
Table01
Table02
6. List the fields in the table
Mysql>show columns from TABLE01;
Field invalid Null Key Default Extra
FIELD01 Int (one) YES
FIELD02 Char YES
7. Table Data Fill in
Inserting data
Mysql>insert into Table01 (FIELD01, FIELD02) VALUES (1, ' a ');
Query OK, 1 row Affected (0.00 sec)
8. Increase of fields
... One field at a time
Mysql>alter table table01 Add Column field03 char (20);
Query OK, L row affected (0.04 sec)
Records:1 duplicates:0 warnings:0
... Multiple fields at once
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 with "add column".
Has it run yet? Let's see.
Mysql>select * from TABLE01;
FIELD01 field02 field03 field04 field05
1-null NULL NULL
9. Multi-line command input
The MySQL command-line interface allows the statement to be entered as a single line, or it can be expanded into multiline input. There is no grammatical difference between the two. With multiple lines of input, you can break down the SQL statement step-by-step, making it easier to understand.
In multiline mode, the annotation adds each row to the previous line until you use the semicolon ";" To end this SQL statement. Once you type a semicolon and press ENTER, the statement is executed.
The following example is the two input methods for 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 break a word, such as:
That's right
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 field's string to multiple lines, or hard return will be stored in the data:
Standard operation
Mysql>insert into Table33 (FIELD02)
->values
-> (' Who thought of Foo? ');
Hard return is stored in the 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. Data embedding in a table
Mysql>insert into Table01 (FIELD01, FIELD02, Field03, FIELD04, FIELD05) values
-> (2, ' second ', ' Another ', ' 1999-10-23 ', ' 10:30:00 ');
Query OK, 1 row Affected (0.00 sec)
The standard date format is "Yyyy-mm-dd".
The standard Time format is "Hh:mm:ss".
Quotation marks are required to give the above standard date and time format.
The date can also be "YYYYMMDD" and the time can be entered as "HHMMSS", but the value does not need to be quoted again.
Numeric values do not need to be quoted. This save is not related to the data type, which has a formatted column to contain (for example: text, date, time, integer, etc.).
MySQL has a very useful command buffer. It keeps the SQL statement you've typed into it, and you don't have to repeat it over and over again for the same command. Next we'll look at an example of this.
Add another data using the command buffer (and any date and time format)
Press the UP ARROW key on the keyboard two times.
Enter.
Enter a new value within the parentheses and end with a semicolon.
(3, ' A third ', ' more ', 19991024, 103004);
Enter.
Does the new value exist inside?
Mysql>select * from TABLE01;
FIELD01 field02 field03 field04 field05
1-null NULL NULL
2 Second another 1999-10-23 10:30:00
3 A third more 1999-10-24 10:30:04
11. Data updates for tables
Modify one field at a time
Pay attention to grammar again. Text needs to be enclosed in quotes but not in numbers.
mysql> Update table01 set field03= ' new info ' where field01=1;
Query OK, 1 row Affected (0.00 sec)
Change multiple fields at once
Remember to separate each updated field with a comma.
mysql> Update table01 set field04=19991022, field05=062218 where field01=1;
Query OK, 1 row Affected (0.00 sec)
Update multiple data at once
mysql> Update table01 set field05=152901 where field04>19990101;
Query OK, 3 Rows Affected (0.00 sec)
12. Delete data
Mysql> deletes the from table01 where field01=3;
Query OK, 1 row Affected (0.00 sec)
13. Exit
Mysql>quit
Bye
Now you've learned a few basic commands for running a database in MySQL. Because MySQL is operated by executing SQL calls, a sufficient array of powerful tools is needed in your processing. For example, by joining related fields, you can display data from several tables at the same time. Similarly, SQL allows you to synthesize, update, or delete multiple data that meet specific criteria. If you also want to master it, the next step is to learn all the knowledge of SQL.
