MySQL: Database and its Commands

MySQL Database:

Here we will learn how to connect to the MySQL database using the client tools that comes with the MySQL.

MySQL has its own client interface, allowing you to move data around and change database configuration. Note that you must use a password to log in. Assigning database users allows you to limit access to server tables that have multiple users. Each MySQL server, where tables are grouped together, can host many databases. Normally, a web application database has its own proprietary database.

You will need to know the following:

The IP address of the database server

The name of database

The username

The password

If you have installed MySQL server on your computer, you will be able to use the defaults from the installation and the password you specified. This chapter looks at two ways to communicate with MySQL, the command line and the phpMy Admin, a web-based tool.

 

Creating a MySQL Database:

Here you are going to create a database called store. The create database command work like this:

CREATE DATABASE store;

If this works then you will get a result:

Query ok, 1 row affected (0.03sec)

Database names cannot contain any spaces. On UNIX servers, such as Linux and Mac OS X, database names are case sensitive.

To start using this database type:

USE ‘store’;

You will get the result:

Database Changed.

Assuming you have done everything correctly, you will be set up with new data and selected it for use. Creating tables is an important concept, so that’s where we are headed!

 

Assessing the database with command line:

One way of communicating MySQL is via MySQL command line client. Depending on operating system which you are using, you either need to open a command shell for windows (type cmd from the Run dialog) or open a terminal session, in Unix environments.

Once you reach command line, type MySQL and press enter.

The syntax for MySQL command is:

mysql –h hostname –u user –p

If you have installed MySQL on your computer, the defaults username is root. You can omit the hostname flag and value. Enter your password when MySQL displays: “Enter Password” prompt.

If the password, username and the hostname are correct, you will see a banner message.

 

MySQL- Prompts:

At MySQL prompt, you can enter database commands followed by enter.

There is also a set of commands that MySQL itself interprets. For list of commands, type help or h at the mysql> pronmpt.

 

Commands Prompts Meanings:

mysql	Waiting for a command
->	Waiting for the next line of a command
‘>	Waiting for the next line of a string that starts with a single quote
“>	Waiting for the next line of  a string that starts with the double quote

Commands:

MySQL Client Commands:

COMMAND	PARAMETER	MEANING
Quit		Exists command line
Use	Database Name	Use a specific database
Show	Tables or database	Shows the list of tables or databases available
Describe	Table Name	Describe a table’s column
Status		Display database version and status
Source	Filename	Executes commands from file and script

The given commands allows you to perform tasks such as executing SQL commands that are stored in a script file using the source.

mysql> SHOW DATABASES;

+————-+

|Database|

+————-+

|mysql|

+————-+

1 row in set (0.00sec)

To scroll back to the commands you have already entered in the MySQL, use the up arrow key.

The default database that is present after an install is called mysql. The mysql database also stores the database user authentication information.

The use command allows you to do this.

To connect to the mysql database, type the following at the MySQL prompt:

USE ‘mysql’;

This returns:

Database Changed

If your ISP supplied a different database name then use that instead of mysql.

Here are some commands used for database and table manipulation. You can manage your database/table or make changes to your to them by using these commands.

1. CREATE DATABASE [IF IT AINT EXIXTS] db_name

This command is used for creating a database.

2. CREATE TABLE [IF IT AINT EXISTS] tbl_name (col1 col_type, col2 col_type,…)

This command creates the table.

3. ALTER TABLE tbl_name ADD col col_type [AFTER col],…

Used for adding a new column to a table in the database.

4. ALTER TABLE MODIFY col new_col_type,…

Used for changing the different column type definations

5. ALTER TABLE CHANGE old_col new_col_new_col_type,…

Used for changing the name of columns and their definations.

6. ALTER TABLE DROP col, …

Used for removing the column from a table in the database.

1. RENAME TABLE tbl_name TO new_tbl_name

Used for renaming the table.

8. INSERT [IGNORE] INTO tbl_name[(col1, col2, .....)] VALUES (value1, value2, …..)

Used for inserting rows in table

9. UPDATE [IGNORE] tbl_name SET col1=value1, col2=value2,….. WHERE condition [ORDER BY.....] [LIMIT count]

Used for the purpose of modifying information this is already stored in the table.

1. DELETE [IGNORE] FROM tbl_name WHERE condition [ORDER BY... ] [LIMIT CONDITION]

Used for deleting the information from the table.

1. TRUNCATE TABLE tbl_name

This is used for deleting all information within the table.

1. DROP TABLE [IF EXISTS] tbl_name

Used for deleting a table of database.

1. DROP DATABASE [IF EXISTS] db_name

Used for deleting a database.

If you want to delete the information records from a database table, you can use the DELETE FROM statement.

For example:

Here is a table LAMP containing some records:

Subject	Category	Chapters
PHP	Programming Language	26
MySQL	Query Language	25

Now the following code will delete all records from the LAMP table where the category is programming language.

<?php

$con = mysql_connect(“localhost”,”PHP”,”abc123″);

if (!$con)

{
die(‘Could not connect: ‘ . mysql_error());
}

mysql_select_db(“my_db”, $con);

//here WHERE statement specifies the record or records to be deleted.

mysql_query(“DELETE FROM Persons WHERE lastName=’Programming Language’”);

mysql_close($con);

?>

After running the above given code the table will look like:

Subject	Category	Chapters
MySQL	Query	25

Table Manipulation:

After you have created a table and started storing information in it and you need to change the column types.

For example:

In case a field having 30 characters and actually it needs 100. You could start redefining the table, but you would lose all your data. No problem, MySQL allows you to modify column types without any loss in data.

Renaming a Table:

For renaming a table, use ALTER TABLE table RENAME newtable. In this example, we are renaming a table from books to publications.

ALTER TABLE ‘books’ RENAME ‘publications’;

This would look like:

mysql> ALTER TABLE books RENAME publications;

Query OK. Rows affected (0.12 sec)

mysql>

Changing a column data type:

To change column data type, Use ALTER TABLE MODIFY column datatype. The following syntax allows you to modify author field so that the column can take 150 characters.

ALTER TABLE ‘author’ MODIFY ‘author’ VARCHAR (150);

Changing a column data type will look like:

mysql> ALTER TABLE authors MODIFY author VARCHAR (150);

Query OK, 4 rows affected (0.14sec)

Records: 4 Duplicates: 0 Warning: 0

mysql>

 

Adding a column:

To add a column, use ALTER TABLE table ADD column datatype. Here, we are changing a publication table so a timestamp is automatically added to it.

ALTER TABLE publications ADD times TIMESTAMP;

For example:

mysql> ALTER TABLE books ADD time TIMESTAMP;

Query OK, rows affected (0.12sec)

Records : 2 Dublicates : 0 Warnings : 0

mysql>

If you look at your database tables and decide you don’t need a specific column, you can remove it.

To remove a column, use ALTER TABLE table DROP column.

Here we are removing the pages column; therefore, we’ll no longer know how many pages are in a book listed in a database.

Updating Data using update statement in a table:

For updating existing records with in a table, update statement is used as shown in the following syntax:

UPDATE table_name
SET column1=value, column2=value2, column3=value3,…..,column=valueN;
WHERE some_column=some_value

 

Now here is the example to update records in an existing table using update statement:

<?php

$con = mysql_connect(“localhost”,”required name”,”required password”);

if (!$con)

{
die(‘Could not connect: ‘ . mysql_error());

}

mysql_select_db(“my_db”, $con);

mysql_query(“UPDATE tablename SET Age = ‘age’

WHERE FirstName = ‘firstname’ AND LastName = ‘lastname’”);

mysql_close($con);

?>

After running the above given code the table will get updated with the new given records.

 

Backing up and Restoring Data in MySQL:

Even the best maintained databases occasionally develop problems. Hardware failures, in particular, can create some errors in web pages. Now that you are using a database, just backing up files (HTML, PHP and images) on your web server is not enough. There’s nothing worse than information your web browser that they have to reenter the information, such as their accounts, or have to recreate your catalog items. Having a complete backup can make the difference between an hour of downtime and having to recreate the wheel. There are a couple of tactics that we will discuss for backing up your database.

 

Copying Database Files:

You can also do a simple file backup of your MySQL database’s data files, in the same way that you can backup your HTML and PHP files. If you can backup files, you can back up the MySQL database files.

We don’t recommend this tactic for moving from one machine to another server, since different versions of MySQL may expect these files to be in different format. MySQL stores its data files in a special data directory7u that is usually located in C:ProgramsFilesMySQLMySQL Server 4.1data[database name] on windows and in /var/lib/mysql on Unix variants such as Linux and Mac OS X.

To fully backup and restore MySQL database using your current data files, all the files must be replaced in the same directory from which they were backed up.

Then the database must be restated.

 

The mysqldump Command:

It’s better to use MySQL command line tool for making complete database backups.

The same tools you will use to backup and restore can also be used to change platforms or move your database from one server to another.

mysqldump creates  a text file containing SQL statements required to rebuild the database objects and insert the data.

The mysqldump command is accessible from the command line and takes parameters for backing up a single table, a single database, or everything.

The command’s syntax is:

mysqldump –u user –p objects_to_backup

The default mode for mysqldump is to export backup and then to standard output, which is usually the screen.

Backing up:

The command for backing up database is known as test from the shell prompt.

mysqldump –u root –p test>my_backup.sql

The above given code tells mysqldump to log into database as the root with a password, and to backup the test database.

The command output is saved to a file called my_backup.sql with the help of the redirect character also known as the greater-than symbol(>).

(An example showing the first portion of the output mysqldump creates)

The contents of the my_backup.sql file:

–MySQL dump 10.9

–

–Host: localhost Database: test

– ———————————————–

– Server version 4.1.11-Debian__4-log

–

– Table structure for table ‘authors’

–

DROP TABLE IF EXISTS ‘authors’;

CREATE TABLE ‘authors’ (

‘author_id’ int(11) NOT NULL auto_increment,

‘title_id’ int(11) NOT NULL default ‘0’,

‘author’ varchar(125) default NULL,

PRIMARY KEY (‘author_id’)

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

–

– Dumping data for table ‘authors’

–

LOCK TABLES ‘authors’ WRITE;

INSERT INTO ‘authors’ VALUES (1, 1, ‘xx’), (2, 1,’yy’), (3,2, ‘zz’), (4,2, ‘aa’);

UNLOCK ATBLES;

The given two major sections within the example are creating the authors table and populating the data for the table.

For backing up only a single table from a database, simply add the name of the table after the name of database, as shown in the following example

Example:

The following command illustrates how to back up only the authors table:

$ mysqldump –u root –p test authors>authors.sql

If you just want to backup everything on the database.

To do this, use the –all-databases command-line switch. The resulting database back up file will contain the commands necessary to create the databases and users, making a complete database restore a snap.

For using this parameter:

$ mysqldump –u root –p –all-databases>my_backup.sql

For creating an empty database copy just the structure for testing, use the –no-data switch:

$ mysqldump –u root –p –no-create-info test>data.sql

Of course, having a backup of your database doesn’t do you much good if you don’t know how to restore the database from it.

Restoring a MySQL Backup:

It is not difficult to recreate your database from a mysqldump file.

The contents of the backup file are simply SQL statements and can therefore be processed by the mysql command line client to restore the backed up data.

If you did a selective backup of only one database, it’s a bit more complex. To restore that type of backup file, use the –D command-line switch:

mysql –u root –p –D test<my_backup.sql

This is how we restore default dump files.

 

Working With Other Formats:

Working with SQL-based files is suitable; but when you want to hoard your data in further formats.

For example, a general method of representing a listing of data is in CSV (comma-separated-values) format. The mysqldump command supports this format.

All you require to do is specify the –no-create-info, –tab, and –fields-terminated-by arguments like this:

mysqldump –u root –p –no-create-info –tab=/home/jon –fields-terminated-by=’,’test

This tells mysqldump to generate separate files for each table in the test database.

They’ll all be placed in the directory /home/jon. Each file’s name will be the name of the table that is being exported.

Each file contains the records in the respective table separated by the comma character (,) that was specified on the command line.