Explication détaillée des contraintes, requêtes multi-tables et sous-requêtes dans MySQL

1. Contraintes de clé primaire des contraintes

Contraintes : des contraintes sont ajoutées aux colonnes pour contraindre les colonnes.

1. Contrainte de clé primaire (identification unique) : non nulle, unique, référencée

Lorsqu'une colonne de la table est désignée comme clé primaire, la classe ne peut pas être vide ni avoir de doublon Il existe

deux façons de spécifier la clé primaire lors de la création d'une table :

CREATE TABLE stu(
    sid  CHAR(6) PRIMARY KEY,
    sname  VARCHAR(20),
    age  INT,
    sex  VARCHEAR(10)
);

CREATE TABLE stu(
    sid  CHAR(6) ,
    sname  VARCHAR(20),
    age  INT,
    sex  VARCHEAR(10),
    PRIMARY KEY(sid)
);

Spécifiez la colonne sid comme colonne de clé primaire, c'est-à-dire ajoutez une contrainte de clé primaire à. la colonne sid

Spécifier lors de la modification de la table Clé primaire :

ALTER TABLE stu ADD PRIMARY KEY(sid);

Supprimer la clé primaire :

ALTER TABLE stu DROP PRIMARY KEY;

2. 🎜>Parce que les caractéristiques de la colonne de clé primaire sont les suivantes : elle doit être unique et ne peut pas être vide, donc nous avons généralement la clé primaire spécifiée sous forme d'entier, puis sa croissance automatique est définie. Cela garantit que la colonne de clé primaire est. unique et non nul lors de l'insertion de données.

Spécifiez l'auto-incrémentation de la clé primaire lors de la création d'une table

Définissez l'auto-incrémentation de la clé primaire lors de la modification de la table :

CREATE TABLE stu(
    sid  INT PRIMARY KEY AUTO_INCREMENT,
    sname  VARCHAR(20),
    age  INT,
    sex  VARCHEAR(10)
);

Supprimez la clé primaire Auto-incrémentation de la clé lors de la modification de la table :

ALTER TABLE stu CHANGE sid sid INT AUTO_INCREMENT;

Test de l'auto-incrémentation de la clé primaire :

ALTER TABLE stu CHANGE sid sid INT ;

Contraintes non nulles

INSERT INTO stu VALUES(NULL,'zhangsan',23,'man');
INSERT INTO stu(sname,age,sex) VALUES(NULL,'zhangsan',23,'man');

Car certaines colonnes ne le peuvent pas. être défini sur des valeurs nulles, vous pouvez ajouter des contraintes non nulles.

Par exemple :

définit une contrainte non nulle sur la colonne sname.

CREATE TABLE stu (
　　 sid INT PRIMARY KEY AUTO_INCREMENT,
　　 sname  VARCHAR(20) NOT NULL,
　　 age   INT,
　　 sex  VARCHAR(10)
);

4. Contraintes uniques

Certaines colonnes du garage ne peuvent pas avoir de valeurs répétées, vous pouvez donc ajouter des contraintes uniques aux colonnes.

Par exemple :

2. Modèle conceptuel

CREATE TABLE stu (
　　 sid INT PRIMARY KEY AUTO_INCREMENT,
　　 sname  VARCHAR(20) NOT NULL UNIQUE,
　　 age   INT,
　　 sex  VARCHAR(10)
);

1. Modèle objet : en Java c'est un domaine, par exemple : Utilisateur, Étudiant.

2. Modèle relationnel : les tables de la base de données sont 1-vers-plusieurs, 1-vers-1 et plusieurs-vers-plusieurs.

3. Contraintes de clé étrangère

La clé étrangère doit être la valeur de la clé primaire d'une autre table (la clé étrangère doit référencer la clé primaire.)

Clés étrangères peut être répété

Les clés étrangères peuvent être vides

1. Ajouter des contraintes de clé étrangère lors de la création

2. Ajouter des contraintes de clé étrangère lors de la modification de la table :

CREATE TABLE dept (
    deptno INT PRIMARY KEY AUTO_INCREMENT,
    dname VARCHAR(50)
);
insert into dept values(10,'研发部');
insert into dept values(20,'人力部');
insert into dept values(30,'财务部');

CREATE TABLE emp (
    empno INT PRIMARY KEY AUTO_INCREMENT,
    ename VARCHAR(50),
    deptno INT,
    CONSTRAINT fk_emp_dept FOREIGN KEY(dno) REFERENCES dept(deptno) 
);

CREATE TABLE dept (
    deptno INT PRIMARY KEY AUTO_INCREMENT,
    dname VARCHAR(50)
);
INSERT INTO dept VALUES(10,'研发部');
INSERT INTO dept VALUES(20,'人力部');
INSERT INTO dept VALUES(30,'财务部');

INSERT INTO emp(empno,ename) VALUES(null,'zhangsan');
INSERT INTO emp(empno,ename,deptno) VALUES(null,'lisi',10);


INSERT INTO emp(empno,ename,deptno) VALUES(null,'zhangsan',80);
/* Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails 
(`mydb2`.`emp`, CONSTRAINT `fk_emp_dept` FOREIGN KEY (`deptno`) REFERENCES `dept` (`deptno`))
*/

IV , Modèle relationnel de base de données

ALTER TABLE emp ADD CONSTRAINT fk_emp_dept FOREIGN KEY(dno) REFERNCES dept(deptno);

1. Relation un-à-un

Il est spécial d'établir une relation un-à-un dans une table. nécessaire de faire de la clé primaire d’une des tables à la fois une clé primaire et une clé étrangère.

2. Relation plusieurs-à-plusieurs

CREATE TABLE hasband (
    hid INT PRIMARY KEY AUTO_INCREMENT,
    hname VARCHAR(50)
);

CREATE TABLE wife (
    wid INT PRIMARY KEY AUTO_INCREMENT,
    wname VARCHAR(50),
    CONSTRAINT fk_wife_hasband FOREIGN KEY (wid)  REFERENCES hasband(hid) 
);

Pour établir une relation plusieurs-à-plusieurs dans une table, vous devez utiliser une table intermédiaire, c'est-à-dire qu'il vous en faut trois tables et deux clés étrangères dans la table intermédiaire. Référencez respectivement les clés primaires des deux autres tables.

Établir une relation dans la table intermédiaire, telle que :

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) 
);

5. Requête multi-tables

INSERT INTO stu_tea VALUES(5,1);
INSERT INTO stu_tea VALUES(2,2);
INSERT INTO stu_tea VALUES(3,2);

1. Fusionner l'ensemble de résultats

Requête de jointure

Sous-requête

2 Requête de résultat de fusion

Nécessite que le type et le nombre de colonnes dans l'ensemble de résultats soient fusionnés. les tables sont les mêmes

UNION, supprimez les lignes en double

UNION ALL, ne supprimez pas les lignes en double

Rejoindre la requête

①Catégorie

SELECT * FROM 表1名
UNION ALL
SELECT * FROM 表2名;

🎜>

Connexion

Jointure externe

Jointure externe gauche

Jointure externe droite

Jointure externe complète (mysql ne prend pas en charge)

Jointure naturelle (appartient à une méthode simplifiée)

②Jointure interne

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

Utiliser le filtrage conditionnel pour supprimer les informations inutiles du produit cartésien.

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

SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno;
SELECT e.ename, e.sal, d.dname FROM emp e, dept d WHERE e.deptno=d.deptno;

Naturel : SELECT * FROM table 1 alias 1 ; NATURAL JOIN Table 2 Alias ​​​​2 ;

SELECT e.ename, e.sal , d.dname  FROM emp e INNER JOIN dept d ON  e.deptno=d.deptno;

Tous les enregistrements interrogés par la jointure interne remplissent les conditions

③Outer Join

SELECT e.ename, e.sal , d.dname  FROM emp e NATURAL JOIN dept d;

Externe gauche : SELECT * FROM Table 1 Alias ​​​​1 LEFT OUTER JOIN Table 2 Alias ​​​​2 ON Alias ​​​​1.xx = Alias ​​​​2.xx

Les enregistrements de la table de gauche seront interrogés, qu'ils remplissent ou non les conditions, tandis que les enregistrements de la table de droite ne peuvent être récupérés que s'ils remplissent les conditions. Les enregistrements du tableau de gauche qui ne remplissent pas les conditions seront nuls dans le tableau de droite.

Extérieur gauche naturel : SELECT * FROM Table 1 Alias ​​​​1 NATURAL LEFT OUTER JOIN Table 2 Alias ​​​​2 ON Alias ​​​​1.xx = Alias ​​​​2.xx

Extérieur droit : SELECT * FROM Table 1 Alias ​​​​1 RIGHT OUTER JOIN Table 2 Alias ​​​​2 ON Alias ​​​​1.xx = Alias ​​​​2.xx

SELECT e.ename, e.sal , IFNULL(d.dname,'无部门') AS dname  FROM emp e LEFT OUTER JOIN dept d ON  e.deptno=d.deptno;

Les enregistrements à droite; Il sera demandé dans la table s'ils remplissent les conditions ou non, tandis que les enregistrements de la table de gauche ne pourront être récupérés que s'ils remplissent les conditions. Les enregistrements du tableau de droite qui ne remplissent pas les conditions seront nuls dans le tableau de gauche.

Extérieur droit naturel : SELECT * FROM Table 1 Alias ​​​​1 NATURAL RIGHT OUTER JOIN Table 2 Alias ​​​​2 ON Alias ​​​​1.xx = Alias ​​​​2.xx

Lien complet : vous pouvez utiliser UNION pour terminer Entièrement connecté.

4. Sous-requête

Il y a une requête dans la requête (vérifiez le nombre de mots-clés sélectionnés)

SELECT e.ename, e.sal , d.dname 
FROM emp e LEFT OUTER JOIN dept d 
ON e.deptno=d.deptno
UNION
SELECT e.ename, e.sal , d.dname 
FROM emp e RIGHT OUTER JOIN dept d 
ON e.deptno=d.deptno;

①La position où elle apparaît

Après WHERE Existe en tant que condition

Existe sous forme de tableau après FROM (plusieurs lignes et plusieurs colonnes)

②Condition

Une seule ligne et une seule colonne : SELECT * FROM table 1 alias 1 WHERE colonne 1 [=, >, 95ec6993dc754240360e28e0de8de30a=, <=, !=] (SELECT colonne FROM table 2 alias 2 WHERE condition) ; : SELECT * FROM table 1 alias 1 WHERE Colonne 1 [IN,ALL,ANY] (SELECT colonne FROM table 2 alias 2 WHERE condition

Une seule ligne et plusieurs colonnes : SELECT * FROM table 1) ; alias 1 WHERE (colonne 1, colonne 2) IN (SELECT colonne 1, colonne 2 FROM table 2 alias 2 WHERE condition

SELECT * FROM emp WHERE sal=(SELECT MAX(sal) FROM emp);

Plusieurs lignes et colonnes : SELECT * FROM table 1 alias 1, ( SELECT...) table 2 alias 2 condition WHERE

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!