Creating a MYSQL Database

In my previous post, I had introduced databases and different applications of the database. Although the pertinent part of the post was on how to use Microsoft windows shell there was more information about MySQL basics.
Moving on... Once the set up of MYSQL database is done we need to create a database that can be accessed by other applications (acts as a backend to many web-applications).

Creation of Database

mysql > create database database_name; 

this above command is used to create a database, various errors you might encounter while creation of database are :

• ERROR 1007 (HY000): Can't create database'database_name'; database exists - database already exists
• ERROR 1044 (42000): Access denied for user 'user_name'@'%' to database 'database_name' - user has no access permission 

mysql > use database_name;

• ERROR 1049 (42000): Unknown database 'database_name' 
• ERROR 1044 (42000): Access denied for user 'user_name'@'%' to database 'database_name'

Creation of tables

mysql> create table table_name(column_1 data_type1(size),column_2 data_type2(size));
In the above command the datatypes can differ based on what king of data you store in a particular field.
Datatypes:

Numerical values

• bigint
• bit
• decimal
• int
• money
• numeric
• smallint 
• float

Date

• date
• datetime

String

• char
• varchar
• text

To find more about the datatypes refer the below link

Datatypes in MySQL

 

Insertion of values into the table

Method 1:
mysql> insert into table_name(column_1, column_2) values (1, 'first_value');

Method 2:
mysql> insert into table_name values (1, 'first_value');

Errors that occur while entering the above statements during the following reasons:

• ERROR 1146 (42S02): Table 'new.table_name' doesn't exist- when there is change the table name or that particular table doesn't exist
• ERROR 1136 (21S01): Column count doesn't match value count at row 1 - when we try to insert more or less number of values for the given defined values.

View the table elements

mysql> select * from table_name;

This above command is not a predefined view that is present in Database which more towards creating a virtual table only for the viewing purpose. 

We are using select * command to extract all the values in the table. Using that we can test if the elements are inserted in the desired way or not.

These are the few basic commands and errors that might occur when you run those commands.

That's all folks!