mysql 回顾
数据库的设计必须满足三范式
1NF: 强调列的原子性,列不可拆分
eg: 一张表(联系人) 有(姓名,性别,电话)三列,但是现实中电话又可分为家庭电话和公司电话,这种表结构设计就不符合第一范式了,正确的应该是继续拆分(姓名,性别,家庭电话,公司电话)
- 2NF: 首先满足1NF,另外包含两点:
- 表必须有一个主键
- 非主键列必须完全依赖于主键,而不能只依赖与主键的一部分
eg: 有这样一张表
OrderDetail:(OrderID,ProductID,UnitPrice,Discount,Quantity,ProductName)。
我们知道在一个订单中可以订购多种产品,所以单单一个 OrderID 是不足以成为主键的,主键应该是(OrderID,ProductID)。
显而易见 Discount(折扣),Quantity(数量)完全依赖(取决)于主键(OderID,ProductID),而 UnitPrice,ProductName
只依赖于 ProductID。所以 OrderDetail 表不符合 2NF。不符合 2NF 的设计容易产生冗余数据
正确的做法应该是进行分表:
【OrderDetail】表拆分为【OrderDetail】(OrderID,ProductID,Discount,Quantity)和【Product】(ProductID,UnitPrice,ProductName)来消除原订单表中UnitPrice,ProductName多次重复的情况。
3NF 首先要满足2NF,另外非主键列必须直接依赖于主键,不能存在传递关系,即:非主键列A 依赖于非主键列B, 非主键列B依赖于主键的情况
eg: 订单表
Order(OrderID,OrderDate,CustomerID,CustomerName,CustomerAddr,CustomerCity)主键是(OrderID)
其中 OrderDate,CustomerID,CustomerName,CustomerAddr,CustomerCity 等非主键列都完全依赖于主键
(OrderID),所以符合 2NF。不过问题是 CustomerName,CustomerAddr,CustomerCity 直接依赖的是 CustomerID(非主键列),而不是直接依赖于主键,它是通过传递才依赖于主键,所以不符合 3NF。
正确的方式:Order 拆分为【Order】(OrderID,OrderDate,CustomerID)和【Customer】(CustomerID,CustomerName,CustomerAddr,CustomerCity)从而达到 3NF
范式小结
第二范式 和 第三范式容易混淆,关键在于, 2NF: 非主键列是否完全依赖于主键,还是依赖于主键的一部分; 3NF: 非主键列是直接依赖于主键,还是直接依赖于非主键列。
数据库的 CURD
数据源:
-- students表
create table students(
id int unsigned primary key auto_increment not null,
name varchar(20) default '',
age tinyint unsigned default 0,
height decimal(5,2),
gender enum('男','女','中性','保密') default '保密',
cls_id int unsigned default 0,
is_delete bit default 0
);
-- classes表
create table classes (
id int unsigned auto_increment primary key not null,
name varchar(30) not null
);
添加数据:
-- 向students表中插入数据
# 主键id 是自动增长的,使用全列插入时需要占位,通常用0,default、null 来占位
insert into students values
(0,'小明',18,180.00,2,1,0),
(0,'彭于晏',29,185.00,1,1,0),
(0,'刘德华',59,175.00,1,2,1),
(0,'黄蓉',38,160.00,2,1,0),
(0,'凤姐',28,150.00,4,2,1),
(0,'王祖贤',18,172.00,2,1,1),
(0,'周杰伦',36,NULL,1,1,0),
(0,'静香',12,180.00,2,4,0),
(0,'郭靖',12,170.00,1,4,0),
(0,'周杰',34,176.00,2,5,0);
-- 向classes表中插入数据
insert into classes values (0, "一班"), (0, "二班");
基本命令
create databse db_name charset=utf8; # 创建数据库
show databses; # 显示所有数据库
show create database db_name; # 查看数据库的基本信息
use db_name; # 切换数据库
select database(); # 查看当前所用数据库
select now(); # 查看当前时间
MySQL查询语句
as 可给字段,或者给表起别名
select s.id, s.name, s.gender from students as s;
消除重复行
select distinct gender from students;
- 条件where 子句
优先级(由高到低): 小括号,not, 比较运算符,逻辑运算符,and比or先运算
注意: 不推荐使用 a) 负向查询条件:NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等,会导致全表扫描 b) %开头的模糊查询,会导致全表扫描- 比较运算符
- 逻辑运算符
- 模糊查询
- like
eg: select * from students where name like; '黄%' # 查询姓黄的学生 - % 表示任意多个任意字符
eg: select * from students where name like; '黄_' # 查询姓黄且名字是一个字的学生 - _ 表示一个任意字符
- like
- 范围查询
- in 表示在一个非连续的范围内
eg: select * from students where id in (1, 3, 8); - between...and... 表示在一个连续的范围内
eg: select * from students where id between 3 and 8;
- in 表示在一个非连续的范围内
- 空判断
- null (与''不同)
- is not null
- 排序 order by 默认升序 asc
- asc 升序
desc 降序
eg: 查询未删除的男生信息,按学号降序select * from students where gender=1 and is_delete=0 order by id desc;
- 聚合函数
- count
- max
- min
- sum
- avg
- 分组 group by 一般结合聚合函数使用
将查询结果按照1个或多个字段进行分组,字段值相同的为一组- group by + group_concat(字段名) 将分组结果 根据字段名 输出对应字段值的集合
select gender, group_concat(name) from stud
- group by + group_concat(字段名) 将分组结果 根据字段名 输出对应字段值的集合