Informatics Practices for Class XI (CBSE)/Simple queries

Note: Based on the Language chapter of the MySQL book.

Browsing the databases

The following SQL commands provide information about the databases located on the current server. The INFORMATION_SCHEMA table containing this information is SCHEMATA.

The mysqlshow command line tool can be used instead.

You can't show databases if the server has been started with the—skip-all-databases option.

If you don't have the 'SHOW DATABASES' privilege, you'll only see databases on which you have some permissions.

List databases

Show all databases:

SHOW DATABASES;

The SCHEMA keywords can be used in place of DATABASES. MySQL doesn't support standard SQL SCHEMAs, so SCHEMA is a synonym of database. It has been added for compatibility with other DBMS's.

Add a filter on the databases names

SHOW DATABASES LIKE 'pattern';

The LIKE operator here works as in normal SELECTs or DML statements. So you can list all databases whose name starts with 'my':

SHOW DATABASES LIKE'MY%';

Add complex filters

You can add more complex filters using the WHERE clause:

SHOW DATABASES WHERE (conditions);

WHERE clause allows you to use regular expressions, '<' and '>' operators, string functions or other useful expressions to filter the records returned by SHOW DATABASES.

List tables and views

The following SQL commands provide information about the tables and views contained in a database. The INFORMATION_SCHEMA tables containing this information are `TABLES` and `VIEWS`.

Since the following statements provide very little information about views, if you need to get metadata about them you'll probably prefer to query the VIEWS table.

The mysqlshow command line tool can be used instead.

Show all tables

USE database;
SHOW TABLES;

SHOW TABLES FROM database;

The 2 forms shown above are equivalent.

Apply a filter

You can apply a filter to the tables names, to show only tables whose name match a pattern. You can use the LIKE operatore, as you do in SELECTs or in the DML statements:

SHOW TABLES LIKE `pattern`;

Also, you can apply a more complex filter to any column returned by the SHOW TABLES command using the WHERE clause:

SHOW TABLES WHERE condition;

(see below)

Extra info

By default, SHOW TABLES returns only one column containing the name of the table. You can get extra information by using the FULL keyword:

SHOW FULL TABLES;

This will add a column called `Table_type`. This can have 3 values: 'BASE TABLE' for tables, 'VIEW' for views ans 'SYSTEM VIEW' for special tables created by the server (normally used only INFORMATION_SCHEMA tables).

So you can only list tables:

SHOW FULL TABLES WHERE `Table_type`='BASE TABLE';

Or, you can only list views:

SHOW FULL TABLES WHERE `Table_type`='VIEW';

Show only open tables

You can get a list of the non-temporary tables (not views) which are open in the cache:

SHOW OPEN TABLES;

This command has the same parameters as SHOW TABLES, except for FULL (useless in this case). You can't get this information from the INFORMATION_SCHEMA.

List fields

The following SQL commands provide information about the columns in a table or in a view. The INFORMATION_SCHEMA table containing this information is COLUMNS.

The mysqlshow command line tool can be used instead.

DESCRIBE

DESCRIBE `table`;
DESCRIBE `database`.`table`;
DESCRIBE `table` 'filter';

DESC can be used as a shortcut for DESCRIBE.

'filter' can be a column name. If a column name is specified, only that column will be shown. If 'filter' contains the '%' or the '_' characters, it will be evaluated as a LIKE condition. For example, you can list all fields which start with 'my':

DESC `table` 'my%';

SHOW COLUMNS

EXPLAIN `table`; --synonym
SHOW [FULL] FIELDS FROM `table`; -- synonym
SHOW COLUMNS FROM `table`; --synonym
SHOW COLUMNS FROM `table` FROM `database`;
SHOW COLUMNS FROM `table` LIKE 'pattern';
SHOW COLUMNS FROM `table` WHERE condition;

FIELDS and COLUMNS are synonyms. EXPLAIN is a synonym of SHOW COLUMNS / FIELDS too, but it doesn't support all of its clauses.

A databases name can be specified both in the form

SHOW COLUMNS FROM `table` FROM `database`;

both:

SHOW COLUMNS FROM `database`.`table`;

Extra info

Using the FULL keyword, extra info can be retried: the columns' collation, privileges you have on the column and the comment.

List indexes

The following SQL commands provide information about the indexes in a table. Information about keys is contained in the `COLUMNS` table in the INFORMATION_SCHEMA.

The mysqlshow -k command line tool can be used instead.

SHOW INDEX FROM `TABLE`;
SHOW INDEX FROM `TABLE` FROM `databases`;

The KEYS reserved word can be used as a synonym of INDEX. No other clauses are provided.

INFORMATION_SCHEMA

information_schema is a virtual database provided by MySQL 5 and later, that contains metadata about the server and the databases.

Specifying names

In this book, we will quote the MySQL identifiers (tables names, fields, databases, etc.) using backquotes (`).

Backquote is ASCII 96. It can be type on GNU/Linux systems by pressing: ALT+'.

Most often, this is optional. However, this allows better error messages from MySQL. For example, this error is not very helpful:

mysql> SELECT user_id, group_id FROM user,group LIMIT 1;
ERROR 1064 (42000): You have an error in your SQL syntax;
 check the manual that corresponds to your MySQL server version
 for the right syntax to use near 'group LIMIT 1' at line 1

But this one is better:

mysql> SELECT user_id, group_id FROM `user`,`group` LIMIT 1;
ERROR 1146 (42S02): Table 'savannah.group' doesn't exist

Ok, it was just a missing s:

mysql>  SELECT user_id, group_id FROM `user`,`groups` LIMIT 1;
+---------+----------+
| user_id | group_id |
+---------+----------+
|     100 |        2 |
+---------+----------+
1 row in set (0.02 sec)

This syntax allows the user to use reserver words and some illegal characters in objects' names. It is even possible to use backquotes by tying it twice:

RENAME TABLE `user` TO ````

However, this is not a portable syntax. The SQL standard recommends the use of a double quote ("). If you want to write portable SQL quote, do not quote the identifiers. But is there something like portable SQL, even remotely? ;)

Definitions: what are DDL, DML and DQL?

• DDL (Data Definition Language) refers to the CREATE, ALTER and DROP statements
• DML (Data Manipulation Language) refers to the INSERT, UPDATE and DELETE statements
• DQL (Data Query Language) refers to the SELECT, SHOW and HELP statements (queries)
• DCL (Data Control Language) refers to the GRANT and REVOKE statements

User Variables

Session Variables

• The ability to set variables in a statement with the := assignment operator:
• For e.g. (@total) to calculate the total in an example, you have to have the total column first because it must be calculated before the individual percentage calculations
• Session variables are set for the duration of the thread.
• In the vast majority of cases you'd use a programming language to do this sort of thing.
• Mysql variables can be useful when working on the Mysql command line.
• If no records are returned, the user variable will not be set for that statement.
• A user variable set in the field list cannot be used as a condition.

select @test := 2;
select @test + 1

• The value of a variable is set with the SET statement or in a SELECT statement with :=

set @startdate='some_start_date', @enddate='some_end_date'

SELECT @toremember:=count(*) FROM membros;

select @numzero := count(*) from table1 where field=0; 
select @numdistinct := count(distinct field) from table1 where field <> 0 ; 
select @numzero @numdistinct;

• You can copy values retrieved by a SELECT into one or more variables:

SELECT INTO

Global Variables

A global variable is visible to all users.

Alias

An expression and a column may be given aliases using AS. The alias is used as the expression's column name and can be used with order by or having clauses. For e.g.

SELECT 
    CONCAT(last_name,' ', first_name) AS full_name,
    nickname AS nick 
FROM
    mytable 
ORDER BY
    full_name

These aliases can be used in ORDER BY, GROUP BY and HAVING clauses. They should not be used in WHERE clause.

A table name can have a shorter name for reference using AS. You can omit the AS word and still use aliasing. For e.g.

SELECT
    COUNT(B.Booking_ID), U.User_Location 
FROM
    Users U
LEFT OUTER JOIN
    Bookings AS B
ON 
    U.User_ID    = B.Rep_ID AND
    B.Project_ID = '10'
GROUP BY
    (U.User_Location)

Aliasing plays a crucial role while you are using self joins. For e.g. people table has been referred to as p and c aliases!

SELECT
    p.name                                   AS parent,
    c.name                                   AS child,
    MIN((TO_DAYS(NOW())-TO_DAYS(c.dob))/365) AS minage
FROM
    people AS p 
LEFT JOIN
    people AS c 
ON
    p.name=c.parent WHERE c.name IS NOT NULL
GROUP BY
    parent HAVING minage > 50 ORDER BY p.dob;

Queries

SELECT

select syntax is as follows:

SELECT *
FROM table
WHERE condition
GROUP BY grouping field
HAVING group condition
ORDER BY order
LIMIT limit, offset

List of fields

You must specify what data you're going to retrieve in the SELECT clause:

SELECT DATABASE() -- returns the current db's name
SELECT CURRENT_USER() -- returns your username
SELECT 1+1 -- returns 2

Any SQL expression is allowed here.

You can also retrieve all fields from a table:

SELECT * FROM `stats`

If you SELECT only the necessary fields, the query will be faster.

The table's name

If you are retrieving results from a table or a view, usually you specify the table's name in the FORM clause:

SELECT id FROM `stats`—retrieve a field called id from a table called stats

Or:

SELECT MAX(id) FROM `stats`
SELECT id*2 FROM `stats`

You can also use the `db_name`.`table_name` syntax:

SELECT id FROM `sitedb`.`stats`

But you can also specify the table's name in the SELECT clause:

SELECT `stats`.`id`—retrieve a field called id from a table
SELECT `sitedb`.`stats`.`id`

WHERE

You can set a filter to decide what records must be retrieved.

For example, you can retrieve only the record which has an id of 42:

SELECT * FROM `stats` WHERE `id`=42

Or you can read more than one record:

SELECT * FROM `antiques` WHERE buyerid IS NOT NULL

GROUP BY

You can group all records by one or more fields. The record which have the same value for that field will be grouped in one computed record. You can only select the grouped record and the result of some aggregate functions, which will be computed on all records of each group.

For example, the following will group all records in the table `users` by the field `city`. For each group of users living in the same city, the maximum age, the minimum age and the average age will be returned:

SELECT city, MAX(age), MIN(age), AVG(age) GROUP BY `city`,

In the following example, the users are grouped by city and sex, so that we'll know the max, min and avg age of male/female users in each city:

SELECT city, sex, MAX(age), MIN(age), AVG(age) GROUP BY `city`, `sex`

HAVING

The HAVING clause declares a filter for the records which are computed by the GROUP BY clause. It's different from the WHERE clause, that operates before the GROUP BY. Here's what happens:

1. The records which match to the WHERE clause are retrieved
2. Those records are used to compute new records as defined in the GROUP BY clause
3. The new records that match to the HAVING conditions are returned

This means which WHERE decides what record are used to compose the new computed records.

HAVING decides what computed records are returned, so it can operate on the results of aggregate functions. HAVING is not optimized and can't use indexes.

Incorrect use of HAVING:

SELECT city, sex, MAX(age), MIN(age), AVG(age) GROUP BY `city` HAVING sex='m'

This probably gives a wrong results. MAX(age) and other aggregate calculations are made using all values, even if the record's sex value is 'f'. This is hardly the expected result.

Incorrect use of HAVING:

SELECT city, sex, MAX(age), MIN(age), AVG(age) GROUP BY `city`, `sex` HAVING sex='m'

This is correct and returns the expected results, but the execution of this query is not optimized. The WHERE clause can and should be used, because, so that MySQL doesn't computes records which are excluded later.

Correct use of HAVING:

SELECT city, sex, MAX(age), MIN(age), AVG(age) GROUP BY `city` HAVING MAX(age) > 80

It must group all records, because can't decide the max age of each city before the GROUP BY clause is execute. Later, it returns only the record with a MAX(age)>80.

ORDER BY

You can set an arbitrary order for the records you retrieve. The order may be alphabetical or numeric.

SELECT * FROM `stats` ORDER BY `id`

By default, the order is ASCENDING. You can also specify that the order must be DESCENDING:

SELECT * FROM `stats` ORDER BY `id` ASC—default
SELECT * FROM `stats` ORDER BY `id` DESC—inverted

NULLs values are considered as minor than any other value.

You can also specify the field position, in place of the field name:

SELECT `name`, `buyerid` FROM `antiques` ORDER BY 1 -- name
SELECT `name`, `buyerid` FROM `antiques` ORDER BY 2 -- buyerid
SELECT `name`, `buyerid` FROM `antiques` ORDER BY 1 DESC

SQL expressions are allowed:

SELECT `name` FROM `antiques` ORDER BY REVERSE(`name`)

You can retrieve records in a random order:

SELECT `name` FROM `antiques` ORDER BY RAND()

If a GROUP BY clause is specified, the results are ordered by the fields named in GROUP BY, unless an ORDER BY clause is present. You can even specify in the GROUP BY clause if the order must be ascending or descending:

SELECT city, sex, MAX(age) GROUP BY `city` ASC, `sex` DESC

If you have a GROUP BY but you don't want the records to be ordered, you can use ORDER BY NULL:

SELECT city, sex, MAX(age) GROUP BY `city`, `sex` ORDER BY NULL

LIMIT

You can specify the maximum of rows that you want to read:

SELECT * FROM `antiques` ORDER BY id LIMIT 10

This statement returns a maximum of 10 rows. If there are not 10 rows, it returns the number of rows you find. The limit clause is usually used with ORDER BY.

You can get a given number of random records:

SELECT * FROM `antiques` ORDER BY rand() LIMIT 1 -- one random record
SELECT * FROM `antiques` ORDER BY rand() LIMIT 3

You can specify how many rows should be skipped before starting return the record found. The first record is 0, not one:

SELECT * FROM `antiques` ORDER BY id LIMIT 10
SELECT * FROM `antiques` ORDER BY id LIMIT 0, 10—synonym

You can use the LIMIT clause to get the pagination of results:

SELECT * FROM `antiques` ORDER BY id LIMIT 0, 10—first page
SELECT * FROM `antiques` ORDER BY id LIMIT 10, 10—second page
SELECT * FROM `antiques` ORDER BY id LIMIT 20, 10—third page

Also, the following syntax is accepted:

SELECT * FROM `antiques` ORDER BY id LIMIT 10 OFFSET 10

You can LIMIT to check the syntax of a query without waiting it returns the results:

SELECT ... LIMIT 0

Optimization tips:

• SQL_CALC_FOUND_ROWS speeds down the LIMIT.
• LIMIT is particularly useful for SELECTs which use ORDER BY, DISTINCT and GROUP BY, because their calculations doesn't have to involve all the rows.
• If the query is resolved by the server copying internally the results into a temporary table, LIMIT helps MySQL to calculate how many memory is required by the table.

DISTINCT

The DISTINCT keyword can be used to remove all duplicate rows from the resultset:

SELECT DISTINCT * FROM `stats`—no duplicate rows
SELECT DISTINCTROW * FROM `stats`—synonym
SELECT ALL * FROM `stats`—duplicate rows returned (default)

You can use it to get the list of all values contained in one field:

SELECT DISTINCT `type` FROM `antiques` ORDER BY `type`

Or you can use it to get the existing combinations of some values:

SELECT DISTINCT `type`, `age` FROM `antiques` ORDER BY `type`

If one of the fields you are SELECTing is the PRIMARY KEY or has a UNIQUE index, DISTINCT is useless. Also, it's useless to use DISTINCT in conjunction with the GROUP BY clause.

IN and NOT IN

SELECT id
FROM stats
WHERE position IN ('Manager', 'Staff')

SELECT ownerid, 'is in both orders & antiques'
FROM orders, antiques WHERE ownerid = buyerid
UNION
SELECT buyerid, 'is in antiques only'
FROM antiques WHERE buyerid NOT IN (SELECT ownerid FROM orders)

EXISTS and ALL

(Compatible: Mysql 4+)

SELECT ownerfirstname, ownerlastname
FROM owner 
WHERE EXISTS (SELECT * FROM antiques WHERE item = 'chair')

SELECT buyerid, item 
FROM antiques
WHERE price = ALL (SELECT price FROM antiques)

Optimization hints

There are some hints you may want to give to the server to better optimize the SELECTs. If you give more than one hints, the order of the keywords is important:

SELECT [ALL | DISTINCT | DISTINCTROW ]
   [HIGH_PRIORITY] [STRAIGHT_JOIN]
   [SQL_SMALL_RESULT | SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
   [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
   ...

HIGH_PRIORITY

Usually, DML commands (INSERT, DELETE, UPDATE) have higher priority than SELECTs. If you specify HIGH_PRIORITY though, the SELECT will have higher priority than DML statements.

STRAIGHT_JOIN Force MySQL to evaluate the tables of a JOIN in the same order they are named, from the leftmost.

SQL_SMALL_RESULT It's useful only while using DISTINCT or GROUP BY. Tells the optimizer that the query will return few rows.

SQL_BIG_RESULT It's useful only while using DISTINCT or GROUP BY. Tells the optimizer that the query will return a many rows.

SQL_BUFFER_RESULT Force MySQL to copy the result into a temporary table. This is useful to remove LOCKs as soon as possible.

SQL_CACHE Forces MySQL to copy the result into the query cache. Only works if the value of query_cache_type is DEMAND or 2.

SQL_NO_CACHE Tells MySQL not to cache the result. Useful if the query occurs very seldom or if the result often change.

SQL_CALC_FOUND_ROWS Useful if you are using the LIMIT clause. Tells the server to calculate how many rows would have been returned if there were no LIMIT. You can retrieve that number with another query:

SELECT SQL_CALC_FOUND_ROWS * FROM `stats` LIMIT 10 OFFSET 100;
SELECT FOUND_ROWS();

UNION and UNION All

(Compatible: Mysql 4+)

Following query will return all the records from both tables.

SELECT * FROM English 
UNION ALL
SELECT * FROM Hindi

UNION is the same as UNION DISTINCT.
If you type only UNION, then it is considered that you are asking for distinct records. If you want all records, you have to use UNION ALL.

SELECT word FROM word_table WHERE id = 1
UNION
SELECT word FROM word_table WHERE id = 2

(SELECT magazine FROM pages)
UNION DISTINCT
(SELECT magazine FROM pdflog)
ORDER BY magazine

(SELECT ID_ENTRY FROM table WHERE ID_AGE = 1)
UNION DISTINCT 
(SELECT ID_ENTRY FROM table WHERE ID_AGE=2)

Data manipulation

INSERT

The syntax is as follows:

Insert value1 into Column1, value2 into Column2, and value3 into Column3:

INSERT INTO TableName (Column1, Column2, Column3)
VALUES (value1, value2, value3)

Insert one record (values are inserted in the order that the columns appear in the database):

INSERT INTO TableName
VALUES (value1, value2, value3)

Insert two records:

INSERT INTO TableName
VALUES (value1, value2, value3), (value4, value5, value6)

INSERT INTO antiques VALUES (21, 01, 'Ottoman', 200.00);
INSERT INTO antiques (buyerid, sellerid, item) VALUES (01, 21, 'Ottoman');

You can also insert records 'selected' from other table.

INSERT INTO table1(field1, field2)
SELECT field1, field2
FROM table2

INSERT INTO World_Events SELECT * FROM National_Events

Performance tips:

• To insert many rows, consider using LOAD DATA INFILE instead.
• If bulk INSERTs are too slow and they operate on indexed non-empty tables, maybe you should increase the value of bulk_insert_buffer_size.
• Before performing bulk inserts, you may want to disable thekeys.
• LOCKing a table also speeds up the INERT.

UPDATE

The syntax is:

UPDATE table SET field = newvalue WHERE criteria ORDER BY field LIMIT n

Examples are:

UPDATE owner SET ownerfirstname = 'John'
  WHERE ownerid = (SELECT buyerid FROM antiques WHERE item = 'Bookcase');

UPDATE antiques SET price = 500.00 WHERE item = 'Chair';

UPDATE order SET discount=discount * 1.05

UPDATE tbl1 JOIN tbl2 ON tbl1.ID = tbl2.ID
  SET tbl1.col1 = tbl1.col1 + 1
  WHERE tbl2.status='Active'

UPDATE tbl SET names = REPLACE(names, 'aaa', 'zzz')

UPDATE products_categories AS pc
  INNER JOIN products AS p ON pc.prod_id = p.id
  SET pc.prod_sequential_id = p.sequential_id

UPDATE table_name SET col_name =
  REPLACE(col_name, 'host.domain.com', 'host2.domain.com')

UPDATE posts SET deleted=True
  ORDER BY date LIMIT 1

With ORDER BY you can order the rows before updating them, and only update a given number of rows (LIMIT).

It is currently not possible to update a table while performing a subquery on the same table. For example, if I want to reset a password I forgot in SPIP:

mysql> UPDATE spip_auteurs SET pass =
 (SELECT pass FROM spip_auteurs WHERE login='paul') where login='admin';
ERROR 1093 (HY000): You can't specify target table 'spip_auteurs' for update in FROM clause

TODO: [1] describes a work-around that I couldn't make to work with MySQL 4.1. Currently the work-around is not use 2 subqueries, possibly with transactions.

Performance tips

• UPDATEs speed depends of how many indexes are updated.
• If you UPDATE a MyISAM table which uses dynamic format, if you make rows larger they could be splitted in more than one part. This causes reading overhead. So, if your applications often do this, you may want to regularly run an OPTIMIZE TABLE statement.
• Performing many UPDATEs all together on a LOCKed table is faster than performing them individually.

REPLACE

REPLACE works exactly like INSERT, except that if an old record in the table has the same value as a new record for a PRIMARY KEY or a UNIQUE index, the old record is deleted before the new record is inserted.

With IGNORE, invalid values are adjusted to the closest values and inserted; warnings are produced but the statement does not abort.

Prior to MySQL 4.0.1, INSERT ... SELECT implicitly operates in IGNORE mode. As of MySQL 4.0.1, specify IGNORE explicitly to ignore records that would cause duplicate-key violations.

DELETE

DELETE [QUICK] FROM table1
TRUNCATE table1

• If you don't use a WHERE clause with DELETE, all records will be deleted.
• It can be very slow in a large table, especially if the table has many indexes.
• If the table has many indexes, you can make the cache larger to try making the DELETE faster (key_buffer_size variable).
• For indexed MyISAM tables, in some cases DELETEs are faster if you specify the QUICK keyword (DELETE QUICK FROM ...). This is only useful for tables where DELETEd index values will be reused.
• TRUNCATE will delete all rows quickly by DROPping and reCREATE-ing the table (not all Storage Engines support this operation).
• TRUNCATE is not transaction-safe nor lock-safe.
• DELETE informs you how many rows have been removed, but TRUNCATE doesn't.
• After DELETing many rows (about 30%), an OPTIMIZE TABLE command should make next statements faster.

DELETE FROM `antiques`
  WHERE item = 'Ottoman'
  ORDER BY `id`
  LIMIT 1

You can order the rows before deleting them, and then delete only a given number of rows.

Table manipulation

CREATE TABLE

Create table syntax is: Create table tablename (FieldName1 DataType,
FieldName2 DataType)

The rows returned by the "select" query can be saved as a new table. The datatype will be the same as the old table. For e.g. CREATE TABLE LearnHindi
select english.tag, english.Inenglish as English, hindi.Inhindi as Hindi
FROM English, Hindi
WHERE english.tag = hindi.tag

ALTER TABLE

ALTER TABLE command can be used when you want to add / delete /modify the columns and / or the indexes; or, it can be used to change other table properties.

Add a column:

ALTER TABLE awards
ADD COLUMN AwardCode int(2)

Modify a column:

ALTER TABLE awards
CHANGE COLUMN AwardCode VARCHAR(2) NOT NULL

ALTER TABLE awards
MODIFY COLUMN AwardCode VARCHAR(2) NOT NULL

Drop a column:

ALTER TABLE awards
DROP COLUMN AwardCode

Re-order the record in a table:

ALTER TABLE awards ORDER BY id

(this operation is only supported by some Storage Engines; it could make some query faster)

Renaming a table

In order to rename a table, you must have ALTER and DROP privileges on the old table name (or on all the tables), and CREATE and INSERT privileges on the new table name (or on all the tables).

You can use ALTER TABLE to rename a table:

RENAME TABLE `old_name` TO `new_name`

You can rename more than one table with a single command:

RENAME TABLE `old1` TO `new1`, `old2` TO `new2`, ...

RENAME is a shortcut. You can also use the ALTER TABLE statement:

ALTER TABLE `old` RENAME `new`

Using ALTER TABLE you can only rename one table per statement, but it's the only way to rename temporary tables.

DROP TABLE

DROP TABLE `awards`

Will completely delete the table and all the records it contains.

You can also drop more than one table with a single statement:

DROP TABLE `table1`, `table2`, ...

There are come optional keywords:

DROP TEMPORARY TABLE `table`;
DROP TABLE `table` IF EXISTS;

TEMPORARY must be specified, to drop a temporary table. IF EXISTS tells the server that it must not raise an error if the table doesn't exist.

Using NULL

Null is a special logical value in SQL. Most programming languages have 2 values of logic: True and False. SQL also has NULL which means "Unknown". A NULL value can be set.

NULL is a non-value, so it can be assigned to TEXT columns, INTEGER columns or any other datatype. A column can not contain NULLs only if it has been declared as NOT NULL (see ALTER TABLE).

INSERT into Singer
       (F_Name, L_Name, Birth_place, Language) 
       values 
       ("", "Homer", NULL, "Greek"),
       ("", "Sting", NULL, "English"),
       ("Jonny", "Five", NULL, "Binary");

Do not quote the NULL. If you quote a Null then you name the person NULL. For some strange reason, NULLs do not show visually on windows XP in Varchar fields but they do in Fedora's version, so versions of mysql can give different outputs. Here we set the value of Sting and Homer's first name to a zero length string "", because we KNOW they have NO first name, but we KNOW we do not know the place they were born. To check for a NULLs use

SELECT * from Singer WHERE Birth_place IS NULL;
or
SELECT * from Singer WHERE Birth_place IS NOT NULL;
or
SELECT * from Singer WHERE isNull(Birth_place)

Remember, COUNT never counts NULLS.

select count(Birth_place) from Singer;
0
and sum(NULL) gives a NULL answer.

Normal operations (comparisons, expressions...) return NULL if at least one of the compared items is NULL:

SELECT (NULL=NULL) OR (NULL<>NULL) OR (NOT NULL) OR (1<NULL) OR (1>NULL) OR (1 + NULL) OR (1 LIKE NULL)

because all the expressions between in parenthesis return NULL. It's definitely logical: if you don't know the value represented by NULL, you don't know is it's =1 or <>1. Be aware that even (NULL=NULL and (NOT NULL) return NULL.

Dealing with NULL

The function 'COALESCE' can simplify working with null values. for example, to avoid showing null values by treating null as zero, you can type:

SELECT COALESCE(colname,0) from table where COALESCE(colname,0) > 1;

In a date field, to treat NULL as the current date:

ORDER BY (COALESCE(TO_DAYS(date),TO_DAYS(CURDATE()))-TO_DAYS(CURDATE()))

EXP(SUM(LOG(COALESCE(*the field you want to multiply*,1))) 

The coalesce() function is there to guard against trying to calculate the logarithm of a null value and may be optional depending on your circumstances.

SELECT t4.gene_name, COALESCE(g2d.score,0), 
COALESCE(dgp.score,0), COALESCE(pocus.score,0) 
FROM t4 
LEFT JOIN g2d ON t4.gene_name=g2d.gene_name 
LEFT JOIN dgp ON t4.gene_name=dgp.gene_name 
LEFT JOIN pocus ON t4.gene_name=pocus.gene_name;

Use of IFNULL() in your SELECT statement is to make the NULL any value you wish.

IFNULL(expr1,expr2)

If expr1 is not NULL, IFNULL() returns expr1, else it returns expr2.

IFNULL() returns a numeric or string value, depending on the context in which it is used:

mysql> SELECT IFNULL(1,0);
-> 1
mysql> SELECT IFNULL(NULL,10);
-> 10
mysql> SELECT IFNULL(1/0,10);
-> 10
mysql> SELECT IFNULL(1/0,'yes');
-> 'yes'

Null handling can be very counter intuitive and could cause problems if you have an incorrect function in a delete statement that returns null. For example, the following query will delete all entries.

DELETE FROM my_table WHERE field > NULL (or function returning NULL)

If you want to have NULL values presented last when doing an ORDER BY, try this:

SELECT * FROM my_table ORDER BY ISNULL(field), field [ ASC | DESC ]

Reserved Words

Difficult Column Names, Like `DATE`—use backtick. If using "date" as a column name, enclose it in backticks ` as follows:

CREATE TABLE IF NOT EXISTS stocks (
  pkey int NOT NULL auto_increment,
  `date` date,
  ticker varchar(5),
  open decimal (9,2),
  high decimal (9,2),
  low decimal (9,2),
  close decimal (9,2),
  volume int,
  timeEnter timestamp(14),
  PRIMARY KEY (pkey)
);

Data Types

varchar

VARCHAR is shorthand for CHARACTER VARYING. 'n' represents the maximum column length (up to 255 characters) char(n) is similar to varchar(n) with the only difference that char will occupy fixed length of space in the database whereas varchar will need the space to store the actual text. For example, a VARCHAR(10) column can hold a string with a maximum length of 10 characters. The actual storage required is the length of the string (L), plus 1 byte to record the length of the string. For the string 'abcd', L is 4 and the storage requirement is 5 bytes.

text

A BLOB or TEXT column with a maximum length of 65,535 characters.

integer

Specifying an n value has no effect whatsoever. Regardless of a supplied value for n, maximum (unsigned) value stored is 429 crores. If you want to add negative numbers, add the "signed" keyword next to it.

decimal

decimal(n,m) decimal(4,2) means numbers up to 99.99 (and NOT 9999.99 as you may expect) can be saved. Four digits with the last 2 reserved for decimal.

Dates

Out of the three types DATETIME, DATE, and TIMESTAMP, the DATE type is used when you need only a date value, without a time part. MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format. The DATETIME type is used when you need values that contain both date and time information. The difference between DATETIME and TIMESTAMP is that the TIMESTAMP range is limited to 1970-2037 (see below).

TIME can be used to only store the time of day (HH:MM:SS), without the date. It can also be used to represent a time interval (for example: -02:00:00 for "two hours in the past"). Range: '-838:59:59' => '838:59:59'.

YEAR can be used to store the year number only.

If you manipulate dates, you have to specify the actual date, not only the time - that is, MySQL will not automagically use today as the current date. On the contrary, MySQL will even interpret the HH:MM:SS time as a YY:MM:DD value, which will probably be invalid.

The following examples show the precise date range for Unix-based timestamps, which starts at the Unix Epoch and stops just before the first new year before the $2^{31}-1$ usual limit (2038).

mysql> SET time_zone = '+00:00'; -- GMT
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT FROM_UNIXTIME(-1);
+-------------------+
| FROM_UNIXTIME(-1) |
+-------------------+
| NULL              |
+-------------------+
1 row in set (0.00 sec)

mysql> SELECT FROM_UNIXTIME(0); -- "Epoch"
+---------------------+
| FROM_UNIXTIME(0)    |
+---------------------+
| 1970-01-01 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT FROM_UNIXTIME(2145916799);
+---------------------------+
| FROM_UNIXTIME(2145916799) |
+---------------------------+
| 2037-12-31 23:59:59       |
+---------------------------+
1 row in set (0.00 sec)

mysql> SELECT FROM_UNIXTIME(2145916800);
+---------------------------+
| FROM_UNIXTIME(2145916800) |
+---------------------------+
| NULL                      |
+---------------------------+
1 row in set (0.00 sec)

set and enum

A SET datatype can hold any number of strings from a predefined list of strings specified during table creation. The SET datatype is similar to the ENUM datatype in that they both work with predefined sets of strings, but where the ENUM datatype restricts you to a single member of the set of predefined strings, the SET datatype allows you to store any of the values together, from none to all of them.

Operators

MySQL uses some standard SQL operators and some non-standard operators. They can be used to write expressions which involve constant values, variables, values contained in fields and / or other expressions.

Precedence

Operator precedence

Table of operator precedence:

INTERVAL
BINARY, COLLATE
!
- (unary minus), ~ (unary bit inversion)
^
*, /, DIV, %, MOD
-, +
<<, >>
&
|
=, <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, IN
BETWEEN, CASE, WHEN, THEN, ELSE
NOT
&&, AND
XOR
||, OR
:=

Modifiers:

• PIPES_AS_CONCAT - If this SQL mode is enabled, || has precedence on ^, but - and ~ have precedence on ||.
• HIGH_NOT_PRECEDENCE - If this SQL mode is enabled, NOT has the same precedence level as !.

Use of parenthesis

You can use parenthesis to force MySQL to evaluate a subexpression before another independently from operator precedence:

SELECT (1 + 1) * 5 -- returns 10

You can also use parenthesis to make an expression more readable by humans, even if they don't affect the precedence:

SELECT 1 + (2 * 5) -- the same as 1 + 2 * 5

Assignment operators

You can use the = operator to assign a value to a column:

UPDATE `myTable` SET `uselessField`=0

When you want to assign a value to a variable, you must use the := operator, because the use of = would be ambiguous (is it as assignment or a comparison?)

SELECT @myvar := 1

You can also use SELECT INTO to assign values to one or more variables.

Comparison operators

Equality

If you want to check if 2 values are equal, you must use the = operator:

SELECT True = True—returns 1
SELECT True = False—returns 0

If you want to check if 2 values are different, you can use the <> or != operators, which have the same meaning:

SELECT True <> False—returns 1
SELECT True != True—returns 0

<> return 1 where = returns 0 and vice versa.

IS and NULL-safe comparison

When you compare a NULL value with a non-NULL value, you'll get NULL. If you want to check if a value is null, you can use IS:

SELECT (NULL IS NULL) -- returns 1
SELECT (1 IS NULL) -- returns 0
SELECT (True IS True) -- returns an error!

You can check if a value is non-NULL:

SELECT (True IS NOT NULL) -- returns 1

There is also an equality operator which considers NULL as a normal value, so it returns 1 (not NULL) if both values are NULL and returns 0 (not NULL) if one of the values is NULL:

SELECT col1 <=> col2 FROM myTable

There is not a NULL-safe non-equality operator, but you can type the following:

SELECT NOT (col1 <=> col2) FROM myTable

IS and boolean comparisons

IS and IS NOT can also be used for boolean comparisons. You can use them with the reserved words TRUE, FALSE and UNKNOWN (which is merely a synonym for NULL).

SELECT 1 IS TRUE—returns 1
SELECT 1 IS NOT TRUE—returns 0
SELECT 1 IS FALSE—returns 0
SELECT (NULL IS NOT FALSE) -- returns 1: unknown is not false
SELECT (NULL IS UNKNOWN) -- returns 1
SELECT (NULL IS NOT UNKNOWN) -- returns 0

Greater, Less...

You can check if a value is greater than another value:

SELECT 100 > 0 -- returns 1
SELECT 4 > 5 -- return 0

You can also check if a value is minor than another value:

SELECT 1 < 2 -- returns 1
SELECT 2 < 2 -- returns 0

This kind of comparisons also works on TEXT values:

SELECT 'a' < 'b' -- returns 1

Generally speaking, alphabetical order is used for TEXT comparisons. However, the exact rules are defined by the COLLATION used. A COLLATION defines the sorting rules for a given CHARACTER SET. For example, a COLLATION may be case-sensitive, while another COLLATION may be case-insensitive.

You can check if a value is equal or greater than another value. For example, the following queries have the same meaning:

SELECT `a` >= `b` FROM `myTable`
SELECT NOT (`a` < `b`) FROM `myTable`

Similarly, you can check if a value is less or equal to another value:

SELECT `a` <= `b` FROM `myTable`

BETWEEN

If you want to check if a value is included in a given range, you can use the BETWEEN ... AND ... operator. AND doesn't have its usual meaning. Example:

SELECT 20 BETWEEN 10 AND 100—returns 1

The value after BETWEEN and the value after AND are included in the range.

You can also use NOT BETWEEN to check if a value is not included in a range:

SELECT 8 NOT BETWEEN 5 AND 10—returns 0

IN

You can use the IN operator to check if a value is included in a list of values:

SELECT 5 IN (5, 6, 7) -- returns 1
SELECT 1 IN (5, 6, 7) -- returns 0

You should not include in the list both numbers and strings, or the results may be unpredictable. If you have numbers, you should quote them:

SELECT 4 IN ('a', 'z', '5')

There is not a theorical limit to the number of values included in the IN operator.

You can also use NOT IN:

SELECT 1 NOT IN (1, 2, 3) -- returns 0

Logical operators

MySQL boolean logic

MySQL doesn't have a real BOOLEAN datatype.

FALSE is a synonym for 0. Empty strings are considered as FALSE in a boolean context.

TRUE is a synonym for 1. All non-NULL and non-FALSE data are considered as TRUE in a boolean context.

UNKNOWN is a synonym for NULL. The special date 0/0/0 is NULL.

NOT

NOT is the only operator which has only one operand. It returns 0 if the operand is TRUE, returns 1 if the operand is FALSE and returns NULL if the operand is NULL.

SELECT NOT 1 -- returns 0
SELECT NOT FALSE—returns 1
SELECT NOT NULL—returns NULL
SELECT NOT UNKNOWN—returns NULL

! is a synonym for NOT.

SELECT !1

AND

AND returns 1 if both the operands are TRUE, else returns 0; if one of the operands is NULL, returns NULL.

SELECT 1 AND 1 -- returns 1
SELECT 1 AND  -- return 0
SELECT  AND NULL—returns NULL

&& is a synonym for AND.

SELECT 1 && 1

OR

OR returns TRUE if at least one of the operands is TRUE, else returns FALSE; if one of the operands is NULL, returns NULL.

SELECT TRUE OR FALSE—returns 1
SELECT 1 OR 1 -- returns 1
SELECT FALSE OR FALSE—returns 0
SELECT NULL OR TRUE—returns NULL

|| is a synonym for OR.

SELECT 1 || 0

XOR

XOR (eXclusive OR) returns 1 if only one of the operands is TRUE and the other operand is FALSE; returns 0 if both the operands are TRUE o both the operands are FALSE; returns NULL if one of the operands is NULL.

SELECT 1 XOR 0 -- returns 1
SELECT FALSE XOR TRUE—returns 1
SELECT 1 XOR TRUE—returns 0
SELECT 0 XOR FALSE—returns 0
SELECT NULL XOR 1 -- returns NULL

Synonyms

AND can be written as &&
OR can be written ad ||
NOT can be written as !

Only NOT (usually) has a different precedence from its synonym. See operator precedence for detail.

Arithmetic operators

MySQL supports operands which perform all basic arithmetic operations.

You can type positive values with a '+', if you want:

SELECT +1 -- return 1

You can type negative values with a '-'. - is an inversion operand:

SELECT -1 -- returns -1
SELECT -+1 -- returns -1
SELECT—1 -- returns 1

You can make sums with '+':

SELECT 1 + 1 -- returns 2

You can make subtractions with '-':

SELECT True - 1 -- returns 0

You can multiply a number with '*':

SELECT 1 * 1 -- returns 1

You can make divisions with '/'. Returns a FLOAT number:

SELECT 10 / 2 -- returns 5.0000
SELECT 1 / 1 -- returns 1.0000
SELECT 1 / 0 -- returns NULL (not an error)

You can make integer divisions with DIV. Resulting number is an INTEGER. No reminder. This has been added in MySQL 4.1.

SELECT 10 DIV 3 -- returns 3

You can get the reminder of a division with '%' or MOD:

SELECT 10 MOD 3 -- returns 1

Using + to cast data

You can convert an INTEGER to a FLOAT doing so:

SELECT 1 + 0.0 -- returns 1.0
SELECT 1 + 0.000—returns 1.000
SELECT TRUE + 0.000—returns 1.000

You can't convert a string to a FLOAT value by adding 0.0, but you can cast it to an INTEGER:

SELECT '1' + 0 -- returns 1
SELECT '1' + FALSE—returns 1
SELECT '' + ''—returns 0

Text operators

There are no concatenation operators in MySQL.

Arithmetic operators convert the values into numbers and then perform arithmetic operations, so you can't use + to concatenate strings.

You can use the CONCAT() function instead.

LIKE

The LIKE operator may be used to check if a string matches to a pattern. A simple example:

SELECT * FROM articles WHERE title LIKE 'hello world'

The pattern matching is usually case insensitive. There are two exceptions:

• when a LIKE comparison is performed against a column which has been declared with the BINARY flag (see CREATE TABLE);
• when the expression contains the BINARY clause:

SELECT * 'test' LIKE BINARY 'TEST' -- returns 0

You can use two special characters for LIKE comparisons:

• _ means "any character" (but must be 1 char, not 0 or 2)
• % means "any sequence of chars" (even 0 chars or 1000 chars)

Note that "\" also escapes quotes ("'") and this behaviour can't be changed by the ESCAPE clause. Also, the escape character does not escape itself.

Common uses of LIKE:

• Find titles starting with the word "hello":

SELECT * FROM articles WHERE title LIKE 'hello%'

• Find titles ending with the word "world":

SELECT * FROM articles WHERE title LIKE '%world'

• Find titles containing the word "gnu":

SELECT * FROM articles WHERE title LIKE '%gnu%'

These special chars may be contained in the pattern itself: for example, you could need to search for the "_" character. In that case, you need to "escape" the char:

SELECT * FROM articles WHERE title LIKE '\_%' -- titles starting with _
SELECT * FROM articles WHERE title LIKE '\%%' -- titles starting with %

Sometimes, you may want to use an escape character different from "\". For example, you could use "/":

SELECT * FROM articles WHERE title LIKE '/_%' ESCAPE '/'

When you use = operator, trailing spaces are ignored. When you use LIKE, they are taken into account.

SELECT 'word' = 'word ' -- returns 1
SELECT 'word' LIKE 'word ' -- returns 0

LIKE also works with numbers.

SELECT 123 LIKE '%2%' -- returns 1

If you want to check if a pattern doesn't match, you can use NOT LIKE:

SELECT 'a' NOT LIKE 'b' -- returns 1

SOUNDS LIKE

You can use SOUNDS LIKE to check if 2 text values are pronounced in the same way. SOUNDS LIKE uses the SOUNDEX algorythm, which is bases on English rules and is very approximate (but simple and thus fast).

SELECT `word1` SOUNDS LIKE `word2` FROM `wordList`—short form
SELECT SOUNDEX(`word1`) = SOUNDEX(`word2`) FROM `wordList`—long form

SOUNDS LIKE is a MySQL-specific extension to SQL. It has been added in MySQL 4.1.

Regular expressions

You can use REGEXP to check if a string matches to a pattern using regular expressions.

SELECT 'string' REGEXP 'pattern'

You can use RLIKE as a synonym for REGEXP.

Bitwise operators

Bit-NOT:

SELECT ~0 -- returns 18446744073709551615
SELECT ~1 -- returns 18446744073709551614

Bit-AND:

SELECT 1 & 1 -- returns 1
SELECT 1 & 3 -- returns 1
SELECT 2 & 3 -- returns 2

Bit-OR:

SELECT 1 | 0 -- returns 1
SELECT 3 | 0 -- returns 3
SELECT 4 | 2 -- returns 6

Bit-XOR:

SELECT 1 ^ 0 -- returns 1
SELECT 1 ^ 1 -- returns 0
SELECT 3 ^ 1 -- returns 2

Left shift:

SELECT 1 << 2 -- returns 4

Right shift:

SELECT 1 >> 2 -- 0

Import / export

Aside from mysqldump (cf. MySQL/Administration), you can also export raw data using:

SELECT ... FROM table INTO OUTFILE 'path'
LOAD DATA INFILE 'path' INTO TABLE table

Examples:

SELECT * FROM destinataire INTO OUTFILE '/tmp/test' WHERE id IN (41, 141, 260, 317, 735, 888, 1207, 2211);
# in another database/computer/etc.:
LOAD DATA INFILE '/tmp/test' INTO TABLE destinataire;

Beware that the MySQL daemon itself will write the file, not the user you run the MySQL client with. The file will be stored on the server, not on your host. Moreover, the server will need write access to the path you specify (usually, the server can _not_ write in your home directory, e.g.). Hence why we (unsecurely) used /tmp in the examples.

Exercises

Practicing SELECT

Table `list`

ID	Name	Surname	FlatHave	FlatWant
1	Shantanu	Oak	Goregaon
2	Shantanu	Oak	Andheri
3	Shantanu	Oak		Dadar
4	Ram	Joshi		Goregaon
5	Shyam	Sharma		Andheri
6	Ram	Naik	Sion
7	Samir	Shah	Parle
8	Ram	Joshi	Dadar
9	Shyam	Sharma	Dadar

Exercise I - Questions

• Who has a flat in "Goreagon" and who wants to buy one?
• Who has a flat in "Parle" and who wants to buy one?
• Where does "Shantanu Oak" own the flats and where does he want to buy one?
• How many entries have been recorded so far?
• How many flats are there for sale?
• What are the names of our clients?
• How many clients do we have?
• List the customers whose name start with "S"?
• Rearrange the list Alphabetically sorted.

Exercise I - Answers

• select * from list where FlatHave = "Goregaon" and FlatWant = "Goregaon"
• select * from list where FlatHave = "Parle" and FlatWant = "Parle"
• select FlatHave,FlatWant from list where Name = "Shantanu" and Surname = "Oak"
• select count(*) from list
• select count(FlatHave) from list where FlatHave is not null
• select distinct Name, Surname from list
• select count(distinct Name, surname) from list
• select * from list where Name like "S%"
• select Surname, Name, FlatHave, FlatWant from list order by Name

Table `grades`

ID	Name	Math	Physics	Literature
1	John	68	37	54
2	Jim	96	89	92
3	Bill	65	12	57
4	Jeri	69	25	82

Exercise II - Questions

• A list of all students who scored over 90

on his or her math paper?

• A list of all students who scored more than 85 in all subjects?
• Declare Results: Print the results of all students with result column.
• Find out total marks of all the students.
• What are the average marks of the class for each subject?
• What are the minimum marks in Math?
• What are the maximum marks in Math?
• Who got the highest marks in Math?

Exercise II - Answers

Note: many problems have more than one correct solution.

• SELECT * FROM grades WHERE math > 90
• SELECT name FROM grades WHERE math > 85 AND physics > 85 AND literature > 85
• SELECT *, IF( (math <= 35 OR physics <= 35 OR literature <= 35), 'fail', 'pass') AS result FROM grades ORDER BY result DESC
• SELECT name, math+physics+literature FROM grades
• SELECT AVG(math), AVG(physics), AVG(literature) FROM grades
• SELECT MIN(math) FROM grades
• SELECT MAX(math) FROM grades
• SELECT * FROM students ORDER BY math DESC LIMIT 1

Examples

Finding Duplicates

SELECT Vendor, ID, Count(1) as dupes
FROM table_name
GROUP BY Vendor, ID HAVING Count(1) >1

SELECT txt, COUNT(*)
FROM dupes
GROUP BY txt HAVING COUNT(*) > 1;

SELECT id, COUNT( id ) AS cnt, 
FROM myTable
GROUP BY id HAVING cnt > 1

Remove duplicate entries.

Assume the following table and data.

CREATE TABLE IF NOT EXISTS dupTest
(pkey int(11) NOT NULL auto_increment,
an int, b int, c int, timeEnter timestamp(14),
PRIMARY KEY (pkey));

insert into dupTest (a,b,c) values (1,2,3),(1,2,3),
(1,5,4),(1,6,4);

Note, the first two rows contains duplicates in columns a and b. It contains other duplicates; but, leaves the other duplicates alone.

ALTER IGNORE TABLE dupTest ADD UNIQUE INDEX(a,b);