哇!MySQL中SQL数据库基础知识大汇总出炉了

前言

这是我听老师讲课做的笔记,考试要看的。 这是视频地址
作者:RodmaChen
关注我的csdn博客,更多Linux笔记知识还在更新
本人只在csdn写博客

配套这篇文章练习更香新手入门——MySQL中基于SQL语言增删改查等基础的练习

SQL是Structured QueryLanguage的缩写,即结构化查询语言。SQL是一门标准的计算机语言,用于访问和操作数据库,其主能包括数据定义、数据操纵、数据查询和数据控制。SQL已成为RDBMS的标准语言,但不同的RDBMS使用的SQL版本有一些差异,它们都有一些私有
扩展。

按照功能用途,可以将SQL语言分为4类:DDL、DML、DQL和DCL。
在这里插入图片描述

一. DDL——数据定义语言

数据库、表、视图等的建立、删除

  1. 查看已经有的数据库:show databases;
  2. 创建数据库:create database 数据库名;
  3. 删除数据库: drop database 数据库名;
  4. 创建数据表:create table 数据表名(表结构)
  5. 使用数据库:use 数据库名;
  6. 显示数据表:show tables;
  7. 查看数据表结构:desc 数据表;或 show columns from 数据表;
  8. 删除表:drop table 数据表;
  9. 对表进行添加、修改、删除:alter 命令,如下列:
alter table contacts add sex varchar(1);

二. DML——数据操纵语言

添加、删除和修改数据表中的记录

命令功能
insert into 数据表名(列表名)values(‘输入值’);插入数据
updata 数据表名 set 列表名='输入的值';修改数据
delete from 数据表名[where 列表名];

插入单条数据命令:

insert into   table_name (field1, field2, ..., fieldN)
values(value1, value2, ..., valueN);

插入多条数据命令:

insert into   table_name (field1, field2, ..., fieldN) 
values(valueA1, valueA2, ..., valueAN), (valueB1,valueB2, 
..., valueBN),, (valueN1, valueN2, ..., valueNN);

注意事项:
1、如果字段是字符型,值必须使用单引号或者双引号,如”value”;如果值本身带单引号或双引号,需要转义
2、如果所有列都要添加数据,insert into语句可以不指定列,即

INSERT INTO table_name VALUES 
(value1, value2, ..., valueN);

修改语句:

update table_name set field1=newValue1, 
field2=newValue2 [where Clause]

注意事项:
1、可以同时更新一个或多个字段
2、可以通过where子句来指定更新的范围,如果不带where,则更新数据表中的所有记录

delete语法:

delete from table_name [where Clause]

注意事项:
1、可以通过where子句来指定删除的范围,如果不带where,则删除数据表中的所有记录

三. DCL——数据控制语言

数据库对象的权限管理和事务管理

3. 1 DCL的介绍

我们知道,SQL语言按功能用途分为4类,分别是DDL、DML、DQL和DCL。其中,DCL是数据控制语言,主要用于管理用户和权限。在企业中,这部分工作通常是由DBA完成,一般开发人员很少接触。

DCL主要能做什么?

  1. 创建用户
  2. 删除用户
  3. 修改密码
  4. 给用户赋予权限
  5. 撤销用户权限

MySQL的权限体系大致分为5个层级,全局层级、数据库层级、表层级、列层级和子程序层级。

在这里插入图片描述
MySQL的权限信息主要存储在以下几张表中,当用户连接数据库时,MySQL会根据这些表对用户进行权限验证。

在这里插入图片描述

3.2 用户管理——user

注意!:在MySQL中,使用CREATE USER来创建用户,用户创建后没有任何权限。

  1. 创建用户
CREATE USER '用户名' [@'主机名'] [IDENTIFIED BY '密码'];

注意:MySQL的用户账号由两部分组成:用户名和主机名,即用户名@主机名,主机名可以是IP或机器名称,主机名为%表示允许任何地址的主机远程登录MySQL数据库。

  1. 删除用户
DROP USER '用户名' [@'主机名'];
  1. 修改密码
ALTER USER '用户名'@'主机名' IDENTIFIED BY '新密码';

3.3 权限管理——grant和revoke

在MySQL数据库中,使用grant命令授权,revoke命令撤销授权。

  1. 授权
grant all privileges on databaseName.tableName to '用户名' [@'主机名'] ;
  1. 撤销授权
revoke all privileges on databaseName.tableName from '用户名' [@'主机名'] ;
  1. 刷新权限
FLUSH PRIVILEGES;
  1. 查看权限
show grants for '用户名' [@'主机名'] ;

使用grant和revoke进行授权、撤销授权时,需要指定具体是哪些权限,这些权限大体可以分为3类,数据类、结构类和管理类

在这里插入图片描述

3.4 禁止root用户远程登录

在实际工作中,公司的DBA都会设置禁止root用户远程登录,请问这是为什么?

  1. root是MySQL数据库的超级管理员,几乎拥有所有权限,一旦泄露后果非常严重;
  2. root是MySQL数据库的默认用户,所有人都知道,如果不禁止远程登录,可以针对root用户暴力破解密码。

3.5 忘记root用户密码的解决方法

解决步骤

在这里插入图片描述
步骤一:关闭权限验证

mysqld --defaults-file="C:\ProgramData\MySQL\MySQL Server 8.0\my.ini" --console --skip-grant-tables --shared-memory

步骤二:修改root用户密码

MySQL关闭权限验证后,直接通过 mysql 命令即可连接到数据库,并可正常执行各类操作。

// 刷新权限
FLUSH PRIVILEGES;
//修改root用户的密码
ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';

四. DQL查询——数据查询语言

4.1 select简单查询

在SQL中,使用select语句来查询数据。不同的关系数据库,select语法会有细微差别,在MySQL官网 https://dev.mysql.com/doc/refman/8.0/en/select.html 可以查到支持的select语法。

格式:

select 列表名 from 数据表名;(* 表示全部列表名)

4.2 where条件查询

在SQL中,insert、update、delete和select后面都能带where子句,用于插入、修改、删除或查询指定条件的记录。

4.2.1 单条件查询

格式:

SELECT 列表名 from 数据表 where 列表名 运算符 value

在这里插入图片描述

4.2.2 多条件查询

在where子句中,使用and、or可以把两个或多个过滤条件结合起来。

格式:

select 列表名 from 数据表名 where 条件1 AND 条件2 OR 条件3

在这里插入图片描述

4.2.3 in和like在where中的使用

运算符 IN 允许我们在 WHERE 子句中过滤某个字段的多个值。

  1. where子句使用in语法
select column_name from table_name where column_name in(value1, value2,)

在where子句中,有时候我们需要查询包含xxx 字符串的所有记录,这时就需要用到运算符like

  1. where子句使用like语法
select 列表名 from 数据表名 where  列表名 like %value%

说明:

  • LIKE子句中的%类似于正则表达式中的*,匹配任意0个或多个字符
  • LIKE子句中的_匹配任意单个字符
  • LIKE子句中如果没有%_,就相当于运算符=的效果

4.3 MySQL常用函数讲解及用法

4.3.1 MySQL内置函数

我们通常说的MySQL函数指的是MySQL数据库提供的内置函数,包括数学函数、字符串函数、日期和时间函数、聚合函数、条件判断函数等,这些内置函数可以帮助用户更方便地处理表中的数据,简化用户的操作。

在这里插入图片描述

4.3.2 返回当前——日期和时间函数now()

函数now()用于返回当前的日期和时间。在实际应用中,大多数业务表都会带一个创建时间create_time字段,用于记录每一条数据的产生时间。在向表中插入数据时,就可以在insert语句中使用now()函数

在这里插入图片描述
示例如下:

insert into user(id, name, create_time) values(1, 'zhangsan', now());
4.3.3 指定的格式显示日期/时间——date_format()

函数date_format()用于以指定的格式显示日期/时间。在实际应用中,一般会按照标准格式存储日期/时间,如 2019-12-13 14:15:16 。在查询使用数据时,往往又会有不同的格式要求,这时就需要使用date_format()函数进行格式 转换。

在这里插入图片描述

示例如下:

select name, date_format(birthday, '%Y/%m/%d') from user;
4.3.4 聚合函数——计算值

聚合函数是对一组值进行计算,并返回单个值。
MySQL常用的聚合函数有5个,分别是count、sum、avg、min和max

在这里插入图片描述

4.3.5 处理NULL值——函数ifnull()

函数ifnull()用于处理NULL值。
ifnull(v1,v2),如果 v1 的值不为 NULL,则返回 v1,否则返回 v2。

在这里插入图片描述

4.3.6 判断条件语句——case when

case when是流程控制语句,可以在SQL语句中使用case when来获取更加准确和直接的结果。

SQL中的case when类似于编程语言中的if else或者switch。

列:
在这里插入图片描述

4.4 查询结果排序与分页

按某数值排序,从高到低,从大到小等等

4.4.1 order by 排序
  1. 在SQL中,使用order by对查询结果集进行排序,可以按照一列或多列进行排序。
  2. order by语法

SELECT column_name1, column_name2 FROM table_name1, table_name2ORDER BY column_name, column_name [ASC|DESC]

说明:

  1. ASC表示按升序排列,DESC表示按降序排列。
  2. 默认情况下,对列按升序排列。

第一页:获取20条数据
第二页:从20条数据开始获取20条
第三页:从第40条开始获取20条

4.4.2 limit 分页查询
  1. SELECT语句中使用LIMIT子句来约束要返回的记录数,通常使用LIMIT实现分页。
  2. limit语法
    SELECT column_name1, column_name2 FROM table_name1, table_name2 LIMIT [offset,] row_count

说明:

  1. offset指定要返回的第一行的偏移量。第一行的偏移量是0,而不是1。
  2. row_count指定要返回的最大行数。

4.5 数据的分组和筛选

4.5.1 group by——分组

从字面上理解,group by表示根据某种规则对数据进行分组,它必须配合聚合函数进行使用,对数据进行分组后可以进行count、sum、avg、max和min等运算。

group by语法

SELECT column_name, aggregate_function(column_name) FROM table_name GROUP BY column_name

说明:

  1. aggregate_function表示聚合函数。
  2. group by可以对一列或多列进行分组。
4.5.2 having——筛选各组数据

在 SQL 中增加 HAVING 子句原因是,WHERE 关键字无法与聚合函数一起使用。HAVING 子句可以对分组后的各组数据进行筛选。

having语法

SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator 
value GROUP BY column_name HAVING  aggregate_function(column_name) operator value
4.5.3 group_concat——显示统计数量列表

使用group by可以分组统计每个部门有多少员工。假如,除了统计每个部门的员工数量之外,还想知道具体是哪些员工(员工列表),又该如何实现呢?

在这里插入图片描述

group_concat 配合group by一起使用,用于将某一列的值按指定的分隔符进行拼接,MySQL默认的分隔符为逗号。

group_concat语法

group_concat([distinct] column_name [order by column_name asc/desc ] [separator '分隔符'])

在这里插入图片描述

4.6 去除重复数据——distinct

distinct用于在查询中返回列的唯一不同值(去重复),支持单列或多列。在实际的应用中,表中的某一列含有重复值是很常见的,如employ表的dept列。如果在查询数据时,希望得到某列的所有不同值,可以使用distinct。

distinct语法

select distinct 列表名1,列表请2  from 数据表名;

在这里插入图片描述

4.7 表连接(内连接、外连接、自连接)

多张表,表连接
主要学习内连接和左连接

  1. 什么是表连接

表连接(JOIN)是在多个表之间通过一定的连接条件,使表之间发生关联,进而能从多个表之间获取数据

  1. 表连接语法
SELECT table1.column, table2.column FROM table1, 
table2 WHERE table1.column1 = table2.column2;
  1. 表连接的几种方式

在这里插入图片描述

  1. 各种表连接的区别

在这里插入图片描述

交叉连接(cross join):没有用where子句的交叉连接将产生笛卡尔积,第一个表的行数乘以第二个表的行数等于笛卡尔积和结果集的大小。

  1. 内连接

在这里插入图片描述

  1. 左连接

在这里插入图片描述

4.8 自连接查询的场景及使用

自连接是一种特殊的表连接,它是指相互连接的表在物理上同为一张表,但是逻辑上是多张表。自连接通常用于表中的数据有层次结构,如区域表、菜单表、商品分类表等。

自连接语法

SELECT A.column, B.column FROM table A, table B WHEREA.column = B.column;

在这里插入图片描述

4.9 子查询EXISTS和IN的使用

如果运算符 in 后面的值是来源于某个查询结果,并非是指定的几个值,这时就需要用到子查询。子查询又称为内部查询或嵌套查询,即在 SQL 查询的 WHERE 子句中嵌入查询语句。

子查询in语法

SELECT column_name FROM table_name 
WHERE  column_name `IN`(
SELECT column_name FROM table_name [WHERE]
);

在这里插入图片描述

EXISTS是子查询中用于测试内部查询是否返回任何行的布尔运算符。将主查询的数据放到子查询中做条件验证,根据验证结果(TRUE 或 FALSE)来决定主查询的数据结果是否保留。

where子句使用exists语法

SELECT column_name1 FROM table_name1 
WHERE EXISTS (SELECT * FROM table_name2 
WHERE condition);

在这里插入图片描述

本人博客:https://blog.csdn.net/weixin_46654114
本人b站求关注:https://space.bilibili.com/391105864
转载说明:跟我说明,务必注明来源,附带本人博客连接。

请给我点个赞鼓励我吧
在这里插入图片描述

©️2020 CSDN 皮肤主题: Age of Ai 设计师:meimeiellie 返回首页