MySQL Quick Start Guide (1)

I. SQL fast-Structured Query Language (SQL) is a standard language used to query relational databases. It includes several keywords and consistent syntax to facilitate database components (such as tables, indexes, and fields). The following is an important quick reference for SQL. For SQL syntax and features added to standard SQL, please query the MySQL manual. 1. Create a table

I. SQL fast-Structured Query Language (SQL) is a standard language used to query relational databases. It includes several keywords and consistent syntax to facilitate database components (such as tables, indexes, and fields). The following is an important quick reference for SQL. For SQL syntax and features added to standard SQL, please query the MySQL manual. 1. Creating a table is

I. SQL Quick Start

The Structure Query Language (SQL) is a standard language used to query relational databases. It includes several keywords and consistent syntax to facilitate database components (such as tables, indexes, and fields).

The following is an important quick reference for SQL. For SQL syntax and features added to standard SQL, please query the MySQL manual.

1. Create a table

A table is one of the most basic elements of a database. Tables and tables can be independent or associated with each other. The basic syntax for creating a table is as follows:

create table table_name  (column_name datatype {identity |null|not null},  …)

The table_name and column_name parameters must meet the requirements of identifier in the user database. The parameter ype is a standard SQL type or a type provided by the user database. You must use the non-null clause to input data for each field.

Create table also has some other options, such as creating a temporary table and reading some fields from other tables using the select clause to form a new table. In addition, when creating a table, you can use the primary key, KEY, INDEX, and other identifiers to set certain fields as PRIMARY keys or indexes.

Note the following when writing:
◆ List complete fields in a pair of parentheses.
◆ Field names are separated by commas.
◆ Add a space after the comma between field names.
◆ Do not use a comma after the last field name.
◆ All SQL statements are ended with a semicolon.

Example:

mysql> CREATE TABLE test (blob_col BLOB, index(blob_col(10)));

2. Create an index

Indexes are used to query databases. Generally, a database has a variety of index solutions, each of which is precise to a specific query class. Indexes can accelerate the database query process. The basic syntax for creating an index is as follows:

create index index_name  on table_name (col_name[(length)],... )

Example:

mysql> CREATE INDEX part_of_name ON customer (name(10));

3. Change the table structure

When using a database, you sometimes need to change its table structure, including changing the field name or even changing the relationship between different database fields. The alter command can be implemented. Its basic syntax is as follows:

alter table table_name alter_spec [, alter_spec ...]

Example:

mysql> ALTER TABLE t1 CHANGE a b INTEGER;

4. delete data objects

Many databases are dynamically used, and sometimes a table or index needs to be deleted. Most database objects can be deleted using the following command:

drop object_name  mysql> DROP TABLE tb1;

5. Execute the query

Query is the most commonly used SQL command. The query database must rely on factors such as structure, index, and field type. Most databases contain an optimizer that converts user query statements into optional forms to improve query efficiency.

It is worth noting that MySQL does not support standard SQL 92 nested where clauses, that is, it only supports one 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 where the definition is selected. where_definition can have different formats, but they all follow the following format:

◆ Field name Operation expression
◆ Field name operation field name

In the first form, the value of the field is compared with the expression. In the second form, the values of the two fields are compared. Depending on the data type, the following operations may be selected in search_condition:

◆ = Check for Equality
◆! = Check whether the value is different
◆> (Or> =) check whether the left value is greater than (or greater than or equal to) the right value
◆ <(Or <=) check whether the left value is less than (or less than or equal to) the right value
◆ [Not] between check whether the left value is within a certain range
◆ [Not] in check whether a member of a specific set is on the left
◆ [Not] like check whether the left side is a substring on the right
◆ Is [not] null check whether the left side is null

Here, wildcard _ can be used to represent any character, and % represents any string. Keyword, And You can generate complex words that use multiple standard sets of boolean expressions during runtime checks.

Example:

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 Table Data

When using a database, you often need to modify the data in its table, such as adding new data to the table, deleting the original data in the table, or changing the original data in the table. Their basic syntax is as follows:

Add data:

insert [into] table_name [(column(s))]  values (expression(s))

Example:

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 use the following command to define the database you want to use:

use database_name

8. Statistical functions

SQL has some statistical functions, which are helpful for generating data tables. The following describes several common statistical functions:

◆ Sum (exepression) calculates the sum of the expressions
◆ Avg (exepression) calculates the average value of the expression
◆ Count (exepression)
◆ Count (*) count the number of records
◆ Max (exepression) calculates the maximum value.
◆ Min (exepression) calculates the minimum value

Exepression is any valid SQL expression. It can be one or more records or a combination of other SQL functions.