MySQL数据库管理

一、sql语句：

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

二、SQL语言分类：

DDL：数据定义语言，用于创建数据库对象，如库、表、索引等
DML：数据操纵语言，用于对表中的数据进行管理
DQL：数据查询语言，用于从数据表中查找符合条件的数据记录
DCL：数据控制语言，用于设置或者更改数据库用户或角色权限

三、常用的数据类型：

int：整型    用于定义整数类型的数据
float：单精度浮点4字节32位        准确表示到小数点后六位double：双精度浮点8字节64位
char：固定长度的字符类型        用于定义字符类型数据。
varchar：可变长度的字符类型        
text：文本
image：图片
decimal(5,2)：5个有效长度数字，小数点后面有2位        指定长度数组
 
char的长是固定的，varchar会根据数据实际长度储存，但存储性能没有char好，需要根据应用场景自行匹配。

四、DDL数据定义：

 1.DDL库的创建和使用：

1.创建库：

create database 库名;

删除库：

drop database 库名;

 更改库：

alter database 字符编码;

4.查看库：

show databases;

show create database 库名; #查看库结构

5.使用当前库：

use 库名;

2.DDL表的管理：

     2.1 创建表：

1.创建表：
 
CREATE TABLE 表名(字段1 数据类型,字段2 数据类型[,...] [,PRIMARY KEY (主键名)]);
 
示例：
create table www (id int, name varchar(5), sex char(1));

 2.2 删除表：

删除表：
drop table 表名;        #删除表
truncate table 表名     #删除表数据

  2.3 更改表：

更改表：
1.添加字段：
alter table 表名 add 字段名 字段类型;
 
2.修改字段：
alter table 表名 change 旧字段 新字段 数据类型 [字段属性];
 
3.删除字段
alter table 表名 drop 字段;
 
4.修改表名：
alter table 旧表名 rename 新表名;

 2.4 表查询：

1.查询当前所有表：
show tables;
 
2.查询表结构：
desc 表名;
 
3.查询建表语句：
show create table 表名;

五、DML数据操纵：

 1.DML添加数据：

1.给指定字段添加数据：
insert into 表名 (字段1, 字段2, ...) values (字段1的值, 字段2的值, ...);
 
2.给全部字段添加数据：
insert into 表名 values (字段1的值, 字段2的值, ...);        #要按照表结构的字段顺序设置值

2.DML修改数据：

update 表名 set 字段1=值, ... where 条件表达式;
 
  不添加where修改整表。

  3.DML删除数据：

delete from 表名 where 条件表达式;

六、DQL数据查询：

1.查看所有字段
select * from 表名 [where 条件表达式];
 
2.查看指定字段
select 字段1,字段2,... from 表名 [where 条件表达式];
 
3.纵向查看每行记录字段的值
select * from 表名\G   
 
4.分页查询：
select * from 表名 limit N;      #查看表的前N行记录
select * from 表名 limit N,M;    #查看表的前N行之后的连续M行记录（不包含第N行

七、DCL数据库权限控制：

  1.用户管理：

1.查看当前登录的用户
select user();
 
2.创建用户：
create user '用户名'@'源地址'   identified by '密码';
                      localhost/IP/网段/主机名/%
 
3.修改用户名和允许访问的主机：
rename user '旧用户名'@'源地址' to '新用户名'@'源地址';
 
4.修改用户密码：
set password for '用户名'@'源地址' = password('密码');
alter user '用户名'@'源地址' identified by '密码';

 2.权限管理：

1.指定用户授权：
grant 权限1,权限2,....  on 库名.表名   to  '用户名'@'源地址'  [identified by '密码'];     #5.7版本支持创建用户和权限授权，8.0版本只能用于权限授权
      all                     *.*
 
2.查询权限：
show grants for '用户名'@'源地址';
 
3.撤销权限：
revoke 权限1,权限2,....  on 库名.表名  from  '用户名'@'源地址';

八、MySQL 的约束：

 1.常用约束类型：

primary key      主键约束    字段的值不能重复，且不能为null，一个自建的表只能有一个主键
unique key       唯一性约束  字段的值不能重复，能为null，一个表可以有多个唯一键
not null         非空约束    字段的值不能为null
default          默认值约束  字段的值如果没有设置则使用默认值自动填充
auto_increment   自增约束    字段的值如果没有设置默认会从1开始每次自动递增1，要求自增字段必须设置主键
foreign key      外键约束    保证相关联的表数据的完整性和一致性
int(N) zerofill  零填充
 

create table www (id int primary key auto_increment, name varchar(5), sex char(1) not null);

2.创建创建外键约束：

  注意：

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

    删数数据记录时，要先从表再主表，也就是说删除主键表的记录时必须先删除其他与之关联的表中的记录。

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

 3.查看和删除外键约束：

show create table student;
desc student;
 
alter table student drop foreign key FK_pro;
alter table student drop key FK_pro;

九、数据库高级操作：

 1.克隆表：

create table 新表 like 旧表;                #克隆表结构
insert into 新表 (select * from 旧表);      #克隆表数据 可实现表结构和表数据与旧表都一样
 
create table 新表 (select * from 旧表);     #表数据与旧表一样，表结构与旧表可能不一样

2.创建临时表：

  临时表可以跟普通的表一样增删改查表中的数据，但是show tables是查看不到的，临时表只能在当前会话中有效，在其它会话中或者退出当前会话连接，临时都会失效

create temporary table 表名 (....);

 3.找回 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';
 
FLUSH PRIVILEGES;
quit
 
mysql -u root -pabc123
 
注意：最后再把 /etc/my.cnf 配置文件里的 skip-grant-tables 删除，并重启 mysql 服务。