mysql数据库的管理
目录
一、常用的数据类型
二、MySQ数据库基础操作
1、登录数据库
2、查看当前的 MySQL 版本信息及连接用户名
3、查看当前服务器中的数据库
4．查看数据库中包含的表
5．查看表的结构（字段）
6、MySQL的6大约束属性
三、SQL语句
1）DDL语句（管理数据库对象）
1、创建新的数据库
2、创建新的表
3、 查看库或表
4、删除指定数据表（慎重）
5、删除指定数据库（慎重）
6、克隆表
7、清空表
8、创建临时表
9、外键约束
2）DML语句（管理数据记录）
1、表中插入新数据记录
2、查询数据记录
3、修改、更新数据表中的数据记录
4、在数据表中删除指定的数据记录
3）DQL语句（管理表名和表结构）
1、修改表名
2、扩展表结构（增加字段）
3．修改字段（列）名，添加唯一键
4、删除字段
5、添加主键（一个表只能有一个主键）
6、删除主键
4）DCL语句（管理用户管理）
1、新建用户
2、修改用户名
3、删除用户
4、修改用户密码
5、数据库用户授权
一、常用的数据类型
| int(n) | 整型类型。4个字节，无符号[0,2^32-1]，有符号[-2^31,2^31-1] ^次方 |
| tinyint(n) | 整型类型。1个字节，范围（-128~127） |
| smallint(n) | 整型类型。2个字节，范围（-32768~32767） |
| float(n,m) | 单精度浮点：8位精度，4字节32次方位。n为总个数，m为小数位数，如字段定义float(4,2)，12.1212则存入的是12.12 |
| double(n,m) | 双精度浮点：16精度，8字节64次方位。n为总个数，m为小数位数 |
| char （n） | 固定长度的字符类型,（n）指定长度。 指定长度为n，不管数据是几个字节都会占用n个字节的空间。多于n个字符也只占用n个字符的空间。 查询速度快，删除修改不会产生碎片。 |
| varchar（n） | 可变长度的字符类型。varchar模式能够节约磁盘空间，适合存放地址数据。 指定长度为n，空数据占用1个结束符字节长度；n个字符数据会占用n+1个字节空间；多于n个字符也是占用n+1个字符空间。 节约空间，但删除修改会产生碎片需要额外定时清理。 |
| text | 长文本，没有长度限制，最多65535 |
| image | 图片 |
| decimal(5,2) | 5个有效长度数字，小数点后面有2位 |
注意：int(n)单独使用时n不起任何作用，配合zerofill自动往前填充0直到满足n位数。
二、MySQ数据库基础操作
1、登录数据库
企业中很多使用navicat来管理数据库，非常简单。此软件也有命令行功能，以下截图均来自软件命令行工具。
#字符界面登录
[root@centOS2 ~]# mysql -u root -pAbc123
2、查看当前的 MySQL 版本信息及连接用户名
select version();
select user();
3、查看当前服务器中的数据库
#查看所有库
show databases;
初始状态下的MySQL会附带4个系统数据库
information_schema 主要存储系统中的一些数据库对象信息，如数据库名，数据库的表，表栏的数据类型与访问权限等 mysql 主要存储系统的用户权限信息 performance_schema 主要存储数据库服务器性能参数信息 sys MySQL5.7之后引入的一个新的 sys 数据库，sys 库里面的表、视图、函数以及存储过程可以让用户快速了解MySQL的一些信息，它的数据来源于performance_schema
4、查看数据库中包含的表
#切换库，查看表
use 库名;
show tables;
#不切换库直接查看表
show tables from 库名;
5、查看表的结构（字段）
#切换库查看表结构，describe可缩写为desc
use 库名;
describe 表名; 
#不切换库直接查看表结构
desc 库名.表名;
6、MySQL的6大约束属性
| 主键约束 primary key | 字段的值不能重复，且不能为null，一个表只能有一个主键 |
| 唯一键约束 unique key | 字段的值不能重复，但可以为null，一个表可以有多个唯一键 |
| 非空约束 not null | 字段的值不能为null |
| 默认值约束 default 'XXX' | 字段的值如果没有设置值则会使用默认值自动填充 |
| 自增约束 auto increment | 字段的值如果没有设置值会默认从开始每行自动递增1，而且要求设置自增的字段必须为主键 |
| 外键约束 foreiqn key | 用来保证外键表和主键表相关联的数据的完整性和一致性 |
键值对 key/value kv key名:value host:www.xy101.com
三、SQL语句
SQL语句是用于维护管理数据库，包括数据查询、数据更新、访问控制、对象管理等功能。大部分的关系型数据库都采用SQL语句作为它们的管理语句，所以掌握MySQL的SQL语句很容易掌握Oracle和SQL server的SQL语句。
SQL语言分类
| DDL | 数据定义语言，用于创建数据库对象，如库、表、索引等 |
| DML | 数据操纵语言，用于对表中的数据进行增删改操作管理 |
| DQL | 数据查询语言，用于从数据表中查找符合条件的数据记录 |
| DCL | 数据控制语言，用于设置或者更改数据库用户或角色权限 |
1）DDL语句（管理数据库对象）
1、创建新的数据库
create database 数据库名;
2、创建新的表
#切换库在创建表
use 库名；
create table 表名 (字段1 数据类型,字段2 数据类型[,...][,primary key (主键名)]);
主键名是表中很重要的字段，可以使字段每个值不能重复且不能为空，主键字段只能有一个，能实现排序功能；#不切换库直接创建表
create table 库名.表名 (字段1 数据类型,字段2 数据类型[,...][,primary key (主键名)]);
3、查看库或表
show databases; #查看所有库
show tables from 库名; #查看库中所有表
desc 库名.表名; #查看库中指定表的结构
4、删除指定数据表（慎重）
#先切换到库里再删除表
use 库名；
drop table 表名; #不用切换到库直接删除表
drop table 数据库名.表名;
5、删除指定数据库（慎重）
drop database 库名;
6、克隆表
方法一：可实现表结构和表数据与旧表都一样
create table 新表名 like 旧表名; #克隆表结构
insert into 新表名 select * from 旧表名; #克隆表数据 
方法二：表数据和旧表是一样的，新表的结构和旧表的不一定一样
create table 新表名 (select * from 旧表名); 
7、清空表
delete清空表是一条一条的删除记录，清空表效率较慢；AUTO_INCREMENT自增字段仍保持原有的记录；
delete from 表名; 
truncate清空表是直接重建表，清空表效率较快；AUTO_INCREMENT自增字段的记录也会重置；
truncate table 表名;
8、创建临时表
临时表跟一般的表的相同点是都可以进行增删改查表数据；区别是show tables; 查不到临时表，临时表只能再当前会话中有效，退出当前会话，临时表就会失效。
create temporary table 表名 (....); 
9、外键约束
如果同一个字段x在表一中是主键，在表二中不是主键，且字段的数据类型、字符长度和约束类型设置都相同，则能称字段x为表二的外键，表一可称为主表或父表，表二可称为外表或从表（注意从表不能为临时表）。
外键约束能够保证数据的完整性和一致性（插入数据时，需要先插入主键表的公共字段的相关数据，才能在外键表插入相关联的数据；删除数据时，需要先保证外键表已经没有相关数据，才能在主键表删除相关联的数据）。
主键表创建主键约束
alter table 主表名 add primary key (公共字段);外键表创建外键约束
alter table 从表名 add foreign key(公共字段) references 主表名(公共字段);
删除外键
alter table 表名 drop foreign key 外键别名;删除主键
alter table 表名 drop key 键名;2）DML语句（管理数据记录）
1、表中插入新数据记录
方法一
insert into 表名(字段1,字段2[,...]) values (字段1的值,字段2的值,...);
方法二
insert into 表名 values (字段1的值，字段2的值...);
2、查询数据记录
use 库名；
方法一：查看表中指定字段和值
select 字段1,字段2,字段3... from 表名;
方法二：查看表中所有字段和值
select * from 表名;
select 字段1，字段2... from 表名 where 条件表达式; 
#分页查询
select 字段 from 表名 limit N; #查看表的前N行的记录
select 字段 from 表名 limit N,M; #查看表的第N行之后的连续M行的记录（不包含第N行）
当一个表的字段非常多显得很乱时，可以用此命令单列依次查看字段数据（mysql客户端才支持此命令）
select * from 表名\G；
3、修改、更新数据表中的数据记录
update 表名 set 字段名1=字段值1[,字段名2=字段值2] [where 条件表达式];
#[]表示可加可不加。如果不加where语句，会默认把所有行的字段改掉
4、在数据表中删除指定的数据记录
delete from 表名 [where 条件表达式];
3）DQL语句（管理表名和表结构）
1、修改表名
alter table 旧表名 rename 新表名;
2、扩展表结构（增加字段）
#添加字段，指定数据类型，默认值为XX
alter table 表名 add 字段 数据类型(n) default 'XX';
3、修改字段（列）名，添加唯一键
alter table 表名 change 旧列名 新列名 数据类型 [unique key];
#unique key可以给表中多个字段设置
4、删除字段
#删除表中某个字段
alter table 表名 drop 字段名;
5、添加主键（一个表只能有一个主键）
alter table 表名 add primary key(字段);
6、删除主键
alter table 表名 drop primary key；
4）DCL语句（管理用户管理）
1、新建用户
create user '用户名'@'来源地址' identified by [password] '密码';
来源地址可以是 localhost、IP、网段、%代表任意地址、主机名，[identified by password '密码']不加表示无密码登录，不建议无密码
#'root'@'localhost'表示本地用户，创建本地用户登录的账号和密码
create user '用户名'@'localhost' identified by '密码';
#查看用户
select user(); 
#（了解）密文方法创建'root'@'%'形式的某个主机登录的用户
select user,host,authentication_string from mysql.user; #host表示登录主机，authentication_string表示密文保存密码的字段。
select password('指定密码'); 这一步为了生成密文密码
#密文方法创建某个主机登录的用户
create user '用户名'@'%' identified by passwd '密文';
2、修改用户名
rename user '用户名'@'来源地址' to '新用户名'@'来源地址';3、删除用户
drop user '用户名'@'来源地址';4、修改用户密码
set password for '用户名'@'来源地址' = password('密码'); #只能root用户使用此命令修改
set password =password ('新密码'); #只能修改当前用户密码
alter user '用户名'@'来源地址' identified by '密码'; #只能root用户使用此命令修改面试题：如何找回 root 密码？
1）修改MySQL配置文件，在 [mysqld] 配置项下面添加配置 skip-grant-tables
2）重启MySQL服务，使用 mysql 命令即可直接登录数据库
3）执行命令 update mysql.user set authentication_string=password('密码') where user='root'; 来修改root用户密码，flush privileges；刷新后退出
4）还原MySQL配置文件注释掉添加的配置，再重启MySQL服务，即可使用命令 mysql -u root -p密码 [-h 服务器地址 -P 端口] 登录数据库
5、数据库用户授权
刚创建的新用户可用权限很少，默认情况下只有登录权限，没有操作数据库的权限，所以创建用户后需要授权。
#在5.7或之前支持 grant 创建新用户和授予权限，8.0开始只能用于授予权限.all表示所有权限
grant 权限1,权限2,.... on 库名.表名 to '用户名'@'来源地址' [identified by '密码']; all [privileges] *.*（代表 任意库.任意表）
#刷新生效
flush privileges;
#查看用户有哪些权限
show grants for '用户名'@'来源地址'; 
#收回权限，all表示收回所有权限
revoke 权限列表 on 库名.表名 from '用户名'@'来源地址';
revoke all on 库名.表名 from '用户名'@'来源地址'; 实操题
创建一张名为table_fp的表格，含有以下字段：dm(代码)，hm(号码)，rq(日期)，bz(标志)，xfhm(销方公司号码)，xfmc(销方公司名称)，gfhm(购方公司号码)，gfmc(购方公司名称)，je(总金额)，se(总税额)，主键为dm和hm字段。现要求做如下操作：插入数据，修改数据，删除数据，查询数据第3-5行数据，最后把一个字段的名字改了。
use mydb； create table table_fp (dm int(4) zerofill auto_increment, hm int, rg varchar(11) not null, bz varchar(4) not null unique key, xfhm int not null, xfmc varchar(20) not null, gfhm int not null, gfmc varchar(20) not null, je float(6,2), se float(6,2), primary key(dm,hm));
insert into table_fp (hm,rg,bz,xfhm,xfmc,gfhm,gfmc,je,se) values (125123123,2024-05-06,'01',125123123,'company',125123456,'zhongtong',2340.5567,332.1153);insert into table_fp (hm,rg,bz,xfhm,xfmc,gfhm,gfmc,je,se) values (125121212,2024-05-09,'07',125121212,'company',125123456,'yuantong',1111.5567,2323.11);insert into table_fp (hm,rg,bz,xfhm,xfmc,gfhm,gfmc,je,se) values (125121999,2024-06-10,'03',125121999,'company',125123456,'yunda',5123.5,1100.19);insert into table_fp (hm,rg,bz,xfhm,xfmc,gfhm,gfmc,je,se) values (125555555,2024-06-14,'05',125555555,'company',125123456,'debang',1058.230,80.1011);insert into table_fp (hm,rg,bz,xfhm,xfmc,gfhm,gfmc,je,se) values (125888888,2024-06-14,'06',125888888,'company',125123456,'jitu',2098.5,200.1011);insert into table_fp (hm,rg,bz,xfhm,xfmc,gfhm,gfmc,je,se) values (125125125,2024-06-19,'02',125125125,'company',125123456,'shunfeng',4011.22,1001.1);
delete from table_fp where dm=0001;
select * from table_fp limit 3,5;
alter table table_fp change se sumtax float(6,2);
