MySQL Database relational operations

# # MySQL Database

# # # Knowledge Points:

1. Data operation increase, delete, change
2. FOREIGN KEY constraint requirements
3. A one-to-many table relationship
4. One-to-one table relationship
5. Many-to-many table relationships
6. Referential actions for FOREIGN KEY constraints

# # Data manipulation

# # # Insert Data

Method One:

"' SQL
INSERT [INTO] table_name [(column_name,...)]
{values| VALUE} ({expr| DEFAULT},...), (...),...;
```

Method Two:

"' SQL
INSERT [into] tbl_name SET col_name={expr| DEFAULT},...;
```

"MySQL
#例:
mysql> CREATE TABLE ' tb1 ' (
' id ' INT PRIMARY KEY auto_increment,
' Name ' VARCHAR () not NULL,
' Age ' INT DEFAULT 18
);
Query OK, 0 rows affected (0.54 sec)

mysql> INSERT into ' tb1 ' (' name ')
VALUES (' Rose '),
(' Taka ')
;
Query OK, 2 rows affected (0.08 sec)
Records:2 duplicates:0 warnings:0

Mysql> SELECT * from ' tb1 ';
+----+-------+------+
| ID | name | Age |
+----+-------+------+
| 1 | Rose | 18 |
| 2 | Taka | 18 |
+----+-------+------+
2 rows in Set (0.00 sec)

mysql> INSERT into ' tb1 ' SET ' name ' = ' taka ';
Query OK, 1 row affected (0.07 sec)

mysql> INSERT into ' tb1 ' SET ' name ' = ' Budong ', ' age ' = 24;
Query OK, 1 row affected (0.07 sec)
```

# # # Update data

"MySQL
UPDATE Tb_name
SET col_name1={expr1| default}[,col_name2={expr2| DEFAULT}] ...
[WHERE where_condition];
```

"MySQL
#例:
Mysql> SELECT * from ' tb1 ';
+----+--------+------+
| ID | name | Age |
+----+--------+------+
| 1 | Rose | 18 |
| 2 | Tulple | 18 |
| 3 | Taka | 18 |
| 4 | Budong | 24 |
+----+--------+------+
4 rows in Set (0.00 sec)

mysql> UPDATE ' tb1 ' SET ' age ' = ' age ' +1;
Query OK, 4 rows affected (0.06 sec)
Rows Matched:4 Changed:4 warnings:0

mysql> UPDATE ' tb1 ' SET ' age ' =20 WHERE ' name ' = ' taka ';
Query OK, 1 row affected (0.10 sec)
Rows matched:1 changed:1 warnings:0

mysql> UPDATE ' tb1 ' SET ' age ' =21 WHERE ' id ' <3;
Query OK, 2 rows affected (0.10 sec)
Rows matched:2 Changed:2 warnings:0

Mysql>
```

# # # Delete Data

"' SQL
DELETE from Tbl_name [WHERE Where_conditon];
```

Do not add where to delete all records

"MySQL
#例:
Mysql> SELECT * from ' tb1 ';
+----+--------+------+
| ID | name | Age |
+----+--------+------+
| 1 | Rose | 21 |
| 2 | Tuple | 21 |
| 3 | Taka | 21 |
| 4 | Budong | 26 |
+----+--------+------+
4 rows in Set (0.00 sec)

Mysql> DELETE from ' tb1 ' WHERE ' id ' = 4;
Query OK, 1 row affected (0.06 sec)

Mysql> DELETE from ' tb1 ';
Query OK, 3 rows affected (0.13 sec)

Mysql> SELECT * from ' tb1 ';
Empty Set (0.00 sec)

Mysql>
```

# # # FOREIGN KEY constraint ' FOREIGN key '

FOREIGN KEY constraint ' FOREIGN key ', maintaining data consistency, integrity implementation one-to-one or one-to-many relationships.

##### requirements for FOREIGN KEY constraints:

The storage engine for the data table can only be InnoDB
The foreign key column and the reference column data type are consistent
The foreign key must be linked to the key.

"MySQL
#添加外键的格式:
ALTER TABLE Yourtablename
ADD [CONSTRAINT foreign key name] FOREIGN key [id] (index_col_name, ...)
REFERENCES tbl_name (index_col_name, ...)
[on DELETE {CASCADE | SET NULL | NO ACTION | RESTRICT}]
[on UPDATE {CASCADE | SET NULL | NO ACTION | RESTRICT}]
```

##### one-to-many relationship

For example, a college can have a large number of students, and a student belongs to only one college (usually), and the relationship between the college and the student is one-to-many relationships, which is achieved through a foreign key association.

"MySQL
#例:
#创建学院表:
mysql> CREATE TABLE ' Department ' (
' id ' INT PRIMARY KEY auto_increment,
' Name ' VARCHAR (not NULL)
);
Query OK, 0 rows affected (0.61 sec)

#创建学生表:
mysql> CREATE TABLE ' student ' (
' id ' INT PRIMARY KEY auto_increment,
' Name ' VARCHAR () not NULL,
' dept_id ' INT,
-FOREIGN KEY (' dept_id ') REFERENCES ' Department ' (' ID ')
);
Query OK, 0 rows affected (0.51 sec)

#插入数据
mysql> INSERT into ' Department ' (' name ')
VALUES (' A '),
(' B ')
;
Query OK, 2 rows affected (0.10 sec)
Records:2 duplicates:0 warnings:0

Mysql> INSERT into ' student ' (' name ', ' dept_id ')
VALUES (' s1 ', 1),
(' S2 ', 2),
(' S3 ', 2)
;
Query OK, 3 rows affected (0.08 sec)
Records:3 duplicates:0 warnings:0
```

##### a pair of relations

For example, students have a school number, name, college, but students have some such as telephone, home address and other more private information, this information will not be placed in the student table, will create a new student's details table to store. The relationship between the student table and the student's detail table is a one-to-one relationship, because a student has only a single piece of detailed information. This relationship is achieved by means of a foreign key plus a unique key. You can also have a foreign key Garther (contains a unique constraint).

"MySQL
#例:
#学生表:
mysql> DESC ' student ';
+---------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+----------------+
| ID | Int (11) | NO | PRI | NULL | auto_increment |
| name | varchar (20) | NO | | NULL | |
| dept_id | Int (11) | YES | MUL | NULL | |
+---------+-------------+------+-----+---------+----------------+
3 Rows in Set (0.06 sec)

#建立详细学生表1111: FOREIGN key + primary key
mysql> CREATE TABLE ' student_details ' (
' id ' INT PRIMARY KEY,
' Age ' INT,
' Gender ' CHAR (1),
-FOREIGN KEY (' id ') REFERENCES ' student ' (' ID ')
);
Query OK, 0 rows affected (0.67 sec)

#建立详细学生表2222: FOREIGN key + Unique key
mysql> CREATE TABLE ' student_details ' (
' id ' INT PRIMARY KEY auto_increment,
' Age ' INT,
' Gender ' CHAR (1),
' s_id ' INT UNIQUE KEY,
FOREIGN KEY (' s_id ') REFERENCES ' student ' (' s_id ')
);
Query OK, 0 rows affected (0.02 sec)
```

##### Many-to-many relationships

For example, students have to enroll in elective courses, one student can enroll in more than one course, and a lot of students enroll in a course, so the student table and curriculum form a many-to-many relationship. For a many-to-many relationship, you need to create a third relational table that implements this relationship in the form of a foreign key plus a primary key in the relational table.

"MySQL
#例:
#建立课程表:
mysql> CREATE TABLE ' Course ' (
' id ' INT PRIMARY KEY auto_increment,
' Name ' VARCHAR () not NULL,
);
Query OK, 0 rows affected (1.18 sec)

#学生与课程多对多关系表
mysql> CREATE TABLE ' select ' (
' s_id ' INT,
' crs_id ' INT,
-PRIMARY KEY (' s_id ', ' crs_id '),
-FOREIGN KEY (' s_id ') REFERENCES ' student ' (' ID '),
-FOREIGN KEY (' crs_id ') REFERENCES ' course ' (' ID ')
);
Query OK, 0 rows affected (0.50 sec)
```

##### referential actions for foreign KEY constraints:

"MySQL
1.CASCADE automatically delete or update matching rows in a child table when deleted or updated from a parent table
2.SET NULL when the row is deleted or updated from the parent table, the foreign key column in the child table is set to NULL.
If you use this option, you must ensure that the child table column does not specify not NULL
3.RESTRICT deny Delete or update to parent table
4.NO action-standard SQL keyword, same as restrict in MySQL
```

MySQL Database relational operations