👨‍🎓 Blogger introduction:
IT Bond, a Jianghu person jeames007，10 year DBA hands-on background
 China DBA union(ACDU)Member, currently engaged in DBA And program programming

SQL is almost a necessary skill for the production and research position of Internet companies, but if you only know SQL, you can't do anything.
1. If you are a data analyst, you need to skillfully translate the data and index requirements in your mind into SQL logic to query data, and then complete your own data analysis report. Your output is analysis report, not SQL code;
2. If you are a warehouse engineer (partial application layer), you need to design the model according to the business logic and write the scheduling task to output data for business personnel to use. Your output is data model and table;
3. If you are an algorithm engineer, you may need to use SQL to implement user tags, feature engineering, etc., but these are the basic preparations for your model training and evaluation, and your output is an algorithm model that can improve some indicators.

Therefore, everyone should use SQL, but it is not SQL itself that is used to measure output. You need to use this tool to create other value.
Mr. IT bond takes you to be a master of SQL. Let's start~

1. Constraint creation

1.1 table level integrity constraints

Create TB according to the following table structure_ Student data sheet,
It is required to define the primary key in the form of table level integrity constraint, and specify the name of the primary key constraint as pk_student

CREATE TABLE tb_student (
 studentNo CHAR(10) NOT NULL,
 studentName VARCHAR(10) NOT NULL,
 sex CHAR(2),
 birthday DATE, 
 native VARCHAR(20),
 nation VARCHAR(20) default 'Chinese',
 classNo CHAR(6),
 constraint pk_student primary key(studentNo)
) engine=InnoDB default charset=gb2312;

1.2 train level integrity constraints

Create TB according to the following table structure_ class.
Requirements: use InnoDB storage engine, gb2312 character set, and the primary key constraint is column level integrity constraint,
The unique constraint is the table level integrity constraint, and its constraint name is uq_class

CREATE TABLE tb_class (
 classNo CHAR(6) PRIMARY KEY NOT NULL,
 className VARCHAR(20) NOT NULL,
 department VARCHAR(20),
 grade ENUM('1','2','3','4'), 
 classNum TINYINT,
 constraint uq_class unique(className)
) engine=InnoDB default charset=gb2312;

1.3 cascade update deletion

Delete data table tb_student，According to table tb_student Structure re creation tb_student，
Create while creating tb_student reach tb_class Foreign key constraints for
(The attributes with the same meaning in the two tables are classNo，
therefore classNo yes tb_student Foreign key of the,
The constraint name is fk_student，
The corresponding reference actions are defined, and the update operation is cascaded( cascade)，
Delete operation is restricted(restrict)，The data table engine is InnoDB，The character set is gb2312

drop table tb_student;

CREATE TABLE tb_student (
 studentNo CHAR(10) NOT NULL,
 studentName VARCHAR(10) NOT NULL,
 sex CHAR(2),
 birthday DATE, 
 native VARCHAR(20),
 nation VARCHAR(20) default 'Chinese',
 classNo CHAR(6),
 constraint fk_student FOREIGN KEY (classNo)
 references tb_class(classNo) on delete restrict on update cascade
) engine=InnoDB default charset=gb2312;

1.4 foreign keys

Create TB according to the following table structure_ Course table,
Requirement: the foreign key name is fk_course, the engine is InnoDB, and the default character set is gb2312

CREATE TABLE tb_course (
 courseNo CHAR(6) NOT NULL primary key comment 'Course number',
 courseName VARCHAR(20) unique not NULL comment 'Course name',
 credit DECIMAL(3,1) not NULL comment 'credit',
 courseHour TINYINT(2) not NULL comment 'Class hours', 
 term TINYINT(2) comment 'Opening semester',
 priorCourse CHAR(6) comment 'Prerequisite courses',
 constraint fk_course FOREIGN KEY(priorCourse) REFERENCES tb_course(courseNo)
) engine=InnoDB default charset=gb2312;

Define data table tb_ The structure of the score table is shown in the following table. Create the corresponding foreign key
The engine is InnoDB, and the default character set is gb2312

Alter table tb_student add primary key(studentNo);   --Add primary key

CREATE TABLE tb_score(
 studentNo CHAR(10) NOT NULL comment 'Student number',
 courseNo CHAR(6) NOT NULL comment 'Course number',
 credit DECIMAL(4,1) not NULL comment 'achievement',
 constraint fk_score_stuNo FOREIGN KEY(studentNo) REFERENCES tb_student(studentNo),  
 constraint fk_score_courNo FOREIGN KEY(courseNo) REFERENCES tb_course(courseNo),
 constraint pk_score PRIMARY KEY(studentNo,courseNo)
) engine=InnoDB default charset=gb2312;

Note: the primary key corresponding to the foreign key constraint (only the primary key in the table can be used)

2. Integrity constraint update

Delete in table tb_ Foreign key constraint fk defined in score_ score_ stuNo
alter table tb_score drop foreign key fk_score_stuNo;

Delete in table tb_ Primary key constraint defined in student.
Alter table tb_student drop primary key;

Add the primary key constraint, and use the alter table statement in tb_student adds the primary key to studentNo again.
Alter table tb_student add primary key(studentNo);

Add foreign key constraints and use the alter table statement in TB_ Re add foreign keys to studentNo in the score table,
The primary key of is TB_ The studentNo of the student table and the foreign key name is fk_score_stuNo.
ALTER TABLE tb_score ADD CONSTRAINT fk_score_stuNo FOREIGN KEY(studentNo) REFERENCES tb_student(studentNo);

3. Skill development

3.1 constraint type

Constraint mainly completes the inspection of data to ensure the integrity of database data; If there is interdependent data, ensure that the data is not deleted.
There are five common types of constraints:
1. not null: Non empty constraint, specifying that a column is not empty
2. unique:  Unique constraint, specifying that the data of a column or a combination of columns cannot be repeated
3. primary key: Primary key constraint, which specifies that the data of a column or a combination of columns is not empty and unique
4. foreign key: Foreign key to specify that the record in this column belongs to one record in the main table and refers to another data
5. check: Check, specify an expression to verify the specified data

Everyone likes, collects, pays attention to and comments 👇🏻👇🏻👇🏻 WeChat official account 👇🏻👇🏻👇🏻