MySQL基础-基本语法-优快云博客

本文链接：https://blog.youkuaiyun.com/ljb0077/article/details/117135270

本文详细介绍了MySQL的安装、基本命令、数据库操作、数据类型、数据表操作、视图创建与删除、数据的增删改查以及查询语法。内容涵盖创建与删除数据库、备份与恢复、字符集与校对规则、数据表的修改、数据备份恢复、数值、字符串和日期类型、以及DML和DQL的各种操作。此外，还讨论了视图的使用以及分页查询等高级查询技巧。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1、MySQL的安装

参考：MySQL5.7免安装版

2、MySQL 基本命令

MySQL是一种数据库管理系统。数据库是数据的结构化集合。它可以是任何东西，从简单的购物清单到画展，或企业网络中的海量信息。要想将数据添加到数据库，或访问、处理计算机数据库中保存的数据，需要使用数据库管理系统，如MySQL服务器。计算机是处理大量数据的理想工具，因此，数据库管理系统在计算方面扮演着关键的中心角色，或是作为独立的实用工具，或是作为其他应用程序的组成部分。

为了连接服务器，当调用mysql时，通常需要提供一个MySQL用户名并且很可能需要一个密码。如果服务器运行在登录服务器之外的其它机器上，还需要指定主机名。联系管理员以找出进行连接所使用的参数 (即，连接的主机、用户名和使用的密码)。知道正确的参数后，可以按照以下方式进行连接：

1）启动和关闭MySQL服务

# 启动MySQL服务
shell> net start mysql
# 关闭MySQL服务
shell> net stop mysql

2）连接与断开服务器

shell> mysql -h host -u user -p
Enter password: ********

2.1 DDL 数据库相关操作

单行与多行注释：

# 单行注释
-- 单行注释

/*
  多行注释
*/

2.2.1 数据库基本操作

3）查看当前实例下包含多少个数据库（注意结尾分号），格式是show databases [like '数据库名']，

mysql> show databases;

# 查看是否存在名字为db_test的数据库
mysql> show databases like 'db_test';

# 查看名字是db开头的数据库有哪些
mysql> show databases like 'db%';

# 查看名字是test结尾的数据库有哪些
mysql> show databases like '%test';

注意：MySQL 默认以分号作为每条命令的结束符，所以在每条 MySQL 命令结束后都应该输一个英文分号（ ; ）。

成功地连接后，可以在mysql>提示下输入QUIT (或\q)随时退出：

mysql> QUIT
Bye

4）创建数据库

mysql> create database [IF NOT EXISTS] 数据库名;

# 可以使用SHOW CREATE DATABASE查看 db_test01 数据库的定义声明，查看该数据库的指定字符集是什么
mysql> show create database db_test01;

如：

# 创建数据库名为db01的数据库
create database db01;

# 创建数据库名为db02的数据库，并指定字符集为utf8
create database db02 character set utf8;

# 创建数据库名为db03的数据库，并指定字符集为utf8、校对规则为utf8_bin。
create database db03 character set utf8 collate utf8_bin;

补充：

character set ：指定数据库采用的字符集，默认是utf8

collate：指定数据库字符集的校对规则（常用的是utf8_bin区分大小写，utf8_general_ci不区分大小写）。默认的是utf8_general_ci

5）删除数据库

mysql> drop database [ if exists ] 数据库名;	# 使用IF EXISTS从句，可以防止系统报错(数据库不存在时)

6）进入数据库：
```
mysql> use 数据库名;
```

7）修改数据库

在 MySQL 中，可以使用 ALTER DATABASE 来修改已经被创建或者存在的数据库的相关参数。修改数据库的语法格式为：

ALTER DATABASE [数据库名] { 
[ DEFAULT ] CHARACTER SET <字符集名> |
[ DEFAULT ] COLLATE <校对规则名>}

语法说明如下：

ALTER DATABASE 用于更改数据库的全局特性。
使用 ALTER DATABASE 需要获得数据库 ALTER 权限。
数据库名称可以忽略，此时语句对应于默认数据库。
CHARACTER SET 子句用于更改默认的数据库字符集。

使用命令行工具将数据库 test_db 的指定字符集修改为 gb2312，默认校对规则修改为 gb2312_unicode_ci，输入 SQL 语句与执行结果如下所示：

mysql> ALTER DATABASE test_db
    -> DEFAULT CHARACTER SET gb2312
    -> DEFAULT COLLATE gb2312_chinese_ci;
mysql> SHOW CREATE DATABASE test_db;
+----------+--------------------------------------------------------+
| Database | ALTER Database                                        |
+----------+--------------------------------------------------------+
| test_db  | ALTER DATABASE `test_db` /*!40100 DEFAULT CHARACTER SET gb2312 */|
+----------+--------------------------------------------------------+
1 row in set (0.00 sec)

2.2.2 数据库的备份与恢复

1）备份数据库：

shell> mysqldump -u root -p -B 数据库名1  [数据库名2...] > d:\\beifen\bf.sql

2）恢复数据库：（注意，要进入MySQL命令行再执行）

mysql> source d:\\beifen\bf.sql

2.2 MySQL常用数据类型（列类型、字段类型）

在这里插入图片描述

2.2.1 数值类型

在这里插入图片描述

2.2.2 字符串类型

在这里插入图片描述

2.2.3 日期类型

在这里插入图片描述

特别注意：

CHAR(size) 固定长度字符串最大255字符
VARCHAR(size) 0~65535字节
可变长度字符串最大65532字节（utf8编码最大21844字符1-3个字节用于记录大小）

如果表的编码是utf8 varchar (size) size = (65535-3) / 3 = 21844 utf8编码是3个字节表示一个汉字

如果表的编码是gbk varchar (size) size = (65535-3) / 2 = 32766 utf8编码是2个字节表示一个汉字

2.3 DDL 数据表相关操作

1）进入特定数据库后，查询该数据库下的有哪些表：
```
mysql> show tables;
```
2）查看数据表的表结构定义:
```
mysql> desc 表名
```
3）创建数据表
```
create table tb_01(
    id int,
    `name` varchar(255),
    `sex` varchar(255),
    `birthday` date)
    character set utf8 collate utf8_bin engine INNODB;
```
数据类型默认是有符号的，若是创建某个字段是无符号的，则：
```
# 指定id类型是无符号的int
create table tb_01(id int unsigned);
```
说明：其中character set和collate若不指定，则与其所在的数据库的字符集及校对规则保持一直。engine INNODB是指定引擎，存储机制。

MySQL 数据库通常支持如下两种存储机制。
- MyISAM：这是 MySQL 早期默认的存储机制，对事务支持不够好。
- InnoDB： InnoDB 提供事务安全的存储机制。 InnoDB 通过建立行级锁来保证事务完整性，并以Oracle 风格的共享锁来处理 Select 语句。系统默认启动 InnoDB 存储机制，如果不想使用 InnoDB存储机制，则可以使用 skip-innodb 选项。
对比两种存储机制， InnoDB 比 MylSAM 多了事务支持的功能。如果使用 5.0 以上版本的 MySQL 数据库系统，通常无须指定数据表的存储机制，因为系统默认使用 InnoDB 存储机制。如果需要在建表时显式指定存储机制，则可在标准建表语法的后面添加下面任意一句。
```
ENGINE=MyISAM   --强制使用 MylSAM 存储机制。
ENGINE=InnoDB   --强制使用 InnoDB 存储机制。
```

2.3.1 修改表结构的语法

1）重命名数据表名 alter & rename

# 将 tb_test 数据表重命名为 tb01
alter table tb_test
rename to tb01;

2）增加新列 alter & add

修改表结构使用 alter table, 修改表结构包括增加列定义、修改列定义、删除列、重命名列等操作。

为数据表增加字段的 SQL 语句如下：

# 为 tb01 数据表增加一个 new_id 字段， 该字段的类型为 int
alter table tb01
add new_id int;

# 为tb01表增加 aa、 bb 字段， 两个字段的类型都为 varchar(255)
alter table tb01
(
  add
  aa varchar(255) default 'XXX',
  bb varchar(255)
) ;

3）修改列定义 alter & modify

# 将 tb01 表的 new_id 列修改成 varchar (255 > 类型
 alter table tb01
 modify new_id varchar(255);
 
 # 将 tb01 表的 bb 列修改成 int 类型
 alter table tb01
 modify bb int;

4）删除列 alter & drop

# 删除 tb01 表中的 aaa 字段
alter table tb01
drop aa;

2.3.2 备份和恢复数据表

# 备份数据库中的某些表
shell> mysqldump -u 用户名 -p密码 数据库名 表1 表2 表n > d:\\beifen\tables\bf_tb.sql

对比备份数据库：多了个 -B 参数

# 备份整个数据库
shell> mysqldump -u root -p -B 数据库名1  [数据库名2...] > d:\\beifen\bf.sql

2.3.3 删除表

# 删除数据表tb01
drop table tb01;

注意：删除数据表的效果如下：

表结构被删除，表对象不再存在。
表里的所有数据也被删除。
该表所有相关的索引、约束也被删除。

2.3.4 truncate 截断表

对于大部分数据库而言， truncate 都被当成 DDL 处理， truncate 被称为“ 截断” 某个表一一它的作用是删除该表里的全部数据，但保留表结构。相对于 DML 里的 delete 命令而言， truncate 的速度要快得多，而且 truncate 不像 delete 可以删除指定的记录， truncate 只能一次性删除整个表的全部记录。

truncate 表名

2.4 视图

视图看上去非常像一个数据表，但它不是数据表，视图只是一个或多个数据表中数据的逻辑显示。

使用视图有如下几个好处：

可以限制对数据的访问。
可以使复杂的查询变得简单。
提供了数据的独立性。
提供了对相同数据的不同显示。

因为视图只是数据表中数据的逻辑显示一一也就是一个查询结果，所以创建视图就是建立视图名和查询语句的关联。

2.4.1 创建视图

# 如果该视图不存在， 则创建视图； 如果指定视图名的视图己经存在， 则使用新视图替换原有视图。 后面的 subquery 就是一个查询语句， 这个查询可以非常复杂。
create or replace view 视图名
as
subquery

为了强制不允许改变视图的数据， MySQL 允许在创建视图时使用 with check option 子句，使用该子句创建的视图不允许修改，如下所示：

create or replace view my_view_test
as
select id,name from tb_03
# 指定不允许修改该视图的数据
with check option;

2.4.2 删除视图

drop view 视图名

2.5 DML 数据操作语法

DML 主要操作数据表里的数据，主要是增、删、改操作。

2.5.1 insert 插入

insert 用于向指定数据表中插入记录。例如我们的 tb_03 表中有两个字段：name 和 sex

insert into tb_03(id, name, sex)
values(3, '李四' , 'M');

# 也可以使用子查询的值来插入
insert into tb_03(name, sex)
select name, sex from tb_02;

# 也可以同时插入多行值
insert into tb_01
values(1 , "mark", 'M'),
	(2 , "jack",'W');

2.5.2 update 修改

# 语法是：
update table_name
set columnl= valuel[, column2 = value2] …
[WHERE condition];

# 把 tb_02 表中所有记录的 name 列的值都改为’mary’。
update tb_02
set `name` = "mary";

# 只修改 id 大于 2 的记录
update tb_02
set `name` = "jack"
where id > 2;

2.5.3 delete删除

delete from 语句用于删除指定数据表的记录。使用 delete from 语句删除时不需要指定列名，因为总是整行地删除。使用 delete from 语句可以一次删除多行，删除哪些行采用 where 子句限定，只删除满足 where 条件的记录。没有 where 子句限定将会把表里的全部记录删除。

delete from tb_02
where id = 3;

2.6 DQL数据查询语法

单表查询的语法：

SELECT 字段列表
FROM 表名列表
WHERE 条件列表
GROUP BY 分组列表
HAVING 分组之后的条件
ORDER BY 排序
LIMIT 分页限定

2.6.1 条件查询

>、<、<=、>=、=、<>：其中<>表示不等于
BETWEEN ... AND
IN(集合)
LIKE：(_)单个任意字符；(%)多个任意字符
IS NULL或 IS NOT NULL
and 或 &&
or 或 ||

2.6.2 排序查询

语法：order by 子句

order by 排序字段1 排序方式1, 排序字段2 排序方式2, ...，默认升序(ASC)
排序方式：默认升序（ASC）、降序（DESC）

示例：查询users表中得所有数据，以name字段降序
```
SELECT *
FROM users
ORDER BY `name` DESC;
```

2.6.3 聚合函数

对查询结果进行计算。（聚合计算排除了Null的值）

count：计算个数

计算students表中，math成绩大于75的人数
```
SELECT COUNT(`name`) FROM students WHERE math>75;
```
max：计算最大值

查询students表中，男生的math成绩最高的分数
```
SELECT MAX(`math`) FROM students WHERE `sex`="M";
```
min：计算最小值
sum：计算和
avg：计算平均值

2.6.4 分组查询

group by：

示例：在students表中，分别查询男生、女生的数学平均分

SELECT sex, AVG(math), COUNT(id) FROM students GROUP BY sex;

结果：
+------+-----------+-----------+
| sex  | avg(math) | count(id) |
+------+-----------+-----------+
| 女   |   86.5000 |         2 |
| 男   |   73.5000 |         3 |
+------+-----------+-----------+

# 只让数学成绩大于70分的参与上述分组查询，并分组要大于2
SELECT sex, AVG(math), COUNT(id) FROM students WHERE math>70 GROUP BY sex HAVING COUNT(id) > 2;	# 不推荐
SELECT sex, AVG(math), COUNT(id) AS renshu FROM students WHERE math>70 GROUP BY sex HAVING renshu > 2; # 推荐写法

where和having的区别：
- where 在分组之前进行限定，如果不满足，不参与分组。having在分组之后进行限定，如果不满足，则不会被查询出来
- where 限定中不可以有聚合函数；having 限定中可以进行聚合函数的判断

2.6.5 分页查询

limit n,m：查询结果中返回从索引为n开始，数量为m的数据

# 假设每页数据是3个
SELECT * FROM students LIMIT 0,3;	# 第一页，从索引为0开始，3条数据
SELECT * FROM students LIMIT 3,3;	# 第二页，从索引为3开始，3条数据
SELECT * FROM students LIMIT 6,3;	# 第三页，从索引为6开始，3条数据