SQL Concepts

There are some SQL Concepts that we must know before we can start using SQL Language.  

Tables Consist of Columns and Rows. When we create table we specify the names and types of columns, to make sure that proper data is stored in our columns.

The main Database concepts that we are going to learn here are

  • Datatypes
  • Primary Key
  • Auto Increment
  • Not Null
  • Unsigned

To understand these Databse Concepts consider the following table. We have created a table here for storing our products information. You can think of this table as a record register for a shop keeper. In this table our columns are ItemID, ItemName, ItemPrice, ItemCat, ItemDesc

+——–+————-+———–+———-+————————————–+
| ItemID | ItemName    | ItemPrice | ItemCat  | ItemDesc                             |
+——–+————-+———–+———-+————————————–+
|     1   | Premonition  |          9.99| Music      | New & Used from ú59.49       |
|     2   | benz            |       700.00| cars        | a mercedes benz                 |
|     3   | Laptop         |       700.00| computer | very cheap                          |
+——–+————-+———–+———-+————————————–+

DataType

Notice that in the above table every column has a specific type of data. For example, ItemID has only numbers (1, 2, 3). Similarly ItemName has few characters string for example benz, laptop. That means every column has a special datatype.

ItemID is int
ItemName is varchar
ItemPrice is decimal
ItemCat is varchar
ItemDesc is text

We will discuss SQL DataTypes in detial later in this tutorial here

Primary Key

Primary Key uniquely identify a record in table. Consider the above table example, If you notice every Value in ItemID column in unique and not repeated. That is why ItemID column in this example is Primary key column. That means two or more Item Names, Prices, Category, Description can be similar. But Item ID will always be unique.

  • Primary Key is always unique
  • Primary Key never repeated or kept null
  • Primary Key can be on one column or many columns togather
  • MySQL will automatically index Primary Key column

Auto Increment

AutoIncrement is used when we don't want to enter value and we want SQL to handle values. For example in above table ItemID column has product IDs. We will enter name, price, cat, description of products and database will automatically assign it an id, if we define our ItemID column as auto increment. In MySQL the keyword that defines auto increment is auto_increment, on the other hand the same action is performed in MsSQL server by identity

UnSigned

Unsigned means that the column can only have a zero or positive value

You can find more on Database Concepts by using our Simple Tutorial on Database Concepts. Clcik here to read more about Database Concepts