Mysql 基本操作与权限机制

Mysql 基本操作与权限机制

MySQL是一种关系数据库管理系统，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

SQL 语言主要用于存取数据，查询数据。更新数据和管理关系数据库系统。

• DDL语句 数据库定义语言： 数据库、表、识图、索引、存储过程。
• DML语句 数据库操纵语言：插入数据 INSERT 、删除数据库DELETE、更新数据库UPDATE
• DCL语句 数据库控制语言： 如控制用户的访问权限GRANT、REVOKE
• DQL语句 数据库查询语言： 查询数据SELECT

安装

• rpm -ivh

• yum makecahe //刷新yum库

• md5sum 包名 //检查完整性

• yum list | grep mysql-community-server //安装mysql-server

  yum list -y

查看密码

# grep 'password' /var/log/mysqld.log 

登录

# mysql -uroot -p‘密码’

名词解释

数据库： 表的管理单元
表：记录的管理单元
记录：字段的管理单元
字段(列)：字段名，字段类型（长度），字段约束组成
类型：字符，数字，日期
约束：不能为空，自动增长

库操作

系统数据库

information_schema

虚拟库，主要存储了系统中的一些数据库对象的信息，例如用户表信息、列信息、权限信息、字符信息等

performance_schema

主要存储数据库服务器的性能参数。

mysql

授权库，主要存储系统用户的权限信息

sys

主要存储数据库服务器性能参数

创建数据库

mysql> create  database db2;

数据库命名规则

• 区分大小写
• 唯一性
• 不能使用关键字 create select
• 不能单独使用数字

查看数据库

mysql> show databases;
mysql> show create database wing;

查看所在库

mysql> select database();

切换数据库

mysql> use wing;

删除数据库

mysql> drop database 数据库名;

表操作

数据类型（常见）

数值类型

• 整数类型INT

  1. int 正常大小整数
  2. tinyint 0 -255 大小的整形数据

• 浮点整数型float

• 定点数类型 decimal

• 二进制bit类型测试

字符串类型

• varchar

  列中的值为可变长字符串，长度，0~65535

• char

  列的长度固定为创建表时声明长度，0~255

• BLOB 特殊符号

枚举和集合

• enum 枚举类型 （单选）

  只能在给定范围内选择一个值

• set 集合类型 （多选）

  在给定范围内可以选择一个或一个以上的值

时间和日期类型

• date
• time
• datetime
• timestamp
• year

表完整性约束

PRIMARY KEY (PK)

标识该字段为该表的主键，可以唯一的标识记录，不可以为空 UNIQUE + NOT NULL

FOREIGN KEY (FK)

标识该字段为该表的外键，实现表与表（父表主键/子表1外键/子表2外键）之间的关联

NOT NULL

标识该字段不能为空

UNIQUE KEY (UK)

标识该字段的值是唯一的，可以为空，一个表中可以有多个UNIQUE KEY

AUTO_INCREMENT

标识该字段的值自动增长（整数类型，而且为主键）

DEFAULT

为该字段设置默认值

UNSIGNED

无符号，正数

ZEROFILL

使用0填充，例如0000001

基础操作

表示数据库存储数据的基本单位，由若干个字段组成，主要用来存储数据记录。

使用编辑器编辑指令

mysql> edit
mysql> \e

命令行操作数据库

^注：在脚本使用

# mysql -u root -p1 -e "use db2;create table t3(name char(20),pass char(100));insert into t3 set name='wing',pass=password('123')"

在mysql客户端内执行系统命令

mysql> system  ls
mysql> \!  ls

表数据操作

数据操作

语法

create table 表名(
        字段名1  类型[(宽度) 约束条件],
        字段名2  类型[(宽度) 约束条件],
        字段名3  类型[(宽度) 约束条件])[存储引擎 字符集];
# 在同一张表中，字段是不能相同
# 宽度和约束条件可选
# 字段名和类型必须写

mysql> CREATE  DATABASE school;                     //创建数据库school
mysql> use school;
mysql> create table student1(
    -> id int,
    -> name varchar(50),                 
    -> sex enum('m','f'),
    -> age int
    -> );

查看表

mysql> show tables; 

查看表结构

mysql> desc  school;

表中插入内容

mysql> insert into 表名（字段1，字段2...）values（字段值列表...）；
# 指定字段插入
mysql> inser into 表名 values（顺序插入）；

查看所有表中所有字段的值

mysql> select * from 表名;

查看表中指定字段的值

mysql> select 字段名  from hello;

更新数据

语法

 语法：
    UPDATE 表名 SET
            字段1=值1,
            字段2=值2,
            WHERE CONDITION；

示例

mysql> update hello set   name="hai"  where  id=12;
                    更改的地方           条件
# 条件用来定位

修改表

修改表名

alter table  表名   rename  新表名;

添加字段

1.默认追加到最后
alter table 表名  add  字段名  数据类型  约束条件 ；
2.追加到开头
alter table 表名  add  字段名  数据类型  约束条件 first； 
3.追加到某个字段之后
alter table 表名  add  字段名  数据类型  约束条件 after 旧字段名；

修改字段

1.修改数据类型
alter table  表名 modify 字段名 数据类型 完整性约束条件；
2.修改字段名和数据类型
alter table 表名 change 旧字段 新字段 新数据类型 完整性约束；

删除字段

alter  table  表名 drop 字段名；

复制表

复制表结构和记录

^注：不会复制：主键、外键、索引

cretae   table 新表名 select * from  旧表名;

复制表结构

^包括key

create  tabe  新表名 like 旧表名；

删除表

drop  table  表名；

删除表数据

delete from  表名  where  字段名=内容;

单表查询

基本查询

简单查询

select * from 表名；
select 字段名，字段名 from 表名；

四则运算查询

select 字段名+运算 from 表名；

定义显示格式

select concat（字段名，字段名） from  表名；

单条件查询

select * from 表名 where  字段名=内容；

多条件查询

select  *  from 表名  where 字段名+条件 and  字段名+条件；
                                       or                         

关键字查询

select  *  from 表名  where not 字段名+条件； 
select  *  from 表名  where 字段名 is null；
select  *  from 表名  where 字段名 in/no in （条件）；

关键字like 模糊查询

1.通配符 % 代表一个或多个
select * from 表名 where  字段名 like ‘xx%’
2.通配符 _ 每一个代表一个字节
select * from 表名 where  字段名 like ‘xx_’
# 注意 一定要用单引号。

查询排序

单列排序

1.按从小到大
select * from 表名 where  order by  字段名；   //默认从到大
select * from 表名 where  order by  字段名 asc //从小到大
2.按从大到小
select * from 表名 where  order by  字段名 desc；

按多列排序

select * from 表名 order by  字段名 asc/desc ，字段名 asc/desc；

限制查询的记录数

select * from   表名  order by  字段名 desc  limit 5； //显示5条
select * from   表名  order by  字段名 desc  limit 0，5 //0条到第5条

使用正则表达式查询

select * from  表名  where  字段名 regep  '内容'

多表查询

交叉连接

生成笛卡尔积，它不使用任何匹配条件

内连接

只连接匹配的行

• where

select 表1.字段1，表1.字段2，表2.字段1，表2.字段2 from 表1，表2 where 表1.字段=字段

• inner join

select 表1.字段1，表1.字段2，表2.字段1，表2.字段2 from 表1 inner join 表2  on 字段=字段

外链接

• 左连接 (left)：会显示左边表内所有的值，不论在右边表内不匹配

select 表1.字段1，表1.字段2，表2.字段1，表2.字段2 from  表1 left join 表2
on 表1.字段1=表2.字段2;

• 右连接(right) ：会显示右边表内所有的值，不论在左边表内不匹配

select 表1.字段1，表1.字段2，表2.字段1，表2.字段2 from  表1 right join 表2 on 表1.字段1=表2.字段2;

全外链接

包含左、右两个表的全都行。

select * from  表1 full join 表2 on 表1.字段1=表2.字段2;

复合条件查询

内连接查询+条件；

子查询

将一个查询语句嵌套在另一个查询语句中。

内层查询语句的查询结果，可以为外层查询语句提供查询条件。

带IN关键字的子查询

select * from  表1  where  字段1   IN （select  字段1 from  表2）;  

权限机制

权限级别

• Global level 系统级，所有库，所有表的权限
• database level 某个数据库中所有的表的权限
• table level 库中某个表的权限
• column level 表中某个字段的权限
• procs level 某个存储过程的权限
• proxies level 代理服务器的权限

权限原理

语法格式

grant 权限列表 on 库名.表名 to '用户名'@'客户端主机' [identified  '密码'   with option参数]

权限列表

all：所有权限（不包括授权权限）
select.update  查询更新

数据库.表名

*.*               所有库下的所有表            Global level
web.*             web库下的所有表            Database level
web.stu_info       web库下的stu_info表       Table level

客户端主机

 %   所有主机
 192.168.2.%            192.168.2.0网段的所有主机
 192.168.2.168          指定主机
 localhost               本地主机

with_option参数

GRANT OPTION：              授权选项
MAX_QUERIES_PER_HOUR：       定义每小时允许执行的查询数
MAX_UPDATES_PER_HOUR：       定义每小时允许执行的更新数
MAX_CONNECTIONS_PER_HOUR：   定义每小时可以建立的连接数
MAX_USER_CONNECTIONS：       定义单个用户同时可以建立的连接数

权限应用的顺序

user (Y|N) ==> db ==> tables_priv ==> columns_priv

查看权限表

Global level

select * from mysql.user\G; 

Database level

select * from mysql.db\G;

Table level

select * from mysql.tables_priv\G;

Column level

select * from mysql.columns_priv\G;
前提是有库，有表，有权限。

用户管理

登录和退出

# nysql -h IP -P 端口  -uroot -p‘密码’  -e“sql语句”
-h 指定主机名       默认本机
-P 指定服务器端口   默认3306
-u 指定用户名       默认root
-p 指定登录密码     默认空密码
-e 接sql语句

创建用户

create user

create user  name@'localhost' identified by 'password';

grant … on … to

grant授权

grant all on *.*  to 'name'@'localhost'  identified  by 'password';
       |       |---左库 右表     |--本地或远程
       |---可换select insert 等                        

^注：在mysql8.0.11后grant授权需要先创建账号再进行授权。

手动更新权限表的时候需要刷新权限

mysql> flush privileges;

删除用户

drop user 语句删除

drop  user  'name'@'localhost';

delete语句删除

delete  from mysql.user  where user='name' and  host='loacalhost';

修改密码

修改自己的密码

1.系统命令修改
# mysqladim  -uroot -p'密码'  password '新密码'
2.修改表
mysql> update mysql.user set authentication_string=password where user='root' and host='localhost';
3.mysql命令修改
mysql> set password=password('新密码');
mysql> set password='新密码';   #不同版本两种方式

修改其他用户的密码

mysql> set password for achan@'loaclhost'=password('新密码');
mysql> set password for achan@'loaclhost'='新密码';

忘记root密码

1.修改配置文件，使登录mysql不需要密码
2.登陆之后修改密码，取消上一步操作

# vim /etc/my.cnf
[mysqld]
skip-grant-tables    <---增加

# systemctl  restart  mysqld
# myslq -uroot

grant

赋予权限

grant all on *.* to admin1@'%' identified by '密码';

回收权限

查看权限

1.查看自己的权限
show grants\G
2.查看别人的权限
SHOW GRANTS FOR admin1@'%'\G

回收权限revoke

revoke  权限列表  on 数据库名  from 用户名@客户端主机；
revoke delete  ON *.*  from  admin1@’%’;      //回收部分权限
revoke all provo;eges  on *.*  from admin2@’%’;   //回收所有权限

远程登录

创建账户

create user tom@'%'  identified by 'password';
%包括127.0.0.1和localhost

远程登录

# mysql -utom -h IP  -P 3307

端口

默认端口3306

端口号修改

# vim /etc/my.cnf
[mysqld]
prot=3307

本地登录，端口号不生效，任何端口都可以，远程登录需使用改过的端口。