YouTip

Creating a MySQL data table requires the following information: * Table name * Table field names * Data type definition for each table field ### Syntax The following is the general SQL syntax for creating a MySQL data table: CREATE TABLE table_name ( column1 datatype, column2 datatype, ...); **Parameter Description:** * `table_name` is the name of the table you want to create. * `column1`, `column2`, ... are the column names in the table. * `datatype` is the data type for each column. Here is a specific example, creating a user table **users**: ## Example CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50)NOT NULL, email VARCHAR(100)NOT NULL, birthdate DATE, is_active BOOLEAN DEFAULT TRUE ); Example Analysis: * `id`: User ID, integer type, auto-increment, serves as the primary key. * `username`: Username, variable-length string, cannot be NULL. * `email`: User email, variable-length string, cannot be NULL. * `birthdate`: User's birth date, date type. * `is_active`: Whether the user is activated, boolean type, default value is true. The above is just a simple example, using some common data types including INT, VARCHAR, DATE, BOOLEAN. You can choose different data types based on actual needs. The AUTO_INCREMENT keyword is used to create an auto-incrementing column, and PRIMARY KEY is used to define the primary key. If you wish to specify the data engine, character set, and collation when creating the table, you can use the **CHARACTER SET** and **COLLATE** clauses: ## Example CREATE TABLE mytable ( id INT PRIMARY KEY, name VARCHAR(50) )CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; The above code creates a table using the utf8mb4 character set and utf8mb4_general_ci collation. In the following example, we will create a data table tutorial_tbl in the database: ## Example CREATE TABLE IF NOT EXISTS`tutorial_tbl`( `tutorial_id`INT UNSIGNED AUTO_INCREMENT, `tutorial_title`VARCHAR(100)NOT NULL, `tutorial_author`VARCHAR(40)NOT NULL, `submission_date`DATE, PRIMARY KEY(`tutorial_id`) )ENGINE=InnoDB DEFAULT CHARSET=utf8; Example Analysis: * If you do not want a field to be **NULL**, you can set the field's attribute to **NOT NULL**, as seen in the tutorial_title and tutorial_author fields in the example above. If you input empty data for this field when operating the database, an error will occur. * **AUTO_INCREMENT** defines the column as an auto-incrementing attribute, generally used for primary keys, where the value automatically increments by 1. * **PRIMARY KEY** keyword is used to define a column as the primary key. You can use multiple columns to define the primary key, separated by commas , . * **ENGINE** sets the storage engine, **CHARSET** sets the character encoding. * * * ## Creating a Table via Command Prompt You can easily create a MySQL data table through the mysql> command window. You can use the SQL statement **CREATE TABLE** to create a data table. ### Example The following is an example of creating the data table tutorial_tbl: ## Example root@host# mysql -u root -p Enter password: ******* mysql>USE ; DATABASE changed mysql>CREATE TABLE tutorial_tbl( -> tutorial_id INT NOT NULL AUTO_INCREMENT, -> tutorial_title VARCHAR(100)NOT NULL, -> tutorial_author VARCHAR(40)NOT NULL, -> submission_date DATE, ->PRIMARY KEY( tutorial_id ) ->)ENGINE=InnoDB DEFAULT CHARSET=utf8; Query OK,0 ROWS affected (0.16 sec) mysql> **Note:** The MySQL command terminator is a semicolon ; . **Note:** -> is a line break indicator, do not copy it. ## Creating a Data Table Using PHP Script You can use PHP's mysqli_query() function to create a data table in an existing database. This function takes two parameters and returns TRUE on successful execution, otherwise FALSE. ### Syntax mysqli_query(connection,query,resultmode); | Parameter | Description | | --- | --- | | _connection_ | Required. Specifies the MySQL connection to use. | | _query_ | Required. Specifies the query string. | | _resultmode_ | Optional. A constant. Can be any of the following values: * MYSQLI_USE_RESULT (Use this if you need to retrieve large amounts of data) * MYSQLI_STORE_RESULT (Default) | ### Example The following example uses a PHP script to create a data table: ## Create Data Table <?php$dbhost = 'localhost'; $dbuser = 'root'; $dbpass = '123456'; $conn = mysqli_connect($dbhost, $dbuser, $dbpass); if(! $conn){die('Connection failed: ' . mysqli_error($conn)); }echo'Connection successful
'; $sql = "CREATE TABLE tutorial_tbl( ". "tutorial_id INT NOT NULL AUTO_INCREMENT, ". "tutorial_title VARCHAR(100) NOT NULL, ". "tutorial_author VARCHAR(40) NOT NULL, ". "submission_date DATE, ". "PRIMARY KEY ( tutorial_id ))ENGINE=InnoDB DEFAULT CHARSET=utf8; "; mysqli_select_db($conn, ''); $retval = mysqli_query($conn, $sql); if(! $retval){die('Data table creation failed: ' . mysqli_error($conn)); }echo"Data table created successfullyn"; mysqli_close($conn); ?> After successful execution, you can view the table structure via the command line: !(#)