MySQL Basic knowledge collation

Directory

1. Client commands

2. Server-side commands

3. Common data types

3.1. Numerical type

3.2, character type

3.3, date and time type

3.4. Boolean type

4. How to execute MySQL

5. User Management

1. Client commands

The client command does not need to end with a semicolon, if you want to get help for the client command:

Mysql> Help

MySQL common client commands are as follows:

mysql> quit or \q    #表示退出mysqlmysql > go or \g      #表示无论语句的结束符是什么都把语句送到服务器端执行 is used when the default terminator is modified and is not known, such as:mysql> select  Database () \g     #显示当前所在的数据库mysql > use or \u  db name      # Indicates setting the default database   such as:mysql> \u test     #表示使用test这个数据库mysql > ego or \g     #表示sql命令取回的数据纵向显示   For example:mysql> select * from mysql.user\g      #表示读取mysql库中user表中的所有数据, if not \g, the data readability is not high mysql> system or \!  #表示在不退出mysql客户端程序下执行shell命令, This is kind of like executing a shell command in vim mysql>  status or \s      #表示获取当前mysql的状态信息    Mysql>  delimiter or \d    #表示更换语句结束符, which means you can change the default semicolon-ending symbol to another symbol 

2. Server-side commands

The server-side command must have a statement terminator, the default is a semicolon, get the server-side command Help to use the following command:

mysql> help command

Common server-side commands:

Mysql> select version ();     #表示显示mysql数据库的版本号mysql > show  databases;     #显示mysql中有哪些数据库mysql > show databases like  ' t% ';      #显示以字母 "T" database mysql> show variables;    #显示服务器参数变量, as you can use ' like ' ' Such clauses to do Fuzzy Lookup mysql> show variables like  ' datadir% ';mysql> show status;      #显示服务器状态变量mysql > help create table   #获取创建表支持的数据类型mysql >  show character set;    #显示mysql所支持的字符集mysql > show collation;    #显示排序规则mysql > show processlist;  #显示服务器当前所有mysql线程列表mysql > show indexes  from  table name;   #显示表中的索引信息mysql > show table status\g  # View the table status in the current default database, do not add semicolons to the end of "\g" mysql> show table status [from | in]  the database \G     #查看指定数据库中的表状态

3. Common data types

To get help creating a table, you can see the supported data types and execute the following statement:

mysql> help CREATE table;

3.1, Numerical type: Numerical type is also divided into accurate numerical and approximate numerical type

The integer type in the exact numeric type is as follows:

A), tinyint: Occupies 1bype (bytes), that is, 8 bits, indicating a range of values, signed (-128,127) unsigned (0,255). Why is the range of symbols (-128,127)? Because the highest bit is used to denote the sign bit when the symbol value is represented, the highest bit is 0 for positive numbers, the highest bit is 1 for negative numbers, this highest bit does not represent a numeric size and represents only symbols. The symbol of the value of the minimum value of "10000000", the maximum value is "01111111", in the computer is a complement to the size of the binary number, here is a principle, positive complement is its own, negative complement is you take the reverse and add 1, so the minimum value "10000000" The complement is "011111111", converted to decimal 127, plus 1 is 128, because it is a negative number, so the minimum is "128", and the positive complement is its own, so the conversion into binary is 127, so the range is (-128,127).

b),smallint: Occupy 2bytes, indicate range-(2^15-1+1), 2^15-1 or 0, 65535, calculation method as above.

c), Mediumint: Occupy 3bytes, indicate range "-(2^23-1+1), 2^23-1" or "0,2^24-1"

d), int: Occupy 4bytes, indicate range "-(2^31-1+1), 2^31-1" or "0,2^32-1"

e), bigint: Occupy 8bytes, indicate range "-(2^63-1+1), 2^63-1" or "0,2^64-1"

Numeric types are typically followed by some modifiers, with the following modifiers in the exact numeric type:

Not NULL: Indicates that the value of the defined field cannot be a null value

Default ' defaults ': Represents the default value in a defined field

Unsigned: Indicates that the value of the defined field is unsigned

Auto_increment: Indicates automatic growth of values

Floating-point types in approximate numeric types:

A), Fload: single-precision value, occupy 4bytes

b), double: multi-precision value, occupy 8bytes

Numeric types are typically followed by some modifiers, and the modifiers after the floating-point character are generally:

Not NULL,default ' defaults ', unsigned,zerofill,auto_increment

3.2, character type

A), case-insensitive character types:

CHAR (n): fixed-length character type, n denotes numeric value

VARCHAR (n): variable-length character type

b), character-case-sensitive type:

Binary (N): fixed-length character type, n denotes numeric value

varbinary (n): variable-length character type

c), a character type that represents a number of characters:

Text,blob These two are representations of many character types, text is case insensitive, and blobs are case-sensitive. The two types of characters also have corresponding variants

Text:tinytext,text,mediumtext,longtext Store content size in order

Blob:tinyblob,blob,mediumblob,longblob Store content size in order

This type of actual data is not stored in the table, but is stored in the database perimeter, the table is just a pointer to the corresponding object.

Common modifiers:

Modifier: not null,default ' default value '

3.3. Date-Time type

Data: Occupied 3bytes, Range: 1000-01-01 to 9999-12-31, type can be used for a date value without the need for time part, such as: ' Yyyy-mm-dd '

Time: Occupied 3bytes, Range: -838:59:59 to 838:59:59, in the middle there are a lot of expressions in the timeframe is wasted, this type is used for a time, such as: ' Hh-mm-ss '

DateTime: Occupied 8bytes, Range: 1000-01-01 00:00:01 to 9999-12-31 23:59:59, is a combination of date and time, which means ' yyyy-mm-dd hh-mm-ss '

Year (2): Occupied 1byte, Range: xx to 99, representing 2-bit years

Year (4): Occupied 1byte, Range: 1901 to 2155, representing 4-bit years

Timestamp: Occupies 4bytes, Greenwich Mean Time, similar to datetime is the save date in the daytime, the format is ' Yyyy-mm-dd HH:MM:SS ', but the range indicated is different from DateTime, the value in "1970-01-01 00:00:01-2038-01-18 22:14:07 "between

Common modifiers: null,not null,default ' default value '

3.4. Boolean type

tinyint (1): In fact, is a kind of shaping, to show only one, for the binary, one is not "0" is "1"

3.5. Built-in type (character type)

Set: A set, such as a set (A, A, b), that can hold data as ' a ' or ' B. ' or ' ab ' or ' ba '

Enum: An enum, such as an enum (A, b), that holds the data as ' a ' or ' B '

4. How to execute MySQL

4.1. Interactive mode:

-u ' user ' =--user= ' user '

-h ' hostname ' =--host= ' host name '

-P ' password ' =--password= ' password '

-d ' database name ' =--database= ' database name ', indicating which database to use as the default database after access to MySQL

-e ' sql statement ' =--execute= ' SQL statement ', which indicates that the connection database executes the SQL statement directly and returns the value directly to the shell

Example:

[[email protected] ~]# mysql --user= ' root '  --host= ' localhost '  --password= ' 111111 '   #表示以root用户接入本地的Mysql服务, if you omit the--user= '   and--host= '   option, the default is to log in to the local MySQL as the root user, that is, the command can be simplified to "MySQL  -p "[[email protected] ~]# mysql --user= ' root '  --host= ' localhost '  -- password= ' 111111 '  -D mysql   #登陆后以mysql库作为默认数据库, omitting the connection to the database with "Mysql> use mysql" To switch the operation of the Database Mysql> select database (); +------------+| database ()  |+------------+|  mysql      |+------------+1 row in set  (0.01 sec)   [[email protected] ~]# mysql --user= ' root '  --host= ' localhost '  --password= ' 111111 '  -e  ' select user,host,password from mysql.user; '   +-----------+-----------+-------------------------------------------+| user       | host      | password                                    |+-----------+-----------+-------------------------------------------+| root       | localhost | *fd571203974ba9afe270fe62151ae967eca5e0aa | |  root      | jason     |                                              | |  root      | 127.0.0.1 |                                              | |            | localhost |                                              | |            | jason     |                                              | |  cactiuser | localhost | *fd571203974ba9afe270fe62151ae967eca5e0aa |+----------- +-----------+-------------------------------------------+# "-e" option means that SQL is executed directly after connecting to the databasestatement, retrieving the value does not stop at "mysql> ", but returns to the shell 

4.2, Batch processing mode:

[Email protected] ~]# vim mysql.sqlselect User,host,password from Mysql.user;a), [[email protected] ~]# mysql-p111111 < ; MYSQL.SQLB), mysql> source/root/mysql.sql

Both of these methods can be a batch operation of SQL, note that mysql.sql This script file permissions problem, in "mysql>" in the SQL script batch operation when the MySQL user is likely to need to SQL script file to have the readable permissions.

5. User Management

5.1. Create and delete users

To create a user:

Syntax:mysql> create user ' username ' @ ' host name ' identified by ' password ';

The user name and the host can use a wildcard character:

'% ' means any character that matches any length; (172.16.%.%)

' _ ' means matching any single character

mysql> create user ' zhaochj ' @ '% ' identified by ' 111111 '; Users #创建一个名为 ' Zhaochj '

mysql> flush Privileges; #因用户信息有改变, so do not forget to refresh the permissions table after user creation, so that user information is loaded into memory.

To delete a user:

mysql> drop user ' username ' @ ' host ';

5.2. Grant permissions to users

Grammar:

Mysql> Grant all on the Library name. Table name to ' user name ' @ ' host '; #表示把一个库中的一个表的所有权限授权给一个用户

Mysql> Grant all on the Library name. Table name to ' user name ' @ ' host ' identified by ' password '; #表示创建一个用户并授予一个数据库中一个表的all permissions, If the user is authorized for all tables on a database, the wildcard character that matches all tables is the "*" Number

5.3. Modify User Password

Two methods:

A, mysql> set password for ' user name ' @ ' hostname ' =password (' password ');

B,]# mysqladmin-u user name password ' new password '-p original password

This article is from the "knowledge needs summary and records" blog, please be sure to keep this source http://zhaochj.blog.51cto.com/368705/1629681

MySQL Basic knowledge collation