MySQL Tutorial

Learn MySQL step by step

 

Oracle DBA

Learn Oracle 11g / 12c Database Admin step by step


Oracle SQL

Tutorial for Oracle SQL


Oracle DBA Interview Questions

Most asked Oracle DBA Interview Questions.

60 Technical Questions
42 Backup & Recovery Questions
Unix For Oracle DBA 20 Questions






 

 

 

 

 

 

 

 

 

Creating Tables in MySQL

 

The table is the main structure for storing data in databases. A table is composed of rows and columns.

A table can represent a single entity that you want to track within your system. This type of a table could represent a list of the employees within your organization, or the orders placed for your company's products.

a sample table 

Consider the following guidelines when designing your tables:

Before creating a Table you need to decide what type of data each column can contain. This is known as datatype. We need to specify the datatype at the time of creation of a table and once you specify the datatype you can enter only that type of data in that column. Like for example in the above example "Employees" table, if you specify Empno column as integer then you can enter only numerical values into it. You cannot enter values with alphabets like A101, b102.

Datatypes in MySQL

MySQL comes with several data types to meet almost every need. These data types can be classified into following  categories

For more information about data types please see next chapter

Now lets see how to create a table in MySQL. For example let us create a employees table with the following structure

  1. Empno
  2. Name
  3. Salary
  4. Comm
  5. Jdate

To create this table we need to start MySQL Command Line Client, so let us start it my clicking Start Button in Windows and then clicking on MySQL->MySQL Command Line Client as shown in the picture below

mysql command line client

Enter root password to login

Then issue "use sales" statement to select 'sales' database. We will be creating the table in this database

mysql client use database

Now to create the table give the following command at mysql> prompt

create table employees (
        empno    int,
        name     varchar(100),
        salary   decimal (10,2),
        comm     int,
        jdate    datetime);

Once you type the above statement you should get "Query OK" message as shown below

create-table-mysql

The above statement is bare minimum for creating tables, we have left many advance features like constraints, indexes, default values  and database engine, as we are in the beginner stage and we will dealt with it in the next chapters.

Now to enter rows in the table we can use INSERT statement.

Let us enter the following row into employee tables

empno:101
name:Scott
salary:4300
comm: 20
jdate: 10-10-2012

To insert a row give the insert SQL statement as shown below

insert into employees values (101,'Scott',4300,20,'10/10/12');

Similarly we can also insert several rows in one INSERT statement like this,

insert into employees values (102,'Smith',4000,15,'21/05/11'),
            (103,'Karen',2400,20,'04/02/09'),
            (104,'Fraz',5400,30,'27/11/11');

insert multiple rows in mysql

Next to view the rows in employees table, we can give SELECT statement

select * from employees;

select-rows-mysql-table

We learn more about SELECT statement in next chapters

Now how to view the structure of table in MySQL. Suppose later on after some days you forgot the number of columns in Employees tables or forgot the exact names of these columns. In this case, you can give "SHOW COLUMNS FROM <TABLE_NAME> statement or
DESCRIBE <TABLE_NAME> . In MySQL both statements are identical.

describe table in mysql

Next MySQL Datatypes

 

 

 


HomeContact Us

Data Loader

Data Loader is a simple yet powerful tool to
export and import Data between many common database formats


Forms Data Loader

Tool to load data into Oracle E-Business Suite R12 / Oracle Apps using Macros and Forms Record and Playback

Interface Computers Academy © 2007-2017 All Rights Reserved