Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to use MySQL, the article is very detailed, has a certain reference value, interested friends must read it!

I. SQL crash

Here are some important SQL quick references, consult the MySQL manual for SQL syntax and features added to standard SQL.

1. create tables

Tables are one of the most basic elements of. Tables can be independent or interrelated. The basic syntax for creating tables is as follows:

create table table_name

(column_name datatype {identity |null|not null},

…)

where the parameters table_name and column_name must satisfy the requirements of an identifier in the user database, and the parameter datatype is a standard SQL type or a type provided by the user database. The user enters data for each field using a non-null clause.

Create table has other options, such as creating temporary tables and using select clauses to read fields from other tables to form new tables. Also, in the creation of the table is available PRIMARY KEY, KEY, INDEX and other identifiers to set certain fields as primary keys or indexes.

Note in writing:

A complete list of fields is given in parentheses.

Field names are separated by commas.

A space should be added after commas between field names.

Do not use a comma after the last field name.

All SQL statements end with a semicolon ";".

Example:

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

2. create an index

Indexes are used for querying databases. Most databases have multiple indexing schemes, each specialized in a particular query class. Indexing can speed up the query process against the database. 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 table structure

In the process of using a database, it is sometimes necessary to change its table structure, including changing field names and even changing the relationships between different database fields. The command that can implement the above changes is alter, whose 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 used dynamically, and sometimes it may be necessary to delete a table or index. Most database objects can be deleted with the following command:

drop object_name

mysql> DROP TABLE tb1;

5. execute the query

Query is the most used SQL command. Querying a database depends on factors such as structure, index, and field type. Most databases contain an optimizer that converts user queries into alternative forms to improve query efficiency.

It is worth noting that MySQL does not support nested where clauses in SQL92, i.e. it only supports a single where clause. Its 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_definition can have different formats, but they all follow the following form:

field name operation expression

field name operation field name

In the first form, the standard compares the value of a field to an expression; in the second form, it compares the values of two fields. Depending on the type of data being compared, the actions in search_condition may be selected as follows:

= Check for equality

!= Check for inequality

> (or>=) Check if the left value is greater than (or equal to) the right value

< (或 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

In the process of using a database, you often have to modify the data in its tables, 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:

Data added:

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 changes:

update table_name

set column1=expression1,

column2=expression2,…

where search_condition

7. database rollover

When multiple databases exist, you can define which database the user wants to use with the following command:

use database_name

8. statistical function

SQL has statistical functions that are useful for generating tables of data. Here are a few common statistical functions:

sum (expression) evaluates the sum of expressions

avg (expression) Computes the average of expressions

count (expression) A simple count of expressions

count (*) Number of records

max (expression) Maximum

min (expression) Find the minimum

Where expression is any valid SQL expression, it can be one or more records, or it can be a combination of other SQL functions.

Second, MySQL use guidance

1. Creating a New Database with MySQL

Running in shell:

$>mysqladmin create database01

Database "database01" created.

2. start the MySQL

Running 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

Type help for help.

3. Replacement of database

mysql>use database01

database changed.

4. create tables

mysql>create table table01 (field01 integer, field02 char(10));

Query OK, 0 rows affected (0.00 sec)

5. List of tables

mysql>show tables;

Tables in database01

Table01

table02

6. List the 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 of table

insert data

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

Query OK, 1 row affected (0.00 sec)

8. Addition 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 restart with "add column".

Is it working? Let's see.

mysql>select * from table01;

field01 field02 field03 field04 field05

1 first NULL NULL NULL

Second, MySQL use guidance

1. Creating a New Database with MySQL

Running in shell:

$>mysqladmin create database01

Database "database01" created.

2. start the MySQL

Running 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

Type help for help.

3. Replacement of database

mysql>use database01

database changed.

4. create tables

mysql>create table table01 (field01 integer, field02 char(10));

Query OK, 0 rows affected (0.00 sec)

5. List of tables

mysql>show tables;

Tables in

The above is "MySQL how to use" all the content of this article, thank you for reading! Hope to share the content to help everyone, more relevant knowledge, welcome to pay attention to the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.