表结构 、 MySQL键值
案例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>
