MySQL Database learning experience (3)

Three. MySQL Common sense

(i) field type

1.int[(M)]

Normal size integer type

2.double[(m,d)] [Zerofill]

Normal size (double precision) floating-point number type

3.DATE

Date type. The scope of support is ' 1000-01-01 ' to ' 9999-12-31 '. MySQL displays date values in ' YYYY-MM-DD ' format, but allows you to assign values to date columns using strings or numbers

4.CHAR (M)

Fixed-length string type, when stored, always fills the right to the specified length with a space

5.BLOB TEXT

Blob or text type with a maximum length of 65535 (2^16-1) characters.

6.VARCHAR

Variable-length string type, the most commonly used type.

(ii) Basic operations

1: Show the database

Mysql>show DATABASES;

2: The currently selected database,

Mysql> SELECT DATABASE ();

+------------+

| DATABASE () |

+------------+

| Test |

+------------+

3. Table information contained in the current database:

Mysql> show TABLES;

+---------------------+

| Tables in Test |

+---------------------+

| Mytable1 |

| Mytable2 |

+---------------------+

4. Get table structure

mysql> desc Mytable1;

+---------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+---------+-------------+------+-----+---------+-------+

| S1 | varchar (20) | YES | | NULL | |

+---------+-------------+------+-----+---------+-------+

5. 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 datatype {identity |null|not null},

...)

Where the parameters table_name and COLUMN_NAME must meet the requirements of the recognizer (identifier) in the user database, the parameter datatype is 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. 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 Guest (name varchar, sex varchar (2), age int (3), career varchar (10));

6. 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 number on guest (number (10));

7. 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 can 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 T2swherest1.name = t2.name;

Mysql> Select College, Region, seed from tournament

Order by region, seed;

Mysql> Select Col_name from Tbl_nameswherescol_name > 0;

8. Change the 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 dbname add column userid int (one) NOT null primary key auto_increment;

In this way, a field userid is added to the table dbname, and the type is int (11).

9. Modify the data in the 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>insertsintosmydatabase values (' php ', ' MySQL ', ' asp ', ' SQL Server ', ' JSP ', ' Oracle ');