SQL

SQL

Content

* Introduction to SQL
* DDL
* DML
* Creating Database
* Creating Table
* Deleting Table
* Deleting Database
* Data Types
* Inserting Data into Table
* Updating Table
* Deleting Table Data
* Selecting Table Data
* Constraints
* NOT NULL
* UNIQUE Constraint
* Primary Key
* Foreign Key
* Default Constraint
* Auto Increment

Introduction to SQL

* Most of the actions you need to perform on a database are done with SQL statements.
* Some database systems require a semicolon at the end of each SQL statement. 
* SQL is a language common to access almost all the DBMS software.
* SQL can be divided into two parts 
 - The Data Manipulation Language (DML) 
 - The Data Definition Language (DDL)

DDL

* The DDL part of SQL permits database tables to be created or deleted.
* It specify links between tables, and impose constraints between tables. 
* The most important DDL statements in SQL are:
 - CREATE DATABASE ==> creates a new database 
 - ALTER DATABASE ==> modifies a database 
 - CREATE TABLE ==> creates a new table 
 - ALTER TABLE ==> modifies a table 
 - DROP TABLE ==> deletes a table
 - DROP DATABASE ==> delete a database

DML

* The DML is Data Manipulation Language.
* It is the set of commands to manipulate application data stored in the database.
* The query and update commands form the DML part of SQL:
 - SELECT ==> extracts data from a database 
 - UPDATE ==> updates data in a database 
 - DELETE ==> deletes data from a database 
 - INSERT ==> INTOinserts new data into a database

Creating Database

* Any user of MySQL can create database, provided he has been privileged. Providing privileges will be study in later chapters
* User root is predefined user and has all the privileges
* The CREATE DATABASE statement is used to create a database.

CREATE DATABASE database_name;

* Database is a collection of tables.


Creating Table

* After creating database you can create tables in it. First select a database through use command.
* Following is the syntax to create table

CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type,
column_name3 data_type,
....
);


Describing table


Deleting Table

* Deleting table is different from deleting content of the table. 
* Deleting table means the whole structure is deleted, thus all data stored in the table will also be removed.
* The DROP TABLE statement is used to delete a table.

DROP TABLE table_name;


Deleting Database

* The DROP DATABASE statement is used to delete a database. 

DROP DATABASE database_name;


Data Types

* SQL support variety of data types.

Data Type ==> Description 

char(size) ==> Holds a fixed length string (can contain letters, numbers, and special characters). The fixed size is specified in parenthesis. Can store up to 255 characters 
varchar(size) ==> Holds a variable length string (can contain letters, numbers, and special characters). The maximum size is specified in parenthesis. Can store up to 255 characters. Note: If you put a greater value than 255 it will be converted to a TEXT type 
TEXT==> Holds a string with a maximum length of 65,535 characters 
MEDIUMTEXT==> Holds a string with a maximum length of 16,777,215 characters 
LONGTEXT ==> Holds a string with a maximum length of 4,294,967,295 characters 

Data Type ==> Description 

enum(x,y,z) ==> Let you enter a list of possible values. You can list up to 65535 values in an ENUM list. If a value is inserted that is not in the list, a blank value will be inserted
int(size) ==> -2147483648 to 2147483647 normal. 0 to 4294967295 UNSIGNED. The maximum number of digits may be specified in parenthesis 
float(size,d)==> The maximum number of digits may be specified in the size parameter. The maximum number of digits to the right of the decimal point is specified in the d parameter. It occupies 4 bytes.
double(size,d)==> A large number with a floating decimal point. The maximum number of digits may be specified in the size parameter. The maximum number of digits to the right of the decimal point is specified in the d parameter. It occupies 8 bytes.

Data Type ==> Description 

date ==> A date. Format: YYYY-MM-DD. The supported range is from '1000-01-01' to '9999-12-31' 
time ==> A time. Format: HH:MM:SS
The supported range is from '-838:59:59' to '838:59:59' 
bigint(size)==>  The signed range is -9223372036854775808 to 9223372036854775807.  
mediumint(size)==> The signed range is -8388608 to 8388607. 

Inserting Data into table

* The INSERT INTO statement is used to insert a new row in a table

INSERT INTO table_name 
(column1, column2, column3,...) 
VALUES (value1, value2, value3,...); 




Updating table

* The UPDATE statement is used to update existing records in a table.

UPDATE table_name SET column1=value, column2=value2,...
WHERE some_column=some_value;


Deleting Table data

* The DELETE statement is used to delete rows in a table.

DELETE FROM table_name
WHERE some_column=some_value;

Selecting data from table(s)

* The SELECT statement is used to select data from a database. 

SELECT column_name(s) FROM table_name;

* Above SELECT command is to fetch all the rows from the table with selected set of columns.
* SELECT command can also be used to retrieve all the rows from the table with all its columns. In such case write asterisk (*) in the place of column names
SELECT * FROM table_name;

* The WHERE clause is used to extract only those records that fulfill a specified criterion. 

SELECT column_name(s)
FROM table_name
WHERE column_name operator value;

Constraints

* Constraints are used to limit the type of data that can go into a table
* Constraints can be specified when a table is created (with the CREATE TABLE statement) or after the table is created (with the ALTER TABLE statement).
* Following are the constraints:
 - NOT NULL
 - UNIQUE
 - PRIMARY KEY
 - FOREIGN KEY
 - DEFAULT

NOT NULL

* The NOT NULL constraint enforces a column to NOT accept NULL values.
* The NOT NULL constraint enforces a field to always contain a value. This means that you cannot insert a new record, or update a record without adding a value to this field. 
* You can define any of the column NOT NULL as mentioned in the following syntax

CREATE TABLE table_name
(
column_name1 data_type NOT NULL,
column_name2 data_type NOT NULL,
column_name3 data_type,
....
);

UNIQUE Constraint

* The UNIQUE constraint uniquely identifies each record in a database table. 
* The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of columns 
* Note that you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table. 

Example

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
UNIQUE (P_Id)
);

* You can apply Unique constraint even after creating table using alter command

ALTER TABLE Persons
ADD UNIQUE (P_Id);

Primary Key
* The PRIMARY KEY constraint uniquely identifies each record in a database table. 
* A primary key column cannot contain NULL values.
* Primary keys must contain unique values.
* Each table should have a primary key, and each table can have only ONE primary key

Example

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (P_Id)
);

* You can define primary key even after creating table using alter command

ALTER TABLE Persons
ADD PRIMARY KEY (P_Id);

Foreign Key

* A FOREIGN KEY in one table points to a PRIMARY KEY in another table.

Example

CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
);

* Foreign key may repeat but value must exist in the master table where it is a primary key.
* You can add foreign key constraint even after creating the table using alter command
ALTER TABLE Orders
ADD FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id);

Default Constraint

* The DEFAULT constraint is used to insert a default value into a column.
* The default value will be added to all new records, if no other value is specified. 

Example

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Bhopal'
);

* You can set default constraint even after creating the table

ALTER TABLE Persons
ALTER City SET DEFAULT 'Bhopal';

Auto Increment

* Very often we would like the value of the primary key field to be created automatically every time a new record is inserted. 

Example

CREATE TABLE Persons
(
P_Id int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (P_Id)
);

<<Prev     Home     Next>>