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)
)