• Login
  • Register
  • Profile

All About MySQL

INDEX

Introduction

MySQL is a free, widely used SQL engine. It can be used as a fast database as well as a rock-solid DBMS using a modular engine architecture.
What is SQL?
Structured Query Language is a third generation language for working with relational databases. Being a 3G language it is closer to human language than machine language and therefore easier to understand and work with.
Dr. E. F. Ted Codd who worked for IBM described a relational model for database in 1970.
In 1992, ANSI (American National Standards Institute), the apex body, standardized most of the basic syntax.
Its called SQL 92 and most databases (like Oracle, MySQL, Sybase, etc.) implement a subset of the standard (and proprietary extensions that makes them often incompatible).

Why MYSQL?
Free as in Freedom - Released with GPL version 2 license (though a different license can be bought from Oracle, see below)
Cost - Free!
Support - Online tutorials, forums, mailing list (lists.mysql.com), paid support contracts.
Speed - One of the fastest databases available
Ease of use - less need of training / retraining.
Portability - easily import / export from Excel and other databases
Scalable - Useful for both small as well as large databases containing billions of records and terabytes of data in hundreds of thousands of tables.
Permission Control - selectively grant or revoke permissions to users.

Creating your own MySQL account and database:
Now that MySQL is installed, you wouldn't necessarily have your own account, so you have to log in as root.
To do this type:
"root" as a password.
This means that you're logging on as the user "root".
Once you've managed to log in, your command-line should look like this:
mysql>
Now you can check what databases (if any) are available to your user (in this case "root" ):
Show databases;
Let's get straight to the chase and create our own database. Let's call it people. While we're doing this we can also create our own user account. Two birds with one stone.
So first create the database:
create database user;
Now we want to grant ( GRANT ) all user rights ( ALL ) from ( ON ) the entire ( * ) user database to ( TO ) your account ( yourusername@localhost ) with your user password being userpass ( IDENTIFIED BY "userpass" ).
So we'd input this as:
GRANT ALL ON user.* TO yourusername@localhost IDENTIFIED BY "userpass";
Yeh! You now have your own user account. Let's say you chose ted as your username. You've configured MySQL to say that ted can play around with the user database in whatever ways he wishes.
To start working with the user database, you can now login as ted:
mysql -u ted -p

Creating tables with information in your database:
Ted has now created a user database. So we want now to enter some information into a table.
Login as ted.
Firstly, we need to make sure we're working with the people database. So typing:
select database();
You can see the list of databases available in the system. You can select any one. Say
use user;
TO show existing tables in the database.
show tables;

Creating a new table:
CREATE TABLE employee
(
ID int not null auto_increment primary key,
firstName varchar(30),
lastName varchar(30),
age int,
gender varchar(13)
);

Then press ENTER.
The table will be created.

To see the properties of your table type:
describe employee;
OR
desc employee;
For inserting the values you need to use the following method.

INSERT INTO employee
(firstName, lastName, age, gender)
values
("Bill", "Harper", 17, "male");

Now if you want to print on the screen all the information about your table:
mysql>select * from employee;

Databases Manipulation

Creation
CREATE DATABASE DATABASE;
mysqladmin create is a command-line wrapper for this function.
Deletion
DROP DATABASE DATABASE;
mysqladmin drop is a command-line wrapper for this function.

Rename
SQl uses 'as' clause for renaming relations(table) as well as the attributes.
syntax:
oldname as newname.
Example:
select customer_name as cust_name from customer;
You also need to drop privileges on customer_name and recreate them on cust_name:
UPDATE mysql.db SET `Db`='cust_name' WHERE `Db`='customer_name';
FLUSH PRIVILEGES;

Copy
There is no direct copy command in MySQL. However, this can easily be done using some tools like phpMyAdmin

Languages

Browsing the databases

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

You can't modify structure and data of information_schema. You can only query the tables.

Many information_schema tables provide the same data you can retrieve with a SHOW statement. While using SHOW commands is faster (the server responds much faster and you type less characters), the information_schema provides a more flexible way to obtain and organize the metadata.

List databases
The INFORMATION_SCHEMA table containing the databases information is SCHEMATA.
The mysqlshow command line tool (DOS/Unix) 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.
The following SQL commands provide information about the databases located on the current server.

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 DBMSs.

Adding Filter on Database Names

Like operator:
It is used to search pattern matching in the realation. By searching the informations.
mysql> SHOW DATABASES LIKE 'pattern';
The LIKE operator here works as in normal SELECTs or DML statements. So you can list all databases whose name contains 'pattern' into it.
mysql>SHOW DATABASES LIKE 'my%';
This will list all databases whose name starts with 'my';

Like operators can be used for searching different patterns such as:
1) Percentage(%) for matching any substring.
2) underscore(_) for matching any character.

Example:
mysql>select * from customer where cust_name like 'Jim%';
It will select all the details of customers whose name starts with 'Jim'.
mysql>select * from customer where cust_street like '%Main%';
It will select all the details of the customers where street name contains 'Main' in its center.

What are DDL, DML and DQL?
DDL (Data Definition Language) refers to the CREATE, ALTER and DROP statements
DDL allows to add / modify / delete the logical structures which contain the data or which allow users to access / mantain the data (databases, tables, keys, views...). DDL is about "metadata".

DML (Data Manipulation Language) refers to the INSERT, UPDATE and DELETE statements
DML allows to add / modify / delete data itself.

DQL (Data Query Language) refers to the SELECT, SHOW and HELP statements (queries)
SELECT is the main DQL instruction. It retrieves data you need. SHOW retrieves infos about the metadata. HELP... is for people who need help.

DCL (Data Control Language) refers to the GRANT and REVOKE statements
DCL is used to grant / revoke permissions on databases and their contents. DCL is simple, but MySQL's permissions are rather complex. DCL is about security.

DTL (Data Transaction Language) refers to the START TRANSACTION, SAVEPOINT, COMMIT and ROLLBACK [TO SAVEPOINT] statements
DTL is used to manage transactions (operations which include more instructions none of which can be executed if one of them fails).

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.
The value of a variable is set with the SET statement or in a SELECT statement with :=

SELECT @test := 2;
SELECT @test + 1; -- returns 3

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:
USE hediard;
SET @id = 0, @name = '';
SELECT id, name INTO @id, @name FROM table1 LIMIT 1;
SELECT @id, @name;

Global Variables
A global variable is visible to all users, it allows to modify the configuration files settings during the session or definitely. So when changing them, it's necessary to precise this permanent or ephemera criteria, with respectively set global and set session. Example:

mysql> set @@global.max_connections = 1000;
mysql> show global variables like 'wait_timeout';
mysql> set @@session.wait_timeout=120;

Data Types

VARCHAR
VARCHAR is shorthand for CHARACTER VARYING. 'n' represents the maximum column length (upto 65,535 characters). 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 or 2 bytes (1 if the length is < 255) to record the length of the string.
For the string 'abcd', L is 4 and the storage requirement is 5 bytes.

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.

TEXT and BLOB
A BLOB or TEXT column with a maximum length of 65,535 characters. The required space is the real length of the stored data plus 2 bytes (1 byte if length is < 255). The BLOB / TEXT data is not stored in the table's datafile. This makes all operations (INSERT / UPDATE / DELETE / SELECT) involving the BLOB / TEXT data slower, but makes all other operations faster.

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 upto 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.

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.

Example:

SET("madam", "mister") -- authorizes an empty field, "madam", "mister", "madam, mister", or "mister, madam"
ENUM("madam", "mister") -- authorizes an empty field, "madam" or "mister"

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.

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

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

DELETE and TRUNCATE
DELETE [QUICK] FROM `table1`
TRUNCATE [TABLE] `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.
For a InnoDB table with FOREIGN KEYs constraints, TRUNCATE behaves like DELETE.
DELETE FROM `antiques`
WHERE item = 'Ottoman'
ORDER BY `id`
LIMIT 1

Queries

SELECT:

SELECT *
 FROM a_table_name
 WHERE condition
 GROUP BY grouped_field
 HAVING group_name condition
 ORDER BY ordered_field
 LIMIT limit_number, offset;

The table's name
If you are retrieving results from a table or a view, usually you specify the table's name in the FROM 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:
The records which match to the WHERE clause are retrieved
Those records are used to compute new records as defined in the GROUP BY clause
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 less than 10 rows, it returns the number of rows found. 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 to return the records 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 acceptable:

SELECT * FROM `antiques` ORDER BY id LIMIT 10 OFFSET 10
You can use the LIMIT clause to check the syntax of a query without waiting for it to return the results:
SELECT ... LIMIT 0

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.

UNION and UNION All
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)

Joins

The Most important aspect of SQL is its relational features. You can query, compare and calculate two different tables having entirely different structure. Joins and subselects are the two methods to join tables. Both methods of joining tables should give the same results. The natural join is faster on most SQL platforms.
In the following example a student is trying to learn what the numbers are called in hindi. 

 CREATE TABLE english (Tag INT, Inenglish VARCHAR(255));
 CREATE TABLE hindi (Tag INT, Inhindi VARCHAR(255));
 
 INSERT INTO english (Tag, Inenglish) VALUES (1, 'One');
 INSERT INTO english (Tag, Inenglish) VALUES (2, 'Two');
 INSERT INTO english (Tag, Inenglish) VALUES (3, 'Three');
 
 INSERT INTO hindi (Tag, Inhindi) VALUES (2, 'Do');
 INSERT INTO hindi (Tag, Inhindi) VALUES (3, 'Teen');
 INSERT INTO hindi (Tag, Inhindi) VALUES (4, 'Char');


Cartesian join (CROSS JOIN)
A Cartesian join is when you join every row of one table to every row of another table.

SELECT * FROM english, hindi
It is also called Cross Join and may be written in this way:

SELECT * FROM english CROSS JOIN hindi
Inner Join
SELECT hindi.Tag, english.Inenglish, hindi.Inhindi
FROM english, hindi
WHERE english.Tag = hindi.Tag
-- equal
SELECT hindi.Tag, english.Inenglish, hindi.Inhindi
FROM english INNER JOIN hindi ON english.Tag = hindi.Tag
Tag Inenglish Inhindi
2 Two Do
3 Three Teen
You can also write the same query as

SELECT hindi.Tag, english.Inenglish, hindi.Inhindi
FROM english INNER JOIN hindi
ON english.Tag = hindi.Tag
Natural Joins using "using" (Compatible: MySQL 4+; but changed in MySQL 5) The following statement using "USING" method will display the same results.

SELECT hindi.tag, hindi.Inhindi, english.Inenglish
FROM hindi NATURAL JOIN english
USING (Tag)
Outer Joins
Tag	Inenglish	Tag	Inhindi
1	One	 	 
2	Two	2	Do
3	Three	3	Teen
 	 	4	Char

LEFT JOIN / LEFT OUTER JOIN
The syntax is as follows:

SELECT field1, field2 FROM table1 LEFT JOIN table2 ON field1=field2

SELECT e.Inenglish AS English, e.Tag, '--no row--' AS Hindi
FROM english AS e LEFT JOIN hindi AS h
ON e.Tag=h.Tag
WHERE h.Inhindi IS NULL
English tag Hindi
One 1 --no row-

Right Outer Join
SELECT '--no row--' AS English, h.tag, h.Inhindi AS Hindi
FROM english AS e RIGHT JOIN hindi AS h
ON e.Tag=h.Tag
WHERE e.Inenglish IS NULL
English tag Hindi --no row-- 4 Char

Make sure that you have the same name and same data type in both tables.
The keywords LEFT and RIGHT are not absolute, they only operate within the context of the given statement: we can reverse the order of the tables and reverse the keywords, and the result would be the same.
If the type of join is not specified as inner or outer then it will be executed as an INNER JOIN.

Full Outer Join
As for v5.1, MySQL does not provide FULL OUTER JOIN. You may emulate it this way:
     (SELECT a.*, b* 
         FROM tab1 a LEFT JOIN tab2 b
         ON a.id = b.id)
 UNION
     (SELECT a.*, b* 
         FROM tab1 a RIGHT JOIN tab2 b
         ON a.id = b.id)

Multiple joins
It is possible to join more than just two tables:

SELECT ... FROM a JOIN (b JOIN c ON b.id=c.id) ON a.id=b.id
Here is an example from Savane:

 mysql> SELECT group_type.type_id, group_type.name, COUNT(people_job.job_id) AS COUNT 
        FROM group_type
         JOIN (groups JOIN people_job ON groups.group_id = people_job.group_id) 
         ON group_type.type_id = groups.TYPE
        GROUP BY type_id ORDER BY type_id;

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 ]

Operators

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 NULL <=> NULL -- 1
SELECT TRUE <=> TRUE -- 1
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 UNKOWN) -- 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 theoretical 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 at least 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 the two operands are 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.

Arithmatic 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

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 algorithm, which is based 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

Export data
Data can be exported using the "INTO OUTFILE" keyword

SELECT * FROM destinataire INTO OUTFILE '/tmp/test' WHERE id IN (41, 141, 260, 317, 735, 888, 1207, 2211);
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.

You can also use the command line to export data

mysql < query.txt > output.txt
where query.txt contains an sql-query and the output will be stored in output.txt

Import data In another database/computer/etc. the data can be imported:

LOAD DATA INFILE '/tmp/test' INTO TABLE destinataire;
additional options are

FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
to specify how the document is set up and whether there is a header. The columns in the data file can be mapped to the columns of the database table if they do not correspond and it is thus also possible to omit certain columns using a dummy variable:

 LOAD DATA LOCAL INFILE
 '/tmp/test'
 INTO TABLE destinataire 
 FIELDS TERMINATED BY '\t'
 LINES TERMINATED BY '\n'
 IGNORE 1 LINES 
 ( 
 @dummy,
 name,
 phone_number,
 @dummy,
 @dummy,
 @dummy,
 @dummy,
 @dummy,
 @dummy,
 @dummy
 )

In this example, we only need the second and third column of the data file and store these values in the name and phone_number column of our database table.

Functions

Syntax:
Function names are case insensitive. You can write them as you prefer:

SELECT DATABASE() -- ok
SELECT DATABASE() -- ok
SELECT DATABASE() -- ok
If the IGNORE_SPACE SQL_MODE is not set, you can not put a space between the function name and the first parenthesis. It would return a 1064 error. IGNORE_SPACE is usually 0. The reason is that the parser is faster if that flag is disabled. So:

SELECT DATABASE () -- usually not accepted
SELECT DATABASE() -- always works fine
However, this restriction only applies to the native MySQL functions. UDFs and stored functions may be written with a space after the name.

You can't use a value calculated in the SELECT clause as a constraint in the WHERE clause (its a chicken & egg problem); the WHERE clause is what determines the values in the SELECT clause. What you want is the HAVING clause which is applied *after* all matching rows have been found.

General functions

Type-indipendent functions.
BENCHMARK(times, expression)
Executes expression n times and returns how time it spent. Useful to find bottlenecks in SQL expressions.

SELECT BENCHMARK(10000, 'hello'); -- Treatment in 0.0010 sec

CHARSET(string)
Returns the CHARACTER SET used by string.

SELECT CHARSET(20130101); -- binary
SHOW CHARACTER SET; -- displays all the different installed CHARACTER SET

COALESCE(value, ...)
Returns the first argument which is not NULL. If all arguments are NULL, returns NULL. There must be at least one argument.

SELECT COALESCE(NULL, 'hello', NULL); -- hello

COERCIBILITY(string)
Returns the coercibility (between 0 to 5):

SELECT COERCIBILITY('hello'); -- 4

COLLATION(string)
Returns the COLLATION used by the string.

SELECT COLLATION('hello'); -- utf8_general_ci
CONNECTION_ID()
Returns the id of the current thread.

SELECT CONNECTION_ID(); -- 31
CONVERT(value, type)
Returns value converted to the specified type.

SELECT CONVERT ('666', UNSIGNED INTEGER)
CONVERT(string USING charset)
Converts the passed string to the specified CHARACTER SET.

SELECT CONVERT ('This is a text' USING utf8)
CURRENT_USER()
Returns the username and the hostname used in the current connection.

SELECT CURRENT_USER()
SELECT CURRENT_USER -- it's correct
DATABASE()[edit]
Returns the current database's name, set with the USE command.

SELECT DATABASE()
FOUND_ROWS()
After a SELECT with a LIMIT clause and the SQL_CALC_FOUND_ROWS keyword, you can run another SELECT with the FOUND_ROWS() function. It returns the number of rows found by the previous query if it had no LIMIT clause.

SELECT SQL_CALC_FOUND_ROWS * FROM stats ORDER BY id LIMIT 10 OFFSET 50
SELECT FOUND_ROWS() AS n
GREATEST(value1, value2, ...)
Returns the greatest argument passed.

IF(val1, val2, val3)
If val1 is TRUE, returns val2. If val1 is FALSE or NULL, returns val3.

IFNULL(val1, val2)
If val1 is NULL, returns val2; else, returns val1.

ISNULL(value)
If the value passed is NULL returns 1, else returns 0.

INTERVAL(val1, val2, val3, ...)
Returns the location of the first argument which is greater than the first one, beginning by zero in the integers in parameter:

SELECT INTERVAL(10, 20, 9, 8, 7); -- 0
SELECT INTERVAL(10, 9, 20, 8, 7); -- 1
SELECT INTERVAL(10, 9, 8, 20, 7); -- 2
SELECT INTERVAL(10, 9, 8, 7, 20); -- 3
NULLIF(val1, val2)
If val1 = val2, returns NULL; else, returns val1.

LEAST(value1, value2, ...)
Returns the minimum argument passed.

Date and time
SELECT * FROM mytable
WHERE datetimecol >= (CURDATE() - INTERVAL 1 YEAR) AND
datetimecol < (CURDATE() - INTERVAL 1 YEAR) INTERVAL 1 DAY;

SELECT IF(DAYOFMONTH(CURDATE()) <= 15,
DATE_FORMAT(CURDATE(), '%Y-%m-15'),
DATE_FORMAT(CURDATE() + INTERVAL 1 MONTH, '%Y-%m-15')) AS next15
FROM TABLE;

SELECT YEAR('2002-05-10'), MONTH('2002-05-10'), DAYOFMONTH('2002-05-10')

SELECT PurchaseDate FROM TABLE WHERE YEAR(PurchaseDate) <= YEAR(CURDATE())

SELECT COLUMNS FROM TABLE
WHERE start_time >= '2004-06-01 10:00:00' AND end_time <= '2004-06-03 18:00:00'

SELECT * FROM t1
WHERE DATE_FORMAT(datetime_column, '%T') BETWEEN 'HH:MM:SS' AND 'HH:MM:SS'

SELECT Start_time, End_time FROM TABLE
WHERE Start_time >= NOW() - INTERVAL 4 HOUR

SELECT NOW() + INTERVAL 60 SECOND

SELECT UNIX_TIMESTAMP('2007-05-01'); -- 1177970400
SELECT FROM_UNIXTIME(1177970400); -- 2007-05-01 00:00:00

Aggregate functions
COUNT(field)
If * is given, instead of the name of a field, COUNT() returns the number of rows found by the query. It's commonly used to get the number of rows in a table.

SELECT COUNT(*) FROM `antiques`
If the DISTINCT keyword is used, identical rows are counted only once.

SELECT COUNT(DISTINCT *) FROM `antiques`
If a field name is given, returns the number of non-NULL values.

SELECT COUNT(`cost`) FROM `antiques`
If a field name is given and the DISTINCT keyword is given, returns the number of non-NULL values, and identical values are counted only once.

SELECT COUNT(DISTINCT `cost`) FROM `antiques`
You can count non-NULL values for an expression:

SELECT COUNT(`longitude` + `latitude`) FROM `cities`
This returns the number of rows where longitude and latitude are both non-NULL.

MAX(field)
MAX() can be used to get the maximum value for an expression in the rows matching to a query. If no row matches the query, returns NULL.

SELECT MAX(`cost`) FROM `antiques`
SELECT MAX(LENGTH(CONCAT(`first_name`, ' ', `last_name`))) FROM `subscribers`

MIN(field)
MIN() can be used to get the minimum value for an expression in the rows matching to a query. If no row matches the query, returns NULL.
SELECT MIN(`cost`) FROM `antiques`

AVG(field)
AVG() can be used to get the average value for an expression in the rows matching to a query. If no row matches the query, returns NULL.
SELECT AVG(`cost`) FROM `antiques`

SUM(field)
SUM() can be used to get the sum of the values for an expression in the rows matching to a query. If no row matches the query, returns NULL.
If SUM(DISTINCT expression) is used, identical values are added only once. It has been added in MySQL 5.1.

SELECT SUM(`cost`) FROM `antiques`
GROUP_CONCAT(field)
GROUP_CONCAT() can be used to concatenate values from all records for a group into a single string separated by comma or any additional token you like.

CREATE TEMPORARY TABLE p (
id INTEGER, ptype VARCHAR(10), pname VARCHAR(50)
);

INSERT INTO p VALUES
(1,'mp3','iPod'),
(2,'mp3','Zune'),
(3,'mp3','ZEN'),
(4,'notebook','Acer Eee PC'),
(4,'notebook','Everex CloudBook');

SELECT * FROM p;

SELECT ptype,group_concat(pname)
FROM p
GROUP BY ptype;

SELECT ptype,group_concat(' ',pname)
FROM p
GROUP BY ptype;
Aggregate bit functions
General syntax:

FUNCTION_NAME(''expression'')
These functions calculate expression for each row of the result set and perform the calculation between all the expressions. These are bitwise functions. The precision used is 64 bit.

AND
SELECT BIT_AND(ip) FROM log

OR
SELECT BIT_OR(ip) FROM log
(returns 0 if there are no rows)

XOR
SELECT BIT_XOR(ip) FROM log

Our Sponsors

Have any suggestions?