文章目录
SQL语言
- DDL:数据定义语言(Data Definition Language)
CREATE: 创建
DROP: 删除
ALTER: 修改
DESC: 查看
- DML:数据操作语言(Data Manipulation Language)
INSERT: 增
DELETE: 删
UPDATE: 改
- DCL:数据控制语言(Data Control Language)
GRANT: 授权
REVOKE: 撤权
- DQL:数据查询语言(Data Query Language)
SELECT: 查
DDL
创建数据库
- 语法
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
- 操作
MariaDB [(none)]> create database if not exists cr;
列出数据库
- 语法
SHOW {DATABASES | SCHEMAS} [LIKE 'pattern' | WHERE expr]
- 操作
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| cr |
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
MariaDB [(none)]> show databases like 'cr';
+---------------+
| Database (cr) |
+---------------+
| cr |
+---------------+
选择数据库
- 语法
USE db_name;
- 操作
MariaDB [(none)]> use cr
MariaDB [cr]> use test
MariaDB [test]>
创建表
- 语法
直接创建
create table [if not exists] tbl_name
(create_definition,...) [table_options ]...
通过查询创建,会将查询结果插入到表
create table [if not exists] tbl_name
[as] select_statement
复制表结构到信标,不复制数据
create table [if not exists] tbl_name
LIKE old_table_name
- 说明
create_definition:字段名+数据类型+约束条件
字段名:
自定义
数据类型:
数值型:tinyint、smallint、mediumint、int、bigint
float、double
decimal
bit
字符型:char、varchar、tinytext、text、mediumtext、longtext
binary、varbinary、tinyblob、blob、mediumblob、longblob
日期时间型:date、time、datetime、timestamp、year
枚举和集合:enum、set
约束条件:
适用所有数据类型:
null 、 not null 、 default default_value 、 comment 'comment_string'
适用数值类型:
UNSIGNED 、 AUTO_INCREMENT(要求该字段为primary key 或 unique key)
主键约束:primary key
主键字段的值非空且唯一
可通过primary key(字段1,字段2……)定义复合主键
非空约束:not null
非空字段的值不可为null
唯一性约束:unique key
唯一性约束字段的值不可重复,但可为null
可通过unique key 'key name' (字段1,字段2……)定义复合唯一约束
外键约束:foreign key
外键约束字段的值是另一张表的主键,可为null
CONSTRAINT key_name FOREIGN KEY(col_name) REFERENCES table_name(col_name)
[ON update reference_option] | [ON delete reference_option]
reference_option:
RESTRICT:不做任何操作
CASCADE:级联操作
SET NULL:设置为null
NO ACTION:默认,不做任何操作
table_options:表参数:
ENGINE [=] engine_name # show engines; 查看
[DEFAULT] CHARSET [=] charset_name #show charset; 查看
……省略……
- 操作
创建学生表和学科表
学生表
MariaDB [cr]> create table student (
-> id int unsigned not null primary key auto_increment,
-> student_number char(50) not null unique key,
-> name varchar(30) not null,
-> sex enum('male','female') default null,
-> age int unsigned,
-> admission_data date)
-> engine=InnoDB charset=utf8;
学科表
MariaDB [cr]> create table student (
-> id int unsigned not null primary key auto_increment,
-> subject_number char(20) not null unique key,
-> name varchar(50) not null)
-> engine=InnoDB charset=utf8;
创建关系表(成绩)
MariaDB [cr]> create table score (
-> student_number char(50) default null,
-> subject_number char(20) default null,
-> score tinyint,
-> UNIQUE KEY (`student_number`,`subject_number`),
-> foreign key(student_number) references student(student_number),
-> foreign key(subject_number) references subject(subject_number)
-> )engine=InnoDB charset=utf8;
通过查找复制表
MariaDB [cr]> create table subject_copy select * from subject;
Query OK, 3 rows affected (0.018 sec)
Records: 3 Duplicates: 0 Warnings: 0
MariaDB [cr]> select * from subject_copy;
+----+----------------+--------+
| id | subject_number | name |
+----+----------------+--------+
| 1 | 2001 | 语文 |
| 2 | 2002 | 数学 |
| 3 | 2003 | 英语 |
+----+----------------+--------+
复制表结构
MariaDB [cr]> create table score_structure like score;
Query OK, 0 rows affected (0.013 sec)
MariaDB [cr]> select * from score_structure;
Empty set (0.001 sec) #没有数据
MariaDB [cr]> desc score_structure;
+----------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+------------+------+-----+---------+-------+
| student_number | char(50) | YES | MUL | NULL | |
| subject_number | char(20) | YES | MUL | NULL | |
| score | tinyint(4) | YES | | NULL | |
+----------------+------------+------+-----+---------+-------+
查看表
- 列出所有表
show tables;
show tables like '%tb_name%';
- 查看表结构
desc tb_name;
show create table \G;
- 查看表状态
show table status;
show table status like '%tb_name%';
删除表
- 语法
drop table if exists tb_name;
- 操作
MariaDB [cr]> drop table student;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails
MariaDB [cr]> SET FOREIGN_KEY_CHECKS = 0; #取消外键约束检查,立即生效
MariaDB [cr]> drop table student;
MariaDB [cr]> SET FOREIGN_KEY_CHECKS = 1; #删除后恢复
修改表
- 修改表名
alter table tb_name rename new_tb_name;
- 修改表选项
alter table tb_name table_options;
alter table tb_name engine=InnoDB; #修改默认引擎
alter table tb_name charset=utf8; #修改默认字符集
- 添加字段
alter table tb_name add col_name column_definition [first | after col_name];
- 删除字段
alter table tb_name drop col_name;
- 修改字段
保留原字段名
alter table tb_name modify col_name new_column_definition;
完全修改
alter table tb_name change old_col_name new_col_name new_column_definition;
DML
被操作的表:
MariaDB [cr]> CREATE TABLE employee(
-> empno INT PRIMARY KEY comment '雇员编号',
-> ename VARCHAR(20) comment '雇员姓名',
-> job VARCHAR(20) comment '雇员职位',
-> mgr INT comment '雇员上级编号',
-> hiredate DATE comment '雇佣日期',
-> sal DECIMAL(7,2) comment '薪资',
-> deptnu INT comment '部门编号'
-> );
插入数据
- 语法
插入数据
insert [into] tb_name [(col,...)] {values | value} (var,...),(...)...
蠕虫复制
insert [into] tb_name select_statement
insert [into] tb_name1(col_name1,col_name2) select col_name1,col_name2 from tb_name2
- 操作
MariaDB [cr]> insert into employee(empno,ename,job) values
(0001,"张三","经理"),
(0002,"李四","主管"),
(0003,"王五","职员");
删除数据
- 语法
delete from tb_name [where where_condition]
delete from tb_name [order by col1,col2 asc|desc] [limit row_count]
- 操作
MariaDB [cr]> delete from employee; #删除表中所有
MariaDB [cr]> delete from employee order by empno desc limit 2; #删除按empno逆序的前两个
- 补充
truncate table tb_name;
delete from tb_name;
drop table tb_name;
truncate: 删除数据、释放空间,保留表结构
delete: 删除数据、不释放空间,保留表结构;操作保存在日志,用于回滚
drop: 删除数据和表结构,释放空间
删除速度:
drop > truncate > delete
更新数据
- 语法
UPDATE table_name
SET
col_name1 = column_definition1,
col_name2 = column_definition2,
...
WHERE
where_condition;
- 操作
MariaDB [cr]> update employee set job="老板" where ename="张三";
DQL
被操作的表:
部门表
MariaDB [cr]> CREATE TABLE dept(
-> deptnu INT PRIMARY KEY comment '部门编号',
-> dname VARCHAR(50) comment '部门名称',
-> addr VARCHAR(50) comment '部门地址'
-> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
员工表
MariaDB [cr]> CREATE TABLE employee(
-> empno INT PRIMARY KEY comment '雇员编号',
-> ename VARCHAR(50) comment '雇员姓名',
-> job VARCHAR(50) comment '雇员职位',
-> mgr INT comment '雇员上级编号',
-> hiredate DATE comment '雇佣日期',
-> sal DECIMAL(7,2) comment '薪资',
-> deptnu INT comment '部门编号'
-> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
创建工资等级表
MariaDB [cr]> CREATE TABLE salgrade(
-> grade INT PRIMARY KEY comment '等级',
-> lowsal INT comment '最低薪资',
-> higsal INT comment '最高薪资'
-> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
插入dept表数据
INSERT INTO dept VALUES (10, '研发部', '北京');
INSERT INTO dept VALUES (20, '工程部', '上海');
INSERT INTO dept VALUES (30, '销售部', '广州');
INSERT INTO dept VALUES (40, '财务部', '深圳');
插入emp表数据
INSERT INTO employee VALUES (1009, '唐僧', '董事长', NULL, '2010-11-17', 50000, 10);
INSERT INTO employee VALUES (1004, '猪八戒', '经理', 1009, '2001-04-02', 29750, 20);
INSERT INTO employee VALUES (1006, '猴子', '经理', 1009, '2011-05-01', 28500, 30);
INSERT INTO employee VALUES (1007, '张飞', '经理', 1009, '2011-09-01', 24500,10);
INSERT INTO employee VALUES (1008, '诸葛亮', '分析师', 1004, '2017-04-19', 30000, 20);
INSERT INTO employee VALUES (1013, '林俊杰', '分析师', 1004, '2011-12-03', 30000, 20);
INSERT INTO employee VALUES (1002, '牛魔王', '销售员', 1006, '2018-02-20', 16000, 30);
INSERT INTO employee VALUES (1003, '程咬金', '销售员', 1006, '2017-02-22', 12500, 30);
INSERT INTO employee VALUES (1005, '后裔', '销售员', 1006, '2011-09-28', 12500, 30);
INSERT INTO employee VALUES (1010, '韩信', '销售员', 1006, '2018-09-08', 15000,30);
INSERT INTO employee VALUES (1012, '安琪拉', '文员', 1006, '2011-12-03', 9500, 30);
INSERT INTO employee VALUES (1014, '甄姬', '文员', 1007, '2019-01-23', 7500, 10);
INSERT INTO employee VALUES (1011, '妲己', '文员', 1008, '2018-05-23', 11000, 20);
INSERT INTO employee VALUES (1001, '小乔', '文员', 1013, '2018-12-17', 8000, 20);
插入salgrade表数据
INSERT INTO salgrade VALUES (1, 7000, 12000);
INSERT INTO salgrade VALUES (2, 12010, 14000);
INSERT INTO salgrade VALUES (3, 14010, 20000);
INSERT INTO salgrade VALUES (4, 20010, 30000);
INSERT INTO salgrade VALUES (5, 30010, 99990);
简单查询
- 语法
查询所有数据
select * from tb_name;
查询某几个字段数据
select col1,col2... from tb_name;
as用法 #在select中可使用tb_name.col_name表示该表中的这个字段,使用as可为table或colnum设置别名
select col_name as "字段名" from tb_name;
select a.col_name,b.col_name from tb_name1 as a,tb_name2 as b;
- 操作
MariaDB [cr]> select * from dept
MariaDB [cr]> select * from employee
MariaDB [cr]> select * from salgrade;
聚合函数
- 语法
select aggregation(col_name) from tb_name
aggregation:
distinct(col_name):去重
count(col_name|*):统计
sum(col_name):求和
max(col_name):最大值
avg(col_name):平均值
min(col_name):最小值
concat(col1,['string' | col2]):拼接字符串
操作:
查看领导有那些人
MariaDB [cr]> select * from employee where empno in (select distinct(mgr) from employee where mgr is not null);
统计领导有几人
MariaDB [cr]> select count(*) as leader_num from employee where empno in (select distinct(mgr) from employee where mgr is not null);
查公司的最高、最低和平均工资
MariaDB [cr]> select min(sal), max(sal), avg(sal) from employee;
where条件查询
- 语法
select col1 [as alias1],col2 [as alias2]... from tb_name
where where_condition
where_condition:col_name+操作符+值
算数操作符: +, -, *, /, %
比较操作符: =, !=, >, >=, <, <=
逻辑运算符: not, and, or, xor
范围查询: between min_num and max_num
离散查询: in (var1, var2, ...)
空值查询: is null; is not null
模糊查询: like 'string'
'%':任意长度的任意字符
'_':任意单个字符
正则查询: regexp 'pat'
- 操作
查看员工编号是偶数的员工
MariaDB [cr]> select * from employee where empno%2=0;
查看工资大于2万的员工
MariaDB [cr]> select * from employee where sal >= 20000;
查看销售员工资大于1.5万的员工
MariaDB [cr]> select * from employee where job='销售员' and sal >= 15000;
查看工资1-2万的员工
MariaDB [cr]> select * from employee where sal between 10000 and 20000;
查看没有上级的员工
MariaDB [cr]> select * from employee where mgr is null;
查看小乔和甄姬工资
MariaDB [cr]> select ename,sal from employee where ename in('小乔','甄姬');
查看11年入职的人
MariaDB [cr]> select * from employee where hiredate like '2011%';
查看姓唐的人
MariaDB [cr]> select * from employee where ename regexp '^唐';
查看员工对应的工资等级
MariaDB [cr]> select a.ename,b.grade from employee as a,salgrade as b where a.sal>=b.lowsal and a.sal<=higsal;
group by分组查询
- 语法
select [col_name], [aggregation(col_name)] from tb_name
group by col_name,...;
- 操作
统计每个部门多少人
select deptnu, count(*) from employee group by deptnu;
having条件查询
- 语法
对 分组 结果进一步筛选
select [col_name], [aggregation(col_name)] from tb_name
group by col_name,...
having [col_name|aggregation(col_name)];
- 操作
统计经理有多少人
MariaDB [cr]> select job, count(*) from employee group by job having job='经理';
查询每个部们中各职位最高工资的人
MariaDB [cr]> select * from employee group by deptnu,job having max(sal);
order by排序查询
- 语法
select * from tb_name order by col_name1,col_name2 asc|desc;
select * from tb_name
group by col_name,...
having [col_name|aggregation(col_name)];
order by col_name asc|desc;
- 操作
查看每个职位最高工资的人,结果逆序显示
MariaDB [cr]> select * from employee group by job having max(sal) order by sal desc;
limit限制查询
- 语法
select * from tb_name limit [m,]n; #第m开始,取出n条
- 操作
取出工资前三的人
MariaDB [cr]> select * from employee order by sal desc limit 3;
子查询
- 语法
where子句中的子查询:
select * from tb_name where where_condition
where_condition:字段名+操作符+值(是另一条select statement)
select * from tb_name1 where {exists|not exists}
(select 1 fom tb_name2 where where_condition)
from子句中的子查询:可以理解为查找出来的结果是一张表,而这张表是另外一个查询语句的输入
select tb_alias.col_name1,tb_alias.col_name2
from (select statement) as tb_alias
where tb_alias.col_name+筛选条件
- 操作
列出甄姬部门同事的工资
MariaDB [cr]> select * from employee where deptnu=(select deptnu from employee where ename='甄姬');
列出每个部门工资最高的同事
MariaDB [cr]> select * from employee where empno in (select empno from employee group by deptnu having max(sal));
连接查询
- 语法
有a、b两表
左连接:以表a为基准按照给定字段将b表扩充到a表
右连接:以表b为基准按照给定字段把a表扩充到b表
内连接:两张表中按照给定字段匹配的记录
左连接:
select * from tb_name1 as t1
left join tb_name2 as t2
on t1.col_name=t2.col_name
[where where_condition]
右连接:
select * from tb_name1 as t1
right join tb_name2 as t2
on t1.col_name=t2.col_name
[where where_condition]
内连接:
select * from tb_name1 as t1
inner join tb_name2 as t2
on t1.col_name=t2.col_name
[where where_condition]
- 操作
左连接
select * from employee as a left join dept as b on a.deptnu=b.deptnu;
右连接
select * from dept as a right join employee as b on a.deptnu=b.deptnu;
内连接
MariaDB [cr]> select * from dept as a inner join employee as b on a.deptnu=b.deptnu where ename='小乔';
联合查询
- 语法
(select statement1) union (select statement2)
- 操作
销售的工资从低到高显示,文员的工资从高到低显示
MariaDB [cr]> (select * from employee where job='销售员' order by sal asc limit 999999) union (select * from employee where job='文员' order by sal desc limit 999999);
#注:不加limit显示结果不正确
DCL
权限级别
- 全局权限(Global Level)
对所有数据库都具有权限,权限信息存储在mysql.user表中。 - 库级权限(Database Level)
只对对应数据库具有权限,权限信息存储在mysql.db表中。 - 表级权限(Table Level)
只对对应数据库下指定的表具有权限,权限信息存储在mysql.tables_priv表中。 - 字段权限(Colnum Level)
只对对应数据库下对应表的指定字段具有权限,权限信息存储在mysql.columns_priv表中。 - 存储过程权限(Routime Level)
对存储过程和函数具有权限,权限信息存储在mysql.procs_priv表中。
mysql通过身份认证后,按照如下顺序进行权限分配:
mysql.user→mysql.db→mysql.tables_priv→mysql.columns_priv
创建用户
- 语法
CREATE [OR REPLACE] USER [IF NOT EXISTS]
user_specification [,user_specification ...]
[WITH resource_option [resource_option ...] ]
[password_option | lock_option]
user_specification:
username [authentication_option]
authentication_option:
IDENTIFIED BY 'password'
IDENTIFIED BY PASSWORD 'password_hash'
resource_option:
MAX_QUERIES_PER_HOUR count #限制每小时查询次数
MAX_UPDATES_PER_HOUR count #限制每小时修改次数
MAX_CONNECTIONS_PER_HOUR count #限制每小时最大连接数
MAX_USER_CONNECTIONS count #限制用户最大连接数
MAX_STATEMENT_TIME time #查询语句最大执行时间
password_option:
PASSWORD EXPIRE #指定账户的密码过期
PASSWORD EXPIRE DEFAULT #配置文件中default_password_lifetime=N定义
PASSWORD EXPIRE NEVER #永不过期
PASSWORD EXPIRE INTERVAL N DAY #指定密码N天过期
lock_option:
ACCOUNT LOCK #锁定账户
ACCOUNT UNLOCK #解锁账户
- 操作
创建一个普通用户
MariaDB [mysql]> create user 'cr'@'localhost' identified by '123';
修改用户
- 语法
ALTER USER [IF EXISTS]
user_specification [,user_specification] ...
[WITH resource_option [resource_option] ...]
[password_option | lock_option]
user_specification:
username [authentication_option]
authentication_option:
IDENTIFIED BY 'password'
IDENTIFIED BY PASSWORD 'password_hash'
IDENTIFIED {VIA|WITH} authentication_plugin
authentication_plugin: #认证插件:mysql_native_password 和 unix_socket
#每次更换认证插件后都需要修改密码才能生效
#编译安装完成后root用户无密码登陆,此时需要修改密码
resource_option
MAX_QUERIES_PER_HOUR count #限制每小时查询次数
MAX_UPDATES_PER_HOUR count #限制每小时修改次数
MAX_CONNECTIONS_PER_HOUR count #限制每小时最大连接数
MAX_USER_CONNECTIONS count #限制用户最大连接数
MAX_STATEMENT_TIME time #查询语句最大执行时间
password_option:
PASSWORD EXPIRE #指定账户的密码过期
PASSWORD EXPIRE DEFAULT #配置文件中default_password_lifetime=N定义
PASSWORD EXPIRE NEVER #永不过期
PASSWORD EXPIRE INTERVAL N DAY #指定密码N天过期
lock_option:
ACCOUNT LOCK #锁定账户
ACCOUNT UNLOCK #解锁账户
}
- 操作
设置账户过期
MariaDB [(none)]> alter user 'cr'@'localhost' password expire;
解锁账户过期
MariaDB [(none)]> alter user 'cr'@'localhost' password expire never;
锁定账户
MariaDB [(none)]> alter user 'cr'@'localhost' account lock;
解锁账户
MariaDB [(none)]> alter user 'cr'@'localhost' account unlock;
修改密码
MariaDB [(none)]> alter user 'cr'@'localhost' identified by '1234';
删除用户
- 语法
DROP USER IF EXISTS 'user'@'host';
- 操作
MariaDB [(none)]> drop user 'cr'@'localhost';
用户授权
- 语法
GRANT
priv_type [(column_list)]
[, priv_type [(column_list)]] ...
ON [object_type] priv_level
TO user_specification [WITH with_option]
with_option:
GRANT OPTION #授予grant权限
MAX_QUERIES_PER_HOUR count #限制每小时查询次数
MAX_UPDATES_PER_HOUR count #限制每小时修改次数
MAX_CONNECTIONS_PER_HOUR count #限制每小时最大连接数
MAX_USER_CONNECTIONS count #限制用户最大连接数
MAX_STATEMENT_TIME time #查询语句最大执行时间
user_specification:
username [authentication_option]
username:
'user'@'host'
authentication_option:
IDENTIFIED BY 'password'
IDENTIFIED BY PASSWORD 'password_hash'
password_hash:
select password('password');
object_type:
TABLE
FUNCTION
PROCEDURE
priv_level:
*
*.*
db_name.*
db_name.tbl_name
tbl_name
db_name.routine_name
priv_type:
Global Privileges:
CREATE USER #有权限使用 create user 或 grant 创建用户
FILE #有权限使用 load data infile 读写文件
GRANT OPTION #有权限使用 grant 授予全局权限,仅能授权自己拥有的权限
PROCESS #有权限使用 show processlist 或 mysqladmin processlist 查看用户的进程
RELOAD #有权限使用 flush 或 等效的mysqladmin命令
REPLICATION CLIENT #有权限使用 show master status 和 show slave status 查看主从服务器状态
REPLICATION SLAVE #主从服务器,从服务器向主服务器上读取二进制日志,同步数据
SHOW DATABASES #有权限使用 show databases 列出所有数据库
SHUTDOWN #有权限使用 shutdown 或 mysqladmin shutdown 关闭服务
SUPER #有权限使用超级用户语句:
#CHANGE MASTER TO, KILL, PURGE LOGS, SET 系统变量 或 mysqladmin debug
Database Privileges:
CREATE #有权限使用 create database 创建数据库
CREATE ROUTINE #有权限使用 create procedure 和 create function 创建存储过程和函数
CREATE TEMPORARY TABLES #有权限使用 create temporary table 创建临时表
DROP #有权限使用 drop database 删除数据库
EVENT #有权限使用 create envent、 drop event、 alter event 创建,删除和更改事件
GRANT OPTION #有权限使用 grant 授予数据库权限,仅能授权自己拥有的权限
LOCK TABLES #有权限使用 lock table 显式锁
Table Privileges:
ALTER #有权限使用 alter table 修改表结构
CREATE #有权限使用 create table 创建表
CREATE VIEW #有权限使用 create view 创建视图
DELETE #有权限使用 delete 从表中删除行
DELETE HISTORY #有权限使用 delete history语句从表中删除历史行
DROP #有权限使用 drop table 删除表 或 drop view语句删除视图
GRANT OPTION #有权限使用 grant 授予表权限,仅能授权自己拥有的权限
INDEX #有权限使用 create index 创建索引
INSERT #有权限使用 insert 插入数据
REFERENCES #未使用
SELECT #有权限使用 select 插入数据
SHOW VIEW #有权限使用 show view 列出视图
TRIGGER #有权限使用 create trigger 和 drop trigger 创建和删除触发器
UPDATE #有权限使用 update 更新数据
Column Privileges:
INSERT (column_list) #有权限使用 insert 对指定的字段插入数据
REFERENCES (column_list) #未使用
SELECT (column_list) #有权限使用 insert 对指定的字段查询数据
UPDATE (column_list) #有权限使用 insert 对指定的字段更新数据
Function Privileges:
ALTER ROUTINE #有权限使用 alter function 更改存储过程
EXECUTE #
GRANT OPTION #有权限使用 grant 授予函数权限,仅能授权自己拥有的权限
Procedure Privileges:
ALTER ROUTINE #有权限使用 alter procedure 更改存储过程
EXECUTE #
GRANT OPTION #有权限使用 grant 授予存储过程权限,仅能授权自己拥有的权限
Proxy Privileges:
PROXY #授权该用户可代理
- 操作
授权已有账户
MariaDB [mysql]> grant select on test.* to 'cr'@'localhost';
授权并新建账户
MariaDB [mysql]> grant all privileges on *.* to 'test'@'localhost' identified by '1234' with grant option;
回收权限
REVOKE
priv_type [(column_list)]
[, priv_type [(column_list)]] ...
ON [object_type] priv_level
FROM user [, user] ...
REVOKE ALL PRIVILEGES, GRANT OPTION
FROM user [, user] ...
忘记密码
1、配置文件
添加:skip-grant-tables
2、重启服务
mysql.server restart
3、登陆
mysql
4、刷新权限
flush privileges
5、修改密码
alter user 'user'@'host' = password('password');
6、刷新权限
flush privileges
7、配置文件
注释:skip-grant-tables
8、重启服务
mysql.server restart