第二章 MySQL简介

web小张
80 阅读8分钟

数据库简介

数据库的作用

  1. 持久的存储数据
  2. 备份和恢复数据
  3. 快速的存取数据
  4. 权限控制

数据库的类型

  • 关系型数据库

    特点：表和表之间存在关联

    优点：能够表达复杂的数据关系；配合SQL，能够精确地查找到想要的数据

    缺点：读写数据性能比较差，尤其是海量数据的读写；数据结构比较死板

    代表：MySQL、Oracle、SQL Server

  • 非关系型数据库

    特点：用极其简单的数据结构存储数据

    优点：海量数据读写效率较高；格式灵活

    缺点：难以表示复杂的数据结构；对复杂查询的效率不好

    代表：MongoDB、Redis、Membase

术语

  • DB

    DataBase，数据库

  • DBA

    DataBase Administrator，数据库管理员

  • DBMS

    DataBase Management System，数据库管理系统

  • DBS

    DataBase System，数据库系统

    DBS = DB + DBA + DBMS

MySQL的使用

使用MySQL前，需要开启MySQL服

  • 进入MySQL命令行交互

    mysql -uroot -p...

  • 查看当前数据库的字符集编码

    show variables like 'character\_set\_%';

  • 查看当前已有的数据库

    show databases;

数据库设计

SQL

Structured Query Language，结构化查询语言，大部分关系型数据库，拥有着基本一致的SQL语法

SQL可以划分为DDL、DML、DCL

  • DDL

    Data Definition Languages，数据定义语言

    用于操作数据库对象，数据库对象包括库、表、视图、存储过程等

  • DML

    Data Manipulation Language，数据操控语言

    用于操作数据库中的记录

  • DCL

    Data Control Language，数据控制语言

    用于操作用户权限

管理库

  • 创建库

    create database 库名;

  • 使用/切换库

    use 库名;

  • 删除库

    drop database 库名;

管理表

  • 创建表

    create table 表名(字段名 字段类型 [字段约束], 字段名 字段类型 [字段约束]);

    MySQL中的字段的类型大致包括：

    • bit

      占1位，0或1，false或true

    • int

      占32位，整数

    • decimal(m, n)

      能精确计算的实数

      m是总的数字位数，n是小数位数

    • char(n)

      固定长度为n的字符

    • varchar(n)

      长度可变，最大长度为n的字符

    • text

      大量的字符

    • date

      日期

    • datetime

      日期和时间

    • time

      时间

  • 修改表

    alter table 表名 modify 字段名 新的字段类型;

  • 删除表

    drop table 表名;

主键和外键

根据表的设计原则，每张表都需要有一个主键

主键必须满足以下要求：

  1. 唯一
  2. 不能更改
  3. 无业务含义

外键是用于与其他表（也可以是本表）产生关系的列，外键需要链接到其他表（也可以是本表）的主键

alter table 子表表名 add constraint 外键名 foreign key（子表字段名）references 表名(主表字段名);

表关系

表与表的关系分为：

  • 一对一关系

    一个A对应一个B，一个B也对应一个A

    实现方式：将其中一张表中的主键同时设置为外键

  • 一对多关系

    一个A对应多个B，一个B对应一个A，A与B是一对多，B与A是多对一

    实现方式：在多端的表中设置外键

  • 多对多关系

    一个A对应多个B，一个B对应多个A

    实现方式：需要新建一张中间表（也称为关系表），中间表中至少包含两个外键，分别对应A和B两张表

三大范式

  1. 数据库中的每一字段都不可再分割
  2. 非主键列必须依赖于主键列
  3. 非主键列必须直接依赖于主键列

表记录的增删改

在数据库中，数据库对象最好加上反引号``（避免与关键字重名），字符和日期时间数据应该加上单引号''

增

insert into 表名 (字段1, 字段2, 字段3) values (值1, 值2, 值3), (值1, 值2, 值3);

删

delete from 表名 [where 条件字段名 = 值];

改

update 表名 set 操作字段名 = 值 [where 条件字段名 = 值];

单表基本查询

基础查询

  • 查询某几列

    select 字段1, 字段2, 字段3 from 表名;

  • 为列定别名

    select 字段名 as 别名 from 表名;

    为表定别名：

    select 字段名 from 表名 as 别名;

  • 查询所有列

    select * from 表名;

  • 去重

    select distinct 去重字段1[, 去重字段2] from 表名;

    distinct需要出现在所有查询字段的最前面，作用是对这些字段的组合数据进行去重

  • 值变换

    select case 字段名 when 值1 then 新值1 when 值2 then 新值2 else 值3 end from 表名;

    假设user表中的性别字段ismale是用1和0来表示的，但想在查询结果中显示男和女，则可以使用case

    select case ismale when 1 then '男' else '女' end as sex from user;

条件查询

  • =、!=

    select * from 表名 where 条件字段名 = 值;

    如果要查询的字段的值为null，则不能用=，需要使用is关键字

    select * from 表名 where 条件字段名 is null;

  • in

    select * from 表名 where 条件字段名 in (值1, 值2, 值3);

  • >、<、>=、<=

    select * from 表名 where 条件字段名 > 值;

  • between and

    select * from 表名 where 条件字段名 between 小值 and 大值;

  • like

    select * from 表名 where 条件字段名 模糊匹配的字符串;

    模糊查询：'_三%'：'三'前面必须出现1个任意字符，后面可以出现0 或 多个任意字符

  • and

    select * from 表名 where 条件1 and 条件2;

  • or

    select * from 表名 where 条件1 or 条件2;

执行顺序：from > where > select

排序

升序排序：

select * from 表名 order by 排序字段名 asc;

降序排序：

select * from 表名 order by 排序字段名 desc;

组合排序：

select * from 表名 order by 排序字段名1 asc, [排序字段名2 desc];

执行顺序：from > where > select > order by

分页

select * from 表名 limit n, m;

limit n, m：跳过前n条数据，取出m条数据

查询第x页的y条数据，limit (x - 1) * y, y

执行顺序：from > where > select > order by > limit

联表查询

全连接

select * from 表名1, 表名2;

全连接查询出来的结果叫做笛卡尔积

全连接的两张表可以是同一张表

左连接

左连接会把左表中的记录全部查出，右表当中只查出满足条件的记录，如果右表没有满足条件的记录，则查询结果中属于右表的字段均会显示null

select * from 左表表名 left join 右表表名 on 条件;

右连接

右连接会把右表中的记录全部查出，左表当中只查出满足条件的记录，如果左表没有满足条件的记录，则查询结果中属于左表的字段均会显示null

select * from 左表表名 right join 右表表名 on 条件;

内连接

select * from 表名1 inner join 表名2 on 条件;

内连接可以连接两张以上的表

select * from 表名1 inner join 表名2 on 条件1 inner join 表名3 on 条件2;

多表查询的关键字均属于from之下，所以这些关键字均在where之前运行

函数和分组

函数

MySQL中的函数分为内置函数和自定义函数

内置函数

数学函数：

  • ABS(x)

    返回x的绝对值

  • CEILING(x)

    返回大于x的最小整数值

  • FLOOR(x)

    返回小于x的最大整数值

  • MOD(x, y)

    返回x/y的模（余数）

  • PI()

    返回π的值（圆周率）

  • RAND()

    返回0到1之间的随机数

  • ROUND(x)

    返回参数x的四舍五入的有y位小数的值

  • TRUNCATE(x, y)

    返回数字x截断为y位小数的结果

聚合函数：

  • AVG(col)

    返回指定列的平均值

  • COUNT(col)

    返回指定列中非NULL值的个数

  • MIN(col)

    返回指定列中的最小值

  • MAX(col)

    返回指定列中的最大值

  • SUM(col)

    返回指定列的所有值之和

聚合函数会将所有列聚合起来，因此不能既查询聚合结果又查询普通记录

字符函数：

  • CONCAT(s1, s2, ..., sn)

    将s1，s2, ..., sn连接成一个字符串

  • CONCAT_WS(sep, s1, s2, ..., sn)

    将s1，s2, ..., sn通过sep字符拼接成一个字符串

  • TRIM(str)

    去除字符串首部和尾部的所有空白字符

  • LTRIM(str)

    去除字符串str开头处的空白字符

  • RTRIM(str)

    去除字符串str结尾处的空白字符

日期函数：

  • CURDATE()或CURRENT_DATE()

    返回当前的日期

  • CURTIME()或CURRENT_TIME()

    返回当前的时间

  • TIMESTAMPDIFF(part, date1, date2)

    返回从date1到date2之间相隔的part值

    part可以是：MICROSECOND、SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER、YEAR

分组

将具有相同字段值的记录合并为一组：

select 字段1, 字段2 from 表名 group by 字段1, 字段2;

注意：

  • 分组查询中所查询的字段，只能是group by关键字后面出现的字段以及聚合函数字段（聚合的字段可以不在group by之中）
  • 分组查询中的聚合函数，聚合的是一个组的记录，而不是所有记录

要对分组后的内容进行筛选，可以使用下面的方式：

select 字段1, 字段2 from 表名 group by 字段1, 字段2 having 条件;

执行顺序：from > where > group by > having > select > order by > limit

不同数据库之间这些关键字的运行顺序有所不同，这仅仅是MySQL中的关键字运行顺序

注意：having之后的条件可以使用聚合函数，where后面的条件不可以使用聚合函数

avatar
文章
阅读
粉丝