MYSQL03: Creating table

After a database is created, tables are created in that database to store data. To create table two SQL commands CREATE and TABLE are used. Then information about the table is given to the server. Following is syntax for create a table:

CREATE TABLE tableName(
   field1,
   field2,
   ...
   fieldN
);

Example

CREATE TABLE students(
   id int auto_increment,
   name text NOT NULL,
   age  smallint,
   PRIMARY KEY(id)
);

This will create a table named students with three fields {id, name, age}. Notice that name of the table is always plural as it has more than one student records. When creating the table we provide data type of the field.

students
id
name
age




*Note

Mysql support three types of data types, number, text and date.

Number data type can be broken down to INT, TINYINT, SMALINT, MEDIUMINT, BIGINT, FLOAT, DOUBLE and DECIMAL

Text data type broken down into: CHAR, VARCHAR, TINYTEXT, TEXT, BLOB, MEDIUMTEXT, MEDIUMBLOB, ENUM and SET

Finally, Date data type consist of: DATE, DATETIME, TIMESTAMP, TIME and YEAR

PRIMARY KEY is the field in the table that has unique value for each individual record. That being said the value of primary key cannot repeat in the same table. Since the primary key is essential to identify the record uniqueness, the value cannot be null.

AUTO INCREMENT  a unique number automatically generated in incremental order when a new record is successfully inserted into the table. It is typical to set this constraint to the primary key.

NOT NULL constraint makes sure the field has a value. Trying to insert a new value without providing this field will be unsuccessful.





Comments

Post a Comment

Popular posts from this blog

Drawing Simple Pie Chart

Image
To draw pie chart with 5 sectors. The program calculates the total of all the input given by user and find the angle in degrees to draw each sectors. When the program first loads, chart based on default values is painted by the program. User can edit the values in the text field and presses draw button to redraw the chart. Each time the draw button is pressed, new set of angle is calculated and repainted. I have attached the the sources for this program. It is simple and needs upgrades. Pie.java This class executed the program and initializes the components JTextField and JPanel to draw the chart. package coolApp.graphics; import java.awt.*; import java.awt.event.*; import javax.swing.*; public class Pie { JTextField a,b,c,d,e; JButton btnDraw; double total; JPanel contentPane; Pie(){ JFrame frame = new JFrame("Pie - CoolApps"); frame.setSize(500,500); contentPane = new JPanel(); contentPane.setLayout(new BorderLayout()); frame...
Read more

VB.net connecting to SQL Server

Connecting to SQL server 2014 from VB.Net 2017, read values from the database and display the data in windows form. Before begin to write this program, first we need to import SqlClient class. Imports System.Data.SqlClient Step 1 Create a sql connection object. This is to make connection to object. Dim conn As New SqlConnection() Step 2 Set the connection string to the sqlconnection object: conn.ConnectionString = connStr Road block The variable connStr is a connection string. Connection object need connection string to make connection to database. It has information like who providing the database, the name of the database and some security information. Dim connStr As String = "Data Source = ServerInstanceName;Initial Catalog=testDB;Integrated Security=True" Let's break down the connection string into the three main components. Dim provider As String = "ServerInstanceName;" Dim database As String = "Initial Catalog=testDB;" Dim ...
Read more

Setting up data set and display it

Before we display some data in DataGridView control in Visual Basic window we must create a data set. Data Set is an object that can hold data tables where all the data is stored in rows and columns. the first step is to create a table. Dim  dataTableObjectName as DataTable = New DataTable(tableName) this will create a table which is reference using the dataTableObjectName Example Dim userTable As DataTable = New DataTable("Users") Next step is adding columns to the table Dim column as DataColumn dataTableObjectName.Columns.Add (columnName, columnType) Example userTable.Columns.Add("userID", Type.GetType("System.Int.32")) ' adding second and third column userTable.Columns.Add("userName", Type.GetType("System.String")) userTable.Columns.Add("userContact", Type.GetType("System.String")) once we we columns in the table we can add row The rows contains records or the data for each user. dat...
Read more