Servage Magazine

Information about YOUR hosting company – where we give you a clear picture of what we think and do!

MySQL Commands – Part 1

Monday, June 16th, 2014 by Servage

wThis will be an intro to the most important MySQL commands for your everyday projects.

I’ll cover most of these as we proceed, but first, you need to remember a couple of points about MySQL commands:

• SQL commands and keywords are case-insensitive. CREATE, create, and CrEaTe all mean the same thing. However, for the sake of clarity, the recommended style is to use uppercase.

• Table names are case-insensitive on Windows, but case-sensitive on Linux and OS X. So, for portability purposes, you should always choose a case and stick to it.

The recommended style is to use lowercase or mixed upper- and lowercase for table names.

Creating a database

Get the ball rolling by issuing the following command to create a new database called publications:

CREATE DATABASE publications;

A successful command will return a message that doesn’t mean much yet—“Query OK, 1 row affected (0.00 sec)”—but will make sense soon. Now that you’ve created the database, you will want to work with it, so issue:

USE publications;

You should now see the message “Database changed.” You’re now set to proceed with the following examples.

Creating users

Now that you’ve seen how easy it is to use MySQL, and created your first database, it’s time to look at how you create users—you probably won’t want to grant your PHP scripts root access to MySQL, as it could cause a real headache should you get hacked.

To create a user, issue the GRANT command, which takes the following form (don’t type this in—it’s not an actual working command):

GRANT PRIVILEGES ON database.object TO ‘username’@’hostname’ IDENTIFIED BY ‘password';

This should be pretty straightforward, with the possible exception of the data base.object part. What this refers to is the database itself and the objects it contains, such as tables.

So, let’s create a user who can access just the new publications database and all its objects, by entering the following (replacing the username jim and the password mypasswd with ones of your choosing):

GRANT ALL ON publications.* TO ‘jim’@’localhost’ IDENTIFIED BY ‘mypasswd';

What this does is allow the user jim@localhost full access to the publications database using the password mypasswd. You can test whether this step has worked by entering QUIT to exit and then rerunning MySQL the way you did before, but instead of entering -u root -p, type -u jim -p, or whatever the username is that you created.

All you have to do now is enter your password when prompted, and you will be logged in. By the way, if you prefer, you can place your password immediately following the -p (without any spaces) to avoid having to enter it when prompted. But this is considered poor practice, because if other people are logged in to your system, there may be ways for them to look at the command you entered and find out your password.

You can grant only privileges that you already have, and you must also have the privilege to issue GRANT commands. There are a whole range of privileges you can choose to grant if you are not granting all privileges.

For further details, please visit the following site, which also covers the REVOKE command, which can remove privileges once granted: http://tinyurl.com/mysqlgrant.

 

Sources for further reading

MySQL Commands - Part 1, 3.7 out of 5 based on 3 ratings
You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

No comments yet (leave a comment)

You are welcome to initiate a conversation about this blog entry.

Leave a comment

You must be logged in to post a comment.