module 5

Database name: It is the name of a new database that should be unique in the MySQL server instance. The IF NOT EXIST clause avoids an error when we create a database that already exists.
Charset Name: It is the name of the character set to store every character in a string. MySQL database server supports many character sets. If we do not provide this in the statement, MySQL takes the default character set.
Collation Name: It is optional that compares characters in a particular character set.

Creating Databases (code example)

Code Example:

CREATE DATABASE employee;
Show databases;

Selecting Databases

- SELECT Database is used in MySQL to select a particular database to work with. This query is used when multiple databases are available with MySQL Server.

You can use SQL command USE to select a particular database.

Syntax:

USE database_name;

Code example:

- USE database1;

Drop Databases

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.

Therefore, we should have to be very careful while removing the database in MySQL because we will lose all the data available in the database.
If the database is not available in the MySQL server, the DROP DATABASE statement throws an error.

Drop Databases (if exists)

We can drop an existing database in MySQL by using the DROP DATABASE statement with the below syntax:

DROP DATABASE [IF EXISTS] database_name;

In MySQL, we can also use the below syntax for deleting the database. It is because the schema is the synonym for the database, so we can use them interchangeably.

- DROP SCHEMA [IF EXISTS] database_name;

Drop Databases

Workbench Parameters

- Database_name: It is the name of an existing database that we want to delete from the server. It should be unique in the MySQL server instance.
- If Exists: It is optional. It is used to prevent from getting an error while removing a database that does not exist.

Drop Databases (code example)

Code Example:

SHOW DATABASES;
DROP DATABASE database1;

Creating Databases

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 a structured format. It is similar to worksheets in spreadsheet applications like MS Excel.

A table creation command requires three things:

Name of the table
Names of fields
Definitions for each field

Create Table (syntax)

MySQL allows us to create a table into the database by using the CREATE TABLE command.

SYNTAX:

CREATE TABLE [IF NOT EXISTS] table_name(
column_definition1,
column_definition2,
........,
table_constraints
);

Create Table (code example)

Code Example:

CREATE TABLE employees (
NAME VARCHAR (20),
ID VARCHAR(15),
ADDRESS VARCHAR(50)
);
SHOW TABLES;
DESCRIBE employee;

Alter Table

The 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.

Alter Table (syntax)

Syntax for adding single column:

- ALTER TABLE table_name
- ADD new_column_name column_definition
- [ FIRST | AFTER column_name ];

Alter Table (more syntax)

SYNTAX:

ALTER TABLE table_name
ADD new_column_name column_definition
[ FIRST | AFTER column_name ],
ADD new_column_name column_definition
[ FIRST | AFTER column_name ], ... ;

Modify Column

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

SYNTAX:

ALTER TABLE table_name
MODIFY column_name column_definition
[ FIRST | AFTER column_name ];

Drop a Column in a table

SYNTAX:

ALTER TABLE table_name
DROP COLUMN column_name;

Rename a Column in a table

SYNTAX:

ALTER TABLE table_name
CHANGE COLUMN old_name new_name
column_definition
[ FIRST | AFTER column_name ]

Rename a table

SYNTAX:

ALTER TABLE table_name
RENAME TO new_table_name;

Truncate a 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;

Truncate a table (where)

We cannot use the WHERE clause with this command so that filtering of records is not possible.
We cannot rollback the deleted data after executing this command because the log is not maintained while performing this operation.

Truncate a table (foreign key)

We cannot use the truncate statement when a table is referenced by a foreign key or participates in an indexed view.
The TRUNCATE command doesn't fire DELETE triggers associated with the table that is being truncated because it does not operate on individual rows.

Describe a table

- DESCRIBE means to show the information in detail.
- DESCRIBE command to show the structure of our table, such as column names, constraints on column names, etc. The DESC command is a short form of the DESCRIBE command. Both DESCRIBE and DESC command are equivalent and case sensitive.

Syntax

{DESCRIBE | DESC} table_name;

Drop a table

- MYSQL uses a Drop Table statement to delete the existing table. This statement removes the complete data of a table along with the whole structure or definition permanently from the database.

Syntax:

DROP TABLE table_name;

OR,

DROP TABLE schema_name.table_name;