Wiki - SQL Commands

[Kayot]

I'm putting this guide here so as to not lose it. This was a chat involving samandhi, ndnet, and myself. The chat involved SQL syntax and bitmask, which till now bitmasks were elusive to me. Bitmask work on interesting terms.

ndnet wrote the bitmask part, I already understood the basic idea, but this model really helps.

Example -

Select * From table;

Desc - This puts up all the tables, let assume one bitmask field. In this example there is only 5 entry's.

     +------+
     | mask |
     +------+
     | 5 | <--- 0101 = 0 + 4 + 0 + 1 = 5
     | 2 | <--- 0010 = 0 + 0 + 2 + 0 = 2
     | 3 | <--- 0011 = 0 + 0 + 2 + 1 = 3
     | 8 | <--- 1000 = 8 + 0 + 0 + 0 = 8
     | 4 | <--- 0100 = 0 + 4 + 0 + 0 = 4
     +------+

Goal - I want every line where the number 4 (or 0100) is turned on, this would include values 4 and five 5 because 4 = 0100, and 5 = 0101. Both have the number 4 bit turned on.

Select * From table where mask & 4;

Result:

     +------+
     | mask |
     +------+
     | 5 | <--- 0101 = 0 + 4 + 0 + 1 = 5
     | 4 | <--- 0100 = 0 + 4 + 0 + 0 = 4
     +------+

But what if I don't want 4's? What if I want everything but 4's?

Select * from table where not mask & 4;

     +------+
     | mask |
     +------+
     | 2 | <--- 0010 = 0 + 0 + 2 + 0 = 2
     | 3 | <--- 0011 = 0 + 0 + 2 + 1 = 3
     | 8 | <--- 1000 = 8 + 0 + 0 + 0 = 8
     +------+

And thats all about bitmaks, a list of operators;

BINARY Cast a string to a binary string
BIT_AND() Return bitwise and
BIT_COUNT() Return the number of bits that are set
BIT_LENGTH() Return length of argument in bits
BIT_OR() Return bitwise or
BIT_XOR() Return bitwise xor
& Bitwise AND
| Bitwise OR
^ Bitwise XOR
/ Division operator