MySQL的基础用法

叶小楠2024-10-072024-12-09

MySQL的基础用法 - by Karey

SQL通用语法

1.SQL语句可以单行或多行书写，以分号结尾。

2.SQL语句可以使用空格 / 缩进来增强语句的可读性。

3.MySQL数据库的SQL语句不区分大小写，关键字建议使用大写。

4.MySQL中的注释：

 - 单行注释：-- 注释内容 或 # 注释内容
 - 多行注释：/* 注释内容 */

SQL分类

分类	全称	说明
DDL	Data Defimition Language	数据定义语言，用来定义数据库对象（数据库，表，字段）
DML	Data Manipulation Language	数据操作语言，用来对数据库中的数据进行增删改
DQL	Data Query Language	数据查询语言，用来查询数据库中表的记录
DCL	Data Contro Language	数据控制语言，用来创建数据库用户，控制数据库的访问权限

1-DDL-数据库操作

查询

1	show databases;

查询当前所在数据库

1	select database();

创建数据库

create database [IF NOT EXIST] database_name [default charset字符集] [collate排序规则];

示例：
create database gxhqxx;

删除数据库

1	drop database [IF NOT EXIST] database_name;

使用数据库

1	use database_name;

查询当前所在数据库中的某一张表的所有数据

1	select * from table_name;

1.1-DDL-表操作-查询

查询当前数据库下的所有表

1	show tables;

查询表结构

desc table_name;

示例：
mysql> desc user;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | YES  |     | NULL    |       |
| name  | int(11)      | YES  |     | NULL    |       |
| age   | varchar(255) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

查询指定表的建表语句

show create table table_name;

示例：
mysql> show create table user;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user  | CREATE TABLE `user` (
  `id` int(11) DEFAULT NULL COMMENT '用户标识',
  `name` int(11) DEFAULT NULL COMMENT '用户名',
  `age` varchar(255) DEFAULT NULL COMMENT '用户年龄'
) ENGINE=InnoDB DEFAULT CHARSET=latin1            |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

1.2-DDL-表操作-创建

创建一个简单表

create table table_name(
	字段1 字段2类型[comment 字段1注释],
	字段1 字段2类型[comment 字段1注释],
	字段1 字段2类型[comment 字段1注释],
	字段1 字段2类型[comment 字段1注释]
	)[comment 表注释];

示例：
create table user(
	id int comment '用户标识',
    name int comment '用户名',
    age varchar(255) comment '用户年龄'
)

在MySQL建表语句中最后一个字段后面不需要添加逗号 “ ， ”

[comment]为可加可不加字段

1.3-DDL-表操作-数据类型

1.3.1 - 数值类型

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 Bytes	(-128，127)	(0，255)	小整数值
SMALLINT	2 Bytes	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 Bytes	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT或INTEGER	4 Bytes	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 Bytes	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4 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)	单精度浮点数值
DOUBLE	8 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的值	小数值

1.3.2 - 字符串类型

类型	大小	用途
CHAR	0-255 bytes	定长字符串
VARCHAR	0-65535 bytes	变长字符串
TINYBLOB	0-255 bytes	不超过 255 个字符的二进制字符串
TINYTEXT	0-255 bytes	短文本字符串
BLOB	0-65 535 bytes	二进制形式的长文本数据
TEXT	0-65 535 bytes	长文本数据
MEDIUMBLOB	0-16 777 215 bytes	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215 bytes	中等长度文本数据
LONGBLOB	0-4 294 967 295 bytes	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295 bytes	极大文本数据

1.3.3 - 日期和时间类型

类型	大小 ( bytes)	范围	格式	用途
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:01’ UTC 到 ‘2038-01-19 03:14:07’ UTC结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日凌晨 03:14:07	YYYY-MM-DD hh:mm:ss	混合日期和时间值，时间戳

1.4-DDL-表操作-修改

添加字段

alter table table_name add 字段名 类型（长度） [comment 注释] [约束];

示例:
alter table emp add nickname varchar(20);

修改数据类型

1	alter table table_name modify 字段名新数据类型(长度);

修改字段名和字段类型

alter table table_name change 旧字段名 新字段名 类型(长度) [comment注释] [约束];

示例：
alter table emp change nickname username varchar(30) commnet '昵称';

删除字段

alter table table_name drop 字段名;

示例:
alter table emp drop nickname;

修改表名

alter table table_name rename to new_table_name;

示例:
alter table emp rename to new_emp;

删除表

drop [if exists] table_name ;

示例:
drop emp;

删除指定表，并重新创建该表

truncate table table_name;

示例:
truncate table emp;

注意：在删除表时，表中的全部数据也会被删除。

1.5-DDL-语法总结

DDL-数据库操作

show databases;					[查看所有数据库]
create databases database_name;	[创建一个数据库]
use database_name;				[使用一个数据库]
select database();				[查看当前所处的数据库]
drop database database_name;	[删除一个数据库]

DDL-表操作

show tables;												[查看当前数据库下的所有数据表]
create table table_name;									[创建一个表]
desc table_name;											[查看表结构]
show create table table_name;								[查看表创建使用的语句]
alter table table_name add / modify / change / drop / rename to;	
[修改一张表。 add:添加一个字段 modify:修改数据类型 change:修改字段名和字段类型 drop:删除一个字段 rename to:修改一张表的表名]
drop table table_name;										[删除一张表]

2-DML-添加数据

小提示

》》插入数据（insert）

》》修改数据（update）

》》删除数据（delete）

给指定字段添加数据

1	insert into table_name(字段名1,字段名2,...) values(值1，值2,...);

给全部字段添加数据

1	insert into table_name values (值1，值2,...);

批量添加数据

insert into table_name (字段1，字段2，...) values (值1,值2,...),(值1,值2,...),(值1,值2,...);
insert into table_name values(值1,值2,...),
							 (值1,值2,...),
							 (值1,值2,...);

注意：

插入数据时，指定的字段顺序要与值的循序是一一对应的。

字符串和日期类型数据应该包含在引号中。

插入的数据大小，应该在字段的规定范围内

2.1-DML-修改数据

update table_name set 字段名1=值1，字段名2=值2,... [where 条件];

示例：
update emp set name = 'xiaozhang',age = '21' where id = 1;

注意：修改语句的调前可有可无，如果没有条件，那么将会修改整张表的所有数据。

2.2-DML-删除数据

delete from table_name [where 条件];

删除单一数据示例：
delete from emp where name = 'xiaozhang';

删除表中的所有数据：
delete from emp;

注意：delete语句的条件可有可无，如果没有条件，那么将会删除整张表的所有数据。

delete语句不能删除某一个字段的值（可以用update）。

2.3-DML-语法总结

添加数据

1	insert into table_name (字段1，字段2，...) values (值1，值2,...) [,(值1，值2，...)...];

修改数据

1	update table_name set 字段1 = 值1 ，字段2 = 值2 [where 条件];

删除数据

1	delete from table_name [where 条件];

3-DQL-数据库查询操作基本语法

select
	字段列表
from 
	表名列表
where
	条件列表
group by
	分组字段列表
having
	分组后条件列表
order by
	排序字段列表
limit
	分页参数

3.2-DQL-基本查询

查询多个字段

1
2
3

select 字段1，字段2，字段3... from table_name;

select * from table_name;

设置别名

1	select 字段1 [as 别名1], 字段2[as 别名2] ... from table_name;

去除重复记录

1	select distinct 字段列表 from table_name;

3.3-DQL-条件查询

语法

1	select 字段列表 from table_name where 条件列表;

条件

比较运算符	功能
>	大于
>=	大于等于
<	小于
<=	小于等于
=	等于
<> 或 !=	不等于
between … and …	在某个范围之内（含最小，最大值）
in(…)	在in之后的列表中的值，多选一
like 占位符	模糊匹配（_匹配单个字符，%匹配任意字符）
is null	是NULL

逻辑运算符	功能
and 或者 &&	并且（多个条件同时成立）
or 或者 \|\|	或者（多个条件任意一个成立）
not 或者 !	非，不是

3.4-DQL-排序查询

在查询的末尾添加asc或者desc对想要查询的数据进行排序选项，asc为从小到大，desc为大到小排序

# 按照酒店住客评分最高到最低的酒店进行排序（大到小）
SELECT * FROM `comments` ORDER BY 住客评分 desc , 日期 asc

# 按照酒店住客评分最高到最低的酒店进行排序（大到小），日期最新的在前面（大到小）
SELECT * FROM `comments`  ORDER BY 住客评分 desc , 日期 desc

3.5-DQL-模糊查询

在 SQL 中，DQL（Data Query Language）是用于查询数据的子语言，而模糊查询是一种常见的查询方式，主要通过 LIKE 操作符和通配符来查找符合部分条件的数据。

模糊查询的语法

1
2
3

SELECT 列名
FROM 表名
WHERE 列名 LIKE 模式;

LIKE 是模糊匹配的关键字。
模式使用通配符来表示模糊查询的规则。

常用的通配符

%

：表示任意长度的任意字符（包括空字符）。
- 例如：'a%' 表示以 a 开头的任意字符串。
_

：表示单个任意字符。
- 例如：'a_' 表示以 a 开头且后面跟着一个任意字符的字符串。
[]

：表示匹配括号中任意一个字符（仅适用于某些数据库，如 SQL Server）。
- 例如：'[a-c]%' 表示以 a、b 或 c 开头的任意字符串。
[^]

：表示不匹配括号中的任何一个字符（仅适用于某些数据库）。
- 例如：'[^a-c]%' 表示不以 a、b 或 c 开头的任意字符串。

示例

示例 1：查找以 “a” 开头的名字

1
2
3

SELECT * 
FROM 用户表
WHERE 姓名 LIKE 'a%';

示例 2：查找包含 “张” 的名字

1
2
3

SELECT * 
FROM 用户表
WHERE 姓名 LIKE '%张%';

示例 3：查找名字长度为 3 的记录

1
2
3

SELECT * 
FROM 用户表
WHERE 姓名 LIKE '___';

示例 4：查找以 “李” 开头，且第二个字符是 “小” 的名字

1
2
3

SELECT * 
FROM 用户表
WHERE 姓名 LIKE '李小%';

示例 5：查找不包含 “测试” 的记录

1
2
3

SELECT * 
FROM 用户表
WHERE 姓名 NOT LIKE '%测试%';

实用场景

搜索功能：用户模糊输入关键字，返回匹配内容。
日志查询：从日志表中查找符合特定时间或格式的记录。
内容过滤：查找包含敏感词的文本记录。

3.6-DQL-分组查询

分组查询简介

分组查询（GROUP BY）用于将查询结果按照某一列或多列的值进行分组，然后对每个分组进行聚合操作（如计数、求和、求平均值等）。通常与聚合函数（如 COUNT、SUM、AVG、MAX、MIN）一起使用。

基本语法

SELECT 列1, 聚合函数(列2)
FROM 表名
WHERE 条件
GROUP BY 列1
HAVING 条件
ORDER BY 列名;

说明：

**GROUP BY 列1**：将查询结果按照列1的值进行分组。
聚合函数：对每个分组进行汇总操作。
HAVING 子句：用于过滤分组后的结果（类似于 WHERE，但 WHERE 作用于分组前的行）。
**ORDER BY**：对最终结果进行排序。

示例

示例 1：按部门分组统计员工人数

1
2
3

SELECT 部门, COUNT(*) AS 员工数量
FROM 员工表
GROUP BY 部门;

结果（假设表中有以下数据）：

部门	员工数量
技术部	10
财务部	5

示例 2：计算每个部门的平均工资

1
2
3

SELECT 部门, AVG(工资) AS 平均工资
FROM 员工表
GROUP BY 部门;

示例 3：查找员工总数大于 5 的部门

SELECT 部门, COUNT(*) AS 员工数量
FROM 员工表
GROUP BY 部门
HAVING COUNT(*) > 5;

示例 4：按产品分组，统计每种产品的销售总额，并按总额降序排序

SELECT 产品名称, SUM(销售额) AS 总销售额
FROM 销售表
GROUP BY 产品名称
ORDER BY 总销售额 DESC;

示例 5：多列分组

按部门和职位统计每个职位的人数：

1
2
3

SELECT 部门, 职位, COUNT(*) AS 人数
FROM 员工表
GROUP BY 部门, 职位;

注意事项

GROUP BY 中的列必须出现在 SELECT 中，除非它们是聚合函数的一部分。
WHERE 和 HAVING 的区别

：
- WHERE 作用于分组前的原始数据。
- HAVING 作用于分组后的结果。
如果使用 ORDER BY 排序，必须作用于最终查询结果。