MySQL Database:
MySQL is a fast, easy-to-use RDBMS being used for many small and big businesses. MySQL is developed, marketed, and supported by MySQL AB, which is a Swedish company. MySQL is becoming so popular because of many good reasons:
· MySQL is released under an open-source license. So you have nothing to pay to use it.
· MySQL is a very powerful program in its own right. It handles a large subset of the functionality of the most expensive and powerful database packages.
· MySQL uses a standard form of the well-known SQL data language.
· MySQL works on many operating systems and with many languages including PHP, PERL, C, C++, JAVA, etc.
· MySQL works very quickly and works well even with large data sets.
· MySQL is very friendly to PHP, the most appreciated language for web development.
· MySQL supports large databases, up to 50 million rows or more in a table. The default file size limit for a table is 4GB, but you can increase this (if your operating system can handle it) to a theoretical limit of 8 million terabytes (TB).
· MySQL is customizable. The open-source GPL license allows programmers to modify the MySQL software to fit their own specific environments.
MySQL works very well in combination of various programming languages like PERL, C, C++, JAVA and PHP. Out of these languages, PHP is the most popular one because of its web application development capabilities.This tutorial focuses heavily on using MySQL in a PHP environment. If you are interested in MySQL with PERL, then you can look into PERL and MySQL Tutorial.
PHP provides various functions to access MySQL database and to manipulate data records inside MySQL database. You would require calling PHP functions in the same way you call any other PHP function.
The PHP functions for use with MySQL have the following general format:
mysql_function(value,value,...);
MySQL Connection using MySql binary:
You can establish MySQL database using MySQL binary at the command prompt.
Example :-
Here is a simple example to connect to MySQL server from command prompt:
[root@host]# mysql -u root -p
Enter password:******
This will give you mysql> command prompt where you will be able to execute any SQL command.
Create Database using MySQL admin:
You would need special privileges to create or to delete a MySQL database. So assuming you have access to root user, you can create any database using MySql MySQL admin binary.
Example :-
Here is a simple example to create database called TUTORIALS:
[root@host]# mysqladmin -u root -p create TUTORIALS
Enter password:******
Selecting MySQL Database from Command Prompt:
This is very simple to select a particular database from mysql> prompt. You can use SQL command use to select a particular database.
Example :-
Here is an example to select database called TUTORIALS:
[root@host]# mysql -u root -p
Enter password:******
mysql> use TUTORIALS;
Database changed
mysql>
Drop Database using MySQL admin:
You would need special privileges to create or to delete a MySQL database. So assuming you have access to the root user, you can create any database using MySql MySQL admin binary.
Be careful while deleting any database because you will lose your all the data available in your database.
Here is an example to delete a database created:
[root@host]# mysqladmin -u root -p drop TUTORIALS
Enter password:******
MySQL uses many different data types broken into three categories: numeric, date and time, and string types. The table creation command requires:
· Name of the table
· Names of fields
· Definitions for each field
Syntax :
Here is generic SQL syntax to create a MySQL table:
CREATE TABLE table_name (column_name column_type);
It is very easy to drop an existing MySQL table, but you need to be very careful while deleting any existing table because data lost will not be recovered after deleting a table.
Syntax :
Here is generic SQL syntax to drop a MySQL table:
DROP TABLE table_name ;
To insert data into MySQL table, you would need to use SQL INSERT INTO command. You can insert data into MySQL table by using mysql> prompt or by using any script like PHP.
Syntax:
Here is generic SQL syntax of INSERT INTO command to insert data into MySQL table:
INSERT INTO table_name ( field1, field2,...fieldN )
VALUES
( value1, value2,...valueN );
The SQL SELECT command is used to fetch data from MySQL database. You can use this command at mysql> prompt as well as in any script like PHP.
SELECT field1, field2,...fieldN table_name1, table_name2...
[WHERE Clause]
[OFFSET M ][LIMIT N]
There may be a requirement where existing data in a MySQL table needs to be modified. You can do so by using SQL UPDATE command. This will modify any field value of any MySQL table.
Syntax
Here is generic SQL syntax of UPDATE command to modify data into MySQL table:
UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]
· You can update one or more field altogether.
· You can specify any condition using WHERE clause.
· You can update values in a single table at a time.
If you want to delete a record from any MySQL table, then you can use SQL command DELETE FROM. You can use this command at mysql> prompt as well as in any script like PHP.
Syntax
Here is generic SQL syntax of DELETE command to delete data from a MySQL table:
DELETE FROM table_name [WHERE Clause]
· If WHERE clause is not specified, then all the records will be deleted from the given MySQL table.
· You can specify any condition using WHERE clause.
· You can delete records in a single table at a time.
Exporting Data with the SELECT ... INTO OUTFILE Statement:
The syntax for this statement combines a regular SELECT with INTO OUTFILE filename at the end. The default output format is the same as for LOAD DATA, so the following statement exports the tutorials_tbl table into /tmp/tutorials.txt as a tab-delimited, linefeed-terminated file:
mysql> SELECT * FROM tutorials_tbl
-> INTO OUTFILE '/tmp/tutorials.txt';
Importing Data with LOAD DATA:
MySQL provides a LOAD DATA statement that acts as a bulk data loader. Here's an example statement that reads a file dump.txt from your current directory and loads it into the table mytbl in the current database:
mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl;
· If the LOCAL keyword is not present, MySQL looks for the datafile on the server host using looking into absolute pathname fully specifies the location of the file, beginning from the root of the filesystem. MySQL reads the file from the given location.
· By default, LOAD DATA assumes that datafiles contain lines that are terminated by linefeeds (newlines) and that data values within a line are separated by tabs.
· To specify a file format explicitly, use a FIELDS clause to describe the characteristics of fields within a line, and a LINES clause to specify the line-ending sequence. The following LOAD DATA statement specifies that the datafile contains values separated by colons and lines terminated by carriage returns and new line character.
No comments:
Post a Comment