DBMS

Database Management
System
BATCH 5
What is Database ?
The database is a collection of inter-related data which is used to retrieve, insert and delete the data efficiently.
It is also used to organize the data in the form of a table, schema, views, and reports, etc.
For example: The college Database organizes the data about the
admin, staff, students and faculty etc.
Using the database, you can easily retrieve, insert, and delete the
information.
Database Management System
 Database management system is a software which is used to manage the database. For
example: MySQL, Oracle , etc are a very popular commercial database which is used in different
applications.
 DBMS provides an interface to perform various operations like database creation, storing data in it,
updating data, creating a table in the database and a lot more.
 It provides protection and security to the database. In the case of multiple users, it also maintains data
consistency.
What is MySQL?
 MySQL is currently the most popular database management system software

used for managing the relational database.
This model is designed for the end-users called clients to access the resources from a central
computer known as a server using network services.
SQL Commands
MySQL Create Database
We can create a new database in MySQL by using the CREATE DATABASE statement with the below syntax:
CREATE DATABASE [IF NOT EXISTS] database_name ;
We can check the created database using the following query:
SHOW DATABASES;
We can use the below command to access the database that enables us to create a table and
other database objects.
USE database_name;
• We can drop/delete/remove a MySQL database quickly with the MySQL DROP DATABASE
command.
• It will delete the database along with all the tables, indexes, and constraints permanently.
DROP DATABASE [IF EXISTS] database_name;
MySQL CREATE TABLE
• A table is used to organize data in the form of rows and columns and used for both storing and
displaying records in the structure format.
• It is similar to worksheets in the spreadsheet application. A table creation command requires three
things:
• Name of the table

• Names of fields
• Definitions for each field
CREATE TABLE [IF NOT EXISTS] table_name(
column_definition1,
column_definition2,
........,
table_constraints
);
Parameter Description
database_name It is the name of a new table. It should be unique in the MySQL database that we have selected.
The IF NOT EXIST clause avoids an error when we create a table into the selected database
that already exists.
column_definition It specifies the name of the column along with data types for each column. The columns in table
definition are separated by the comma operator. The syntax of column definition is as follows:
column_name1 data_type(size) [NULL | NOT NULL]
table_constraints It specifies the table constraints such as PRIMARY KEY, UNIQUE KEY, FOREIGN KEY,
CHECK, etc.
We need to use the following command to see the newly created table:
SHOW TABLES;
We can use the following command to see the information or structure of the newly created table:
DESCRIBE table_name;
MySQL ALTER Table
 MySQL ALTER statement is used when you want to change the name of your table or any table
field.
 It is also used to add or delete an existing column in a table.
 The ALTER statement is always used with "ADD", "DROP" and "MODIFY" commands according
to the situation.
1) ADD a column in the table

ALTER TABLE table_name
ADD new_column_name column_definition
[ FIRST | AFTER column_name ];
2) Add multiple columns in the table
Syntax:

[ FIRST | AFTER column_name ],
[ FIRST | AFTER column_name ],
...
;
3) MODIFY column in the table

The MODIFY command is used to change the column definition of the table.
Syntax:

MODIFY column_name column_definition
[ FIRST | AFTER column_name ];
4) DROP column in table
Syntax:
DROP COLUMN column_name;
5) RENAME column in table

Syntax:
CHANGE COLUMN old_name new_name
column_definition
[ FIRST | AFTER column_name ]
6) RENAME table
Syntax:
RENAME TO new_table_name;
MySQL TRUNCATE Table
 The TRUNCATE statement in MySQL removes the complete data without

removing its structure.
 It is a part of DDL or data definition language command.
 Generally, we use this command when we want to delete an entire data from a
table without removing the table structure.
 Syntax:
TRUNCATE [TABLE] table_name;

MySQL INSERT Statement
 MySQL INSERT statement is used to store or add data in MySQL table within the database.
 We can perform insertion of records in two ways using a single query in MySQL:
1. Insert record in a single row
2. Insert record in multiple rows
Syntax:
The below is generic syntax of SQL INSERT INTO command to insert a single record
in MySQL table:
INSERT INTO table_name ( field1, field2,...fieldN )
VALUES
( value1, value2,...valueN );
If we want to insert multiple records within a single command, use the following statement:
INSERT INTO table_name VALUES

( value1, value2,...valueN ) ,
( value1, value2,...valueN ) ,
...........
( value1, value2,...valueN );
MySQL UPDATE Query
 MySQL UPDATE query is a DML statement used to modify the data of the MySQL
table within the database.
 The UPDATE statement is used with the SET and WHERE clauses. The SET
clause is used to change the values of the specified column. We can update
single or multiple columns at a time.
 Syntax:Following is a generic syntax of UPDATE command to modify data into
the MySQL table:
UPDATE table_name
SET column_name1 = new-value1,
column_name2=new-value2, ...
[WHERE Clause]
MySQL DELETE Statement
 MySQL DELETE statement is used to remove records from the MySQL table that
is no longer required in the database.
DELETE FROM table_name WHERE condition;
The SQL SELECT Statement

The SELECT statement is used to select data from a database.
SELECT column1, column2, ...

FROM table_name;
Thank You

DBMS

Uploaded by

Copyright:

Available Formats

DBMS

Uploaded by

Document Information

Original Description:

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

DBMS

Uploaded by

Copyright:

Available Formats

Database Management

 MySQL is currently the most popular database management system software

CREATE DATABASE [IF NOT EXISTS] database_name ;

We can check the created database using the following query:

DROP DATABASE [IF EXISTS] database_name;

MySQL CREATE TABLE

• Name of the table

1) ADD a column in the table

ALTER TABLE table_name

3) MODIFY column in the table

ALTER TABLE table_name

5) RENAME column in table

 The TRUNCATE statement in MySQL removes the complete data without

TRUNCATE [TABLE] table_name;

INSERT INTO table_name VALUES

The SQL SELECT Statement

SELECT column1, column2, ...

You might also like