本文已参与「新人创作礼」活动，一起开启掘金创作之路。 在MySQL管理软件中，可以通过SQL语句中的DML语言来实现数据的操作，包括使用INSERT实现数据 的插入、DELETE实现数据的删除以及UPDATE实现数据的更新。
更新数据 insert
更新数据 update
删除数据 delete
一、插入数据INSERT
1. 插入完整数据（顺序插入）
语法一：
INSERT INTO 表名(字段1,字段2,字段3…字段n) VALUES (值1,值2,值3…值n);
语法二：
INSERT INTO 表名 VALUES (值1,值2,值3…值n);
2. 指定字段插入数据
语法：
INSERT INTO 表名(字段2,字段3…) VALUES (值2,值3…);
3. 插入多条记录
语法：
INSERT INTO 表名 VALUES
(值1,值2,值3…值n),
(值1,值2,值3…值n),
(值1,值2,值3…值n);
4. 插入查询结果
语法：
INSERT INTO 表1(字段1,字段2,字段3…字段n)
SELECT (字段1,字段2,字段3…字段n) FROM 表2
WHERE …;
例子：
mysql> create table student7(id int,name varchar(20),sex enum('m','f'),age int(2));
mysql> desc student7;
+-------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| sex | enum('m','f') | YES | | NULL | |
| age | int(2) | YES | | NULL | |
+-------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> insert into student7 values(1,'jack','m',20); \\顺序插入数据
mysql> insert into student7(name,age) values('bob',21); \\指定字段插入数据
mysql> insert into student7 values(6,'jex','m',21),(7,'bob1','f',22); \\插入多条记录
插入查询结果：
mysql> create table student_his(id int,name varchar(20),sex enum('m','f'),age int(2));
mysql> insert into student_his select * from student7 where name = 'bob'; \\插入查询结果
mysql> insert into student_his select * from student7 where age > 17; \\插入符合条件的
二、更新数据UPDATE
语法：
UPDATE 表名 SET
字段1=值1,
字段2=值2,
WHERE CONDITION;
示例：
mysql> update student7 set id=8; #修改全部
mysql> select * from student7;
+------+------+------+------+
| id | name | sex | age |
+------+------+------+------+
| 8 | jex | m | 21 |
| 8 | bob1 | f | 22 |
+------+------+------+------+
2 rows in set (0.00 sec)
mysql> update student7 set id=9 where name="bob1"; 指定条件
mysql> select * from student7;
+------+------+------+------+
| id | name | sex | age |
+------+------+------+------+
| 8 | jex | m | 21 |
| 9 | bob1 | f | 22 |
+------+------+------+------+
2 rows in set (0.00 sec)
mysql> update student7 set id=10,name="youngfit" where name="bob1";
mysql> select * from student7;
+------+----------+------+------+
| id | name | sex | age |
+------+----------+------+------+
| 8 | jex | m | 21 |
| 10 | youngfit | f | 22 |
+------+----------+------+------+
2 rows in set (0.01 sec)
三、删除数据DELETE
语法：
DELETE FROM 表名
WHERE CONITION;
示例：
DELETE FROM mysql.user
WHERE authentication_string=’’;
delete from 表名 where 条件;
mysql> delete from student7 where name="jack";
delete from 表名; //删除表的全部数据
mysql> delete from student7;
Query OK, 3 rows affected (0.00 sec)
mysql> select * from student7;
Empty set (0.01 sec)
数据库必须具有UTF8字符集
create database jiradb character set utf8 collate utf8_bin;
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER,INDEX on <JIRADB>.* TO '<USERNAME>'@'<JIRA_SERVER_HOSTNAME>' IDENTIFIED BY '<PASSWORD>';
flush privileges;
# 给用户设定密码的两种方式
1. UPDATE user SET password = PASSWORD('your_password') WHERE user = 'username';
FLUSH PRIVILEGES;
2. set password for root@localhost = password('your_password');
# 创建用户并授权用户
create user 'username'@'localhost' identified by 'your_password';
create database database_name default character set = 'utf8' default collate = 'utf8_general_ci';
grant all privileges on database_name.* to 'username'@'localhost';
flush privileges;
查看mysql数据库中的所有用户
SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;
查看某个用户的权限
show grants for 'nextcloud'@'%';
or
select * from mysql.user where user='root' \G;
查看当前用户
select user();
修改用户密码
use mysql;
UPDATE user SET password=PASSWORD('新密码') WHERE user='用户';
flush privileges;
修改用户权限及密码
grant 权限 on 库名.表名 to '用户名'@’网段‘ identified by "该用户的密码"；
grant all privileges on nextcloud.* to 'nextcloud'@'%' identified by 'du..olctx..entest.1';
删除用户
drop user 'nextcloud'@'%';
root更改密码
set password for root@localhost = password("Songhaofeng@123");
mysql> create database confluence default character set utf8 collate utf8_bin;
mysql> grant all on confluence.* to 'confluence'@'%' identified by 'confluence';
mysql> flush privileges;
如出现报错信息：ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
有时候，只是为了自己测试，不想密码设置得那么复杂，譬如说，我只想设置root的密码为123456。
必须修改两个全局参数：
mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password_length=1;
Query OK, 0 rows affected (0.00 sec)
修改MySQL初始密码
[root@localhost ~]# mysqladmin -uroot -p'/DBpfulrh8kl' password 'Song@123'
