SQL Supplements for Junior Programmers (Add, Delete, Modify and Check)

foreword

Although MySQL is often used in development, most database operations are implemented through ORM (SpringDataJPA), and I have not touched SQL from the bottom.
Coupled with the author's non-professional status, I have almost no understanding of native SQL statements.
This article is written for myself, a note about native SQL.
Learning should master the boundaries. Since this part is not the core of programming, there is no need to memorize the code, but only need to understand and paste the modification.

Small Tip:

How do beginners debug SQL code? The exercises can be visualized with Navicat or PHPMyAdmin.
First use the graphical interface to build a few tables, and then practice operating the data table with SQL statements.
This avoids the difficulty of using the pure command line in the early stage.

1. CRUD

It is written in the REST specification: GET is used for query, POST is used for adding, UPDATE is used for modification, and DELETE is used for deletion. CRUD is the most common way to move bricks, so what is their SQL code?

Let's take a single table as an example (that is, without a foreign key).

The commands for single-table operations have strong regularities. Includes the following elements:

• instruction
• field
• target table
• parameter
• semicolon

The instruction tells SQL what to do, is it increment? Or modify? or delete?
Fields tell SQL which columns to operate on, such as finding out the names and ages of all students.
The target table tells SQL which table to operate on, such as deleting a class in the class table?

Check - SELECT

It is written as:

 SELECT 字段1,字段2,... FORM 表名 WHERE 条件;

Example:

 // 从学生表查找ID为1的姓名和年龄
SELECT name, age FORM student where id=1;

(Auxiliary memory:)
The command at this time is SELECT
The target table uses FORM 表名 to determine the parameters mainly to increase the filtering conditions and accurately find the desired data

Paging query - LIMIT parameter (for MySQL)

It is written as:

 SELECT 字段1,字段2,... FORM 表名 WHERE 条件 LIMIT 条数;

Example:

 // 从学生表查找10条性别是女的学生的所有字段
SELECT * FORM student where sex=1 LIMIT 10;

(Auxiliary memory:)
LIMIT does not belong to the syntax of native SQL and has different implementations for different distributions.
Among them, MySQL uses the LIMIT statement, and the function is to take out only ten.
This query method is often used for paging queries on the Web.

Add - INSERT

Writing:

 INSERT INTO 表名 (字段1,字段2,...) VALUES (值1,值2,...);

Example:

 // 向学生表插入姓名为张三、性别为女、年龄20岁的学生
INSERT INTO student (name, sex, age) VALUES (zhangsan, 1, 20);

(Auxiliary memory:)
For an increment operation, the instruction becomes an INSERT.
The increment operation must give not only the field but also the value, so VALUES is required.
There are some changes in the prefix of the action table at this time, imagine:
When executing a query, the data goes from the database to the user, so use FORM;
But when inserting, data goes from user to database, so INTO.
Since the insert will directly insert the last piece of data, there is no WHERE parameter.

Modify - UPDATE

Writing:

 UPDATE 表名 SET 字段1=值1,字段2=值2,字段3=值3,... WHERE 条件;

Example:

 // 在学生表中找到id为1的学生，然后更新姓名=张三、年龄=20、性别=男
UPDATE student SET name=zhangsan,age=20,sex=0,... WHERE id=1;

(Auxiliary memory:)
The command at this time is UPDATE.
Since the field needs to be modified, SET needs to be used
The modification is only for one or several pieces of data, and the WHERE condition must be used to limit it, otherwise the data of the entire table will be modified!

delete - DELETE

 DETELE FORM 表名 WHERE 条件;

(Auxiliary memory:)
The command to delete is DELETE
Since the data is taken from the table, it is FROM
The deletion is only for one or several pieces of data, and the WHERE condition must be used to limit it, otherwise the data of the entire table will be deleted!

2. WHERE parameter

WHERE defines query conditions in SQL, but there are several points to note:

WHERE collocation operator

regular operator

• = field equals value
• > field is greater than value
• < field is less than value
• >= field is greater than or equal to value
• <= field is less than or equal to the value

special operator

• LIKE fuzzy query (field contains value)
• IN belongs to (the value of the field belongs to the given set)
• BETWEEN Interval (the value of the field belongs to the given interval)

Fuzzy query means that the field to be queried does not need to be strictly equal to a string, but only needs to contain a string.
For example, using LIKE '%zhang%' as a condition, 'zhangsan' includes 'zhang', it will be detected.
There are three types of fuzzy queries:

• The prefix matches must start with the same: zhangsan%
• The infix match can be the same in the middle: %zhangsan%
• Suffix matching ends must be the same: %zhangsan

WHERE logical operations

Logic terms (AND, OR, NOT) are definitely used in programming languages, and SQL also has AND and OR.

Logical operators connect two conditions.
The AND operation uses AND, which means that both conditions are met to be valid.
The OR operation uses OR, which means that either condition is valid.
In actual usage scenarios, multiple logical characters can be used consecutively, and parentheses are used to define the priority.

Writing:

 SELECT 字段 FROM 表名 WHERE 条件1 AND/OR 条件2;

Example:

 // 在学生表取出年龄大于18岁的女生的所有字段
SELECT * FROM student WHERE sex=1 AND age > 18;

3. Multi-table query (join/JOIN)

Common entity relationships in software development include:

• One-to-one (0…1 : 0…1)
• One-to-many/many-to-one (0…1 : 0…n)
• Many-to-many (0…n : 0…n)

Among them, one-to-one, one-to-many, and many-to-one use foreign key associations, while many-to-many use intermediate table associations.

For the three cases of foreign key association, when querying, it is often necessary to find out the associated table2 through table1.

At this time, you can use JOIN to combine the two tables into one table.

An association table is like two sets, table one and table two,
Their intersection is the part of the records in the two tables that are associated with foreign keys.

A class has multiple students, and each student belongs to a class. So students and classes are many-to-one.
If you want to display the information of their class when querying students:

 SELECT student.id, student.name, klass.name, klass.count  
FROM Students  
INNER JOIN Klass  
ON Klass.id=Student.klass_id;

Although the rookie tutorial is very clear, we can simplify it a bit here:

• We define the table using the FORM field as the left table
• Define the table using the JOIN field as the right table

The same can also be done for the four types of connections:

• INNER JOIN general connection: only check the data associated with the foreign key, that is, take the intersection
• LEFT JOIN left join: the left table is fully checked, the right table is random, and the empty data is set to NULL, that is, set A is taken
• RIGHT JOIN right join: the right table is fully checked, the left table is random, and the empty data is set to NULL, that is, set B is taken
• FULL JOIN full join: both left and right tables are checked, and the empty data on both sides are set to NULL, that is, the union is taken.

4. Summary

With the powerful ORM (entity relationship mapping) of the programming language, the operation of adding, deleting, modifying and querying almost no longer needs to be done by handwritten SQL, but we cannot rely on ORM and forget how to write native SQL.

This is also a required course. At least you can understand the basic writing method of adding, deleting, modifying and checking, so you can know how to work around some special needs, and you can successfully debug by viewing the generated SQL statements when JPA has problems.

The content mentioned in the article basically covers the needs of daily additions, deletions, modification and checking, and how to define primary keys and foreign keys, how to create and delete tables, how to add and edit fields, these infrequent operations, will be discussed later.

References:

Rookie Tutorial: https://www.runoob.com/sql/sql-tutorial.html