SQL NOTES

STRUCTURED QUERY LANGUAGE

It a relational database language. It is universally accepted DBMS language used to manipulate data base. This language was originally called Structured English Language (Sequel), later its name was changed to SQL.
American National Standard Institute (ANSI) published SQL standard.
SQL was developed in 1970 by Donald D Chamberlin Raymond F Boyce
at IBM Lab

Components of SQL

Data definition Language (DDL)

Data Manipulation Language (DML)

Data Control Language (DCL)

Data definition Language (DDL)

It is used to define a schema or delete a table or modify a table. The result of the compilation of DDL statements are stored in special file called data dictionary.
Data dictionary contains data about data (meta data)

The DDL Commands are :

CREATE TABLE
ALTER TABLE
DROP TABLE
CREATE VIEW
DROP VIEW

Data Manipulation Language (DML)

It is used to manipulate content of the table. It is a set of commands used for inserting records or modifying records or deleting records or inserting records.

The DML Commands are :

INSERT INTO
UPDATE
DELETE
SELECT

Data Control Language (DCL)

It is used to control the security of the data. DCL includes commands that control a data base including administrative privileges and committing data

The DCL Commands are :

GRANT :- to set privileges to the database users
REVOKE :- withdraw user’s access privileges given by using GRANT command

Transaction Control Language (TCL)

It deals with transaction within a database.

The TCL Commands are :

COMMIT:- commit a transaction.
ROLLBACK:- rollback a transaction in case of any error occurs.
SAVEPOINT:- to rollback the transaction making points within groups.
SET TRANSACTION:- specify characteristics for the transaction.

MySQL

It an open source SQL DBMS for developing web based software applications.

MySQL is so popular because of :

MySQL is released under an open source license.
MySQL uses a standard form of SQL language.
MySQL work with many languages like PHP, PERL, C, C++, JAVA, and many OS (Operating System).
MySQL works quickly and supports large DB.

Opening MySQL

In Linux
    Applications -> Accessories -> Terminal
                                                                     and type following command
    mysql –u root –p

In Windows

Start -> Programs -> MySQL ->
MySQL Server -> MySQL Command line client

To exit from MySQL

mysql > EXIT; OR mysql > QUIT;

Creating Database

Syntax:
CREATE DATABASE <database name> ;eg: -
CREATE DATABASE RGMHSS;

Opening Database

Syntax:
USE <database name> ;

eg: -
USE RGMHSS;
View Databases

SHOW DATABASES
SQL Data Types

Data types defines the type of value that may be entered in the column of a table. It ensure correctness of data
MySQL data types are classified into three:

Numeric Data Types
String( Text) Data Types
Date & Time Data Types

Numeric Data Types

It can be any normal number. They can be added, subtracted, divided & multiplied.

The numeric data types are :-
INT or INTEGER :- It is used to store integer number. It can store –ve and +ve values
eg:- mark INT,
rollno INTEGER,

DEC or DECIMAL :- It is used to store numbers with decimal point.

Synatx:
    DEC (Size, D)
    Here size indicate the total no of digits including decimal part. D represent the no of digits after the decimal points.
eg:-   price DEC (4, 2),
    avg DECIMAL(5, 3),

String (Text ) Data Types

    String is a group of characters.
The String data types are :-
CHAR or CHARACTER:- It include letters, digits, special symbols etc. The CHAR is a fixed length character data type.
Synatx:
    CHAR (Size) or CHARACTER (Size)
    Here, size is the maximum number of character. The value of size can be 0 to 255.
         If size is not mentioned size is taken as 1
eg:- name CHAR(20),
          sex CHAR,
              or
    name CHARACTER(20),
          sex CHARACTER,

VARCHAR

It represent variable length string. It is similar to char but space allocated for the data depends only on actual size of the string.

eg:- name CHAR(20),
sex CHAR,
Default value of VARCHAR is 1

Date & Time Data Types

The data type used to store date type value is DATE and to store time value is TIME data type.

DATE (YYYY – MM - DD)

It is used to store date such as Date of Birth, Date of admission etc.
MySQL standard date format is

YYYY – MM – DD
eg:- ‘2016 – 07- 18’
‘1998 / 07 / 18’
‘19970718’

TIME (HH : MM : SS)
It is used to store time such as starting time, ending time etc.

MySQL standard time format is

HH : MM : SS
eg:- ’11 : 30 : 16’
’22 : 32 : 45’

SQL commands ( DDL & DML )

CREATE TABLE                DDL
INSERT INTO                    DML
UPDATE                             DML
DELETE                     DML
SELECT                       DML
ALTER TABLE                  DDL
DROP TABLE                    DDL
CREATE VIEW                  DDL
DROP VIEW                      DDL

CREATE TABLE

It is a DDL command used to create a table.

syntax:

CREATE TABLE <table name>

(<col-name><data type>[<constrains>],
<col-name><data type>[<constrains>] );

eg:-

CREATE TABLE student(rollno INT PRIMARY KEY,
name VARCHAR (20) NOT NULL );<table name> - name of the table we used to create.
<col-name> - name of the column in the table.
<constrains> - specify the rules that we can set on the value of columns

Rules for naming Tables & Columns

Letters, Digits, $ and underscore can ne included in a name.
The name must be contain at least one character
( names with only digits are invalid)
They must not contain white spaces, special symbols.
The name must not be an SQL keywords.
The name should not duplicate with the names of other tables in the same data base and with other columns in the same table.

Constraints in SQL

    They are used to control value that can be stored in a column. They are also called integrity constraints. They could be column level or table level.
Column Constraints :
    They are applied only to individual columns. They are written immediately after the data type of the column.

NOT NULL
AUTO_INCREMENT
UNIQUE
PRIMARY KEY
DEFAULT

NOT NULL – This constraint is used to specify a column should not be blank or empty.
    eg:- name VARCHAR(20) NOT NULL,

AUTO_INCREMENT – It is used to assign serial numbers automatically. By default the starting value for AUTO_INCREMENT is 1 and it will be incremented by 1.
    The AUTO_INCREMENT column must be defined as the primary key of the table.
    Only one AUTO_INCREMENT column per table is allowed.

eg:- rollno INT PRIMARY KEY AUTO_INCREMENT,

UNIQUE – It is used to specify that no two rows have same value in a specified column.

    eg:- rollno INT UNIQUE,

PRIMARY KEY – It is used to specify PRIMARY KEY of a table. It is similar to UNIQUE constrains. But there can be only one PRIMARY KEY in a table.

eg:- admno INT PRIMARY KEY

DEFAULT - It is used to specify default value of a column . This value will be stored automatically when no data is given to this column.
    eg:- sex CHAR DEFAULT ‘ m’

Use of Constraints

CREATE   TABLE student(
        rollno INT PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR (20) NOT NULL,
        gender CHAR DEFAULT ‘m’,
        dob DATE );

Table Constraints :
    These constrains are similar to Column Constraints. But these are applied on group of columns in table. It appears at the end of the table definition.

CREATE   TABLE student(
    rollno INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR (20) NOT NULL,
    gender CHAR DEFAULT ‘m’,
    dob DATE,
    UNIQUE ( rollno, name ) );Here, no two rows can have the same values for the columns rollno and name when taken together.

Viewing the structure of table

    DESCRIBE or DESC command is used to display the structure of the table.

syntax:


DESCRIBE <table name>;

or

DESC <table name>;

eg:-

DESCRIBE student;

or

DESC student;

This is just a basic note ..Subscribe us for more updates
©Coders world

Search This Blog

CODERS WORLD

SQL NOTES

STRUCTURED QUERY LANGUAGE

MySQL

Comments

Post a Comment