Web DAY16 database integrity constraints, MySQL coding issues, backup and recovery, multi-table queries

Constraints

* The constraint is added to the column, to constrain the column!

1. PRIMARY KEY constraint (unique identifier)

Non-empty * * *

Unique * * *

Quoted * * * *

* When a column of a table is designated as the primary key, the column cannot be empty and duplicate values cannot occur.

* Two ways to specify a primary key when creating a table:

>

CREATE TABLE Stu (

Sid CHAR (6) PRIMARY KEY,

Sname VARCHAR (20),

Age INT,

Gender VARCHAR (10)

);

Specifies that the SID column is a primary key column, that is, the primary KEY constraint is added for SID columns

>

CREATE TABLE Stu (

Sid CHAR (6),

Sname VARCHAR (20),

Age INT,

Gender VARCHAR (10),

PRIMARYKEY (SID)

);

Specifies that the SID column is a primary key column, that is, the primary KEY constraint is added for SID columns

* Specify primary key when modifying table: Alter TABLE Stu ADD PRIMARYKEY (SID);

* Delete primary key: ALTER TABLE stu DROP PRIMARYKEY;

2. Primary key self-growth

* Because the properties of the primary key column are unique and cannot be null, we typically specify the primary key class as an integer and then set its autogrow, which guarantees the unique and non-empty characteristics of the primary key column when the data is inserted.

* Specify primary key self-growth when creating table

CREATE TABLE Stu (

Sidint PRIMARY KEY auto_increment,

Sname VARCHAR (20),

Age INT,

Gender VARCHAR (10)

);

* Set PRIMARY key self-growth when modifying table: ALTER table stu changesid Sid INT Auto_increment;

* Delete primary key from growth when modifying table: ALTER table stu changesid Sid INT;

* Test primary KEY self-growth:

> INSERT into Stu VALUES (NULL, ' Zhangsan ', and ' male ');

> INSERT into Stu (sname,age,gender) VALUES (' Zhangsan ', ' Male ');

3. Non-null constraints

* Because some columns cannot be set to a null value, you can add a non-null constraint to the column.

For example

CREATE TABLE Stu (

Sidint PRIMARY KEY auto_increment,

Sname VARCHAR () not NULL,

Age INT,

Gender VARCHAR (10)

);

* A non-null constraint is set on the sname column

4. Unique constraints

* Garage Some columns cannot set duplicate values, so you can add a unique constraint to the column.

For example

CREATE TABLE Stu (

Sidint PRIMARY KEY auto_increment,

Sname VARCHAR () not NULL UNIQUE,

Age INT,

Gender VARCHAR (10)

);

* Set a UNIQUE constraint on the sname column

5. Conceptual model

Object model: Can be associated in two directions, and refers to an object, not a primary key!

Relational model: You can only refer to a party by multiple parties, and only the primary key, not a whole row of records.

Object model: domain!!! in Java For example: User, Student

is a

has a (association)

> 1 to 1

> 1-to-many

> Many-to-many

Use a

Relational model: Tables in the database!!!

When we want to complete a software system, we need to extract the entities in the system to form a conceptual model.

For example, departments and employees are entities in the system. The entity in the conceptual model will eventually become a class in Java, a table in the database.

There are also relationships between entities, and there are three kinds of relationships:

* 1-to-many: for example, each employee is subordinate to one department, and one department can have multiple employees, the employees are multi-party, and the department is a side.

* 1 to 1: For example, husband and wife is a one-to-one relationship, a husband can only have a wife, and a wife can only have a husband.

* Many-to-many: the relationship between teachers and students is many-to-many, a teacher can have multiple students, a student can have more than one teacher.

The conceptual model becomes the entity class in Java (JavaBean)

The class uses member variables to complete the relationship, and is generally a bidirectional association!

Many-to-one bi-directional correlation, that is, employee-related departments, departments, and associates

Class Employee {//multi-party    Association ... Private Department Department;  }  Class Department {//one party associated with multiparty ...    Private list<employee> employees;  }  Class Husband {    ...    Private Wife Wife;  } Class Wife {    ...    Private Husband  }  class Student {    ...    Private list<teacher> Teachers  }  classteacher {    ...    Private list<student> students;  }

6. FOREIGN KEY constraints

* The foreign key must be the value of the primary key of the other table (the foreign key to reference the primary key!) )

* Foreign keys can be repeated

* The foreign key can be empty

* There can be more than one foreign key in a single table!

The conceptual model becomes a table in the database

A many-to-one relationship in a database table, you only need to refer to the 1-party primary key in multiple parties using a separate column

/* Employee Table */

Create Talbe EMP (

empno INT Primary key,/* employee number */

...

DEPTNO int/* Department Number */

);

/* Department Table */

CREATE TABLE Dept (

DEPTNO Int Primary key,/* Division number */

...

);

The value of the Deptno column in the EMP table represents the department number that the current employee is subordinate to. That means the Emp.deptno must be real in the Dept table!

But we have to be bound to it, or else the department number that the employee belongs to does not exist. This constraint is a foreign key constraint.

We need to add a foreign key constraint to Emp.deptno and constrain its value to exist in Dept.deptno. The foreign key must be the primary key of the other table!

Syntax: CONSTRAINT constraint name FOREIGN key (foreign key column name) REFERENCES Association table (primary key of the associated table)

Specifying a FOREIGN KEY constraint when creating a table

Create Talbe EMP (

empno int PRIMARY KEY,

...

Deptno int,

CONSTRAINT fk_emp FOREIGN KEY (MGR) REFERENCES EMP (empno)

);

To add a foreign key constraint when modifying a table

ALERT TABLE EMP

AddConstraint fk_emp_deptno FOREIGN KEY (deptno) REFERENCES dept (DEPTNO);

To delete a foreign key constraint when modifying a table

ALTER TABLE EMP

DROP FOREIGN KEY fk_emp_deptno;/* constraint name */

--------------------------

7 Relationship between a database table and a table

one to one:

Examples include T_person tables and t_card tables, i.e. people and identity cards. This situation requires finding out the master-slave relationship, i.e. who is the primary table and who is from the table. People can have no ID, but the ID must be someone to do, so the person is the main table, and the identity card is from the table.

There are two possible scenarios for designing from a table:

Add a Foreign key column (relative to the T_user table) in the T_card table, and add a unique constraint to the foreign key;

Add a FOREIGN KEY constraint (relative to the T_user table) to the primary key of the T_card table, that is, the primary key of the T_card table is also a foreign key.

one-to-many (multi-pair):

The most common is one-to-many! A pair of many and many to one, which is from which angle to see. T_user and T_section relations, from the T_user view is a one-to-many, and from the t_section point of view is a lot of a! This situation is created in the multi-party foreign key!

Many-to-many :

For example, T_stu and T_teacher tables, where a student can have multiple teachers, and a teacher can have multiple students. This situation often requires creating an intermediate table to handle many-to-many relationships. For example, create a table T_stu_tea table, give two foreign keys, a foreign key relative to the T_stu table, and another foreign key relative to the T_teacher table.

Database one-to-one relationships

It is special to establish a one-to-two relationship in a table, which requires that the primary key, which is the primary key, is the foreign key.

CREATE TABLE Husband (

HID int PRIMARY KEY,

...

);

Create TABLE wife (

wid int PRIMARY KEY,

...

ADD CONSTRAINT fk_wife_wid FOREIGN KEY (WID) REFERENCES Husband (HID)

);

The wife table of WID is the primary key, but also the foreign key of the relative husband table!

Husband.hid is the primary key and cannot be repeated!

Wife.wid is the primary key, cannot be duplicated, is also a foreign key, must come from Husband.hid.

So if there is a record in the wife table Wid is 1, then the other records in the wife table wid can no longer be 1, because it is the primary key.

At the same time, the value of 1 must exist in Husband.hid because WID is a foreign key. This completes a one-to-one relationship.

The primary key from the table is the foreign key!

Database Many-to-many relationships

Establishing a many-to-many relationship in a table requires the use of an intermediate table, which requires three tables, two foreign keys in the intermediate table, and a reference to the primary key of the other two tables, respectively.

CREATE TABLE Student (

Sid int PRIMARY KEY,

...

);

CREATE TABLE Teacher (

Tid int PRIMARY KEY,

...

);

CREATE TABLE Stu_tea (

Sid Int,

Tid int,

ADD CONSTRAINT fk_stu_tea_sid FOREIGN KEY (SID) REFERENCES student (SID),

ADD CONSTRAINT fk_stu_tea_tid FOREIGN KEY (tid) REFERENCES teacher (TID)

);

At this point, each record in the Stu_tea table is to illustrate the relationship between student and teacher tables.

For example, records in the Stu_tea table: SID is 1001,tid to 2001, this indicates that the student with number 1001 has a teacher numbered 2001

Sid Tid

101 201 */* The student with number 101 has a teacher numbered 201 */

101 202 */* The student with number 101 has a teacher numbered 202 */

101 203 */* The student with number 101 has a teacher numbered 203 */

102 201 */* The student with number 102 has a teacher numbered 201 */

102 204 */* The student with number 102 has a teacher numbered 204 */

-----------------------

Coding

1. View MySQL database encoding

*show VARIABLES like ' char% ';

2. Code interpretation

*character_set_client:mysql Use this code to interpret the data sent by the client, for example, the code is UTF8, then if the client sends the data is not UTF8, then there will be garbled

*character_set_results:mysql will convert the data to this encoding, and then send to the client, for example, the code is UTF8, then if the client does not use UTF8 to interpret, then there will be garbled

Other encodings are supported in Chinese, which means they cannot be used Latin1

3. Console garbled problem

* garbled when inserting or modifying:

> This is because GBK is used by default under CMD, and character_set_client is not the reason for GBK. We just need to make the two codes the same.

> Because the code to change the cmd is inconvenient, so we go to set Character_set_client to GBK.

* The data of the query is garbled:

> This is because Character_set_results is not GBK, and CMD uses GBK for the default reason. We just need to make the two codes the same.

> Because the code to change the cmd is inconvenient, so we go to set Character_set_results to GBK.

* The statement that sets the variable:

> Set CHARACTER_SET_CLIENT=GBK;

> Set CHARACTER_SET_RESULTS=GBK;

Note that setting a variable is only valid for the current connection, and when you exit the window, log in to MySQL again, and you need to set the variable again.

For once and for all, you can set it in My.ini:

You can set the DEFAULT-CHARACTER-SET=GBK.

　

4. Specify the default encoding

We have specified a default encoding of UTF8 when we installed MySQL, so we do not need to specify the encoding again when we create the database and create the table.

For once and for all, you can set it in My.ini:

You can set the Character-set-server=utf8.

character_set_client | UTF8--and MySQL treats the data passed by our client as utf8! One is to pass the UTF8 to it, and the second is if we pass the GBK, then we need to modify this variable to GBK

character_set_connection | Utf8

Character_set_database | Utf8

Character_set_results | UTF8---MySQL sends the data to the client UTF8. One is the client with UTF8 encoding, the second is if the client uses GBK to encode, then need to modify this variable to GBK.

Character_set_server | Utf8

Character_set_system | Utf8

----------------------------

Character_set_client=utf8, no matter what encoded data the client sends, MySQL is the data of UTF8!

> If the client sends a GBK

> servers will treat as UTF8

> Summary: Inevitable garbled!

There are two ways to deal with problems;

> Let the client send UTF8 data (not feasible)

> Change character_set_client to GBK

SETCHARACTER_SET_CLIENT=GBK; --only valid in the current window, that is, close the window, then open, and back to UTF8.

Character_set_results=utf8, the data with what code to send to the client!

> If the server is sending UTF8 data to the client

> client will think of it as GBK, because our little black screen can only show GBK

> Summary: Inevitable garbled!

There are two ways to deal with problems:

> Let the server Send GBK data: Set CHARACTER_SET_RESULTS=GBK

> Let the small black screen use UTF8 to interpret (not feasible)

My.ini

configured in the general configuration file, you can

[Client]

port=3306

[MySQL]

DEFAULT-CHARACTER-SET=GBK/* It can be once and for all! It can modify three variables: client, results, connection*/

-------------------------

Backup and Recovery

Database--SQL statements

SQL statement--database

1. Database export SQL script (back up database contents, not backup DATABASE!) )

> Mysqldump–u user name –p password database name > generated script file path

> Example: mysqldump-uroot-p123 mydb1>c:\mydb1.sql (same as Mysql.exe and Mysqld.exe, in bin directory)

> Note, do not score numbers, do not log in to MySQL, run directly under CMD

> Note that the generated script file does not contain the CreateDatabase statement

2. Execute SQL Script

The first way

> Mysql-u user name-p password Database < script file path

> For example:

* Delete the MYDB1 library first, then recreate the MYDB1 library

*mysql-uroot-p123 Mydb1<c:\mydb1.sql

> Note, do not score numbers, do not log in to MySQL, run directly under CMD

The second way

> Log in to MySQL

> Source SQL Script path

> For example:

* Delete the MYDB1 library first, then recreate the MYDB1 library

* Switch to MYDB1 Library

*source C:\mydb1.sql

---------------------------------

Database--SQL: Backup

SQL--Database: Recovery

------------------

mysqldump-uroot-p123 mydb3>c:/a.sql--> Backup

Mysql-uroot-p123 Mydb3<c:/a.sql---recovery

SOURCE C:/a.sql---Recover

---------------------------------

Multi-Table Query

Keyword execution order

SELECT *

From EMP, dept, (SELECT * from EMP)

where

GROUP BY

Having

ORDER BY

Limit

Classification

* Merge result Sets (Learn)

* Connection Query

* Sub-query

Merging result sets

* Requires a merged table with the same column type and number of columns

*union, removing duplicate rows

*union all, do not remove duplicate rows

SELECT * FROM CD

UNION All

SELECT * from AB;

Connection Query

1. Classification

* Internal Connection

* External Connection

> Left Outer connection

> Right Outer connection

> Full external connection (MySQL not supported)

* Natural connection (in a simplified way)

2. Internal connection

* Dialect: SELECT * FROM table 1 alias 1, table 2 alias 2 WHERE alias 1.xx= alias 2.xx

* Standard: SELECT * FROM table 1 alias 1 INNER JOIN table 2 alias 2 on alias 1.xx= alias 2.xx

* Nature: SELECT * FROM table 1 alias 1 NATURAL JOIN table 2 alias 2

* All records in the internal connection query satisfy the criteria.

3. External connection

* LEFT outer: SELECT * FROM table 1 alias 1 left OUTER JOIN table 2 alias 2 on alias 1.xx= alias 2.xx

> The left table record will be queried whether or not it satisfies the condition, and the right table will only come out if it satisfies the criteria. The left table does not satisfy the condition of the record, the right table part is null

* LEFT outer nature: SELECT * FROM table 1 alias 1 NATURAL outerjoin table 2 alias 2 on alias 1.xx= alias 2.xx

* Right outside: SELECT * FROM table 1 alias 1 right OUTER JOIN table 2 alias 2 on alias 1.xx= alias 2.xx

> The right table record will be queried whether or not it satisfies the condition, and the left table will only come out if it satisfies the criteria. The right table does not satisfy the condition of the record, and its left table part is null

* Right outside nature: SELECT * FROM table 1 alias 1 NATURAL outerjoin table 2 alias 2 on alias 1.xx= alias 2.xx

* Full Link: You can use union to complete the link

Sub-query

: There are queries in the query (see the number of select keywords!) ）

1. Where to appear:

*where after a condition exists

exists as a table after *from (multiple rows and columns)

2. Conditions

* (* * *) Single column: SELECT * FROM table 1 alias 1 where column 1 [=, >, <, >=, <=,! =] (select column from table 2 alias 2 WHERE condition)

* (* *) multi-line single column: SELECT * FROM table 1 alias 1 where column 1 [in, all, any] (select column from table 2 alias 2 where Condition)

* (*) Single-row Multi-column: SELECT * FROM table 1 alias 1 where (column 1, column 2) in (SELECT column 1, column 2 from table 2 alias 2 WHERE condition)

* (* * *) Multiline Multi-column: SELECT * FROM table 1 alias 1, (SELECT ...) alias 2 WHERE condition

====================================================

Cartesian product

{A, B, c} {A}

{A1, A2, B1, B2, C1, c2}

====================================================

Web DAY16 database integrity constraints, MySQL coding issues, backup and recovery, multi-table queries