SQL :- structured QUERY language

                               How To Install MySQL


MySQL (pronounced My-Ess-Cue-Ell) is a powerful open source relational database management system that uses SQL. This means that MySQL stores data in tables rather than in one large file. This allows for greater efficiency, flexibility, and speed. MySQL is often used in conjunction with other products from the open source community such as PHP and Apache. This tutorial will teach you how to install and configure MySQL on your computer.
  1. Point to http://dev.mysql.com/downloads/ in your web browser. In the section listed "MySQL Community Edition," click the link designated as the current release. At the time of this writing, the latest stable release of MySQl is Version 5.0. There is also a beta release, which you can obtain if you don't mind a few bugs.  Here is the direct link for the latest stable version.
  2. Select the install package for the platform you own. If you are a Windows owner, simply select the "pick a mirror" link to the right of the text that says "Windows (x86)."
  3. Follow the instructions and select a mirror near to your geographic location. Click the link to download the file and save it onto your computer.
  4. Using a zip extraction program such as WinZip, the free 7-Zip, or the program that comes with Windows, extract the files from the zip to another folder.
  5. Access the folder you created and double click setup.exe.
  6. Follow the instructions for the installation and when you reach the final prompt that reads "configure mysql server now," check the box next to it. Click Finish.
  7. Now we have finished the installation and will begin the configuration. Click "Next."
  8. Click the radio button next to "Standard Configuration" and click "Next."
  9. Check the "Install As Windows Service" and "Launch the MySQL Server Automatically" checkboxes. Click "Next".
  10. Now you need to create a root password you will need to access your server. Type in a password that is not easy to guess and make sure the "enable root access from remote machines" is checked. Click "Next."
  11. On this window, click "Execute" to start MySQL and click "Finish."
  12. To ensure that MySQL is properly installed, go to your task bar and go to "Start" > "All Programs" > "MySQL" > "MySQL Server 4.1" > "MySQL Command line client."
     
  13. Congratulations on installing MySQl to your system.




                     How To Optimize MySQL Queries


If querying is taking lot of time, and database connections and backlogs are growing, then it is time to optimize your database. In MySQL, you can use mysqlreport command to generate report on sufficiently used database and establish a baseline for further actions. You may also be required to modify configuration file (my.cnf) to log slow queries. Identify the slow running queries and the possible causes. Usually, the causes are related to indexes like no index or improper index. This article gives a detailed description of which commands and concepts we can use to optimize MySQL.

Step 1

Using indexes
Using indexes makes the query faster in the same way as an index in a book helps in looking for a particular word.
To know how and which indexes are used in a given query, use EXPLAIN command as a prefix to select query.
EXPLAIN SELECT emp_id FROM ORGN_DATA WHERE dept_code = 'IT';
This command returns following information:

Table - Which table(s) are used in the query
Type - JOIN type. Values can be system, const, eq_ref, ref, range, index, all
Possible Keys - All keys which can be used for indexes
Key - The key actually used for index
Key Length - Shorter keys are better
Ref - Other Columns used with key to get the results
Rows - Number of rows from where data will come
Extra - Extra information. Some possible values can be using index, using where, using temporary, using filesort
You should reconsider your table structure if Possible Keys contain NULL value.
A NULL value in Key field indicates no index is being used. Using no indexes will deter the performance if there are too many records in the table because, for any query, all the records will be searched for the given condition.
Use ALTER command to add index as given below:
ALTER TABLE table_name ADD INDEX (column_name);
You can do indexing on multiple columns using:
ALTER TABLE table_name ADD INDEX (column_name1,..,column_nameN);
MySQL uses leftmost prefixing, so automatically indexes will be added for column_name1, (column_name1,column_name2),...(column_name1,..,column_NameN-1). This is quite helpful in situations like searching based on surname so an index on (surname, firstname, middlename) will automatically add multiple indexes used in faster querying.
Points to remember:
  • Choose the column(s) for indexing very carefully.
  • Indexed fields should be used for searching and query should be re-framed if any calculation is performed on indexed fields.
  • Index itself takes storage space.
  • Each operation on database requires updating index as well.
  • A smaller Key Length reported by EXPLAIN command is better. Smaller key length means lesser storage space is required by index file and also that time to search in index is less. For this, indexes can be set on part of columns. For example, INDEX(col1(10),col2(20)). Again, you have to judiciously decide what length of columns to be used for indexing.

Step 2

Using ANALYZE command
Use ANALYZE command to generate key distribution for the table to be used by MySQL query optimizer to decide which indexes are best to use in a query.

ANALYZE TABLE table_name;

Step 3

Using OPTIMIZE command

If there are too many inserts/deletes in a table then OPTIMIZE command must be frequently used to optimize disk head movement during retrieval. This is particularly useful if variable size columns are used like varchar, text, blob etc. OPTIMIZE command does defragmentation of the fragmentation caused by variable length fields like VARCHAR.
OPTIMIZE TABLE table_name;

Step 4

Using special functions for loading data
For loading data from a file into a database table, using LOAD DATA INFILE is much faster than reading data from file iteratively and using INSERT command. The syntax for this command is:

LOAD DATA INFILE 'filedata.dat' INTO TABLE table_name (col1,col2,...,colM) FIELDS TERMINATED BY '|'");

Step 5

Setting PRIORITY of commands
If there are more queries than data insertions, you may lower priority of INSERT command using:
INSERT LOW_PRIORITY or SELECT HIGH_PRIORITY
If client is not interested in results of INSERT, then it can be immediately freed using following command:

INSERT DELAYED
.
This makes the system faster by batching insertions.

Step 6

DELETE vs. TRUNCATE
DELETE deletes row by row, while TRUNCATE deletes all rows at once. So, if you are not interested in number of rows deleted from a table as result of DELETE command, then use TRUNCATE with following syntax:

TRUNCATE TABLE table_name;

Step 7

Lowering permission checking overhead
Simpler permissions reduce permission checking overhead. Use GRANT command to set the permissions. General syntax for GRANT is:
GRANT Oper1,...,OperN ON db_name.tb_name TO user_name@computer_name IDENTIFIED BY password
For example:

GRANT SELECT,UPDATE ON EMP_DET.SALARY_DET TO feroz@localhost IDENTIFIED BY 'abc123';

If user account 'feroz' is not there, then it will be created on localhost (although user can access data from any computer) and password 'abc123' will be assigned to it.

Step 8

Use BENCHMARK() function
To know how much a given MySQL function or expression is taking time, use MySQL built-in function:

BENCHMARK(loop_count, expression)
It always returns 0 but also prints time taken by the expression.

Step 9

Synchrozing data types
If columns in tables contain identical information, then make their data types also same so that joins can be performed faster.

Step 10

Miscellaneous
  • Insert multiple rows with single SQL statement.
  • Remove unnecessary paranthesis in WHERE clause.
  • If SQL_SMALL_RESULT option is used, then MySQL uses temporary in-memory tables which provide faster access.

Query optimization is not a one time job. If your database is heavily used then you may have to repeat the optimization process every 5-6 months.
 

           How To Use Structured Query Language (SQL)


                                             
                                            Computer keyboard
Structured Query Language (SQL) is a computer language for creating database and manipulating data. SQL is an ANSI (American National Standard Institute) standard and is supported by almost all Relational Data Base Management Systems (RDBMS) like Oracle, MySQL, SQLServer, MS Access, PostGreSQL etc. SQL has two parts:
  • Data Definition Language (DDL): to create, alter, or drop tables and indexes.
  • Data Manipulation Language (DML): to insert, update, retrieve or delete the data in the tables.
Here's how to use SQL.
  1. Install an RDBMS package. You can download MySQL from http://www.mysql.org for your operating system (OS) and install it using the given instructions. For Windows OS, it can be installed by double-clicking the installer and choosing the default values on each stage.
  2. Start Mysql service. In command prompt window, change directory to C:\mysql\bin (if you have installed MySQL under C:) and issue following command to start MySQL service:
    NET START mysql
  3. Start Mysql client. In command prompt window, change directory to C:\mysql\bin and issue the command mysql to get mysql prompt.
  4. Create a database. On MySQL prompt, enter command 'create database' followed by any database name. Remember to put semi-colon at the end of command:
    create database emp;
  5. Set the created database as active one. To do this, issue the 'USE' command followed by database name on mysql prompt:
    use emp;
  6. Create a table. To do this, use 'CREATE TABLE' command with name and data-type of each table field. You can also specify PRIMARY KEY and any other constraint like NOT NULL. For example:

    CREATE TABLE person
    (NAME VARCHAR(80) PRIMARY NOT NULL,
    DSGN VARCHAR(5),
    AGE INTEGER,
    PAY INTEGER

    );
  7. Insert some data into the created table. This is accomplished through 'INSERT INTO' command followed by table name and values to be inserted.
    • If you need to insert a row with values for all columns, then use the following command:
      INSERT INTO person VALUES ('Kakul','MGR',26,35000);
    • If you need to insert values for selected columns only, then you need to specify those column names also in the command as shown:

      INSERT INTO person (NAME) VALUES ('Feroz');
    Notice that a character value is enclosed within single quotes and each command is terminated with a semi-colon.
  8. Update the table.
    • Use UPDATE command with SET and name-value pairs like:
      UPDATE person SET pay=50000;
    • For updating a particular row, use WHERE clause in UPDATE command like:

      UPDATE person SET pay = 20000 WHERE NAME = 'Kakul';
  9. Retrieve the stored data. Use SELECT command to retrieve the data. For conditional retrieval, you may use WHERE clause. Try following queries:
    • To retrieve all COLUMNS and all rows:
      SELECT * FROM person;
    • To get the sorted list, use ORDER BY clause:query retrieve the table name from database
      SELECT * FROM person ORDER BY name;
    • To retrieve few columns of all rows:
      SELECT name FROM person;
    • To retrieve all columns of a particular row:
      SELECT * FROM person where name = 'Feroz';
    • To retrieve selected columns of a particular row:

      SELECT pay FROM person WHERE name = 'Kakul';
    • To retrieve a row with columns having a particular pattern (i.e. pay of all those employees whose name starts with K):
      SELECT pay FROM person WHERE name like 'K%';
    • To count number of records in the table (say you want to know number of employees):
      SELECT COUNT(*) FROM person;
    • To get the sum of a column (say you need to know total pay to be paid):

      SELECT SUM(PAY) FROM person;
    • Use AND/OR in WHERE clause to retrieve data based on multiple condition:

      SELECT * FROM person WHERE name LIKE 'K%' AND pay > 5000;
    • To group the results, use GROUP BY as in following:
      SELECT * FROM person GROUP BY dsgn;
    • To show groups satisfying a criteria, use HAVING as illustrated below:

      SELECT * FROM person GROUP BY dsgn HAVING pay > 12000;
    • To get results if a field has any of the given value, use IN clause:
      SELECT * FROM person where name IN ('Feroz','Kakul');
    You may try querying with other functions also like AVG, DISTINCT, BETWEEN etc.
  10. Add a column to the table. This is done through ALTER command like:
    ALTER TABLE person ADD experience INTEGER;
  11. Set an alias for person table using few columns only. To do this, use AS as illustrated below:
    SELECT NAME,DSGN FROM person AS employees;
  12. Delete records from the table.
    • To delete a particular record, use DELETE command with WHERE clause like:
      DELETE * FROM person WHERE name='Feroz';
    • To delete all records, use:

      DELETE FROM person;

      All records can also be deleted using TRUNCATE command such as:
      TRUNCATE TABLE person;
  13. Drop the column added in step 10 above. You have to again use ALTER command with DROP like this:

    ALTER TABLE person DROP experience;
    Note that with ADD you have to specify data-type of the column also which is obviously not required with DROP.
  14. Drop the created table. Use DROP TABLE command followed by table name.
    DROP TABLE person;
  15. Drop database also. Use DROP DATABASE command followed by database name.


  Learning MySQL Commands: Creating a Server Database



                               Enjoying home office
A database organizes data so that it can be easily accessed, altered, and managed. Like virtually all database software, MySQL uses Structured Query Language (SQL-pronounced sequel), the standard language for requesting information from a database. It is an open-source software, meaning that its free to use and modify for your personal use, and we will provide SQL help in this article to make you comfortable using it. Despite its low price, MySQL has increasingly found wide adoption in industry because of its fast performance, reliability, ease of use, and versatility in working with programming languages. Join the 5 million active users of MySQL including NASA, FedEx and the computer nerd down the block by learning to use the basic asdcommands of MySQL.
It is not difficult to learn MySQL if you follow this MySQL tutorial step-by-step. 
  1. Make sure you have installed MySQL correctly. Once you have done so, go to the command line application of your operating system and enter: 'mysql -u root'. This opens up MySQL in the root username. If you have already set another account, use that log-in information.
  2. Create a Database. Now the commandline will read 'mysql>' and everything you type after it will be processed by MySQL. We will use the "create database" command to create a database called first_database. Notice that there can be no space in the filename.
    mysql> create database first_database;
  3. Create a Table. A database is a collection of data organized into tables. The following code mysql> creates a table with 5 columns:
    mysql> create table first_table
    -> (ID int not null primary key auto_increment,
    -> last_name varchar(25),
    -> first_name varchar(25),
    -> email varchar(25),
    -> date_joined timestamp(14),
    -> about text);
    • Line 1: Creates a column labeled ID that generates a unique integer (int) number that increases by an increment (auto_increment) of one for every new entry. This is known as a primary key because every record (or row) has a unique id to differentiate the rows (useful when several people have the same last name).
    • Line 2 and 3: Creates columns labeled "last_name" and "first_name" that contain the last names and first names, respectively, of entries. The maximum number of characters is 25 ( varchar(25) ).
    • Line 4: Creates a column for the e-maillearn mysqladdresses of members. Again the maximum number of characters is 25.
    • Line 5: Creates a column that records the date (timestamp) the member joined. The number 14 'timestamp()' refers to the most commonly used display format: YYYYMMDDHHMMSS.
    • Line 6: Creates a column with a short description of the member.
  4. Insert Data. Now we will manually add data to the table:
    mysql> insert into first_table
    -> (ID, last_name, first_name, email, date_joined, about) values
    -> (NULL, 'Doe', 'John', 'johndoe@emailaddress.com' ,now(),
    -> 'This is a short description of John Doe.');
    • Line 1: Tells MySQL that you want to add a record to the table.
    • Line 2: Lists the names of the columns in the order we will use to enter the values.
    • Line 3+4: Lists the values for the columns in the order listed previously. The value 'now()' makes the timestamp record the time and date when the row was created.
    Repeat this until there are a total of four records so that we can run a query.
  5. Running Queries. Now that there are a total of four records we can run a query, the database term for requesting data from a table. We use the 'select' command to query 'first_table'. Here we specifically want to see the last and first names and email addresses from the database.
    mysql> select last_name,first_name,email from first_table;
    The asterisk after the 'select' command is known as the universal selector meaning that you want to query every column.
    mysql> select * from first_table;
  6. Updating an Entry. The next step in learning SQL is updating. Let's say John Doe has changed his e-mail address because of too much spam. The following code uses the 'UPDATE' command and 'SET' subcommand to change the email addresses of a person whose last name is 'Doe'. The 'WHERE' subcommand is important because it determines which person's email address should be changed (where the last name is Doe). Without the 'WHERE' command all records in 'first_table' would have the same email address (johnnydee@emailaddress  .com).
    mysql> UPDATE first_table SET email = 'johnnydee@emailaddress .com'
    -> WHERE last_name = 'Doe';
  7. Deleting a Record. You may need to make changes in the MySQL server. Let's say John Doe dies (I couldn't resist), or maybe he just opted-out of an email subscription list. To delete a record, no surprise here, we use the 'DELETE' command. 'FROM' determines the table you are working with and 'WHERE' determines the specific record you want to remove.
    mysql> DELETE FROM first_table WHERE lastname = 'Doe';
Now you have learned the MySQL commands and MySQL tools to perform basic database tasks. The MySQL documentation website is a great resource for learning about new commands. While it's probably more practical to use a GUI based database administration program, knowing the basics of SQL will be very useful to you especially if you want to work with a programming language or want to perform some maintenance tasks manually.

                        Word searching in MySQL


How to create a table with word search indexing.
mysql> CREATE TABLE t (a VARCHAR(200), b TEXT, FULLTEXT (a,b));
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO t VALUES
    -> ('MySQL has now support', 'for full-text search'),
    -> ('Full-text indexes', 'are called collections'),
    -> ('Only MyISAM tables','support collections'),
    -> ('Function MATCH AGAINST()','is used to do a search'),
    -> ('Full-text search in MySQL', 'implements vector space model');
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM t WHERE MATCH (a,b) AGAINST ('MySQL');
+---------------------------+-------------------------------+
| a | b |
+---------------------------+-------------------------------+

| MySQL has now support | for full-text search |
| Full-text search in MySQL | implements vector-space-model |
+---------------------------+-------------------------------+
2 rows in set (0.00 sec)
mysql> SELECT *,MATCH a,b AGAINST ('collections support') as x FROM t;
+---------------------------+-------------------------------+--------+
| a | b | x |
+---------------------------+-------------------------------+--------+
| MySQL has now support | for full-text search | 0.3834 |
| Full-text indexes | are called collections | 0.3834 |
| Only MyISAM tables | support collections | 0.7668 |
| Function MATCH AGAINST() | is used to do a search | 0 |
| Full-text search in MySQL | implements vector space model | 0 |
+---------------------------+-------------------------------+--------+
5 rows in set (0.00 sec)

Comments

Popular posts from this blog

Hack Like a Pro: Perl Scripting for the Aspiring Hacker

Understanding the Link between social media , ID theft and your credit card

HERE'S HOW THE HEARTBLEED BUG SCURRIED INTO THE HEARTS AND MINDS OF MILLIONS