MySQL基础知识

MySQL

基础概念

  • 数据库:DataBase(DB),是存储和管理数据的仓库
  • 数据库管理系统:DataBase Management System(DBMS),操纵和管理数据库的大型软件
  • SQL:Structured Query Language,操作关系型数据库的编程语言,定义了一套操作关系型数据库统一标准
  • MYSQL连接语法:mysql -u用户名 -p密码 [-h数据库服务器IP地址 -P端口号]

MySQL数据模型

  • 关系型数据库(RDBMS):建立在关系模型基础上,由多张相互连接的二维表组成的数据库
    • 使用表存储数据,格式统一,便于维护
    • 使用SQL语言操作,标准统一,使用方便,可用于复杂查询

SQL简介

SQL通用语法

  • SQL语句可以单行/多行书写,以分号结束
  • SQL语句中可以增加缩进/空格来增强可读性
  • SQL语句中的关键字不区分大小写
  • SQL语句注释:单行注释(-- 注释),多行注释(/注释/)

SQL分类

  • DDL(Data Definition Language):数据定义语言,用来定义数据库对象(数据库,表,字段)
  • DML(Data Manipulation Language):数据操作语言,用来对数据库表中的数据进行增删改
  • DQL(Data Query Language):数据查询语言,用来查询数据库中表的记录
  • DCL(Data Control Language):数据控制语言,用来创建数据库角色,控制数据库的访问权限

DDL

数据库

  • 查询所有数据库:show databases;
  • 查询当前数据库:select database();
  • 使用数据库:use 数据库名;
  • 创建数据库:create database [if not exists] 数据库名;
  • 删除数据库:drop database [if exists] 数据库名;
  • 上述语法中的database,也可以替换成schema,如create schema db01;

  • 创建
/*
    create table 表名(
        字段1 字段类型 [约束] [comment 字段1注释],
        ...
        字段n 字段类型 [约束] [comment 字段n注释]
    )[comment 表注释];
 */
 -- 约束
 /*
 	概念:约束是作用于表中字段上的规则,用于限制存储在表中的数据
 	目的:保证数据库中数据的正确性、有效性和完整性
 */
约束描述关键字
非空约束限制该字段值不能为nullnot null
唯一约束保证字段的所有数据都是唯一,不重复的unique
主键约束主键是一行数据的唯一标识,要求非空且唯一primary key (auto_increment自增)
默认约束保存数据时,如果未指定该字段值,则采用默认值default
外键约束让两张表的数据建立连接,保证数据的一致性和完整性foreign key
create table tb_user(
	id int primary key comment 'ID,唯一标识',
    username varchar(20) not null unique comment '用户名',
    name varchar(10) not null comment '姓名',
    age int comment '年龄',
    gender char(1) default '男' comment '性别'
) comment '用户表';
  • 查询

    • 查询当前数据库所有表:show tables;
    • 查询表结构:desc 表名;
    • 查询建表语句:show create table 表名;
  • 修改

    • 添加字段:alter table 表名 add 字段名 类型(长度) [comment 注释] [约束];
    • 修改字段:alter table 表名 modify 字段名 新数据类型(长度);
    • 修改字段名和字段类型:alter table 表名 change 旧字段名 新字段名 类型(长度) [comment 注释] [约束];
    • 删除字段:alter table 表名 drop column 字段名;
    • 修改表名:rename table 表名 to 新表名;
-- 为表tb_emp 添加字段 qq varchar(11)
alter table tb_emp add qq varchar(11) comment 'QQ';

-- 修改tb_emp 字段类型 qq varchar(13)
alter table tb_emp modify qq varchar(13) comment 'QQ';

-- 修改tb_emp字段名qq为qq_num varchar(13)
alter table tb_emp change qq qq_num varchar(13) comment 'QQ';

-- 删除tb_emp的qq_num字段
alter table tb_emp drop column qq_num;

-- 将tb_emp表名修改为emp
rename table tb_emp to emp;
  • 删除
    • 删除表:drop table [if exists] 表名;
    • 在删除表时,表中的全部数据也会被删除

DML

  • insert语法
    • 指定字段添加数据:insert into 表名(字段名1,字段名2) values(值1,值2);
    • 全部字段添加数据:insert into 表名 values(值1,值2,...);
    • 批量添加数据(指定字段):insert into 表名(字段名1,字段名2) values(值1,值2),(值1,值2);
    • 批量添加数据(全部字段):insert into 表名 values(值1,值2,...),(值1,值2,...);
    • 注意:
      • 插入数据时,指定的字段顺序需要与值的顺序是一一对应的
      • 字符串和日期型数据应该包含在引号中
      • 插入的数据大小,应该在字段的规定范围内
insert into tb_emp(username,name,gender,create_time,update_time) values ('wuji','张无忌',1,now(),now());

insert into tb_emp(id,username,password,name,gender,image,job,entrydate,create_time,update_time) values (null,'zhiruo','123','周芷若',2,'1.jpg',1,'2010-01-01',now(),now());

insert into tb_emp values (null,'zhiruo2','123','周芷若',2,'1.jpg',1,'2010-01-01',now(),now());

insert into tb_emp(username,name,gender,create_time,update_time) values ('weifuwang','韦一笑',1,now(),now()),('xieshiwang','谢逊',1,now(),now());
  • update
    • 修改数据:update 表名 set 字段名1 = 值1,字段名2 = 值2,...[where 条件];
    • 修改语句的条件可以有,也可以没有,如果没有条件,则会修改整张表的所有数据
update tb_emp set name = '张三',update_time = now() where id = 1;
  • delete
    • 删除数据:delete from 表名 [where 条件];
    • DELETE语句的条件可以有,也可以没有,如果没有条件,则会删除整张表的所有数据
    • DELETE语句不能删除某一个字段的值,(如果要操作,可以使用UPDATE,将该字段的值置为NULL)
delete from tb_emp where id = 1;

DQL

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

基本查询

  • 查询多个字段:select 字段1,字段2,字段3 from 表名;
  • 查询所有字段(通配符):select * from 表名;
  • 设置别名:select 字段1 [as 别名1],字段2[as 别名2] from 表名;
  • 去除重复记录:select distinct 字段列表 from 表名;
select name,entrydate from tb_emp;

select id,username,password,name,gender,image,job,entrydate,create_time,update_time from tb_emp;
select * from tb_emp;

select name as 姓名,entrydate as 入职日期 from tb_emp;
select name 姓名,entrydate 入职日期 from tb_emp;
select name '姓 名',entrydate 入职日期 from tb_emp;

select distinct job from tb_emp;

条件查询

  • 条件查询:select 字段列表 from 表名 where 条件列表;
select * from tb_emp where name = '杨逍';

select * from tb_emp where id <= 5;

select * from tb_emp where job is null;

select * from tb_emp where job is not null;

select * from tb_emp where password != '123456';


select * from tb_emp where password <> '123456';

select * from tb_emp where entrydate >= '2000-01-01' and entrydate <= '2010-01-01';

select * from tb_emp where entrydate between '2000-01-01' and '2010-01-01';

select * from tb_emp where entrydate between '2000-01-01' and '2010-01-01' and gender = 2;

select * from tb_emp where job = 2 or job = 3 or job = 4

select * from tb_emp where job in (2,3,4);

-- 查询 姓名 为两个字的员工信息
select * from tb_emp where name like '__';

-- 查询 姓 ‘张’ 的员工信息
select * from tb_emp where name like '张%'
比较运算符功能
>大于
>=大于等于
<小于
<=小于等于
=等于
<> 或!=不等于
between…and…在某个范围之内(含最小,最大值)
in(…)在in之后的列表中的值,多选一
like 占位符模糊匹配( _ 匹配单个字符, % 匹配任一个字符)
is null是null
逻辑运算符功能
and 或 &&并且(多个条件同时成立)
or 或 ||或者(多个条件任意一个成立)
not 或 !非,不是

分组查询

  • 聚合函数
    • 介绍: 将一列数据作为一个整体,进行纵向计算
    • 语法: select 聚合函数(字段列表) from 表名;
    • null值不参与所有聚合函数运算
    • 统计数量可以使用: count(*) count(字段) count(常量) 推荐使用count(*)
-- count(字段)
select count(id) from tb_emp;

-- count(常量)
select count('A') from tb_emp;

-- count(*)
select count(*) from tb_emp;

select min(entrydate) from tb_emp;

select max(entrydate) from tb_emp;

select avg(id) from tb_emp;

select sum(id) from tb_emp;
  • 分组查询
    • select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件];
    • 分组之后,查询的字段一般为聚合函数和分组字段,查询其他字段无任何意义
    • 执行顺序: where > 聚合函数 > having
-- 根据性别分组,统计男性和女性员工的数量
select gender,count(*) from tb_emp group by gender;

-- 先查询入职时间在‘2015-01-01’(包含)以前的员工,并对结果根据职位分组,获取员工数量大于等于2的职位
select job,count(*) where entrydate <= '2015-01-01' group by job having count(*) >= 2;
  • where 与 having 区别
    • 执行时机不同: where是分组之前进行过滤,不满足where条件,不参与分组;而having是分组之后对结果进行过滤
    • 判断条件不同,where不能对聚合函数进行判断,而having可以

排序查询

  • select 字段列表 from 表名 [where 条件列表][group by 分组字段] order by 字段1 排序方式1,字段2 排序方式2...;
  • ASC: 升序(默认值)
  • DESC: 降序
  • 如果是多字段排序,当第一个字段值相同时,才会根据第二个字段进行排序
select * from tb_emp order by entrydate asc;

select * from tb_emp order by entrydate desc;

-- 根据 入职时间 对员工进行 升序排序,入职时间相同,再按照 更新时间 进行降序排序
select * from tb_emp order by entrydate , update_time desc;

分页查询

  • select 字段列表 from 表名 limit 起始索引,查询记录数
  • 起始索引从0开始,起始索引=(查询页码 - 1) * 每页记录数
  • 分页查询是数据库的方言,不同数据库有不同的实现,MySQL中是LIMIT
  • 如果查询的是第一页数据,起始索引可以省略,直接简写为limit 10
-- 从起始索引0 开始查询员工数据,每页显示5条记录
select * from tb_emp limit 0,5;

-- 查询第1页员工数据,每页显示5条数据
select * from tb_emp limit 0,5;

-- 查询第2页员工数据,每页显示5条数据
select * from tb_emp limit 5,5;

-- 查询第3页员工数据,每页显示5条数据
select * from tb_emp limit 10,5;

-- 起始索引 = (页码 - 1) * 每页记录数
select *
from tb_emp
where name like '%张%'
	and gender = 1
	and entrydate between '2000-01-01' and '2015-12-31'
order by update_time desc
limit 0,10;
-- if(条件表达式,true取值,false取值)
select if(gender = 1,'男性员工','女性员工') 性别,count(*) from tb_emp group by gender;

-- case 表达式 when 值1 then 结果1 when 值2 then 结果2 ... else ... end
select job
	(case job when 1 then '班主任' when 2 then '讲师' when 3 then '学工主管' when 4 then '教研主管' else '未分配职位' end) 职位
	,count(*)
from tb_emp group by job;

多表设计

一对多

  • 案例: 员工与部门的关系
  • 关系实现:在数据库表中多的一方,添加字段,来关联一方的主键
create table tb_dept{
	id int unsigned primary key auto_increment comment '主键ID',
	name varchar(10) not null unique comment '部门名称',
	create_time datetime not null comment '创建时间',
	update_time datetime not null comment '修改时间'
} comment '部门表';
create table tb_emp{
	id int unsigned primary key auto_increment comment 'ID',
	username varchar(20) not null unique comment '用户名',
	password varchar(32) default '123456' comment '密码',
	name varchar(10) not null comment '姓名',
	gender tinyint unsigned not null comment '性别,说明:1 男,2女'
	image varchar(300) comment '图像',
	job tinyint unsigned comment '职位,说明:1 班主任,2 讲师,3 学工主管'
	entrydate date comment '入职时间',
	dept_id int unsigned comment '部门ID',
	create_time datetime not null comment '创建时间',
	update_time datetime not null comment '修改时间'
} comment '员工表';
  • 物理外键
    • 使用foreign key定义外键关联另外一张表
    • 缺点:
      • 影响增 删 改的效率(需要检查外键关系)
      • 仅用于单节点数据库, 不适合与分布式 集群场景
      • 容易引发数据库的死锁问题, 消耗性能
-- 创建表时指定
/*
    create table 表名(
        字段名 数据类型,
        ...
        [constraint] [外键名称] foreign key(外键字段名) references 主表(字段名)
    );
*/

-- 建完表后,添加外键
/*
	alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表(字段名);
*/
alter table tb_emp
	add constraint tb_emp_fk_dept_id
		foreign key (dept_id) reference
tb_dept(id);
  • 逻辑外键
    • 在业务逻辑中, 解决外键关联
    • 通过逻辑外键, 就可以很方便的解决上述问题

### 一对一

  • 案例: 用户和身份证

  • 一对一关系, 多用于单表拆分, 将一张表的基础字段放在一张表中, 以提升操作效率

  • 实现: 在任意一方加入外键, 关联另外一方的主键, 并且设置外键为唯一的(UNIQUE)

多对多

  • 案例: 学生与课程的关系
  • 实现: 建立第三张中间表, 中间表至少包含两个外键, 分别关联两方主键

多表查询

  • 多表查询: 指从多张表中查询数据

  • 笛卡尔积: 笛卡尔积是指在数学中,两个集合(A集合和B集合)的所有组合情况 (在多表查询时, 需要消除无效的笛卡尔积)

  • 分类

    • 连接查询
      • 内连接: 相当于查询A B交集部分数据
      • 外连接
        • 左外连接: 查询左表所有数据(包括两张表交集部分数据)
        • 右外连接: 查询右表所有数据(包括两张表交集部分数据)
    • 子查询

内连接

  • 隐式内连接: select 字段列表 from 表1,表2 where 条件...;
  • 显式内连接: select 字段列表 from 表1 [inner] join 表2 on 连接条件;
select tb_emp.name,tb_dept.name from tb_emp,tb_dept where tb_emp.dept_id = tb_dept.id;

-- 别名
select e.name,d.name from tb_emp e,tb_dept d where e.dept_id = d.id;

select tb_emp.name,tb_dept.name from
tb_emp inner join tb_dept on tb_emp.dept_id = tb_dept.id; 

外连接

  • 左外连接: select 字段列表 from 表1 left [outer] join 表2 on 连接条件...;
  • 右外连接: select 字段列表 from 表1 right [outer] join 表2 on 连接条件...;
-- 查询员工表 所有 员工的姓名,和对应的部门名称(左外连接)
select e.name,d.name from tb_emp e left join tb_dept d on e.dept_id = d.id;

-- 查询部门表 所有 部门的姓名,和对应的员工名称(右外连接)
select e.name,d.name from tb_emp e right join tb_dept d on e.dept_id = d.id;

select e.name,d.name from tb_dept d left join tb_emp e on e.dept_id = d.id;

子查询

  • 介绍: SQL中嵌套select语句,称为嵌套查询,又称子查询

  • 形式:select * from t1 where column1 = (select column1 from t2...)

  • 子查询外部的语句可以是 insert / update / delete /select 的任何一个,最常见的是select

  • 分类

    • 标量子查询:子查询返回的结果为单个值(数字 字符串 日期等), 最简单的形式
      • 常用的操作符: = <> > >= < <=
    -- 查询 教研部 的部门ID
    select * from tb_emp where dept_id = (select id from tb_dept where name = '教研部');
    
    -- 查询在 方东白 入职之后的员工信息
    select * from tb_emp where entrydate > (select entrydate from tb_emp where name = '方东白');
    
    • 列子查询:子查询返回的结果为一列(可以是多行)
      • 常用的操作符: in not in等
    -- 查询 教研部 和 咨询部 的所有员工信息
    select * from tb_emp where dept_id in (select id from tb_dept where name = '教研部' or name = '咨询部');
    
    
    
    • 行子查询:子查询返回的结果为一行(可以是多列)
      • 常用的操作符: = <> in not in
    -- 查询与 韦一笑 的 入职日期 及 职位 都相同的员工信息
    select * from tb_emp where entrydate = (select entrydate from tb_emp where name = '韦一笑') and job = (select job from tb_emp where name = '韦一笑');
    -- 优化
    select * from tb_emp where (entrydate,job) = (select entrydate,job from tb_emp where name = '韦一笑');
    
    • 表子查询:子查询返回的结果为多行多列, 常作为临时表
      • 常用的操作符: in
    -- 查询入职日期是 2006-01-01 之后的员工信息,及其部门名称
    select e.*,d.name from (select * from tb_emp where entrydate > '2006-01-01') e,tb_dept d where e.dept_id = d.id;
    
-- 查询价格低于 10元 的菜品的 名称、价格 及其 菜品的分类名称
select d.name,d.price,c.name from dish d,category c where d.category_id = c.id and d.price < 10;

-- 查询 所有价格在10元(含)到50(含)之间 且 状态为’起售‘的菜品,展示出 菜品的名称、价格 及其 菜品的分类名称 (即使菜品没有分类,也需要将菜品查询出来)
select d.name,d.price,c.name from dish d left join category c on d.category_id = c.id where d.price between 10 and 50 and d.status = 1;

-- 查询每个分类下最贵的菜品,展示出分类的名称,最贵的菜品的价格
select c.name,max(d.price) from dish d,category c where d.category_id = id group by c.name;

-- 查询 各个分类下 菜品状态为’起售‘,并且 该分类下菜品总数量大于等于3 的 分类名称
select c.name,count(*) from dish d,category c where d.category_id = c.id and d.status = 1 group by c.name having count(*) >= 3;

-- 查询出 商务套餐A 中包含了哪些菜品 (显示出套餐名称、价格、包含的菜品名称、价格、份数)
select s.name,s.price,d.name,d.price,sd.copies
from setmeal s,setmeal_dish sd,dish d
where s.id = sd.setmeal_id and sd.dish_id = d.id and s.name = '商务套餐A';

-- 查询出 低于菜品平均价格的菜品信息(展示出菜品名称、菜品价格)
select name,price from dish where price < (select avg(price) from dish);

事务

  • 概念: 事务是一组操作的集合,它是一个不可分割的工作单位, 事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求, 即这些操作要么同时成功, 要么同时失败
  • 注意: 默认MySQL的事务是自动提交的, 也就是说, 当执行一条DML语句, MySQL会立即隐式的提交事务

事务控制

  • 开启事务: start transaction; / begin;
  • 提交事务: commit;
  • 回滚事务: rollback;

四大特性(ACID)

  • 原子性: 事务是不可分割的最小单元, 要么全部成功, 要么全部失败
  • 一致性: 事务完成时, 必须使所有的数据都保持一致状态
  • 隔离性: 数据库系统提供的隔离机制, 保证事务在不受外部并发操作影响的独立环境下运行
  • 持久性: 事务一旦提交或回滚, 它对数据库中的数据的改变就是永久的

索引

  • 概念: 索引(index) 是帮助数据库 高效获取数据 的数据结构

  • 优点:

    • 提高数据查询的效率, 降低数据库的IO成本
    • 通过索引列对数据进行排序, 降低数据排序的成本, 降低CPU消耗
  • 缺点:

    • 索引会占用存储空间
    • 索引大大提高了查询效率, 同时却降低了insert update delete的效率

结构

  • MySQL数据库支持的索引结构有很多, 如: Hash索引 B+Tree索引 Full-Text索引等 我们平常所说的索引, 如果没有特别指明, 都是指默认的B+Tree结构组织的索引

在这里插入图片描述

  • 每一个节点, 可以存储多个key(有n个key, 就有n个指针)
  • 所有的数据都存储在叶子节点, 非叶子节点仅用于索引数据
  • 叶子节点形成了一颗双向链表, 便于数据的排序及区间范围查询

语法

  • 创建索引: create [unique] index 索引名 on 表名(字段名...);
  • 查看索引: show index from 表名;
  • 删除索引: drop index 索引名 on 表名;
  • 注意
    • 主键字段, 在建表时, 会自动创建主键索引
    • 添加唯一约束时, 数据库实际上会添加唯一索引
-- 为tb_emp表的name字段建立一个索引
create index idx_emp_name on tb_emp(name);

-- 查询 tb_emp表的索引信息
show index from tb_emp;

-- 删除tb_emp表中的name字段的索引
drop index idx_emp_name on tb_emp;
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值