MySQL数据库管理

本章目录

数据库基本操作
数据库用户授权

查看数据库结构

• 登入数据库

[root@www ~]# mysql -uroot -pabc123    （-p后面加登入密码，一般生产环境中这样登入）
[root@www ~]# mysql -u root -p         直接回车
Enter password:                          输入登录密码

• 查看数据库信息

mysql> show databases;      一般都是以；结束

• 进入库

mysql> use mysql   进入到mysql的一个库
mysql> show tables;     查看当前库下的所有表
mysql> describe user    显示字段属性 ；查看数据库结构

常用的数据类型

int：整形 用于定义整数类型的数据
float：单精度浮点4字节32位 准确表示到小数点后六位
double：双精度浮点8字节64位
char：固定长度的字符类型 用于定义字符类型数据
varchar：可变长度的字符类型 varchar设置上线
text：文本
image：图片
decimal（5，2）：5个有效长度数字，小数点后面有两位 指定长度数组
#char 如果存入数据的手机长度比指定长度要小，会补空格至指定长度，如果存入的数据的实际长度大于指定长度，低版本会被截取，高版本会报错

数据库管理SQL语句

• SQL语言
  Structured Query Language的缩写，即结构化查询语言
  关系型数据库的标准语言
  用于维护管理数据库：
  包括数据查询、数据更新、访问控制、对象管理等功能
• SQL语言分类:
  DDL:数据定义语言，用于创建数据库对象，如库、表、索引等
  DML:数据操纵语言，用于对表中的数据进行管理
  DQL:数据查询语言，用于从数据表中查找符合条件的数据记录
  DCL:数据控制语言，用于设置或者更改数据库用户或角色权限
• 创建一个新的数据库

mysql> create database school;    创建
mysql> use school；   进库

mysql> create table ky11 (id int not null,name varchar(20)not null,score decimal(5,2)not null,age int(5),address varchar(50) default 'KY11',primary key(id));      在库下面创建一个表
mysql> show tables;   查看表 
mysql> describe ky11       查看表的结构
    -> ;        这里提示忘了加；，补充一下就行

• 删除指定的数据表
  use数据库名
  DROP TABLE school.表名
  DROP TABLE[数据库名.]表名;
  #如不用USE进入库中，则需加上数据库名
  删除指定的数据库
  DROP DATABASE数据库名;

DML管理表中的数据记录

insert：插入新数据
update：更新原有数据
delete：删除不需要的数据

mysql> insert into ky11 (id,name,score,age,address) values(1,'xiaolv',88.111,20,'杭州');     插入一段数据
mysql> select * from ky11;         查询表中数据
注：主键是唯一的 ，

mysql> select * from ky11\G ；       \G表示以列的形式输出
mysql> insert into ky11 values(4,'zhao',59,29,'深圳'),(5,'zhangsan',10,40,'新疆');      可以一次添加多组数据，使用 ， 隔开

查询数据时，密码字串以加密的形式显示；若不使用password，查询时以明文显示。
添加一个新的表

mysql> create table ky12 (id int not null,name char(10) not null,score decimal(5,2),passwd char(48) default'', primary key (id));
                                                                                                                                       添加一个密码

mysql> insert into ky12 values(2,'zhangsan',55,PASSWORD('abc123'));   对密码设置加密

• update更新原有数据
  修改、更新数据中的数据记录
  格式： update 表名 set 字段名1=字段值1[字段名2=字段值2] [where 条件表达式]

mysql> update ky12 set name='xiaolv' where id=1;    where后面加的作为判断 

如果不加where 判断，则表中都会被修改

• delete：删除不需要的数据（表内容）
  在数据表中删除指定的数据记录
  格式：delete from 表名 [where 条件表达式]

mysql>  delete from ky12 where id='1';      确定唯一性，精准删除

• select
  格式

select   字段名1，字段名2 [....]   from  表名 [where  条件表达式]；

例：

mysql> select id,name from ky11 where score>80;  输出分数大于80的id和name  字段
mysql> select * from ky11 where id>1 and id<3;    输出第二行的字段
mysql> select * from ky11 limit 2;    只显示前三行  （前三行包括0行的标头）
mysql> select * from ky11 limit 2,3;      只显示第三行后的前三行内容    2表示从第三行开始，3表示基于某行后的前三行

DCL

• alter 修改表名和表结构
  alter table 旧表名 rename 新表名；
  例：

mysql> alter table ky12 rename ky12_new;

• 扩展表结构（增加字段）
  alter table 表名 add address varchar（5） default ‘地址不详’；
  #default ‘地址不详’： 表示此字段设置默认值为地址不详，可用 not null 配合使用
  修改字段（列）名，添加唯一键
  alter table 表名 change 旧列名 新列名 数据类型[ unique key];
  unique key ：唯一键 （特性：唯一，但可以为空，控制只允许出现一次）
  alter table yyy change name user name varchar(12) unique key;
  #change 可修改字段名、数据类型、约束等所有项
  例：

mysql> alter table ky11 add hobid int(5);

mysql> alter table ky11 change address address_new varchar(50) unique key default '地址不详';
mysql> insert into ky11 values(6,'didi',73,17,default,'1');     添加一个新的数据

• 删除字段
  格式
  alter table 表名 drop 字段名；
• 数据表高级操作

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

mysql> create table ky12_kole like ky12_new;

• 清空表，删除表内所有数据
  1、delete from yyy3；
  delete清空表后，返回的结果内有删除的记录条目;
  delete工作时是一行一行的删除记录数据的;如果表中有自增长字段，使用delete from删除所有记录后，再次新添加的记录会从原来最大的记录ID后面继续自增写入记录。
  属于DML
  可回滚
  可带where
  表结构在，表内容要看where执行的情况
  删除速度慢，需要逐行删除
  2、truncate table test01;
  truncate清空表后，没有返回被删除的条目:truncate 工作时是将表结构按原样重新建立，因此在速度上truncate会比delete清空表快;使用truncate table 清空表内数据后，
  ID会从l开始重新记录
  属于DDL
  不可回滚
  不可带where
  表内容删除
  删除速度快
  3、drop table table_name
  属于DDL
  不可回滚
  不可带where
  表内容和结构删除
  删除速度快
  注：删除速度 drop>truncate>delete
  安全性：delete 最好
• 创建临时表

create temporary  table xxxx (id int(4),name varchar(10));

临时表：只会保存在内存中，在数据库退出连接之前的所有操作，都是在内存中进行的，不会保存在磁盘里面，退出连接后，临时表会释放掉

永久表：sql 写入——》数据表中—》先把数据保存在内存中—》写入到磁盘

MySQL中6种常见的约束

主健约束(prima ry key)
外键约束( foreign key)
非空约束(not null)
唯一性约束(unique [ key l index ] )
默认值约束(default)
自增约束(auto_increment)

外键的定义：如果同一个属性字段x在表一中是主键，而在表二中不是主键，则字段x称为表二的外键。
创建外键约束作用（误删、修改），保证数据的完整性和一致性。

主键表和外键表的理解

1、以公共关键字作主键建的表为主键表(父表、主表)
2、以公共关键字作外键的表为外键表i从表、外表)
注意:与外键关联的主表的字段必须设置为主键。要求从表不能是临时表，主表外键字段和从表的字段具备相同的数据类型、字符长度和约束。

• 创建主表

mysql> create table p1 (hobid int(4), hobname varchar(50));

创建从表

mysql> create table p2 (id int(5) primary key auto_increment,name varchar(10),age int(3),,hobid int(4));

为主表添加一个主键约束，主键名以pk开头

mysql> alter table p1 add constraint PK_hobid primary key (hobid); 

为从表添加外键 ，将从表和主表建立外键关联，外键以FK 开头

mysql> alter table p2 add constraint FK_hobid foreign key (hobid) references p1 (hobid);

查看设置

mysql> show create table p2
    -> ;

• 插入新的数据记录时，先主表再从表

mysql> insert into p1 values(1,'runing');     主
mysql> insert into p2 values(1,'speaking',18,1);   从

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

mysql> drop tables test1;
mysql> drop tables test;

如果要删除外键约束字段先删除外键约束，再删除外键名

mysql> alter table test01drop foreign key FK_hob;
mysql> alter table test01 drop key Fk_hob ;

数据库用户管理

1、新建用户
CREATEUSER ‘用户名’@'来源地址’[IDENTIFIED BY[PASSWORD] ‘密码’];
‘用户名’:指定将创建的用户名．
‘来源地址T:指定新创建的用户可在哪些主机上登录，可使用IP地址、网段、主机名的形式，本地用户可用localhost，允许任意主机登录
可用通配符%
‘密码’:若使用明文密码，直接输入’密码’，插入到数据库时由Mysql自动加密;
若使用加密密码，需要先使用SELECT PASSWORD(‘密码’);获取密文，再在语句中添PASSWORD ‘密文’;若省略"IDENTIFIED BY"部分，则用户的密码将为空(不建议使用)
直接创建

mysql> create user 'lisi'@'localhost' identified by 'abc123';

加密创建

mysql> select password ('abc123');
mysql> create user 'zhangsan'@'localhost' identified by password '*6691484EA6B50DDDE1926AA220

• 查看用户信息
  创建后的用户保存再mysql数据库的user表里
  user mysql；
  select user， authentication_string,host from user;
  select user， authentication_string,host from mysql.user;

mysql> select user,authentication_string,host from user;  拉取数据

重命名指定
rename user ‘zhangsan’@‘localhost’ to ‘lisi’@‘localhost’;

mysql> rename user 'zhangsan'@'localhost' to 'wangwu'@'localhost';

删除用户
drop user ‘lisi’@‘localhost’;

修改当前密码
set password = password（‘abc123’）;

mysql> set password = password ('123123');  修改的是当前登录用户的密码 

修改其他用户密码
set password for ‘user’@‘localhost’ = password(‘abc123c’);

忘记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"

 vim /etc/my.cnf
 systemctl restart mysqld.service   重启服务

数据库用户授权

• 授予权限
  grant 提权
  grant 权限列表 on 数据库名，表名 to ‘用户名’@ ‘来源地址’ [identified by ‘密码’]；
  select、alter、update、delete、drop、truncate 可以用来修改部分权限
  
  flush privileges； 刷新权限（刷新的是权限表，可以让权限提前生效）
• 查看权限

show grants for 'zhangsan'@'%';

• 移除权限
  revoke权限列表ON数据库名.表名FROM用户名@来源地址;
  revoke all on . from ’ lisi’@’%’;
  show grants for ’ lisi’ @ ‘%’;
  usage权限只能用于数据库登陆，不能执行任何操作;usage权限不能被回收，即revoke不能删除用户。