文章

MySQL

MySQL数据库基础

发表于 更新于
『印象·日出』克劳德·莫奈 1872
『印象·日出』克劳德·莫奈 1872
作者 Diexie
18 分钟阅读

参考资料

菜鸟教程mysql

菜鸟教程sql

MySQL官方文档

MySQL速查表

菜鸟速查表

CS186

基本概念

DBMS（Database Management System）：数据库管理系统，是一种操纵和管理数据库的大型软件系统。

SQL（Structured Query Language）：结构化查询语言，是一种用来访问和操作数据库的标准语言。

MySQL：是一个关系型数据库管理系统，由瑞典MySQL AB公司开发，目前属于Oracle公司。

数据库基础

数据库组成

  • 数据库：存储数据的仓库
  • 数据表：数据的集合
  • 数据行：数据表中的一条记录
  • 数据列：数据表中的一个字段
  • 索引：提高查询效率

层级关系:

1
2
3
4
5
6
7
8

|
|--- 数据库
|    |
|    |--- 数据表
|         |
|         |--- 数据行
|              |
|              |--- 数据列

数据类型

数值

类型大小范围（有符号）范围（无符号）用途
TINYINT1 Bytes(-128，127)(0，255)小整数值
SMALLINT2 Bytes(-32 768，32 767)(0，65 535)大整数值
MEDIUMINT3 Bytes(-8 388 608，8 388 607)(0，16 777 215)大整数值
INT或INTEGER4 Bytes(-2 147 483 648，2 147 483 647)(0，4 294 967 295)大整数值
BIGINT8 Bytes(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)(0，18 446 744 073 709 551 615)极大整数值
FLOAT4 Bytes(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)0，(1.175 494 351 E-38，3.402 823 466 E+38)单精度浮点数值
DOUBLE8 Bytes(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)双精度浮点数值
DECIMAL对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2依赖于M和D的值依赖于M和D的值小数值

字符串

类型大小用途
CHAR0-255 bytes定长字符串
VARCHAR0-65535 bytes变长字符串
TINYBLOB0-255 bytes不超过 255 个字符的二进制字符串
TINYTEXT0-255 bytes短文本字符串
BLOB0-65 535 bytes二进制形式的长文本数据
TEXT0-65 535 bytes长文本数据
MEDIUMBLOB0-16 777 215 bytes二进制形式的中等长度文本数据
MEDIUMTEXT0-16 777 215 bytes中等长度文本数据
LONGBLOB0-4 294 967 295 bytes二进制形式的极大文本数据
LONGTEXT0-4 294 967 295 bytes极大文本数据

注意:

  • 需要使用char(n) varchar(n) 来定义字符串长度。
  • CHAR是定长的，VARCHAR是变长的。
  • 字符串需要使用引号括起来。

日期和时间

类型大小(Bytes)范围格式用途
DATE31000-01-01/9999-12-31YYYY-MM-DD日期值
TIME3‘-838:59:59’/’838:59:59’HH:MM:SS时间值或持续时间
YEAR11901/2155YYYY年份值
DATETIME8‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’YYYY-MM-DD hh:mm:ss混合日期和时间值
TIMESTAMP4‘1970-01-01 00:00:01’ UTC 到 ‘2038-01-19 03:14:07’ UTCYYYY-MM-DD hh:mm:ss混合日期和时间值，时间戳

Enum和Set(枚举和集合)

  • ENUM: 枚举类型，用于存储单一值，可以选择一个预定义的集合。
  • SET: 集合类型，用于存储多个值，可以选择多个预定义的集合。

空间数据类型（Spatial Data Types）

GEOMETRY, POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, GEOMETRYCOLLECTION: 用于存储空间数据（地理信息、几何图形等）。

SQL语句

注意点 SQL语句不区分大小写，但是建议关键字大写，表名、字段名小写。 SQL语句以分号;结尾! SQL语句可以分行书写。 SQL注释：单行注释--，多行注释/* */。

SQL语句可以使用空格和缩进增加可读性。

数据库操作

创建数据库

1
2
3

CREATE DATABASE database_name;
-- 为避免数据库名已存在，可以使用IF NOT EXISTS
CREATE DATABASE IF NOT EXISTS database_name;

查看现有数据库

1

SHOW DATABASES; -- 注意是复数的DATABASES

选择(使用)数据库

1

USE database_name;

删除数据库

1
2
3
4
5

-- 直接删除
DROP DATABASE database_name;

-- 如果存在则删除
DROP DATABASE IF EXISTS database_name;

数据表操作

创建数据表

1
2
3
4
5
6

CREATE TABLE table_name(
--  列名    类型,
    column_name datatype,
    column_name2 datatype,
    ...
);

添加约束

1
2
3
4
5
6
7

CREATE TABLE table_name(
    column_name datatype PRIMARY KEY,
    column_name2 datatype NOT NULL,
    column3 datatype DEFAULT 0,
    column4 datatype UNIQUE,
    ...
);
  • PRIMARY KEY: 主键，NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
    1
2
3
4
5
6
7
8
9

    CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (P_Id, LastName) -- P_Id 和 LastName 组成主键
)

  • FOREIGN KEY: 外键，保证一个表中的数据匹配另一个表中的值的参照完整性。

    默认情况下，外键指向的是另一个表的主键。

    1
2
3
4
5
6
7
8
9

    CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id) -- P_Id 是 Persons 表中的外键
FOREIGN KEY (P_Id) REFERENCES Persons_2 -- P_Id 是 Persons_2 表主键的外键
)
  • AUTO_INCREMENT: 自动增长
  • NOT NULL: 非空

  • DEFAULT: 默认值，为列设置默认值。

    1
2
3
4

    CREATE TABLE table_name(
    column_name datatype DEFAULT default_value,
    ...
);
  • UNIQUE: 唯一

  • CHECK: 检查约束，保证列中的值符合指定的条件。

    1
2
3
4
5
6
7
8
9
10

    -- 创建检查约束
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CHECK (P_Id>0 AND City='Sandnes')
)

    1
2
3
4
5
6
7

    -- ALTER TABLE 时加检查约束
ALTER TABLE table_name
ADD CHECK (condition);

-- 添加命名的检查约束
ALTER TABLE table_name
ADD CONSTRAINT check_name CHECK (condition);

    1
2
3

    -- 撤销检查约束
ALTER TABLE table_name
DROP CHECK check_name;

  • INDEX: 索引，用于更快速的查询数据。

    1
2
3
4

    CREATE TABLE table_name(
    column_name datatype INDEX,
    ...
);

查看数据表

1

SHOW TABLES;

删除数据表

1
2
3
4
5

-- 直接删除
DROP TABLE table_name;

-- 如果存在则删除
DROP TABLE IF EXISTS table_name;

删除表中所有数据，但保留表的结构

1

TRUNCATE TABLE table_name;

导入数据表

1
2

-- 导入数据表
SOURCE file_path;

创建表索引

CREATE INDEX 语句用于在表中创建索引。

1
2
3

-- 在表上创建一个简单的索引。允许使用重复的值：
CREATE INDEX index_name
ON table_name (column1, column2, ...);

1
2
3

-- 在表上创建一个唯一的索引。不允许使用重复的值：
CREATE UNIQUE INDEX index_name
ON table_name (column1, column2, ...);

修改表

ALTER TABLE 用于添加、删除或修改表中的列。

1
2
3
4
5
6
7
8
9
10
11

-- 添加列
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;

数据操作

向表中插入数据

1
2

INSERT INTO table_name (column_name, column_name2, column3, ...)
VALUES (value1, value2, value3, ...);

注意: 插入数据时，如果省略列名，则必须为每个值提供一个值。 上述为一个语句。

查询表中的数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

-- 查询所有列的所有行
SELECT * FROM table_name;

-- 查询指定列的所有行
SELECT column_name, column_name2 FROM table_name;

-- 满足条件的行
SELECT * FROM table_name WHERE condition;

-- 按照某列降序排列
SELECT * FROM table_name ORDER BY column DESC;

-- 按照某列升序排列
SELECT * FROM table_name ORDER BY column ASC;

-- 查询前n行
SELECT * FROM table_name LIMIT n;

完整的查询语句:

1
2
3
4
5
6
7

SELECT [DISTINCT] <column expression list>
FROM <single table>
[WHERE <predicate>]
[GROUP BY <column list>
[HAVING <predicate>]  ]
[ORDER BY<columnlist>]
[LIMIT <integer>];

实现流程:

img

SELECT DISTINCT 语句用于返回唯一不同的值。

1
2

SELECT DISTINCT column_name
FROM table_name;
关于UNION:

MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合，并去除重复的行。

UNION 操作符必须由两个或多个 SELECT 语句组成，每个 SELECT 语句的列数和对应位置的数据类型必须相同。

1
2
3

SELECT column_name, column_name2 FROM table1
UNION
SELECT column_name, column_name2 FROM table2;
关于INTERSECT:

MySQL 不支持 INTERSECT 操作符。

关于EXCEPT:

MySQL 不支持 EXCEPT 操作符。

关于WHERE条件:

可以使用条件运算符: =, <>, >, <, >=, <=，也可以使用逻辑运算符: AND, OR, NOT：

注意: 等于号为=，不是==。

1

SELECT * FROM table_name WHERE column_name = value1 AND column_name2 = value2;
关于LIKE:
1
2
3
4
5
6
7
8
9
10

-- 使用通配符查询
SELECT * FROM table_name WHERE column_name LIKE 'a%'; -- 查询以a开头的数据

SELECT * FROM table_name WHERE column_name LIKE '%a'; -- 查询以a结尾的数据

SELECT * FROM table_name WHERE column_name LIKE '%or%'; -- 查询包含or的数据

SELECT * FROM table_name WHERE column_name LIKE '_r%'; -- 查询第二个字符为r的数据

SELECT * FROM table_name WHERE column_name LIKE 'a_%_%'; -- 查询以a开头且长度至少为3的数据

SQL同时支持正则表达式，使用RLIKE或REGEXP关键字。

1

SELECT * FROM table_name WHERE column_name RLIKE 'a.*';
关于ORDER BY:

使用 ORDER BY 子句对结果集进行复合的降序/升序排序。

1
2
3
4
5
6

-- 按照列1升序，列2降序
SELECT * FROM table_name ORDER BY column_name, column_name2 DESC;
-- 按照列1升序，列2降序
SELECT * FROM table_name ORDER BY column_name ASC, column_name2 DESC;
-- 按照列1降序，列2升序
SELECT * FROM table_name ORDER BY column_name DESC, column_name2;
关于LIMIT:

LIMIT 子句用于规定要返回的记录的数目。

1
2

-- 查询前n行
SELECT * FROM table_name LIMIT n;
关于BETWEEN:

BETWEEN 操作符用于选取介于两个值之间的数据范围。

1
2
3

-- 查询列1在value1和value2之间的数据
SELECT * FROM table_name
WHERE column_name BETWEEN value1 AND value2;
关于IN:

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

1
2
3

-- 查找列1的值为value1或value2或value3的数据
SELECT * FROM table_name
WHERE column_name IN (value1, value2, value3);
关于SELECT TOP:

按照指定的列对结果集进行排序，并返回指定数量/百分比的行。

1
2
3
4
5

-- 查询前n行
SELECT TOP n * FROM table_name;

-- 查询前n%的行
SELECT TOP n PERCENT * FROM table_name;
关于AS别名

为列或表取别名，可以使查询结果更易读。

列别名:

1
2
3
4
5
6

SELECT column_name AS alias_name
FROM table_name;

-- 同时取多个别名
SELECT column_name AS alias_name, column_name2 AS alias_name2
FROM table_name;

表别名:

1
2

SELECT column_name(s)
FROM table_name AS alias_name;

使用场景:

1
2
3

SELECT column_name AS alias_name
FROM table_name AS alias_name
WHERE alias_name.column_name = value;

更新表中的数据

1
2
3

UPDATE table_name
SET column_name = value1, column_name2 = value2
WHERE condition;

删除满足条件的行

1
2

DELETE FROM table_name 
WHERE condition;

连接表

SQL JOIN 用于把来自两个或多个表的行结合起来, 生成一个新的临时表。

通用语法:

1
2
3
4
5
6

SELEcT <column expression List>
FROM tabLe_name
[INNER | NATURAL
| {LEFT | RIGHT | FULL } OUTER] JOIN tabLe_name
ON <qualification_List>
WHERE

img

  • INNER JOIN：如果表中有至少一个匹配，则返回行(默认的JOIN)

    1
2
3
4

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

  • LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行

  • RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行

  • FULL JOIN：只要其中一个表中存在匹配，则返回行

复制表

SELECT INTO 语句从一个表中选取数据，然后把数据插入另一个表中。

1
2
3
4

-- 复制表结构
SELECT * INTO new_table_name
FROM old_table_name;
WHERE condition;

INSERT INTO 语句从一个表中插入数据到另一个已存在的表中。

1
2
3
4

-- 复制表数据
INSERT INTO new_table_name
SELECT * FROM old_table_name
WHERE condition;

两种用法的区别在于是否已经存在新表。select into from 要求目标表不存在，因为在插入时会自动创建；insert into select from 要求目标表存在。

创建视图

视图是一种虚拟的表，是一个 SELECT 语句的结果集。

1
2
3
4
5
6
7
8

-- 创建视图
CREATE VIEW view_name AS
SELECT column1, column2
FROM table_name
WHERE condition;

-- 删除视图
DROP VIEW view_name;

内置函数

  • AVG()：返回平均值
    1
2

    SELECT AVG(column_name)
FROM table_name;
  • COUNT()：返回行数
    1
2

    SELECT COUNT(column_name)
FROM table_name;
  • FIRST()：返回第一个记录的值
    1
2

    SELECT FIRST(column_name)
FROM table_name;
  • LAST()：返回最后一个记录的值
    1
2

    SELECT LAST(column_name)
FROM table_name;
  • MAX()：返回最大值
    1
2

    SELECT MAX(column_name)
FROM table_name;
  • MIN()：返回最小值
    1
2

    SELECT MIN(column_name)
FROM table_name;
  • SUM()：返回总和
    1
2

    SELECT SUM(column_name)
FROM table_name;
  • GROUP BY：分组
    1
2
3

    SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name;
  • HAVING：分组后的条件
    1
2
3
4

    SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name
HAVING COUNT(*) > 10;
  • UCASE()：将某列的值转换为大写
    1
2

    SELECT UCASE(column_name)
FROM table_name;
  • LCASE()：将某列的值转换为小写
    1
2

    SELECT LCASE(column_name)
FROM table_name;
  • MID()：从某个文本字段中提取字符
    1
2

    SELECT MID(column_name, start, length)
FROM table_name;
  • LEN()：返回某列的长度
    1
2

    SELECT LEN(column_name)
FROM table_name;
  • ROUND()：对数值进行四舍五入
    1
2

    SELECT ROUND(column_name, decimals)
FROM table_name;
  • NOW()：返回当前的日期和时间
    1
2

    SELECT NOW()
FROM table_name;
  • FORMAT()：格式化数字
    1
2

    SELECT FORMAT(column_name, decimals)
FROM table_name;
软件工程, 数据库
模板
本文由作者按照 CC BY 4.0 进行授权