MySQL Common command collation

1. Connect MySQL database:

#mysql-H 1.1.1.1-uroot-p Zabbix

2. mysql Password change

mysqladmin command format: Mysqladmin-u user Name----old password Password new password (password function)

2) forgot root password

# vi/etc/my.cnf 

Restart Mysqld

3. mysql build new user and authorize

Grant Select,insert,update,delete on * * to [E- Mail protected]"%" identified by "ABC";

4, MySQL additions and deletions to check

< add;:

Build Library: Create a database and assign users:

1. CREATE database name;

2. GRANT select,insert,update,delete,create,drop,alter on database name. * To database name @localhost identified by ' password ';

3. SET PASSWORD for ' database name ' @ ' localhost ' = old_password (' password ');

Build Table:mysql> CREATE TABLE MyClass (
> ID int (4) NOT null primary key auto_increment,
> Name char () NOT NULL,
> Sex int (4) NOT null default ' 0 ',
> Degree double (16,2));

< delete;:

if present, delete the library:mysql> drop database if exists drop_database;

Delete table: Mysql>drop table < name >;

Delete table record:mysql> delete from MyClass where id=1;

Delete index:mysql> ALTER TABLE name DROP INDEX name;

Delete field:mysql> ALTER TABLE name drop field name;

< change;:
The table inserts the record:mysql> insert into table values (1, ' Tom ', ' 2, ' (2, ' Joan ', and 59);

Modify table data: mysql> Update table Set name= ' Mary ' where id=1;

plus index: mysql> ALTER TABLE name add index index name (field name 1[, field Name 2 ...]);

Plus PRIMARY KEY index: mysql> ALTER TABLE name add primary key (field name);

plus unique restriction index: mysql> ALTER TABLE name add unique index name (field name);

add new field: ALTER TABLE name add field name int NOT NULL default 0 after ' RegionID ' (can specify field position)

Modify field name and field type: mysql> ALTER TABLE table_name change old_field_name new_field_name field_type;

Rename table: ALTER TABLE t1 rename t2;/ Rename table name to new table name;

< search;:

Check table structure: DESC table name; mysql> Show columns from MyClass;

Check table data: Select < Field 1, Field 2, ...> from < table name > where < expression >;

5, database backup mysqldump

Backup table structure: mysqldump-u user_name-p-d–add-drop-table database_name > Outfile_name.sql
-D No data –add-drop-table add a drop table before each CREATE statement

This article is from the "Linux_ ant" blog, please make sure to keep this source http://onlyoulinux.blog.51cto.com/7941460/1529925