当前位置:   article > 正文

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

mysql 从入门到精通

参考视频

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

题单推荐

入门

进阶

SQL语句类型

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

DDL——数据库操作

操作数据库

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

进入文件(数据库)

USE 数据库名;
  • 1

操作表结构

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

示例:

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

示例:

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

示例:

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

示例:

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

示例:

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

示例:

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

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 表名

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12

补充:

  • 使用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')
    
    • 1
    • 2
    • 3

数据的更新与删除

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

示例:

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

truncate 表名
  • 1
  • 2
  • 3

示例:

delete from user_list where age is null

truncate  exam_record
  • 1
  • 2
  • 3

补充:

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 分页参数
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

基本查询

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

条件查询

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

在这里插入图片描述

聚合函数

count  -- 统计数量
max    -- 最大值
min    -- 最小值
avg    -- 求平均值
sum    -- 求和

select 聚合函数(字段列表) from 表名
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

示例代码;

select avg(age) from emoplyee
  • 1

分组查询

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

注意点

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

语句模板:

select 目标字段 
from 表名 
(where 条件)
group by 分组
having 过滤条件
  • 1
  • 2
  • 3
  • 4
  • 5

示例:

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

排序查询

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

补充

排序方式:

  • ASC:升序

  • DESC:降序

示例代码:

select * from emoplyee order by age ASC
  • 1

分页查询

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

示例:

select * from emoplyee limit 4,3;
  • 1

DQL的执行顺序

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

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

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

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

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

用户管理

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

权限控制

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

在这里插入图片描述

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

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

函数

字符串函数

在这里插入图片描述

示例:

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

数值函数

常用的数值函数

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

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

select round(rand()*100000,0)
  • 1

日期函数

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

示例代码:

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 );
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8

流程控制函数

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语句)
  • 1
  • 2
  • 3
  • 4

约束

概述

概念

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

目的

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

约束的分类

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

示例:

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)
);
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

外键约束

外键概念

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

在这里插入图片描述

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

外键语法

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

-- 第二种
alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表(主表列名)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9

示例代码:

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

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

在这里插入图片描述

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

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

多表查询

多表关系

前言

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

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

一对多

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

在这里插入图片描述

实现:

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

多对多

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

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 '中间表'

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21

一对一

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

在这里插入图片描述

实现:

在任意一方加入外键,关联另一方的主键,并设置外键约束为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
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17

多表查询

概述

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

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

内连接

-- 隐式内连接
select 字段列表 from1,表2 where ...

-- 显式内连接
select 字段列表 from1 inner join2 on 筛选条件
  • 1
  • 2
  • 3
  • 4
  • 5

外连接

-- 左外连接
select 字段列表 from1 left outer join2 on 筛选条件...;

-- 右外连接
select 字段列表 from1 right outer join2 on 筛选条件
  • 1
  • 2
  • 3
  • 4
  • 5

自连接

select 字段列表 from1 join1 on 筛选条件...
  • 1

联合查询(union)

select 字段列表 from1 ...
union(all)    -- 加all不去重
select 字段列表 from2 ...
  • 1
  • 2
  • 3

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

子查询

子查询的概念

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

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

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

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

示例:

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

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

在这里插入图片描述

示例:

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

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

示例:

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

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

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

声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/秋刀鱼在做梦/article/detail/769188?site
推荐阅读
相关标签
  

闽ICP备14008679号