MySQL 基础篇

文章目录

MySQL 基础篇

1. 数据库概述

  • 关系型数据库典型的数据结构就是 数据表,这些数据表的组成都是结构化的(Structured)
  • 将数据放到表中,表再放到库中;
  • 一个数据库中可以有多个表,每个表都有一个唯一的标识名;

1.1. 表、记录、字段

  • E-R(entity-relationship,实体-联系)模型中有三个主要概念是:实体集、属性、联系集
  • 一个实体集(class)对应数据库中的一个表(table);一个实体(instance)则对应于数据表中的一行(row),也成为一条记录(record);一个属性(attribute)对应于数据库表中的一列(column),也称为一个字段(field);
ORM 思想(Object Relational Mapping)
表 <---> 类
一条记录 <---> 实例对象(实体)
列 <---> 类中的一个字段(field)

1.2. 表的关联关系

  • 表与表之间的数据记录之间有四种关系(relationship):一对一关联、一对多关联、多对多关联、自我引用;
1.2.1. 一对一关联(one-to-one)

不常用,一对一可以创建成一张表(当然有时候字段太多也可以拆分下);

  • 外键唯一:主表的主键与从表的外键(唯一),形成主外键关系,外键唯一;
  • 外键是主键:主表的主键和从表的主键,形成主外键关系;
1.2.2. 一对多关联(ont-to-many)

常见实例场景:客户表和订单表、分类表和商品表、部门表和员工表

一对多建表原则:在从表(多方)创建一个字段,字段作为外键指向主表(一方)的主键;

1.2.3. 多对多关联(mant-to-many)

多对多关系中必须有第三个表,通常称为 联接表,它将多对多关系划分为两个一对多关系,将这两个表的主键都插入到第三个表中;

  • 举个栗子:学生-课程

    • 学生表:学号、姓名、班级
    • 课程表:课程号、授课老师、简介
    • 选课表:一个学生可以选多门课,一门课可以被多个学生选择
  • 再举个栗子:产品-订单

    《订单表》和《产品表》有一种多对多的关系,这种关系是通过《订单明细表》建立两个一对多关系来定义的。一个订单可以有多个产品,每个产品可以出现在多个订单中;

    • 产品表
    • 订单表
    • 订单明细表

2. SQL 之 SELECT

2.1. 基本规则

  • 为了可读性,SQL 各子句分行写,必要时使用缩进

  • 每条命令以 ;\g\G 结束

  • 列的别名,尽量使用双引号 "",不建议省略 as

  • 字符串型和日期类型的数据使用单引号 '' 表示

  • MySQL 在 Windows 下大小写不敏感,而在 Linux 下大小写敏感;

    • 数据库名、表名、表的别名、变量名是严格区分大小写的
    • 关键字、函数名、列明(或字段名)、列的别名(字段的别名)时忽略大小写的
  • 大小写规范:

    1. 数据库名、表名、表别名、字段名、字段别名等都小写
    2. SQL 关键字、函数名、绑定变量等都大写
  • 注释

    单行注释:# 注释文字
    单行注释:-- 注释文字  --(--后面必须有一个空格)
    多行注释:/* 注释文字 */
    
  • 命名规则

    1. 数据库、表名在 30 个字符以内,变量名限制为 29 个
    2. 必须只能包含 A-Z、a-z、0-8、_ 共 63 个字符
    3. 同一个 MySQL 中,数据库不能同名;同一个数据库中,表不能同名;同一个表中,字段不能重名;
    4. 字段中不能和保留字、数据库系统或常用方法冲突,冲突则需要用 ` 着重号引起来
    5. 保持字段名和类型的一致性
  • 导入现有的数据表、表的数据:

    1. source 文件的全路径名:source d:\\book.sql
    2. 使用图形化界面工具导入

2.2. 基本语法

  1. 伪表 DUAL: 一个不存在的表,它的字段和记录都是无意义的,用来方便操作;

    SELECT 1 + 1
    # 上面的 SQL 会被自动补为
    SELECT 1 + 1
    FROM DUAL
    
  2. 列的别名 alias: 简称 as,当列的别名中有空格时,需要使用双引号 "" 引起来;

    SELECT employee_id emp_id, last_name AS lname, department_id "部门 id"
    FROM employees;
    
  3. 去除重复行 DISTINCT

    SELECT DISTINCT department_id
    FROM employees
    
  4. 空值 null: 空值不等同于 0,空值参与运算,结果也一定为空;

  5. 查询常数: 在查询结果中添加一列或多列

    SELECT '尚硅谷', 123, employee_id, last_name
    FROM employees
    
  6. 显示表结构 DESCRIBE

    DESCRIBE employees
    # 简写为 DESC
    DESC employees
    
  7. 过滤数据 WHERE

    SELECT *
    FROM employees
    # 过滤条件
    WHERE department_id = 90
    

2.3. 运算符

只要 NULL 值参与运算,结果就为 NULL;有特殊情况 <=> 安全等于号,运算符主要需要注意的就是 NULL 值参与运算是结果是什么;

2.3.1. 算术运算符

算术运算符主要用于数学运算,可以连接运算符前后的两个数值或表达式,对数值或表达式进行加减乘除或取模运算;

运算符 名称 示例
+ 加法运算符 SELECT A + B
- 减法运算符 SELECT A - B
* 乘法运算符 SELECT A * B
/DIV 除法运算符 SELECT A / B
%MOD 求模(求余)运算符 SELECT A % B
SELECT 100, 100 + 0, 100 - 0, 100 + 50, 100 + 50 - 0, 100 + 35.5, 100 - 35.5
FROM DUAL;
# 100 100 100 150 150 135.5 64.5

SELECT 100 + '1' # 在 SQL 中,+ 没有连接的作用
FROM DUAL;
# 101

SELECT 100 + '1' # 此时将 'a' 看做 0 处理
FROM DUAL;
# 100

SELECT 100 + NULL # NULL 参与运算结果为 NULL
FROM DUAL;
# 100

SELECT 100 * 1, 100 * 1.0, 100 / 1.0, 100 / 2, 100 + 2 * 5 / 2, 100 / 3, 100 DIV 0
FROM DUAL;
# 100 100.0 100.0000 50.0000 105.0000 33.3333 NULL

SELECT 12 % 3, 12 % 5, 12 MOD -5, -12 % 5, -12 % -5
FROM DUAL
# 0 2 2 -2 -2
2.3.2. 比较运算符
运算符 名称 示例
= 等于
<=> 安全等于
<>(!=) 不等于
< 小于
<= 小于等于
> 大于
>= 大于等于
  1. 等于 =

    SELECT 1 = 2,1 != 2, 1 = '1', 1 = 'a', 0 = 'a', 'a' = 'a', 'ab' = 'ab', 'a' = 'b'
    FROM DUAL # 两边都是字符串则以 ASCII 码比较
    # 0 1 1 0 1 1 1 0
    
  2. 安全等于 <=>:与 =唯一区别 是: <=> 可以用来对 NULL 进行判断;

    SELECT 1 <=> NULL, NULL <=> NULL # 两边都是 NUll 则为 1,一边是 NULL则为 0
    FROM DUAL;
    # 0 1
    

比较运算符之一些关键字;

运算符 名称 示例
IS NULL 判断值、字符串或表达式是否为空 SELECT B FROM TABLE WHERE A IS NULL
IS NOT NULL 判断值、字符串或表达式是否不为空 SELECT B FROM TABLE WHERE A IS NOT NULL
LEAST 在多个值中返回最小值 SELECT D FROM TABLE WHERE C LEAST(A, B)
GREATEST 在多个值中返回最大值 SELECT D FROM TABLE WHERE C GREATEST(A, b)
BETWEEN AND 判断一个值是否在两个值之间,闭区间 SELECT D FROM TABLE WHERE C BETWEEN A AND B
ISNULL 判断一个值、字符串或表达式是否为空 SELECT B FROM TABLE WHERE ISNULL(A)
IN 判断一个值是否为列表中的任意一个值 SELECT D FROM TABLE WHERE C IN (A, B)
NOT IN 判断一个值是否不是一个列表中的任意一个值 SELECT D FROM TABLE WHERE C NOT IN (A, B)
LIKE 判断一个值是否符合模糊匹配规则 SELECT C FROM TABLE WHERE A LIKE B
REGEXP 判断一个值是否符合正则表达式的规则 SELECT C FROM TABLE WHERE A REGEXP B
RLIKE 判断一个值是否符合正则表达式的规则 SELECT C FROM TABLE WHERE A RLIKE B
# % 代表不确定个数的字符(0 个,1 个,多个)
# _ 代表一个不确定的字符
# \_ 代表 _
# \\ 代表 \
SELECT last_name
FROM employees
WHERE last_name LIKE "%a%";

# 把转义字符设置为 $
SELECT last_name
FROM employees
WHERE last_name LIKE "_$_a%" ESCAPE '$';

REGEXP 运算符,正则;语法格式为:expr REGEXP 匹配条件 如果 expr 满足匹配条件,返回 1;不满足则返回 0;若 expr 或 匹配条件任意一个为 NULL,则结果为 NULL;

(1) '^' 匹配以紧跟着的字符开头的字符串
(2) '$' 匹配以紧跟着的字符结尾的字符串
(3) '.' 匹配任意一个单字符
(4) '[...]' 匹配方括号内任意一个字符,[0-9] 代表任意数字
(5) '*' 拓展零个或多个他前面的字符

SELECT 'xbai-hang' REGEXP '^x', 'xbai-hang' REGEXP 'g$', 'xbai-hang' REGEXP 'bai'
FROM DUAL;
# 1 1 1 
2.3.3. 逻辑运算符

逻辑运算主要用来判断表达式的真假,在 MySQL 中,逻辑运算符的返回结果为 1、0、NULL;

OR 和 AND 可以一起使用,AND 的优先级高于 OR;

运算符 作用 示例
NOT! 逻辑非 SELECT NOT A
AND&& 逻辑与 SELECT A AND B
SELECT A && B
OR 或 ` `
XOR 逻辑异或 SELECT A AND B
2.3.4. 位运算符

将操作数转换成二进制,然后进行位运算,最后将结果转换为十进制;

运算符 作用 示例
& 按位与 SELECT A & B
` ` 按位或
^ 按位异或 SELECT A ^ B
~ 按位取反 SELECT ~A
>> 按位右移 SELECT A >> 2
<< 按位左移 SELECT A << 2
2.3.5. 运算符优先级
优先级 运算符
1 :==, =(赋值)
2 `
3 &&, AND
4 NOT
5 BETWEEN, CASE, WHEN, THEN, ELSE
6 =(比较运算符), <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, IN
7 `
8 &
9 <<, >>
10 -, +
11 *, /, DIV, %, MOD
12 ^
13 -(负号), ~(按位取反)
14 !
15 ()

2.4. 排序

如果没有排序操作,默认查询返回的数据是以添加数据的顺序显示的;

2.4.1. 排序规则
  • 使用 ORDER BY 子句排序;
    • ASC(ascend):升序,ORDER BY 子句的默认值
    • DESC(descend):降序
  • ORDER BY 子句在 SELECT 语句的结尾;
2.4.2. 单列排序
# 默认升序,不写 ASC 也是升序
SELECT employee_id, last_name, salary
FROM employees
ORDER BY salary DESC;

# WHERE 要在 FROM 之后、ORDER BY 之前
SELECT employee_id AS emp_id, salary
FROM employees
WHERE department_id IN (50, 60, 70)
-- WHERE emp_id IN (50, 60, 70) # 错误的,WHERE 子句中不能用别名
ORDER BY emp_id DESC;
2.4.3. 多列排序

当第一列列值相同时,对第二列进行排序;如下例中 部门 id 降序排序,相同部门时按 薪资高低升序排序;

# 先按照 department_id 降序排序,再按照 salary 升序排序
SELECT employee_id, department_id, salary
FROM employees
ORDER BY department_id DESC, salary ASC;

2.5. 分页

WHERE、ORDER BY、LIMIT 声明顺序(非执行顺序):先 WHERE,后 ORDER BY,最后 LIMIT

2.5.1. 使用规则
  • LIMIT 位置偏移量, 条目数,偏移量为 0 时可以省略;
    • LIMIT 0, 条目数 等价于 LIMIT 条目数
  • MySQL 8.0 新特性:LIMIT ... OFFSET ...
    • LIMIT 条目数 OFFSET 偏移量
SELECT employee_id, last_name
FROM employees
LIMIT 0, 20; -- 每页 20 条记录,显示第 1 页

SELECT employee_id, last_name
FROM employees
LIMIT 20, 20; -- 每页 20 条记录,显示第 2 页

# 需求:每页显示 pageSize 条记录,此时显示第 pageNo 页
# 公式:LIMIT (pageNo - 1) * pageSize, pageSize;
SELECT employee_id, last_name
FROM employees
LIMIT 20, 20;
  • 分页显示公式: (当前页数 - 1) * 每页条数,每页条数

    SELECT * FROM table
    LIMIT (pageNo - 1) * pageSize, pageSize;
    
  • LIMIT 子句必须放在整个 SELECT 语句的最后

  • 使用 LIMIT的好处:

    约束返回结果的数量可以 减少数据表的网络传输量,也可以 提升查询效率当直到返回结果只有 1 条,就可以使用 LIMIT 1 ;这样做的好处是 SELECT 不需要扫描完整的表,只需要检索到一条符合条件的记录即可返回;

2.6. ⭐多表查询

多表查询:也称为关联查询,指两个或更多个表一起完成查询操作;

前提条件:一起查询的表之间有关联关系(一对多,一对一),它们之间有关联字段,这个关联字段可能建立了外键,也可能没有建立外键;

比如:员工表和部门表,两个表依靠 部门编号 进行关联;

【Alibaba 开发手册 强制】超过三个表禁止 join。需要 join 的字段,数据类型保持绝对一致;多表关联查询时, 保证被关联的字段需要有索引。

说明:即使双表 join 也要注意表索引、SQL 性能。

  • 表中有相同列时,在列名之前加上表名前缀
  • 给表起别名后,SELECT、WHERE 中使用表名则必须用表的别名
  • 从 sql 优化的角度,建议多表查询时,每个字段前都指明其所在的表
等值连接与非等值连接
  • 等值连接: 即 WHERE 语句的条件是等值判断;

    SELECT e.employee_id, d.department_name, e.department_id AS department_id
    FROM employees AS e, departments AS d
    WHERE e.department_id = d.departmen_id
    
  • 非等值连接

    SELECT e.last_name, e.salary, j.grade_level
    FROM employees e, job_grades j
    WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal
    
自连接与非自连接

涉及多表查询的都是非自连接;自连接仅涉及一张表(自我引用,逻辑层面视为多张表)

SELECT emp.employee_id, emp.last_name, mgr.employee_id, mgr.last_name
FROM  employees AS emp, employees AS mgr
WHERE emp.manager_id = mgr.employee_id;
内连接与外连接
  • 内连接:合并具有同一列的两个以上的表的行,结果集中不包含一个表与另一个表不匹配的行;

    SELECT 字段列表
    FROM A 表 [INNER] JOIN B 表
    ON 关联条件
    WHERE 等其子句
    
    SELECT e.last_name, d.department_name
    FROM employees e JOIN departments d
    ON e.department_id = d.department_id;
    
  • 外连接:两个表在连接过程中除了返回满足连接条件的行以外 还返回左(或右)表中不满足条件的行,这种连接称为左(或右)外连接,没有匹配的行时,结果表中相应的列为空(NULL);

  • 如果是左外连接,则连接条件中左边的表也称为 主表 ,右边的表称为 从表

    SELECT 字段列表
    FROM A 表 LEFT | RIGHT [OUTER] JOIN B 表
    ON 关联条件
    WHERE 等其子句
    
    SELECT last_name, department_name
    FROM employees e LEFT JOIN  departments d
    ON e.department_id = d.department_id
    
    SELECT last_name, department_name
    FROM employees e RIGHT JOIN  departments d
    ON e.department_id = d.department_id
    
  • 如果是右外连接,则连接条件中右边的表也称为 主表 ,左边的表称为 从表

2.7. UNION 的使用

UNION 用于合并查询结果,将多条 SELECT 语句的结果组合成单个结果集;合并时,两个表对应的列数和数据类型必须相同,并相互对应;

各个 SELECT 语句之间使用 UNION 或 UNION ALL 关键字隔离;

语法格式:

SELECT colum, ... FROM table1
UNION [ALL]
SELECT colum, ... FROM table2
  • UNION UNION 操作符返回两个查询的结果集的并集,并去除重复记录
  • UNION ALL UNION ALL 操作符返回两个查询结果的并集,对于两个结果集的重复部分,不去重

注意:执行 UNION ALL 语句时所需要的资源比 UNION 语句少。如果明确知道合并数据后的结果数据不存在重复数据,或者不需要去除重复的数据,则尽量使用 UNION ALL 语句,以提高数据查询的效 率。

2.8. SQL99 新特性

自然(NATURAL)连接

自然连接 NATURAL JOIN 自动查询两张连接表中的 所有相同字段 ,然后进行 等值连接

SELECT employee_id, last_name, department_name
FROM employees e JOIN  departments d
ON e.department_id = d.department_id
AND e.manager_id = d.manager_id;

上面的查询语句可以写为如下语句:

# 改写为自然连接
SELECT employee_id, last_name, department_name
FROM employees e NATURAL JOIN  departments d
USING 连接

USING 连接制定了具体的相同的字段名称,在 USING 的括号 () 中填入要指定的同名字段;

SELECT employee_id, last_name, department_name
FROM employees e JOIN  departments d
ON e.department_id = d.department_id

上面的查询语句可以写为如下语句:

SELECT employee_id, last_name, department_name
FROM employees e JOIN  departments d
USING(department_id)

3. 单行函数

在 MySQL 中,函数分为内置函数与自定义函数;内置函数从实现的功能角度可以分为: 数值函数、字符串函数、日期和时间函数、流程控制函数、加密与解密函数、获取 MySQL 信息函数、聚合函数等;

3.1. 单行函数的理解

  • 操作数据对象
  • 接受参数返回一个结果
  • 0
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
要在MySQL数据库中存储圆形要素,需要使用空间数据类型和空间函数。 MySQL支持几种空间数据类型,包括POINT、LINESTRING、POLYGON、MULTIPOINT、MULTILINESTRING、MULTIPOLYGON和GEOMETRYCOLLECTION。其中,圆形可以用POLYGON类型来示。一个简单的圆形可以示为一个由多边形组成的正多边形,多边形的边数越多,示的圆形越接近圆形。 例如,以下代码创建了一个圆形的多边形: ``` SET @center = GeomFromText('POINT(116.397428 39.90923)'); SET @radius = 1000; SET @poly = CONCAT('POLYGON((', X(@center) + @radius * COS(RADIANS(45)),' ', Y(@center) + @radius * SIN(RADIANS(45)),',', X(@center) + @radius * COS(RADIANS(135)),' ', Y(@center) + @radius * SIN(RADIANS(135)),',', X(@center) + @radius * COS(RADIANS(225)),' ', Y(@center) + @radius * SIN(RADIANS(225)),',', X(@center) + @radius * COS(RADIANS(315)),' ', Y(@center) + @radius * SIN(RADIANS(315)),',', X(@center) + @radius * COS(RADIANS(45)),' ', Y(@center) + @radius * SIN(RADIANS(45)),'))'); ``` 在MySQL中,可以使用空间函数进行圆形的操作,例如计算两个圆形之间的距离、判断一个点是否在圆形内等。 以下是一个例子,计算一个点是否在圆形内: ``` SET @center = GeomFromText('POINT(116.397428 39.90923)'); SET @radius = 1000; SET @point = GeomFromText('POINT(116.383668 39.902188)'); SELECT Contains( GeomFromText(CONCAT('POLYGON((', X(@center) + @radius * COS(RADIANS(45)),' ', Y(@center) + @radius * SIN(RADIANS(45)),',', X(@center) + @radius * COS(RADIANS(135)),' ', Y(@center) + @radius * SIN(RADIANS(135)),',', X(@center) + @radius * COS(RADIANS(225)),' ', Y(@center) + @radius * SIN(RADIANS(225)),',', X(@center) + @radius * COS(RADIANS(315)),' ', Y(@center) + @radius * SIN(RADIANS(315)),',', X(@center) + @radius * COS(RADIANS(45)),' ', Y(@center) + @radius * SIN(RADIANS(45)),' ))'), @point ); ``` 如果返回值为1,则示点在圆形内,否则不在。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

睫毛进眼睛了!

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值