Summary of common mysql commands and summary of common mysql commands

Summary of common mysql commands and 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














Mysql Common commands

Www.w3school.com.cn/ SQL /index.asp this is about SQL, very detailed
Www.w3school.com.cn/php/php_mysql_intro.asp. This is about MySQL and is also very detailed.
Www.w3school.com.cn/php/php_ref_mysql.asp MySQL function manual, very comprehensive

Mysql Common commands

Mysql common command line Daquan machine first move, mysql service start and stop net stop mysql net start mysql second move, login mysql syntax is as follows: mysql-u user name-p User Password Type COMMAND mysql-uroot-p, press enter and prompt you to enter the password, enter 12345, then press enter to enter mysql, mysql prompt is: mysql> Note: if it is connected to another machine, you need to add a parameter-h Machine IP. 3. Add the new user format: grant permission on database. * to username @ login host identified by "password". For example, add a user user1 password password1 so that it can log on to the local machine, all databases are permitted to query, insert, modify, and delete databases. First, use the root user to connect to mysql, and then type the following command: grant select, insert, update, delete on *. * to user1 @ localhost Identified by "password1"; if you want this user to log on to mysql on any machine, change localhost to "% ". If you do not want user1 to have a password, you can run another command to remove the password. Grant select, insert, update, delete on mydb. * to user1 @ localhost identified by ""; Step 4: log on to mysql by operating the database and run the following commands at the mysql prompt. Each Command ends with a semicolon. 1. display the Database List. Show databases; by default, there are two databases: mysql and test. Mysql inventory contains the mysql system and user permission information. We change the password and add users, in fact, this database is actually operated. 2. display data tables in the database: use mysql; show tables; 3. display the data table structure: describe table name; 4. create and delete databases: create database name; drop database name; 5. create a table: use Database Name; create table Name (Field List); drop table name; 6. Clear table records: delete from table name; 7. display table records: select * from table name; Step 5: export and import data 1. export data: mysqldump -- opt test> mysql. test exports the database test to mysql. test file, which is a text file such as mysqldump-u root-p123456 -- databases dbname> mysql. dbname is to export the database dbname to the mysql file. Dbname. 2. import data: mysqlimport-u root-p123456 <mysql. dbname. No need to explain it. 3. Import text data to the database: field data of text data is separated by the tab key. Use test; load data local infile "file name" into table Name; 1: use the SHOW statement to find the database on the server: mysql> show databases; 2. CREATE a database mysqldata mysql> create database mysqldata; 3: select the DATABASE mys you created ...... remaining full text>