MySQL(基础篇)——SQL+DDL+DML+DQL+DCL+函数+约束+多表联查+事务

进击的web

已于 2023-05-04 10:32:24 修改

阅读量225

点赞数 1

分类专栏：数据库文章标签： mysql 数据库 sql

于 2023-04-18 20:09:05 首次发布

本文链接：https://blog.csdn.net/m0_54283213/article/details/130229895

版权

数据库专栏收录该内容

2 篇文章 0 订阅

订阅专栏

一.MySQL学习（基础篇）

文章目录

- 一.MySQL学习（基础篇）

Ⅰ.概念

数据库(DataBase DB)：存储数据的仓库，数据有组织存储
数据库管理系统(DataBase Management System DBMS)：操纵、管理数据库的大型软件
SQL(Structured Query Language)：操作关系型数据库的编程语言，定义了一套操作关系型数据库的统一标准

请添加图片描述

Ⅱ.SQL

1.SQL通用语法

SQL语句可以单行或多行书写，以分号结尾
SQL语句可以使用空格/缩进来增强语句的可读性
MySQL数据库的SQL语句不区分大小写，建议使用大写
注释：
- 单行注释：–注释内容或 #注释内容（MySQL独有）
- 多行注释：/* 注释内容 */

2.SQL分类

DDL(Data Definition Language)：
- 数据定义语言，用来定义数据库对象（数据库，表，字段）
DML(Data Manipulation Language)：
- 数据操作语言，用来对数据库表中的数据进行增删改
DQL(Data Query Language)：
- 数据查询语言，用来查询数据库中表的记录
DCL(Data Control Language)：
- 数据控制语言，用来创建数据库用户、控制数据库的访问权限

3.DDL

1.数据库操作

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

创建

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

删除
```
DROP DATABASE[IF EXISTS]数据库名;
```
使用
```
USE 数据库名;
```

2.表操作

1.查询

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

2.创建

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

3.数据类型

double(精度,标度);

在这里插入图片描述

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-M4SQuMbK-1681819203978)(D:/typora/图片/image-20230418114432586.png)]

4.修改

添加

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

#举例
ALTER TABLE emp ADD nickname varchar(20) COMMENT `昵称`;

修改

修改数据类型

ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);

修改字段名和字段类型

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

#举例
ALTER TABLE emp CHANGE nickname username varchar(30) COMMENT`昵称`;

修改表名

ALTER TABLE 表名 RENAME TO 新表名;

#举例
ALTER TABLE emp RENAME TO employee;

删除

删除表

DROP TABLE[IF EXISTS] 表名;

删除指定表，并重新创建该表
```
TRUNCATE TABLE 表名;
```

4.DML

1.添加数据

给指定字段添加数据

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

给全部字段添加数据

INSERT INTO 表名 VALUES(值1,值2,...),(值1,值2,...),(值1,值2,...);

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

例子

INSERT INTO employee(id,workno,name,gender,age,idcard,entrydate) VALUES (1,'1','Itcast','男',10,'1302531265432652316','2000-01-01');

2.修改数据

修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据
```
UPDATE 表名 SET 字段名=值1,字段名2=值2,...[WHERE 条件];
```

3.删除数据

DELETE语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据
DELETE语句不能删除某一个字段的值（可以使用UPDATE）-
```
DELETE FROM 表名[WHERE 条件];
```

5.DQL

1.语法

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

2.基本查询

查询多个字段

#查询多个字段
SELECT 字段1,字段2,字段3... FROM 表名;
#查询所有字段
SELECT * FROM 表名;

设置别名

SELECT 字段1[AS 别名1],字段2[AS 别名2] ... FROM 表名;
#AS可以去掉

SELECT workaddress AS '工作地址' FROM emp;
SELECT workaddress '工作地址' FROM emp;

去除重复记录

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可以。
注意
- 执行顺序：where > 聚合函数 > having
- 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义

6.排序查询

语法

#如果是多字段排序，当地一个字段值相同时，才会根据第二个字段进行排序
SELECT 字段列表	FROM 表名 ORDER BY 字段1 排序方式1,字段2 排序方式2;

排序方式
- ASC：升序（默认值）
- DESC：降序

7.分页查询

语法

SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询记录数;

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

8.执行顺序

6.DCL

1.管理用户

查询用户
```
USE mysql;
SELECT * FROM user;
```

创建用户

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

修改用户密码

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

删除用户
```
DROP USER `用户名` @ `主机名`;
```
注意
- 主机名可以使用%通配
- 这类SQL开发人员操作的比较少，主要是DBA(Database Administrator 数据库管理员) 使用

2.权限控制

在这里插入图片描述

查询权限

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

授予权限

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

撤销权限

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

Ⅲ.函数

1.字符串函数

2.数值函数

在这里插入图片描述

3.日期函数

在这里插入图片描述

4.流程函数

例子1

例子2

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7anjWaoW-1681819203982)(D:/typora/图片/image-20230418162511504.png)]

Ⅳ.约束

1.概述

概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据
目的：保证数据库中数据的正确、有效性和完整性

2.常见外键约束

在这里插入图片描述

注意：约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束

3.外键约束

概念：外键用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。

添加外键

CREATE TABLE 表名(
		字段名	数据类型,
		...
		[CONSTRAINT] [外键名称] FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名)
);

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名);

删除外键

ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

在这里插入图片描述

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名(主表字段名) ON UPDATE CASCADE ON DELETE CASCADE;

Ⅴ.多表查询

1.连接查询

1.内连接(查询的是两张表交集的部分)

隐式内连接

SELECT 字段列表 FROM 表1,表2 WHERE 条件...;

显示内连接

SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件...;

2.外连接

左外连接——表1(左表)的所有数据

SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件...;

右外连接——表2(右表)的所有数据

SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件...;

3.自连接

可以是内连接查询，也可以是外连接查询

语法

SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件...;

4.联合查询

联合查询的多张表的列数必须一致，字段类型也需要一致
union all会将全部的数据直接合并在一起，union会对合并之后的数据去重

语法

SELECT 字段列表 FROM 表A ...
UNION[ALL]
SELECT 字段列表 FROM 表B ...;

5.子查询/嵌套查询

概念：SQL语句中嵌套SELECT语句

语法:

SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);

子查询的外部语句可以是INSERT/UPDATE/DELETE/SELECT的任何一个
分类
- 按结果分类
  - 标量子查询(子查询结果为单个值)
  - 列子查询(子查询结果为一列)
  - 行子查询(子查询结果为一行)
  - 表子查询(子查询结果为多行多列)
- 按位置分类
  - WHERE之后
  - FROM之后
  - SELECT之后

1.标量子查询

返回的结果是单个值(数字、字符串、日期等)，最简单的形式

常用的操作符：= <> > >= < <=

SELECT * FROM emp WHERE dept_id = (SELECT id FROM dept WHERE name = '销售部');

SELECT * FROM emp WHERE entrydate > (SELECT entrydate FROM emp WHERE name = '东方白')

2.列子查询

查询返回的结果是一列(可以是多行)
常用操作符：
- IN：在指定的集合范围内，多选一
- NOT IN：不在指定的集合范围内
- ANY：子查询返回列表中，有任意一个满足即可
- SOME：与ANY等同，使用SOME的地方都可以使用ANY
- ALL：子查询返回列表的所有值都必须满足

SELECT * FROM emp WHERE dept_od IN (SELECT id FROM dept WHERE name = '销售部' or name = '市场部');

SELECT * FROM emp WHERE salary > ALL(SELECT salary FROM emp WHERE dept_id = (SELECT id FROM dept WHERE name = '销售部'));

SELECT * FROM emp WHERE salary > ANY(SELECT salary FROM emp WHERE dept_id = (SELECT id FROM dept WHERE name = '研发部'));

3.行子查询

子查询返回的结果是一行(可以是多列)
常用的操作符：=、<> 、IN、NOT IN

SELECT * FROM emp WHERE (salary,managerid) = (SELECT salary,managerid FROM emp WHERE name = '张无忌');

4.表子查询

子查询返回的结果是多行多列、
常用操作符：IN

SELECT * FROM emp WHERE (job,salary) IN (SELECT job,salary FROM emp WHERE name = '鹿杖客' OR name = '宋远桥'); 

SELECT e.*,d.* FROM (SELECT * FROM emp WHERE entrydate > '2006-01-01') e LEFT JOIN dept d ON e.dept_id = d.id;

Ⅵ.事务

1.简介

事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。
默认MySQL的事务是自动提交的，也就是说，当执行一条DML语句，MySQL会立即隐式的提交事务。

2.事务操作

查看/设置事务提交方式

SELECT @@autocommit;
SET @@autocommit = 0;

开启事务
```
START TRANSACTION 或 BEGIN
```
提交事务
```
COMMIT;
```
回滚事务
```
ROLLBACK;
```

3.事务的四大特性

原子性(Atomicity)：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
一致性(Consistency)：事务完成时，必须使所有的数据都保持一致状态。
隔离性(Isolation)：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
持久性(Durability)：事务一旦提交或回滚，它对数据库中的数据的改变使永久的。

4.并发事务问题

脏读：一个事务读到另外一个事务还没有提交的数据
不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。
幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了“幻影”。

5.事务隔离级别

在这里插入图片描述

查看事务隔离级别
```
SELECT @@TRANSACTION_ISOLATION;
```

设置事务隔离级别

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE};

SESSION：当前客户端窗口有效
GLOBAL：所有客户端会话窗口有效

进击的web

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL(基础篇)——SQL+DDL+DML+DQL+DCL+函数+约束+多表联查+事务

概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据目的：保证数据库中数据的正确、有效性和完整性事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。默认MySQL的事务是自动提交的，也就是说，当执行一条DML语句，MySQL会立即隐式的提交事务。
复制链接

扫一扫

专栏目录