Constraint : A constraint is a condition or check applicable on a field or a set of
fields.
SQL Constraints
1. NOT NULL : Ensures that a column cannot have NULL value.
2. Primary Key : Used to uniquely identify a row in the table.
Creating Databases
Create database [If not Exists] <database name>;
E.g, create database abc;
Opening Databases
Use <databasename>
e.g., use abc;
Removing Databases
Drop database <databasename>;
E.g, drop database abc;
Data Definition Language(DDL) Commands
1. Create
2. Alter
3. Drop
Creating tables:
Syntax:
Create table <tablename>(<columnname><datatype>[(<size>)],
<columnname><datatype>(<size>)) …..);
e.g.,
Create table student(Sname varchar(10) not null,GRNO int primary key, Class int
not null, Section char(2));
To view the structure of the table
Desc <tablename>;
E.g., desc student;
ALTER TABLE Command
In MySQL SQL, Alter Table command is used :
1. To add a new column
2. To modify an existing column(datatype, size, default value)
Add a new column
Syntax : ALTER TABLE <tablename> ADD <columnname> <datatype> [(size)]
[<constraint>]
E.g., Alter table Empl add tel_no int;
Modify an existing column
Syntax : ALTER TABLE <tablename> MODIFY <columnname> <new datatype>
[(new size)] [FIRST | AFTER column];
E.g., Alter table Empl modify job char(30);
Dropping a table from database
Syntax : DROP TABLE <tablename>;
E.g., drop table student;
Data Manipulation Language Commands:
1. Insert
2. Update
3. Delete
Insert Data into Table
Syntax1 : INSERT INTO <tablename> VALUES(<value1>, <value2>, ….>)
Remember string and date data should be put inside single quotes and number data
to write without quotes.
Syntax2 : INSERT INTO <tablename>(<col1>,<col2>,…) values(<val1>,<val2>,
…);
E.g., INSERT INTO Employee values(1, ’Din’, ‘Joseph’, ‘Jdim’, 5000);
INSERT INTO Employee(ID, FirstName, LastName, UserID, Salary) values(2,
’Jagannath’, ‘Mishra’, ‘jnmishra’, 4000);
Insert NULL values
To insert value NULL in a specific column, you can type NULL without quotes and
NULL will be inserted in that column.
Inserting Dates
The format to insert date is ‘yyyy-mm-dd’.
Modifying Data with UPDATE command
• Sometimes you need to change some or all of the values in an existing column.
This can be done using UPDATE command.
• The UPDATE command specifies the rows to be changed using the WHERE
clause, and the new data using the SET keyword.
Syntax: UPDATE <tablename> SET <columnname>=<new value> WHERE
<condition>;
Here condition is optional.
Deleting data from table using DELETE Command
• Rows can be removed from the given table using DELETE command.
• This command will remove the entire rows which satisfy the given condition,
not selected fields from the table.
Syntax : DELETE FROM <tablename> WHERE <condition>;
Condition is optional.
