赞
踩
完成如下练习:
主键使用规则:
实现此案例需要按照如下步骤进行。
步骤一:练习主键的创建、查看、删除、添加、验证主键
- //语法格式1
- create table 库.表( 表头名 数据类型 primary key , 表头名 数据类型 , ..... );
- //建表
- mysql> create table db1.t35(
- -> name char(10) ,
- -> hz_id char(10) primary key ,
- -> class char(10)
- -> );
- Query OK, 0 rows affected (0.49 sec)
- //查看表头
- 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)
- -> );
- Query OK, 0 rows affected (0.39 sec)
- //查看表头
- 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 ;
- //例子
- mysql> alter table db1.t36 drop primary key ;
- Query OK, 0 rows affected (1.00 sec)
- Records: 0 Duplicates: 0 Warnings: 0
- //查看表头
- 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)
- //插入第1条记录 正常
- 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>
- //与第1条重复不可以
- mysql> insert into db1.t35 values ("john","888","nsd2107");
- ERROR 1062 (23000): Duplicate entry '888' for key 'PRIMARY'
- //不重复也不是null可以
- mysql> insert into db1.t35 values ("john","988","nsd2107");
- Query OK, 1 row affected (0.07 sec)
- //查看表记录
- mysql> select * from db1.t35 ;
- +------+-------+---------+
- | name | hz_id | class |
- +------+-------+---------+
- | bob | 888 | nsd2107 |
- | john | 988 | nsd2107 |
- +------+-------+---------+
- 2 rows in set (0.00 sec)
步骤二:练习复合主键的使用
- //创建复合主键 表头依次是客户端ip 、服务端口号、访问状态
- mysql> 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"); 可以
- insert into db1.t39 values ("2.1.1.1",80,"allow");可以
- //查看记录
- mysql> select * from db1.t39;
- +---------+------+--------+
- | cip | port | status |
- +---------+------+--------+
- | 1.1.1.1 | 22 | deny |
- | 1.1.1.1 | 80 | deny |
- | 2.1.1.1 | 80 | allow |
- +---------+------+--------+
- 3 rows in set (0.00 sec)
- //删除复合主键
- mysql> alter table db1.t39 drop primary key;
- Query OK, 3 rows affected (1.10 sec)
- Records: 3 Duplicates: 0 Warnings: 0
- //查看表头
- mysql> desc db1.t39;
- +--------+----------------------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +--------+----------------------+------+-----+---------+-------+
- | cip | varchar(15) | NO | | NULL | |
- | port | smallint | NO | | NULL | |
- | status | enum('deny','allow') | YES | | NULL | |
- +--------+----------------------+------+-----+---------+-------+
- 3 rows in set (0.00 sec)
- //没有复合主键约束后 ,插入记录不受限制了
- mysql> insert into db1.t39 values("2.1.1.1",80,"allow");
- Query OK, 1 row affected (0.06 sec)
- mysql> insert into db1.t39 values("2.1.1.1",80,"deny");
- Query OK, 1 row affected (0.08 sec)
- //查看表记录
- mysql> select * from db1.t39;
- +---------+------+--------+
- | cip | port | status |
- +---------+------+--------+
- | 1.1.1.1 | 22 | deny |
- | 1.1.1.1 | 80 | deny |
- | 2.1.1.1 | 80 | allow |
- | 2.1.1.1 | 80 | allow |
- | 2.1.1.1 | 80 | deny |
- +---------+------+--------+
- 5 rows in set (0.00 sec)
- //添加复合主键时 字段下的数据与主键约束冲突 不允许添加
- mysql> alter table db1.t39 add primary key(cip,port);
- ERROR 1062 (23000): Duplicate entry '2.1.1.1-80' for key 't39.PRIMARY'
- //删除重复的数据
- mysql> delete from db1.t39 where cip="2.1.1.1";
- Query OK, 3 rows affected (0.05 sec)
- mysql> select * from db1.t39;
- +---------+------+--------+
- | cip | port | status |
- +---------+------+--------+
- | 1.1.1.1 | 22 | deny |
- | 1.1.1.1 | 80 | deny |
- +---------+------+--------+
- 2 rows in set (0.00 sec)
- //添加复合主键
- mysql> alter table db1.t39 add primary key(cip,port);
- Query OK, 0 rows affected (0.67 sec)
- Records: 0 Duplicates: 0 Warnings: 0
- //查看表头
- mysql> desc db1.t39;
- +--------+----------------------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +--------+----------------------+------+-----+---------+-------+
- | cip | varchar(15) | NO | PRI | NULL | |
- | port | smallint | NO | PRI | NULL | |
- | status | enum('deny','allow') | YES | | NULL | |
- +--------+----------------------+------+-----+---------+-------+
- 3 rows in set (0.00 sec)
步骤三:练习与auto_increment连用的效果
表头设置了auto_increment属性后,
插入记录时,如果不给表头赋值表头通过自加1的计算结果赋值
要想让表头有自增长 表头必须有主键设置才可以
查看表结构时 在 Extra (额外设置) 位置显示
建表时 创建有auto_increment 属性的表头。实现的效果如下:
行号 姓名 班级 住址
1 bob nsd2107 bj
2 bob nsd2107 bj
3 bob nsd2107 bj
4 bob nsd2107 bj
1)建表
- mysql> create table db1.t38 (
- -> 行号 int primary key auto_increment ,
- -> 姓名 char(10) ,
- -> 班级 char(7) ,
- -> 住址 char(10)
- -> );
- Query OK, 0 rows affected (0.76 sec)
- //查看表头
- mysql> desc db1.t38 ;
- +--------+----------+------+-----+---------+----------------+
- | Field | Type | Null | Key | Default | Extra |
- +--------+----------+------+-----+---------+----------------+
- | 行号 | int | NO | PRI | NULL | auto_increment |
- | 姓名 | char(10) | YES | | NULL | |
- | 班级 | char(7) | YES | | NULL | |
- | 住址 | char(10) | YES | | NULL | |
- +--------+----------+------+-----+---------+----------------+
- 4 rows in set (0.00 sec)
- //插入表记录 不给自增长表头赋值
- mysql> insert into db1.t38(姓名,班级,住址)values("bob","nsd2107","bj");
- Query OK, 1 row affected (0.05 sec)
- mysql> insert into db1.t38(姓名,班级,住址)values("bob","nsd2107","bj");
- Query OK, 1 row affected (0.04 sec)
- mysql> insert into db1.t38(姓名,班级,住址)values("tom","nsd2107","bj");
- Query OK, 1 row affected (0.05 sec)
- //查看表记录
- mysql> select * from db1.t38;
- +--------+--------+---------+--------+
- | 行号 | 姓名 | 班级 | 住址 |
- +--------+--------+---------+--------+
- | 1 | bob | nsd2107 | bj |
- | 2 | bob | nsd2107 | bj |
- | 3 | tom | nsd2107 | bj |
- +--------+--------+---------+--------+
- 3 rows in set (0.00 sec)
自增长使用注意事项
- //给自增长字段的赋值
- mysql> insert into db1.t38(行号,姓名,班级,住址)values(5,"lucy","nsd2107","bj");
- Query OK, 1 row affected (0.26 sec)
- //不赋值后 用最后1条件记录表头的值+1结果赋值
- mysql> insert into db1.t38(姓名,班级,住址)values("lucy","nsd2107","bj");
- Query OK, 1 row affected (0.03 sec)
- //查看记录
- mysql> select * from db1.t38 ;
- +--------+--------+---------+--------+
- | 行号 | 姓名 | 班级 | 住址 |
- +--------+--------+---------+--------+
- | 1 | bob | nsd2107 | bj |
- | 2 | bob | nsd2107 | bj |
- | 3 | tom | nsd2107 | bj |
- | 5 | lucy | nsd2107 | bj |
- | 6 | lucy | nsd2107 | bj |
- +--------+--------+---------+--------+
- 5 rows in set (0.00 sec)
- //删除所有行
- mysql> delete from db1.t38 ;
- //再添加行 继续行号 而不是从 1 开始
- mysql> insert into db1.t38(姓名,班级,住址)values("lucy","nsd2107","bj");
- mysql> insert into db1.t38(姓名,班级,住址)values("lucy","nsd2107","bj");
- mysql> insert into db1.t38(姓名,班级,住址)values("lucy","nsd2107","bj");
- //查看记录
- mysql> select * from db1.t38;
- +--------+--------+---------+--------+
- | 行号 | 姓名 | 班级 | 住址 |
- +--------+--------+---------+--------+
- | 8 | lucy | nsd2107 | bj |
- | 9 | lucy | nsd2107 | bj |
- | 10 | lucy | nsd2107 | bj |
- +--------+--------+---------+--------+
- 3 rows in set (0.01 sec)
- //truncate删除行 再添加行 从1开始
- mysql> truncate table db1.t38;
- Query OK, 0 rows affected (2.66 sec)
- //插入记录
- mysql> insert into db1.t38(姓名,班级,住址)values("lucy","nsd2107","bj");
- Query OK, 1 row affected (0.04 sec)
- mysql> insert into db1.t38(姓名,班级,住址)values("lucy","nsd2107","bj");
- Query OK, 1 row affected (0.30 sec)
- //查看记录
- mysql> select * from db1.t38;
- +--------+--------+---------+--------+
- | 行号 | 姓名 | 班级 | 住址 |
- +--------+--------+---------+--------+
- | 1 | lucy | nsd2107 | bj |
- | 2 | lucy | nsd2107 | bj |
- +--------+--------+---------+--------+
- 2 rows in set (0.01 sec)
- mysql>
完成如下练习:
外键使用规则:
作用:
实现此案例需要按照如下步骤进行。
步骤一:练习外键的创建、查看、删除、添加
//创建外键命令
- create table 库.表(
- 表头列表 ,
- foreign key(表头名) #指定外键
- references 库.表(表头名) #指定参考的表头名
- on update cascade #同步更新
- on delete cascade #同步删除
- )engine=innodb;
需求: 仅给公司里已经入职的员工发工资
首先创建存储员工信息的员工表
表名 yg
员工编号 yg_id
姓名 name
- #创建员工表
- create table db1.yg (
- yg_id int primary key auto_increment ,
- name char(16)
- ) engine=innodb;
创建工资表
表名 gz
员工编号 gz_id
工资 pay
- #创建工资表 指定外键表头
- mysql> create table db1.gz(
- gz_id int , pay float,
- foreign key(gz_id) references db1.yg(yg_id)
- on update cascade on delete cascade
- )engine=innodb ;
- //查看工资表外键
- mysql> show create table db1.gz \G
- *************************** 1. row ***************************
- Table: gz
- Create Table: CREATE TABLE `gz` (
- `gz_id` int(11) DEFAULT NULL,
- `pay` float 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
- //删除外键
- 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 DEFAULT NULL,
- KEY `gz_id` (`gz_id`)
- ) ENGINE=InnoDB DEFAULT CHARSET=latin1
- 1 row in set (0.00 sec)
- mysql>
- //添加外键
- 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 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)、外键字段的值必须在参考表字段值范围内
- 员工表插入记录
- 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,60000);
- mysql> select * from db1.gz;
- +-------+----------+
- | gz_id | pay |
- +-------+----------+
- | 1 | 50000 |
- | 2 | 60000 |
- +-------+----------+
- 2 rows in set (0.00 sec)
- #没有的3号员工 工资表插入记录报错
- mysql> insert into db1.gz values(3,50000);
- 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)
- 员工表 插入编号3的员工
- mysql> insert into db1.yg (name) values ("Lucy");
- mysql> select * from db1.yg;
- 可以给3号员工 发工资了
- mysql> insert into db1.gz values(3,40000);
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 | 60000.00 |
- | 9 | 40000.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)
- 删除编号2的员工
- mysql> delete from db1.yg where yg_id=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)
- 查看工资表也没有编号2的工资了
- mysql> select * from db1.gz;
- +-------+----------+
- | gz_id | pay |
- +-------+----------+
- | 1 | 50000.00 |
- | 9 | 40000.00 |
- +-------+----------+
- 2 rows in set (0.00 sec)
- mysql>
4)、外键使用注意事项
- #被参考的表不能删除
- 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遍工资 且有员工编号的员工才能发工资
- # 如果重复发工资和没有编号的发了工资 删除记录后 再添加主键
- delete form db1.gz;
- alter table db1.gz add primary key(gz_id);
- 保证每个员工只能发1遍工资 且有员工编号的员工才能发工资
- mysql> insert into db1.gz values (1,53000); 报错
- mysql> insert into db1.gz values (9,58000); 报错
- mysql> insert into db1.gz values (NULL,80000); 报错
完成如下练习:
使用规则:
实现此案例需要按照如下步骤进行。
步骤一:练习索引的创建、查看、删除、添加
1)建表时创建索引命令格式
- CREATE TABLE 库.表(
- 字段列表 ,
- INDEX(字段名) ,
- INDEX(字段名)
- );
例子
- Create database home;
- Use home;
- CREATE TABLE tea4(
- id char(6),
- name varchar(6),
- age int(3),
- gender ENUM('boy','girl') DEFAULT 'boy',
- INDEX(id),INDEX(name)
- );
查看索引
- 查看表头是否有索引
- des 库.表;
例子
- mysql> desc home.tea4;
- +--------+--------------------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +--------+--------------------+------+-----+---------+-------+
- | id | char(6) | YES | MUL | NULL | |
- | name | varchar(6) | YES | MUL | NULL | |
- | age | int(3) | YES | | 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>
查看索引详细信息
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) | YES | | NULL | |
- | name | varchar(6) | YES | MUL | NULL | |
- | age | int(3) | YES | | 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>
已有表添加索引命令
CREATE INDEX 索引名 ON 库.表(字段名);
例子
- mysql> create index nianling on home.tea4(age);
- mysql> desc home.tea4;
- +--------+--------------------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +--------+--------------------+------+-----+---------+-------+
- | id | char(6) | YES | | NULL | |
- | name | varchar(6) | YES | MUL | NULL | |
- | age | int(3) | YES | 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>
步骤二:验证索引
- mysql> desc tarena.user;
- +----------+-------------+------+-----+---------+----------------+
- | Field | Type | Null | Key | Default | Extra |
- +----------+-------------+------+-----+---------+----------------+
- | id | int | NO | PRI | NULL | auto_increment |
- | name | char(20) | YES | | NULL | |
- | password | char(1) | YES | | NULL | |
- | uid | int | YES | | NULL | |
- | gid | int | YES | | NULL | |
- | comment | varchar(50) | YES | | NULL | |
- | homedir | varchar(80) | YES | | NULL | |
- | shell | char(30) | YES | | NULL | |
- +----------+-------------+------+-----+---------+----------------+
- 8 rows in set (0.00 sec)
2)使用name表头做筛选条件,查找记录
- mysql> select * from tarena.user where name="sshd";
- +----+------+----------+------+------+-------------------------+-----------------+---------------+
- | id | name | password | uid | gid | comment | homedir | shell |
- +----+------+----------+------+------+-------------------------+-----------------+---------------+
- | 17 | sshd | x | 74 | 74 | Privilege-separated SSH | /var/empty/sshd | /sbin/nologin |
- +----+------+----------+------+------+-------------------------+-----------------+---------------+
- 1 row in set (0.00 sec)
3)验证是否使用索引
- mysql> explain select * from tarena.user where name="sshd" \G
- *************************** 1. row ***************************
- id: 1
- select_type: SIMPLE
- table: user 表名
- partitions: NULL
- type: ALL
- possible_keys: NULL
- key: NULL 使用的索引名
- key_len: NULL
- ref: NULL
- rows: 27 查找的总行数
- filtered: 10.00
- Extra: Using where 额外说明
- 1 row in set, 1 warning (0.00 sec)
4)查看表的总行数,查找sshd 用做的是全表扫描
- mysql> select count(*) from tarena.user;
- +----------+
- | count(*) |
- +----------+
- | 28 |
- +----------+
- 1 row in set (0.01 sec)
5) 给name表头设置索引
- mysql> create index name on tarena.user(name);
- Query OK, 0 rows affected (0.43 sec)
- Records: 0 Duplicates: 0 Warnings: 0
- mysql> desc tarena.user; 查看
- +----------+-------------+------+-----+---------+----------------+
- | Field | Type | Null | Key | Default | Extra |
- +----------+-------------+------+-----+---------+----------------+
- | id | int | NO | PRI | NULL | auto_increment |
- | name | char(20) | YES | MUL | NULL | |
- | password | char(1) | YES | | NULL | |
- | uid | int | YES | | NULL | |
- | gid | int | YES | | NULL | |
- | comment | varchar(50) | YES | | NULL | |
- | homedir | varchar(80) | YES | | NULL | |
- | shell | char(30) | YES | | NULL | |
- +----------+-------------+------+-----+---------+----------------+
- 8 rows in set (0.00 sec)
6)验证索引
- mysql> explain select * from tarena.user where name="sshd" \G
- *************************** 1. row ***************************
- id: 1
- select_type: SIMPLE
- table: user 表名
- partitions: NULL
- type: ref
- possible_keys: name
- key: name 使用的索引名
- key_len: 21
- ref: const
- rows: 1 查找的总行数
- filtered: 100.00
- Extra: NULL 额外说明
- 1 row in set, 1 warning (0.00 sec)
授权是在数据库服务器里添加用户并设置权限及密码;重复执行grant命令时如果库名和用户名不变时,是追加权限。授权步骤如下:
授权信息保存在mysql库的如下表里:
在192.168.88.50 数据库服务器练习用户授权
在192.168.88.51 数据库服务器测试
实现此案例需要按照如下步骤进行。
步骤一:在192.168.88.50 数据库服务器做如下授权练习
- 命令操作如下所示:
-
- //数据库管理员登陆
- ]# mysql -uroot -pNSD2023...a
- 1)允许所有主机使用root连接数据库服务,对所有库和所有表有完全权限、密码为123qqq…A
-
- mysql> create user root@"%" identified by "123qqq...A"; 创建用户
- Query OK, 0 rows affected (0.08 sec)
- mysql> grant all on *.* to root@"%" ; 授予权限
- Query OK, 0 rows affected (0.13 sec)
- 2)允许192.168.88.0/24网段主机使用plj连接数据库服务,仅对gamedb库有完全权限、密码为tarena
-
- mysql> create user plj@"192.168.88.0/24" identified by "tarena"; 创建用户
- Query OK, 0 rows affected (0.06 sec)
- mysql> grant all on gamedb.* to plj@"192.168.88.0/24"; 授予权限
- Query OK, 0 rows affected (0.05 sec)
- 3)允许在本机使用pljadmin用户连接数据库服务器,仅对tarena库有查询、插入、更新、删除记录的权限,密码为NSD2023…a
-
- mysql> create user pljadmin@"localhost" identified by "NSD2023...a"; 创建用户
- Query OK, 0 rows affected (0.05 sec)
- mysql> grant select , insert , update,delete on tarena.* to pljadmin@"localhost";授予权限
- Query OK, 0 rows affected (0.06 sec)
- 4)允许192.168.88.51主机使用yaya用户连接数据库服务,仅对tarena库有查询权限,密码为tarena1
-
- mysql> create user yaya@"192.168.88.51" identified by "tarena1" ; 创建用户
- Query OK, 0 rows affected (0.10 sec)
- mysql> grant select on tarena.* to yaya@"192.168.88.51"; 授予权限
- Query OK, 0 rows affected (0.07 sec)
- 5)给yaya用户追加,插入记录的权限
-
- mysql> grant insert on tarena.* to yaya@"192.168.88.51";
- Query OK, 0 rows affected (0.05 sec)
- 6)查看添加的用户
-
- //添加的用户保存在 mysql库的user表里
- mysql> select host,user from mysql.user;
- +-----------------+------------------+
- | host | user |
- +-----------------+------------------+
- | % | root |
- | 192.168.88.0/24 | plj |
- | 192.168.88.51 | yaya |
- | localhost | mysql.infoschema |
- | localhost | mysql.session |
- | localhost | mysql.sys |
- | localhost | pljadmin |
- | localhost | root |
- +-----------------+------------------+
- 8 rows in set (0.00 sec)
- //查看已有用户的访问权限
- mysql> show grants for yaya@"192.168.88.51";
- +--------------------------------------------------------------+
- | Grants for yaya@192.168.88.51 |
- +--------------------------------------------------------------+
- | GRANT USAGE ON *.* TO `yaya`@`192.168.88.51` |
- | GRANT SELECT, INSERT ON `tarena`.* TO `yaya`@`192.168.88.51` |
- +--------------------------------------------------------------+
- 2 rows in set (0.00 sec)
- //用户对某一个库的访问权限保存在mysql库的db表里
- mysql> select * from mysql.db where db="tarena" and user="yaya" \G
- *************************** 1. row ***************************
- Host: 192.168.88.51
- Db: tarena
- User: yaya
- Select_priv: Y
- Insert_priv: Y
- Update_priv: N
- Delete_priv: N
- Create_priv: N
- Drop_priv: N
- Grant_priv: N
- References_priv: N
- Index_priv: N
- Alter_priv: N
- Create_tmp_table_priv: N
- Lock_tables_priv: N
- Create_view_priv: N
- Show_view_priv: N
- Create_routine_priv: N
- Alter_routine_priv: N
- Execute_priv: N
- Event_priv: N
- Trigger_priv: N
- 1 row in set (0.00 sec)
- mysql>
- 7)撤销plj用户删库、删表、删记录的权限
-
- mysql> revoke delete,drop on gamedb.* from plj@"192.168.88.0/24" ;
- 8)修改yaya用户的登陆密码为123456
-
- mysql> set password for yaya@"192.168.88.51"="123456" ;
- Query OK, 0 rows affected (0.05 sec)
- 9)删除pljadmin用户
-
- mysql> drop user pljadmin@"localhost" ;
- Query OK, 0 rows affected (0.04 sec)
步骤二:在192.168.88.51测试授权
命令格式 mysql -h数据库服务器ip地址 –u用户名 -p密码
- 1)在mysql51连接mysql50 (使用50 添加的yaya 用户)
-
- [root@mysql51 ~]# mysql -h192.168.88.50 -uyaya -p123456
- mysql> show grants; //查看权限
- +--------------------------------------------------------------+
- | Grants for yaya@192.168.88.51 |
- +--------------------------------------------------------------+
- | GRANT USAGE ON *.* TO `yaya`@`192.168.88.51` |
- | GRANT SELECT, INSERT ON `tarena`.* TO `yaya`@`192.168.88.51` |
- +--------------------------------------------------------------+
- 2 rows in set (0.00 sec)
- mysql> select user();//查看登陆信息
- +--------------------+
- | user() |
- +--------------------+
- | yaya@192.168.88.51 |
- +--------------------+
- 1 row in set (0.00 sec)
- mysql> insert into tarena.user(name,uid) values("jim",11); //权限内可以执行
- Query OK, 1 row affected (0.06 sec)
- mysql> delete from tarena.salary ;
- ERROR 1142 (42000): DELETE command denied to user 'yaya'@'192.168.88.51' for table 'salary' //超出权限 报错
- mysql>
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。