MySQL create, alter and drop table, DB   

In previous posts we saw some DML(Data Manipulation Language) examples. Now we will have a look on DDL(Data Definition Language):

• CREATE DATABASE
• DROP DATABASE
• CREATE table
• MODIFY table
• DROP table

CREATE DATABASE

Before to do anything else in world of SQL and Database you need to create your database. The second example sets the encoding to UTF. For MySQL 5.7 server default is latin1_swedish which is changed in MySQL 8:

CREATE DATABASE test;
CREATE DATABASE `test` /*!40100 COLLATE 'utf8_bin' */

You can check more about MySQL 5.7 vs MySQL 8 here: MySQL 5.7 vs MySQL 8

MySQL DROP DATABASE

Deleting or removing Database is very simple and you need to be really careful here. In order to drop database you need to have admin privilege :

DROP DATABASE test;

MySQL show databases

You can check what databases you have on your server by this command:

SHOW DATABASES;

MySQL create table

Once you create your DB you will need to make a design and then to create your tables. Creation of tables could be done by tools or by SQL. This is a sample SQL for creating a table: aboys with 3 columns related to another table named agirls

CREATE TABLE `aboys` (
	`id` INT(11) NULL DEFAULT NULL,
	`name` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_bin',
	`relation` INT(11) NULL DEFAULT NULL,
	INDEX `FK_aboys_agirls` (`relation`),
	CONSTRAINT `FK_aboys_agirls` FOREIGN KEY (`relation`) REFERENCES `agirls` (`id`)
)
COLLATE='utf8_bin'
ENGINE=InnoDB
;

MySQL change table columns

If you want to change columns of a table then you can do:

-- add new column
ALTER TABLE students ADD birthDate date; 
-- remove column
ALTER TABLE students DROP birthDate date; 
-- change type
ALTER TABLE students ALTER COLUMN DateOfBirth int; 

MySQL remove table

Removing or dropoing table can be done by:

DROP TABLE students;