MySQL - Usage of Commands in Data Base

MySQL - Usage of Commands in Data Base

AIM

To execute following DDL [Data definition Language] and DML [Data manipulating Language] MySQL queries

1. CREATE - to create a database and its objects like (table, index, views, store procedure, function, and triggers)

2. ALTER - alters the structure of the existing database.

3. DROP - delete objects from the database.

4. SELECT - retrieve data from a database.

5. INSERT - insert data into a table.

6. UPDATE - updates existing data within a table.

7. DELETE - Delete all records from a database table.

Procedure

1. Open MySQL command prompt from XAMPP Control panel.

2. To login in to your Database using User name and password.

3. Execute and get the output of given DDL MySQL queries.

SYNTAX:

Based on the below syntax queries are built and executed.

* CREATE DATABASE testDB;

* CREATE TABLE table_name (column1datatype,column2datatype,column3datatype, .... );

* ALTER TABLE `table_name` ADD COLUMN `column_name` `data_type`;

* DROP DATABASE databasename;

* SELECT * FROM TABLE NAME

* INSERT INTO table_name( field1, field2,...fieldN ) VALUES ( value1, value2,...valueN );

* UPDATE table_name SET field1 = new-value1, field2 = new-value2 [WHERE Clause]

* DROP TABLE table_name ;

PROGRAM:

CREATE DATABASE SchoolDB;

CREATE TABLE student(

            studentID int,

            LastName varchar(255),

            FirstName varchar(255),

            Address varchar(255),

            City varchar(255)

            );

ALTER TABLE `members` ADD COLUMN `credit_card_number` VARCHAR(25);

            DROP DATABASE SchoolDB

            SELECT * FROM student

            INSERT INTO student (studentID ,LastName , FirstName,Address,City ) VALUES (“002”, “Ram”,”Kumar”, “ROJA NAGAR”,”CHENNAI”);

            SELECT * FROM student

            UPDATE student SET LastName = ‘SRI’ WHERE studentID = 003;

            SELECT * FROM student

            DROP TABLE student ;

            SELECT * FROM student

OUTPUT