MySQL数据库学习

Last updated on a day ago

基础应用

配置

启动与停止MySQL服务

在安装MySQL后，可以通过命令行启动服务，只需输入以下命令

1
net start [服务名]

我的服务名为mysql57，所以输入

1
net start mysql57

不知道服务名的可以win+R输入services.msc，在弹出的【服务】管理器中可以找到名字带有MySQL的服务，比如MySQL57,当然，也可以在这里打开服务。

如果启动服务时出现“拒绝访问”，可以用管理员模式打开终端,WIN+R，输出cmd，然后CTRL+SHIFT+ENTER即可。

如果想要停止服务，则输入

1
net stop [服务名]

比如：

1
net stop mysql57

登录

登录的指令需要在MySQL安装目录的bin文件下输入，默认的文件路径为c:\Program Files\MySQL，可以通过cd指令进入

1
cd c:\Program Files\MySQL\MySQL Server 5.7\bin

然后通过mysql指令登录

1
mysql -h [主机名] -u [用户名] -p

默认为

1
mysql -h localhost -u root -p

之后输入密码即可

配置PATCH变量

每次都要进入文件夹运行非常麻烦，不过只要设置了PATH就可以全局使用了。设置方法如下

  1. 右键“我的电脑”-“属性”-“高级系统设置”-“环境变量”’
  2. 在系统变量中找到“patch”
  3. 添加bin文件的路径，比如”c:\Program Files\MySQL\MySQL Server 5.7\bin”

现在就可以全局使用mysql了

更改配置文件

  1. 使用配置向导修改配置

    即mysqlinstaller，使用方法与安装MySQL时类似，软件路径为：C:\Program Files\MySQL\MySQL Installer for Windows

  2. 手动修改配置

    MySQL默认的安装路径是C:\Program Files\MySQL\MySQL Server 5.7,大多数版本包含以下文件夹

    文件夹 内容
    bin 客户端程序和服务端程序
    C:\Program Files (x86)\MySQL\MySQL Server 5.7\Data 数据库和日志文件
    include 包含（头）文件
    lib 库文件
    share 字符集、语言等消息
    examples 示例程序和脚本

    在安装目录下还包含ini类型的文件，如：

    • my.ini：当前应用的配置文件
    • my_日期时间.ini：修改配置后自动生成的以日期时间命名的配置文件

    手工修改配置就是修改my.ini的内容。

创建与维护数据库

数据库特点

SQL有以下特点

  1. 一体化： 集数据定义语言、数据操纵语言、数据控制语言元素为一体。
  2. 使用方式： 交互使用方式和嵌入高级语言中的使用方式
  3. 非过程化语言：只需要提出“干什么”，不需要指出“如何干”，语句的操作用系统自动完成
  4. 人性化：符合人们的思维方式、容易理解和掌握

SQL的分类

在MySQL中，SQL可以分为3类：数据定义语言、数据操纵语言、数据控制语言：

  1. 数据定义语言（Data Definition Language ,DDL）

    DDL是最基础的SQL类型，用来创建数据库和创建、修改、删除数据库中的各种对象，为其他语言的操作提供对象。最常用的DDL语句是：CREATE、DROP、ALTER

  2. 数据操纵语言（Data Manipulation Language ,DML)

    DML用于完成数据查询和数据更新操作，其中数据更新是指对数据进行插入、删除和修改操作。最常用的DML语句是：SELECT、INSERT、UPDATA、DELETE

  3. 数据控制语言（Data Control Language, DCL）

    DCL是用来设置或更改数据库用户或角色权限的语句，主要包括GRANT语句和REVOKE语句。GRANT语句可以将指定的安全对象的权限授予相应的主体，REVOKE语句则删除授予的权限

数据库文件

在MySQL中，每个数据库都对应存放一个与数据库同名的文件夹中。 My circle数据库文件有.frM，.MYD和.NYI中的三种。其中.FRM是描述表结构的文件，.myd的是表的数据文件。.NYI是表数据文件中的索引文件。它们都存放在与数据库同名的文件夹中。

自动创立的数据库

MySQL安装完成之后，将会在data目录下自动创建四个数据库，在连接到数据库后，可以通过SHOW DATABASES;命令查看当前所有数据库

数据库 作用
mysql 描述用户访问权限。
information_schema 保存关于MySQL服务器维护的所有其他数据库的信息，如数据库名、数据库表、表栏的数据类型与访问权限等。
performance_schema 主要用于收集数据库服务器性能参数。
sys 通过视图的形式把information_schema和performance_schema结合起来，查询出更加容易理解的数据存储过程，可以执行一些性能方面的配置，也可以得到一些性能诊断报告内容。

不要随意修改和删除系统自带的数据库

创建数据库

使用CREATE DATABASE创建数据库，他的语法格式如下：

1
CREATE DATABASE database_name;

注意不要忘记分号，database_name是要创建的数据库的名字

打开数据库

使用USE打开数据库，他的语法格式如下：

1
USE database_name;

删除数据库

使用DROP删除数据库，他的语法格式如下：

1
DROP DATABASE database_name;

删除时一定要注意，数据库中存储的所有内容会被一同删除，且无法恢复

数据库存储引擎

存储引擎就是存储数据，为存储的数据建立索引和更新查询数据等技术的实现方法。因为在关系数据库中，数据是以表的形式存储的，所以存储引擎简而言之就是指表的类型。数据库的存储引擎决定了表在计算机中的存储方式。

查看数据库支持的存储引擎可以用SHOW ENGINES语句，将会返回一个表格，其中Support列表示能否使用，YES代表可以使用，NO不行，DEFAULT表示当前默认默认使用的存储引擎。以下是5.7的展示结果：

Engine Support Comment Transactions XA Savepoints
InnoDB DEFAULT 支持事务、行级锁定和外键 是 是 是
MRG_MYISAM 是 一组相同的MyISAM表 否 否 否
MEMORY 是 基于哈希存储在内存中，适用于临时表 否 否 否
BLACKHOLE 是 /dev/null存储引擎（向其中写入的任何内容都会消失） 否 否 否
MyISAM 是 MyISAM存储引擎 否 否 否
CSV 是 CSV存储引擎 否 否 否
ARCHIVE 是 归档存储引擎 否 否 否
PERFORMANCE_SCHEMA 是 性能模式 否 否 否
FEDERATED 否 分布式MySQL存储引擎 NULL NULL NULL

以下是部分引擎特点：

  • InnoDB：支持事务、行级锁定和外键，适合于处理高并发和复杂事务的应用。
  • MyISAM：不支持事务和行级锁定，但具有高速读取和插入数据的特性，适合于读多写少的应用。
  • MEMORY：将数据存储在内存中，适用于临时表和需要高速读写的应用。
  • CSV：将数据以逗号分隔的形式存储在文本文件中，适用于处理简单的数据集。
  • ARCHIVE：用于归档和压缩数据，适合于大量历史数据的存储和查询。

数据表

什么是表

表是数据库中最重要、最基本的操作对象，是存储数据的基本单位，一个表就是一个关系，表实质上就是行列的集合，每一行代表一条记录，每一列代表记录的一个字段。

表的命名

完整的数据表名称应该有数据库名和表名两部分组成：

1
databse_name.table_name

同时要遵守MySQL对数据库和表的命名原则：

  1. 名称可以有当前字符集中的任何字母、数据、字符组成
  2. 最长64个字符，但同时也受限于所用操作系统限定的长度
  3. 如果要用引号，必须使用单引号，单双引号并不禁止变量解释
  4. 文件系统的大小写敏感性影响到如何命名和引用数据表。比如UNIX，名称my_tbl和MY_TBL是两个不同的表。从开发环境转移到新系统运行时一定要注意。

表的结构

数据表像表格一样拥有列（Column）和行（Row）。用数据库的专业术语表示列既是字段（Field），每个字段分别存储不同性质的数据，每一行中各个字段的数据构成一条数据记录（Record）。

结构（Structure）和数据记录是表的两大组成部分，表在存储数据记录之前，需要先定义结构，需要定义拥有哪些字段和这些字段的特性（字段名称、数据类型、长度等）

查看表结构可以用DESC

1
DESC table_name;

输出结果如下：

1
2
3
4
5
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| NAME  | varchar(255) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+

字段名

字段名相当于表格首行的表头，各个字段分别用来存储不同性质的数据，字段名同样必须符合MySQL命名格式

创建表

使用CREATE

1
2
3
CREATE [TEMPORARY] TABLE [ IF NOT EXISTS] [库名.]表名 ( 
  表的结构定义 
)[ 表选项 ]

如：

1
2
3
4
5
mysql> CREATE TABLE mytable (
    id int PRIMARY KEY AUTO_INCREMENT,
    name varchar(55) DEFAULT NULL,
    age int NOT NULL
) ENGINE=InnoDB CHARSET=utf8mb4 COLLATE utf8mb4_general_ci;
  • id列是一个INT整数类型，PRIMARY KEY设置id列作为主键，并且使用AUTO_INCREMENT属性使其自动递增。
  • name列是一个VARCHAR可变长度的字符串类型，最大长度为55个字符，并且设置了DEFAULT NULL属性，表示该列默认为空（可以为空）。
  • age列是一个INT整数类型，NOT NULL不允许为空。
  • ENGINE设置存储引擎，CHARSET设置字符集，COLLATE设置排序方法

如果库名带有小数点或者是数据库关键字，则需要将库名用”`”括起来，注意这不是单引号是反引号，键盘左上角那玩意。比如:

1
2
CREATE TABLE `sql.TEST`;
CREATE TABLE sql.TEST;

是两个不同的意思，第一个表示在当前使用的库下创建名为sql.test的表，第二个表示，在名为sql的数据库里面创建一个名为test的表，如果是关键字，不适用反引号则会报错。

删除和清空表

删除表用DROP,清空用的是 TRUNCATE,只会清空表的数据，但是结构还在。

1
2
DROP TABLE [IF EXISTS] table_name;
TRUNCATE TABLE table_name;

使用DELETE也可以删除数据，可以清楚指定行，这三者的区别是：

  1. delete 和 truncate 仅仅删除表数据，drop 连表数据和表结构一起删除，打个比方，delete 是单杀，truncate 是团灭，drop 是把电脑摔了。

  2. delete 是 DML 语句，操作完以后如果没有不想提交事务还可以回滚，truncate 和 drop 是 DDL 语句，操作完马上生效，不能回滚，打个比方，delete 是发微信说分手，后悔还可以撤回，truncate 和 drop 是直接扇耳光说滚，不能反悔。

  3. 执行的速度上，drop>truncate>delete，把库比作抽屉，drop是直接把抽屉丢了（1步），truncate是把里面的东西倒出来然后安回去（2步），delete是在抽屉里找到我要丢的，单独丢掉，然后把抽屉放回去（3步）。

插入数据

使用INSERT INTO语句来插入数据

1
2
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

如果值是字符串类型，则需要用单引号或双引号括起来

1
INSERT INTO mytable (id, name, age) VALUES (1, 'xiaoming', 20);

插入新行

id name age
1 xiaoming 20

如果要插入多行，可以指定多组VALUES，用逗号分隔

1
2
3
4
INSERT INTO mytable (id, name, age)
VALUES
	(4, 'xiaohong', 21),
	(5, 'tom', 20);

查询数据

查询数据用SELECT语句查询

1
2
3
4
5
SELECT column1, column2, colunm3 
FROM table_name
[WHERE condition]
[ORDER BY column_name [ASC | DESC]]
[LIMIT number];
  • WHERE condition 是一个可选的子句，用于指定过滤条件，只返回符合条件的行。
  • ORDER BY column_name [ASC | DESC] 是一个可选的子句，用于指定结果集的排序顺序，默认是升序（ASC）。
  • LIMIT number 是一个可选的子句，用于限制返回的行数。

实例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
#返回所有数据
SELECT * FROM mytable;

#返回特定字段
SELECT id,name FROM mytable;

#WHERE语句，返回满足设定条件的行
#以下表示返回age字段等于20的行
SELECT * FROM mytable WHERE age = 20;

#ORDER BY 语句，默认表示按照某列的升序排序，ASC表示升序可以省略
SELECT * FROM mytable ORDER BY age;

#ORDER BY 语句设置DESC，表示按照某列的降序排序
SELECT * FROM mytable ORDER BY id DESC;

#LIMIT 语句，限制返回的行数
SELECT * FROM mytable LIMIT 2;

WHERE语句还支持各种运算符，比较运算符、逻辑运算符（NOT AND OR）、通配符等都能使用。

如果要使用通配符，需要用到LIKE，如果用了LIKE但没用通配符，那么他的效果和=是一样的

1
2
3
4
5
6
7
8
#通配符%，表示0个或多个字符，返回name字段带有xiao前缀的行
SELECT * FROM mytable WHERE  name LIKE 'xiao%';

#通配符_，表示一个字符，返回name字段第二个字母为x的行
SELECT * FROM mytable WHERE name LIKE '_x%';

#不区分大小写匹配
SELECT * FROM mytable WHERE name LIKE 'x%' COLLATE utf8mb4_general_ci;

WHERE还有个IN和NOT IN条件，可以筛选与指定数据相等的行

1
2
3
4
5
#返回age字段是11或20的
SELECT * FROM mytable WHERE age IN (11, 20);

#取上面的补集
SELECT * FROM mytable WHERE age NOT IN (11, 20);

WHERE还有BETWEEN、NOT BETWEEN、IS NULL 、IS NOT NULL

1
2
3
4
5
#BETWEEN，筛选属于给定区间的数据，可以是时间、数字、文本
#两边都是闭区间，即包含两边
SELECT * FROM mytable WHERE id BETWEEN 1 AND 5;

#如果需要取补集可以用NOT BETWEEN

更新数据

UPDATE用来更新数据，语法如下：

1
2
3
UPDATE table_name
SET column1=value1,column1=value2...
WHERE condition;

示例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# 更新多个列
  UPDATE mytable
  SET age=20,name='lihua'
  WHERE id=1;

# 使用表达式更新
	UPDATE mytable
	SET age=age+1;
	
# 使用子查询的值
	UPDATE mytable
  SET name = (
  	SELECT name FROM 座位表
  	WHERE 座位表.id = mytable.id
  	LIMIT 1
  );

删除数据

删除数据用DELETE语句

1
2
DELETE FROM table_name
WHERE condition;

如果没有指定条件，则所有记录都会被删除。

修改表结构

使用ALTER可以修改表的结构，重命名，添加删除字段等

1
ALTER TABLE [表名] {MODIFY | RENAME | ADD | DROP}

  • 修改字段类型

    1
    ALTER TABLE [表名] MODIFY COLUMN  [字段名] [新类型]

    比如修改name字段为varchar200类型

    1
    ALTER TABLE table_name MODIFY COLUMN name VARCHAR(200);

  • 重命名字段

    1
    ALTER TABLE [表名] RENAME COLUMN  [字段名] [新字段名];

UNION操作符

用于连接两个及以上的SELECT语句的结果组合到一个集合并去除重复。语法格式：

1
2
3
4
5
6
7
8
SELECT column1, column2, ...
FROM table1
WHERE condition1
UNION
SELECT column1, column2, ...
FROM table2
WHERE condition2
[ORDER BY column1, column2...]

ORDER BY 子句是一个可选的子句，用于指定合并后的结果集的排序顺序。

每个 SELECT 语句的列数和对应位置的数据类型必须相同,如果数量不等可以用NULL补位

使用UNION的时候会自动去除重复行，如果不想去除，可以用UNION ALL

ORDER BY排序

在使用SELECT时，可以对获取的数据进行排序，ORDER BY可以设定指定一个或多个字段以哪种方式进行排序，并支持ASC升序或 DESC降序

1
2
3
SELECT column1, column2...
FROM mytable
ORDER BY column1 [ASC|DESC],column2[ASC|DESC],...;

当有指定了多个字段时，会按照书写顺序依次排序，比如上诉代码中，会先以column1为依据排序，如果有column1相同的行，则会继续以column2进行排序。

除了使用字段外，还可以用表达式进行排序

1
2
3
SELECT product_name, price * discount_rate AS discounted_price
FROM products
ORDER BY discounted_price DESC;

以上 SQL 语句将选择产品表 products 中的产品名称和根据折扣率计算的折扣后价格，并按折扣后价格降序 DESC 排序

在SQL中，如果需要把指定的字段中出现了NULL值可以用NULLS FIRST或NULLS LAST设置NULL值的排序

1
2
3
SELECT product_name, price
FROM products
ORDER BY price DESC NULLS LAST;

而在mysql中，NULL值默认为最小，即升序最前，降序最后，如果想改变，由于MySQL并没有NULLS语法，所以需要使用CASE或IS实现相同的效果

1
2
3
SELECT name
FROM mytable
ORDER BY CASE WHEN name IS NULL THEN 1 ELSE 0 END, name DESC;

上述代码表示如果name是NULL值则返回1，否则返回0，数据库会先根据这个返回值进行排序，而默认是升序排序，所以1也就是name IS NULL，会被置底，接着其他name则会按照DESC降序排序.如果想让NULL置顶，可以更改01的位置，或者在END后添加DESC

1
2
3
SELECT name
FROM mytable
ORDER BY name IS NULL DESC, name DESC;

上述代码表示，先根据（name IS NULL）的值（1/0）进行排序，然后再根据name进行排序，上面的代码会将null值置顶，其余的name按照降序排序。和用case方法类似。不过这种方法在不同的数据库中得到了不同的效果，建议手动尝试下。

GROUP 分组

GROUP BY可以根据一个或多个字段进行分组，还可以使用COUNT,SUM,AVG等函数。语法格式:

1
2
3
4
SELECT column1, aggregate_function(column2)
FROM table_name
WHERE condition
GROUP BY column1;

示例：

1
2
# 按名字分组，查看班里重名的人数
SELECT name ,COUNT(*) FROM 座位表 GROUP BY name;

使用WITH ROLLUP可以在实现分组统计基础上再进行统计（SUM, AVG, COUNT...）