Mysql's Common commands collection _mysql

Here are the MySQL commands that we often use and are very useful. Below you see # means execute command on UNIX command line, see Mysql> is currently logged on to MySQL server, execute MySQL command on MySQL client.
Login to MySQL, if you are connected to a remote database, you need to specify hostname with-H.

Login to MySQL, if you are connected to a remote database, you need to specify hostname with-H.

# [MySQL dir]/bin/mysql-h hostname-u root-p

Create a database.

mysql> CREATE DATABASE [databasename];

Lists all databases.

mysql> show databases;

Switch to a database.

mysql> use [DB name];

Displays all tables for a database.

Mysql> Show tables;

View the field format of the datasheet.

mysql> describe [table name];

Deletes a database.

mysql> drop Database [database name];

Deletes a data table.

mysql> drop table [table name];

Displays all the data for a data table.

Mysql> SELECT * FROM [table name];

Returns the column information for the specified data table.

Mysql> Show columns from [table name];

Use the value "whatever" filter to display selected rows.

Mysql> SELECT * FROM [table name] WHERE [field name] = "whatever";

Displays all records that contain name "Bob" and phone number "3444444".

Mysql> SELECT * FROM [table name] WHERE name = "Bob" and Phone_number = ' 3444444 ';

Displays all records that do not contain the name "Bob" and phone number "3444444", sorted with the Phone_number field.

Mysql> SELECT * FROM [table name] WHERE name!= "Bob" and Phone_number = ' 3444444 ' ORDER by Phone_number;

Displays all the records with name "Bob" and phone number "3444444".

Mysql> SELECT * FROM [table name] WHERE name is like "bob%" and Phone_number = ' 3444444 ';

Displays name 1th through 5th with the letter "Bob" and phone number "3444444".

Mysql> SELECT * FROM [table name] WHERE name like "bob%" and Phone_number = ' 3444444 ' limit 1, 5;

Use regular expressions to find records. Use regular expression binary to enforce case sensitivity. This command looks for any records that start with a.

Mysql> select * FROM [table name] WHERE rec rlike "^a";

Returns the only different records.

mysql> SELECT DISTINCT [column name] from [table name];

Displays the selected record in ascending or descending order.

Mysql> SELECT [col1],[col2] from [table name] ORDER BY [col2] DESC;

Returns the number of rows.

Mysql> SELECT COUNT (*) from [table name];

Counts the sum of the specified column values.

Mysql> SELECT SUM (*) from [table name];

Join table.

Mysql> Select Lookup.illustrationid, lookup.personid,person.birthday from lookup left join who is on lookup.personid= Person.personid=statement to join birthday in person table with primary illustration ID;

Create a new user. Log in as root. Switch to MySQL database, create user, refresh permissions.

# mysql-u root-p
mysql> use MySQL;
Mysql> INSERT into User (Host,user,password) VALUES ('% ', ' username ', Password (' Password '));
mysql> flush Privileges;

Change the user password from the Unix command line.

# [MySQL dir]/bin/mysqladmin-u username-h hostname.blah.org-p password ' new-password '

Change the user password from the MySQL command line. Log in as root, set password, update permissions.

#/etc/init.d/mysql Stop
# mysqld_safe--skip-grant-tables &
# mysql-u Root

mysql> use MySQL;
mysql> Update user Set Password=password ("Newrootpassword") where user= ' root ';
mysql> flush Privileges;
Mysql> quit

#/etc/init.d/mysql Stop
#/etc/init.d/mysql start

When the root password is empty, set the root password.

# mysqladmin-u Root Password NewPassword

Update the root password.

# mysqladmin-u Root-p OldPassword NewPassword

Allows the user "Bob" to connect to the server with the password "passwd" from localhost. Log in as root and switch MySQL database. Set permissions and update permissions.

# mysql-u Root-p

mysql> use MySQL;
Mysql> grant usage on *.* to bob@localhost identified by ' passwd ';
mysql> flush Privileges;

Use the--password parameter if you do not want to manually enter your password

 Mysqldump-h database_ip-u Username--password=123456--opt databasename > Backup-file.sql
 mysqldump-h Database _ip-d-u Username--password=123456 databasename >database_structure.sql

Set permissions for database db. Log in as root, switch to MySQL database, grant permissions, update permissions.

# mysql-u Root-p

mysql> use MySQL;
Mysql> INSERT into db (Host,db,user,select_priv,insert_priv,update_priv,delete_priv,create_priv,drop_priv) VALUES ('% ', ' databasename ', ' username ', ' y ', ' y ', ' y ', ' y ', ' y ', ' N ');
mysql> flush Privileges;

Or

Mysql> grant all privileges in databasename.* to Username@localhost;
mysql> flush Privileges;

Updates data for a table that already exists.

mysql> UPDATE [table name] SET select_priv = ' y ', insert_priv = ' y ', update_priv = ' y ' WHERE [field name] = ' user ';

Deletes the row in the table [field name] = ' whatever '.

Mysql> DELETE from [table name] where [field name] = ' whatever ';

The permissions/privileges to update the database.

mysql> flush Privileges;

Deletes a column.

mysql> ALTER TABLE [table name] Drop column [column name];

New columns to DB.

mysql> ALTER TABLE [table name] Add column [new column name] varchar (20);

Change the name of the column.

mysql> ALTER TABLE [table name] change [old column name] [new column name] varchar (50);

Add a unique column.

mysql> ALTER TABLE [table name] Add unique ([column name]);

Set the column value to a larger point.

mysql> ALTER TABLE [table name] modify [column name] VARCHAR (3);

Deletes a unique column.

mysql> ALTER TABLE [table name] DROP INDEX [COLMN name];

Import a CSV file to the table.

mysql> LOAD DATA INFILE '/tmp/filename.csv ' replace into TABLE [table name] FIELDS terminated by ', ' LINES terminated B Y ' n ' (field1,field2,field3);

Export all databases to the SQL file.

# [MySQL dir]/bin/mysqldump-u root-ppassword--opt >/tmp/alldatabases.sql

Export a database.

# [MySQL dir]/bin/mysqldump-u username-ppassword--databases databasename >/tmp/databasename.sql

Exports a table from a database.

# [MySQL dir]/bin/mysqldump-c-u username-ppassword databasename tablename >/tmp/databasename.tablename.sql

Restore the database (datasheet) from the SQL file.

# [MySQL dir]/bin/mysql-u username-ppassword databasename </tmp/databasename.sql

Create data table Example 1.

mysql> CREATE table [table name] (FirstName VARCHAR), MiddleInitial VARCHAR (3), LastName VARCHAR (a), suffix VARCHAR (3), OfficeId VARCHAR, userid VARCHAR, username VARCHAR (8), email VARCHAR (MB), phone VARCHAR (), Groups VARCHAR ( ), Datestamp Date,timestamp time,pgpemail VARCHAR (255));

Create data table Example 2.

mysql> CREATE TABLE [table name] (PersonID int () NOT NULL auto_increment primary key,firstname varchar (km), MiddleName varchar (m), Lastnamevarchar () Default ' Bato ');

Save query results to a file

 Select title from book into outfile '/tmp/outfile.txt ';

Find redundant records in a table, and duplicate records are judged by a field (Peopleid)

 SELECT * from people where Peopleid to (select Peopleid from People GROUP by 
 Peopleid have count (Peopleid) > 1);

Do not duplicate records in a query table (excluding duplicate records)

 SELECT * from phome_ecms_wma where title is (select DISTINCT title from phome_ecms_wma);

Deletes a duplicate record in a table, which is judged by a field (title)

 Select *,count (distinct title) into outfile '/tmp/table.bak ' to phome_ecms_wma Group by title;
 Delete from phome_ecms_wma;
 LOAD DATA INFILE '/tmp/table.bak ' REPLACE into table phome_ecms_wma character set UTF8;

Randomly select records

 SELECT *from URL Order by RAND () LIMIT 5;

Query Database Current encoding

 Mysql> Show variables like "character_set%";

Modify a table field type

 Mysql> ALTER TABLE table_name change last_action last_action datetime not NULL default ' 0000-00-00 00:00:00 ';

Add a new field to a table

 mysql> ALTER TABLE host ADD Ks_mac VARCHAR (100);

Remove a field from a table

 mysql> ALTER TABLE table_name DROP field_name; 

Renaming tables

 Mysql>alter table T1 rename T2;

To index a field

 Mysql> ALTER TABLE tablename ADD index index name (field name 1[, field Name 2 ...]);
 Mysql> ALTER TABLE tablename Add index Emp_name (name);

Index of the Primary keyword

 Mysql> ALTER TABLE TableName ADD PRIMARY key (ID);

Index with unique restriction criteria

 Mysql> ALTER TABLE tablename add unique emp_name2 (cardnumber);

Delete an index

 Mysql>alter table tablename DROP index emp_name;

Remote access MySQL settings

 mysql> GRANT all privileges on database_test.* to root@192.168.1.9 identified by ' 123
 456 '; 
mysql> FLUSH privileges;