SQL Supplements for Junior Programmers - ② (Table Operations)

foreword

Although MySQL is often used in development, most database operations are implemented through ORM mapping, and I have not touched SQL from the bottom.

This article follows the SQL Supplements of Junior Programmers-① (Add, Delete, Modify and Check)
The main contents include: database building, table building, fields, constraints, queries, etc.

In the actual development process, compared with adding, deleting, modifying, and checking, these low-frequency operations such as table creation, table deletion, and constraints are less important, because the database is still adding, deleting, modifying and checking (to be precise, checking) most of the time.

Therefore, the content of this article does not need to be memorized, just understand.

Thanks to the powerful visualization tool Navicat (or PHPMyAdmin), we can use the mouse to complete the table building and convert it into SQL statements.
It can be said that if you can use Navicat, the SQL statement does not need to be remembered (because it can be generated by software).

1. Play Navicat

This software has 30 trial versions, and you can also find a harmonious version, which can be searched by yourself.

After opening the software to connect to the MySQL service, all databases of the current MySQL will be displayed on the left:

Create a new database (graphical)

Right-click on any database on the left -> New Database, and enter the information.

(I need to learn about popular science here, why use utf8mb4?
Because MySQL's built-in utf8 is a castrated version, it is represented by 3 characters and the real UTF-8 is 1~4 variable characters. In other words, MySQL's utf8 cannot represent all UTF-8 characters and utf8mb4 is the real 4-character UTF-8
This is a pit of MySQL)

You can see that the table test1 has been displayed,
How to do this with the command line?
First of all, you need to understand what is a "query"

Query

A group of SQL statements entered in the SQL terminal that can perform a specific function is called a query.
Note that the Query here is not a query (SELECT) of additions, deletions, and changes, nor is it limited to queries.

It can be understood that the execution process of all SQL statements is a query process.
That is, rudely simplified, query == execute SQL statement
Query includes not only CRUD, but also various library operations and table operations.

Next, create a database named test2 using SQL in the query.

New database (command line)

First, create a new query in Navicat (that is, create a new function to enter an SQL statement)
Execute after entering SQL code

 // 建立数据库
CREATE DATABASE 数据库名 

// 示例：建立名为test2的数据库
CREATE DATABASE test2

After the refresh, the new database will appear.

After double-clicking the newly created library, the icon turns green, and the content behind the article can be manipulated in the Query of this database.

Similarly, the statement to delete the database is:

 // 删除数据库
DROP DATABASE 数据库名

// 删除名为test2的数据库
DROP DATABASE test2

Demonstrate Navicat to generate SQL statements

This is the most amazing thing about software.

First create a new table, and then edit the fields in the table arbitrarily.

After editing, you do not need to save, click SQL Preview at this time.

At this point, you can find that the software has automatically generated SQL statements for us:

This is very helpful for beginners to learn SQL, and it is helpful to quickly establish the association between certain functions and SQL codes in the brain.

At this point, if you understand and use Navicat proficiently, you don't need to read the following content (not).

2. Common table operations

CREATE

Now we can take out the SQL code generated by Navicat just now and take a look:

 // 软件生成的SQL语句

CREATE TABLE `test2`.`无标题`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NULL,
  `sex` int(1) NULL,
  `phone` varchar(255) NULL,
  PRIMARY KEY (`id`)
);

We can conclude that the way to create a table is:

 // 创建数据表

CREATE TABLE `数据库名`.`数据表名`  (
    `字段1` 字段1的属性,
    `字段2` 字段2的属性,
    ......
    `字段n` 字段n的属性,
)

Note that the single quote-like symbol here is not a single quote ' , but the key above TAB.

Mnemonic:

• The CREATE command applies to all new functions, such as new databases, data tables, indexes, etc.
• The operation object of the library is DATABASE, and the operation object of the table is TABLE
• When creating a new data table, you need to write the fields and attributes of the table, but you do not need to create a new database.

Create a data table named table1 in this way, and then prepare to see how to delete it.

DROP and TRUNCATE

The writing method of SQL has strong regularity, we just want to find out this regularity for understanding and memory.

 // 删除某个数据表
DETELE TABLE 数据表名

// 举例：删除table1数据表
DETELE TABLE table1

Mnemonic:

• The same as CREATE usage, DROP applies to all functions that represent deletion, such as deleting databases, tables, indexes
• The operation object of the library is DATABASE, and the target operation object is TABLE
• For databases and data tables, the deleted statement structure is DELETE + operation object + object name

There is a special operation for the table, which can be commonly understood as emptying the table, that is, only deleting the data but not the structure:

 // 情况某个数据表的数据，但不改变其结构
TRUNCATE TABLE table_name
// 举例：清空table1
TRUNCATE TABLE table1

Mnemonic:

• Clear data can only be used on data tables
• The structure of the case statement and the delete statement are only different from the instructions, and the others are the same

summary

Here, the establishment and deletion of the database are analogous, and the establishment, deletion and clearing of the data table are summarized.

3. Field operation

Preconditions: Create a table named table2 and set some fields arbitrarily, similar to the following:

The software automatically generates the following SQL statements. If readers are too lazy to manually add fields, they can directly execute them to obtain the same environment as this article.

 // 软件生成的SQL语句

CREATE TABLE `test2`.`无标题`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `name` varchar(255) NULL COMMENT '姓名',
  `age` int(11) NULL COMMENT '年龄',
  `number` varchar(11) NULL COMMENT '学号',
  PRIMARY KEY (`id`)
);

So we can summarize the way to add a field:

 // 最精简的字段写法
`字段名` 类型(长度) 是否为空约束,

// 带注释的字段写法
`字段名` 类型(长度) 是否为空约束 COMMENT '注释内容',

Keep your eyes open here, field names are symbols above TAB, and comments are single quotes!

Now that you know how to write fields, how many field types are there?
Actually a lot, only a small part in the picture.

But the most commonly used are still int, varchar, date, timestamp,
As for other types, when you need to use them, add one with navicat first, and then look at the generated SQL.
This is called teaching a man to fish (assurance).

constraint

"Master, you translate for the translator, what is the constraint?"
"Constraints are constraints."

The essence of constraints is to limit the range of field values.
If there are no constraints, a field can take any value in its type

But sometimes the fields are not allowed to appear NULL or duplicate values, etc., then constraints are required.

Common constraints include:

• NOT NULL does not allow fields to be empty
• UNIQUE does not allow duplicate values (such as student IDs)
• DEFAULT default value
• primary key constraint
• foreign key constraints

Non-null, unique, default values are well understood.
Click the design table in Navicat, add two fields, set non-null and default values respectively:

The following SQL statements are automatically generated:

 // 自动生成的SQL语句

ALTER TABLE `test2`.`table2` 
ADD COLUMN `test_null` varchar(255) NOT NULL AFTER `number`,
ADD COLUMN `test_default` varchar(255) NULL DEFAULT 123123123 AFTER `test_null`;

This can lead to the writing of these constraints:

• Use NOT NULL for non-null constraints, otherwise write NULL
• The default value uses DEFAULT 默认值
• Unique value Navicat has no option to set directly, actually use UNIQUE

Then there is a question: is there any order requirement when multiple constraints are written at the same time?

Let's do a test, set non-null and default values at the same time, the generated statement, you can see that null is in front and default is behind:

 // 自动生成的SQL语句

ALTER TABLE `test2`.`table2` 
ADD COLUMN `test_default` varchar(255) NOT NULL DEFAULT 123123123 AFTER `name`;

Next, switch the position and execute it in Query:

 // 调换约束的顺序

ALTER TABLE `test2`.`table2` 
ADD COLUMN `test_default` varchar(255) DEFAULT 123123123 NOT NULL AFTER `name`;

It can be done, SQL developers aren't that stupid to think about it.

summary

We can adjust the writing of the general fields mentioned above:

 // 一般情况下字段写法，其中约束可以改变顺序
`字段名` 类型(长度) 约束1 约数2 ... COMMENT '备注内容',

Next, let's talk about the very important primary and foreign keys

Primary key (PRIMARY KEY)

For a table, the primary key is the unique identification of a record (a row of data) in this table.
That is, from a business perspective, the backend can obtain a uniquely determined object through the primary key.

SQL stipulates that the primary key must have the following requirements:

• The primary key must be a unique value (no need to write out UNIQUE , after declaring the primary key, it comes UNIQUE effect)
• The primary key must be non-null (need to write NOT NULL on the field)

In addition, in the actual development process, there are usually the following conventions:

• The column name of the primary key is id
• The primary key is of type int
• The primary key is self-incrementing (need to write out AUTO_INCREMENT )

We go back to the SQL statement generated when the table was first built, and we can summarize the writing of the primary key:

 // 软件生成的SQL语句

CREATE TABLE `test2`.`无标题`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NULL,
  `sex` int(1) NULL,
  `phone` varchar(255) NULL,
  PRIMARY KEY (`id`)
);

 // 归纳主键定义的写法

CREATE TABLE `数据库名`.`数据表名`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  ......
  PRIMARY KEY (`id`)
);

So, the gist is as follows (mnemonic):

• The field name is id, and you need to add non-null and auto-increment constraints
• Declare the primary key at the end of all fields to be id
• Pay attention to the format of the quotation marks

Extension: Union Primary Key

Generally speaking, the primary key has only one column, such as ID, then this column is unique,
For example, there is already a student with an ID of 666 in the data table. Inserting a student with an ID of 666 or changing the ID of another student to 666 will result in an error. This is uniqueness.

The combined primary key involves more than two fields, and an error will be reported only if the data on these two columns are the same.
For example, set the joint primary key name and number, that is to say, only the name and student ID are repeated at the same time will be judged as duplicate data.
Suppose the database has a student whose name is Zhang San and the student number is 123456
At this time, if you insert a new student whose name is Zhang San and student number 777777, you can insert and then insert a student whose name is Li Si and student number 123456, but if you insert the name of Zhang San and student number 123456, the database will Error: Duplicate primary key.

foreign key

The foreign key only appears in multi-table query, it is essentially the only index in the associated data table stored in the current data table, in most cases it is the primary key.
To simplify, the foreign key is stored in the current table, the primary key of other tables.

If it is said that the purpose of the primary key is to accurately obtain unique objects by ID in a single table query,
Then the foreign key is to accurately obtain the unique object associated with it in the multi-table query.

For example, students and classes are many-to-one, and each student belongs to a class, then add a class ID field to the student table, which is the foreign key.
When querying a student, you can get the class it belongs to through the student's 班级ID field.

The next step is to study the writing method of foreign keys, first create a class table"

 // 创建班级
CREATE TABLE `klass`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NULL,
  PRIMARY KEY (`id`)
);

Then create a student table and add a foreign key klass_id pointing to the klass id field of the table --- 0bace0e05fc41c0a25cce8c0b45f5a7d ---.
First create the field normally:

Then on the foreign key page, add a foreign key:

When deleting and updating, there is a drop-down menu with four options, namely cascade, no operation, limit, and empty.
Briefly explain:
Cascading refers to deleting associated students when a class is deleted.
No operation will cause the foreign key of students to remain unchanged when the class is deleted. At this time, an error will be reported when checking the class by students. Restriction means that when there are students in the class, it cannot be deleted. Leave blank means that when the class is deleted, the original students in the class The klass_id field was changed to NULL
Now just pick one at will and see the SQL:

 // 软件生成的SQL语句

CREATE TABLE `test2`.`无标题`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NULL,
  `number` int(11) NULL,
  `sex` int(1) NULL,
  `klass_id` int(11) NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `klass_id` FOREIGN KEY (`klass_id`) REFERENCES `test2`.`klass` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
);

 // 外键的写法
CONSTRAINT `klass_id` FOREIGN KEY (`klass_id`) REFERENCES `test2`.`klass` (`id`) ON DELETE CASCADE ON UPDATE CASCADE

// 归纳
CONSTRAINT `外键名` FOREIGN KEY (`本表中的字段`) REFERENCES `数据库名`.`关联表名` (`关联表字段`) ON DELETE 删除操作模式 ON UPDATE 更新操作模式

The commonly used functions of foreign key constraints in actual development are:
When using restricted mode, a record cannot be deleted when it is associated with a foreign key.
For example, when there are students in the class, the class cannot be deleted.

summary

Primary key constraints and foreign key constraints are super-frequently used features, and it's important to understand what they mean.

4. Summary

Unlike the previous article, the content of this article is almost completely unnecessary to memorize. First, it is because of low-frequency operations. Second, because there are already very intelligent generation tools. We are not DBAs, and we will not deal with table creation and deletion every day.
The author thinks that it is more important to describe how to generate SQL. After mastering this operation, no matter what statement you have never seen before, you can face it flexibly.