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,'','');

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

巴特西

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

最新文章

热门文章