Archive for the ‘Database’ Category


DDL

Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples:

  • CREATE – to create objects in the database
  • ALTER – alters the structure of the database
  • DROP – delete objects from the database
  • TRUNCATE – remove all records from a table, including all spaces allocated for the records are removed
  • COMMENT – add comments to the data dictionary
  • RENAME – rename an object

DML

Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples:

  • SELECT – retrieve data from the a database
  • INSERT – insert data into a table
  • UPDATE – updates existing data within a table
  • DELETE – deletes all records from a table, the space for the records remain
  • MERGE – UPSERT operation (insert or update)
  • CALL – call a PL/SQL or Java subprogram
  • EXPLAIN PLAN – explain access path to data
  • LOCK TABLE – control concurrency

DCL

Data Control Language (DCL) statements. Some examples:

  • GRANT – gives user’s access privileges to database
  • REVOKE – withdraw access privileges given with the GRANT command

TCL

Transaction Control (TCL) statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.

  • COMMIT – save work done
  • SAVEPOINT – identify a point in a transaction to which you can later roll back
  • ROLLBACK – restore database to original since the last COMMIT
  • SET TRANSACTION – Change transaction options like isolation level and what rollback segment to use

Courtesy:-http://www.orafaq.com/faq/what_are_the_difference_between_ddl_dml_and_dcl_commands


MySQL and SQL Server are the two leading databases that support front end applications related to various domains. The differences between MySQL and SQL Server are listed below:

MySQL SQL Server
MySQL is available for free since MySQL is an open source. SQL Server is not an open source and payment has to be made to use SQL Server.
MySQL offers only updateable views. SQL Server offers indexed views which are much more powerful, performance wise.
MySQL does not support XML. SQL Server supports XML.
MySQL provides only table level security. SQL Server provides column level security.
MySQL does not offer any certification for security. SQL Server has C2 compliant certification. Database security is verified by third party.
Earlier versionsof MySQL does not support triggers. Only MySQL 5.0 supports triggers. SQL Server provides triggers.
User defined functions are not supported in MySQL. User defined functions are supported in SQL Server.
Cursor feature is not available in MySQL. Cursor feature is available in SQL Server.
Stored procedures and full join facility is not offered in MySQL. Stored procedures and full join facility are offered in SQL Server.
Import and Export functions have very limited support in MySQL. Import and export are extensively supported in MySQL.
Transaction support is very much limited in MySQL. Transaction support is extensively and fully offered in SQL Server.
Replication support is very much limited in MySQL. Replication support is extensively and fully offered in SQL Server.
Auto tuning is not supported in MySQL. Auto tuning is supported in SQL Server.
Job scheduling and profiling are not available in MySQL. Job scheduling and profiling are available in MySQL.
Online backup support and clustering support is limited in MySQL. Online backup support and clustering support is extensive and complete in SQL Server.
Log Shipping and Storage Area Network support is not available in MySQL. Log Shipping and Storage Area Network support is available in SQL Server.
OLAP Services, Data Reporting and Data Mining are not supported in MySQL. OLAP Services, Data Reporting and Data Mining are supported in SQL Server.

Common Ports

Posted: April 21, 2014 in Database
Tags:

Common Ports

20 FTP data (File Transfer Protocol)
21 FTP (File Transfer Protocol)
22 SSH (Secure Shell)
23 Telnet
25 SMTP (Send Mail Transfer Protocol)
43 whois
53 DNS (Domain Name Service)
68 DHCP (Dynamic Host Control Protocol)
79 Finger
80 HTTP (HyperText Transfer Protocol)
110 POP3 (Post Office Protocol, version 3)
115 SFTP (Secure File Transfer Protocol)
119 NNTP (Network New Transfer Protocol)
123 NTP (Network Time Protocol)
137 NetBIOS-ns
138 NetBIOS-dgm
139 NetBIOS
143 IMAP (Internet Message Access Protocol)
161 SNMP (Simple Network Management Protocol)
194 IRC (Internet Relay Chat)
220 IMAP3 (Internet Message Access Protocol 3)
389 LDAP (Lightweight Directory Access Protocol)
443 SSL (Secure Socket Layer)
445 SMB (NetBIOS over TCP)
666 Doom
993 SIMAP (Secure Internet Message Access Protocol)
995 SPOP (Secure Post Office Protocol)

Ports between 1024 and 29151 are known as the Registered Ports. Basically, programs are supposed to register their use of these ports and thereby try to be careful and avoid stomping on each other. Here are some common ports and their programs.

1243 SubSeven (Trojan – security risk!)
1352 Lotus Notes
1433 Microsoft SQL Server
1494 Citrix ICA Protocol
1521 Oracle SQL
1604 Citrix ICA / Microsoft Terminal Server
2049 NFS (Network File System)
3306 mySQL
4000 ICQ
5010 Yahoo! Messenger
5190 AOL Instant Messenger
5632 PCAnywhere
5800 VNC
5900 VNC
6000 X Windowing System
6699 Napster
6776 SubSeven (Trojan – security risk!)
7070 RealServer / QuickTime
7778 Unreal
8080 HTTP
26000 Quake
27010 Half-Life
27960 Quake III
31337 BackOrifice (Trojan – security risk!)


Sometimes when we don’t have access to modify configurations on server like in php.ini file and we still need to enable short tags for our php code. We can do it by .htaccess file. If you have not created any .htaccess file yet, create one in root directory of your website and add

php_value short_open_tag 1

That’s it for .htaccess file.

Now if you want to enable that using php.ini and if you can…

just set

short_open_tag=On

in php.ini at server and you are done.. 😀

 

Courtesy:-http://techdc.blogspot.in/2013/04/how-to-enable-php-short-tags-with.html

Joins in MySQL

Posted: January 21, 2014 in Database, MYSQL, Php

This article was written in 2011 and remains one of our most popular posts. If you’re keen to learn more about MySQL, you may find this recent article on administering MySQL of great interest.

“JOIN” is an SQL keyword used to query data from two or more related tables. Unfortunately, the concept is regularly explained using abstract terms or differs between database systems. It often confuses me. Developers cope with enough confusion, so this is my attempt to explain JOINs briefly and succinctly to myself and anyone who’s interested.

Related Tables

MySQL, PostgreSQL, Firebird, SQLite, SQL Server and Oracle are relational database systems. A well-designed database will provide a number of tables containing related data. A very simple example would be users (students) and course enrollments:

‘user’ table:

id name course
1 Alice 1
2 Bob 1
3 Caroline 2
4 David 5
5 Emma (NULL)

MySQL table creation code:


CREATE TABLE `user` (
	`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
	`name` varchar(30) NOT NULL,
	`course` smallint(5) unsigned DEFAULT NULL,
	PRIMARY KEY (`id`)
) ENGINE=InnoDB;

The course number relates to a subject being taken in a course table…

‘course’ table:

id name
1 HTML5
2 CSS3
3 JavaScript
4 PHP
5 MySQL

MySQL table creation code:


CREATE TABLE `course` (
	`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
	`name` varchar(50) NOT NULL,
	PRIMARY KEY (`id`)
) ENGINE=InnoDB;

Since we’re using InnoDB tables and know that user.course and course.id are related, we can specify a foreign key relationship:


ALTER TABLE `user`
ADD CONSTRAINT `FK_course`
FOREIGN KEY (`course`) REFERENCES `course` (`id`)
ON UPDATE CASCADE;

In essence, MySQL will automatically:

  • re-number the associated entries in the user.course column if the course.id changes
  • reject any attempt to delete a course where users are enrolled.
important: This is terrible database design!

This database is not efficient. It’s fine for this example, but a student can only be enrolled on zero or one course. A real system would need to overcome this restriction — probably using an intermediate ‘enrollment’ table which mapped any number of students to any number of courses.

JOINs allow us to query this data in a number of ways.

INNER JOIN (or just JOIN)

SQL INNER JOINThe most frequently used clause is INNER JOIN. This produces a set of records which match in both the user and course tables, i.e. all users who are enrolled on a course:


SELECT user.name, course.name
FROM `user`
INNER JOIN `course` on user.course = course.id;

Result:

user.name course.name
Alice HTML5
Bob HTML5
Carline CSS3
David MySQL

LEFT JOIN

SQL LEFT JOINWhat if we require a list of all students and their courses even if they’re not enrolled on one? A LEFT JOIN produces a set of records which matches every entry in the left table (user) regardless of any matching entry in the right table (course):


SELECT user.name, course.name
FROM `user`
LEFT JOIN `course` on user.course = course.id;

Result:

user.name course.name
Alice HTML5
Bob HTML5
Carline CSS3
David MySQL
Emma (NULL)

RIGHT JOIN

SQL RIGHT JOINPerhaps we require a list all courses and students even if no one has been enrolled? A RIGHT JOIN produces a set of records which matches every entry in the right table (course) regardless of any matching entry in the left table (user):


SELECT user.name, course.name
FROM `user`
RIGHT JOIN `course` on user.course = course.id;

Result:

user.name course.name
Alice HTML5
Bob HTML5
Carline CSS3
(NULL) JavaScript
(NULL) PHP
David MySQL

RIGHT JOINs are rarely used since you can express the same result using a LEFT JOIN. This can be more efficient and quicker for the database to parse:


SELECT user.name, course.name
FROM `course`
LEFT JOIN `user` on user.course = course.id;

We could, for example, count the number of students enrolled on each course:


SELECT course.name, COUNT(user.name)
FROM `course`
LEFT JOIN `user` ON user.course = course.id
GROUP BY course.id;

Result:

course.name count()
HTML5 2
CSS3 1
JavaScript 0
PHP 0
MySQL 1

OUTER JOIN (or FULL OUTER JOIN)

SQL FULL OUTER JOINOur last option is the OUTER JOIN which returns all records in both tables regardless of any match. Where no match exists, the missing side will contain NULL.

OUTER JOIN is less useful than INNER, LEFT or RIGHT and it’s not implemented in MySQL. However, you can work around this restriction using the UNION of a LEFT and RIGHT JOIN, e.g.


SELECT user.name, course.name
FROM `user`
LEFT JOIN `course` on user.course = course.id

UNION

SELECT user.name, course.name
FROM `user`
RIGHT JOIN `course` on user.course = course.id;

Result:

user.name course.name
Alice HTML5
Bob HTML5
Carline CSS3
David MySQL
Emma (NULL)
(NULL) JavaScript
(NULL) PHP

I hope that gives you a better understanding of JOINs and helps you write more efficient SQL queries.

Courtesy: http://www.sitepoint.com/understanding-sql-joins-mysql-database/


How do I import a MySQL .SQL text file to MySQL database sever using command line or gui tools?

You can import a MySQL script (or .sql) file into MySQL server using

  1. Unix / Linux shell prompt.
  2. phpMyAdmin web based gui tool.

Unix / Linux shell prompt example

Copy a .sql file to a remote server using sftp or scp client:
$ scp foo.sql vivek@serer1.cyberciti.biz:~/
Login into a remote server using ssh client:
$ ssh vivek@server1.cyberciti.biz
Type the following command to import a .sql file:

 
mysql -u USERNAME -p -h localhost YOUR-DATA-BASE-NAME-HERE < YOUR-.SQL.FILE-NAME-HERE

In this example, import a ‘foo.sql’ file into ‘bar’ database using vivek as username:

 
mysql -u vivek -p -h localhost bar < foo.sql

phpMyAdmin

Login to phpMyAdmin. Open a web-browser and type phpMyAdmin url:
http://server1.cyberciti.biz/phpmyadmin/
In phpMyAdmin, choose the database you intend to work with from the database menu list (located on the left side).

phpMyAdmin Database SelectionFig.01: phpMyAdmin Database Selection

Choose the IMPORT tab > Click on Browse your computer “Choose file” > Select file > Choose Ok > Choose Go

phpMyAdmin Importing .SQL FileFig.02: phpMyAdmin Importing .SQL File