MySQL 学习笔记

发表于 分类于 Waline： 本文字数： 5.4k 阅读时长 ≈ 5 分钟

MySQL 一些不熟的语句

DISTINCT

DISTINCT 关键词用于返回唯一不同的值。

1
SELECT DISTINCT column_name,column_name FROM table_name;

UPDATE

UPDATE 语句用于更新表中已存在的记录。

1
UPDATE table_name SET column1=value1,column2=value2,... WHERE some_column=some_value;

注意 : UPDATE 语句中的 WHERE 子句！WHERE 子句规定哪条记录或者哪些记录需要更新。如果您省略了 WHERE 子句，所有的记录都将被更新！

DELETE

DELETE 语句用于删除表中的行(记录)。

1
DELETE FROM table_name WHERE some_column=some_value;

注意 : SQL DELETE 语句中的 WHERE 子句！WHERE 子句规定哪条记录或者哪些记录需要删除。如果您省略了 WHERE 子句，所有的记录都将被删除！

REGEXP

示例：正则表达式 B[an]*s 匹配下述字符串中的任何一个：Bananas，Baaaaas，Bs，以及以 B 开始，以 s 结束、并在其中包含任意数目 a 或 n 字符的任何其他字符串。

以下是可用于随 REGEXP 操作符的表的模式。

应用示例，查找用户表中 Email 格式错误的用户记录：

1
SELECT * FROM users WHERE email NOT REGEXP '^[A-Z0-9._%-]+@[A-Z0-9.-]+.[A-Z]{2,4}$'
  • ^ 匹配字符串的开始位置，如 ^a 表示以字母 a 开头的字符串。
  • $匹配字符串的结束位置，如 X$ 表示以字母 X 结尾的字符串。
  • . 这个字符就是英文下的点，它匹配任何一个字符，包括回车、换行等。
  • *星号匹配 0 个或多个字符，在它之前必须有内容。如：mysql> SELECT 'xxxyyy' REGEXP 'x*';
  • +加号匹配 1 个或多个字符，在它之前也必须有内容。加号跟星号的用法类似，只是星号允许出现 0 次，加号则必须至少出现一次。
  • ?问号匹配0 次或 1 次。

示例：

1
2
3
4
5
6
7
8
9
10
11
//查询找到所有的名字以 st 开头：
mysql> SELECT name FROM person_tbl WHERE name REGEXP '^st';

//查询找到所有的名字以 ok 结尾：
mysql> SELECT name FROM person_tbl WHERE name REGEXP 'ok$';

//查询找到所有的名字包含 mar 的字符串：
mysql> SELECT name FROM person_tbl WHERE name REGEXP 'mar';

//查询找到所有名称以元音开始和 ok 结束的：
mysql> SELECT name FROM person_tbl WHERE name REGEXP '^[aeiou]|ok$';

  • {m,n}这是一个更全面的方法，它可以实现前面好几种保留字的功能。

    • a* 可以写成 a{0,}。
    • a+ 可以写成 a{1,}。
    • a? 可以写成 a{0,1}。

  • {i}表示字符只能出现 i 次；

  • {i,}表示字符可以出现 i 次或 i 次以上；

  • {i,j}表示字符只能出现 i 次以上，j 次以下，包括 i 次和 j 次。其中的整型参数必须大于等于 0，小于等于 RE_DUP_MAX（默认是 255）。 如果有两个参数，第二个必须大于等于第一个。

  • [a-dX]匹配 “a”、“b”、“c”、“d” 或 “X”。

  • [^a-dX]匹配除 “a”、“b”、“c”、“d”、“X” 以外的任何字符。

  • [、]必须成对使用：

IN 操作符

IN 操作符允许您在 WHERE 子句中规定多个值。

1
SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,...);

BETWEEN 操作符

BETWEEN 操作符选取介于两个值之间的数据范围内的值。这些值可以是数值、文本或者日期。

1
SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;

AS 操作符

通过使用 AS 操作符，可以为表名称或列名称指定别名。创建别名是为了让列名称的可读性更强。AS 在给表取别名时可省略。

1
2
3
4
5
# 列的 SQL 别名语法
SELECT column_name AS alias_name FROM table_name;

# 表的 SQL 别名语法
SELECT column_name(s) FROM table_name AS alias_name;

JOIN

JOIN 子句用于把来自两个或多个表的行结合起来，基于这些表之间的共同字段。

  • INNER JOIN：如果表中有至少一个匹配，则返回行
  • LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行
  • RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行
  • FULL JOIN：只要其中一个表中存在匹配，则返回行

INNER JOIN

INNER JOIN 要求两表中都要匹配才返回行。

1
2
# INNER 可省
SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name=table2.column_name;

LEFT JOIN

LEFT JOIN 关键字从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL。

1
SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name=table2.column_name;

注意：在某些数据库中，LEFT JOIN 称为 LEFT OUTER JOIN。

RIGHT JOIN

RIGHT JOIN 关键字从右表（table2）返回所有的行，即使左表（table1）中没有匹配。如果左表中没有匹配，则结果为 NULL。

1
SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON table1.column_name=table2.column_name;

注意：在某些数据库中，RIGHT JOIN 称为 RIGHT OUTER JOIN。

FULL OUTER JOIN

FULL OUTER JOIN 关键字只要左表（table1）和右表（table2）其中一个表中存在匹配，则返回行。

FULL OUTER JOIN 关键字结合了 LEFT JOIN 和 RIGHT JOIN 的结果。

1
SELECT column_name(s) FROM table1 FULL OUTER JOIN table2 ON table1.column_name=table2.column_name;

注意：MySQL中不支持 FULL OUTER JOIN

UNION 操作符

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

请注意，UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。但是名称不用对应相同，结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。

1
SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2;

注意：UNION 操作符会默认去重。如果允许重复的值，请使用 UNION ALL。

INSERT INTO SELECT

INSERT INTO SELECT 语句从一个表复制数据，然后把数据插入到一个已存在的表中。目标表中任何已存在的行都不会受影响。

1
INSERT INTO  (准备好的表) SELECT *（或者取用自己想要的结构）FROM 表名 WHERE 各种条件
1
2
3
4
5
//我们可以从一个表中复制所有的列插入到另一个已存在的表中：
INSERT INTO table2 SELECT * FROM table1;

//或者我们可以只复制希望的列插入到另一个已存在的表中：
INSERT INTO table2 (column_name(s)) SELECT column_name(s) FROM table1;
1
2
3
4
5
6
7
8
9
10
11
12
13
// 复制表结构及其数据
CREATE TABLE table_name_new AS SELECT * FROM table_name_old

// 只复制表结构
CREATE TABLE table_name_new AS SELECT * FROM table_name_old WHERE 1=2;
// 或者
CREATE TABLE table_name_new LIKE table_name_old

// 只复制表数据，如果两个表结构一样：
INSERT INTO table_name_new SELECT * FROM table_name_old

// 只复制表数据，如果两个表结构不一样：
INSERT INTO table_name_new(column1,column2...) SELECT column1,column2... FROM table_name_old

SELECT INTO FROM

SELECT INTO FROM将查询出来的数据整理到一张新表中保存，表结构与查询结构一致。

1
SELECT *（查询出来的结果） INTO newtable（新的表名）FROM WHERE （后续条件）

SQL 约束（Constraints）

SQL 约束用于规定表中的数据规则。如果存在违反约束的数据行为，行为会被约束终止。约束可以在创建表时规定（CREATE TABLE），或者在表创建之后规定（ALTER TABLE）。

1
2
3
4
5
6
7
CREATE TABLE table_name
(
column_name1 data_type(size) constraint_name,
column_name2 data_type(size) constraint_name,
column_name3 data_type(size) constraint_name,
....
);

在 SQL 中，我们有如下约束：

  • NOT NULL指示某列不能存储 NULL 值。
  • UNIQUE保证某列元素值必须唯一。
  • PRIMARY KEY主键标识(NOT NULL 和 UNIQUE 的结合)。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
  • FOREIGN KEY保证一个表中的数据匹配另一个表中的值的参照完整性。
  • CHECK保证列中的值符合指定的条件。
  • DEFAULT规定没有给列赋值时的默认值。

ALTER TABLE

ALTER TABLE 语句用于在已有的表中添加、删除或修改列。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
// 添加列
ALTER TABLE table_name ADD column_name datatype

// 删除列
ALTER TABLE table_name DROP COLUMN column_name

// 改变表中列的数据类型
ALTER TABLE table_name MODIFY COLUMN column_name datatype

// 修改表列属性
ALTER TABLE 表名 CHANGE 原列名 新列名  类型； --修改表的列属性名
ALTER TABLE 表名 MODIFY 列名 类型 ；  --修改表的类类型
ALTER TABLE 表名 DROP 列名；  --删除表的某一列
ALTER TABLE 表名 ADD  列名 类型；--添加某一列
ALTER TABLE 表名 RENAME 新表名； --修改表名

AUTO INCREMENT

AUTO-INCREMENT 会在新记录插入表中时生成一个唯一的数字。比如自动地创建主键字段的值。

VIEW

视图是基于 SQL 语句的结果集的可视化的表。视图包含行和列，就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。

您可以向视图添加 SQL 函数、WHERE 以及 JOIN 语句，也可以呈现数据，就像这些数据来自于某个单一的表一样。

1
2
3
4
5
// 创建视图
CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition;

// 删除视图
DROP VIEW view_name;

注意：视图总是显示最新的数据！每当用户查询视图时，数据库引擎通过使用视图的 SQL 语句重建数据。

mysql 复杂操作合集

重置自增ID

也就是说如何重排 auto_increment

两种方法：

  1. 清空表时使用truncate命令，而不用delete命令
1
TRUNCATE TABLE table_name ;

使用truncate命令的好处：

1）、速度快
2）、可以对自增 ID 进行重排，使自增 ID 仍从1开始计算

  1. 清空表数据后，使用 alter 修改表
1
ALTER TABLE table_name AUTO_INCREMENT = 1;

时间戳设置

  • DEFAULT CURRENT_TIMESTAMP：表示当插入数据的时候，该字段默认值为当前时间
  • ON UPDATE CURRENT_TIMESTAMP：在数据库数据有更新的时，UPDATE_TIME 的时间会自动更新。