ddl mysql 隐式提交 mysql ddl权限

一、数据库定义语言（DDL）：

创建以下操作：

　　1、添加数据库：create database 库名；

mysql> create database student;
Query OK, 1 row affected (0.16 sec)


mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| student          |
+--------------------+
5 rows in set (0.01 sec)

　　2、删除数据库：drop database 库名；

mysql> drop database student;
Query OK, 0 rows affected (0.16 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

　3、创建mydbtest数据库并修改字符集：

-- 创建一个使用utf8字符集的mydb2数据库。

create database mydbtest character set gbk;
Query OK, 1 row affected (0.33 sec)

mysql> show create database mydbtest;
+----------+------------------------------------------------------------------+
| Database | Create Database                                                  |
+----------+------------------------------------------------------------------+
| mydbtest | CREATE DATABASE `mydbtest` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+------------------------------------------------------------------+
1 row in set (0.02 sec)


-- 修改mydb2字符集为uft8;
alter database mydbtest character set utf8;
Query OK, 1 row affected, 1 warning (0.13 sec)

mysql> show create database mydbtest;
+----------+-------------------------------------------------------------------+
| Database | Create Database                                                   |
+----------+-------------------------------------------------------------------+
| mydbtest | CREATE DATABASE `mydbtest` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-------------------------------------------------------------------+

1 row in set (0.00 sec)

　4、创建表t_student 并查看该表的字段和结构：

-- 1.选中数据库
mysql> use mydbtest;
Database changed

-- 2.创建数据表
create table t_student(
id bigint primary key auto_increment,
name varchar(12) not null,
age int
);

Query OK, 0 rows affected (0.53 sec)

-- 3.查看所创建的表
mysql> show tables;
+--------------------+
| Tables_in_mydbtest |
+--------------------+
| t_student          |
+--------------------+
1 row in set (0.00 sec)

-- 4.查看表中的字段
mysql> desc t_student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | bigint(20)  | NO   | PRI | NULL    | auto_increment |
| name  | varchar(12) | NO   |     | NULL    |                |
| age   | int(11)     | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

-- 5.查看表的结构
mysql> show create table t_student;
+-----------+---------------+
| Table     | Create Table
----------------------------+
| t_student | CREATE TABLE `t_student` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(12) NOT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
-----------------------------------------------
1 row in set (0.05 sec)

5.对数据表的结构的修改：

-- 1、表名改为t_user。	
rename table t_student to t_user;

Query OK, 0 rows affected (0.20 sec)

mysql> show tables;
+--------------------+
| Tables_in_mydbtest |
+--------------------+
| t_user             |
+--------------------+
1 row in set (0.00 sec)


-- 2、在上面员工表的基本上增加一个password列。

mysql> alter table t_student add password varchar(20);
Query OK, 0 rows affected (0.18 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t_student;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | bigint(20)  | NO   | PRI | NULL    | auto_increment |
| name     | varchar(12) | NO   |     | NULL    |                |
| age      | int(11)     | YES  |     | NULL    |                |
| password | varchar(20) | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

-- 3、修改age列，修改为varchar类型。

mysql> alter table t_student modify age varchar(80);
Query OK, 0 rows affected (0.28 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t_student;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | bigint(20)  | NO   | PRI | NULL    | auto_increment |
| name     | varchar(12) | NO   |     | NULL    |                |
| age      | varchar(80) | YES  |     | NULL    |                |
| password | varchar(20) | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

-- 4、列名name修改为username

mysql> alter table t_student change name username varchar(20) not null;

Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t_student;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | bigint(20)  | NO   | PRI | NULL    | auto_increment |
| username | varchar(20) | NO   |     | NULL    |                |
| age      | varchar(80) | YES  |     | NULL    |                |
| password | varchar(20) | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

-- 5、删除password列。

mysql> alter table t_student drop password;
Query OK, 0 rows affected (0.30 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t_student;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | bigint(20)  | NO   | PRI | NULL    | auto_increment |
| username | varchar(20) | NO   |     | NULL    |                |
| age      | varchar(80) | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.03 sec)
	
-- 6、修改表的字符集为gbk

mysql> alter table t_student character set gbk;
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t_student;

| Table     | Create Table

| t_student | CREATE TABLE `t_student` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) CHARACTER SET utf8 NOT NULL,
  `age` varchar(80) CHARACTER SET utf8 DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk |

1 row in set (0.00 sec)

6.主键和外键约束：

-- 1.创建一个t_class班级表，
create table t_class(
cid bigint primary key auto_increment,
cname varchar(20)
);

Query OK, 0 rows affected (0.16 sec)

mysql> desc t_class;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| cid   | bigint(20)  | NO   | PRI | NULL    | auto_increment |
| cname | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

-- 2.增加t_student学生表cid字段
alter table t_student add cid bigint not null;
mysql> desc t_student;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | bigint(20)  | NO   | PRI | NULL    | auto_increment |
| username | varchar(20) | NO   |     | NULL    |                |
| age      | varchar(80) | YES  |     | NULL    |                |
| cid      | bigint(20)  | NO   |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)


-- 3.增加学生表外键约束
添加外键约束语法：alter table 从表 add constraint 外键（形如：FK_从表_主表） foreign key (从表外键字段) references 主表(主键字段);

mysql> alter table t_student add constraint fk_t_student_t_class foreign key(cid) ref
rences t_class(cid);

Query OK, 0 rows affected (0.37 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t_student;

-------------------------------------------------------------------+
| Table   | Create Table
-------------------------------------------------------------------+
| student | CREATE TABLE `t_student` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) CHARACTER SET utf8 NOT NULL,
  `age` varchar(80) CHARACTER SET utf8 DEFAULT NULL,
  `cid` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_t_student_class` (`cid`),
  CONSTRAINT `fk_student_class` FOREIGN KEY (`cid`) REFERENCES `t_class` (`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
-------------------------------------------------------------------+
1 row in set (0.01 sec)

 

二、MYSQL权限管理

1.用户管理

1.1.使用create命令创建用户
---------------------------------------
语法：create user 'user'@'host' identified by 'password';
user：表示用户名
host：允许用户登录的主机名称。
      (1)可以是:localhost 仅限本机访问，或是一个IP地址：172.20.15.19 ,
      (2)还可以用通配置符：如果172.20.15.%  ,（默认%）表示所有主机都可以访问。
password：用户密码。还可以用password（）函数加密密码。

例1：create user 'radius'@'localhost' identified by password 'radius' ;
用password(）函数加密radius。

1.2.直接修改mysql数据库中的user表中的用户：
---------------------------------------
用SQL语句修改user表后，需要flush privileges 刷新一下，重新加载用户权限表。这种方式也可以修改其他普通用户的密码。
例2：insert into user(user,password,host) values('mysqltest',password('123456'),'172.20.%');

1.3.删除用户drop命令
-----------------------------------
格式: drop user 'user'@'host'
drop user 'mysqltest'@'172.20.%' ;

1.4.SQL语句删除user表中的用户
-----------------------------------
delete from user where user='mysqltest' and host='172.20.%';

1.5.修改密码：update更改user表
----------------------------------
mysql> update mysql.user set password=password('mysql') where user='root' and host='%' ;

直接修改mysql数据库中的user表中的root用户。update之后还需要flush privileges 刷新一下，重新加载用户权限表。这种方式也可以修改其他普通用户的密码。

1.6.修改密码：set ,alter命令方式
-----------------------------------
set password for 'root'@'%'=password('mysql');

alter user 'root'@'localhost' indentified by password('mysql');

1.7.重置root密码：mysqladmin 
-----------------------------------
没有登录mysql之前，使用mysqladmin命令重置root密码
mysqladmin -u root -h localhost -p password "123456" ;

1.8.MYSQL 8.0修改密码
-----------------------------------
mysql8.0 user表中没有了password字段，用plugin和authentication_string这两个字段

MySQL8.0之前的版本密码加密规则：mysql_native_password，

MySQL8.0密码加密规则：caching_sha2_password

plugin:默认方式caching_sha2_password ，要想远程登录必需将对应的用户plugin字段改成：mysql_native_password

authentication_string：密码

ALTER命令方式
====================
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

SQL语句
====================
update user set plugin='mysql_native_password' ,authentication_string=password('123456') where user='root' and host='localhost' ;

 

1.9.root用户忘记密码
------------------------------------
(1)停止mysql服务，
service mysql stop 
或
systemctl status mysqld.service

(2) 输入命令
window下：     mysqld  --skip-grant-tables ;
linux下：    mysqld_safe  --skip-grant-tables  user=mysql;

(3)用root用户进入mysql不需要密码：
mysql  -u  root ;

(4)修改密码：
update mysql.user set Password=password('password') where User='user'; 

(5)刷新加载权限表
flush  privileges;

(6)重启mysql服务
service mysql start 
或
systemctl start mysqld.service

 

2.权限管理

2.1.grant命令授权
--------------------------------
语法：grant privileges on db.table to 'user'@'host';
grant：授权命令
privileges: 权限,all表示所有权限，所可以指定具体的如：select_priv,insert_priv,delete_priv等等
有哪些权限可以在mysql数据库中的user表中查询      

db：数据库名，如：radius.radacct表示radius库radacct表，也可用通配符*：radius.* 或 *.*表示所有库和表
user：用户名称。
host：主机

2.2.revoke 命令权限收回
--------------------------------
语法：revoke  privileges on db.table to 'user'@'host';
revoke ：权限收回
privileges: 权限,all表示所有权限，所可以指定具体的如：select,insert,delete等等
有哪些权限可以在mysql数据库中的user表中查询      

db：数据库名，如：radius.radacct表示radius库radacct表，也可用通配符*：radius.* 或 *.*表示所有库和表
user：用户名称。
host：主机

2.3.直接修改user表中的权限字段
--------------------------------
update user set select_priv='y',insert_priv='y',delete_priv='y' where user='root' and host='localhost';