Mysql从入门到精通——Mysql知识点总结(基础篇)

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群版 2核4GB 100GB
推荐场景:
搭建个人博客
云数据库 RDS MySQL,高可用版 2核4GB 50GB
简介: Mysql从入门到精通——Mysql知识点总结(基础篇)

SQL语句类型

  1. DDL:数据定义语言,用来定义数据库对象(数据库,表,字段)
  2. DML:数据操作语言,对数据库表中的数据进行增删改
  3. DQL:数据查询语言,用来查询数据库中表的记录
  4. DCL:数据控制语言,用来创建数据库对象,控制数据库的访问权限

DDL——数据库操作

操作数据库

  • 查看当前的所有的数据库
SHOW DATABASE;
  • 创建数据库
CREATE DATABASE 数据库名称 CHARSET utf8 COLLATE utf8_general_ci; 
  • 删除数据库
DROP DATABASE  数据库名;

进入文件(数据库)

USE 数据库名;

操作表结构

查询当前数据库中的所有表
show tables
查询表结构
describe 表名
查询指定表的建表语句:
show create table `表名
创建表结构
create table `表名`(
  字段 字段类型 comment 注释
)

示例:

create table user_list(
    id int primary key comment "编号",
    name varchar(50) comment "姓名",
    age int comment "年龄",
    gender varchar(1) comment "性别"
)comment "使用者列表";
往表中添加字段
alter table 表名 add 字段名 类型(长度) [comment注释] [约束]

示例:

alter table user_list add Nickname varchar(50) comment "绰号"
修改表
修改表的数据类型
alter table 表名 modify 字段名 数据类型

示例:

alter table user_list MODIFY Nickname varchar(10)
修改字段名和字段类型
alter table 表名 change 旧字段 新字段 类型

示例:

alter table user_list change Nickname nickname varchar(6)
删除表的字段
alter table 表名 drop 字段名 

示例:

alter table user_list drop  nickname
修改表名
alter table 表名 rename to 新表名

示例:

alter table user_list rename to User_List
删除表
-- 删除表
drop table [if exists]表名
-- 删除指定表,并且重新创建表
truncate table 表名

DML——表内数据的增删改

添加数据

-- 给指定字段添加数据
insert into 表名(字段名1,字段名2)values(值1,值2)
-- 给全部字段添加数据
insert into 表名 values(值1,值2)
-- 批量添加数据
insert into 表名(字段1,字段2...)values(值1,值2)(值1,值2)
-- 将查找出来的数据加入表中
insert into 表名(字段1,字段2...)values select SELECT 字段1,字段2... FROM 表名

补充:

  • 使用replace into来插入数据
    replace into 首先尝试插入数据到表中,如果发现表中已经有此行数据(根据主键或者唯一索引判断)则先删除此行数据,然后插入新的数据;否则,直接插入新数据。,示例:
replace into examination_info(exam_id,tag,difficulty,duration,release_time)
values
(9003,'SQL', 'hard', 90, '2021-01-01 00:00:00')

数据的更新与删除

数据的更新
update 表名 set 字段名1=值1,字段名2=值2 ... (where 条件)

示例:

update user_list 
set  name='luoyu',age=16,gender='男'
where name='fengxu';
数据的删除:
delete 表名 where 条件
truncate 表名

示例:

delete from user_list where age is null
truncate  exam_record

补充:

delete,drop,truncate的区别

1.DROP TABLE 清除数据并且销毁表,是一种数据库定义语言(DDL Data Definition Language), 执行后不能撤销,被删除表格的关系,索引,权限等等都会被永久删除。

2.TRUNCATE TABLE 只清除数据,保留表结构,列,权限,索引,视图,关系等等,相当于清零数据,是一种数据库定义语言(DDL Data Definition Language),执行后不能撤销。

3.DELETE TABLE 删除(符合某些条件的)数据,是一种数据操纵语言(DML Data Manipulation Language),执行后可以撤销。(还不太明白怎么撤销TT,在什么情况下可以撤销,求大神指点。

运行速度一般DROP最快,DELETE最慢,但是DELETE最安全。

DQL——数据的查询

DQL语法汇总:

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

基本查询

select 字段1,字段2,字段3 from 表名
select 字段1(as 别名),字段2,字段3 from 表名
-- 去重
select distinct 字段列表 from 表名列表

条件查询

select 字段列表 from 表名列表 where 条件列表

聚合函数

count  -- 统计数量
max    -- 最大值
min    -- 最小值
avg    -- 求平均值
sum    -- 求和
select 聚合函数(字段列表) from 表名

示例代码;

select avg(age) from emoplyee

分组查询

sql语句中我们可以利用group by来创建并且用having来进行分组过滤

注意点

  • where与having的区别:
  1. where是分组前进行过滤,不满足where条件,不参加分组,having是分组后对结果进行过滤
  2. where不能对聚合函数进行判断,但是having可以
  • 分组之后,having查询的字段一般为聚合函数与分组字段,查询其他字段无意义

语句模板:

select 目标字段 
from 表名 
(where 条件)
group by 分组
having 过滤条件

示例:

select count(*),workaddress
from emoplyee
where emoplyee.age<45
group by workaddress
having count(*)>=2

排序查询

select 字段列表 from 表名列表 order by 字段1 排序方式,字段2 排序方式

补充

排序方式:

  • ASC:升序
  • DESC:降序

示例代码:

select * from emoplyee order by age ASC

分页查询

select 字段列表 from 表名 limit 起始索引,查询记录数;

示例:

select * from emoplyee limit 4,3;

DQL的执行顺序

在讲解SQL语句的执行顺序之前我们可以再来看一下最开始我们所说的SQL语句的编写顺序:

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

而它的执行顺序其实如下所示:

select 字段列表     -- 4
from 表名列表   -- 1
where 条件列表    -- 2
group by 分组字段列表   -- 3
having 分组后条件列表    -- 3
order by 排序字段列表   -- 5
limit 分页参数    -- 6

DCL——数据控制(实现对数据库用户的管理,控制数据的访问权限)

用户管理

查询用户
use mysql;
select * from user;
创建用户
create user '用户名'@'主机名' identified by
修改用户密码
alter user '用户名'@'主机名' identified with mysql_native_password by '1234';
删除用户
drop user '用户名'@'主机名'

权限控制

MySQL中定义了很多种权限,常用的主要有以下几种:

查询权限
show grants for '用户名'@'主机名'
授予权限
grant 权限列表 on 数据库.表名 to '用户名'@'主机名'
撤销权限
remove 权限列表 on 数据库.表名 from '用户名'@'主机名'

注意:多个权限之间使用逗号分隔

函数

字符串函数

示例:

update emoplyee
set  workno =lpad(workno,5,'0')
where 1=1;

数值函数

常用的数值函数

ceil(x)   -- 向上取整
floor(x)  -- 向下取整
mod(x,y)  -- 返回x/y的余数
rand()    -- 返回0-1的随机数
round(x,y) -- 返回x四舍五入的值,保留y位小数

示例函数:我们可以生成一个六位数的验证码

select round(rand()*100000,0)

日期函数

curdate() -- 返回当前日期
curtime() -- 返回当前时间
now()     -- 返回当前的时间与日期
year(date)    -- 获取指定date的年
month(date)   -- 获取指定date的月
day(date)     -- 获取指定date的日
date_add(date,insert expr type) -- 返回一个时间间隔expr后的时间值
datediff(date1,date2)  -- 返回两个日期间隔的天数

示例代码:

select curdate();
select curtime();
select datediff('2004-09-03','2000-01-01');
select now();
select YEAR(now());
select month(now());
select day(now());
select date_add(now(),INTERVAL 70 DAY );

流程控制函数

if(value,t,f) -- 如果value为true返回t,否则f
ifnull(value1,value2) -- 如果value1不为空返回value1,否则value2
case when[val1]then[res1]...else[default] end -- 如果value1为真,返res1 ... 否则返回default默认值(参考switch语句)
case [expr] when [val1] then [res1] ... else[default] end -- (参考if-else语句)

约束

概述

概念

约束指的是作用于表中字段的规则,用于限制表中数据

目的

保证数据库中数据完整,正确且有效

约束的分类

not null -- 非空约束
unique  -- 唯一约束
primary key -- 一行数据的唯一标识,要求不空白且唯一
default -- 默认约束(字段值未指定则使用默认值)
check   -- 检查约束(保证字段满足某一条件)
foreign key -- 外键约束(多表联结时再仔细介绍)

示例:

create table my_list(
    id int primary key auto_increment comment '主键',
    name varchar(10) not null ,unique (name),
    age int check(age>0 and age<=120),
    status char(1) default '1',
    gender char(1)
);

外键约束

外键概念

外键让两张表之间的数据建立连接,进而保证数据的一致与完整性,比如说员工表中有员工所属部门的编号,而对应编号的部门在另一张表上面。如下所示:

这时候我们就需要使用外键将它们连接在一起(注意:有外键的表是子表)

外键语法

外键的添加
-- 第一种
create table 表名(
    字段1 类型1
    ... 
    [constraint] 表名 add constraint 外键名称 foreign key(外键字段名) references 主表(主表列名)
);
-- 第二种
alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表(主表列名)

示例代码:

alter table emoplyee add constraint emp_dept_id foreign key (dept_id) references dept(id);
删除外键
alter table 表名 drop foreign key 外键名称
外键的删除与更新行为

一般而言,外键的删除/更新状态主要有以下几种:

alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表(主表列) on update 对应状态 on delete 对应状态;

备注 :mysql所使用的引擎是Innodb

多表查询

多表关系

前言

我们在开发的时候,需要对数据库的表结构进行设计,由于不同的情况与需求,我们需要分析并设计不同的表结构,同时由于各个表结构之间也存在着各种联系,基本上有以下几种结构:

  • 一对多
  • 多对多
  • 一对一

一对多

实现:在多的一方建立外键,指向一的一方的外键

实现:

alter table employees add constraint emplpyee_dept foreign key (DepartmentID) references departments(DepartmentID);

多对多

实现:创建第三张中间表,中间表至少包含两个外键,用来关联两边主键

create table student(
    id int auto_increment primary key comment '主键',
    name varchar(10) not null comment '名字',
    no varchar(20) not null comment  '学生编号'
)comment '学生表';
create table course(
    id int auto_increment primary key comment '主键',
    name varchar(20) comment '课程名称'
) comment '课程表';
create table student_course(
    id int auto_increment primary key comment '主键',
    studentid int not null comment '学生编号',
    couseid int not null comment '课程编号',
    constraint fk_course foreign key (couseid) references course(id),
    constraint fk_student foreign key (studentid) references student(id)
)comment '中间表'

一对一

应用场景:一般用于单表拆分,把基础字段放到一张表里面,其他详细信息放到另一张表里面

实现:

在任意一方加入外键,关联另一方的主键,并设置外键约束为unique

create table tb_user(
    id int auto_increment primary key comment '主键',
    name varchar(10) not null comment  '姓名',
    age int not null comment '年龄',
    gender int not null comment '性别:1为男,2为女',
    phone_number varchar(20) not null comment '电话号码'
);
create table tb_user_edu(
    id int auto_increment primary key comment '主键',
    degree varchar(10) not null comment '学历',
    major varchar(10) not null comment '专业',
    primary_school varchar(30) not null comment '小学',
    middle_school varchar(30) not null comment '高中',
    univetsity varchar(30) not null comment '大学',
    userid int not null comment '对应编号',
    constraint fk_userid foreign key (userid) references tb_user(id)
)

多表查询

概述

多表查询一般指的是我们从多张表里面查询数据,它的风雷主要有以下几种:

  1. 连接查询
  • 内连接:查询多个表之间的交集
  • 外连接
  • 左外连接:查询左表所有数据以及两张表交集部分数据
  • 右外连接:查询右表所有数据以及两张表交集部分数据
  • 自连接:当前表与自身的
  1. 子查询

内连接

-- 隐式内连接
select 字段列表 from 表1,表2 where ...
-- 显式内连接
select 字段列表 from 表1 inner join 表2 on 筛选条件

外连接

-- 左外连接
select 字段列表 from 表1 left outer join 表2 on 筛选条件...;
-- 右外连接
select 字段列表 from 表1 right outer join 表2 on 筛选条件

自连接

select 字段列表 from 表1 join 表1 on 筛选条件...

联合查询(union)

select 字段列表 from 表1 ...
union(all)    -- 加all不去重
select 字段列表 from 表2 ...

注意:联合查询的多张表的列数必须保持一致,字段类型也需要保持一致

子查询

子查询的概念

我们在sql语句中嵌套select语句,称为嵌套查询,又叫子查询

select * from t1 where column1=(select column1 from t2)

注意: 子查询外部的语句可以是insert/update/select的任意一个

子查询的分类
  • 标量子查询(子查询的结果为单个值)
  • 列子查询(子查询的结果为一列)
  • 行子查询(子查询的结果为一行)
  • 表子查询(子查询的结果为多行多列)
标量子查询

示例:

查找销售部所有员工的信息(员工信息和部门信息不在一张表中)

select *
from employees
where dept_id=(select id from dept where name='销售部')
列子查询

示例:

查询比销售部所有员工工资都高的员工信息

select *
from employees
where salary>all(select salary from employees where dept_id=select id from dept where name='销售部')
行子查询

示例:

查找与a工资和直属领导相同的员工

select * 
from employees
where (salary,manageid)=(select salary,manageid from employees where name='a')
表查询

基本与行子查询类似,这里不做赘述。

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
12天前
|
SQL 关系型数据库 MySQL
「Python入门」python操作MySQL和SqlServer
**摘要:** 了解如何使用Python的pymysql模块与MySQL数据库交互。首先,通过`pip install pymysql`安装模块。pymysql提供与MySQL的连接功能,例如创建数据库连接、执行SQL查询。在设置好MySQL环境后,使用`pymysql.connect()`建立连接,并通过游标执行SQL(如用户登录验证)。注意防止SQL注入,使用参数化查询。增删改操作需调用`conn.commit()`来保存更改。pymssql模块类似,但导入和连接对象创建略有不同。
15 0
「Python入门」python操作MySQL和SqlServer
|
17天前
|
SQL 关系型数据库 MySQL
【MySQL从入门到精通】常用SQL语句分享
【MySQL从入门到精通】常用SQL语句分享
27 2
|
21天前
|
关系型数据库 MySQL 数据库
『Django』模型入门教程-操作MySQL
一个后台如果没有数据库可以说废了一半。日常开发中大多数时候都在与数据库打交道。Django 为我们提供了一种更简单的操作数据库的方式。 在 Django 中,模型(Model)是用来定义数据库结构的类。每个模型类通常对应数据库中的一个表,类的属性对应表中的列。通过定义模型,Django 的 ORM(Object-Relational Mapping)可以将 Python 对象映射到数据库表,并提供一套 API 来进行数据库操作。 本文介绍模型的用法。
|
26天前
|
Java 关系型数据库 MySQL
Mybatis入门之在基于Springboot的框架下拿到MySQL中数据
Mybatis入门之在基于Springboot的框架下拿到MySQL中数据
20 4
|
5天前
|
存储 关系型数据库 MySQL
MySQL小白教程:从入门到查询高手
MySQL小白教程:从入门到查询高手
|
1月前
|
SQL 存储 监控
精通MySQL:从入门到精通的全方位指南
第一章:MySQL入门 1.1 MySQL概述 简要介绍MySQL的历史、特点和应用场景
|
23天前
|
关系型数据库 MySQL 数据库
轻松入门:使用Docker安装MySQL数据库的完全指南
轻松入门:使用Docker安装MySQL数据库的完全指南
|
28天前
|
SQL 关系型数据库 MySQL
MySQL数据库数据模型概念入门及基础的SQL语句2024
MySQL数据库数据模型概念入门及基础的SQL语句2024
22 0
|
1月前
|
SQL 关系型数据库 MySQL
精通MySQL:从入门到精通
第一章:MySQL入门 1.1 MySQL简介 简要介绍MySQL的历史、发展、特点和用途
|
1月前
|
存储 关系型数据库 MySQL
MySQL基础指南:从入门到精通
MySQL基础指南:从入门到精通

推荐镜像

更多