MySQL Simple Queries

MYSQL

INTRODUCTION TO MySQL

MySQL is a relational database management system(RDBMS). MySQL stands for “My Structured Query Language” it is also pronounced as My SEQuel. MySQL is a freely available open source RDBMS that uses SQL.

MySQL is a fast, easy-to-use RDBMS used for small and big business applications. MySQL is developed, marketed, and supported by a Swedish Company MySQL AB. It is also used in many high-profile, large scale world wide web products including Wikipedia, Google and Facebook

Some of the key features of MySQL are:

Ø  Cost: MySQL is Open source, and is available free.

Ø  Trusted: MySql is used by some of the most important and prestigious organizations and sites, all of whom entrust it with their critical data

Ø  MySQL is released under an open-source license so it is customizable. It requires no payment for its usage

Ø  MySQL is a platform independent application which works on many operating systems like Windows, UNIX, LINUX etc. And has a compatibility with many languages including Java, C++, PHP, PERL etc.

Ø  Performance: Speed MySQL runs very fast

Ø  MySQL can handle large databases. Some real life MySQL database contain 50 million records, some

Ø  have up to 60,000 tables and about 5,000,000,000 rows

Database: A database may be defined as a collection of interrelated data stored together to serve multiple applications.

Relational Database: It is a collection of logically related tables

Duplication of data is known as Data Redundancy

Byte a byte is a group of 8 Bits and is used to store a character

Data item: A data item is the smallest unit of named data.

Record/Tuple: A record is a named collection of data items which represents a complete unit of information

Table/ Relation: A table is a named collection of all occurrences of a given type of logical record

Attributes/Fields: The columns of a table are referred as attribute

Degree The number of attributes in a relation

Cardinality The number of rows in a relation

Views A view is a virtual table that does not exist in its own but is derived from one or more base tables

Primary Key a set of one or more attributes that can uniquely identifies tuples within relation

Candidate Key All attribute combinations that can serve as primary key

Alternate Key A candidate key that is not primary key

Foreign key whose values are derived from the primary key of some other tables

Creating Database

->CREATE DATABASE [IF NOT EXISTS] database_name;

Create database statement will create the database with the given name you specified.

Show Databases

SHOW DATABSE statement will show all the database server. You can use show database you have created or to see all the databases list

-> SHOW DATABASES;

Selecting Database

To select a database, which you plan to work with, you use USE statement

-> USE database_name;

Removing Database

Removing database means you delete the database. All the data and related objects inside the database are permanently deleted and cannot be undone.

-> DROP DATABASE [IF EXISTS] database_name;

To exit from MySQL

MySQL> QUIT or EXIT;

MySQL Data Types Every column should belong to a unique domain (known as datatype). These data types help to describe the kind of information a particular column holds.

Creating Tables

To create table we use the CREATE TABLE statement. The usual form of this statement is:

CREATE TABLE [IF NOT EXISTS] table_name(

column_list) ;

table_name is the name of table you would like to create

CREATE TABLE employees (             

             employeeNumber into(11) NOT NULL,   

             lastName varchar(50) NOT NULL,     

             firstName varchar(50) NOT NULL,    

             email varchar(100) NOT NULL,       

             jobTitle varchar(50) NOT NULL,     

             PRIMARY KEY  (employeeNumber));

DESCRIBE

The describe command is used to see the structure of the table

DESCRIBE table_name; / Desc table_name/ Explain TableName;

INSERT Statement

INSERT statement allows you to insert one or more rows to the table.

Insert into tab_name values(value1, value2, value3);

UPDATE COMMAND

SQL UPDATE statement is used to update existing data in a data table. It can be used to change values of single row, group of rows or even all rows in a table.

UPDATE table_name SET column_name1=expr1 [WHERE condition]

DELETE COMMAND

To remove a data row or all rows from a table you can use SQL DELETE statement.

DELETE FROM table_name [WHERE conditions];

Altering Table Structures

Beside creating table, MySQL allows you to alter existing table structures with a lot of options. Here are the ALTER TABLE statement:

ALTER TABLE table_name ADD column_name datatype;

ALTER TABLE table_name Modify column_name datatype;

ALTER TABLE table_name DROP column column_name datatype;

ALTER TABLE table_name CHANGE oldcolum_name newcolumn_name;

Rename a Table

ALTER TABLE Old_table_name Rename to New_table_name;

Dropping Tables

To delete table from the database, you can use DROP TABLE statement:

DROP TABLE [IF EXISTS] table_name ;

MySQL allows you to drop multiple table at once by listing them and separated each by a comma. IF EXISTS is used to prevent you from deleting table which does not exist in the database.

Empty Table's Data

In some cases, you want to delete all table data in a fast way and reset all auto increment columns.

TRUNCATE TABLE table_name;

SELECT STATEMENT

If you want to view only the information of specific columns, you may write your query as

SELECT column_name1, Column_name2 FROM table_name;

If you want to see the entire table i.e every column of a table, asterisk (*) can be substituted for a complete list of columns

SELECT * FROM table_name;

WHERE Clause

WHERE clause enables you to select a particular rows which match its conditions or search criteria. In our example we can find the president of company by doing this query:

SELECT firstname,lastname,email  FROM employees WHERE jobtitle="president"

DISTINCT With DISTINCT keyword, you can eliminate the duplicated result from SELECT statement.

SELECT DISTINCT jobTitle FROM employees;

GROUP BY

If you need to find number of employee who hold each job, you can use GROUP BY clause. GROUP BY clause allows use to retrieve rows in group. Here is the query example:

SELECT count(*), jobTitle FROM employees GROUP BY jobTitle;

HAVING Clause

HAVING clause usually use with GROUP BY clause to selecting a particular of group. For example:

SELECT count(*), jobTitle FROM employees GROUP BY jobTitle HAVING count(*) = 1

Sorting with ORDER BY

The ORDER BY clause allows you to sort the result set on one or more column in ascending or descending order. To sort the result set in ascending order you use ASC and in descending order you use DESC keywords.

Condition based on Range (BETWEEN): The between operator defines a range of values that the column values must fall in to make the condition true. The range has both lower and upper value

Mysql> SELECT rollno, name FROM student WHERE marks BETWEEN 50 AND 60;

Condition based on a List (IN): The IN operator selects values that match any value in the given list of values that the column values must fall into to make condition true

MySQL> select rollno, name from student where marks IN (68, 70, 76, 80);

Condition based on pattern matche (LIKE): Like clause can be used instead of equal sign to match patterns

MySQL> Select * From Student WHERE name LIKE “%ya”;

LOGICAL OPERATORS: The logical operators AND, OR, NOT are used to connect conditions in the WHERE

Mysql> SELECT rollno, name FROM student WHERE marks > 50 AND mark < 60;

Precedence determines which operation will be performed first in an expression

OPERATORS	PRECEDENCE
( )	1
* /	2
+ -	3
< <= > >=	4
LIKE IN BETWEEN	5
AND OR	6

SQL IN operator SQL IN allows you to select values which match any one of a list of values. The usage of SQL IN is as follows:

SELECT column_list FROM table_name WHERE column IN ("list_item1","list_item2"…)

SELECT officeCode, city, phone FROM offices WHERE country NOT IN ('USA','France')

LIKE Operator

SQL LIKE allows you to perform pattern matching in your characters column in a database table. SQL LIKE is often used with SELECT statement in WHERE clause. MySQL provides you two wildcard characters for use with LIKE, the percentage % and underscore _.

Percentage (%) wildcard allows you to match any string of zero or more characters

Underscore (_) allows you to match any sing character.

SELECT employeeNumber, lastName, firstName FROM employees WHERE firstName LIKE 'a%';

To search all employees which have last name ended with ‘on’ string you can perform the query as follows:

SELECT employeeNumber, lastName, firstName FROM employees WHERE lastName LIKE '%on';