SQL表连接查询

USE h
/*默认值*/
CREATE TABLE emp(
        id INT,
        ename VARCHAR(5),
        address VARCHAR(20) DEFAULT '山东淄博'(默认值）
        )
 INSERT INTO emp(id,ename) VALUES(1,'张三');
 INSERT INTO emp VALUES(2,'李四','山东济南');
  INSERT INTO emp VALUES(3,'小红','');
  SELECT * FROM emp;
  
  
  /*不能为空值*/
 CREATE TABLE emp02(
        id INT NOT NULL,（不能为空值,前面不加is）
        ename VARCHAR (5),
        sex VARCHAR (5)
        )
INSERT INTO emp02 VALUES(1,'王瑞昌','男');
INSERT INTO emp02 VALUES(1,'王昌','男');
SELECT * FROM emp02;    

 
/*唯一约束，可以插入重复null，但别的值不能重复*/
CREATE TABLE emp03(
        /*非空（null）+唯一*/
        id INT NOT NULL UNIQUE,（唯一约束），
        ename VARCHAR(5)
        )
        INSERT INTO emp03 VALUES(1,'小红');
        /*id不能重复*/
         INSERT INTO emp03 VALUES(1,'小名');
         SELECT * FROM emp03;
         
         
         /*主键*/
      CREATE TABLE emp04(
        id INT PRIMARY KEY,
        ename VARCHAR (5)
        )   
        SELECT * FROM emp04; 
       INSERT INTO emp04 VALUES(1,'小红'); 
       /*不能插null*/
       INSERT INTO emp04 VALUES(NULL,'小兰');
       /*id不能重复*/
       INSERT INTO emp04 VALUES(1,'小兰');
       /*正确情况*/
       INSERT INTO emp04 VALUES(2,'小兰');
       
       
       /*自增长（前提是主键）*/
       CREATE TABLE emp05(
        id INT PRIMARY KEY AUTO_INCREMENT,(自增长)
        ename VARCHAR (5)
        ) 
        /*id自增长*/
        INSERT INTO emp05(ename) VALUES('小兰');
         INSERT INTO emp05(ename) VALUES('小红');
          INSERT INTO emp05(ename) VALUES('小明');
          SELECT * FROM emp05;
          /*删除id2后再插入2，id从3开始*/
          DELETE FROM emp05 WHERE id='2'
          
          
          /*前面id固定4位，自动补0*/
        CREATE TABLE emp06(
        id INT(4) ZEROFILL(自动补0) PRIMARY KEY AUTO_INCREMENT,
        ename VARCHAR (5)
        ) 
        SELECT * FROM emp06;
        INSERT INTO emp06(ename) VALUES('小兰');
         INSERT INTO emp06(ename) VALUES('小红');
          INSERT INTO emp06(ename) VALUES('小明');
          
          
        /*数据删除*/
        DELETE FROM emp05 WHERE id='2'
        
        /*外键(仿乱写)*/
        /*主表*/
        CREATE TABLE dept(
               id INT PRIMARY KEY,
               ename VARCHAR (10)
        )
        /*附表*/
        CREATE TABLE employee(
               id INT PRIMARY KEY,
               ename VARCHAR (5),
               deptid INT,   
                /*外键写法*/          
               CONSTRAINT enmployee_dept_fk FOREIGN KEY(deptid) REFERENCES dept(id)            
        )
      INSERT INTO dept VALUES(20,'秘书部');  
      INSERT INTO dept VALUES(30,'经理部');  
      /*数据修改*/
      UPDATE dept SET id=50 WHERE id=20;
      SELECT * FROM dept;
      INSERT INTO employee VALUES(1,'张三',20);
      /*前面表没有50所以失败*/
      INSERT INTO employee VALUES(2,'李三',50); 
      /*前表有30所以成功*/
      INSERT INTO employee VALUES(2,'李三',30);
     
      SELECT * FROM employee;
      /*数据修改*/
      UPDATE employee SET deptid=30 WHERE id=1;
      
      /*表连接*/
      /*交叉连接(完全连接)如果不加条件*/
      SELECT * FROM zj1 INNER JOIN fb1;
      /*内连接(有条件on)*/
      SELECT * FROM zj1 INNER JOIN fb1 ON zj1.`protype_id`=fb1.`protype_id`;
      /*左外连接*/
      SELECT * FROM zj1 LEFT JOIN fb1 ON zj1.`protype_id`=fb1.`protype_id`;
      /*右外连接*/
      SELECT * FROM zj1 RIGHT JOIN fb1 ON zj1.`protype_id`=fb1.`protype_id`;
      /*找出索尼4g手机所属类别*/
      SELECT zj1.protype_name FROM fb1 INNER JOIN zj1 ON zj1.`protype_id`=fb1.`protype_id`
      WHERE protype_name LIKE '%索尼%' AND protype_name LIKE '%4G%';
       /*查找属于手机数码的商品*/
      SELECT *FROM fb1 INNER JOIN zj1 ON zj1.`protype_id`=fb1.`protype_id`
      WHERE protype_name='手机数码';
      /*子查询*/
       /*查找属于手机数码的商品*/
      SELECT * FROM fb1 WHERE protype_id=(
      SELECT protype_id FROM zj1 
      WHERE protype_name='手机数码'
      )
      /*找出索尼4g手机所属类别 (=可以用in)*/
      SELECT * FROM zj1 WHERE protype_id=(
      SELECT protype_id FROM fb1
      WHERE pro_name LIKE '%索尼%' AND pro_name LIKE '%4G%'
      )
      /*    */
      SELECT pro_name FROM fb1 WHERE protype_id IN (1,2);
      
      /*联合查询*/
      SELECT protype_id,protype_name FROM zj1
      UNION
      SELECT pro_id,pro_name FROM fb1;