mysql数据库

一、数据库的基本概念

数据

描述事务的符号记录
包括数字，文字、图形、图像、声音、档案记录等
以“记录”形式按统一的格式进行存储

表

将不同的记录组织一起
用来存储具体数据

数据库

表的集合，是存储数据的仓库
以一定的组织方式存储的相互有关的数据集合

数据库管理系统（DBMS）

是实现对数据库资源有效组织、管理和存取的系统软件

数据库系统

是一个人机系统，由硬件、OS、数据库、DBMS、应用软件
和数据库用户组成
用户可以通过DBMS或应用程序操作数据库

数据库表中行是记录，列是字段
每一行称为一个记录，用来描述一个对象的信息
每一列称为一个字段，用来描述对象的一个属性

关系型数据库：Oracle、MySQL 、SQLServer、Sybase、Informix、access、DB2、FoxPRO
非关系型数据库：Redis、mangodb、memcached、postgresql

非关系型数据库

非关系数据库也被称作NoSQL，以K-V键值对进行存储
存储数据不以关系模型为依据，不需要固定的表格式
非关系型数据库的优点
1、数据库可高并发读写
2、对海量数据高效率存储与访问
3、数据库具有高扩展性与高可用性

nosql非关系型·数据库（比如redis）主要是把一些数据拿到内存/缓存上，比如说tomcat访问mysql数据库内容 ，redis先把数据放到内存\缓存上，tomcat可以更快的访问到，其次可以减轻mysql数据库压力。通常会把一些高热数据放到内存/缓存上

redis可以把保存在内存/缓存上的数据持久化。

关系型数据库和非关系数据库的区别

关系型数据库存储的往往式字符、字符串、数值、布尔值等。
非关系型数据库存储的往往式图片、视频、语音等。
关系型数据库存储在磁盘里，非关系型数据库存储在内存\缓存中。

MySQL语句

SQL语言分类：

DDL：数据定义语言，用于创建数据库对象，如库、表、索引等,删除数据库和表
DML：数据操作语言，用于对表中的数据进行管理
DQL：数据查询语言，用于从数据表中查找符合条件的数据记录
DCL：数据控制语言，用于设置或者更改数据库用户或角色权限

---

DDL

查看数据库信息

mysql> show databases;

进入库

mysql> use 库名称;

创建数据库

create database dyf;

查看当前库下的所有表

mysql> show tables;

创建表

 create table shudent (id int not null,name varchar(20) not null,score decimal(5,2) not null,age int(5),address varchar(50) default '汉族',primary key(id));

创建临时表
临时表创建成功之后，使用show tables命令是看不到创建的临时表的，临时表会在连接退出后被销毁。（quit 数据库 直接消失）
如果在退出连接之前，也可以可执行增删改查等操作，比如使用drop table语句手动直接删除临时表。PS：无法创建外键

create temporary table 表名 (字段1 数据类型，字段2 数据类型[, ...][,primary key(主键名)]);

拓展：当表不存在的时候创建

create table if not exists xsxinxi1(id int(5) zerofill primary key auto_increment not null,name varchar(10) not null,address varchar(50) default '江苏');
 #zerofill 当数字不满int里5位  就会自动补全  00001
#auto_increment：自动增长

克隆表

create table 新表名 like 旧表名;

备份内容

insert into 克隆表 select*from 被克隆的表;

克隆表并将表里面的数据添加到新的数据表里面

create table 新表名(select*from 旧表名);  #但是没有主键、唯一键

删除表

drop table 表名;
drop table 库名.表名;

显示表的结构（字段）

mysql> describe/desc 表名称;                  #完整写法   数据库名.表名

获取数据表的表结构、索引等信息

show create table 表名\G

Field：字段名称
Type：数据类型
Null：是否允许为空
Key：主键
Default：默认值
Extra：扩展属性，例如：标志符号（标识了种子，增量/步长）

常见的数据类型：

int ：整形      用于定义整数类型的数据
float ：单精度浮点4字节32位       准确表示到小数点后六位
double ：双精度浮点8字节64位 
char ：固定长度的字符类型     用于定义字符类型数据
#char如果存入数据的实际长度比指定长度要小，会补空格至指定长度，如果存入的数据的实际长度大于指定长度，低版本会被截取，高版本会报错。
varchar ：可变长度的字符类型
text ：文本
image ：图片
decimal(5,2) ：5个有效长度数字，小数点后面有2位    指定长度数组
  # 截取会四舍五入    截断直接就截断

---

DML

查看数据表中所有内容

select * from 表名;

插入数据

insert into shudent (id,name,score,age,address) values (1,'丁宇飞',100,99,'杭州');
 insert into shudent values(2,'定于飞',88,99,'上海'),(3,'丁与非',89,98,'苏州');   # 插入两条数据

插入加密密码

 insert into student values(1,'张三',password('123'));

update更新原有数据

update 表名 set 字段名1=字段值1[,字段名2=字段值2] [where 条件表达式];

 update student set name='wang',password='' where id=1;  修改多个字段

delete：删除不需要的数据（表内容）这样主要删除的是行
#delete 清空表后，返回的结果内有删除的记录条目；
delete工作时是一行一行的删除记录数据的；如果表中有自增长字段，使用delete from删除所有记录后，再次新添加的记录会从原来最大的记录ID后面继续自增写入记录。 可以基于日志进行恢复

delete from 表名   删除表里面所有信息  
delete from 表名 [where 条件表达式];

truncate删除表里面数据；
#truncate清空表后，没有返回被删除的条目；truncate工作时是将表结构按原样重新建立，因此速度上truncate会比delete清空快；使用truncate table清空表内数据后，ID会从1开始重新记录

truncate table xinxi;     

DQL

select 字段名1,字段名2[,...] from 表名[where 条件表达式];

查看表中前几行数据

select*from shudent limit 2;

select*from shudent limit 2,3;  选取表中第二行下后三行数据

DCL

alter 修改表名和表结构

alter table 旧表名 rename 新表名;

扩张表结构(增加字段)

alter table 表名  add address varchar(50) [not null][default '地址不详'];

修改字段名，添加唯一键
唯一键特性：数值唯一，可以是空，但空也只能出现一次

alter table school change id id_new int unique key;

删除字段

alter table 表名 drop 字段;

小结

drop
属于DDL，不可回滚，不可带where ，表内容和结构都删除，，删除速度快

truncate
属于DDL，不可回滚，不可带where，表内容删除，删除速度快

delete
属于DML，可回滚，可以带where，表结构记录在，删除速度慢，一行一行删除的。

日志

数据库日志文件用于备份，恢复，故障定位、分析是最核心的部分
mysql与oracle日志有所区别
mysql写一条数据，同步到日志中一条
oracle：重做日志组
一个组中至少3个日志成员，轮流存储日志
还会有另一个组与之同步/备份

存储引擎

myisam和innodb
myisam：快速读取，不支持事务
innode ：更注重写，支持事务

Mysql中6中常见的约束/规则

主键约束（primary key）
外键约束（foreign key）
非空约束（not null）
唯一性约束（unique [key | index]）
默认值约束（default）
自增约束（auto_increment）

外键的定义：如果同一个属性的字段在表一中是主键，而在表二中不是主键，那么该字段称为表二的外键。
把公共键作为主键的表是父表、主表，把公共键作为外键的表是从表、外表。
与外键关联的主表的字段必须设置位主键，要求从表不能是临时表，主表外键字段和从表字段具备相同的数据类型、字符长度和约束。

创建主键约束，主键名建议以“PK_”开头

alter table 主表名 add constraint PK_xxx primary key(xxx);

再为从表添加外键约束，并将从表中的字段和主表中的字段建立外键关联。

alter table 从表名 add constraint FK_xxx foreign key(xxx) references 主表名(xxx)

创建外键 防止误删，修改，保证数据的完整性和一致性
主表从表插入数据 先插入主表，再插入从表。
主表从表删除数据 先删除从表，再删除主表。
主表和从表 删除主键与外键 需要先删除主键，再删除外键

数据库用户管理

新建用户

create user '用户名'@'来源地址' [identified by [password] '密码'];

用户名：指定将创建的用户名
来源地址：指定新创建的用户可在哪些主机上登录，可使用IP地址、网段、本地主机名localhost，任意用户用%
密码：若使用明文密码，直接输入’密码’，插入到数据库时有mysql在自动加密；
若使用加密密码，需要先使用select password(‘密码’)；获取密文，再在语句中添password ，若省略“identified by”部分，用户的密码将为空（不建议使用）

运用加密的密码

查看用户信息
创建后的用户保存再mysql数据库的user表里

use mysql;
select user,authentication_string,Host from user;

重命名用户

rename user '旧用户名'@'localhost' to '新用户名'@'localhost';

删除用户

drop user 'lisi'@'localhost';

修改当前用户密码

set password = password('abc123')
#默认修改当前登录用户的密码

修改其他用户密码
set password for ‘user1’@‘localhost’ = password(‘abc123’);

忘记数据库root密码的解决方法

1、修改/etc/my.cnf 配置文件，免密登录

vim /etc/my.cnf
[mysql]
skip-grant-tables       #使登录mysql不使用授权表

systemctl restart mysqld
mysql # 直接登录

2、使用sql语句修改密码
update mysql.user set authentication_string = password(‘abc123’) where user=‘root’;

flush privileges; 刷新数据库
quit
mysql -u root -pabc123
最后还要 把 /etc/my.cnf 里添加的内容删除

数据库用户提权

grant 权限列表 on 数据库名.表名 to '用户名'@'来源地址' [identified by '密码'];

#权限列表：用于列出授权使用的各种数据库操作，以逗号进行分隔，如“select,insert,update”。使用’all’标识所有授权，可授权执行任何操作。
#数据库名.表名：用于指定授权操作的数据库和表的名称，其中可以使用通配符" * "。例如，使用"dyf.*“标识授权的操作对象为dyf数据库中的所有表
#identified by：用于设置用户连接数据库时所使用的密码字符串。在新建用户时，若省略"identified by” 部分，用户密码将为空。
#用户名@来源地址：指定新创建的用户用户名和可在哪些主机上登录，可使用IP地址、网段、本地主机名localhost，任意用户用%

允许用户zhangsan在本地查询school数据库中所有表的数据记录，但禁止查询其它数据库中表的记录。

grant select on school.* to 'zhangsan'@'localhost' identified by 'abc123';

#这个首先时创建了这个用户，赋予了登录USAGE登录的基础权限，然后限制了select

允许用户list在所有终端远程连接mysql，并拥有所有权限

grant all [privileges] on *.* to 'list'@'%' identified by 'abc123';

flush privileges; #刷新权限

查看权限

show grants for '用户名'@'%'；

撤销权限

revoke 权限列表 on 数据库名.表名 from '用户名'@'来源地址';

需要精准撤销权限

revoke all on * . * from ‘list’@’%’;
#USAGE权限只能用于数据库登录，不能执行任何操作；USAGE权限不能被回收，即revoke不能删除用户
flush privileges;