Advanced SQL
1. Joining tables:
You can join two or more tables extremely easy with SQL. Assuming the following example:
1.1 Create your tables with phpmyadmin.
Create some database, for example: names
Then, dump the following code below, in order to create the table homecity:
-- phpMyAdmin SQL Dump -- version 2.11.5 -- http://www.phpmyadmin.net -- -- Host: localhost -- Generation Time: Apr 01, 2008 at 06:54 PM -- Server version: 5.0.45 -- PHP Version: 4.4.8 SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; -- -- Database: `names` -- -- -------------------------------------------------------- -- -- Table structure for table `homecity` -- CREATE TABLE IF NOT EXISTS `homecity` ( `id` bigint(20) NOT NULL auto_increment, `city` varchar(20) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ; -- -- Dumping data for table `homecity` -- INSERT INTO `homecity` (`id`, `city`) VALUES (1, 'Los Angeles'), (2, 'New York'); 1.2 Then dump the following code, so that to create another table: name -- phpMyAdmin SQL Dump -- version 2.11.5 -- http://www.phpmyadmin.net -- -- Host: localhost -- Generation Time: Apr 01, 2008 at 06:55 PM -- Server version: 5.0.45 -- PHP Version: 4.4.8 SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; -- -- Database: `names` -- -- -------------------------------------------------------- -- -- Table structure for table `name` -- CREATE TABLE IF NOT EXISTS `name` ( `id` bigint(20) NOT NULL auto_increment, `name` varchar(20) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ; -- -- Dumping data for table `name` -- INSERT INTO `name` (`id`, `name`) VALUES (1, 'John'), (2, 'Jane');Now, let's try to join the two tables: Name + homecity. Why do we do this? Because we want to associate the names of John to his home city and Jane to her home city. We do this, using this SQL query. Open SQL on your phpmyadmin and type and execute this:
SELECT name.name, homecity.city
FROM name, homecity
WHERE name.id = homecity.id
You should then see similar to the following result:
Now the tables are joined:
Note that this query does NOT change your tables! It only shows you information about them, but it doesn't change your tables forever or even for a moment. It just doesn't rearrange anything, but it's used to show you in a moment the information you are looking for.
If we want to know who is from Los Angeles? We add the "AND" clause at the end of the previous query, so now it looks like this:
SELECT name.name, homecity.city
FROM name, homecity
WHERE name.id = homecity.id
AND homecity.city = 'Los Angeles'
We do the same thing as described above, but using the JOIN clause. The JOIN clause returns only results if it finds match between the fields. The difference is that in the previous example we relied on the primary keys. Now even if we don't have primary keys we can use JOIN to get the results:
Inner Join:
SELECT name.name, homecity.city
FROM name
[B]INNER JOIN[/B] homecity
WHERE name.id = homecity.id
We can use as well LEFT JOIN. Left join shows all rows from the first table even if there is no corresponding match in the second table. For example, we have a name "Michael" with primary key which is 3 in our first table(names). However in the other table(city) we have only two cities with primary keys...1 and 2. Since we don't have 3 rows there, it is obvious that we don't get any results corresponding to Michael. Now let's insert firstly the value of Michael in our "names" database and then demonstrate the LEFT JOIN.
Open SQL on phpmyadmin and insert the value for Michael.
Then open again the SQL window in phpmyadmin and write this code:
SELECT name.name, homecity.city
FROM name
LEFT JOIN homecity ON name.id = homecity.id
You will see a similar screen when you execute it:
You can use and the RIGHT join, which is similar although it shows only the rows from the right table which doesn't have a corresponding value in their left value. Insert the values MIAMI and SAN FRANCISCO in the table: city. Then type this code to learn more about the RIGHT JOIN:
SELECT name.name, homecity.city
FROM name
RIGHT JOIN homecity ON name.id = homecity.id
Though now, you must notice something very interesting. In the previous case when you used to insert the value "Michael", It then appeared that the ID of Michael becomes 3 and it hasn't association with the right table. Now you add two values(San Francisco with ID 3 and Miami with ID 4). So, as you can see Michael has value 3 and San Francisco has value of 3 and they are now here connected.
Now if we want to show one and the same fields from two separate tables, we can use UNION. For example we can now add two fields with one and the same name...Lets call those field: phones. Now open your SQL window in phpmyadmin and add the following code into "name".
ALTER TABLE `name` ADD `phones` VARCHAR( 20 ) NOT NULL ;Now once again type the following code into "homecity":
ALTER TABLE `homecity` ADD `phones` VARCHAR( 20 ) NOT NULL ;Now, in the homecity table insert a phone number, for example: 415-310-2209
Now, in the name table insert some different phone number, for example: 212-492-3333
And now we show them with the UNION clause:
SELECT phones
FROM name
UNION
SELECT phones
FROM homecity;Keep in mind that the UNION statement shows only values which are different. For example, it doesn't show duplicates. So, if the phone number 415-310-2209 is used and in the name table and in the homecity table, it will be skipped. But, you can even show the duplicated values with the use of UNION ALL.Before that you can add the phone number 415-310-2209 and to the other table where it is not listed(name). Then use the UNION ALL:SELECT phones FROM name UNION ALL SELECT phones FROM homecity
Indexes:
Indexes are essential part of any large SQL database, meaning that sites like Wikipedia, Yahoo, Flickr and other SQL driven sites have them, for sure. The idea behind indexes is that it creates track of your records. If you try to use windows search on your desktop, you most probably have seen the "error" that you need to create and index before you can use Windows desktop search. Now we can easily create indexes in SQL. Consider the following example:
CREATE UNIQUE INDEX Index1
ON NAME(name);
You have now indexed city on the homecity table. If you need to index more fields use that syntax:
CREATE UNIQUE INDEX Index1
ON HOMECITY(city, phones);Then you will index and city and phones.
SQL functions:
SQL as well supports mathematical functions. Now, let's try to sum the amount of some numeric values. Assuming that we have a table where we have the names of Bill Gates and Steve Ballmer. We also keep track of their money. Say, for example that Bill Gates has 60,000,000,000 USD and Steve Ballmer has 20000000000 USD. Now, if we were from the IRS we would like to find out how much money have they at all, since we need this for our tracks on Microsoft. We do this using the SUM clause in SQL:
SELECT SUM(column) FROM table;
Note that you can use the next functions as well:
ABS()
Return the absolute value
ACOS()
Return the arc cosine
ASIN()
Return the arc sine
ATAN2(), ATAN()
Return the arc tangent of the two arguments
ATAN()
Return the arc tangent
CEIL()
Return the smallest integer value not less than the argument
CEILING()
Return the smallest integer value not less than the argument
CONV()
Convert numbers between different number bases
COS()
Return the cosine
COT()
Return the cotangent
CRC32()(v4.1.0)
Compute a cyclic redundancy check value
DEGREES()
Convert radians to degrees
DIV(v4.1.0)
Integer division
/
Division operator
EXP()
Raise to the power of
FLOOR()
Return the largest integer value not greater than the argument
LN()
Return the natural logarithm of the argument
LOG10()
Return the base-10 logarithm of the argument
LOG2()
Return the base-2 logarithm of the argument
LOG()
Return the natural logarithm of the first argument
-
Minus operator
MOD()
Return the remainder
%
Modulo operator
OCT()
Return an octal representation of a decimal number
PI()
Return the value of pi
+
Addition operator
POW()
Return the argument raised to the specified power
POWER()
Return the argument raised to the specified power
RADIANS()
Return argument converted to radians
RAND()
Return a random floating-point value
ROUND()
Round the argument
SIGN()
Return the sign of the argument
SIN()
Return the sine of the argument
SQRT()
Return the square root of the argument
TAN()
Return the tangent of the argument
*
Times operator
TRUNCATE()
Truncate to specified number of decimal places
-
Change the sign of the argument
Example of ATAN:
SELECT ATAN(3342);
http://forum.codecal...als/apps/18.jpg
http://forum.codecal...als/apps/19.jpg
Conclusion:
Now that we showed you how powerful SQL can be, you need perhaps a better understanding of what hosting needs, what data types and so on do you need in order to handle the best SQL application. We will explain this in detail in the next tutorial called: SQL data types.
Edited by Jordan, 05 April 2008 - 11:31 AM.
