MYSQL学习笔记

MYSQL学习记录

库（database），表（table）

SQL语句：

DQL（数据查询）：select

• 简单查询：

select 字段名1,字段名2... from 表名;
select 字段名 as 新字段名 from 表名;

​ select (字段名1),(字段名2)… from (table_name) ; ps: 字段可以参与数学运算

​ 在字段号后加上 as (rename) 可以给查询结果的列重命名。命名可以为中文。

​ 标准sql语句要求字符串用单引号括起来，尽管mysql支持双引号。

​ 将字段名写作 “*” 代表查询该表所有数据。效率较低。

• 条件查询：

select
	字段,字段...
from
	表名
where
	条件 ;

​ 条件语句包括：

​ =, ＜, >, <=, >=, != ,<> (不等于),

​ between … and … (左闭右开区间),

​ is (not) NULL (NULL不等于0.0),

​ or(或), and(并),

​ (not) in(或): in (条件一,条件二),

​ like (模糊查询) ：

​ %表示任意多个字符，_表示任意一个字符

​ like ‘%字符%’ ;

​ like ‘_字符%’ 表示查找第二位是字符的数据；

​ 要查询带有_或%的字符，写成\_或\%

• 排序：

select
	字段,字段...
from
	表名
order by
	条件
;

​ asc升序，desc降序，默认升序

​ 用逗号隔开排序条件，表示在前面字段无法排序，才启动后面的字段排序

​ 可以用数字代替字段，表示指定列

​ 条件和排序可以组合，先执行条件，再执行排序，语句顺序也同上

• 分组函数：

​ count ()计数

​ sum ()求和

​ max ()最大值

​ min ()最小值

​ avg ()平均数

​ 对某一组数据处理，又称多行处理函数：多行处理，一行输出。

​ 分组函数自动忽略NULL

​ 单行处理函数：ifnull(字段，替换值)如果数据为null则替换

​ 只要有NULL参与的计算，结果都为NULL

​ 分组函数不能直接用于where子句中

​ count(*)表示统计表中的所有不为NULL的数据总数量

• 分组查询：

​ group by 和 having

​ group by：按照某个字段或者某些字段进行分组。

​ having：是对分组后的数据进行再次过滤。

select
	字段，字段...
	分组函数（）
from
	表
group by
	字段，字段;

​ 分组函数一般和group by组合使用，

​ 并且任何一个分组函数都在group by执行后才会执行。

​ group by在where之后执行

​ 如果没有group by整张表自成一组。 == select from group by * ;

​ 没有被group by分组的字段，查询出的结果没有意义。

​ 因此，select后面只能够被分组的字段和分组函数。

select
	字段，字段...
from
	表
group by
	字段，字段...
having
	条件 ;

​ where搞不定的情况再用having。

​ 因为先过滤再分组效率较高。

​ where 可以和 having 一起用。

• 查询去重

select
distinct
	字段，字段...
from
	表

​ distinct 只能出现在所有字段的最前面。

​ distinct 会对所有字段的重复去重

• 连接查询（跨表查询）

1. 内连接：

   表之间是平等的，只有都能匹配上的数据才会查询出来

   等值连接（条件是等值关系）

   非等值连接（条件是非等值关系）

   自连接（在同一张表）

   SQL92
   select
   	字段，字段...
   from
   	表，表...
   where
   	条件
   
   SQL99
   select
   	字段，字段...
   from
   	表
   (inner) join
   	表
   on
   	条件
   

2. 外连接：

   分为主表和副表，主要查询主表，顺带查询副表，主表有的数据，附表没有，则会用null代替(主表数据无条件查出来)

   左（外）连接：左边是主表

   右（外）连接：右边是主表

   左连接
   select
   	字段，字段...
   from
   	表(主表)
   left (outer) join
   	表
   on
   	条件 ;
   右连接
   select
   	字段，字段...
   from
   	表
   right (outer) join
   	表(主表)
   on
   	条件 ;
   

3. 全连接：

4. 注意：

   笛卡尔乘积现象：当两张表连接查询时，没有任何条件限制下，查询结果条数是两张表的数据条数的乘积

   避免了笛卡尔现象也不会减少查询次数，只会影响查询结果的显示

   在from子句中，在表名后输入一个字符串表示用该字符串代表 表，俗称取别名。

   ... from emp e ...
   

   给表取别名是个好习惯，通过表名（表的别名）和字段用’ . '连接，来指定那张表中的字段，避免重名造成的困扰。

   查询多个表时，就多写几个join on

• 嵌套查询（子查询）

  select语句中可嵌套的位置在：

  select
  	(select)
  from
  	(select)
  where
  	(select)
  

• union（可以将查询结果集相加）

  select ...
  union
  select ...
  

  合并后的列名为第一句的字段名

  第一句的查询列数和第二句的必须一样

• limit

  limit是mysql独有的

  limit取结果集中的部分数据

  select
  	字段，字段...
  from
  	表名
  limit 
  	startIndex
  	length
  

  startIndex表示起始位置，从0开始，0表示第一条数据。

  length表示取几个

  startIndex省略时，默认从0开始往后取

  查询分页：

  int pageNo = n;
  int pageSize = 10;
  limit (pageNo - 1) * pageSize , pageSize ;
  

• 执行顺序

select		5

from		1

where		2

group by	3

having		4

order by	6

limit		7

DML（数据操作）：insert delete update

• insert插入数据

insert into 表名(字段1,字段2,字段3,...) values(值1,值2,值3,...) ;
insert into 表名 select语句 ;//将查询结果插入到表中

• delete删除数据

  delete from 表 where 条件 ;
  

  不加where条件表示删除所有。

• update修改数据

update 表名 set 字段1=值1 , 字段2=值2 , ... where 条件 ;

​ 不加where条件表示修改所有。

DDL（数据定义）：create drop alter

• 创建表

create table 表名 (
	字段1 数据类型 (default 数据),
	字段2 数据类型 (default 数据),
	字段3 数据类型 (default 数据),
	...
);

• 复制表

  create table 表名 as select语句 ;
  

  将查询结果当作表创建出来

• 删除表

  drop table if exists 表名;
  

• 删除大表

  truncate table 表名 ;
  

  直接截断，不可回滚。永久丢失。

• 常见数据类型：

int	整数型

bigint	长整型

float	浮点型

char	定长字符串

varchar	可变长字符串

date	日期类型

BLOB	二进制大对象（储存图片、视频等流媒体信息）Binary Large OBject

CLOB	字符大对象（存放大文本）Character Large OBject

...

​ char会给字符串分配固定空间，varchar会在不超过上限的情况下动态分配空间

​ 当某个字段的数据长度不发生变化时，是定长的，可以使用char

​ 反之不固定时，使用varchar

• 约束

  在创建表时可以对表的字段添加相应的约束，添加约束的目的在于保证表中数据的合法性、有效性、完整性。

  常见约束：
  非空约束（not null）：约束字段不能为NULL

  ​ 唯一约束（unique）：约束字段不能重复，但可以为NULL

  ​ 主键约束（primary key）：约束字段既不能为NULL也不能重复（简称PK）

  ​ 作用：主键值是这行记录在这张表中的唯一标识

  ​ 分类：单一主键（推荐）和复合主键（多个字段联合起来添加一个主键，不推荐）

  ​ 自然主键（推荐）和业务主键（主键值与系统业务挂钩，最好不要）

  ​ 一张表只能有一个主键

  ​ 在主键约束后加上 auto_increment，主键字段会自动维护一个自增的数字，从1开始， 依次加1。

  ​ 外键约束（foreign key）：添加外键约束的值必须来自于某个字段（简称FK）

  foreign key （字段）references 表（字段）;
  

  ​ 此时，外键的来源称为父表，当前表称为子表。

  ​ 外键引用的的值必须具有唯一性

  ​ 在创建，删除，添加时，必须严格按照父子关系，顺序操作。

  ​ 检查约束（check）：oracle中有，mysql没有，且暂不支持。

  约束可以在创建时在数据类型后添加（列级约束）

  也可以在最后用逗号和前面子句隔开，使用约束词(字段，字段)。（表级约束）除了not null

  如：

  create table 表(
  	字段1,
  	字段2,
  	... ,
  	约束(字段1,字段2...)
  )
  

TCL（事务操作）：commit rollback

• 事务（Transactions）

  • 一个事务是一个完整的业务逻辑单元，不可再分。

  • 要想保证两条或以上的DML语句同时成功或失败，那么需要使用数据库的“事务机制”。

  • 事务的存在是为了保证数据的完整性，安全性。

  • mysql事务默认情况下自动提交，关闭自动提交，使用语句 start transaction；启动事务，使用commit提交，使用rollback回滚

  • 四大特性：

    • A 原子性：最小工作单元，不可再分。
    • C 一致性：事务必须保证多条DML语句同时成功或同时失败。
    • I 隔离性：事务A和事务B之间具有隔离。
    • D 持久性：最终数据必须持久化到硬盘文件中，事务才算成功结束

  • 关于事务的隔离性：事务存在隔离级别，理论上包括四个：

    • 第一级别：读未提交（read uncommitted）
      • 对方事务还未提交，我方事务可以读取对方未提交的数据
      • 存在脏读（Dirty Read）现象：表示读到脏数据
    • 第二级别：读已提交（read committed）
      • 对方事务提交后的数据我方可以读取
      • 解决了脏读现象
      • 出现问题：不可重复读（表示在我方事务未结束时，多次读取对方提交的数据得到的不一样）
    • 第三级别：可重复读（repeatable read）
      • 解决不可重复读的问题
      • 出现问题：读取到的数据是幻想
    • 第四级别：序列化读/串行化读（serializable）
      • 解决了所有问题
      • 效率低，需要排队

  • 原理：启动事务机制（开始）

    ​ DML语句1；

    ​ DML语句2；

    ​ DML语句3；

    ​ …

    ​ （只是记录到操作历史记录中，并不会真实修改数据）

    ​ 提交事务或回滚事务（结束）（此时根据操作修改或取消修改，并消除历史操作）

• commit

  提交

• rollback

  回滚

• savepoint

  储存状态

  savepoint name；储存一个点

  rollback name；回滚到点

DCL（权限管理）：

• 新建用户：

CREATE USER username IDENTIFIED BY 'password';

• 授权

grant 权限 on databasename.tablename to 'username' @loginip identified by 'password' with grant option;

• 撤销权限

revoke 权限 on databasename(.tablename) from username;

DBA（导入导出）：

• 导出数据库中的数据:

  • 在windows的dos窗口中执行（导出库）

    ​ mysqlump databasename >D:\databasename.sql -u -p

  • 在windows的dos窗口中执行（导出表）

    ​ mysqlump databasename tablename >D:\databasename.sql -u -p

• 导入数据

  • 在mysql中

    ​ create database databasename；

    ​ use databasename；

    ​ source D:\databasename.sql

注意：

1. 任何一条语句以";"结尾。
2. sql语句不区分大小写。数据区分大小写。

存储引擎

​ 表的存储方式，每一种存储引擎有不同的存储方式，各有其优缺点

Engine	Support	Comment	Transactions	XA	Savepoints
InnoDB	DEFAULT	Supports transactions, row-level locking, and foreign keys	YES	YES	YES
MRG_MYISAM	YES	Collection of identical MyISAM tables	NO	NO	NO
MEMORY	YES	Hash based, stored in memory, useful for temporary tables	NO	NO	NO
BLACKHOLE	YES	/dev/null storage engine (anything you write to it disappears)	NO	NO	NO
MyISAM	YES	MyISAM storage engine	NO	NO	NO
CSV	YES	CSV storage engine	NO	NO	NO
ARCHIVE	YES	Archive storage engine	NO	NO	NO
PERFORMANCE_SCHEMA	YES	Performance Schema	NO	NO	NO
FEDERATED	NO	Federated MySQL storage engine	NO	NO	NO

MyISAM 是MYSQL最常用的，具有以下特征：

• 使用三个文件表示每个表：
  • 格式文件（frm）
  • 数据文件（MYD）
  • 索引文件（MYI）
• 灵活的AUTO_INCREMENT字段处理
• 可被转换为压缩、只读表来节省空间
• 不支持事务

InnoDB 是MYSQL默认的

• 支持事务、行级锁、外键等，比较安全
• 主要特征：
  • 表结构储存在（.frm）文件中
  • 表数据存储在tablespace中（逻辑概念），无法被压缩，无法转换为只读
  • 提供崩溃后自动恢复机制
  • 支持级联删除和级联更新（有联系的数据）

MEMORY

• 缺点：不支持事务，数据容易丢失。因为所有数据和索引储存在内存中。
• 优点：查询速度最快

索引

• 建立索引可以缩小扫描范围，从而实现高效查询。

• 索引不被推荐在需要频繁修改的数据表中，一旦数据改动，索引就需要重新排序、进行维护。

• 添加索引是给某个字段或者某些字段添加

• 什么时候考虑给字段添加索引？

  • 数据量庞大（根据需求和环境）

  • 该字段很少的DML操作

  • 该字段经常出现在where子句中

• 主键和具有unique约束的字段会自动添加索引

  ​ 根据主键查询效率高。

• 添加/删除索引：

create index name on 表名(字段名);drop index name;

• 底层索引

  • 索引底层使用数据结构：B + Tree
  • 生成的索引存在硬盘文件或内存中（根据储存引擎），并且会携带每个数据的物理地址
  • 索引会自动排序，之后对数据（物理地址）分区，存进B+Tree中
  • 对有索引的数据列查询，等于直接查询物理地址，通过地址定位表中数据。

• 索引分类：

  • 单一索引：给单一字段添加索引
  • 复合索引：给多个字段联合起来添加一个索引
  • 主键索引：主键上自动添加索引
  • 唯一索引：有unique约束的字段会自动添加索引
  • …

• 索引什么时候失效？

  模糊查询时，第一个通配符使用的是%，这时候索引失效。

  …

视图（view）

• 站在不同的角度去看数据（同一张表的数据，通过不同的角度去看待）。
• 创建/删除视图

create view name as select语句;drop view name;

• 对视图进行增删改查，会影响到原表数据。（通过视图影响原表数据，不是直接操作的原表）
• 可以对视图进行CRUD操作
• 面向视图操作

select 字段名 from name;update name set 字段1=值1 , 字段2=值2 , ... where 条件;delete from name where 条件;

• 视图的作用
  • 视图可以隐藏表的实现细节。保密级别较高的系统，数据库只对外提供相关的视图，只对视图对象进行CRUD。

MYSQL语句

1. show databases ；展示数据库
2. show tables ；展示当前数据库的表
3. show tables from (database_name) ; 查看其他库中的表
4. show create table (table_name) ; 查看创建表的SQL语句
5. use (database_name) ；使用指定数据库
6. create database (database_name) ; 创建数据库
7. source (文件目录) ；导入sql脚本
8. desc (table_name) ; 展示数据库结构
9. select database() ; 查看当前数据库
10. select version() ; 查看mysql版本号
11. set global transaction isolation level （）；设置全局隔离级别
12. explain + SQL语句；查询该语句的执行计划
13. \c 结束一条语句
14. exit 退出

数据库设计三范式

设计表的依据。依照这个三范式设计的表不会出现数据冗余。

三范式：

• 第一范式：任何一张表都应该有主键，并且每个字段原子性不可再分。

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

  ​ 多对多，三张表，关系表两外键

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

  ​ 一对多，两张表，多得表加外键

提醒：在实际开发中，以满足客户需求为主，有的时候会拿冗余换执行速度。

一对一怎么设计？

拆分大表为小表，表之间得关系设计有两种方案：

• 主键共享
• 外键唯一