数据批量处理
查看默认检索目录
一次性向表里存储多行数据或一次性把表里的多行数据都取出来
show variables like "secure_file_priv";
修改默认检索目录
安装数据库服务软件时,自动创建检索目录
目录的所有者和组用户均为mysql
数据导入:把系统文件的内容存储到数据库服务的表里 (文件内容要规律)
数据导入命令:
mysql> load data infile "/检索目录/文件名" into table 库名.表名
fields terminated by "文件中列的间隔符号"
lines terminated by "\n" ; 文件中行的结束符号
数据导入操作步骤:
1) 建库
2) 建表
根据导入文件的内容创建表头名 表头个数
表头使用的数据类型 根据文件内容定义
3) 把系统文件拷贝的检索目录里
4) 导入数据
5) 查看表记录
echo secure_file_priv=/myload >>/etc/my.cnf
mkdir /myload
systemctl restart mysqld
数据导出
数据导出命令格式1
select 字段名列表 from 库.表 [where 条件] into outfile "/检索目录/文件名" ;
数据导出命令格式2
fields terminated by 文件中的列的间隔符号,不指定默认是一个 tab 键的宽度
select 字段名列表 from 库.表 where 条件
into outfile "/检索命令名/文件名" fields terminated by "符号" ;
数据导出命令格式3
lines terminated by 文件中行间隔符号 不指定默认一条记录就是文件中的1行
select 字段名列表 from 库.表 where 条件 into outfile "/检索命令名/文件名" fields terminated by "符号" lines terminated by "符号" ;
select * from db1.t3 into outfile "/myload/3.txt"
fields terminated by ":"
lines terminated by "-----";
字段基本约束
字段约束: 设置在表头上 ,用来限制字段赋值;每种约束都有各自的功能。
1、NOT NULL :非空,用于保证表头的值不能为空。
2、DEFAULT:默认值,不给表头赋值时,保证表头有值。
3、UNIQUE:唯一索引,用于保证表头的值具有唯一性,可以为空。
create table db1.t31(
name char(10) not null ,
class char(7) default "nsd",
likes set("money","game","film","music") not null default "film,music" );
表头name赋null值 报错
mysql> insert into db1.t31 values (null, null , null);
ERROR 1048 (23000): Column 'name' cannot be null
表头likes赋null值 报错
mysql> insert into db1.t31 values ("bob", null , null);
ERROR 1048 (23000): Column 'likes' cannot be 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)
唯一索引 (unique)
约束的方式:表头值唯一 , 但可以赋null 值
mysql> create database if not exists DB1;
create table DB1.t43 (姓名 char(10) , 护照 char(18) unique );
mysql> desc DB1.t43 ;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| 姓名 | char(10) | YES | | NULL | |
| 护照 | char(18) | YES | UNI | NULL | |
+--------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
赋null值 可以
mysql> insert into DB1.t43 values("bob",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;
+------+--------+
| 姓名 | 护照 |
+------+--------+
| bob | NULL |
| tom | 666888 |
| jim | 766888 |
+------+--------+
3 rows in set (0.00 sec)
主键 (primary key)
约束方式:表头值不允许重复,且不允许赋NULL值
使用说明:
1、表中只能有一个主键表头
2、多个表头做主键,称为复合主键,必须一起创建
3、主键标志PRI
4、主键通常与auto_increment 连用
5、通常把表中唯一标识记录的表头设置为主键[记录编号表头]
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 ; 例子 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 | | +-------+----------+------+-----+---------+-------+
复合主键
表中的多个表头一起做主键 ,有一个不同就可以
复合主键的约束方式: 多条记录 主键的值不允许同时相同
创建复合主键
mysql> drop table db1.t39;
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"); 可以了
删除复合主键
mysql> alter table db1.t39 drop primary key;
主键与auto_increment连用
表头设置了auto_increment属性后,
插入记录时,如果不给表头赋值表头通过自加1的计算结果赋值
要想让表头有自增长 表头必须有主键设置才可以
mysql> create table db1.t38(
-> 行号 int primary key auto_increment,
-> 姓名 char(10),
-> 班级 char(7),
-> 住址 char(10)
-> );
Query OK, 0 rows affected (0.22 sec)
truncate删除行 再添加行 从1开始
mysql> truncate table db1.t39;
外键
作用:保证数据一致性
插入记录时,表头值在另一张表 表头值范围内选择
外键的使用规则:
表存储引擎必须是innodb (在进阶课程里讲 现在仅需要知道如何指定表使用innodb存储引擎)
字段类型要一致
被参照字段必须要是索引类型的一种(通常是 primary key)
通过显示建表命令查看外键 ,并获取外键名
Mysql> show create table 库名.表名 \G
create table 库.表(
表头列表 ,
foreign key(表头名) #指定外键
references 库.表(表头名) #指定参考的表头名
on update cascade #同步更新
on delete cascade #同步删除
)engine=innodb;
> alter table 库.表 drop foreign key 外键名;
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 库名.表名 \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 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 库.表 drop foreign key 外键名;
例子
删除工资表的外键
mysqcccccccccccccccccccwws
查看不到外键
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 库.表 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 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)
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>
外键使用注意事项
#被参考的表不能删除
mysql> drop table db1.yg;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
mysql>
建表三范式
1表头具有原子性
2基于1具有主键可以确定每一行
3基于1、2 一张表存放一种类型的数据 合理不放年龄 放出生年月日 来算出年龄
1 简述数据导入命令格式。
mysql > load data infile "/目录名/文件名" into table 库名. 表名
fields terminated by “符号” lines terminated by "\n";
2 简述主键的使用规则。
- 1)字段值不允许重复,且不允许赋NULL值
- 2)一个表中只能有一个primary key字段
- 3)多个表头做主键,称为复合主键,必须一起创建
- 4)主键的标志是PRI
- 5)主键通常与auto_increment 连用
- 6)通常把行号表头设置为主键
3 简述创建外键语法格式及使用规则。
- 语法格式
- CREATE TABLE 库名.表名 (
- 表头名列表,
- FOREIGN KEY(表头名) REFERENCES 库名.表名(表头名)
- ON UPDATE CASCADE ON DELETE CASCADE
- )ENGINE=innodb;
- 使用规则:
- 1)表存储引擎必须是innodb
- 2)字段类型要一致
- 3)被参照字段必须要是索引类型的一种(primary key)