MySql
数据约束
数据约束是针对表中的列加约束。
约束分类
-
实体完整性
实体完整性:确保每条记录的唯一性,能够被唯一区分。 主键约束(整数,字符串):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 #唯一约束
)
-
域完整性
域完整性:针对数据的取值做约束。 非空约束: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有 限定值的范围(性别:男,女)
注意:不写外键规则,但存在外键约束,这种称为逻辑外键。定义外键规则,称为物理外键。
-
引用完整性
引用完整性:多表之间有数据的依赖关系,要确保依赖关系的正确性 外键约束:foreign key 特征:存在于多表之间,如果一个表中某列的数据依赖于另外一个表的数据,此时要对这个列加外键约束。该列的值不能随便写。 注意: 1.有外键关联的两个表,从表插入的外键列的数据必须在主表中存在 2.主表中的数据被关联的情况下,不能删除 3.主表中的数据被关联的情况下,不能修改 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