- Building a Web Application with PHP and MariaDB:A Reference Guide
- Sai Srinivas Sriparasa
- 542字
- 2021-07-16 11:36:54
The students table
Let us take a look at the following fields in the table and what work they perform:

Note
It is advised to use a character datatype for fields such as zip codes or SSNs. Though the data is going to be a number, integer datatypes are notorious for removing preceding zeroes, so if there is a zip code that starts with a zero, such as 06909
, of an integer datatype, the zip code would end up in the column as 6909
.
Now let us convert this table structure into executable SQL, to create our table, we will be using the CREATE
DDL command, followed by TABLE
and then append it with the table structure. In SQL, the column description is done by mentioning the column name first and then adding the datatype of the column. The STUDENTS
table has multiple columns, and the column information has to be separated by a comma (,
).

Now that the query has been executed, the students table has been created. To verify if the students table has been successfully built, and to view a list of existing tables that are in the current database, we can use the SHOW
utility command and append that with TABLES
:

We have successfully used the show tables;
command SQL statement to retrieve a list of existing tables, and have verified that our students
table exists in our course_registry
database. Now, let us verify if our students
table has the same table structure as we originally intended it to have. We will use the DESCRIBE
utility command followed by the table name to understand the table structure:

Now let us move on to the courses
table, this table will house all the available courses for which a student can register. The courses table will contain a unique identifier for the course (course_id
), the name of the course (course_name
), and a brief description of the course (course_description
).
The courses table
Let us now look at the fields and the type of values they are storing:

Now let us convert this table structure into executable SQL to create our courses
table:

Now that the query has been executed, let us run the SHOW TABLES
command to verify if the courses
table has been created:

The output from the SHOW TABLES
command returns the list of current tables, and the courses
table is one of them. Now that we have built the students
table and the courses
table, let us build the bridge
table that would hold the association between the two tables. This table would contain the data about the students who were enrolled to a particular course.
The students_courses table
Let us now look at the fields in this table and their respective values:

Now, let us convert this table structure into executable SQL, to create our courses
table using the following command:

Now that the query has been executed, let us run the SHOW TABLES
command to verify if the courses
table has been created:

The output from the SHOW TABLES
command returns the list of current tables, and the students_courses
table is one of them.
- iOS面試一戰到底
- Boost C++ Application Development Cookbook(Second Edition)
- Python GUI Programming Cookbook
- 低代碼平臺開發實踐:基于React
- SQL Server與JSP動態網站開發
- Unity UI Cookbook
- Tableau 10 Bootcamp
- Java面向對象程序設計
- Service Mesh實戰:基于Linkerd和Kubernetes的微服務實踐
- 現代C++編程實戰:132個核心技巧示例(原書第2版)
- 從零學Java設計模式
- PostgreSQL Developer's Guide
- 零基礎學編程系列(全5冊)
- Mastering Node.js
- Learning jqPlot