Web developers notes,programming tutorials.

phpMyAdmin can be used to export or backup MySQL databases easily. phpMyAdmin allows users to save database dump as file or display on screen, which involves exporting SQL statements from the server, and transmitting the data across slower network connection or Internet to user’s computer. This process slow the exporting process, increase database locking time and thus MySQL unavailability, slow the server and may simply crash the Apache HTTPD server if too many incoming web connections hogging the system’s resources.

The better way to backup and export MySQL database is by doing the task locally on the server, so that the tables’ data can be instantly dumped on the local disk without delay. Thus export speed will be faster and reduce the time MySQL database or table is locked for accessing. This tutorial is the guide on how to backup (export) and restore (import) MySQL database(s) on the database server itself by using the mysqldump and mysql utilities. There are basically two methods to backup MySQL, one is by copying all table files (*.frm, *.MYD, and *.MYI files) or by using mysqlhotcopy utility, but it only works for MyISAM tables. Below tutorial will concentrate on mysqldump which works for both MyISAM and InnoDB tables.

How to Export or Backup or Dump A MySQL Database

To export a MySQL database into a dump file, simply type the following command syntax in the shell. You can use Telnet or SSH to remotely login to the machine if you don’t have access to the physical box.

mysqldump -u username -ppassword database_name > dump.sql

Replace username with a valid MySQL user ID, password with the valid password for the user (IMPORTANT: no space after -p and the password, else mysqldump will prompt you for password yet will treat the password as database name, so the backup will fail) and database_name with the actual name of the database you want to export. Finally, you can put whatever name you like for the output SQL dump file, here been dump.sql.

The while data, tables, structures and database of database_name will be backed up into a SQL text file named dump.sql with the above command.

How to Export A MySQL Database Structures Only

If you no longer need the data inside the database’s tables (unlikely), simply add –no-data switch to export only the tables’ structures. For example, the syntax is:

mysqldump -u username -ppassword –no-data database_name > dump.sql

How to Backup Only Data of a MySQL Database

If you only want the data to be backed up, use –no-create-info option. With this setting, the dump will not re-create the database, tables, fields, and other structures when importing. Use this only if you pretty sure that you have a duplicate databases with same structure, where you only need to refresh the data.

mysqldump -u username -ppassword –no-create-info database_name > dump.sql

How to Dump Several MySQL Databases into Text File

–databases option allows you to specify more than 1 database. Example syntax:

mysqldump -u username -ppassword –databases db_name1 [db_name2 …] > dump.sql

How to Dump All Databases in MySQL Server

To dump all databases, use the –all-databases option, and no databases’ name need to be specified anymore.

mysqldump -u username -ppassword –all-databases > dump.sql

How to Online Backup InnoDB Tables

Backup the database inevitable cause MySQL server unavailable to applications because when exporting, all tables acquired a global read lock using FLUSH TABLES WITH READ LOCK at the beginning of the dump until finish. So although READ statements can proceed, all INSERT, UPDATE and DELETE statements will have to queue due to locked tables, as if MySQL is down or stalled. If you’re using InnoDB, –single-transaction is the way to minimize this locking time duration to almost non-existent as if performing an online backup. It works by reading the binary log coordinates as soon as the lock has been acquired, and lock is then immediately released.

Syntax:

mysqldump -u username -ppassword –all-databases –single-transaction > dump.sql

How to Restore and Import MySQL Database

You can restore from phpMyAdmin, using Import tab. For faster way, upload the dump file to the MySQL server, and use the following command to import the databases back into the MySQL server.

mysql -u username -ppassword database_name

Postgres SQL - Functions:
In SQL Server and MySQL use a proprietary extension of SQL to provide stored procedure functionality (amongst other features). Microsoft calls it Transact SQL, or “T-SQL�, and as far as I know, MySQL just calls it “MySQL�. Both variants are different enough to occasionally trip up a developer but are written around the common SQL-92/2003 standards. The net result is that the syntax used inside stored procedures resembles SQL code used without sprocs; the same “SELECTs�, “INSERTs�, etc. but with a few extra commands added for control flow and the like. Postgres stored procedures can also be written with a SQL-like language called “PL/pgSQL�.
But, as an added twist, a developer could instead chose to write it in Perl, Python, or Tcl using PL/Perl, PL/Python, or PL/Tcl. Perl/Python/Tcl hackers may rejoice at this thought and it does open up a great many possibilities.

CREATE FUNCTION Orders_details(order_id int) RETURNS int AS $$
DECLARE
qty int;
BEGIN
SELECT COUNT(*) INTO ord
FROM Orders
WHERE oid= order_id;
RETURN ord;
END;
$$ LANGUAGE plpgsql;

The mentionable differences (besides the self-explanatory LANGUAGE command):
Microsoft SQL Server has distinct roles for both user-defined functions (UDFs) and stored procedures. A UDF is typically used within the context of a query, while a sproc takes input and/or output from/to an application. MySQL also has both procedures and functions, but they are defined almost identically and work almost identically. Postgres dispenses with the difference and everything is simply defined as a “function�. As with MySQL, we also see the double dollar-sign (�$$�). However, here we’re using it for a different kind of work-around. With Postgres, the function definition is just one long string and if single quotes are contained somewhere within it,
they’d have to be escaped using a pair of single quotes: �. If there were already escaped quotes within the function, they’d then need to be escaped again, giving us a total of four quotes.
To make life easier (and our code more legible), we can surround the definition with double dollar signs which negates the need for escaping.

Calling the function is also a bit different:

SELECT Orders_details(17);

By the same way we can create trigger procedures in postgres sql by return as trigger.
In this we call this function, each time that particular trigger is executing.
Like mysql, we can create the triggers on before/after insert or delete or update a table.
we can create trigger as follows:

CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
FOR EACH ROW EXECUTE PROCEDURE emp_check();

And function is as follows:
CREATE FUNCTION emp_check() RETURNS trigger AS $emp_stamp$
BEGIN
IF NEW.empname IS NULL THEN
RAISE EXCEPTION ‘empname cannot be null’;
END IF;
END;
RETURN NEW;
$emp_stamp$ LANGUAGE plpgsql;

Join Operator in SQL
————————–

Left join, Right join, Inner join

Join operation in SQL is used to get results from more than one tables according to over requirement criteria.

For this used some types of join operator they are

Left Join
Right Join
Inner Join

Tables in a database can be related to each other with keys. A primary key is a column with a unique value for each row. Each primary key value must be unique within the table. The purpose is to bind data together, across tables, without repeating all of the data in every table.

For Examples, We use three tables related to each other, They are

1. Emp Table

It contains the Personal details of the employee.
Records in the Emp Table are

+—–+——-+———-+——-+———+——-+
| eno | ename | address | city | pincode | phone |
+—–+——-+———-+——-+———+——-+
| 101 | name1 | address1 | city1 | 12345 | 12345 |
| 102 | name2 | address2 | city2 | 12345 | 23456 |
| 104 | name3 | address3 | city3 | 34567 | 34567 |
+—–+——-+———-+——-+———+——-+

here, eno (employee no.) is a Primary Key. By using this key, we integrate the other tables by using join operator.

2. Salary Table

It contains the Salary details of the employee.
Records in the Salary Table are

+—–+——–+———-+———+———+———-+
| eno | deptno | basicpay | hra | pf | netpay |
+—–+——–+———-+———+———+———-+
| 101 | 1 | 6000.00 | 5000.00 | 1000.00 | 10000.00 |
| 102 | 2 | 8000.00 | 4000.00 | 2000.00 | 10000.00 |
| 103 | 2 | 10000.00 | 5000.00 | 3000.00 | 12000.00 |
+—–+——–+———-+———+———+———-+

Left Join
———–

It is used when we need all records in the left table eventhough it not present in the
other table.

Query for Left join is

SELECT * FROM emp left join salary ON emp.eno=salary.eno

It Shows all the records in the emp table eventhough it not present in the salary table,
here eno of emp table matching with eno of salary table then, the fields in the salary table are dispayed otherwise it shows NULL. The result of the above query is
*** 1. row ***
eno: 101
ename: name1
address: address1
city: city1
pincode: 12345
phone: 12345
eno: 101
deptno: 1
basicpay: 6000.00
hra: 5000.00
pf: 1000.00
netpay: 10000.00

*** 2. row ***
eno: 102
ename: name2
address: address2
city: city2
pincode: 12345
phone: 23456
eno: 102
deptno: 2
basicpay: 8000.00
hra: 4000.00
pf: 2000.00
netpay: 10000.00
*** 3. row ***
eno: 104
ename: name3
address: address3
city: city3
pincode: 34567
phone: 34567
eno: NULL
deptno: NULL
basicpay: NULL
hra: NULL
pf: NULL
netpay: NULL

Right Join
————

It is used when we need all records in the right table even though it not present in the
other table.

Query for Right join is
SELECT * FROM emp right join salary ON emp.eno=salary.eno

It Shows all the records in the salary table even though it not present in the emp table,
here, eno of emp table matching with eno of salary table then, the fields in the emp table are dispayed otherwise it shows NULL. The result of the above query is
*** 1. row ***
eno: 101
ename: name1
address: address1
city: city1
pincode: 12345
phone: 12345
eno: 101
deptno: 1
basicpay: 6000.00
hra: 5000.00
pf: 1000.00
netpay: 10000.00
*** 2. row ***
eno: 102
ename: name2
address: address2
city: city2
pincode: 12345
phone: 23456
eno: 102
deptno: 2
basicpay: 8000.00
hra: 4000.00
pf: 2000.00
netpay: 10000.00
*** 3. row ***
eno: NULL
ename: NULL
address: NULL
city: NULL
pincode: NULL
phone: NULL
eno: 103
deptno: 2
basicpay: 10000.00
hra: 5000.00
pf: 3000.00
netpay: 12000.00

Inner Join
————-

It is used when we need all records that are present in both the tables.

Query for inner join is

SELECT * FROM emp inner join salary ON emp.eno=salary.eno
or
SELECT * FROM emp,salary WHERE emp.eno=salary.eno

It Shows the records from emp and salary table, when eno of both tables are equal.
The result of the above query is

*** 1. row ***
eno: 101
ename: name1
address: address1
city: city1
pincode: 12345
phone: 12345
eno: 101
deptno: 1
basicpay: 6000.00
hra: 5000.00
pf: 1000.00
netpay: 10000.00

*** 2. row ***
eno: 102
ename: name2
address: address2
city: city2
pincode: 12345
phone: 23456
eno: 102
deptno: 2
basicpay: 8000.00
hra: 4000.00
pf: 2000.00
netpay: 10000.00