Advanced SQL ( Logical, arithmetic, comparison, and bit operators )

Logical Operators

Logical Operators
Operator	Syntax	Description
AND, &&	c1 AND c2, c1 && c2	Only true if both conditions c1 and c2 are true.
OR, ||	c1 OR c2, c1 || c2	True if either c1 or c2 is true.
!, NOT	! c1, NOT c1	True if c1 is false, false if c1 is true.

mysql> SELECT 1 AND 0;

+---------+

| 1 AND 0 |

+---------+

|       0 |

+---------+

mysql> SELECT NOT(1 AND 0);

+--------------+

| NOT(1 AND 0) |

+--------------+

|            1 |

+--------------+

mysql> SELECT !((1 OR 0) AND (0 OR 1));

+--------------------------+

| !((1 OR 0) AND (0 OR 1)) |

+--------------------------+

|                        0 |

+-----------------------------------+

Arithmetic Operators

Arithmetic Operators
Operator	Syntax	Description
+	a + b	Adds a and b together, returning the sum of both
-	a - b	Subtracts b from a, returning the difference
*	a * b	Multiplies a and b, returning the product of both
/	a / b	Divides a by b, returning the quotient
%	a % b	a modulus b, returning the remainder after a / b

For example, adding together two columns of type INT will produce an INT:

mysql> SELECT 2+1;

+-----+

| 2+1 |

+-----+

|   3 |

+-----+

mysql> SELECT 4-2/4;

+-------+

| 4-2/4 |

+-------+

|  3.50 |

+-----------+

Comparison Operators

Comparison Operators
Operator	Syntax	Description
=	a = b	True if both a and b are equal (excluding NULL).
!=, <>	a != b, a <> b	True if a is not equal to b.
>	a > b	True if a is greater than b.
<	a < b	True if a is less than b.
>=	a >= b	True if a is greater than or equal to b.
<=	a <= b	True if a is less than or equal to b.
<=>	a <=> b	True if a and b are equal (including NULL).
IS NULL	a is NULL	True if a contains a NULL value.
IS NOT NULL	a IS NOT NULL	True if a does not contain a NULL value.
BETWEEN	a BETWEEN b and c	True if a is between the values of b and c, inclusive.
NOT BETWEEN	a NOT BETWEEN b and c	True if a is not between the values of b and c, inclusive.
LIKE	a LIKE b	True if a matches b on an SQL pattern match.
NOT LIKE	a NOT LIKE b	True if a does not match b on an SQL pat-tern match. The two acceptable wildcard characters are % (which means any number of characters) and _ (which means one character).
IN	a IN (b1, b2, b3…)	True if a is equal to anything in the list.
NOT IN	a NOT IN (b1,b2,b3…)	True if a is not equal to anything in the list.
REGEXP, RLIKE	a REGEXP b, a RLIKE b	True if a matches b with a regular expression.
NOT REGEXP, NOT RLIKE	a NOT REGEXP b, a NOT RLIKE B	True if a does not match b with a regular expression.

The following code represents the = comparison operator:

SELECT * FROM TABLE1 WHERE FIELD1 = 13. 

Each row in the table is then compared to see whether the condition is true or false. For the first row, the expression reduces to this:

15 = 13

This is false, so the row is not returned. For the second row, the expression reduces to the following:

13 = 13

This is true, so this time the row is returned.

Bit Operators

Bit OPerators
Operator	Syntax	Description
&	a & b	Bitwise AND
|	a | b	Bitwise OR
<<	a << b	Left shift of a by b bit positions
>>	a >> b	Right shift of a by b bit positions

For a bitwise OR, either digit should be 1 for the result to be 1. So, shows a bitwise OR performed on the same 9 and 7.

Bitwise OR operation: 9|7

All columns have at least one 1 present, so the result for each is a 1 overall. And 1111 is equivalent to 15 in binary:

mysql> SELECT 9|7;

+-----+

| 9|7 |

+-----+

|  15 |

+-----+

The << is the left shift operator, so << b means that the bits of a are shifted left by b columns. For example: 2 << 1. In binary, 2 is 10. If this is shifted left 1 bit, you get 100, which is 4. For example:

mysql> SELECT 2 << 1;

+--------+

| 2 << 1 |

+--------+

|      4 |

+--------+

mysql> SELECT 15 << 4;

+---------+

| 15 << 4 |

+---------+

|     240 |

+---------+

Now you have 15, which is 1111; when shifted 4 bits left, you get 11110000. Convert this to decimal in the usual way. 

Converting the binary number 11110000 to decimal

Now total this:

128 + 64 + 32 + 16 = 240

Bitwise operations are performed as BIGINTs, meaning there's a limit of 64 bits. Shifting beyond 64 bits, or with a negative number, just returns 0. For example:

mysql> SELECT 3 << 64;

+---------+

| 3 << 64 |

+---------+

|       0 |

+---------+

The >> is the right shift operator, so a >> b shifts the bits of a right by b columns. Bits shifted beyond the "ones" column are lost. And, again, shifting by a negative number returns 0.
For example:

mysql> SELECT 3 >> 1;

+--------+

| 3 >> 1 |

+--------+

|      1 |

+--------+

In binary, 3 is 11, shifted right by 1 with 1 floating past the ones column (or 1.1 if you'd like, although there is no decimal point in binary notation). Because you're dealing with integers, the numbers to the right of the "decimal point" are dropped (perhaps we should call it the binary point, but there's probably a Hollywood movie coming out by that name), and you're left with 1 (in both binary and decimal). For example:

mysql> SELECT 19 >> 3;

+---------+

| 19 >> 3 |

+---------+

|       2 |

+---------+

In the example above, 19 is 10011, shifted right by 3 is 10, with 011 dropping away. 10 is 2 in decimal.

mysql> SELECT 4 >> 3;

+--------+

| 4 >> 3 |

+--------+

|      0 |

+--------+

This one shifts too far to the right, losing all the bits.

ADVANCED JOINS(INNER,OUTER,RIGHT, AND NATURAL JOINS)

INNER JOINS
mysql> SELECT first_name,surname,value FROM customer INNER JOIN sales ON id=customer;

Left Joins (Left Outer Joins)

mysql> SELECT first_name,surname,value FROM sales LEFT JOIN customer

 ON id=customer;

Right Joins (Right Outer Joins)

mysql> SELECT first_name,surname,value FROM customer RIGHT JOIN sales ON id=customer;

+------------+-------------+-------+

| first_name | surname     | value |

+------------+-------------+-------+

| Yvonne     | Clegg       |  2000 |

| Winston    | Powers      |   250 |

| Winston    | Powers      |   500 |

| Patricia   | Mankunku    |   450 |

| Yvonne     | Clegg       |  3800 |

| Johnny     | Chaka-Chaka |   500 |

| NULL       | NULL        |   670 |

+------------+-------------+-------+

Natural Joins

mysql> SELECT first_name,surname,value FROM customer NATURAL JOIN sales;

+------------+-------------+-------+

| first_name | surname     | value |

+------------+-------------+-------+

| Yvonne     | Clegg       |  2000 |

| Winston    | Powers      |   250 |

| Winston    | Powers      |   500 |

| Patricia   | Mankunku    |   450 |

| Yvonne     | Clegg       |  3800 |

| Johnny     | Chaka-Chaka |   500 |

+------------+-------------+-------+

JOINING RESULTS WITH UNION

To see the use of this statement, let's create another table, containing a list of customers handed over from the previous owner of your store:

mysql> CREATE TABLE old_customer(id int, first_name varchar(30),surname varchar(40));

mysql> INSERT INTO old_customer VALUES (5432, 'Thulani', 'Salie'),(2342, 'Shahiem', 'Papo');

Now, to get a list of all customers, both old and new, you can use the following:

mysql> SELECT id, first_name, surname FROM old_customer UNION SELECT id, first_name,surname FROM customer;

+------+------------+-------------+

| id   | first_name | surname     |

+------+------------+-------------+

| 5432 | Thulani    | Salie       |

| 2342 | Shahiem    | Papo        |

|    1 | Yvonne     | Clegg       |

|    2 | Johnny     | Chaka-Chaka |

|    3 | Winston    | Powers      |

|    4 | Patricia   | Mankunku    |

+------+------------+-------------+



USER VARIABLES

The value of a variable is set with the SET statement or in a SELECT statement with :=. To see all the sales reps with a commission higher than the average commission, you could do the following:

mysql> SELECT @avg := AVG(commission) FROM sales_rep;

+-------------------------+

| @avg := AVG(commission) |

+-------------------------+

|                 11.0000 |

+-------------------------+
mysql>SELECT surname,first_name FROM sales_rep WHERE commission>@avg;
+----------+------------+
| surname  | first_name |
+----------+------------+
| Gordimer | Charlene   |
+----------+------------+

The at (@) sign signifies a MySQL variable. The average
commission is stored in the variable @avg, which can
be accessed again at a later stage.

PERFORMING TRANSACTIONS WITH BEGIN AND COMMIT



mysql> BEGIN;

Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO innotest(f1) VALUES(2);

Query OK, 1 row affected (0.06 sec)

mysql> COMMIT;

Query OK, 0 rows affected (0.05 sec)

mysql> EXIT

Bye

C:\Program Files\MySQL\bin> mysql firstdb

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 9 to server version: 4.0.1-alpha-max

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT f1 FROM innotest;

+------+

| f1   |

+------+

|    1 |

|    2 |

+------+

2 rows in set (0.11 sec)


Table Locks

The syntax to lock a table is as follows:

LOCK TABLE tablename {READ|WRITE} To unlock a table simply use the UNLOCK TABLES statement, as follows:

UNLOCK TABLES

The following demonstrates a table-level lock in action, and will work with any table type. First, lock the table from Window1:
mysql> LOCK TABLE customer READ;
Query OK, 0 rows affected (0.01 sec)

Other threads can now read, but not write, as you can see by
trying the following from Window2:
mysql> SELECT * FROM customer;
+------+------------+-------------+
| id   | first_name | surname     |
+------+------------+-------------+
|    1 | Yvonne     | Clegg       |
|    2 | Johnny     | Chaka-Chaka |
|    3 | Winston    | Powers      |
|    4 | Patricia   | Mankunku    |
+------+------------+-------------+


RUNNING IN MYSQL BATCHMODE

Doing this is called running MySQL in batch mode (as opposed to interactively when you connect to the server and type in the commands yourself ).

Create a text file test.sql containing the following two lines: 

INSERT INTO customer(id,first_name,surname) VALUES(5,'Francois','Papo');

INSERT INTO customer(id,first_name,surname) VALUES(6,'Neil','Beneke');

Remember to add a hostname, username, and password if required.
(This example shows the shortened version for ease of reading.)

If you connect to the MySQL server now, you'll see that these two
records have been added:
mysql> SELECT * FROM customer;
+------+------------+-------------+
| id   | first_name | surname     |
+------+------------+-------------+
|    1 | Yvonne     | Clegg       |
|    2 | Johnny     | Chaka-Chaka |
|    3 | Winston    | Powers      |
|    4 | Patricia   | Mankunku    |
|    5 | Francois   | Papo        |
|    6 | Neil       | Beneke      |
+------+------------+-------------+