数据导入导出 、 管理表记录 、 匹配条件 、 MySQL管理工具
1 字段约束
字段约束的作用是什么？ 设置在表头上 ，用来限制字段赋值
字段都有哪些约束？（每一种约束都有各自的功能）
字段约束分类：
1、 PRIMARY KEY：主键，用于保证该字段的值具有唯一性并且非空。
2、NOT NULL ：非空，用于保证该字段的值不能为空。
3、DEFAULT：默认值，用于保证该字段有默认值。
4、UNIQUE：唯一索引，用于保证该字段的值具有唯一性，可以为空。
5、 FOREIGN KEY：外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值，在从表添加外键约束，用于引用主表中某些的值。
查看表的字段约束条件使用的命令
mysql> desc 库.表； 比如 desc db1.t3;
mysql> desc db1.t3;
字段名 数据类型 |-------->约束条件<------------|
空 键值 默认值 额外设置
+----------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+-------+ | name | char(50) | YES | | NULL | | | password | char(1) | YES | | NULL | | | uid | int(11) | YES | | NULL | | | gid | int(11) | YES | | NULL | | | comment | varchar(200) | YES | | NULL | | | homedir | varchar(60) | YES | | NULL | | | shell | varchar(30) | YES | | NULL | | +----------+--------------+------+-----+---------+-------+ 7 rows in set (0.00 sec)
mysql>
字段约束的使用？
建表时给表头设置默认和不允许赋null值
mysql> create table db1.t31(name char(10) not null , class char(7) default "nsd2107",
-> likes set("money","game","film","music") not null default "film,music");
Query OK, 0 rows affected (0.33 sec)
mysql> desc db1.t31;
+-------+------------------------------------+------+-----+------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------------------------+------+-----+------------+-------+
| name | char(10) | NO | | NULL | |
| class | char(7) | YES | | nsd2107 | |
| likes | set('money','game','film','music') | NO | | film,music | |
+-------+------------------------------------+------+-----+------------+-------+
3 rows in set (0.00 sec)
mysql>验证默认值和不允许为null
mysql> insert into db1.t31 values (null, null , null);
ERROR 1048 (23000): Column 'name' cannot be null 表头name不允许赋null值 报错
mysql> insert into db1.t31 values ("bob", null , null);
ERROR 1048 (23000): Column 'likes' cannot be null 表头likes不允许赋null值 报错
mysql> insert into db1.t31 values ("bob",null,"money,game,film");符合约束不报错
Query OK, 1 row affected (0.06 sec)
mysql> insert into db1.t31(name) values("jim"); 不赋值的表头使用默认值赋值
mysql> insert into db1.t31 values ("lucy","nsd2108","game,film"); 根据需要自定义字段的值
mysql> select * from db1.t31;
------+---------+-----------------+
| name | class | likes |
+------+---------+-----------------+
| bob | NULL | money,game,film |
| jim | nsd2107 | film,music |
| lucy | nsd2108 | game,film |
+------+---------+-----------------+
3 rows in set (0.01 sec)查看表结构时 key 列 包括 普通索引 、唯一索引 、主键 ，当不设置的时候key是空白的 ，
如果设置的话会有对应的标志
mysql> desc db1.t3; （表头的key 是空白的 ）
唯一索引 （unique） 约束的方式：表头的值唯一(表头的值不能重复) 但可以赋null 值
mysql> create database if not exists DB1;
姓名 身份证号
create table DB1.t43 (name char(10) , hz_id char(18) unique );
mysql> desc DB1.t43 ;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name | char(10) | YES | | NULL | |
| hz_id | char(18) | YES | UNI | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> insert into DB1.t43 values("bob",null); 赋null值 可以
mysql> insert into DB1.t43 values("tom","666888");
mysql> insert into DB1.t43 values("jim","666888");
ERROR 1062 (23000): Duplicate entry '666888' for key 'hz_id'值重复 报错
mysql> insert into DB1.t43 values("jim","766888"); 不重复 可以
mysql> select * from DB1.t43;
+------+--------+
| name | hz_id |
+------+--------+
| bob | NULL |
| tom | 666888 |
| jim | 766888 |
+------+--------+
3 rows in set (0.00 sec)
mysql>*********主键********
主键使用规则
1、 字段值不允许重复，且不允许赋NULL值
2、一个表中只能有一个primary key字段
3、多个字段都作为主键，称为复合主键，必须一起创建
4、主键字段的标志是PRI
5、主键通常与auto_increment 连用
6、通常把表中唯一标识记录的字段设置为主键[记录编号字段]
建表时，创建主键 （2种语法格式 要记住）
语法格式1
create table 库.表（ 字段名 类型 primary key , 字段名 类型 , ..... ）;
mysql> create table db1.t35(name char(10) , hz_id char(10) primary key , class char(10) ); mysql> desc db1.t35; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | name | char(10) | YES | | NULL | | 姓名 | hz_id | char(10) | NO | PRI | NULL | | 身份证号 | class | char(10) | YES | | NULL | | 班级 +-------+----------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
语法格式2
create table 库.表（ 字段名 类型 , 字段名 类型 , primary key（字段名） ）;
mysql> create table db1.t36(name char(10) , hz_id char(10) , class char(10),primary key(hz_id) ); mysql> desc db1.t36; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | name | char(10) | YES | | NULL | | | hz_id | char(10) | NO | PRI | NULL | | | class | char(10) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
删除主键命令格式（要记住）向表头下存储数据不受主键的限制
mysql> alter table 库.表 drop primary key ;
例子2
mysql> alter table db1.t36 drop primary key ; mysql> desc db1.t36; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | name | char(10) | YES | | NULL | | | hz_id | char(10) | NO | | NULL | | | class | char(10) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql>
添加主键命令格式（要记住）
mysql> alter table 库.表 add primary key(表头名);
mysql> alter table db1.t36 add primary key(hz_id); mysql> desc db1.t36; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | name | char(10) | YES | | NULL | | | hz_id | char(10) | NO | PRI | NULL | | | class | char(10) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
使用t35 验证主键的约束
mysql> desc db1.t35; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | name | char(10) | YES | | NULL | | | hz_id | char(10) | NO | PRI | NULL | | | class | char(10) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
mysql> insert into db1.t35 values ("bob","888","nsd2107");
Query OK, 1 row affected (0.05 sec)
mysql> insert into db1.t35 values ("john",null,"nsd2107"); 空不可以
ERROR 1048 (23000): Column 'hz_id' cannot be null
mysql>
mysql> insert into db1.t35 values ("john","888","nsd2107"); 与第1条重复不可以
ERROR 1062 (23000): Duplicate entry '888' for key 'PRIMARY'
mysql>
mysql> insert into db1.t35 values ("john","988","nsd2107"); 不重复也不是null 可以
Query OK, 1 row affected (0.07 sec)
mysql> select * from db1.t35;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
主键与auto_increment连用:
当给字段设置了auto_increment属性后，插入记录时，如果不给字段赋值
字段会通过自加1的计算结果赋值
要想让字段有自增长 那么字段必须有主键的设置才可以
查看表结构时 在 Extra （额外设置） 位置显示
建表时 创建有auto_increment 属性的表头。
实现的效果如下
行号 姓名 班级 住址
1bob nsd2107 bj
2bob nsd2107 bj
3 bob nsd2107 bj
create table db1.t38 (行号 int primary key auto_increment , 姓名 char(10) , 班级 char(7) , 住址 char(10));
或
create table db1.t39 (行号 int auto_increment , 姓名 char(10) , 班级 char(7) , 住址 char(10),primary key(行号));
mysql> desc db1.t38;
+--------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+----------------+
| 行号 | int(11) | NO | PRI | NULL | auto_increment |
| 姓名 | char(10) | YES | | NULL | |
| 班级 | char(7) | YES | | NULL | |
| 住址 | char(10) | YES | | NULL | |
+--------+----------+------+-----+---------+----------------+
mysql> desc db1.t39;
+--------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+----------------+
| 行号 | int(11) | NO | PRI | NULL | auto_increment |
| 姓名 | char(10) | YES | | NULL | |
| 班级 | char(7) | YES | | NULL | |
| 住址 | char(10) | YES | | NULL | |
+--------+----------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
mysql> insert into db1.t39(姓名,班级,住址)values("bob","nsd2107","bj");
mysql> insert into db1.t39(姓名,班级,住址)values("bob","nsd2107","bj");
mysql> insert into db1.t39(姓名,班级,住址)values("tom","nsd2107","bj");
mysql> select * from db1.t39;
+--------+--------+---------+--------+
| 行号 | 姓名 | 班级 | 住址 |
+--------+--------+---------+--------+
| 1 | bob | nsd2107 | bj |
| 2 | bob | nsd2107 | bj |
| 3 | bob | nsd2107 | bj |mysql> insert into db1.t39(行号,姓名,班级,住址)values(5,"lucy","nsd2107","bj"); 也可以自定义 自增长字段的值
Query OK, 1 row affected (0.26 sec)
mysql> insert into db1.t39(姓名,班级,住址)values("lucy","nsd2107","bj"); 从新字段值开始+1
Query OK, 1 row affected (0.03 sec)
mysql> insert into db1.t39(姓名,班级,住址)values("lucy","nsd2107","bj"); 从新字段值开始+1
Query OK, 1 row affected (0.03 sec)
mysql> delete from db1.t39 ； 删除所有行
再添加行 继续行号 而不是从 1 开始
mysql> insert into db1.t39(姓名,班级,住址)values("lucy","nsd2107","bj");
mysql> insert into db1.t39(姓名,班级,住址)values("lucy","nsd2107","bj");
mysql> insert into db1.t39(姓名,班级,住址)values("lucy","nsd2107","bj");
mysql> select * from db1.t39;
+--------+--------+---------+--------+
| 行号 | 姓名 | 班级 | 住址 |
+--------+--------+---------+--------+
| 8 | lucy | nsd2107 | bj |
| 9 | lucy | nsd2107 | bj |
| 10 | lucy | nsd2107 | bj |
+--------+--------+---------+--------+
3 rows in set (0.01 sec)
mysql> truncate table db1.t39; # 删除行 再添加行 从1开始
Query OK, 0 rows affected (2.66 sec)
mysql> select * from db1.t39;
Empty set (0.01 sec)
mysql> insert into db1.t39(姓名,班级,住址)values("lucy","nsd2107","bj");
Query OK, 1 row affected (0.04 sec)
mysql> select * from db1.t39;
+--------+--------+---------+--------+
| 行号 | 姓名 | 班级 | 住址 |
+--------+--------+---------+--------+
| 1 | lucy | nsd2107 | bj |
+--------+--------+---------+--------+
1 row in set (0.00 sec)
mysql> insert into db1.t39(姓名,班级,住址)values("lucy","nsd2107","bj");
Query OK, 1 row affected (0.30 sec)
mysql> select * from db1.t39;
+--------+--------+---------+--------+
| 行号 | 姓名 | 班级 | 住址 |
+--------+--------+---------+--------+
| 1 | lucy | nsd2107 | bj |
| 2 | lucy | nsd2107 | bj |
+--------+--------+---------+--------+
2 rows in set (0.01 sec)
mysql>给已有表添加行号字段（通常把表中唯一标识记录的字段作为主键 就是行号字段）
mysql> select * from db1.t3; 没加行号前查看
给db1库下t3表 每行 添加 行号 （第1行号 就是1 第2行行号就2 依此类推）
mysql> alter table db1.t3 add id int primary key auto_increment first; mysql> select * from db1.t3; #添加后查看 mysql> select * from db1.t3 where id <= 3; #查找用户使用行号做查询条件 可以快速查找到指定的行
复合主键 表中的多个表头一起做主键
复合主键的约束方式: 多条记录 主键字段的值不允许同时相同
例子如下
create table db1.t39(cip varchar(15) , port smallint ,
status enum("deny","allow") , primary key(cip,port)
);
insert into db1.t39 values ("1.1.1.1",22,"deny");
insert into db1.t39 values ("1.1.1.1",22,"deny"); 同时相同报错
insert into db1.t39 values ("1.1.1.1",80,"deny"); 可以了~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
外键的核心思想：保证数据的一致性
插入记录时，字段值在另一个表字段值范围内选择。
外键的使用规则：
表存储引擎必须是innodb （在进阶课程里讲 现在仅需要知道如何指定表使用innodb存储引擎）
字段类型要一致
被参照字段必须要是索引类型的一种(通常是 primary key)
创建外键命令格式
create table 库.表(表头列表 ,
foreign key(表头名) #指定外键
references 库.表(表头名) #指定参考的表头名
on update cascade #同步更新
on delete cascade #同步删除
)engine=innodb;
提个需求： 只给公司里已经入职的员工发工资
首先创建存储员工信息的员工表
员工表 yg
员工编号 姓名
yg_id name
1 bob
2 bob
#创建员工表 create table db1.yg (yg_id int primary key auto_increment , name char(16) ) engine=innodb; #查看表的存储引擎 show create table db1.yg \G #没有表记录 select * from db1.yg;
在创建工资表
工资表 gz
员工编号 工资
gz_id pay
#创建工资表 mysql> create table db1.gz( -> gz_id int , pay float(7,2), -> foreign key(gz_id) references db1.yg(yg_id) -> on update cascade on delete cascade -> )engine=innodb ; Query OK, 0 rows affected (0.22 sec) mysql> desc db1.gz; 通过查看表结构查看不到外键的设置 MUL 是 普通索引的标志 +-------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+-------+ | gz_id | int(11) | YES | MUL | NULL | | | pay | float(7,2) | YES | | NULL | | +-------+------------+------+-----+---------+-------+ 2 rows in set (0.01 sec) #通过显示建表命令查看表的外键 ，并获取外键名称 mysql> show create table db1.gz \G mysql> show create table db1.gz \G *************************** 1. row *************************** Table: gz Create Table: CREATE TABLE `gz` ( `gz_id` int(11) DEFAULT NULL, `pay` float(7,2) DEFAULT NULL, KEY `gz_id` (`gz_id`), CONSTRAINT `gz_ibfk_1` FOREIGN KEY (`gz_id`) REFERENCES `yg` (`yg_id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) #删除外键 (通过外键名称 删除表头的外键设置) mysql> alter table 库.表 drop FOREIGN KEY 外键名; mysql> alter table db1.gz drop FOREIGN KEY gz_ibfk_1; mysql> show create table db1.gz \G *************************** 1. row *************************** Table: gz Create Table: CREATE TABLE `gz` ( `gz_id` int(11) DEFAULT NULL, `pay` float(7,2) DEFAULT NULL, KEY `gz_id` (`gz_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql> #在已有表里添加外键 mysql> alter table 库.表 add foreign key(表头名) references 库.表(表头名) on update cascade on delete cascade; mysql> alter table db1.gz add foreign key(gz_id) references db1.yg(yg_id) on update cascade on delete cascade ; mysql> show create table db1.gz \G *************************** 1. row *************************** Table: gz Create Table: CREATE TABLE `gz` ( `gz_id` int(11) DEFAULT NULL, `pay` float(7,2) DEFAULT NULL, KEY `gz_id` (`gz_id`), CONSTRAINT `gz_ibfk_1` FOREIGN KEY (`gz_id`) REFERENCES `yg` (`yg_id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
外键验证：
1 外键字段的值必须在参考表字段值范围内
2 验证同步更新（ on update cascade)
3 验证同步删除（ on delete cascade) 练习到 17:18
外键验证：
1 外键字段的值必须在参考表字段值范围内
mysql> insert into db1.yg (name) values ("jerry"),("tom");
mysql> select * from db1.yg;
mysql> insert into db1.gz values(1,50000);
mysql> insert into db1.gz values(2,50000);
mysql> select * from db1.gz;
+-------+----------+
| gz_id | pay |
+-------+----------+
| 1 | 50000.00 |
| 2 | 50000.00 |
+-------+----------+
2 rows in set (0.00 sec)
mysql> insert into db1.gz values(3,50000); #因为没有编号是3的员工
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db1`.`gz`, CONSTRAINT `gz_ibfk_1` FOREIGN KEY (`gz_id`) REFERENCES `yg` (`yg_id`) ON DELETE CASCADE ON UPDATE CASCADE)
mysql>
mysql> insert into db1.yg (name) values ("Lucy")；
mysql> select * from db1.yg;
mysql> insert into db1.gz values(3,50000);有编号是3的员工可以发工资了
2 验证同步更新（ on update cascade)
mysql> select * from db1.yg;
+-------+-------+
| yg_id | name |
+-------+-------+
| 1 | jerry |
| 2 | tom |
| 3 | lucy |
+-------+-------+
3 rows in set (0.00 sec)
把yg表里编号是3的改成9
mysql> update db1.yg set yg_id=9 where yg_id=3;
mysql> select * from db1.yg;
+-------+-------+
| yg_id | name |
+-------+-------+
| 1 | jerry |
| 2 | tom |
| 9 | lucy |
+-------+-------+
3 rows in set (0.00 sec)工资表里编号是3的自动变成 9
mysql> select * from db1.gz; +-------+----------+ | gz_id | pay | +-------+----------+ | 1 | 50000.00 | | 2 | 50000.00 | | 9 | 50000.00 | +-------+----------+ 3 rows in set (0.00 sec) mysql>
3 验证同步删除（ on delete cascade)
mysql> select * from db1.yg; #删除前查看员工表记录 +-------+-------+ | yg_id | name | +-------+-------+ | 1 | jerry | | 2 | tom | | 9 | lucy | +-------+-------+ 3 rows in set (0.00 sec) mysql> delete from db1.yg where yg_id=2; 删除编号是2的员工 Query OK, 1 row affected (0.04 sec) mysql> select * from db1.yg; 删除后查看 +-------+-------+ | yg_id | name | +-------+-------+ | 1 | jerry | | 9 | lucy | +-------+-------+ 2 rows in set (0.00 sec) mysql> select * from db1.gz; 查看工资表也没有编号是2的工资了 +-------+----------+ | gz_id | pay | +-------+----------+ | 1 | 50000.00 | | 9 | 50000.00 | +-------+----------+ 2 rows in set (0.00 sec) mysql>
#被参考的表不能删除 mysql> drop table db1.yg; ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails mysql>
给gz表的gz_id表头 加主键标签
保证每个员工只能发1遍工资 且有员工编号的员工才能发工资
mysql> alter table db2.gz add primary key(gz_id); # 如果重复发工资和没有编号的发工资了要delete form db1.gz; mysql> insert into db1.gz values (1,53000); 报错 mysql> insert into db1.gz values (9,58000); 报错 mysql> insert into db1.gz values (NULL,80000); 报错
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
二、mysql索引
大白话介绍索引：（什么是索引） 给表头加了索引标签之后，会对表头下的数据生成排队信息保存在表对应的文件里(表名.ibd)
比如给db1库下t3表的表头加了索引
对应的存储文件是/var/lib/mysql/db1/t3.ibd
官方对索引的介绍
是帮助MySQL高效获取数据的数据结构。
为快速查找数据而排好序的一种数据结构。
类似书的目录 。
可以用来快速查询表中的特定记录，所有的数据类型都可以被索引。
Mysql索引主要有三种结构：Btree、B+Tree 、Hash 。
索引优点具体如下：
可以大大提高MySQL的检索速度
索引大大减小了服务器需要扫描的数据量
索引可以帮助服务器避免排序和临时表
索引可以将随机IO变成顺序IO
索引的缺点：
虽然索引大大提高了查询速度，同时却会降低更新表的速度，
如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存索引文件。
建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。
如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。
对于非常小的表，大部分情况下简单的全表扫描更高效。
索引的分类：
普通索引 (index)
不应用任何限制条件的索引，该索引可以在任何数据类型中创建。
字段本身的约束条件可以判断其值是否为空或唯一。
创建该类型索引后，用户在查询时，便可以通过索引进行查询。
唯一索引 (unique)
使用UNIQUE参数可以设置唯一索引。
创建该索引时，索引的值必须唯一。
通过唯一索引，用户可以快速定位某条记录。
主键是一种特殊唯一索引。
全文索引 (FULLTEXT)
使用FULLTEXT参数可以设置索引为全文索引。
全文索引只能创建在CHAR、VARCHAR或者TEXT类型的字段上。
查询数据量较大的字符串类型的字段时，使用全文索引可以提高查询速度。
在默认情况下，应用全文搜索大小写不敏感。如果索引的列使用二进制排序后，
可以执行大小写敏感的全文索引。
单列索引
顾名思义，单列索引即只对应一个字段的索引。
应用该索引的条件只需要保证该索引值对应一个字段即可。
可以包括普通、唯一、全文索引。
多列索引
多列索引是在表的多个字段上创建一个索引。
该索引指向创建时对应的多个字段，用户可以通过这几个字段进行查询。
要想应用该索引，用户必须使用这些字段中的第一个字段。
2.4 普通索引(index)的管理
普通索引(index)的使用规则（需要牢记）具体如下：
一个表中可以有多个index
字段的值可以重复，且可以赋值为null
通常在where条件中的字段上配置Index
index索引字段的标志为mul
创建普通索引(index)
1)建表时创建索引命令格式
CREATE TABLE 库.表(
字段列表 ,
INDEX(字段名) ,
INDEX(字段名) ,
);
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)
);查看索引
1）查看表头是否有索引 des 库.表；
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 | |
+--------+--------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> system ls /var/lib/mysql/home/tea4.ibd 保存排队信息的文件
/var/lib/mysql/home/tea4.ibd
mysql>2）查看索引详细信息 show index from 库.表；
show index from home.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 #排队算法 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: 2 rows in set (0.00 sec) mysql>
删除索引 的命令格式 DROP INDEX 索引名 ON 库.表；
mysql> drop index id on home.tea4 ;
mysql> desc home.tea4;
+--------+--------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------------+------+-----+---------+-------+
| id | char(6) | NO | | NULL | |
| name | varchar(6) | NO | MUL | NULL | |
| age | int(3) | NO | | NULL | |
| gender | enum('boy','girl') | YES | | boy | |
+--------+--------------------+------+-----+---------+-------+
4 rows in set (0.14 sec)
mysql> show index from home.tea4 \G
*************************** 1. 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:
1 row in set (0.00 sec)
mysql>2)在已有表添加索引命令格式 CREATE INDEX 索引名 ON 库.表(字段名)；
mysql> create index nianling on home.tea4(age);
mysql> desc home.tea4;
+--------+--------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------------+------+-----+---------+-------+
| id | char(6) | NO | | NULL | |
| name | varchar(6) | NO | MUL | NULL | |
| age | int(3) | NO | MUL | NULL | |
| gender | enum('boy','girl') | YES | | boy | |
+--------+--------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> show index from home.tea4 \G
*************************** 1. 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:
*************************** 2. row ***************************
Table: tea4
Non_unique: 1
Key_name: nianling 设置的索引名
Seq_in_index: 1
Column_name: age 表头名
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
2 rows in set (0.00 sec)
mysql>explain命令 : 可以查看执行的查询select语句， 是否使用到索引做查询了
mysql> desc db1.t3; 所有表头都没有MUL 标记 +----------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | char(50) | YES | | NULL | | | password | char(1) | YES | | NULL | | | uid | int(11) | YES | | NULL | | | gid | int(11) | YES | | NULL | | | comment | varchar(200) | YES | | NULL | | | homedir | varchar(60) | YES | | NULL | | | shell | varchar(30) | YES | | NULL | | +----------+--------------+------+-----+---------+----------------+ 8 rows in set (0.00 sec) mysql> select count(*) from db1.t3; 查看表的总行数 +----------+ | count(*) | +----------+ | 22 | +----------+ 1 row in set (0.00 sec) mysql> select * from db1.t3 where name="sshd"; +----+------+----------+------+------+-------------------------+-----------------+---------------+ | id | name | password | uid | gid | comment | homedir | shell | +----+------+----------+------+------+-------------------------+-----------------+---------------+ | 18 | sshd | x | 74 | 74 | Privilege-separated SSH | /var/empty/sshd | /sbin/nologin | +----+------+----------+------+------+-------------------------+-----------------+---------------+ 1 row in set (0.00 sec) mysql> mysql> mysql> explain select * from db1.t3 where name="sshd"; 使用没有索引的表头做查询条件 找1条记录也要遍历整张表的所有行（key 、 rows） +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t3 | NULL | ALL | NULL | NULL | NULL | NULL | 22 | 10.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> create index xingming on db1.t3(name) ; 给db1库下的t3表的name表头下的数据创建排队信息 索引名叫xingming mysql> desc db1.t3; +----------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | char(50) | YES | MUL | NULL | | | password | char(1) | YES | | NULL | | | uid | int(11) | YES | | NULL | | | gid | int(11) | YES | | NULL | | | comment | varchar(200) | YES | | NULL | | | homedir | varchar(60) | YES | | NULL | | | shell | varchar(30) | YES | | NULL | | +----------+--------------+------+-----+---------+----------------+ 8 rows in set (0.00 sec) mysql> explain select * from db1.t3 where name="sshd"; 不会遍历所有行 (key 、 rows) +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+ | 1 | SIMPLE | t3 | NULL | ref | xingming | xingming | 51 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) mysql> select * from db1.t3 where name="sshd"; +----+------+----------+------+------+-------------------------+-----------------+---------------+ | id | name | password | uid | gid | comment | homedir | shell | +----+------+----------+------+------+-------------------------+-----------------+---------------+ | 18 | sshd | x | 74 | 74 | Privilege-separated SSH | /var/empty/sshd | /sbin/nologin | +----+------+----------+------+------+-------------------------+-----------------+---------------+ 1 row in set (0.00 sec) mysql>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
mysql用户管理:
1 用户授权
1.1 什么是用户授权： 数据库管理员root用户连接数据库服务后，添加普通用户、设置用户权限和 用户密码
1.2 为什么要学习用户授权： 默认情况，只允许数据管理员root 在本机访问数据服务。
默认不允许其他客户端访问服务 也不能使用其他用户在本机连接数据库服务
1.3 用户授权命令格式
GRANT 权限列表 ON 库名 TO 用户名@"客户端地址" IDENTIFIED BY “密码” WITH GRANT OPTION;
权限列表 ： 就是添加的用户对指定的库名具有的访问权限表示方式(就是学过的sql命令)
ALL 表示所有权限 （表示所有命令）
USAGE 表示无权限 （除了查看之外啥权限都没有 ， 看的命令show desc ）
SELECT,UPDATE,INSERT 表示只有个别权限
SELECT, UPDATE (字段1,字段N) 表示权限仅对指定字段有访问权限
库名 ：添加的使用户对服务器上的那些库有访问权限 可以表示的方式有：
*.* 表示 所有库所有表 第1个表示所有库名 第2个 表示所有表名
库名.* 表示一个库下的所有表 例如 tarena.*
库名.表名 表是一张表 例如 tarena.user
用户名： 添加用户时自定义即可， 存储在mysql库下user 表的user字段下
客户端地址： 表示网络中的那些主机可以使用添加的用户连接数据库服务表示的方式有：
% 表示网络中的所有主机
192.168.4.% 表示 192.168.4网段内的所有主机
192.168.4.1 表示仅仅是192.168.4.1 一台主机
localhost 表示数据库服务器本机
密码 ： 添加的用户连接数据服务时使用的密码 ，要符合数据库服务的密码策略
WITH GRANT OPTION ： 让添加的用户也可以使用grant命令再添加用户，但用户本身要对mysql库有insert的权限
2 权限撤销 ： 删除已有授权用户的权限 命令格式如下
REVOKE 权限列表 ON 库名 FROM 用户名@"客户端地址";
注意 : 库名的表示方式 要和 用户授权时的表示方式一样
3 删除添加的用户
drop user 用户名@"客户端地址";
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~练习如下:(host50 做数据库服务器 host51 做客户端验证用户授权)
诉求：
允许网络中的所有主机都可以使用数据服务器host50的数据管理员root用户连接 50 数据库服务 连接密码123qqq...A 对所有库表有完全权限 且有授权权限
可以使用admin用户在host50主机，连接本机的数据库服务
连接密码是123qqq...A 仅对 服务器上的所有表有查询权限。
[root@host50 ~]# mysql -hlocalhost -uroot -p654321
mysql> grant all on *.* to root@"%" identified by "123qqq...A" with grant option; mysql> grant select on *.* to admin@"localhost" identified by "123qqq...A";
查看用户
select user , host from mysql.user; mysql> select user , host from mysql.user; +-----------+-----------+ | user | host | +-----------+-----------+ | root | % | | admin | localhost | | mysql.sys | localhost | | root | localhost | +-----------+-----------+ 4 rows in set (0.00 sec)
查看一样用户的访问权限
mysql> show grants for root@"%"; +-------------------------------------------------------------+ | Grants for root@% | +-------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION | +-------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> show grants for admin@"localhost" ; +--------------------------------------------+ | Grants for admin@localhost | +--------------------------------------------+ | GRANT SELECT ON *.* TO 'admin'@'localhost' | +--------------------------------------------+ 1 row in set (0.00 sec)
给已有追加权限（库名 用户名 客户端地址都不变就是追加权限）
给admin用户追加插入记录的权限
mysql> grant insert on *.* to admin@"localhost" ; mysql> show grants for admin@"localhost" ; 再次查看 +----------------------------------------------------+ | Grants for admin@localhost | +----------------------------------------------------+ | GRANT SELECT, INSERT ON *.* TO 'admin'@'localhost' | +----------------------------------------------------+ 1 row in set (0.00 sec) mysql>
修改已有授权用户的连接密码（修改的密码也要与当前数据库服务的密码策略匹配 ，修改的密码要使用password() 函数加密 ， 密码存储在 mysql.user表的authentication_string字段下,存储的是加密后的密码）
mysql> set password for 用户名@"客户端地址"=password("新密码");
mysql> set password for admin@"localhost"=password("123abc");
mysql> select user , host,authentication_string from mysql.user;在客户端host51 使用host50 主机添加的用户连接数据库服务器host50
命令格式 mysql -h192.168.4.50 -u用户名 -p密码
在51 主机 使用50 授权的root 用户连接 50 数据库服务器
[root@host51 ~]# mysql -h192.168.4.50 -uroot -p123qqq...A
mysql> select @@hostname; #查看连接服务器的主机名 +------------+ | @@hostname | +------------+ | host50 | +------------+ 1 row in set (0.01 sec)
mysql> select user(); #查看连接用户名和客户端地址 +-------------------+ | user() | +-------------------+ | root@192.168.4.51 | +-------------------+ 1 row in set (0.00 sec)
mysql> show grants; #显示访问权限 +-------------------------------------------------------------+ | Grants for root@% | +-------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION | +-------------------------------------------------------------+ 1 row in set (0.00 sec)
mysql> 测试用户权限 （因为拥有的是all 的权限 所有什么命令都可以执行 ，可以把没有用的库全删除。）
在50 主机撤销网络中所有主机使用root连接的权限
#只撤销 with grant option的权限。
mysql> revoke grant option on *.* from root@"%";
#只撤销用户删除记录的权限
mysql> revoke delete on *.* from root@"%";
#查看当前已有的权限
mysql> show grants for root@"%"
#删除用户当前所有的权限
mysql> revoke all on *.* from root@"%";
#查看当前已有的权限
mysql> show grants for root@"%";
删除用户 drop user 用户名@"客户端地址";
例子： drop user root@"%";
例子： drop user admin@"localhost";
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~与用户相关的命令
[root@host51 ~]# mysql -uroot -pNSD2107...a host51 #管理员登录 mysql> grant select on *.* to plj@"localhost" identified by "123qqq...A"; #授权用户 plj mysql> exit; #断开连接
[root@host51 ~]# mysql -uplj -p123qqq...A #使用plj 用户登录
mysql> set password=password("新密码"); #用户登陆后修改自己的连接密码 (要复合密码策略要求)
mysql> select password("abc123...A"); #加密函数 password（） 命令演示
+-------------------------------------------+
| password("abc123...A") |
+-------------------------------------------+
| *482907C0B13E321A83A84C2FBB881C5BD4377076 |
+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> set password=password("abc123...A"); #plj用户修自己的连接密码
Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> exit; #断开连接 Bye
[root@host51 ~]# mysql -uplj -p123qqq...A #旧密码登陆报错 [root@host51 ~]# mysql -uplj -pabc123...A #新密码登陆成功
数据库管理员可以重置授权用户的登陆密码
[root@host51 ~]# mysql -uroot -pNSD2107...a #管理员root 用户登录
mysql> SET PASSWORD FOR 用户名@"客户端地址"=PASSWORD("密码")；
mysql> set password for plj@"localhost"=password("123qqq...A"); #重置 plj用户连接密码Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> exit
[root@host51 ~]# mysql -uplj -p123qqq...A #使用修改后的密码登陆
给添加的用户改名
[root@host51 ~]# mysql -uroot -pNSD2107...a
mysql> grant select on *.* to yaya@"%" identified by "123qqq...A"; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> select user , host from mysql.user; +-----------+-----------+ | user | host | +-----------+-----------+ | yaya | % | | mysql.sys | localhost | | plj | localhost | | root | localhost | +-----------+-----------+
mysql> rename user yaya to jingyaya ; #使用命令修改 但客户端地址必须是 % Query OK, 0 rows affected (0.01 sec)
mysql> select user , host from mysql.user; +-----------+-----------+ | user | host | +-----------+-----------+ | jingyaya | % | | mysql.sys | localhost | | plj | localhost | | root | localhost | +-----------+-----------+ 4 rows in set (0.00 sec) mysql> # 通过修改表记录改用户名 mysql> update mysql.user set user="panglijing" where user="plj" ; mysql> flush privileges; mysql> select user , host from mysql.user; +------------+-----------+ | user | host | +------------+-----------+ | jingyaya | % | | mysql.sys | localhost | | panglijing | localhost | | root | localhost | +------------+-----------+ 4 rows in set (0.00 sec) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
用户权限的追加 ： 在一样权限的基础上添加新权限。
[root@host51 ~]# mysql -uroot -pNSD2107...a
mysql> grant select on *.* to bob@"%" identified by "123qqq...A"; mysql> show grants for bob@"%"; +----------------------------------+ | Grants for bob@% | +----------------------------------+ | GRANT SELECT ON *.* TO 'bob'@'%' | +----------------------------------+ mysql> grant insert on *.* to bob@"%"; mysql> show grants for bob@"%"; +------------------------------------------+ | Grants for bob@% | +------------------------------------------+ | GRANT SELECT, INSERT ON *.* TO 'bob'@'%' | +------------------------------------------+ 1 row in set (0.00 sec)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 授权库mysql库的使用：（ 保存grant命令的执行结果。）
使用到了4张表 分别存储不同的授权信息
mysql> use mysql; mysql> show tables;
user表 #记录已有的授权用户及权限 （全局权限 权限all 库名 *.*）
例如: grant all on *.* to pljadmin@"%" identified by "123qqq...A" with grant option；
db表 #记录已有授权用户对数据库的访问权限
例如: grant all on gamedb.* to adminONE@"%" identified by "123qqq...A" ；
tables_priv表 #记录已有授权用户对表的访问权限
create database bbsdb; create table bbsdb.t1(name char(10) , age int); create table bbsdb.t2(name char(10) , id int);
例如：grant select,insert on bbsdb.t1 to adminTWO@"%" identified by "123qqq...A";
columns_priv表 #记录已有授权用户对字段的访问权限
例如：grant select,update(name) on bbsdb.t2 to adminthr@"%" identified by "123qqq...A";
可以通过查看表记录获取已有授权用户及访问权限 ；也可以修改表记录 修改授权用户的访问权限 。
user表的使用
查看当前数据库服务已有的用户
select host,user,authentication_string from mysql.user;
user字段存储用户名
host字段存储客户端地址
authentication_string 字段存储连接密码(加密的密码)
mysql> select host,user from mysql.user; #查看表记录 mysql> select * from mysql.user where user="pljadmin" \G #查看所有列 *************************** 1. row *************************** Host: % User: pljadmin Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Reload_priv: Y Shutdown_priv: Y Process_priv: Y File_priv: Y Grant_priv: Y References_priv: Y Index_priv: Y Alter_priv: Y Show_db_priv: Y Super_priv: Y Create_tmp_table_priv: Y Lock_tables_priv: Y Execute_priv: Y Repl_slave_priv: Y Repl_client_priv: Y Create_view_priv: Y Show_view_priv: Y Create_routine_priv: Y Alter_routine_priv: Y Create_user_priv: Y Event_priv: Y Trigger_priv: Y Create_tablespace_priv: Y ssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0 max_updates: 0 max_connections: 0 max_user_connections: 0 plugin: mysql_native_password authentication_string: *F19C699342FA5C91EBCF8E0182FB71470EB2AF30 password_expired: N password_last_changed: 2021-11-09 16:31:07 password_lifetime: NULL account_locked: N 1 row in set (0.00 sec) mysql>
mysql> update mysql.user set Grant_priv="N" where user="pljadmin"; #修改表记录 mysql> flush privileges; #是修改生效 mysql> show grants for pljadmin@"%"; #查看用户权限 +-----------------------------------------------+ | Grants for pljadmin@% | +-----------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'pljadmin'@'%' | +-----------------------------------------------+ 1 row in set (0.00 sec)
mysql> select * from mysql.user where user="pljadmin" \G #查看表记录 *************************** 1. row *************************** Host: % User: pljadmin Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Reload_priv: Y Shutdown_priv: Y Process_priv: Y File_priv: Y Grant_priv: N References_priv: Y Index_priv: Y Alter_priv: Y Show_db_priv: Y Super_priv: Y Create_tmp_table_priv: Y Lock_tables_priv: Y Execute_priv: Y Repl_slave_priv: Y Repl_client_priv: Y Create_view_priv: Y Show_view_priv: Y Create_routine_priv: Y Alter_routine_priv: Y Create_user_priv: Y Event_priv: Y Trigger_priv: Y Create_tablespace_priv: Y ssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0 max_updates: 0 max_connections: 0 max_user_connections: 0 plugin: mysql_native_password authentication_string: *F19C699342FA5C91EBCF8E0182FB71470EB2AF30 password_expired: N password_last_changed: 2021-11-09 16:31:07 password_lifetime: NULL account_locked: N 1 row in set (0.00 sec) mysql>
db表的使用
mysql> select host,user,db from mysql.db; #查看表记录 +-----------+-----------+--------+ | host | user | db | +-----------+-----------+--------+ | % | adminONE | gamedb | | localhost | mysql.sys | sys | +-----------+-----------+--------+ 2 rows in set (0.00 sec)
mysql> select * from mysql.db where db="gamedb" \G #查看表记录 *************************** 1. row *************************** Host: % Db: gamedb User: adminONE Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Grant_priv: N References_priv: Y Index_priv: Y Alter_priv: Y Create_tmp_table_priv: Y Lock_tables_priv: Y Create_view_priv: Y Show_view_priv: Y Create_routine_priv: Y Alter_routine_priv: Y Execute_priv: Y Event_priv: Y Trigger_priv: Y 1 row in set (0.00 sec)
mysql> update mysql.db set delete_priv="N",update_priv="N" where user="adminONE"; #修改满足条件字段的值 mysql> flush privileges; mysql> select * from mysql.db where db="gamedb" \G #查看表记录 *************************** 1. row *************************** Host: % Db: gamedb User: adminONE Select_priv: Y Insert_priv: Y Update_priv: N Delete_priv: N
tables_priv表的使用
mysql> select * from mysql.tables_priv;#查看表记录 +-----------+-------+-----------+------------+----------------+---------------------+---------------+-------------+ | Host | Db | User | Table_name | Grantor | Timestamp | Table_priv | Column_priv | +-----------+-------+-----------+------------+----------------+---------------------+---------------+-------------+ | localhost | sys | mysql.sys | sys_config | root@localhost | 2021-11-03 10:46:17 | Select | | | % | bbsdb | adminTWO | t1 | root@localhost | 0000-00-00 00:00:00 | Select,Insert | | +-----------+-------+-----------+------------+----------------+---------------------+---------------+-------------+ 2 rows in set (0.00 sec) mysql>
columns_priv表使用
mysql> select * from mysql.columns_priv; #查看表记录 +------+-------+----------+------------+-------------+---------------------+-------------+ | Host | Db | User | Table_name | Column_name | Timestamp | Column_priv | +------+-------+----------+------------+-------------+---------------------+-------------+ | % | bbsdb | adminthr | t2 | name | 0000-00-00 00:00:00 | Update | +------+-------+----------+------------+-------------+---------------------+-------------+ 1 row in set (0.00 sec) mysql>
