Basic Queries

  [WHERE]

·         [GROUP BY]

·         [HAVING]

·         [ORDER BY]

SELECT degree,count(name) from student GROUP BY degrees;

SELECT degree,count(name) from student WHERE year=2 GROUP BY degrees;

SELECT degree as “course”,count(name) from student GROUP BY degrees HAVING count(name)>3;

SELECT *from student ORDER BY name desc;

Security for the Database

There are guidelines to access your databases and you can create your own user and set your own password that no one can access but you.

Here are the examples:

Create user:

This statement creates new user accounts in mysql.

Grant:

The grant statement gives you the power to enable all, or a selection of, user privileges for a database or for a specific table.

Revoke:

 Enables administrators to remove account privileges to access the user.

http://dev.mysql.com/doc/refman/5.0/en/security-guidelines.html

Account Management

It is stored in the tables of the mysql database. 

CREATE USER

This statement creates new user accounts in mysql.

 The grant statement gives you the power to enable all, or a selection of, user privileges for a database or for a specific table.

This statement state the syntax of renaming the user in your mysql.

  Enables administrators to remove account privileges to access the user.

 This statement removes one or more user accounts. 

This statement assigns a password to an existing user account.

http://dev.mysql.com/doc/refman/5.0/en/account-management-sql.html

Privilages System

Some commands:

Select_priv                Permission to run SELECT queries

Insert_priv                  Permission to run INSERT statements

Update_priv              Permission to run UPDATE statements

Delete_priv                Permission to run DELETE statements

Create_priv               Permission to CREATE tables and databases

Drop_priv                  Permission to DROP tables and databases

Reload_priv              Permission to RELOAD the database (a FLUSH statement for example)

Shutdown_priv          Permission to SHUTDOWN the database server

Process_priv            Permission to view or kill PROCESSes.

File_priv                    Permission to read and write FILEs (for example LOAD DATA INFILE)

Grant_priv                 Permission to GRANT available permissions to other users

References_priv       Permissions to create, modify or drop INDEXes

Alter_priv                   Permission to ALTER table structures.

http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html

MYSQLCHECK

Optimizing a table

Optimize- to write computer programming instructions for a task in as few lines as possible to maximize the speed and efficiency of program execution. From the definition itself, optimizing the table is to optimize the table. It is to give the high quality of the table. When you delete lot of rows from a table, optimizing it helps to get the unused space and defragment the data file. This might improve performance on huge tables that has gone through several updates. The command for optimizing the table: optimize table [table_name].

Checking a table

It is for checking the tables for error. The command for checking the table: check table [table_name]. It is a default operation. It can also check views for problems, such as tables that are referenced in the view definition that no longer exist. It is also supported for partitioned tables. There are types of check: quick, fast, changed, medium, and extended. It also changes the table if there is an error.   

Repairing a table

The advantage or benefit of repairing a table is fixing the table except the unique keys that are not unique. The command for repairing a table: repair table [table_name]. It is for repairing the table for the possible corrupt table. The repair table statement might produce many rows of information for each repaired table. Repairing the table also checks the table to see whether an upgrade is required.

Analyzing a table

Analyzing tables improves performance by updating the index information for a table so that MySQL can make a better decision on how to join tables. While mysqlcheck is executing, the analyze command the table is locked and available for other process only in the read mode. Analyzing the table analyzes and stores the key distribution for a table. The command for analyzing the table:analyze table [table_name].

MYSQL SERVER LOGS

The Error Log

 The Error log contains user-defined events and certain system events. Also you can troubleshoot your mysql server if there is a problem related to it.  When you start your mysql using mysql-noinstall the error log is also running. The error log contains the error that present in your databases. You can save your error log in your C:\WINDOWS\my.ini [log = filename.log]. And when you shutdown your databases, the error log is also shutting down.

The General Query Log

The General Query Log serves as the keeper of the mysqld. This is the general record of what mysqld is doing. Every time we connect or disconnect the mysqld, the server writes information and it logs the SQL statements. It is very useful because when you suspect an error and want to know the exactly details of what the client sent, the general query log can show the record. You can save your error log in your C:\WINDOWS\my.ini [log-error = filename.log].

Binary Log

The Binary log includes the events that describe the changes in database such as the tables. Also it describes the changes in the sql statements which we used. The binary log also contains information about how long each statement took that updated data. The binary log is not used for statements such as SELECT or SHOW that do not modify data. If you want to log all statements, use the general query log. You can save your error log in your C:\WINDOWS\my.ini [log-bin = filename.log].

The Slow Query Log

The slow query log took the sql statement more than the long query time which is the default value is 10. When you begin mysqld, the minimum is zero but the priority is 1 and the value for this variable must be integer. You can save your slow query log in C:\WINDOWS\my.ini [log-slow-queries=file_name.log]. As you start the mysqld it also start and when you stop the mysqld it also stop. 

INSTALLING OF MYSQL NOINSTALL

Step 1:

Extract the zip file of mysql-noinstall. (any version)

Step 2:

Create a folder mydata in C: or D: and copy the folder data (found inside the mysql) and put it to the mydata folder that you created.

Step 3:

Create a my.ini file inside the WINDOWS FOLDER found in C: . The my.ini file contains:

            [mysqld]

# set basedir to your installation path

basedir=c:/mysql

# set datadir to the location of your data directory

datadir=c:/mydata/data

Step 4:

Run the following command to the command prompt:

            cd c:/mysql/bin

            mysql start

            mysql –u root

to shutdown:

            mysqladmin –u root