MySQL之基本操作与用户授权

一 基本操作

1 SQL分类

• 数据库：database

• 表：table，行：row 列：column

• 索引：index

• 视图：view

• 存储过程：procedure

• 存储函数：function

• 触发器：trigger

• 事件调度器：event scheduler，任务计划

• 用户：user

• 权限：privilege

 2 SQL 语言规范

• 在数据库系统中，SQL 语句不区分大小写，建议用大写

• SQL语句可单行或多行书写，默认以 " ; " 结尾

• 关键词不能跨多行或简写

• 用空格和TAB 缩进来提高语句的可读性

• 子句通常位于独立行，便于编辑，提高可读性

3 SQL语句分类

• DDL: Data Defination Language 数据定义语言

CREATE，DROP，ALTER

• DML: Data Manipulation Language 数据操纵语言

INSERT，DELETE，UPDATE

软件开发：CRUD

• DQL：Data Query Language 数据查询语言

SELECT

• DCL：Data Control Language 数据控制语言

GRANT，REVOKE

• TCL：Transaction Control Language 事务控制语言

COMMIT，ROLLBACK，SAVEPOINT

查看数据库信息

 show databases；

查看数据库中的表信息 

use mysql

show tables

方法二

# 格式  show  tables  from  库名
 
 show tables from mysql;

方法三

纵向查看方式：

desc  表名\G;

显示数据表的结构

mysql> use mysql
mysql> describe user; 

Field:字段名称

type: 数据类型

Nu11 :是否允许为空

Key :主键

Type: 数据类型

Null :是否允许为空

key :主键

Default :默认值

Extra :扩展属性，例如:标志符列(标识了种子，增量/步长)1 2

id: 1 3 5 7

常见的数据类型

二 创建数据库和表

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

使用DDL语句新建库、表

创建数据库

创建数据表

create table mcb (id int not null,name char(16) not null,score decimal(4,2),passwd char(45) default'',primary key (id));

或create 

 查看定义字段

select *from mcb250;

select * from where  id =2

三 对表中的数据进行管理

1 DDL语句删除库、表

删除指定的数据表

mysql> drop table auth；

usersQuery OK, 0 rows affected (0.01 sec)

方法①

drop table mcb38;

DROP TABLE[数据库名.1表名

方法②

删除指定的数据库

mysql> drop database auth；

Query OK, 0 rows affected (0.00 sec)

DELETE:删除表指定数据

删除表，还能建；删除库就彻底没有希望了，需谨慎

INSERT:插入新数据

UPDATE: 更新原有数据

2  查询

查询时可不指定条件 

查找符合条件的数据记录

清空表

方法一：

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

方法二

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

 三种删除命令区别 

总结：

• 不再需要一张表的时候，用drop
• 想删除部分数据行时候，用delete，并且带上where子句
• 保留表结构而删除所有数据的时候用truncate
• 删除速度：drop> truncate > delete
• delete 安全性最好 

建立临时表

用于保存一些临时数据，不会长期存在

 
mysql> create temporary table TCL (id int(6) zerofill primary key auto_increment,name varchar(16) not null,cardid int(18) not null unique key,hobby varchar(600),sike int(11));
Query OK, 0 rows affected (0.00 sec)
 
mysql> select * from TCL;
Empty set (0.01 sec)
 
mysql> insert into TCL values(2,'mg',01,'huaqian',10);
Query OK, 1 row affected (0.00 sec)
 
mysql> select * from TCL;
+--------+------+--------+---------+------+
| id     | name | cardid | hobby   | sike |
+--------+------+--------+---------+------+
| 000002 | mg   |      1 | huaqian |   10 |
+--------+------+--------+---------+------+
1 row in set (0.00 sec)
 
mysql> 

复制表结构

创建新表，导入数据

create table 新表名 like 旧表名;
#通过LIKE方法，复制旧表的结构生成新表
 
insert into 新表名 (select * from 旧表名);     
#再将旧表数据导入新表

克隆表

第一种方式：迁移表 表的结构 属性 约束 数据

 第二种方式：备份数据 表的复制

总结：克隆表有两种

① 迁移表：表的结构、属性、约束、数据等

② 备份数据：表的属性、内容（数据）

三 表结构的管理

修改表名

格式：

alter table 旧表名 rename 新表名;

扩展表结构(增加字段)

 格式：

alter table 表名 add 字段名 数据类型;

 修改字段(列)名，添加唯一键

删除表达内容 

 四 数据库的用户授权

1.新建用户

格式：
create user '用户名'@'来源地址' [identified by [passwd] '密码'];

2 加密密码创建用户：

 SELECT PASSWORD('密码');    #先获取加密的密码
 ​
 CREATE USER 'm'@'localhost' IDENTIFIED BY PASSWORD '加密的密码';

 3 查看用户密码的信息

用户信息存放在 mysql 数据库下的 user 表（MySQL 服务下存在一个系统自带的 mysql 数据库）

 

4 查看用户信息，

创建用户保存在mysql数据库的user表里

use mysql;
 
select user,authentication_string,Host from user;

5 登录用户用户名的更改

重命名指定

rename user 'cpu'@'locahost' to 'mcb250'@'localhost'；

 修改用户密码

 set password = password('3721');

 删除登录用户

drop user 'mcb550'@'localhost';

修改其他用户密码 

 6 忘记root密码的解决方法

① 修改 /etc/my.cnf 配置文件，免密登录mysql

② 重启数据库服务

③ 无密码登录之后，修改数据库用户中的 root 密码

 
update mysql.user set authentication_string = password('250') where user='root';
#用于更新MySQL数据库系统表mysql.user中的用户root的密码。AUTHENTICATION_STRING是MySQL 5.7.0以后版本中用于存储密码的字段名

⑤ 撤销添加的免密码认证设置，重启数据库服务(玩玩就行)

五 管理登录用户的权限

在 MySQL 中，用户提权是指将用户的权限从一个级别提升到另一个更高的级别。或者根据需求给予合理的权限。

[NO_AUTO_CREATE_USER]， 即在grant语句中禁止创建空密码的账户，使用grant语法创建用户必须带上 “identified by”关键字设置账户密码，否则就被认为是非法的创建语句。 

 查看用户已有权限的操作

show grants;
#查看当前用户（自己）的权限

show grants for 'NBA'@'localhost';
 
#查看其他用户的权限

对已存在用户授权示

grant select,insert on study.class to 'mcb'@'localhost';
 
show grants for 'mcb'@'localhost';

对不存在的用户进行创建并授权 

撤销用户权限 

 revoke 权限列表/ALL on 库名.表名 from '用户名'@'来源地址';

用户的远程登录

 grant all privileges on *.* to 'NBA'@'192.168.11.%' identified by '123';