First time with MySQL !

Everybody will have a day when they decide to try something which they knew for a long time. That day is very unique, they will try their best, seek help from many, refer to many things and finally it ends up in merry when they succeed in running it. I have been learning SQL for the past 50 days but I could not have a hands on experience till today. I decided to somehow run SQL today, and the reason I am on FC4 made me to go in for MySQL. My Window’ian friends have always been complaining that I am becoming more into Open source softwares / Linux there days, and to add fire into that burning fuel I wanted to successfully run MySQL :DI began the endeavor by first installing MySQL server using the Add/Remove programs option. After successfully installing MySQL, I searched the Main Menu for a MySQL option. Not finding them there ( I know where to go now 😉 ) I opened up the terminal and typed ‘mysql’. To make me smile, MySQL started up and showed me ‘ mysql> ‘ prompt. As I assumed to have successfully got into MySQL, i tried my first command straight away !

mysql > CREATE TABLE PDATA (NAME CHAR(10), PHONENUM CHAR(10));

Like hitting me in the head, it said ” Error: No database found”. I resolved to use the help to find something

mysql> help

The help gave me many commands but there wasn’t one how to create a database. Luckily I had my LinuxGuru on line, so IM’d him to find what to do. He said I have to create a database using,

$ mysqladmin create data_base

It created the corresponding database “data_base” and gave me the result ” Query OK, 0 rows selected (0.01 sec) “. I again assumed the process has ended and I can go on with creating tables, issuing the CREATE TABLE statement. To my surprise again got an error message but this time as ” Permission denied to access database as @localhost”.

This time he suggested me to refer the MySQL site for help. Then I created the required permission for me to login as parth@localhost and access the MySQL server. Then I again tried to login as user, but still wasn’t able to as it needed a slightly different command. Finally, after again referring to the MySQL Site I was able to finally start MySQL as user and create tables and insert data into it.

The entire procedure for a first-time-run of MySQL is as follows,

1. Loging in as ‘root’ or obtaining root permission with ‘su’, issue the following command at shell,

[root@localhost]$ mysql –user=root mysql

mysql > GRANT ALL PRIVILEGES ON *.* TO ‘user_name’@’localhost’
IDENTIFIED BY ‘passwd’ WITH GRANT OPTION;

This assigns ‘all’ privileges to the user ‘user_name@localhost’ who can be identified and can access mysql using the password ‘passwd’.

2. We have to create a database for the user to use, by issuing the following command as root ( or su ),

[root@localhost]$ mysqladmin create user_database

which will give the reply as ” Query OK, 0 rows affected (0,01 sec) ” on successful creation of the database.

3. Login as user by issuing the following command in the shell,

[user_name@localhost]$ mysql –user=user_name –password=passwd

The welcome text will indicate your successful login: “Welcome to MySQL Monitor.” and you will get the ‘mysql’ prompt.

4. Now, we have to change the database into ours by issuing the following command,

mysql > USE user_dbase

which will be confirmed with “Database changed”.

5. Now, you can issue the MySQL statements at the ‘mysql’ prompt and your ‘user-dbase’ will be the used database.

To Create Table : mysql > CREATE TABLE PDATA (NAME CHAR(10), PHONENUM CHAR(10));To add data into the table : mysql > INSERT INTO PDATA (NAME , PHONENUM) VALUES ( Name , 9880098800 );

To view the table : mysql > SELECT * FROM PDATA;

Advertisements

10 thoughts on “First time with MySQL !

  1. First time here.. This article would indeed be helpful to lot of newbies..

    Am a mysql programmer too and am working full time in open source technolgies..

  2. Few years ago I had abandoned MySQL due to:
    – lack of referential integrity checks
    – lack of transactions support
    – lack of subqueries

    Are those things present in the current MySQL engine?

  3. hello there…. Good intro quite alright but it doesnt solve my problem of jst how do u start the whole process of programmin with mysql.. i ve a project to embark on, and i t involves the use of web blogs and a bit of chatting… i ‘d lyk to know jst how do i get mysql then use it with php. thanks

  4. realllllllly i ‘d be greatful if anyone can give my first crawlin lessons…… lemme put this way, when i do get mysql installed, what do i do next. i mean how do i do wat u explained, is it an ms dos prompt???

  5. Think I spotted typo!!

    Where it says:

    [root@localhost]$ mysql –user=root mysql

    it should be

    [root@localhost]$ mysql –user=root mysql

    or

    [root@localhost]$ mysql –u root mysql

    or

    [root@localhost]$ mysql –uroot mysql

  6. OK, this has probably to do with the form or something. A double dash gets somewhere replaced by a single one.

    So, also my comment got garbled.

    If you use the “user=” format, you have to put two (!!) dashes in front, not one.

  7. That is not a single dash, though it looks like single dash for you. When you type two dashes next to one another, then it changes into a *long* dash. That’s what happened in my case too.

    Anyway, thanks for the comment 🙂

  8. it’s interesting that i found this topic under your blog because today i have started learning MySQL but i was not fortunate enough to learn more because i had a few problems with my pc the short cut for mysql is not working and i cannot login from ms dos any suggetions i use this command to login

    mysql -u username -p password -h hostname
    when i enter this it prompt for a password when i enter a password it says wrong db name any suggestions

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s