Summary of common mysql commands _ MySQL

Modify field type altertable'table _ name' modifycolumn1_archar (50); add field altertable'table _ name' add1_archar (50); add PRIMARYKEY (primary key index) altertable'table _ name'a # Modify the field type
Alter table 'Table _ name' modify column ip varchar (50 );

# Adding fields
Alter table 'Table _ name' add ip varchar (50 );

# Add a primary key (primary key index)
Alter table 'Table _ name' add primary key ('IP ');

# Add a UNIQUE (UNIQUE index)
Alter table 'Table _ name' add unique ('IP ');

# Add an INDEX (common INDEX)
Alter table 'Table _ name' add index ip ('IP ');

# Add a joint index
Alter table 'Table _ name' add index userip ('IP', 'name ');

# Add FULLTEXT (full-text index)
Alter table 'Table _ name' add fulltext ('column ');

# Deleting an index
Alter table 'Table _ name' drop index ip;

Grant all privileges on *. * TO 'root' @ '192. 168.3.150 'IDENTIFIED
'Mypassword' with grant option;

# Add a user name "test". The Connection host for password 123 is localhost, which can also be written as ip address, 192.168.0.% (192.168.0. any), or % (no host restriction)
Insert into mysql. user (Host, User, Password, ssl_cipher, x509_issuer, x509_subject) values ("localhost", "test", password ("123 "),'','', '');
# Refresh the permission table after adding the permission list
Flush privileges;

# Authorization

# Authorize all Database permissions to users whose username is test and whose password is 123
Grant all privileges on *. * to test @ localhost identified by '20140901 ';

# Grant all Database permissions to users whose username is test and password is 123, and test has the permissions authorized to other users
Grant all privileges on *. * to test @ localhost identified by '000000' with grant option;

# Authorize the select permission for the testdb database to a user whose username is test and whose password is 123
Grant select on testdb. * to test @ localhost identified by '20140901 ';

# Refresh the permission table after authorization is complete
Flush privileges;

# Field type

The numeric column types include integer and floating point types.

TINYINT: a positive integer with a very small size of 1 byte, with the symbol:-128 ~ 127, without symbols: 0 ~ 255
SMALLINT: 2-byte small integer, with the symbol:-32768 ~ 32767, without symbols: 0 ~ 65535
MEDIUMINT: a 3-byte integer of medium size, with the symbol-8388608 ~ 8388607, without symbols: 0 ~ 16777215
INT: 4-byte standard integer, with the symbol:-2147483648 ~ 2147483647, without symbols: 0 ~ 4294967295
BIGINT: 8-byte big integer, with the symbol:-9223372036854775808 ~ 9233372036854775807, without symbols: 0 ~ 18446744073709551615
FLOAT: 4-byte single-precision floating point number. minimum non-zero value: +-1.175494351E-38; maximum non-zero value: +-3.402823466E + 38
DOUBLE: 8-byte DOUBLE-precision floating point number, minimum non-zero value: +-2.225074255072014e-308, maximum non-zero value: +-1.7976931348623157E + 308
DECIMAL (M, D): a floating point number expressed in string form in M + 2 bytes. its Value range is variable, determined by the values of M and D.

Character type
One character, one byte, one Chinese character = 2 characters, two bytes

CHAR [(M)] M bytes fixed length
VARCHAR [(M)] M-byte variable length, storage size is the actual length of the input data bytes
TINYBLOD, TINYTEXT 2 ^ 8-1 bytes L + 1 byte
BLOB, TEXT 2 ^ 16-1 bytes L + 2
MEDIUMBLOB, MEDIUMTEXT 2 ^ 24-1 bytes L + 3
LONGBLOB, LONGTEXT 2 ^ 32-1 bytes L + 4
ENUM ('value1', 'value2',...) 65535 members 1 or 2 bytes
SET ('value1 ', 'value2',...) 64 members, 1, 3, 4, or 8 bytes