当前位置:首页 > 有云笔记 > RDBMS1 > 正文内容

表结构 、 MySQL键值

小白3年前 (2022-03-10)RDBMS1269830

案例1:表管理

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

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

   第二步:创建存储数据的库 (存放表的目录)

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

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

第五步:断开连接

库名是有命名规则? (要记牢)

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

区分字母大小写,

具有唯一性

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

建库的基本命令   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> 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)
mysql>

建表命令格式   (要记牢)

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_学生库    |
+---------------------+
| 学生信息表          |
+---------------------+
1 row in set (0.00 sec)
mysql> desc 学生库.学生信息表;
+--------+----------+------+-----+---------+-------+
| Field  | Type     | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| 姓名   | char(10) | YES  |     | NULL    |       |
| 班级   | char(9)  | YES  |     | NULL    |       |
| 性别   | char(4)  | YES  |     | NULL    |       |
| 年龄   | int(11)  | YES  |     | NULL    |       |
+--------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql>
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.29 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)
mysql>

理论知识:

第一个问题:  建表时,根据什么来决定表里建几个表头  ,表头名都叫什么?                          

通过“ER关系模型” 来创建 (根据存储实体来创建)

比如:创建1张表 用来存储学生信息  (学生就是要存储的实体)

想想要存储学生的哪些信息,就创建对应个数的表头名

再比如创建存储员工信息的表:

想想表里应该创建几个表头 表头名都叫什么 

姓名  工号   部门  岗位   电话   邮箱  家庭地址  工资

第二个问题:表建好了, 如何断定表创建的是否合理?

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

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

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

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

发现创建的表 ,建的不合理  就是可以修改表:

命令格式: alter  table  库名.表名   操作命令;

可以做哪些修改呢?(操作命令)

添加新表头   add         

删除表头   drop          

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

修改表头名  change 

修改表名  rename 

mysql> alter table studb.stu rename studb.stuinfo;  #修改表名 
mysql> alter table studb.stuinfo drop age ;  #删除字段
mysql> use studb;
Database changed
mysql> show tables;
+-----------------+
| Tables_in_studb |
+-----------------+
| stuinfo         |
+-----------------+
1 row in set (0.00 sec)
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 rows in set (0.00 sec)
mysql> alter table studb.stuinfo add  mail  char(30) ; #添加在末尾

添加在指定位置 

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    |       |
+--------+----------+------+-----+---------+-------+
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 |       |
+--------+-------------+------+-----+-------------+-------+
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        |       |
| school     | char(10)    | YES  |     | NULL        |       |
| class_name | char(9)     | YES  |     | NULL        |       |
| gender     | char(4)     | YES  |     | NULL        |       |
| mail       | varchar(50) | NO   |     | plj@tedu.cn |       |
+------------+-------------+------+-----+-------------+-------+
mysql> alter table  studb.stuinfo  drop school , drop 班级 ,drop email ;   # 一起删除多个表头
Query OK, 0 rows affected (0.59 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc  studb.stuinfo; #查看 
+--------+----------+------+-----+---------+-------+
| Field  | Type     | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| number | char(9)  | YES  |     | NULL    |       |
| name   | char(10) | YES  |     | NULL    |       |
| gender | char(4)  | YES  |     | NULL    |       |
| age    | int(11)  | YES  |     | NULL    |       |
+--------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql>

使用modify  修改表头的位置  

mysql> alter  table studb.stuinfo  modify age int  after name ;   
Query OK, 0 rows affected (0.47 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc studb.stuinfo;
+--------+----------+------+-----+---------+-------+
| Field  | Type     | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| number | char(9)  | YES  |     | NULL    |       |
| name   | char(10) | YES  |     | NULL    |       |
| age    | int(11)  | YES  |     | NULL    |       |
| gender | char(4)  | YES  |     | NULL    |       |
+--------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)

复制表  (拷贝已有的表  类型系统命令 cp  的功能 )

仅仅复制表头命令格式:(只复制表结构)

表头和数据复制都复制的命令格式

复制表  (拷贝已有的表  和系统命令 cp  的功能一样 )  作用: 备份已有的表

表头和数据复制都复制的 命令格式create  table  库名.表名   select  *  from  库名.表名 ;

例子:拷贝 tarena库下的user表到   studb库里 表名不变

mysql> use studb;
mysql> show tables;
+-----------------+
| Tables_in_studb |
+-----------------+
| stuinfo         |
+-----------------+
1 row in set (0.00 sec)
mysql> create table studb.user select  * from tarena.user;
mysql> show tables;
+-----------------+
| Tables_in_studb |
+-----------------+
| stuinfo         |
| user            |
+-----------------+
mysql> select  * from studb.user; #查看数据了

仅仅复制表头命令格式:(只复制表结构)

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

例子

mysql> use studb;
Database changed
mysql> show tables;
+-----------------+
| Tables_in_studb |
+-----------------+
| stuinfo         |
| user            |
+-----------------+
2 rows in set (0.00 sec)
mysql> create table studb.user2  like tarena.user;  #仅仅复制表头
Query OK, 0 rows affected (0.24 sec)
mysql> show tables;
+-----------------+
| Tables_in_studb |
+-----------------+
| stuinfo         |
| user            |
| user2           |
+-----------------+
3 rows in set (0.00 sec)
mysql> select  * from studb.user2;
Empty set (0.00 sec)
mysql> desc studb.user2;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int(11)     | NO   | PRI | NULL    | auto_increment |
| name     | char(20)    | YES  |     | NULL    |                |
| password | char(1)     | YES  |     | NULL    |                |
| uid      | int(11)     | YES  |     | NULL    |                |
| gid      | int(11)     | YES  |     | NULL    |                |
| comment  | varchar(50) | YES  |     | NULL    |                |
| homedir  | varchar(80) | YES  |     | NULL    |                |
| shell    | char(30)    | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)
mysql>

管理表记录 : 对表中存储的行做管理 ,操作包括  查看select   插入 insert into   更新 update  删除 delete 

用来做练习的表结构

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_name | char(9)     | YES  |     | NULL        |       |
| gender     | char(4)     | YES  |     | NULL        |       |
| mail       | varchar(50) | NO   |     | plj@tedu.cn |       |
+------------+-------------+------+-----+-------------+-------+
6 rows in set (0.00 sec)

查询  : 查看表中的行  使用select 

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

插入  : 向表里添加新行 使用 insert  into 

第一种情况:不指定列名插入记录(必须给所有列赋值)

1.1 INSERT   INTO    库名.表名  VALUES (值列表);  //插入1行

mysql> insert into studb.stuinfo values ("1","jim","tarena","nsd2108","boy","jim@tedu.cn");

1.2 INSERT   INTO    库名.表名  VALUES (值列表),(值列表),...;  //插入多行

mysql> insert into studb.stuinfo 
values 
("2","lucy","tarena","nsd2109","girl","lucy@163.com"),
("3","john","tarena","nsd2111","boy","john@163.com"),
("4","lili","tarena","nsd2109","girl","lili@163.com");

注意:值的顺序必须和表中列的顺序一致,且数据类型要匹配

mysql> select  * from studb.stuinfo;
+--------+------+--------+------------+--------+--------------+
| number | name | school | class_name | gender | mail         |
+--------+------+--------+------------+--------+--------------+
| 1      | jim  | tarena | nsd2108    | boy    | jim@tedu.cn  |
| 2      | lucy | tarena | nsd2109    | girl   | lucy@163.com |
| 3      | john | tarena | nsd2111    | boy    | john@163.com |
| 4      | lili | tarena | nsd2109    | girl   | lili@163.com |
+--------+------+--------+------------+--------+--------------+
4 rows in set (0.00 sec)

第二种情况:指定列名插入记录(仅须给指定列赋值)

2.1 INSERT   INTO  库名.表名(列名列表)  VALUES (值列表);  只插入1条记录 

insert into  studb.stuinfo(number,name) values ("5","alices");

2,2 INSERT   INTO 库名.表名(列名列表)   VALUES (值列表),(值列表),...;   插入多条记录

insert into  studb.stuinfo(number,name) values ("6","bob"),("7","lilei"),("8","hmm");
mysql> select  * from studb.stuinfo;
+--------+--------+--------+------------+--------+--------------+
| number | name   | school | class_name | gender | mail         |
+--------+--------+--------+------------+--------+--------------+
| 1      | jim    | tarena | nsd2108    | boy    | jim@tedu.cn  |
| 2      | lucy   | tarena | nsd2109    | girl   | lucy@163.com |
| 3      | john   | tarena | nsd2111    | boy    | john@163.com |
| 4      | lili   | tarena | nsd2109    | girl   | lili@163.com |
| 5      | alices | NULL   | NULL       | NULL   | plj@tedu.cn  |
| 6      | bob    | NULL   | NULL       | NULL   | plj@tedu.cn  |
| 7      | lilei  | NULL   | NULL       | NULL   | plj@tedu.cn  |
| 8      | hmm    | NULL   | NULL       | NULL   | plj@tedu.cn  |
+--------+--------+--------+------------+--------+--------------+
8 rows in set (0.00 sec)

注意: 列和值的顺序要一致 ; 列名先后顺序不重要 ;没有赋值的列使用默认值、自增长结果赋值。

第三种情况: 除了insert into 之外的其他插入记录方式

3.1 select 查询结果插入数据  注意:值的顺序必须和表中列的顺序一致,且数据类型要匹配

mysql> insert into studb.stuinfo (name , gender ) (select name , gender from studb.stuinfo where number in (1,3));
mysql> select  * from studb.stuinfo;
+--------+--------+--------+------------+--------+--------------+
| number | name   | school | class_name | gender | mail         |
+--------+--------+--------+------------+--------+--------------+
| 1      | jim    | tarena | nsd2108    | boy    | jim@tedu.cn  |
| 2      | lucy   | tarena | nsd2109    | girl   | lucy@163.com |
| 3      | john   | tarena | nsd2111    | boy    | john@163.com |
| 4      | lili   | tarena | nsd2109    | girl   | lili@163.com |
| 5      | alices | NULL   | NULL       | NULL   | plj@tedu.cn  |
| 6      | bob    | NULL   | NULL       | NULL   | plj@tedu.cn  |
| 7      | lilei  | NULL   | NULL       | NULL   | plj@tedu.cn  |
| 8      | hmm    | NULL   | NULL       | NULL   | plj@tedu.cn  |
| NULL   | jim    | NULL   | NULL       | boy    | plj@tedu.cn  |
| NULL   | john   | NULL   | NULL       | boy    | plj@tedu.cn  |
+--------+--------+--------+------------+--------+--------------+
10 rows in set (0.00 sec)

3.2  set 命令插入数据  

注意 值要和字段的数据类型匹配

格式: mysql> insert into 库.表 set  字段名=值 , 字段名=值 , 字段名=值 ;

mysql> insert into studb.stuinfo set  name="nb" , gender="boy" , mail="nb@tedu.cn" ;
mysql> select  * from studb.stuinfo;
+--------+--------+--------+------------+--------+--------------+
| number | name   | school | class_name | gender | mail         |
+--------+--------+--------+------------+--------+--------------+
| 1      | jim    | tarena | nsd2108    | boy    | jim@tedu.cn  |
| 2      | lucy   | tarena | nsd2109    | girl   | lucy@163.com |
| 3      | john   | tarena | nsd2111    | boy    | john@163.com |
| 4      | lili   | tarena | nsd2109    | girl   | lili@163.com |
| 5      | alices | NULL   | NULL       | NULL   | plj@tedu.cn  |
| 6      | bob    | NULL   | NULL       | NULL   | plj@tedu.cn  |
| 7      | lilei  | NULL   | NULL       | NULL   | plj@tedu.cn  |
| 8      | hmm    | NULL   | NULL       | NULL   | plj@tedu.cn  |
| NULL   | jim    | NULL   | NULL       | boy    | plj@tedu.cn  |
| NULL   | john   | NULL   | NULL       | boy    | plj@tedu.cn  |
| NULL   | nb     | NULL   | NULL       | boy    | nb@tedu.cn   |
+--------+--------+--------+------------+--------+--------------+
11 rows in set (0.00 sec)

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

批量修改  (不加筛选条件修改) 

命令格式: update  库名.表名   set  字段名 =  值 ,  字段名 =  值 ;

mysql> update studb.stuinfo set gender="no" , mail="student@163.com" ;
mysql> select  * from studb.stuinfo;
+--------+--------+--------+------------+--------+-----------------+
| number | name   | school | class_name | gender | mail            |
+--------+--------+--------+------------+--------+-----------------+
| 1      | jim    | tarena | nsd2108    | no     | student@163.com |
| 2      | lucy   | tarena | nsd2109    | no     | student@163.com |
| 3      | john   | tarena | nsd2111    | no     | student@163.com |
| 4      | lili   | tarena | nsd2109    | no     | student@163.com |
| 5      | alices | NULL   | NULL       | no     | student@163.com |
| 6      | bob    | NULL   | NULL       | no     | student@163.com |
| 7      | lilei  | NULL   | NULL       | no     | student@163.com |
| 8      | hmm    | NULL   | NULL       | no     | student@163.com |
| NULL   | jim    | NULL   | NULL       | no     | student@163.com |
| NULL   | john   | NULL   | NULL       | no     | student@163.com |
| NULL   | nb     | NULL   | NULL       | no     | student@163.com |
+--------+--------+--------+------------+--------+-----------------+
11 rows in set (0.00 sec)

 仅修改与筛选条件匹配的字段 (修改时加筛选条件) 

命令格式 :update  库名.表名   set  字段名=值,字段名=值 where  筛选条件 ; 

mysql> update studb.stuinfo set school="tarena" , class_name="nsd2108" where number=5;
mysql> select  * from studb.stuinfo;
+--------+--------+--------+------------+--------+-----------------+
| number | name   | school | class_name | gender | mail            |
+--------+--------+--------+------------+--------+-----------------+
| 1      | jim    | tarena | nsd2108    | no     | student@163.com |
| 2      | lucy   | tarena | nsd2109    | no     | student@163.com |
| 3      | john   | tarena | nsd2111    | no     | student@163.com |
| 4      | lili   | tarena | nsd2109    | no     | student@163.com |
| 5      | alices | tarena | nsd2108    | no     | student@163.com |
| 6      | bob    | NULL   | NULL       | no     | student@163.com |
| 7      | lilei  | NULL   | NULL       | no     | student@163.com |
| 8      | hmm    | NULL   | NULL       | no     | student@163.com |
| NULL   | jim    | NULL   | NULL       | no     | student@163.com |
| NULL   | john   | NULL   | NULL       | no     | student@163.com |
| NULL   | nb     | NULL   | NULL       | no     | student@163.com |
+--------+--------+--------+------------+--------+-----------------+
11 rows in set (0.00 sec)
mysql> update studb.stuinfo set class_name="nsd2108" where class_name is null ;  #使用空 做修改条件
mysql> select  * from studb.stuinfo; #查看修改结果
+--------+--------+--------+------------+--------+-----------------+
| number | name   | school | class_name | gender | mail            |
+--------+--------+--------+------------+--------+-----------------+
| 1      | jim    | tarena | nsd2108    | no     | student@163.com |
| 2      | lucy   | tarena | nsd2109    | no     | student@163.com |
| 3      | john   | tarena | nsd2111    | no     | student@163.com |
| 4      | lili   | tarena | nsd2109    | no     | student@163.com |
| 5      | alices | tarena | nsd2108    | no     | student@163.com |
| 6      | bob    | NULL   | nsd2108    | no     | student@163.com |
| 7      | lilei  | NULL   | nsd2108    | no     | student@163.com |
| 8      | hmm    | NULL   | nsd2108    | no     | student@163.com |
| NULL   | jim    | NULL   | nsd2108    | no     | student@163.com |
| NULL   | john   | NULL   | nsd2108    | no     | student@163.com |
| NULL   | nb     | NULL   | nsd2108    | no     | student@163.com |
+--------+--------+--------+------------+--------+-----------------+
11 rows in set (0.00 sec)

删除  : 删除表中行  使用delete 

仅删除符合条件的行   (删除命令有筛选条件)  delete   from  库.表  where  筛选条件;

删除表里的所有行(删除命令有筛选条件)       delete   from  库.表;

mysql> delete from studb.stuinfo where number is null ;   #加条件删除
Query OK, 3 rows affected (0.02 sec)
mysql> select  * from studb.stuinfo;#查看
+--------+--------+--------+------------+--------+-----------------+
| number | name   | school | class_name | gender | mail            |
+--------+--------+--------+------------+--------+-----------------+
| 1      | jim    | tarena | nsd2108    | no     | student@163.com |
| 2      | lucy   | tarena | nsd2109    | no     | student@163.com |
| 3      | john   | tarena | nsd2111    | no     | student@163.com |
| 4      | lili   | tarena | nsd2109    | no     | student@163.com |
| 5      | alices | tarena | nsd2108    | no     | student@163.com |
| 6      | bob    | NULL   | nsd2108    | no     | student@163.com |
| 7      | lilei  | NULL   | nsd2108    | no     | student@163.com |
| 8      | hmm    | NULL   | nsd2108    | no     | student@163.com |
+--------+--------+--------+------------+--------+-----------------+
8 rows in set (0.00 sec)
mysql> delete from studb.stuinfo;  #没加条件
Query OK, 8 rows affected (0.06 sec)
mysql> select  * from studb.stuinfo;  #查看不到记录
Empty set (0.00 sec)

注意 也可以使用  truncate  table  #库.表; 删除表里的记录                 

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

- TRUNCATE不支持WHERE条件

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

- TRUNCATE不能回滚,DELETE可以

- 效率略高于DELETE

案例2:mysql数据类型

数据类型分类:数值类型   字符类型   日期时间类型   枚举类型

每种类型都有对应的关键字表示 和具体的存储范围 及 存储空间

比如存储: 身高 、 体重  、 工资  、  奖金 适合使用数值类型 

比如存储: 姓名 、  家庭地址 、 收货地址   适合使用字符类型 

比如存储: 生日 、  出生年份 、 入职时间  、  下班时间 、 注册时间   适合使用日期时间

比如存储: 爱好 、  性别 、 社保医院   适合使用枚举类型

数值类型   :表头下可以存储数据   

整数类型(能存储正整数和负整数的类型) 例如  23(正整数)    -23(负整数)

每种整数类型分为无符号存储范围和有符号存储范围 

无符号存储范围起始数字从 数字零开始

有符号存储范围  就是可以存储负数

使用unsigned 命令定义使用数值类型的无符号存储范围

整数类型不存储小数,如果数字有小数的话会把小数部分四舍五入后只保存整数部分

浮点类型(能存储小数的类型)  例如  23.22 

level 游戏级别   money   游戏币的钱

mysql> create table  studb.t1(level  tinyint unsigned ,  money  double  );   

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.01 sec)
mysql> insert into studb.t1  values (80,88);   #在存储的范围内
mysql> insert into studb.t1  values (301,1.292);  #超出字段level 的存储范围报错
ERROR 1264 (22003): Out of range value for column 'level' at row 
mysql> insert into studb.t1  values (255,1.292);  #在存储范围内
Query OK, 1 row affected (0.02 sec)
mysql> insert into studb.t1  values (1.292,6.78);   #整数类型 不存储小数位 
Query OK, 1 row affected (0.03 sec)
mysql> select  * from studb.t1;
+-------+-------+
| level | money |
+-------+-------+
|    80 |    88 |
|   255 | 1.292 |
|     1 |  6.78 |
+-------+-------+

查看服务自带的表的表结构使用的数据类型 (可以看明白)

mysql> desc mysql.user;  

  max_questions            | int(11) unsigned                

  max_updates              | int(11) unsigned                  

  max_connections          | int(11) unsigned                  

  max_user_connections     | int(11) unsigned 

  password_lifetime        | smallint(5) unsigned 

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

char(字符个数)    定长字符类型(固定长度)     存储范围 1-255字符

varchar(字符个数) 变长      (长度不固定)     存储范围 1-65535字符 (有效存储范围到65532)

一个英文字母 是一个字符

一个汉字也是一个字符

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) );
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.02 sec)
mysql> insert into studb.t2 values ("ab","ab");
Query OK, 1 row affected (0.04 sec)
mysql> insert into studb.t2 values ("abc","abc");
Query OK, 1 row affected (0.01 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","abcd");
Query OK, 1 row affected (0.05 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  | abcd    |
+------+---------+
4 rows in set (0.00 sec)

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

#查看表使用的字符集

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  

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)

修改的表使用的字符集    alter  table   库名.表   DEFAULT CHARSET=utf8 ;

mysql> create  database  if  not  exists  db1;
mysql> create table   db1.t5(id int,name char(10)); #没指定字符集 默认是latin1 
mysql> show  create table   db1.t5   \G  #查看 
*************************** 1. row ***************************
Table: t5
Create Table: CREATE TABLE `t5` (
  `id` int(11) DEFAULT NULL,
  `name` char(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> alter table db1.t5 DEFAULT CHARSET utf8;   #把表使用的字符集修改为utf8
mysql> show  create table   db1.t5 \G  #再次查看
*************************** 1. row ***************************
       Table: t5
Create Table: CREATE TABLE `t5` (
  `id` int(11) DEFAULT NULL,
  `name` char(10) CHARACTER SET latin1 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

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

单选    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.21 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 ("小包总","男","美女,睡,金钱");
ERROR 1265 (01000): Data truncated for column '爱好' at row 1
mysql> 
mysql> 
mysql> insert into studb.t8 values ("丫丫","女","帅哥,吃");
Query OK, 1 row affected (0.02 sec)
mysql> select  * from studb.t8;
+--------+--------+------------+
| 姓名   | 性别   | 爱好       |
+--------+--------+------------+
| 丫丫   | 女     | 帅哥,吃    |
+--------+--------+------------+
1 row in set (0.00 sec)
mysql>

日期时间类型  表头存储与日期时间格式的数据

年        year       YYYY     2021

日期    date        YYYYMMDD    20211104

时间    time       HHMMSS         143358        

日期时间(既有日期又有时间)  

datetime   或  timestamp       YYYYMMDDHHMMSS     20211104143648

mysql> create table studb.t6( 姓名  char(10), 生日  date , 出生年份 year , 家庭聚会  datetime , 

聚会地点  varchar(15), 上班时间 time)default charset utf8;

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 ("翠华",20211120,1990,20220101183000,"天坛校区",090000);

 使用2位数给year的表头赋值也是可以的  但会自动补全4位数

 01-69  之间的数字使用20补全4位数的年 2001~2069

 70-99  之间的数字使用19补全4位数的年 1970~1999

mysql> insert into studb.t6(姓名,出生年份) values ("狗剩",53),("铁头娃",81);
Query OK, 2 rows affected (0.04 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> select  * from  studb.t6;
+-----------+------------+--------------+---------------------+--------------+--------------+
| 姓名      | 生日       | 出生年份     | 家庭聚会            | 聚会地点     | 上班时间     |
+-----------+------------+--------------+---------------------+--------------+--------------+
| 翠华      | 2021-11-20 |         1990 | 2022-01-01 18:30:00 | 天坛校区     | 09:00:00     |
| 狗剩      | NULL       |         2053 | NULL                | NULL         | NULL         |
| 铁头娃    | NULL       |         1981 | NULL                | NULL         | NULL         |
+-----------+------------+--------------+---------------------+--------------+--------------+
3 rows in set (0.00 sec)
mysql>

datetime   与  timestamp  的区别?

第一个区别是存储范围不一样 (datetime存储范围大  timestamp范围小)

存储数据的方式不一样 , 怎么个不用看演示:

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    # 使用 system 可以在mysql 提示符下执行 操作系统命令   
2021年 11月 05日 星期五 15:24:12 CST

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

datetime类型         不赋值  使用null  赋值 

mysql> system date
2021年 12月 08日 星期三 09:46:45 CST
mysql> insert into studb.t7(聚会时间) values (20211224223000);
mysql> insert into studb.t7(开会时间) values (20220101093000);
mysql> select  * from  studb.t7;
+---------------------+---------------------+
| 聚会时间            | 开会时间            |
+---------------------+---------------------+
| 2021-12-24 22:30:00 | 2021-12-08 09:47:55 |
| NULL                | 2022-01-01 09:30:00 |
+---------------------+---------------------+
2 rows in set (0.00 sec)
mysql>

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

案例3:数据批量处理  (一次性向表里存储很多数据或一次性把表里的数据都取出来)

注意:数据导入或导出 存放数据的文件必须在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 -p123qqq...B
mysql> show variables like  "secure_file_priv";
+------------------+----------+
| Variable_name    | Value      |
+------------------+----------+
| secure_file_priv   | /myload/ |
+------------------+----------+
1 row in set (0.00 sec)
mysql>

数据导入(一次性向表里存储很多数据)把系统文件的内容存储到数据库服务的表里

文件的内容要规律

诉求:将/etc/passwd文件导入db1库的t3表里 

数据导入格式:

mysql>  load   data  infile   "/检索目录/文件名"   into    table    库名.表名   

fields  terminated by  "文件中列的间隔符号"   lines   terminated by   "\n"  ; 

数据导入的操作步骤:

1) 要创建存储数据库(如果没有的话)

2)  建表 (根据导入文件的内容 创建 表头名  表头个数  表头数据类型  根据文件内容定义)

3) 把系统文件拷贝的检索目录里

4 )  数据库管理执行导入数据的命令

5) 查看数据

mysql> create database if not exists db1; 
mysql> drop  table db1.t3; #删除重名的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) );
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> 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" ;
mysql> select * from db1.t3 ;

数据导出(一次性把表里的数据都取出来)把数据库服务的表里数据保存到系统文件里

注意导出的数据不包括表头名 ,只有表里行。存放导出数据的文件名 不需要事先创建

且具有唯一。

数据导出命令格式:

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行

mysql> system ls /myload
passwd
mysql> select * from db1.t3  where  uid <= 10   into  outfile  "/myload/a.txt";
Query OK, 9 rows affected (0.00 sec)
mysql> system ls /myload
a.txt  passwd
mysql> 
mysql> system cat  /myload/a.txt ;
root    x       0       0       root    /root   /bin/bash
bin      x       1       1       bin     /bin    /sbin/nologin
daemon  x       2       2       daemon  /sbin   /sbin/nologin
adm     x       3       4       adm     /var/adm        /sbin/nologin
lp      x       4       7       lp      /var/spool/lpd  /sbin/nologin
sync    x       5       0       sync    /sbin   /bin/sync
shutdown        x       6       0       shutdown        /sbin   /sbin/shutdown
halt    x       7       0       halt    /sbin   /sbin/halt
mail    x       8       12      mail    /var/spool/mail /sbin/nologin
mysql> 
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
mysql> 
mysql> select name , homedir , uid  from db1.t3  where  uid <= 10  into outfile "/myload/c.txt"
fields  terminated by ":"   lines  terminated by "!!!" ;
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>
表结构 、 MySQL键值  您阅读本篇文章共花了: 

分享到:

    扫描二维码推送至手机访问。

    版权声明:本文由有云转晴发布,如需转载请注明出处。

    本文链接:https://yyzq.cf/?id=138

    分享给朋友:

    发表评论

    访客

    ◎欢迎参与讨论,请在这里发表您的看法和观点。