MySQL数据库

一、数据库概述

数据库，顾名思义，就是遵循一定数据格式的数据集合，可以认为它是对文件系统的改进。解决了不同操作系统之间，数据格式的兼容性问题。也就是说，只要是同一个数据库的数据文件，即使是从Windows迁移到了Linux上，也是可以正常处理的。

二、数据库的分类

画红框的是我们重点关注的。

• Oracle
  这是一种关系型数据库。它的特点是闭源收费，但是功能强大稳定，而且有一支专业的技术支撑团队。使用这种数据库的一般是大型企业、银行业、金融业。
• MySQL
  它也是关系型数据库。它的特点是开源免费，功能还是不错的，也比较稳定。通常使用这种数据库的，是中小企业等。因为它是开源的，所以我们有些企业可以对它做定制化、二次开发，以支持自己特殊的业务。比如阿里。说它是目前应用最广泛的DBMS,是MySQL的订制二次开发,也是比较好用。
• Memcached、Redis
  这2者是非关系型数据库（Not Only SQL）。它们通常用K-V键值对的形式存储数据，使用场景为缓存（可做为关系型数据库的补充）。它俩都是基于内存存储的，它们的数据都是在内存中，所以访问速度要快。Redis还提供了定期向磁盘中进行序列化的机制，它的IO性能也是非常高的。
• MongoDB
  这个也是NOSQL数据库。它使用文档形式存储数据。它使用文档形式存储数据。使用场景，通常是大数据量高并发访问。它的性能也非常高。

关系型和非关系型数据库的区别：

• 关系型数据库：
  通过查询数据，得到对象。而非关系型数据库，保存键值对，查询处理数据，得到数组。对于java而言,一个类就可以对应一个表,一个类对象就可以对应表中一行数据,一个成员变量可以对应表中的一个列(字段),能够做到一一映射,数据库中以表作为基本单位。
• 非关系型数据库：
  存储键值对。即有关系型数据库处理数据的方便性,也能处理大量数据,比较火的就是Redis、mongoDB。

ORM : Object Relationl Mapping 对象关系映射,它的作用是在关系型数据库和对象之间作一个映射，这样，我们在具体的操作数据库的时候，就不需要再去和复杂的SQL语句打交道，只要像平时操作对象一样操作它就可以了,更加面向对象。

三、MySQL介绍

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，目前属于 Oracle旗下产品。MySQL 是最流行的关系型数据库关系系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS(Relational Database Management System，关系数据库管理系统) 应用软件之一。
MySQL是一种关系数据库管理系统，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。
MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。MySQL 软件采用了双授权政策，分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型网站的开发都选择 MySQL 作为网站数据库。

数据库对象：

包括表，视图，函数，存储过程，索引等。

• 表：
  就是存储数据的，里面有一条条的记录，记录是由字段组成的。每个字段都有自己的字段名、字段值、字段类型（数据类型）。字段是存储数据的基本单元。类似Java中的对象,用于存储数据。
  表(table)是一种结构化的文件，可以用来存储特定类型的数据，如：学生信息，课程信息，都可以放到表中。另外表都有特定的名称，而且不能重复。表中具有几个概念：列、行、主键。 列叫做字段(Column),行叫做表中的记录,每一个字段都有:字段名称/字段数据类型/字段约束/字段长度。
• 视图
  我们可以理解为一张虚表（它在物理磁盘上并不真实存在）。视图是基于select语句查询生成的结果集。当一条select语句比较复杂而且调用的频率也比较高时，我们不想再写一遍语句，这时候可以把这条select语句创建为一个视图。每次使用视图的时候，就可以把它当成一张表来用。
• 函数
  通常是一个功能的实现，我们大部分使用的是系统函数（MYSQL提供的），我们也可以自定义函数。它的调用套路通过使用select 函数名(参数1,参数2…);来调用。它只有一个返回值。
• 存储过程
  也是一个功能的实现。我们所说的数据库编程，SQL语句编程，就主要是指自定义一个存储过程。调用存储过程是使用exec 存储过程名 参数1等来调用。它是可以返回多个值，也可以没有返回值。
• 索引
  我们之前接触过，它就像一本书的目录一样，是为了加快我们查询速度而建立的。

数据类型

（1）数值型

（2）时间类型

（3）字符串型：

char与 varchar的区别？

char(100)类型的字段一旦定义，不管里面是否真的有值，它就固定会占用100个字符对应的存储空间，这种类型对空间的利用率其实并不高。varchar(100)类型的字段定义后，它所占用的空间就是里面存储内容占用的空间，但是最大不会超过100个字符，这种类型对空间的利用率是很高的。

比如 手机号码(目前都是11位,前缀不考虑),就可以使用char定长。

四、SQL

1、SQL分类

• 数据查询语言(DQL-Data Query Language)
  代表关键字:select
• 数据操纵语言(DML-Data Manipulation Language)
  代表关键字:insert,delete,update
• 数据定义语言(DDL-Data Definition Language)
  代表关键字:create ,drop,alter,
• 事务控制语言(TCL-Transactional Control Language)
  代表关键字:commit ,rollback;
• 数据控制语言(DCL-Data Control Language)
  代表关键字:grant,revoke.

2、SQL使用

（1）TCL：用户创建和授权

• 创建用户：CREATE USER ‘username’@‘host’ IDENTIFIED BY ‘password’;
  CREATE USER 'a'@'127.0.0.1' IDENTIFIED BY '123';

• 授权：GRANT ALL PRIVILEGES ON . TO ‘username’@’%’ IDENTIFIED BY ‘password’ ;
  GRANT ALL PRIVILEGES ON *.* TO 'a'@'127.0.0.1';
  ( *.*代表所有数据库和所有表，@ 后面是访问MySQL的客户端IP地址（或是主机名），% 代表任意的客户端，如果填写 localhost 为本地访问（那此用户就不能远程访问该mysql数据库了）。

• 刷新权限：FLUSH PRIVILEGES;

• 收回权限：REVOKE ALL PRIVILEGES ON . FROM ‘a’@‘127.0.0.1’;

• 删除用户：DROP USER ‘a’@‘127.0.0.1’;

（2）DDL: 数据定义语言

• 创建数据库 dd
  CREATE DATABASE dd;

• 创建day01数据库，day01已经存在，加上if not防止出错
  CREATE DATABASE if not EXISTS day01;

• 删除 dd 数据库
  DROP DATABASE dd;

• 在dd库创建tt表，包含id和name属性
  CREATE TABLE IF NOT EXISTS tt(
  id INT,
  name VARCHAR(20)
  );

• 删除表
  DROP TABLE tt;

• 更改表名
  alter table 表名 rename 新表名;

• 更改字段名
  alter table 表名 change 列名 新列名 数据类型;
  （当然数据类型可以不改,但是必须得写）

• 添加字段
  alter table 表名add 列名类型;
  如 alter table t_teacher add birthday datetime; 默认添加到尾部
  alter table t_teacher add birthday datetime after teacher_name; 把列添加到指定列的后面
  alter table t_teacher add sex2 char(2) first; 添加到第一列,需要把表关掉,刷新一下,再打开才能显示出来

• 删除字段
  alter table 表名 drop 列名;
  如 alter table t_teacher drop birthday;

• 更改字段类型(尽量不要更改)
  alter table 表名 modify 列名 新数据类型;
  如 alter table t_teacher modify sex2 varchar(20);
  alter table 表名 modify 列名 数据类型 comment ‘该列的注释说明’; 更改类型的同时,还能添加注释说明

• 查看建表语句
  show create table 表名;

（3）DML: 数据操纵语言

添加：insert into 表名 (列名1,列名2) values (值1,值2);
删除：delete from 表名 where 列名 = 值;
修改：update 表名 set 列名1=值 , 列名2=值 where 列名 = 值;

（4）DQL: 数据查询语言

select 列限定 from 表限定 where 行限定

3、条件判断

• and 并且，相当于Java的 &&

• or 或，相当于Java的 ||

• 关系表达式
  > : 大于
  < : 小于
  >= : 大于等于
  <= : 小于等于
  = : 相等
  <> : 不等于

• between and (包含98和100)
  select * from student where score between 98 and 100;

• in
  给出一个数据集合(1,3,10,20),获取学生id在这个数据集合中的学生信息
  select * from student where id in (1,3,10,20);

• like 模糊查询
  % 匹配任意个数的任意字符
_ 匹配单个任意字符
  如 : 把name中,把姓张的查询出来
  select * from student where name like ‘张%’;
  如 : 把 name中,姓名有两个字的查询出来
  select * from student where name like ‘__’;

• Order by 排序
  select 列限定 from 表限定 order by 列名 asc/desc;
  Asc : 升序
  Desc : 降序

• Limit
  限制条数,通常和order by一起使用,因为我们使用排序之后,再去获取前几条数据,比较有价值,比如成绩前三名。
  select 列限定 from 表限定 limit 条数;
  select 列限定 from 表限定 limit 开始值(不包含) ,条数;
  如 : 查询学生表,分数前三名的信息
  select * from student order by score desc limit 3;
  如 : 查询学生表,分数第二名和第三名
  select * from student order by score desc limit 1,2;

4、单表查询 ( 组函数 )

MYSQL中有一类特殊的函数，用于统计，或者分组统计,分组关键字使用 group by。

常用组函数有 :

count(*) : 总条数
max(字段名) : 最大值
min(字段名): 最小值
avg(字段名): 平均值
sum(字段名): 总和

示例数据：

create table student (
    id int ,
    name varchar(20),
    teacher_id int,
    score decimal(18,2) ,
    primary key (id)
);
create table teacher(
    id int ,
    name varchar(20),
    primary key (id)
);
insert into teacher (id,name)values(1,'张老师');
insert into teacher (id,name)values(2,'王老师');
insert into student (id,name,teacher_id,score)values(1,'张三',1,90);
insert into student (id,name,teacher_id,score)values(2,'李四',2,88.9);
insert into student (id,name,teacher_id,score)values(3,'王五',1,45.7);
insert into student (id,name,teacher_id,score)values(4,'赵六',1,84);
insert into student (id,name,teacher_id,score)values(5,'小明',2,92.5);
insert into student (id,name,teacher_id,score)values(6,'小红',2,47);

Group by

如 : 查询每个老师分别带了多少学生(显示老师id即可)
select teacher_id, count(*) as stu_count from student group by teacher_id;

如 : 查询每个老师带的学生中的最高分数
select teacher_id, count(*) as stu_count,max(score) as stu_max_score from student group by teacher_id;

如 : 查询每个老师所带学生的总成绩与平均分
select teacher_id, sum(score) as sum,avg(score) as avg from student group by teacher_id;

Having

刚才我们使用group by 和组函数,可以对数据进行分组查询,并且也可以查询到平均值等数据，但是有时候我们也需要做一些判断,比如求出平均值了,我只想要平均值，大于60分的平均分数,这时候用where就不行了
select teacher_id, avg(score) as avg from student where avg > 60 group by teacher_id;

这个时候就需要使用having进行过滤
select teacher_id, avg(score) as avg from student group by teacher_id having avg > 60;

五、约束

（1）主键（primary key）

我们的java类,对应的就是一张表,成员变量对应一个字段,类对象对应一条数据,那么对象都有一定的唯一性。

比如判断对象是否相等,我们通常使用equals()方法和hashCode()方法,那么怎么在数据库中表示数据的唯一性呢?主键！

主键通常用于唯一确定表中的一条记录，设置为主键的字段是不能为NULL并且不能重复的。

主键可以设置在一个字段上，也可以设置在多个字段上。（但大多数场景都是设置在一个字段上，这个字段通常是业务主键或者流水号）。

主键设置可以划分为两种：

第一种 : 创建表语句时,添加主键约束

CREATE TABLE t_teacher(
	`t_id` int primary key,//写在字段后
	`t_name` VARCHAR(20)
);

CREATE TABLE t_teacher(
	`t_id` int,
	`t_name` VARCHAR(20),
	primary key (`t_id`) //单独设置
);

第二种 : 创建表完成之后,通过alter添加主键约束

语法 : alter table 表名 add primary key(列名,列名…);

create table t_teacher(
 	`t_id` int,
	`t_name` VARCHAR(20)
);
alter table t_teacher add primary key(`t_id`);

（2）外键（foreign key）

对于java代码来说,外键就是类的关联关系(一个类的成员变量是另外一个类的对象引用)，像这种一个类的变量可以找到另外一个类对象的这种关联关系,在数据库中怎么体现呢? 外键！！！

一个表中的外键列,需要参照另一个表的主键值生成，并且一个表的外键列的值和另一个表的主键值的数据类型必须一致，然后就可以通过这个表中的外键去找另一个表的主键，能找到主键就能根据主键找到对应的一行数据。

设置外键有两种方式 :

第一种 : 创建表时添加外键约束

create table t_teacher(
	`id` int primary key auto_increment,
	`name` VARCHAR(20)
);

create table t_student(
	`id` int primary key auto_increment,
	`name` VARCHAR(20),
	`teacher_id` int,
	foreign key(`teacher_id`) references t_teacher(`id`)
);

第二种 : 创建完表之后,添加外键约束

create table t_teacher(
	`id` int primary key auto_increment,
	`name` VARCHAR(20)
);

create table t_student(
	`id` int primary key auto_increment,
	`name` VARCHAR(20),
	`teacher_id` int,
);
-- 给 t_student 表添加外键
 alter table `t_student` add foreign key (`id`) references `t_teacher` (`id`);

（3）唯一（unique）

该约束用来保证表中列元素不重复，但是可以同时为 null

第一种设置方式：创建表时进行设置

create table t_student(
	`id` int primary key auto_increment,
	`name` VARCHAR(20) unique
);

第二种设置方式：创建完表之后进行设置

create table t_student(
	`id` int primary key auto_increment,
	`name` VARCHAR(20)
);
alter table t_student add unique (`name`);

（4）非空（not null）和默认值（default）

所有的类型的值都可以是null，包括int、float 等数据类型。设置为not null的字段，必须填入数据，经常和default一起使用,当不填写数据的时候，把默认值设置成指定的值

给 t_teacher 表的 name字段设置非空，默认值为 无名氏：

create table t_teacher (
	`id` int primary key auto_increment,
	`name` VARCHAR(20) not null DEFAULT '无名氏'
);

创建表之后,添加约束

create table t_teacher (
	`id` int primary key auto_increment,
	`name` VARCHAR(20)
);

alter table t_teacher  modify `name` varchar not null default '无名氏';

六、子查询

子查询又叫嵌套查询。它通常可以位于SELECT后面，FROM后面 ，WHERE后面，共三种使用场景。当我们查询一个表没有办法实现功能的时候,就需要使用子查询。

1、场景一 : select后面

select 字段名,(查询语句) from 表名;

如 : 查询所有学生的信息并显示老师的名字
select *,(
select name from teacher where id=teacher_id
) as teacher_name from student ;

当位于SELECT后面时，要注意：
（1）一定要在两个表之间找好对应关系（teacher.id必须是主键或者必须保证teacher.id在teacher表中是唯一的）
（2）子查询中只能有一个字段（子查询的结果必须是一行一列）
（3）使用子查询的时候，建议大家养成使用别名的好习惯，这样可以让我们的查询语句更加清晰。别名可以用来命令新字段，也可以用来命名新表。

2、场景二 : from后面

select *,
	case rank
	when 'A' then '优'
	when 'B' then '良'
	when 'C' then '差'
	end rank_ch
from (
	select *,
	case 
	when score < 60 then 'C' 
	when score >=60 and score <80 then 'B' 
	when score >=80 then 'A' 
	end as rank 
from student
) a;

（1）我们可以把子查询当成一张表
（2）必须要有别名,因为子查询优先被执行,子查询的别名,可以让别的查询当做表或者列去操作

3、场景三 : where后面

如 : 在不知道teacher_id 和 老师名字的对应关系的情况下,想查询出张老师下面的所有学生信息
select * from student where teacher_id in (
select id from teacher where name=‘张老师’
);

当位于WHERE后面时，要注意：
（1）多条数据要用in而不要用=,如果确定子查询的结果为一行一列的话,就可以用 =
（2）如果返回结果为多行一列的话，要用 in
（3 ）子查询中的SELECT后面只能有一个字段（多个字段的话会报错）

七、Union与 union all

八、常用函数

• select version() ;显示当前MySQL软件的版本
• select database();显示当前所处数据库是哪个
• select char_length(‘中国’);返回字符个数。
• select length(‘中国’);返回字符所占字节数,MySQL中，一个UTF8编码的汉字占3个字节
• select concat( ‘a’, ‘b’, ‘c’, ‘d’);返回 ‘abcd’。字符串拼接函数
• select concat_ws( ‘=’, ‘a’, ‘b’, ‘c’);返回 ‘a=b=c’。字符串拼接函数,第一个是拼接间隔符
• select upper(‘abcd’);返回ABCD。将参数中所有小写字母转换为大写
• select lower(‘ABCD’);返回abcd。将参数中所有大写字母转换为小写
• select substring( ‘系统信息类’, 1, 3 );返回 系统信。第2个参数代表从1开始的第几个字符，第3个参数代表截取字符个数
• select trim(’ abc ');返回 abc。用于删去参数左右的所有空格
• select curdate();返回当前日期
• select curtime();返回当前时间
• select now();返回当前日期时间
• select unix_timestamp();返回当前日期时间对应的时间戳（单位秒）
• select unix_timestamp(‘2018-05-24 20:00:00’);返回参数指定的日期时间对应的时间戳（单位秒）
• select from_unixtime(1527163397);返回参数指定时间戳（单位秒）对应的日期时间
• select datediff( ‘2018-05-23’, now() );返回两个参数对应日期相差的天数（用第一个参数减第二个参数）
• select adddate( now(), -2 );返回指定天数前/后的日期时间（第一个参数是日期时间，第二个参数是天数，向后加是正数，向前减是负数）
• select year(‘2019-02-24’);返回2019 获得年份
• select month(‘2019-02-24’) 返回2 获得月份
• select day(‘2019-02-24’) 返回 24 获取日
• select if( <判断条件>, <条件为真时的返回值>, <条件为假时的返回值> );相当于Java中的三目运算符<判断条件> ? <条件为真的返回值> : <条件为假的返回值>。
  如select if(1=1, 2, 3);返回2。
• select ifnull(<表达式或者字段>, <表达式或者字段为NULL时的返回值>);通常用于给有可能有NULL的情况下的提供默认值。
• select ifnull(null,‘无名氏’) ; null这里可以写列名 就会把该列值为null的 以无名氏显示