MySQL 基础回顾

mysql 回顾 数据库的设计必须满足三范式 1NF: 强调列的原子性,列不可拆分 eg: 一张表(联系人) 有(姓名,性别,电话)三列,但是现实中电话又可分为家庭电话和公司电话,这种表结构设计...

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查询语句
  1. as 可给字段,或者给表起别名

    select s.id, s.name, s.gender from students as s;

  2. 消除重复行

    select distinct gender from students;

  3. 条件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; '黄_' # 查询姓黄且名字是一个字的学生
      • _ 表示一个任意字符
    • 范围查询
      • in 表示在一个非连续的范围内
        eg: select * from students where id in (1, 3, 8);
      • between...and... 表示在一个连续的范围内
        eg: select * from students where id between 3 and 8;
    • 空判断
      • null (与''不同)
      • is not null
  4. 排序 order by 默认升序 asc
    • asc 升序
    • desc 降序
      eg: 查询未删除的男生信息,按学号降序

      select * from students where gender=1 and is_delete=0 order by id desc;

  5. 聚合函数
    • count
    • max
    • min
    • sum
    • avg
  6. 分组 group by 一般结合聚合函数使用
    将查询结果按照1个或多个字段进行分组,字段值相同的为一组
    • group by + group_concat(字段名) 将分组结果 根据字段名 输出对应字段值的集合
      select gender, group_concat(name) from students group by gender;
    • group by + 聚合函数
      eg: 按性别分别统计年龄的平均值
      select gender, avg(age) from students group by gender;
    • group by + having
      用来分组查询后指定一些条件来输出查询结果, 作用和where一样,但是只能用于group by
      select gender,count() from students group by gender having count()>2;
    • gounp by + with rollup
      with rollup作用: 最后新增一行,来记录当前列里所有记录的总和
  7. 连接查询多表查询 join 表连接原理: 笛卡尔积
    • 内连接
    • 右连接 在内连接的基础上添加右表数据,右表中没有的数据字段使用null填充
    • 左连接 在内连接的基础上添加左表数据,右表中没有的数据字段使用null填充
    # 笛卡尔积 在其他数据库中内连接和笛卡尔积石油区别的,在mysql中 join 和 inner join 是一样的
    select * from table1 [inner] join table2;   

条件查询(on子句 过滤笛卡尔积)

语法: select * from table1 inner|left|right join table2 on table1.field = table2.field

  1. 自关联
    应用场景 区域信息,分类信息(如淘宝分类栏,大类,小类,具体分类)

  2. 子查询
    • 标量子查询 一行一列
      子查询的结果是一个标量
      eg: 查询大于平年龄的学生
    select * from students where age > (select avg(age) from students);
    • 列子查询 返回的结果是一列多行
    • 行子查询 返回的结果是一行多列
      行元素: 将多个字段合成一个行元素,在行级子查询中会使用到行元素
    select * from students where (height, age) = (select max(height),max(age) from students);

    主查询和子查询的关系
    · 子查询是嵌入主查询的
    · 子查询要么充当条件,要么作为数据源
    · 子查询也是一条完整的select语句

  3. 事务
    事务是多个SQL语句操作的序列,这些操作要么都执行,要么都不执行,如有有一个失败,便回滚到原始状态
    应用场景: 充话费,银行转账,地铁卡充值等
  • 原子性 Atomicity
  • 一致性 Consistency
  • 隔离性 Isolation
  • 持久性 Durability

    1️⃣只有针对表的insert, update, delete 才能使用事务进行管理
    2️⃣终端修改数据的命令会自动触发事务, insert, update, delete
    3️⃣如果需要将自动开启的事务改为手动提交 关闭自动 set autocommit=0;

其他

1️⃣ ubuntu mysql 终端无法输入中文解决方法

export LANG=en_US.UTF-8 # 修改本地用户的字符集即可

2️⃣ 数据库默认字符集为utf8 只能存储3个字节的数据,标准的emoji表情是4个字节,所以要支持emoi表情的话就要修改字符集

utf8 --> utf8mb4 # 前提是mysql版本 > 5.5.3

mb4: most byte 4,专门兼容四个字节的,utf8mb4是向下兼容utf8的,即使修改了字符集也不会影响线上数据。

REFER:
58到家数据库30条军规解读

  • 发表于 2019-03-28 18:40
  • 阅读 ( 147 )
  • 分类:网络文章

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除