Science Library - free educational site

MySQL Queries

SQL stands for Structured Query Language. MySQL is a common SQL language which permits sites to access and have full editing capabilities with databases on a server. MySQL is very powerful when used in conjunction with a server-side language like PHP.

Once the database has been accessed, MySQL utilises query strings to create, delete and edit tables. To access a database, the following information must be provided. This can be done within the HTML content, or once for the session. Often, it is convenient to create a config file, which can access the mysqli_connect file. Due to the security issues involved, the mysqli_connect file should be located outside the root of the domain.

Create Table

This is an example of CREATE TABLE:

  • CREATE TABLE IF NOT EXISTS `Customers` (
  • 'cust_id' int(4) unsigned NOT NULL AUTO_INCREMENT,
  • 'first_name' VARCHAR(32) NOT NULL,
  • 'second_name' VARCHAR(32) NULL,
  • 'telephone' int(24) unsigned NULL,
  • 'address' VARCHAR(84) NULL,
  • 'city' VARCHAR(32) NULL,
  • 'country' VARCHAR(32) NOT NULL,
  • PRIMARY KEY ('cust_id')
  • ) ENGINE=MyISAM CHARSET=utf8 AUTO_INCREMENT=1 ;
Adding Records

INSERT INTO tablename (column1, column2...) VALUES (value1, value2...);

INSERT INTO tablename VALUES (value1, value2, NULL, ...);

In the first example above, the columns are specified, and therefore any can be nominated, in any order. In the second example, the values must be listed in the exact order as the columns in the table.

In SQL, strings, date and time values are quoted, but numeric, functions, and NULL values are not: e.g.

INSERT INTO characters (name, age, town, password, date) VALUES ('Arthur Dent', 42, 'Croydon', SHA1('fortytwo'), NOW());

SHA1('password') encrypts the password to a 40-character string before it is stored. An older alternative, MD5('password'), encrypts the password as a 32-character string. Both of these are irreversible (an exercise as futile as trying to redraw the original model from a Picasso abstract painting).

NOW() inserts the current date and time.

Selecting Records

SELECT col1, col2 FROM tablename;

SELECT * FROM tablename;

In the first case, any number of specified columns, and in the second case all columns, will be returned. Although never essential, by specifying the columns in a SELECT query, an array can be simply created with the required data in the order needed.

Conditionals

In the above SELECT examples, all the records in the table will be returned. To restrict the query (which is usually required), various conditionals may be used.

The WHERE term introduces a condition which limits the returned results. These are the operators which may be used in an SQL query:

OperatorMeaning
=equals
<less than
>greater than
<=less than or equal to
>=greater than or equal to
!=not equal to
< >not equal to
IS NOT NULLvalue set
IS NULLno value set
BETWEEN ... and ...sets range for value
NOT BETWEEN ... and ...outside this range
INone of the values following
OR (or ||)one of two alternative conditionals is true
AND (or &&)both conditionals must be true
NOT (or !)condition is not true

For example, imagine you actually have some members of a Scottish bagpipe appreciation club. You wish to send an email to all members whose origin is south of the Wall, using the column 'origin':

SELECT first_name, last_name, email FROM members WHERE origin = 'Sassenach';

This example also illustrates that queries will still work even if the returned number of records is zero.

Alternatively, you could specify all non-Scottish people, using the conditional in the negative, combined with the condition that the person must be retired:

SELECT first_name, last_name, email FROM members WHERE origin != 'Scotland' AND age >= 65;

You could extend the search to those who live in Wales, and change the age range to adults who are at the usual working age:

SELECT first_name, last_name, email FROM members WHERE (origin = 'Sassenach') OR (origin = 'Wales') AND (age BETWEEN 18 and 65);

Note that strings are in quote marks, and numbers not.

NULL event

Care must be taken when dealing with records that are empty, or have a value of an empty string.

WHERE email IS NULL; is the conditional for no input in the column.

WHERE email = ''; is the conditional for an empty string in the column.

The distinction here is that WHERE email IS NULL; would not return true if an email was removed from a record, whereas WHERE email = ''; would.

Password check

When a returning user enters their password into a form field, this value can be passed to the SQL query as the conditional: WHERE pass = SHA1($password);. The variable is assigned as $password = $_POST['password']. If there is no match for the encrypted password in the database table, the condition is not met, and the query result will be an empty array.

Post variables may be used in MySQL queries, but they need to be surrounded by curly brackets ({}): WHERE pass = SHA1({$_POST['password']});.

Mathematical operators in conditionals

A neat trick is to use mathematical operators (+,-,*,/) in a query conditional.

Example: to check that an appliance is still covered by warranty, whose duration is stored in the variable $warranty_length:

WHERE current_year <= $purchase_year+$warranty_length;

LIKE and NOT LIKE

A query can also seek from a column all records which contain strings as part of their value, by using a wild card. The underscore symbol (_) represents a single character, and the percentage symbol (%) represents any number of characters (zero, one or more).

WHERE favourite_animal LIKE "dog%";

This query will return all records which have values starting with 'dog': so 'dog', 'dogs' and 'doggies' would qualify, but 'hounddog' would not.

The query NOT LIKE would return all records except those which have 'dog' as the first three characters in the value.

Similarly, %dog indicates that any number of characters can appear before the final 'dog', and %dog% asks for all values where 'dog' occurs within them.

To specify the exact number of characters, the underscore (_) can be used:

WHERE user_id LIKE '1_ _'; //1 followed by two underscores

would return all 3-digit user id numbers starting with '1' (i.e. between 100 and 199). This is equivalent to the query conditional: WHERE user_id BETWEEN 100 and 199;. Notice that the LIKE '1_ _' conditional treats the value as a string (therefore within quote marks), rather than a number (no quote marks).

SORTING query results

SELECT * FROM table ORDER BY column ASC;

ASC and DESC are optional, and will order the results in ascending or descending order, depending on their type (by number sequence or alphabetical order).

SELECT * FROM table ORDER BY columnA, columnB;

will order the results first by columnA, then by columnB.

NULL values will always be displayed first.

Limiting Results of a Query

By default, all records matching the WHERE conditional will be returned. However, there are many cases where the number of records needs to be limited:

SELECT * FROM table LIMIT x;

where 'x' is the number of records to be returned.

SELECT * FROM table LIMIT a, b;

In this example, 'b' records are returned starting at 'a'.

SELECT * FROM table LIMIT 10, 10;

This would return 10 records starting at record 10. This is useful for pagination. Suppose you have a large number of records, but want to display only, say, ten per page. Replace the 10 in the above query with a variable which increases by ten in a loop, returning groups of ten records, which can be placed on consecutive pages. This is how the search system on this site functions.

The default ORDER is ASC. To list the last, use DESC.

UPDATE

Suppose you wish to change the data in a column in a table. Select the record by a WHERE conditional, then use SET to replace whatever is currently in the column to a new value:

UPDATE table SET column_name=newvalue WHERE column1=value;

For example, in a table called 'users', user number (user_id) 12 has changed his telephone number. The query would run something like this:

UPDATE users SET tel='(41)092929292' WHERE user_id=12 LIMIT 1;

The 'LIMIT 1' at the end is not strictly necessary, but is good practice to avoid doing regrettable things such as resetting the data in a column down the entire table.

To replace the text of a column partially or wholly, use the REPLACE() function:

UPDATE table SET column = REPLACE(column, 'text_old', 'text_new');

DELETE

DELETE FROM table WHERE column=value;

This query will delete the record or records which satisfy the WHERE conditional. Again, setting the LIMIT will prevent unwelcomely enthusiastic outcomes.

TRUNCATE TABLE tablename will empty a table of all data. DROP TABLE tablename will delete the entire table (careful, always make regular backups of your database!).

ALIAS

SELECT user_name AS name FROM users_table AS ut WHERE ut.id=4;

Aliases are a shorthand way of referring to long column and table names. Notice the use of 'ut.id', indicating that the id is to be taken from the 'ut' (users_table).

Functions

SELECT *, FUNCTION(column) FROM table;

This query selects all records from the 'table' table and in the results applies a FUNCTION() to the 'column' nominated by name.

Text Functions

e.g.: SELECT CONCAT(t1, t2) FROM table;

MySQL can apply a number of predefined functions to elements of a returned array. In the example above, CONCAT(t1, t2) returns an array element containing t1t2.

A string may also be inserted, for example between two parts of a name:

e.g.: SELECT CONCAT(fname, ' ', lname) AS fullname FROM members;

The first name and second name are separated by a space and given an alias (optional) of a new array element called 'fullname'. This technique is useful for speeding up data handling and to make it more legible to other programmers, or yourself 2 weeks later.

FunctionReturns
CONCAT(x1, x2, ...)x1x2...
CONCAT_WS(S, x1, x2, ...)x1Sx2S...
LENGTH(x)length of x
LEFT(x, y)y characters to the left of x
RIGHT(x, y)y characters to the right of x
TRIM(x)any spaces removed either side of x
UPPER(x)x returned all upper case
LOWER(x)x returned all lower case
SUBSTRING(s, x, y)y characters from s starting with x
REGEXP()conducts a regular expression operation
NOT REGEXP()conducts a regular expression operation by exclusion

e.g. CONCAT_WS(' ', fname, lname) returns 'Freddy McGurty'. The _WS suffix adds a separator (in this example a blank space, but it could be a comma and/or any other string) between the returned elements. i.e. CONCAT_WS(', ', lname, fname) returns 'McGurty, Freddy'.

Numeric Functions

There are a number of mathematical operations which MySQL can perform on data:

FunctionReturns
FORMAT(n, x)Number formatted with commas every 103, to x decimal places
ROUND(n, x)n rounded to x decimal places
RAND()a random number between 0.0 and 1.0
ABS(n)the absolute value of n
CEILING(n)n rounded to the next highest integer
FLOOR(n)n as an integer (decimal places truncated)
MOD(n, x)The remainder after dividing n by x
POW(n, x)n to the xth power
SQRT(n)square root of n
Examples

FORMAT(123456, 2) → 1,234.56

SELECT * FROM table ORDER BY RAND() LIMIT 1; → selects all records in a random order, then returns the first

Time and Date

DATETIME and TIMESTAMP provide both the date and time of the creation of the record.If only a part of this is required, these functions may be used: HOUR(), MINUTE(), SECOND(), DAYNAME(), DAYOFMONTH(), MONTHNAME(), MONTH(), YEAR(), CURDATE(), CURTIME(), NOW(), UNIX_TIMESTAMP().

MONTH() returns the number of the month (1-12), CUR means current, NOW() returns both the current date and time, UNIX_TIMESTAMP() returns the number of seconds since Jan 1 1970 (epoch). The time is taken from the server, not the client computer.

SELECT DATE(birth) AS dob FROM members ORDER BY birth ASC LIMIT 1;

In this example, the table 'members' has a column called 'birth', containing dates of birth. The date of birth of the youngest member is returned as 'dob'. To return the oldest, change ASC to DESC.

DATETIME stores the full date in the format YYYY-MM-DD HH:MM:SS in the table.

DATE_FORMAT and TIME_FORMAT can be used to return this data in any desired format. Some examples:

SELECT DATE_FORMAT(registration_date, '%M %D, %Y') → September 19th, 2014

SELECT DATE_FORMAT(registration_date, '%e %b \'%y') → 19 Sep '14

SELECT DATE_FORMAT(registration_date, '%d.%c.%y') → 19.09.14

SELECT TIME_FORMAT(registration_date, '%r') → 07:54:42 PM

SELECT TIME_FORMAT(registration_date, '%T') → 19:54:42 PM

SELECT TIME_FORMAT(registration_date, '%l:%i %p') → 07:54 PM

SELECT TIME_FORMAT(registration_date, '%H.%i.%S') → 19:54:42

In this example, all the members will be listed, along with their date of registration in the format e.g. 19 Sep '14, in order of date of registration (oldest first).

SELECT name, DATE_FORMAT(registration_date, '%e %b \'%y') AS Date FROM members ORDER BY registration_date ASC;

All the date and time data is drawn from the server computer. If the client's computer timestamp is needed, PHP cannot be used. Instead, JavaScript can be used.

Content © Renewable-Media.com. All rights reserved. Created : June 4, 2014 Last updated :January 15, 2016

Latest Item on Science Library:

The most recent article is:

Trigonometry

View this item in the topic:

Vectors and Trigonometry

and many more articles in the subject:

Subject of the Week

Mathematics

Mathematics is the most important tool of science. The quest to understand the world and the universe using mathematics is as old as civilisation, and has led to the science and technology of today. Learn about the techniques and history of mathematics on ScienceLibrary.info.

Mathematics

Great Scientists

Niels Bohr

1885 - 1962

Niels Bohr, 1885 - 1962, was a Danish physicist, and founder of the Copenhagen School, which proposes as a consequence of quantum mechanics that there is no fundamental reality, a view much opposed by Albert Einstein.

Niels Bohr, 1885 - 1962. A Danish physicist
Transalpine traduzioni

Quote of the day...

Since light travels faster than sound, some people appear bright until you hear them speak.

ZumGuy Internet Promotions

Umwelt.Science