mysql进阶（一）

列属性完整性

primary key 主键

• 绝对确定唯一，99%不会再去更改，且能确定数据的存在性（不能为空）
• 优点
  • 查询快且方便
  • 保证数据完整性，到哪张表都能查
• 用数字做主键的好处
  • 规律性
  • 唯一性
  • 处理方便（相对string而言）
• 举例：我们的身份证

create table t_8(
id int(18) primary key,
name varchar(30)
);

• 忘记添加？我们可以后期添加主键

alter table t_8 add primary key (id);

• 删除主键

alter table t_8 drop primary key;

组合键/复合键

• 其实实际情况我们是可以有多个主键的

• 但是用的少

  • 作用并不大
  • 维护性差

• 组合键本质其实也是一个主键，多个字段组成一个

• 应用：用户注册中的唯一的ID和唯一的“昵称”

  • 缺陷：不好判断改用哪个；拓展性差

unique 唯一键

• 与primary key的区别

  • 不是用来区分数据的
  • 一个表里可以有多个主键
  • 在自己表里作用折腾，与其它表不一定有关联（不会被其他表引用）
  • 可以为空

• 用途：保证数据唯一不重复（比如：电话号码…）

create table t_9(
id int(18) primary key,
name varchar(30),
phone varchar(11) unique
);

• 忘记添加？我们可以后期添加主键

alter table t_9 add unique (phone);

• 唯一键同样可以组合，一张表内全部是unique也行，但用处不大…

• 删除唯一键

alter table t_9 drop index phone;

注释语法

create table tt(
id int(20), # this is primary key!
phone varchar(11) unique comment '电话号码'
);

程序员咱们一般看comment（SQL内字段注释）

在show create中我们可以看到comment注释

约束

• 保证数据完整性：一张表内要有个主键标识

• 保证数据类型：int还是varchar，能空还是不能空，该default要给

• 保证数据表的完整性

foreign key 外键

• 背景

  • 有一张学生表【id】，有学生特定学号ID——主表
  • 有一张食堂订单表【id|student_id】，有特定交易订单号ID——从表

• 需要我们关注的

  • 食堂订单表的student_id不能是学生表没有的
  • 如果学生表中有某个id被删除了，食堂订单表中的记录应当还在
  • 学生表的id不能修改
  • 删除表操作：先删从，后删主

• 带外键表的创建

# 输入内容
mysql> create table canteen(
    -> id int primary key,
    -> money decimal(10,4),
    -> tid int(11),
    -> foreign key (tid) references teacher(id)	# 外键引用
    -> );
# 输出内容
Query OK, 0 rows affected (0.02 sec)
# 输入内容
mysql> desc canteen;
# 输出内容
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id    | int(11)       | NO   | PRI | NULL    |       |
| money | decimal(10,4) | YES  |     | NULL    |       |
| tid   | int(11)       | YES  | MUL | NULL    |       |
+-------+---------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

• 后期添加外键

alter table canteen add foreign key (tid) references teacher(id);

• 删除外键

# 首先查看创建信息
| canteen | CREATE TABLE `canteen` (
  `id` int(11) NOT NULL,
  `money` decimal(10,4) DEFAULT NULL,
  `tid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `tid` (`tid`),
  CONSTRAINT `canteen_ibfk_1` FOREIGN KEY (`tid`) REFERENCES `teacher` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
# 按CONSTRAINT删除
alter table canteen drop foreign key canteen_ibfk_1;

• 三种操作（假设开除id=5的学生）

  • 严格（前面的基本操作）
  • 置空（将食堂表sid=5的sid都置null，条目不删）【删除用】
  • 级联（将食堂表sid=5的条目同时更改/删除）【更新用】

mysql> create table eatery(
    -> id int(20) primary key,
    -> money decimal(10,4),
    -> sid int(4),
    # 删除置空，更新级联
    -> foreign key (sid) references student(id) on delete set null on update cascade
    -> );

• tips
  • 添加的外键必须是另一个表的“唯一”键（包括主键、唯一键）
  • 并发项目禁止“外键”
  • key那一栏的MUL代表可重复，比如说一个人可以买一个相同的东西很多次

数据库设计思维

数据库设计的基础概念

• 关系

  • 关系型数据库
  • 两张表的共有字段去确定数据的完整性

• 行【Entity】

  • 一条数据（记录）
  • 实体

• 列【Field】

  • 一个字段
  • 属性

总的来说，实体和属性就是为了OOP

• 数据冗余
  • 虽然有的时候为了性能，不需要设置total字段，但是如果查询频繁，考虑到并发性能，是要设置的，比如：高考
  • 减少，而非杜绝
  • 不管怎么样，还是一定要保证数据完整性

实体与实体之间的关系

• 一对多（比如：学生的食堂消费记录）

• 一对一（比如：学生请一个家教）

• 多对一

• 多对多

Codd范式

（实际有六种，只是常用的是三个）

• 第一范式：确保字段的原子性，不可再分

  • 比如：时间段2018-2019要改成start=2018，end=2019
  • 地址可不可分看项目，如果需要分区统计什么的，就要分

• 第二范式：非键字段必须依赖于键字段

  • 意思就是别扯淡，学生表就只放学生基本信息
  • 不要没事找事！！！比如：调查在校情侣，专门弄个情侣表

• 第三范式：消除传递依赖

  • 比如：知道语数英成绩了，就不要再列个总分了（根据项目来，跟前面不一样）

单表查询

玩一玩select

mysql> select '哈哈哈';
+--------+
| 哈哈哈 |
+--------+
| 哈哈哈 |
+--------+
1 row in set (0.00 sec)

mysql> select 2*7;
+-----+
| 2*7 |
+-----+
|  14 |
+-----+
1 row in set (0.00 sec)

mysql> select '哈哈哈' as '你笑啥？';
+----------+
| 你笑啥？ |
+----------+
| 哈哈哈   |
+----------+
1 row in set (0.00 sec)

mysql> select 2*7 as result;
+--------+
| result |
+--------+
|     14 |
+--------+
1 row in set (0.00 sec)

# 完整（dual是默认伪表，可以用来起别名）
mysql> select 2*7 as result from dual;
+--------+
| result |
+--------+
|     14 |
+--------+
1 row in set (0.00 sec)

关于from的笛卡儿积

mysql> select * from t1;
+------+------+
| id   | name |
+------+------+
|    1 | Tom  |
|    2 | Jack |
+------+------+
2 rows in set (0.00 sec)

mysql> select * from t2;
+------+------+
| s1   | s2   |
+------+------+
|   10 |    3 |
|    5 |    7 |
+------+------+
2 rows in set (0.00 sec)

mysql> select * from t1,t2;
+------+------+------+------+
| id   | name | s1   | s2   |
+------+------+------+------+
|    1 | Tom  |   10 |    3 |
|    2 | Jack |   10 |    3 |
|    1 | Tom  |    5 |    7 |
|    2 | Jack |    5 |    7 |
+------+------+------+------+
4 rows in set (0.00 sec)

说白了，就是【11 12 21 22】，但是很少用就是了

where

没啥好说的，就是筛选条件，有点像编程语言的if

（支持逻辑运算）

in

类似where中or的用法，可以搭配not使用

# in用法
select * from student where address in('北京','上海');
# where用法
select * from student where address ='北京' or address = '上海';
# not in,就是反过来
select * from student where address not in('北京','上海');

between

大小范围筛选

# between用法(相当于取[15,20])
select * from student where age between 15 and 20;
# where用法
select * from student where age>=15 and age<=20;

NULL

查询空

# null
select * from student where age is null;
# not null
select * from student where age is not null;

聚合函数

类似系统自带的数学函数

mysql> use school
Database changed
mysql> create table score(
    -> id int,
    -> chinese int,
    -> english int,
    -> math int
    -> );
Query OK, 0 rows affected (0.03 sec)

# 咔咔一顿操作
mysql> select * from score;
+------+---------+---------+------+
| id   | chinese | english | math |
+------+---------+---------+------+
|    1 |      98 |      99 |  100 |
|    2 |      90 |      92 |   80 |
+------+---------+---------+------+
2 rows in set (0.00 sec)

# 查总的语文成绩
mysql> select sum(chinese) from score;
+--------------+
| sum(chinese) |
+--------------+
|          188 |
+--------------+
1 row in set (0.00 sec)

常用的：

• sum(field)
• avg(field)
• max(field)
• min(field)
• count(field) [返回指定列的值的数目]

模糊查询

• 查找张*（后面可以跟若干个字符）

select * from student where name like '张%';

• 查找张*（后面只能跟1个字符）

select * from student where name like '张_';

排序查询 order by

• 升序

select * from score order by chinese asc;

• 降序

select * from score order by chinese desc;

分组查询 group by

• 查分组平均年龄（可以是性别，地区…）

mysql> select * from teacher;
+----+------+-------------+---------+------+
| id | name | phone       | address | age  |
+----+------+-------------+---------+------+
|  1 | Jack | 18359730121 | DTEN    | 23   |
|  2 | Tom  | 13850074544 | FZU     | 24   |
|  3 | Sid  | 54664554658 | DTEN    | 25   |
+----+------+-------------+---------+------+
3 rows in set (0.00 sec)

mysql> select avg(age) as '年龄',address as '地址' from teacher group by address;
+------+------+
| 年龄 | 地址 |
+------+------+
|   24 | DTEN |
|   24 | FZU  |
+------+------+
2 rows in set (0.00 sec)

• 聚合分组查询

mysql> select group_concat(name),address from teacher group by address;
+--------------------+---------+
| group_concat(name) | address |
+--------------------+---------+
| Jack,Sid           | DTEN    |
| Tom                | FZU     |
+--------------------+---------+
2 rows in set (0.00 sec)

查询结果集的虚拟表 having

我们可以先试着在查完的表里查表（套娃）

mysql> select avg(age) as '年龄',address as '地址' from teacher group by address;
+------+------+
| 年龄 | 地址 |
+------+------+
|   24 | DTEN |
|   22 | FZU  |
+------+------+
2 rows in set (0.00 sec)

mysql> select avg(age) as '年龄',address as '地址' from teacher group by address where age>23;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where age>23' at line 1

哎呀，不行？要用having！

mysql> select avg(age) as '年龄',address as '地址' from teacher group by address having 年龄>23;
+------+------+
| 年龄 | 地址 |
+------+------+
|   24 | DTEN |
+------+------+
1 row in set (0.00 sec)

（这边不规范，应该用英文age比较好）

where针对的是数据库表中的一个个实体，而having的查询是针对结果集的字段筛选

限定位置查询 limit

• limit start,len（起始位置，跨度）

mysql> select * from teacher limit 0,2;
+----+------+-------------+---------+------+
| id | name | phone       | address | age  |
+----+------+-------------+---------+------+
|  1 | Jack | 18359730121 | DTEN    | 23   |
|  2 | Tom  | 13850074544 | FZU     | 22   |
+----+------+-------------+---------+------+
2 rows in set (0.00 sec)

mysql> select * from teacher limit 1,2;
+----+------+-------------+---------+------+
| id | name | phone       | address | age  |
+----+------+-------------+---------+------+
|  2 | Tom  | 13850074544 | FZU     | 22   |
|  3 | Sid  | 54664554658 | DTEN    | 25   |
+----+------+-------------+---------+------+
2 rows in set (0.00 sec)

mysql> select * from teacher limit 1,1;
+----+------+-------------+---------+------+
| id | name | phone       | address | age  |
+----+------+-------------+---------+------+
|  2 | Tom  | 13850074544 | FZU     | 22   |
+----+------+-------------+---------+------+
1 row in set (0.00 sec)

可以配合排序使用（查出年龄最大的老师）

mysql> select * from teacher order by age desc limit 0,1;
+----+------+-------------+---------+------+
| id | name | phone       | address | age  |
+----+------+-------------+---------+------+
|  3 | Sid  | 54664554658 | DTEN    | 25   |
+----+------+-------------+---------+------+
1 row in set (0.00 sec)

去重 distinct

mysql> select distinct address from teacher;
+---------+
| address |
+---------+
| DTEN    |
| FZU     |
+---------+
2 rows in set (0.00 sec)

# all就算不写，也是默认的
mysql> select all address from teacher;
+---------+
| address |
+---------+
| DTEN    |
| FZU     |
| DTEN    |
+---------+
3 rows in set (0.00 sec)