SQL Introduction

SQl Introduction

Structured Query Language, or SQL, is the standard language used to communicate with a database,
and or change records and user privileges, and perform queries.

There Language, which became an ANSI standard in 1989, is currently used by almost all of today’s commercial RDBMS.

SQL, statements fall into one of three categories.(Types of SQL)

Data Definition Language(DDL) : DDL Consists of statements that define the structure and relationships of a database and its table.

These Statements are used to Create, drop and modify databases and tables.

Data Manipulation Language(DML) : DML statements are related to altering and extracting data from a database.

These statements are used to add records to, update records in, and delete records from, a database; perform queries; retrieve table records matching one or more user specified criteria; and join tables together using their common fields.

Data Control Language(DCL) : DCL statements are sued to define access levels and security privileges for a database.

You would use these statements to grant or deny user privileges; assign roles; change passwords; view permissions; and create rulesets to protect access to data.

The Syntax of SQL is quite intuitive. every SQL statement begins with an “action word”, like DELETE, INSERT,ALTER etc.

it ends with a semicolon. whitespace, tabs, carriage returns are ignored.
Some example of valid SQL statements :
CREATE DATABASE emplyee;
SELECT name FROM users where email =”sanjeevtech2@gmail.com”;
DELETE FROM cars WHERE year_of_manufacture < 1980;

MySQL Constraints

MySQL Constraints

Constraints are the utilities that restrict entry of data into the table if the specified condition is not fulfilled.

They are primarily of the following type —

Primary key, foreign key, check, unique, not null

Primary Key : Primary key prevents duplicate entry.
It is called row based constraints(it means inside a table there is only one field can be declared primary )
No null Value can be entered .

Unique Key : It will not let enter a duplicate value in the field.
But a Null Value can be entered.
It is called column based constraint.

Foreign Key : A column is called foreign key when it refers to the primary key column of another table.

Not null : It will not allow entering null Value in the field.
But duplicate value can be entered.

MySQL Index

indexing is basically of four types :
=> Primary
=> Unique
=> Index
=> full-text

primary and unique both prevent the duplicate entry .

Primary : it can never be null. only one field may be primary. it is row based index

Unique: multiple fields may be unique, may be null. it is column based

Index and full-text both are used for filtration. when you need to search , if
you have to write the full name of field i.e. indexing. on the other hand if the
searching is based on the basis of characters it is full-text.

MySQL Common Queries

Basic SQL Queries with syntax

SQL Statement	What It Does
CREATE DATABASE database-name	Creates a new database
DROP DATABASE database-name	Deletes a database
CREATE TABLE table-name(field1, field2, . . .)	Creates a new table
DROP TABLE table-name	Deletes a table
RENAME Table Old-table-name TO New-table-name	Renames a table
ALTER TABLE table-name ADD(field1, field2, . . .)	Add fields in existing table
ALTER TABLE table-name DROP(field1)	Deletes fields from table
INSERT INTO table-name (field1, field2, . . . ) VALUES(value1, value2, . . .)	Inserts a new record into a table with specified values
UPDATE table-name SET field1=value1, field2=value2,… [WHERE condition]	Updates records in a table with new values
DELETE FROM table-name [WHERE condition]	Deletes records from a table
SELECT field1, field2, . . . FROM table-name [WHERE condition]	Retrieves matching records from a table
SELECT * FROM table-name	Retrieves all records from a table

MySQL Common Queries examples

What does auto_increment value mean in MySQL ?

Auto_increment: The word auto increment means ‘increment by itself’.
When we insert any new record to the table and if any field of that table is set to auto_increment then it generates a unique number for that field automatically.

1) How to rearrange auto_increment value

When you deletes a auto_incremented value then it doesn’t automatically rearrange the value.
if you want to rearrange the auto_incremented value then use the queries

1 2 3 4 5 6 7 8 9 10 11

Alter table reg drop id; // here table name is "reg" and auto_increment filed is "id"   Alter table reg auto_increment=1; //assign auto_increment=1(if you want to start from 100  then auto_increment=100)   Alter table reg ADD id bigint unsigned not null auto_increment primary key first; //Now  add a new filed "id", (define auto_increment)

---

2) What is the use of count in MySQL

if you want to count total number of rows from a table then use count()
Eg

1 2 3 4 5

// here "employee" is  table name select count(*)  from employee

---

3) What is the use of max in MySQL ?

max is used to select the maximum value of the particular field.
Example to find out the max salary from employee table :

1 2 3 4 5 6

// here "employee" is  table name   select max(salary) from employee;

---

4) Find out the second maximum salary from employee table using nested query

1 2 3 4 5

select max(salary) from employee where salary < ( select max(salary) from employee);

---

5) What is the use of min in MySQL ?

Min : min is used to select the minimum value of the particular field.
Example to find out the minimum salary from employee table:

1 2 3 4 5 6

// here "employee" is  table name select min(salary) from employee;

---

6) Example to find out the second minimum salary from employee table ?

1 2 3 4 5 6

select min(salary) from employee where salary > ( select min(salary) from employee); // here "employee" is  table name

---

7) Example to find out all the records of the employees whose salary is maximum in employee table:

1 2 3 4 5 6

// here "employee" is  table name select * from employee where salary = (select max(salary) from employee)

---

8) What is the use of order by in MySQL?

Order by : This is used to set the record in a order(descending order or ascending order).
Example for descending order

1 2 3 4 5 6

// here "employee" is  table name select * from employee ORDER BY salary desc;

Example for ascending order:

1 2 3 4 5 6 7 8 9

select * from employee ORDER BY salary asc;   //OR   select * from employee; // here "employee" is  table name

---

9) What is the use of limit in mySql ?

Limit : It is used to specify the number of records to return.
It applies the constraint on the number of rows in result set.
Means if we have used ‘LIMIT 5,10 ‘ then it means it will select 10 record from starting from 6th.

Example to select 10 records starting from 6 in the given offset

1 2 3 4 5 6 7 8 9

select * from employee limit 5,10;   // OR   Select * from employee limit 10 OFFSET  5; // both queries will give the same result.

Example to select the last records from employee table

1 2 3 4 5 6

select * from employee ORDER BY id Desc limit 1 ; // here "employee" is  table name and it returns the last entry

Example to select the 1st records from employee table

1 2 3 4 5 6

select * from employee ORDER BY id ASC limit 1 ; // here "employee" is  table name and it returns the first entry

Example to select 1st and last rerocds from emp table

1 2 3 4 5 6 7 8 9

(select id,name,salary from employee ORDER BY id Asc limit 1) UNION (select  id, name, salary from employee ORDER BY id Desc limit 1);   <span class="comments">// here "employee" is  table name and it returns the id,name, and salary of first user and last user</span>

---

10) What is the use of between ?

Between is used to select the records from given lower and upper limit.
Example to select student whose age between 25 to 40

1 2 3 4 5 6

select * from student where age between 25 and 40; // here "student" is  table name

Example to select records between two dates

1 2 3 4 5 6 7

select * from employee where date between “2012-04-01”  and “2012-04-10”; // here "employee" is  table name. //Year,month and date separated by '-' so pass date as a string “2012-04-01”.

---

11) What is the use of DISTINCT keyword ?

Distinct : It only returns distinct values . it rectifies the data from repeated(duplicate) values.
Example to select unique student name from student table.

1 2 3 4 5 6

SELECT DISTINCT  name    FROM student; // here "student" is  table name while  "name" is field name

---

12)What is the use of IN CLAUSE operator ?

We can use “IN” clause to replace many “OR” conditions. It allows you to specify multiple values in a where clause.
Example using multiple OR clause

1 2 3 4 5 6

select * from employee where daily_page=100 OR daily_page=150 OR daily_page=200; // here "employee" is  table name.it return the details of  employee  whose daily_page=100 or 150 or 200

Example using IN Clause

1 2 3 4 5 6

Select * from employee where daily_page IN(100,150,200); // here "employee" is  table name.it return the details of  employee  whose daily_page=100 or 150 or 200

---

13)What is the use of foreign key in mysql ? How to define foreign key

The key which is pointing to the primary key of another table is called as foreign key.
Foreign keys concept doesn’t work in MYISAM storage engine change your storage engine as innodb.

First create a person table

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

CREATE TABLE Person   (   P_id int auto_increment primary key,   name char(30) not null,   email varchar(50) unique key   )

Create an ORDERS tables with a foreign key(P_id)

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

CREATE TABLE Orders   (   O_Id int  auto_increment PRIMARY KEY ,   OrderNo int NOT NULL,   P_id int,   FOREIGN KEY (P_id) REFERENCES Person(P_id)   ); // Here P_id field is foreign key(the primary key of Person table)