当前位置：首页 > article >正文

MySQL数据库2——SQL语句

article 2024/11/18 12:38:05

一.`SQL`基础

1.SQL通用语法

1.SQL语句可以单行或多行书写，以分号结尾。
2.SOL语句可以使用空格/缩进来增强语句的可读性。
3.MySQL数据库的SQL语句不区分大小写，关键字建议使用大写

注释：

单行注释：-- 注释内容或#注释内容(MySQL特有)
多行注释：/*注释内容*/

2.SQL语句分类

分类	说明
`DDL`	数据定义语言，用来定义数据库对象（数据库、表、字段、视图）
`DML`	数据操作语言，用来对数据库表中的数据进行增删改
`DQL`	数据查询语言，用来查询数据库中表的记录
`DCL`	数据控制语言，用来创建数据库用户、控制数据库的访问权限

3.基本数据类型

（1）数值型：

INTEGER	长整数（也可以写成INT），长度为`4B`
`SMALLINT`	短整数，长度为`2B`
REAL	浮点数，`4B`
DOUBLE PRECISION	双精度浮点数，`8B`
FLOAT(n)	浮点数，精度至少为n位数字
`NUMERT(p,d)`	定点数，由p位数字组成（不包括符号、小数点），小数点后有d位数字

其中NUMERT(p,d)也可以写成DECIMAL(p,d)或DEC(p,d)

（ 2）字符串型：

CHAR(n)	长度为n的定长字符串
`VARCHAR(n)`	具有最大长度为n的变长字符串

（3）位串型：

位串：二进制序列

BIT(n)	长度为n的二进制位串
BIT VARYING(n)	最大长度为n的变长二进制位串

（4）时间型：

DATE	日期，包含年、月、日，行位`YYYY-MM-DD`
TIME	时间，包含一日的时、分、秒，行为`HH: MM: SS`

二.`DDL`

1.DDL-数据库操作

查询：

SHOW DATABASES;                              -- 查询所有数据库
SELECT DATABASE();                           -- 查询当前数据库

创建：

CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则];

[]中的内容是可选项，[IF NOT EXISTS]表示如果同名数据库不存在就创建一个，如果存在就不管；后面字符集一般选择utf8，但一般都会使用utf8mb4

删除：

DROP DATABASE [IF EXISTS] 数据库名;

中间选项是为了确保删除一个不存在的数据库不会报错。

使用：

USE 数据库名;

常用来切换需要操作的数据库，和SELECT DATABASE(); 搭配使用。

演示操作：

在这里插入图片描述

2.DDL-表操作

查询：

SHOW TABLES;                               -- 查询当前数据库所有表
DESC 表名;                                 -- 查询表结构
SHOW CREATE TABLE 表名;                    -- 查询指定表的建表语句

创建：

CREATE TABLE 表名(
	字段1	字段1类型[COMMENT 注释],
	字段2	字段2类型[COMMENT 注释],
	……
	字段n	字段n类型[COMMENT 注释]
)[COMMENT 表注释];

注意：最后一个字段后面没有,。

演示：

在这里插入图片描述

3.DDL-表修改

添加字段：

ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释] [约束];

修改字段：

ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);                                 -- 修改指定字段的数据类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT 注释] [约束];       -- 修改字段名和数据类型

删除字段：

ALTER TABLE 表名 DROP 字段名;

修改表名：

ALTER TABLE 表名 RENAME TO 新表名;

演示：

4.DDL-表删除

删除：

DROP TABLE [IF EXISTS] 表名;                              -- 删除表
TRUNCATE TABLE 表名;                                      -- 删除指定表，并重新创建该表

第二种方式删除后重新建立的表是一张空表。

演示：

在这里插入图片描述

5.图形化界面工具

上面以及以后的SQL语句虽然可以在命令端口执行，它不方便，在实际开发过程中，我们更多的是使用MySQL的图形化界面工具。

当前主流的MySQL图形化界面工具：

在这里插入图片描述

这里我们选择Navicat，仔细摸索一下该软件。

三.`DML`

1.添加数据

给指定字段添加数据：

INSERT INTO 表名(字段1，字段2，……) VALUES(值1，值2，……);

给全部字段添加数据：

INSERT INTO 表名 VALUES(值1，值2，……);

批量添加数据：

INSERT INTO 表名(字段1，字段2，……) VALUES(值1，值2，……),(值1，值2，……),(值1，值2，……);
INSERT INTO 表名 VALUES(值1，值2，……),(值1，值2，……),(值1，值2，……);

注意：

插入数据时，指定的字段顺序和值的顺序是一一对应的
字符串和日期型数据应包含在引号中
插入的数据大小，应该在字段的规定范围内

2.修改数据

修改数据：

UPDATE 表名 SET 字段1=值1,字段2=值2,……[WHERE 条件];

修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据。

3.删除数据

删除数据：

DELETE FROM 表名 [WHERE 条件]

注意：

DELETE语句的条件可以有，也可以没有，如果没有则会删除整张表的所有数据
DELETE不能删除某一个字段的值（可以使用UPDATE把某字段置空）

四.`DQL`

1.DQL语法

SELECT
	字段列表
FROM
	表名列表
WHERE
	条件列表
GROUP BY
	分组字段列表
HAVING
	分组后条件列表
ORDER BY
	排序字段列表
LIMIT
	分页参数

把其依次分为以下几点：

基本查询
条件查询(WHERE)
聚合函数 (count、max、min、avg、sum)
分组查询 (GROUP BY)
排序查询 (ORDER BY)
分页查询 (LIMIT)

2.基本查询

查询多个字段：

SELECT 字段1,字段2,…… FROM 表名;
SELECT * FROM 表名;

设置别名：

SELECT 字段1 [AS 别名1],字段2 [AS 别名2]…… FROM 表名；

去除重复记录：

SELECT DISTINCT 字段列表 FROM 表名；

3.条件查询

条件查询：

SELECT 字段列表 FROM 表名 WHERE 条件列表；

在这里插入图片描述

逻辑运算符用于条件列表组装多个条件。

4.聚合函数

聚合函数：将一列数据作为一个整体，进行纵向计算。

函数	功能
count	统计数量
max	最大值
min	最小值
avg	平均值
sum	求和

语法：

SELECT 聚合函数(字段列表) FROM 表名;

注意：null值不参与聚合函数的运算。

5.分组查询

语法：

SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件];

WHERE与HAVING的区别：

执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。
判断条件不同：where不能对聚合函数进行判断，而having可以。

练习：

1.根据性别分组，统计男性员工和女性员工的数量

select gender,count(*) from emp group by gender;

2.查询年龄小于45的员工，并根据工作地址分组，获取员工数量大于等于3的工作地址

select count(*) from emp where age < 45 group by workaddress;
select workaddress,count(*) as address_count from emp where age < 45 group by workaddress having address_count >= 3;

遇到麻烦的查询语句，我们一次性写不出来，可以先写基础，然后一步一步的增加功能。

注意：

执行顺序: where > 聚合函数 > having
分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义

6.排序查询

排序查询：

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1, 字段2 排序方式2;

可以看到排序查询是支持多个字段排序的，多字段排序的意思是当第一个字段相同时，看第二个字段，以此类推。

排序方式：

ASC：升序（默认值）
DESC：降序

7.分页查询

语法：

SELECT 字段列表 FROM 表名 LIMIT 起始索引,每页记录数；

注意：

起始索引从0开始，起始索引= (查询页码 - 1)* 每页显示记录数
分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。
如果查询的是第一页数据，起始索引可以省略，直接简写为 limit 10。

练习：

1.查询第一页的员工数据，每页展示10条记录

select * from emp limit 0,10;

2.查询第二页的员工数据，每页展示10条记录

select * from emp limit 10,10;

五.`DCL`

1.管理用户

查询用户：

USE mysql;
SELECT * FROM user;

解释：MySQL的所有用户信息都存储在自带的mysql数据库下的user表中。

在这里插入图片描述

可以看到系统自带的有四个用户，我们的身份是root管理员，注意是用host主机和user用户名两个组合确定一个用户的。

创建用户：

CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

注意：localhost表示本地主机的意思，如果你想其他地方登录，可以使用%表示任意主机可以登录。

修改用户密码：

ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';

删除用户：

DROP USER '用户名'@'主机名';

2.权限控制

MySQL定义了很多种权限，常用的有以下几种：

在这里插入图片描述

查询权限：

SHOW GRANTS FOR '用户名'@'主机名';

授予权限：

GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';

撤销权限：

REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

注意：

多个权限之间，使用逗号分隔
授权时，数据库名和表名以使用 * 进行通配，代表所有

六.`SQL`补充

1.多表查询

前面部分的都是只涉及数据库的一个表，现在补充其他。

涉及一个表的查询，叫单表查询；用来涉及多个表的查询，称为连接查询。

用来连接两个表的条件称为连接条件或连接谓词。

交叉连接（广义笛卡儿积）：
```
SELECT 字段列表 FROM 表名1,表名2;
SELECT 字段列表 FROM 表名1 CROSS JOIN 表名2;
```
注意：这个是基础，就相当于输出笛卡尔积的结果，而后面其他的所有连接都相当于在此基础上做一些筛选，实际过程中该连接也很少使用，其中很多数据是没有意义的。

等值连接：

SELECT 字段列表 FROM 表名1,表名2 WHERE 表1.字段1 = 表2.字段2;

就是在交叉连接的基础上进行了where的等值条件判断

自身连接：一个表与其自己连接
```
SELECT 字段列表 FROM 表名 别名1，表名 别名2 WHERE 条件
```
注意：自身连接需要起别名以示区别，且同名属性需要使用别名前缀
复合条件连接：where子句中有多个条件连接

没什么好说的，有n个表连接那么有n-1个等值条件
内连接：等值连接的另一种写法
```
SELECT 字段列表 FROM 表名1 JOIN 表名2 ON 表1.字段1 = 表2.字段2;
```
相当于把,改成了join，把where改成了on
外连接：分为左连接、右连接、全连接

说不清楚，看视频：点击这里