The SELECT Statement - Retrieval Queries

Retrieval queries are needed in order to get information from database. The SELECT statement is used for retrieving information from the databases.

SELECT  <attribute_list>
FROM    <table_list>
WHERE   <condition>

The attribute list is the attribute you want to select, the table list is the name of the table to be queried and the condition filters the information to be retrieved.

Example of simple SELECT query:

In this example, we only select 2 attributes from the table employee, without a condition.
When you add conditions to the select statement, this filters the information to be retrieved by the SELECT statement.
When we add the conditon "where sex='M'; are displayed.

Using of asterisk(*) for attributes will select all available attributes from the table.

 

Using aliases will modify the headings of the retrieved queries.

The statement to be added: as 'alias' 

Database Security

      
The Database is the storage of all data of systems, and SQL has security guidelines to be able to secure the data and restrict access to the database. Here are some of the security guidelines to be followed:

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

 MYSQL Access Privilege System

 Create User

 Using the root user is unsecure  because the root has all the access/privileges and no restrictions, thus we should create a user with restricted access privileges to prevent database ruin. In the example above, a user 'joed' was created without a password.

• To enable the user to connect with no password (which is insecure), include no IDENTIFIED BY clause:
  

  CREATE USER 'joed'@'localhost';

• To assign a password, use IDENTIFIED BY with the literal plaintext password value:
  

  CREATE USER 'joed'@'localhost' IDENTIFIED BY 'mypass'; 

  Grant User

   Grant is the command to grant priviliges to a user. To use grant, you must first have an existing user account created.In our example, the use 'joed' was granted SELECT to table department in database college.

       Revoke

        Revoke is used to revoke/remove account privileges granted on a user.
        Example:

In this example, the user revoked the SELECT on college.department, meaning, the user 'joed' can no longer use the SELECT command in college.department.

Here is the list of some privileges in MYSQL:

User Account Management

User Account management allows us to manage the users that will be available to have access in the database. Since we already discussed the how to CREATE a user, we'll discuss the remaining Account Management like dropping, renaming or setting a new password for the user.

SET PASSWORD

 In the example above, the user 'joed' set new password which is 'newpass'.User can change their passwords anytime they want, just be sure to consult the security guidelines when choosing your password.

RENAME USER

In the example above, the user 'joed' has been changed to 'joedsantiago'. This does not alter the password for the user, the password remains the same. 

DROP USER

DROP USER does what is it supposed to do, drop/erase the user, also removing any grants and privileges on that user.

Different MYSQL Server Logs

Error Logs
These logs are the record of startup and shutting down of server. It also records the errors occuring when you start or shutdown the server.
log=general.log

General Query Logs
The general query log stores records of the sql queries the user inputted. It is necessary when tracing the sql queries used by user.
log-bin=binary.log

Binary Logs
The binary log contains “events” that describe database changes such as table creation operations or changes to table data. It also contains events for statements that potentially could have made changes (for example, a DELETE which matched no rows). The binary log also contains information about how long each statement took that updated data.
log-error=error.log

Slow Query Logs
Slow query logs can be use to spot queries which takes longer time than usual. It is used so that user can optimize queries which are slow.
log-slow-queries=slow.log 

MYSQL Table Maintenance

Analyze table
One of the reasons to issue ANALYZE TABLE is that when we have a lot of modification of a table (INSERT or DELETE for example), over time the optimizer might not make the best choices when trying to decide if it should use a specific index or not. By analyzing the table, we help it to make a more educated choice.
To analyze tables, use the command ANALYZE TABLE ‘tablename’;

Check table
Well, check table does what it sounds like, it checks table for errors. It also checks if a table is compatible to current version after an upgrade. To use this, use the commands: CHECK TABLE ‘tablename’;

Optimize table
OPTIMIZE TABLE is used to remove overhead, sort indexes for better access, and generally keep your tables in good order.
To use it, type OPTIMIZE TABLE ‘tablename’;

Repair tables
REPAIR TABLE tries to repair a corrupted or broken table.
Command: REPAIR TABLE ‘tablename’;