MySQL initial experience

Robust: MySQL

MySQL syntax

Mysql5.0.41 non-installation configuration method

Common mysql5.0 commands

Mysql5.0.41 user manual Olympic Edition

Chinese character support problem in MySQL-5.0.41-win32 DoS

Proficient in MySQL (reproduced)

Download: http://www.pcbookcn.com/article/51_1.htm

Related documents: http://dev.mysql.com/doc/refman/5.1/zh/index.html

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 data is invalid {identity | null | not null },

...)

The table_name and column_name parameters must meet the requirements of identifier in the user database. Invalid parameter data 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 the complete fields in a pair of parentheses.

Field names are separated by commas.

Enter a space after the comma (,) between field names.

The last field name is not followed by a comma.

All SQL statements end 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 checks 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. Keywords <and>, <or>, and <not> can be used to generate complex words. They use multiple standard sets of boolean expressions when running the check.

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 expressions

AVG (exepression) calculates the average value of the expression

Count (exepression) performs simple counting on the Expression

Count (*) number of statistical records

Max (exepression) returns 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.

Ii. MySQL user guide

1. Use MySQL to create a new database

Run in shell:

$> Mysqladmin create database01

Database "database01" created.

2. Start MySQL

Run in 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

Invalid 'help' for help.

3. Change the 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 tables

Mysql> show tables;

Tables in database01

Table01

Table02

6. list fields in the table

Mysql> show columns from table01;

Invalid Field null key default extra

Field01 int (11) Yes

Field02 char (10) Yes

7. Enter data in the table

Insert data

Mysql> insert into table01 (field01, field02) values (1, 'first ');

Query OK, 1 row affected (0.00 Sec)

8. Add 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 a time

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 start again with "add column.

Is it running? Let's take a look.

Mysql> select * From table01;

Field01 field02 field03 field04 field05

1 first null

9. Multi-line command input

The MySQL command line interface allows a statement to be input as one line, or expanded into multiple lines. There is no syntax difference between the two. With multi-line input, you can break down SQL statements step by step to make it easier to understand.

In the multi-row mode, the annotator adds each row to the previous row until you use the Semicolon ";" to end the SQL statement. Once you type a semicolon and press enter, this statement is executed.

The following example shows two input methods for the same strict SQL statement:

Single Row Input

Mysql> Create Table table33 (field01 integer, field02 char (30 ));

Multi-line Input

Mysql> Create Table table33

-> (Field01

-> Integer,

-> Field02

-> Char (30 ));

Note that you cannot disconnect words, such:

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 the string of the field to multiple rows. Otherwise, press enter to store the data:

Standard Operation

Mysql> insert into table33 (field02)

-> Values

-> ('Who thought of Foo? ');

Press enter to store data

Mysql> insert into table33 (field02)

-> Values

-> ('Who thought

-> Of Foo? ');

The result is as follows:

Mysql> select * From table33;

Field01 field02

Null who thought of Foo?

Null who thought

Of Foo?

10. Table Data embedding

Mysql> insert into table01 (field01, field02, field03, field04, field05) Values

-> (2, 'second', 'Another ', '2017-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 ".

The quotation marks must be in the preceding standard date and time format.

The date format can also be "yyyymmdd" or "hhmmss", but the value does not need to be enclosed in quotation marks.

No quotation marks are required for numeric values. This type of storage is irrelevant to the data type. These data types are included in formatted columns (such as text, date, time, and integer ).

MySQL has a very useful command buffer. It saves the SQL statement you have already typed and used it. For the same command, you do not have to repeat the input over and over again. Next, let's look at this example.

Add another data using the command buffer (and any date and time format)

Press the up arrow on the keyboard twice.

Press enter.

Enter a new value in parentheses and end with a semicolon.

(3, 'a third', 'more', 19991024,103 004 );

Press enter.

Is the new value included?

Mysql> select * From table01;

Field01 field02 field03 field04 field05

1 first null

2 second another 10:30:00

3 a third more 10:30:04

11. Update table data

Modify a field at a time

Pay attention to the syntax again. The text must be enclosed in quotation marks but not digits.

Mysql> Update table01 set field03 = 'new info' where field01 = 1;

Query OK, 1 row affected (0.00 Sec)

Change multiple fields at a time

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 a time

Mysql> Update table01 set field05 = 152901 where field04> 19990101;

Query OK, 3 rows affected (0.00 Sec)

12. delete data

Mysql> Delete from table01 where field01 = 3;

Query OK, 1 row affected (0.00 Sec)

13. Exit

Mysql> quit

Bye

-------------------------- (Books are borrowed and recorded for ease of viewing ------------------------------------------------------------------------------------

1. Use the show statement to view databases on the server

Mysql> show databases;

2. Create a database example

Mysql> Create Database example;

3. Select a database

Mysql> use example;

4. Use the show statement to View tables in the database.

Mysql> show tables;

5. Create a database. Create a birthday table for a class member. The table contains the name, gender, and date of birth.

Mysql> cteate table mytable

-> (Name varchar (20 ),

-> Sex char (1 ),

-> Birth date;

6. display the table structure

Mysql> describe mytable;

7. Add records to the table

Use the SELECT statement to view the data in the table.

Mysql> select * From mytable;

Empty set (0.00 Sec)

Add a new record

Mysql> insert into mytable

-> Values ('echo ', 'F', '2017-05-14 ');

Using the SELECT statement above, we can find that a new record is added to the mytable table.

8. load data into a database table in text mode. It is troublesome to enter one entry. All records can be added to the database using text files.

Create a text file "mysql.txt" under G:/code. Each line contains a record, which is separated by a tab, it is given in the order of columns listed in the create table statement, for example:

Jerry M 1977-07-07

Mary F 1978-12-12

Commy F 1970-09-02

You can run the command to load the file "mysql.txt" to the mytable table.

Mysql> load data local infile "G: // code // mysql.txt 'into table mytable;

View:

Mysql> select * From mytable;

Delete table:

Mysql> drop table mytable;

Delete Database

Mysql> drop database example;

Use the show databases statement to view the results;