2018-07-11CROSS JOIN+INNER JOIN+LEFT JOIN+RIGHT JOIN-白红宇

2018-07-11CROSS JOIN+INNER JOIN+LEFT JOIN+RIGHT JOIN

阅读量：4950 次

发布时间：2019-06-11

本文共 4519 字，大约阅读时间需要 15 分钟。

Date和DateTime：数据要加单引号！

数据库插入日期不能用varchar型，要用Date/DateTime类型！

数据约束：对用户操作表的数据进行约束！

当用户对使用默认值的字段不插入值的时候，就使用默认值！

//DEFAULT‘山东淄博’

CREATE TABLE USER(

UID INT,

UNAME VARCHAR(10),

UADRESS VARCHAR(30) DEFAULT'天上人间'

)

SELECT * FROM USER

INSERT INTO USER(UID,UNAME) VALUES (001,'嫖一生')

INSERT INTO USER VALUES (002,'骚猪','东莞一霸')

//如果写成这样：INSERT INTO USER VALUES (001,'嫖一生')

//会报以下错误：

Column count doesn't match value count at row 1

//应该写成这样：

INSERT INTO USER(UID,UNAME) VALUES (001,'嫖一生')

① 对默认值字段插入NULL是可以的（可以DEFAULT'NULL'，也可以DEFAULT'天上人间'时INSERT INTO一个NULL值）！

② 对默认值字段可以插入非NULL！

非空约束：NOT NULL

CREATE TABLE USER(

UID INT NOT NULL,

UNAME VARCHAR(10),

UADRESS VARCHAR(30) DEFAULT'天上人间'

)

//设置UID插入时不能为NULL值！

如果按照这样插入值的话：

INSERT INTO USER(UNAME,UADRESS) VALUES('咣咣','山东济南');

会报以下错：

Field 'UID' doesn't have a default value！

INSERT INTO USER VALUES(NULL,'哈哈','山东青岛');

//会报错：Column 'UID' cannot be null！

唯一约束：对字段的值不能重复！

CREATE TABLE USER(

UID INT UNIQUE,

UNAME VARCHAR(10),

UADRESS VARCHAR(30) DEFAULT'天上人间'

)

//创建表指定UID字段唯一约束！

//以下插入一条数据：

INSERT INTO USER VALUES(001,'张三','美国拉斯维加斯');

//重复插入第二遍的时候，提示：

Duplicate entry '1' for key 'UID'（重复为UID字段插入1，代表UID设置了UNIQUE约束）！

主键：非空+唯一

① 通常情况下，每张表都会设置一个主键字段，用于标记表中的每条记录的唯一性！

② 建议不要选择表的包含业务含义的字段作为主键，建议给每张表独立设计一个非业务含义的ID字段！

//一个表内只能有一个主键！

CREATE TABLE USER(

UID INT PRIMARY KEY,

UNAME VARCHAR(10),

UADRESS VARCHAR(30) DEFAULT'天上人间'

)

//执行以下语句：

INSERT INTO USER(UNAME,UADRESS) VALUES('李四','美国洛杉矶');

//会报以下错误：

Field 'UID' doesn't have a default value！

//执行以下语句两次，第一次成功，第二次报错：

INSERT INTO USER VALUES(001,'张三','美国拉斯维加斯');

//会报以下错误：

Duplicate entry '1' for key 'PRIMARY'

标识种子+标识自增量：

自增列必须要是一个主键！

//如果执行以下代码：

CREATE TABLE USER(

UID INT AUTO_INCREMENT,

UNAME VARCHAR(10),

UADRESS VARCHAR(30) DEFAULT'天上人间'

)

//会报错，原因上面已经写了：

Incorrect table definition; there can be only one auto column and it must be defined as a key！

//同一个INSERT INTO执行3遍：

INSERT INTO USER(UNAME,UADRESS) VALUES('李四','美国洛杉矶');

会产生以下效果（按顺序自动加入UID）：

//如果DELETE这个ID是3的，然后再INSERT INTO以下，数据会从4开始！

ZEROFILL：用0填充，这样的话INT数据类型必须要有一个精度！

//执行以下建表语句：

CREATE TABLE USER(

UID INT(3) ZEROFILL,

UNAME VARCHAR(10),

UADRESS VARCHAR(30) DEFAULT'天上人间'

)

//执行INSERT INTO语句，出现以下效果：

INSERT INTO USER VALUES(1,'李四','美国洛杉矶');

建表ID建议定义成VARCHAR型！

//不能影响自增长约束

DELETE FROM USER;

//可以影响自增长约束

TRUNCATE TABLE USER;

外键：约束两个表的数据，说白了就是别的表的主键！

解决数据冗余高问题：独立出一张表！

//外键的前提：必须是别的表的主键，才能拿过来作为自己的外键！

//这个表的外键，与依赖表的主键，数据类型必须一致，名字建议也一致！

//建立部门表：

CREATE TABLE DEPT(

DEPTID INT PRIMARY KEY,

DEPTNAME VARCHAR(10)

)

//建立员工表，并设置部门表的主键为自己表的外键：

CREATE TABLE EMPER(

EMPID INT,

EMPNAME VARCHAR(10),

DEPTID INT,

CONSTRAINT EMPER_DEPT_FK FOREIGN KEY (DEPTID) REFERENCES DEPT(DEPTID)

)

//为部门表插入数据：

INSERT INTO DEPT VALUES(1,'开发部');

INSERT INTO DEPT VALUES(2,'实施部');

INSERT INTO DEPT VALUES(3,'运维部');

//为员工表插入数据：

INSERT INTO EMPER VALUES(123,'张三',1);

INSERT INTO EMPER VALUES(456,'李四',2);

INSERT INTO EMPER VALUES(789,'王五',3);

//检查一下是否正确：

SELECT * FROM DEPT

SELECT * FROM EMPER

//如果添加一个DEPT表内没有的DEPTID给EMPER：

INSERT INTO EMPER VALUES(321,'赵六',4);

//会报以下错误：

Cannot add or update a child row: a foreign key constraint fails (`testa`.`emper`, CONSTRAINT `EMPER_DEPT_FK` FOREIGN KEY (`DEPTID`) REFERENCES `dept` (`DEPTID`))

//绕过EMPER表，修改DEPT表可以成功：

UPDATE DEPT SET DEPTNAME ='运营部' WHERE DEPTID=3

//绕过EMPER表，删除DEPT表的数据：

DELETE FROM DEPT WHERE DEPTID=2

//会报以下错误：

Cannot delete or update a parent row: a foreign key constraint fails (`testa`.`emper`, CONSTRAINT `EMPER_DEPT_FK` FOREIGN KEY (`DEPTID`) REFERENCES `dept` (`DEPTID`))

被约束的表称为副表，约束别人的表称为主表，外键设置在副表上的！

主表的参考字段通用为主键！

添加数据：先添加主表，再添加副表

修改数据：先修改副表，再修改主表（针对于主键/外键字段）

删除数据：先删除副表，再删除主表

CROSS JOIN：SELECT * FROM PRODUCT AS A CROSS JOIN PRODUCT_TYPE AS B

INNER JOIN：SELECT * FROM PRODUCT AS A INNER JOIN PRODUCT_TYPE AS B ON A.PROTYPE_ID=B.PROTYPE_ID

LEFT JOIN：SELECT * FROM PRODUCT AS A LEFT JOIN PRODUCT_TYPE AS B ON A.PROTYPE_ID=B.PROTYPE_ID

RIGHT JOIN：SELECT * FROM PRODUCT AS A RIGHT JOIN PRODUCT_TYPE AS B ON A.PROTYPE_ID=B.PROTYPE_ID

MYSQL内不支持FULL JOIN！

//查出索尼4G手机的类别：

SELECT * FROM PRODUCT AS A INNER JOIN PRODUCT_TYPE AS B ON A.PROTYPE_ID=B.PROTYPE_ID WHERE A.PINPAI='索尼' AND A.PRO_NAME LIKE '%手机%'