标签：name 用户备忘录 user mysql table select

MySQL8下载安装

腾讯云Ubuntu20.04服务器下载MySQL8并开启远程服务

下载

sudo apt update #更新Ubuntu存储库
sudo apt install mysql-server #下载mysql
sudo systemctl status mysql #查看mysql状态

配置启动

找到mysqld文件路径

find/ -name mysql.server

或

find / -name mysqld

配置文件路径

cp mysqld 文件路径 /etc/init.d/mysqld #复制文件到init.d
systemctl enable mysqld

登录mysql

以root身份免密登录mysql

su root #切换到root身份
mysql #登录mysql服务器

设置密码

use mysql;
flush privileges;
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your passwords'; #更换密码
flush privileges;
quit;

开通远程账户和权限

mysql -uroot -pyourpasswords #使用账号密码重新登录mysql

select host, user, authentication_string, plugin from user; #列出用户
create user 'root'@'%' identified by 'root';  #mysql8.0要先创建用户 # root为用户名，%需要访问的机器的IP，root是密码
grant all privileges on * .* to 'root'@'%'; #用户授权，root为用户名，%表示需要访问的机器的IP可以是任意
alter user 'root'@'%' identified with mysql_native_password by 'yourpasswords'; #设置访问用户密码
flush privileges; #刷新权限
GRANT ALL ON *.* TO `root`@`%` WITH GRANT OPTION; #授权操作
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'yourpasswords'; 
flush privileges;
quit;

开放端口

sudo iptables -I INPUT -p tcp --dport 3306 -j ACCEPT
iptables-save  //保存

永久保存

sudo invoke-rc.d iptables-persistent save
sudo invoke-rc.d iptables-persistent reload

sudo /etc/init.d/iptables-persistent save
sudo /etc/init.d/iptables-persistent reload

修改mysql配置文件

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

bind-address = 127.0.0.1 注释掉这一行，即前面加#。该语句表示只允许本机访问，注销后允许开启远程访问，保存退出

#bind-address = 127.0.0.1

重启mysql服务，查看端口

netstat -aptn|grep 3306

如果顺利的话，状态如下图示：

服务器网页端防火墙规则添加开放的端口

mysql.infoschema 用户问题

Mysql Error:The user specified as a definer (‘mysql.infoschema’@’localhost’) does not exist 解决方案

create user 'mysql.infoschema'@'localhost' identified by '123456'; #创建用户
grant all on *.* to 'mysql.infoschema'@'localhost'; #授权

使用mysql

使用命令行客户端连接到 MySQL

连接

mysql -h localhost -P 3306 -uroot -pyourpasswords

查看当前用户

whoami

退出

mysql> exit;

创建数据库

数据库是许多表的集合，逻辑关系如下：
数据库服务器数据库→表（由列定义）→行

创建数据库

mysql> CREATE DATABASE company;

切换到数据库：

mysql> use company;

也可以在链接数据库时

mysql u root - p company

查询数据库

查询连接到了哪个数据库

mysql> select database()

查询有权访问的所有数据库

mysql> show databases;

查看数据库目录

mysql> show variables like 'datadir';

检查数据目录内的文件:

sudo ls - lhtr /usr/local/mysql/data/

创建表

mysql数据类型

数字:TINY,INT,SMALL,INT,MEDIUMINTINT,BIGINT,BIT
浮点数:DECIMAL,FLOAT,DOUBLE
字符串:CHAR,VARCHAR,BINARY,VARBINARY,BLOB,TEXT,ENUM,SET
Spatial
Json

表的定义

mysql> CREATE TABLE IF NOT EXISTS 'company'.'customers'(
'id' int signed AUTO INCREMENT PRIMARY KEY , 
'first_name' varchar (20), 
'last_name' varchar (20) , 
'country' varchar (20) 
) ENGINE=InnoDB;

句点符号：可以使用database.table如果已经连接数据库，则可以简单地使用customers而不是 company.customers
IF NOT EXISTS：如果存在一个具有相同名字的表，并且你指定了这个子句，MySQL只会抛出一个警告，告知表已经存在，否则MySQL将抛出一个错误
id:它被声明为一个整型数，因为它只包含整型数。除此之外，还有两个关键字，AUTO INCREMENT和PRIMARY KEY
AUTO CREMENT：自动生成线性递增序列，因此不必担心每一行的id分配值
PRI MARY KEY：每行都由一个 UNIQUE列标识。只有一列应该在表中定意。如果一个包含AUTO_INCREMEN列，则它会被视为PRIMARY_KEY
first_name、last_name和conuntry:他们包含字符串，一次他们被定义为varchar
Engine:与列定义一起，还应该指定存储引擎。一些类型的存储引擎包括InnoDB、MyISAM、 FEDERATED、FEDERATED、BLACKHOLE、CSV和MEMORY。在所有引擎中，InnoDB是唯一的事务引擎，也是默认引擎。

列出所有引擎

mysql> show engines\G;

查看表结构

mysql> show create table customers\G

或

mysql> desc customers;

mysql会在数据目录内创建.ibd文件

sudo ls -lhtr /usr/local/mysql/data/company

克隆表结构

create table new_customers like customers;

插入、更新和删除行

INSERT、UPDATE、DELETE、SELECT 称为数据操作语言（DML）语句。 INSERT、UPDATE、DELETE称为写操作，或者简称为写（write）。 SELECT 是一个读操作称为读（read）。

插入

mysql> insert ignore into 'company'.'customers'(first_name,last_name,country)
values
('mike','christensen','USA'),
('andy','hollands','australia');

或者明确插入id：

mysql> insert ignore into 'company'.'customers'(id,first_name,last_name,country)
values
(1,'mike','christensen','USA'),
(2,'andy','hollands','australia');

IGNORE ：如果该行已经存在，并给出了IGNORE句，则新数据将被忽略， INSERT语句仍然会执行成功，同时生成一个警告和重复数据的数目。反之，如果未给出IGNORE子句，则 INSERT 语句会生成一条错误信息行的唯性由主键标识。

查询警告：

mysql> show warnings;

更新

update语句用于修改表中的现有的记录：

mysql> update customers set first_name='andy',country='UK' where id = 2;

where:会是用于过滤的子句。在where子句后指定的任何条件都会用于过滤，被筛选出来的行都会被更新

删除

mysql> delete from customers where id=4 and first_name='andy';

replace、insert和on duplicate key update

在很多情况下，我们需要处理重复项，行的唯一性由主键标识。如果行已经存在，则REPLACE会简单地删除行并插入新行；如果行不存在，则REPLACE等同于INSERT。

mysql> replace into customers values (1,'mike','christensen','america');

如果你想在行已经存在的情况下处理重复项，则需要使ON DUPLICATE KEY UPDATE如果指定了 ON DUPLICATE KEY UPDATE 选项，并且 INSERT 语句在 PRIMARY KEY 中引发了重复值，则MySQL 会用新值更新已有行。

insert into customers values (1,'mike','christensen','UK') on duplicate key update customers=customers+values(customers);

清空表所有数据

mysql> truncate table customers;

加载示例数据

mysql - u root - p < xxx.sql

查询数据

mysql> select * from xxtable;

可以使用星号（*）选择所有列

选择列

假如列出table表中的id和name列：

mysql> select id,name from table;

计数

从表中计算条目数量：

mysql> select count(*) from table;

条件过滤

从表table找到name为mike，sex为1的所有id：

mysql> select id from table where name=mike and sex=1;

操作符

equality

参考前面使用进行过滤的例子。

in

检查一个值是存在一组值中,例如：计算所有name为mike或andy所有条目数量：

mysql> select count(*) from table where name in ('mike','andy');

between...and

检查一个值是否在一个范围内，例如找到所有id为[0-10]范围内的条目：

mysql> select count(*) from table where id between 0 and 10;

not

找出id不是[0-10]的所有条目：

mysql> select count(*) from table where id not between 0 and 10;

简单匹配模式

可以使用like运算符来实现简单模式匹配。使用下画线（_)来精准匹配一个字符，使用（%来匹配任意数量的字符。

计算所有名字以m开头的条目个数：

mysql> select count(*) from table where name like 'm%';

计算所有名字以m开头并且以e结尾的条目个数：

mysql> select count(*) from table where name like 'm%e';

计算所有名字包含'ik'的条目个数：

mysql> select count(*) from table where name like '%ik%';

计算所有名字以e结尾的条目个数：

mysql> select count(*) from table where name like '%e';

计算所有名字任意两个字符开头之后是k后面任意字符的的条目个数：

mysql> select count(*) from table where name like '__k%';

正则表达式

可以利用RLIKE或REGEXP运算符在WHERE子句中使用正则表达式。使用REGEXP的方法有多种（见下表）。

表达式	描述
*	零次或多次重复
+	一个或多个重复
?	可选字符
.	任何字符
\ .	区间
^	以...开始
$	以...结束
[abc]	只有a,b或c
[^abc]	非a非b也非c
[a-z]	字符a-z
[0-9]	数字0-9
^...$	开始和结束
\d	任何数字
\D	任何非数字字符
\s	任何空格
\S	任何非空格字符
\w	任何字母数字字符
\W	任何非字母数字字符
	m次重复
	m到n次重复

跳转链接：更多正则表达式

计算所有名字以m开头的条目个数：

mysql> select count(*) from table where name rlike '^m';

限定结果

查询id小于20的任意10条目，可以使用limit子句实现：

mysql> select id,name from table where id < 20 limit 10;

使用表别名

默认情况下，select子句中给出的任何列都将显示在结果中，前面计数示例统计结果在COUTN(*)，可以使用as更改别名：

mysql> select count(*) as count from table where name rlike '^m';

对结果排序

查询id最大的5个条目：

mysql> select id from table order by id desc limit 5;

也可以不指定列名，使用第n列进行排序：

mysql> select id from table order by 2 desc limit 5;

对结果分组（聚合函数）

可以在列上使用group by子句对结果进行分组，然后使用aggregate（聚合）函数，例如count、max、min和average。还可以在group by子句的列上使用函数。
跳转链接:更多聚合函数

count

分别计算所有sex为1和sex为2的条目：

mysql> select sex,count(*) from table group by sex;

找出name中最常见的2个，并排序。

mysql> select name,count(name) as count from table group by name order by count desc limit 10;

sum

查找每年给予员工的薪水总额，并按薪水高低对结果进行排序。YEAR()函数将返回给定日期所在的年份：

mysql> select '2017-06-12',year('2017-06-12');
mysql> select year(from_data),sum(salary) as sum from salaries group by year(from_data) order by sum desc;

average

查找平均工资最高的10名员工：

mysql> select emp_no,avg(salary) as avg from salaries group by emp_no order by avg desc limit 10;

distinct

可以使用distinct子句过滤出表中的不同条目：

mysql> select distinct name from table;

having

可以通过添加having子句来过滤group by子句结果:

mysql> select emp_no,avg(salary) as arg from salaries group by emp_no having avg > 14000 by avg desc;

创建用户

不应该访问mysql时使用root用户，除非localhost的管理任务。应该创建用户、限制访问、限制资源使用等等。

创建用户命令

使用root用户连接到mysql并执行create user命令来创建新用户。

mysql> create user if not exists 'company_read_only'@'localhost' identified with mysql_native_password by 'yourpasswords' with max_queries_per_hour 500 max_updates_per_hour 100;

用户名：company_read_only
仅从localhost访问
可以限制对IP范围的访问，例如 10.148.%.%。通过给出%，用户可以从任何主机访问
密码：yourpasswords
使用mysql_native_password（默认）身份验证。
还可以指定任何可选的身份验证，例如sha256_password、LDAP或Kerberos。
用户可以在一小时内执行的最大查询数为500。
用户可以在一小时内执行的最大更新次数为100次。

当客户端连接到mysql服务器时，它会经历两个访问控制阶段：

连接验证
请求验证

在连接验证过程中卖服务器通过用户名和连接的主机名来识别连接。服务器会调用用户认证插件并验证密码。服务器还会检查用户是否被锁定。
在请求验证阶段，服务器会检查用户是否有足够的权限执行每项操作。
前面的语句中，必须以明文的方式输入密码，这些密码可以记录在命令历史记录文件$ HOME/.mysql_history中。为了避免使用明文密码，可以在本地服务器计算hash值直接使用hash密码。

mysql> select password('yourpassword');
mysql> create user if not exists 'company_read_only'@'localhost' identified with mysql_native_password by 'hash';

hash 是密码计算出来的哈希值

授予和撤销用户的访问权限

可以限制访问特定的数据库或表，或限制特定操作，如select、insert和update。需要拥有grant权限，才能为其他用户授予权限。

授予权限

将read only(select)权限授予company_read_only用户：

mysql> grant select on company.* to 'company_read_only'@'localhost';

星号(*)表示数据库的所有表。

将insert权限授予新的company_insert_only用户：

mysql> grant insert on company.* to 'company_insert_only'@'localhost' identified by 'xxxx';
mysql> show warnings\G

将write权限授予新的company_write用户：

mysql> grant insert,delete,update on company.* to 'company_write'@'%' identified with mysql_native_password as 'yourpasswords';

限制查询指定的表：

mysql> grant select on employees.employees to 'employees_read_only'@'%' identified with mysql_native_password as 'yourpassword';

限制查询指定列：

mysql> grant select（name） on employees.employees to 'employees_read_only'@'%' identified with mysql_native_password as 'yourpassword';

拓展授权：

可以通过执行新授权来拓展授权。

mysql> grant select(salary) on employees.salaries to 'employees_ro'@'%';

创建SUPER用户。需要一个管理员账户来管理该服务器。all表示除grant权限之外的所有权限。

mysql> create user 'dbadmin'@'%' identified with mysql_native_password by 'yourpasswords';
mysql> grant all on *.* to 'dbadmin'@'%';

授予grant特权。用户拥有grant option权限才能授予其他用户权限。可以将grant特权扩展到dbadmin超级用户：

mysql> grant grant option on *.* to 'dbadmin'@'%';

跳转链接：更多有关的权限类型

检查授权

检查所有用户的授权，检查employees_ro用户的授权:

mysql> show grants for 'employees_ro'@'%'\\G

检查dbadmin用户的授权。可以看到dbadmin用户拥有的所有授权：

mysql> show grants for 'dbadmin'@'%'\G

撤销权限

撤销'company_write'@'%'用户的delete访问权限:

mysql> revoke delete on company.* from 'company_write'@'%';

撤销employee_ro用户对薪水列的访问权限：

mysql> revoke select(salary) on emploees.salaries from 'employees_ro'@'%';

修改mysql.user表

所有用户信息及权限都存储在mysql.user表中，通过访问此表并修改此表来创建用户并授予权限。使用grant、revoke、set password或rename user等账户管理语句间接修改授权表，则服务器会通知这些更改，并立即子啊此将授权表加载到内存中。
如果使用insert、update和delete等语句直接修改授权表，则更改不会影响权限检查，除非你重新启动服务器或指示其重新加载表。如果直接更改授权表，但忘记了重新加载表，那么在重新启动服务器之前，这些更改无效。
可以通过执行下面语句完成grant表的重新加载：

mysql> flush privileges;

查询mysql.user表以找出dbadmin用户的所有条目：

mysql> select * from mysql.user where user='dbadmin'\G

可以更新mysql.user表并重新加载实现访问主机权限限制为localhost：

mysql> update mysql.user set host='localhost' where user='dbadmin';
mysql> flush privileges;

设置用户密码有效期

创建一个具有过期密码的用户

当开发人员第一次登录并尝试执行任何语句时，错误1820（HY0000）:将被抛出。在执行此语句之前，必须使用alter user语句重置密码：

mysql> create user 'developer'@'%' identified with mysql_native_password as 'yourpassword' password expire;
mysql> quit;
mysql -udeveloper -pyourpasswords
mysql> show databases; #抛出 1820异常

developer必须以下命令更改密码:

mysql> alter user 'developer'@'%' identified with mysql_native_password by 'yournewpassword';

手动设置过期用户：

mysql> alter user 'developer'@'%' password expire;

要求用户每隔90天更改一次密码:

mysql> alter user 'developer'@'%' password expire interval 90 day;

锁定用户

如果发现账户有任何问题，可以将其锁定。mysql支持使用create user或alter user锁定用户。

通过将alter lock子句添加到alter user语句来锁定账户：

mysql> alter user 'developer'@'%' account lock;

解锁用户

mysql> alter user 'develop'@'%' account unlock;

为用户创建角色

mysql的角色是一个权限的集合，角色的权限可以被授权和撤销。账户被授予角色后，该角色权限就会授予该用户，以避免为多个用户单独分配权限的麻烦。

创建角色

mysql> create role 'app_read_only','app_writes','app_developer'

使用gramt语句为角色分配权限

mysql> grant all on employees.* to 'app_read_only';
mysql> create user emp_read_only identified 'yourpasswords';
mysql> grant 'app_read_only' to 'emp_read_only'@'%';

使用mysql可视化工具navicat

连接mysql

新建数据库

创建表

标签：name,用户,备忘录,user,mysql,table,select
From： https://www.cnblogs.com/hggzhang/p/16599899.html

mysql备忘录

MySQL8下载安装

腾讯云Ubuntu20.04服务器下载MySQL8并开启远程服务

下载

配置启动

登录mysql

设置密码

开通远程账户和权限

开放端口

修改mysql配置文件

重启mysql服务，查看端口

服务器网页端防火墙规则添加开放的端口

mysql.infoschema 用户问题

使用mysql

使用命令行客户端连接到 MySQL

连接

查看当前用户

退出

创建数据库

创建数据库

切换到数据库：

查询数据库

查询连接到了哪个数据库

查询有权访问的所有数据库

查看数据库目录

创建表

mysql数据类型

表的定义

列出所有引擎

查看表结构

克隆表结构

插入、更新和删除行

插入

更新

删除

replace、insert和on duplicate key update

清空表所有数据

加载示例数据

查询数据

选择列

计数

条件过滤

操作符

equality

in

between...and

not

简单匹配模式

计算所有名字以m开头的条目个数：

计算所有名字以m开头并且以e结尾的条目个数：

计算所有名字包含'ik'的条目个数：

计算所有名字以e结尾的条目个数：

计算所有名字任意两个字符开头之后是k后面任意字符的的条目个数：

正则表达式

计算所有名字以m开头的条目个数：

限定结果

使用表别名

对结果排序

对结果分组（聚合函数）

count

分别计算所有sex为1和sex为2的条目：

找出name中最常见的2个，并排序。

sum

average

distinct

having

创建用户

创建用户命令

授予和撤销用户的访问权限

授予权限

将read only(select)权限授予company_read_only用户：

将insert权限授予新的company_insert_only用户：

将write权限授予新的company_write用户：

限制查询指定的表：

限制查询指定列：

拓展授权：

创建SUPER用户。需要一个管理员账户来管理该服务器。all表示除grant权限之外的所有权限。

授予grant特权。用户拥有grant option权限才能授予其他用户权限。可以将grant特权扩展到dbadmin超级用户：

检查授权

检查所有用户的授权，检查employees_ro用户的授权: