MySQL知识总结

1. 常用命令

1.1 查看MySQL版本

长选项：mysql --version

段选项：mysql -V

1.2 创建数据库

create database 数据库名称；

// 在数据库中建立表，因此创建表的时候必须要先选择数据库。

use 数据库名称；

1.3 查询当前使用的数据库

select database();

// 查询数据库版本也可以使用

select version();

1.4 终止一条语句

如果想要终止一条语句，可以键入\c

1.5 退出mysql

可以使用\q、QUIT或EXIT

 

2. 查看数据库的表结构

2.1 查看和指定现有的数据库

show database;

2.2 指定当前缺省数据库

use 数据库名称；

2.3 查看当前使用的库

select database();

2.4 查看数据库中的表

show tables；

2.5 查看其他库中的表

show tables from 数据库名称；

2.6 查看表的结构

desc 表的名称；

2.7 查看表的创建语句

show create table 表的名称；

 

3 简单的查询

3.1 查询一个字段

// 查询员工名

select ename from emp;

3.2 查询多个字段

select empno, ename from emp;

3.3 查询全部字段

select * from emp;

采用这种方法虽然简单，但是*号不是很明确，建议查询全部字段相关字段写到select语句的后面，在以后java连接数据库的时候，是需要在java程序中编写SQL语句的，这个时候编写的SQL语句不建议使用select * 这种形式，建议写明字段，这样可读性强。

3.4 计算员工年薪

// 列出员工的编号，姓名和年薪

select empno, ename, sal*12 from emp;

3.5 将查询出来的字段显示为中文

select empno as '员工编号', ename as '员工姓名', sal * 12 as '年薪' from emp;

// 其实as关键字是可以省略的

4. 条件查询

between ... and ...　　// 等同于>= and <=

and 　　　　　　　　// 并且

or　　　　　　　　　// 或者

4.1 等号操作符

// 查询薪水为5000的员工

select empno, ename, sal from emp where sal=5000;

// 查询job为MANAGER的员工

select empno, ename from emp where job='manager'

// mysql默认情况下大小写是不敏感的

注意：

MySQL在windows下是不区分大小写的，将script文件导入MySQL后表名也会自动转化为小写，结果再 想要将数据库导出放到linux服务器中使用时就出错了。因为在linux下表名区分大小写而找不到表，查了很多都是说在linux下更改MySQL的设置使其也不区分大小写，但是有没有办法反过来让windows 下大小写敏感呢。其实方法是一样的，相应的更改windows中MySQL的设置就行了。

具体操作：

在MySQL的配置文件my.ini中增加一行：

lower_case_table_names = 0

其中 0：区分大小写，1：不区分大小写

MySQL在Linux下数据库名、表名、列名、别名大小写规则是这样的：

1、数据库名与表名是严格区分大小写的；

2、表的别名是严格区分大小写的；

3、列名与列的别名在所有的情况下均是忽略大小写的；

4、变量名也是严格区分大小写的；    MySQL在Windows下都不区分大小写

 

4.2 <>操作符

查询薪水不等于5000的员工

select empno, ename, sal from emp where sal <> 5000;

一下写法等同于以上写法，建议使用第一种写法

select empno, ename, sal from emp where sal != 5000;

 

4.3 between ... and ... 操作符

查询薪水为1600到3000的员工（第一种方式，采用>=和<=）

select empno, ename, sal from emp where sal >= 1600 and sal <= 3000;

查询薪水为1600到3000的员工（第二种方式，采用between…and…）

select empno, ename, sal from emp where sal between 1600 and 3000;

// 关于between...and...，它是包含最大值和最小值的

 

4.4 is null

null为空，但是不是空串，为null可以设置这个字段不填值，如果查询为null字段，采用is null

查询津贴为空的员工

select * from emp where comm=null;

 

5. 存储引擎

在创建表时，可使用ENGINE选项为CREATE TABLE语句显示指定存储引擎。

CREATE TABLE TABLENAME (NO INT) ENGINE = MyISAM;

现有表的存储引擎可使用ALTER TABLE语句来改变：ALTER TABLE TABLENAME ENGINE = INNODB;

5.1 常用的存储引擎

MyISAM存储引擎

MyISAM存储引擎是MySQL最常用的引擎。

特征：

使用三个文件表示每个表：

　　格式文件——存储表结构的定义（mytable.frm）

　　数据文件——存储表行的内容（mytable.MYD）

　　索引文件——存储表上索引（mytable.MYI）

灵活的AUTO_INCREMENT字段处理

可被转换为压缩、只读表来节省空间

 

InnoDB存储引擎

InnoDB存储引擎是MySQL的缺省引擎

特征：

用COMMIT（提交）、SAVEPOINT及ROLLBACK（回滚）支持事物处理

在MySQL服务器崩溃后提供自动恢复

支持外键及引用的完整性，包括级联删除和更新

 

MEMORY存储引擎

使用MEMORY存储引擎的表，其数据存储在内存中，且行的长度固定，这两个特点使得MEMORY存储引擎非常快。

特点：

数据及索引被存储在内存中

 

选择合适的存储引擎

MyISAM表最适合用于大量的数据读，少量的数据更新的混合操作

如果查询中包含较多的数据更新操作，应使用InnoDB。其行级锁机制和多版本的支持为数据读取和更新的混合操作提供了良好的并发机制

MEMORY存储引擎用来存储非永久性需要的数据，或者是能够从基于磁盘的表中重新生成数据

 

6 事务

 

 

事务可以保证多个操作原子性，要么全成功，要么全失败。对于数据库来说事物保证批量的DML要么全成功，要么全失败。事务具有四个特征ACID

  • 原子性：整个事务中的所有操作，必须作为一个单元全部完成（或全部取消）
  • 一致性：在事务开始之前与结束之后，数据库保持一致状态
  • 隔离性：一个事务不会影响其他事务的运行
  • 持久性：在事物完成之后，该事务对数据库所做的更改将持久地保存在数据库中，并不会被回滚

事务中的一些概念：

  • 事务：一批操作（一组DML）
  • 开启事务
  • 回滚事务
  • 提交事务
  • SET AUTOCOMMIT

当执行DML语句时其实就是开启一个事务

 

事务的隔离级别

事务的隔离级别决定了事务之间可见的级别。

当多个客户端并发地访问同一个表时，可能出现下面的一致性问题：

  • 脏读取：一个事务开始读取了某行数据，但是另外一个事务已经更新了此数据但没有能够及时提交，这就出现了脏读取。
  • 不可重复读：在同一个事务中，同一个操作对同一个数据的前后两次读取产生了不同的结果，这就是不可重复读。
  • 幻像读：幻像读是指同一个事务中以前没有的行，由于其他事务的提交而出现的新行。

 

四个隔离级别：

InnoDB实现了四个隔离级别，用以控制事务所做的修改，并将修改通告至其它并发事务：

  • 读未提交：允许一个事务可以看到其他事务未提交的修改。
  • 读已提交：允许一个事务只能看到其他事物已经提交的修改，未提交的修改是不可见的。
  • 可重复读：确保如果一个事务中执行两次相同的SELECT语句，都能得到相同的结果，不管其它事务是否提交这些修改。
  • 串行化：将一个事务与其他事务完全地隔离。

 

隔离级别的作用范围

事务隔离级别的作用范围分为两种：

  • 全局级：对所有的会话有效
  • 会话级：只对当前会话有效

 

7 索引

索引原理

索引是用来找出在一个列上用一特定值的行。没有索引，MySQL不得不首先以第一条记录开始，然后读完整个表直到它找出相关的行。对于一个有序字段，可以运用二分查找，这就是为什么性能能得到本质上的提高。MYISAM和INNODB都是用B+Tree作为索引结构

 

什么时候需要给字段添加索引：

  • 表中该字段的数据量庞大
  • 经常被检索，经常出现在where子句中的字段
  • 经常被DML操作的字段不建议添加索引

 

索引等同于一本书的目录

主键会自动添加索引，所以尽量根据主键查询效率较高

 

8 视图

什么是视图：

  • 视图是一种根据查询（也就是SELECT表达式）定义的数据库对象，用于获取想要看到和使用的局部数据
  • 视图有时也被称为虚拟表
  • 视图可以被用来从常规表或其他视图中查询数据
  • 相对于从基表中直接获取数据，视图有以下好处：
    • 访问数据变得简单
    • 可被用来对不同用户显示不同的表的内容

视图的作用：

  • 提高检索效率
  • 隐藏表的实现细节[面向视图检索]

 

9 数据库设计的三范式：

  • 第一范式：数据库表中不能出现重复记录，每一个字段是原子性的不能再分。每一行必须唯一，也就是每个表必须有主键，这是我们数据库设计的最基本的要求。
  • 第二范式：建立在第一范式的基础之上，另外要求所有非主键字段完全依赖主键，不能产生部分依赖
  • 第三范式：建立在第二范式基础上，非主键字段不能传递依赖于主键字段。（不要产生传递依赖关系）

 

posted @ 2021-04-28 21:48  Veritas_des_Liberty  阅读(75)  评论(0)  编辑  收藏  举报