数据库整理

MySql

数据约束

数据约束是针对表中的列加约束。

约束分类

1. 实体完整性

    实体完整性：确保每条记录的唯一性，能够被唯一区分。
   
    	主键约束（整数，字符串）：primary key
   
    		特征：加了主键约束的列，值必须唯一，并且不能为空。
   
    					针对主键约束的列会自动生成索引文件
   
    					一个表中只能有一个主键。但是主键可以有多个列构成(联合主键)
   
    		特点：1. 主键列的数据不能重复 
   
    					2.主键列的数据不能为空 
   
    					3.主键列可以是整数也可以是字符串
   
    					4. 主键列可以由一列构成，也可以由多列构成(联合主键)。一个表中只能有一个主键
   

#主键约束
create table student
(
	studentId int primary key,  
	name varchar(20),
	sex char(1),
	age int
)

#联合主键
create table exam
(
	studentId int, #学号
	courseId int , #课程编号
	score int , #分数
	primary key (studentId,courseId) #联合主键，学号和课程编号联合起来是唯一的
)

自增约束（整型）：auto_increment

			特征：自增约束只能加到整数类型的列上，一个表只能有一个列加自增约束

						该列的值会有系统自动生成，数据也是唯一的，一般和主键约束一起使用

			特点：1.针对自增约束的列系统自动生成唯一编号

						2.自增约束必须在主键约束的基础上使用，主键列必须是整数类型

						3.自增约束不一定保证连续性

						4.自增约束会在前一条记录的基础上增1,默认从1开始

#自增约束
create table student
(
	studentId int primary key  auto_increment,
	name varchar(20),
	sex char(1),
	age int
)

唯一（索引）约束：unique

			特征：1.唯一约束的列数据必须唯一 

						2.列值允许空(可以有多个空值)

#唯一约束
create table student
(
	studentId int primary key auto_increment,
	name varchar(10),
	sex char(1),
	phone varchar(11) unique #唯一约束
)

2. 域完整性

    域完整性：针对数据的取值做约束。
   
    	非空约束：not null 	
   
    		特征: 加了该约束的列，不能为空，该列必须有值
   
    	默认值约束：default	
   
    		特征: 加了默认值约束的列，如果没有提供值时，自动用默认值替代。如果提供了值则用提供值的填充。
   

#默认值约束
create table student
(
	studentId int primary key auto_increment,
	name varchar(20) not null,
	sex char(1) not NULL,
	phone varchar(11) not null unique,
	address varchar(20) default '郑州' #默认值
)

外键约束：foreign key

			特征:：存在于多表之间，如果一个表中某列的数据依赖于另外一个表的数据，此时要对这个列加外键约束。该列的值不能随便写。

#外键约束
create table student
(
	studentId int primary key auto_increment,
	name varchar(20),
	sex char(1),
	birthday date,
	bjId int , #对班级编号加外键约束
	foreign key (bjId) # 定义当前表中哪个列作为外键
	references classInfo(classId) #关联另外一个表的那个列
)

check约束：mysql没有，ms SQLserver有 限定值的范围（性别：男，女）

		注意：不写外键规则，但存在外键约束，这种称为逻辑外键。定义外键规则，称为物理外键。

3. 引用完整性

    引用完整性：多表之间有数据的依赖关系，要确保依赖关系的正确性
   
    	外键约束：foreign key	特征：存在于多表之间，如果一个表中某列的数据依赖于另外一个表的数据，此时要对这个列加外键约束。该列的值不能随便写。
   
    	注意：
   
    	1.有外键关联的两个表，从表插入的外键列的数据必须在主表中存在
   
    	2.主表中的数据被关联的情况下，不能删除
   
    	3.主表中的数据被关联的情况下，不能修改
   
    	4.有外键关联的两个表，删除的时候，先删除从表再删除主表
   

4. 用户自定义完整性

    用户自定义完整性: 特殊业务规则的定义，目前不做深入。
   
    用其他特定规则实现数据约束。
   

补充
代码结构 说明

desc 表名 查看表结构信息

show tables 显示表的数据结构

drop 表名 删除表

use 数据库名 使用数据库

show databases 显示当前连接下的所有数据库

查询

第一节 增删改语句

1.1 级联

涉及到有主外键关联的数据表： 

1、删除数据时：需要先删外键表，再删主键表 

2、更新数据时： 

3、录入数据时：先录入主键，再录入外键 

级联：当更新(删除）主键表中的数据时，外键表中引用的数据会自动更新(删除） 

cascade:删除主键时自动删除外键表中引用的数据，修改时自动更新外键表中引用的数据

create table emp
(
	empId int primary key auto_increment,
	empName varchar(20) not null,
	sex char(1) not null,
	birthday date,
	deptId int,
	foreign key (deptId)
	REFERENCES dept(deptId)
	on delete CASCADE #针对外键设置删除级联规则
  	on update cascade #针对外键设置更新级联规则
)

第二节 查询

查询语法：

select  #查询  列1，列2

			#输出的结果

from 表名

where 条件

group by 列 

having 条件

order by 列 升序/降序

limit 位置,条数

1.1 去重查询

特点：尽量少用，会影响性能

select distinct 列名 from 表名;

1.2 条件查询where语句

SQL语句中的条件表达式：查询条件、判断条件、修改条件 

条件运算符：=  >  <  >=  <=   不等：<> 和 != 

算术运算符：+ - * / %（mod) 不支持：++ -- 复合赋值 += -= 

逻辑运算符：

		and（&&）：并且 ：多个条件同时成立 就true 否则返回false

		not（!）：非 :取反 

		or（||）:或 ，多个条件只要有一个成立，结果：true ，都不成立：false

范围查询

	between 开始值 and 结束值: 用于数值数据(有范围)。

	in (范围数据): 针对数值，非数值进行范围过滤。

SELECT
	*
from emp
where birthday between '2000-01-01' and '2005-12-31 23:59:59'

select
	empId,empName,sex,birthday
from emp
where empId in (1,3,5)

1.3 模糊查询

1.3.1 通配符

用% 通配符实现模糊查询

a. % 匹配任意长度的任意内容

b. 与like结合使用

用_ 通配符实现模糊查询

a. _ 匹配单个任意内容

b. 必须和like结合使用

1.4having用法

1.与where的区别:where用在分组之前过滤数据,having用在分组之后过滤数据

1.5多表关联

1.在数据库中不同的数据都要独立存储到一张表中,这些数据之间也会存在一些联系,如果要多表数据,就要将多张表关联起来

多表关联前提:两表之间必须有对等的数据列

eg:

学生(学号,姓名,性别,班级编号)，班级(班级编号,班级名字) : 对等数据列就是班级编号

学生(学号,姓名,性别,班级编号), 商品(商品编号,商品名字,价格): 没有对等数据列，不能关联

学生(学号,姓名,性别,班级编号), 课程(课程编号,课程名字) : 没有对等数据列，不能关联

学生(学号,姓名,性别,班级编号),成绩(成绩编号,学号,课程编号,分数): 有对等数据列学号，可以关联

课程(课程编号,课程名字),成绩(成绩编号,学号,课程编号,分数): 有对等数据列课程编号，可以关联

a:

where等值连接:

两表中对等数据的值必须对应的才能出来

select  from 表一,表二

#请查询学生的学号，姓名，性别，班级名称
SELECT
	student.studentId,
	student.name,
	student.sex,
	classinfo.className
from student,classinfo
where student.classId = classinfo.classId

b:

[inner] join 内连接(等值连接)

SELECT *

from 表1 别名1

[inner] join 表2 别名2

on 别名1.对等数据列 = 别名2.对等数据列 # 必须加条件

where 条件

group by 列

having 条件

order by 列

limit 位置,记录数

limit 位置,记录数

SELECT
	s.studentId,s.name,s.sex,c.className
from student s
join classInfo c
on s.classId = c.classId 
where c.className = 'AAA01'

c:

left [outer] join 左外连接

select
	s.*,c.*
from student s   # 写在前面的表称为左表
left outer join classInfo c  #写在后面的表称为右表
on s.classId = c.classId

d:

right [outer] join 右外连接

select 
  c.*,s.*
from classInfo c
right join student s
on c.classId = s.classId

e:
cross join 交叉连接

#交叉连接(不需要条件)
select s.*,c.*
from student s 
cross join classInfo c

四表查询:

where 等值连接

查询所有学生的:班级名字,学生名字,性别,课程名字,分数

并且按照科目倒序排列

select
	ci.className,s.studentname,s.sex,co.courseName,e.score
from student s ,class ci,course co,exam e
where s.classId = ci.classId
and s.studentId = e.studentId
and co.courseId = e.courseId
order by co.courseName asc,e.score desc #asc升序 desc降序

inner join 连接

SELECT
	ci.className,s.studentname,s.sex,co.courseName,e.score
from student s 
join class ci
on s.classId = ci.classId # 两表连接之后就变成一张表
join exam e   
on s.studentId = e.studentId
join course co
on e.courseId = co.courseId

#查询参加英语考试的学生
select s.studentId,s.studentname,s.sex,c.courseName,e.score
from exam e
join course c
on e.courseId = c.courseId 
join student s
on e.studentId = s.studentId
where c.courseName = '英语'

子查询

子查询: 在查询中再嵌套另外一个查询

简单子查询: 子级查询能够独立运行

子查询实现: 学生的班级编号等于AAA01班级编号

select * from student
where classId = (
	select classId from class where className = 'AAA01'
)

select s.*,c.*
from student s
join class c
on s.classId = c.classId
where c.className = 'AAA01'

查询语文是78份以上学生编号
select studentId from exam
where courseId =
(select courseId from course where coursename = '语文')
and score>78

select * from student 
where studentId in (
select studentId from exam
where courseId = (select courseId from course where courseName = '语文') and score>78
)

查询参加英语考试的学生
select * from exam
where courseId = 
(select courseId from course
 where courseName = '英语')

从学生信息中将参加英语考试的学生排除掉
select * from student
where studentId not in
(
select studentId from exam
where courseId = 
(select courseId from course where courseName = '英语')
)

相关子查询

自己查询不能独立运行,要依赖查询的数据
三表关联

select studentId,courseId,
	(select studentname from student where studentId =exam.studentId ) as 学生姓名,
	(select courseName from course where courseId = exam.courseId) as 课程名字
from exam;

分组查询,关联查询

查询每个学生的所有课程总分:学生姓名,总分
SELECT
	s.studentname,aa.总分
from student s
join (
	select studentId,sum(score) as 总分 from exam  #临时表/虚拟表
	group by studentId
) as aa
on s.studentId = aa.studentId

#关联查询2
select s.studentname,sum(e.score) as 总分
from student s
join exam e
on s.studentId = e.studentId
group by s.studentname


#相关子查询
select (select name from student where studentId =exam.studentId ) as 姓名,sum(score) as 总分 
from exam
group by studentId