Tutorials in ‘MySQL’ Category

How to backup and restore your MySQL database or table structure to in Linux

Whenever there is a database involved, an important task that should never be neglected is backing it up. In the case of MySQL on a Linux machine, backing up consists of executing a single command.
Backing up a MySQL database can be done with one simple command:

mysqldump -h <host> -u <user> -p<password> <db_name> > backup.sql

For example if you want to backup a local database called db1 using the user user1 with password pass1 you should write:

mysqldump -h localhost -u user1 -ppass1 db1 > backup.sql

The backup.sql file contains an SQL query for creating the exact same database. Restoring such a dump-file is just a matter of one command too:

mysql -h <host> -u <user> -p<password> <db_name> < backup.sql

Following the naming from our previous example will result in the following command to restore the database:

mysql -h localhost -u user1 -ppass1 db1 < backup.sql

In the perfect environment the backup should be created by a cronjob or anacronjob.
Another useful thing that one may want to backup is the table structure. For tables whose structure is relatively often changed this can prove to be very helpful. It is done by using one simple modification of the command for backing up an entire database:

mysqldump -d -h <host> -u <user> -p<password> <db_name> > backup.sql

Did you notice the difference? It is the -d option, which specifies that data should not be dumped. Using our previous examples this will be transform to:

mysqldump -d -h localhost -u user1 -ppass1 db1 > backup.sql
VN:F [1.9.18_1163]
Rating: 6.3/10 (3 votes cast)

How to Handle Null Values with the COALESCE Function in SQL Server

This tutorial will show how to use the COALESCE function to handle null values at run time. The COALESCE() function is used to specify how we want to treat NULL values. It returns the first non-null expression among its arguments.

Setting Up

In this example we will create a table called Products with columns of P_Id, ProductName, UnitPrice, UnitsInStock, and UnitsOnOrder. To create a table we must use the CREATE TABLE statement.

CreateProductsTable.sqlce
Create a Products table.
CREATE TABLE Products
(
P_Id int not null,
ProductName nvarchar(50) not null,
UnitPrice float not null,
UnitsInStock int not null,
UnitsOnOrder int null
)

Yes, it is possible to find a good web host. Sometimes it takes a while to find one you are comfortable with. After trying several, we went with Server Intellect and have been very happy thus far. They are by far the most professional, customer service friendly and technically knowledgeable host we’ve found so far.

Notice we added null to the UnitsOnOrder column, as we will need a null value to work with when using the COALESCE function.

Now that we have our table created, we need to insert data into it. This is done with the INSERT INTO, SELECT, and UNION statements. INSERT INTO specifies where we want to enter the values, SELECT are the values we want to insert, and UNION does not allow duplicate values.

InsertProductsValues.sqlce
Insert data into the Products table.
INSERT INTO Products (P_Id, ProductName, UnitPrice, UnitsInStock, UnitsOnOrder)
SELECT 1, ”Soda”, 10.45, 16, 15
UNION
SELECT 2, ”Bread”, 32.56, 23, null
UNION
SELECT 3, ”Apples”, 15.67, 9, 20

COALESCE Function Query

So now that we have our table set up with data, we are now able to write query to manipulate the data. What we want to do is handle null values in the UnitsOnOrder column with the help of the COALESCE function at execution of the query.

In this query we will select the ProductName and UnitPrice columns, but we will set unit price to multiply the total of UnitsInStock and UnitsOnOrder columns. Meaning we must first add UnitsInStock and UnitsOnOrder and then multiply that number with the UnitPrice to get the total we are looking for. As you may have noticed, we intentionally put a null into the Bread entity so we can change it when called in the COALESCE function.

CoalesceFunction.sqlce
Use the COALESCE function to handle the null values.
SELECT ProductName, UnitPrice * (UnitsInStock + COALESCE(UnitsOnOrder,0)) AS UnitPrice
FROM Products Read the rest of this entry »

VN:F [1.9.18_1163]
Rating: 0.0/10 (10 votes cast)

Creating database diagrams using IBM Data Studio Developer

This tutorial will show you how to create database diagrams using IBM Data Studio Developer.
When dealing with complex databases it is quite useful to be able to visualize data. If you use IBM Data Studio Developer then you are in luck, because it has a built-in feature that helps users visualize data really quick.
First open IBM Data Studio Developer. Expand the desired database: Read the rest of this entry »

VN:F [1.9.18_1163]
Rating: 1.8/10 (15 votes cast)

Create a dynamic menu with PHP

Ever wanted to create an unlimited levels menu using PHP and MySQL in Dreamweaver? Things are easier than it sounds if you use the Creative DW Menu Pack developer extension – you just need a database and the extension will do the rest. Let me show you how to do this with the latest Dreamweaver CS5. Read the rest of this entry »

VN:F [1.9.18_1163]
Rating: 0.1/10 (10 votes cast)

MySQL Database Tutorials – creating a database, importing tables,

Many people tell me they aren’t very fond of scripts, because the install frightens them a bit. And it’s true: creating a database, importing tables, your site’s root path… if you don’t know what you’re doing, this looks pretty complicated.
Luckily, it is not, once you get the hang of it.
Read the rest of this entry »

VN:F [1.9.18_1163]
Rating: 0.0/10 (10 votes cast)

Repair Corrupted MySql Table

Most web servers uses MySQL data base to store data for thier websites. Some website with high traffic makes very strong load of MySQL data base servers. Especially this websites where MySQL is used for main data storage platform like user login/logout, session tracking, transactions, etc. In this case, if the data bases not engineered good enough can appear some failures with them. Also with combination of other outside factors like hardware overloading, operation system setup errors, electricity problems, etc. MySQL databases can get corrupted and the data from the databases are no more will be accessible for reading and writing.

In this tutorial I will show you How to Repair Corrupted MySql Tables and Databases. Read the rest of this entry »

VN:F [1.9.18_1163]
Rating: 0.0/10 (0 votes cast)

Setup MySQL cross replication

In this tutorial I will give you example configuration to setup cross-server replication in MySQL. The normal replication concept is pretty much explained in the term so I will not focus much on it. I will explain it just with a sentence so that you get familiar with the terminology.

Replication enables data from one MySQL database server (called the master) to be replicated to one or more MySQL database servers (slaves).

Cross-server replication is some small upgrade to the same concept. What it means is that the master and the slave change roles. Replication goes in something like a loop as shown in the picture below. Read the rest of this entry »

VN:F [1.9.18_1163]
Rating: 0.2/10 (11 votes cast)

How to count unique records with sql

In tis tutorial we will show you some very basic SQL queries for extracting the exact information that we need from our database.

For our tutorial I have used a simple table with three fields over MySQL database server.

msisdn – is a string field and is the Primary key to the table. This means that values in that field are unique and no duplicates are allowed.
promo_sc – is an integer which will not be used in our demonstration
status – is also an integer number and will be the field we would want to count in the example. Read the rest of this entry »

VN:F [1.9.18_1163]
Rating: 0.0/10 (0 votes cast)

How to Import CSV file into MySql database

We will continue from our previous tutorial for How to Import Excel XLS file into MySql database into the exact solution of the problem – Converting the file into CSV (Comma Separated Value) and next easily importing into MySql. Read the rest of this entry »

VN:F [1.9.18_1163]
Rating: 4.0/10 (3 votes cast)

How to Import Excel XLS file into MySql database

Quite often all of us have to get the data from some Excel table and to fill into MySql database. There is not implemented such standard converting features both on MS Excel and MySql server.

In this tutorial I will show you How to Import Excel XLS file into MySql database.

To done this task, the user has several variants.

First method is no so smart but is very simple and clear method. User have to parse the data is suitable columns. One column for each filed in the data base. Also in front of each value, in each column have to be placed ” symbol and at the end have to be place ”, symbol combination. In the last column, each row have to be ended with ”); symbol combination. Next have to be inserted one column foremost of all. And in each row have to enter: INSERTO INTO `TABLE` (`field1`, `field2`, `fieldN`) VALUES( . Next this have to be saved like normal text file and copy & paste in MySql console or PhpMyAdmin SQL command field.

Yes. I know that this is very dump method, but long time age this was helpful for me. Read the rest of this entry »

VN:F [1.9.18_1163]
Rating: 1.2/10 (11 votes cast)