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

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 '%手机%'

 

//查出所有产品类型为手机数码的产品：

SELECT * FROM PRODUCT AS A INNER JOIN PRODUCT_TYPE AS B ON A.PROTYPE_ID=B.PROTYPE_ID WHERE B.PROTYPE_NAME='手机数码'

 

//子查询：一个查询作为另一个查询的条件！

SELECT * FROM PRODUCT WHERE PROTYPE_ID IN (SELECT PROTYPE_ID FROM PRODUCT_TYPE WHERE PROTYPE_NAME='手机数码')

 

//如果要对整个联合结果进行排序或LIMIT，则应该对各自的SELECT语句加括号：

（SELECT语句1）

UNION

（SELECT语句2）

ORDER BY.....

LIMIT....

 

=号：一个值！

IN：一个范围！