学习

MySQL用法及速查

学习与总结简单的SQL查询

数据库操作

创建数据库

创建主语句：

CREATE DATABASE name;

支持中文（设置编码）：

CREATE DATABASE IF NOT EXISTS 数据库名 DEFAULT CHARSET uft8 COLLATE utf8_general_ci;

删除数据库

删除语句

DROP DATABASE name;

选择数据库

USE 数据库名

显示数据库

SHOW DATABASES;

表

数据类型

字符串：

  • char(n) 固定长度为n的字符串，不足部分用空格补全
  • varchar(n) 长度不固定的字符串，但长度不能超过n

数字：

  • smallint：小正数
  • int：一般整数
  • bigint：大数
  • float
  • double

时间：

  • date：日期
  • time：时间
  • year：年份
  • datetime：混合存储日期加时间

显示数据表（SHOW）

  • 显示数据表
SHOW TABLE;
  • 显示全部数据行，每个结果一列
SHOW COLUMNS FROM 表名;

也可以使用 *DESCRIBE* 来实现同样的方法。

创建表 （CREATE TABLE）

基础语法

创建表使用的语法是 CREATE TABLE，需要同时给出表列的名字和类型，并用逗号分隔，可选其他属性（NOT NULL、 AUTO_INCREMENT、 DEFUALT）。

语句：

CREATE TABLE 表名 (
id int NOT NULL,
name varchar NOT NULL,
...,
PRIMARY KEY(id), #设置主键
);
主键（PRIMARY KEY）

主键是一整个表的索引，指向唯一的数据，所以主键的值必须是唯一的。

这并不代表主键只能是一列，主键也可以是多列，如果主键由多列组成，那么多列所构成的组合必须是唯一的。

设定主键语句

PRIMARY KEY(列名);
自动递增（AUTO_INCREMENT）

有时候，我们想某一列可以自动实现递增以来实现数据的唯一性，那么就可以设置该列为自动递增列。
每次新建行时，除非自己指定数据，否则该列就会自动寻找下一个可用的递增的数据进行填充。

  • 如何获得自动递增的最后数值：使用last_insert_id()函数即可返回最后一个递增的值。
设定默认值（DEFAULT）

如果想给某个数据设定一个预置值（默认值），可以在建表前在对应列名前使用DEFAULT关键字，设定默认值，这样子在插入数据时如果不对这一列设定数据，使用的则是默认值。

设置引擎（ENGINE）

引擎就是处理数据事务的方式，常见的引擎如下：

  • InnoDB ：常见的事务处理引擎，不支持全文本搜索。
  • MEMORY ：顾名思义，数据存储在内存中，速度快，但内存占用高。
  • MyISAM：性能高，支持全文本搜索，但不支持事务处理。

设定搜索引擎方式：

CREATE TABLE 表名 (
id int NOT NULL,
name varchar, NOT NULL,
...,
PRIMARY KEY(id), #设置主键
) ENGINE = 引擎;

注意：外键不能跨引擎！！！

更新表（ALTER TABLE）

要更新一个表的列定义，可以用 ALTER TABLE。

增加列（ADD）

给表增加列，用ADD。
语句：

ALTER TABLE 表名
ADD 列名 类型 其他;
删除列（DROP）

删除列，用DROP。

ALTER TABLE 表名
DROP COLUMN 列名;

删除表（DROP TABLE）

顾名思义，删除整个表

DROP TABLE 表名;

重命名表（RENAME TABLE）

RENAME TABLE 原表名 TO 新表名;

插入数据（INSERT）

往数据表里插入数据是常用的语句，关键字为 INSERT INTO...VALUE ，常见的用法有两种。

  1. 直接按照表字段顺序全插入相应数据

例子：

INSERT INTO 表名 
VALUES(
数据1,
数据2,
数据3,
...
数据n
);

这种写法比较简洁，但缺点是必须录入所有数据（不想录入的可以用NULL代替），且必须要按照表内字段顺序插入相应的数据。

  1. 先说明字段，再对应插入数据
    例子：
INSERT INTO 表名(
字段1,
字段2,
字段3,
)VALUES(
值1,
值2,
值3
);

这种写法比较安全，可以自定义需要插入的字段，数据的顺序也比较明确，缺点是比较繁琐。
同时这种写法可以省略一些非必填字段，灵活度比较高。

可以在同一个字段下插入多个数据
例子：

INSERT INTO 表名(
字段1,
字段2,
字段3,
)VALUES(
值1,
值2,
值3 #第一组数据
),(
值1,
值2,
值3 #第二组数据
);

如上所示，即可插入多个数据。

  1. 往表里插入从另一个表中检索的数据
    例子：
INSERT INTO 表名(
字段1,
字段2,
字段3,
)
SELECT 
字段1数据,
字段2数据,
字段3数据
FROM 检索的表;

这样子就可以将从另一个表的数据导入。

更新数据（UPDATE）

更新数据使用非常简单，只需要指出需要更新的表和设定更新的值即可，因为需要准确修改数据，所以要通过WHERE来确定修改的数据，使用方法如下：

UPDATE 表
SET 需要修改的数据1 = 修改的新数据,
需要修改的数据2 = 修改的新数据
WHERE 过滤条件
  • 虽然语法简单，但WHERE的使用稍不注意就会导致修改出错，所以要谨慎使用。
  • 多个修改数据用逗号（,）间隔。
  • 如果需要去除某一项数据，将其修改为NULL即可。

删除数据（DELETE）

删除数据的操作也是非常的简单，只需要用WHERE去过滤出需要删除的行即可。

语法：

DELETE FROM 表
WHERE 过滤条件;

这样子就可以将过滤出来的行删除，但如果省略了WHERE将会删除所有行，达到清空表的效果，不过我们一般不用这个来清空表，一般使用

TRUNCATE 表;

来清空整个表，这句的实际含义是重建一个新表。

检索语句（SELECT）

基础

  1. 检索基本语法
SELECT 列名, 列名 FROM 表名;
  • 多个列名以逗号分隔；
  • 可以用通配符 * 来实现查找全部数据;
  1. 检索去重
SELECT DISTINCT 列名 FROM 表名;
  • 在需要去重的列名前声明为DISTINCT即可。
  1. 限制检索显示数量
SELECT 列名 
FROM 表名
LIMIT 限制数; #只有一个限制数表示从头开始到限制数个

或者

SELECT 列名 
FROM 表名
LIMIT 开始列，限制数; #从第几列开始到限制数个
  1. 完全限定的列名
SELECT 表名.列名 FROM 表名;
  • 使用表名.列名进行完全限定。

排序检索数据（ORDERBY）

  1. 排序检索数据语法
SELECT 列名
FROM 表名
ORDER BY 列名 #按照升序排序
  • 可以按照多个列进行排序，优先级从前到后，用逗号分隔
  • 默认为升序，如需降序在列名后加 DESC

过滤数据（WHERE）

1 基础
  1. 过滤数据语句
SELECT 列名
FROM 表名
WHERE 列名 = 数据;
  • 注意：与排序（ORDER BY） 共用时，应该使排序位于WHRER之后。
  • 与串作比较的时候使用引号，与数值作比较时无需引号
  1. 操作符
操作符作用
=等于
<>(!=)不等于
BETWEEN...AND...在指定值之间
  1. 空值检查：IS NULL
2 组合子句
  1. 组合操作符
逻辑操作符实际含义（C++）
AND&&
OR| |
IN(min, max)
NOT!
  • 不加括号情况下，AND优先级 > OR优先级
  • 在MySQL下 NOT对往后取反的有 IN、BETWEEN与EXIST。

通配符过滤（LIKE）

通配符为WHERE子句的特殊形式，使用形式如下：

SELECT 列
FROM 表
WHERE 需匹配项 LIKE Condition;
  1. 百分号通配符(%)
    %表示在该字符在该位置出现任意次数，例如：
    'fatha%' ：查找所有开头为fatha的结果
    '%fatha' ：查找所有以fatha结尾的结果
    '%haha%' ：查找所有带有haha的结果
    'fat%ha'：查找以fat开头且以ha结尾的结果
  • ！：%无法匹配到NULL
  1. 下划线通配符( _ )
    下划线通配符与百分号通配符作用相似，但是每一个下划线代表只匹配一个相应的字符。

正则表达式过滤（REGEXP）

  1. 正则表达式子句
SELECT 列名
FROM 表名
WHERE 待查找列 
REGEXP 正则表达式;
  • BINARY ：在MySQL中，以该关键字来区分匹配字串的大小写。
  1. 进行OR匹配
    匹配符：| 或者 []

“|”匹配符例子：

...
REGEXP '前缀 conditon 1 | condition 2 | condition 3';

"[]"匹配符例子：

...
REGEXP '[abc]';

包含abc中任意字符即可。

  1. []的否定：^
SELECT * FROM STUDENT
WHERE name REGEXP 'test [^12]';

  1. [] 匹配范围：-
    例： [1-4] = [1234]

  2. 匹配特殊字符（转义）

拼接字段

拼接语句（Concat）
SELECT Concat(列1, '(', 列2, ')')
FROM 表名
...

使用Concat函数连接多个数据为一列数据，用逗号分隔。

使用别名（AS 导出列）
SELECT 列名 AS 别名
FROM 表名
...

用AS给列名起别名，使结果更易读。

去除空格函数（Trim）
Trim(需要去除左右空格的串) #去除左右空格
RTrim() #去除串右方空格
LTrim() #去除串左方空格
对数据进行四则运算

表中的数据可能不是我们所需要的数据，可以使用四则运算符号来进行四则运算。

SELECT 数据1 四则运算 数据2 FROM 表;

使用函数

数据分组（GROUP BY）

对指定列数据进行分组，通常与其他统计函数一起使用，每行输出一个类别。

SELECT 组, 组 
FROM 表
GROUP BY 要分组的列
  • 可以按照多个组来进行分组
  • NULL数据会独立开一组
  • GROUP BY 必须在WHERE之后，否则筛选不会生效
  • 如果在SELECT中使用表达式，则在GROUP BY中使用同样的表达式
WITH ROLLUP（？）
过滤分组（HAVING）

HAVING子句作用于GROUP BY语句进行过滤，作用和WHERE十分相似。

SELECT 组, 组 
FROM 表
GROUP BY 要分组的列
HAVING 过滤的条件

分组排序

如果分组后的结果需要按照特定列进行排序，那么可以使用ORDER BY指定特定的列进行排序。

各语句优先级

子查询（嵌套查询）

  • 原理：将一个查询的结果作为另一个查询的样本。

语句：

SELECT 列名 
FROM 表名
WHERE 要查询的值 比对符 (
SELECT 子查询的值 FROM 表名 WHERE 子查询条件
);

注意：要确保子查询的返回可以被上级查询正确调用！

组合查询（UNION）

组合查询，就是将多个查询语句（SELECT）的结果联合成一个表进行返回。

格式：

SELECT 列1，列2，列3
FROM 表
WHERE 条件
UNION
SELECT 列1，列2，列3
FROM 表
WHERE 条件;

这样就可以将两个查询的结果联合成一个表了。
结果默认为去重，不需要去重可以使用 UNION ALL 。
排序可以在末尾添加 ORDER BY 。

注意：

  1. 多个查询语句中列名必须为相同项，可不同顺序。
  2. 数据必须为一致或者可以进行隐式转换。

表联结（JOIN）

普通多表联结

  • 作用：可以获得多个表的数据，输出的形式为两个表的笛卡尔积

示例：

SELECT * FROM 表1, 表2
...

由上可见，联立多个表只需将不同的表名用逗号间隔即可。

表内联结（inner join）

  • 作用：连待联结的两个表 ON 条件符合的部分加入一个新表并联合展示
  • 展示范围：两个表的交集

示例：

SELECT * FROM 表1 inner join 表二 ON 条件

输出为两个表的联结输出。

左联结、右联结（Left Right join）

  • 连待联结的两个表 ON 条件符合的部分和左（右）表不符合的数据加入一个新表并联合展示，另一个表不符合的数据用NULL显示
  • 展示范围：左（右）表

示例：

SELECT * FROM 表1 left join 表二 ON 条件; #左联结
SELECT * FROM 表1 right join 表二 ON 条件; #右联结

表的别名

表的别名可以让我们在写查询语句的时候更容易，方法与列的别名相似。

方法1：使用AS

SELECT * FROM 表 AS 别名;

方法2：直接在表名后使用空格

SELECT * FROM 表 别名;

两种方法的实现效果一致，但注意的是，表的别名只在查询中生效，不会返回客户端主机，因此只能在查询语句中使用！

自联结

相同的表进行联结叫做自联结，需要使用别名实现。

视图（VIEW）

  • 原理：视图，就是将一条查询语句的查询结果作为一个虚表保存下来，内容还是原来的内容（有点像C里的指针），可供二次复用。

  • 创建视图：

CREATE VIEW 视图名 AS SELECT 查询子句;
  • 删除视图：
DROP VIEW 视图名;

  • WITH CHECK OPTION ：在创建视图后，如果进行数据的更新则对创建视图时的查询过滤条件进行检测，如果不符合该过滤条件，则禁止修改。

  • 注意：

  1. 若视图是由两个以上基本表导出的，则此视图不允许更新。
  2. 若视图的字段来自字段表达式或常数，则不允许对此视图执行INSERT和UPDATE操作，但允许执行DELETE操作。
  3. 若视图的字段来自集函数，则此视图不允许更新。
  4. 若视图定义中含有GROUP BY子句，则此视图不允许更新。
  5. 若视图定义中含有DISTINCT短语，则此视图不允许更新。
  6. 一个不允许更新的视图上定义的视图也不允许更新。

索引 （INDEX）

概况

  • 用途：当数据量非常大的时候，那么检索的时间效率就会变得低下，而索引可以帮我们快速定位到要查找的数据。
  • 原理：hash表或者二叉树。
  • 附带：可选数据的单一性。
  • 语句：
  1. 创建索引
CREATE INDEX 索引名 ON 表名(列名);
  1. 查看表内的索引
SHOW INDEX FROM 表名;
  1. 删除索引
DROP INDEX 索引名 ON 表名;

单列索引与多列索引（待补充）

触发器（TRIGGER）

  • 触发器的主要作用在于在对表进行了某一操作之后，会自动执行一些操作，提高效率。
  • 支持的语句有：INSERT、 DELETE、 UPDATE。
  • 可选在语句执行前（BEFORE）或者执行后（AFTER）执行。
  • FOR EACH ROW：对每行必须执行，因为MySQL没有对语句执行，所以必须写。

触发器的创建

创建触发器时，需要给出4个信息：

  1. 整个数据表 唯一的触发器名
  2. 触发器关联的表
  3. 触发器响应什么活动
  4. 触发器在什么时候执行

语句：

CREATE TRIGGER 触发器名 
[AFTER|BEFORE(什么时候触发)] 
[INSERT|DELETE|UPDATE(什么活动触发)]
ON 触发的表名
FOR EACH ROW
触发器需要执行的语句;

触发器的NEW和OLD

INSERTDELETEUPDATE
OLDNULL待删除的数据更新前的数据
NEW待插入的数据NULL更新后的数据
  • 删除活动触发时，可用OLD来访问要进行删除的行，注意该数据为只读，不能进行更新。
  • 同理，插入活动触发时，可用NEW来访问要进行插入的行。
  • 更新活动触发时，两者都可以使用。

BEGIN...END代码块

触发器语句可以使用BEGIN...END代码块将多句语句连接起来，以达到可以执行多句语句的效果。

格式如下：

CREATE TRIGGER 触发器名 
[AFTER|BEFORE(什么时候触发)] 
[INSERT|DELETE|UPDATE(什么活动触发)]
ON 触发的表名
FOR EACH ROW
BEGIN
语句1;
语句2;
...
END

事务

事务可以理解为一个数据的操作提交过程

主要语句有：

  • BEGIN ：开始事务
  • COMMIT：提交事务
  • ROLLBACK：回滚事务
  • SAVEPOINT 回滚点 ：保存回滚点
  • ROLLBACK TO 回滚点：回滚事务到回滚点处

事务一旦提交，就表示对数据库的操作正式生效！！！

Edited on September, 11th, 2023.

Comment