|
|
Home | Switchboard | Unix Administration | Red Hat | TCP/IP Networks | Neoliberalism | Toxic Managers |
| (slightly skeptical) Educational society promoting "Back to basics" movement against IT overcomplexity and bastardization of classic Unix | |||||||
Please also see our dedicated mySQL FAQ
Overview
What you need
Connecting
Basic Queries
Advanced Queries
Table Joins
Tips and Functions
Overview
MySQL is the world's most popular open source, binary database server. It is capable of supporting over 50,000,000 records. SQL (Structured Query Language) is the language used to to communicate with the MySQL server. With the aid of this tutorial, you will learn how to build a RDBMS (Relational DataBase Management System). Table Joins, are the key to building efficient databases and harnessing the essential power of MySQL. Without table joins, a complex relational database will be as slow as a standard flat file database. PerlScripts JavaScripts.com will teach you how to utilise table joins and greatly increase the speed of your database queries. PerlScripts JavaScripts.com has taught lead programmers of other programming firms how to utilize the true power of MySQL.
What you need
You will need a web host that offers access to a MySQL server. Most hosts offer some sort of control panel, allowing you to set up and create your databases. If you need MySQL hosting, we have MySQL enabled plans from as little as $13.70. Once you have a host, you will need to ask them for certain information or use your control panel to create a database. Our Basic hosting plan ($13.70/mo) allows you to create up to 3 databases. The information you need is :
Path to the server
Database name
Username
Password
Connecting to the DB Server
Connecting to the database is easy. First you must load the MySQL module. Near the of your script add this code :
use Mysql;
The line above will load the module ( or class of predefined subroutines ) named Mysql.pm into your script. To make database connections quick and easy, place your login details into a set of variables. These variables will be passed as arguments to the connect subroutine and should be declared near the of your script or before using the connect command.
$DBHOST = "localhost";
$DBNAME = "mydatabase";
$DBUSER = "perlscripts";
$DBPASS = "ywe6ywnq";
You now need to begin constructing what is known as an object. Below is
an example of how to utilize the connect subroutine.
$DB = Mysql->connect($DBHOST, $DBNAME, $DBUSER, $DBPASS);
Once the above code is executed, assuming the login details are correct, you are connected to the MySQL server. For demonstration purposes only, the code below could be used to select and print the first 100 records from a table named "employees".
$DB = Mysql->connect($DBHOST, $DBNAME, $DBUSER, $DBPASS);
$qry = qq~SELECT * FROM employees WHERE id < 100~;
while( @emps = $qry->fetchrow) {
print qq~
$emps[0], $emps[1], $emps[2] <br>
~;
}
The code above, when translated into English, says "Connect to the server,
select all columns from the table named employees where id is less than
100, then while the data is placed into an array called emps using the fetchrow
method, print columns 1, 2 and 3 then a line break."
Basic Queries
Some of the more popular queries are the easiest to execute, but can also be the most dangerous. For example, to delete a record, you might use :
DELETE FROM employees WHERE age > 35
However, if you forgot to enter a WHERE clause and condition, you would delete the entire table.
Advanced Queries
Advanced queries should be used in preference to extracting data then formatting it using another language. Many programmers either do not have the knowledge or just do not understand the power and speed of MySQL. The MySQL server is all about speed and efficiency. Manipulate and format the data as much as possible using MySQL before passing it on to Perl or PHP. Use built in functions and nested functions to achieve your desired result. In most cases, you will be able to extract all necessary records from all tables in one call to the database.
Table Joins are by far the most useful function on large and complex
databases. They allow you to select and retrieve records from multiple tables
in one action. When selecting records from multiple tables, you must use
a common denominator other wise many more records than you expected will
be returned. There must a column name common to two or more tables. Let's
assume we have two tables with the following structure :
Table 1
employees
Columns
id
firstname
lastname
address
Table 2
salary
Columns
id
hourly
ssn
Note that the common column is the id column. Your where clause
must contain a common column, otherwise more records than you expect will
be returned. In the following select statement, each table is separated
by a comma. We are selecting all columns (denoted by an asterisk *) where
the id is identical in both tables.
SELECT * FROM employees, salary WHERE employees.id = salary.id &&
employees.firstname = 'Grayson'
In the example above, column names are preceded by table names to avoid
confusion. Strings (as opposed to integers) must be quoted. The id is an
integer (a whole number) so it does not need to be quoted. The first name
is a string, so it must be quoted. Note that the common column is the
id. Without the id check (employees.id = salary.id), all records
from both tables that contain the string 'Grayson' would be returned.
MySQL Tips and Functions
| • Comparison operators | |
| = | Equals |
| < | Less than |
| <= | Less than or equal to |
| > | Greater than |
| >= | Greater than or equal to |
| <> | Not equal to |
| != | Not equal to |
| LIKE | Compare strings |
| • Mathematical operators | |
| ( ) | Use parentheses to force precedence |
| + | add |
| - | subtract |
| * | multiply |
| / | divide |
| % | modulo |
| • Conditional operators | |
| || | Logical "or" |
| | | Bitwise "or" |
| OR | Same as Logical "or" |
| && | Logical "and" |
| & | Bitwise "and" |
| AND | Same as Logical "and" |
| • Some Common Functions | |
| CONCAT(str1,str2) | Concatenates strings |
| DAYNAME(date) | Returns the dayname for the given date |
| LAST_INSERT_ID( ) | Returns the last record ID inserted |
| LENGTH(column) | Returns the length of a string in characters |
| TRUNCATE(n,n) | Returns stated number of decimal places |
| UNIX_TIMESTAMP( ) | Returns the epoch |
| • Some Aggregate Functions | |
| AVG(column) | Average number from returned results |
| MAX(column) | Returns the largest number from returned results |
| MIN(column) | Returns the smallest number from returned results |
| SUM(column) | Returns the sum of all returned results |
Society
Groupthink : Two Party System as Polyarchy : Corruption of Regulators : Bureaucracies : Understanding Micromanagers and Control Freaks : Toxic Managers : Harvard Mafia : Diplomatic Communication : Surviving a Bad Performance Review : Insufficient Retirement Funds as Immanent Problem of Neoliberal Regime : PseudoScience : Who Rules America : Neoliberalism : The Iron Law of Oligarchy : Libertarian Philosophy
Quotes
War and Peace : Skeptical Finance : John Kenneth Galbraith :Talleyrand : Oscar Wilde : Otto Von Bismarck : Keynes : George Carlin : Skeptics : Propaganda : SE quotes : Language Design and Programming Quotes : Random IT-related quotes : Somerset Maugham : Marcus Aurelius : Kurt Vonnegut : Eric Hoffer : Winston Churchill : Napoleon Bonaparte : Ambrose Bierce : Bernard Shaw : Mark Twain Quotes
Bulletin:
Vol 25, No.12 (December, 2013) Rational Fools vs. Efficient Crooks The efficient markets hypothesis : Political Skeptic Bulletin, 2013 : Unemployment Bulletin, 2010 : Vol 23, No.10 (October, 2011) An observation about corporate security departments : Slightly Skeptical Euromaydan Chronicles, June 2014 : Greenspan legacy bulletin, 2008 : Vol 25, No.10 (October, 2013) Cryptolocker Trojan (Win32/Crilock.A) : Vol 25, No.08 (August, 2013) Cloud providers as intelligence collection hubs : Financial Humor Bulletin, 2010 : Inequality Bulletin, 2009 : Financial Humor Bulletin, 2008 : Copyleft Problems Bulletin, 2004 : Financial Humor Bulletin, 2011 : Energy Bulletin, 2010 : Malware Protection Bulletin, 2010 : Vol 26, No.1 (January, 2013) Object-Oriented Cult : Political Skeptic Bulletin, 2011 : Vol 23, No.11 (November, 2011) Softpanorama classification of sysadmin horror stories : Vol 25, No.05 (May, 2013) Corporate bullshit as a communication method : Vol 25, No.06 (June, 2013) A Note on the Relationship of Brooks Law and Conway Law
History:
Fifty glorious years (1950-2000): the triumph of the US computer engineering : Donald Knuth : TAoCP and its Influence of Computer Science : Richard Stallman : Linus Torvalds : Larry Wall : John K. Ousterhout : CTSS : Multix OS Unix History : Unix shell history : VI editor : History of pipes concept : Solaris : MS DOS : Programming Languages History : PL/1 : Simula 67 : C : History of GCC development : Scripting Languages : Perl history : OS History : Mail : DNS : SSH : CPU Instruction Sets : SPARC systems 1987-2006 : Norton Commander : Norton Utilities : Norton Ghost : Frontpage history : Malware Defense History : GNU Screen : OSS early history
Classic books:
The Peter Principle : Parkinson Law : 1984 : The Mythical Man-Month : How to Solve It by George Polya : The Art of Computer Programming : The Elements of Programming Style : The Unix Hater’s Handbook : The Jargon file : The True Believer : Programming Pearls : The Good Soldier Svejk : The Power Elite
Most popular humor pages:
Manifest of the Softpanorama IT Slacker Society : Ten Commandments of the IT Slackers Society : Computer Humor Collection : BSD Logo Story : The Cuckoo's Egg : IT Slang : C++ Humor : ARE YOU A BBS ADDICT? : The Perl Purity Test : Object oriented programmers of all nations : Financial Humor : Financial Humor Bulletin, 2008 : Financial Humor Bulletin, 2010 : The Most Comprehensive Collection of Editor-related Humor : Programming Language Humor : Goldman Sachs related humor : Greenspan humor : C Humor : Scripting Humor : Real Programmers Humor : Web Humor : GPL-related Humor : OFM Humor : Politically Incorrect Humor : IDS Humor : "Linux Sucks" Humor : Russian Musical Humor : Best Russian Programmer Humor : Microsoft plans to buy Catholic Church : Richard Stallman Related Humor : Admin Humor : Perl-related Humor : Linus Torvalds Related humor : PseudoScience Related Humor : Networking Humor : Shell Humor : Financial Humor Bulletin, 2011 : Financial Humor Bulletin, 2012 : Financial Humor Bulletin, 2013 : Java Humor : Software Engineering Humor : Sun Solaris Related Humor : Education Humor : IBM Humor : Assembler-related Humor : VIM Humor : Computer Viruses Humor : Bright tomorrow is rescheduled to a day after tomorrow : Classic Computer Humor
The Last but not Least Technology is dominated by two types of people: those who understand what they do not manage and those who manage what they do not understand ~Archibald Putt. Ph.D
Copyright © 1996-2021 by Softpanorama Society. www.softpanorama.org was initially created as a service to the (now defunct) UN Sustainable Development Networking Programme (SDNP) without any remuneration. This document is an industrial compilation designed and created exclusively for educational use and is distributed under the Softpanorama Content License. Original materials copyright belong to respective owners. Quotes are made for educational purposes only in compliance with the fair use doctrine.
FAIR USE NOTICE This site contains copyrighted material the use of which has not always been specifically authorized by the copyright owner. We are making such material available to advance understanding of computer science, IT technology, economic, scientific, and social issues. We believe this constitutes a 'fair use' of any such copyrighted material as provided by section 107 of the US Copyright Law according to which such material can be distributed without profit exclusively for research and educational purposes.
This is a Spartan WHYFF (We Help You For Free) site written by people for whom English is not a native language. Grammar and spelling errors should be expected. The site contain some broken links as it develops like a living tree...
|
|
You can use PayPal to to buy a cup of coffee for authors of this site |
Disclaimer:
The statements, views and opinions presented on this web page are those of the author (or referenced source) and are not endorsed by, nor do they necessarily reflect, the opinions of the Softpanorama society. We do not warrant the correctness of the information provided or its fitness for any purpose. The site uses AdSense so you need to be aware of Google privacy policy. You you do not want to be tracked by Google please disable Javascript for this site. This site is perfectly usable without Javascript.
Last modified: March 12, 2019
