mysql进阶（一）

最新推荐文章于 2024-09-11 06:54:51 发布

王帅真

最新推荐文章于 2024-09-11 06:54:51 发布

阅读量371

点赞数

分类专栏： MySQL 文章标签：数据库 mysql

本文链接：https://blog.csdn.net/weixin_44677382/article/details/109173805

版权

MySQL 专栏收录该内容

6 篇文章 1 订阅

订阅专栏

列属性完整性

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)