数据库的简单查询,约束和简单多表操作

数据库的查询,约束和多表操作

单表查询-排序

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);