05-day3 dark horse Java Web notes Mysql constraints, database design, backup and restore

05-day3 dark horse Java Web notes Mysql constraints, database design, backup and restore

constraint

Concept: limit the data in the table to ensure the correctness, effectiveness and integrity of the data.

Classification:

  1. Primary key constraint: primary key
  2. Non NULL constraint: not null
  3. Unique constraint: unique
  4. Foreign key constraint: foreign key

Primary key constraint

primary key

be careful:

  1. Meaning: non empty and unique
  2. A table can only have one field as primary key
  3. A primary key is the unique identifier of a record in a table

When creating a table, add a primary key constraint

create table stu(
			id int primary key,-- to id Add primary key constraint
			name varchar(20)
);

Delete primary key

-- error alter table stu modify id int ;
ALTER TABLE stu DROP PRIMARY KEY;

After creating the table, add the primary key

ALTER TABLE stu MODIFY id INT PRIMARY KEY;

Non NULL constraint

not null, value cannot be null

Add constraints when creating tables

CREATE TABLE stu(
			id INT,
			NAME VARCHAR(20) NOT NULL -- name Is not empty
		);

After creating the table, add a non empty constraint

ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;

Delete non empty constraint for name

ALTER TABLE stu MODIFY NAME VARCHAR(20);

Unique constraint

unique, the value cannot be repeated

When creating tables, add unique constraints

CREATE TABLE stu(
		id INT,
		phone_number VARCHAR(20) UNIQUE -- Added unique constraint
 );
 #Note that in mysql, the value of a column limited by a unique constraint can have multiple null s

Delete unique constraint

ALTER TABLE stu DROP INDEX phone_number;

After creating the table, add a unique constraint

ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE;

Foreign key constraint

foreign key, which enables the table to generate a relationship with the table, so as to ensure the correctness of the data.

When you create a table, you can add foreign keys

create table Table name(				....				Foreign key column				constraint Foreign key name foreign key (Foreign key column name) references Main table name(Main table column name)			);

Delete foreign key

ALTER TABLE Table name DROP FOREIGN KEY Foreign key name;

After creating the table, add the foreign key

ALTER TABLE Table name ADD CONSTRAINT Foreign key name FOREIGN KEY (Foreign key field name) REFERENCES Main table name(Main table column name);

Cascade operation

#Add cascading operation ALTER TABLE Table name ADD CONSTRAINT Foreign key name FOREIGN KEY (Foreign key field name) REFERENCES Main table name(Main table column name) ON UPDATE CASCADE ON DELETE CASCADE  ;# Classification: 				 1. Cascade update: ON UPDATE CASCADE 				 2. Cascade delete: ON DELETE CASCADE 

Automatic growth

Concept: if a column is numeric, use auto_increment can be used to complete automatic growth

When creating a table, add a primary key constraint and complete the primary key self growth

create table stu(			id int primary key auto_increment,-- to id Add primary key constraint			name varchar(20)		);

Delete auto growth

ALTER TABLE stu MODIFY id INT;

Add auto growth

ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;

Database design

Relationship between multiple tables

Classification:

  1. one-on-one
    • Such as: person and ID card
    • Analysis: a person has only one ID card, and one ID card can only correspond to one person
  2. One to many
    • E.g. departments and employees
    • Analysis: a department has multiple employees, and one employee can only correspond to one department
  3. Many to many
    • E.g. students and courses
    • Analysis: a student can choose many courses, and a course can also be selected by many students

realization

One to many (many to one):

E.g. departments and employees
Implementation method: establish a foreign key on one of the multiple parties and point to the primary key of one party.

Many to many:

E.g. students and courses
Implementation method: the third intermediate table is needed to implement the many to many relationship. The middle table contains at least two fields, which are used as the foreign keys of the third table and point to the primary keys of the two tables respectively

One to one (understanding):

Such as: person and ID card
Implementation method: one-to-one relationship. You can add a unique foreign key on either side to point to the primary key of the other party.

case

-- Create tourism route classification table tab_category-- cid Tourism route classification, primary key, automatic growth-- cname Tourism route classification name is not empty, unique, string 100 CREATE TABLE tab_category (			cid INT PRIMARY KEY AUTO_INCREMENT,			cname VARCHAR(100) NOT NULL UNIQUE);		-- Create tour route table tab_route/*		rid Tourist routes, primary key, automatic growth 		 rname tourist route name is non empty and unique, string 100 		 Price price 		 rdate launch time, date type 		 cid foreign key, category*/CREATE TABLE tab_route(			rid INT PRIMARY KEY AUTO_INCREMENT,			rname VARCHAR(100) NOT NULL UNIQUE,			price DOUBLE,			rdate DATE,			cid INT,			FOREIGN KEY (cid) REFERENCES tab_category(cid));				/*Create user table tab_user 		 uid user primary key, self growing 		 Username username is 100 long, unique and non empty 		 Password password length: 30, non empty 		 Name real name length 100 		 Birthday birthday 		 sex, fixed length string 1 		 Phone number, string 11 		 email mailbox, string length 100		*/CREATE TABLE tab_user (			uid INT PRIMARY KEY AUTO_INCREMENT,			username VARCHAR(100) UNIQUE NOT NULL,			PASSWORD VARCHAR(30) NOT NULL,			NAME VARCHAR(100),			birthday DATE,			sex CHAR(1) DEFAULT 'male',			telephone VARCHAR(11),			email VARCHAR(100));				/*		Create favorite tabletab_ favorite 		 rid travel route id, foreign key 		 date collection time 		 Uid, user id, foreign key 		 rid and uid cannot be duplicate. Set the composite primary key. The same user cannot collect the same line twice		*/		CREATE TABLE tab_favorite (			rid INT, -- line id			DATE DATETIME,			uid INT, -- user id			-- Create composite primary key			PRIMARY KEY(rid,uid), -- composite keys 			FOREIGN KEY (rid) REFERENCES tab_route(rid),			FOREIGN KEY(uid) REFERENCES tab_user(uid));

Paradigm of database design

Concept: some specifications to follow when designing a database. To follow the latter paradigm requirements, you must first follow all the previous paradigm requirements

When designing relational database, we should comply with different specification requirements and design a reasonable relational database. These different specification requirements are called different paradigms. Various paradigms present sub specifications. The higher the paradigm, the smaller the database redundancy.

At present, relational database has six paradigms: the first paradigm (1NF), the second paradigm (2NF), the third paradigm (3NF), bath Codd paradigm (BCNF), the fourth paradigm (4NF) and the fifth paradigm (5NF, also known as perfect paradigm).

Classification:

Case table

First normal form (1NF): each column is an indivisible atomic data item

The case diagram does not meet the requirements of the first paradigm because there are separable data items in the column

Separate the two columns and modify them to the following figure to achieve the first normal form

But there are still problems:
1. There is very serious data redundancy (duplication): name, department name and department head
2. Problems in data addition: the data is illegal when adding new departments and department heads
3. Problems in data deletion: when Zhang Wuji graduates, deleting the data will delete the data of the Department together.

Second normal form (2NF): on the basis of 1NF, the non code attribute must be completely dependent on the code (on the basis of 1NF, part of the functional dependence of the non main attribute on the main code is eliminated)

Concept:

Functional dependencies:

A – > b, if the value of the unique B attribute can be determined through the value of the a attribute (attribute group), then B is said to depend on a.
For example: student number – > name. (student number, course name) -- > score

To determine the name, you need to determine the name (attribute) through the student number

To determine the score, you need to pass the student number and course name (attribute group)

Full functional dependency:

A – > B. If a is an attribute group, the determination of B attribute value depends on all attribute values in a attribute group.

For example: (student number, course name) -- > score

If you want to determine the score, you must determine the course name through the student number in the attribute group. None of them is indispensable.

Partial functional dependencies:

A – > B. If a is an attribute group, the value of B attribute only depends on some values in a attribute group.

For example: (student number, course name) – > score

Transfer function dependency:

A – > b, B – > C. if the value of the unique B attribute can be determined through the value of the a attribute (attribute group), and the value of the unique C attribute can be determined through the value of the B attribute (attribute group), then the C transfer function depends on a

For example: student number – > department name, department name – > department head

Code:

If an attribute or attribute group in a table is completely dependent on all other attributes, the attribute (attribute group) is called the code of the table. For example, the code in the table is: (student number, course name)

Primary attribute: all attributes in the code attribute group
Non primary attribute: the attribute of the over code attribute group

After eliminating some dependencies, the following figure is shown, from the first paradigm to the second paradigm, which solves the problem of data redundancy

Existing problems:

2. Problems in data addition: the data is illegal when adding new departments and department heads
3. Problems in data deletion: when Zhang Wuji graduates, deleting the data will delete the data of the Department together.

The third normal form (3NF): on the basis of 2NF, any non primary attribute does not depend on other non primary attributes (eliminating transitive dependency on the basis of 2NF)

Backup and restore of database

Command line:

Backup: mysqldump -u user name -p Password database name > Saved path
Restore:			1. Login database			2. Create database			3. Use database			4. Execute the file. source File path

Tags: Java Database MySQL SQL

Posted on Thu, 09 Sep 2021 16:44:32 -0400 by thepip3r