The SELECT Statement Part 2: Multiple Queries, Multiple Tables and More...

     This is the continuation of the previous discussion about the "SELECT" statement. Previously, we discussed using the "SELECT" statement on single query retrieval. Today we will further discuss the "SELECT" statement on how to use it in multiple queries and multiple tables.

NESTING OF QUERIES
     A nested query is a query that occurs if a query is used on another query. That another query is called the 'outer query'. We use nested query if you want to get a more specific data in a database which is only attainable if you get another query from the database.
     Here is sample situation. Suppose you would like to get the maximum price of the albums with the highest possible ratings in a database. You are likely to use this statement "SELECT max(price) from album where rating = max(rating);".
     This statement would result to this error:

     To fix this statement, you would need to use a nested query. Here is an example:

NULL IN SQL QUERIES
     The NULL keyword is another keyword that can be used after the WHERE-clause. The NULL keyword is used to determine if a value is missing or undefined or not applicable in an attribute. To use this you will need to use the IS NULL or IS NOT NULL keywords.
     Here are some examples:

THE JOINED RELATIONS
     Joined relations are used to get data from two different tables at the same time. It is often used on conditions where you would need to combine two separate tables or relations. To use this you would need to use the JOIN function. You can use either LEFT JOIN, INNER JOIN, RIGHT JOIN and OUTER JOIN keywords.
     Here is an example:

AGGREGATE FUNCTION
     From the examples above, there is a COUNT and MAX keywords used. Those keywords are an example of aggregate functions. Aggregate functions are used like mathematical equations to make retrieval of computed data easier. The aggregate functions are the COUNT, SUM, MAX, MIN and AVG. Just like their keywords suggests, the COUNT gets the total number of count on that attribute, the SUM gets the sum of all data in that attribute, the MAX gets the maximum value of the data in that attribute, the MIN gets the minimum value of the data in that attribute and the AVG gets the average of the data in that attribute.
     Here is an example:

GROUP BY-CLAUSE
     GROUP BY-clause is used to specify which will be the grouping attribute. The grouping attribute will be the attribute wherein all value are different. Usually if that attribute has many data with the same value, the one that will be displayed is the first data of that value.
     Here is an example:

HAVING-CLAUSE
     HAVING-clause is usually added at the latter part of the attribute after a GROUP BY-clause. It is used to add condition on the group created by the GROUP BY-clause.
     Here is an example:

ORDER BY-CLAUSE
     ORDER BY is the clause use to sort the data displayed either in ascending or descending order. It is usually added at the last part of the statement. To choose which order would you like to use just add ASC for ascending or DESC for descending at the end of the ORDER BY-clause.
     Here is an example:

The SELECT Statement Part 1: Single Retrieval Query Statement

     Retrieval of queries are done if you want to gather a data in the database and do something about that data like viewing and updating. The retrieval query used in MySQL is the "SELECT" statement. There are different ways of using the "SELECT" statement. Today we will discuss how to use the "SELECT" statements in single retrieval query statement.

SIMPLE SQL QUERY
     The simple SQL query is using the "SELECT" statement with basic query parts. These parts include these:
   SELECT  <attribute_list>
   FROM    <table_list>
   WHERE   <condition>
     The attribute_list is the list of attribute you would like to retrieve or view, the table_list is the list of table you want to get the attribute from, and the condition is the expression that you wanted to verify first before retrieving the data.
     Here is an example:

ALIASES
     Another way of displaying the attribute is by using aliases. Aliases are used to modify the headings of the attributes displayed in the table. To use Aliases you will need to use the "AS" keyword after the attribute you wanted to rename.
     Here is an example:

UNSPECIFIED WHERE-CLAUSE
     An unspecified where-clause means that you don't need a condition in getting your data. This will result to the display of all possible records in the database.
     Here is an example:

THE USE OF Asterisk(*)
     Asterisk(*) is used if you don't want to specify which attribute would you like to display or use. It would mean that all attributes in that table will be displayed.
     Here is an example:

USE OF DISTINCT
     If you want to display records of the database wherein there is a distinct value, you can use the DISTINCT keyword. To use this just add "DISTINCT" before the attribute you wanted to have a distinction with.
     Here is an example:

     Now these are the basic query retrieval techniques in MySQL. Next time, we will continue on discussing the "SELECT" statement on how to use it in multiple queries and multiple tables.

MYSQL Database Security Part 2: User Account Management

     Another important thing in MYSQL Security is the user account management. User Account management allows us to manage the users that will be available to have access in the database. We already discuss how to create a user now we are going to discuss other functions you can do to a user.

     There are many different statements you can perform to a user. Today we’ll discuss the most basic statements. Those are the CREATE USER, DROP USER, GRANT, RENAME USER, REVOKE and SET PASSWORD.

     In the previous discussion, specifically MYSQL Security Part 1, we have already discussed how to use the create user, grant and revoke statements. As a recall we create user by using the CREATE USER statement and the syntax would be like this:

  CREATE USER ‘the_username’@’localhost/ip_address’ IDENTIFIED BY 'the_password’;

And we grand and revoke privileges to a user by using the GRANT and REVOKE statements like these:

  GRANT <the_privilege> ON <databasename>.<tablename> TO ‘the_username’@’localhost /ip_address’;

  REVOKE <the_privilege> ON <databasename>.<tablename> FROM ‘the_username’@’localhost /ip_address’; 

Now we’ll discuss the other statements we have mentioned earlier. 

DROP USER

     Dropping a user would mean you’re going to remove that user.  To be able to drop a user the user logged in must have the global CREATE USER privilege or the DELETE privilege. The syntax for deleting a user is this:

  DROP USER ‘the_username’@’localhost /ip_address’;

This is an example: 

RENAME USER

The RENAME USER statement is used to rename the existing user. To be able to rename a user, the user logged in must have the UPDATE privilege. The syntax for this statement is this:

  RENAME USER ‘the_username’@’localhost/ip_address’ TO ‘new_username’@’localhost/ip_address’;

This is an example:

SET PASSWORD

The SET PASSWORD statement is used to assign a password to an already existing user account. It can be used to change the old password to a new one. The syntax for this statement is this:

  SET PASSWORD FOR ‘the_username’@’localhost/ip_address’ = PASSWORD(‘new_password’);

This is an example:

For further discussion about MSQL User Account Management you can click here.

Reference: mysql.com

MYSQL Database Security Part 1: Access Privilege System

     The database is the central source and storage of data a system has so securing the database is one of the main concerns of system developers. In the MYSQL database, there are security guidelines and methods made available to be used to secure the data and restrict the access in the database. Here are some security guidelines:

• Do not give anyone (except MySQL root accounts) access to the user table
• Learn how the MySQL access privilege system works
• Do not store plaintext passwords in your database
• Do not choose passwords from dictionaries
• Invest in a firewall
• Applications that access MySQL should not trust any data entered by users
• Do not transmit plain (unencrypted) data over the Internet

      These are the security guidelines everyone should be aware to have a secured database but today we are going to discuss some methods and syntaxes that would make our database more secure.
     In the security guidelines above, the second line talked about learn how the MYSQL access privilege works. Now we’ll explain how it works.
     The MYSQL access privilege system is created to authenticate which user accesses the database and it grants different kinds of privileges a user can access in the database. It can make the database accessible to users but make them have limited access to the functions one can do to the database or in its structure.      There are different kinds of privileges provided by MYSQL but before we discuss those, we are going discuss to how to create a user in MYSQL.
     Creating a user is important because using the root user is unsafe and unsecure because the user has all the privileges that could ruin the database. We create a user to limit the access of a user to the database. To create a user we’ll use the syntax like this:
     CREATE USER ‘the_username’@’localhost/ip_address’ IDENTIFIED BY ‘the_password’;
     Here is an example:

     To provide privileges to the user we are going to use the “GRANT” statement. The syntax would be like this: 
  GRANT <the_privilege> ON <databasename>.<tablename> TO ‘the_username’@’localhost/ip_address’;
     Here is an example:

     The ‘SELECT’ above is the privilege that is given to the user ‘chochologist’@’localhost’. The ‘college’ is the name of the table in the database named ‘clsu’. 

     Here are some privilege that are available for MYSLQ:

     Sometimes you may need to remove the privileges granted to a user. To do this you can use the ‘REVOKE’ statement. The syntax would be like this:

  REVOKE <the_privilege> ON <databasename>.<tablename> FROM ‘the_username’@’localhost /ip_address’;

     Here is an example:  

     For more kinds of privileges you can visit the MYSQL privileges site here.

     References: mysql.com

Table Maintenance: Check Analyze Optimize Repair

          MYSQL tables has many maintenance statements. They are the analyze, backup, check, checksum, optimize, repair and restore. Today I'll be discussing four of them, precisely the check, analyze, optimize and repair. These four are the most familiar statements among the maintenance statements. I'll discuss them one by one. Let's start with "check".

CHECK TABLE

          As the statement suggests, it is a maintenance statement that checks if there is an error in the table. The syntax to use this is CHECK TABLE <table name>;. The output in this statement are the table, the op, the msg_type and the msg_text. Here is a sample image:

          As you can see the table column contains the name of the table together with its corresponding database. The op column always contains check as its value. The msg_type may contain the values 'status', 'error', 'info' and 'warning'. And finally the msg_text displays an informational message about the table.

         The 'check table' statements have different options to be used. They are the quick, fast, changed, medium and extended options. The quick option informs that it must scan the rows to check for incorrect links that is not needed. The fast option informs to check only the tables that have  not been closed properly.  The changed option checks only the tables that have been changed since the last check or that have not been closed properly. The medium option tells to scans the rows to verify that deleted links are valid. This also calculates a key checksum for the rows and verifies this with a calculated checksum for the keys. Finally the exteded option tells to do a full key lookup for all keys for each row. This ensures that the table is 100% consistent. To use these options you can use this syntax: CHECK TABLE <table name> <option>;.

ANALYZE TABLE
          Analyze table is the maintenance statement that analyzes and stores the key distribution for a table. The key distribution is used by mysql to decide the order in which tables should be joined when a join statement is used on something other than a constant. In addition, key distributions can be used on deciding which indexes is to be used for a specific table within a query.
          The syntax to use this statement is ANALYZE TABLE <table name>;. Just like the check table statement, analyze table also outputs the table, the op, the msg_type and the msg_text. Here is a sample image:

       
OPTIMIZE TABLE
          Optimize table is the maintenance statement that is used when you have deleted a large part of a table or if you have made changes to a table with rows that have variable-length. It is used to reclaim the unused space after deleting a row and it is also used to defragment the data file. This statement can improve the performance of statements performed on an optimize table. 
          The syntax to use this statement is OPTIMIZE TABLE <table name>;. Just like the two table statements above, optimize table also outputs the table, the op, the msg_type and the msg_text. Here is a sample image:

REPAIR TABLE
          Just like its statement suggest, the repair table is a maintenance statement that repairs a corrupted table. This statement gets back all your data from a myISAM table. Repair table is not a recommended to be used because it may cause data loss but when you need to use it you should first create a backup of your table. Just like the check table statement, repair table also have options like quick and extended.
          The syntax to use this statement is REPAIR TABLE <table name>;. Just like the three table statements above, optimize table also outputs the table, the op, the msg_type and the msg_text. Here is a sample image:

          These statements are the basic table maintenance statements available in MYSQL. Each statements have their additional options to be use. You can explore it more by trying different options if you like but I recommend to backup your tables before using those. You might never know what to expect when using these options.

Source: mysql.com

The MYSQL Server Logs

          In MYSQL server, there are many logs available which helps find out what activity is taking place. Those are the error log, general query log, binary log and the slow query log. Today we will discuss the importance and the function of these logs.

ERROR LOG
          The error log enables us to know the problems that are encountered while starting, running or stopping the server of simply the mysqld.exe.  It contains the when was mysqld started and when was it stopped. It also contains critical errors that occurred while the server was running. It also writes a message to the error log if mysqld notice that a table needed to be automatically checked or repaired. The error log is important because using the error log you can determine where or what operation did the mysqld died.  

          The default name of error log is 'host_name'.err. To change the name of the error log file you can go to your my.ini file in windows and write at the bottom log-error='name you like'.log or if you want you can use .err instead of .log to show that it is an error log.  

GENERAL QUERY LOG

          The general query log shows us the established client connections and statements received from the clients. It is the general record of the activities done by mysqld. When the client connects and disconnects the server writes its log in the general query log. It also records the SQL statements received from the clients. It is important because it can be used to determine what the client sent to mysqld so you would know what caused an error. It makes you aware what the mysqld received from a client which can be used to determine the causes of an error.
           If you want to change the name of the general query log file you can go to your my.ini file in windows and write at the bottom log='name you like'.log and save it.

BINARY LOG

          The binary log shows us the statements that may have changed the data. It also contains events that describe the changes in the database like creation of a table or changes to the table data. It also shows information about how long each statement took that updated the data. 
          The binary log is important because of its two purposes. The first one is it can be used for replication. The binary log is used on master replication servers as a record of the statements to be sent to slave servers. The master server sends the events contained in its binary log to its slaves, which execute those events to make the same data changes that were made on the master. 

          Its second purpose is to keep the database up to date from the point of the backup after it is restored using a backup. After a backup has been restored, the events in the binary log that were recorded after the backup was made are re-executed.

          If you want to change the name of the binary log file you can go to your my.ini file in windows and write at the bottom log-bin='name you like' and save it.

SLOW QUERY LOG

          The slow query log shows us the queries that took more than the long query time seconds to execute. The minimum value of long query time is 1 and its default value is 10.  Slow queries are important because it can be used to find queries that take long time to execute. Those queries that take long time to execute can become subject for optimization.

           If you want to change the name of the slow query log file you can go to your my.ini file in windows and write at the bottom log-slow-query='name you like'.log and save it.

          These logs are created to provide information about the server options that enable logging in each specific log section. By default these logs are disabled. You can use the my.ini to control these logs and assign the name you want to use.  Hope you like the discussion.

Source: mysql.com

How to use MYSQL no-install on Windows

          MYSQL is the world's most popular open source database. Installing it is quite easy but people usually use the default installation and it makes the database easy to be detected. Personally, I think it is not bad but I prefer using the no-install mysql. Using it makes you in control on the location of the data it stores and other things like logs and others.
          Now I will teach you how to use the mysql no-install and show how you can customize its storage location on your own. You can use the following procedures:
      1. First of all, download the mysql no-install zip file here.
      2. Unzip the file and rename the folder anything you want. (I'll rename the folder mysql.)
      3. Then place the folder to any location you want. (I'll put mine in drive D: so the directory is D:\mysql.)
      4. If you want to control the location of the data create a folder you want the data to be place.(I'll name my folder mydata and place it in the drive D so my directory for the data will be D:\mydata.)
      5. Go to the mysql folder and copy the data folder and paste it in the mydata folder.
      6. Then to customize the directory of the data open a notepad and write these according to your directory:
      [mysqld]
      basedir="D:\mysql"
      datadir="D:\mydata\data"
      7. Save this as my.ini and place it in the Windows folder.

          Now to access mysql follow these procedures:
      1. Open a command prompt.
      2. Open the directory of mysql\bin. I'll be using these commands:
      d: 
      cd mysql\bin 
       3. To open a server use this command: (add your password after the root if you have one)
      mysqld -u root
       4. Now you have a server open another command prompt and like the procedure above go to the mysql\bin directory.
       5. To access mysql use this command: (add your password after the root if you have one)
      mysql -u root
       6. To close the server exit mysql and use this command:
      mysqladmin -u root shutdown

         Now that ends our tutorial. You can now discover everything else on your own. Using the my.ini file, you have the control on your mysql. Goodluck!