Capturing important knowledge points in mysql (1)

I. Classification of mysql commands

Mysql commands can be divided into client commands and server commands:

1) Client commands

\ C: early termination of concluding remarks

\ G: Send the statement to the server for execution no matter what the statement Terminator is;

\ G: no matter what the statement Terminator is, the statement is directly sent to the server for execution and the result is displayed in a vertical bar;

\! COMMAND: execute shell commands

\ W: The warning message is displayed after the statement is executed;

\ #: Supports full-function complementing for newly created objects;

\ R: reconnect to the server

Supplement: mysql client Commands include mysqldump (Backup Tool), mysqlimport (import tool), and mysqlcheck.

2) server commands:

Mysqld

Mysql_multi: supports multi-instance

Mysql_safe: supports Secure threads.

Mysqlbinlog: View mysql binary tools

Mysqlhotcopy: mysql Backup Tool

The server statement has a statement Terminator. The default value is semicolon.

\ D: Define the statement Terminator. delimiter defines the statement Terminator)

// Statement Terminator

Eg: show database // use \ d if you want to change it back

All server statements can only be executed by the client, but the client does not need statement Terminator;

How can I obtain help from server commands?

Help COMMAND: Get help information

Help command index: to obtain the index help information for further help information, you can copy the link after the URL displayed after executing the help command index and paste it to the browser to obtain the official documentation)

Ii. Use of the mysqladmin command:

1) Introduction: mysqladmin is a dedicated Command Used to execute management commands. It is also a client command that connects to the server to manage commands. mysqladmin is unique and has many sub-commands.

2) usage: mysqladmin [option] command [arg] [command [arg]...

Mysqladmin-uroot-p password 'new _ pass' sets the password for the user

3) Common commands:

Processlist Process List, which can list all processes being executed on the server

Status to view the mysql status

-- Sleep N: Display frequency

-- Count N: displays multiple statuses.

Eg: mysqladmin status -- sleep 2 mysql is displayed once every two seconds

Eg: mysqladmin status -- sleep 2 -- count 2 mysql displays twice in two seconds

Extended-status: displays status variables.

Varitables: Display Server Variables

Flush-privileges: enables mysqld to re-read the authorization table, which is equivalent to reload;

Flush-status: resets the status variables of most servers.

Flush-tables; close all opened tables

Flush-threads: reset the thread pool or thread Cache

Flush-logs: Binary and relay log scrolling

Flush-hosts: refresh the host and clear the internal information of the host.

Kill: kill a thread

Refresh: equivalent to executing flush-hosts and flush-logs simultaneously.

Shutdown: Shut down the mysql server process and stop the mysql server.

Version: the server version and current status.

Start-slave: start replication and start the slave Server replication thread (the replication thread includes two SQL threads and IO threads)

Stop-slave: Disable replication and stop the replication thread;

Iii. Storage Engine

1) Concept of the storage engine: In a sense, the storage engine is called the table type. Each table can be independently selected as a storage engine, the storage engine is not a concept between databases but between tables. Multiple tables in the same database can use different storage engines, which means that the underlying storage mechanisms are different.

2) storage engines mainly include MyISAM and InnoDB

MyISAM: (storage engine, also known as the table type) No transactions, table locks; three files for each table are as follows:

. Form: table structure definition file

. MYD: Table data definition file

. MYI: Table index definition file

InnoDB: transactions and row locks. All Tables share a tablespace file. (We recommend that you create an independent tablespace file for each table ;)

. Form: Table Structure

. Ibd: Table data and table indexes are stored in the tablespace)

3) show engines: displays all engines supported by the current server,

Show table status [like...] displays the status information of a table.

Eg: show table status like 'use'; displays the status information of the use table.

For example: show table status like 'use' \ G, view the attribute information of the use table and display it in vertical bars.

4. Knowledge about mysql data types

1. mysql Data Type:

1) numeric type

Exact Value

Int

Decimal

Approximate value

Float

Double

Real Number

2) balanced

Fixed Length: char (#), binary

Variable Length: varchar (#) and varbinary

Text, blob, text is case insensitive, blob is case sensitive)

Enum, set built-in type)

3) Date and Time

Data, time, datatime, timestamp

2. Significance of data types:

1) stored value type

2) occupied storage space

3) Fixed Length and Variable Length

4) how to compare and sort

5) can an index be created?

2. numeric type:

Int integer

If the value is 0-255 or-128-127, the value is --.

Smallint small integer)

Mediumint integer)

Int integer)

Bigint)

Decimal: fixed point value

Float: Single-precision floating point value 4 bytes

Double: double-precision floating point value 8 bytes

Bit: stores data by bit.

3. String data type:

The char character type can only store up to 255 characters in length

Varchar can only store a maximum of 65535 characters long and Case Insensitive)

Binary Fixed Length

Varbinary variable length (case sensitive)

Tinyblob micro blob 255-byte Binary large objects, which are byte strings (case sensitive)

Blob standard blob 64kb

Mediumblob intermediate blob 16 Mb

Longblob long blob 4 Gb

Tinytext micro text is case-insensitive) can only store up to 255 characters

Up to 65535 characters can be stored in text standard text

Mediumtext intermediate text can only store up to 16777215 characters

Longtext long text can only store up to 4294967235 characters

The enum type can store up to 65535

Set 1-64

4. Date and Time type:

Data 3 bytes

Time 3 bytes

Datatime 8 bytes

Timestamp 4 bytes

Year 1 bytes

5. string type modification methods:

Not null cannot be blank

Null can be null.

Default specifies the default value

Binary does not distinguish character size

Show characher sets; displays the character set of the current relational database.

Show collation; displays the sorting rules supported by the current relational database service.

Show global varables like '% char % ';

Select database (); indicates executing a function (a built-in function)

Select last_insert_id ();

Auto_increment automatically increases (integer non-empty unsigned)

Eg: create table test (id int unsigned auto_increment not null primary key, Name char (20 ))

Unsigned

Cerate table test (id int unsigned

Supplement:

1. mysql also supports name completion.

Name completion: the name of each database and the name of each field in each table must be loaded into the memory when the database is opened, this may cause a lot of latency when connecting to mysql, because it must traverse the definition of each table in each database, which is very slow.

-- Html: allows the database to be displayed in html format.

2. Keys and Indexes

1) keys are also known as constraints and can be used as indexes. Special indexes have special limitations, such as different values): they are stored in B + Tree structures.

2) indexes are internal implementation technologies of relational databases and fall into the scope of internal models. When the create index statement defines indexes, you can define indexes as unique indexes, non-unique indexes, or clustered indexes.

3) There are two types of indexes:

B + tree: the advantage of Dynamic Balancing

Hash: fast search

Almost all fields support B + tree indexes, but not all fields support hash indexes.

4) Principles for creating indexes:

1) Create an index for a large table with more than 5000 records) and retrieve less than 2% to 4% of the total number of rows.

2) A table can create any number of indexes, but not too many indexes. This will increase the overhead of system maintenance. After the index is created, it will be maintained by the system.

3) columns frequently appear in the where clause or join condition as index keywords

4) The index keyword can be a column or a composite index composed of multiple columns. If it is a composite index, the system uses the index when the query condition contains the primary keyword to speed up the query. If the query condition only contains the secondary keyword, the system does not use the index.

5) The index information is stored in the data dictionary.

This article is from the "show_only" blog, please be sure to keep this source http://10240214.blog.51cto.com/6634068/1195438