Mysql's Common commands collection _mysql

Source: Internet
Author: User
Tags mysql client mysql commands create database mysql command line

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; 
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.