1202、表管理、数据类型、数据批量处理

最新推荐文章于 2022-08-19 15:36:54 发布

朱帅杰

最新推荐文章于 2022-08-19 15:36:54 发布

阅读量435

点赞数

分类专栏： 12 RDBMS关系型数据库管理系统文章标签：数据库 mysql database

本文链接：https://blog.csdn.net/weixin_56619848/article/details/125172624

版权

12 RDBMS关系型数据库管理系统专栏收录该内容

11 篇文章 0 订阅

订阅专栏

文章目录

- `使用 system 可以在mysql 提示符下执行操作系统命令`
一、表管理
二、数据类型
三、数据批量处理

`使用 system 可以在mysql 提示符下执行操作系统命令`

一、表管理

1 客户端把数据存储到数据库服务器上过程是什么样的

**第一步:**连接数据库服务器（连接的方式：命令行脚本访问图形工具）

**第二步:**创建存储数据的库（存放表的目录）

**第三步:**创建存储数据的表 (表就是存储数据的文件)

**第四步:**插入表记录 (向文件里添加行)

**第五步:**断开连接

2 库名是有命名规则

**仅可以使用数字、字母、下划线、**不能纯数字

区分字母大小写，

具有唯一性

不可使用指令关键字、特殊字符

3 建库的基本命令 create database 库名；

mysql> CREATE DATABASE gamedb ;
mysql> CREATE DATABASE GAMEDB ;

mysql> CREATE DATABASE GAMEDB ;     			# 报错
mysql> CREATE DATABASE IF NOT EXISTS gamedb ;   # 正常

mysql> show  databases;          				#查看库

mysql>

4 删库 drop database 库名；

mysql> drop database gamedb;        # 删除数据库   只允许删除自己创建的
Query OK, 0 rows affected (0.00 sec)

mysql> drop database gamedb;       # 删除不存在的库，会报错
ERROR 1008 (HY000): Can't drop database 'gamedb'; database doesn't exist

mysql> drop database if exists gamedb;   # 删除不存在的库，可屏蔽报错信息
Query OK, 0 rows affected, 1 warning (0.00 sec)

5 建表命令格式

使用“建表范式”衡量表创建的是否合理

在这里插入图片描述

1NF 表头下的数据不能再拆分

2NF 一张表里只存储一种数据信息，不能用一张表存储多种数据信息

3NF 表中表头的数据不能依赖其他表头的数据

create  table  库名.表名(
表头名1  数据类型, 
表头名2  数据类型，
表头名3  数据类型，
表头名4  数据类型
);

mysql> create database 学生库;    
mysql> use  学生库;
mysql> create table 学生库.学生信息表(姓名 char(10) , 班级 char(9) , 性别 char(4) , 年龄 int );
mysql> show tables;
+------------------+
| Tables_in_学生    |
+------------------+
| 学生信息表         |
+------------------+

mysql> desc 学生.学生信息表;
+--------+----------+------+-----+---------+-------+
| Field  | Type     | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| 姓名   | char(10) | YES  |     | NULL    |       |
| 班级   | char(9)  | YES  |     | NULL    |       |
| 性别   | char(4)  | YES  |     | NULL    |       |
| 年龄   | int(11)  | YES  |     | NULL    |       |
+--------+----------+------+-----+---------+-------+

mysql> drop table 学生.学生信息表;         # 删除表

mysql> drop database 学生;

mysql> create database studb;
Query OK, 1 row affected (0.00 sec)

mysql> create table studb.stu(name char(10) , class char(9) , gender char(4) , age int );
Query OK, 0 rows affected (0.33 sec)

mysql> desc studb.stu; 				# 查看表头结构
+--------+----------+------+-----+---------+-------+
| Field  | Type     | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| name   | char(10) | YES  |     | NULL    |       |
| class  | char(9)  | YES  |     | NULL    |       |
| gender | char(4)  | YES  |     | NULL    |       |
| age    | int(11)  | YES  |     | NULL    |       |
+--------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)

6 修改表

命令格式： alter table 库名.表名 操作命令；

1.修改表名 rename

1.修改表名 rename  
mysql> alter table studb.stu rename studb.stuinfo;     # 修改表名

2.删除表头 drop

2.1 删除表头  drop
mysql> alter table studb.stuinfo drop age ;  		# 删除字段
2.2 一起删除多个表头
#  mysql> alter table  studb.stuinfo  drop school,drop 班级,drop email;

mysql> use studb;

Database changed
mysql> show tables;
+-----------------+
| Tables_in_studb |
+-----------------+
| stuinfo         |
+-----------------+


mysql> desc stuinfo;
+--------+----------+------+-----+---------+-------+
| Field  | Type     | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| name   | char(10) | YES  |     | NULL    |       |
| class  | char(9)  | YES  |     | NULL    |       |
| gender | char(4)  | YES  |     | NULL    |       |
+--------+----------+------+-----+---------+-------+

3.添加新表头 add

3.添加新表头  add
mysql> alter table studb.stuinfo add  mail  char(30) ; 		# 添加在末尾

添加在指定位置 first添加到表头 after 表头名添加到…之后

mysql> alter table  studb.stuinfo  add  number  char(9)  first , add  school char(10)  after name;

mysql> desc studb.stuinfo; 查看表结构
+--------+----------+------+-----+---------+-------+
| Field  | Type     | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| number | char(9)  | YES  |     | NULL    |       |
| name   | char(10) | YES  |     | NULL    |       |
| school | char(10) | YES  |     | NULL    |       |
| class  | char(9)  | YES  |     | NULL    |       |
| gender | char(4)  | YES  |     | NULL    |       |
| mail   | char(30) | YES  |     | NULL    |       |
+--------+----------+------+-----+---------+-------+

4 .修改表头存储数据的数据类型 modify

4.1修改表头存储数据的 数据类型 modify
mysql> alter table  studb.stuinfo      # 修改字段类型
    -> modify
    -> mail varchar(50) not null default "plj@tedu.cn";
    
mysql> desc studb.stuinfo;
+--------+-------------+------+-----+-------------+-------+
| Field  | Type        | Null | Key | Default     | Extra |
+--------+-------------+------+-----+-------------+-------+
| number | char(9)     | YES  |     | NULL        |       |
| name   | char(10)    | YES  |     | NULL        |       |
| school | char(10)    | YES  |     | NULL        |       |
| class  | char(9)     | YES  |     | NULL        |       |
| gender | char(4)     | YES  |     | NULL        |       |
| mail   | varchar(50) | NO   |     | plj@tedu.cn |       |
+--------+-------------+------+-----+-------------+-------+

4.2 使用modify  修改表头的位置
mysql>alter  table studb.stuinfo  modify mail char(50)  after name ;
mysql> desc studb.stuinfo;                                            
+--------+----------+------+-----+---------+-------+
| Field  | Type     | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| number | char(9)  | YES  |     | NULL    |       |
| name   | char(10) | YES  |     | NULL    |       |
| mail   | char(50) | YES  |     | NULL    |       |
| school | char(10) | YES  |     | NULL    |       |
| class  | char(9)  | YES  |     | NULL    |       |
| gender | char(4)  | YES  |     | NULL    |       |
+--------+----------+------+-----+---------+-------+

5.修改表头名 change

5.修改表头名 change
mysql> alter table studb.stuinfo change  class class_name char(9) ; # 修改表头名
mysql> desc studb.stuinfo;			# 查看修改
+------------+----------+------+-----+---------+-------+
| Field      | Type     | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+-------+
| number     | char(9)  | YES  |     | NULL    |       |
| name       | char(10) | YES  |     | NULL    |       |
| mail       | char(50) | YES  |     | NULL    |       |
| school     | char(10) | YES  |     | NULL    |       |
| class_name | char(9)  | YES  |     | NULL    |       |
| gender     | char(4)  | YES  |     | NULL    |       |
+------------+----------+------+-----+---------+-------+

7 复制表

作用： 备份已有的表

1.表头和数据都复制

命令格式：create table 库名.表名 select * from 库名.表名；

mysql> use studb;
mysql> show tables;

mysql> create table studb.user select * from tarena.user;  
# 将tarena.user备份到studb.user
mysql> show tables;
+-----------------+
| Tables_in_studb |
+-----------------+
| stuinfo         |
| user            |
+-----------------+
2 rows in set (0.00 sec)

2.仅仅复制表头命令格式：（只复制表结构）

命令格式: CREATE TABLE 库名.表名 LIKE 库名.表名

mysql> use studb;
mysql> show tables;
+-----------------+
| Tables_in_studb |
+-----------------+
| stuinfo         |
| user            |
+-----------------+

mysql> create table studb.user2  like tarena.user;  # 仅仅复制表头

mysql> select  * from studb.user2;
Empty set (0.00 sec)
mysql> desc studb.user2;
# 有表头结构，没有数据

8 管理表记录

对表中存储的行做管理，

操作包括：

查看 select

插入 insert into

更新 update

删除 delete

1.插入表记录

1.1 不指定列名插入记录（必须给所有列赋值）

insert  into  库名.表名  values (值列表); 		    	# 插入1行
insert  into  库名.表名  values (值列表),(值列表)...;   # 插入多行

mysql> desc studb.stuinfo;
+------------+----------+------+-----+---------+-------+
| Field      | Type     | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+-------+
| number     | char(9)  | YES  |     | NULL    |       |
| name       | char(10) | YES  |     | NULL    |       |
| mail       | char(50) | YES  |     | NULL    |       |
| school     | char(10) | YES  |     | NULL    |       |
| class_name | char(9)  | YES  |     | NULL    |       |
| gender     | char(4)  | YES  |     | NULL    |       |
+------------+----------+------+-----+---------+-------+
mysql> select * from studb.stuinfo;
Empty set (0.00 sec)

mysql> insert into studb.stuinfo values("1001","bob","123@tedu.cn","tarena","nsd2202","girl");  # 插入1行

mysql> insert into studb.stuinfo values
("1001","bob","123@tedu.cn","tarena","nsd2202","girl"), ("1003","tom","456@tedu.cn","tarena","nsd2202","girl"), ("1004","momo","112@tedu.cn","tarena","nsd2202","girl");		 # 插入多行	

mysql>  select  * from studb.stuinfo;
+--------+------+-------------+--------+------------+--------+
| number | name | mail        | school | class_name | gender |
+--------+------+-------------+--------+------------+--------+
| 1001   | bob  | 123@tedu.cn | tarena | nsd2202    | girl   |
| 1001   | bob  | 123@tedu.cn | tarena | nsd2202    | girl   |
| 1003   | tom  | 456@tedu.cn | tarena | nsd2202    | girl   |
| 1004   | momo | 112@tedu.cn | tarena | nsd2202    | girl   |
+--------+------+-------------+--------+------------+--------+

1.2 指定列名插入记录（仅需给指定列赋值）

insert  into  库名.表名(列名列表)  values (值列表); 		     # 插入1行
insert  into  库名.表名(列名列表)  values (值列表),(值列表)...;  # 插入多行

mysql> insert into  studb.stuinfo(number,name) values("1002","john"); # 插入1行
mysql>  select  * from studb.stuinfo;
+--------+------+-------------+--------+------------+--------+
| number | name | mail        | school | class_name | gender |
+--------+------+-------------+--------+------------+--------+
| 1001   | bob  | 123@tedu.cn | tarena | nsd2202    | girl   |
| 1001   | bob  | 123@tedu.cn | tarena | nsd2202    | girl   |
| 1003   | tom  | 456@tedu.cn | tarena | nsd2202    | girl   |
| 1004   | momo | 112@tedu.cn | tarena | nsd2202    | girl   |
| 1002   | john | NULL        | NULL   | NULL       | NULL   |
+--------+------+-------------+--------+------------+--------+
5 rows in set (0.00 sec)
#----------------------------------------------------------------------#
mysql> insert into  studb.stuinfo(number,name) values("1005","jerry"),("1006","lili");								# 插入多行
mysql>  select  * from studb.stuinfo;                                 
+--------+-------+-------------+--------+------------+--------+
| number | name  | mail        | school | class_name | gender |
+--------+-------+-------------+--------+------------+--------+
| 1001   | bob   | 123@tedu.cn | tarena | nsd2202    | girl   |
| 1001   | bob   | 123@tedu.cn | tarena | nsd2202    | girl   |
| 1003   | tom   | 456@tedu.cn | tarena | nsd2202    | girl   |
| 1004   | momo  | 112@tedu.cn | tarena | nsd2202    | girl   |
| 1002   | john  | NULL        | NULL   | NULL       | NULL   |
| 1005   | jerry | NULL        | NULL   | NULL       | NULL   |
| 1006   | lili  | NULL        | NULL   | NULL       | NULL   |
+--------+-------+-------------+--------+------------+--------+
7 rows in set (0.00 sec)

1.3 使用select查询结果赋值

insert  into  库名.表明(字段列表)  ( select  字段列表  from  库.表  where  条件);

mysql>  insert into studb.stuinfo(number,name) (select number,name from studb.stuinfo where name in ("bob","john","lili") );


mysql>  select  * from studb.stuinfo;                                 
+--------+-------+-------------+--------+------------+--------+
| number | name  | mail        | school | class_name | gender |
+--------+-------+-------------+--------+------------+--------+
| 1001   | bob   | 123@tedu.cn | tarena | nsd2202    | girl   |
| 1001   | bob   | 123@tedu.cn | tarena | nsd2202    | girl   |
| 1003   | tom   | 456@tedu.cn | tarena | nsd2202    | girl   |
| 1004   | momo  | 112@tedu.cn | tarena | nsd2202    | girl   |
| 1002   | john  | NULL        | NULL   | NULL       | NULL   |
| 1005   | jerry | NULL        | NULL   | NULL       | NULL   |
| 1006   | lili  | NULL        | NULL   | NULL       | NULL   |
| 1007   | NULL  | NULL        | tarena | NULL       | boy    |
| 1001   | bob   | NULL        | NULL   | NULL       | NULL   |
| 1001   | bob   | NULL        | NULL   | NULL       | NULL   |
| 1002   | john  | NULL        | NULL   | NULL       | NULL   |
| 1006   | lili  | NULL        | NULL   | NULL       | NULL   |
+--------+-------+-------------+--------+------------+--------+
12 rows in set (0.00 sec)

1.4 使用set命令赋值

insert  into  库名.表名  set  字段=值, 字段=值, ;

mysql>  insert into studb.stuinfo set number="1007" , school="tarena", gender="boy";

mysql>  select  * from studb.stuinfo;                                 
+--------+-------+-------------+--------+------------+--------+
| number | name  | mail        | school | class_name | gender |
+--------+-------+-------------+--------+------------+--------+
| 1001   | bob   | 123@tedu.cn | tarena | nsd2202    | girl   |
| 1001   | bob   | 123@tedu.cn | tarena | nsd2202    | girl   |
| 1003   | tom   | 456@tedu.cn | tarena | nsd2202    | girl   |
| 1004   | momo  | 112@tedu.cn | tarena | nsd2202    | girl   |
| 1002   | john  | NULL        | NULL   | NULL       | NULL   |
| 1005   | jerry | NULL        | NULL   | NULL       | NULL   |
| 1006   | lili  | NULL        | NULL   | NULL       | NULL   |
| 1007   | NULL  | NULL        | tarena | NULL       | boy    |
+--------+-------+-------------+--------+------------+--------+
8 rows in set (0.00 sec)

2.修改表记录(更新数据)

更新：修改行中列的数据使用update

2.1批量修改（不加筛选条件修改）

命令格式： update 库名.表名 set 字段名 = 值，字段名 = 值；

mysql> update studb.stuinfo set school="tarena",gender="no";

mysql> select * from studb.stuinfo;
+--------+-------+-------------+--------+------------+--------+
| number | name  | mail        | school | class_name | gender |
+--------+-------+-------------+--------+------------+--------+
| 1001   | bob   | 123@tedu.cn | tarena | nsd2202    | no     |
| 1001   | bob   | 123@tedu.cn | tarena | nsd2202    | no     |
| 1003   | tom   | 456@tedu.cn | tarena | nsd2202    | no     |
| 1004   | momo  | 112@tedu.cn | tarena | nsd2202    | no     |
| 1002   | john  | NULL        | tarena | NULL       | no     |
| 1005   | jerry | NULL        | tarena | NULL       | no     |
| 1006   | lili  | NULL        | tarena | NULL       | no     |
| 1007   | NULL  | NULL        | tarena | NULL       | no     |
| 1001   | bob   | NULL        | tarena | NULL       | no     |
| 1001   | bob   | NULL        | tarena | NULL       | no     |
| 1002   | john  | NULL        | tarena | NULL       | no     |
| 1006   | lili  | NULL        | tarena | NULL       | no     |
+--------+-------+-------------+--------+------------+--------+
12 rows in set (0.00 sec)

2.2 仅修改与条件匹配

mysql> update studb.stuinfo set name="haha" where number="1001";

mysql> select * from studb.stuinfo;
+--------+-------+-------------+--------+------------+--------+
| number | name  | mail        | school | class_name | gender |
+--------+-------+-------------+--------+------------+--------+
| 1001   | haha  | 123@tedu.cn | tarena | nsd2202    | no     |
| 1001   | haha  | 123@tedu.cn | tarena | nsd2202    | no     |
| 1003   | tom   | 456@tedu.cn | tarena | nsd2202    | no     |
| 1004   | momo  | 112@tedu.cn | tarena | nsd2202    | no     |
| 1002   | john  | NULL        | tarena | NULL       | no     |
| 1005   | jerry | NULL        | tarena | NULL       | no     |
| 1006   | lili  | NULL        | tarena | NULL       | no     |
| 1007   | NULL  | NULL        | tarena | NULL       | no     |
| 1001   | haha  | NULL        | tarena | NULL       | no     |
| 1001   | haha  | NULL        | tarena | NULL       | no     |
| 1002   | john  | NULL        | tarena | NULL       | no     |
| 1006   | lili  | NULL        | tarena | NULL       | no     |
+--------+-------+-------------+--------+------------+--------+
12 rows in set (0.00 sec)

3、删除表中行 delete

格式：

仅删除符合条件的行（删除命令有筛选条件） 
delete  from  库名.表名  where  筛选条件；

删除表里的所有行（删除命令有筛选条件）    
delete   from  库名.表名；

`truncate  table 库名.表名； `       删除表里的记录

truncate 与 delete 删除记录的区别！！！ (要知道 )

- TRUNCATE不支持WHERE条件

- 自增长列，TRUNCATE后从1开始；DELETE继续编号

- TRUNCATE不能回滚，DELETE可以

- 效率略高于DELETE

只删除与条件匹配的行

mysql> delete from studb.stuinfo where name="haha";
Query OK, 4 rows affected (0.04 sec)

mysql> select * from studb.stuinfo;
+--------+-------+-------------+--------+------------+--------+
| number | name  | mail        | school | class_name | gender |
+--------+-------+-------------+--------+------------+--------+
| 1003   | tom   | 456@tedu.cn | tarena | nsd2202    | no     |
| 1004   | momo  | 112@tedu.cn | tarena | nsd2202    | no     |
| 1002   | john  | NULL        | tarena | NULL       | no     |
| 1005   | jerry | NULL        | tarena | NULL       | no     |
| 1006   | lili  | NULL        | tarena | NULL       | no     |
| 1007   | NULL  | NULL        | tarena | NULL       | no     |
| 1002   | john  | NULL        | tarena | NULL       | no     |
| 1006   | lili  | NULL        | tarena | NULL       | no     |
+--------+-------+-------------+--------+------------+--------+
8 rows in set (0.00 sec)

删除所有行

mysql> delete from studb.stuinfo;
Query OK, 8 rows affected (0.04 sec)

mysql> select * from studb.stuinfo;
Empty set (0.00 sec)

二、数据类型

MySQL帮助文档

1、数值类型

（1）整数类型

仅存储数值的整数部分

类型	名称	有符号范围	无符号范围
tinyint	微小整数	-128 ～ 127	0～255
smallint	小整数	-32768～32767	0～65535
mediumint	中整数	-2^23 ～ 2^23-1	0～ 2^24-1
int	大整数	-2^31～231-1	0～2^32-1
bigint	极大整数	-2^63～263-1	0～2^64-1
unsigned	使用无符号存储范围

（2）浮点类型(能存储小数的类型)

在这里插入图片描述

# level 游戏级别   money   游戏币的钱
mysql> create table studb.t1(level tinyint unsigned,money double);
Query OK, 0 rows affected (0.14 sec)

mysql> desc studb.t1;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| level | tinyint(3) unsigned | YES  |     | NULL    |       |
| money | double              | YES  |     | NULL    |       |
+-------+---------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into studb.t1 values(80,88);			# 在存储的范围内
Query OK, 1 row affected (0.04 sec)

mysql> insert into studb.t1 values(301,1.29);		# 超出字段level 的存储范围报错
ERROR 1264 (22003): Out of range value for column 'level' at row 1
mysql> insert into studb.t1 values(255,1.29);		# 在存储范围内
Query OK, 1 row affected (0.04 sec)

mysql> insert into studb.t1 values(1.25,7.25);		# 整数类型 不存储小数位
Query OK, 1 row affected (0.04 sec)

mysql> select * from studb.t1;
+-------+-------+
| level | money |
+-------+-------+
|    80 |    88 |
|   255 |  1.29 |
|     1 |  7.25 |
+-------+-------+
3 rows in set (0.00 sec)

查看服务自带的表的表结构使用的数据类型

mysql>  desc mysql.user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field                  | Type                              | Null | Key | Default               | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host                   | char(60)                          | NO   | PRI |                       |       |
| ....                   | ........                          | ..   | ... |                       |       |
| max_questions          | int(11) unsigned                  | NO   |     | 0                     |       |
| max_updates            | int(11) unsigned                  | NO   |     | 0                     |       |
| max_connections        | int(11) unsigned                  | NO   |     | 0                     |       |
| max_user_connections   | int(11) unsigned                  | NO   |     | 0                     |       |
| .......                | ......                            | ...  |     | ....                  |       |
| password_lifetime      | smallint(5) unsigned              | YES  |     | NULL                  |       |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+

2、字符类型

(表头下存储的是汉字或者是英文字母)

（1）常用字符类型

类型	名称	存储范围
char(字符个数)	定长字符类型（固定长度）	1-255字符
varchar(字符个数)	变长（长度不固定）	1-65535字符（有效存储范围到65532）

一个英文字母  是一个字符
一个汉字     也是一个字符

1) char和varchar的异同

char 和 varchar 共同点 都不能超出指定的字符个数

char 和 varchar 不同点 存储的字符个数小于指定的字符个数时处理的方式不一样，

具体如下：

host  char(3)
a     补2个空格 凑够定义字符个数3
ab    补1个空格 凑够定义字符个数3
abc   正好够定义字符个数3 不补空格
adbc  超出了定义字符个数3 报错不让存

address  varchar(3) 
a    不会补空格 直接就存a
ab   不会补空格 直接就存ab
abc  不会补空格 直接就存abc
adbc 超出了定义字符个数3 报错不让存


mysql> create table studb.t2(name char(3),address varchar(5));
Query OK, 0 rows affected (0.19 sec)

mysql> desc studb.t2;
+---------+------------+------+-----+---------+-------+
| Field   | Type       | Null | Key | Default | Extra |
+---------+------------+------+-----+---------+-------+
| name    | char(3)    | YES  |     | NULL    |       |
| address | varchar(5) | YES  |     | NULL    |       |
+---------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into studb.t2 values("a","a");
Query OK, 1 row affected (0.06 sec)

mysql> insert into studb.t2 values("ab","ab");
Query OK, 1 row affected (0.05 sec)

mysql> insert into studb.t2 values("abc","abc");
Query OK, 1 row affected (0.06 sec)

mysql> insert into studb.t2 values("abcd","abcd");
ERROR 1406 (22001): Data too long for column 'name' at row 1
mysql> insert into studb.t2 values("abc","abcde");
Query OK, 1 row affected (0.04 sec)

mysql> insert into studb.t2 values("abc","abcdef");
ERROR 1406 (22001): Data too long for column 'address' at row 1
mysql> select * from studb.t2;
+------+---------+
| name | address |
+------+---------+
| a    | a       |
| ab   | ab      |
| abc  | abc     |
| abc  | abcde   |
+------+---------+
4 rows in set (0.00 sec)

2) 默认不允许给表头存储中文要存储中文建表是要指定表使用中文字符集

# 查看表使用的字符集
mysql> show create table studb.t1 \G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `level` tinyint(3) unsigned DEFAULT NULL,
  `money` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

#  说明 ：
# ENGINE=InnoDB             定义存储引擎（存储引擎后边会讲） 
# DEFAULT   CHARSET=latin1  定义字符集 （默认字符集是 latin1  西方国家使用的字符编码）

建表时指定表使用的字符集 utf8

# 建表时指定表使用的字符集 utf8 
mysql> create  table  studb.t3( name  char(3) , address  varchar(10) )  default  charset  utf8;

mysql> show create table studb.t3 \G
*************************** 1. row ***************************
       Table: t3
Create Table: CREATE TABLE `t3` (
  `name` char(3) DEFAULT NULL,
  `address` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> desc studb.t3;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | char(3)     | YES  |     | NULL    |       |
| address | varchar(10) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into studb.t3 values ("张翠山","武当山");
Query OK, 1 row affected (0.02 sec)

mysql> insert into studb.t3 values ("张真人","武当山");
Query OK, 1 row affected (0.03 sec)

mysql> select  * from studb.t3;
+-----------+-----------+
| name      | address   |
+-----------+-----------+
| 张翠山    | 武当山    |
| 张真人    | 武当山    |
+-----------+-----------+
2 rows in set (0.00 sec)

（2）其他字符类型

通常用来存储视频、音频、图片、较大的文本…

类型	名称	范围
tinytext	短文本	0-255字节
tinyblob	二进制形式短文本	0-255字节
text	长文本数据	0-65535字节
blob	二进制形式的长文本	0-65535字节
mediumblob	二进制形式的中等长度文本	0-16777215字节
mediumtext	中等长度文本数据	0-16777215字节
longblob	二进制形式极大文本	0-4284867295字节
longtext	极大文本	0-4284867295字节

3、枚举类型

给表头赋值时，值必须在类型规定的范围内选择

单选 enum(值1，值2，值3 …)

多选 set(值1，值2，值3 …)

mysql> create table studb.t8(
    姓名 char(10), 
    性别 enum("男","女","保密"), 
    爱好 set("吃","睡")
)default charset utf8;
Query OK, 0 rows affected (0.42 sec)

mysql> desc studb.t8;
+--------+----------------------------+------+-----+---------+-------+
| Field  | Type                       | Null | Key | Default | Extra |
+--------+----------------------------+------+-----+---------+-------+
| 姓名   | char(10)                    | YES  |     | NULL    |      |
| 性别   | enum('男','女','保密')       | YES  |     | NULL    |       |
| 爱好   | set('吃','睡')              | YES  |      | NULL    |      |
+--------+----------------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)


mysql>  insert into studb.t8 values ("小包总","男人","帅哥,睡,金钱");
ERROR 1265 (01000): Data truncated for column '性别' at row 1
mysql>  insert into studb.t8 values ("小w","男","睡");
Query OK, 1 row affected (0.04 sec)
mysql>  insert into studb.t8 values ("盈盈","女","吃,睡");
Query OK, 1 row affected (0.04 sec)


mysql> select * from studb.t8;
+--------+--------+---------+
| 姓名   | 性别   | 爱好      |
+--------+--------+---------+
| 小w    | 男     | 睡       |
| 盈盈   | 女     | 吃,睡     |
+--------+--------+---------+
2 rows in set (0.00 sec)

4、日期时间类型

（1）表头存储与日期时间格式的数据

类型	名称	范围	赋值形式
year	年	1901~2155	YYYY
date	日期	0001-01-01~9999-12-31	YYYYMMDD
time	时间	01:00:00~23:59:59	HHMMSS
datetime	日期时间	1000-01-01 00:00:00~9999-12-31 23:59:59	YYYYMMDDHHMMSS
timestamp	日期时间	1970-01-01 00:00:00~2038-01-19 00:00:00	YYYYMMDDHHMMSS

mysql> create table studb.t6(
    -> 姓名 char(10),
    -> 生日 date,
    -> 出生年份 year,
    -> 家庭聚会 datetime,
    -> 聚会地点 varchar(15),
    -> 上班时间 time) default charset utf8;
Query OK, 0 rows affected (0.22 sec)

mysql> desc studb.t6;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| 姓名         | char(10)    | YES  |     | NULL    |       |
| 生日         | date        | YES  |     | NULL    |       |
| 出生年份     | year(4)     | YES  |     | NULL    |       |
| 家庭聚会     | datetime    | YES  |     | NULL    |       |
| 聚会地点     | varchar(15) | YES  |     | NULL    |       |
| 上班时间     | time        | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql> insert into studb.t6 values
    -> ("铁子",20220606,1997,20220607183000,"郑州",090000);
Query OK, 1 row affected (0.03 sec)

mysql> insert into studb.t6(姓名,出生年份)values("憨憨",23),("端端",80);
Query OK, 2 rows affected (0.04 sec)
Records: 2  Duplicates: 0  Warnings: 0

# 使用2位数给year的表头赋值也是可以的  但会自动补全4位数
# 01-69  之间的数字使用20补全4位数的年 2001~2069
# 70-99  之间的数字使用19补全4位数的年 1970~1999
mysql> select * from studb.t6;
+--------+------------+--------------+---------------------+--------------+--------------+
| 姓名   | 生日       | 出生年份     | 家庭聚会            | 聚会地点     | 上班时间     |
+--------+------------+--------------+---------------------+--------------+--------------+
| 铁子   | 2022-06-06 |         1997 | 2022-06-07 18:30:00 | 郑州         | 09:00:00     |
| 憨憨   | NULL       |         2023 | NULL                | NULL         | NULL         |
| 端端   | NULL       |         1980 | NULL                | NULL         | NULL         |
+--------+------------+--------------+---------------------+--------------+--------------+
3 rows in set (0.00 sec)

（2）datetime 与 timestamp 的区别？

1）第一个区别是存储范围不一样

（datetime存储范围大 timestamp范围小）

存储数据的方式不一样

2）第二个区别

timestamp类型    不赋值 使用系统时间自动赋值

datetime类型     不赋值 使用null 赋值

mysql>  create   table  studb.t7(聚会时间   datetime , 开会时间  timestamp );
Query OK, 0 rows affected (0.25 sec)

mysql> desc studb.t7;
+--------------+-----------+------+-----+-------------------+-----------------------------+
| Field        | Type      | Null | Key | Default           | Extra                       |
+--------------+-----------+------+-----+-------------------+-----------------------------+
| 聚会时间     | datetime  | YES  |     | NULL              |                             |
| 开会时间     | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+--------------+-----------+------+-----+-------------------+-----------------------------+
2 rows in set (0.00 sec)


mysql> system date
2022年 06月 07日 星期二 20:04:31 CST

mysql>  insert into studb.t7(聚会时间) values (20211224223000);
Query OK, 1 row affected (0.03 sec)

mysql>  insert into studb.t7(开会时间) values (20220101093000);
Query OK, 1 row affected (0.05 sec)

mysql> select  * from  studb.t7;
+---------------------+---------------------+
| 聚会时间            | 开会时间            |
+---------------------+---------------------+
| 2021-12-24 22:30:00 | 2022-06-07 20:04:55 |
| NULL                | 2022-01-01 09:30:00 |
+---------------------+---------------------+
2 rows in set (0.00 sec)

三、数据批量处理

(一次性向表里存储很多数据或一次性把表里的数据都取出来)

检索目录: 数据导入或导出,存放数据的文件必须在mysql服务要求的目录下

首先：要知道数据库服务默认的检索目录

#查看默认的检索目录

mysql> show variables like  "secure_file_priv";
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv  | /var/lib/mysql-files/ |
+------------------+-----------------------+
1 row in set (0.01 sec)
mysql> exit

[root@host50 ~]# ls -l /var/lib/mysql-files/
总用量 0
[root@host50 ~]# ls -ld /var/lib/mysql-files/
drwxr-x--- 2 mysql mysql 6 11月 29 2016 /var/lib/mysql-files/
[root@host50 ~]#

其次：修改数据库服务默认的检索目录

[root@host50 ~]# vim /etc/my.cnf
[mysqld]
secure_file_priv=/myload      #手动添加
:wq
[root@host50 ~]# mkdir /myload
[root@host50 ~]# chown mysql /myload/
[root@host50 ~]# ls -ld /myload/
drwxr-xr-x 2 mysql root 6 11月  5 16:37 /myload/
[root@host50 ~]# setenforce 0
setenforce: SELinux is disabled
[root@host50 ~]# systemctl  restart mysqld
[root@host50 ~]# mysql -uroot -p123456
mysql> show variables like  "secure_file_priv";
+------------------+----------+
| Variable_name    | Value      |
+------------------+----------+
| secure_file_priv   | /myload/ |
+------------------+----------+
1 row in set (0.00 sec)

1、数据导入

（一次性向表里存储很多数据）

把系统文件的内容存储到数据库服务的表里文件的内容要规律

数据导入格式：

mysql>  load   data  infile   "/检索目录/文件名"   into  table    库名.表名   
fields  terminated by  "文件中列的间隔符号"   
lines   terminated by   "\n"  ;

数据导入的操作步骤：

诉求：将/etc/passwd文件导入db1库的t3表里	
1） 要创建存储数据库（如果没有的话）
2） 建表 (根据导入文件的内容 创建 表头名  表头个数  表头数据类型  根据文件内容定义)
3） 把系统文件拷贝的检索目录里
4)  数据库管理执行导入数据的命令
5） 查看数据
mysql> create database if not exists db1;
Query OK, 1 row affected (0.00 sec)

mysql> drop table db1.t3;
ERROR 1051 (42S02): Unknown table 'db1.t3'
mysql> create table db1.t3(
    -> name char(50),password char(1),
    -> uid int, gid int,comment varchar(200),
    -> homedir varchar(60),shell varchar(30));
Query OK, 0 rows affected (0.31 sec)

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.01 sec)

mysql> select * from db1.t3;
Empty set (0.00 sec)

mysql> system cp /etc/passwd /myload/
mysql> system ls /myload
passwd
mysql> load data infile"/myload/passwd" into table db1.t3
    -> fields terminated by ":"
    -> lines terminated by "\n";
Query OK, 22 rows affected (0.05 sec)
Records: 22  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from db1.t3;

2、数据导出

（一次性把表里的数据都取出来）

1）把数据库服务的表里数据保存到系统文件里。

2）注意导出的数据不包括表头名，只有表里的行。

3）存放导出数据的文件名不需要事先创建且具有唯一。

数据导出命令格式 1

select   字段名列表 from  库.表   where  条件  into  outfile   "/检索命令名/文件名" ;

数据导出命令格式 2

select   字段名列表 from  库.表   where  条件  
into  outfile   "/检索命令名/文件名"  fields  terminated by  "列间隔符号" ；
# fields  terminated by  文件中的列的间隔符号，不指定默认是一个 tab 键的宽度

数据导出命令格式 3

select   字段名列表 from  库.表   where  条件  into  outfile   "/检索命令名/文件名"  fields  terminated by  "列间隔符号"    lines     terminated by  "行间隔符号" ；##	# lines terminated by 文件中行间隔符号,不指定默认一条记录就是文件中的1行

数据导出例子

# 1. 格式一
mysql> select * from db1.t3 where uid<=4 into outfile "/myload/a.txt"; 
Query OK, 5 rows affected (0.01 sec)

mysql> system ls /myload
a.txt  passwd

# 2. 格式二
mysql> select name,homedir,uid from db1.t3 where uid<=10 into outfile "/myload/b.txt" fields terminated by ":";
Query OK, 9 rows affected (0.00 sec)

mysql> system cat /myload/b.txt
root:/root:0
bin:/bin:1
daemon:/sbin:2
adm:/var/adm:3
lp:/var/spool/lpd:4
sync:/sbin:5
shutdown:/sbin:6
halt:/sbin:7
mail:/var/spool/mail:8

# 3. 格式三
mysql> select name,homedir,uid from db1.t3 where uid<=10 into outfile "/myload/c.txt" fields terminated by ":" lines terminated by "!!!";
Query OK, 9 rows affected (0.00 sec)

mysql> system cat /myload/c.txt
root:/root:0!!!bin:/bin:1!!!daemon:/sbin:2!!!adm:/var/adm:3!!!lp:/var/spool/lpd:4!!!sync:/sbin:5!!!shutdown:/sbin:6!!!halt:/sbin:7!!!mail:/var/spool/mail:8!!!mysql>

朱帅杰

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
1202、表管理、数据类型、数据批量处理

**第一步:**连接数据库服务器（连接的方式：命令行脚本访问图形工具）**第二步:**创建存储数据的库（存放表的目录）**第三步:**创建存储数据的表 (表就是存储数据的文件)**第四步:**插入表记录 (向文件里添加行)**第五步:**断开连接**仅可以使用**不能纯数字区分字母大小写，具有唯一性不可使用指令关键字、特殊字符4 删库 drop database 库名；5 建表命令格式使用“建表范式”衡量表创建的是否合理1NF 表头下的数据不能再拆分2NF 一张表里只存储一种数据信息
复制链接

扫一扫