数据库mysql

什么是数据库

数据库

database（db）存储和管理数据的仓库

数据库管理系统

database management system（dbms）操纵和管理数据库的大型软件

sql

structured query language操作关系型数据库的编程语言，定义了一套操作关系型数据库统一 标准

mysql概述

安装配置

安装

官网下载压缩包

配置

配置系统环境变量         新建MYSQL_HOME         path配置bin目录

初始化mysql        管理员身份运行cmd         mysqld --initialize-insecure

注册mysql服务        mysqld -install

启动mysql服务        net start mysql（关闭mysql服务        net stop mysql）

修改默认账号密码        mysqladmin -u root password 1234

登录mysql

win+r cmd打开输入        mysql -u root -p 1234[-h数据库服务器ip地址  -p端口号]

卸载mysql

管理员身份运行命令提示符

1 net stop mysql

2 mysqld -remove mysql

3 删除环境变量配置

数据模型

关系型数据库

建立在关系模型基础上，由多张互相连接的 二维表 组成的数据库

特点：使用表存储数据，格式统一，便于维护        使用sql语言操作，标准统一，使用方便，可用于复杂查询

sql简介

sql:

一门操作关系型数据库的编程语言，定义操作所有关系型数据库的  统一标准

通用语法：

sql可以单行或多行书写，以分号结尾

可使用空格/缩进来增强语句的可读性

mysql数据库的sql语句不区分大小写

注释：1单行注释：  --(#)内容  2多行注释：/* */

分类

DDL（data definition language） 数据定义语言，定义数据库对象（数据库，表，字段）

DML（data manipulation language）数据操纵语言，对数据库表中的数据进行增删改查

DQL（data query language） 数据查询语言，查询数据库中表的记录

DCL（data control language）数据控制语言，创建数据库用户，控制数据库访问权限

数据库设计-DDL

数据库操作(database也可替换schema)

查询

show DATABASES;（查询所有数据库）

SELECT DATABASE();（查询当前数据库）

USE web;
SELECT DATABASE();

使用

use 数据库名;(使用数据库)

创建

create database [if not exists] 数据库名;

create database if not exists a;

删除

drop database [if exists] 数据库名;

idea中操作数据库

表（创建，查询，修改，删除）

DDL（表操作）

创建

约束

概念：作用于表中字段上的规则，用于限制存储在表中的数据

目的：保证数据库中数据的正确性，有效性和完整性

非空约束        限制该字段值不能null为        not null

唯一约束        保证字段的所有数据都是唯一的，不能重复        unique

主键约束        主键是一行数据的唯一标识，要求非空且唯一        primary key (auto_increment自增)

默认约束        保存数据时，为指定该字段值，默认        default        gender char(1) default '男' comment '性别';

外键约束        让两张表的数据建立连接，保证数据的一致性和完整性        foreign key

数据类型

数值

tinyint 1(byte) （-128，127）（sign） （0，255）（unsign） 小整数值

int 4（byte）  大整数值

bignit 8 极大整数值

double 8 双精度浮点数值 double（5，2） 5表示整个数字长度 2表示u小数位位个数

decimal 小数值（精度更高）（5，2）同上

字符串类型

char 0-255 bytes 定长字符串 char（10）最多只能存10个字符，不足10个字符，占用10个字符空间  性能高 浪费空间

varchar 0-65535  变长字符串  varchar（10）最多只能存10个字符串，不足10个，按实际长度存储 性能低 节省空间

日期类型

 date 3byte YYYY-MM-DD 日期值

datetime 8byte YYYY-MM-DD HH:MM:SS 混合日期和时间值

表查询

查询数据库所有表：show tables;

查询表结构： desc 表名;

查询建表语句： show create table 表名;

表修改

添加字段：alter table 表名 add 字段名 类型（长度） [comment 注释] [约束];

修改字段类型：alter table 表名 modify 字段名 新数据类型(长度);

修改字段名和字段值：alter table 表名 change 旧字段名 新字段名 类型（长度） [comment 注释] [约束];

删除字段：alter table 表名 drop column 字段名;

修改表名： rename table 表名 to 新表名;

表删除

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,...);

now()当前时间

注意：插入数据时，指定的字段顺序需要与值的顺序是一一对应的

        字符串和日期型数据应该包含在引号内

        插入的数据大小，应当在字段的规定范围内

UPDATE

update 表名 set 字段名1=值1，字段名2=值2,...[where 条件];

无条件则为所有数据

DELETE

delete from 表名 [where 条件];

注意：无条件则删除整张表的所有数据

        不能删除某一个字段的值，可以使用update，将该字段的值置为null

数据库操作-DQL

基本查询

查询多个字段：select 字段1,字段2,... from 表名;

查询所有字段（通配符）:select * from 表名;

*号代表查询所有字段(不推荐)  -- 不直观   ，性能低

设置别名： select 字段1 [as 别名1],字段2 [as 别名2] from 表名;

去重复记录:select distinct 字段列表 from 表名;

条件查询

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

分组查询

聚合函数

介绍：将一列数据作为一个整体，进行纵向计算

语法：select 聚合函数（字段列表） from 表名;

注意：不对null值进行运算,推荐使用count(*)   ，where后不能使用聚合函数

count 统计数量  

max 最大值

min 最小值

avg 平均值

sum 求和

分组查询

select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件];

where 与 having 区别：

        执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；having是分组之后对结果进行过滤

        判断条件不同：where不能对聚合函数进行判断，而having可以

注意：

        分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无意义

        执行顺序：where>聚合函数>having

排序查询

语法

条件查询： select 字段列表 from 表名 [where 条件列表][group by 分组字段] order by 字段1 排序方式1,字段2 排序方式2 ...;   （多个字段  第一个相同，再看第二个）

ASC:升序（默认值）

DESC：降序

分页查询

语法

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

注意：

        起始索引从0开始，起始索引=（查询页码-1）*每页记录数

        分页查询是数据库的方言，mysql中是limit

        如果查询的是第一页数据，起始索引可以省略，直接简写为limit 每页记录数

案例

select *
from tb_emp
where name like '%张%'
    and gender = 1
    and entrydate between '2000-01-01' and '2015-12-12'
order by update_time desc  //降序
limit 0,10;

流程控制函数

if(条件表达式,true取值,false取值)

case 表达式 when 值1 then 结果1 when 值2 then 结果2 ...else... end

SELECT if(sex=1,'男性','女性') 性别 ,COUNT(*) FROM s GROUP BY sex;

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

echarts

官网上可以看图表（折线图，饼图，柱状图）

多表设计

概念

根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，各个表结构之间存在各种联系，基本上分为三种：

一对多（多对一）

多对多

一对一

一对多

设计

一对多关系实现：在数据库表中多的一方，添加字段，来关联低的一方的主键

多表问题

现象

部门数据可以直接删除，然而还有部分员工归属于该部门下，此时出现了数据的不完整，不一致问题

分析

目前上述的两张表，在数据库层面，并未建立关联，所以无法保证数据的一致性和完整性

外键约束

--创建表时指定
create table 表名(
    字段名 数据类型,
    ...
    [constraint] [外键名称] foreign key(外键字段名) references 主表(字段名)
);
--建表完成后，添加外键
alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表(字段名);

物理外键

使用foreign key 定义外键关联另外一张表

缺点：

影响增删改的效率（需要检查外键关系）

仅用于单节点数据库，不适用于分布式，集群场景

容易引发数据库的死锁问题，消耗性能

不推荐使用

逻辑外键

在业务层逻辑中，解决外键关联

通过逻辑外键，就可以很方便的解决上述问题

推荐使用

一对一

关系：多用于单表拆分，将一张表的基础字段放在一张表，其他字段放在另一张表中，以提升操作效率

实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的（unique）

多对多

学生与课程的关系

实现：

建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

多表查询

概述

从多张表中查询数据

笛卡尔积：两个集合（a，b集合）的所有组合情况

分类

连接查询

内连接：

查询A,B交集部分数据

隐式内连接：select 字段列表 from 表1,表2 where 条件...;

select e.name,d.name from tb_emp e,tb_dept d where e.edpt_id=d.id;

显式内连接：select 字段列表 from 表1 [inner] join 表2 on 连接条件...;

外连接：

        左外连接：查询左表所有数据（包括交集部分数据）

                select 字段列表 from 表1 left [outer] join 表2 on 连接条件...;

        右外连接：查询右表所有数据（包括交集部分）

                select 字段列表 from 表1 right [outer] join 表2 on 连接条件...;

子查询

概述

sql语句中嵌套select语句，称为嵌套查询，又称子查询

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

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

标量子查询：

子查询返回的结果为单个值（数字，字符串，日期等），最简单

常用的操作符：= ，<>， >， >=， <， <=

列子查询

子查询返回的结果为一列(可以是多行)

常用的操作符： in ，not in

行子查询

子查询返回的结果为一行（可以是多列）

常用操作符：=，<>,in ,not in

select * from tb_emp where (entrydate,job)=(select entrydate,job from tb_emp where name='韦一笑');

表子查询

子查询返回的结果为多行多列，常作为临时表

常用操作符： in

//查询入职日期是“2006-01-01”之后的员工信息，及其部门名称

//查询日期是“2006-01-01”之后的员工信息
select * from tb_emp where entrydate > "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;

事务

介绍

事务是一组操作的集合，他是一个不可分割的工作单位。事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作  要么同时成功，要么同时失败

注意：

        默认mysql的事务是自动提交的，也就是所，当执行一条DML语句，mysql会立即隐式的提交事务

操作

事务控制

开启事务：

        start transaction;/begin;

提交事务：(成功)

        commit;

回滚事务：（失败）

        rollback;

四大特性ACID

原子性：Atomicity

        事务是不可分割的最小单元，要么全部成功，要么全部失败

一致性：Consistency

        事务完成时，必须使所有的数据都保持一致的状态

隔离性：Isolation

        数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行

持久性：Durability

        事务一旦提交或回滚，他对数据库中的数据的改变就是永久的

索引

数据库优化方法之一

介绍

        索引（index）是帮助数据库  高效获取数据   的   数据结构

优点：

        提高数据查询的效率，降低数据库的IO成本

        通过索引列对数据进行排序，降低数据排序的成本，降低cpu消耗

缺点：

        索引会占用存储空间

        索引大大提高了查询效率，同时也降低了insert，update，delete的效率

结构

mysql数据库支持的索引结构有很多，如：hash，b+tree，full-text（索引）等，默认B+Tree结构组织的索引

语法

创建索引：

        create [unique] index 索引名 on 表名 (字段名,...);

查看索引：

        show index from 表名;

删除索引：

        drop index 索引名 on 表名;

注意：

        主键字段，在建表时，会自动创建主键索引

        添加唯一约束时，数据库实际上会添加唯一索引