Capturing important knowledge points in mysql (1)

I. Classification of mysql commands can be divided into client commands and server commands: 1) Client commands \ c: early termination of the final sentence \ g: no matter what the statement Terminator is, directly send this statement to the server

I. Classification of mysql commands can be divided into two types: client commands and server commands: 1) Client commands \ c: Early termination Conclusion \ g: send the statement to the server regardless of the statement Terminator.

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: Get the index help information (for further help information, you can copy the link after the URL displayed after the help command index command is executed and paste it in the browser to get 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: tablespace (which stores table data and table indexes)

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

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 (large characters, text are 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

The value of tinyint (micro integer) is 0-255 when it is signed, and-128-127 when it is unsigned.

Smallint (small integer)

Mediumint (medium integer)

Int (integer)

Bigint (big integer)

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 (Case Insensitive)

Binary Fixed Length

Varbinary variable length (case sensitive)

Tinyblob micro blob 255 bytes (large binary objects, which are case sensitive byte strings)

Blob standard blob 64kb

Mediumblob intermediate blob 16 Mb

Longblob long blob 4 Gb

Tinytext micro text (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.