首页  思维导图  详情

MySQL数据库

2019-07-17 11:51:38   2  举报





AI智能生成

MySQL数据库基础学习内容

MySQL

数据库

基础学习

作者其他创作

大纲/内容

1_初涉MySQL

1-1 MySQL概述

关系型数据库管理系统

由瑞典MySQL AB 公司开发

目前属于 Oracle 旗下产品

1-2 MySQL的安装与配置

下载MySQL

安装并配置

目录结构

分支主题

1-3 基本命令

启动与停止MySQL服务

开启 net start mysql

关闭 net stop mysql

MySQL登录与退出

mysql -u root -p 回车

mysql -u root -h localhost -P 3306 -p

退出

exit；

quit；

\q；

修改MySQL提示符

prompt

完整的日期

当前使用的数据库名称

服务器名称

当前用户名称

显示当前数据库版本

SELECT VERSION()；

显示当前日期

SELECT NOW();

显示当前用户

SELECT USER();

2_SQL简介

2-1 SQL介绍

2-2 SQL的分类

DDL: 数据库的定义, 与数据库/表结构: create, drop, alter

DML: 数据操纵语言: 操作表数据 insert update delete

DCL: 数据控制语言: 设置用户的访问权限安全

DQL: 数据查询语言: select from where

2-3 SQL语法规范

关键字与函数名称全部大写

数据库名称、表名称、字段名称全部小写

SQL语句必须以分号结尾

3_MySQL基础操作

3-1 操作数据库

创建数据

1.使用默认的字符集与校对

create database 数据库名;

查看数据库

show databases; 查看所有数据库

查看数据库定义: show create database 数据库名字

select database(); 查看当前正在使用的数据库

修改数据库

alter database 数据库名称 character set 编码 collate 校对

删除数据库(慎用！)

drop database 数据库名字

选中数据库

use 数据库的名字

3-2 MySQL数据类型

数据类型概述

数据类型分类

整型

浮点型

字符型

日期时间型

3-3 约束

概述

它是用于限定表中字段

类型

主键约束: primary key

每张表只有一个主键，但是可以定义多个字段

保证记录的唯一性

自动为not null

唯一约束: unique

每张表可以有多个

保证记录的唯一性

记录可以有多个null

非空约束: not null

默认值约束：default 值

外键约束：FOREIGN KEY (字段) REFERENCES 参照表名 (参照字段)

保证数据一致性和完整性

实现数据表之间一对一或一对多关系

分支主题

自增：auto_increment

操作

添加

ALTER TABLE 表名 ADD PRIMARY KEY(字段名)

ALTER TABLE 表名 ADD UNIQUE(字段名)

ALTER TABLE 表名 ADD FOREIGN KEY （外键字段名）REFERENCES 参照表名（参照表字段名）

删除

ALTER TABLE 表名 DROP PRIMARY KEY

ALTER TABLE 表名 DROP {INDEX|KEY} 字段名

ALTER TABLE 表名 DROP FOREIGN KEY 外键约束的名字（不是字段名称）

3-4 操作数据表

创建数据表

1.创建表---使用默认的字符集与校对

create table 表名( 列名列的类型(长度) 约束,  列名2 列的类型(长度) 约束 );

2.创建表--指定字符集与校对

create table 表名( 列名列的类型(长度) 约束,  列名2 列的类型(长度) 约束 )character set 字符集 collate 校对;

查看数据表

show tables ; 查看当前数据库中所有的表名

show create table 表名: 查看表的定义结构/创建语句

desc 表名 : 查看表的结构

修改表

添加列

alter table 表名 add 列名列的类型列的约束

修改列定义

alter table 表名 modify 列名列的类型列的约束

修改列定义或名称

alter table 表名 change 旧列名新列名列的类型列的约束

删除列

alter table 表名 drop 列名

修改表的字符集

alter table 表名 character set 字符集

修改数据表名

rename table 旧表名 to 新的表名

ALTER TABLE 旧表名 RENAME 新表名

删除表

drop table 表名

3-5 操作数据表中的记录

插入数据

insert into 表名(列名1,列名2)values(值1,值2);

insert into 表名 values(值1,值2);

批量插入:insert into 表名values(值1,值2),(值1,值2),(值1,值2);

删除数据

delete from 表名 [where 条件]

truncate table 表名: 先删除表,再重建表

更新数据

update 表名 set 列名=值, 列名=值 [where 条件]

查询数据

通用格式: select [distinct] [*] [列名1,列名2] from 表名 where 条件 group by ..having 条件过滤 order by 排序

查询指定列

select 字段 from 表名;

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

查询所有列

select * from 表名;

查询表中所有字段.

注意:使用"*"在练习，学习过程中可以使用，在实际开发中，不建议使用。

去掉重复记录

select distinct 字段 from 表名;

distinct它的作用是去除重复.

使用别名

SELECT column as 别名 from 表名;

在查询中可以直接对列进行运算

我们在sql操作中，可以直接对列进行运算。

ifnull函数使用

在对数值类型的列做运算的时候，如果做运算的列的值为null的时，运算结果都为null，为了解决这个问题可以使用ifnull函数

4_MySQL进阶操作

4-1 条件查询

where子句

格式 :select 字段 from 表名 where 条件;

1.比较运算符

> < <= >= != <>

大于、小于、大于(小于)等于、不等于

2.逻辑运算符

AND语法

OR语法

NOT语法

3.between ...and

相当于 >= and <=

注意:between 后面的值必须是小值 and后面的是大值

4.in

可以比较多个值

5.like

模糊查询

通配符使用:

% 匹配多个

_ 匹配一个

6.null值操作

is null; 判断为空

is not null; 判断不为空

order by排序

SELECT column1, column2. column3..

FROM table

order by column asc|desc

order by 子句是select的最后的一个子句

asc 升序 (默认)

desc 降序

聚合函数

sum() : 求和

计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；

Select sum(列名)｛,sum(列名)…｝ from tablename

avg() : 平均值

计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；

count() : 统计数量

统计指定列不为NULL的记录行数；

max() : 最大值

计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；

min() : 最小值

计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；

分组group by

SELECT column1, column2. column3.. FROM table

group by column having ...

分组操作中的having子句是用于在分组后对数据进行过滤的，作用类似于where条件

关键字顺序

from：首先执行from，找到要查询的emp表；

where：判断条件，筛选出工资大于15000的所有记录；

group by：根据以上关键字执行的结果上对记录按照指定列进行分组

having：对分组后的信息进行筛选；

select：选择所需要的列信息；

order by：对查询信息进行排序。

在SQL语言中，第一个被处理的子句是from字句，尽管select字句最先出现，但是几乎总是最后被处理。

4-2 MySQL 子查询

嵌套在内部的查询

由比较运算符引发的子查询

由[NOT] IN/EXISTS引发的子查询

使用INSERT...SELECT插入记录

分支主题

4-3 连接

概述

连接是在多个表之间通过一定的连接条件，使表之间发生关联，进而能从多个表之间获取数据。

连接的语法结构

SELECT 字段 FROM A表，B表 WHERE 连接条件;

A表连接类型 B表 ON 连接条件

分类

INNER JOIN

A\B表符合连接条件的记录

LEFT JOIN

A表的全部记录以及B表符合连接条件的记录

RIGHT JOIN

A表的全部记录以及B表符合连接条件的记录

应用

多表更新

参照B表中的记录更新A表

A\B表中有相同字段时，要给表起别名避免冲突

多表连接

多表删除

无限级分类表设计

自身连接

注意事项

笛卡尔积

第一个表中的所有行和第二个表中的所有行都发生连接。

笛卡尔积在下列情况产生：

连接条件被省略

连接条件是无效的

为了避免笛卡尔积的产生，通常需要在WHERE子句中包含一个有效的连接条件。

4-4 函数

运算符和函数

MySQL 字符函数

MySQL 数值运算符和函数

MySQL 比较运算符和函数

MySQL 日期时间函数

MySQL 信息函数

MySQL 聚合函数

MySQL 加密函数

自定义函数

MySQL 自定义函数简介

MySQL 创建不带参数的自定义函数

MySQL 创建带有参数的自定义函数

MySQL 创建具有复合结构函数体的自定义函数

4-5 视图、索引、事务

视图

视图概述

视图是逻辑上来自一个或多个表的数据集合。

为什么使用视图

限制其它用户对数据库表的访问,因为视图可以有选择性的显示数据库表的一部分；

容易实现复杂的查询；

对于相同的数据可以产生不同的视图；

创建视图

CREATE VIEW 视图名 AS SELECT 字段 FROM 表名 WHERE 条件;

从视图检索数据

从视图中检索数据，同从表中检索数据一样，只不过是只能看到视图所定义的那些列。

修改视图

删除视图

删除视图并不会删除数据，因为视图是基于数据库中的基表的虚表。

DROP VIEW 视图名

索引

概述

索引是一种特殊的数据库结构，可以用来快速查询数据库表中的特定记录。

索引是提高数据库性能的重要方式。

MySQL中，所有的数据类型都可以被索引。

特点

索引由数据库表中一列或多列组合而成，其作用是提高对表中数据的查询速度。

索引是创建在表上的，是对数据库表中一列或多列的值进行排序的一种结构。

索引可以提高查询的速度。通过索引，查询数据时可以不必读完记录的所有信息，而只是查询索引列。

索引分类

普通索引

惟一性索引

单列索引

全文索引

多列索引

空间索引

创建索引

创建表的时候创建索引

在已经存在的表上创建索引

使用ALTER TABLE语句来创建索引

删除索引

DROP INDEX 索引名 ON 表名 ;

事务

概述

事务特征

原子性（Atomicity）

事务就像“原子”一样，不可被分割，组成事务的DML操作语句要么全成功，要么全失败，不可能出现部分成功部分失败的情况。

一致性（Consistency）

一旦事务完成，不管是成功的，还是失败的，整个系统处于数据一致的状态。

隔离性（Isolation）

一个事务的执行不会被另一个事务所干扰。比如两个人同时从一个账户从取钱，通过事务的隔离性确保账户余额的正确性。

持久性（Durability）

也称为永久性，指事务一旦提交，对数据的改变就是永久的，不可以再被回滚。

事务处理

处理方式

begin开始一个事务

rollback事务回滚

commit 事务提交

MySQL默认是自动提交的，也就是你提交一个sql，就直接执行！

set autocommit = 0 禁止自动提交

set autocommit = 1 开启自动提交

隐式结束

隐式提交：当下列任意一种情况发生时，会发生隐式提交

执行一个DDL(CREATE、ALTER、DROP、TRUNCATE、RENAME）语句；

执行一个DCL(GRANT、REVOKE)语句；

隐式回滚：当下列任意一种情况发生时，会发生隐式回滚

客户端强行退出

客户端连接到服务器端异常中断

系统崩溃

4-6 用户和权限管理

权限表

通过网络连接服务器的客户对MySQL数据库的访问由权限表内容来控制。

user

记录允许连接到服务器的账号信息

存储了用户对某个数据库的操作权限，决定用户能从哪个主机存取哪个数据库。

host

存储了某个主机对数据库的操作权限，配合db权限表对给定主机上数据库级操作权限做更细致的控制。

tables_priv表

tables_priv表用来对表设置操作权限

columns_priv表

columns_priv表用来对表的某一列设置权限

procs_priv表

procs_priv表可以对存储过程和存储函数设置操作权限

权限系统的工作原理

概述

当MySQL允许一个用户执行各种操作时，它将首先核实用户向MySQL服务器发送的连接请求，然后确认用户的操作请求是否被允许。

访问控制分为两个阶段：

连接核实阶段

请求核实阶段

账户管理

创建新用户

使用CREATE USER语句创建新用户。

删除账户

使用DROP USER语句删除用户。

修改用户名

使用RENAME USER语句修改用户。

修改密码

使用ALTER USER语句修改密码

4-7 表分区管理

表为什么要分区

什么是表分区

当表分区通俗来讲就是允许把一个数据表根据一定的规则，跨文件系统划分成多个可以设置为任意大小的部分。

MySQL从5.1起开始支持表分区，MySQL安装后，默认是开启表分区支持的。

可以通过下面语句来查看你的MySQL是否支持分区：

show variables like '%partition%';

表分区的优点

表分区可以存储更多的数据

分区表的数据更容易维护

分区表的数据可以分布在不同的物理设备上

优化查询

性能的提升

分区类型

RANGE分区

LIST分区

COLUMNS分区

HASH分区

KEY分区

4-8 MySQL存储过程

MySQL 存储过程简介

存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。

MySQL 存储过程与自定义函数的区别

分支主题

优点

存储过程可封装，并隐藏复杂的商业逻辑。

存储过程可以回传值，并可以接受参数。

存储过程无法使用 SELECT 指令来运行，因为它是子程序，与查看表，数据表或用户定义函数不同。

存储过程可以用在数据检验，强制实行商业逻辑等。

缺点

存储过程，往往定制化于特定的数据库上，因为支持的编程语言不同。当切换到其他厂商的数据库系统时，需要重写原有的存储过程。

存储过程的性能调校与撰写，受限于各种数据库系统

4-9 MySQL存储引擎

MySQL 存储引擎简介

MySQL 相关知识点之并发处理

并发控制

当多个连接对数据进行修改时保持数据的一致性和完整性

锁

共享锁（读锁）

排它锁（写锁）

锁颗粒

表锁

开销小

行锁

开销大

MySQL 相关知识点之事务处理

MySQL 相关知识点之外键和索引

MySQL 各个存储引擎特点

MyISAM

InnoDB

Memory

CSV