MySQL commands, and MySQL usage

To be honest this won’t be a tutorial, but a list with useful MySQL commands common used to create, mange and maintain MySQL databases, as well as many PHP and Perl API functions you can use to interface with MySQL.
Certainty they are command line commands, but some of them can be used in PHPMyAdmin also.

Let’s start with the ‘first’ MySQL command used for connecting to a database server:

Create a database command on SQL server.

List all databases on a SQL server.

Switch (start using) a database.

The next will show all the tables in a database.

Let’s print database”s field formats.

Here is how to delete a database.

This is how to delete a MySQL database table.

Show all data in a table.

Shows the columns and column information that belongs to a particular table.

Show certain selected rows with the value "anything".

Show all records containing the name "SAM" AND social security number ”768129873123”.

Show all records not containing the name " SAM " AND the social security number ”768129873123” order by the social_security_number field.

mysql> SELECT * FROM [table name] WHERE name != 

Filed Under: MySQL

Anthony Gee About the Author: Anthony G. is an IT specialist with more than 9 years of solid working experience in the Web Hosting industry. Currently works as server support administrator, involved in consultative discussions about Web Hosting and server administration. One of the first writers in the Onlinehowto.net website, now writing for Free Tutorials community - he is publishing tutorials and articles for the wide public, as well as specific technical solutions.

Comments (13)

  1. Dave K says:

    In this string:

    grant usage on *.* to SAM@localhost identified by ‘passwd’;

    I was wandering if I want to set privileges to for all interfaces instead for localhost only how is the right syntax?

  2. Tonny Potter says:

    The command will look like this one:

    grant usage on *.* to SAM@’%’ identified by ‘passwd’;

    Take in mind that this is not really clever looked from security side.

    Better use this MySQL command:

    grant usage on *.* to SAM@’192.168.0.0/24′ identified by ‘passwd’;

    Which will add access to certain IP range.

  3. Sam Raahan says:

    Hi. Very useful MySQL commands. I bookmarked it. For sure I will use in the future. But, I have a question. How to migrate a MySQL database without a dump file directly to other MySQL server?

  4. Tonny says:

    Sam, if you want to move the database from one server to another without dump you can copy the files straight from one MySQL server to another, make sure you have absolutely the same. For example on some Linux systems the MySQL files are under /var/lib/mysql and if you just copy them to another server in the same location it will start working.

    If you are asking for a command which moves/migrates MySQL database directly to another server you may find useful this command:

    mysqldump –add-drop-table -h OLDDBHOST -u UNAME -p’PASSWORD’ DBNAME | mysql -h NEWDBHOST -u UNAME -p’PASSWORD’ DBNAME

    I will add that to the list though since it is useful.

  5. Guest says:

    Tonny I saw you answered to Raahan that you can copy the MySQL binary files to another server, and they will work.

    I tried that, but it is not working. I cannot understand from your post if the MySQL versions must be equal?

  6. Tonny says:

    The both MySQL server versions must be exactly the same, even compiled with the same parameters. Otherwise there could be always an issue.

  7. PhillR says:

    Nice list of MySQL commands! But I am wondering how to import txt or csv file to MySQL from the command line?

  8. Tonny says:

    Well, you’ve missed it, but I there is field here called :”Load a CSV file into a table.”

    Also you can check this separate tutorial: http://www.freetutorialssubmit.com/Tutorials/MySQL/How-to-Import-CSV-file-into-MySql-database/1353

  9. MofP says:

    I didn’t find any Statements and clauses on the site. :(

  10. mysq boy says:

    Mysql import compressed file:
    gunzip < database.sql.sql.gz | mysql -u user -p passwd database You can also combine two or more backup files to restore at the same time, using the cat command: cat backup1.sql backup.sql | mysql -u user -p passwd

  11. missy eliot says:

    I want to find and replace in MYSQL

    • Anthony Gee Mo Su says:

      Hey Missy I got this one for find/replace in mysql:

      update [table] set [field] = replace([field],'[string_old]’,'[string_new]’);

  12. Coman says:

    Very useful combination of commands to reset fogotten mysql root (if you have Plesk (admin)) password:

    #/etc/init.d/mysql stop
    #/usr/bin/mysqld_safe –skip-grant-tables &
    #mysql

    mysql> UPDATE mysql.user SET password=PASSWORD(‘MyNewPass’) WHERE User=’root’;
    mysql>FLUSH PRIVILEGES;
    mysql>exit

    #/etc/init.d/mysql restart

Leave a Reply