Basic MySQL Setup

Suppose you’ve downloaded and installed wampserver on your local Windows machine and want to start manipulating MySQL databases. What now?

First of all you need to know where on your hard drive all the MySQL stuff is. I installed wampserver on my C drive in a new folder called “wamp” and the path to the mysql.exe command is:


C:\wamp\bin\mysql\mysql5.1.36\bin


We’re going to need this path when we go to the DOS command so copy it and then click Start > Run > cmd. Type in the following:


"C:\wamp\bin\mysql\mysql5.1.36\bin\mysql" -u root


Note that you’re just calling the mysql command in the path you copied, logging on as user “root”. Let’s play around with some basic commands.


show databases;


This command displays all the databases you currently have. Let’s create a new database to play with:


create database testdb;


Don’t forget the semicolons at the end of each command. Now we have a database called testdb that has no tables in it. Because testdb is the database we now want to work with, issue the following:


use testdb;


We don’t want to grant root access to everyone who logs on, so let’s create a user. You’d be forgiven for thinking that we’re going to use some create user command, but we’re not:


grant privileges on testdb.* to 'username@hostname' identified by 'thepassword';


As this is your local system, you can use localhost for hostname. This command gives username@localhost full access to the testdb database using the password ‘thepassword’. If you type in the ‘quit;’ command, you can then log on to mysql using the following:


"C:\wamp\bin\mysql\mysql5.1.36\bin\mysql" -u username -p


You will then need to type in your password. But we still have no tables in the testdb database. Let’s create one:


create table users (
fname varchar(32),
lname varchar(32),
uname varchar(32),
password varchar(32)
);


To display the fields (or columns in the table) and their properties:


describe users;