SQL REFERENCE (Page 2)
ALTER TABLE
CREATE INDEX
CREATE TABLE
CREATE VIEW
Data Types
DELETE Rows
DROP INDEX
DROP TABLE
GRANT
INSERT
SELECT

GRANT
Use the GRANT command to grant privileges to a user.

ClauseDescriptionRequired
GRANT privilege Indicates the privilege(s) to be granted. Yes
ON database object Indicates the database object(s) to which the privilege(s) pertain. Yes
TO username Indicates the user(s) to whom the the privilege(s) are to be granted. Yes

The following GRANT command grants the user Johnson the privilege of selcting rows from the Rep Table

  GRANT SELECT ON Rep TO Johnson
  ;

Back to Top


INSERT
Use the INSERT command to

ClauseDescriptionRequired
INSERT table name Indicates the name of the table to be altered. Yes
alteration Indicates the type of alteration to be performed. Yes

The following INSERT command would add one movie to the table created below:

CREATE TABLE movies (
  movie_id   NUMBER,
  title      VARCHAR2(30),
  director   VARCHAR2(30),
  actor      VARCHAR2(30),
  actress    VARCHAR2(30),
  company    VARCHAR2(30),
);
INSERT INTO movies VALUES ('231','JFK','Oliver Stone','Kevin Kostner','Sissy Spacek','Warner Bros.');

Back to Top


SELECT

Use the SELECT command to retrieve records from a database. The keywords used in a select query are summarized in the following table:

KeywordDescription
SELECT Retrieves fields from one or more tables.
FROM Tables containing the fields.
WHERE Criteria to restrict the records returned.
GROUP BY Determines how the records should be grouped.
HAVING Used with GROUP BY to specify the criteria for the grouped records.
ORDER BY Criteria for ordering the records.
LIMIT Limit the number of records returned.

The simplest SELECT query is to retrieve all records from a single table. The following example lists all fields from the movies table: SELECT * FROM movies;

To select specific fields from a table, you provide a comma-separated list of field names. The following example selects the title and director from the movies table: SELECT title, director FROM movies;

Limiting Records

The WHERE clause may be used to limit records. The following lists the comparison operators available with MySQL to limit the records returned with the WHERE clause.

KeywordDescription
= Equal to
<>> or != Not equal to
< Less than
<= Less than or equal to
< greater than
>= greater than or equal to
LIKE Used to compare strings
BETWEEN Checks for values between a range
IN Checks for values in a list
NOT IN Ensures the value is not in the list

When working with strings, the % character may be used as a wildcard. The following example retrieves all fields from the search table where the Keyword field contains the text, "cookies". SELECT * FROM search WHERE Keywords LIKE '%cookies%';

The underscore character may be used as a placeholder. The following example selects all records from the search table, where the Page name beings with 'P', followed by four characters (four underscores are used). SELECT * FROM search WHERE Page LIKE 'P____';

The BETWEEN clause may be used with numbers, dates and text. The following example retrieves all fields from Products, where the cost is between 1000 and 4000. SELECT * FROM Products WHERE cost BETWEEN 1000 AND 4000;

The OR clause may be used to specify a range of values to check against. The following example lists all records where the Category is either ASP or PHP in the search table. SELECT * FROM search WHERE Category = 'ASP' OR Category = 'PHP';

If you have many values that you want to check against, you can use the IN clause as it makes the code alot more readable. The following is the above statement using the IN clause. SELECT * FROM search WHERE Category IN ('ASP', 'PHP');

Similarly, you can use the NOT modifier with the IN clause to check for values that are not within the list. The following example returns all records where the Category is not equal to ASP or PHP SELECT * FROM search WHERE Category NOT IN ('ASP', 'PHP');

Joining Tables

Sometimes the data you require may come from two or more tables. Supposing our search table contained a foreign key called AuthorID that related to a primary key of the same name in an author table, we could retrieve records by linking the two fields. The following example lists the Surname and Forename from an author table, and the Directory and Page from the search table written by that author.

SELECT author.Surname, author.Forename, search.Directory, search.Page FROM search, author WHERE author.AuthorID = search.AuthorID;

You can add further clauses to the WHERE clause, using the AND operator. The following example extends the previous example to return only records where the Author has the Surname, Lemon.

listLemons.sql:

SELECT author.Surname, author.Forename, search.Directory, search.Page 
	FROM search, author 
	WHERE author.AuthorID = search.AuthorID AND author.Surname = 'Lemon';

The file may then be used with MySQL as follows:

\. listLemons.sql

Selecting Distinct Records

Our "search" table contains a list of pages in directories. If we were to list all directories from the table, we would end up with duplicate records, as there may be more than one page in a directory. The DISTINCT modifier may be used to ensure that one one record is returned for each Directory name. SELECT DISTINCT Directory FROM search;

Aggregate Functions

The GROUP BY modifier may be used to perform aggregate functions, such as COUNT records. The following example lists the distinct Directories, along with a count of how many records there are for that Directory in the search table. SELECT Directory, COUNT(*) FROM search GROUP BY Directory;

The AS modifier may be used to provide meaningful column names for the result. In the above example, the column headings from running the query are, Directoy and COUNT(*). The following example uses the column name Entries instead of COUNT(*). SELECT Directory, COUNT(*) AS Entries FROM search GROUP BY Directory;

If you want the column name to contain spaces, you must put the name in single quotes. The next example uses a column name of Number of Entries. SELECT Directory, COUNT(*) AS 'Number of Entries' FROM search GROUP BY Directory;

List of Aggregate Functions Available in MySQL

The following table contains a list of the aggregate function available in MySQL.

FunctionExampleDescription
AVG() SELECT AVG(cost) FROM Invoice GROUP BY ClientID; Returns the average value in a group of records. The example returns the average order for each customer.
COUNT() SELECT COUNT(cost) FROM Invoice GROUP BY ClientID; Returns the number of records in a group of records. The example returns the number of orders for each customer.
MAX() SELECT MAX(cost) FROM Invoice GROUP BY ClientID; Returns the largest value in a group of records. The example returns the largest order by each customer.
MIN() SELECT MIN(cost) FROM Invoice GROUP BY ClientID; Returns the lowest value in a group of records. The example returns the smallest order by each customer.
SUM() SELECT SUM(cost) FROM Invoice GROUP BY ClientID; Returns the sum of a field. The example returns the total amount spent by each customer.

The Having Clause

The WHERE clause is used to restrict records in a query. If you wish to restrict records from an aggregate function, you use the HAVING clause. The difference is that the HAVING clause restricts the records after they have been grouped. The following lists all customers who have spent over 20,000 on average. SELECT AVG(cost) FROM Invoice GROUP BY ClientID HAVING AVG(cost) > 20000;

The Order By Clause

The ORDER BY clause may be used to order the records returned. The following example lists all Pages in the search table in alphabetical order. SELECT Page FROM search ORDER BY Page;

The ORDER BY clause may use the ASC or DESC modifiers to determine if the records should be in ascending or descending order. If neither are provided, the records are shown in ascending order. The following example lists all Pages in the search table in descending order. SELECT Page FROM search ORDER BY Page DESC;

Limiting the Records Returned

The LIMIT clause may be used to limit the records returned by the SELECT statement. You specify the start row (start from zero), and the number of records returned. The following example lists the first 10 records from the search table. SELECT * FROM search LIMIT 0, 10;

The following example would retrieve the next 10 records from the search table. SELECT * FROM search LIMIT 10, 10;

Back to Top