MYSQL Tutorial Part 2
In this part, will learn how to create, use and delete databases and tables with syntax and examples.
DATABASE:
- Database is a collection of data in a structured format.
- Organize the data as a table, row, column, and index.
- It is used to work with a large amount of information by storing, retrieving, and manipulating data.
CREATE DATABASE:
The CREATE DATABASE statement is used to create a new SQL database.
Syntax
CREATE DATABASE databasename;
Example
CREATE DATABASE Students;
RENAME DATABASE:
The RENAME DATABASE statement is used to rename the existing SQL database.
Syntax
RENAME DATABASE old_databasename TO new_databasename;
Example
RENAME DATABASE OldStudents TO Students;
It was added in MYSQL 5.1.7 version, but there is a lot of database content loss issue. So it was removed. If need to rename the database name, then create a new empty database and rename each table into the new database.
Syntax
RENAME TABLE old_db.table TO new_db.table;
SHOW DATABASE:
- The SHOW DATABASE statement is used to show or list all the databases.
- The SHOW SCHEMAS statement is also used to list all the databases.
- Both commands give the same result
Syntax
SHOW DATABASES;
USE DATABASE:
The USE DATABASE statement is used to use the database as a current database.
Syntax
USE DATABASE;
Example
USE Students;
DROP DATABASE:
The DROP DATABASE statement is used to drop an existing SQL database.
Syntax
DROP DATABASE databasename;
Example
DROP DATABASE Students;
CREATE TABLE:
The CREATE TABLE statement is used to create a new table in a database.
Syntax
CREATE TABLE table_name ( column1 datatype, column2 datatype,);
Example
CREATE TABLE Students(rollno int, name varchar(50), department varchar(30), district varchar(40), fees int);
CREATE A TABLE USING ANOTHER TABLE:
Syntax
CREATE TABLE new_table_name AS SELECT column1, column2 FROM existing_table_name WHERE ....;
Example
CREATE TABLE TestTable AS SELECT name, department FROM Students;
DROP TABLE:
The DROP TABLE statement is used to drop an existing table in a database.
Syntax
DROP TABLE table_name;
Example
DROP TABLE Students;
TRUNCATE TABLE:
The TRUNCATE TABLE statement is used to delete the data inside a table, but not the table itself.
Syntax
TRUNCATE TABLE table_name;
Example
TRUNCATE TABLE Students;
DELETE vs TRUNCATE vs DROP :
| DELETE | TRUNCATE | DROP |
|---|---|---|
| The DELETE command is Data Manipulation Language Command. | The TRUNCATE command is a Data Definition Language Command. | The DROP command is Data Definition Language Command. |
| The DELETE command deletes one or more existing records from the table in the database. Using WHERE clause, able to delete the particular records. | The TRUNCATE Command deletes all the rows from the existing table, leaving the row with the column names. Can't able to use the WHERE clause. | The DROP Command drops the complete table from the database. |
| Can able to restore the deleted rows from the database using ROLLBACK command. | Can't restore all the deleted rows from the database using ROLLBACK command. | Can't get the deleted table from the database using ROLLBACK command. |
ALTER TABLE:
- The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.
- The ALTER TABLE statement is also used to add and drop various constraints on an existing table.
ALTER TABLE - ADD COLUMN
To add a column in a table, use the following syntax:
ALTER TABLE table_name ADD column_name datatype;
Example
ALTER TABLE Students ADD DateOfBirth date;
ALTER TABLE - DROP COLUMN
To delete a column in a table, use the following syntax (notice that some database systems don't allow deleting a column):
ALTER TABLE table_name DROP COLUMN column_name;
Example
ALTER TABLE Students DROP COLUMN DateOfBirth;
ALTER TABLE - MODIFY COLUMN
To change the data type of a column in a table, use the following syntax:
ALTER TABLE table_name MODIFY COLUMN column_name datatype;
Example
ALTER TABLE Students MODIFY COLUMN DateOfBirth year;
Comments
Post a Comment