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.
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.
CODE
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;
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.
CODE
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:
CODE
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.
CODE
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:
CODE
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.
Ok let's test the Foreign Key now.
Let’s try enter another product insert this time entering a SupplierID of 2.
CODE
INSERT INTO products (SupplierID, ProductName, UnitPrice) VALUE (‘2’,’Beads’,’5’);
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!
Forum Index:
Programming Help |
C and C++ |
VB6 |
Java |
VB.NET |
C# |
ASP.NET |
PHP |
ColdFusion |
Perl and Python |
Ruby |
Databases |
Other Languages |
Game Programming |
Mobile Development |
Software Development |
Computer Science |
Industry News |
Programming Tutorials |
C++ Tutorials |
Visual Basic Tutorials |
Java Tutorials |
VB.NET Tutorials |
C# Tutorials |
Linux Tutorials |
PHP Tutorials |
ColdFusion Tutorials |
Windows Tutorials |
HTML/JavaScript Tutorials |
CSS Tutorials |
Flash Tutorials |
Web Promotion Tutorials |
Graphic Design & Photoshop Tutorials |
Software Development Tutorials |
Database Tutorials |
Other Language Tutorials |
ASP.NET Tutorials |
Game Programming Tutorials |
WPF & Silverlight Tutorials |
Beginners guide to Creating simple MySQL tables, Intro to: creating db, tables, columns, keys...
2 Feb, 2008 - 12:06 PM
| 
