Shulou(Shulou.com)05/31 Report--

This article is about how to use SQL to build a relational database, the editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

Using SQL to build a relational database is easier than you think.

Building a database using SQL is easier than most people think. In fact, you don't even need to be an experienced programmer to create a database using SQL. In this article, I will explain how to use MySQL 5.6to create a simple relational database management system (RDMS). Before I begin, I'd like to thank SQL Fiddle, by the way, the tool I use to run scripts. It provides a useful sandbox for testing simple scripts.

In this tutorial, I will build a database that uses the simple schema shown in the entity relationship diagram (ERD) below. The database lists students and the courses they are studying. To keep it simple, I used two entities (that is, tables) with only one relationship and dependency. These two entities are called dbo_students and dbo_courses.

The diversity of the database is one-to-many, because each course can contain many students, but each student can only take one course.

A quick description of terms:

A table is called an entity.

A field is called a property.

A record is called a tuple.

The script used to build the database is called the schema.

Build the architecture

To build the database, use the CREATE TABLE command, and then define the name and data type of each field. The database uses VARCHAR (n) (string) and INT (n) (integer), where n represents the length of the value that can be stored. For example, INT (2) can be 01.

This is the code used to create two tables:

CREATE TABLE dbo_students (student_id INT (2) AUTO_INCREMENT NOT NULL, student_name VARCHAR (50), course_studied INT (2), PRIMARY KEY (student_id)); CREATE TABLE dbo_courses (course_id INT (2) AUTO_INCREMENT NOT NULL, course_name VARCHAR (30), PRIMARY KEY (course_id))

NOT NULL means that the field cannot be empty, and AUTO_INCREMENT means that when a new tuple is added, the ID number is automatically generated, adding 1 to the previously stored ID number to strengthen the complete reference between entities. PRIMARY KEY is the unique identifier attribute for each table. This means that each tuple has its own different identity.

Relationship as a constraint

At present, the two forms exist independently and do not have any connection or relationship. To connect them, you must identify a foreign key. In dbo_students, the foreign key is course_studied and its source is in dbo_courses, meaning that the field is referenced. The specific command in SQL is CONSTRAINT, and another command called ALTER TABLE will be used to add this relationship so that the table can be edited even after the schema has been built.

The following code adds the relationship to the database construction script:

ALTER TABLE dbo_studentsADD CONSTRAINT FK_course_studiedFOREIGN KEY (course_studied) REFERENCES dbo_courses (course_id)

Using the CONSTRAINT command is not actually necessary, but it is a good habit because it means that constraints can be named and make maintenance easier. Now that the database is complete, it's time to add some data.

Add data to the database

INSERT INTO is a command used to directly select which attributes (that is, fields) data to add. First declare the entity name, then declare the attributes, and the following is the data added to the entity to create a tuple. If NOT NULL is specified, this means that the property cannot be left blank. The following code shows how to add a record to a table:

INSERT INTO dbo_courses (course_id,course_name) VALUES; INSERT INTO dbo_courses (course_id,course_name) VALUES; INSERT INTO dbo_courses (course_id,course_name) VALUES; INSERT INTO dbo_students (student_id,student_name,course_studied) VALUES; INSERT INTO dbo_students (student_id,student_name,course_studied) VALUES INSERT INTO dbo_students (student_id,student_name,course_studied) VALUES; INSERT INTO dbo_students (student_id,student_name,course_studied) VALUES (004)

Now that the database schema is complete and the data has been added, it is time to run the query on the database.

Query

The query follows a collection structure that uses the following commands:

SELECT FROM WHERE

To display all records within the dbo_courses entity and display the course code and course name, use *. This is a wildcard that eliminates the need to type all attribute names. It is not recommended in the production database. ) the code for the query here is:

SELECT * FROM dbo_courses

The output queried here shows all tuples in the table, so all available courses can be displayed:

| | course_id | course_name | |-|-| | 1 | Software Engineering | | 2 | Computer Science | | 3 | Computing |

In later articles, I'll use one of three types of joins to explain more complex queries: inner joins, outer joins, and cross joins.

This is the complete script:

CREATE TABLE dbo_students (student_id INT (2) AUTO_INCREMENT NOT NULL, student_name VARCHAR (50), course_studied INT (2), PRIMARY KEY (student_id)); CREATE TABLE dbo_courses (course_id INT (2) AUTO_INCREMENT NOT NULL, course_name VARCHAR (30), PRIMARY KEY (course_id)); ALTER TABLE dbo_studentsADD CONSTRAINT FK_course_studiedFOREIGN KEY (course_studied) REFERENCES dbo_courses (course_id) INSERT INTO dbo_courses (course_id,course_name) VALUES; INSERT INTO dbo_courses (course_id,course_name) VALUES; INSERT INTO dbo_courses (course_id,course_name) VALUES; INSERT INTO dbo_students (student_id,student_name,course_studied) VALUES; INSERT INTO dbo_students (student_id,student_name,course_studied) VALUES INSERT INTO dbo_students (student_id,student_name,course_studied) VALUES; INSERT INTO dbo_students (student_id,student_name,course_studied) VALUES; SELECT * FROM dbo_courses

SQL is not difficult; I think it is simpler than programming, and the language is universal for different database systems.

The above is how to use SQL to build a relational database. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.