mysql备忘录
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
新建数据库
创建表
