MYSQL基礎操作之數據約束與關聯查詢

 一、MYSQL約束

1.預設值約束，當欄位沒有插入值的時候，mysql自動給該欄位分配預設值。
預設值的欄位允許為空。
對預設值欄位也可以插入null。

1 CREATE TABLE STUDENT(
2     ID INT,
3     NAME VARCHAR(20),
4     ADDRESS VARCHAR(20) DEFAULT '京口區'
5 );
6 INSERT INTO STUDENT (ID, NAME ) VALUES (1,'張三');
7 INSERT INTO STUDENT (ID , NAME,ADDRESS ) VALUES (2,'李四' ,NULL);

2.非空約束

1 CREATE TABLE STUDENT(
2     ID INT,
3     NAME VARCHAR(20),
4     GENDER VARCHAR(2) NOT NULL
5 );

2.1非空欄位必須賦值(錯誤顯示)

INSERT INTO STUDENT (ID , NAME) VALUES(1, '李四');

2.2不能插入null（錯誤顯示）

INSERT INTO STUDENT (ID , NAME) VALUES (1, '張三' ,NULL);

3.唯一約束

1 CREATE TABLE STUDENT (
2     ID INT UNIQUE,
3     NAME VARCHAR(20)
4 );
5 INSERT INTO STUDENT (ID , NAME) VALUES (1, '張三');

下麵語句執行發生錯誤

INSERT INTO STUDENT (ID , NAME) VALUES (1, '李四');

錯誤提示

4.主鍵約束(非空+唯一)，通常每張表都會設置一個主鍵欄位。用於標註表記錄的唯一性。
　主鍵一般都是沒有業務含義的。

1 CREATE TABLE STUDENT(
2     ID INT PRIMARY KEY,
3     NAME VARCHAR(20)
4 );
5 
6 INSERT INTO STUDENT (ID , NAME) VALUES (1, '張三');

下麵語句執行顯示錯誤

INSERT INTO STUDENT (ID , NAME ) VALUES (NULL , '李四');

錯誤提示

下麵錯誤也是一種錯誤

INSERT INTO STUDENT (ID ,NAME ) VALUES (1, '李四');

5.自增長約束
可以自動的遞增

 CREATE TABLE STUDENT(
    ID INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20)
);
INSERT INTO STUDENT (NAME) VALUES ('張三');
INSERT INTO STUDENT (NAME) VALUES ('李四');

整表數據刪除，不影響自增的刪除語句

DELETE FROM STUDENT;

刪除表之後再次（不用再創建表），添加插入語句如圖所示

刪除表中所有數據,刪除外鍵也是用這個語句。

DELETE FROM STUDENT;

再次插入數據

6.外鍵約束

被約束的表為副表，外鍵設置在副表上

eg:創建員工表（副表），創建部門表（主表）,先創建主表，再創建副表

CREATE TABLE DEPT(
    ID INT PRIMARY KEY,
    DEPTID VARCHAR(20)
);

INSERT INTO DEPT (ID , DEPTNAME) VALUES (1, '軟體設計部門');
INSERT INTO DEPT (ID , DEPTNAME) VALUES (2, '人事部');
INSERT INTO DEPT (ID , DEPTNAME) VALUES (3, '財務部');
INSERT INTO DEPT (ID , DEPTNAME) VALUES (4, '運營部');

CREATE TABLE EMPLOYEE(
    ID INT PRIMARY KEY AUTO_INCREMENT,
    EMPNAME VARCHAR(20),
    DEPTID INT,
    CONSTRAINT EMPLOYEE_DEPT_FK FOREIGN KEY (DEPTID) REFERENCES DEPT(ID)
);

INSERT INTO EMPLOYEE (EMPNAME , DEPTID) VALUES ('張三' , 1);
INSERT INTO EMPLOYEE (EMPNAME , DEPTID) VALUES ('李四' , 2);
INSERT INTO EMPLOYEE (EMPNAME , DEPTID) VALUES ('王五' , 3);
INSERT INTO EMPLOYEE (EMPNAME , DEPTID) VALUES ('趙六' , 4);

查詢兩張表

7.下麵進行級聯關聯，意味著，當主表修改時，副表也修改

修改以上表的創建語句，並重新插入新的語句

 1 DROP TABLE EMPLOYEE;
 2 
 3 CREATE TABLE EMPLOYEE(
 4     ID INT PRIMARY KEY AUTO_INCREMENT,
 5     EMPNAME VARCHAR(20),
 6     DEPTID INT,
 7     CONSTRAINT EMPLOYEE_DEPT_FK FOREIGN KEY (DEPTID) REFERENCES DEPT(ID) ON UPDATE CASCADE ON DELETE CASCADE
 8 );
 9 
10 INSERT INTO EMPLOYEE (EMPNAME , DEPTID) VALUES ('張三' , 1);
11 INSERT INTO EMPLOYEE (EMPNAME , DEPTID) VALUES ('李四' , 2);
12 INSERT INTO EMPLOYEE (EMPNAME , DEPTID) VALUES ('王五' , 3);
13 INSERT INTO EMPLOYEE (EMPNAME , DEPTID) VALUES ('趙六' , 4);

查詢如圖所示

修改主表的一條記錄

UPDATE DEPT SET ID=5 WHERE ID=4;

主表的運行結果

副表的運行結果

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

二、關聯查詢

1.交叉查詢

SELECT * FROM EMPLOYEE,DEPT;

2.內連接查詢

多表查詢規則：1）查詢哪些表    2）確定哪些欄位    3）表與表之間的查詢條件（連接表的數量-1）

1 SELECT EMPNAME , dept.DEPTNAME
2         FROM EMPLOYEE
3         INNER JOIN DEPT
4         ON EMPLOYEE.DEPTID = DEPT.ID;

3.使用別名

1 SELECT EMPNAME AS '員工姓名', D.DEPTNAME AS '部門' -- 註意這裡的D
2                 FROM EMPLOYEE AS E
3                 INNER JOIN DEPT AS D
4                 ON E.DEPTID = D.ID;

4.左外連接查詢，左邊的數據一定會完全顯示。查詢時先寫左表

首先我們改造一下副表

UPDATE EMPLOYEE SET DEPTID = NULL WHERE  ID= 4;

如果是內連接查詢則顯示

如果左連接查詢

1  SELECT    D.DEPTNAME, E.EMPNAME
2                  FROM DEPT AS D -- 左表
3                  LEFT OUTER JOIN EMPLOYEE AS E -- 右表
4                  ON D.ID = E.DEPTID;

右連接其實和左連接一樣，只是查詢表的位置不同，下麵是右連接，顯示的和左連接的相同

1  SELECT D.DEPTNAME,E.EMPNAME
2                 FROM EMPLOYEE AS E-- 左表
3                 RIGHT JOIN DEPT AS D -- 右表
4                 ON E.ID = D.ID;

5.自連接查詢，自連接查詢一般應用於表數據為樹狀結構。

首先我們創建表並查詢

 1 CREATE TABLE PERSON(
 2     ID INT PRIMARY KEY AUTO_INCREMENT,
 3     NAME VARCHAR(20),
 4     BOSSID INT
 5 );
 6 
 7 INSERT INTO PERSON (NAME,BOSSID) VALUES ('張三',NULL);
 8 INSERT INTO PERSON (NAME,BOSSID) VALUES ('李四',1);
 9 INSERT INTO PERSON (NAME,BOSSID) VALUES ('王五',2);
10 INSERT INTO PERSON (NAME,BOSSID) VALUES ('趙六',3);
11 INSERT INTO PERSON (NAME,BOSSID) VALUES ('李七',3);
12 
13 SELECT P.NAME AS '老闆' ,B.NAME AS '員工'
14     FROM PERSON AS P
15     RIGHT JOIN PERSON AS B
16     ON P.ID = B.BOSSID;

顯示結果

註意上面創建表的BOSSID的結構。