Mysql 数据库管理

manny
324 阅读14分钟

Mysql 数据库管理

数据库基本操作

数据库–>数据表–>行（记录）：用来描述一个对象的信息 列（字段）：用来描述对象的一个属性

1.1 常见的数据类型

int整型
floct单精度浮点 4字节32位
double双精度浮点 8字节64位
char固定长度的字符类型
varchar可变长度的字符类型
text文本
decimal（5,2）5个有效长度数字，小树点后面有2位

char与varchar的区别

img

  • char只能保存4个字节的长度，多了不看，少了用空格替代。
  • vachr最多保存4个字节，默认有一个结束符，最多4+1。
  • vachar对比char更能节约磁盘空间，但是增删改的过程中会产生磁盘碎片，导致读写效率没有char好。

1.2 查看数据库结构

1.2.1 查看当前服务器的数据库
 #大小写不区分，分号“;”表示结束
 SHOW DATABASES;

image-20220523011525059

1.2.2 查看数据中包含的表
 USE 数据库名；
 SHOW TABLES;
 ​
 SHOW TABLES FROM 数据库名；    #用于直接查看表

image-20220523012350648

直接查看表

image-20220523012524703

1.2.3 查看表的结构
 USE 数据库名；
 DESCRIBE 表名；
 可缩写成:DESC 表明；
 ________________________________________________
 #跳过直接查看表的结构
 DESCRIBE  [数据库名].[表名]
 ------------------------------------------------
 #以命令的形式查看表结构
 SHOW CREATE TABLE 表名；

切换到库再查看表；

image-20220523013626049

跳过库直接查看表的结构

image-20220524214100739

以命令的形式查看表结构

image-20220526235234397

1.3 SQL语句的类型

SQL语句用于维护管理数据库，包括数据查询、数据更新、访问控制、对象管理等功能。

SQL语句分类：

语句代表的意思
DDL数据定义语言，用于创建数据库对象，如库、表、索引等（create ）
DML数据操纵语言，用于对表中的数据进行管理(insert drop delete update )
DQL数据查询语言，用于从数据表中查找符合条件的数据记录(select )
DCL数据控制语言，用于设置或者更改数据库用户或角色权限(grant )

1.4 新增删除数据库的库和表

1.4.1 创建新的数据库
 create database 数据库名；

image-20220524215318547

1.4.2 创建新的表

主键一般选择能代表唯一性的字段不允许取空值（NULL），一个表只能有一个主键。

 CREATE TABLE 表名 (字段1 数据类型,字段2 数据类型 [,...][,PRIMARY KEY (主键名)]);

实例：

 use banji
 create table 18ban(id int,name char(10) not null,age int,sex char(4),score decimal(5,2))

image-20220524232354835

1.4.3 删除指定的数据表
 use库名；
 drop table表名；
 ----------------------------------
 drop table 库名.表名；   #库外删除指定表

use库名； drop table表名；

image-20220524233532070

drop table 库名.表名；

image-20220524233848796

1.4.4 删除指定的数据库
 drop database 库名；

image-20220524234324478

删除了库后库下面的表也会被删除

1.5 管理表中的数据记录

1.5.1 项数据表中插入插入新的数据
 insert into 表名 (字段1，字段2 [,.....]) values (字段1的值，字段2的值，.....);
 ​
 insert into 表名  values (字段1的值，字段2的值，.....);#不指定字段则会按照表结构的顺序插入数据

示例：

 creat database class;
 create table ky18 （id int,name char(10) not null,age int,sex char(4),score    decimal(5,2));     ##这步就是前面的创建表，就是先把标的列名先定义好，下面根据列名插入对应的行内容
 show tables;
 insert into ky18 (id,name,age,sex,score) values(1,'张三',18,'男',66.5);

新建库和表

image-20220525002246356

插入数据

image-20220525003023823

这里的select * from ky18为查询语句

image-20220525003324040

如果在表中插入数据时不指定字段，会按照默认的字段排序匹配

总结：

1.5.2 查询数据记录
 SELECT 字段名1,字段名2[,...] FROM 表名 [WHERE 条件表达式];
 ------------------------------------------------------------------------------------
 select * from 表名; 
 #这里的*表示所有字段则会按照表结构的顺序显示
 ​
 select 字段1，字段2，.... from 表名;  
 #指定字段查询数据记录
 ​
 select * from 表名 \G;  
 #以列表方式竖向显示数据记录
 ​
 select * from 表名 limit n; 
 #表示查看头n行的数据记录
 ​
 select * from 表名 limit n，m; 
 #表示查看n行后的m行数据记录
 ​
 select 字段1，字段2.... from 表名 where 条件表达式;  
 #指定表内容查询相关数据记录

select * from 表名;

image-20220525004631408

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

image-20220525004855795

select * from 表名 \G;

image-20220525005118081

select * from 表名 limit n;

image-20220525005847304

select * from 表名 limit n，m;

image-20220525010309127

select 字段1，字段2.... from 表名 where 条件表达式;

image-20220525010747549

注意：中文需要用' '，

可以使用and和or同事查看多个条件，or只要满足一个条件即可，and需要同事满足1和2两个条件

1.5.3 修改、更新数据中的数据记录
  UPDATE 表名 SET 字段名1=字段值1[,字段名2=字段值2] [WHERE 条件表达式]; 
  #这里注意使用的时候一定要加where语句去匹配条件不然会修改指定字段的所有内容

image-20220525011547488

1.5.4 在数据表中删除指定的数据记录
  DELETE FROM 表名 [WHERE 条件表达式];

image-20220525012031337

1.6 修改表名和表结构

1.6.1 修改表名
 ALTER TABLE 旧表名 RENAME 新表名;

image-20220525012918806

1.6.2 扩展表结构（增加字段）
 ALTER TABLE 表名 ADD 字段名 数据类型 default 'default值';
 #default 'default值'：表示此字段设置默认值 未填写则自动填入默认值；可与 NOT NULL 配合使用

image-20220525015613936

1.6.3 修改字段（列）名，添加唯一键

唯一键： unique key 主键： primary key

共同点：字段的值都是唯一性，不允许有重复的值 不同点：一个表中 只能有1个主键，但是可以有多个唯一键 主键字段中不允许有null值，唯一键是允许有null

 ALTER TABLE 表名 CHANGE 旧列名 新列名 数据类型 [unique key];

image-20220526151517366

1.6.4 指定字段为主键
 ALTER TABLE 表名 ADD PRIMARY KEY (字段名);
 #设置为主键后字段的值得是唯一

image-20220526152951692

1.6.5 删除字段

  ALTER TABLE 表名 drop 字段名;

image-20220526153138559

使用 if 判断 创建表并测试自增和填充（案例）

按行创建表，更加有利于我们操作

 use class;
 ​
 create table if not exists info(
 id int(4) zerofill primary key auto_increment,
 name char(20) not null default '匿名',
 sid int(18) not null unique key,
 hobby varchar(50));
 ​
 ​
 -------------------------------------------------------------------------------------
 注释：
 - if not exists：表示检测要创建的表是否已存在，如果不存在就继续创建
 - int(4) zerofill：表示若数值不满4位数，则前面用“0”填充，例0001
 - auto_increment：表示此字段为自增长字段，即每条记录自动递增1，默认从1开始递增；自增长字段数据不可以重复；自增长字段必须是主键；如添加的记录数据没有指定此字段的值且添加失败也会自动递增一次
 - unique key：表示此字段唯一键约束，此字段数据不可以重复；一张表中只能有一个主键, 但是一张表中可以有多个唯一键
 - not null：表示此字段不允许为NULL

创建表

image-20220526211032124

测试自增和填充

image-20220526211901176

重点：自增长字段数据不可以重复；自增长字段必须是主键；如添加的记录数据没有指定此字段的值且添加失败也会自动递增一次

1.7 数据表高级操作

1.7.1 克隆表的结构
 CREATE TABLE 新表名称 like 旧表名称； #克隆表的结构

image-20220526232600933

1.7.2 克隆表的数据
 INSERT INTO 要导入数据记录的表名 SELECT * FROM 想要的数据表名;

image-20220526233048926

1.7.3 创建表的同时导入表结构和数据
 CREATE TABLE 新表名称 ( SELECT * FROM 想要的数据表名);

image-20220526233437470

1.7.4 清空表--删除表内的所有数据
 方法1
 DELETE FROM 表名；
 #DELETE清空表后，返回的结果内有删除的记录条目；DELETE工作时是一行一行的删除记录数据的；如果表中有自增长字段，使用DELETE FROM 删除所有记录后，再次新添加的记录会从原来最大的记录 ID 后面继续自增写入记录。 

 方法2
 TRUNCATE TABLE 表名；
 #TRUNCATE 清空表后，没有返回被删除的条目；TRUNCATE 工作时是将表结构按原样重新建立，因此在速度上 TRUNCATE 会比 DELETE 清空表快；使用 TRUNCATE TABLE 清空表内数据后，自增auto_increment会从 1 开始重新记录

方法1

image-20220527002058810

方法2

image-20220527002341163

1.7.5 创建临时表

临时表创建成功之后，使用SHOW TABLES命令是看不到创建的临时表的，临时表会在连接退出后被销毁。 如果在退出连接之前，也可以可执行增删改查等操作，比如使用 DROP TABLE 语句手动直接删除临时表。

 CREATE TEMPORARY TABLE 表名 （字段1 数据类型，字段2 数据类型[,....] [,PRIMARY KEY (主键名)]）；

 #添加临时表test
 create temporary table test (
 id int(4) zerofill primary key auto_increment,
 name varchar(10) not null,
 cardid int(18) not null unique key,
 hobby varchar(50));
 ​
 #查看当前库中所有表
 show tables;    
 #在临时表中添加数据
 insert into test values(1,'老六',123456789,'running');    
 #查看当前表中所有数据
 select * from test;
 #退出数据库
 quit                        
 ​
 #重新登录后进行查看      
 mysql -u root -p
 use kgc;
 #查看之前创建的临时表中所有数据，发现已经被自动销毁
 select * from test;
 ​

image-20220527003326966

image-20220527003459568

临时表只有当前终端的用户用select * from表名可以看到，其他终端的用户是看不到的

1.7.6 创建外键约束，保证数据的完整性和一致性
  • 外键的定义：如果同一个属性字段x在表一中是主键，而在表二中不是主键，则字段x称为表二的外键。

主键表和外键表的理解：

  • 以公共关键字作主键的表为主键表（父表、主表）
  • 以公共关键字作外键的表为外键表（从表、外表）

注意：与外键关联的主表的字段必须设置为主键。要求从表不能是临时表，主从表的字段具备相同的数据类型、字符长度和约束。

 create table kc (cid int,cname char(10));
 #创建主表名为kc字段1 cid int类型字段2 cname char类型10字节
 ​
 create table xy (xid int,xname char(10),age int,cid int);
 #创建从表名为xy 字段1 xid int类型 字段2 xname char类型10字节字段3 age int类型 字段4 cid int类型
 ​
 alter table kc add constraint pk_cid primary key(cid);
 #为主表添加一个主键约束，主键名一般以pk开头（不强求）
 ​
 alter table xy add constraint fk_cid foreign key(cid) references kc(cid);
 #为从表添加外键并将从表的cid字段与主表的cid字段建立关联，外键名一般以fk开头（不强求）
 ​
 #插入新的数据记录时要先插入主表的在插入从表的
 insert into kc values(1,'云计算');
 insert into kc values(2,'大数据');
 insert into kc values(3,'java');
 ​
 insert into xy values(2,'张三',18,1);
 insert into xy values(1,'李四',20,3);
 insert into xy values(3,'王五',22,2);
 ​
 #删除数据记录时要先删除从表在删主表，也就是说删除主键表必须先删除其他与之关联的表中记录
 delete from xy where cid=1;
 delete from kc where cname='云计算';
 select * from xy;
 select * from kc;
 ​

1.8 数据库用户管理

新建用户

‘用户名’：指定将创建的用户名

  • ‘来源地址’：指定新创建的用户可在哪些主机上登录，可使用IP地址、网段、主机名的形式，本地用户可用localhost，允许任意主机登录可用通配符%

‘密码’：

  • 若使用明文密码，直接输入’密码’，插入到数据库时由Mysql自动加密；
  • 若使用加密密码，需要先使用SELECT PASSWORD(‘密码’); 获取密文，再在语句中添加 PASSWORD ‘密文’;
  • 若省略“IDENTIFIED BY”部分，则用户的密码将为空（不建议使用）
1.8.1 使用明文创建用户
 1.新建用户
 CREATE USER '用户名'@'来源地址' [ IDENTIFIED BY [PASSWORD] '密码']：

image-20220529100147654

1.8.2 使用密文创建用户
 select password('123123');;    #获取密文
 #创建用户
 create user 'xixi'@'localhost' identified by '*E56A114692FE0DE073F9A1DD68A00EEB9703F3F1';
 #退出
 quit
 #验证登录
 mysql -u xixi -p123123

image-20220529100513207

1.8.3 查看用户信息
  • 创建后的用户保存在 mysql 数据库的 user 表里
 use myaql;  #切换库
 #查看表结构
 desc user;
 #指定字段用户，登录认证字符串，登录主机，查看数据记录
 select User,authentication_string,Host from user;

image-20220529105821886

1.8.4 重命名用户
 rename user '原用户名'@'登录主机' to '新用户名'@'登录主机';

image-20220529110238466

1.8.5 删除用户
 DROP USER '用户名'@'登陆主机'；

image-20220529110518359

1.8.6 修改当前用户名密码
 SET PASSWORD = PASSWORD('123123');

image-20220529110923397

1.8.7 修改其他用户密码
 set password for '用户名'@'登录主机'='password'（'新的密码'）

image-20220529111727081

1.8.8 忘记 root 密码的解决办法

 #修改配置文件，添加配置，使登录mysql不使用授权表
 vim /etc/my.cnf
 ​
 #添加此行
 skip-grant-tables
 systemctl restart mysql
 mysql#登录数据库
 ​
 #使用update修改root密码，刷新数据库 
 update mysql.user set authentication_string = password('123456') where user='root';
 flush privileges; 
 #退出
 quit
 ​
 #编辑配置文件
 vim /etc/my.cnf
 #删除添加的配置
 skip-grant-tables
 #登录
 mysql -u root -p123456
 ​

临时修改配置文件，添加配置，使登录mysql暂时不需要使用授权表

image-20220529145946944

重启服务，并尝试直接登录mysql

image-20220529151950500

使用update修改root密码，刷新数据库

image-20220529152723069

删除前面再/etc/my.cnf配置文件下添加的内容

image-20220529152853493

使用新密码登录成功

image-20220529153116240

1.8.9 数据库用户授权

GRANT语句：专门用来设置数据库用户的访问权限。当指定的用户名不存在时，GRANT语句将会创建新的用户；当指定的用户名存在时，GRANT 语句用于修改用户信息。

 GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'来源地址' [IDENTIFIED BY '密码'];
  • 权限列表：用于列出授权使用的各种数据库操作，以逗号进行分隔，如 “select,insert,update” 。使用“all”表示所有权限，可授权执行任何操作。
  • 数据库名.表名：用于指定授权操作的数据库和表的名称，其中可以使用通配符“”。例如，使用“kgc.”表示授权操作的对象为 kgc数据库中的所有表。
  • ‘用户名@来源地址’：用于指定用户名称和允许访问的客户机地址，即谁能连接、能从哪里连接。来源地址可以是域名、IP 地址，还可以使用“%”通配符，表示某个区域或网段内的所有地址，如“%.accp.com”、“192.168.80.%”等。
  • IDENTIFIED BY：用于设置用户连接数据库时所使用的密码字符串。在新建用户时，若省略“IDENTIFIED BY”部分， 则用户的密码将为空。
实例1

允许用户peng在本地查询class数据库中所有表的数据记录，但禁止查询其他数据库中的表的记录

 #验证用户peng是否可以登录
 ------------------------------------------------
 mysql -u peng -p123123#登录用户
 show databases;  #查看所有库
 quit退出
 ​
 #使用root权限增删其他用户访问库或表的权限；
 ------------------------------------------------
 mysql -u root -p123123  #登录root用户
 grant select on class.* to 'peng'@'localhost' identified by '123123';  #授权*表示库下的所有表也可以指定表
 quit #退出
 ​
 #登录peng账号，查看权限是都修改成功
 ------------------------------------------------
 mysql -u peng -p123123  #用户登录验证
 show databases;    #查看所有库
 use class;     #切换库
 show tables;   #查看所有表
 select * from ky18;   #查看表所有字段数据记录

验证用户peng是否可以登录

image-20220529171037087

使用root权限增删其他用户访问库或表的权限；

image-20220529171932083

登录peng账号，查看权限是都修改成功

image-20220529172156644

实例2

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

 mysql -u root -p123456  #使用root用户登录
 use mysql;#切换库
 select User,authentication_string,Host from user; #查看所有用户
 grant all on *.* to 'shu'@'%' identified by '123123'; 
 #给与xixi用户授权允许查看所有库所有表所有主机登录并给与密码1999612
 select User,authentication_string,Host from user;
 #再次查看用户表会发现多了一个xixi用户
 quit#退出
 ​
 mysql -u shu -p123123  #使用shu用户登陆了
 或
 mysql -u 用户 -p123123 -h 目标ip/主机 -P 端口号
 ​
 show databases;  #查看当前所有库
 use class;   #切换库
 show tables;  #查看所有表
 select * from ky18;   #查看表内所有段数据记录

image-20220529195640461

新建用户并给予他查看权限

image-20220529200226766

验证

image-20220529203445076

1.8.10 查看用户权限
 SHOW GRANTS FOR '用户名'@'来源地址';

image-20220529203738360

1.8.11 撤销权限
 REVOKE ALL ON *.* FROM '用户'@'来源地址'；
 #撤销所有权限
 #也可以撤销单独权限如select,insert,update

image-20220529203952323

取消所有操作权限后，默认只会留下登录权限

\

avatar
文章
阅读
粉丝