INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [(col_name,...)]
    SELECT ...
    [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

With INSERT ... SELECT, you can quickly insert many rows into a table from one or many tables. For example:

INSERT INTO tbl_temp2 (fld_id)
  SELECT tbl_temp1.fld_order_id
  FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;

The following conditions hold for a INSERT ... SELECT statements:

In the values part of ON DUPLICATE KEY UPDATE, you can refer to columns in other tables, as long as you do not use GROUP BY in the SELECT part. One side effect is that you must qualify non-unique column names in the values part.

Full outer join

A full outer join combines the results of both left and right outer joins. The joined table will contain all records from both tables, and fill in NULLs for missing matches on either side.

Example full outer join:

SELECT *  
FROM   employee 
       FULL OUTER JOIN department 
          ON employee.DepartmentID = department.DepartmentID

Right outer join

A right outer join closely resembles a left outer join, except with the tables reversed. Every record from the "right" table (B) will appear in the joined table at least once. If no matching row from the "left" table (A) exists, NULL will appear in columns from A for those records that have no match in A.

A right outer join returns all the values from the right table and matched values from the left table ( NULL in case of no matching join predicate).

Example right outer join:

SELECT * 
FROM   employee 
       RIGHT OUTER JOIN department 
          ON employee.DepartmentID = department.DepartmentID

Cross join

A cross join or cartesian join provides the foundation upon which all types of inner joins operate. A cross join returns the cartesian product of the sets of records from the two joined tables. Thus, it equates to an inner join where the join-condition always evaluates to True.

If A and B are two sets, then cross join = A X B.

The SQL code for a cross join lists the tables for joining (FROM), but does not include any filtering join-predicate.

Example of an explicit cross join:

SELECT *
FROM   employee CROSS JOIN department

Example of an implicit cross join:

SELECT *
FROM   employee, department;

Left outer join

The result of a left outer join for tables A and B always contains all records of the "left" table (A), even if the join-condition does not find any matching record in the "right" table (B). This means that if the ON clause matches 0 (zero) records in B, the join will still return a row in the result—but with NULL in each column from B. This means that a left outer join returns all the values from the left table, plus matched values from the right table (or NULL in case of no matching join predicate).

For example, this allows us to find an employee's department, but still to show the employee even when their department does not exist (contrary to the inner-join example above, where employees in non-existent departments get filtered out).

Example of a left outer join(new):

SELECT *  
FROM   employee 
       LEFT OUTER JOIN department 
          ON employee.DepartmentID = department.DepartmentID

Equi-join

An equi-join (also known as an equijoin), a specific type of comparator-based join, or theta join, uses only equality comparisons in the join-predicate. Using other comparison operators (such as <) disqualifies a join as an equi-join. The query shown above has already provided an example of an equi-join:

SELECT *
FROM   employee 
       INNER JOIN department 
          ON employee.DepartmentID = department.DepartmentID

Natural join

A natural join offers a further specialization of equi-joins. The join predicate arises implicitly by comparing all columns in both tables that have the same column-name in the joined tables. The resulting joined table contains only one column for each pair of equally-named columns.

The above sample query for inner joins can be expressed as a natural join in the following way:

SELECT *
FROM   employee NATURAL JOIN department

Example of an explicit inner join:

SELECT *
FROM   employee 
       INNER JOIN department 
          ON employee.DepartmentID = department.DepartmentID

Example of an implicit inner join:

SELECT *  
FROM   employee, department 
WHERE  employee.DepartmentID = department.DepartmentID

Explicit Inner join result:

MySQL supports incremental backups: You need to start the server with the --log-bin option to enable binary logging; see Section 5.10.3, “The Binary Log”. At the moment you want to make an incremental backup (containing all changes that happened since the last full or incremental backup), you should rotate the binary log by using FLUSH LOGS. This done, you need to copy to the backup location all binary logs which range from the one of the moment of the last full or incremental backup to the last but one. These binary logs are the incremental backup; at restore time, you apply them as explained further below. The next time you do a full backup, you should also rotate the binary log using FLUSH LOGS, mysqldump --flush-logs, or mysqlhotcopy --flushlog. See Section 7.13, “mysqldump — A Database Backup Program”, and Section 7.14, “mysqlhotcopy — A Database Backup Program”.

If your MySQL server is a slave replication server, then regardless of the backup method you choose, you should also back up the master.info and relay-log.info files when you back up your slave's data. These files are always needed to resume replication after you restore the slave's data. If your slave is subject to replicating LOAD DATA INFILE commands, you should also back up any SQL_LOAD-* files that may exist in the directory specified by the --slave-load-tmpdir option. (This location defaults to the value of the tmpdir variable if not specified.) The slave needs these files to resume replication of any interrupted LOAD DATA INFILE operations.

To login (from unix shell) use -h only if needed.

[mysql dir]/bin/mysql -h hostname -u root -p

Create a database on the sql server.

create database [databasename];

List all databases on the sql server.

show databases;

Switch to a database.

use [db name];

To see all the tables in the db.

show tables;

To see database's field formats.

describe [table name];

To delete a db.

drop database [database name];

To delete a table.

drop table [table name];

Show all data in a table.

SELECT * FROM [table name];

Returns the columns and column information pertaining to the designated table.

show columns from [table name];

Show certain selected rows with the value "whatever".

SELECT * FROM [table name] WHERE [field name] = "whatever";

Show all records containing the name "Bob" AND the phone number '3444444'.

SELECT * FROM [table name] WHERE name = "Bob" AND phone_number = '3444444';

Show all records not containing the name "Bob" AND the phone number '3444444' order by the phone_number field.

SELECT * FROM [table name] WHERE name != "Bob" AND phone_number = '3444444' order by phone_number;

Show all records starting with the letters 'bob' AND the phone number '3444444'.

SELECT * FROM [table name] WHERE name like "Bob%" AND phone_number = '3444444';

Use a regular expression to find records. Use "REGEXP BINARY" to force case-sensitivity. This finds any record beginning with a.

SELECT * FROM [table name] WHERE rec RLIKE "^a$";

Show unique records.

SELECT DISTINCT [column name] FROM [table name];

Show selected records sorted in an ascending (asc) or descending (desc).

SELECT [col1],[col2] FROM [table name] ORDER BY [col2] DESC;

Return number of rows.

SELECT COUNT(*) FROM [table name];

Sum column.

SELECT SUM(*) FROM [table name];

Join tables on common columns.

select lookup.illustrationid, lookup.personid,person.birthday from lookup
left join person on lookup.personid=person.personid=statement to join birthday in person table with primary illustration id;

Switch to the mysql db. Create a new user.

INSERT INTO [table name] (Host,User,Password) VALUES('%','user',PASSWORD('password'));

Change a users password.(from unix shell).

[mysql dir]/bin/mysqladmin -u root -h hostname.blah.org -p password 'new-password'

Change a users password.(from MySQL prompt).

SET PASSWORD FOR 'user'@'hostname' = PASSWORD('passwordhere');

Allow the user "bob" to connect to the server from localhost using the password "passwd"

grant usage on *.* to bob@localhost identified by 'passwd';

Switch to mysql db.Give user privilages for a db.

INSERT INTO [table name] (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES ('%','databasename','username','Y','Y','Y','Y','Y','N');

or

grant all privileges on databasename.* to username@localhost;

To update info already in a table.

UPDATE [table name] SET Select_priv = 'Y',Insert_priv = 'Y',Update_priv = 'Y' where [field name] = 'user';

Delete a row(s) from a table.

DELETE from [table name] where [field name] = 'whatever';

Update database permissions/privilages.

FLUSH PRIVILEGES;

Delete a column.

alter table [table name] drop column [column name];

Add a new column to db.

alter table [table name] add column [new column name] varchar (20);

Change column name.

alter table [table name] change [old column name] [new column name] varchar (50);

Make a unique column so you get no dupes.

alter table [table name] add unique ([column name]);

Make a column bigger.

alter table [table name] modify [column name] VARCHAR(3);

Delete unique from table.

alter table [table name] drop index [colmn name];

Load a CSV file into a table.

LOAD DATA INFILE '/tmp/filename.csv' replace INTO TABLE [table name] FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (field1,field2,field3);

Dump all databases for backup. Backup file is sql commands to recreate all db's.

[mysql dir]/bin/mysqldump -u root -ppassword --opt >/tmp/alldatabases.sql

Dump one database for backup.

[mysql dir]/bin/mysqldump -u username -ppassword --databases databasename >/tmp/databasename.sql

Dump a table from a database.

[mysql dir]/bin/mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql

Restore database (or database table) from backup.

[mysql dir]/bin/mysql -u username -ppassword databasename < /tmp/databasename.sql

Set the default character set:

mysql --default-character-set=utf8

mysql> SHOW VARIABLES LIKE 'char%';

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

mysql> GRANT ALL PRIVILEGES ON *.* TO 'ian'@'192.168.1.11'
	 IDENTIFIED BY 'mypassword'
	 WITH GRANT OPTION;