How to create a database and a table in MySQL

Creating a database

To create a database in MySQL, we use the following syntax:

create database xyz;  

Here, xyz is a variable name, but you can name it whatever you want.

Accessing or opening an existing database

To open or use an existing database, use the following syntax:

use xyz; 

Here, xyz is the database we created earlier.

To see the current working database

MySQL contains multiple databases. To see which database the end-user is currently working on, use the following syntax:

select xyz; 

Here, xyz is the database we created earlier.

Listing databases

This command lists out all the databases that are available in MySQL on that server. The syntax for this is:

show database;

Dropping a database

This helps to drop a database. If a database is dropped in MySQL, then all the tables inside that database are also dropped. The syntax is:

drop database xyz;

Here, xyz is the database we created earlier.

Table operations

Creating a table

To create a table, use the following syntax:

create table abc(column1 datatype(size), column3 datatype(size));

We can add any number of columns. Here, abc is the table name, but it can be any name.

column1, column2, and column3 are the column names (the columns can be in any number and have any name). The datatypes (it can be any datatype accepted in MySQL) and the size can be specified according to the datatype and need in the column.

Note: Column and database names should not have spaces. If we want to separate them, we use underscore _ symbol.

Insert command

This command helps to insert data into a table. The syntax is:

insert into abc values('value1','value2', 'value3');

Here, abc is the table we previously created. The values (value1, value2, and value3) can be anything based on the data type and size described while creating the table.

Note : For char, varchar, date, and time datatypes of data, it is compulsory to enclose them in “” or ’ '. For integers, decimal quotations are optional. Nulls shouldn’t be enclosed in quotes.

Select command

This command helps to view the details of a table or extract details from a table.

Extracting complete details from the table

To see the whole table, use the following syntax:

select * from abc;

Here, abc is the table we previously created.

Extracting a few columns from table

To display a few columns from a the table, we use the following syntax:

select column1,column2 from abc;

Here, abc is the previously created table, and column1 and column2 are 2 of the columns created.