MySQL基础

最新推荐文章于 2023-04-10 11:47:51 发布

周八营业的代码人

最新推荐文章于 2023-04-10 11:47:51 发布

阅读量798

点赞数 1

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

本文链接：https://blog.csdn.net/weixin_44320429/article/details/123562777

版权

数据库专栏收录该内容

3 篇文章 0 订阅

订阅专栏

MySQL数据类型

数值类型

字符串类型

日期类型

通用语法及分类

DDL

DDL——数据库操作

查询

-- 查询所有数据库
SHOW DATABASES;

-- 查询当前使用的数据库
SELECT DATABASE();

创建

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

删除

DROP DATABASE [IF EXISTS] 数据库名;

使用
```
USE 数据库名;
```

DDL——表操作

查询表

-- 查询当前数据库所有表
SHOW TABLES;

-- 查询表结构
DESC 表名;

-- 查询指定表的建表语句
SHOW CREATE TABLE 表名;

创建表

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

create table emp(
	id int,
    workno varchar(10) comment '编号',
    name varchar(10) comment '姓名',
    gender char(1) comment '性别',
    age tinyint unsigned comment '年龄',
    idcard varchar(18) comment '身份证号',
    entrydate date comment '入职时间'
) comment '员工表';

添加字段(ADD)

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

修改字段

修改数据类型

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

修改字段名和字段类型

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

删除字段
```
ALTER TABLE 表名 DROP 字段名;
```

修改表名

ALTER TABLE 表名 RENAME TO 新表名;

删除表

-- 删除表
DROP TABLE [IF EXISTS] 表名;

-- 删除表后再创建表，效果相当于仅删除数据但保留表结构
TRUNCATE TABLE 表名;

DML

添加数据

-- 1.给指定字段添加数据
INSERT INTO 表名(字段名1,字段名2,...) VALUE(值1, 值2,...);

-- 2.给全部字段添加数据
INSERT INTO 表名 VALUE (值1,值2,...);

-- 3.批量添加数据
INSERT INTO 表名(字段名1,字段名2,...) VALUE(值1,值2,...),(值1,值2,...),(值1,值2,...);
INSERT INTO 表名 VALUE(值1,值2,...),(值1,值2,...),(值1,值2,...);

注意：

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

修改数据
```
UPDATE 表名 SET 字段名1=值1,字段名2=值2,...[WHERE 条件];
```
注意：此处的where条件可以没有，若没有，则修改所有数据
删除数据
```
DELETE FROM 表名 [WHERE 条件];
```
注意：
- delete语句的条件可以没有，若没有，则删除整张表的所有数据；
- delete语句不能删除某一个字段的值

DQL

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

基本查询

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

-- 2.设置别名
SELECT 字段1 [AS 别名],字段2 [AS 别名]...FROM 表名;

-- 3.去除重复记录
SELECT DISTINCT 字段列表 FROM 表名;

条件查询

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

条件

聚合函数

介绍
- 将一列数据作为一个整体，进行纵向计算
常见聚合函数

语法

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

分组查询

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

where和having区别
- 执行实际不同：where是分组之前进行个过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤；
- 判断条件不同：where不能对聚合函数进行判断，而having可以。
注意
- 执行顺序：where > 聚合函数 > having；
- 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。

排序查询

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

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

注意：如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序。

分页查询

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

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

DQL语句执行顺序

DCL

DCL，全称为Data Control Language（数据控制语言）用来管理数据库用户、控制数据库的访问权限。

DCL-管理用户

-- 1.查询用户
USE mysql;
SELECT * FROM user;

-- 2.创建用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

-- 3.修改用户密码
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';

-- 4.删除用户
DROP USER '用户名'@'主机名';

注意
- 主机名可以使用 % 通配
- 这类SQL开发人员操作的比较少，主要是DBA（Datebase Administrator 数据库管理员）使用。

DCL-权限控制

MySQL中定义了很多种权限，但是常用的就以下几种：

查询权限

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

授予权限

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

撤销权限

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

注意：
- 多个权限之间，使用逗号分割
- 授权时，数据库名和表名可以使用*进行分配，代表所有

函数

函数是指一段可以直接被另一段程序调用的程序或代码。

字符串函数

MySQL中内内置了很多字符串函数，常用的几个如下：

-- concat：hellomysql
select concat('hello', 'mysql');

-- lower：hello
select lower('Hello');

-- upper：HELLO
select upper('Hello');

-- lpad：---01
select lpad('01', 5, '-');

-- rpad：01---
select rpad('01', 5, '-');

-- trim：Hello MySQL
select trim(' Hello MySQL ');

-- substring：llo My
select substring('Hello MySQL', 3, 6)

数值函数

常见的数值函数：

-- ceil：2
select ceil(1.1);

-- floor：1
select floor(1.9);

-- mod：1
select mod(5, 4);

-- rand
select rand();

-- round：2.35
select round(2.345, 2);

日期函数

常见的日期函数如下：

流程函数

流程函数也是很常用的一类函数，可以在SQL语句中实现条件筛选，从而提高语句的效率。

select if(false, 'ok', 'error');	-- 返回error
select ifnull('', 'default');	-- 返回空字符串
select ifnull(null, 'defalut');	-- 返回default

-- case when then else end
-- 需求：查询emp表中员工姓名和工作地址（北京/上海---->一线城市，其他---->二线城市）
select 
	name, 
	(case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end) as '工作地址'
from emp;

-- 案例：统计班级各个学院的成绩，展示的规则如下：
-- >= 85：优秀
-- >= 65：合格
-- 否则：不合格
select 
	id,
	name,
	(case when math >= 85 then '优秀' when math >= 65 then '合格' else '不合格' end) as '数学成绩';
	(case when english >= 85 then '优秀' when english >= 65 then '合格' else '不合格' end) as '英语成绩';
	(case when chinese >= 85 then '优秀' when chinese >= 65 then '合格' else '不合格' end) as '语文成绩';
from score;

约束

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

外键约束

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

添加外键语法

-- 创建表时添加外键
CREATE TABLE 表名(
	字段名 数据类型,
    ...
    [CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCE 主表(主表列名)
);

-- 修改表结构添加外键
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCE 主表(主表列名);

删除/更新行为

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

多表查询

概述：项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间存在着各种联系，基本上分为三种：一对一、一对多、多对多。
一对一
- 案例：用户和用户详情的关系
- 关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率
- 实现：在任意一方加入外键，关联另外一方的主键，并设置为唯一的（UNIQUE）。
一对多
- 案例：部门与员工的关系
- 关系：一个员工属于一个部门，一个部门包含多个员工
- 实现：在多的一方建立外键，指向一的一方的主键。
多对多
- 案例：学生与课程的关系
- 关系：一个学生选修多门课程，一门课程被多个学生选择
- 实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键。

多表查询概述

概述：从多张表中查询数据
笛卡尔积：笛卡尔积是指在数学中，两个集合A和B的所有组合情况。（在多表查询时，需要消除无效的笛卡尔积）
多表查询分类
- 连接查询
  - 内连接：相当于查询A、B交集部分的数据
```
-- 隐式内连接
SELECT 字段列表 FROM 表1,表2 WHERE 条件...;

-- 显式内连接: INNER JOIN ... ON ...
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件...;
```
  - 外连接
    - 左外连接：查询左表的所有数据，以及两张表交集部分的数据
```
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件...;
```
    - 右外连接：查询右表的所有数据，以及两张表交集部分的数据
```
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件...;
```
  - 自连接
```
SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件...;
```
    自连接查询，可以是内连接查询，也可以是外连接查询。
- 联合查询-union，union all
  - 对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。
```
SELECT 字段列表 FROM 表A...
UNION [ALL]
SELECT 字段列表 FROM 表B...;

-- union all直接将数据合并在一起，不会去重；union会去重
```
  对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。
- 子查询
  - SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询
```
SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);
```
  子查询外部的语句可以是INSERT / UPDATE / DELETE / SELECT任何一个。
  - 根据子查询结果不同，分为：
    - 标量子查询（子查询结果为单个值）
      
      常见的操作符：=、<>、>、>=、<、<=
    - 列子查询（子查询结果为一列）
      
      常见的操作符：IN、NOT IN、ANY、SOME、ALL
    - 行子查询（子查询结果为一行）
      
      常见的操作符：=、<>、IN、NOT IN
    - 表子查询（子查询结果为多行多列）
      
      常见的操作符：IN
  - 根据子查询位置，分为：WHERE之后、FROM之后、SELECT之后。

事务

事务简介

事务是一组操作的集合，他是一个不可分割的工作单位，事务会把所有操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

事务操作

查看/设置事务提交方式

SELECT @@autocommit;
SET @@autocommit = 0;	-- 为0表示手动提交

-- 也可以不设置事务提交方式
-- 手动开启一个事务
START TRANSACTION;
-- 或者使用
BEGIN;

提交事务
```
COMMIT;
```
回滚事务
```
ROLLBACK;
```

事务四大特性(ACID)

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

并发事务问题

事务隔离级别

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

-- 设置事务隔离级别
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

注意：事务的隔离级别越高，数据越安全，但是性能越低。

周八营业的代码人

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL基础

MySQL数据类型数值类型字符串类型日期类型通用语法及分类DDLDDL——数据库操作查询-- 查询所有数据库SHOW DATABASES;-- 查询当前使用的数据库SELECT DATABASE();创建CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则];删除DROP DATABASE [IF EXISTS] 数据库名;使用USE 数据库名;
复制链接

扫一扫