【数据库入门】动力节点mysql入门基础04

最新推荐文章于 2024-09-04 16:15:45 发布

MSC419

最新推荐文章于 2024-09-04 16:15:45 发布

阅读量95

点赞数

分类专栏： # 数据库文章标签： mysql 数据库

本文链接：https://blog.csdn.net/qq_38939822/article/details/121039551

版权

数据库专栏收录该内容

5 篇文章 0 订阅

订阅专栏

文章目录

1 索引
2 视图
3 DBA常用命令
- 3.1 数据导出
- 3.2 数据导入
4 数据库设计三范式

1 索引

1.1 索引

索引被用来快速找出在一个列上用一特定值的行。如果没有索引， MySQL 不得不首先以第一条记录开始，然后读完整个表直到它找出相关的行。表越大，花费时间越多。对于一个有序字段，可以运用二分查找（ Binary Search），这就是为什么性能能得到本质上的提高。

注意：

主键， unique 都会自动添加索引
在任何数据库当中，任何一张表的任何一条记录在硬盘存储上都有
一个硬盘的物理存储编号。
在mysql当中，索引是一个单独的对象，不同的存储引擎以不同的形式存在。
在MyISAM存储引擎中，索引存储在一个.MYI文件中。
在InnoDB存储引擎中索引存储在一个逻辑名称叫做tablespace的当中。
在MEMORY存储引擎当中索引被存储在内存当中。
不管索引存储在哪里，索引在mysql当中都是一个树的形式存在。（自平衡二叉树：B-Tree）
唯一性比较弱的字段上添加索引用处不大。
索引是各种数据库进行优化的重要手段。优化的时候优先考虑的因素就是索引。

什么条件下，我们会考虑给字段添加索引呢？

条件1：数据量庞大（到底有多么庞大算庞大，这个需要测试，因为每一个硬件环境不同）
条件2：该字段经常出现在where的后面，以条件的形式存在，也就是说这个字段总是被扫描。
条件3：该字段很少有DML(insert delete update)操作。（因为DML之后，索引需要重新排序。）

建议不要随意添加索引，因为索引也是需要维护的，太多的话反而会降低系统的性能。
建议通过主键查询，建议通过unique约束的字段进行查询，效率是比较高的。

1.2 索引的应用

1.2.1 创建与删除索引

1.创建索引：

mysql> create index emp_ename_index on emp(ename);

给emp表的ename字段添加索引，起名：emp_ename_index

2.删除索引：

mysql> drop index emp_ename_index on emp;

将emp表上的emp_ename_index索引对象删除。

1.2.2 查看索引

show index from 表名;#查看表里的索引
explain select * from emp where ename = 'KING';#explain关键词可以查看该语句查了多少行数据

mysql> show index from emp;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| emp   |          0 | PRIMARY  |            1 | EMPNO       | A         |          14 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.09 sec)

#查了14行数据，说明没有索引
mysql> explain select * from emp where ename = 'KING';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   14 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> create index emp_ename_index on emp(ename);
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

#加上索引之后只查了一行
mysql> explain select * from emp where ename = 'KING';
+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys   | key             | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | emp   | NULL       | ref  | emp_ename_index | emp_ename_index | 33      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> show index from emp;
+-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name        | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| emp   |          0 | PRIMARY         |            1 | EMPNO       | A         |          14 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| emp   |          1 | emp_ename_index |            1 | ENAME       | A         |          14 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.00 sec)

1.2.3 索引失效

情况1：开头要模糊匹配，如：select * from emp where ename like '%T';

情况2：使用or的时候会失效，如果使用or那么要求or两边的条件字段都要有索引，才会走索引。所以不建议使用or

情况3：使用复合索引的时候，没有使用左侧的列查找，索引失效。复合索引：两个字段，或者更多的字段联合起来添加一个索引。如：create index emp_job_sal_index on emp(job,sal);

情况4：在where当中索引列参加了运算，如：select * from emp where sal+1 = 800;

情况5：在where当中索引列使用了函数，如：select * from emp where lower(ename) = 'smith';

2 视图

2.1 什么是视图

view:站在不同的角度去看待同一份数据。

2.2 创建与删除视图

create view view_name as DQL语句;
drop view 视图名;

mysql> create table dept2 as select * from dept;
Query OK, 4 rows affected (0.05 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from dept2;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.00 sec)

mysql> create view dept2_view as select * from dept2;
Query OK, 0 rows affected (0.01 sec)

mysql> drop view dept2_view;
Query OK, 0 rows affected (0.01 sec)

2.3 视图的作用

我们可以面向视图对象进行增删改查，对视图对象的增删改查，会导致原表被操作！
视图的特点：通过对视图的操作，会影响到原表数据。

mysql> create view dept2_view as select * from dept2;
Query OK, 0 rows affected (0.02 sec)

#面向视图查询
mysql> select * from dept2_view;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.00 sec)

#面向视图插入
mysql> insert into dept2_view(deptno,dname,loc) values(60,'SALES','BEIJING');
Query OK, 1 row affected (0.00 sec)

#查询原表数据
mysql> select * from dept2;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
|     60 | SALES      | BEIJING  |
+--------+------------+----------+
5 rows in set (0.00 sec)

#面向视图删除
mysql> delete from dept2_view;
Query OK, 5 rows affected (0.01 sec)

#查询原表数据
mysql> select * from dept2;
Empty set (0.00 sec)

#创建视图对象
mysql> create view
    -> emp_dept_view
    -> as
    -> select e.ename,e.sal,d.dname
    -> from emp e
    -> join dept d
    -> on e.deptno=d.deptno;
Query OK, 0 rows affected (0.01 sec)

#查询视图对象
mysql> select * from emp_dept_view;
+--------+---------+------------+
| ename  | sal     | dname      |
+--------+---------+------------+
| SMITH  |  800.00 | RESEARCH   |
| ALLEN  | 1600.00 | SALES      |
| WARD   | 1250.00 | SALES      |
| JONES  | 2975.00 | RESEARCH   |
| MARTIN | 1250.00 | SALES      |
| BLAKE  | 2850.00 | SALES      |
| CLARK  | 2450.00 | ACCOUNTING |
| SCOTT  | 3000.00 | RESEARCH   |
| KING   | 5000.00 | ACCOUNTING |
| TURNER | 1500.00 | SALES      |
| ADAMS  | 1100.00 | RESEARCH   |
| JAMES  |  950.00 | SALES      |
| FORD   | 3000.00 | RESEARCH   |
| MILLER | 1300.00 | ACCOUNTING |
+--------+---------+------------+
14 rows in set (0.00 sec)

#面向视图更新
mysql> update emp_dept_view set sal=1000 where dname='ACCOUNTING';
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0

#原表数据被更新
mysql> select * from emp;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 1000.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 1000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1000.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)

视图对象在实际开发中的作用：方便，简化开发，利于维护

假设有一条非常复杂的SQL语句，而这条SQL语句需要在不同的位置上反复使用。每一次使用这个sql语句的时候都需要重新编写，很长，很麻烦，怎么办？
以把这条复杂的SQL语句以视图对象的形式新建。在需要编写这条SQL语句的位置直接使用视图对象，可以大大简化开发。并且利于后期的维护，因为修改的时候也只需要修改一个位置就行，只需要修改视图对象所映射的SQL语句。

我们以后面向视图开发的时候，使用视图的时候可以像使用table一样。可以对视图进行增删改查等操作。视图不是在内存当中，视图对象也是存储在硬盘上的，不会消失。

3 DBA常用命令

3.1 数据导出

在windows的dos命令窗口中：

mysqldump bjpowernode>D:\bjpowernode.sql -uroot -p密码

导出指定的表

mysqldump bjpowernode emp>D:\bjpowernode.sql -uroot -p密码

3.2 数据导入

注意：需要先登录到mysql数据库服务器上。
然后创建数据库：create database bjpowernode;
使用数据库：use bjpowernode
然后初始化数据库：source D:\bjpowernode.sql

4 数据库设计三范式

第一范式：要求任何一张表必须有主键，每一个字段原子性不可再分。

第二范式：建立在第一范式的基础之上，要求所有非主键字段完全依赖主键，不要产生部分依赖。

第三范式：建立在第二范式的基础之上，要求所有非主键字段直接依赖主键，不要产生传递依赖。

设计数据库表的时候，按照以上的范式进行，可以避免表中数据的冗余，空间的浪费。

4.1 第一范式

最核心，最重要的范式，所有表的设计都需要满足。必须有主键，并且每一个字段都是原子性不可再分。

学生编号 	学生姓名 	联系方式
------------------------------------------
1001		张三		zs@gmail.com,1359999999
1002		李四		ls@gmail.com,13699999999
1001		王五		ww@163.net,13488888888

以上是学生表，满足第一范式吗？
不满足，第一：没有主键。第二：联系方式可以分为邮箱地址和电话

学生编号(pk) 	学生姓名		邮箱地址				联系电话
----------------------------------------------------
1001				张三		zs@gmail.com	1359999999
1002				李四		ls@gmail.com	13699999999
1003				王五		ww@163.net		13488888888

4.2 第二范式

建立在第一范式的基础之上，要求所有非主键字段必须完全依赖主键，不要产生部分依赖。

学生编号 	学生姓名 	教师编号 	教师姓名
----------------------------------------------------
1001			张三		001		王老师
1002			李四		002		赵老师
1003			王五		001		王老师
1001			张三		002		赵老师

这张表描述了学生和老师的关系：1个学生可能有多个老师，1个老师有多个学生.这是非常典型的多对多关系

分析以上的表是否满足第一范式？不满足第一范式。

学生编号+教师编号(pk)			学生姓名  		教师姓名
----------------------------------------------------
1001			001				张三			王老师
1002			002				李四			赵老师
1003			001				王五			王老师
1001			002				张三			赵老师

学生编号教师编号，两个字段联合做主键，复合主键（PK: 学生编号+教师编号）经过修改之后，以上的表满足了第一范式。
但是满足第二范式吗？不满足，“张三”依赖1001，“王老师”依赖001，显然产生了部分依赖。
产生部分依赖有什么缺点？数据冗余了。空间浪费了。“张三”重复了，“王老师”重复了。

为了让以上的表满足第二范式，需要这样设计：使用三张表来表示多对多的关系

	学生表
	学生编号(pk)			学生名字
	------------------------------------
	1001					张三
	1002					李四
	1003					王五
	
	教师表
	教师编号(pk)			教师姓名
	--------------------------------------
	001					王老师
	002					赵老师

	学生教师关系表
	id(pk)				学生编号(fk)					教师编号(fk)
	------------------------------------------------------
	1						1001						001
	2						1002						002
	3						1003						001
	4						1001						002

背口诀：多对多，三张表，关系表两个外键

4.3 第三范式

第三范式建立在第二范式的基础之上要求所有非主键字典必须直接依赖主键，不要产生传递依赖。

学生编号（PK） 		学生姓名 	班级编号  		班级名称
---------------------------------------------------------
	1001				张三		01			一年一班
	1002				李四		02			一年二班
	1003				王五		03			一年三班
	1004				赵六		03			一年三班

以上表的设计是描述：班级和学生的关系。很显然是1对多关系，一个教室中有多个学生。

分析以上表是否满足第一范式？满足第一范式，有主键。

分析以上表是否满足第二范式？满足第二范式，因为主键不是复合主键，没有产生部分依赖。主键是单一主键。

分析以上表是否满足第三范式？第三范式要求：不要产生传递依赖
一年一班依赖01，01依赖1001，产生了传递依赖。
不符合第三范式的要求。产生了数据的冗余。

那么应该怎么设计一对多呢？

	班级表：一
	班级编号(pk)						班级名称
	----------------------------------------
	01								一年一班
	02								一年二班
	03								一年三班

	学生表：多

	学生编号（PK） 	学生姓名			 班级编号(fk)
	-------------------------------------------
	1001				张三			01			
	1002				李四			02			
	1003				王五			03			
	1004				赵六			03

背口诀：一对多，两张表，多的表加外键

4.4 总结

一对多，两张表，多的表加外键
多对多，三张表，关系表两个外键

一对一：
一对一放到一张表中不就行了吗？为啥还要拆分表？
在实际的开发中，可能存在一张表字段太多，太庞大。这个时候要拆分表。
一对一怎么设计？
没有拆分表之前：一张表

	t_user
	id		login_name		login_pwd		real_name				email				address........
	---------------------------------------------------------------------------
	1			zhangsan		123				张三				zhangsan@xxx
	2			lisi			123				李四				lisi@xxx
	...

这种庞大的表建议拆分为两张：

	t_login 登录信息表
	id(pk)		login_name		login_pwd	
	---------------------------------
	1				zhangsan		123			
	2				lisi			123			

	t_user 用户详细信息表
	id(pk)		real_name		email				address........	login_id(fk+unique)
	-----------------------------------------------------------------------------------------
	100			张三				zhangsan@xxx								1
	200			李四				lisi@xxx										2

口诀：一对一，外键唯一

数据库设计三范式是理论上的。实践和理论有的时候有偏差。最终的目的都是为了满足客户的需求，有的时候会拿冗余换执行速度。因为在sql当中，表和表之间连接次数越多，效率越低。（笛卡尔积）
有的时候可能会存在冗余，但是为了减少表的连接次数，这样做也是合理的，并且对于开发人员来说，sql语句的编写难度也会降低。