How to create table in mysql using cmd - The general form of SQL to create a table simply as follows:
CREATE TABLE table_name (
field1 type(long),
field2 type(long),
...
fieldn type(long),
PRIMARY KEY (field_key)
);
The general form above is a common form of simplified table making. Naming tables and fields have the same rules as naming the database.
MySQL provides various data types with specifications and lengths of each. Data types for fields in MySQL are shown in the following table:
Table name: student
To create the table above, the query or SQL command is as follows:
CREATE TABLE student (
studentnumber varchar(10) NOT NULL,
name varchar(30) NOT NULL,
dateofbirth date,
address text,
PRIMARY KEY(studentnumber)
);
If the query to create the table above successfully executed, it will display the message as follows:
Query OK, 0 rows affected (0.14 sec)
Consider the following picture:
In the above command, a few things to note:
SHOW TABLES;
The above command will display all the tables that already exist in a database. Examples of the results of the above command are as follows:
To see the structure of the "student" table in more detail, try the command or query as follows:
DESC student;
DESC is an abbreviation of DESCRIBE (in a query can be written complete or only the first 4 characters) and the student is the name of the table to be seen in the structure. From the above command, will display the structure of student tables as follows:
From the student table structure shown above, it can be seen that:
CREATE TABLE table_name (
field1 type(long),
field2 type(long),
...
fieldn type(long),
PRIMARY KEY (field_key)
);
The general form above is a common form of simplified table making. Naming tables and fields have the same rules as naming the database.
MySQL provides various data types with specifications and lengths of each. Data types for fields in MySQL are shown in the following table:
KIND of TYPE
|
TYPE
|
INFORMATION
|
NUMERIK
|
TINYINT
|
-128 s/d 127 SIGNED
0 s/d 255 UNSIGNED
|
TINYINT
|
-32768 s/d 32767 SIGNED
0 s/d 65535 UNSIGNED.
|
|
MEDIUMINT
|
-8388608 s/d 8388607 SIGNED
0 s/d 16777215 UNSIGNED
|
|
INT
|
-2147483648 s/d 2147483647 SIGNED
0 s/d 4294967295 UNSIGNED.
|
|
BIGINT
|
-9223372036854775808 s/d
9223372036854775807 SIGNED
0 s/d 18446744073709551615 UNSIGNED.
|
|
FLOAT
|
Fraction
of single precision
|
|
DOUBLE
|
Double
precision fractional numbers
|
|
DECIMAL
|
Numbers
with decimals
|
|
DATE/TIME
|
DATE
|
Date
with format: YYYY-MM-DD
|
DATETIME
|
Date
and time with format: YYYY-MM-DD HH: MM: SS
|
|
TIMESTAMP
|
Date
and time with format: YYYYMMDDHHMMSS
|
|
TIME
|
Time
with HH format: MM:SS
|
|
YEAR
|
Year
with format: YYYY
|
|
STRING
|
CHAR
|
0 -
255 characters
|
VARCHAR
|
0 -
255 characters
|
|
TINYTEXT
|
String
with a maximum length of 255 characters
|
|
TEXT
|
String
with maximum length of 65535 characters
|
|
BLOB
|
String
with maximum length of 65535 characters
|
|
MEDIUMTEXT
|
String with a maximum
length of 16777215 characters
|
|
MEDIUMBLOB
|
String with a maximum
length of 16777215 characters
|
|
LONGTEXT
|
String with maximum length
of 4294967295 characters
|
|
LONGBLOB
|
String with maximum length
of 4294967295 characters
|
|
SPECIAL
|
ENUM
|
Data
type with specific content
|
SET
|
Data
type with specific content
|
How to Create Table in MySQL using CMD
For example, we will create a new table with the following structure:Table name: student
No
|
Field
Name
|
Type
|
Long
|
1
|
studentnumber
|
Varchar
|
10
|
2
|
name
|
Varchar
|
30
|
3
|
dateofbirth
|
Date
|
-
|
4
|
address
|
Text
|
-
|
To create the table above, the query or SQL command is as follows:
CREATE TABLE student (
studentnumber varchar(10) NOT NULL,
name varchar(30) NOT NULL,
dateofbirth date,
address text,
PRIMARY KEY(studentnumber)
);
If the query to create the table above successfully executed, it will display the message as follows:
Query OK, 0 rows affected (0.14 sec)
Consider the following picture:
In the above command, a few things to note:
- CREATE TABLE is the basic command of table creation.
- student is the name of the table to be created.
- studentnumber, name, dateofbirth address is the field name
- Varchar, date and text are the data types of fields
- NOT NULL is an option to state that a field can not be empty.
- PRIMARY KEY is a command to specify which field will be the primary key of the table.
- 10 and 30 behind the data type is the maximum length of a field
- For date and text data types (and some other data types) the maximum character length does not need to be specified.
- Do not forget to end the command with a semicolon (;)
SHOW TABLES;
The above command will display all the tables that already exist in a database. Examples of the results of the above command are as follows:
To see the structure of the "student" table in more detail, try the command or query as follows:
DESC student;
DESC is an abbreviation of DESCRIBE (in a query can be written complete or only the first 4 characters) and the student is the name of the table to be seen in the structure. From the above command, will display the structure of student tables as follows:
From the student table structure shown above, it can be seen that:
- There are 4 (four) fields with each type.
- Primary Key of the student table is studentnumber. See the Key field in the studentnumber field.
- For studentnumber field and name, default can not be empty. Look at the Null and Default columns in the studentnumber and name fields
- For dateofbirth and address fields, the defaults may be empty. Look at the Null and Default columns in the dateofbirth and address fields.
Comments
Post a Comment