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

参考视频

黑马程序员 MySQL数据库入门到精通i

题单推荐

入门

进阶

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. 连接查询
   • 内连接：查询多个表之间的交集
   • 外连接
     • 左外连接：查询左表所有数据以及两张表交集部分数据
     • 右外连接：查询右表所有数据以及两张表交集部分数据
   • 自连接:当前表与自身的
2. 子查询

内连接

-- 隐式内连接
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')

表查询

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