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