VINOD KUMAR VERMA, PGT(CS), KV OEF KANPUR &
SACHIN BHARDWAJ, PGT(CS), KV NO.1 TEZPUR
CREATE DATABASE [IF NOT EXISTS] DATABASENAME
Note: IF NO EXISTS will attempt to create database only if
the given name database not exists. It command is executed
without it and database already exists then error message
‘database already exists’ will come
Example:
Create Database mydata1;
Create Database if not exists mydata1;
VINOD KUMAR VERMA, PGT(CS), KV OEF KANPUR &
SACHIN BHARDWAJ, PGT(CS), KV NO.1 TEZPUR
To start working in any database it must be opened by USE
command.
USE databasename;
Example:
Use mydata1;
VINOD KUMAR VERMA, PGT(CS), KV OEF KANPUR &
SACHIN BHARDWAJ, PGT(CS), KV NO.1 TEZPUR
To get the list of all created database –
Show databases;
VINOD KUMAR VERMA, PGT(CS), KV OEF KANPUR &
SACHIN BHARDWAJ, PGT(CS), KV NO.1 TEZPUR
To remove already existing database –
remove database [IF EXISTS] databasename
Example:
Drop database mydata1;
VINOD KUMAR VERMA, PGT(CS), KV OEF KANPUR &
SACHIN BHARDWAJ, PGT(CS), KV NO.1 TEZPUR
Create Table Tablename(colname
datatype(size), colname2 datatype(size),…);
Example:
Create table emp(empno int, name varchar(20),
dept varchar(20), salary int)
VINOD KUMAR VERMA, PGT(CS), KV OEF KANPUR &
SACHIN BHARDWAJ, PGT(CS), KV NO.1 TEZPUR
A constraints refers to condition or limitation
we apply on any column so that only correct
information will be entered in table.
MySQL allows to apply constraint by two
methods
▪ At the time of table creation
▪ After table creation
VINOD KUMAR VERMA, PGT(CS), KV OEF KANPUR &
SACHIN BHARDWAJ, PGT(CS), KV NO.1 TEZPUR
PRIMARY KEY : ensures unique value in any column
also forces data entry mandatory. Only one primary key
can be applied in one table
UNIQUE : also allows unique value in any column but it
allows NULL values and can be applied to n times
NOT NULL : it will make data entry mandatory for
applied column i.e. NULL will not be allowed
DEFAULT : it allows to specify any value which will be
automatically inserted in applied column if we not
specify applied column at the time of data entry using
INSERT
VINOD KUMAR VERMA, PGT(CS), KV OEF KANPUR &
SACHIN BHARDWAJ, PGT(CS), KV NO.1 TEZPUR
CHECK : allows to specify range of values that can be
entered in applied column like salary must be greater
than 2000, marks must be greater than 0 or dept must
be in given list of values etc.
Note: in mysql the database engine will ignore the
check constraints.
FOREIGN KEY: allows to establish relationship between
2 tables. Foreign key column will be dependent on
PRIMARY KEY column of another table and allows to
enter only those values in foreign key whose
corresponding value exists in PRIMARY KEY
VINOD KUMAR VERMA, PGT(CS), KV OEF KANPUR &
SACHIN BHARDWAJ, PGT(CS), KV NO.1 TEZPUR
Now lets check PRIMARY KEY is working or not by inserting
duplicate empno
Now lets check NOT NULL is working or not by inserting
NULL value in name column
VINOD KUMAR VERMA, PGT(CS), KV OEF KANPUR &
SACHIN BHARDWAJ, PGT(CS), KV NO.1 TEZPUR
Now let us check how DEFAULT constraint to use. (Remember to use DEFAULT
CONSTRAINT, The applied column name will not be used with INSERT
Default value
‘Marketing’ is
automatically
inserted
VINOD KUMAR VERMA, PGT(CS), KV OEF KANPUR &
SACHIN BHARDWAJ, PGT(CS), KV NO.1 TEZPUR
Create another table to store training details of
employee as-
mysql> create table training(empno int,
trainingname varchar(20),startdate date,
enddate date, constraint myfkey foreign
key(empno) references ABCLtd(empno));
VINOD KUMAR VERMA, PGT(CS), KV OEF KANPUR &
SACHIN BHARDWAJ, PGT(CS), KV NO.1 TEZPUR
Inserted successfully
because matching
empno is in ABCLtd
Error, empno 3
not in ABCLtd
Now Let us try to insert records in our training table:
VINOD KUMAR VERMA, PGT(CS), KV OEF KANPUR &
SACHIN BHARDWAJ, PGT(CS), KV NO.1 TEZPUR
Note: after foreign key is applied, we cannot delete any
record or update primary key value in master table
because its related records will be in foreign key table
2 main options available while applying foreign key:
1. ON DELETE CASCASE : it means if any record from
master table is deleted its related records in foreign key
table will also be deleted
2. ON UPDATE CASCADE: it means if primary key value in
master table is changed then it will be automatically
reflected in foreign key table
VINOD KUMAR VERMA, PGT(CS), KV OEF KANPUR &
SACHIN BHARDWAJ, PGT(CS), KV NO.1 TEZPUR
Column level constraint is given With column
definition
with column definition
Example: create table visitor(vid int primary key,
vname varchar(20));
Table level constraints are given after all column
definition.
Example: create table visitor(vid int primary key,
vname varchar(20), primary key(vid));
VINOD KUMAR VERMA, PGT(CS), KV OEF KANPUR &
SACHIN BHARDWAJ, PGT(CS), KV NO.1 TEZPUR
MySQL allows us to give names to constraints to
that when error occurs due to constraint violation
then this name will appears to help us in identifying
for which column this error occurs.
Example:mysql> create table training(empno int,
trainingname varchar(20),startdate date,
enddate date, constraint myfkey foreign
key(empno) references ABCLtd(empno));
VINOD KUMAR VERMA, PGT(CS), KV OEF KANPUR &
SACHIN BHARDWAJ, PGT(CS), KV NO.1 TEZPUR
MySQL allows us to get the structure of table like
list of columns, data type, size and key information
of table using DESC / DESCRIBE command
Example
VINOD KUMAR VERMA, PGT(CS), KV OEF KANPUR &
SACHIN BHARDWAJ, PGT(CS), KV NO.1 TEZPUR
Python allows us to create either fresh table of table
based on existing table. Now we will see how we can
create table based on existing table like backup of a
table or copy of a table.
Full Copy
▪ Create table XYZLtd as select * from ABCLtd;
Selected Column copy
▪ Create table ABCCorp as select empno, name, salary from
ABCLtd;
Selected Record Copy
▪ Create table ITTable as select * from ABCLtd where dept=‘IT’;
VINOD KUMAR VERMA, PGT(CS), KV OEF KANPUR &
SACHIN BHARDWAJ, PGT(CS), KV NO.1 TEZPUR
ALTER TABLE command allows us to perform
the following operations:
▪ Adding new column in existing table
▪ Dropping existing column from table
▪ Modifying column definition in table
▪ Changing the name of column
▪ Adding or dropping constraint after table
creation.
VINOD KUMAR VERMA, PGT(CS), KV OEF KANPUR &
SACHIN BHARDWAJ, PGT(CS), KV NO.1 TEZPUR
After new column is added, if
you select record it will display
NULL in that column for
previous record, we have to
update it using UPDATE
command
VINOD KUMAR VERMA, PGT(CS), KV OEF KANPUR &
SACHIN BHARDWAJ, PGT(CS), KV NO.1 TEZPUR
ALTER TABLE ABCLtd drop designation;
VINOD KUMAR VERMA, PGT(CS), KV OEF KANPUR &
SACHIN BHARDWAJ, PGT(CS), KV NO.1 TEZPUR
VINOD KUMAR VERMA, PGT(CS), KV OEF KANPUR &
SACHIN BHARDWAJ, PGT(CS), KV NO.1 TEZPUR
VINOD KUMAR VERMA, PGT(CS), KV OEF KANPUR &
SACHIN BHARDWAJ, PGT(CS), KV NO.1 TEZPUR
SACHIN BHARDWAJ, PGT(CS), KV NO.1 TEZPUR
VINOD KUMAR VERMA, PGT(CS), KV OEF KANPUR &
IN PLACE OF “FIRST” WE CAN ALSO USE
“AFTER COLUMN NAME”
FOR E.G. TO SET DEPTNO AFTER ENAME
ALTER TABLE EMP MODIFY DEPTNO INT
AFTER ENAME
In this way
we can add
any
constraint
VINOD KUMAR VERMA, PGT(CS), KV OEF KANPUR &
SACHIN BHARDWAJ, PGT(CS), KV NO.1 TEZPUR
VINOD KUMAR VERMA, PGT(CS), KV OEF KANPUR &
SACHIN BHARDWAJ, PGT(CS), KV NO.1 TEZPUR
While dropping Primary Key, if it is connected with child table, it will not gets deleted
By default, however if you want to drop it we have to issue following commands
ALTER TABLE EMP DROP PRIMARY KEY CASCADE
VINOD KUMAR VERMA, PGT(CS), KV OEF KANPUR &
SACHIN BHARDWAJ, PGT(CS), KV NO.1 TEZPUR
DROP TABLE[IF EXISTS] tablename
Example
▪ Drop Table emp;
▪ Drop table if exists emp;
VINOD KUMAR VERMA, PGT(CS), KV OEF KANPUR &
SACHIN BHARDWAJ, PGT(CS), KV NO.1 TEZPUR
