Mysql Table Views

     A view is a specific look or a peek on data from one or more tables. It is created to act as a dynamic, virtual table computed or collated from the data in the database. It acts like a table or is simply a psuedo-table. It is a query that is stored and is named like a table and it can be referenced like a table. Its field can be from one or more real tables in the database. If you insert, edit or delete a data in the view from which the view comes from one table only that data will also be altered in the real table. But if you try to alter a data in a view from different tables mysql does not allow such alteration thus you will end in an error.
     To create a view you will need to use this syntax:
     CREATE VIEW name_of_view AS
     SELECT column_names 
     FROM table_name
     WHERE condition

     In creating views you can use all of the different types queries available in mysql like the ones with the JOIN clause and GROUP clause and with different conditions you like.
     To query a view, you can query like an ordinary table. Alteration of the view will only be acceptable if the view is from only one table. To show the list of views you can use the SHOW FULL TABLES statement.
     Here is an example:

Performing Transactions with BEGIN and COMMIT

     MYSQL supports the use of transaction in its server. Transaction  is a sequential group of database manipulation operations, which is performed as if it were one single work unit. In other words, a transaction will never be complete unless each individual operation within the group is successful. If any operation within the transaction fails, the entire transaction will fail.
     There are many statement you can use to provide control over the use of transactions. I will discuss four of them, the START TRANSACTION or BEGIN statement, the COMMIT statement, the ROLLBACK statement, and the SET AUTOCOMMIT statment. To start a new transaction, you need to use the START TRANSACTION or BEGIN statement. The COMMIT statement coommits the current transaction which makes its changes permanent. The ROLLBACK statement rolls back the current transaction cancelling the changes made to it. The SET AUTOCOMMIT statement disables or enables the default autocommit mode for the current session.
     Here is a sample code that uses transaction with ROLLBACK:
     CREATE TABLE T(Year INT) TYPE=InnoDB;
     BEGIN;
     INSERT INTO T(Year) VALUES(2012);
     SELECT * FROM T;
     ROLLBACK;
     SELECT  * FROM T;
     Here is the output:

     Here is another code that uses transaction with COMMIT and ROLLBACK:
     BEGIN;
     INSERT INTO T(Year) VALUES(2012);
     SELECT * FROM T;
     COMMIT;
     ROLLBACK;
     SELECT  * FROM T;
     Here is the output:

   
      A good practice of using transactions is by using the Table Lock. The Table Lock locks the table being used and prevents other user from accessing that table. This way one user can access the table at a time preventing the inconsistency in changes made to the database. To use Table Locks you will need to use the LOCK TABLES statement like this 'LOCK TABLES T READ'. The T is the table and the READ is the access your giving to that table. To unlock the table use the UNLOCK TABLES statement.
     For more information about Table Locks you can visit the mysql site here.

Logical, Arithmetic, Comparison, and Bit Operators

     Operators are the building blocks of complex queries meaning these operators are used in the SELECT statement. There are 4 types of operators. They are the 'Logical operators', 'Arithmetic operators', 'Comparison operators' and the 'Bit operators'. I will discuss these operators one by one.

LOGICAL OPERATORS

      Logical operators allow you to relate numbers of condition in various ways. The outcome would be reduce to either true(1) or false(0). The logical operators are the AND(&&), OR(||), and the NOT(!). The AND(&&) operator results to true only if both conditions are true. The OR(||) operator results to true if one of the conditions is true. And the NOT(!) operator results to the opposite value of the condition.

ARITHMETIC OPERATORS

     Arithmetic operators are used to perform basic mathematical operations while using the SELECT statement. There are 5 different type of arithmetic operators. They are the (+) sign, (-) sign, (*) sign, (/) sign and the (%) sign. The (+) sign is used to get the sum of the values you want to add. The (-) sign is used to get the difference of the values you want to subtract. The (*) sign is use to get the product and the (/) sign is used to get the quotient. The (%) sign is used to get the remainder of the values you want to divide.

COMPARISON OPERATORS

     Comparison operators are used when making comparison between values. There are many comparison operators available in mysql. There is the (=) sign that checks if both values being compared are equal. The (!=) and (<>) signs checks if both values being compared are not equal. The (>) sign checks if the first value is greater than the second value while the (<) sign checks if the first value is less than the second value. There is also the (>=) sign and the (<=) sign which checks respectively if first value if greater than of equal to or less than or equal to the second value. The (<=>) sign checks if both values are equal including their null values. The IS NULL operation checks if the value contains a NULL value. The IS NOT NULL operation checks if the value does not contain a NULL value. The BETWEEN operation checks if the value your comparing is between the value of the other two values you compared. There is also the LIKE and NOT LIKE operation which checks the first value matches or do no matches the SQL pattern of the second value.

BIT FUNCTIONS
     Bit functions are operators used by bits. There are seven kinds of bit functions. These are the BIT_COUNT(), (&) sign, (~) sign, (|) sign, (^) sign, (<<) sign and the (>>) sign. The BIT_COUNT() returns the number of bits that are set. The (&) sign is the bitwise AND. The (~) sign inverts the bits. The (|) sign is the bitwise OR. The (^) sign is the bitwise XOR. The (<<) sign is the left shif while the (>>) sign is the right shift.

For more information on how to use operators you can visit the site here.