linux之database(数据库基础)
01: 数据库服务概述 、 构建MySQL服务 、 数据库基本管理 、 MySQL数据类型02: 表结构 、 MySQL键值
03: 数据导入导出 、 管理表记录 、 匹配条件 、 MySQL管理工具
04: 用户授权 、 完全备份 、 增量备份
05: percona软件介绍 、 innobackupex备份与恢复
文章目录
表结构 、 MySQL键值
案例1:约束条件
案例2:修改表结构
案例3:index 普通索引
案例4:primary key 主键
案例5:foreign key 外键
约束条件
问题
具体要求如下:
如图-1所示设置约束条件
图-1
步骤
实现此案例需要按照如下步骤进行。
一:设置约束
1)在db2库里创建t2表时设置字段约束条件
mysql>
mysql> create database db2; //建库
Query OK, 1 row affected (0.00 sec)
mysql> use db2; //切换库
Database changed
mysql> create table t2 ( //建表
-> class char(9),
-> name char(10) not null ,
-> age tinyint not null default 19 ,
-> likes set("a","b","c","d") default "a,b"
-> );
Query OK, 0 rows affected (0.05 sec)
mysql> desc t2; //查看表结构
+-------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------------------+------+-----+---------+-------+
| class | char(9) | YES | | NULL | |
| name | char(10) | NO | | NULL | |
| age | tinyint(4) | NO | | 19 | |
| likes | set('a','b','c','d') | YES | | a,b | |
+-------+----------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql>
mysql> insert into t2 values (null,"bob",29,"c,d");
Query OK, 1 row affected (0.05 sec)
mysql> insert into t2(class,name) values ("nsd1902","tom");
Query OK, 1 row affected (0.05 sec)
mysql> insert into t2 values (null,null,null,null);
ERROR 1048 (23000): Column 'name' cannot be null //不允许赋null值
MariaDB [db2]>
MariaDB [db2]> select * from db2.t1; //查看记录
+---------+------+-----+-------+
| class | name | age | likes |
+---------+------+-----+-------+
| NULL | bob | 29 | c,d |
| nsd1902 | tom | 19 | a,b |
+---------+------+-----+-------+
2 rows in set (0.00 sec)
mysql>
修改表结构
问题 具体要求如下:
添加字段 修改字段名 修改字段类型 删除字段 修改表名
步骤
实现此案例需要按照如下步骤进行。
一:添加字段
1)在studb库下创建tea6表
Mysql> create database studb;
mysql> CREATE TABLE studb.tea6(
-> id int(4) ,
-> name varchar(4) NOT NULL,
-> age int(2) NOT NULL
-> );
Query OK, 0 rows affected (0.34 sec)
mysql>
2)为tea6表添加一个address字段
mysql> DESC tea6;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id | int(4) | YES | | NULL | |
| name | varchar(4) | NO | | NULL | |
| age | int(2) | NO | | NULL | |
+-------+------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
添加address字段:
mysql> ALTER TABLE tea6 ADD address varchar(48);
Query OK, 0 rows affected (0.84 sec)
Records: 0 Duplicates: 0 Warnings: 0
添加后(默认作为最后一个字段):
mysql> DESC tea6;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id | int(4) | YES | | NULL | |
| name | varchar(4) | NO | | NULL | |
| age | int(2) | NO | | NULL | |
| address | varchar(48) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
3)在tea6表的age列之后添加一个gender字段
添加操作:
mysql> ALTER TABLE tea6 ADD gender enum('boy','girl') AFTER age;
Query OK, 0 rows affected (0.59 sec)
Records: 0 Duplicates: 0 Warnings: 0
确认添加结果:
mysql> DESC tea6;
+---------+--------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------------+------+-----+---------+-------+
| id | int(4) |YES | | NULL | |
| name | varchar(4) | NO | | NULL | |
| age | int(2) | NO | | NULL | |
| gender | enum('boy','girl') | YES | | NULL | |
| address | varchar(48) | YES | | NULL | |
+---------+--------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
二:修改字段名和字段类型
将tea6表的gender字段改名为sex,并添加非空约束
修改操作:
mysql> ALTER TABLE tea6 CHANGE gender
-> sex enum('boy','girl') NOT NULL;
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
确认修改结果:
mysql> DESC tea6;
+---------+--------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------------+------+-----+---------+-------+
| id | int(4) | YES | | NULL | |
| name | varchar(4) | NO | | NULL | |
| age | int(2) | NO | | NULL | |
| sex | enum('boy','girl') | NO | | NULL | |
| address | varchar(48) | YES | | NULL | |
+---------+--------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
三:删除字段
删除tea6表中名为sex的字段:
mysql> ALTER TABLE tea6 DROP sex; //删除操作
Query OK, 0 rows affected (0.52 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC tea6; //确认删除结果
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id | int(4) | YES | | NULL | |
| name | varchar(4) | NO | | NULL | |
| age | int(2) | NO | | NULL | |
| address | varchar(48) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
index 普通索引
问题
具体要求如下:
在已有表里添加index字段 建表时,添加index字段 查看表索引 删除表索引
步骤
实现此案例需要按照如下步骤进行。
一:索引的创建与删除
1)创建表的时候指定INDEX索引字段
创建库home:
mysql> create database home;
Query OK, 1 row affected (0.00 sec)
允许有多个INDEX索引字段。比如,以下操作在home库中创建了tea4表,将其中的id、name作为索引字段:
mysql> USE home;
Database changed
mysql> 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)
-> );
Query OK, 0 rows affected (0.59 sec)
查看新建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 | |
+--------+--------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
2)删除现有表的某个INDEX索引字段
比如,删除tea4表中名称为named的INDEX索引字段:
mysql> drop INDEX name ON tea4; //删除name字段的索引
Query OK, 0 rows affected (0.18 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC tea4; //确认删除结果
+--------+--------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------------+------+-----+---------+-------+
| id | char(6) | NO | MUL | NULL | |
| name | varchar(6) | NO | | NULL | |
| age | int(3) | NO | | NULL | |
| gender | enum('boy','girl') | YES | | boy | |
+--------+--------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
3)在已有的某个表中设置INDEX索引字段
比如,针对tea4表的age字段建立索引,名称为 nianling:
mysql> CREATE INDEX nianling ON tea4(age); //针对指定字段创建索引
Query OK, 0 rows affected (0.62 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC tea4; //确认创建结果
+--------+--------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------------+------+-----+---------+-------+
| id | char(6) | NO | MUL | NULL | |
| name | varchar(6) | NO | | NULL | |
| age | int(3) | NO | MUL | NULL | |
| gender | enum('boy','girl') | YES | | boy | |
+--------+--------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
4)查看指定表的索引信息
使用SHOW INDEX 指令:
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: 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)
Query OK, 0 rows affected (0.30 sec)
Mysql>
Query OK, 0 rows affected (0.47 sec)
Records: 0 Duplicates: 0 Warnings: 0
primary key 主键
问题
具体要求如下:
建表时,创建主键 在已有表里添加主键 建表时创建复合主键 删除主键 设置字段值自增长
步骤
实现此案例需要按照如下步骤进行。
一:练习主键的使用
1)建表时设置PRIMARY KEY主键索引
Mysql> create database db2;
mysql> CREATE TABLE db2.biao01(
-> id int(4) PRIMARY KEY, //直接在字段定义时约束
-> name varchar(8)
-> );
Query OK, 0 rows affected (0.19 sec)
或者:
mysql> CREATE TABLE db2.biao02(
-> id int(4),
-> name varchar(8),
-> PRIMARY KEY(id) //所有字段定义完,最后指定
-> );
Query OK, 0 rows affected (0.17 sec)
在建表的时候,如果主键字段为int类型,还可以为其设置AUTO_INCREMENT自增属性,这样当添加新的表记录时,此字段的值会自动从1开始逐个增加,无需手动指定。比如,新建一个tea6表,将id列作为自增的主键字段:
mysql> CREATE TABLE db2.tea6(
-> id int(4) AUTO_INCREMENT,
-> name varchar(4) NOT NULL,
-> age int(2) NOT NULL,
-> PRIMARY KEY(id)
-> );
Query OK, 0 rows affected (0.29 sec)
2)删除现有表的PRIMARY KEY主键索引
如果要移除某个表的PRIMARY KEY约束,需要通过ALTER TABLE指令修改。比如,以下操作将清除biao01表的主键索引。
清除前(主键为id):
mysql> DESC db2.biao01;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id | int(4) | NO | PRI | NULL | |
| name | varchar(8) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
清除操作:
mysql> ALTER TABLE db2.biao01 DROP PRIMARY KEY;
Query OK, 0 rows affected (0.49 sec)
Records: 0 Duplicates: 0 Warnings: 0
清除后(无主键):
mysql> DESC db2.biao01;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id | int(4) | NO | | NULL | |
| name | varchar(8) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
当尝试删除tea6表的主键时,会出现异常:
mysql> ALTER TABLE tea6 DROP PRIMARY KEY;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
这是因为tea6表的主键字段id具有AUTO_INCREMNET自增属性,提示这种字段必须作为主键存在,因此若要清除此主键必须先清除自增属性——修改id列的字段定义:
mysql> ALTER TABLE tea6 MODIFY id int(4) NOT NULL;
Query OK, 0 rows affected (0.75 sec)
Records: 0 Duplicates: 0 Warnings: 0
然后再清除主键属性就OK了:
mysql> ALTER TABLE tea6 DROP PRIMARY KEY; //清除主键
Query OK, 0 rows affected (0.39 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc tea6; //确认清除结果
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id | int(4) | NO | | NULL | |
| name | varchar(4) | NO | | NULL | |
| age | int(2) | NO | | NULL | |
+-------+------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
3)为现有表添加PRIMARY KEY主键索引
重新为tea6表指定主键字段,仍然使用id列:
mysql> ALTER TABLE tea6 ADD PRIMARY KEY(id); //设置主键字段
Query OK, 0 rows affected (0.35 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC tea6; //确认设置结果
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id | int(4) | NO | PRI | NULL | |
| name | varchar(4) | NO | | NULL | |
| age | int(2) | NO | | NULL | |
+-------+------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
4)建表时创建复合主键
mysql>
mysql> create table db2.t6(
-> class char(7),
-> name char(15),
-> pay enum("yes","no") default "no",
-> primary key(class,name,pay) //指定多个字段一起做主键
-> );
Query OK, 0 rows affected (0.04 sec)
mysql> desc db2.t6;
+-------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| class | char(7) | NO | PRI | | |
| name | char(15) | NO | PRI | | |
| pay | enum('yes','no') | NO | PRI | no | |
+-------+------------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql>
foreign key 外键
问题
具体要求如下:
创建员工表yg 如表-1所示 创建工资表gz如表-2所示 ,并设置外键实现同步更新与同步删除 测试外键 删除外键
表-1 员工表yg的数据
表-2 工资表gz的数据
一:创建外键
1)创建yg表,用来记录员工工号、姓名,其中yg_id列作为主键,并设置自增属性
mysql> CREATE TABLE yg(
-> yg_id int primary key AUTO_INCREMENT,
-> name char(16)
-> )engine=innodb;
Query OK, 0 rows affected (0.15 sec)
Mysql>
2)创建gz表,用来记录员工的工资信息
其中gz_id需要参考员工工号,即gz表的gz_id字段设为外键,将yg表的yg_id字段作为参考键:
mysql> CREATE TABLE gz(
-> gz_id int,
-> name char(16) ,
-> gz float(7,2) ,
-> FOREIGN KEY(gz_id) REFERENCES yg(yg_id) //创建外键
-> ON UPDATE CASCADE ON DELETE CASCADE //同步更新、同步删除
-> )engine=innodb;
Query OK, 0 rows affected (0.23 sec)
Mysql>
3)为yg表添加2条员工信息记录
因yg_id有AUTO_INCREMENT属性,会自动填充,所以只要为name列赋值就可以了。
插入表记录可使用INSERT指令,这里先执行下列操作,具体在下一章学习:
mysql> INSERT INTO yg(name) VALUES('Jerry'),('Tom');
Query OK, 2 rows affected (0.16 sec)
Records: 2 Duplicates: 0 Warnings: 0
确认yg表的数据记录:
mysql> SELECT * FROM yg;
+-------+-------+
| yg_id | name |
+-------+-------+
| 1 | Jerry |
| 2 | Tom |
+-------+-------+
2 rows in set (0.00 sec)
4)为gz表添加2条工资信息记录
同上,数据参考图-2,插入相应的工资记录(gz_id字段未指定默认值,也未设置自增属性,所以需要手动赋值):
mysql> INSERT INTO gz(gz_id,name,gz)
-> VALUES(1,'Jerry',12000),(2,'Tom',8000)
-> ;
Query OK, 2 rows affected (0.06 sec)
Records: 2 Duplicates: 0 Warnings: 0
确认gz表的数据记录:
mysql> SELECT * FROM gz;
+-------+-------+----------+
| gz_id | name | gz |
+-------+-------+----------+
| 1 | Jerry | 12000.00 |
| 2 | Tom | 8000.00 |
+-------+-------+----------+
2 rows in set (0.05 sec)
5)验证表记录的UPDATE更新联动
将yg表中Jerry用户的yg_id修改为1234:
mysql> update yg SET yg_id=1234 WHERE name='Jerry';
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0
确认修改结果:
mysql> SELECT * FROM yg;
+-------+-------+
| yg_id | name |
+-------+-------+
| 2 | Tom |
| 1234 | Jerry |
+-------+-------+
2 rows in set (0.00 sec)
同时也会发现,gz表中Jerry用户的gz_id也跟着变了:
mysql> SELECT * FROM gz;
+-------+-------+----------+
| gz_id | name | gz |
+-------+-------+----------+
| 1234 | Jerry | 12000.00 |
| 2 | Tom | 8000.00 |
+-------+-------+----------+
2 rows in set (0.00 sec)
6)验证表记录的DELETE删除联动
删除yg表中用户Jerry的记录:
mysql> DELETE FROM yg WHERE name='Jerry';
Query OK, 1 row affected (0.05 sec)
确认删除结果:
mysql> SELECT * FROM yg;
+-------+------+
| yg_id | name |
+-------+------+
| 2 | Tom |
+-------+------+
1 row in set (0.00 sec)
查看gz表中的变化(Jerry的记录也没了):
mysql> SELECT * FROM gz;
+-------+------+---------+
| gz_id | name | gz |
+-------+------+---------+
| 2 | Tom | 8000.00 |
+-------+------+---------+
1 row in set (0.00 sec)
7)删除指定表的外键约束
先通过SHOW指令获取表格的外键约束名称:
mysql> SHOW CREATE TABLE gz\G
*************************** 1. row ***************************
Table: gz
Create Table: CREATE TABLE `gz` (
`gz_id` int(4) NOT NULL,
`name` char(16) NOT NULL,
`gz` float(7,2) NOT NULL DEFAULT '0.00',
KEY `name` (`name`),
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=utf8
1 row in set (0.00 sec)
其中gz_ibfk_1即删除外键约束时要用到的名称。
删除操作:
mysql> ALTER TABLE gz DROP FOREIGN KEY gz_ibfk_1;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
确认删除结果:
mysql> SHOW CREATE TABLE gz\G
*************************** 1. row ***************************
Table: gz
Create Table: CREATE TABLE `gz` (
`gz_id` int(4) NOT NULL,
`name` char(16) NOT NULL,
`gz` float(7,2) NOT NULL DEFAULT '0.00',
KEY `name` (`name`),
KEY `gz_id` (`gz_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
重要的事情说三遍
作为一个为linux奉献一生的码员,很是荣幸和骄傲,这里我总结了一些linux的精华,也就是速成文章,后面还会继续更新,望大家关注,绝对有用!