MySQL- - -第三天

第三天


1、表的创建(建表)

1.1、 建表的语法格式:(建表属于DDL语句,DDL包括:create drop alter)

create table 表名(字段名1 数据类型, 字段名2 数据类型, 字段名3 数据类型);

create table 表名(
	字段名1 数据类型, 
	字段名2 数据类型, 
	字段名3 数据类型
);

表名:建议以t_ 或者 tbl_开始,可读性强。见名知意。
字段名:见名知意。
表名和字段名都属于标识符。

1.2、关于mysql中的数据类型

很多数据类型,我们只需要掌握一些常见的数据类型即可。

	varchar(最长255)
		可变长度的字符串
		比较智能,节省空间。
		会根据实际的数据长度动态分配空间。

		优点:节省空间
		缺点:需要动态分配空间,速度慢。

	char(最长255)
		定长字符串
		不管实际的数据长度是多少。
		分配固定长度的空间去存储数据。
		使用不恰当的时候,可能会导致空间的浪费。

		优点:不需要动态分配空间,速度快。
		缺点:使用不当可能会导致空间的浪费。

		varchar和char我们应该怎么选择?
			性别字段你选什么?因为性别是固定长度的字符串,所以选择char。
			姓名字段你选什么?每一个人的名字长度不同,所以选择varchar。

	int(最长11)
		数字中的整数型。等同于java的int。

	bigint
		数字中的长整型。等同于java中的long。

	float	
		单精度浮点型数据

	double
		双精度浮点型数据

	date
		短日期类型

	datetime
		长日期类型

	clob
		字符大对象
		最多可以存储4G的字符串。
		比如:存储一篇文章,存储一个说明。
		超过255个字符的都要采用CLOB字符大对象来存储。
		Character Large OBject:CLOB

	blob
		二进制大对象
		Binary Large OBject
		专门用来存储图片、声音、视频等流媒体数据。
		往BLOB类型的字段上插入数据的时候,例如插入一个图片、视频等,
		你需要使用IO流才行。

1.3、创建一个学生表?

学号、姓名、年龄、性别、邮箱地址
create table t_student(
	no int,
	name varchar(32),
	sex char(1),
	age int(3),
	email varchar(255)
);

删除表:
	drop table t_student; // 当这张表不存在的时候会报错!

	// 如果这张表存在的话,删除
	drop table if exists t_student;、

1.4、插入数据insert (DML)

语法格式:
	insert into 表名(字段名1,字段名2,字段名3...) values(值1,值2,值3);

	注意:字段名和值要一一对应。什么是一一对应?
		数量要对应。数据类型要对应。

insert into t_student(no,name,sex,age,email) values(1,'zhangsan','m',20,'zhangsan@123.com');
insert into t_student(email,name,sex,age,no) values('lisi@123.com','lisi','f',20,2);

insert into t_student(no) values(3);

注意:insert语句但凡是执行成功了,那么必然会多一条记录。没有给其它字段指定值的话,默认值是NULL。

给sex设置默认值‘m’,在插入数据时,即使不给sex赋值这一项也不是NULL,而是默认值‘m’

create table t_student(
	no int,
	name varchar(32),
	sex char(1) default 'm',
	age int(3),
	email varchar(255)
);


删除表

	drop table t_student; // 当这张表不存在的时候会报错!

	// 如果这张表存在的话,删除
	drop table if exists t_student;

insert语句中的“字段名”可以省略吗?可以- - -(但values中列的数量和赋值顺序都不能改变)

	insert into t_student values(2); //错误的

	// 注意:前面的字段名省略的话,等于都写上了!所以值也要都写上!
	insert into t_student values(2, 'lisi', 'f', 20, 'lisi@123.com');

一次插入多行数据

insert into t_student values(3,'zhangsan','f',34,'zhangsan@163.com'),(4,'wangwu','f',22,'wangwu@qq.com');


1.5、insert插入日期

数字格式化:format
	select ename,sal from emp;
	+--------+---------+
	| ename  | sal     |
	+--------+---------+
	| SMITH  |  800.00 |
	| ALLEN  | 1600.00 |
	| WARD   | 1250.00 |
	| JONES  | 2975.00 |
	| MARTIN | 1250.00 |
	| BLAKE  | 2850.00 |
	| CLARK  | 2450.00 |
	| SCOTT  | 3000.00 |
	| KING   | 5000.00 |
	| TURNER | 1500.00 |
	| ADAMS  | 1100.00 |
	| JAMES  |  950.00 |
	| FORD   | 3000.00 |
	| MILLER | 1300.00 |
	+--------+---------+

	格式化数字:format(数字, '格式')
		select ename,format(sal, '$999,999') as sal from emp;
		+--------+-------+
		| ename  | sal   |
		+--------+-------+
		| SMITH  | 800   |
		| ALLEN  | 1,600 |
		| WARD   | 1,250 |
		| JONES  | 2,975 |
		| MARTIN | 1,250 |
		| BLAKE  | 2,850 |
		| CLARK  | 2,450 |
		| SCOTT  | 3,000 |
		| KING   | 5,000 |
		| TURNER | 1,500 |
		| ADAMS  | 1,100 |
		| JAMES  | 950   |
		| FORD   | 3,000 |
		| MILLER | 1,300 |
		+--------+-------+

str_to_date:将字符串varchar类型转换成date类型
date_format:将date类型转换成具有一定格式的varchar字符串类型。

drop table if exists t_user;
create table t_user(
	id int,
	name varchar(32),
	birth date // 生日也可以使用date日期类型
);

create table t_user(
	id int,
	name varchar(32),
	birth char(10) // 生日可以使用字符串,没问题。
);

生日:1990-10-11 (10个字符)

注意:数据库中的有一条命名规范:
	所有的标识符都是全部小写,单词和单词之间使用下划线进行衔接。

mysql> desc t_user;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(32) | YES  |     | NULL    |       |
| birth | date        | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

插入数据?
	insert into t_user(id,name,birth) values(1, 'zhangsan', '01-10-1990'); // 1990年10月1日
	出问题了:原因是类型不匹配。数据库birth是date类型,这里给了一个字符串varchar。

	怎么办?可以使用str_to_date函数进行类型转换。
	str_to_date函数可以将字符串转换成日期类型date?
	语法格式:
		str_to_date('字符串日期', '日期格式')

	mysql的日期格式:
		%Y	年
		%m 月
		%d 日
		%h	时
		%i	分
		%s	秒
	
	insert into t_user(id,name,birth) values(1, 'zhangsan', str_to_date('01-10-1990','%d-%m-%Y'));

	str_to_date函数可以把字符串varchar转换成日期date类型数据,
	通常使用在插入insert方面,因为插入的时候需要一个日期类型的数据,
	需要通过该函数将字符串转换成date。

好消息?
	如果你提供的日期字符串是这个格式,str_to_date函数就不需要了!!!
		%Y-%m-%d
	insert into t_user(id,name,birth) values(2, 'lisi', '1990-10-01');

查询的时候可以以某个特定的日期格式展示吗?
	date_format
	这个函数可以将日期类型转换成特定格式的字符串。

	select id,name,date_format(birth, '%m/%d/%Y') as birth from t_user;
	+------+----------+------------+
	| id   | name     | birth      |
	+------+----------+------------+
	|    1 | zhangsan | 10/01/1990 |
	|    2 | lisi     | 10/01/1990 |
	+------+----------+------------+

	date_format函数怎么用?
		date_format(日期类型数据, '日期格式')
		这个函数通常使用在查询日期方面。设置展示的日期格式。
	
	mysql> select id,name,birth from t_user;
	+------+----------+------------+
	| id   | name     | birth      |
	+------+----------+------------+
	|    1 | zhangsan | 1990-10-01 |
	|    2 | lisi     | 1990-10-01 |
	+------+----------+------------+
	以上的SQL语句实际上是进行了默认的日期格式化,
	自动将数据库中的date类型转换成varchar类型。
	并且采用的格式是mysql默认的日期格式:'%Y-%m-%d'

	select id,name,date_format(birth,'%Y/%m/%d') as birth from t_user;
	
	java中的日期格式?
		yyyy-MM-dd HH:mm:ss SSS

1.6、date和datetime两个类型的区别?
date是短日期:只包括年月日信息。
datetime是长日期:包括年月日时分秒信息。

drop table if exists t_user;
create table t_user(
	id int,
	name varchar(32),
	birth date,
	create_time datetime
);


id是整数
name是字符串
birth是短日期
create_time是这条记录的创建时间:长日期类型

mysql短日期默认格式:%Y-%m-%d
mysql长日期默认格式:%Y-%m-%d %h:%i:%s

insert into t_user(id,name,birth,create_time) values(1,'zhangsan','1990-10-01','2020-03-18 15:49:50');

在mysql当中怎么获取系统当前时间?
	now() 函数,并且获取的时间带有:时分秒信息!!!!是datetime类型的。

	insert into t_user(id,name,birth,create_time) values(2,'lisi','1991-10-01',now());

2、表的复制

原理:

	将一个查询结果当做一张表新建!!!!!
	这个可以完成表的快速复制!!!!
	表创建出来,同时表中的数据也存在了!!!

语法:

create table 表名 as select语句    (将查询结果当作表创建出来)

create table emp1 as select * from emp;


3、将查询结果插入到一张表中?

语法:

insert into table 表名  select语句    (将查询结果插入到一张已存在的表中)

insert into emp1  select * from emp;  (列数要对应相等)

insert into emp1  select * from emp1;(查询自身再赋给自己也是可以的)


4、修改表中数据(DML): update

语法格式:
update 表名 set 字段名1=值1,字段名2=值2,字段名3=值3... where 条件;

注意:没有条件限制会导致整个表的所有数据全部更新

案例:将id=2的name修改为jack,将birth修改为2000-10-11

update t_user set name = 'jack', birth = '2000-10-11' where id = 2;


5、删除数据 delete (DML)

语法格式?
	delete from 表名 where 条件;

注意:没有条件,整张表的数据会全部删除!

delete from t_user where id = 2;

insert into t_user(id) values(2);

delete from t_user; // 删除所有!


快速删除表中的数据?【truncate比较重要,必须掌握】

//删除dept_bak表中的数据
delete from dept_bak; //这种删除数据的方式比较慢。

mysql> select * from dept_bak;
Empty set (0.00 sec)

delete语句删除数据的原理?(delete属于DML语句!!!)
	表中的数据被删除了,但是这个数据在硬盘上的真实存储空间不会被释放!!!
	这种删除缺点是:删除效率比较低。
	这种删除优点是:支持回滚,后悔了可以再恢复数据!!!

truncate语句删除数据的原理?
	这种删除效率比较高,表被一次截断,物理删除。
	这种删除缺点:不支持回滚。
	这种删除优点:快速。

用法:truncate table dept_bak; (这种操作属于DDL操作。)

大表非常大,上亿条记录????
	删除的时候,使用delete,也许需要执行1个小时才能删除完!效率较低。
	可以选择使用truncate删除表中的数据。只需要不到1秒钟的时间就删除结束。效率较高。
	但是使用truncate之前,必须仔细询问客户是否真的要删除,并警告删除之后不可恢复!

	truncate是删除表中的数据,表还在!

删除表操作?
	drop table 表名; // 这不是删除表中的数据,这是把表删除。

6、对表结构的增删改?

什么是对表结构的修改?
添加一个字段,删除一个字段,修改一个字段!!!

对表结构的修改需要使用:alter
属于DDL语句

DDL包括:create drop alter

第一:在实际的开发中,需求一旦确定之后,表一旦设计好之后,很少的
进行表结构的修改。因为开发进行中的时候,修改表结构,成本比较高。
修改表的结构,对应的java代码就需要进行大量的修改。成本是比较高的。
这个责任应该由设计人员来承担!

第二:由于修改表结构的操作很少,所以我们不需要掌握,如果有一天
真的要修改表结构,你可以使用工具!!!!

修改表结构的操作是不需要写到java程序中的。实际上也不是java程序员的范畴。


7、约束(非常重要,五颗星*****)

7.1、什么是约束?

约束对应的英语单词:constraint

创建表的时候,我们可以给表中的字段加上一些约束,来保证这个表中数据的完整性、有效性!!!

约束的作用就是为了保证:表中的数据有效!!

7.2、约束包括哪些?

非空约束:not null (约束的字段不能为NULL
唯一性约束: unique (约束的字段不能重复
主键约束: primary key (简称PK)(约束的字段既不能NULL,也不能重复)
外键约束:foreign key(简称FK)
检查约束:check(mysql不支持,oracle支持)

我们这里重点学习四个约束:
	not null
	unique
	primary key
	foreign key

7.3、非空约束:not null(只有列级约束

非空约束not null约束的字段不能为NULL

mysql> create table t_user(
-> id int,
-> username varchar(255) not null,
-> password varchar(255)
-> );

mysql> insert into t_user(id,password) values(1,'123');
ERROR 1364 (HY000): Field 'username' doesn't have a default value

上述中创建表时约束字段username不能为空,故当插入数据时,没有插入username默认为NULL,与约束条件矛盾,出现错误

7.4、唯一性约束: unique

唯一性约束unique约束的字段不能重复,但是可以为NULL。

drop table if exists t_user;

create table t_user(
	id int,
	username varchar(255) unique
);

insert into t_user values(1,'zhangsan');
insert into t_user values(2,'zhangsan');  // username 重复会报错

insert into t_user values(3);    // username默认null,null可以重复
insert into t_user values(4);
insert into t_user values(5);

两个字段联合起来不能重复(表级约束:在所有字段后(即多个字段联合添加1个约束,在一次插入中两个字段都不能重复,有一个没有重复是可以插入成功

create table t_user(
	id int,
	usercode varchar(255),
	username varchar(255),
	unique(usercode,username)
);

两个字段任意一个都不能重复(列级约束:(在一个字段后)- - -不同

create table t_user(
	id int,
	usercode varchar(255) unique,
	username varchar(255) unique,
);

unique 和not null可以联合吗?

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。

7.5、主键约束(primary key,简称PK)非常重要五颗星*****

主键约束的相关术语?

	主键约束:就是一种约束。
	主键字段:该字段上添加了主键约束,这样的字段叫做:主键字段
	主键值:主键字段中的每一个值都叫做:主键值。

什么是主键?有啥用?

	主键值是每一行记录的唯一标识。
	主键值是每一行记录的身份证号!!!

记住:任何一张表都应该有主键,没有主键,表无效!!

主键的特征not null + unique(主键值不能是NULL,同时也不能重复!

drop table if exists t_user;

create table t_user(
	id int primary key,
	username varchar(255),
	email varchar(255)
);

insert into t_user(id,username,email) values(1,'zs','zs@163.com');
insert into t_user(id,username,email) values(2,'ls','ls@163.com');
insert into t_user(id,username,email) values(3,'ww','ww@163.com');

insert into t_user(id,username,email) values(1,'jack','jack@163.com');
ERROR 1062 (23000): Duplicate entry '1' for key 't_user.PRIMARY'

mysql提供主键值自增 - - - (auto_increment)

oracle当中也提供了一个自增机制,叫做:序列(sequence)对象。

drop table if exists t_user;

create table t_user(
	id int primary key auto_increment,
	username varchar(255),
	email varchar(255)
);

insert into t_user(username,email) values('zs','zs@163.com');
insert into t_user(username,email) values('ls','ls@163.com');
insert into t_user(username,email) values('ww','ww@163.com');

insert into t_user(id,username,email) values(1,'jack','jack@163.com');
ERROR 1062 (23000): Duplicate entry '1' for key 't_user.PRIMARY'

可以这样添加主键吗,使用表级约束

	drop table if exists t_user;
	create table t_user(
		id int,
		name varchar(255),
		primary key(id)  // 表级约束
	);
	insert into t_user(id,name) values(1,'zhangsan');

	//错误
	insert into t_user(id,name) values(1,'lisi');
	ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

表级约束主要是给多个字段联合起来添加约束

	drop table if exists t_vip;
	// id和name联合起来做主键:复合主键!!!!
	create table t_user(
		id int,
		name varchar(255),
		email varchar(255),
		primary key(id,name)
	);
	insert into t_user(id,name,email) values(1,'zhangsan','zhangsan@123.com');
	insert into t_user(id,name,email) values(1,'lisi','lisi@123.com');

	//错误:不能重复
	insert into t_user(id,name,email) values(1,'lisi','lisi@123.com');
	ERROR 1062 (23000): Duplicate entry '1-lisi' for key 'PRIMARY'

在实际开发中不建议使用:复合主键建议使用单一主键!因为主键值存在的意义就是这行记录的身份证号,只要意义达到即可,单一主键可以做到。复合主键比较复杂,不建议使用!!!

一个表中主键约束能加两个吗?(不能)

	drop table if exists t_user;
	create table t_user(
		id int primary key,
		name varchar(255) primary key
	);
	ERROR 1068 (42000): Multiple primary key defined

结论:一张表,主键约束只能添加1个。(主键只能有1个。)

主键值建议使用:主键值一般都是数字,一般定长

	int
	bigint
	char
	等类型。

	不建议使用:varchar来做主键。主键值一般都是数字,一般都是定长的!

主键除了:单一主键和复合主键之外,还可以这样进行分类?

	自然主键:主键值是一个自然数,和业务没关系。
	业务主键:主键值和业务紧密关联,例如拿银行卡账号做主键值。这就是业务主键!

	在实际开发中使用业务主键多,还是使用自然主键多一些?
		自然主键使用比较多,因为主键只要做到不重复就行,不需要有意义。
		业务主键不好,因为主键一旦和业务挂钩,那么当业务发生变动的时候,
		可能会影响到主键值,所以业务主键不建议使用。尽量使用自然主键。


7.6、外键约束(foreign key,简称FK)非常重要五颗星*****- - -foreign key(…) references 父类表名(…)
foreign key(外键)、references(引用)的意思

外键约束涉及到的相关术语:

	外键约束:一种约束(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班
	分析以上方案的缺点:
		数据冗余,空间浪费!!!!
		这个设计是比较失败的!

第二种方案:班级一张表、学生一张表??(采用外键约束的方式- - -保证cno字段中的值都是100和101

	t_class 班级表
	classno(pk)			classname
	------------------------------------------------------
	100					北京市大兴区亦庄镇第二中学高三1班
	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字段中的每一个值都是外键值


将以上表的建表语句写出来:

关键所在:
foreign key(cno) references t_class(classno)

t _student中的cno字段引用t_class表中的classno字段,此时t_student表叫做子表,t_class表叫做父表

注意

	删除表的顺序?
		先删子,再删父。

	创建表的顺序?
		先创建父,再创建子。

	删除数据的顺序?
		先删子,再删父。

	插入数据的顺序?
		先插入父,再插入子。

drop table if exists t_student;
drop table if exists t_class;

create table t_class(
	classno int,
	classname varchar(255),
	primary key(classno)
);

create table t_student(
	no int,
	name varchar(255),
	cno int,
	primary key(no),
	foreign key(cno) references t_class(classno)
);

insert into t_class values(101,'xxxxxxxxxxxxxxxxxxx');
insert into t_class values(102,'yyyyyyyyyyyyyyyyyyy');

insert into t_student values(1,'zs1',101);
insert into t_student values(2,'zs2',102);
insert into t_student values(3,'zs3',102);
insert into t_student values(4,'zs4',102);
insert into t_student values(5,'zs5',102);

select * from t_class;
select * from t_student;

insert into t_student values(5,‘zs5’,103);
//由于外键的存在,t_student的cno中数据不能随便写,要根据父类中存在的复制,t_class的classno中没有103,添加失败

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (bjpowernode.t_student, CONSTRAINT t_student_ibfk_1 FOREIGN KEY (cno) REFERENCES t_class (classno))


思考:子表中的外键引用的父表中的某个字段,被引用的这个字段必须是主键吗?

不一定是主键,但至少具有unique约束(不能重复)

测试:外键可以为NULL吗?
外键值可以为NULL


8、存储引擎(了解内容)

8.1、什么是存储引擎,有什么用呢?

存储引擎是MySQL中特有的一个术语,其它数据库中没有。(Oracle中有,但是不叫这个名字)

存储引擎这个名字高端大气上档次。

实际上存储引擎是一个表存储/组织数据的方式。

不同的存储引擎,表存储数据的方式不同。

每一个存储引擎都有自己的优缺点,需要再合适的时机选择合适的存储引擎。

8.2、怎么给表添加/指定“存储引擎”呢?
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;

`” 引号中的是”飘号“(键盘数字1左边的键位),在MySQL中,凡是标识符都可以用飘号括起来。但最好别用,不通用。

8.3、怎么查看mysql支持哪些存储引擎呢?

mysql> select version();  // 查看当前使用的mysql的版本
+-----------+
| version() |
+-----------+
| 5.5.36    |
+-----------+

命令: show engines \G    - - - 查看当前版本的mysql支持的引擎

8.4、关于mysql常用的存储引擎介绍一下

MyISAM存储引擎?(不支持事务

它管理的表具有以下特征:
	使用三个文件表示每个表:
		格式文件 — 存储表结构的定义(mytable.frm)
		数据文件 — 存储表行的内容(mytable.MYD)
		索引文件 — 存储表上索引(mytable.MYI):索引是一本书的目录,缩小扫描范围,提高查询效率的一种机制。
	可被转换为压缩、只读表来节省空间

	提示一下:
		对于一张表来说,只要是主键,
		或者加有unique约束的字段上会自动创建索引。

	MyISAM存储引擎特点:
		可被转换为压缩、只读表来节省空间
		这是这种存储引擎的优势!!!!
	
	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引擎缺点:不安全,关机之后数据消失。因为数据和索引都是在内存当中。


9、事务(Transaction)(重点:五颗星*****,必须理解,必须掌握)

9.1、什么是事务?

一个事务其实就是一个完整的业务逻辑。
是一个最小的工作单元。不可再分。

什么是一个完整的业务逻辑?
	假设转账,从A账户向B账户中转账10000.
	将A账户的钱减去10000(update语句)
	update t_act set balance = balance - 10000 where actno = 'act-001';
	将B账户的钱加上10000(update语句)
	update t_act set balance = balance + 10000 where actno = 'act-002';
	这就是一个完整的业务逻辑。

以上的操作是一个最小的工作单元,要么同时成功,要么同时失败,不可再分。

两个update语句要求必须同时成功或者同时失败,这样才能保证钱是正确的。(就需要使用数据库的“事务机制”)

9.2、只有DML语句才会有事务这一说,其它语句和事务无关!!!

insert、delete、update- - -(增删改)

只有以上的三个语句和事务有关系,其它都没有关系。

因为 只有以上的三个语句是数据库表中数据进行增、删、改的。
只要你的操作一旦涉及到数据的增、删、改,那么就一定要考虑安全问题。

数据安全第一位!!!

9.3、假设所有的业务,只要一条DML语句就能完成,还有必要存在事务机制吗?

正是因为做某件事的时候,需要多条DML语句共同联合起来才能完成,所以需要事务的存在。如果任何一件复杂的事儿都能一条DML语句搞定,那么事务则没有存在的价值了。

到底什么是事务呢?
	说到底,说到本质上,一个事务其实就是多条DML语句同时成功,或者同时失败!

事务:就是批量的DML语句同时成功,或者同时失败!

在这里插入图片描述
无论提交事务还是回滚事务,历史操作都会被清空;不同的是提交事务会把操作同步到硬盘文件中,而回滚事务则不会对硬盘文件进行操作

9.4、提交事务,回滚事务?

提交事务:commit; 语句
回滚事务:rollback; 语句(回滚永远都是只能回滚到上一次的提交点(savepoint)!)

事务对应的英语单词是:transaction

测试一下,在mysql当中默认的事务行为是怎样的?
	mysql默认情况下是支持自动提交事务的。(自动提交)
	什么是自动提交?
		每执行一条DML语句,则提交一次!

	这种自动提交实际上是不符合我们的开发习惯,因为一个业务
	通常是需要多条DML语句共同执行才能完成的,为了保证数据
	的安全,必须要求同时成功之后再提交,所以不能执行一条
	就提交一条。

怎么将mysql的自动提交机制关闭掉呢?
	先执行这个命令:start transaction;

演示事务:

	---------------------------------回滚事务----------------------------------------
	mysql> use bjpowernode;
	Database changed
	mysql> select * from dept_bak;
	Empty set (0.00 sec)

	mysql> start transaction;
	Query OK, 0 rows affected (0.00 sec)

	mysql> insert into dept_bak values(10,'abc', 'tj');
	Query OK, 1 row affected (0.00 sec)

	mysql> insert into dept_bak values(10,'abc', 'tj');
	Query OK, 1 row affected (0.00 sec)

	mysql> select * from dept_bak;
	+--------+-------+------+
	| DEPTNO | DNAME | LOC  |
	+--------+-------+------+
	|     10 | abc   | tj   |
	|     10 | abc   | tj   |
	+--------+-------+------+
	2 rows in set (0.00 sec)

	mysql> rollback;
	Query OK, 0 rows affected (0.00 sec)

	mysql> select * from dept_bak;
	Empty set (0.00 sec)


	---------------------------------提交事务----------------------------------------
	mysql> use bjpowernode;
	Database changed
	mysql> select * from dept_bak;
	+--------+-------+------+
	| DEPTNO | DNAME | LOC  |
	+--------+-------+------+
	|     10 | abc   | bj   |
	+--------+-------+------+
	1 row in set (0.00 sec)

	mysql> start transaction;
	Query OK, 0 rows affected (0.00 sec)

	mysql> insert into dept_bak values(20,'abc
	Query OK, 1 row affected (0.00 sec)

	mysql> insert into dept_bak values(20,'abc
	Query OK, 1 row affected (0.00 sec)

	mysql> insert into dept_bak values(20,'abc
	Query OK, 1 row affected (0.00 sec)

	mysql> commit;
	Query OK, 0 rows affected (0.01 sec)

	mysql> select * from dept_bak;
	+--------+-------+------+
	| DEPTNO | DNAME | LOC  |
	+--------+-------+------+
	|     10 | abc   | bj   |
	|     20 | abc   | tj   |
	|     20 | abc   | tj   |
	|     20 | abc   | tj   |
	+--------+-------+------+
	4 rows in set (0.00 sec)

	mysql> rollback;
	Query OK, 0 rows affected (0.00 sec)

	mysql> select * from dept_bak;
	+--------+-------+------+
	| DEPTNO | DNAME | LOC  |
	+--------+-------+------+
	|     10 | abc   | bj   |
	|     20 | abc   | tj   |
	|     20 | abc   | tj   |
	|     20 | abc   | tj   |
	+--------+-------+------+
	4 rows in set (0.00 sec)

9.5、事务包括4个特性

A:原子性
	说明事务是最小的工作单元。不可再分。

C:一致性
	所有事务要求,在同一个事务当中,所有操作必须同时成功,或者同时失败,
	以保证数据的一致性。

I:隔离性
	A事务和B事务之间具有一定的隔离。
	教室A和教室B之间有一道墙,这道墙就是隔离性。
	A事务在操作一张表的时候,另一个事务B也操作这张表会那样???

D:持久性
	事务最终结束的一个保障。事务提交,就相当于将没有保存到硬盘上的数据,保存到硬盘上!

9.6、重点研究一下事务的隔离性!!!

oracle数据库默认的隔离级别是:read committed(读已提交)

mysql中默认的事务隔离级别是:repeatable read (可重复读)

A教室和B教室中间有一道墙,这道墙可以很厚,也可以很薄。这就是事务的隔离级别。
这道墙越厚,表示隔离级别就越高。

事务和事务之间的隔离级别有哪些呢?4个级别

	读未提交:read uncommitted(最低的隔离级别)《没有提交就读到了》
		什么是读未提交?
			事务A可以读取到事务B未提交的数据。
		这种隔离级别存在的问题就是:
			脏读现象!(Dirty Read)
			我们称读到了脏数据。
		这种隔离级别一般都是理论上的,大多数的数据库隔离级别都是二档起步!

	读已提交:read committed《提交之后才能读到》
		什么是读已提交?
			事务A只能读取到事务B提交之后的数据。
		这种隔离级别解决了什么问题?
			解决了脏读的现象。
		这种隔离级别存在什么问题?
			不可重复读取数据。(读不到和之前一样的数据)
			什么是不可重复读取数据呢?
				在事务开启之后,第一次读到的数据是3条,当前事务还没有
				结束,可能第二次再读取的时候,读到的数据是4条,3不等于4
				称为不可重复读取。

		这种隔离级别是比较真实的数据,每一次读到的数据是绝对的真实。
		oracle数据库默认的隔离级别是:read committed

	可重复读:repeatable read《提交之后也读不到,永远读取的都是刚开启事务时的数据》
		什么是可重复读取?
			事务A开启之后,不管是多久,每一次在事务A中读取到的数据
			都是一致的。即使事务B将数据已经修改,并且提交了,事务A
			读取到的数据还是没有发生改变,这就是可重复读。
		可重复读解决了什么问题?
			解决了不可重复读取数据。
		可重复读存在的问题是什么?
			可以会出现幻影读。
			每一次读取到的数据都是幻象。不够真实!
		
		早晨9点开始开启了事务,只要事务不结束,到晚上9点,读到的数据还是那样!
		读到的是假象。不够绝对的真实。

		mysql中默认的事务隔离级别就是这个!!!!!!!!!!!

	序列化/串行化:serializable(最高的隔离级别)
		这是最高隔离级别,效率最低。解决了所有的问题。
		这种隔离级别表示事务排队,不能并发!
		synchronized,线程同步(事务同步)
		每一次读取到的数据都是最真实的,并且效率是最低的。

9.7、验证各种隔离级别

查看隔离级别:
select @@global.transaction_isolation;

+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| READ-UNCOMMITTED               |
+--------------------------------+
mysql默认的隔离级别

被测试的表t_user
验证:read uncommited
mysql> set global transaction isolation level read uncommitted;

事务A												事务B

use bjpowernode;
												use bjpowernode;
start transaction;
select * from t_user;
												start transaction;
												insert into t_user values('zhangsan');
select * from t_user;

验证:read commited
mysql> set global transaction isolation level read committed;

事务A												事务B

use bjpowernode;
												use bjpowernode;
start transaction;
												start transaction;
select * from t_user;
												insert into t_user values('zhangsan');
select * from t_user;
												commit;
select * from t_user;

验证:repeatable read
mysql> set global transaction isolation level repeatable read;

事务A												事务B

use bjpowernode;
												use bjpowernode;
start transaction;
												start transaction;
select * from t_user;
												insert into t_user values('lisi');
												insert into t_user values('wangwu');
												commit;
select * from t_user;

验证:serializable
mysql> set global transaction isolation level serializable;

事务A												事务B

use bjpowernode;
												use bjpowernode;
start transaction;
												start transaction;
select * from t_user;
insert into t_user values('abc');


10、索引(index)

10.1、什么是索引?

索引是在数据库表的字段上添加的,是为了提高查询效率存在的一种机制。
一张表的一个字段可以添加一个索引,当然,多个字段联合起来也可以添加索引。
索引相当于一本书的目录,是为了缩小扫描范围而存在的一种机制。

对于一本字典来说,查找某个汉字有两种方式:
	第一种方式:一页一页挨着找,直到找到为止,这种查找方式属于全字典扫描。
	效率比较低。
	第二种方式:先通过目录(索引)去定位一个大概的位置,然后直接定位到这个
	位置,做局域性扫描,缩小扫描的范围,快速的查找。这种查找方式属于通过
	索引检索,效率较高。

t_user
id(idIndex)	name(nameIndex)	email(emailIndex)		address  (emailAddressIndex)
----------------------------------------------------------------------------------
1				zhangsan...
2				lisi
3				wangwu
4				zhaoliu
5				hanmeimei
6				jack

select * from t_user where name = 'jack';

以上的这条SQL语句会去name字段上扫描,为什么?
	因为查询条件是:name='jack'

如果name字段上没有添加索引(目录),或者说没有给name字段创建索引,
MySQL会进行全扫描,会将name字段上的每一个值都比对一遍。效率比较低。

MySQL在查询方面主要就是两种方式:
第一种方式:全表扫描
第二种方式:根据索引检索。

注意:
	在实际中,汉语字典前面的目录是排序的,按照a b c d e f....排序,
	为什么排序呢?因为只有排序了才会有区间查找这一说!(缩小扫描范围
	其实就是扫描某个区间罢了!)

	在mysql数据库当中索引也是需要排序的,并且这个所以的排序和TreeSet
	数据结构相同。TreeSet(TreeMap)底层是一个自平衡的二叉树!在mysql
	当中索引是一个B-Tree数据结构。

	遵循左小又大原则存放。采用中序遍历方式遍历取数据。

10.2、在mysql当中,主键上,以及unique字段上都会自动添加索引的!!!!

什么条件下,我们会考虑给字段添加索引呢

条件1:数据量庞大(到底有多么庞大算庞大,这个需要测试,因为每一个硬件环境不同)
条件2:该字段经常出现在where的后面,以条件的形式存在,也就是说这个字段总是被扫描。
条件3:该字段很少的DML(insert delete update)操作。(因为DML之后,索引需要重新排序。)

建议不要随意添加索引,因为索引也是需要维护的,太多的话反而会降低系统的性能。

建议通过主键查询,建议通过unique约束的字段进行查询,效率是比较高的。

10.3 查看sql语句的执行计划,type(ALL)是全表查询

mysql> explain select ename,sal from emp where sal = 5000;

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   14 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

10.4、索引怎么创建?怎么删除?语法是什么?

创建索引:
			create index 索引名称 on 表名 (字段名)
	mysql> create index emp_ename_index on emp(ename);
	给emp表的ename字段添加索引,起名:emp_ename_index

删除索引:
			drop index 索引名称 on 表名 
	mysql> drop index emp_ename_index on emp;
	将emp表上的emp_ename_index索引对象删除。

10.5、索引的实现原理?

假设有一张用户表:t_user

id(PK)					name						每一行记录在硬盘上都有物理存储编号
----------------------------------------------------------------------------------
100						zhangsan					0x1111
120						lisi						0x2222
99						wangwu			     		0x8888
88						zhaoliu					    0x9999
101						jack						0x6666
55						lucy						0x5555
130						tom					    	0x7777

提醒1:在任何数据库当中主键上都会自动添加索引对象,id字段上自动有索引,
因为id是PK。另外在mysql当中,一个字段上如果有unique约束的话,也会自动
创建索引对象。

提醒2:在任何数据库当中,任何一张表的任何一条记录在硬盘存储上都有
一个硬盘的物理存储编号。

提醒3:在mysql当中,索引是一个单独的对象,不同的存储引擎以不同的形式
存在,在MyISAM存储引擎中,索引存储在一个.MYI文件中。在InnoDB存储引擎中
索引存储在一个逻辑名称叫做tablespace的当中。在MEMORY存储引擎当中索引
被存储在内存当中。不管索引存储在哪里,索引在mysql当中都是一个树的形式
存在。(自平衡二叉树:B-Tree)

在这里插入图片描述
通过B-Tree缩小扫描范围,底层索引进行排序,分区,索引会携带数据在表中“物理地址”,最终通过索引检索到数据之后,获取到关联的物理地址,通过物理地址定位表中的数据,效率是最高的。

	select ename from emp where ename = 'SMITH';
	通过索引转换为:
	select ename from emp where 物理地址 = 0x3;

10.6、索引是各种数据库进行优化的重要手段。优化的时候优先考虑的因素就是索引。
索引在数据库当中分了很多类

单一索引:一个字段上添加索引。
复合索引:两个字段或者更多的字段上添加索引。

主键索引:主键上会(自动)添加索引。
唯一性索引:具有unique约束的字段上会(自动)添加索引。
.....

注意:唯一性比较弱(含重复字段)的字段上添加索引用处不大。

10.7、索引有失效的时候,什么时候索引失效呢?

失效的第1种情况:(使用模糊匹配,通配符%开头

	select * from emp where ename like '%T';

	ename上即使添加了索引,也不会走索引,为什么?
		原因是因为模糊匹配当中以“%”开头了!
		尽量避免模糊查询的时候以“%”开始。
		这是一种优化的手段/策略。

	mysql> explain select * from emp where ename like '%T';
	+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
	| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
	+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
	|  1 | SIMPLE      | emp   | ALL  | NULL          | NULL | NULL    | NULL |   14 | Using where |
	+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

失效的第2种情况:使用or时,两边的条件至少有一个没有索引

	使用or的时候会失效,如果使用or那么要求or两边的条件字段都要有
	索引,才会走索引,如果其中一边有一个字段没有索引,那么另一个
	字段上的索引也会实现。所以这就是为什么不建议使用or的原因。

	mysql> explain select * from emp where ename = 'KING' or job = 'MANAGER';
	+----+-------------+-------+------+-----------------+------+---------+------+------+-------------+
	| id | select_type | table | type | possible_keys   | key  | key_len | ref  | rows | Extra       |
	+----+-------------+-------+------+-----------------+------+---------+------+------+-------------+
	|  1 | SIMPLE      | emp   | ALL  | emp_ename_index | NULL | NULL    | NULL |   14 | Using where |
	+----+-------------+-------+------+-----------------+------+---------+------+------+-------------+

失效的第3种情况:使用复合索引的时候,没有使用左侧的列查找,索引失效

	使用复合索引的时候,没有使用左侧的列(job)查找,索引失效
	什么是复合索引?
		两个字段,或者更多的字段联合起来添加一个索引,叫做复合索引。
	
	create index emp_job_sal_index on emp(job,sal);
	
	mysql> explain select * from emp where job = 'MANAGER';
	+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-------------+
	| id | select_type | table | type | possible_keys     | key               | key_len | ref   | rows | Extra       |
	+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-------------+
	|  1 | SIMPLE      | emp   | ref  | emp_job_sal_index | emp_job_sal_index | 30      | const |    3 | Using where |
	+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-------------+
	
	mysql> explain select * from emp where sal = 800;
	+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
	| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
	+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
	|  1 | SIMPLE      | emp   | ALL  | NULL          | NULL | NULL    | NULL |   14 | Using where |
	+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

失效的第4种情况:where当中索引列参加了运算,索引失效

	在where当中索引列参加了运算,索引失效。
	mysql> create index emp_sal_index on emp(sal);

	explain select * from emp where sal = 800;
	+----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+
	| id | select_type | table | type | possible_keys | key           | key_len | ref   | rows | Extra       |
	+----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+
	|  1 | SIMPLE      | emp   | ref  | emp_sal_index | emp_sal_index | 9       | const |    1 | Using where |
	+----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+

	mysql> explain select * from emp where sal+1 = 800;
	+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
	| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
	+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
	|  1 | SIMPLE      | emp   | ALL  | NULL          | NULL | NULL    | NULL |   14 | Using where |
	+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

失效的第5种情况:where当中索引列使用了函数

	在where当中索引列使用了函数
	explain select * from emp where lower(ename) = 'smith';
	+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
	| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
	+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
	|  1 | SIMPLE      | emp   | ALL  | NULL          | NULL | NULL    | NULL |   14 | Using where |
	+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

失效的第6...
失效的第7...


11、视图(view)

11.1、什么是视图?

view:站在不同的角度去看待同一份数据

11.2、怎么创建视图对象?怎么删除视图对象?

表复制:把查询出来的数据当作一张表创建出来

mysql> create table dept2 as select * from dept;

dept2表中的数据:
mysql> select * from dept2;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+

创建视图对象:把查询出来的数据当作一个视图创建出来

	create view dept2_view as select * from dept2;

删除视图对象:

	drop view dept2_view;

注意只有DQL语句(select)才能以view的形式创建。

	create view view_name as 这里的语句必须是DQL语句;

11.3、用视图做什么?对视图对象进行增删改查(CRUD),导致原表被操作- - -会影响原表数据

我们可以面向视图对象进行增删改查,对视图对象的增删改查,会导致
原表被操作!(视图的特点:通过对视图的操作,会影响到原表数据。)

//面向视图查询
select * from dept2_view; 

// 面向视图插入
insert into dept2_view(deptno,dname,loc) values(60,'SALES', 'BEIJING');

// 查询原表数据
mysql> select * from dept2;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
|     60 | SALES      | BEIJING  |
+--------+------------+----------+

// 面向视图删除
mysql> delete from dept2_view;

// 查询原表数据
mysql> select * from dept2;
Empty set (0.00 sec)


// 创建视图对象
create view 
	emp_dept_view
as
	select 
		e.ename,e.sal,d.dname
	from
		emp e
	join
		dept d
	on
		e.deptno = d.deptno;

// 查询视图对象
mysql> select * from emp_dept_view;
+--------+---------+------------+
| ename  | sal     | dname      |
+--------+---------+------------+
| CLARK  | 2450.00 | ACCOUNTING |
| KING   | 5000.00 | ACCOUNTING |
| MILLER | 1300.00 | ACCOUNTING |
| SMITH  |  800.00 | RESEARCH   |
| JONES  | 2975.00 | RESEARCH   |
| SCOTT  | 3000.00 | RESEARCH   |
| ADAMS  | 1100.00 | RESEARCH   |
| FORD   | 3000.00 | RESEARCH   |
| ALLEN  | 1600.00 | SALES      |
| WARD   | 1250.00 | SALES      |
| MARTIN | 1250.00 | SALES      |
| BLAKE  | 2850.00 | SALES      |
| TURNER | 1500.00 | SALES      |
| JAMES  |  950.00 | SALES      |
+--------+---------+------------+

// 面向视图更新
update emp_dept_view set sal = 1000 where dname = 'ACCOUNTING';

// 原表数据被更新
mysql> select * from emp;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 1000.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 1000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1000.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+

11.4、视图对象在实际开发中到底有什么用?《方便,简化开发,利于维护》

	create view 
		emp_dept_view
	as
		select 
			e.ename,e.sal,d.dname
		from
			emp e
		join
			dept d
		on
			e.deptno = d.deptno;
	
	
	假设有一条非常复杂的SQL语句,而这条SQL语句需要在不同的位置上反复使用。
	每一次使用这个sql语句的时候都需要重新编写,很长,很麻烦,怎么办?
		可以把这条复杂的SQL语句以视图对象的形式新建。
		在需要编写这条SQL语句的位置直接使用视图对象,可以大大简化开发。
		并且利于后期的维护,因为修改的时候也只需要修改一个位置就行,只需要
		修改视图对象所映射的SQL语句。

我们以后面向视图开发的时候,使用视图的时候可以像使用table一样。可以对视图进行增删改查等操作。视图不是在内存当中,视图对象也是存储在硬盘上的,不会消失。

再提醒一下:
视图对应的语句只能是DQL语句。但是视图对象创建完成之后,可以对视图进行增删改查等操作。

	小插曲:
		增删改查,又叫做:CRUD。
		CRUD是在公司中程序员之间沟通的术语。一般我们很少说增删改查。
		一般都说CRUD。

		C:Create(增)
		R:Retrive(查:检索)
		U:Update(改)
		D:Delete(删)


12、DBA常用命令?

重点掌握:
	数据的导入和导出(数据的备份)
	其它命令了解一下即可。(这个培训日志文档留着,以后忘了,可以打开文档复制粘贴。)

数据导出?(注意:在windows的dos命令窗口中:- - -不是登录mysql后进行

	mysqldump bjpowernode>D:\bjpowernode.sql -uroot -p123456
	
	可以导出指定的表吗?
	mysqldump bjpowernode emp>D:\bjpowernode.sql -uroot -p123456

数据导入?(注意:需要先登录到mysql数据库服务器上。)

	然后创建数据库:create database bjpowernode;
	使用数据库:use bjpowernode
	然后初始化数据库:source D:\bjpowernode.sql


12、数据库设计三范式重点内容,面试经常问

12.1、什么是数据库设计范式?

数据库表的设计依据。教你怎么进行数据库表的设计。

12.2、数据库设计范式共有?3个

第一范式

要求任何一张表必须有主键,每一个字段原子性不可再分。

在这里插入图片描述

解决方式:加入主键,拆分非原子性字段

在这里插入图片描述

第二范式

建立在第一范式的基础之上,要求所有非主键字段完全依赖主键,不要产生部分依赖。

在这里插入图片描述
解决方式:(处理多对多关系),使用三张表,关系表两个外键
在这里插入图片描述

第三范式

建立在第二范式的基础之上,要求所有非主键字段直接依赖主键,不要产生传递依赖。

在这里插入图片描述
解决方式:(处理一对多关系),使用两张表,(数据多的)表加外键

在这里插入图片描述

声明:三范式是面试官经常问的,所以一定要熟记在心!

设计数据库表的时候,按照以上的范式进行,可以避免表中数据的冗余,空间的浪费


12.3、总结表的设计?

一对多:
	一对多,两张表,多的表加外键!!!!!!!!!!!!

多对多:
	多对多,三张表,关系表两个外键!!!!!!!!!!!!!!!

一对一:
一对一放到一张表中不就行了吗?为啥还要拆分表?在实际的开发中,可能存在一张表字段太多,太庞大。这个时候要拆分表。

两种方案

复习:外键是引用,主键是不可重复、不为空

1主键共享t_user_datail中主键id,也是外键引用t_user_login中的id
在这里插入图片描述
2、外键唯一t_user的login_id作为外键引用t_user中的id,但此外键用unique修饰,表示其内容不能重复

	一对一怎么设计?
		没有拆分表之前:一张表
			t_user
			id		login_name		login_pwd		real_name		email				address........
			---------------------------------------------------------------------------
			1			zhangsan		123				张三				zhangsan@xxx
			2			lisi			123				李四				lisi@xxx
			...
		
		这种庞大的表建议拆分为两张:
			t_login 登录信息表
			id(pk)		login_name		login_pwd	
			---------------------------------
			1			zhangsan		123			
			2			lisi			123			

			t_user 用户详细信息表
			id(pk)		real_name		email				address........	login_id(fk+unique)
			-----------------------------------------------------------------------------------------
			100			张三				zhangsan@xxx							    	1
			200			李四				lisi@xxx										2


			口诀:一对一,外键唯一!!!!!!!!!!

12.4、嘱咐一句话:

数据库设计三范式是理论上的。

实践和理论有的时候有偏差。

最终的目的都是为了满足客户的需求,有的时候会拿冗余换执行速度。

因为在sql当中,表和表之间连接次数越多,效率越低。(笛卡尔积)

有的时候可能会存在冗余,但是为了减少表的连接次数,这样做也是合理的,
并且对于开发人员来说,sql语句的编写难度也会降低。

面试的时候把这句话说上:他就不会认为你是初级程序员了!
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值