- SQL语句查询
- 排序
通过order by语句，可以将查询出的结果进行排序。放置在select语句的最后。
格式:
SELECT * FROM 表名 ORDER BY 排序字段 ASC|DESC;
ASC 升序 (默认)
DESC 降序
#1.使用价格排序(降序)
SELECT * FROM product ORDER BY price DESC;
#2.在价格排序(降序)的基础上，以分类排序(降序)
SELECT * FROM product ORDER BY price DESC,category_id DESC;
#3.显示商品的价格(去重复)，并排序(降序)
SELECT DISTINCT price FROM product ORDER BY price DESC;
-
- 聚合
之前我们做的查询都是横向查询，它们都是根据条件一行一行的进行判断，而使用聚合函数查询是纵向查询，它是对一列的值进行计算，然后返回一个单一的值；另外聚合函数会忽略空值。
今天我们学习如下五个聚合函数：
- count：统计指定列不为NULL的记录行数；
- sum：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；
- max：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
- min：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
- avg：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；
#1 查询商品的总条数
SELECT COUNT(*) FROM product;
#2 查询价格大于200商品的总条数
SELECT COUNT(*) FROM product WHERE price > 200;
#3 查询分类为'c001'的所有商品价格总和
SELECT SUM(price) FROM product WHERE category_id = 'c001';
#4 查询分类为'c002'所有商品的平均价格
SELECT AVG(price) FROM product WHERE category_id = 'c002';
#5 查询商品的最大价格和最小价格
SELECT MAX(price),MIN(price) FROM product;
-
- 分组
分组查询是指使用group by字句对查询信息进行分组。
- 格式：
SELECT 字段1,字段2…聚合函数(字段名) FROM 表名 GROUP BY分组字段 HAVING 分组条件;
分组操作中的having子语句，是用于在分组后对数据进行过滤的，作用类似于where条件。
- having与where的区别:
- having是在分组后对数据进行过滤.
where是在分组前对数据进行过滤
-
- having后面可以使用分组函数(统计函数)
where后面不可以使用分组函数。
#1 统计各个分类商品的个数
SELECT category_id ,COUNT(*) FROM product GROUP BY category_id ;
#2 统计各个分类商品的个数,且只显示个数大于1的信息
SELECT category_id ,COUNT(*) FROM product GROUP BY category_id HAVING COUNT(*) > 1;
- SQL备份与恢复
- SQL备份
选中数据库，右键 ”备份/导出” ， 指定导出路径，保存成.sql文件即可。
正在上传…重新上传取消
正在上传…重新上传取消
-
- SQL恢复
数据库列表区域右键“从SQL转储文件导入数据库”， 指定要执行的SQL文件，执行即可。
正在上传…重新上传取消
命令行:
备份:mysqldump –uroot –proot 数据库名 数据库表名> 文件
恢复:在mysql的命令行下:
use 数据库名
source 文件名
- SQL约束
- 主键约束
PRIMARY KEY 约束唯一标识数据库表中的每条记录。
主键必须包含唯一的值。
主键列不能包含 NULL 值。
每个表都应该有一个主键，并且每个表只能有一个主键。
-
-
- 添加主键约束
-
- 方式一：创建表时，在字段描述处，声明指定字段为主键：
CREATE TABLE Persons
(
Id_P int PRIMARY KEY,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
-
- 格式：[constraint 名称] primary key (字段列表)
- 关键字constraint可以省略，如果需要为主键命名，constraint不能省略，主键名称一般没用。
- 字段列表需要使用小括号括住，如果有多字段需要使用逗号分隔。声明两个以上字段为主键，我们称为联合主键。
CREATE TABLE Persons
(
FirstName varchar(255),
LastName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT pk_PersonID PRIMARY KEY (FirstName,LastName)
)
或
CREATE TABLE Persons
(
FirstName varchar(255),
LastName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (FirstName,LastName)
)
- 方式三：创建表之后，通过修改表结构，声明指定字段为主键：
ALTER TABLE Persons ADD [CONSTRAINT 名称] PRIMARY KEY (字段列表)
CREATE TABLE Persons
(
FirstName varchar(255),
LastName varchar(255),
Address varchar(255),
City varchar(255)
)
ALTER TABLE Persons ADD PRIMARY KEY (FirstName,LastName)
-
-
- 删除主键约束
-
如需撤销 PRIMARY KEY 约束，请使用下面的 SQL：
ALTER TABLE Persons DROP PRIMARY KEY
-
- 自动增长列
我们通常希望在每次插入新记录时，数据库自动生成字段的值。
我们可以在表中使用 auto-increment（自动增长列）关键字，自动增长列类型必须是整形，自动增长列必须为键(一般是主键)。
- 下列 SQL 语句把 "Persons" 表中的 "P_Id" 列定义为 auto-increment 主键
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，数据库将自动维护主键值：
INSERT INTO Persons (FirstName,LastName) VALUES ('Bill','Gates')
INSERT INTO Persons (P_Id,FirstName,LastName) VALUES (NULL,'Bill','Gates')
- 面试：delete和truncate的区别
- Delete删除表中的数据，但不重置auto-increment记录数。
- Truncate删除表中的数据，auto-increment记录数将重置。Truncate其实先删除表然后再创建表。
- 扩展：默认地，AUTO_INCREMENT 的开始值是 1，如果希望修改起始值，请使用下列 SQL 语法：
ALTER TABLE Persons AUTO_INCREMENT=100
-
- 非空约束
NOT NULL 约束强制列不接受 NULL 值。
NOT NULL 约束强制字段始终包含值。这意味着，如果不向字段添加值，就无法插入新记录或者更新记录。
- 下面的 SQL 语句强制 "Id_P" 列和 "LastName" 列不接受 NULL 值：
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
扩展:
show variables like 'sql_mode%':查询当前链接的数据库的sql验证级别
Mysql的版本不同;对于sql语句的严格性不同,所以某些sql语句有可能也会通过执行 但是会提出警告.
可以将咱们当前的回话设置成严格模式 set session sql_mode='STRICT_TRANS_TABLES' ; (了解)
-
- 唯一约束
UNIQUE 约束唯一标识数据库表中的每条记录。
UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。
PRIMARY KEY 拥有自动定义的 UNIQUE 约束。
请注意，每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。
-
-
- 添加唯一约束
-
与主键添加方式相同，共有3种，
- 方式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)
-
-
- 删除唯一约束
-
- 如需撤销 UNIQUE 约束，请使用下面的 SQL：
- 如果添加唯一约束时，没有设置约束名称，默认是当前字段的字段名。
-
- 外键约束
FOREIGN KEY 表示外键约束，将在多表中学习。
- 多表操作
实际开发中，一个项目通常需要很多张表才能完成。例如：一个商城项目就需要分类表(category)、商品表(products)、订单表(orders)等多张表。且这些表的数据之间存在一定的关系，接下来我们将在单表的基础上，一起学习多表方面的知识。
正在上传…重新上传取消
-
- 表与表之间的关系
- 一对多关系：
- 常见实例：客户和订单，分类和商品，部门和员工.
- 一对多建表原则：在从表(多方)创建一个字段，字段作为外键指向主表(一方)的主键.
正在上传…重新上传取消
- 多对多关系：
- 常见实例：学生和课程、用户和角色
- 多对多关系建表原则：需要创建第三张表,中间表中至少两个字段，这两个字段分别作为外键指向各自一方的主键.
正在上传…重新上传取消
- 一对一关系：(了解)
- 在实际的开发中应用不多.因为一对一可以创建成一张表.
- 两种建表原则：
- 外键唯一：主表的主键和从表的外键（唯一），形成主外键关系，外键唯一unique。
- 外键是主键：主表的主键和从表的主键，形成主外键关系。
-
- 外键约束
现在我们有两张表“分类表”和“商品表”，为了表明商品属于哪个分类，通常情况下，我们将在商品表上添加一列，用于存放分类cid的信息，此列称为：外键
正在上传…重新上传取消
正在上传…重新上传取消
此时“分类表category”称为：主表，“cid”我们称为主键。“商品表products”称为：从表，category_id称为外键。我们通过主表的主键和从表的外键来描述主外键关系，呈现就是一对多关系。
外键特点：
-
-
- 从表外键的值是对主表主键的引用。
- 从表外键类型，必须与主表主键类型一致。
-
- 声明外键约束
语法：alter table 从表 add [constraint] [外键名称] foreign key (从表外键字段名) references 主表 (主表的主键);
[外键名称] 用于删除外键约束的，一般建议“_fk”结尾
alter table 从表 drop foreign key 外键名称
- 使用外键目的：
- 保证数据完整性
-
- 一对多操作
- 分析
- 一对多操作
正在上传…重新上传取消
- category分类表，为一方，也就是主表，必须提供主键cid
- products商品表，为多方，也就是从表，必须提供外键category_id
-
-
- 实现：分类和商品
-
###创建分类表
create table category(
cid varchar(32) PRIMARY KEY ,
cname varchar(100) #分类名称
);
# 商品表
CREATE TABLE `products` (
`pid` varchar(32) PRIMARY KEY ,
`name` VARCHAR(40) ,
`price` DOUBLE
);
#添加外键字段
alter table products add column category_id varchar(32);
#添加约束
alter table products add constraint product_fk foreign key (category_id) references category (cid);
-
-
- 操作
-
#1 向分类表中添加数据
INSERT INTO category (cid ,cname) VALUES('c001','服装');
#2 向商品表添加普通数据,没有外键数据，默认为null
INSERT INTO products (pid,pname) VALUES('p001','商品名称');
#3 向商品表添加普通数据，含有外键信息(数据存放在)
INSERT INTO products (pid ,pname ,category_id) VALUES('p002','商品名称2','c001');
#4 向商品表添加普通数据，含有外键信息(数据不存在) -- 不能异常
INSERT INTO products (pid ,pname ,category_id) VALUES('p003','商品名称2','c999');
#5 删除指定分类(分类被商品使用) -- 执行异常
DELETE FROM category WHERE cid = 'c001';
-
- 多对多
- 分析
- 多对多
正在上传…重新上传取消
- 商品和订单多对多关系，将拆分成两个一对多。
- products商品表，为其中一个一对多的主表，需要提供主键pid
- orders 订单表，为另一个一对多的主表，需要提供主键oid
- orderitem中间表，为另外添加的第三张表，需要提供两个外键oid和pid
-
- 需要创建第三张表,中间表中至少两个字段，这两个字段分别作为外键指向各自一方的主键.
-
-
- 实现：订单和商品
-
### 商品表[已存在]
### 订单表
create table `orders`(
`oid` varchar(32) PRIMARY KEY ,
`totalprice` double #总计
);
### 订单项表
create table orderitem(
oid varchar(50),-- 订单id
pid varchar(50)-- 商品id
);
###---- 订单表和订单项表的主外键关系
alter table `orderitem` add constraint orderitem_orders_fk foreign key (oid) references orders(oid);
###---- 商品表和订单项表的主外键关系
alter table `orderitem` add constraint orderitem_product_fk foreign key (pid) references products(pid);
### 联合主键（可省略）
alter table `orderitem` add primary key (oid,pid);
-
-
- 操作
-
#1 向商品表中添加数据
INSERT INTO products (pid,pname) VALUES('p003','商品名称');
#2 向订单表中添加数据
INSERT INTO orders (oid ,totalprice) VALUES('x001','998');
INSERT INTO orders (oid ,totalprice) VALUES('x002','100');
#3向中间表添加数据(数据存在)
INSERT INTO orderitem(pid,oid) VALUES('p001','x001');
INSERT INTO orderitem(pid,oid) VALUES('p001','x002');
INSERT INTO orderitem(pid,oid) VALUES('p002','x002');
#4删除中间表的数据
DELETE FROM orderitem WHERE pid='p002' AND oid = 'x002';
#5向中间表添加数据(数据不存在) -- 执行异常
INSERT INTO orderitem(pid,oid) VALUES('p002','x003');
#6删除商品表的数据 -- 执行异常
DELETE FROM products WHERE pid = 'p001';
3902
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
