9_Adv_Queries

Advanced Query Techniques
Peter Komisar © Conestoga College version 1.1

reference: MySQL Reference Manual. MySQL Structured Query
Language(SQL) ,D.Geller, R. Roselius, Danielle Waleri, DDC Publi.
Core MySQL, Leon Atkinson, Prentice-Hall Publishing, 'Joins Note,
http://www.udel.edu/evelyn/SQL-Class2/SQLclass2_Join.html

Comments

Below we show a script which is a set of SQL
commands which can be executed as a group.
Sometimes you might wish to provide comments
in a script or comment some commands out.

MySQL Comments

MySQL supplies three comment styles. These
are exampled below. This example is from 'Core
MySQL'.

MySQL Comment Types

# // the number symbol
-- // two hyphens
/* */ // C programming language comments

The latter of the three types listed above afford
multi-line comments.

Example // adapted from Core MySQL

SELECT NOW( ); # Get Current Time
SELECT NOW( ); -- Get Current Time again
/*
* Get the time,
* yet again!
*/
SELECT NOW( );

Execution

mysql> SELECT NOW( ); # Get Current Time
+---------------------+
| NOW( )              |
+---------------------+
| 2008-03-15 23:30:57 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT NOW( ); -- Get Current Time again
+---------------------+
| NOW( )              |
+---------------------+
| 2008-03-15 23:30:57 |
+---------------------+
1 row in set (0.00 sec)

mysql> /*
   /*> * Get the time,
   /*> * yet again!
   /*> */
mysql> SELECT NOW( );
+---------------------+
| NOW( )              |
+---------------------+
| 2008-03-15 23:30:57 |
+---------------------+
1 row in set (0.00 sec)

MySQL Data Insert into a Secondary Table
// also an example using comments

A problem you will likely encounter using MySQL
comes up when you try and insert data into a
child table that declares it's primary key is a
foreign key.

The following script found in 'Core MySQL' by Leon
Atkinson is used. This book was based on an older
version of MySQL, circa 3.x. The script doesn't run
as written in newer versions of MySQL which enforce
a foreign key constraint.

MySQL. it seems, is over zealously checking for rows
data being added to the secondary table. It's checking
facility can be turned off temporarily with the following
command.

Example

SET FOREIGN_KEY_CHECKS = 0;

The following script shows this command being used
and by the way, shows the three comment types in use.

Script // from 'Core MySQL'

CREATE TABLE item (
    ID INT(6) NOT NULL AUTO_INCREMENT,
    Name CHAR(32) NOT NULL,
    Description CHAR(255) DEFAULT 'No Description',

    PRIMARY KEY(ID),
    KEY(Name)
);

INSERT INTO item VALUES(1, 'Toothbrush',1.25, NULL );
INSERT INTO item VALUES(2, 'Comb', 2.50 ,NULL );
INSERT INTO item VALUES(3, 'Brush', 3.00, NULL );
INSERT INTO item VALUES(4, 'Toothpaste', .75, NULL);

CREATE TABLE item_option (
    ID INT(6) NOT NULL AUTO_INCREMENT,
    Item INT(6) NOT NULL,
    Name CHAR(32) NOT NULL,

PRIMARY KEY(ID) ,
FOREIGN KEY(Item) REFERENCES Item (ID)
);

SET FOREIGN_KEY_CHECKS = 0;

# gets around what appears to be a MySQL bug
/* remove the KEY_CHECK settings to see error */
-- just to show the third comment style!

INSERT INTO item_option VALUES ( 1, 2, 'Red Plastic') ;
INSERT INTO item_option VALUES ( 2, 2, 'Blue Plastic');

SET FOREIGN_KEY_CHECKS = 1; --restores foreign key checking

Execution

mysql> CREATE TABLE item (
    ->     ID INT(6) NOT NULL AUTO_INCREMENT,
    ->     Name CHAR(32) NOT NULL,
    ->     Description CHAR(255) DEFAULT 'No Description',
    ->
    ->     PRIMARY KEY(ID),
    ->     KEY(Name)
    -> );
Query OK, 0 rows affected (0.08 sec)

mysql>
mysql> INSERT INTO item VALUES(1, 'Toothbrush',1.25, NULL );
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> INSERT INTO item VALUES(2, 'Comb', 2.50 ,NULL );
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> INSERT INTO item VALUES(3, 'Brush', 3.00, NULL );
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> INSERT INTO item VALUES(4, 'Toothpaste', .75, NULL);
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql>
mysql> CREATE TABLE item_option (
    ->     ID INT(6) NOT NULL AUTO_INCREMENT,
    ->     Item INT(6) NOT NULL,
    ->     Name CHAR(32) NOT NULL,
    ->
    -> PRIMARY KEY(ID) ,
    -> FOREIGN KEY(Item) REFERENCES Item (ID)
    -> );
Query OK, 0 rows affected (0.08 sec)

mysql>
mysql> SET FOREIGN_KEY_CHECKS = 0;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> # gets around what appears to be a MySQL bug
mysql> /* remove the KEY_CHECK settings to see error */
mysql> -- just to show the third comment style!
mysql>
mysql> INSERT INTO item_option VALUES ( 1, 2, 'Red Plastic') ;
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO item_option VALUES ( 2, 2, 'Blue Plastic');
Query OK, 1 row affected (0.02 sec)

mysql>
mysql> SET FOREIGN_KEY_CHECKS = 1; --restores foreign key checking
Query OK, 0 rows affected (0.00 sec)



GROUP BY

(Aggregate) Functions

Many times tables are used to store statistical
data. The GROUP BY Function and a set of
functions called the Aggregate functions are
useful for querying such statistical tables.

A Mosquito Bite Table of Statistics

In the following script we create a table which
tracks mosquito bites reported in four small towns
in July and August of 2007.

CREATE TABLE mosquito_bites(
mosquito_bites_id INT(8) PRIMARY KEY AUTO_INCREMENT,
town CHAR(20),
day DATE,
number_of_bites INT(6)
);

INSERT INTO mosquito_bites VALUES

(1, 'Monticello', '2007-07-01', 7),
(2, 'Dundalk', '2007-07-01', 4),
(3, 'Palmerston', '2007-07-01', 3),
(4, 'Elmira', '2007-07-01', 6),
(5, 'Monticello', '2007-07-15', 11),
(6, 'Dundalk', '2007-07-15', 9),
(7, 'Palmerston', '2007-07-15', 11),
(8, 'Elmira', '2007-07-15', 15),
(9, 'Monticello', '2007-08-01', 15),
(10, 'Dundalk', '2007-08-01', 22),
(11, 'Palmerston', '2007-08-01', 14),
(12, 'Elmira', '2007-08-01', 12),
(13, 'Monticello', '2007-08-15', 19),
(14, 'Dundalk', '2007-08-15', 29),
(15, 'Palmerston', '2007-08-15', 13),
(16, 'Elmira', '2007-08-15', 11)
;

SELECT * FROM mosquito_bites;

mysql> CREATE TABLE mosquito_bites(
    -> mosquito_bites_id INT(8) PRIMARY KEY AUTO_INCREMENT,
    -> town CHAR(20),
    -> day DATE,
    -> number_of_bites INT(6)
    -> );
Query OK, 0 rows affected (0.09 sec)

mysql>
mysql> INSERT INTO mosquito_bites VALUES
    ->
    -> (1, 'Monticello', '2007-07-01', 7),
    -> (2, 'Dundalk', '2007-07-01', 4),
    -> (3, 'Palmerston', '2007-07-01', 3),
    -> (4, 'Elmira', '2007-07-01', 6),
    -> (5, 'Monticello', '2007-07-15', 11),
    -> (6, 'Dundalk', '2007-07-15', 9),
    -> (7, 'Palmerston', '2007-07-15', 11),
    -> (8, 'Elmira', '2007-07-15', 15),
    -> (9, 'Monticello', '2007-08-01', 15),
    -> (10, 'Dundalk', '2007-08-01', 22),
    -> (11, 'Palmerston', '2007-08-01', 14),
    -> (12, 'Elmira', '2007-08-01', 12),
    -> (13, 'Monticello', '2007-08-15', 19),
    -> (14, 'Dundalk', '2007-08-15', 29),
    -> (15, 'Palmerston', '2007-08-15', 13),
    -> (16, 'Elmira', '2007-08-15', 11)
    -> ;
Query OK, 16 rows affected (0.05 sec)
Records: 16 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM mosquito_bites;
+-------------------+------------+------------+-----------------+
| mosquito_bites_id | town       | day        | number_of_bites |
+-------------------+------------+------------+-----------------+
|                 1 | Monticello | 2007-07-01 |               7 |
|                 2 | Dundalk    | 2007-07-01 |               4 |
|                 3 | Palmerston | 2007-07-01 |               3 |
|                 4 | Elmira     | 2007-07-01 |               6 |
|                 5 | Monticello | 2007-07-15 |              11 |
|                 6 | Dundalk    | 2007-07-15 |               9 |
|                 7 | Palmerston | 2007-07-15 |              11 |
|                 8 | Elmira     | 2007-07-15 |              15 |
|                 9 | Monticello | 2007-08-01 |              15 |
|                10 | Dundalk    | 2007-08-01 |              22 |
|                11 | Palmerston | 2007-08-01 |              14 |
|                12 | Elmira     | 2007-08-01 |              12 |
|                13 | Monticello | 2007-08-15 |              19 |
|                14 | Dundalk    | 2007-08-15 |              29 |
|                15 | Palmerston | 2007-08-15 |              13 |
|                16 | Elmira     | 2007-08-15 |              11 |
+-------------------+------------+------------+-----------------+
16 rows in set (0.00 sec)

First we can use the SUM method on the table
as a whole. This tells us the total number of bites.

Example

SELECT SUM(number_of_bites) FROM mosquito_bites;

Execution

mysql> SELECT SUM(number_of_bites) FROM mosquito_bites;
+----------------------+
| SUM(number_of_bites) |
+----------------------+
|                  201 |
+----------------------+
1 row in set (0.00 sec)

To make the selection of the SUM on each town the
GROUP BY command can be used. Notice in both
the SUM and AVG functions, the rows are listed
largest to smallest based on their return values. To
maintain order, the ORDER BY clause may be used.

Example

SELECT mosquito_bites_id, town,SUM(number_of_bites) FROM mosquito_bites GROUP BY town;
Execution

mysql> SELECT mosquito_bites_id, town,SUM(number_of_bites)     -> FROM mosquito_bites GROUP BY town; +-------------------+------------+----------------------+ | mosquito_bites_id | town       | SUM(number_of_bites) | +-------------------+------------+----------------------+ |                 2 | Dundalk    |                   64 | |                 4 | Elmira     |                   44 | |                 1 | Monticello |                   52 | |                 3 | Palmerston |                   41 | +-------------------+------------+----------------------+ 4 rows in set (0.00 sec)Similarly we can use the the average function, AVG( ) to find the average number of bites per town. ExampleSELECT mosquito_bites_id, town,AVG(number_of_bites) FROM mosquito_bites GROUP BY town;
Execution

mysql> SELECT mosquito_bites_id, town,AVG(number_of_bites)
    -> FROM mosquito_bites GROUP BY town;
+-------------------+------------+----------------------+
| mosquito_bites_id | town       | AVG(number_of_bites) |
+-------------------+------------+----------------------+
|                 2 | Dundalk    |              16.0000 |
|                 4 | Elmira     |              11.0000 |
|                 1 | Monticello |              13.0000 |
|                 3 | Palmerston |              10.2500 |
+-------------------+------------+----------------------+
4 rows in set (0.03 sec)

Below the aggregate functions that are useful in statistical
analysis of groups are listed.

GROUP BY (Aggregate) Functions // from MySQL 5.0 manual

Function Name	Description
AVG( )	Return the average value of the argument
BIT_AND( )	Return bitwise and
BIT_OR( )	Return bitwise or
BIT_XOR( )*	Return bitwise xor
COUNT(DISTINCT)	Return the count of a number of different values
COUNT( )	Return a count of the number of rows returned
GROUP_CONCAT*	Return a concatenated string
MAX( )	Return the maximum value
MIN( )	Return the minimum value
STD( ), STDDEV( )	Return the population standard deviation
STDDEV_POP( )**	Return the population standard deviation
STDDEV_SAMP( )**	Return the sample standard deviation
SUM( )	Return the sum
VAR_POP( )**	Return the population standard variance
VAR_SAMP( )**	Return the sample variance
VARIANCE( )*	Return the population standard variance

* version 4.1 , **version 5.03
The LIMIT Modifier Our table is really very small from a statistical point of view. On a realistic table we might want to limit the number of rows that are process. In the following SELECT, the number of rows returned is limited to two. Also the ORDER BY clause is used to maintain the order according to ids. Notice there is a Microsoft 'good practice' used here where it is suggested to prefix the id column with the name of the table. Example mysql> SELECT mosquito_bites_id, town,AVG(number_of_bites)     -> FROM mosquito_bites GROUP BY town ORDER BY mosquito_bites_id LIMIT 2; +-------------------+------------+----------------------+ | mosquito_bites_id | town       | AVG(number_of_bites) | +-------------------+------------+----------------------+ |                 1 | Monticello |              13.0000 | |                 2 | Dundalk    |              16.0000 | +-------------------+------------+----------------------+ 2 rows in set (0.00 sec) The LIMIT command can also be used with two numbers, the first specifying the row number and the second the number of rows returned. In the following example the id value is set to 1 after which two rows are printed. that point. Example
SELECT mosquito_bites_id, town, AVG(number_of_bites)
FROM mosquito_bites GROUP BY town
ORDER BY mosquito_bites_id LIMIT 1,2;

Execution
mysql> SELECT mosquito_bites_id, town, AVG(number_of_bites)
    -> FROM mosquito_bites GROUP BY town
    -> ORDER BY mosquito_bites_id LIMIT 1,2;
+-------------------+------------+----------------------+
| mosquito_bites_id | town       | AVG(number_of_bites) |
+-------------------+------------+----------------------+
|                 2 | Dundalk    |              16.0000 |
|                 3 | Palmerston |              10.2500 |
+-------------------+------------+----------------------+
2 rows in set (0.00 sec)

Correlated Subqueries

"A correlated subquery is a subquery that contains a reference
to a table that also appears in the outer query." - MySQL 5.0 Manual

Example // from MySQL 5.0 Manual

SELECT * FROM t1 WHERE column1 = ANY
(SELECT column1 FROM t2 WHERE t2.column2 = t1.column2);

"Notice that the subquery contains a reference to a column of t1,
even though the subquery's FROM clause does not mention a table
t1. So, MySQL looks outside the subquery, and finds t1 in the outer
query."

MySQL Scoping rule

"MySQL evaluates from inside to outside, so nested
expressions are evaluated first."

In the following example, in the second nested SELECT
'manager_id' finds it's context in the outer SELECT
statement's reference to store.

The ANY KEYWORD

The ANY keyword, which must follow a comparison operator,
means “return TRUE if the comparison is TRUE for ANY of the
values in the column that the subquery returns.” That would
be true for 'any one'
match.

Example

SELECT manager_id,street,store_number,city
FROM store WHERE manager_id =ANY
    (SELECT manager_id
    FROM mgr_contacts
      WHERE store.city = mgr_contacts.city
    );

Execution

mysql> SELECT manager_id,street,store_number,city
    -> FROM store WHERE manager_id =ANY
    ->     (SELECT manager_id
    ->       FROM mgr_contacts
    ->       WHERE store.city = mgr_contacts.city
    ->     );
+------------+------------------+--------------+---------+
| manager_id | street           | store_number | city    |
+------------+------------------+--------------+---------+
|       1012 | 3099 College St. |         1010 | Toronto |
+------------+------------------+--------------+---------+
1 row in set (0.00 sec)

The ALL Keyword

The ALL Keyword is used like ANY however is used
to find all matches.

Example & Execution

mysql> SELECT manager_id,street,store_number,city
    -> FROM store WHERE manager_id =ALL
    ->     (SELECT manager_id
    ->       FROM mgr_contacts
    ->       WHERE store.city = mgr_contacts.city
    ->     );
+------------+----------------------+--------------+---------------+
| manager_id | street               | store_number | city          |
+------------+----------------------+--------------+---------------+
|       1893 | 1111 Rosemary Blvd. |         1001 | Victoria      |
|       4930 | 579a Delaware St.    |         1002 | Vancouver     |
|       2309 | 2 Shepard Rd.        |         1003 | Edmonton      |
|       1234 | 555 Queen St.        |         1004 | Calgary       |
|       9991 | 33 WheatField Ave.   |         1005 | Saskatoon     |
|       1029 | 4301 Main St. West   |         1006 | Regina        |
|       1111 | 1099 Portage Ave.    |         1007 | Winnipeg      |
|       2143 | 507 King St.         |         1008 | Brandon       |
|       1011 | 2044 Arlington Ave. |         1009 | Ottawa        |
|       1012 | 3099 College St.     |         1010 | Toronto       |
|       2019 | 62 Broadway Ave.     |         1013 | Orangeville   |
|       2222 | 444 Vincent Blvd.    |         1014 | Moncton       |
|       3345 | 305 Wade St.         |         1015 | Bathurst      |
|       2435 | 444 Governor Blvd.   |         1016 | Halifax       |
|       8876 | 486 Hyde St.         |         1017 | Sydney        |
|       1239 | 99 Victoria Ave.     |         1018 | Charlottetown |
|       6685 | 3 St. Laurent St.    |         1019 | St. John      |
|       9387 | 112 Marlee St.      |         1020 | Labrador City |
|       1212 | 488 Trembley        |         1021 | Montreal      |
|       2882 | 95 Baptiste Ave.     |         1022 | Quebec City   |
|       7665 | 777 Rue Fountaine    |         1023 | Hull          |
|       NULL | NULL                 |         9999 | NULL          |
+------------+----------------------+--------------+---------------+
22 rows in set (0.00 sec)

The SOME Keyword is Equivalent to ANY in '<> SOME'

Use of the word SOME is rare, but this example shows why it might be useful. To most people's ears, the English phrase “a
is not equal to any b” means “there is no b which is equal to a,” but that is not what is meant by the SQL syntax. The syntax means “there is some b to which a is not equal.” Using <> SOME instead helps ensure that everyone understands the
true meaning of the query. -MySQL Manual

EXISTS & NOT EXISTS

The following example at first glance is puzzling as
it seems to ignore the criteria where province ='NL'.

How EXISTS works is to evaluate to true if any matches
are made in the associated SELECT. ( Operationally,
it is similar to ANY.)

Here there is a match so this acts as a 'green' flag to
go ahead and execute the outer statment which, in this
case, is used to select all distinct values for the city
column of store.

Example

SELECT DISTINCT city FROM
WHERE EXISTS (SELECT * F
WHERE province='NL');

Execution

mysql> SELECT DISTINCT city FROM
    ->   WHERE EXISTS (SELECT * F
    ->                 WHERE province='NL');
+---------------+
| city          |
+---------------+
| Victoria      |
| Vancouver     |
| Edmonton      |
| Calgary       |
| Saskatoon     |
| Regina        |
| Winnipeg      |
| Brandon       |
| Ottawa        |
| Toronto       |
| Barrie        |
| Orangeville   |
| Moncton       |
| Bathurst      |
| Halifax       |
| Sydney        |
| Charlottetown |
| St. John      |
| Labrador City |
| Montreal      |
| Quebec City   |
| Hull          |
| NULL          |
+---------------+
23 rows in set (0.00 sec)

Combined SELECT Statements

We saw how MySQL supports the UNION
statement to effectively create the Full Outer
Join.

Different databases supply different sorts
of combination statements.

Generically speaking they can be described
as the following:

Combination Statements

UNION
INTERSECTION // aka INTERSECT
MINUS
UNION ALL

The class companion manual shows that Oracle
supports all these commands. As a part of the
assignment you are asked to determine which
of the list is supported in either MySQL or MSSQL.

SHOW Commands

The SHOW command is used to show a lot of different
aspects of the database. It is useful to list all the elements
that can be shown using the SHOW command.

SHOW Syntax
// about databases, tables, columns,
// or status information about the server.

SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [LIKE 'pattern']
SHOW CREATE DATABASE db_name
SHOW CREATE FUNCTION funcname
SHOW CREATE PROCEDURE procname
SHOW CREATE TABLE tbl_name
SHOW DATABASES [LIKE 'pattern']
SHOW ENGINE engine_name {LOGS | STATUS }
SHOW [STORAGE] ENGINES
SHOW ERRORS [LIMIT [offset,] row_count]
SHOW FUNCTION CODE sp_name
SHOW FUNCTION STATUS [LIKE 'pattern']
SHOW GRANTS FOR user
SHOW INDEX FROM tbl_name [FROM db_name]
SHOW INNODB STATUS
SHOW PROCEDURE CODE sp_name
SHOW PROCEDURE STATUS [LIKE 'pattern']
SHOW [BDB] LOGS
SHOW MUTEX STATUS
SHOW PRIVILEGES
SHOW [FULL] PROCESSLIST
SHOW PROFILE [types] [FOR QUERY n] [OFFSET n] [LIMIT n]
SHOW PROFILES
SHOW [GLOBAL | SESSION] STATUS [LIKE 'pattern']
SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern']
SHOW [OPEN] TABLES [FROM db_name] [LIKE 'pattern']
SHOW TRIGGERS
SHOW [GLOBAL | SESSION] VARIABLES [LIKE 'pattern']
SHOW WARNINGS [LIMIT [offset,] row_count]

Additional SHOW Commands
// provide information about replication master and slave servers

SHOW BINARY LOGS
SHOW BINLOG EVENTS
SHOW MASTER STATUS
SHOW SLAVE HOSTS
SHOW SLAVE STATUS

We used the SHOW CHARACTER SETS command in the
previous note. We can use the SHOW Columns command
to do the same job as the DESCRIBE command as is
exampled below.

Example

SHOW COLUMNS FROM store;

Execution

mysql> SHOW COLUMNS FROM store;
+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| STORE_NUMBER | int(11)      | NO   | PRI | 0       |       |
| MANAGER_ID   | int(11)      | YES |     | NULL    |       |
| STREET       | varchar(45) | YES |     | NULL    |       |
| CITY         | varchar(25) | YES |     | NULL    |       |
| PROVINCE     | char(2)      | YES |     | NULL    |       |
| POSTAL_CODE | char(6)      | YES |     | NULL    |       |
| AREA_CODE    | char(3)      | YES |     | NULL    |       |
| PHONE_NUMBER | char(7)      | YES |     | NULL    |       |
| GST_TAX_RATE | decimal(2,1) | YES |     | NULL    |       |
| PST_TAX_RATE | decimal(2,1) | YES |     | NULL    |       |
| HST_TAX_RATE | decimal(3,1) | YES |     | NULL    |       |
+--------------+--------------+------+-----+---------+-------+
11 rows in set (0.00 sec)

Using the FULL optional modifier which add security and
character set information.

Database Access Control

Creating MySQL Accounts and Granting Privileges

One of the SHOW functions is to show the security
privileges that are assigned to a database. It is
necessary to control the access that is available
to different users to the functions and commands
of a database.

The following list shows the different levels of
privilege that can be granted to users.

Privilege Levels

Privileges can be granted at several levels:

Global level

all databases on a given server
privileges are stored in the mysql.user table.
GRANT ALL ON *.* and
REVOKE ALL ON *.*

grant and revoke only global privileges.

Database level

all objects in a given database.
privileges are stored in the mysql.db and mysql.host tables.
GRANT ALL ON db_name.* and
REVOKE ALL ON db_name.*

grant and revoke only database privileges.

Table level

all columns in a given table
privileges are stored in the mysql.tables_priv table.
GRANT ALL ON db_name.tbl_name and
REVOKE ALL ON db_name.tbl_name

grant and revoke only table privileges.

Column level

apply to single columns in a given table.
privileges are stored in the mysql.columns_priv table.
Using REVOKE

specify the same columns that were granted.

Routine level

CREATE ROUTINE, ALTER ROUTINE
EXECUTE, and GRANT privileges
apply to stored routines (functions and procedures).
They can be granted at the global and database levels.
Also, except for CREATE ROUTINE, these privileges
can be granted at the routine level for individual routines
and are stored in the mysql.procs_priv table.

Creating User Accounts

Before privileges can be granted users have to
be created. This can be done a number of ways.
Users can be created, then granted privileges.
Alternatively, the GRANT command can be used
to create a user and simultaneously to grant
privileges. You can also modify the grant tables
directly.

You can create MySQL accounts in two ways:

Using CREATE USER or GRANT
Modifying the MySQL Grant tables directly

CREATE USER

The CREATE USER command is a recent addition
to MySQL, added in version 5.0.2. It has the following
syntax. You must have top level privileges to use it.

// top-level privileges as in the 'global CREATE USER'
// privilege or INSERT privilege for the mysql database.

CREATE USER, for each new user, creates a new
record in the mysql.user table that has no privileges.
(An error occurs if the account already exists.) For
more details see the MySQL Manual.

CREATE USER Syntax

CREATE USER user [IDENTIFIED BY [PASSWORD] 'password']
    [, user [IDENTIFIED BY [PASSWORD] 'password']] ...

DROP USER

If it is necessary to drop a user the DROP USER
command may be used. It has the following form.

DROP USER Syntax

DROP USER user [, user] ...

The DROP USER statement is used to removes one
or more MySQL accounts. To use the command, you
need the global CREATE USER privilege or the DELETE
privilege for the mysql database.

We now try an example. In the following example
execution combo, a user 'charlie' is created with
the password, 'brown'. The user 'charlie' is then
issued privileges on all tables in the database using
the wildcard syntax *.*. (More on the GRANT command
in a moment.)

Example and Execution // console output has been re-formatted

mysql> CREATE USER charlie@localhost IDENTIFIED BY 'brown';
Query OK, 0 rows affected (0.02 sec)

mysql> GRANT SELECT,INSERT,UPDATE,DELETE ON *.* TO 'charlie'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW Grants;
+--------------------------------------------------+
| Grants for root@localhost                        |
+--------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost'|
|IDENTIFIED BY PASSWORD                            |
|'*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B'       |
|WITH GRANT OPTION                                 |
+--------------------------------------------------+
1 row in set (0.00 sec)

mysql> show grants for charlie@localhost;
+--------------------------------------------------+
| Grants for charlie@localhost                    |
+--------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO   |
|'charlie'@'localhost' IDENTIFIED BY PASSWORD    |
|'*B2F28AC057FC6600B00B3D2D45B7179B8D0C396C'       |
+--------------------------------------------------+
1 row in set (0.00 sec)

mysql> DROP USER charlie@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for charlie@localhost;
ERROR 1141 (42000): There is no such grant defined for
user 'charlie' on host 'localhost'

The GRANT Statement

The GRANT statement is a very powerful and complex
SQL command. It can create users and grant privileges.

Usually a database is shared by many individuals with
varying access privileges. The GRANT command is
used to assign various privileges to different users.

Following is the Full Description of the GRANT Command
in MySQL. You can see the security can be backed by
used full strength encryption and digital signatures.

The latter part of the command show that limits can
be placed on the quantities of queries that can be
made by a specific user.

GRANT Command

GRANT priv_type [(column_list)] [, priv_type [(column_list)]] ...
    ON [object_type] {tbl_name | * | *.* | db_name.*}
    TO user [IDENTIFIED BY [PASSWORD] 'password']
        [, user [IDENTIFIED BY [PASSWORD] 'password']] ...
    [REQUIRE
        NONE |
        [{SSL| X509}]
        [CIPHER 'cipher' [AND]]
        [ISSUER 'issuer' [AND]]
        [SUBJECT 'subject']]
    [WITH with_option [with_option] ...]

object_type =
    TABLE
| FUNCTION
| PROCEDURE

with_option =
    GRANT OPTION
| MAX_QUERIES_PER_HOUR count
| MAX_UPDATES_PER_HOUR count
| MAX_CONNECTIONS_PER_HOUR count
| MAX_USER_CONNECTIONS count

GRANT Examples

Following is the grant condition for the single user
that enjoys all rights. The Grant statement automatically
creates the user if the user does not exist.

Example

mysql> show grants;

+----------------------------------------------+
| Grants for root@localhost                    |
+----------------------------------------------+
| GRANT ALL PRIVILEGES ON *.*                  |
| TO 'root'@'localhost' IDENTIFIED BY PASSWORD |
|'*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B'   |
|WITH GRANT OPTION                             |
+----------------------------------------------+
1 row in set (0.00 sec)

SuperUsers, LocalHost and AnyHost,%

Following are a couple examples from the MySQL
manual. The following two users, 'monty@localhost'
and 'monty@%' are super user accounts with full
privileges. The former, can be only be used when
connecting from the local host while the latter, can
connect from any other host.

MySQL Manual 5.0 Example

GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost'
IDENTIFIED BY 'some_pass' WITH GRANT OPTION;

// notice the single quotes on strings on either side of @ symbol

MySQL Manual 5.0 Example

GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%'
IDENTIFIED BY 'some_pass' WITH GRANT OPTION;

Execution // output reformatted

mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%'
    -> IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTs FOR 'monty'@'%';
+--------------------------------------------+
| Grants for monty@%                         |
+--------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%' |
| IDENTIFIED BY PASSWORD                     |
|'*BF06A06D69EC935E85659FCDED1F6A80426ABD3B' |
|WITH GRANT OPTION                           |
|--------------------------------------------+
1 row in set (0.00 sec)

For more details using the GRANT command see
the MySQL Manual.

Inserting New Users and Privileges

A final technique is to insert new users into the 'user'
table. This is a table in the 'mysql' database. You would
need to switch to this database.

Format of INSERT for the User Table

INSERT INTO user (Host,User,Password)
 VALUES('localhost','user3',PASSWORD('pass3'));

Privileges are inserted into the 'db' table.
The following DESCRIBE statement shows
the syntax used for the different privileges in
the fist column.

Description of the 'db' table holding Privileges

mysql> describe db;
+-----------------------+---------------+------+-----+---------+-------+
| Field                 | Type          | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host                  | char(60)      | NO   | PRI |         |       |
| Db                    | char(64)      | NO   | PRI |         |       |
| User                  | char(16)      | NO   | PRI |         |       |
| Select_priv           | enum('N','Y') | NO   |     | N       |       |
| Insert_priv           | enum('N','Y') | NO   |     | N       |       |
| Update_priv           | enum('N','Y') | NO   |     | N       |       |
| Delete_priv           | enum('N','Y') | NO   |     | N       |       |
| Create_priv           | enum('N','Y') | NO   |     | N       |       |
| Drop_priv             | enum('N','Y') | NO   |     | N       |       |
| Grant_priv            | enum('N','Y') | NO   |     | N       |       |
| References_priv       | enum('N','Y') | NO   |     | N       |       |
| Index_priv            | enum('N','Y') | NO   |     | N       |       |
| Alter_priv            | enum('N','Y') | NO   |     | N       |       |
| Create_tmp_table_priv | enum('N','Y') | NO   |     | N       |       |
| Lock_tables_priv      | enum('N','Y') | NO   |     | N       |       |
| Create_view_priv      | enum('N','Y') | NO   |     | N       |       |
| Show_view_priv        | enum('N','Y') | NO   |     | N       |       |
| Create_routine_priv   | enum('N','Y') | NO   |     | N       |       |
| Alter_routine_priv    | enum('N','Y') | NO   |     | N       |       |
| Execute_priv          | enum('N','Y') | NO   |     | N       |       |
+-----------------------+---------------+------+-----+---------+-------+
20 rows in set (0.00 sec)

An example of privileges being assigned is shown
in the following example. Nine columns are specified
and nine values are given, three strings and six
'Y' signifying 'yes'.

Example

mysql> INSERT INTO db
    ->     (Host,Db,User,Select_priv,Insert_priv,
    ->     Update_priv,Delete_priv,Create_priv,Drop_priv)
    ->     VALUES('localhost','bankaccount','custom',
    ->     'Y','Y','Y','Y','Y','Y');

FLUSH Privileges

The above statement would be typically followed
immediately by a FLUSH Privileges statement as
follows.

Example

mysql> FLUSH PRIVILEGES;

The MySQL manual states clearly why this is the
case.

" The reason for using FLUSH PRIVILEGES when you create
accounts with INSERT is to tell the server to re-read the grant
tables. Otherwise, the changes go unnoticed until you restart
the server."

You didn't see FLUSH used with GRANT for the following
reason.

"With GRANT, FLUSH PRIVILEGES is unnecessary."

Assignment

1 ) MSSQL users:

a ) What are the comment types supported
in Microsoft's SQL Server?

b) Which of the following commands are
supported in MSSQL?

i) UNION
ii) INTERSECTION // aka INTERSECT
iii) MINUS
iv) UNION ALL

c ) Does MSSQL support a GRANT command?

d) Does MSSQL support the SHOW command?

OR

1) MySQL users:

a ) What are the comment types supported
in MySQL Server?

b) Which of the following commands are
supported in MySQL?

i) UNION
ii) INTERSECTION // aka INTERSECT
iii) MINUS
iv) UNION ALL

2) Create a table with five students. Select
appropriate data types for a three column
table, holding name, Season and Mark.
Each student has a mark entry for Fall,
Winter and Spring.

Example

Bob Smith      Fall    79
Sally Sparrow Fall    86
Dave Davison   Fall    55
etc.

Bob Smith      Winter 90
etc.

Bob Smith Spring      66

a ) Create a SELECT statement the
shows the overall average of the whole
group, first for each individual season,
and then over the four seasons.

b) Use the GROUP BY command to show the
overall average of each individual student.

In your submission show your SQL
statements and console outputs.