【Mysql】 MySQL数据库——管理基本操作 SQL语句

一.MySQL数据类型

1.常用的数据库类型

int	整型   无符号[0,2^32-1]，有符号[-2^31,2^31-1]
float	单精度浮点    4字节32位
double	双精度浮点    8字节64位
char	固定长度的字符类型
varchar	可变长度的字符类型
text	文本
image	图片
decimal(5,2)	5个有效长度数字，小数点后面有2位

2.char和varchar区别

值	CHAR(4)	存储需求	VARCHAR(4)	存储需求
''	' '	4个字节	''	1个字节
'ab'	'ab '	4个字节	'ab '	3个字节
'abcd'	'abcd'	4个字节	'abcd'	5个字节
'abcdefgh'	'abcd'	4个字节	'abcd'	5个字节

CHAR和VARCHAR类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被

保留等方面也不同。在存储或检索过程中不进行大小写转换

字节大小

• char无论是否有值，都会占用固定长度的字节大小，保存在磁盘上都是4字节
• varchar在保存字符时，默认会加一个隐藏的结束符，因此结束符会多算一个字节

优劣比较

• varchar比char节省磁盘空间
• 但varchar类型的数据读写速度比char慢，因为char是连续的磁盘空间，e而varchar在多次增删改查中会产生一些磁盘空间碎片

二、管理MySQL的数据库 

相关命令操作

登录数据库： 

[root@localhost ~]# mysql -uroot -pabcd1234  #-u用户名 -p密码

1.查看数据库结构

1）查看当前的MySQL版本信息及连接用户名

SELECT VERSION();        //查看当前的 MySQL 版本信息
 
SELECT USER();         //查看当前的 MySQL连接用户名

2）查看当前服务器中的数据库：

show databases;            //当前服务器中的数据库
show databases\G         //当前服务器中的数据库

3）查看数据库中包含的表

show tables from 库名;
或者
use 库名;
show tables;

4）查看表的结构（字段）

use 库名;
describe 表名; 可简写为desc 表名;

或者
desc 库名.表名;

2.SQL语言

关系型数据库，都是使用SQL语句来管理数据库中的数据。

SQL，即结构化查询语言(Structured Query Language) 。

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

SQL中的名词介绍

• 数据库：database
• 表：table，行：row 列：column
• 索引：index
• 视图：view
• 存储过程：procedure
• 存储函数：function
• 触发器：trigger
• 事件调度器：event scheduler，任务计划
• 用户：user
• 权限：privilege

数据库系统中，SQL 语句不区分大小写；可单行或多行书写，默认以 " ; " 结尾。
数据库名表名用户名区分大小，必须以字母开头后续可以包括字母数字和三个特殊字符（# _ $）

SQL语言分类：

DDL:数据定义语言，用于创建数据库对象，如库、表、索引等.

DML:数据操纵语言，用于对表中数据进行管理（增、删、改）.

DQL:数据查询语言，用于从数据表中查找符合条件的数据记录.

DCL:数据控制语言，用于设置或者更改数据库用户或权限.

1、DDL数据定义语言

用于创建数据库对象，如库、表、索引等

1、库的增删改查；

create database 库名; #创建库

drop database 库名;  ##删除库

show databases;  ##查看有哪些库

use 库名;  ##切换库

2、表的增删改查；

use 数据库名；  #先切换库
create table 表名 （字段1 数据类型，字段2 数据类型，.....,primary key（设为主键的字段名））； 
#创建数据表结构
#主键一般选择能代表唯一性的字段不允许取空值（NULL)，一个表只能有一个主键

create table [库名.]表名 (字段1 数据类型 [约束属性],字段1 数据类型 [约束属性],.....);
##创建数据表结构
  
drop table 表名;
drop table [库名.]表名;
##删除表 谨慎！！
 
desc [库名.]表名;
##查询表结构 以表格形式显示
show create table [库名.]表名;
##以命令的形式显示表结构
 
 
alter table [库名.]旧表名 rename [库名.]新表名;

 

3、字段的增删改查；

alter table [库名.]表名 add 字段名称 字段数据类型 [约束属性];
##表中新增一个字段，一定要跟上数据的属性
 
alter table [库名.]表名 add 约束属性;
##可以直接添加表的约束属性 如primary key(字段名) 或者 unique key(字段名) 
 
alter table [库名.]表名 change 旧字段名 新字段名 字段数据类型 [约束属性];
##修改一个字段的名称或者数据类型
 
alter table [库名.]表名 drop 字段名;
##删除一个字段
 
desc table [库名.]表名;
##表格形式查询表结构
 
desc table [库名.]表名\G;
##纵向查询表结构
 
show create table [库名.]表名;
##命令形式查看表结构

4、六大约束属性以及应用场景

• primary key 表示主键约束 ；主键字段的值必须是唯一且不可重复，也不能为空，一个表中，只能有一个主键
• unique key 表示唯一键约束；唯一键字段的值不能重复，但是可以为空，一个表中可以有多个唯一键；
• not null 表示非空约束；表示该字段的值不能为空；
• default 表示默认值约束；当该字段的数据为空时，使用默认值填充；
• auto_increment 表示自增约束，字段如果没有设置值，那么数据会默认从1开始自增长，且自增字段必须为主键
• foreign key 表示外键约束，用于确保外键表和主键表的数据完整和一致性
• int(N) zerofill 表示N位数据零填充

2、DML语言 数据库操作语言

数据库的数据增删改查

insert into [库名.]表名(字段1,字段2,....)values(字段1的值,字段2的值,....)
insert into [库名.]表名 values(所有字段的值 一一对应);
##添加数据记录
 
update [库名.]表名 set 字段名=字段值 where 条件表达式 [and/or 条件表达式2];
##修改数据的值
 
delete from [库名.]表名 where 条件表达式 [and/or 条件表达式2];
##删除数据的值
 
delete from [库名.]表名;
##清空数据表，只剩表结构

3、DQL语言 数据库查询语言

对数据库中的数据查询）

select * from [库名.]表名;
##查看表中的所有字段对应的所有值
 
select 字段1,字段2,... from [库名.]表名 where 条件表达式 [and/or 条件表达式2];
##查询某些字段 满足条件的数据
 
select * from [库名.]表名 limit N;
##查询前N行，包括第N行的数据
 
select * from [库名.]表名 limit N,M;
##查询第N行以后，不包含第N行，后面M行的数据

4、DCL语言 用于对用户权限的管理

1、账号的增删改查，密码的修改与查询

create user '用户名'@'源地址' identified by '密码';
##创建用户
 
rename user '用户名'@'源地址' to '新用户名'@'新源地址';
##修改用户名
 
drop user '用户名'@'源地址';
##删除用户
 
select user,host,authentication_string from mysql.user;
##查看有哪些用户信息

alter user 'liqi'@'localhost' identified by 'abc123';
 
update mysql.user set authentication_string=password('123456') where user='liqi';
 
set password for 'liqi'@'localhost' =password('abc123');
 
三种修改密码的方式

2、账号权限的设置

show grants for '用户名'@'源地址';
 
grant 权限列表 on 库.表 to '用户名'@'源地址';
 
revoke 权限列表 on 库.表 from '用户名'@'源地址';

5、克隆数据表的两种方式与区别

##方法一：
create table 新表 like 旧表;                 #克隆表结构
insert into 新表 (select * from 旧表);       #克隆表数据        可以实现表结构和表数据与旧表都一致
 
##方法二：
create table 新表 (select * from 旧表);      #表数据和旧表一致，表结构和旧表不一定一致

6、临时表的创建

create temporary table temp(id int primary key,name varchar(20),age int);
 
 
create temporary table 表名 (....);       
临时表跟普通表一样可以进行增删改查，但是show tables是查不到的；
                                          
临时表只能在当前会话中有效，退出当前会话或在其它会话中，临时表都会失效

7、外键约束

保证数据的完整性和一致性

外键的定义：如果同一个属性字段X在表一中是主键，而在表二中不是主键，则字段X称为表二的外键。

主键表和外键表的理解：
（1）以公共关键字作主键的表为主键表（父表、主表）
（2）以公共关键字作外键的表为外键表（从表、外表）

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

 1.创建主从表

#创建主表 profession
create table profession(proid int primary key auto_increment,proname varchar(20));
 
#创建从表 student
 create table student(id int primary key,name varchar(10) unique key,age int,proid int);
 
#为从表 student 表添加外键，并将 student 表的 proid 字段和 profession 表的 pid 字段建立外键关联。外键名建议以“FK_”开头。
alter table student add constraint FK_01 foreign key (proid) references profession(proid);
 
desc student;

2.主从表中插入数据

#插入新的数据记录时，要先主表再从表
insert into profession values(1,'云计算');
insert into profession values(2,'大数据');
insert into student values(1,'zhangsan',18,1);
insert into student values(2,'lisi',19,1);
insert into student values(3,'wangwu',20,2);

3.主从表中删除数据

#删数数据记录时，要先从表再主表，也就是说删除主键表的记录时必须先删除其他与之关联的表中的记录。
delete from student where proid=1;
delete from profession where pid=1;

4.删除外键约束

#查看和删除外键约束
show create table student;
desc student;
alter table student drop foreign key FK_pro;
alter table student drop key FK_pro;

8、忘记root密码 该如何处理

（1）修改 /etc/my.cnf 配置文件，不使用密码直接登录到 mysql
vim /etc/my.cnf
[mysqld]
skip-grant-tables					#添加，使登录mysql不使用授权表
 
systemctl restart mysqld            #重启服务
 
mysql								#直接登录
 
（2）使用 update 修改 root 密码，刷新数据库
update mysql.user set authentication_string=password('abc123') where user='root' and host='localhost';

vim /etc/my.cnf

systemctl restart mysqld 

mysql -u root -pabc123
 
注意：最后再把 /etc/my.cnf 配置文件里的 skip-grant-tables 删除，并重启 mysql 服务。