Database collation
MySql
数据约束
数据约束是针对表中的列加约束。
- 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
-
域完整性
域完整性：针对数据的取值做约束。 非空约束：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
-
引用完整性
引用完整性：多表之间有数据的依赖关系，要确保依赖关系的正确性 外键约束：foreign key 特征：存在于多表之间，如果一个表中某列的数据依赖于另外一个表的数据，此时要对这个列加外键约束。该列的值不能随便写。 注意： 1.有外键关联的两个表，从表插入的外键列的数据必须在主表中存在 2.主表中的数据被关联的情况下，不能删除 3.主表中的数据被关联的情况下，不能修改 4.有外键关联的两个表，删除的时候，先删除从表再删除主表- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
-
用户自定义完整性
用户自定义完整性: 特殊业务规则的定义，目前不做深入。 用其他特定规则实现数据约束。- 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
推荐阅读