sql中的连接表达式，视图，事务等。

给定两张表

表A

create table A(ID int,name varchar(20),description varchar(20));
insert into A values(1,'N1','AD1');
insert into A values(2,'N2','AD2');
mysql> SELECT * FROM a;
+----+------+-------------+
| ID | name | description |
+----+------+-------------+
|  1 | N1   | AD1         |
|  2 | N2   | AD2         |
+----+------+-------------+

表B

create table B(ID int,name varchar(20),age int);
insert into B values(1,'N1',20);
insert into B values(3,'N2',21);

mysql> select * from B;
+----+------+-----+
| ID | name | age |
+----+------+-----+
| 1 | N1 | 20 |
| 3 | N2 | 21 |
+----+------+-----+

一：连接

1：自然连接

在共同的属性上（ID,name）取相同的值的元组（行）构成的模式（关系，表）。

mysql> select * from A natural join B;
+----+------+-------------+-----+
| ID | name | description | age |
+----+------+-------------+-----+
|  1 | N1   | AD1         |  20 |
+----+------+-------------+-----+

2: join....on ，指定条件的笛卡尔连接，在on上指定查询条件

mysql> select * from A join B on A.name=B.name;        //在属性name上值相等就行， 相同属性会出现多次，可以在*上代替属性集合
+----+------+-------------+----+------+-----+
| ID | name | description | ID | name | age |
+----+------+-------------+----+------+-----+
|  1 | N1   | AD1         |  1 | N1   |  20 |
|  2 | N2   | AD2         |  3 | N2   |  21 |
+----+------+-------------+----+------+-----+

3：左外自然连接，在连接natural join 左边的表的元组全部出现，右边表与左边表在相同属性上相等时，会出现。属性不同时，右边表的属性为null。

出现左表的属性和右表中与左表不同的属性。

mysql> select * from A natural left outer join B;
+----+------+-------------+------+
| ID | name | description | age  |
+----+------+-------------+------+
|  1 | N1   | AD1         |   20 |
|  2 | N2   | AD2         | NULL |
+----+------+-------------+------+

4：左外连接。自然连接会去除相同的属性

mysql> select * from A left outer join B on A.ID=B.ID;
+----+------+-------------+------+------+------+
| ID | name | description | ID   | name | age  |
+----+------+-------------+------+------+------+
|  1 | N1   | AD1         |    1 | N1   |   20 |
|  2 | N2   | AD2         | NULL | NULL | NULL |
+----+------+-------------+------+------+------+

5:内连接，在相同的属性上相同的值，两张表的属性都出现。

mysql> select * from A INNER join B on A.ID=B.ID;
+----+------+-------------+----+------+-----+
| ID | name | description | ID | name | age |
+----+------+-------------+----+------+-----+
|  1 | N1   | AD1         |  1 | N1   |  20 |
+----+------+-------------+----+------+-----+

二：视图

1：创建视图C

mysql> create view C as select * from A natural left outer join B;

2:使用视图

mysql> select * from C;
+----+------+-------------+------+
| ID | name | description | age  |
+----+------+-------------+------+
|  1 | N1   | AD1         |   20 |
|  2 | N2   | AD2         | NULL |
+----+------+-------------+------+

3：显式的指定视图的属性名

create view D(ID,D_name) as select ID,name from A natural left outer join B;

mysql> select * from D;
+----+--------+
| ID | D_name |
+----+--------+
| 1 | N1 |
| 2 | N2 |
+----+--------+

三：事务

1：set autocommit=0; 设置mysql手动提交。set autocommit=1; 自动提交commit. 这样就相当于每次更新之后立刻提交

2：开启事务，插入，回滚。

mysql> set autocommit=0;
mysql> start transaction;  //开启事务
mysql> insert into A values(3,'N3','AD3');             //插入
mysql> select * from A;            
+----+------+-------------+
| ID | name | description |
+----+------+-------------+
|  1 | N1   | AD1         |
|  2 | N2   | AD2         |
|  3 | N3   | AD3         |
+----+------+-------------+
mysql> rollback;    //回滚
mysql> select * from A;
+----+------+-------------+
| ID | name | description |
+----+------+-------------+
|  1 | N1   | AD1         |
|  2 | N2   | AD2         |
+----+------+-------------+

3：开启事务，插入，提交，回滚

mysql> start transaction;
Query OK, 0 rows affected

mysql> insert into A values(3,'N3','AD3');
Query OK, 1 row affected

mysql> select * from A;
+----+------+-------------+
| ID | name | description |
+----+------+-------------+
|  1 | N1   | AD1         |
|  2 | N2   | AD2         |
|  3 | N3   | AD3         |
+----+------+-------------+
3 rows in set

mysql> commit work;
Query OK, 0 rows affected

mysql> select * from A;
+----+------+-------------+
| ID | name | description |
+----+------+-------------+
|  1 | N1   | AD1         |
|  2 | N2   | AD2         |
|  3 | N3   | AD3         |
+----+------+-------------+
3 rows in set

mysql> rollback;         //回滚无效
Query OK, 0 rows affected

mysql> select * from A;
+----+------+-------------+
| ID | name | description |
+----+------+-------------+
|  1 | N1   | AD1         |
|  2 | N2   | AD2         |
|  3 | N3   | AD3         |
+----+------+-------------+
3 rows in set

四：完整性

1：为id添加唯一约束

alter table A modify id int unique;

2：为属性添加约束，例如约束id<4 （mysql中不能使用check,需要为改约束定义触发器）。 https://www.cnblogs.com/duodushu/p/5446384.html

mysql> CREATE TRIGGER TestField1_BeforeInsert BEFORE INSERT ON A  
FOR EACH ROW  
BEGIN  
    IF NEW.ID > 4 THEN  
        SET NEW.ID = 0;  
    END IF;  
END;
Query OK, 0 rows affected

mysql> insert into A values(5,'5','5');
Query OK, 1 row affected

mysql> select * from A;
+----+------+-------------+
| ID | name | description |
+----+------+-------------+
|  1 | N1   | AD1         |
|  2 | N2   | AD2         |
|  3 | N3   | AD3         |
|  0 | 5    | 5           |
+----+------+-------------+
4 rows in set

五：索引

1：创建索引 create index AindexId on A(ID);

mysql> create index AindexId on A(ID);
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0
mysql> select * from A where ID=1;
+----+------+-------------+
| ID | name | description |
+----+------+-------------+
|  1 | N1   | AD1         |
+----+------+-------------+
1 row in set