数据库day01（复习）

数据库概述

1.数据库的概述
存储数据的的仓库
a.数据库的发展
层次数据库、网络式数据库、关系型数据库、非关系型数据库
b.什么是关系型数据库
以行和列的形式组成的数据库就是关系型数据库
c.关系型数据库的类别
i.免费开源
mysql
ii.收费
oracle

2.数据库存储数据的结构
a.Client指客户端
b. MySQL指服务器
c. DB值数据库，一般情况下一个项目创建一个数据库。
d.表指数据库表，在一个数据库中可以创建多张表。一般情况下一个功能创建一张表。

3．表格中存储数据的方式

4．数据库的操作语言
a. sQL结构化查询语言
b.特征:非过程性语言。上下句sql之间没有逻辑关系。

5.数据库操作权限

操作数据库

1.创建数据库

create database database_name ;
~oncreate database if not exists database_name;

2.查看数据库
查看全部数据库.
show databases;使用数据库:
use database_name;
查看数据的创建:(可以查询数据库字符集使用)

show create database database_name; 
/*create database .....*/
charset=utf-8

3.修改数据库–一般情况下不会修改数据库

alter database database_name character set gbk;

为什么不会经常修改数据库?
软件工程分析。
i.可行性分析–收益、法律要求
ii.需求分析–分析软件有哪些功能
iii.总体设计–设计数据库和表格结构和功能涉及的字段。
iv.详细设计–指定功能的实现内容，和数据库表格中数据的规范
v.代码实现
vi.测试
vii.上线
viii.线上维护

4.删除数据库

drop database database_name;

操作表

1.创建表
create table table_name(
id int,
name varchar(20));

a.字段类型
数值型int double float tiny int smallint bigint字符串型
char(20)查询效率较快。
char指定的字段创建为定长，在查询的时候只需要读取固定长度的数据即可。
如果只存储2个字符的数据，占据的存储空间仍然是20个字符。
varchar(20)查询效率较慢。varchar比char类型更加节省空间，虽然指定存储空间是20个字符，但是真正在存储数据的时候只会占据数据本省的长度，不会占用其他剩余空间，所以相对来说varchar更加节省空间。

日期型
date time datetime timestamp时间戳
大数据类型
逻辑型

b.约束–键
i. primary key主键
唯一性和非空性
auto_increment自动增长
1）主键可以是任意类型字段，但是并不会自动增长，只有在主键字段为数值型的情况下，才可以设置为自动增长。
2)如果一个字段为自动增长的字段，那么这个字段一定会作为主键使用。
3）主键:一个表格一般情况下都会设置一个主键，利用主键可以提升查询效率。
ii. not nul1非空约束
iii. unique唯一约束
iv. foreign key外键

2.查询表
a.查询当前数据库中所有的表格:
show tables;
b.查询当前表格的创建语句:
show create table table_name;
数据库引擎:

lnnoDB:支持事务。执行效率较低。
Mylsam:不支持事务。执行效率较高。
c.修改表

alter table table_nameadd/drop/modify name varchar(20) ;
alter table table_namechange name username varchar(20);
~rename table emp to employee;

d.删除表

drop table table_name;

操作表数据

1.数据的操作
增删改查CRUD
a.插入数据

insert into table_name values(colunm_name, colunm_name.. . . );
insert into table_name(id,name) values(1,'a');

在插入数据时需要确定表名后的字段顺序，以便在values后按照指定的字段顺序书写对应值。
i.注意:
1)插入数据的类型和字段类型要匹配。
2)插入数据的长度不能超过字段要求的长度。
3)字符串和日期类型的数据需要使用单引号或者双引号括起来。(英文符号)
b.删除数据
delete from table_name;#删除整张表中的数据
truncate table_name;#删除整张表，包括表结构。
truncate效率相比delete from更高。
如果是多表，多表之间存在表间关系，直接truncate删除表可能会导致表间关系不再存在。

delete from table_name where id = 1;delete from table_name where id > 1;

c.更新数据

update table_name set column_name= value;#更新整张表中的字段数据。
update table_name set column_name = value where name= 'xxx';

2.查询数据

select * from table_name ;
select kolumn_name..... from table table_name;

以上两种查询效率如何?
以上两者的查询方式，如果在有索引的情况下，效率相同。只是直接书写全部字段名称查询速度相对指书写*要稍高一些，这个速度可以忽略。

a.别名: as

select math+english+chineseas sum from exam;
select math+english+chinesesum from exam e;

b.条件查询
where包含的条件等值查询:=
范围查询:> < >= < like in() is null
c. order by子句
对查询结果排序。
order by在sql语句书写时要放在最后。
order by是所有关键字中最后一个执行的关键字。
from where select having group by order by

select * from exam where name='xx’ order by name='xxx’;

i. order by子句一定要慎用，因为任何查询使用这个关键字后，查询效率都会降低。

d.聚集函数
count计数
记录某一个字段在表中有几行。

select count(*| column_name) from table_name;
select count(1) from table_name;
select count(*) from table_name;

count(1)表示计数表格中第一个字段的行数。
在表格中字段的数量较少的情况下（不超过10个),
count(*) , count(1)两者的查询效率认为是一致的。

ii. sum求和

select sum(math) from table_name where .....;

iii. avg求平均

select avg (math) from table_name where.. . . ;

iv. max,min

select max(col_name),min(col_name) from table_name where
.... 

3 group by分组
group by column_name;根据指定的字段名称分组。

select count(*) from table_name group by id; #根据id分组，分组之后再对各组计数。

where和having区别: where在分组前进行条件过滤，having在分组后进行条件过滤。使用where的地方都可以用having替换。但是having可以使用分组函数，而where后不可以使用。

表间关系
一对一
一对多
多对多

1. case when then else end

a. gender char(1) 1 2 
select gender
case gender
when 1 then "男"
when 2 then "女"
else
N/A
end

多表操作

1.笛卡尔积查询
笛卡尔积查询结果数据量=左边表的数量*右边表的数量
select * from table_name,table_name1;

select * from table_name,table_name1
where table_name1.id = table_name1.t_id;

2.内连接查询

在笛卡尔积查询的基础之上获取左边有且右边表也有的数据。
select * from dept d
inner join emp e
on d.id = e.dept_id;

3.外连接查询
a.左外连接
在内连接查询的基础之上，添加左边表有但是右边表没有的数据。

select * from dept d
leftjoin emp e
on d.id = e.dept_id;

b.右外连接
在内连接查询的基础之上，添加右边表有但是左边表没有的数据。

select * from dept d
leftjoin emp e
on d.id = e.dept_id;

c.全外连接
mysql中没有全外连接关键字full join可以使用union关键字实现全外连接的效果。

select * from dept dleft join emp e
on d.id = e.dept_id
union
select * from dept dright join emp eon d.id = e.dept_id;

事务
1.事务概述
一个事情可以分为多个步骤，这些步骤要么全部成功，要么全部失败，这件事情就可以称之为一个事务。

2．数据库cmd窗口控制事务
mysql数据库拥有默认的事务能力，一个sql语句，就是一个事务。

3．程序控制事务
JDBC—一套接口
mysql — jar
oracle ---- jar

4.事务四个特性:
原子性:事务是一个不可分割的单位，这个单位中的所有操作，要么全部成功要么全部失败。
一致性:事务执行前后，数据在完整性上仍然保持一致。
隔离性:一个事务不可以被另一个事务所影响，所以两者应对相互独立，这就需要将两个事务相互隔离，保证两个事务之间没有任何联系。
持久性:一旦事务被提交，数据库中的数据就发生了真实的修改，这个过程是不可以逆的。

5．隔离性
a.数据库中的原子性、一致性和持久性，数据库服务器都已经作出了实现，我们直接使用这个特性即可，唯独没有实现隔离性，而是提供了四个选项，供用户按照自己的需求，自行选择。这四个选项就是数据库的隔离级别。
b.隔离性本质是想解决线程安全问题，在面对线程安全问题时，不同业务需求对于线程的安全性要求也是不同的，所以每一个数据库使用者，都应该自行选择隔离级别来处理不同的线程安全问题。
c.两个事务都是查操作，是否存在线程安全问题。
d.一个事务读，另一个事务写，是否存在线程安全问题。
存在。
脏读、不可重复读、虚读/幻读

e.两个事务写，是否存在线程安全问题。
存在。
f.隔离级别

set global/session transaction isolation level
任意隔离级别。

g.数据库中的锁
数据库中使用隔离性解决线程安全问题，本质是使用锁来解决线程安全问题。
i.排它锁和共享锁

在非serializable隔离级别之下，查询不添加锁。
在serializaEle隔离级别之下，查询添加共享锁。
在任意隔离级别之下，更新添加排它锁。

共享锁和共享锁可以共存。
共享锁和排它锁不可以共存。
排它锁和排它锁不可以共存。

h.死锁
数据库中一旦发生死锁，数据库服务器会自动选择一个事务，将其销毁，销毁之后，事务中的数据都会回滚到之前的数据内容。

i.~表级锁和行级锁

b.什么是子查询?
i.子查询就是嵌套在主查询中的查询，也称之为内部查询。
例如上方语句:

select name,english from exam where english in(select max(english) from exam);

其中的in括号中的select max(english) from exam，就是一个子查询。它本身也是一个查询语

ii．主查询
包含子查询的查询就是主查询，也称之为外部查询。例如上方语句:

select name,english from exam where english in (select max(english) from exam);其中的in括号外的select name,english from exam where english in(xxx) ;

就是主查询。注意:主查询中select关键字后的字段名称才是要查询的字段名称。

c.子查询可以嵌套在主查询中所有位置
SELECT、FROM、WHERE、GROUP BY、HAVING、ORDER BY。

子查询分类︰
a.非相关子查询:
非相关子查询的执行不依赖于外部的查询。
i、特点∶
子查询总共执行一次，执行完毕后后将值传递给外部查询。
ii.执行过程:
1)执行子查询，其结果不被显示，而是传递给外部查询，作为外部查询的条件使用。
2)执行外部查询，并显示整个结果。
ili、非相关子查询一般可以分为:返回单值的子查询和返回一个列表的子查询，
下面举例说明∶

b.相关子查询:
相关子查询是指引用了外部查询中出现的表中列的子查询。
i.特点︰
外部查询返回一行，子查询就执行一次。
例如，当前外部查询一共返回10条结果，那么子查询就执行或理解为
相关子查询的执行依赖于外部查询。多数情况下是子查询的WHERE子句中引用了外部查询的表。(简单来说∶就是table1在该select外面，而select里面table1.xx=table2.xx)
ii．执行过程:
( 1)从外层查询中取出一行，将行相关列的值传给内层查询。
( 2)执行内层查询，得到子查询操作的值。
( 3)外部查询根据子查询返回的结果或结果集得到满足条件的行。
(4)然后外层查询取出下一个行重复做步骤1-3，直到外层的行全部处理宗毕.

SELECT s.student_id,s.student_name,(SELECT class_name FROM t_class c WHERE c.class_id=s.class_id) FROM t_student s GROUP BY s.student_id;         

相关子查询中子查询次数多，效率慢

mysql存在则插入，不存在则删除

insert into test_table(id,username)
VALUES(4,'fukaiit')
ON DUPLICATE KEY UPDATE username='fukaiit'

执行如上sql，该id不存在(此处id不存在，指的是id为X的记录不存在，包括主键、unique索引列等情况)，相当于执行了insert部分，普通插入，受影响的行：1。

REPLACE INTO test_table(id,username)
VALUES(5,'fukaiit');

执行如上sql，该id不存在，相当于执行了insert，普通插入，受影响的行：1。

REPLACE INTO test_table(id,username)
VALUES(1,'fukaiit');

执行如上sql，该id已存在，相当于执行了删除再插入的操作，受影响的行：2。
执行结果如下，注意删除后再插入是没有email值的，所以此时email为null，注意与ON DUPLICATE KEY UPDATE的区别。

INSERT IGNORE INTO test_table(id,username) 
values(6,'fukaiit');

执行如上sql，该id不存在，相当于执行了insert，普通插入，受影响的行：1。

INSERT IGNORE INTO test_table(id,username) 
values(1,'fukaiit_new');

执行如上sql，该id已存在，忽略了该插入操作，数据没有变化，受影响的行：0。

使用DUAL虚表和NOT EXISTS

该方法与INSERT IGNORE INTO … VALUES…方法的效果相同。

INSERT INTO test_table(id,username)
SELECT 7,'fukaiit' from DUAL 
where NOT EXISTS (select * from test_table where id=7);

执行如上sql，该id不存在，select子句返回结果集为空，NOT EXISTS成立，相当于执行了insert，普通插入，受影响的行：1。

INSERT INTO test_table(id,username)
SELECT 2,'fukaiit' from DUAL 
where NOT EXISTS (select * from test_table where id=2);

执行如上sql，该id已存在，select子句返回结果集不为空，NOT EXISTS不成立，则未执行插入操作，数据没有变化，受影响的行：0。

总结:
-避免使用select*
count(1)或count(列)代替count(*)
-创建表时尽量时char 代替varchar
表的字段顺序固定长度的字段优先
-组合索引代替多个单列索引（经常使用多个条件查询时)
-尽量使用短索引
-使用连接（JOIN）来代替子查询(Sub-Queries)
-连表时注意条件类型需一致
·索引散列值（重复少）不适合建索引，例:性别不适合