Retrieval Queries in MySQL (Select Statements)

Select Syntax is a query method use to retrieve data records.


Download database here: http://www.ziddu.com/download/20022849/RetrieveQuerydatabase.txt.html

::EXAMPLES::

Simple SQL Queries:
SELECT       age
FROM          customer
WHERE        name='Sarah Goodman';

Use of Aliases:
SELECT       S.Fname, S.Lname, E.Fname,   E.Lname 
FROM          Student S E 
WHERE        S.ID=E.ID

Unspecified where clause:
SELECT       name
FROM          customer;

Use of *:
SELECT           *
FROM              customer
WHERE            gender='M';

Use of distinct:
SELECT      DISTINCT name
FROM         customer;

Set operation:
(SELECT     name
FROM         customer
WHERE       gender='M';)
UNION
(SELECT      age
FROM          customer
WHERE        gender='F');

Nested queries:
SELECT       Fname, Lname
FROM          Student
WHERE        ID IN  (SELECT  Dnumber
FROM          Department
WHERE        Dname='ComSci' )


Substring Comparison:
SELECT        gender
FROM          customer
WHERE        name
LIKE            'Sarah Goodman';

Database Security

Access Privilege System

Database is the most important part of a system. all files that are process in the main system are stored in this area/part of system. So, you must secure it properly. To secure it:


* You must not give anyone access to the users table in the MySQL database.
* You should know how MySQL access privilege works.
* You should always use SHOW GRANTS statement to check which account you have access.
* You should not store plain text password in your database.
* You should not transmit plain data over the internet.
* You should invest a firewall.


These are some of the security guidelines that we should aware to secure our databases.


There are too many Permission Privilege in grant and revoke
Select_priv          Permission to run SELECT queries
Create_priv         Permission to CREATE tables and databases
Insert_priv           Permission to run INSERT statements


see others: click here...

Account Management

You should create a user rather than using root because using root is unsafe and unsecured.


+ Creating user:
This is the code to create a user.
CREATE USER ‘username’@’localhost’ IDENTIFIED BY ‘password’;

After creating a user the user is now stored in mysql database.

As you can see, we now created a new user. This is where the user is stored.



+ Grant user:
This is the code to grant a user.
GRANT USAGE ON *.* TO 'username'@'localhost' WITH GRANT OPTION

+ Rename user:
This is the code to rename a user.
RENAME USER ‘username’@’localhost’ TO ‘new_username’@’localhost’;

After renaming a user...

Now, you've change your username.




+ Drop user:
This is a code to drop a user.
DROP USER ‘username’@’localhost’;

Source: www.mysql.com

MySQL Logs

The Error Log


Error log is a log that contains information indicating the start and stops of MySQL server like mysqld and also contains critical errors while the server is running. It is an important point of reference when administering a MySQL Server. You can enable error log by writing log-error in option file (my.ini) and the log files will go to the data. You can name your error logs by writing log-error[=file_name] in option file(my.ini).




The General Query Log


General Query log is a log that contains information about what MySQL server like mysqld is doing. The information that is written inside the log was when the users connect or disconnect. You can enable general query log by writing log in option file (my.ini) and the log files will go to the data. You can name your general query logs by writing log[=file_name] or –l [file_name] in option file(my.ini).




The Binary Log


 Binary log is a log that contains information about events that describe the changes in table data. For example, creating, deleting and editing table operation can be inserted in binary log. It is used on master replication servers as record of the statements. You can enable binary log by writing log-bin in option file (my.ini) and the log files will go to the data. You can name your binary logs by writing log-bin[=base_name] in option file(my.ini).

The Relay Log


Relay log is the same as binary log that contains information about database events and describe database changes such as creating, deleting and editing a table. The term “relay log file” generally denotes an individual numbered file containing database events. Events in the relay log are executed on the slave as part of the SQL thread. You can enable relay log by writing relay-log in option file (my.ini) and the log files will go to the data.

The Slow Query Log


Slow Query log is consisting of MySQL statements that take time to execute. Optimize, analyze and alter table MySQL statements is an example of slow query log but you need to write log-slow-admin-statements in option file to written it to the slow query log. You can enable slow query log by writing log-slow-queries in option file (my.ini) and the log files will go to the data. You can name your slow query logs by writing log-slow-queries[=file_name] in option file(my.ini). 

Installing MySQL on Windows

There are too many ways in installing MySQL. But for me, its better to install the mysql-nointall any version. Because it is the best way to have a better control in your MySQL and in your system.


Follow the steps:


Step 1:
Download mysql-noinstall-5.1.63-win32 on http://www.mysql.com/.




Step 2:
Extract the ZIP archive in any of your partition. And name the folder mysql.




Step 3:
Create a new folder naming mydata. And copy the data folder in mysql folder to mydata folder.




Step 4:
After copying the data folder. Create an option file. Name it my.ini.


Write it inside the my.ini file.

Step 5:
After creating an option file. Paste it in your system through windows folder.




Step 6:
Open the command prompt and ENJOY!!!

Table Maintenance

Optimizing a table

Optimizing a table will automatically defragment a MySQL table and it is very useful for the database when your table is updated or deleted some records. Optimizing tables will work when: first, when there was a deletion of rows in a table it repairs the table. Second, when index pages are not sorted this function used to sort the index page. Lastly, when the table’s statistics are not up to date it is used to update the table’s statistic.

Checking a table

Checking a table is used for checking errors and used to verify data and table handler. Checking command in MySQL will check the file size, record deletion, index reference, data record references index and record links. It also checks the table if it is corrupted, it will find bugs for us to determine what was the error and it will be written in error log. It compresses all information sent between the client and the server.

Repairing a table

Repairing a table is used for repairing the possible corrupted table. If you want to repair corrupted tables, you need to back it up first or move your data file to safe place because if you fail the data might be in worse shape or may become unsalvageable.  It restores index file errors and also used if the records file is corrupted or crashed.  It makes sure that the table is still working, and then it indicates whether the table was valid or whether corrupted.

Analyzing a table

Analyzing a table is used to update index information for a table to improve performance and MySQL can make a better decision on how to join tables. It used to analyze and store the key distribution to a table. It is also used for deciding the order in which tables are joined when we are performing a join on something other than a constant. It is used when connected to a database on the server.