MySQL

MySQL

请求流程：浏览器发起请求，先请求Controller；Controller接收到请求之后，调用Service进行业务逻辑处理；Service再调用Dao，Dao再解析user.xml（数据库）中所存储的数据。

select version();             -- 查看版本
select now();                 -- 显示当前时间


use xlm_data;                  -- 切换使用数据库 use 数据库名
show tables;                   -- 查看数据库下的表
show tables from xlm_data;     -- 查看数据库下的表
desc stu;                      -- 查看表的信息
select * from stu;

数据库设计

基础

mysql -u用户名 -p密码 [-h数据库服务器的IP地址 -P端口号]

连接远程服务器（Linux）

mysql -h127.0.0.1  -p3306 -urooy -p

本机也看做服务器

mysql内置了DBMS数据库管理系统

database可以改成schema

基础知识

数据库database简称DB是存储和管理数据的仓库，数据是有组织的进行存储。

数据库管理系统是管理数据库的大型软件，database management system，简称DBMS。

SQL是结构化查询语句，操作关系型数据库的编程语言，定义操作所有关系型数据库的统一标准。

关系型数据库是建立在关系模型基础上的数据库，是由多张能互相连接的二维表组成的数据库。优点：都是使用表结构，格式一致易于维护。使用通用的SQL语言操作，使用方便可用于复杂查询。数据存储在磁盘中，安全。

二维表，指的是由行和列组成的表

Redis属于非关系型数据库

常见的关系型数据库：Oracle，MySQL，SQL server，postgresql，db2，sqlite，mariadb

MySQL数据模型：客户端-->数据库管理系统-->数据库-->数据表-->数据

SQL：结构化查询语言，一门操作关系型数据库的编程语言，定义操作所有关系型数据库的统一标准，对于同一个需求，每一种数据库操作的方式可能存在一些不一样的地方，我们称之为“方言”。

关键字建议大写

SQL  -->  DBMS  -->  DB,DB,DB
目录里的Frm是表文件
myd是数据文件。

services.msc

services.msc是Windows操作系统中的服务管理器，它提供了一个图形界面来管理和控制系统中的各种服务。

要打开services.msc，可以按下 Windows键 + R 组合键，然后在运行对话框中输入 "services.msc"，然后点击确定。这将打开服务管理器窗口，其中列出了系统中安装的各种服务。

打开

net start mysql

注册

mysqladmin -u root password 1234

登录
登录参数：mysql -u用户名 -p密码 -h要连接的mysql服务器的ip地址（默认127.0.0.1） -P端口号（默认3306）

MySQL -u root -p 1234

退出

exit
quit

卸载

卸载：net stop mysql
	  Mysql -remove mysql
	  最后删除目录及相关环境变量

SQL分类

数据类型

MySQL中的数据类型有很多，主要分为三类：数值类型、字符串类型、日期时间类型。

空值在数学运算中的使用：包含空值的数学表达式值都为空值

数值类型

示例: 
    年龄字段 ---不会出现负数, 而且人的年龄不会太大
	age tinyint unsigned
	
	分数 ---总分100分, 最多出现一位小数
	score double(4,1)

字符串类型

Char存储性能高浪费空间，varchar存储性能低节省空间

示例： 
    用户名 username ---长度不定, 最长不会超过50
	username varchar(50)
	
	手机号 phone ---固定长度为11
	phone char(11)

日期时间类型

示例: 
	生日字段  birthday ---生日只需要年月日  
	birthday date
	
	创建时间 createtime --- 需要精确到时分秒
	createtime  datetime

create_time：记录的是当前这条数据插入的时间。

update_time：记录当前这条数据最后更新的时间。

DDL

DDL中数据库的常见操作：查询、创建、使用、删除。

retrieve查询---------------！！！

查询

show databases;     #查询所有数据库
select database();  #查询当前数据库

创建

create database if not exists  数据库名;

使用

操作某一个数据库下的表时，就需要通过该指令，切换到对应的数据库下

use 数据库名 ;

删除

drop database if exists 数据库名 ;

表结构的操作包含四个部分：创建表、查询表、修改表、删除表。

创建表

create table  表名(
	字段1  字段1类型 [约束]  [comment  字段1注释 ],
	字段2  字段2类型 [约束]  [comment  字段2注释 ],
	......
	字段n  字段n类型 [约束]  [comment  字段n注释 ] 
) [ comment  表注释 ] ;

约束

id:想要限制字段所存储的数据，就需要用到数据库中的约束。

注意：约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束。

主键自增

auto_increment

每次插入新的行记录时，数据库自动生成id字段(主键)下的值

具有auto_increment的数据列是一个正数序列开始增长(从1开始自增)

create table tb_user (
    id int primary key auto_increment, #主键自动增长
    username varchar(20) not null unique,
    name varchar(10) not null,
    age int,
    gender char(1) default '男'
);

为已经创建完成的表字段设置为自动增长列

ALTER TABLE 表名 MODIFY 字段名 数据类型 PRIMARY KEY AUTO_INCREMENT;

查询表

show tables;
desc 表名 ;  #可以查看指定表的字段、字段的类型、是否可以为NULL、是否存在默认值等信息
show create table 表名 ;#查询指定表的建表语句

修改表

#添加字段
alter table 表名 add  字段名  类型(长度)  [comment 注释]  [约束];
#修改数据类型
alter table 表名 modify  字段名  新数据类型(长度);  
alter table 表名 change  旧字段名  新字段名  类型(长度)  [comment 注释]  [约束];
#修改表名
rename table 表名 to  新表名;

删除表或字段

alter table 表名 drop 字段名;   #删除字段
drop  table [ if exists ]  表名;  #删除表

多表设计----------------！！！

数据库操作

DML

用来对数据库中表的数据记录进行增、删、改操作。添加数据（INSERT）、修改数据（UPDATE）、删除数据（DELETE）

添加数据

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 ('weifuwang', '韦一笑', 1, now(), now()),
       ('fengzi', '张三疯', 1, now(), now());

注意：

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

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

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

修改数据

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

例子：将emp表中id为1的员工，姓名name字段更新为'张三'

update emp set name='张三',update_time=now() where id=1;

注意：

修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据。

在修改数据时，一般需要同时修改公共字段update_time，将其修改为当前操作时间。

删除数据

delete from 表名  [where  条件] ;

案例1：删除emp表中id为1的员工

delete from emp where id = 1;

注意：

DELETE 语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据。

DELETE 语句不能删除某一个字段的值(可以使用UPDATE，将该字段值置为NULL即可)。

当进行删除全部数据操作时，会提示询问是否确认删除所有数据，直接点击Execute即可。

DQL

查询

用来查询数据库表中的记录。

查询关键字：SELECT

在一个正常的业务系统中，查询操作的使用频次是要远高于增删改操作的。而在这个查询过程中，还会涉及到条件、排序、分页等操作。

DQL查询语句语法结构：

SELECT
	字段列表
FROM
	表名列表
WHERE
	条件列表
GROUP  BY
	分组字段列表
HAVING
	分组后条件列表
ORDER BY
	排序字段列表
LIMIT
	分页参数

基本查询：

select 字段1, 字段2, 字段3 from  表名;#查询多个字段
elect *  from  表名;#查询所有字段（通配符）
select 字段1 [ as 别名1 ] , 字段2 [ as 别名2 ]  from  表名;#设置别名
select distinct 字段列表 from  表名;#去除重复记录

条件查询：

select  字段列表  from   表名   where   条件列表 ; -- 条件列表：意味着可以有多个条件

学习条件查询就是学习条件的构建方式，而在SQL语句当中构造条件的运算符分为两类：

注意：

查询为NULL的数据时，不能使用 = null

where job is null ;

例子：查询 入职日期 在 '2000-01-01' (包含) 到 '2010-01-01'(包含) 之间的员工信息

-- 方式1：
select id, username, password, name, gender, image, job, entrydate, create_time, update_time
from tb_emp
where entrydate>='2000-01-01' and entrydate<='2010-01-01';
-- 方式2： between...and
select id, username, password, name, gender, image, job, entrydate, create_time, update_time
from tb_emp
where entrydate between '2000-01-01' and '2010-01-01' 
	and gender = 2;  -- 且性别为女
where job in (2,3,4); -- 职位是 2 (讲师), 3 (学工主管), 4 (教研主管) 的
where name like '__';  # 通配符 "_" 代表任意1个字符

聚合函数

之前我们做的查询都是横向查询，就是根据条件一行一行的进行判断，而使用聚合函数查询就是纵向查询，它是对一列的值进行计算，然后返回一个结果值。（将一列数据作为一个整体，进行纵向计算）

空值在数学运算中的使用：包含空值的数学表达式值都为空值

语法：

select  聚合函数(字段列表)  from  表名 ;

注意 :

聚合函数会忽略空值，对NULL值不作为统计。

常用聚合函数：

count(常量)(*)------------!!!!

# count(常量)
select count(0) from tb_emp;
select count('A') from tb_emp;

# count(*)  推荐此写法（MySQL底层进行了优化）
select count(*) from tb_emp;

分组查询

按照某一列或者某几列，把相同的数据进行合并输出。

分组其实就是按列进行分类(指定列下相同的数据归为一类)，然后可以对分类完的数据进行合并计算。

分组查询通常会使用聚合函数进行计算。

语法：

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

例子：根据性别分组 , 统计男性和女性员工的数量

select gender, count(*)
from tb_emp
group by gender; -- 按照gender字段进行分组（gender字段下相同的数据归为一组）

例2：查询入职时间在 '2015-01-01' (包含) 以前的员工 , 并对结果根据职位分组 , 获取员工数量大于等于2的职位

select job, count(*)
from tb_emp
where entrydate <= '2015-01-01'   -- 分组前条件
group by job                      -- 按照job字段分组
having count(*) >= 2;             -- 分组后条件

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

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

where与having区别（面试题）

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

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

排序查询

语法：

select  字段列表  
from   表名   
[where  条件列表] 
[group by  分组字段 ] 
order  by  字段1  排序方式1 , 字段2  排序方式2 … ;

排序方式：ASC ：升序（默认值）、DESC：降序

例子：根据入职时间对公司的员工进行升序排序，入职时间相同，再按照更新时间进行降序排序

select id, username, password, name, gender, image, job, entrydate, create_time, update_time
from tb_emp
order by entrydate ASC , update_time DESC;

注意：

如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序

分页查询

语法：

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

注意:

起始索引从0开始。

计算公式：起始索引 = （查询页码 - 1）* 每页显示记录数

分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT

进阶案例

例子：员工性别统计

-- if(条件表达式, true取值 , false取值)
select if(gender=1,'男性员工','女性员工') AS 性别, count(*) AS 人数
from tb_emp
group by gender;

员工职位统计：

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

------------------！！！

select sno,class+1 from student;

DQL语句-单表操作-------------！！！

DQL语句-多表操作-------------！！！

事物

多表查询

数据库优化

索引

SQL优化

本栏目推荐文章
• Jmeter 如何连接mysql数据库？
• 如何通过Navicat把MySQL远程数据库导入到本地数据库
• 【LeetCode 2701. 连续递增交易】MySQL用户变量编程得到严格递增连续子序列的开始位置，结束位置，长度等统计信息
• 【Leetcode 2474. 购买量严格增加的客户】MySQL用户变量编程解决严格递增连续子序列问题
• mysql发生连接异常Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
• 【LeetCode 2494. 合并在同一个大厅重叠的活动】MySQL用户变量编程解决区间合并问题
• 如何处理Mysql死锁
• 【LeetCode1747. 应该被禁止的 Leetflex 账户】MySQL用户变量编程；尝试维护一个multiset
• Mysql时间常用函数
• 【Leetcode1949. 坚定的友谊】使用MySQL在无向图中寻找{"CompleteTripartite", {1, 1, 3}}这个pattern