MySQL的结构介绍和语句基本用法

数据库的作用和数据库的种类

什么是数据库

数据库（Database）就是按照数据结构来组织，存储和管理数据的仓库
专业的数据库就是管理数据的软件

作用及常见的数据库

对数据进行持久化的保存
方便数据的存储和查询，速度快，安全，方便
可以处理并发访问（操作同一个数据有先后，操作不同数据互不干扰的数据，可以并发操作）
安全的权限管理访问机制

常见数据库

数据库分两大类，一类是关系型数据库，另一类叫做非关系型数据库
关系型数据库：MySQL，Oracle，PostgreSQL，SQLserver等
非关系型数据库：Redis内存数据库，MongoDB文档数据库等

表引擎

1、 服务器处理客户端请求,通过电脑终端、图形化工具、编程语言发送连接请求

2、 存储引擎
MySQL服务器把数据的存储和提取操作都封装到一个叫存储引擎的模块里，实际上具体表的提取，数据的处理都是交给存储引擎来实现的，为了实现不同的功能，MySQL提供了各式各样的存储引擎，不同存储引擎管理的表具体存储结构可能不同，使用的存取算法也可能不同。
把连接管理、查询缓存、语法解析、查询优化这些并不涉及真实数据存储的功能划为给了MySQL server，把真实存取数据的功能划分给了存储引擎，各种不同的存储引擎向上边的MySQL server提供统一的调用接口（也就是存储引擎API），包含几十个底层函数
在MySQL完成查询优化后，只需要按照生成的执行计划调用底层存储引擎提供的API，获取数据后返回客户端
（1） archive
（2） blackhole
（3） csv
（4） federated
（5） merge
（6） ndb
常用
（7） innodb具备外键支持功能的事务存储引擎
（8） myisam主要的非事务处理储存引擎
（9） memory置于内存的表

3、 innodb和myisam表引擎的区别
（1） 事务支持
myisam不支持事务
innpdb支持事务
（2） 存储结构
myisam：每个myisam在磁盘上存储成三个文件
.frm文件存储表结构
.MYD文件存储数据
.MYI文件存储索引
Innodb：主要分成两种文件进行存储
.frm存储表结构
.ibd存储数据和索引（可能是多个文件.idb文件，或者是独立的表空间）
（3） 表锁差异
myisam：只支持表级锁，操作表的时候所有的操作都会加上锁，加锁后只有在
满足insert并发的情况下,可以在尾部插入新的数据
Innodb：支持事务和行级锁
（4） 表主键
myisam：允许没有任何索引和主键的表存在，索引都是保存行的地址
innodb：如果没有设置主键或者非空唯一索引，就自动生成一个6字节的主键
（用户不可见），数据是主索引的一部分，附加索引保存的是主索引的值。Innodb的主键范围更大，最大是myisam的两倍
（5） 表的具体行数
（6） CURD操作
（7） 外键
（8） 查询效率
（9） myisam和innodb两者的应用场景

MySQL基础指令

MySQL基础操作

使用方式：
方式一：通过在命令行敲命令操作
方式二：通过图型界面工具
方式三：通过编程语言执行mysql命令

SQL语句快捷键

\G格式化输出(文本显示、竖立显示)
\s查看服务器端信息
\c结束命令输入操作
\q退出当前sql命令行模式
\h查看帮助
★SQL语句可以换行，要以分号结尾，表名库名创建只能是字母数据下划线

库和表的操作

//登录操作(root是最高权限用户名,p是密码,初始时没有密码)
mysql -u root -p

//查看所有数据库
show databases;

//创建新的数据库
create database 库名 default charset=utf8mb4;

//使用数据库
use 库名;

//删除数据库
drop database 库名

//创建新的表
create table 表名（
	字段名 类型 字段约束，
	字段名 类型 字段约束，
	字段名 类型 字段约束
）engine=innodb default charset=utf8mb4；

//查看某一数据库中的所有表
show tables;

//删表
drop 表名

//添加表字段（列）
alter table 表名 add 字段名 数据类型 字段约束
（精确位置有after、frist表头）

//修改字段
语法格式： alter table 表名 change|modify 被修改的字段信息 
change: 可以修改字段名， modify: 不能修改字段名。 
# 注意：一般情况下，无特殊要求，不要轻易修改表结构
例如：
修改字段类型
alter table user midify num tinyint not null default 12；
修改字段名num为mm且改名为mm
alter table user change num mm int

//删除字段
alter table 表名 drop 字段名 

//修改表中的自增的值
# 在常规情况下，auto_increment 默认从1开始继续递增 
alter table users auto_increment = 1000; 

//

数据库数据类型

数据类型：整型、浮点型、字符串、日期

字符串数据类型
★串值用‘’引住

1、定长串char（1~255个字符的定长串）

接受长度固定的字符串，其长度是在创表前指定的。
指定长度后，就会分配固定的存储空间存放数据。（不指定默认为1）
例如：char（4）定义长度为4的字符串，最大长度不能超过4，并且实际存储
不管存储多少个字符，它都占用4个字符位置和空间

2、变长串varchar

存储可变长度的字符串
例如：varchar（7）如果实际插入4个字符，那么它只占4个字符位置，
而且插入的数据最大长度不能超过7
★MySQL处理定长列比处理变长列快得多，因为varchar要计算存储空间，char的
存储空间是固定不变的

3、其他字符串类型

3、text变长文本类型存储（最大长度为64K）
4、longtext（和text相同，最大长度为4GB）
5、mediutext（和text相同，最大长度为16K）
6、tinytext（和text相同，最大长度为255字节）
7、emum枚举类型（接受最多64K个串组成的一个预定义集合的某个串）
例如 emum（‘男’，‘女’）
8、set（接受最多64K个串组成的一个预定义集合的零个或多个串）

（2）数值类型
	存储的数据取值范围越大，需要的存储空间越多

1、 int整形（-2147483648~2147483647）
2、 tinyint整数形（一个字节8位，-128_{127，如果为unsigned，为0}255）
3、 decimal精度可变浮点值
4、 float单精度
5、 double双精度
例如decimal（5，2）表示数值总共为5位，小数占2位
一般表示货币
★所有数值数据类型（除bit和boolean外）都可以有符号或者无符号
★有符号数值列可以存储正或负值
★无符号数值列只能存储正值（像int就会变为0~42亿）
★默认为有符号，限制为unsigned就是无字符

（3）日期和时间类型

1、date表示YYYY-MM-DD
2、time表示hh：mm：ss
3、datetime表示date和time的组合（8字节）
4、timestamp功能和datetime相同（但范围比较小）
5、year用两位数字表示，范围为70（1970）~69（2069），用四位数字表示，范围
是1901年~2155年

（4）二进制数据类型

可以存储任何数据

表字段约束

（1）unsigned无符号（数值类型使用，限定为正数）
（2）字段类型后面加括号限制宽度
		字符串类型是限制长度
		Int（4）没有意义，默认无符号为int（11），有符号为int（10）
		Int（4）unsigned zerofill只有当数值类型设置成前导零时，设置int的长度才
有意义，显示为四位数0001、0002

（2） not null
（3） default设置默认值
（4） primary key主键不能为空且唯一，一般和自动递增一起使用
（5） auto_increment定义列为自增属性，一般用于主键，数值会自动加1
（6） unique唯一索引（数据不能重复）可以增加查询速度，但是会降低插入和更新速度

数据的CURD（增删查改）

//添加数据
1：标准添加（指定所有字段所有值）
insert into stu（id，name，age，sex，classid）value（1，'张三'，20，'男'，'1712231'）
2：指定部分字段添加
insert into stu（name，classid）value（'李四'，'135845'）
3：不指定添加
insert into stu value （null，'王五'，21，'男'，'13546'）
4：批量添加
insert into stu values 
(null,'zhaoliu',25,'w','lamp94'),   
(null,'uu01',26,'m','lamp94'),  
(null,'uu02',28,'w','lamp92'),    
(null,'qq02',24,'m','lamp92'),   
(null,'uu03',32,'m','lamp138'),    
(null,'qq03',23,'w','lamp94'),    
(null,'aa',19,'m','lamp138'); 

//修改数据
update stu set sex='m',classid='lamp92' where id=2 or id=4;
update stu set sex='m',classid='lamp92' where id in(12,14);(两者等价)

//删除数据
delete from stu where id=100；
delete from stu where id>=20 and id<=30; 
delete from stu where id between 20 and 30;
delete from stu where id>200; 

字符集

字符集和乱码

编码、解码（字符串与二进制的映射）
（1） ASCII字符集（1字节）
（2） ISO 8859-1字符集
可变长编码：
（3） GB2312字符集（1字节或2字节）
（4） GBK字符集
（5） utf8（1~4个字节表示）
MySQL的utf8是utf8mb3只用1~3个字节表示字符
utf8mb4才是真正的utf8字符集
（6） utf16（2或4字节）
（7） utf32（4字节）
★查看字符集show charset

数据库的导出导入

//在新的数据库中 导入备份的数据，导入导出的sql文件 
mysql -u  root -p ops < ./tlxy.sql
//把导出的表sql 导入数据库 
mysql -u  root -p ops < ./tlxy-tts.sql
//导出数据库
mysql -u root -p 库名 > 路径/库名.sql
//导出数据表
mysql -u root -p 库名 表名 > 路径/库名-表名.sql

权限管理

mysql中的root用户是数据库中权限最高的用户，千万不要用在项目中。 可以给不同的用户，或者项目，创建不同的mysql用户，并适当的授权，完成数据库的相关操作
这样就一定程度上保证了数据库的安全。
语法格式：
grant 授权的操作 on 授权的库.授权的表 to 账户@登录地址 identified by ‘密码’;

//在mysql中 创建一个 zhangsan 用户，授权可以对tlxy这个库中的所有表 进行 添加和查询 的权限 
grant select,insert on tlxy.* to zhangsan@'%' identified by '123456';
 
//用户 lisi。密码 123456 可以对tlxy库中的所有表有 所有操作权限 
grant all on tlxy.* to lisi@'%' identified by '123456';
 
//删除用户 
drop user 'lisi'@'%';

千万不要干删库跑路这种事，不仅缺德，还犯法