SQL-主键/非空/默认/唯一,约束--各种小知识

最新推荐文章于 2024-06-28 10:00:00 发布

LIUERTOU

最新推荐文章于 2024-06-28 10:00:00 发布

阅读量1.3k

点赞数

分类专栏： SQL基础文章标签： sql 数据库

本文链接：https://blog.csdn.net/LIUERTOU/article/details/118107767

版权

SQL基础专栏收录该内容

2 篇文章 0 订阅

订阅专栏

主键约束

主键约束就是表的唯一标示，不建议修改，也不建议包含用户的有用信息
主键建议每张表都有，但只能有一个主键
主键是非空唯一的，不能重复，也不能填写空值

# 创建表,并且设置主键
CREATE TABLE person(
    pid INT PRIMARY KEY,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    city VARCHAR(100),
    address VARCHAR(100)
);

USE  test_db;

SHOW TABLES;

DESC person;

# 主键(唯一  非空)
# 主键不能插入null值,也不能不传值
INSERT INTO person VALUES(NULL,'zhang','san','北京','昌平');
INSERT INTO person(first_name, last_name) VALUES('zhang','san');

# 主键数据唯一,已经存在后不能插入相同主键记录
INSERT INTO person VALUES(1,'zhang','san','北京','昌平');
INSERT INTO person VALUES(1,'li','si','北京','昌平');

# 不要轻易修改主键
# 主键不包括对用户有意义的任何数据

删除主键

# 删除主键约束
ALTER TABLE person DROP PRIMARY KEY;
# 查看表结构
DESC person;

# 主键删除后,非空约束依然存在,不能插入null值或者不传值
INSERT INTO person VALUES(NULL,'zhang','san','北京','昌平');
INSERT INTO person(first_name, last_name) VALUES('zhang','san');

# 主键删除之后,唯一约束随之消失,可以插入重复值
INSERT INTO person VALUES(1,'zhang','san','北京','昌平');
INSERT INTO person VALUES(1,'li','si','北京','昌平');

主键自增

主键自增就是可以在不传入主键值，或传入null是，让主键插入默认值，并且自动增长。

# 自动增长
CREATE TABLE person1(
    pid INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    city VARCHAR(100),
    address VARCHAR(100)
);

DESC person1;

# 主键会在什么情况下自增?
# 如果已经设定主键值,则将设定的主键值插入,不会进行自增
INSERT INTO person1 VALUES(2, 'zhang','san','上海','浦东');
INSERT INTO person1 VALUES(6, 'li','si','北京','昌平');

# 当主键值未插入,或者插入值为null的时候进行主键自增
# 主键自增会将为传入数值的主键插入默认值,默认值为最大主键值+1
INSERT INTO person1 VALUES(NULL, 'wang','wu','河北','邢台');
# 未传入值可以进行主键自增
INSERT INTO person1(last_name,first_name) VALUES('liu','zhao');
# 主键自增是默认值的一种体现

# 主键计数
# 清空数据表之后,主键计数会清零么?
# 使用delete清空表单,主键计数不清零
DELETE FROM person1;
INSERT INTO person1 VALUES(NULL, 'wang','wu','河北','邢台');

# 使用truncate去清空表单,主键计数清零
TRUNCATE person1;
INSERT INTO person1 VALUES(NULL, 'li','si','河北','邢台');
# 自增存在时主键约束不能被删除

# 删除自增
ALTER TABLE person1 CHANGE pid pid INT;

DESC person1;
# 删除主键
ALTER TABLE person1 DROP PRIMARY KEY;

非空约束和默认约束

非空not null 当前列不能出现空值
默认 default 当未传值的时候给记录赋默认值

# 非空约束(not null)
CREATE TABLE person2(
    pid INT NOT NULL ,
    first_name VARCHAR(100) NOT NULL ,
    last_name VARCHAR(100),
    city VARCHAR(100) NULL,
    address VARCHAR(100)
);

# 插入数据:无法插入null值
INSERT INTO person2 VALUES(1,NULL,NULL,'北京','昌平');
INSERT INTO person2 VALUES(NULL,'张',NULL,'北京','昌平');
# 不能不插入值除非有默认值
INSERT INTO person2(first_name,last_name) VALUES('zhang','san');

# 可以为空(null) 通常null会被省略

# 默认值default
# default 约束添加的默认值要和我们字段的类型相同
CREATE TABLE person3(
    pid INT PRIMARY KEY ,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    city VARCHAR(100) DEFAULT '北京',
    address VARCHAR(100)
);

DESC person3;

INSERT INTO person3(pid) VALUES(1);

CREATE TABLE person4(
    pid INT PRIMARY KEY ,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    city VARCHAR(100),
    address VARCHAR(100),
    num INT DEFAULT 0
);

唯一约束

唯一约束保证了数据的唯一性，保证不重复

# 唯一约束
CREATE TABLE person5(
    pid INT PRIMARY KEY ,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    city VARCHAR(100) UNIQUE ,
    address VARCHAR(100)
);

INSERT INTO person5(pid) VALUES(1);
INSERT INTO person5(pid) VALUES(2);
# 不可重复但是可以为null
INSERT INTO person5(pid,city) VALUES(3,'北京');
INSERT INTO person5(pid,city) VALUES(4,'北京');


CREATE TABLE person6(
    pid INT,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    city VARCHAR(100) UNIQUE NOT NULL ,
    address VARCHAR(100)
);
# 如果表中没有创建主键,且有一个字段既非空又唯一,则默认其为主键
DESC person6;

CREATE TABLE person7(
    pid INT,
    first_name VARCHAR(100) UNIQUE NOT NULL ,
    last_name VARCHAR(100),
    city VARCHAR(100) UNIQUE NOT NULL ,
    address VARCHAR(100)
);
# 如果表中没有主键,有多个字段满足非空唯一要求,那自动选择一个为主键,其余为唯一约束.
DESC person7;

# 如果已经制定主键,则其余非空唯一字段都为唯一约束

比较查询

条件查询中使用比较运算符进行筛选

# 比较查询
SELECT * FROM product WHERE pname='花花公子';
# 查询价格大于60的所有商品
SELECT * FROM product WHERE price > 60;
# 查询价格大于等于800的所有商品
SELECT * FROM product WHERE price >= 800;
# 查询价格小于2000的所有商品
SELECT
    *
FROM
    product
WHERE
    price < 2000;
# 查询价格不等于800的所有商品
SELECT * FROM product WHERE price != 800;
SELECT * FROM product WHERE price <> 800;
# 查询价格大于2000的所有商品的名称
SELECT pname FROM product WHERE price > 2000;

范围查询

对一个区间或集合的数据进行对比，对比结果成立则返回记录，不成立则不返回
范围查询的两个关键字 between and （连续区间）、 in（不连续区间）

# 范围查询
# between and 查询连续范围数据
# 查询商品价格在800-2000之间的所有商品
# between and 包含范围的边界值,是一个闭区间 --- []
SELECT * FROM product WHERE price BETWEEN 800 AND 2000;
# in 查询非连续区间的数据
# 查询价格为60 和800及3000的所有记录
SELECT * FROM product WHERE price IN (60,800,3000);
# 如果查询范围内无记录则不会输出任何内容但是不会报错
SELECT * FROM product WHERE price IN (21,39,50008);
# 如果查询集仅为一个数据,也要用括号包裹起来,否则会报错.
SELECT * FROM product WHERE price IN (800);

逻辑查询

and 同真即真
or 同假即假
not 取反

# 布尔值 true(真)  false(假)
# 条件判断的结果都是布尔值 条件成立即为true 结果返回  条件不成立,即为false 结果不返回
# 计算机是通过true和false去判断逻辑的成立和不成立
# 逻辑运算符:and(逻辑与)  or(逻辑或)  not(逻辑非)

# and(逻辑与)同真即真
# 查询价格大于60且小于2000的所有记录
# and 左右两侧的条件均成立则返回数据信息
SELECT * FROM product WHERE price >60 AND price < 2000;

# or(逻辑或)同假即假
# 查询价格小于60或价格大于2000的所有数据记录
# or 左右两侧的条件有其中一侧条件成立,即可以被返回
SELECT * FROM product WHERE price < 60 or price > 2000;

# not(逻辑非) 取反
# 查询价格不等于800的所有商品
SELECT * FROM product WHERE NOT (price = 800);
# 使用not 进行大于2000的商品的查询
SELECT * FROM product WHERE NOT(price <= 2000);

模糊查询

模糊查询关键字like
两个通配符:
%：0个或多个任意字符
_：1个任意字符

# like  模糊查询
# 通配符:% 代表0到多个任意字符  _ 代表一个任意字符
# 查询名字中带香的所有商品
SELECT * FROM product WHERE pname LIKE '%香%';
# 查询名字中以香开头的所有商品
SELECT * FROM product WHERE pname LIKE '香%';
# 查询以'霸'结尾的所有商品
SELECT * FROM product WHERE pname LIKE '%霸';
# 查询名字最后一个字为斯,且是四个字的产品名称的商品
SELECT * FROM product WHERE pname LIKE '___斯';
SELECT * FROM product WHERE pname LIKE '__斯';

非空查询

is null 判断是否为空
is not null 判断是否不为空

# 非空查询
# is null(判断是否为空)  或者  is not null(判断是否不为空)
INSERT INTO product(pid,pname,price,category_id) VALUES(14,'蒂花之秀',NULL,NULL);
# 查询所有价格为null的商品
SELECT * FROM product WHERE price IS NULL;
# 能否使用字段名=null的形式进行筛选 不能这样去筛选,因为null与int数据类型不同没有办法正常对比
# SELECT * FROM product WHERE price = NULL;
# 查询所有价格不为null的商品
SELECT * FROM product WHERE price IS NOT NULL;

排序查询

order by 排序
ASC 升序 DESC降序

# 排序查询
# 关键字:order by
SELECT * FROM product ORDER BY price; # 默认按照升序排列
# 排序规则: ASC 升序   DESC 降序
SELECT * FROM product ORDER BY price ASC; # 如果写ASC会警告默认排序规则就是升序
SELECT * FROM product ORDER BY price DESC; # 降序

# 排序还可以结合条件查询来使用
SELECT * FROM product WHERE price >600 ORDER BY price;
# 可不可以先排序再筛选 条件查询要在排序之前
# (SELECT * FROM product ORDER BY price) WHERE price >600;
# 同时参照两个字段排序
# 按照价格对商品进行升序排序,当价格相同时,pid值约大越靠前
# 排序规则靠前的先执行,如果排序值相同则参照第二排序规则
SELECT * FROM product ORDER BY price DESC , pid DESC;
# 按照pid进行降序排序,如果pid相同则价格越大越靠前
SELECT * FROM product ORDER BY  pid DESC, price DESC;

聚合函数

聚合函数就是讲一列数据进行计算得到一个值的函数
常用的聚合函数：max min sum count avg

# 聚合函数
# count 计数 统计非空数据的数量
# 查询商品的总数
SELECT count(*) FROM product;
# 查询价格大于600的商品的总数
SELECT count(*) FROM product WHERE price > 600;
# 查询价格非空的商品的总数  不统计price值为null的数据
SELECT count(price) FROM product;
# sum 求和
# 计算所有商品价格的综合
SELECT sum(price) FROM product;
# 计算所有商品价格大于800的商品的价格总和
SELECT sum(price) FROM product WHERE price > 800;
# max 最大值
# 查询最贵的商品价格
SELECT max(price) FROM product;
# 查询价格小于三千的商品的最高价格
SELECT max(price) FROM product WHERE price < 3000;
# min 最小值  不统计null数据
# 查询商品的最低价格
SELECT  min(price) FROM product;
# 查询商品价格大于800的商品最低价格
SELECT min(price) FROM product WHERE price > 800;
# avg 平均值
# 查询所有商品的平均值
SELECT avg(price) FROM product;
# 查询所有商品价格大于600的商品的平均值
SELECT avg(price) FROM product WHERE price > 600;

# 聚合函数可以用于计算
# 查询所有商品价格最大值和最小值的差值
SELECT max(price) - min(price) FROM product;

分组查询

分组查询一般配合聚合查询进行使用
分组查询：group by
对分组后的结果进行条件筛选不能使用where 需要使用having

# 分组查询
# 按照category_id进行分组
SELECT category_id FROM product GROUP BY category_id;
# 按照category_id进行分组,查看每组中的商品数量
# 使用聚合函数配合分组函数,就可以对每一组的数据进行聚合
SELECT category_id,count(*) FROM product GROUP BY category_id;

# 按照category_id进行分组,查看每组中的商品数量,不包含类别为null的数据
# 对分组后的数据进行条件查询使用having 不能使用where
SELECT category_id,count(*) FROM product GROUP BY category_id HAVING category_id is not null;
# SELECT category_id,count(*) FROM product WHERE product.category_id is not null GROUP BY category_id ;

# 按照category_id进行分组,查看每组中的商品数量,只包含产品数量大于2的分类.
# having可以使用聚合函数参与条件运算,但是在where中不行
SELECT category_id,count(*) FROM product GROUP BY category_id HAVING count(*) > 2;

分页查询

分页查询关键字：limit
m：查询起始位置
n：每页展示的数据条目数
进行分页查询时尽量不要漏查也不要冗余

# 分页查询
SELECT * FROM product;

# 分页的关键字:LIMIT
# 格式:select 列名 from 表名 limit m n
# m:开始位置
# n:显示数量
# 分页操作每页展示三条数据
# 第一页
SELECT * FROM product LIMIT 0,3;
# 如果从第一行开始,0可以省略
SELECT * FROM product LIMIT 3;
# 第二页
SELECT * FROM product LIMIT 3,3;
# 第三页
SELECT * FROM product LIMIT 6,3;

# 定义x为页数   m=(x-1)*n  n 每页展示的数量