mariadb数据库（3）连接查询，视图，事务，索引

--创建学生表
create table students (
id int unsigned not null auto_increment primary key,
name varchar(20) default ‘‘,
age tinyint unsigned default 0,
high decimal(5,2),
gender enum(‘男‘, ‘女‘, ‘中性‘, ‘保密‘) default ‘保密‘,
cls_id int unsigned default 0,
is_delete bit default 0
);

 

--创建班级表
create table classes(
id int unsigned auto_increment primary key not null,
name varchar(20) not null
);

--往students表里插入数据
insert into students values
(0,‘小明‘,18,180.00,2,1,0),
(0,‘小月月‘,19,180.00,2,2,0),
(0,‘彭于晏‘,28,185.00,1,1,0),
(0,‘刘德华‘,58,175.00,1,2,0),
(0,‘黄蓉‘,108,160.00,2,1,0),
(0,‘凤姐‘,44,150.00,4,2,1),
(0,‘王祖贤‘,52,170.00,2,1,1),
(0,‘周杰伦儿‘,34,null,1,1,0),
(0,‘程坤‘,44,181.00,1,2,0),
(0,‘和珅‘,55,166.00,2,2,0),
(0,‘刘亦菲‘,29,162.00,3,3,0),
(0,‘金星‘,45,180.00,2,4,0),
(0,‘静香‘,18,170.00,1,4,0),
(0,‘郭静‘,22,167.00,2,5,0),
(0,‘周杰‘,33,178.00,1,1,0),
(0,‘钱小豪‘,56,178.00,1,1,0),
(0,‘谢霆锋‘,38,175.00,1,1,0);


--向classes表里插入数据
insert into classes values (0, ‘云唯_01期‘),(0, ‘云唯_02期‘);

--内关联
---- 查询能够对应班级的学生以及班级信息
MariaDB [test]> select * from classes inner join students on classes.id=students.cls_id;

--查询所有students表的信息和classes中的name字段
MariaDB [test]> select students.*,classes.name from students inner join classes on classes.id=students.cls_id;

--取别名
MariaDB [test]> select s.*,c.name from students as s inner join classes as c on c.id=s.cls_id;

--依赖c.id排序（默认从小到大）
MariaDB [test]> select c.name,s.* from students as s inner join classes as c on c.id=s.cls_id order by c.id;

--左关联（left）以左边的为准，对应不上就以null代替
MariaDB [test]> select s.*,c.name from students as s left join classes as c on c.id=s.cls_id;

--与上等价，调换了classes和students的位置
MariaDB [test]> select s.*,c.name from classes as c right join students as s on c.id=s.cls_id;

--右关联（以右为准，对应不上不显示）
MariaDB [test]> select s.*,c.name from students as s right join classes as c on c.id=s.cls_id;

--自关联
--创建一个areas表
create table areas(
aid int primary key auto_increment,
name varchar(20),
pid int
);

用rz将表中的信息导入到表中，后缀最好是.sql
在数据库运行： source areas.sql (一定要在当前目录下)

MariaDB [test]> select * from areas as a inner join areas as p on a.aid=p.pid where a.name=‘山西省‘; 
+-----+-----------+------+-----+-----------+------+
| aid | name      | pid  | aid | name      | pid  |
+-----+-----------+------+-----+-----------+------+
|   4 | 山西省    | NULL |   8 | 大同市    |    4 |
|   4 | 山西省    | NULL |  12 | 太原市    |    4 |
+-----+-----------+------+-----+-----------+------+
MariaDB [test]> select a.name,p.name from areas as a inner join areas as p on a.aid=p.pid where a.name=‘山西省‘; 
+-----------+-----------+
| name      | name      |
+-----------+-----------+
| 山西省    | 大同市    |
| 山西省    | 太原市    |
+-----------+-----------+

--子查询
--标量查询
--查询山西省的所有信息
MariaDB [test]> select * from areas where pid=(select aid from areas where name=‘山西省‘);
+-----+-----------+------+
| aid | name      | pid  |
+-----+-----------+------+
|   8 | 大同市    |    4 |
|  12 | 太原市    |    4 |
+-----+-----------+------+
--查询山西省下的所属地的name
MariaDB [test]> select name from areas where pid=(select aid from areas where name=‘山西省‘) ;
+-----------+
| name      |
+-----------+
| 大同市    |
| 太原市    |
+-----------+

--创建一个简单的视图v _user
MariaDB [test]> create view v_user as select * from areas;

--当视图中存在相同的字段名时就会报错，解决方案是取别名
MariaDB [test]> create view v_city as  select a.name,p.name from areas as a inner join areas as p on a.aid=p.pid;
ERROR 1060 (42S21): Duplicate column name ‘name‘
MariaDB [test]> create view v_city as select a.name,p.name as haha from areas as a inner join areas as p on a.aid=p.pid;
Query OK, 0 rows affected (0.00 sec)
Database changed

--查看视图名
MariaDB [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| areas          |
| classes        |
| students       |
| v_city         |
| v_user         |
+----------------+

--删除视图
MariaDB [test]> drop view v_user;
Query OK, 0 rows affected (0.00 sec)

--查询视图的内容与select语句所查询的一样
MariaDB [test]> select * from v_city;
+-----------+-----------+
| name      | haha      |
+-----------+-----------+
| 北京市    | 海淀区    |
| 天津市    | 滨海区    |
| 河北省    | 沧州市    |
| 山西省    | 大同市    |
| 北京市    | 朝阳区    |
| 天津市    | 武清区    |
| 河北省    | 石家庄    |
| 山西省    | 太原市    |
| 海淀区    | 西二旗    |
| 滨海区    | 大港      |
| 沧州市    | 任丘市    |
| 大同市    | 清徐      |
| 海淀区    | 中关村    |
| 滨海区    | 汉沽      |
| 沧州市    | 河间市    |
| 大同市    | 阳曲      |
+-----------+-----------+

实例
--在创建事务之前将表创建好，不能begin后在创建
MariaDB [test]> create table xixi (id int primary key auto_increment,num int unsigned);
MariaDB [test]> insert into xixi values (0,599),(0,289),(0,0);

--事务开始
MariaDB [test]> begin;
MariaDB [test]> select * from xixi;
+----+------+
| id | num  |
+----+------+
|  1 |  599 |
|  2 |  289 |
|  3 |    0 |
+----+------+
MariaDB [test]> update xixi set num=num-299 where id=1;
MariaDB [test]> update xixi set num=num+299 where id=3;
MariaDB [test]> select * from xixi;
+----+------+
| id | num  |
+----+------+
|  1 |  300 |
|  2 |  289 |
|  3 |  299 |
+----+------+

--回滚事务
MariaDB [test]> rollback;

MariaDB [test]> select * from xixi;
+----+------+
| id | num  |
+----+------+
|  1 |  599 |
|  2 |  289 |
|  3 |    0 |
+----+------+

--提交事务
MariaDB [test]> commit;
MariaDB [test]> select * from xixi;
+----+------+
| id | num  |
+----+------+
|  1 |  300 |
|  2 |  289 |
|  3 |  299 |
+----+------+