0
Posted October 12, 2009 by Spyros in DBMS / Databases
 
 

Learn mySQL Programming – Users, Databases, Tables Manipulation

drawer
drawer

The internet has always been about the information. Millions of people literally write billions of words of text everyday online and several others read them. This whole model is solely based on databases and database programming, which is actually the practice of storing information and retrieving it in an easy manner.

If you do not really know about databases, you can even think about your own driver’s license. In order for your country to know that you possess such a diploma, they have included your personal identity information in their own database system so that it is easy for them to enlist all those people who possess a driver’s license and recognize those who do not.

From a programmer’s perspective, database programming is really an area of study that each programmer should really take up. In order to create and manage one or more databases, you need to launch a database management system.In this tutorial, i will be talking about what is the standard in database management systems online and that is mySQL.

You may think now that you don’t really need to know much about mySQL since there are frontend tools like phpmyadmin that do the work for you easily. However, i would heavily suggest that you do not use such tools because of two reasons. First of all, you just install a whole frontend to do some work that can easily be done via the shell and secondly and more important, you do not learn the actual Structured Query Language, which is something that you really need to know as a programmer. Moreover, SQL is so easy that there is no point in not taking the time to learn its basics.

Create and Delete MySQL User Accounts and Change Root User Password

The first time you install mySQL, you can login as root using no password at all. Therefore, there is really a need that you change that immediately, before even adding more users to it. Changing the root password is very easy :


mysqladmin -u root password YOURPASSWORD

Another option would be to directly tamper with mySQL stored passwords. To do so, just login as root (with nothing as password) and type these commands :


use mysql;

update user set password=PASSWORD("YOURPASSWORD") where User='root';

flush privileges;

Creating a new user for every new database that you create is actually a good habit, but most times you just need one user. DO NOT use the root user to create your databases. It would be much better to create a new mysql user :


CREATE USER 'username'@'localhost' IDENTIFIED BY 'yourpassword';

GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' WITH GRANT OPTION;

This will create a new user under the name ‘username’ and grant them with full privileges on every database. Well, actually you may not want to give superuser access to the new user. You can just include some rights only and exclude others. In order to learn more about this process, take a look at the official mysql documents on how to add users to your mysql databases.

To delete a user from your database system, just execute the command :


DROP USER 'username';

Creating and Dropping Databases

Remember that for every database you create, there has to be a user connected to it. Therefore, now that you have created your new user in the previous section, you can now create a new database for that user. Creating and dropping a new database is as easy as :


CREATE DATABASE newdb;

DROP DATABASE newdb;

Create and Drop Tables For Your Databases

The next and logical step after you create your database, is to create some tables for it. A table is actually a way to represent data in mySQL. While the topic about tables in databases can go really far, for the sake of simplicity, i will just talk about the usual tables that just have an element and values connected to it. Therefore, a simple mySQL table could be something like :

Name    Job

=========

John      Student

Maria    Computer Programmer

You see that this is actually a very simple case of a database just storing two values for every person and that is their name and job. Let’s try to create this very simple table. In order to do so, we first need to understand what are the various data types used in mySQL. While there are quite many of these, i will only focus on the ones i have used quite a lot in the past and are used in the vast majority of cases :


varchar(200)

int(10)

datetime or date

text

There are about 15 more data types but i really think that these are more than sufficient for your casual mySQL programming needs for starters. Let me elaborate a bit on what these actually are. Varchar(200) is a data type that is used quite a lot. This describes a string that is at most 200 characters long. In the same manner, int(10) describes an integer that has at most 10 digits. Datetime is a data type that holds the time and data in the predefined mySQL format. The difference with that and Date is that the latter only stores the date and not the time. Finally, Text is a data type that is used so that there is not a predefined length of string that we have to specify. Therefore, if you are looking to store something like a user’s comment, you may want to store that as Text data type, since you would not want to restrict your users to 200, 250 or whatever characters length.

Now that we know that important data types, we can easily describe our data. Remember that we have name and a job description. Well, we would just need to specify two different varchar fields. The fist one can be something like varchar(50) and the second one varchar(150). Thus, a name is at most 50 characters and a job description is at most 150 characters. In an actual database we would be also including another integer type that would specify the id of each person, but for the sake of simplicity, this will be discussed in a later tutorial about primary keys and stuff like that.

Creating our table now is easy and the syntax of the command is also very clear :


CREATE TABLE people (varchar(50) name, varchar(150) job);

This creates a table named people with the aforementioned data types. Notice that mySQL names are case insensitive and so JOB is the same as job. Therefore, just adapt your own coding style. Deleting this table is also easy :


DROP TABLE people;

These are the basic stuff that you need to know about databases and you hopefully gained some insight on mySQL and learned by the examples provided here. Of course, there are many more things to say about mySQL. I wouldn’t want to say everything on just one tutorial so i will stop for now and create a new one describing more indepth topics like insertions and updates  on a database.

Any questions ? Shoot me with a comment maybe :)


Spyros