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.
Switch to a database.
Displays all tables for a database.
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> 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> 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;
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> ALTER TABLE [table name] Drop column [column name];
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);
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);
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
# [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;
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;
Mysql>alter table T1 rename T2;
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);
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;