MySQL学习（五）
补充增删改查
insert语句可以一次插入多条记录吗？【掌握】
可以
insert into t_user(id,name,birth,create_time) values
(1,'zs','1980-10-11',now()),
(2,'lisi','1981-10-11',now()),
(3,'wangwu','1982-10-11',now());
语法：insert into t_user(字段名1,字段名2) values(),(),(),();
快速创建表【了解内容】
create table emp2 as select * from emp;
原理：
将一个查询结果当做一张表新建！！！！！
这个可以完成表的快速复制！！！！
表创建出来，同时表中的数据也存在了！！！
create table mytable as select empno,ename from emp where job = 'MANAGER';
将查询结果插入到一张表当中？insert相关的！！！【了解内容】
create table dept_bak as select * from dept;
select * from dept_bak;
insert into dept_bak select * from dept; #很少用！
快速删除表中的数据
delete from dept_bak; #这种删除数据的方式比较慢。
delete语句删除数据的原理？（delete属于DML语句！！！）
表中的数据被删除了，但是这个数据在硬盘上的真实存储空间不会被释放！！！
这种删除缺点是：删除效率比较低。
这种删除优点是：支持回滚，后悔了可以再恢复数据！！！
truncate table dept_bak;
truncate语句删除数据的原理？（这种操作属于DDL操作。）
这种删除效率比较高，表被一次截断，物理删除。
这种删除缺点：不支持回滚。
这种删除优点：快速。
【truncate比较重要，必须掌握】
如果有上亿条记录的大表
删除的时候，使用delete，也许需要执行1个小时才能删除完！效率较低。
可以选择使用truncate删除表中的数据。只需要不到1秒钟的时间就删除结束。效率较高。
但是使用truncate之前，必须仔细询问客户是否真的要删除，并警告删除之后不可恢复！
truncate是删除表中的数据，表还在！
删除表操作
drop table 表名;
这不是删除表中的数据，这是把表删除。
表结构的增删改
对表结构的修改:添加一个字段，删除一个字段，修改一个字段！！！
对表结构的修改需要使用：alter（属于DDL语句）
DDL包括：create drop alter
第一：在实际的开发中，需求一旦确定之后，表一旦设计好之后，很少的
进行表结构的修改。因为开发进行中的时候，修改表结构，成本比较高。
修改表的结构，对应的java代码就需要进行大量的修改。成本是比较高的。
这个责任应该由设计人员来承担！
第二：由于修改表结构的操作很少，所以我们不需要掌握，如果有一天
真的要修改表结构，你可以使用工具！！！！
修改表结构的操作是不需要写到java程序中的。实际上也不是java程序员的范畴。
约束（非常重要）
什么是约束
在创建表的时候，我们可以给表中的字段加上一些约束，来保证这个表中数据的完整性、有效性！！！
约束的作用就是为了保证：表中的数据有效！！
约束包括哪些
非空约束：not null
唯一性约束: unique
主键约束: primary key （简称PK）
外键约束：foreign key（简称FK）
检查约束：check（mysql不支持，oracle支持）
我们这里重点学习四个约束：
not null
unique
primary key
foreign key
非空约束：not null
非空约束not null约束的字段不能为NULL。
drop table if exists t_vip;
create table t_vip(
id int,
name varchar(255) not null # not null只有列级约束，没有表级约束！
);
insert into t_vip(id,name) values(1,'zhangsan');
insert into t_vip(id,name) values(2,'lisi');
insert into t_vip(id) values(3); # 报错，name后面没有值
小插曲：
xxxx.sql这种文件被称为sql脚本文件。
sql脚本文件中编写了大量的sql语句。
我们执行sql脚本文件的时候，该文件中所有的sql语句会全部执行！
批量的执行SQL语句，可以使用sql脚本文件。
在mysql当中怎么执行sql脚本呢？
mysql> source D:\course\03-MySQL\document\vip.sql
在实际的工作中，第一天到了公司，项目经理会给你一个xxx.sql文件，
你执行这个脚本文件，你电脑上的数据库数据就有了！
唯一性约束: unique
唯一性约束unique约束的字段不能重复，但是可以为NULL
drop table if exists t_vip;
create table t_vip(
id int,
name varchar(255) unique,
email varchar(255)
);
insert into t_vip(id,name,email) values(1,'zhangsan','zhangsan@123.com');
insert into t_vip(id,name,email) values(2,'lisi','lisi@123.com');
insert into t_vip(id,name,email) values(3,'wangwu','wangwu@123.com');
select * from t_vip; # 到这时还会运行成功
insert into t_vip(id,name,email) values(4,'wangwu','wangwu@sina.com');
# 报错，name重复
insert into t_vip(id) values(4);
insert into t_vip(id) values(5);
#name字段虽然被unique约束了，但是可以为NULL，都为NULL不表示重复
新需求：name和email两个字段联合起来具有唯一性！！！！
drop table if exists t_vip;
create table t_vip(
id int,
name varchar(255) unique, # 约束直接添加到列后面的，叫做列级约束。
email varchar(255) unique
);
#这张表这样创建是不符合我以上“新需求”的。
#这样创建表示：name具有唯一性，email具有唯一性。各自唯一。
drop table if exists t_vip;
create table t_vip(
id int,
name varchar(255),
email varchar(255),
unique(name,email) // 约束没有添加在列的后面，这种约束被称为表级约束。
);
insert into t_vip(id,name,email) values(1,'zhangsan','zhangsan@123.com');
insert into t_vip(id,name,email) values(2,'zhangsan','zhangsan@sina.com');
select * from t_vip;
需要给多个字段联合起来添加某一个约束的时候，需要使用表级约束。
not null没有表级约束
unique 和not null的联合使用
drop table if exists t_vip;
create table t_vip(
id int,
name varchar(255) not null unique
);
desc t_vip;
在mysql当中，如果一个字段同时被not null和unique约束的话，该字段自动变成主键字段。（注意：oracle中不一样！）
insert into t_vip(id,name) values(1,'zhangsan');
insert into t_vip(id,name) values(2,'zhangsan'); #报错：name不能重复
insert into t_vip(id) values(2); #报错：name不能为NULL。
主键约束（primary key，简称PK）（非常重要）
相关术语：
? **主键约束：**就是一种约束。
? **主键字段：**该字段上添加了主键约束，这样的字段叫做：主键字段
? **主键值：**主键字段中的每一个值都叫做：主键值。
主键值是每一行记录的唯一标识。
主键值是每一行记录的身份证号！！！
记住：任何一张表都应该有主键，没有主键，表无效！！
主键的特征：not null + unique（主键值不能是NULL，同时也不能重复！）
# 怎么给一张表添加主键约束
drop table if exists t_vip;
#1个字段做主键，叫做：单一主键
create table t_vip(
id int primary key, #列级约束
name varchar(255)
);
insert into t_vip(id,name) values(1,'zhangsan');
insert into t_vip(id,name) values(2,'lisi'); #到这里运行成功
insert into t_vip(id,name) values(2,'wangwu'); #报错，不能重复
insert into t_vip(name) values('zhaoliu'); #报错，不能为NULL
# 可以使用表级约束
drop table if exists t_vip;
create table t_vip(
id int,
name varchar(255),
primary key(id) #表级约束
);
#表级约束主要是给多个字段联合起来添加约束
drop table if exists t_vip;
# id和name联合起来做主键：复合主键！！！！
create table t_vip(
id int,
name varchar(255),
email varchar(255),
primary key(id,name)
);
insert into t_vip(id,name,email) values(1,'zhangsan','zhangsan@123.com');
insert into t_vip(id,name,email) values(1,'lisi','lisi@123.com');
#不会报错，因为id和name只需有其中一个不相同即可
在实际开发中不建议使用：复合主键。建议使用单一主键！
因为主键值存在的意义就是这行记录的身份证号，只要意义达到即可，单一主键可以做到。
复合主键比较复杂，不建议使用！！！
drop table if exists t_vip;
create table t_vip(
id int primary key,
name varchar(255) primary key
);
# 报错
结论：一张表，主键约束只能添加1个。（主键只能有1个。）
主键值建议使用：
int
bigint
char
等类型。
不建议使用：varchar来做主键。主键值一般都是数字，一般都是定长的！
主键除了：单一主键和复合主键之外，还可以这样进行分类？
自然主键：主键值是一个自然数，和业务没关系。
**业务主键：**主键值和业务紧密关联，例如拿银行卡账号做主键值。这就是业务主键！
在实际开发中使用业务主键多，还是使用自然主键多一些？
自然主键使用比较多，因为主键只要做到不重复就行，不需要有意义。
业务主键不好，因为主键一旦和业务挂钩，那么当业务发生变动的时候，
可能会影响到主键值，所以业务主键不建议使用。尽量使用自然主键。
在mysql当中，有一种机制，可以帮助我们自动维护一个主键值
drop table if exists t_vip;
create table t_vip(
id int primary key auto_increment, #auto_increment表示自增，从1开始，以1递增！
name varchar(255)
);
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
select * from t_vip;
#会生成八行id从1到8，name均为zhangsan的表
外键约束（foreign key，简称FK）（非常重要）
外键约束涉及到的相关术语：
**外键约束：**一种约束（foreign key）
**外键字段：**该字段上添加了外键约束
**外键值：**外键字段当中的每一个值。
业务背景：请设计数据库表，来描述“班级和学生”的信息
第一种方案：班级和学生存储在一张表中
t_student
no(pk) name classno classname
? 1 jack 100 北京市大兴区亦庄镇第二中学高三1班
? 2 lucy 100 北京市大兴区亦庄镇第二中学高三1班
? 3 lilei 100 北京市大兴区亦庄镇第二中学高三1班
? 4 hanmeimei 100 北京市大兴区亦庄镇第二中学高三1班
? 5 zhangsan 101 北京市大兴区亦庄镇第二中学高三2班
? 6 lisi 101 北京市大兴区亦庄镇第二中学高三2班
? 7 wangwu 101 北京市大兴区亦庄镇第二中学高三2班
? 8 zhaoliu 101 北京市大兴区亦庄镇第二中学高三2班
分析以上方案的缺点：
数据冗余，空间浪费！！！！
这个设计是比较失败的！
第二种方案：班级一张表、学生一张表
t_class 班级表
classno(pk) classname
100 北京市大兴区亦庄镇第二中学高三0班
101 北京市大兴区亦庄镇第二中学高三1班
t_student 学生表
no(pk) name cno(FK引用t_class这张表的classno)
1 jack 100
2 lucy 100
3 lilei 100
4 hanmeimei 100
5 zhangsan 101
6 lisi 101
7 wangwu 101
8 zhaoliu 101
当cno字段没有任何约束的时候，可能会导致数据无效。可能出现一个102，但是102班级不存在。
所以为了保证cno字段中的值都是100和101，需要给cno字段添加外键约束。
那么：cno字段就是外键字段。cno字段中的每一个值都是外键值。
注意：
t_class是父表
t_student是子表
删除表的顺序？
先删子，再删父。
创建表的顺序？
先创建父，再创建子。
删除数据的顺序？
先删子，再删父。
插入数据的顺序？
先插入父，再插入子。
drop table if exists t_student;
drop table if exists t_class;
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(classno)
);
insert into t_class(classno,classname) values(100,'北京市大兴区亦庄镇第二中学高三0班') ;
insert into t_class(classno,classname) values(101,'北京市大兴区亦庄镇第二中学高三1班') ;
insert into t_student(name,cno) values('jack',100);
insert into t_student(name,cno) values('lucy',100);
insert into t_student(name,cno) values('lilei',100);
insert into t_student(name,cno) values('hanmeimei',100);
insert into t_student(name,cno) values('zhangsan',100);
insert into t_student(name,cno) values('lisi',100);
insert into t_student(name,cno) values('wangwu',100);
insert into t_student(name,cno) values('zhaoliu',100);
注：1、子表中的外键引用的父表中的某个字段，被引用的这个字段不一定是主键，但至少具有unique约束。
? 2、外键可以为NULL
存储引擎（了解）
什么是存储引擎，有什么用呢
存储引擎是MySQL中特有的一个术语，其它数据库中没有。（Oracle中有，但是不叫这个名字）
存储引擎这个名字高端大气上档次。
实际上存储引擎是一个表存储/组织数据的方式。
不同的存储引擎，表存储数据的方式不同。
怎么给表添加/指定“存储引擎”呢
# 展示建表语句
show create table t_student;
# 可以在建表的时候给表指定存储引擎。
CREATE TABLE `t_student` (
`no` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`cno` int(11) DEFAULT NULL,
PRIMARY KEY (`no`),
KEY `cno` (`cno`),
CONSTRAINT `t_student_ibfk_1` FOREIGN KEY (`cno`) REFERENCES `t_class` (`classno`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8
在建表的时候可以在最后小括号的")"的右边使用：
ENGINE来指定存储引擎。
CHARSET来指定这张表的字符编码方式。
结论：
mysql默认的存储引擎是：InnoDB
mysql默认的字符编码方式是：utf8
#建表时指定存储引擎，以及字符编码方式。
create table t_product(
id int primary key,
name varchar(255)
)engine=InnoDB default charset=gbk;
MySQL支持哪些存储引擎
# 查看mysql版本
select version();
# 查看支持哪些存储引擎
show engines \G
# 共九大引擎，版本不同支持情况不同
常用存储引擎
MyISAM存储引擎：
? 它管理的表具有以下特征：
? 使用三个文件表示每个表：
? 格式文件 — 存储表结构的定义（mytable.frm）
? 数据文件 — 存储表行的内容（mytable.MYD）
? 索引文件 — 存储表上索引（mytable.MYI）：索引是一本书的目录，缩小扫描范围，提高查询效率的一种机制。可被转换为压缩、只读表来节省空间
? 提示一下：对于一张表来说，只要是主键，或者加有unique约束的字段上会自动创建索引。
? 优势特点：可被转换为压缩、只读表来节省空间
? MyISAM不支持事务机制，安全性低。
InnoDB存储引擎
这是mysql默认的存储引擎，同时也是一个重量级的存储引擎。
InnoDB支持事务，支持数据库崩溃后自动恢复机制。
InnoDB存储引擎最主要的特点是：非常安全。
它管理的表具有下列主要特征：
– 每个 InnoDB 表在数据库目录中以.frm 格式文件表示
– InnoDB 表空间 tablespace 被用于存储表的内容（表空间是一个逻辑名称。表空间存储数据+索引。）
? – 提供一组用来记录事务性活动的日志文件
? – 用 COMMIT(提交)、SAVEPOINT 及ROLLBACK(回滚)支持事务处理
? – 提供全 ACID 兼容
? – 在 MySQL 服务器崩溃后提供自动恢复
? – 多版本（MVCC）和行级锁定
? – 支持外键及引用的完整性，包括级联删除和更新
InnoDB最大的特点就是支持事务：
以保证数据的安全。效率不是很高，并且也不能压缩，不能转换为只读，
不能很好的节省存储空间。
MEMORY存储引擎
使用 MEMORY 存储引擎的表，其数据存储在内存中，且行的长度固定，
这两个特点使得 MEMORY 存储引擎非常快。
MEMORY 存储引擎管理的表具有下列特征：
– 在数据库目录内，每个表均以.frm 格式的文件表示。
– 表数据及索引被存储在内存中。（目的就是快，查询快！）
– 表级锁机制。
– 不能包含 TEXT 或 BLOB 字段。
MEMORY 存储引擎以前被称为HEAP 引擎。
MEMORY引擎优点：查询效率是最高的。不需要和硬盘交互。
MEMORY引擎缺点：不安全，关机之后数据消失。因为数据和索引都是在内存当中。
|