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 NULL5 ）;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; 23 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.DEPTNAME2 FROM EMPLOYEE3 INNER JOIN DEPT4 ON EMPLOYEE.DEPTID = DEPT.ID;3.使用别名1 SELECT EMPNAME AS "员工姓名", D.DEPTNAME AS "部门" -- 注意这里的D2 FROM EMPLOYEE AS E3 INNER JOIN DEPT AS D4 ON E.DEPTID = D.ID;4.左外连接查询，左边的数据一定会完全显示。查询时先写左表首先我们改造一下副表UPDATE EMPLOYEE SET DEPTID = NULL WHEREID= 4; 如果是内连接查询则显示如果左连接查询1SELECTD.DEPTNAME, E.EMPNAME2FROM DEPT AS D -- 左表3LEFT OUTER JOIN EMPLOYEE AS E -- 右表4ON D.ID = E.DEPTID; 右连接其实和左连接一样，只是查询表的位置不同，下面是右连接，显示的和左连接的相同1SELECT D.DEPTNAME,E.EMPNAME2 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 ）; 67 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 P15 RIGHT JOIN PERSON AS B16 ON P.ID = B.BOSSID; 显示结果注意上面创建表的BOSSID的结构。本文永久更新链接地址