How to Create Table in MySQL using CMD

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:


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 (;)
Next to see the table of students is really there or not, type the following command:
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.
That's the tutorial of blog blog php programming language about: How to create table in mysql using cmd. Hopefully the tutorial can be useful.

Comments