Implement a basic database

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)

)