MYSQL学习笔记
day04
表
create table 表名(
字段名1 数据类型,
字段名2 数据类型,
字段名3 数据类型
);
表名：建议以t_ 或者tbl_ 开始，可读性强，见名知意。
字段名：见名知意。
表名和字段名都属于标识符。
关于mysql中的数据类型？
varchar: 可变长度字符（最长255）
- 比如name字段 varchar(10) 当存储数据为jack时，只分配4个长度的空间，根据实际传递过来的数据长度动态分配存储空间。
- 优点：比较智能，节省空间。
- 缺点：需要动态分配空间，速度慢。
char：定长字符串（最长255）
- 不管实际的数据长度是多少，分配固定长度的空间去存储数据。
- 缺点：使用不恰当的时候，可能会导致空间的浪费。
- 优点：不需要动态分配空间，速度快。
char和varchar我们应该怎么选择？
例如：性别字段选择char，因为它是固定长度字符串，姓名字段选择varchar，名字长度不一样。
int： 整数 最长默认是11
bigint长整数 (等同于java中的long)
float：单精度浮点型数据
double： 双精度浮点型数据
date： 短日期
datetime： 长日期
clob：字符大对象
- 最多可以存储4G的字符串。比如：存储一篇文章，存储一个说明。
- 超过255个字符的都要采用clob字符大对象来存储，Character Large OBject：clob
blob：二进制大对象
- 专门用来存储图片、声音、视频等流媒体数据。Binary Large OBject。
- 往BLOB类型字段上插入数据的时候，例如插入一个图片、视频等，需要使用IO流才行。
create table t_student(
no int,
name varchar(32),
sex char(1),
age int(3),
email varchar(32)
);
删除表：drop table ( if exists) 表名;
插入数据insert（DML）
insert into 表名(字段名1,字段名2,字段名3...) values (值1,值2,值3...);
字段名与值要一一对应，数量和数据类型要对应。
insert into t_student(no,name,sex,age,email) values(1,'zhangsan','m',12,'zhangsan@123.com');
//交换顺序也可以
insert into t_student(email,age,sex,no,name) values('lisi@123.com',20,'f',2,'lisi');
insert语句但凡是执行成功了，那么必然会多一条记录。如果没有给其他字段指定值的话，默认值是NULL。不过，我们也可以指定默认值。
例如：将性别默认为男‘m’
create table t_student(
no int,
name varchar(32),
sex char(1) default 'm',
age int(3),
email varchar(32)
);
insert语句中的“字段名”可以省略。但是值要全部写上并且要按顺序
insert into t_student values(3,'liuliu','f',22,'liuliu@123.com');
insert插入日期
①format：数字格式化
将薪资加入千分位
select ename,format(sal,'$999,999')as sal from emp;
②str_to_date:
将字符串varchar类型转化为日期date类型。通常使用在插入insert方面，因为插入的时候需要一个日期类型的数据，需要通过该函数将字符串转换为date。
create table t_user(
id int,
name varchar(32),
birth date
);
插入一条数据
insert into t_user(id,name,birth) values(1,'zhangsan',str_to_date('22-04-2001','%d-%m-%Y'));
mysql的日期格式：
%Y 年
%m 月
%d 日
%h 时
%i 分
%s 秒
java中的日期格式：yyyy-MM-dd HH:mm:ss SSS
如果提供的字符串是“%Y-%m-%d”这个格式，str_to_date函数就可以省略,字符串会自动转为date日期格式
insert into t_user(id,name,birth) values(1,'zhangsan','2001-10-23');
数据库中的命名规范：所有标识符都是全部小写，单词和单词之间使用下划线进行衔接。
date_format:将日期类型date转化为具有一定格式的varchar字符串类型
select id,name,date_format(birth,'%m/%d%Y') as birth from t_user;
date_format格式：date_format(日期类型数据，‘日期格式’)
这个函数通常使用在查询日期方面，设置展示的日期格式。
select id,name, birth from t_user;
以上的SQL语句实际上进行了默认的日期格式化，自动将数据库中的date类型转换为varchar类型，采用的日期格式是‘%Y-%m-%d’
date和datetime两个类型的区别
date是短日期：包括年月日信息。
datetime是长日期：包括年月日时分秒信息。
drop table if exists t_user;
create table t_user(
id int,
name varchar(32),
birth date,
create_time datetime
);
mysql中短日期默认格式：%Y-%m-%d
mysql中长日期默认格式：%Y-%m-%d %h:%i:%s
insert into t_user(id,name,birth,create_time) values(1,'zhangsan','1990-01-01','2020-04-22 12:23:33');
mysql当中怎么获取系统当前时间？
now( )函数，并且获取的时间带有：时分秒信息！是datetime类型的
insert into t_user(id,name,birth,create_time) values(2,'lisi','1999-09-26',now());
修改update（DML）
update 表名 set 字段名1 = 值1, 字段名2 = 值2, 字段名3 = 值3 ... where 条件;
注意：没有条件限制会导致所有数据全部更新。
update t_user set name = 'jack', birth = '2020-09-26' where id = 2;
删除delete（DML）
delete from 表名 where 条件;
注意：如果没有where条件，整张表的数据会全部删除！
insert语句插入多条记录
insert into t_user(id,name,birth,create_time) values(3,'liuliu','2001-10-23',now()),(4,'qiqi','2020-08-03',now());
快速创建表？（了解）
create table emp2 as select * from emp;
create table mytable as select empno,ename from emp where job = 'MANAGER';
原理：将一个查询结果当做一张表新建！这个可以完成表的快速复制，表创建出来，同时表中数据也存在了。
将查询结果插入一张表中？(了解)
create table dept_bak as select * from dept;
insert into dept_bak select * from dept;
快速删除表中数据？
delete from dept_bak;
delete语句删除数据的原理：(DML)
表中的数据被删除了，但是这个数据在硬盘上的真实存储空间不会被释放。
缺点：删除效率比较低。
优点：支持回滚，后悔了可以再恢复数据。（start transaction delete rollback）
truncate语句删除数据的原理？(DDL)
truncate table dept_bak;
这种删除效率比较高，表被一次截断，物理删除。
缺点：不支持回滚
优点：快速
当表非常大，上亿条记录，删除的时候使用delete，需要1小时，效率低，可以选择truncate删除表中数据，只需1秒钟，但是使用truncate之前，必须仔细询问客户是否真的要删除，并警告删除之后不可恢复。
删除表操作？
drop table 表名;
这不是删除数据，这是把表删除了。
对表结构的修改
比如：添加一个字段、删除一个字段、修改一个字段。对表结构的修改需要使用：alter、drop、create
- 在实际开发中，需要一旦确定好之后，很少进行表结构的修改，因为在开发中的时候，修改表结构，成本比较高，对应的java代码需要进行大量的修改。这是责任是设计人员来承担。
- 由于修改表结构的操作很少，所以我们不需要掌握，如果真的要修改表结构，可以使用工具
约束（constraint）
在创建表的时候，可以给表中的字段加上一些约束，来保证这个表中数据的完整性、有效性。
约束的作用：保证表中数据有效！！
约束包括那些？
- 非空约束： not null
- 唯一性约束：unique（不能重复，但是可以为null）
- 主键约束：primary key (简称PK)
- 外键约束：foreign key（简称FK）
- 检查约束：check（mysql不支持，oracle支持）
xx.sql这种文件被称为SQL脚本文件，sql脚本文件中编写了大量的SQL语句，当用source + 绝对路径执行sql脚本文件时，该文件中所有的SQL语句会全部执行。当SQL脚本文件中含有大量的SQL语句时，文件内存很大，不建议使用复制粘贴，建议使用source+文件路径。
新需求：name和email两个字段联合起来具有唯一性。(即联合起来不重复)
例如：
insert into t_vip(id,name,email) vaules(1,'zhangsan','zhangsan@123.com');
insert into t_vip(id,name,email) vaules(1,'zhangsan','zhangsan@sina.com');
create table t_vip(
id int,
name varchar(255),
email varchar(255),
unique(name,email)
);
约束直接添加到列后面，叫做列级约束。
约束没有添加到列的后面，这种约束被称为表级约束，当需要给多个字段联合起来添加某一个约束的时候，需要使用表级约束。
not null 只有列级约束，没有表级约束
not null 和unique可以联合吗？
可以。在mysql中，如果一个字段同时被not null unique约束的话，该字段自动变成主键字段（PRI）（Oracle不一样）
主键约束（primary key 简称PK）
- 主键约束：就是一种约束
- 主键字段：该字段上添加了主键约束
- 主键值：主键字段的每一个值
主键的作用：主键值是每一行记录的唯一标识
任何一张表都应该有主键，没有主键，表无效。
主键的特征：not null + unique (主键值不能为null，同时也不能重复)
可以使用表级约束
drop table if exists t_vip;
create table t_vip(
id int,
name varchar(255),
primary key(id)
);
一个字段做主键叫做单一主键，两个字段联合起来做主键叫做复合主键。
在事件开发中不建议使用复合主键，建议使用单一主键。因为主键值存在的意义就是这行记录的身份证号，只要意义达到即可，单一主键可以做到，复合主键比较复杂，不建议使用。
一张表主键约束只能添加一个。
主键值建议使用：int、bigint、char等类型。不建议使用varchar来做主键，主键值一般都是数字，都是定长的。
主键除了单一主键和复合主键这样分类外，还可以这样进行分类
- 自然主键：主键值是一个自然数，和业务没关系
- 业务主键：主键值和业务紧密关联，例如用银行卡账号做主键值
在实际开发中，使用业务主键多，还是使用自然主键多？
自然主键使用比较多，因为主键只要做到不重复就行，不需要有意义。业务主键不好，因为主键一旦和业务挂钩，那么当业务发生变动的时候，可能会影响到主键值。所以业务主键不建议使用，尽量使用自然主键
auto_increment 可以使该字段自增，从1开始
外键约束（foreign key 简称FK）
业务背景：设计数据库表，来描述“班级和学生”的信息
第一种方案：班级和学生存储在一张表中
| id | name | classno | classname |
|---|---|---|---|
| 1 | jack | 100 | 高三1班 |
| 2 | lucy | 100 | 高三1班 |
| 3 | lilei | 100 | 高三1班 |
| 4 | zhangsan | 101 | 高三2班 |
| 5 | lisi | 101 | 高三2班 |
| 6 | wangwu | 101 | 高三2班 |
以上方案的缺点：数据冗余，空间浪费
第二种方案：学生一张表、班级一张表
| classno | classname | ||
|---|---|---|---|
| 100 | 高三1班 | ||
| 101 | 高三2班 |
| id | name | con(班级编号FK引用学生表的classno) |
|---|---|---|
| 1 | jack | 100 |
| 2 | lucy | 100 |
| 3 | lilei | 100 |
| 4 | zhangsan | 101 |
| 5 | lisi | 101 |
| 6 | wangwu | 101 |
当cno字段没有任何约束的时候，可能会导致数据无效，可能出现一个102，但是102在班级表中不存在，所以为了保证cno字段中的值都是100和101，需要给cno字段添加外键约束。
引用外键在的表为子表（学生表），被引用外键的表为父表（班级表）
- 删除表的顺序：先删子，在删父
- 创建表的顺序：先创建父，在创建子
- 删除数据的顺序:先删子，在删父
- 插入数据的顺序：先插入父，在插入子
create table t_class(
classno int primary key,
classname varchar(255)
);
create table t_student(
no int primary key auto_increment,
name varchar(255),
cno int,
foreign key(cno) references t_class(classsno)
);
思考：子表中的外键引用父表中的某个字段，被引用的这个字段必须是主键吗？
不一定是主键，但至少具有unique约束。
测试：外键可以为NULL吗？外键值可以为NULL
946
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
