Implement a basic database

Source: Internet
Author: User
Tags mathematical functions

About a year ago, in order to gain a deep understanding of the internal principles and implementation of the database, I decided to write a prototype program for the database system. This operation lasted for one year on and off, nearly lines of C code are written. Now, apart from transactions and concurrency (only concurrency at the storage engine level), other functions are basically implemented. The database is the underlying software, which is difficult to implement. In order to write this program (now called XSQL), I have read a lot of books on the Internal principles of open-source databases, summarizes the structure of mainstream databases, and then writes XSQL based on its principles. Next, I plan to write the problems, solutions, and technologies involved in implementing this program into a special topic. This will allow more students to have a deeper understanding of the internal principles of the database system, it also allows others to discover the shortcomings in the implementation process and point out the optimization methods.

 

Data Type in XSQL (not implemented in green currently ):

CHAR

Fixed Length string

VARCHAR

Variable-length string

FLOAT

Single precision floating point type

DOUBLE

Dual-precision floating point

INT

Integer

SMALLINT

Short integer

BIGINT

Long Integer

DATETIME

Timestamp

TEXT

Text

FILE

File


 

Functions supported by XSQL:

String Functions

LENGTH (exp)

Returns the length of the exp string.

SUBSTR (exp1, exp2)

Returns the subscript of the string exp2 in exp1. If it does not exist,-1 is returned.

Time Functions

GETDATE ()

Returns the current system time string.

TODATE (exp)

Convert timestamp exp into a time string

TOTIMESTAMP (exp)

Convert the time string exp into a timestamp

Mathematical functions

POWER (exp1, exp2)

Returns the exp2 power of exp1.

SIN (exp)

Returns the sine of exp.

COS (exp)

Returns the cosine of exp.

SQRT (exp)

Returns the square root of exp.

ABS (exp)

Returns the absolute value of exp.

Aggregate functions

MAX (exp)

Returns the maximum exp.

MIN (exp)

Returns the smallest exp.

SUM (exp)

Returns the sum of exp values of all rows.

COUNT (exp)

Number of returned rows

AVG (exp)

Returns the exp average of all rows.

 

Execute the following command in XSQL to create a Student Course Selection relationship table:

Create Table student
(
S # int auto_increment not null,
Name char (20 ),
Age smallint check (age between 15 and 20 ),
Sex char (2) Check (sex in ('male', 'female ')),
Address varchar (50 ),
Reg_time datetime,
Primary key (s #)
)

Insert into student (s #, name, age, sex, address, reg_time)
Values (null, 'wang', 20, 'male', 'harbin ', totimestamp (getdate ()))

Insert into student (s #, name, age, sex, address, reg_time)
VALUES (NULL, 'lil', 19, 'male', 'harbin ', totimestamp (getdate ()))

Insert into student (s #, name, age, sex, address, reg_time)
VALUES (NULL, 'chen', 17, 'male', 'harbin ', totimestamp (getdate ()))

Insert into student (s #, name, age, sex, address, reg_time)
VALUES (NULL, 'wu', 18, 'male', 'harbin ', totimestamp (getdate ()))

Insert into student (s #, name, age, sex, address, reg_time)
VALUES (NULL, 'luo', 20, 'female ', 'harbin', totimestamp (getdate ()))

Insert into student (s #, name, age, sex, address, reg_time)
VALUES (NULL, 'zhou', 20, 'female ', 'harbin', totimestamp (getdate ()))

Create table course
(
C # INT AUTO_INCREMENT not null,
Cname CHAR (15 ),
Teacher CHAR (15 ),
Primary key (c #)
)

Insert into course (C #, cname, teacher) values (null, 'database', 'lil ')
Insert into course (C #, cname, teacher) values (null, 'mat', 'M ')
Insert into course (C #, cname, teacher) values (null, 'chemistry ', 'zhou ')
Insert into course (C #, cname, teacher) values (null, 'Physical ', 'shi ')
Insert into course (C #, cname, teacher) values (null, 'OS', 'wen ')

Create Table SC
(
S # int not null,
C # int not null,
Grade smallint check (grade between 0 and 100 ),
Primary key (s #, C #),
Foreign key (s #) References student (s #),
Foreign key (C #) References course (C #)
)

Insert into SC (s #, C #, grade) values (1, 1, 55)
Insert into SC (s #, C #, grade) values (1, 2, 78)
Insert into SC (s #, C #, grade) values (1, 3, 85)
Insert into SC (s #, C #, grade) values (1, 4, 91)
Insert into SC (s #, C #, grade) values (1, 5, 69)
Insert into SC (s #, C #, grade) values (2, 1, 85)
Insert into SC (s #, C #, grade) values (3,1, 90)
Insert into SC (s #, C #, grade) values (3,2, 86)
Insert into SC (s #, C #, grade) values (3,3, 95)
Insert into SC (s #, C #, grade) values (4,1, 71)
Insert into SC (s #, C #, grade) values (4,4, 63)
Insert into SC (s #, C #, grade) values (5, 1, 70)
Insert into SC (s #, C #, grade) values (5, 2, 65)
Insert into SC (s #, C #, grade) values (5, 3, 80)
Insert into SC (s #, C #, grade) values (5, 5, 65)
Insert into SC (s #, C #, grade) values (6, 1, 78)
Insert into SC (s #, C #, grade) values (6, 3, 90)

 

Query Demo:

-- List all rows in the student table

Select s #, name, age, sex, address, todate (reg_time)

From student

 

-- List all rows in the course table

Select *

FROM course

 

-- Retrieve the student ID and name of course 2

SELECT s #, name

FROM student

Where exists (

SELECT *

FROM SC

WHERE c # = 2 AND s # = student. s #

)

 

-- Retrieve the student ID, name, and admission time for mathematics learning.

SELECT name, student. s #, todate (reg_time) as 'admission time'

FROM student, SC, course

WHERE cname = 'mate' AND student. s # = SC. s # AND SC. c # = course. c #

 

-- Retrieve the names of all students learning all courses

SELECT name

FROM student

Where not exists (

SELECT *

FROM course

Where not exists (

SELECT *

FROM SC

WHERE s # = student. s # ANDc # = course. c #

)

)

 

-- Retrieve student information whose names are less than or equal to the average length

SELECT *

FROM student

Where length (name) <= (select avg (LENGTH (name) FROM student)

 

-- Retrieve information about students whose age is greater than or equal to the average age and whose maximum score is less than 10 or less than the average age and whose difference is not greater than 15

SELECT *

FROM student

WHERE

(

10> (select max (grade)-MIN (grade)

FROM SC

WHERE s # = student. s #) AND age> = (select avg (age) FROM student)

) OR (

15> (select max (grade)-MIN (grade)

FROM SC

WHERE s # = student. s #) AND age <(select avg (age) FROM student)

)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.