mysql学习总结

Mysql是最流行的关系型数据库管理系统,在WEB应用方面MySQL是最好的RDBMS(Relational Database Management System:关系数据库管理系统)应用软件之一.

下载安装

安装:

详细看这里

1
2
3
apt install mysql-server mysql-client
or
apt install mariadb-server mariadb-client

服务端启动:

1
sudo service mariadb start

客户端连接

使用mysql-client连接

1
mysql -h host -u user

使用mycli连接

1
mycli -h host -u user

数据库操作

mysql默认数据库

1
2
3
mysql - 用户权限相关数据
test - 用于用户测试数据
information_schema - MySQL本身架构相关数据

展示所有数据库

1
SHOW DATABASES;

创建数据库

1
CREATE DATABASE DB_NAME;

使用数据库

1
USE DB_NAME;

显示当前使用数据库中所有表

1
SHOW TABLES;

用户管理

创建用户

1
2
3
4
create user '用户名'@'IP地址' identified by '密码';
-- 用户名@IP地址 用户只能在改IP下才能访问
-- 用户名@192.168.1.% 用户只能在改IP段下才能访问(通配符%表示任意)
-- 用户名@% 用户可以再任意IP下访问(默认IP地址为%)

删除用户

1
drop user '用户名'@'IP地址';

修改用户

1
rename user '用户名'@'IP地址'; to '新用户名'@'IP地址';;

修改密码

1
set password for '用户名'@'IP地址' = Password('新密码')

授权管理

1
2
3
4
5
6
show grants for '用户'@'IP地址'                  -- 查看权限
grant 权限 on 数据库.表 to '用户'@'IP地址' -- 授权
revoke 权限 on 数据库.表 from '用户'@'IP地址' -- 取消权限

CREATE USER 'abcxander'@'localhost';
GRANT ALL PRIVILEGES ON *.* to 'abcxander'@'localhost' WITH GRANT OPTION; -- 创建一个可以做任何事情的新用户(如:root)

对于权限做一个总结 官方文档

全局权限

Privilege Description
CREATE USER 使用CREATE USER语句创建用户
FILE 读取和写入服务器上的文件
GRANT OPTION 授予全局特权 您只能授予您所拥有的权限
PROCESS 通过 SHOW PROCESSLIST 或 mysqladmin进程列表 显示活动进程的信息
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调试命令

数据库权限

Privilege Description
CREATE 创建数据库时,需要授予数据库权限来使用CREATE DATABASE语句
CREATE ROUTINE 使用CREATE PROCEDURE和CREATE FUNCTION语句创建存储过程
CREATE TEMPORARY TABLES 使用CREATE TEMPORARY TABLE语句创建临时表,该权限可以写入和删除这些临时表
DROP 删除数据库
EVENT 创建,删除和更改事件(EVENT),在MySQL 5.1.6中添加的语句
GRANT OPTION 授予数据库权限,您只能授予您所拥有的权限
LOCK TABLES 使用LOCK TABLES语句获取显式锁; 您还需要让表具有SELECT权限,以便于锁定它。

表权限

Privilege Description
ALTER 使用ALTER TABLE语句更改现有表的结构
CREATE 使用CREATE TABLE语句创建一个表,您可以为尚不存在的表授予CREATE权限
CREATE VIEW 使用CREATE_VIEW语句创建视图。
DELETE 使用DELETE语句从表中删除行
DROP 使用DROP TABLE语句或使用DROP VIEW语句来删除表和视图
GRANT OPTION 授予表权限,您只能授予您所拥有的权限
INDEX 使用CREATE INDEX语句在表上创建一个索引
INSERT 使用INSERT语句将行添加到表中,也可以在各个列上设置INSERT权限
SELECT 使用SELECT语句从表中读取数据,SELECT特权也可以在各个列上设置
SHOW VIEW 查看视图权限
TRIGGER 执行关联到您更新表上的触发器,执行CREATE TRIGGER和DROP TRIGGER语句。 您仍然可以看到触发器
UPDATE 使用UPDATE语句更新表中的现有行

列权限

可以为表的各个列设置一些权限,要使用列权限,请明确指定表,并在特权类型之后提供表的字段。例如,以下语句将允许用户读取员工的姓名和职位,但不能读取同一表中的其他信息,例如薪水。
GRANT SELECT (name, position) on Employee to 'jeffrey'@'localhost';

Privilege Description
INSERT (column_list) 使用INSERT语句添加一行指定内容,如果使用列级别INSERT权限,则必须在INSERT语句后指定使用的字段,所有其他列将被设置为其默认值,或NULL
SELECT (column_list) 使用SELECT语句读取列中的值,您无法访问或查询您没有SELECT权限的任何列,包括在WHERE,ON,GROUP BY和ORDER BY子句
UPDATE (column_list) 使用UPDATE语句更新现有行列的值,UPDATE语句通常包括一个仅更新某些行的WHERE子句,您必须在表的相应列具有SELECT权限或WHERE子句。

函数权限

Privilege Description
ALTER ROUTINE 使用ALTER FUNCTION语句更改存储函数的特性
EXECUTE 使用存储功能。 你需要通过函数访问的任何表或列的SELECT权限
GRANT OPTION 授予函数权限。 您只能授予您所拥有的权限。

程序权限

Privilege Description
ALTER ROUTINE 使用ALTER PROCEDURE语句更改存储过程的特性
EXECUTE 使用CALL语句执行存储过程,您执行的存储过程可能允许您没有权限的操作,例如将行插入到表中
GRANT OPTION 授予权限。 您只能授予您所拥有的权限

代理权限

Privilege Description
PROXY 允许一个用户作为另一个用户的代理

示例:

GRANT PROXY ON 'marijana'@'somehost' TO 'barrfhionn'@'somehost';

设置每个帐户的资源限制

Limit Type Decription
MAX_QUERIES_PER_HOUR 帐户每小时可发出的报表数量(包括更新)
MAX_UPDATES_PER_HOUR 帐户可以每小时发出的更新数量(不是查询)
MAX_CONNECTIONS_PER_HOUR 帐户可以每小时启动的连接数
MAX_USER_CONNECTIONS 可以从同一帐户接受的同时连接数; 如果为0,将使用max_connections; 如果max_connections为0,则该帐户的并发连接没有限制
MAX_STATEMENT_TIME 用户执行的语句超时(秒)。 另请参见超过一定时间执行的中止声明

帐户SSL / TLS选项

Option Description
REQUIRE NONE 此帐户不需要TLS,但仍可使用
REQUIRE SSL 该帐户必须使用TLS,但不需要有效的X509证书
REQUIRE X509 该帐户必须使用TLS,并且必须具有有效的X509证书
REQUIRE ISSUER ‘issuer’ 该帐户必须使用TLS,并且必须具有有效的X509证书。 此外,证书颁发机构必须是通过’issuer’颁发者指定
REQUIRE SUBJECT ‘subject’ 该帐户必须使用TLS,并且必须具有有效的X509证书。 此外,证书必须是通过’subject’指定
REQUIRE CIPHER ‘cipher’ 该帐户必须使用TLS,并且必须具有有效的X509证书。 此外,用于连接的加密必须使用’cipher’密码中指定的方法之一

示例:

1
2
3
4
GRANT USAGE ON *.* TO 'someone'@'localhost'
REQUIRE SUBJECT '/CN=www.mydom.com/O=My Dom, Inc./C=US/ST=Oregon/L=Portland'
AND ISSUER '/C=FI/ST=Somewhere/L=City/ O=Some Company/CN=Peter Parker/[email protected]'
AND CIPHER 'SHA-DES-CBC3-EDH-RSA';

数据表操作

创建表

1
2
3
4
5
6
create table 表名(
字段名 类型 是否可以为空 defalut 2 auto_increment primary key
字段名 类型 是否可以为空,
primary key(字段名,字段名),
constraint 外键名称(例如:FK_从表_主表) foreign key 从表(外键字段) references 主表(主键字段)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

删除表

1
drop table 表名

清空表

1
2
delete from 表名;     (可通过where来删除部分内容)
truncate table 表名; (清空表)

修改表

操作 sql语句
修改数据库字符集和对照表 ALTER DATABASE DB_NAME CHARACTER SET = \'utf8\' COLLATE = \'utf8_bin\';
添加列 ALTER TABLE 表名 add 字段名 类型;
删除列 ALTER TABLE 表名 drop column 字段名;
修改列类型 ALTER TABLE 表名 modify column 字段名 类型;
修改列字段名,类型 ALTER TABLE 表名 change 原字段名 新字段名 类型;
添加主键 ALTER TABLE 表名 add primary key(字段名);
删除主键 ALTER TABLE 表名 drop primary key;
删除自增并删除主键 ALTER TABLE 表名 modify 字段名 字段名 int, drop primary key;
添加外键 ALTER TABLE 从表 add constraint 外键名称\(例如:FK_从表_主表\) foreign key 从表\(外键字段\) references 主表\(主键字段\);
删除外键 ALTER TABLE 表名 drop foreign key 外键名称;
修改默认值 ALTER TABLE 表名 ALTER column 字段名 SET DEFAULT 1000;
删除默认值 ALTER TABLE 表名 ALTER column 字段名 DROP DEFAULT;

表内容操作

1
2
3
insert into 表名 (字段名,字段名...) values (值,值,值...);
insert into 表名 (字段名,字段名...) values (值,值,值...),(值,值,值...);
insert into 表名 (字段名,字段名...) select (字段名,字段名...) from 表名;

1
2
delete from 表名;
delete from 表名 where id1 and name'abcx';

1
update 表名 set name'abcx' where id>1

普通查询

1
2
3
select * from 表名
select * from 表名 where id > 1
select nid,name,gender as gg from 表名 where id > 1

条件查询

1
2
3
4
5
select * fromwhere id > 1 and name != 'abcx' and num = 12;
select * fromwhere id between 5 and 16; --BETWEEN 操作符选取介于两个值之间的数据范围内的值
select * fromwhere id in (11,22,33);
select * fromwhere id not in (11,22,33);
select * fromwhere id in (select nid from 表); --可以将查询出的内容当作条件

模糊查询

1
2
3
4
5
select * fromwhere name like 'abc%'  -- abc开头的所有(多个字符串)
select * fromwhere name like 'abc_' -- abc开头的所有(一个字符)
select * fromwhere name like 'abc[a-f]' -- [ ] 指定范围 ([a-f]) 或集合 ([abcdef]) 中的任何单个字符
select * fromwhere name like 'abc[^a-f]' -- [^] 不属于指定范围 ([a-f]) 或集合 ([abcdef]) 的任何单个字符
select * fromwhere name like '%[!0-9]%' -- [!] 排除 匹配内容不包含数字

分页查询

1
2
3
select * fromlimit 5; -- 前5行
select * fromlimit 4,5; -- 从第4行开始的5行
select * fromlimit 5 offset 4; -- 从第4行开始的5行

查询排序

1
2
3
select * fromorder byasc;              -- 根据 “列” 从小到大排列
select * fromorder bydesc; -- 根据 “列” 从大到小排列
select * fromorder by1 desc,列2 asc; -- 根据 “列1” 从大到小排列,如果相同则按列2从小到大排序

分组查询

1
2
3
4
5
6
select num fromgroup by num;
select num,nid fromgroup by num,nid;
select num,nid fromwhere nid > 10 group by num,nid order by nid desc;
select num,nid,count(*),sum(score),max(score),min(score) fromgroup by num nid;
select num fromgroup by num having max(id) > 10;
--特别的:group by 必须在where之后,order by之前

连表查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
无对应关系则不显示
select A.num, A.name, B.name
from A,B
Where A.nid = B.nid

自连接,无对应关系则不显示
select A.num, A.name, B.name
from A inner join B
on A.nid = B.nid

左连接,A表所有显示,如果B中无对应关系,则值为null
select A.num, A.name, B.name
from A left join B
on A.nid = B.nid

右连接,B表所有显示,如果B中无对应关系,则值为null
select A.num, A.name, B.name
from A right join B
on A.nid = B.nid

组合查询

1
2
3
4
5
6
7
8
9
10
11
12
组合,自动处理重合
select nickname
from A
union
select name
from B
组合,不处理重合
select nickname
from A
union all
select name
from B