Summary of common mysql commands
# Modifying Field Types
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