约束
约束条件:限制表中的数据,保证添加到数据表中的数据准确和可靠性!凡是不符合约束的数据,插入时就会失败!
约束条件在创建表时可以使用, 也可以修改表的时候添加约束条件
1、约束条件分类:
1)not null :非空约束,保证字段的值不能为空
s_name VARCHAR(10) NOT NULL, #非空
2)default:默认约束,保证字段总会有值,即使没有插入值,都会有默认值!
age INT DEFAULT 18, #默认约束
3)unique:唯一,保证唯一性但是可以为空,比如座位号
s_seat INT UNIQUE,#唯一约束
4)check:检查性约束【MySQL不支持,语法不报错,但无效】
s_sex CHAR(1) CHECK(s_sex='男' OR s_sex='女'),#检查约束(Mysql无效)
5)primary key :主建约束,同时保证唯一性和非空
id INT PRIMARY KEY,#主建约束(唯一性,非空)
6)foreign key:外键约束,用于限制两个表的关系,保证从表该字段的值来自于主表相关联的字段的值!
teacher_id INT REFERENCES teacher(id) #这是外键,写在列级,Mysql无效
注意
1. 列级约束
上面6种约束都可以写,语法都支持,不报错,但外键约束写了mysql无效不起作用
2. 表级约束
非空、默认不支持,其他都可以!
语法:
其他: 【constraint 约束名】 约束类型(字段名称) ,
外键: 【constraint 约束名】 约束类型(字段名称) foreign key(字段名称) references 关联表名(其字段名),
-
DROP TABLE IF EXISTS students;
-
CREATE TABLE students(
-
id INT ,
-
s_name VARCHAR(10) not null,# 非空约束
-
s_sex CHAR(1) default '男', # 默认约束
-
s_seat INT,
-
age INT ,
-
teacher_id INT ,
-
#上面是列级约束,下面有表级约束
-
CONSTRAINT pk PRIMARY KEY(id), #主建约束,pk是起的名,后面一样
-
CONSTRAINT uq UNIQUE(s_seat), #唯一约束
-
CONSTRAINT ck CHECK(s_sex='男' OR s_sex='女'), #检查约束
-
CONSTRAINT fk_students_teacher FOREIGN KEY(teacher_id) REFERENCES teacher(id) #外键约束
-
);
2、MySQL修改表时添加或删除约束
即修改表字段的数据类型或约束
外键删除约束: ALTER TABLE 表名 DROP CONSTRAINT 约束名称
1) 非空约束
alter table students modify column s_name varchar(20) not null; # 添加
alter table students modify column s_name varchar(20) ; # 删除 不写约束条件
2)默认约束
alter table students modify column age int default 18; #添加
alter table students modify column age; #删除
3)唯一键约束
alter table students modify column seat int unique; #添加
alter table students drop index seat; #删除
show index from students; #查看唯一约束
4)主键约束
alter table students modify column id int primary key; #添加
alter table students drop primary key; #删除 约束名称
5)外键约束
alter table students add foreign key(major_id) references majors(id); #添加
alter table students drop foreign key fk_students_teacher; #删除 约束名称
3、自增长列 auto_increment
id int primary key auto_increment,
一个表中有且只能有一个自增长列,自增长列一般和主键搭配
修改表的时候添加自增长列:
alter table t_indentity modify column id int primary key auto_increment;
删除自增长列:
alter table t_indentity modify column id int;
查询
在查询之前,我们也先准备好一些数据
-- 创建 db2 数据库 CREATE DATABASE db2;-- 使用 db2 数据库 USE db2;-- 数据 student 数据表 CREATE
TABLE student (
id INT, -- 编号
NAME VARCHAR(20), -- 姓名
age INT, -- 年龄
sex VARCHAR(5), -- 性别
address VARCHAR(100), -- 地址
math INT, -- 数学
english INT -- 英语);
-- 添加表记录
INSERT INTO student(id,NAME,age,sex,address,math,english) VALUES (1,'马云',55,'男','杭州',66,78),(2,'马化腾',45,'女','深圳',98,87),(3,'马景涛',55,'男','香港',56,77),(4,'柳岩',20,'女','湖南',76,65),(5,'柳青',20,'男','湖南',86,NULL),(6,'刘德华',57,'男','香港',99,99),(7,'马德',22,'女','香港',99,99),(8,'德玛西亚',18,'男','南京',56,65);
数据如下
DQL 排序查询
下图为最后一条 sql 语句排序的结果集
DQL 聚合函数
聚合函数指在查询数据时可以将一列数据进行纵向的计算。掌握下面几个聚合函数。
-- 聚合函数查询的语法 SELECT 聚合函数(列名) FROM 表名
COUNT 函数
-- 注意:如果类中有 NULL 值,不算个数
-- 求 student 表中 math 列有多少条记录,结果为 8
SELECT COUNT(math) FROM student;
-- 求 student 表中 english 列有多少条记录,结果为 7,因为有一个 NULL 值
SELECT COUNT(english) FROM student;
-- 查询所有列个数【比较常用】
SELECT COUNT(*) FROM student;
执行结果为
![](https://i-blog.csdnimg.cn/blog_migrate/960c791d522a9a582c46d965d3cd4a4f.png)
MIN 函数
--查询 math 的最小值
SELECT MIN(math) FROM student;
MAX 函数
-- 查询 math 的最大值
SELECT MAX(math) FROM student;
SUM 函数
-- 查询 math 的和
SELECT SUM(math) FROM student;
AVG 函数
-- 查询 math 的平均值
SELECT AVG(math) FROM student;
DQL 分组查询
按照特定条件把数据进行分组,把每一组当做一个整体,分别对某一组数据进行计算。
-- 分组查询语法,字段列表只能是分组列、或者聚合函数 SELECT 字段列表 FROM 表名 where 分组前条件
GROUP BY 分组列名 HAVING 分组后条件
对所有数据分组查询
-- 按姓别进行分别,查询男、女的 math 的平均值
SELECT sex,AVG(math) FROM student GROUP BY sex;
-- 按性别进行分组,查询男、女同学的 math 平均分,和人数 SELECT sex, AVG(math), COUNT(id) FROM
student GROUP BY sex;
分组前筛选
-- 分组前进行筛选使用 where 子句-- 对>70 分的人按照性别进行分组,查询男、女同学的 math 平均分和人数
SELECT sex, AVG(math),COUNT(id) FROM student WHERE math>70 GROUP BY sex;
分组后筛选
-- 分组后再进行筛选,使用 having 子句-- 对>70 分的人按照性别进行分组,查询男、女同学的 math 平均分
和人数,再筛选人数>2 的数据
SELECT sex, AVG(math),COUNT(id) FROM student WHERE math>70 GROUP BY sex HAVING COUNT(id)>2;
-- 可以使用 AS 语句给聚合函数取别名,改进上面的 sql 语句
SELECT sex, AVG(math) AS 平均分,COUNT(id) AS 个数 FROM student WHERE math>70 GROUP BY sex HAVING
个数>2;
DQL 分页查询
分页查询指当要查询的数据量比较多的时候,采用一次查询固定条记录的方式。
-- 开始索引=(当前页码-1)*个数
select 字段列表 from 表名 limit 开始索引,个数
-- 查询 0 索引开始的 3 条记录
SELECT * FROM student LIMIT 0,3; -- 第 1 页
-- 查询 3 索引开始的 3 条记录
SELECT * FROM student LIMIT 3,3; -- 第 2 页
-- 查询 6 索引开始的 3 条记录
SELECT * FROM student LIMIT 6,3; -- 第 3 页