💖✨MySQL 10000-word in-depth summary, basic + Advanced (6)

This is the 9th day of my participation in the August Wen Challenge.More challenges in August.

👨🎓 author: Java Academic Conference

🏦 Storage: Github, Gitee

✏️ blogs: CSDN, Nuggets, InfoQ, Cloud + Community

💌 public account: Java Academic Party

🚫 special statement: the original is not easy, shall not be reproduced or copied without authorization, if you need to reproduce can contact xiaobian authorization.

🙏 Copyright notice: part of the text or pictures in the article come from the Internet and Baidu Encyclopedia, if there is infringement, please contact xiaobian as soon as possible. Wechat search public Java academic party contact xiaobian.

☠️ Daily toxic chicken Soup: The earliest chicken soup for the soul, such as job in the Bible.

👋 Hello! I am your old friend Java academic party, today continue to share xiaobian carefully for you to arrange the 10000 words MySQL database core knowledge, in the next few days for you to continue to share, 💘 focus not lost!! 💘. A database is a repository for data. Its storage space is large, can store millions, tens of millions, hundreds of millions of data. But the database is not randomly stored data, there are certain rules, otherwise the efficiency of the query will be very low. Database is a computer software system that stores and manages data according to data structure. 🦄

10.5 Constraints on primary Keys

Important: a table has only one primary key and must be remembered.

10.5.1 How to Add a primary key constraint to a Table?

Step 1: Determine whether the T_USER table exists, and if so, drop the entire table and create a new one

drop table if exists t_user;
create table t_user(
   id int primary key,
   username varchar(255),
   email varchar(255)
);
Copy the code

Step 2: Insert data

insert into t_user(id,username,email) values(1,’zs’,’[email protected]’); insert into t_user(id,username,email) values(3,’ls’,’[email protected]’); insert into t_user(id,username,email) values(2,’ww’,’[email protected]’);

Step 3: Look at the table structure

desc t_user;

Step 4: View the data in the table

Insert wrong data

insert into t_user(id,username,email) values(1,’ww’,’[email protected]’);

Description: This error occurs because the value of the inserted id field is the same as the value of the previously inserted field.

insert into t_user(username,email) values(‘ww’,’[email protected]’);

Description: There is no default value for the field ID, the error is caused by the insert id field value NULL.

10.5.2 Conclusions based on the above tests:

  • Id is the primary key, and because of the primary key constraint, the data in the primary key field cannot be NULL and cannot be repeated.

10.5.3 Features of Primary Keys:

  • Cannot be NULL
  • Can’t repeat

10.5.3 Terms related to primary keys

  • Primary key constraint: Primary key
  • Primary key field: after the primary key is added, the ID is called the primary key field
  • Primary key value: Each value in the ID field is a primary key value

10.5.4 What Are primary Keys Used for

  • One of the three design paradigms for tables requires that every table should have a primary key.
  • What a primary key does: The primary key is the unique identifier of the row in the table. (Like a person’s ID number)

10.5.5 Defining primary Keys using Table-level Constraints:

Step 1: Drop the table and create a new table

drop table if exists t_user;
create table t_user(
  id int,
  username varchar(255),
  primary key(id)
);
Copy the code

Step 2: Insert data

insert into t_user(id,username) values(1,’zs’); insert into t_user(id,username) values(2,’ls’); insert into t_user(id,username) values(3,’ww’); insert into t_user(id,username) values(4,’cs’);

Step 3: Look at the table structure

desc t_user;

Note: id is the primary key, so NULL cannot be YES. The key field is PRI, indicating the primary key.

Step 4: View the table data

select * from t_user;

Insert wrong data

insert into t_user(id,username) values(4,’cx’);

Explanation: An error occurred due to duplicate id data inserted.

10.6 Primary Key Value Increment (Key)

10.6.1 mysql Provides Primary Key Increment:

Delete the old table and create a new table

drop table if exists t_user;
create table t_user( 
   id int primary key auto_increment,
   username varchar(255)
);
Copy the code

Note: The ID field automatically maintains an incrementing number, starting at 1 and increasing by 1.

Insert data:

insert into t_user(username) values(‘a’); insert into t_user(username) values(‘a’); insert into t_user(username) values(‘a’); insert into t_user(username) values(‘a’); insert into t_user(username) values(‘a’); insert into t_user(username) values(‘a’);

Look at the structure of the table at this point

Note: extra stands for extra, and you can see that extra data has been added to the table structure, namely primary key increment.

View the data for the table

Note that the id increments from 1,

Tip; Oracle also provides a similar increment called sequence objects.

10.7 Foreign Key Constraints

10.7.1 Terms related to foreign key constraints

  • Foreign key constraints: Foreign key
  • Foreign key field: Adds a field with foreign key constraints
  • Foreign key value: Each value in the foreign key field

10.7.2 Write down the construction sentences of the above tables:

Note: the classno field in T_student refers to the CNO field in t_class, which is called a child table. T_class tables are called parent tables.

Drop table if exists t_student; drop table if exists t_class; Create table t_class(cNO int, cname vARCHar (255), primary key(CNO)); create table t_student( son int, sname varchar(255), classno int, foreign key(classno) references t_class(cno) ); insert into t_class values(101,'xxxxxxxxxxxxx'); insert into t_class values(102,'yyyyyyyyyyyyy'); insert into t_student values(1,'zs1',101); insert into t_student values(1,'zs1',101); insert into t_student values(1,'zs1',101); insert into t_student values(1,'zs1',101); insert into t_student values(1,'zs1',101); select * from t_class; select * from t_student;Copy the code

Insert bad data

insert into t_student values(7,’lisi’,103);

Error message:

mysql> insert into t_student values(7,’lisi’,103); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (bjpowernode.t_student, CONSTRAINT t_student_ibfk_1 FOREIGN KEY (classno) REFERENCES t_class (cno))

10.7.3 Can foreign Keys be NULL for You?

The foreign key value can be NULL.

Step 1: Insert the foreign key as null

insert into t_student(son,sname) values(6,’zs6′);

Step 2: Look at the data

select * from t_student;

10.7.4 Must a Foreign Key Field Reference a field in another table be the primary key?

Note: The referenced field is not necessarily a primary key, but at least has the unique constraint.

Today first share here, tomorrow to continue to share with you, pay attention not to get lost yo, we will see 😊 tomorrow.

Above project source code,Get it for free at planet 🌍planet(making address)If I didn’t have my Github buddies. You can search 🔍 wechat official account:Java academic lie prone, 📭 send MySQL, free to send you the project source code, the code is personally tested by the small editor 🔧, absolutely reliable, free to use.

——–💘 after watching the big guys can pay attention to the xiaobian, will always update the small skills, free to share with you!! 💝 — — — — — — — — —

Click 🌍 planet for quick access to the Github planet!! Inside there are more fun technology, waiting for you to explore yo 💪!!