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:
*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.
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