MySQL数据库和表管理

@_Orange—

已于 2023-10-17 19:27:41 修改

阅读量1.2k

点赞数

文章标签：数据库 mysql

于 2023-08-24 11:14:04 首次发布

本文链接：https://blog.csdn.net/weixin_46254171/article/details/132447994

版权

一、常用的数据类型

int：整型用于定义整数类型的数据
float：单精度浮点4字节32位准确表示到小数点后六位double：双精度浮点8字节64位
char：固定长度的字符类型用于定义字符类型数据。
varchar：可变长度的字符类型
text：文本
image：图片
decimal(5,2)：5个有效长度数字，小数点后面有2位指定长度数组

注：Char如果存入数据的实际长度比指定长度要小，会补空格至指定长度，如果存入的数据的实际长度大于指定长度，低版本会被截取，高版本会报错

二、查看数据库结构

1、查看当前服务中的数据库

show databases;    #查看数据库

2、查看数据库中存在的表

方法一：
use  数据库名;     #切换库
show  tables;     #查看库中的表
 
方法二：
 show tables from 数据库名;    #直接查看某个库中的表
 例：show tables from mysql;

3、查看表结构

 方法一：
 use 数据库名;     #切换库
 desc 表名;     #查看指定表的结构
 
 方法二：
 desc [数据库名.]表名;
 可缩写成：desc 数据库名.表名;
 例：desc mysql.user;

三、 SQL语言分类

（1）DDL：数据定义语言（Data Definition Language），用于创建数据库对象，如库、表、索引等。

如：CREATE，DROP，ALTER 等。

（2）DML：数据操纵语言（Data Manipulation Language），用于对表中的数据进行管理。

如： UPDATE、INSERT、DELETE 等。

（3）DQL：数据查询语言（ Data Query Languag ），用于从数据表中查找符合条件的数据记录。

如： SELECT

（4）DCL：数据控制语言（Data Control Language），用于设置或者更改数据库用户或角色权限。

如： GRANT，REVOKE

四、创建、删除数据库和表

1、创建数据库

create database 库名;    #创建数据库

2、创建表

 CREATE TABLE 表名(字段1 数据类型,字段2 数据类型[,...] [,PRIMARY KEY (主键名)]);
 
 #主键一般选择能代表唯一性的字段，不允许取空值(NULL)，值也不允许重复，主键字段的值是唯一的。一个表只能有一个主键。
 
  USE school;                  #切换school库
 CREATE TABLE class (id int NOT NULL,name char(10) NOT NULL,score decimal (6,2),passwd char(48) DEFAULT '',PRIMARY KEY (id));        #新建表class
 
 #第一个字段名id，数据类型是int整数型，NOT NULL不许为空值。
 #第二个字段名name，数据类型是char固定长度的字符，长度是10个字节。
 #第三个字段名score，数据类型是decimal (6,2)，表示5个有效数字、其中小数点后2位。
 #第四个字段名passwd，DEFAULT '' 表示默认值是无值（不是空值null）。
 #PRIMARY KEY (id)，表示id字段是主键。
 
 DESC class;    #查看class表的结构

3、删除数据表

drop table [数据库名.]表名;
 
例：drop table class01;

4、删除数据库

drop database 数据库名;
 
例：drop database  school;

五、管理数据表中的记录（增删改查）

1、向表中插入新的信息（增）

方法一：为所有字段插入值
 insert into 表名 values (所有字段的值);    #每个字段值用逗号相隔；
 
方法二：为指定字段插入值
 INSERT INTO 表名(字段1,字段2[,...]) VALUES (字段1的值，字段2的值，...);    #注意字段的属性not null，则必须为该字段插入值

select * from class; #查看表中数据

方法一：

方法二：

2、修改、更新数据表中的数据记录(改)

UPDATE 表名 SET 字段名1=字段值1[,字段名2=字段值2] [WHERE 条件表达式];

3、在数据表中删除指定的数据记录（删）

格式:
DELETE FROM 表名 [WHERE 条件表达式];

示例:
DELETE FROM class WHERE id=1;

4、查询表中记录（查）

格式：
SELECT 字段名1,字段名2[,...] FROM 表名[WHERE 条件表达式];

示例：
SELECT * FROM zzz;               #显示所有数据
SELECT id,name,score FROM zzz WHERE id=2;   #查看指定字段
select name from zzz\G  		 #以列表方式竖向显示
select * from info limit 2;   	 #只显示头2行
select * from info limit 2,3;     #显示第2行后的前3行

六、修改表名和表结构

1、修改表名（rename）

格式：
ALTER TABLE 旧表名 RENAME 新表名;
例：
ALTER TABLE zzz RENAME yyy;

2、扩展表结构(增加字段)

ALTER TABLE test ADD address varchar(50) default '地址不详';
#default '地址不详'：表示此字段设置默认值为地址不详，可与NOT NULL配合使用

3、删除字段

格式：
ALTER TABLE 表名 DROP 字段名;

4、删除指定的数据记录

格式:
delete from 表名 where 条件表达式;

示例:
delete from zzz where id=4;

七、数据表高级操作

1、克隆表

方法一：
create table test1 like test;    #复制格式，通过LIKE方法，复制yyy表结构生成yyy2表
insert into test1 select * from test;      #备份内容

方法二：
show create table test\G   #获取数据表的表结构、索引等信息
create table test1(...);    #复制表结构
insert into test1 select * from test;  #导入数据

方法三：
create table test1 (SELECT * from test);   #复制test 表数据到test1中

方法一：

方法二：

方法三：

2、清空表

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

方法二：
truncate table test;
#TRUNCATE清空表后，没有返回被删除的条目: TRUNCATE 工作时是将表结构按原样重新建立，
#因此在速度上TRUNCATE会比DELETE清空表快;使用TRUNCATE TABLE 清空表内数据后，
#ID会从1开始重新记录

#删除
速度
drop> truncate > delete
安全性
delete 最好

方法一：

方法二：

3、临时表

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

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

4、数据库用户授权

授权用户权限 all privileges 代表了哪些权限呢？

insert（插入数据）
select(查询数据)
update（更新表的数据）
delete （删除表中的数据）
drop (删除库和表)
cerate （创建库，表）
index （创建索引）
alter （更改表的属性）
create view （创建视图）
create routine （创建存储过程）
alter routine （修改存存储过程）
event （事件）
trigger on （创建触发器）

4.1 本地授权

格式：
grant 权限列表 on 数据库名.表名 to '用户名'@'来源地址' [identified by '密码'];
例1：
grant select on beijin.* to 'zgc'@'localhost' identified by '123456';
#允许用户zgc在本地查询beijin数据库中所有表的数据记录，但禁止查询其他数据库中的表的记录。

flush privileges;    #刷新权限
quit
mysql -u zgc -p123456

#权限列表：用于列出授权使用的各种数据库操作，以逗号进行分隔，如“select, insert, update”。使用"all"表示所有权限，可授权执行任何操作。

#数据库名.表名：用于指定授权操作的数据库和表的名称，其中可以使用通配符"*"。
例如，使用“kgc.*"表示授权操作的对象为school数据库中的所有表。

#'用户名@来源地址'：用于指定用户名称和允许访问的客户机地址，即谁能连接、能从哪里连接。来源地址可以是域名、IP地址，还可以使用“%”通配符，表示某个区域或网段内的所有地址，如“%.xyw.com"、“192. 168.80.%”等。

#IDENTIFIED BY：用于设置用户连接数据库时所使用的密码字符串。
在新建用户时，若省略“IDENTIFIED BY"部分，则用户的密码将为空。

4.2 远程授权

grant select on beijin.* to 'zgc'@ '%' identified by '123456';
#允许用户zgc在所有终端远程连接mysql，并拥有所有权限。
flush privileges; #刷新

打开远程连接工具

未授权前：无法登录

进行远程授权设置：

4.3 查看权限、撤销权限

#查看权限
格式：
show grants for 用户名@来源地址;
例：
show grants for 'zgc'@'%';

#撤销权限
格式：
revoke 权限列表 on 数据库名.表名 from 用户名@来源地址;
#USAGE权限只能用于数据库登陆，不能执行任何操作; USAGE权限不能被回收，即REVOKE不能删除用户。
flush privileges; #刷新

八、数据库用户管理

1、新建用户

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

#来源地址：localhost(主机名)、IP地址、网段、所有(%通配符)
#密码：

1. 若使用明文密码，直接输入'密码'

2. 使用select password('密码')；获取密文，再在语句中添password '密文'

明文密码创建用户：

密文密码创建用户：

2、查看用户信息

创建后的用户保存在mysql 数据库的user表里
use mysql;
select user,authentication_string,Host from user;

3、重命名指定

格式：
例：
rename user 'lisi'@'localhost' to 'zhangsan'@'localhost';

4、删除用户

格式：
drop user '用户名'@'来源地址';

5、修改当前密码

格式：
set password=password('密码');

6、修改其他用户密码

格式：
set password for '用户'@'来源地址'=password('密码');

7、忘记root密码的解决办法

#修改/etc/my.cnf 配置文件，免密登陆mysql
vim /etc/my.cnf
[mysqld]
skip-grant-tables    #添加，使登录mysql不使用授权表

systemctl restart mysqld
mysql    #直接登录

#然后使用SQL语句修改密码
update mysql.user set authentication_string=password('123456') where user='root';

flush privileges;
quit
mysql -u root -pabc123
#最后再把/etc/my.cnf 配置文件里的skip-grant-tables 删除，并重启mysql服务