• (2 Pages)
  • +
  • 1
  • 2

Beginners guide to Creating simple MySQL tables Intro to: creating db, tables, columns, keys...

#1 Footsie  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 23
  • View blog
  • Posts: 370
  • Joined: 20-September 07

Post icon  Posted 02 February 2008 - 01:06 PM

*
POPULAR

Beginner's guide to creating tables in MySql:

Before I start my Tutorial I just want to say this: I am by no means a brilliant programmer. I am however a beginner myself and have worked through a lot of problems that I have faced when attempting to produce working code. This tutorial is to help those starting out with MySql who have no experience with it whatsoever.

The first thing that you will need is to download MySql. I am working with MySql version 5.0. You can download it from their site.

After setting it up you must click Start > All programs > MySql > MySql 5.0 and then MySql command line client.
This will open a window similar to the old DOS command line. You will have to enter your password and hit enter.
Attached Image

All commands in MySql must end with the delimiter “;”. This tells the database (DB) to execute your command. MySql syntax doesn’t distinguish between upper and lowercase letters so “SHOW” is the same as “show”. At the mysql> command prompt type SHOW databases; and hit enter.
This shows all the DB already created, and any new ones will also appear here. In order to use a specific DB simply type USE “your database name here”;
It will say database changed.

Alright so let’s create a new database so that we can test some sql expressions. Type:
CREATE DATABASE myfirstdb; Now type “show databases” again and you should see your new DB in the list. You can delete this DB by typing DROP DATABASE myfirstdb; but be careful of using this command, especially if you have info in the DB!

A DB is pretty useless with no tables in it, so lets add a couple now. Make sure you type USE myfirstdb; first.
A table is created using the “CREATE TABLE” statement. MySql will only allow you to create a table if you enter at least 1 or more columns. So here is an example of creating a suppliers table.

Note: You might want to type this into Notepad and then copy and paste into the command line before you execute. This makes it easier to edit, so that you don’t have to re-type everything again.
CREATE TABLE suppliers (
	SupplierID SMALLINT UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
	SupplierName VARCHAR(40) NOT NULL, 
	Phone VARCHAR(14) NOT NULL,
	Email VARCHAR(60) NULL,
PRIMARY KEY (SupplierID)
	);


Don’t forget the delimiter “;”!

Right, so what does all of this mean?
The line CREATE TABLE suppliers creates a table named “suppliers” – you could name it whatever you want.
The case of the table name doesn’t matter either – it will always display as lowercase anyway.

The contents of the bracket stipulate the names of each of the columns that will be in your table, as well as the type of info that it will contain, how many characters and whether that value can be null (empty) or not.
Eg: SupplierID SMALLINT UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT The name of the column is “SupplierID”. The type of the column is “SMALLINT”. It is “UNISIGNED” which means it starts at 0. “ZEROFILL” means that if the number is 1 the spaces before the 1 will be filled with zeros, eg: 0001 The column is “NOT NULL” meaning it must be given a value – it cannot be left empty. “AUTO_INCREMENT” tells MySql to automatically assign a value to it and increment it according to the last value in the DB. This is good for an ID because it ensures uniqueness.
The last line PRIMARY KEY (SupplierID) indicates that the identifying key for this table is “SupplierID”.

If you want to delete a table DROP TABLE suppliers will do it. (But don't do it now :))
To list our tables in our new DB type:SHOW TABLES;
Attached Image

If you would like to see all the columns in your table type DESCRIBE suppliers;

The Supplier table will be our “Parent Table” in our first DB. The “Child Table” will be the “Products” table – you can’t have a product without a supplier. Our child table “Products” will rely on a “Supplier” parent being present to supply said product. This is where we use a Foreign Key relationship.
  CREATE TABLE Products (
	ProductID SMALLINT UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
	SupplierID SMALLINT UNSIGNED ZEROFILL NOT NULL,
	ProductName VARchAR(40) NOT NULL, 
	UnitPrice DECIMAL(10,2) NULL,
PRIMARY KEY (ProductID)
			);


Our Primary Key on our “Products” table is “ProductID”. This identifies the unique product. Notice that there is also a “SupplierID” in our “Products” table. This will be our Foreign Key back to the suppliers table identifying which supplier supplies this product.
So let’s add our Foreign Key to our products table:
ALTER TABLE Products
ADD FOREIGN KEY (SupplierID) REFERENCES Suppliers (SupplierID);

Our Foreign Key is on SupplierID in the Products table and relates to (or references) the SuppliersID column in the Suppliers table. Note that the type of the Foreign Key column must match the type of the Primary Key column in the other table that it relates to. The ALTER TABLE command enables you to change your tables once you’ve created them. If you want to see how a table was created use SHOW CREATE TABLE products;

So now we are ready to insert some values into our tables.
INSERT INTO suppliers (SupplierName, Phone, Email) VALUE (‘ABCSupplier’,’1122334455’,’abc@dicforum.com’);


This inserts the values specified in between the ‘’ into each column specified. We ignore the SupplierID because that is set to auto increment. To query the info out of the DB use a Select statement. Eg:SELECT * FROM suppliers; “*” stands for all info in the table. (Check out no2pencils tutorial on the use of SELECT :^: )

Let’s insert a product that “ABCSupplier” supplies. To do that we need to first have a supplier ID. From our select statement we can see that “ABCSuppliers” SupplierID = 1. So our Product insert will look like this:
INSERT INTO products (SupplierID, ProductName, UnitPrice) VALUE (‘1’,’Chocolates’,’10.50’);


Again we let MySql auto generate the ProductID (primary key) but we insert ABCSuppliers's ID in for the SupplierID. SELECT * FROM products; will show us our entry.
Attached Image

Ok let's test the Foreign Key now.
Let’s try enter another product insert this time entering a SupplierID of 2.
INSERT INTO products (SupplierID, ProductName, UnitPrice) VALUE (‘2’,’Beads’,’5’);


Attached Image
The insert fails. This is good! It fails because of the Foreign Key constraint SupplierID in the table Products. This constraint ensures that there must be a supplier (with corresponding SupplierID) for the product to be inserted. Since we have only inserted one supplier so far there cannot be a product with a SupplierID of ‘2’ until we insert a second supplier.

That’s it for this intro into MySql.
  • Creating a database
  • Creating tables & columns
  • Using Primary Keys
  • Using Foreign Keys.

I hope it helps some of you get started with your databases.
Thanks for reading!
;)

Is This A Good Question/Topic? 11
  • +

Replies To: Beginners guide to Creating simple MySQL tables

#2 sam.adams61  Icon User is offline

  • D.I.C Regular

Reputation: 12
  • View blog
  • Posts: 283
  • Joined: 14-July 08

Posted 09 September 2008 - 04:26 PM

Many thanks there Footsie, this was very helpful indeed! Cheers m'dear :^:
Was This Post Helpful? 0
  • +
  • -

#3 RodgerB  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 66
  • View blog
  • Posts: 2,284
  • Joined: 21-September 07

Posted 13 September 2008 - 12:46 AM

I agree. I've been using this guide as a reference every time I've had trouble with SQL. Thanks for the awesome tutorial! :)
Was This Post Helpful? 0
  • +
  • -

#4 Footsie  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 23
  • View blog
  • Posts: 370
  • Joined: 20-September 07

Posted 09 November 2008 - 11:54 AM

Thanks guys.
Glad I could help someone. :)
Was This Post Helpful? 0
  • +
  • -

#5 tanah.melayu  Icon User is offline

  • New D.I.C Head
  • member icon

Reputation: 5
  • View blog
  • Posts: 45
  • Joined: 21-February 09

Posted 25 February 2009 - 12:01 PM

wow! TQ ;)

i'm looking for tutorial such like this for months. lucky i just remembered to check through Dream In Code. I'm well in programming but totally have no idea in creating database. the only i know is using Access. Thnaks a lot.. thumbs up :^:

ps: I'm looking for way to rate this tutorial and i'll give stars for this.
Was This Post Helpful? 0
  • +
  • -

#6 tanah.melayu  Icon User is offline

  • New D.I.C Head
  • member icon

Reputation: 5
  • View blog
  • Posts: 45
  • Joined: 21-February 09

Posted 25 February 2009 - 12:30 PM

i got an error when i try to follow your steps. iu just downloaded MySql5.0. when i try to do this, this came out:

Quote

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 'use suppliers
CREATE TABLE Products <
ProductID SMALLINT UNSIGNED ZEROFILL' at line 2


what it mmeans? :blink:
Was This Post Helpful? 0
  • +
  • -

#7 Footsie  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 23
  • View blog
  • Posts: 370
  • Joined: 20-September 07

Posted 26 February 2009 - 03:24 AM

You need to tell MySql what database to use in the USE statement. If you were following the tutorial above you should have USE myfirstdb instead of "use suppliers" at the beginning of your "CREATE TABLE Products" statement.

You are telling MySql to use a database named "suppliers" when actually "suppliers" is only a table.
Was This Post Helpful? 0
  • +
  • -

#8 sandeep.patwal85  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 14-March 09

Posted 14 March 2009 - 11:27 PM

great and could i get help in learning oracle.
Was This Post Helpful? 0
  • +
  • -

#9 codeMode  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 65
  • Joined: 30-January 08

Posted 15 March 2009 - 06:20 PM

Great tutorial! This has helped me along on my self studies :)
Was This Post Helpful? 0
  • +
  • -

#10 rapheal2k8  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 12-March 09

Posted 27 April 2009 - 07:12 AM

Hi!..After typing
INSERT INTO suppliers (SupplierName, Phone, Email) VALUE (‘ABCSupplier’,’1122334455’,’abc@dicforum.com’);

i got this '> and nothing i type seems to do anything. pls help me out.

Hi!..After typing
INSERT INTO suppliers (SupplierName, Phone, Email) VALUE (‘ABCSupplier’,’1122334455’,’abc@dicforum.com’);

i got this '> and nothing i type seems to do anything. pls help me out.
Was This Post Helpful? 0
  • +
  • -

#11 Footsie  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 23
  • View blog
  • Posts: 370
  • Joined: 20-September 07

Posted 18 May 2009 - 11:34 PM

This sounds like you haven't ended the statement with the correct delimiter.

Check that you are adding the ";" at the end of the INSERT statement.
Was This Post Helpful? 0
  • +
  • -

#12 Crazy Caveman  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 24
  • Joined: 29-June 09

Posted 20 July 2009 - 11:39 AM

this is great man thx
Was This Post Helpful? 0
  • +
  • -

#13 Kairi  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 107
  • Joined: 02-October 09

Posted 07 October 2009 - 04:36 AM

OMG, OMG you are the best there is, I am starting to learn this in college and my lecturer is terrible in teaching this to us. Trust me when i say terrible i mean she hasn't the slighest clue since she wont help us better. let's say she is like speedy gonzales in terms of teaching the material, if ya got a question she doesn't help as much.
Ahemm Sorry bot that... Any way thank you ever so much on doing this. This will be really helpful for my exam next Monday!!!

How can you download the copy of SQL program? Is there any SQL for dum dummies in terms of what i have learnt at this stage?

This post has been edited by Kairi: 07 October 2009 - 04:43 AM

Was This Post Helpful? 0
  • +
  • -

#14 Footsie  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 23
  • View blog
  • Posts: 370
  • Joined: 20-September 07

Posted 19 October 2009 - 04:44 AM

Hi Kairi,
Thanks for the vote of confidence. :)
You can download MySql from their website: MySql Download It is free.

For a beginner to start learning SQL syntax I would look at W3Schools Sql This will teach you standard SQL syntax.

Hope that gets you going!
Was This Post Helpful? 0
  • +
  • -

#15 Tony514  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 1
  • Joined: 12-September 10

Posted 12 September 2010 - 06:52 AM

Just downloaded MySQL and didn't know where to start.This was very helpful.Thanks alot.
Was This Post Helpful? 1
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2