3 - 字段约束|MySQL索引|MySQL用户管理

2024-01-03 02:10:26

字段约束

设置在表头上,用来限制字段赋值
包括:

  • 是否允许给字段赋null值 默认是允许赋null值
  • 默认值 在不配置的时候,mysql服务默认分配的是null值
  • 键值:就是设置在字段上的标签 每种标签都有各自的功能,默认字段没有标签
  • 额外设置 字段可以自增长 默认没有自增长的设置

查看表的字段约束条件使用的命令:

mysql > desc db1.t1

在这里插入图片描述
建表的时候给表头设置默认值和不允许为空值

create table db1.t31(name char(10) not null,class char(7) default "xxx",likes set("money","game","film") not null default "film,music");
  • 修改已有表中字段的默认值
create table db1.t34 (name char(10),age tinyint); # 建表是没有设置默认值和不允许为null
desc db1.t34

在这里插入图片描述

alter table db1.t34 modify name char(10) not null default "" ,modify age tinyint unsigned not null default 25; # 修改字段不允许为null 设置默认值

在这里插入图片描述

主键

查看表结构时 key 列包含

  • 唯一索引 (unique)字段的唯一值(表头值不能重复 可以复null值)
create table DB1.t43(name char(10),hz_id char(18) unique); # 身份证号唯一 
mysql > insert into DB1.t43 values("bob",null)
  • 主键 表头的值 不允许赋null 且不能重复
    表结构中key的标志是PRI
    在这里插入图片描述
    语法格式一:
mysql> create table db1.t35(name char(10),hz_id char(10) primary key ,class cahr(10));

语法格式二:

mysql> create table db1.t36(name char(10),hz_id char(10),class char(10),primary key(hz_id))

在这里插入图片描述
删除主键命令格式 向表头下存储数据不受主键的限制

alter table db1.t36 drop primary key;

添加主键标签

alter table db1.t36 add primary key(hz_id)
  • 复合主键
    多个表头一起做主键,复合主键字段的值不允许同时重复且不允许赋null值
    在这里插入图片描述
mysql> create table db1.t37(cip varchar(15),port smallint ,status enum("allow","deny"),primary key(cip,port))

在这里插入图片描述
删除复合主键

alter table db1.t37 drop primary key; # 不能单个删除

添加复合主建

alter table db1.t37 add primary key(cip,port);

主键使用总结:无论是一个表头做主键 还是多个表头做主键 约束的方式都是不允许给表赋重复的值和null值

  • 主键与auto_increment 连用
    当给表头设置了auto_increment属性后,插入记录时,如果不给表头赋值 表头会通过自加1的计算结果赋值
    要想让表头有自增长的功能,那么必须有主键的设置才可以。
    查看表结构时 在Extra位置显示
create table db1.t38(行号 int primary key auto_increment ,姓名 char(10),班级 char(7),住址 char(10));
insert into db1.t39(姓名,班级,住址) values("bob","nsd2107","bg");
insert into db1.t39(姓名,班级,住址) values("bob","nsd2107","bg");

在这里插入图片描述
也可以自定义自增长 字段的值
自增长列 truncate后从1开始 delete继续编号

给已有表添加行号字段

alter table db1.t3 add id int primary key auto_increment first;

外键

外键的核心思想:保证数据的一致性
插入记录时,字段值在另一个表字段值范围内选择
外键使用规则:

  • 表存储引擎必须时innodb
  • 字段类型要一致
    外键:foreign key
    创建外键命令格式
create table 库.表名(表头列表,
foreign key(表头名)  # 指定外键
references 库.表(表头名) # 指定参考的表头
on update cascade  # 同步更新
on delete cascade # 同步删除
)engine=innodb;

创建工资表

create table db2.gz(gz_id int ,pay float(7,2),
foreign key(gz_id) references yg(yg_id)
on update cascade on delete cascade
)engine=innodb;

查看存储引擎
在这里插入图片描述

删除外键 通过外键名称,删除表头的外键设置

alter table db2.gz drop FOREIGN KEY gz_idfk_1(外键名)

在已有表里添加外键

alter table 库.表 add foreign key(表头名) references 库.表(表头名) on update cascade on delete cascade;

MySQL索引

索引介绍

给表头加了索引标签之后,会对表头下的数据生成排队信息保存
在表对应的文件里(表名.ibd)比如 给db1 库下t3表的表头加了索引
对应的存储文件是 /var/lib/mysql/db1/t3.ibd
在这里插入图片描述

优缺点

优点:

  • 大大提高检索速度
  • 减少服务器扫描的数据量
  • 将随机io变成顺序io
  • 可以帮助服务器避免排序和临时表
    缺点:
  • 减慢表中修改速度
  • 占用磁盘空间

索引使用规则

  • 一个表中可以头多个索引
  • 通常在where条件中的字段上配置索引
  • 可以重复 且可以为null值
  • index索引字段的标志为mul

索引的分类

  • 普通索引 值给表中一个加索引 (index)
  • 唯一索引 (unique)
  • 全文索引 char archar text类型 数据量比较大的 (fulltext)
  • 单列索引 (index(姓名))
  • 多列索引 (index(姓名,年龄…))

索引的管理

  • 创建普通索引(index)
    • 建表时创建索引
create database if not exists home; 
use home ;
create table tea4 (
id char(6) not null,
name varchar(6) not null,
age int(3) not null,
gender enum('boy','girl') default 'boy',
index(id),index(name)
);

查看新建tea4表的字段结构,可以发现两个非空索引字段的KEY标志为MUL:

mysql> DESC tea4;
+--------+--------------------+------+-----+---------+-------+
| Field  | Type               | Null | Key | Default | Extra |
+--------+--------------------+------+-----+---------+-------+
| id     | char(6)            | NO   | MUL | NULL    |       |
| name   | varchar(6)         | NO   | MUL | NULL    |       |
| age    | int(3)             | NO   |     | NULL    |       |
| gender | enum('boy','girl') | YES  |     | boy     |       |
+--------+--------------------+------+-----+---------+-------+

查看索引的详细信息

//查看详细信息
mysql> SHOW INDEX FROM tea4\G
*************************** 1. row ***************************
        Table: tea4
   Non_unique: 1
     Key_name: id
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE                          //使用B树算法
      Comment: 
Index_comment: 
*************************** 2. row ***************************
        Table: tea4
   Non_unique: 1
     Key_name: name                      //索引名称
 Seq_in_index: 1
  Column_name: name                           //字段名称
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
  • 删除索引
drop index id on home.tea4; # 删除name字段的索引
  • 添加索引
create index nianling on tea4(age);      //针对指定字段创建索引
  • 验证select是否使用到索引做查询 explain命令
explain select * from db1.t3 where name='sshd';

在这里插入图片描述
给db1库下的t3表加索引名

create index xingming on db1.t3(name)

在这里插入图片描述

用户管理

用户授权

  • 什么是用户授权
    数据库管理员root用户登陆服务后,添加普通用户设置权限和用户密码。
    默认情况 只允许数据管理员root在本机访问数据服务 默认不允许其他客户端访问服务,也不能使用其他用户在本机
  • 用户授权命令格式
grant 权限列表 on 库名 to 用户名@"客户端地址" identified by "密码" [with grant option](可以用grant添加权限)

库名:表示方式:
在这里插入图片描述
权限列表:

  • ALL 表示所有权限 表示所有命令
  • USAGE 表示无权限 (除了查看之外权限都没有 show desc )
  • SELECT、UPDATE、INSERT 表示只有个别权限
  • SELECT、UPDATE(字段1,字段n)表示权限仅对指定字段有访问权限

用户名:添加用户时 自定义即可 存储在mysql库下user表的user字段下
客户端地址:网络中的哪些主机可以使用添加的用户连接数据库服务 表示的方式有:

  • % 表示网络中的所有主主机
  • 192.168.4.% 表示192.168.4网段内的所有主机
  • 192.168.4.1 表示仅仅是192.168.4.1 一台主机
  • localhost 表示数据库服务器本机

with grant option 让添加的用户也可以使用grant命令再添加用户,但用户本身要对mysql库有insert权限

权限撤销

  • 删除已有授权用户的权限
  • 库名必须和授权时的表示方式一样
    语法:
revoke 权限列表 on 库名 from 用户名@"客户端地址";

删除添加的用户

drop user 用户名@"客户端地址";

两台主机 50 数据库服务器 51客户端

  1. 添加用户dba007,对所有库和所有表有完全权限、且有授权权限,密码为123qqq…A 客户端为网络中的所有主机。
mysql > grant all on *.* to dba007@"%" identified by "123qqq...a" with grant option;
# 查看已添加的用户权限
mysql > show grants for dba007@"%";
+---------------------------------------------------------------+
| Grants for dba007@%                                           |
+---------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'dba007'@'%' WITH GRANT OPTION |
+---------------------------------------------------------------+

登陆的用户查看自己的访问权限

mysql> show grants;

//查看可以使用root用来连接的客户端地址
mysql> select host,user from mysql.user where user="root";
+-------------+------+
| host        | user |
+-------------+------+
| 192.168.4.% | root | //192.168.4.0/24 网段所有主机
| localhost   | root |  //本机登录
+-------------+------+
2 rows in set (0.00 sec)
  1. 允许192.168.4.0/24网段主机使用root连接数据库服务器,对所有库和所有表有完全权限、密码为123qqq…A
[root@host51 ~]# mysql -h192.168.4.50 -udba007 -p123qqq...a
mysql> grant all on *.* to root@"192.168.4.%" identified by "123qqq...a";
mysql> select user,host  from mysql.user;

在这里插入图片描述
3. 撤销root从本机访问权限,然后撤销

[root@host50 ~]# mysql -hlocalhost -uroot -pNSD123...a
//查看登录用户信息
mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql> revoke all on *.* from root@"localhost";
mysql> show grants;
+--------------------------------------------------------------+
| Grants for root@localhost                                    |
+--------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'root'@'localhost'                     |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+--------------------------------------------------------------+
2 rows in set (0.00 sec)
  1. 恢复root本机登陆的权限
[root@dbsvr1 ~]# mysql -h192.168.4.50 -udba007 -p123qqq...A //dba007用户登录
//设置root用户本机登录的权限 
mysql> grant all on  *.* to root@"localhost" identified by "NSD123...a" with grant option;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> SHOW GRANTS FOR root@localhost;              //查看权限
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION | //有了
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)
  1. 允许任意主机使用webuser用户连接数据库服务器,仅对tarena库有查询,插入,更新,删除记录的权限,密码为123qqq…A
//数据库管理员本机登录
[root@host50 ~]# mysql -hlocalhost -uroot -pNSD123...a
mysql> grant select,insert,update,delete on tarena.* to webuser@"%" identified by "123qqq...A";
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show grants for webuser@"%";  //查看webuser用户权限 ,对所有库表没有任何软件仅对tarena库有权限
+---------------------------------------------------------------------+
| Grants for webuser@%                                                |
+---------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'webuser'@'%'                                 |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `tarena`.* TO 'webuser'@'%' |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)
  1. 撤销webuser用户权限,使其仅有查询记录权限。
//在50主机管理员登录
[root@host50 ~]# mysql -hlocalhost -uroot -pNSD123...a
mysql>
//撤销 webuser用户权限,使其仅有查询记录权限。
mysql> revoke insert,update,delete on tarena.* from webuser@"%";
Query OK, 0 rows affected (0.00 sec)
//查看webuser用户权限
mysql> show grants for webuser@"%";
+---------------------------------------------+
| Grants for webuser@%                           |
+---------------------------------------------+
| GRANT USAGE ON *.* TO 'webuser'@'%'          |
| GRANT SELECT ON `tarena`.* TO 'webuser'@'%' | //只剩select权限了
+---------------------------------------------+
2 rows in set (0.00 sec)
  1. 删除dba007用户
//查看已有的授权用户 是否有dba007用户
mysql> select user , host from mysql.user where user="dba007";
+--------+------+
| user   | host |
+--------+------+
| dba007 | %    |
+--------+------+
1 row in set (0.00 sec)
//删除dba007用户
mysql> drop user dba007@"%";
Query OK, 0 rows affected (0.00 sec)

用户权限追加

grant select on *.* to bob@"%" identified by "Ammm"
# 追加权限
grant insert on *.* to bob@'%'

授权库mysql库的使用:保存grant命令的执行结果 使用到了4张表 分别存储不同的授权信息

mysql> use mysql;
mysql> show tables;

在这里插入图片描述
可以通过查看表记录获取已有授权用户及访问权限
也可以修改表记录 修改授权用户的访问权限

user表的使用

查看当前数据库服务已有的用户

select host,user,authentication_string from mysql.user;
# user字段存储用户名
# host字段存储客户端地址
# authentication_string存储链接密码

文章来源:https://blog.csdn.net/shengweiit/article/details/135330097
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。