Java学习 day40_SQL

查询语句执行顺序（数字代表执行顺序）

​ (5) SELECT column_name, …

​ (1) FROM table_name, …

​ (2) [WHERE …]

​ (3) [GROUP BY …]

​ (4) [HAVING …]

​ (6) [ORDER BY …];

​ (7) [LIMIT]

4. 数据完整性

• 实体完整性

  实体完整性其实就是指的每一个表都应该有一个主键，不然用户可能会输入一个重复的数据

  create table person(
      -- 声明id这一列是主键
  	id int PRIMARY KEY,
  	name varchar(20) ,
  	gender varchar(10),
  	age int
  )character set utf8;
  

主键有两个特点

• 不能重复
• 不能为空

一般我们自己去维护这个主键不能重复有点麻烦，所以MySQL给我们提供了一个关键字 auto_increment，这个字段的意思其实就是我们可以让主键由MySQL服务来帮我们维护，会自增。

create table person(
    -- 我们在MySQL里面，推荐大家使用主键自增的策略，主要是从插入数据的效率方面来考虑的
	id int PRIMARY KEY auto_increment,
	name varchar(20) ,
	gender varchar(10),
	age int
)character set utf8;

注意：

• 如果一次添加失败，也会使用到自增，下次再添加，即使数据库中没有这个数，仍然会跳过这个数继续添加
• 如果前面是1，2，3，你添加一个5也可以添加进去，后面再使用自增，就是从6开始

• 域完整性

  域完整性是指我们的表中的每一列都应该有一个具体的数据类型或者是约束条件。

  • not null 不为空

  • unique 不重复

    unique和主键有什么区别呢？

    主键不能为空，unique可以为空，还有一点就是主键和unique底层的存储方式不一样。

• 参照完整性

  参照完整性指的就是外键

  

  有了外键之后，

  • 我们发现当我们新增城市表的数据的时候，我们会根据新增的省份id去查询省份表中是否有对应的id，其实就是去检查这个id是否合法，如果有，就插入，如果没有，就报错
  • 当我们去删除省份表中的数据库的时候，我们会去检查在城市表中是否还有该省份对应的数据，如果有，就不执行删除指令，直接报错，如果没有才会执行删除指令

  我们发现，在我们使用了外键之后，那么会影响到表的删除和插入的效率。并且假如当你使用了外键的时候，那么会影响后期维护数据的难度。
  一般在企业中，表里面的数据比较多，我们不使用外键

  create table city(
  	id int PRIMARY KEY auto_increment,
  	name varchar(20) unique not null, 
  	p_id int,
      -- 外键的语法 pid_fk相当于别名吧，没什么太大作用
  	constraint pid_fk foreign key(p_id) REFERENCES province(id)
  )character set utf8;
  

5. 多表设计

一对一

用户表和用户详情表、学生和学号、人和身份证号

他们是一一对应的

一对一其实就是可以去增加一个字段，维护两个表之间的关系，这个字段增加在两方的任意一方中即可

一对多

班级和学生、国家和省份、订单和商品、省份和城市

多对多

学生和课程（互为一对多的关系，叫做多对多）

三大范式

第一范式

第一范式指的就是原子性，比如用户的收获地址

我们在做数据库的设计的时候，应该要严格的按照需求出发，做一些符合当前产品需求的设计。一般来说，都会让每一列保持原子性。

第二范式

第二范式就是指唯一性。也就是每一个表都有自己的主键。

第三范式

第三范式是指字段不要冗余，不要存在传递依赖。

不要存在传递依赖的意思是：换句话来说就是一张表只讲一件事情，不要讲多件事情

在上述第三范式的例子中，假如我们有一个需求是根据学生的姓名查询对应的学院的名字，那么第一种设计思路可以通过一次就查询出来，但是第二种设计虽然节省了空间，但是要通过查两张表才能查出来，这个时候查询的效率会变低。当我们这种查询的需求远大于磁盘空间的需求和插入的需求的时候，那么我们可以做适当的冗余。这种设计在公司中是很常见的，叫做反范式化设计。

6. 多表查询

6.1 连接查询

• 交叉连接

  

  交叉连接的结果没有实际的意义

• 内连接

  内连接只会保留所有能匹配上的数据

  • 显式内连接

    select * from user inner join user_detail on user.id = user_detail.user_id;

  • 隐式内连接

    select * from user,user_detail where user.id = user_detail.user_id;

• 外连接

  • 左外连接

    左外连接会保留左表的全部数据

    关于左表和右表，其实是看谁在join关键字的左边，谁就是左表，谁在join关键字的右边，谁就是右表

    SELECT
    	id,
    	u.username,
    	u.nickname,
    	d.weight,
    	d.height,
    	d.pic 
    FROM
    	USER AS u
    	LEFT OUTER JOIN user_detail AS d ON u.id = d.user_id;
    

  • 右外连接

    左外连接会保留右表的全部数据

    SELECT
    	*
    FROM
    	USER RIGHT OUTER JOIN user_detail ON USER.id = user_detail.user_id;
    

练习：

-- 查询张三这个学生的学生信息以及选的课程
-- 内连接
-- 显式内连接
SELECT
	s.NAME AS sname,
	c.teacher_name 
FROM
	student AS s
	INNER JOIN s_c AS sc ON s.id = sc.sid
	INNER JOIN course AS c ON sc.cid = c.id 
WHERE
	s.NAME = '张三';
-- 隐式内连接
SELECT
	* 
FROM
	student AS s,
	s_c AS sc,
	course AS c 
WHERE
	s.id = sc.sid 
	AND sc.cid = c.id 
	AND s.NAME = '张三';
-- 外连接
-- 左外连接
SELECT
	* 
FROM
	student AS s
	LEFT JOIN s_c AS sc ON s.id = sc.sid
	LEFT JOIN course AS c ON c.id = sc.cid 

WHERE
	s.NAME = '张三';
-- 右外连接
SELECT
	* 
FROM
	student AS s
	RIGHT OUTER JOIN s_c AS sc ON s.id = sc.sid
	RIGHT OUTER JOIN course AS c ON c.id = sc.cid 
WHERE
	s.NAME = '张三';

在做连接查询的时候，重点是我们需要清楚的知道这些表连接的条件。

6.2 子查询

-- 要去查询一班有哪些同学
select * from clazz where name = '一班';
select * from student where clazz_id = 1;
-- 把一个sql语句的查询结果作为另一个sql语句的查询条件
select * from student where clazz_id = (select id from clazz where name = '一班');

子查询又称为嵌套查询。其实就是存在sql语句的嵌套。

6.3 联合查询

联合查询其实就是把插叙的结果联合起来。一般来说没什么用

select * from student where id  = 1
union
select * from student where id  = 2;

联合查询要求我们联合起来的多条sql语句的查询结果的字段是一致的

7. 数据库的备份与恢复

7.1 命令方式

备份
mysqldump -uroot -p dbName>fileNamePath

备份完之后的结果其实就是一个sql文件，这个sql文件里面记录了这个库里面的所有的表的创建sql语句以及所有的数据。

恢复
因为生成的备份文件中没有生成库的语句，所以首先得新建一个数据库
选中数据库
执行指令
source filePath

7.2 图形化界面方式