数据库的查询,约束和多表操作
单表查询-排序
SELECT * FROM 表名 where 条件 ORDER BY 排序字段 ASC|DESC;
ASC 升序 (默认)
DESC 降序
举例:
在价格排序(降序)的基础上,以分类排序(降序)
SELECT * FROM d_product ORDER BY price DESC,category_id DESC;
单表查询–聚合
概念:
count:统计指定列不为NULL的记录行数;
sum:计算指定列的数值和,如果指定列类型不是数值类型,那么计算结果为0;
max:计算指定列的最大值,如果指定列是字符串类型,那么使用字符串排序运算;
min:计算指定列的最小值,如果指定列是字符串类型,那么使用字符串排序运算;
avg:计算指定列的平均值,如果指定列类型不是数值类型,那么计算结果为0;
举例:
统计价格在800以上一共有多少商品
SELECT COUNT(*) as total from d_product where price>=800;
统计价格的和值
select sum(price) from d_product;
max 获取最大值
获取价格最大的商品的价格
select max(price) from d_product;
单表查询–分组
SELECT 字段1,字段2… FROM 表名 GROUP BY分组字段 HAVING 分组后再筛选的条件;
分组操作中的having子语句,是用于在分组后对数据进行过滤的,作用类似于where条件
备注:
l having与where的区别:
having是在分组后对数据进行过滤.
where是在分组前对数据进行过滤(where是从硬盘读取数据的,有效合理的数据筛选显得尤为重要!)
having后面可以使用分组函数(统计函数)
where后面不可以使用分组函数。
举例:
按照价格分组
select price from d_product group by price;
筛选出评价价格在500以上的商品分组
select count(*),avg(price),category_id from d_product group by category_id having avg(price)>=500;
SQL约束
规约 :
PRIMARY KEY 约束唯一标识数据库表中的每条记录。
主键必须包含唯一的值。
主键列不能包含 NULL 值。
每个表都应该有一个主键,并且每个表只能有一个(一个列,或多列的组合)主键。
主键约束
方式1:
CREATE TABLE Persons
(
Id_P int PRIMARY KEY,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
方式2:
CREATE TABLE Persons
(
FirstName varchar(255),
LastName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT pk_PersonID PRIMARY KEY (FirstName,LastName)
)
方式3:
CREATE TABLE Persons
(
FirstName varchar(255),
LastName varchar(255),
Address varchar(255),
City varchar(255)
)
ALTER TABLE Persons ADD PRIMARY KEY (FirstName,LastName)
删除主键约束
ALTER TABLE Persons DROP PRIMARY KEY
自动增长列
CREATE TABLE Persons
(
P_Id int PRIMARY KEY AUTO_INCREMENT,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
所以,向persons添加数据时,可以不为P_Id字段设置值,也可以设置成null,数据库将自动维护主键值.
注意: 两种区别
l delete和truncate的区别
Delete删除表中的数据,但不重置auto-increment记录数。
Truncate删除表中的数据,auto-increment记录数将重置。Truncate其实先删除表然后再创建表。
非空约束
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
唯一约束
方式1:
CREATE TABLE Persons
(
Id_P int UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
方式2:
CREATE TABLE Persons
(
Id_P int,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT 名称UNIQUE (Id_P)
)
方式3:
ALTER TABLE Persons ADD [CONSTRAINT 名称] UNIQUE (Id_P)
删除唯一约束
ALTER TABLE Persons DROP INDEX 唯一约束的名称
多表操作
外键约束
alter table 从表 add [constraint] [外键名称] foreign key (从表外键字段名) references 主表 (主表的主键);
[外键名称] 用于删除外键约束的,一般建议“_fk”结尾
alter table 从表 drop foreign key 外键名称
案例1:
/*使用数据库*/
USE igeek_02;
####一对多关系
#1 创建主表:分类表
CREATE TABLE category(
cid VARCHAR(32) PRIMARY KEY,
cname VARCHAR(50)
);
#2 创建从表:商品表
CREATE TABLE products(
pid VARCHAR(32) PRIMARY KEY,
pname VARCHAR(50),
price DOUBLE,
category_id VARCHAR(32)
);
#3 将使用 主外键关系进行描述
ALTER TABLE products ADD CONSTRAINT product_fk FOREIGN KEY (category_id) REFERENCES category (cid);
案例2:
##创建省表
CREATE TABLE province(
id VARCHAR(32) PRIMARY KEY,
NAME VARCHAR(50),
description VARCHAR(100)
);
##创建市表
CREATE TABLE city(
id VARCHAR(32) PRIMARY KEY,
NAME VARCHAR(50),
description VARCHAR(100),
pid VARCHAR(32) #对应省表 外键
);
##主外键关系
ALTER TABLE city ADD CONSTRAINT city_fk FOREIGN KEY (pid) REFERENCES province(id);
案例3:
一张表,自关联一对多
CREATE TABLE AREA(
id VARCHAR(32) PRIMARY KEY,
NAME VARCHAR(50),
description VARCHAR(100),
parent_id VARCHAR(32)
);
##自关联一对多
ALTER TABLE AREA ADD CONSTRAINT area_fk FOREIGN KEY (parent_id) REFERENCES AREA(id);