Github link

1. Insert usage - how to insert data?

Syntax : insert into table name ( column1 name , column2 name , column3 name ...) values ​​( column1 value , column2 value , column3 value ...)

 insert into <table name>(<column1 name>, <column2 name>, <column3 name>...) values ​​(<column1 value>, <column2 value>, <column3 value> ...)

implement

• Let's quickly create a database named demo, then use this database, and then create a table (this part is the content mentioned in the previous note (4), I won't explain it here)

 mysql> create database demo;
Query OK, 1 row affected (0.00 sec)
​
mysql> use demo;
Database changed
mysql> CREATE table student(
-> student_id INT,
-> name VARCHAR(40),
-> gender ENUM("M", "F"),
-> admission_date DATE
-> );
Query OK, 0 rows affected (0.04 sec)

• Then we insert the information like the chart into our table

 insert into student(student_id, name, gender, admission_date) values ​​(1, "Jack", "M", "2021-08-21" );

• How to insert more data into our table, such as the chart

Method 1: Keep repeating the above syntax method

 mysql> insert into student(student_id, name, gender, admission_date) values ​​(1, "Jack", "M", "2021-08-21" );
Query OK, 1 row affected (0.00 sec)
​
mysql> insert into student(student_id, name, gender, admission_date) values ​​(2, "Tom", "M", "2020-08-21" );
Query OK, 1 row affected (0.00 sec)
​
mysql> insert into student(student_id, name, gender, admission_date) values ​​(3, "Cindy", "F", "2019-08-21" );
Query OK, 1 row affected (0.00 sec)

Method 2: The method of entering all at once

 mysql> insert into student(student_id, name, gender, admission_date) values ​​(1, "Jack", "M", "2021-08-21" ), (2, "Tom", "M", "2020-08 -21" ), (3, "Cindy", "F", "2019-08-21" );
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0

2. Select usage - how to decide which table column data to display?

grammar:

All columns are displayed : SELECT * from table name

 mysql> SELECT * from student;
+------------+-------+--------+----------------+
| student_id | name | gender | admission_date |
+------------+-------+--------+----------------+
| 1 | Jack | M | 2021-08-21 |
| 2 | Tom | M | 2020-08-21 |
| 3 | Cindy | F | 2019-08-21 |
+------------+-------+--------+----------------+
3 rows in set (0.00 sec)

Show only some columns

• Show only one column: SELECT column name from table name

ex. Show only the name column

 mysql> SELECT name from student;
+-------+
| name |
+-------+
| Jack |
| Tom |
| Cindy |
+-------+
3 rows in set (0.00 sec)

• Specify which columns to display: SELECT column1 name , column2 name , ... from table name

ex. I want to display only id, gender and admission_date

 mysql> SELECT student_id, gender, admission_date from student;
+------------+--------+----------------+
| student_id | gender | admission_date |
+------------+--------+----------------+
| 1 | M | 2021-08-21 |
| 2 | M | 2020-08-21 |
| 3 | F | 2019-08-21 |
+------------+--------+----------------+
3 rows in set (0.00 sec)

3. NULL / NOT NULL usage - how to limit which columns can be left out and which columns must be filled in when data is input

• Display the Table we just created

 mysql> desc student;
+----------------+---------------+------+-----+--- ------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+---------------+------+-----+--- ------+-------+
| student_id | int(11) | YES | | NULL | |
| name | varchar(40) | YES | | NULL | |
| gender | enum('M','F') | YES | | NULL | |
| admission_date | date | YES | | NULL | |
+----------------+---------------+------+-----+--- ------+-------+
4 rows in set (0.00 sec)

You will notice that in addition to the column name and data type, there are actually some things that we can set. This field can be empty, that is, it can be omitted and not entered. In the case of no input, MySQL will use the value in the latter Default field to fill in, which is our default value.

ex. Suppose we want to enter a piece of data, but we only have the name and the enrollment date, so if we enter it in, the other information will be filled with NULL

 mysql> insert into student(name, admission_date) values ​​('Ken', '2020-08-06');
Query OK, 1 row affected (0.00 sec)
​
mysql> SELECT * from student;
+------------+-------+--------+----------------+
| student_id | name | gender | admission_date |
+------------+-------+--------+----------------+
| 1 | Jack | M | 2021-08-21 |
| 2 | Tom | M | 2020-08-21 |
| 3 | Cindy | F | 2019-08-21 |
| NULL | Ken | NULL | 2020-08-06 |
+------------+-------+--------+----------------+
4 rows in set (0.00 sec)

• How to set it to NOT NULL, that is, be sure to fill in the data

In fact, when we create a table, add NOT NULL to the column name we want to set

ex. We recreate a table with name, gender, phone_number columns, and restrict the name to not be empty (NOT NULL)

 mysql> create table student2(name VARCHAR(20) NOT NULL, gender ENUM("M","F"), phone_number VARCHAR(20));
Query OK, 0 rows affected (0.03 sec)

Let's see how this changes

 mysql> show columns from student2;
+-------------+---------------+------+-----+----- ----+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------+------+-----+----- ----+-------+
| name | varchar(20) | NO | | NULL | |
| gender | enum('M','F') | YES | | NULL | |
| phone_number | varchar(20) | YES | | NULL | |
+-------------+---------------+------+-----+----- ----+-------+
3 rows in set (0.00 sec)

Result: You can see that the NULL line of our name is displayed as NO, so the restriction is completed

Then let's try it, what happens if we don't enter the name information

 mysql> INSERT into student2(gender) values ​​("M");
ERROR 1364 (HY000): Field 'name' doesn't have a default value

Result: it will show an error! !

Through this NULL / NOT NULL setting, we can limit which data is required and which data can be omitted when users fill in data into our data table.