mysql 姿势

Posted on |
使用 MySql 的基本姿势

MySql

  • navivat for mysql

BASIC

1
2
3
sudo apt-get install mysql-server
ps aux | grep mysqld
mysql -uroot -p

CONF

1
2
3
# 配置文件
sudo vim /etc/mysql/mysql.conf.d/mysqld.conf
bind-address = 127.0.0.1  # 这种情况外面是链接不进来的 0.0.0.0
1
2
3
4
5
6
# 授权
grant all privileges  on *.* to 'root'@'%';
# 授权并设置密码
grant all privileges  on *.* to 'root'@'%' identified by '***';
# mysql cmd
flush privileges;   # 立即刷新权限

Mariadb

MariaDB 配置文件位于 /etc/my.cnf

  • 安装
1
yum -y install mariadb mariadb-server
1
2
3
4
5
6
7
8
# 启动
systemctl start mariadb
# 开机启动
systemctl enable mariadb
# 停止
systemctl stop mariadb
# 重启
systemctl restart mariadb
1
2
3
SELECT User, Host, Password FROM mysql.user;  -- 查看所有用户
SELECT DISTINCT User FROM mysql.user;    -- 查看所有用户
show grants for test; -- 查看用户权限

数据库操作

忘记密码

1
2
3
4
5
6
7
# 启动免授权服务端 > shell
mysqld --skip-grant-tables
# 客户端   > shell
mysql -u root -p
# 修改用户名密码  > sql
update mysql.user set authentication_string=password('666') where user='root';
flush privileges;

修改密码

1
2
3
4
5
6
7
-- method 1 mysql root
updata user set password=password('新密码') where user= 'root';
FLUSH PRIVILEGES;   -- 将数据读取到内存中，从而立即生效
-- method 2 mysql root
SET PASSWORD FOR root=PASSWORD('新密码');
-- method 3 shell
mysqladmin -u root -p password 新密码

添加用户

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- 创建用户
create user '用户名'@'IP地址' identified by '密码';
-- 删除用户;
drop user '用户名'@'IP地址';
-- 修改用户;
rename user '用户名'@'IP地址' to '新用户名'@'IP地址';
-- 修改密码
set password for '用户名'@'IP地址' = Password('新密码')
-- method 1

-- 格式：grant select on 数据库.* to 用户名@登录主机 identified by “密码”

-- 允许从所有地址访问   非常危险  
grant select,insert,update,delete on *.* to test1@“%” Identified by “abc”;
-- 密码abc
grant select, insert, update, delete on mydb.* to test2@localhost identified by “abc”;

-- 无密码
grant select, insert, update, delete on mydb.* to test2@localhost identified by “”;
-- method 2
create user 'alex'@'192.168.1.1' identified by '123123';    -- alex 只能通过192.168.1.1地址访问数据库
create user 'alex'@'192.168.1.%' identified by '123123';    -- alex 只能在该网段内访问数据库
create user 'alex'@'%' identified by '123123';      -- alex 可以从所有地址访问数据库
delete from user where user='hzq' and host='192.168.11.%'; -- 删除用户

授权

1
2
3
4
5
6
7
--       权限        人 
grant select,insert,update  on db1.t1 to 'alex'@'%';
grant all privileges  on db1.t1 to 'alex'@'%';
revoke all privileges on db1.t1 from 'alex'@'%';
show grants for '用户'@'IP地址'                 -- 查看权限
grant  权限 on 数据库.表 to   '用户'@'IP地址'      -- 授权
revoke 权限 on 数据库.表 from '用户'@'IP地址'      -- 取消权限
1
2
3
4
5
对于目标数据库以及内部其他：
  数据库名.*           数据库中的所有
  数据库名.表          指定数据库中的某张表
  数据库名.存储过程     指定数据库中的存储过程
  *.*                所有数据库
1
2
3
用户名@IP地址         用户只能在改IP下才能访问
用户名@192.168.1.%   用户只能在改IP段下才能访问(通配符%表示任意)
用户名@%             用户可以再任意IP下访问(默认IP地址为%)

基础命令

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
show databases;     -- 显示数据库列表
use mysql;          -- 打开库
show tables;        -- 显示库中的数据表
describe t1;        -- or desc t1; 显示数据表的结构
create database db1;    -- 新建库
create database db2 default charset utf8;   -- !!! 新建库,编码为utf8
-- 创建库 编码为utf8 支持中文
CREATE DATABASE db2 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
-- 创建库 gbk编码
CREATE DATABASE db3 DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;
drop database db1;      -- 删库
drop table t1;          -- 删表
delete from t1;         -- 清空表,主键递增不会复位
truncate table t1;      -- 清空表,主键递增复位
select * from t1;       -- 显示表中的记录
show warnings;          -- 显示最后一个执行的语句所产生的错误、警告和通知
show errors;            -- 只显示最后一个执行语句所产生的错误
show create table t10 \G;   -- ?
flush privileges;       -- 将数据读取到内存中，从而立即生效。

创建表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- 创建表
create table t1(id int auto_increment,name char(10)
)engine=innodb default charset=utf8;

-- innodb 支持事务，原子性操作

-- myisam 存储更快些
create table t1(
        列名 类型 null,
        列名 类型 not null,
        列名 类型 not null auto_increment primary key,
        id int,
        name char(10)
    )engine=innodb default charset=utf8;

-- 是否可以为空
not null    -- 不可为空
null        -- 可为空
auto_increment -- 自增
primary key; -- 表示 约束(不能重复且不能为空); 加速查找
nid int not null defalut 2, -- 默认值 2

自增

自增，如果为某列设置自增列，插入数据时无需设置此列，默认将自增（表中只能有一个自增列）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

create table tb1(
      nid int not null auto_increment primary key,
      num int null)

--  or
create table tb1(
      nid int not null auto_increment,
      num int null,
      index(nid))   -- 主键

-- 注意：
-- 1、对于自增列，必须是索引（含主键）。
-- 2、对于自增可以设置步长和起始值
show session variables like 'auto_inc%';    -- 会话变量
set session auto_increment_increment=2;     -- 自增步长
set session auto_increment_offset=10;       -- 自增起始
show global  variables like 'auto_inc%';    -- 全局变量
set global auto_increment_increment=2;
set global auto_increment_offset=10;

主键

主键，一种特殊的唯一索引，不允许有空值，如果主键使用单个列，则它的值必须唯一，如果是多列，则其组合必须唯一。

1
2
3
4
5
6
7
8
9
10
create table tb1(
 nid int not null auto_increment primary key,
 num int null
)
-- or
create table tb1(
 nid int not null,
 num int not null,
 primary key(nid,num)   -- 多列主键
)

外键

外键，一个特殊的索引，只能是指定内容

1
2
3
4
5
6
7
8
9
10
create table color(
 nid int not null primary key,
 name char(16) not null
)

create table fruit(
nid int not null primary key,
smt char(32) null ,
color_id int not null,
constraint fk_cc foreign key (color_id) references color(nid))

修改表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 添加列：
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 testalter_tbl ALTER i SET DEFAULT 1000;
-- 删除默认值：
ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;