MYSQLi Create Table

In the previous section, we learned how to create a database. After the database is created successfully, we need to put things into the data, and these things are placed in different tables in the database. We learn to create a mysql table in this section.

Create a table

A data table has a unique name and consists of rows and columns.


The CREATE TABLE statement is used to create a MySQL table.


Category       Detailed explanation
Basic grammarCreate table table name (field name 1 field type, .... field name n field type n);
ExampleCreate table user(username varchar(20),password varchar(32));
Example descriptionCreate a table named user, the first field is username, the field type of the table is varchar length is 32 lengths. The second field is password, the type is also varchar, and the length is also 32 lengths.

 Instance

We will create a table called "MyGuests" with 5 columns: "id", "firstname", "lastname", "email" and "reg_date" creation code

CREATE TABLE MyGuests (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP
)


Note on the above table:

The data type specifies what type of data the column can store. Please refer to our data type reference manual for complete data types.

After setting the data type, you can specify properties for other options without columns:


· NOT NULL - Each row must contain a value (not null), and a null value is not allowed.

· DEFAULT value - set the default value

· UNSIGNED - use unsigned numeric types, 0 and positive numbers

· AUTO INCREMENT - Sets the value of the MySQL field to grow automatically each time a new record is added 1

· PRIMARY KEY - Sets the unique identifier for each record in the data table. Usually the column's PRIMARY KEY is set to the ID value and is used with AUTO_INCREMENT.

Each table should have a primary key (this column is the "id" column), and the primary key must contain a unique value.


Instance

The following example shows how to create a table in PHP:


 Header("Content-type:text/html;charset=utf-8"); //Set the encoding
 $servername = "localhost";
$username = "root";
 $password = "root";
 $dbname = "test";
 
 // Create a connection
 $conn = mysqli_connect($servername, $username, $password, $dbname);
 / / Check the connection
 If (!$conn) {
     Die("Connection failed: " . mysqli_connect_error());
 }
  / / Use sql to create a data table
 $sql = "CREATE TABLE MyGuests (
 Id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 Firstname VARCHAR(30) NOT NULL,
 Lastname VARCHAR(30) NOT NULL,
 Email VARCHAR(50),
 Reg_date TIMESTAMP
 )";
 If (mysqli_query($conn, $sql)) {
     Echo "Data table MyGuests created successfully";
 } else {
     Echo "Create data table error: " . mysqli_error($conn);
 }
 Mysqli_close($conn);
 


The above example is that we created a table named MyGuests in a database named "test" with 5 columns, "id", "firstname", "lastname", "email" and "reg_date". ":

 

Introduction to common tools

MySQL can be managed using officially provided tools and third-party tools, so that we don't have to remember some complicated SQL statements to complete the memory. For example: permissions, building tables, backups, etc. Direct use of visual tools


It is more conducive to improving work efficiency.

Commonly used tools are:

phpMyAdmin - (Chinese, recommended) 

Navicat - (Chinese, recommended) 

Mysql workbench - (English, officially produced, recommended when designing ER diagrams)

Heidisql - I personally use it 


phpMyAdmin


phpMyAdmin is a PHP-based, MySQL database management tool that is architect-ed on a web site in a Web-Based manner, allowing administrators to manage MySQL databases using a web interface. By this web interface can be

A better way to enter complex SQL syntax for a simple way, especially to handle the import and export of large amounts of data is more convenient. One of the bigger advantages is that phpMyAdmin is on the web server like other PHP programs.

Execution, but you can use the HTML pages generated by these programs from anywhere, that is, remotely manage the MySQL database, and easily create, modify, and delete databases and data tables. Can also be established by phpMyAdmin

The php syntax is used to facilitate the correctness of the sql syntax required to write a web page.

Your server runs directly into the php run-time environment. Download the installation package and unzip the access address to get started.

Enter http://localhost/phpmyadmin/ on the web page to access, enter your username and password, and enter

We can do some related database operations in it, which can greatly reduce the time we write sql statements. Let's explore it yourself.

The other two tools you go to Baidu to search, download and install, I believe you will operate



Last Updated: 2018-07-06 12:32:44 By akshay

SHARE THIS

share_facebook share_twitter share_google_plus

Please Comment Here :