MySQL 基础回顾_Mysql

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; '黄_' # 查询姓黄且名字是一个字的学生
  - _ 表示一个任意字符
- 范围查询
  - 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
排序 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 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作用: 最后新增一行，来记录当前列里所有记录的总和
连接查询多表查询 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

自关联
应用场景区域信息，分类信息（如淘宝分类栏，大类，小类，具体分类）
子查询
- 标量子查询一行一列
  子查询的结果是一个标量
  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语句
事务
事务是多个sql语句操作的序列，这些操作要么都执行，要么都不执行，如有有一个失败，便回滚到原始状态
应用场景: 充话费，银行转账，地铁卡充值等

原子性 atomicity
一致性 consistency
隔离性 isolation
持久性 durability

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