本文持续更新Mysql相关知识,主要涉及Mysql基本语法,以及Mysql在Spring Boot中的使用。
相关资源:
1、Mysql官网:https://www.mysql.com.
2、菜鸟教程|Mysql:https://www.runoob.com/mysql/mysql-tutorial.html.
3、
Mysql
MySQL 是最流行的关系型数据库管理系统,在 WEB 应用方面 MySQL 是最好的 RDBMS(Relational Database Management System:关系数据库管理系统)应用软件之一。
RDBMS
关键字 | 概念 |
---|---|
数据库 | 数据库是一些关联表的集合,可包含多种(有关联或无关联)表格 |
数据表 | 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格 |
列 | 一列(数据元素) 包含了相同类型的数据, 例如用户性别 |
行 | 一行(元组,或记录)是一组相关的数据,例如一条访问记录 |
冗余 | 存储两倍数据,冗余降低了性能,但提高了数据的安全性 |
主键 | 一个表只能有一个主键。一般使用主键来查询数据 |
外键 | 外键用于关联两个表,即两个表的外键内容相同(表头可能不同) |
复合键 | 复合键(组合键)将多个列作为一个索引键,一般用于复合索引 |
索引 | 使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录 |
参照完整性 | 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件,目的是保证数据的一致性 |
表头(header) | 每一列的名称 |
列(col) | 具有相同数据类型的数据的集合 |
行(row) | 每一行用来描述某条记录的具体信息 |
值(value) | 行的具体信息, 每个值必须与该列的数据类型相同 |
键(key) | 键的值在当前列中具有唯一性 |
安装
网上有大把教程,请自行搜索。
登陆方法
登陆语法:“mysql -h 主机名 -u 用户名 -p”;其中:
-h : 指定客户端所要登录的 MySQL 主机名, 登录本机(localhost 或 127.0.0.1)该参数可以省略;
-u : 登录的用户名;
-p : 告诉服务器将会使用一个密码来登录, 如果所要登录的用户名密码为空, 可以忽略此选项。
例如:在控制台或终端输入命令:“mysql -u root -p”,然后输入密码(默认root),回车登陆。
控制台或终端显示“mysql>”,表示登陆成功。
Mysql管理
Mysql用户等信息存放在数据库“mysql”中。在登陆后,使用命令“use mysql;”使用数据库。
1.添加用户,并授权。
比如:添加用户名为guest,密码为guest,并授权用户可进行 SELECT, INSERT 和 UPDATE操作权限:
mysql> INSERT INTO user
(host, user, password,
select_priv, insert_priv, update_priv)
VALUES ('localhost', 'guest',
PASSWORD('guest'), 'Y', 'Y', 'Y');
mysql> FLUSH PRIVILEGES;
注意:
- 使用MySQL提供的 PASSWORD() 函数来对密码进行加密。
- 在 MySQL5.7 中 user 表的 password 已换成了authentication_string。
- PASSWORD() 加密函数已经在 8.0.11 中移除了,可以使用 MD5() 函数代替。
- 需要执行 FLUSH PRIVILEGES 语句。 这个命令执行后会重新载入授权表。
语法
-
显示数据库列表
mysql> show databases;
-
进入数据库
mysql> use 数据库名称;
-
显示数据表列表
mysql> show tables;
-
显示数据表的属性,属性类型,主键信息
mysql> show columns from 数据表名;
-
显示数据表的详细索引信息,包括PRIMARY KEY(主键)
mysql> show index from 数据表名;
-
数据库管理系统的性能及统计信息
mysql> show table status from 数据库名;
-
创建数据库
mysql> create database 数据库名;
-
删除数据库
mysql> drop database 数据库名;
-
创建数据表
mysql> create table 数据库表 (列名1 数据类型 NOT NULL 或 NULL PRIMARY KEY, ...);
例如:
mysql>DROP TABLE IF EXISTS `t_dept`;
mysql>CREATE TABLE `t_dept` (
`DEPT_ID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '部门ID',
`PARENT_ID` bigint(20) NOT NULL COMMENT '上级部门ID',
`DEPT_NAME` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '部门名称',
`ORDER_NUM` bigint(20) NULL DEFAULT NULL COMMENT '排序',
`CREATE_TIME` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
`MODIFY_TIME` datetime(0) NULL DEFAULT NULL COMMENT '修改时间',
PRIMARY KEY (`DEPT_ID`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '部门表' ROW_FORMAT = Dynamic;
其中,涉及到如下数据类型:
类型 | 大小 | 范围(有符号) | 范围(无符号) | 用途 |
---|---|---|---|---|
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,223,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的值 | 小数值 |
时间日期类型:
类型 | 大小 | 范围 | 格式 | 用途 |
---|---|---|---|---|
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)ENGINE = InnoDB 表示使用InnoDB引擎
2)AUTO_INCREMENT = 11 表示从DEPT_ID=11后开始自增1
3)CHARACTER SET = utf8 表示设置字符格式为utf-8
4)COLLATE = utf8_general_ci 表示排序规则为utf8_general_ci,其中:a)utf8_unicode_ci 和 utf8_general_ci 对中、英文来说没有实质的差别;b)utf8_general_ci 校对速度快,但准确度稍差;c)utf8_unicode_ci 准确度高,但校对速度稍慢;
5)ROW_FORMAT = Dynamic 表示该表每一行是动态长度的(若一张表里面存在varchar、text以及其变形、blob以及其变形的字段的话,那么张这个表其实也叫动态表)
-
删除数据表
mysql> drop table 数据表名;
-
插入数据
mysql> insert into 数据表名 ( field1, field2, ... fieldN ) VALUES ( value1, value2, ... valueN );
-
查询数据
mysql> select 列名1, 列名2, ... , 列名N from 数据表名 [where 查询条件] [limit N] [offset M]
1)查询语句中你可以使用一个或者多个表,表之间使用逗号(,)分割,并使用 where 语句来设定查询条件;
2)select 命令可以读取一条或者多条记录;
3)可以使用星号(*)来代替其他字段,select 语句会返回表的所有字段数据;
4)可以使用 where 语句来包含任何条件;
5)可以使用 limit 属性来设定返回的记录数;
6)可以通过 offset 指定 select 语句开始查询的数据偏移量。默认情况下偏移量为0。 -
where 筛选
mysql> select field1, field2,...fieldN from 数据表名1, 数据表名2 ... [where 查询条件1 [and [or] 查询条件2 .....
1)查询语句中你可以使用一个或者多个表,表之间使用逗号, 分割,并使用 where 语句来设定查询条件;
2)你可以在 where 子句中指定任何条件;
3)你可以使用 and 或者 or 指定一个或多个条件。
4)where 子句也可以运用于 SQL 的 delete 或者 update 命令;
5)where 子句类似于程序语言中的 if 条件,根据 MySQL 表中的字段值来读取指定的数据;
6)where 子句中的查询条件可以是对字段数字的大小比较、字段内容是否匹配等。 -
update 更新
用于对数据表内容的修改:mysql> update 数据表名 set field1=new-value1, field2=new-value2 [where 条件]
1)可以同时更新一个或多个字段;
2)可以在 where 子句中指定任何条件;
3)可以在一个单独表中同时更新数据。 -
delete 删除
用于删除某条数据或全部数据:mysql> delete from 数据表名1 [where 条件]
1)如果没有指定 where 子句,MySQL 表中的所有记录将被删除;
2)可以在 where 子句中指定任何条件;
3)可以在单个表中一次性删除记录。 -
like 匹配
用于读取数据时,匹配包含某些内容的数据。like 子句中使用百分号 %字符来表示任意字符,类似于UNIX或正则表达式中的星号 *。如果没有使用百分号 %, like 子句与等号 = 的效果是一样的。
mysql> select field1, field2,...fieldN from 数据表名 where field1 like 条件[[and [or]] filed2 = 'somevalue'
1)可以在 where 子句中使用 like 子句;
2)可以使用 like 子句代替等号 =;
3)like 通常与 % 一同使用,类似于一个元字符的搜索;
4)可以使用 and 或者 or 指定一个或多个条件;
5)可以在 delete 或 update 命令中使用 where … like 子句来指定条件。 -
union 连接查询结果
用于连接两个以上的 select 语句的结果组合到一个结果集合中。多个 select 语句会删除重复的数据。mysql> select field1, field2, ... fieldN from 数据表名 [where 查询条件] union [all | distinct] select field1, field2, ... fieldN from 数据表名 [where 查询条件];
1)distinct: 可选,删除结果集中重复的数据。默认情况下 union 操作符已经删除了重复数据,所以 distinct 修饰符对结果没啥影响。
2)all: 可选,返回所有结果集,包含重复数据。 -
order by 排序
mysql> select field1, field2,...fieldN from 数据表名1, 数据表名2 ... order by field1 [asc [desc][默认 asc]], [field2...] [asc [desc][默认 asc]]
1)可以设定多个字段来排序;
2)可以使用 asc 或 desc 关键字来设置查询结果是按升序或降序排列。 默认情况下,它是按升序排列。 -
group by 分组
对提取的单个或多个数据进行分组,在分组的列上我们可以使用 count, sum, avg,等函数。mysql> select field, function(field) from 数据表名 where field operator value group by field [with rollup];
1)with rollup表示分组结果按升序排列。
例如:
mysql> select age, count(*) from user where age > 18 group by age;
- inner join 内连接
内连接,简单理解就是取两张表相同的列相连,取交集:
mysql> select a.id, a.age, b.grade from tableA a inner join tableBoot b ON a.age = b.age;
- left join 左连接
左连接,和内连接一样,只是左表全部显示,右表只显示和左表有交集的内容:
mysql> select a.id, a.age, b.grade from tableA a left join tableBoot b ON a.age = b.age;
- right join 左连接
右连接,和左连接正好相反:
mysql> select a.id, a.age, b.grade from tableA a right join tableBoot b ON a.age = b.age;
- NULL 比较
有 IS NULL,IS NOT NULL 和 <=>三种:
mysql> select name from user where name is not NULL;
- 正则表达式
模式 | 描述 |
---|---|
^ | 匹配输入字符串的开始位置。如果设置了 RegExp 对象的 Multiline 属性,^ 也匹配 ‘\n’ 或 ‘\r’ 之后的位置。 |
$ | 匹配输入字符串的结束位置。如果设置了RegExp 对象的 Multiline 属性,$ 也匹配 ‘\n’ 或 ‘\r’ 之前的位置。 |
. | 匹配除 “\n” 之外的任何单个字符。要匹配包括 ‘\n’ 在内的任何字符,请使用象 ‘[.\n]’ 的模式。 |
[…] | 字符集合。匹配所包含的任意一个字符。例如, ‘[abc]’ 可以匹配 “plain” 中的 ‘a’。 |
[^…] | 负值字符集合。匹配未包含的任意字符。例如, ‘[^abc]’ 可以匹配 “plain” 中的’p’。 |
p1|p2|p3 | 匹配 p1 或 p2 或 p3。例如,‘z|food’ 能匹配 “z” 或 “food”。’(z|f)ood’ 则匹配 “zood” 或 “food”。 |
* | 匹配前面的子表达式零次或多次。例如,zo* 能匹配 “z” 以及 “zoo”。* 等价于{0,}。 |
+ | 匹配前面的子表达式一次或多次。例如,‘zo+’ 能匹配 “zo” 以及 “zoo”,但不能匹配 “z”。+ 等价于 {1,}。 |
{n} | n 是一个非负整数。匹配确定的 n 次。例如,‘o{2}’ 不能匹配 “Bob” 中的 ‘o’,但是能匹配 “food” 中的两个 o。 |
{n,m} | m 和 n 均为非负整数,其中n <= m。最少匹配 n 次且最多匹配 m 次。 |
mysql> select name from table where name REGEXP '^aa';
mysql> select name from table where name REGEXP 'no$';
mysql> select name from table where name REGEXP 'big';
mysql> select name from table where name REGEXP '^[aeiou]|no$';
-
事务
主要用于处理操作量大,复杂度高的数据任务。1)原子性:一个事务(transaction)中的所有操作,要么全部完成,要么全部不完成,不会结束在中间某个环节。事务在执行过程中发生错误,会被回滚(Rollback)到事务开始前的状态,就像这个事务从来没有执行过一样。
2)一致性:在事务开始之前和事务结束以后,数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则,这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
3)隔离性:数据库允许多个并发事务同时对其数据进行读写和修改的能力,隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别,包括读未提交(Read uncommitted)、读提交(read committed)、可重复读(repeatable read)和串行化(Serializable)。
4)持久性:事务处理结束后,对数据的修改就是永久的,即便系统故障也不会丢失。
事务控制:
1)BEGIN 或 START TRANSACTION 显式地开启一个事务;
2)COMMIT 也可以使用 COMMIT WORK,不过二者是等价的。COMMIT 会提交事务,并使已对数据库进行的所有修改成为永久性的;
3)ROLLBACK 也可以使用 ROLLBACK WORK,不过二者是等价的。回滚会结束用户的事务,并撤销正在进行的所有未提交的修改;
4)SAVEPOINT identifier,SAVEPOINT 允许在事务中创建一个保存点,一个事务中可以有多个 SAVEPOINT;
5)RELEASE SAVEPOINT identifier 删除一个事务的保存点,当没有指定的保存点时,执行该语句会抛出一个异常;
6)ROLLBACK TO identifier 把事务回滚到标记点;
7)SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。
- 修改字段或表名
1.ALTER
注意:FIRST表示字段修改后放在第一列,AFTER xxx表示放在xxx后面。
1)ADD 增加字段mysql> ALTER TABLE tablename ADD phoneNumber varchar(13) AFTER grade;
2)DROP 删除字段
mysql> ALTER TABLE tablename DROP grade;
3)MODIFY 修改字段
mysql> ALTER TABLE tablename MODIFY phoneNumber varchar(13) DEFAULT 11111111111;
4)CHANGE 重命名字段
mysql> ALTER TABLE tablename CHANGE phoneNumber phoneNumber FIRST;
2 RENAME TO 重命名
mysql> ALTER TABLE tablename RENAME TO tablename2;
- INDEX 索引
创建索引时,你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 where 子句的条件)。
实际上,索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录。
如果是CHAR,VARCHAR类型,length可以小于字段实际长度;如果是BLOB和TEXT类型,必须指定 length。
普通索引:
mysql> CREATE INDEX indexName ON mytable(username(length));
mysql> ALTER table tableName ADD INDEX indexName(columnName);
mysql> CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX [indexName] (username(length)));
mysql> DROP INDEX [indexName] ON mytable;
唯一索引:
mysql> CREATE UNIQUE INDEX indexName ON mytable(username(length))
mysql> ALTER table mytable ADD UNIQUE [indexName] (username(length))
mysql> CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, UNIQUE [indexName] (username(length)));
1)ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键,这意味着索引值必须是唯一的,且不能为NULL。
2)ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的(除了NULL外,NULL可能会出现多次)。
3)ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引,索引值可出现多次。
4)ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ,用于全文索引。
- TEMPORARY 临时表
临时表只在当前连接可见,当关闭连接时,Mysql会自动删除表并释放所有空间。
语法:在创建和删除表时,加上关键字TEMPORARY即可。
mysql> CREATE TEMPORARY TABLE SalesSummary (
-> product_name VARCHAR(50) NOT NULL
-> , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00
-> , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00
-> , total_units_sold INT UNSIGNED NOT NULL DEFAULT 0
);
mysql> INSERT INTO SalesSummary
-> (product_name, total_sales, avg_unit_price, total_units_sold)
-> VALUES
-> ('cucumber', 100.25, 90, 2);
mysql> DROP TABLE SalesSummary;
- 复制表
1、使用 SHOW CREATE TABLE 命令获取创建数据表(CREATE TABLE) 语句,该语句包含了原数据表的结构,索引等;
2、复制以下命令显示的SQL语句,修改数据表名,并执行SQL语句,通过以上命令 将完全的复制数据表结构;
3、如果你想复制表的内容,你就可以使用 INSERT INTO … SELECT 语句来实现。
mysql> SHOW CREATE TABLE runoob_tbl \G;
*************************** 1. row ***************************
Table: runoob_tbl
Create Table: CREATE TABLE `runoob_tbl` (
`runoob_id` int(11) NOT NULL auto_increment,
`runoob_title` varchar(100) NOT NULL default '',
`runoob_author` varchar(40) NOT NULL default '',
`submission_date` date default NULL,
PRIMARY KEY (`runoob_id`),
UNIQUE KEY `AUTHOR_INDEX` (`runoob_author`)
) ENGINE=InnoDB
1 row in set (0.00 sec)
mysql> CREATE TABLE `clone_tbl` (
-> `runoob_id` int(11) NOT NULL auto_increment,
-> `runoob_title` varchar(100) NOT NULL default '',
-> `runoob_author` varchar(40) NOT NULL default '',
-> `submission_date` date default NULL,
-> PRIMARY KEY (`runoob_id`),
-> UNIQUE KEY `AUTHOR_INDEX` (`runoob_author`)
-> ) ENGINE=InnoDB;
mysql> INSERT INTO clone_tbl (runoob_id,
-> runoob_title,
-> runoob_author,
-> submission_date)
-> SELECT runoob_id,runoob_title,
-> runoob_author,submission_date
-> FROM runoob_tbl;
Query OK, 3 rows affected (0.07 sec)
- 获取服务器信息
命令 | 作用 |
---|---|
SELECT VERSION( ) | 服务器版本信息 |
SELECT DATABASE( ) | 当前数据库名 (或者返回空) |
SELECT USER( ) | 当前用户名 |
SHOW STATUS | 服务器状态 |
SHOW VARIABLES | 服务器配置变量 |
- AUTO_INCREMENT
表示序列自动增长。可用于设置索引(主键)。
设置自增值:
mysql> CREATE TABLE insect
-> (
-> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
-> PRIMARY KEY (id),
-> name VARCHAR(30) NOT NULL, # type of insect
-> date DATE NOT NULL, # date collected
-> origin VARCHAR(30) NOT NULL # where collected
mysql> ALTER TABLE insect DROP id;
mysql> ALTER TABLE insect
-> ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
-> ADD PRIMARY KEY (id);
索引从200开始:
mysql> CREATE TABLE insect
-> (
-> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
-> PRIMARY KEY (id),
-> name VARCHAR(30) NOT NULL,
-> date DATE NOT NULL,
-> origin VARCHAR(30) NOT NULL
)engine=innodb auto_increment=200 charset=utf8;
mysql> ALTER TABLE t AUTO_INCREMENT = 200;
- 史蒂夫