MySQL for Absolute Beginners

  You are currently not logged in. You can view the forums, but cannot post messages. Log In | Register

25-Aug-11 01:00
This is a forum topic for discussing the article "MySQL for Absolute Beginners":

http://www.elated.com/articles/mysql-for-absolute-beginners/

Never used MySQL before? Start here! This tutorial walks you through the very basics of MySQL and SQL databases. Learn how to install MySQL, how to issue commands, and how to create and use databases.
25-Aug-11 07:57
Nice tutorial. Thanks.

--
http://www.mauconline.net
26-Aug-11 19:57
Nice article. What type of exercises would you recommend for learning MySQL and SQL?
29-Aug-11 07:49
Thanks for the positive feedback guys

@Raven: Good question! In my PHP book ( http://www.amazon.com/Beginning-PHP-5-3-Matt-Doyle/dp/0470413964 ) I give a fairly simple membership site example, with a Members table and an AccessLog table. I always find it helps to build a practical example - for example, a music database with artists, albums and tracks, or a shopping list manager with ingredients and recipes. Anything that involves real-world things, and uses a couple of tables that you join together, will give you a good grounding in SQL queries.

--
Matt Doyle, Elated
29-Aug-11 18:49
Thanks Matt, I really like the examples you gave me. For the longest I've been coding HTML and CSS, and tampering with PHP. But I've yet to actually dig in and build an application or component, this is just the inspiration I needed!
01-Sep-11 03:37
@Raven: You're welcome, and good luck!

--
Matt Doyle, Elated
01-Sep-11 21:18
ok I set up the database and tried the set table command: source books.sql and I get an error: failed to open books.sql error 2
Books.sql being a notepad file where I copied the code in the tutorial and saved as a file in xampp/mysql/book.txt changing the .txt extension to .sql. What am I not doing right?

--
Jack
02-Sep-11 03:30
@jacknet: Make sure you're running the mysql command from the folder that contains your books.sql file. Also make sure it's called books.sql not book.sql.

--
Matt Doyle, Elated
02-Sep-11 06:56
@matt: Yes it's in the folder/path c:\xampp\mysql\books.sql. I'm sorry but I misspelled it in my email but it's correct in the folder. I type: mysql> source /books.sql And still get the error. Can't figure it out.

--
Jack
02-Sep-11 16:10
This tutorial doesn't work and when you try to get help everyone ignores you.

--
Jack
02-Sep-11 19:08
@jacknet: I was about to help you some more, but then I saw your latest comment and it kinda put me off.

But I'll just add that you don't want that slash (/) before books.sql.

--
Matt Doyle, Elated
03-Sep-11 12:24
Matt - I apologize for my remark yesterday. I'm just frustrated because I followed your tutorial to the letter (more than once) and just couldn't get it to work, and still can't. I saw you answering other folks and felt ignored even after I sent an email. But again I apologize and know in life that you don't start a relationship with remarks like that and it was totally inappropriate for me to say what I did and expect further help. I really want to learn this. I have a great opportunity to join a marketing firm if I can prove I can do this stuff. I programmed in RPG several years ago and got out of computers, as a business, for awhile, then got back in about a year ago as a hardware/network tech for the local Boys and Girls Club. Now I work at the local library doing the same but miss programming and want to get back into it. So if you can forgive me and start over I'd like to learn your book. Thanks for your consideration.
Jack

--
Jack
07-Sep-11 03:44
@jacknet: No problem - just bear in mind that I'm often busy doing other things and don't always have the time to check the forums every few hours. I do my best to offer my help for free on these forums and mostly answer every question, but it's difficult when you have other commitments.

I usually answer all posts in the order they were posted, so if you see me answering other posts first then it's usually because they were posted before yours.

Now, with your MySQL problem, I can see that you've put your books.sql file in the wrong directory:

c:\xampp\mysql\books.sql

It should be in the same directory as your mysql binary:

c:\xampp\mysql\bin\books.sql

Alternatively, simply provide the full path to your books.sql file:

source c:/path/to/books.sql

Does that fix your problem?

--
Matt Doyle, Elated
07-Sep-11 10:31
Many thanks Matt, great tutorial. I have been looking into getting started with some MySQL and PHP, but didn't know where to start. Managed to get Beginning PHP 5.3 book, it's a great start to learning the basics and enjoying it so far.
08-Sep-11 11:14
@Matt: Well....I know you are going to think I'm crazy or my 'puter is. I tried every path possible from the Mysql prompt. I put the books.sql file in bin like you suggested which means I should be able to just type mysql> source books.sql and get the table to set up but all I get still is: Failed to open file 'books.sql' error: 2. I try every combination of commands you suggested in your tutorial and your forum message to me above. It just won't work. I have the file in the c:\xampp\mysql folder and the c:\xampp\mysql\bin folder. I don't know if you can suggest anything else. I know it's probably frustrating for you as it is for me. Thanks, Jack

--
Jack
08-Sep-11 11:29
@matt: Conversely when I just run the commands: mysql> USE bookstore; That works. When I run: mysql> DROP TABLE IF EXISTS books; I got: Query OK, 0 rows affected, 1 warning <0.00 sec>. So I thought I'd setup the table with the CREATE TABLE books; command and when I entered the long string you have in the file book.sql it doesn't work so I'm probably leaveing one little thing out or it's different at the command line than in the file. I'm also setting this all up in another box running ubuntu so maybe I'll have better luck there. Comments?

--
Jack
08-Sep-11 13:47
Well Matt - You're gonna love this. I'm over on my linux box now and get the same results. Created the source file books.sql and put it in my c:\Home\username\Documents\books.sql folder because I can't find the bin folder in Mysql. So I ran the command mysql> source c:\home\jacknet\Documents\books.sql; and same results as before. So I must be doing something wrong on both computers. Just wish I could figure our what that something is. Peace...

--
Jack
08-Sep-11 19:23
@jacknet: You can't use a path like c:\home\jacknet\Documents\books.sql on a Linux box. Unix has no concept of a drive letter. Also the Unix path separator is '/' not '\'.

The error you're getting (assuming it's the same error in each case) is that MySQL can't find the file, so you're using the wrong path in each case.

--
Matt Doyle, Elated
08-Sep-11 19:56
@grimmdanny: Thanks for the kind words. I'm glad you're enjoying the book!

--
Matt Doyle, Elated
10-Sep-11 12:37
@Matt: I agree the program/command "source" can't find the file books.sql. So let me go through the whole setup: I loaded xampp in the c drive. C:\xampp. In this folder is the folder "mysql". I put a copy of the file books.sql in that folder. In the folder mysql is a folder: bin. I also copied the file books.sql into that folder. So whether I run the command as follows: Mysql> source c:\xampp\mysql\books.sql; or Mysql> source c:\xampp\mysql\bin books.sql; or Msql> source books.sql; forward slash, backward slash it doesn't matter. I get the same error everytime "ERROR:
Failed to open file 'books.sql', error: 2
mysql>" This conversation is talking about on my windows 7 machine only. Do you see anything wrong in this. Thanks, Jack

--
Jack
10-Sep-11 12:45
@Matt: Just to clarify more I created the file books.sql by copy/paste the code from your tutorial. My books.sql file looks like this:
USE bookstore;

DROP TABLE IF EXISTS books;
CREATE TABLE books
(
id int unsigned NOT NULL auto_increment,
title varchar(255) NOT NULL,
author varchar(255) NOT NULL,
price decimal(10,2) NOT NULL,
PRIMARY KEY (id)
);
Ok; so I just got the idea to run the commands in the file.
This is what I got:

Current database: bookstore

+--------------------+
| Database |
+--------------------+
| information_schema |
| bookstore |
| cdcol |
| mysql |
| performance_schema |
| phpmyadmin |
| test |
| webauth |
+--------------------+
8 rows in set (0.21 sec)

mysql> USE bookstore;
Database changed
mysql> DROP TABLE IF EXISTS books;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE books
-> (
-> id int unsigned NOT NULL auto_increment,
-> title varchar(255) NOT NULL,
-> author varchar(255) NOT NULL,
-> price decimal(10,2) NOT NULL,
-> PRIMARY KEY (id)
-> );
Query OK, 0 rows affected (0.33 sec)

mysql>
Does this mean I have finally set up the database books?

--
Jack
10-Sep-11 12:50
Yes it does, to wit:
mysql> show tables;
+---------------------+
| Tables_in_bookstore |
+---------------------+
| books |
+---------------------+
1 row in set (0.30 sec)

mysql> explain books;
+--------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| title | varchar(255) | NO | | NULL | |
| author | varchar(255) | NO | | NULL | |
| price | decimal(10,2) | NO | | NULL | |
+--------+------------------+------+-----+---------+----------------+
4 rows in set (0.06 sec)
but this does not explain the mystery of the source command finding the file books.sql.

--
Jack
10-Sep-11 15:16
Well I certainly have used up this thread pretty good. Just to let you know I completed the tutorial on both boxes and as long as I leave that file books.sql alone I'm ok, but I sure wish I could solve that mystery because I'm sure I'll need it in the future. On to build a CMS in an afternoon. Please if you get a chance though Matt let me know your final thoughts on the books.sql mystery. Thanks, Jack

--
Jack
12-Sep-11 06:40
@jacknet: "Does this mean I have finally set up the database books?"

Yes. You can always just copy & paste SQL into the MySQL monitor.

No idea why you're having so many problems with your paths. I don't usually use Windows, but on my Win7 VM I can get it working no problem. You must use '/' not '\' in the MySQL monitor.

Feel free to start a new topic if you're still having path problems:

http://www.elated.com/forums/authoring-and-programming/topic/new/

--
Matt Doyle, Elated
05-Nov-11 11:42
Matt-

Thanks for posting this tutorial, I am trying to learn the basics here and am looking forward to completing your walk-through. One problem though- when I get to the part after you install XAMPP and try to start mysql (I'm on a Mac), I go to terminal and enter "mysql -u root", it just returns "-bash: mysql: command not found".

Any ideas? I've tried googling this problem and cannot find anyone else having the exact same issue. Lots of people having problems starting mysql but none with the same issue.

Hoping that I'm screwing something simple up here...

Any help is GREATLY appreciated!!!
07-Nov-11 00:33
@srafferty: I'm guessing you're running Lion? Apple removed MySQL by default in Lion (it now uses PostgreSQL). Therefore the default Apple mysql command line program will no longer be in your path, so you'll need to explicitly use the XAMPP version instead.

Try:

/Applications/XAMPP/xamppfiles/bin/mysql -u root

If that works, you can add /Applications/XAMPP/xamppfiles/bin/ to your path, then you can just type mysql next time!

Cheers,
Matt

--
Matt Doyle, Elated
05-Jan-12 09:50
this is a very good tutorial for beginners. can we have one for php as well?
11-Jan-12 16:54
@joy39: You might find these helpful:

http://www.elated.com/articles/cat/php/

--
Matt Doyle, Elated
14-Apr-12 17:11
Thanks for clear and easy to follow tutorial, it helps.
04-May-12 05:58
@belamoor: That's good to hear - thanks

--
Matt Doyle, Elated
27-May-12 19:07
Thank you so much once again! I first found your blog when I wanted to make my own Blogger template a few months ago and you absolutely taught me how to do that. Now I am learning databases with you. Your blog is fantastic! I am your devoted reader now.

I am seeing some people had problems with path to books.sql I had too. I was lazy to look for answers so I clicked on Xampp Control Panel PHP MyAdmin and somewhere there I found bookstore database and insert button. This worked and I was able to continue the tutorial.
12-Aug-12 08:48
Hi Buddies,

Well jst wanna say now we have a handy reference of SQL in android market named SQL guru for free .......go through tweaks in the tips and walkthroughs given.....its pretty cool.

https://play.google.com/store/apps/details?id=com.dopdroid.mysqlguru

--
DopeDroid
06-May-13 15:23
Hi Matt, really appreciate this article - it is the simplest and most straightforward definition and explanation of MySQL that I have been able to find on the web. Keep up the good work!
12-Aug-13 21:30
Thank you for the excellent tutorial! You've got a great writing style.
13-Aug-13 01:24
Thanks for all the positive feedback - much appreciated.

--
Matt Doyle, Elated
20-Oct-13 20:46
Thank you for your effort.. works for me ..
can I ask your help in loading a data file into the table "books"
this works:
INSERT INTO books ( title, author, price ) VALUES ( "Roses " , "Jane Peters " , 10.99 ) ;

mysql> LOAD DATA LOCAL INFILE /root/data INTO books;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '/root/data INTO books' at line 1
mysql>

I running RHEL 6.4
this is my data file
]# more data
"The Birds" "Alan H. Green" 12.99
"Stones" "Brad L. Lewis" 7.99
"The Turning Tides" "Scott Griss" 20.99
"Back Street" "Brian L. Scott" 15.99
"Roses" "Lynn George" 11.99

can you share some thought on why unable to input..
Thanks, Stephen

--
stephen
04-Feb-14 15:46
Great tutorial and at long last I thought I was actually going to create my first database but cannot get past the first part of setting up the table!

I'm not a rebellious sort but decided to create a database named USERS rather than BOOKSTORE but I don't think that's the issue!

So I look at where the db is saved and can only find the following path;

xampp/mysql/data/users/db.opt

surely I should have found

xampp/mysql/data/users.sql

and as you can imagine when I put in the path as you advise into the MySQL Monitor mysql prompt then I just get nothing only the prompt winking at me!

I guess I've done something wrong further back so am hopeful you can point me in the right direction so that I can still accomplish the setting up of my first database.

Cheers
04-Feb-14 16:56
What do you get if you type

show databases;

Into a mysql command window?

--
Chris.
So long, and thanks for all the fish.
http://webmaster-talk.eu/
04-Feb-14 17:12
Hi Chris

Thanks for helping...this is what I get

+--------------------+
| Database |
+--------------------+
| information_schema |
| cdcol
| mysql |
| performance_schema |
| phpmyadmin
| test |
| users |
| webauth |
+--------------------+
8 rows in set (0.37 sec)

Cheers
Fozz52
05-Feb-14 19:38
Ok so your user database has been created

The db.opt file is the "characteristics" of the database and contains information about the character set, structure, collation etc. MySQL does not work like Access or Excel where you have a database 'file' per se, the folder is the data location and the data is then held in the 'table' datafiles.
When you start creating tables the data files will be created in the dbname folder.

tablename.MYD are the datafiles
tablename.MYI are the table index(es)
tablename.frm are the format files for the tables.


These will not exist until you create tables and add data to them. You only need to be concerned with the file structure unless you have to do a manual recovery, hopefully that will not happen,

A .sql file is a text file that contains a set of command lines to rebuild the database, table structures and data, that is used for exporting, importing or taking backups of the database.

Does that help?

[Edited by chrishirst on 05-Feb-14 19:39]

--
Chris.
So long, and thanks for all the fish.
http://webmaster-talk.eu/
06-Feb-14 10:48
Hi Chris

Does it help... well Yes and No I still can't create the table!

I'm still getting the same reaction when I put the following into the mysql monitor so how do I create my table in the users database if the users.sql text file was saved in xammp/mysql/

I've put into the command line...

/xammp/mysql/users.sql

but it does nothing and just goes to the next line showing the prompt ->

what am I doing wrong?
06-Feb-14 15:47
To tell mysql to run the command you have to terminate the line with a semicolon ';' this is because you can type in several lines of a query seperated by the <enter> key but the mysql server will not parse the input until it gets ;<enter> as an input line.


Or if you prefer a Windows GUI as MySQL CLI takes a bit of getting used to download and install HeidiSQL ( http://www.heidisql.com/ )

You can see the CLI command assemble in the "command" pane and learn how it works.


to load a .sql command script at the mysql comman prompt

http://dev.mysql.com/doc/refman/5.0/en/mysql-batch-commands.html

--
Chris.
So long, and thanks for all the fish.
http://webmaster-talk.eu/
06-Feb-14 16:52
Hi Chris

Ok sorry about that should have noticed.... however.... after putting the following into the command line

/xammp/mysql/users.sql;

I get this error message...

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MYSQL server version for the right syntax to use near '/xammp/mysql/users.sql' at line 1

I'm really confused now!
07-Feb-14 09:12
.sql files are not like batch (.bat) or command (.cmd) files in Windows you have to tell the mysql daemon what to do with it.

the command line to import a text file is



mysql db < file.ext



Which is equivalent to



use db_name
source file.ext;



The mysql command is to start the mysql CLI daemon so it can accept instructions

The USE command says "Open 'db_name' and set it as the current working database.

The SOURCE command says Open the named file and execute the commands in the sequence they appear.

[Edited by chrishirst on 07-Feb-14 09:12]

--
Chris.
So long, and thanks for all the fish.
http://webmaster-talk.eu/
07-Feb-14 11:04
Hi Chris
Not sure what’s going on here but perhaps this was the problem before and hadn't realised.
I did a show databases; command and got the following;
+--------------------+
| Database |
+--------------------+
| information_schema |
| test |
+--------------------+
2 rows in set (0.01 sec)

Where did the other databases go including the users.sql !
So decided to restart my laptop and create a new database. This time I followed the instructions and created a database called bookstore.sql but then got this error message !
=====================================================================
ERROR 1044 (42000) Access denied for user ‘’@’localhost’ to database ‘bookstore’
=====================================================================
Puzzled as to what’s happening but I’m sure you will be able to help...
07-Feb-14 16:33
You are logged as a user without sufficient privileges to see them or change them.

--
Chris.
So long, and thanks for all the fish.
http://webmaster-talk.eu/
07-Feb-14 17:01
Hi Chris

Without wishing to be flippant you're stating the obvious... I realise what the error message is saying but as I'm the only user of the computer but nothing has changed since I started working through this tutorial a few days ago and created the original user.sql database.

So have you any idea as to why it's suddenly telling me I don't have permission and if you do could you set out the steps I should take ?
08-Feb-14 07:56
For mysql in a "WAMP" environment it does not matter how many users are on the machine, or who is logged in to Windows. It is who is logged in at the mysql command window that matters.

MySQL has an entirely seperate user list to the Windows user list, and uses a UNIX privilege/permissions hierarchy NOT the comparatively weak Windows permissions hierarchy.

When you installed MySQL it will have asked you for a root user password, it is THAT user / password combination that you need to login to the command window with, otherwise you are simply a "user" with no privileges beyond USE to open a database and running SELECT queries against that DB.

Once you are logged in to a 'root' command window (called a 'shell' in UNIX parlance) you will then have full privileges to see, read, change and 'drop' (delete) databases and tables, create users and set privileges to specific databases or actions.

--
Chris.
So long, and thanks for all the fish.
http://webmaster-talk.eu/
09-Mar-14 05:31
First of all a big thank you for this very helpful guide which understands what it means to begin. It gives clear conceptual ideas without swamping (this beginner) with too much detail.

Now to my first question.
Having successfully installed XAMPP.1.8.3-3 on a mac I have it all up and running perfectly.

I used terminal to set passwords for xampp, mySQL/phpAmin, mySQL root and FTP "daemon".

Now I tried mysql -u root and I get "command not found"

Is this because I'm not using terminal as a root user?
If so how do I log in as a root user.

Thanks - Paul

--
Pn
09-Mar-14 05:45
Ah now I have it! With apologies I didn't read enough of the previous conversations and I realise my question has been answered with:-
/Applications/XAMPP/xamppfiles/bin/mysql
to accommodate the current Mac OS

Thanks and let me save you a reply.

--
Pn
10-Mar-14 08:45
Working through commands I have success until I try to create a data base. Then I get (see below) Working on an iMac with mysql on an external drive but all appearing to work normally.


mysql> select now();



mysql> show databases;



mysql> create database bookstore;


ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'bookstore'
mysql>

--
Pn
10-Mar-14 14:33
start the mysql CLI with a user that has 'create' pivileges

e.g;

mysql --user=root --password=password


--
Chris.
So long, and thanks for all the fish.
http://webmaster-talk.eu/
10-Mar-14 15:15
Brilliant thank you. Paul

--
Pn
07-Aug-14 13:14
That was real beginner stuff. It was great. Now the problem is, how to go further

Jnani
08-Aug-14 05:23
http://dev.mysql.com/doc/

--
Chris.
So long, and thanks for all the fish.
http://webmaster-talk.eu/
08-Sep-14 19:45
Hi.. Im at the very beginning. I installed XAMPP and have the mySQL Database Server running. I'm using a MAC and when I go into the Terminal and enter "mysql -u root" and hit ENTER, I get mysql: command not found

Is there something I'm missing?
09-Sep-14 07:12
Quite probably the path to your mysql executable is not in the PATH environment variable.

use the full path to the executable IN the command line or add the location to $PATH

--
Chris.
So long, and thanks for all the fish.
http://webmaster-talk.eu/
04-Mar-16 23:21
Hi, i liked your guide. But faced an immediate problem. I get this error message "Error 1045 <28000> 'Access denied for user 'root'@'localhost' (using password = NO) " when i type mysql -u root in c:\xampp\mysql\bin . Please help
05-Mar-16 04:31
Use:

mysql -u root -p [root password]


and provide the correct password for the mysql 'root' user


You can ALSO find the answer to this very question SIX posts back and is dated 10-Mar-14.

Also see, https://docs.oracle.com/cd/E17952_01/refman-5.6-en/resetting-permissions.html

--
Chris.
So long, and thanks for all the fish.
http://webmaster-talk.eu/
20-Feb-17 03:42
There is also a cool MySQL GUI dbForge Studio for MySQL for database management, administration and development. It's available to download at https://www.devart.com/dbforge/mysql/studio/download.html
20-Feb-17 05:38
Hi,
Its really good to learn and also got to learn some new things,Thanks

--
Aakash Patel
ASP.Net Developer
http://www.ifourtechnolab.com

 
New posts
Old posts

Follow Elated