MySQL基础学习
简介
SQL（结构化查询语言）是用于访问和操作数据库中的数据的标准数据库编程序言。
什么是数据库
数据库Databases是按照数据结构来组织、存储和管理数据的仓库，每个数据库都有一个或多个不同的API用于创建、访问、管理、搜索和复制所保存的数据。
RDBMS 术语
- 数据库：数据库是一些关联表的集合。
- 数据表：表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。
- 列：一列（数据元素）包含了相同的数据，例如邮政编码的数据。
- 行：一行（元祖或记录）是一组相关的数据，例如一条用户订阅的数据。
- 冗余：存储两倍数据，冗余可以使系统速度更快。
- 主键：主键是唯一的。一个数据表中只能包含一个主键，可以使用主键来查询数据。
- 外键：用于关联两个表。
- 复合键：将多个列作为一个索引键，一般用于复合索引。
- 索引：使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似书籍的目录。
- 参照完整性：参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。
语法
SQL命令不区分大小写，通常使用大写- 命令以分号
;结束
验证安装
MySQL安装后，运行下面命令判断是否安装成功
|
1 |
mysqladmin --version |
安装成功后，终端则会显示以下命令
|
1 |
mysqladmin Ver 8.23 Distrib 5.0.9-0, for redhat-linux-gnu on i386 |
连接
输入登录命令后，根据提示输入密码
|
1 2 |
mysql -u root -p # 输入密码 |
密码验证成功后，显示下面的提示，则登录成功
|
1 2 3 4 5 |
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 55 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> |
退出
在mysql命令窗口输入exit进行退出
|
1 2 |
mysql> exit Bye |
数据库操作
创建数据库
创建数据库可能会涉及到权限，如果是root用户，则不会造成权限问题。root用户具有最高权限。
注意：DATABASE没有复数S
|
1 2 |
# CREATE DATABASE 数据库名 CREATE DATABASE study; |
查看所有数据库
|
1 |
SHOW DATABASES; |
删除数据库
|
1 |
DROP DATABASE study; |
注意：DATABASE没有复数S
选择数据库
选择指定的数据库，即进入数据库，后面才可对表进行操作。
|
1 2 3 |
# USE 数据库吗 mysql> use study; Database changed # 提示 |
查看选定的数据库
|
1 |
SELECT DATABASES(); |
数据类型
定义数据字段的类型对数据库的优化是非常重要的。MySQL数据类型大致可分为三类：数值、日期/时间和字符串（字符）类型。
数值
支持所有标准SQL数值数据类型。
关键字 INT 是 INTEGER 的同义词，关键字 DEC 是 DECIMAL 的同义词。
| 类型 | 大小 | 范围（有符号） | 范围（无符号） | 用途 |
|---|---|---|---|---|
| TINYINT | 1 字节 | (-128，127) | (0，255) | 小整数值 |
| SMALLINT | 2 字节 | (-32 768，32 767) | (0，65 535) | 大整数值 |
| MEDIUMINT | 3 字节 | (-8 388 608，8 388 607) | (0，16 777 215) | 大整数值 |
| INT或INTEGER | 4 字节 | (-2 147 483 648，2 147 483 647) | (0，4 294 967 295) | 大整数值 |
| BIGINT | 8 字节 | (-9 233 372 036 854 775 808，9 223 372 036 854 775 807) | (0，18 446 744 073 709 551 615) | 极大整数值 |
| FLOAT | 4 字节 | (-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) | 单精度 浮点数值 |
| DOUBLE | 8 字节 | (-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的值 | 小数值 |
日期和时间类型
表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME、和YEAR。
| 类型 | 大小 (字节) | 范围 | 格式 | 用途 |
|---|---|---|---|---|
| DATE | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |
| TIME | 3 | '-838:59:59'/'838:59:59' | HH:MM:SS | 时间值或持续时间 |
| YEAR | 1 | 1901/2155 | YYYY | 年份值 |
| DATETIME | 8 | 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
| TIMESTAMP | 4 | 1970-01-01 00:00:00/2038 结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038-1-19 03:14:07 | YYYYMMDD HHMMSS | 混合日期和时间值，时间戳 |
字符串类型
| 类型 | 大小 | 用途 |
|---|---|---|
| CHAR | 0-255字节 | 定长字符串 |
| VARCHAR | 0-65535 字节 | 变长字符串 |
| TINYBLOB | 0-255字节 | 不超过 255 个字符的二进制字符串 |
| TINYTEXT | 0-255字节 | 短文本字符串 |
| BLOB | 0-65 535字节 | 二进制形式的长文本数据 |
| TEXT | 0-65 535字节 | 长文本数据 |
| MEDIUMBLOB | 0-16 777 215字节 | 二进制形式的中等长度文本数据 |
| MEDIUMTEXT | 0-16 777 215字节 | 中等长度文本数据 |
| LONGBLOB | 0-4 294 967 295字节 | 二进制形式的极大文本数据 |
| LONGTEXT | 0-4 294 967 295字节 | 极大文本数据 |
数据表操作
创建数据表
数据表包含以下信息：
- 表名
- 表字段名
- 定义每个表字段
创建数据库表通用语法
|
1 |
CREATE TABLE table_name(column_name column_type); |
实例：
|
1 2 3 4 5 6 7 |
CREATE TABLE IF NOT EXISTS article( article_id INT NOT NULL AUTO_INCREMENT, article_title VARCHAR(100) NOT NULL, article_author VARCHAR(50) NOT NULL, create_time DATE, PRIMARY KEY (article_id) ); |
实例解析：
- NOT NULL：定义字段为NOT NULL，如果操作数据库时输入该字段为NULL，则会报错。
- AUTO_INCREMENT：定义列为自增属性，一般用于主键，数值会自动加1。
- PRIMARY KEY：定义列为主键。
查看所有数据表
|
1 |
SHOW TABLES; |
删除数据表
谨慎操作，因为删除数据表后，表中的数据都会消失。
通用语法如下：
|
1 |
DROP TABLE table_name; |
查看数据表所有字段
|
1 |
SHOW COLUMNS FROM table_name; |
数据操作
插入数据
|
1 |
INSERT INTO table_name (field1, field2, ...fieldN) VALUES (value1, value2, ...valueN) |
如果数据是字符型，必须使用单引号或双引号，如"value"。
查询数据
|
1 |
SELECT column_name,column_name FROM table_name [WHERE Clause] [OFFSET M] [LIMIT N] |
解析如下：
- 可以使用一个或多个表，表之间使用逗号
,分割 - 可以读取一条或者多条记录
- 可以使用星号
*来查询表中的所有字段数据 - 可以使用WHERE语句来包含任何条件
- 可以通过OFFSET指定查询开始的数据偏移量，默认为0
- 可以使用LIMIT属性来设定返回的记录数
WHERE 子句
|
1 |
SELECT field1, field2,...fieldN FROM table_name1, table_name2... [WHERE condition1 [AND [OR]] condition2... |
- 可以使用一个或多个表，表之间使用逗号
,分割，并使用WHERE语句来设定查询条件。 - 可以在WHERE子句中指定任何条件。
- 可以使用AND或OR指定一个或多个条件。
- WHERE子句也可以运用于SQL的UPDATE和DELETE命令。
- WHERE子句类似于程序语言中的if条件，根据表中的字段值来读取指定的数据。
以下为操作符列表，可用于WHERE子句中。（假定A=10，B=20）
| 操作符 | 描述 | 实例 |
|---|---|---|
| = | 等号，检测两个值是否相等，如果相等返回true | (A = B) 返回false。 |
| <> 或 != | 不等于，检测两个值是否相等，如果不相等返回true | (A != B) 返回 true。 |
| > | 大于号，检测左边的值是否大于右边的值, 如果左边的值大于右边的值返回true | (A > B) 返回false。 |
| < | 小于号，检测左边的值是否小于右边的值, 如果左边的值小于右边的值返回true | (A < B) 返回 true。 |
| >= | 大于等于号，检测左边的值是否大于或等于右边的值, 如果左边的值大于或等于右边的值返回true | (A >= B) 返回false。 |
| <= | 小于等于号，检测左边的值是否小于于或等于右边的值, 如果左边的值小于或等于右边的值返回true | (A <= B) 返回 true。 |
使用主键来作为WHERE子句的条件查询是非常迅速的。
如果给定的条件在表中没有任何匹配的记录，那么查询则不会返回任何数据。
修改数据
|
1 |
UPDATE table_name SET field1=new-value1, filed2=new-value2 [WHERE Clause] |
解析如下：
- 可以同时更新一个或多个字段
- 可以在WHERE子句中指定任何条件
- 若未指定任何条件，则会同时修改表中所有数据
删除数据
|
1 |
DETELE FROM table_name [WHERE Clause] |
- 如果没有指定WHERE子句，那么将会删除表中的全部记录。
- 可以在WHERE子句中指定任何条件
LIKE 子句
|
1 2 |
SELECT field1, field2,...fieldN FROM table_name1, table_name2... WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue' |
解析如下：
- 可以在WHERE子句中指定任何条件
- 可以在WHERE子句中使用LIKE子句
- 可以使用LIKE子句代替等号
= - LIKE通常与%一同使用，类似于一个元字符的搜索。
- 可以使用AND或OR指定一个或多个条件。
- 可以在UPDATE和DELETE命令中使用
WHERE...LIKE子句来指定条件
排序
使用ORDER BY 子句将查询数据排序后再返回数据。
|
1 2 |
SELECT field1, field2,...fieldN FROM table_name1, table_name2... ORDER BY field1, [field2...] [ASC [DESC]] |
解析如下：
- 可以使用任何字段来作为排序的条件，从而返回排序后的查询结果
- 可以设置多个字段来排序
- 可以使用 ASC（升序）或 DESC（降序）关键字来设置排序查询结果。默认按照升序排列。
- 可以添加 WHERE...LIKE 子句来设置条件。
分组
GROUP BY 语句根据一个或多个列对结果集进行分组。
在分组的列上我们可以使用 COUNT、SUM、AVG等函数。
|
1 2 3 |
SELECT column_name, function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name; |
高阶教程
连接的使用
使用JOIN在两个或多个表中查询数据。JOIN按照功能大致分为以下三类：
- INNER JOIN（内连接或等值连接）：获取两个表中字段匹配关系的记录。
- LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应的记录。
- RIGHT JOIN（右连接）：与
LEFT JOIN相反，用于获取右表所有记录，即使左表没有对应的记录。
NULL 值处理
使用查询语句时，当提供的查询字段为NULL时，查询命令可能无法正常工作。为了解决这种情况，MySQL提供了三大运算符：
- IS NULL：当列的值是
NULL时，此运算符返回true。 - IS NOT NULL：当列的值不是
NULL时，此运算符返回true。 - <=>：当比较的两个值为
NULL时返回true。
NULL与任何其他值比较（即使是NULL）都会返回false，即NULL=NULL也会返回false。
正则表达式
事务
事务主要用于操作量大，复杂度高的数据。比如，在人员管理系统中，当删除一个人员时，既需要删除人员的基本信息，也要删除和该人员相关的信息，如信箱、文章等等。这些数据库操作语句就构成一个事物。
- 在MySQL中，只有使用了 innodb 数据库引擎的数据库或表才支持事务。
- 事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行。
- 事务用来管理 insert、update、delete语句。
一般来说，事务必须满足4个条件（ACID）：Atomicity（原子性或不可分割性）、Consistency（一致性）、Isolation（隔离性或独立性）、Durability（持久性）。
- 原子性：一组事务，要么成功，要么撤回，即事务在执行过程中出错会回滚到事务开始前的状态。
- 一致性：一个事务不论是开始前还是结束后，数据库的完整性都没有被破坏。因此写入的数据必须完全符合所有预设规则（资料精确度、串联性以及后续数据库能够自发完成预定工作）。
- 隔离性：数据库允许多个事务并发的同时对其数据进行读写修改等操作，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离可分为：Read uncommitted（读未提交）、Read committed（读提交）、Repeatable read（可重复读）、Serializable（串行化）。
- 持久性：事务在处理结束后对数据做出的修改是永久的，无法丢失。
相关控制语句如下：
- start transaction 或 begin：显示的开始一个事务
- save 保存点名称：做保存点，一个事务可以有多个保存点
- commit 或 commit work：提交事务，并使数据库中进行的所有修改称为永久性的
- rollback 或 rollback work：回滚结束用户的事务，并撤销正在进行的所有未提交的修改
- release savepoint 保存点名称：删除一个事务的保存点，若没有指定保存点，执行该语句操作会报错。
- rollback to 标记点：将事务滚回标记点。
- set transaction：设置事务的隔离级别。隔离级别有：Read uncommitted（读未提交）、Read committed（读提交）、Repeatable read（可重复读）、Serializable（串行化）。
ALTER命令
删除和添加表字段
- DROP子句：删除表字段（如果数据表只剩下一个字段则无法使用DROP进行删除）
- ADD子句：添加表字段（默认将字段添加到数据表字段的末尾，如需指定位置，则需要使用FIRST和AFTER配合）
|
1 2 3 4 5 6 7 8 9 |
# 删除字段 ALTER TABLE table_name DROP column_name; # 添加字段 需要指定类型等 ALTER TABLE table_name ADD column_name INT; # 添加字段 到第一列 ALTER TABLE table_name ADD column_name2 INT FIRST; # 添加字段 column_name3 到字段column_name 后面 ALTER TABLE table_name ADD column_name3 INT AFTER column_name; |
修改字段类型及名称
- MODIFY子句：修改字段类型（可以加上默认值设置）
- CHANGE子句：修改字段名称（需要紧跟修改的字段名称和类型）
|
1 2 3 4 |
# 修改字段类型 ALTER TABLE table_name MODIFY column_name column_type; # 修改字段名称 ALTER TABLE talbe_name CHANGE column_name column_name|new_column_name column_type; |
修改字段默认值
- SET DEFAULT 类型：设置默认值
- DROP DEFAULT：删除默认值
|
1 2 3 4 |
# 设置column_type默认值 ALTER TABLE table_name ALTER column_name SET DEFAULT column_type; # 删除column_type默认值 ALTER TABLE table_name ALTER column_name DROP DEFAULT; |
修改表名
使用RENAME子句来修改表名
|
1 |
ALTER TABLE table_name RENAME TO new_table_name; |
索引