A collection of common commands from MySQL

Here is a very useful MySQL command that we use frequently. Below you can see the # indicates executing the command from the Unix command line and see mysql > Indicates that you are currently logged in to the MySQL server and that you are executing the mysql command on the mysql client.
Log in to MySQL and specify hostname with -ES10en if connecting to a remote database.

Log in to MySQL and specify hostname with -ES15en if you are connecting to a remote database.

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

Create 1 database.

mysql> create database [databasename];

List all databases.

mysql> show databases;

Switch to 1 database.

mysql> use [db name];

Displays all tables for 1 database.

mysql> show tables;

View the field format of the data table.

mysql> describe [table name];

Delete 1 database.

mysql> drop database [database name];

Delete 1 data table.

mysql> drop table [table name];

Displays all data for 1 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" to filter to display selected rows.

mysql> create database [databasename];

0

Displays all records containing name as "Bob" and phone number as "3444444".

mysql> SELECT * FROM [table name] WHERE name = "Bob" AND phone_number = '3444444';

Displays all records that do not contain name as "Bob" and phone number as "3444444" sorted by phone_number field.

mysql> SELECT * FROM [table name] WHERE name != "Bob" AND phone_number = '3444444' order by phone_number;

Displays all name records beginning with the letter "bob" and phone number "3444444".

mysql> create database [databasename];

3

Displays records 1 through 5 of name beginning with the letter "bob" and phone number "3444444".

mysql> create database [databasename];

4

Use regular expressions to find records. Use regular expression base 2 to enforce case sensitivity. This command looks for any records beginning with a.

mysql> create database [databasename];

5

Returns only one different record.

mysql> create database [databasename];

6

Displays selected records in ascending or descending order.

mysql> create database [databasename];

7

Returns the number of rows.

mysql> create database [databasename];

8

Statistics the sum of the values of the specified column.

mysql> create database [databasename];

9

Join table.

mysql> select lookup.illustrationid, lookup.personid,person.birthday from lookup left join person on lookup.personid=person.personid=statement to join birthday in person table with primary illustration id;

Create a new user. Log in with root. Switch to the mysql database, create users, and 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, and 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.

mysql> show databases;

6 Update the root password.

mysql> show databases;

7 Allows user "bob" to connect to the server from localhost with the password "passwd". Log in with root and switch to mysql database. Set permissions, update permissions.

mysql> show databases;

8

mysql> show databases;

9 If you do not want to enter the password manually, use the --password parameter

 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. Login as root, switch to mysql database, grant permissions, update permissions.

mysql> use [db name];

1

mysql> use [db name];

2 or

mysql> use [db name];

3 Updates data for an existing table.

mysql> use [db name];

4 Delete rows in the table where [field name] = 'whatever'.

mysql> use [db name];

5 Update permissions/privileges on the database.

mysql> use [db name];

6 Delete the column.

mysql> use [db name];

7 New column to db.

mysql> use [db name];

8 Change the column name.

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

Add only one column.

mysql> show tables;

0 Set the column value to be larger.

mysql> show tables;

1 Delete only 1 column.

mysql> show tables;

2 Import 1 CSV file into the table.

mysql> show tables;

3 Export all databases to the sql file.

mysql> show tables;

4 Export 1 database.

mysql> show tables;

5 Export 1 table from 1 database.

mysql> show tables;

6 Restore the database (tables) from the sql file.

mysql> show tables;

7 Create data table example 1.

mysql> show tables;

8 Create data table example 2.

mysql> show tables;

9 Save the query results to a file

mysql> describe [table name];

0 Look for redundant duplicates in the table, which are determined by a field (peopleId)

mysql> describe [table name];

1 No duplicate records in the query table (excluding duplicate records)

mysql> describe [table name];

2 Delete duplicate records in the table, which are determined by a field (title)

mysql> describe [table name];

3 Randomly selected records

mysql> describe [table name];

4 Query the current encoding of the database

mysql> describe [table name];

5 Modify the table field type

mysql> describe [table name];

6 Add a new field to the table

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

Remove 1 field from the table

 mysql> ALTER TABLE table_name DROP field_name; 

Rename table

mysql> describe [table name];

9 Index a field

mysql> drop database [database name];

0 Add the index of the primary keyword

mysql> drop database [database name];

1 Add an index with only 1 constraint

mysql> drop database [database name];

2 Delete an index

mysql> drop database [database name];

3 Remote access to mysql Settings

mysql> drop database [database name];

4