MySQL (ii)-Common client commands, data types, logs

There are 2 types of commands that can be executed within the MySQL client:

1. Client commands:

Do not need to add a statement terminator, the display is only mysql this client command changed how to use, no operation involving the server, directly using Help, can be manipulated.

mysql> helpdelimiter  (\d)  Set statement delimiter.                 used to set the Terminator, delimiter, and then replace the default semicolon. ego        (\g)  Send command to mysql server,  display result vertically.                 used to display the table structure vertically exit       (\q)  Exit mysql. Same  as quit.                is the same as quit , which indicates exiting the MySQL client go         (\g)  send command to mysql  server.                If you replace the semicolon with delimiter, but you forget, you can use this command instead of the Terminator help       (\h)  display this  help.source     (\.)  execute an sql script file. takes a file name as an  argument.                Loads a script that contains SQL statements. For example, to import Bin_log to a text file for recovery                  But be aware that after entering the client, your user identity is the system's MySQL user, not the root                 so MySQL user must have permission to access that file status     (\s)  get status  information from the server.                 shows some status information of MySQL server, can be used to do simple monitoring indicator system     (\!)  Execute a system shell command.                 execute shell commands without exiting the MySQL client, such as "\! ls /tmp" use         (\u)  use another database. takes database name as argument.                 Select Set default database charset    (\c)   Switch to another charset. might be needed for processing binlog  with multi-byte charsets.                 Set Character Sets

2. Server command:

The amount of action required to connect to the server side, at which point the ";" must be added. Terminator

mysql> help COMMAND;

The MySQL client executes the command in 2 ways:

1, interactive mode;

MySQL [-hhostname-uusername-ppassword [-D db_name]] [-e ' optionation ']

Passwords after-p are typically entered interactively using the

-d Specifies the database to be used by default after entering the database, or not, but it needs to be specified with-E.

# mysql-uroot-proot-d MYSQL-E ' select * from user\g ' # mysql-uroot-proot-e ' select * from Mysql.user\g '

2, Batch processing mode:

That is, using SQL scripts, the following commands are equivalent:

# MySQL OPTIONS </path/to/scripts.sql

Mysql> Source/path/to/scripts.sql

# cat/tmp/hveen.sql CREATE DATABASE wpdb; GRANT all on wpdb.* to [e-mail protected] identified by ' yy '; FLUSH privileges;# Mysql-uroot-proot </tmp/hveen.sql or mysql> Source/tmp/hveen.sql

MySQL reads the configuration file in the following order:

/ETC/MY.CNF =/ETC/MYSQL/MY.CNF = $MYSQL _base/my.cnf Installation path = ~/.MY.CNF User home directory

If there is a different repeating option set, the last read will prevail. In this case, you can set the user name and password in the user's home directory each time you log in using the password:

[Client]user=roothost=localhostpassword=root

Character Set Concepts:

    In the computer, all the data in theStorageand operations are usedBinary number(because computers use high and low levels to represent 1 and 0, for example, 52 letters such as a, B, C, D, including uppercase), and 0, 1, and other commonly used symbols (such as *, #, @, and so on) are also used when stored on a computerBinary numberto indicate which binary numbers are used to indicate which symbol, and of course everyone can agree on their own set (this is calledCoding), and if people want to communicate with each other without causing confusion, then everyone must use the same coding rules, so the United States standardization organizations have introducedASCII encoding, which specifies which binary numbers are used to denote these common symbols.

    American StandardThe Information Interchange code is made up ofAmerican National Standards Institute(American National Standards Institute, ANSI), standard singlebytescharacterCodingscenarios that are used to basetextthe data. It began in the late 50 and was finalized in 1967. It was originally an American national standard for use by different computers to communicate with each other as a common Latincharacter encodingStandard, it has beenInternational Organization(International Organization for Standardization, ISO) is established as an international standard, known as ISO 646. Applies to all Latin letter characters.

The smallest storage unit in a computer is byte, while 1byte=8bit, while a bit bit has a change of 2 (0 and 1). So a byte has a 2^8=256 (0 to 255) change, which is not enough to represent all Chinese characters.

But what if you use 2 bytes to represent a Chinese character? 2^16=65536 (0-65535), Baidu a bit of Chinese characters and characters commonly used number will find commonly used is about 7000, the most commonly used is about 4000, so 2 bytes is enough to represent our commonly used Chinese characters.

For example, if we define 01000000 01000000 for "up", then the computer encounters such a binary code will show us as "up". This is called a character set that defines how the "character" that humans can identify and the "binary bit" correspondence that the computer can recognize.

Character sets have many standards over time, such as GB2312 (GB, Simplified Chinese), Gb18030,gbk,big5 (Traditional)

With the advancement of time, each country, each program may have their own different character set standards, and then caused by the country, the process is difficult to communicate, so the ISO International standard organization will be a lot of countries "character" unified together to encode, this is Unicode, also known as unified Code, universal code. It can also be UTF. Then came the utf-8/utf-16/utf-32.

Collation: The same character set has this different collation, such as Chinese characters can be sorted by the first character, you can also sort by the order of strokes.

mysql> SHOW CHARACTER SET; Displays all the character sets supported by the database.       Mysql> SHOW COLLATION; Displays the collation of all characters.       Mysql> SHOW SESSION VARIABLES like '%char% '; Displays the default character set for the current session.

Another common scenario is that our MySQL servers are on Amazon VMS in the United States, and their database default character set is Latiin1 (Latin character Set), and our local clients are using GB2312 by default. Then they will communicate with each other after the use of the same code.

Data type concepts:

mysql> help CREATE TABLE; Find the Data_type keyword: This shows the characters supported by the MySQL database

 character type: CHAR (n):         stores up to n characters. However, if only 1 characters are stored, the storage space of n characters is also consumed.          is a waste of space, but to a certain extent, performance is much better than varchar (N).         char (n) and varchar (n) are case insensitive. VARCHAR (n):         a variable-length char (n), storing up to n characters. If only 1 characters are stored, then only 2 character space is used          because there is a 1-character space to store the Terminator. BINARY (N):         a case-sensitive character type, but stores up to 255 characters. VARBINARY (N):         A variable-length case-sensitive character type that stores up to 65,535 characters. text:         is larger than the above several character types and is case insensitive. blob:         the type relative to text, which is case-sensitive. Character-type modifier:    null,not null,default  ' zi '. 

Numerical:         are also classified into exact numerical and approximate numerical types           The exact integer type:tinyint:         occupies 1B, so it is 2^8=128, which is 0-256. So also called micro-integer, commonly used to denote age          in unsigned cases,  represented from the   0     to  255         in signed case, from  -128  to  127 (default) smallint:         occupies 2B of space, so it is 2^16=65536. Also called a small integer type.          in unsigned cases, representing  0       to   65535         in a signed case, from  -32768  to  32767 (default) Mediumint:          occupies 3B of space, so it's 2^24int:       .   occupies 4B, so the 2^32bigint:         occupies 8B, so it is 2^64 exact integer modifier:     NULL,NOT NULL,DEFAULT  ' Shu '     UNSIGNED  (denotes unsigned, default signed)     auto_increment (auto-increment) approximate floating-point:float:          single precision,4bdouble:         double precision, 8BREAL: Approximate floating-point modifier:    null,not null,default  ' Fu '

DateTime type: Date: storage Date: Storage time datetime: storage date + Time year (2) Annual (4): 2-bit or 4-bit years timestamp: timestamp, from 1970 January 1 0:0 to present time.        Facilitates time localization. Because from that time, no matter in which time zone of January 2, 2015 0:0, elapsed time is the same. Modifier for datetime type: Null,not null,default "

Boolean: 0: Fake 1: True

This article is from the "Go Right on" blog, so be sure to keep this source http://caduke.blog.51cto.com/3365689/1598431

MySQL (ii)-Common client commands, data types, logs