mariadb数据库（3）连接查询，视图，事务，索引，外键（优化）
2024-09-03 01:02:53
--创建学生表
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 |
+-----------+
| 大同市 |
| 太原市 |
+-----------+
二、备份与恢复
在shell终端执行：
[root@localhost ~]# mysqldump -uroot -p0330 --databases test > test.sql #备份单个数据库
[root@localhost ~]# mysqldump -uroot -p0330 --all-database >all_databases.sql #备份全部数据库
[root@localhost ~]# mysqldump -uroot -p0330 test students > students.sql #备份test数据库下的students表
[root@localhost ~]# mysqldump -uroot -p0330 test students areas > student_area.sql #备份多个表，用空格隔开
在数据库执行：
--删除数据库
MariaDB [(none)]> drop database test;
--恢复数据库
MariaDB [(none)]> source test.sql;
--查看发现已恢复
MariaDB [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| areas |
| classes |
| students |
+----------------+
【释】数据库的恢复过程：创建数据库，用数据库，创建表，创建数据（insert）
三、视图
对于复杂的查询，在多个地方被使用，如果需求发生了改变，需要更改sql语句，则需要在多个地方进行修改，维护起来非常麻烦，解决方案就是使用视图。
视图本质就是对查询的封装，视图的用途就是用来查询。
定义视图，建议以v_开头。
视图由两部分组成：（create view 视图名 as）+ （查询命令s”elect“）
--创建一个简单的视图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 |
+-----------+-----------+
| 北京市 | 海淀区 |
| 天津市 | 滨海区 |
| 河北省 | 沧州市 |
| 山西省 | 大同市 |
| 北京市 | 朝阳区 |
| 天津市 | 武清区 |
| 河北省 | 石家庄 |
| 山西省 | 太原市 |
| 海淀区 | 西二旗 |
| 滨海区 | 大港 |
| 沧州市 | 任丘市 |
| 大同市 | 清徐 |
| 海淀区 | 中关村 |
| 滨海区 | 汉沽 |
| 沧州市 | 河间市 |
| 大同市 | 阳曲 |
+-----------+-----------+
四、事务（重点）
为什么要有事务（重点内容，一定要记住呀兄弟）
事务具有ACID特性：原子性(A,atomicity)、一致性(C,consistency)、隔离性(I,isolation)、持久性(D,durabulity)。
- 原子性：事务内的所有操作要么都执行，要么都不执行，它是一个不可分割的工作单位。
- 一致性：数据库总是从一个一致性的状态转换到另一个一致性的状态（在前面的例子当中，一致性确保了，即使在执行第三、四条语句之间系统崩溃，支票账户也不会损失200美元，因为事物最终没有被提交，所以事物中所做的修改也不会保存到数据库中）
- 隔离性：一个事物所做的修改在最终提交之前，对其他事物是不可见的（在前面的例子中，当执行完第三条语句、第四条语句还未开始时，此时有另外一个账户汇总程序开始运行，则其看到的支票账户的余额并没有被减去200美元。）
- 持久性：事务完成后，该事务内涉及的数据必须持久性的写入磁盘保证其持久性。当然，这是从事务的角度来考虑的的持久性，从操作系统故障或硬件故障来说，这是不一定的
事务命令
- 要求：表的引擎类型必须是innodb类型才可以使用事务，这是mysql表的默认引擎
- 查看表的创建语句，可以看到engine=innodb
show create tables students;
修改数据的命令会触发事务，包括insert、update、delete
开启事务，命令如下：
- 开启事务后执行修改命令，变更会维护到本地缓存中，而不维护到物理表中
两种方式：①begin； #两种方式等价
②start transaction
- 结束事务：
结束事务两种方式：①rollback回滚 #放弃缓存中变更的数据
②commit提交 #将缓存中的数据变更维护到物理表中
实例
--在创建事务之前将表创建好，不能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 |
+----+------+
五、索引
创建索引 ： create index 索引名 on 表名（字段名）
create index momo on areas(aid)查看索引 ：show create table areas;
删除索引 ：drop index momo on areas;
测试时间 ：set profiling=1(打开sql语句执行时间)
--测试时间
MariaDB [test]> show profiles;
+----------+------------+--------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+--------------------------------------------+
| | 0.00053225 | select * from areas where name='山西省' |
+----------+------------+--------------------------------------------+
--创建索引（加索引会变快）
MariaDB [test]> create index suoyin on areas(name); MariaDB [test]> select * from areas where name='山西省';
+-----+-----------+------+
| aid | name | pid |
+-----+-----------+------+
| | 山西省 | NULL |
+-----+-----------+------+
--创建索引后再次查看时间（因为数据太少，所以时间差距不是很明显；数据越多，差距越明显）
MariaDB [test]> show profiles;
+----------+------------+--------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+--------------------------------------------+
| | 0.00053225 | select * from areas where name='山西省' |
| | 0.02426824 | create index suoyin on areas(name) |
| | 0.00041717 | select * from areas where name='山西省' |
+----------+------------+--------------------------------------------+
【数据库默认端口：3306】
六、外键
如果一个实体的某个字段指向另一个实体的主键，就称为外键。被指向的实体，称之为主实体（主表），也叫父实体（父表）。负责指向的实体，称之为从实体（从表），也叫子实体（子表）
对关系字段进行约束，当为从表中的关系字段填写值时，会到关联的主表中查询此值是否存在，如果存在则填写成功，如果不存在则填写失败并报错
谁的范围小在谁处设外键
--添加外键
MariaDB [test]> alter table students add constriant fk foreign key (cls_id) references classes(id);
ERROR (): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'tables students add constriant fk foreign key (cls_id) references classes(id)' at line （出现错误的原因是因为表中有不对应外键的值）
--删除不对应的行，再次创建外键即可
MariaDB [test]> delete from students where cls_id>;
--创建外键，删除的时候无法删除，有约束
MariaDB [test]> alter table students add constraint fk foreign key (cls_id) references classes(id);
--查看外键
MariaDB [test]> show create table students;
--创建外键，删的时候关联表中的信息的全部删除
MariaDB [test]> alter table students add constraint fk foreign key (cls_id) references classes(id) on delete cascade
--删除外键
alter table students drop foreign key fk
七、补充
【修改数据库变量值】
show variables like "%commit%";
auto_commit=ON
永久修改：vim /etc/my.cnf.d/server.cnf
autocommit=0
systemctl restart mariadb
一次性修改：set autocommit=1;
set profiling=1……
【查看数据库状态(无法修改)】
show status like '%c%'
最新文章
- 《精通C#》自定义类型转化-扩展方法-匿名类型-指针类型（11.3-11.6）
- 求最长回文子串：Manacher算法
- JavaScript学习笔记-正则表达式(RegExp对象)
- 给IIS添加CA证书以支持https
- TCP之心跳包实现思路
- poj1474Video Surveillance(半平面交）
- [leetcode]_Reverse Integer
- Modoer列表页性能分析及优化
- ccache高速编译工具
- 【LeetCode 160】Intersection of Two Linked Lists
- 附加题-stack的理解
- Android Handler、Lopper消息驱动机制
- c++冒泡排序的模板函数设计
- JDK基本介绍
- 利用Google浏览器调试js代码
- 使用Javascript获取当前目录的绝对路径
- [HAOI2015]树上操作-树链剖分
- 函数式编程之-拒绝空引用异常(Option类型)
- mysql时间加减运算
- Windows 10忘记登录密码不用怕，系统U盘/光盘轻松重置