字段约束｜MySQL索引｜MySQL用户管理
- 字段约束
- 主键
- 外键
- MySQL索引
- 索引介绍
- 优缺点
- 索引使用规则
- 索引的分类
- 索引的管理
- 用户管理
- 用户授权
- 权限撤销
- 用户权限追加
- user表的使用
字段约束
设置在表头上，用来限制字段赋值
包括：
- 是否允许给字段赋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客户端
- 添加用户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)
- 允许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)
- 恢复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)
- 允许任意主机使用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)
- 撤销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)
- 删除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存储链接密码
