WHCSRL 技术网

Database collation

MySql

数据约束

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

约束分类

  1. 实体完整性

     实体完整性:确保每条记录的唯一性,能够被唯一区分。
    
     	主键约束(整数,字符串):primary key
    
     		特征:加了主键约束的列,值必须唯一,并且不能为空。
    
     					针对主键约束的列会自动生成索引文件
    
     					一个表中只能有一个主键。但是主键可以有多个列构成(联合主键)
    
     		特点:1. 主键列的数据不能重复 
    
     					2.主键列的数据不能为空 
    
     					3.主键列可以是整数也可以是字符串
    
     					4. 主键列可以由一列构成,也可以由多列构成(联合主键)。一个表中只能有一个主键
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
#主键约束
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) #联合主键,学号和课程编号联合起来是唯一的
)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17

自增约束(整型):auto_increment

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

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

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

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

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

						4.自增约束会在前一条记录的基础上增1,默认从1开始
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
#自增约束
create table student
(
	studentId int primary key  auto_increment,
	name varchar(20),
	sex char(1),
	age int
)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8

唯一(索引)约束:unique

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

						2.列值允许空(可以有多个空值)
  • 1
  • 2
  • 3
#唯一约束
create table student
(
	studentId int primary key auto_increment,
	name varchar(10),
	sex char(1),
	phone varchar(11) unique #唯一约束
)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  1. 域完整性

     域完整性:针对数据的取值做约束。
    
     	非空约束:not null 	
    
     		特征: 加了该约束的列,不能为空,该列必须有值
    
     	默认值约束:default	
    
     		特征: 加了默认值约束的列,如果没有提供值时,自动用默认值替代。如果提供了值则用提供值的填充。
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
#默认值约束
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 '郑州' #默认值
)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9

外键约束:foreign key

			特征::存在于多表之间,如果一个表中某列的数据依赖于另外一个表的数据,此时要对这个列加外键约束。该列的值不能随便写。
  • 1
#外键约束
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) #关联另外一个表的那个列
)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11

check约束:mysql没有,ms SQLserver有 限定值的范围(性别:男,女)

		注意:不写外键规则,但存在外键约束,这种称为逻辑外键。定义外键规则,称为物理外键。
  • 1
  1. 引用完整性

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

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

补充
代码结构 说明

desc 表名 查看表结构信息

show tables 显示表的数据结构

drop 表名 删除表

use 数据库名 使用数据库

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

查询

第一节 增删改语句

1.1 级联

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

1、删除数据时:需要先删外键表,再删主键表 

2、更新数据时: 

3、录入数据时:先录入主键,再录入外键 

级联:当更新(删除)主键表中的数据时,外键表中引用的数据会自动更新(删除) 

cascade:删除主键时自动删除外键表中引用的数据,修改时自动更新外键表中引用的数据
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
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 #针对外键设置更新级联规则
)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12

第二节 查询

查询语法:

select  #查询  列1,列2

			#输出的结果

from 表名

where 条件

group by 列 

having 条件

order by 列 升序/降序

limit 位置,条数
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17

1.1 去重查询

特点:尽量少用,会影响性能
  • 1
select distinct 列名 from 表名;
  • 1

1.2 条件查询where语句

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

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

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

逻辑运算符:

		and(&&):并且 :多个条件同时成立 就true 否则返回false

		not(!):非 :取反 

		or(||):或 ,多个条件只要有一个成立,结果:true ,都不成立:false

范围查询

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

	in (范围数据): 针对数值,非数值进行范围过滤。
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
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
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9

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 表一,表二
  • 1
  • 2
  • 3
  • 4
  • 5
#请查询学生的学号,姓名,性别,班级名称
SELECT
	student.studentId,
	student.name,
	student.sex,
	classinfo.className
from student,classinfo
where student.classId = classinfo.classId
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8

b:

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

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'
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

c:

left [outer] join 左外连接
  • 1
select
	s.*,c.*
from student s   # 写在前面的表称为左表
left outer join classInfo c  #写在后面的表称为右表
on s.classId = c.classId
  • 1
  • 2
  • 3
  • 4
  • 5

d:

right [outer] join 右外连接
  • 1
select 
  c.*,s.*
from classInfo c
right join student s
on c.classId = s.classId
  • 1
  • 2
  • 3
  • 4
  • 5

e:
cross join 交叉连接

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

四表查询:

where 等值连接

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

并且按照科目倒序排列
  • 1
  • 2
  • 3
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降序
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

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
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
#查询参加英语考试的学生
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 = '英语'
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8

子查询

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

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

子查询实现: 学生的班级编号等于AAA01班级编号
  • 1
  • 2
  • 3
  • 4
  • 5
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 = '英语')
)

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38

相关子查询

自己查询不能独立运行,要依赖查询的数据
三表关联
  • 1
  • 2
select studentId,courseId,
	(select studentname from student where studentId =exam.studentId ) as 学生姓名,
	(select courseName from course where courseId = exam.courseId) as 课程名字
from exam;
  • 1
  • 2
  • 3
  • 4

分组查询,关联查询

查询每个学生的所有课程总分:学生姓名,总分
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
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
#关联查询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
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
推荐阅读