MySQL笔记 —— 基础（概念，对于数据库、表、数据的各种操作语句）

关系型数据库

数据库与普通文件系统的区别在于，数据库拥有数据结构。数据库与普通的txt文件都能存储数据内容，但是数据库拥有数据结构，能够快速查找

而根据数据结构的不同，数据库又可以分为关系型数据库和非关系型数据库

关系型数据库：是指采用了关系模型来组织数据的数据库
关系模型有一对一，一对多，多对多等，关系模型指存储格式是以行列组成的二维表格，其以行和列的形式存储数据，一组行和列被称为一张表。一个关系型数据库就是由二维表及其之间的联系所组成的一个数据组织

非关系型数据库的模型有：

1. 列模型：存储的数据是以列的形式存放的。比如学生信息，一个完整的学生信息是放在一个列中，一个列中可以有多个属性，不同列的属性数量可以不同
2. 键值对模型：存储的数据是键值对的形式
3. 文档类模型：用一个个的文档来存放数据

与列模型以一列为单位存储一条数据不同，关系型数据库是以行为单位来存储数据，一行是一条数据，而不同的列是不同的属性。

关系模型中常用的概念：
关系：一张二维表，每个关系都具有一个关系名，也就是表名
元组：二维表中的一行，在数据库中被称为记录
属性：二维表中的一列，在数据库中被称为字段
域：属性的取值范围，也就是数据库中某一列的取值限制
关键字：一组可以唯一标识元组的属性，数据库中常称为主键，由一个或多个列组成

MySQL就是关系型数据库

安装MySQL时，会安装MySQL的数据库服务器，数据库服务器实质上是一个数据库管理程序，这个管理程序可以管理多个数据库，一般会为每一个应用创建一个数据库。比如说，学校要存储学生和老师的信息，这两者的信息放在一起的话，既可能造成数据的重复冲突，也不利于数据的查询保管，因此分别为学生和老师各创建一个数据库。

而为了保存实体的数据，一般又会在数据库中创建多张表，表里面保存的是实际的数据
比如 10086 一纸春秋 18 男 爱好看书。像这样就是一条数据，里面有五个字段（属性）

具体的结构大概就像这样

使用MySQL，需要通过下面四种SQL语句

• DDL 数据定义语言
  用来定义数据库的对象，比如数据表，视图，索引等
• DML 数据处理语言
  在数据库表中更新、增加和删除记录，比如update、insert、delete
• DCL 数据控制语言
  指用于设置用户权限和控制事务语句，比如grant，if-else，while等
• DQL 数据查询语言，只有一个，那就是 select

创建、查看、删除数据库

• 创建数据库 create database show1;
  
• 查询数据库的创建信息 show create database show1;
  
• 查看已经创建的数据库 show databases;
  
• 删除数据库 drop database show1;
  
• 创建数据库时指定编码格式 create database show1 character set utf8;
  
• 修改已经创建的数据库的编码格式 alter database show2 character set utf8;
  
• 另外创建数据库与删除数据库的时候，还可以加上条件判断。
  分别是if not exists如果不存在则创建 ，if exists 如果存在则删除
  
  
• 切换数据库 use 数据库名
  
• 查看当前使用的数据库 select database();
  

注意，MySQL中不允许修改数据库的名字

创建表、查询表、删除表

1. 创建表

create table if not exists stu1(
	id int not null,
	name varchar(255) not null,
	age int not null,
	primary key(id)
)engine=innodb default charset=utf8;

定义一张表需要，表名，字段名，对每个字段的说明
像上面这个例子，stu1就是表名。id、name、age都是字段名。而字段名后面跟着的各个参数就是对字段的定义要求。

注意一般是int(11)，varchar(255)，int(11)可以简写为int，varchar(255)不能简写

对字段的定义要求又分为两个部分，第一部分是数据类型，第二种是各种参数

数据类型的类型具体可以到菜鸟驿站看
https://www.runoob.com/mysql/mysql-data-types.html

各种参数的含义：

• not null的含义是禁止此字段的数据为空，为空则报错
• AUTO_INCREMENT的含义是自增，比如给id字段加上这个，那么第一个学生的id是1000，如果第二个学生在输入数据时没有给定id值，id就会自动加一变成1001（通常是用在主键上，不能用在其他字段上）
• primary key将列定义为主键，可以指定多个列为主键，列之间以逗号分隔
• engine用于设置存储引擎，一般都设置为InnoDB
• default charset 用于设置编码格式
  如果不设置编码格式，那么表的编码格式与所在数据库的编码格式保持一致

2. 查询建表语句 show create table 表名;
   
3. 查询当前数据库下的所有表 show tables;
   
4. 删除表 drop table if exists 表名;
   

在mysql的命令行模式敲习惯之后，其实会发现代码修改起来很麻烦，比如不小心回车到了下一行，那么发现上一行的代码出现了问题，也不能回去修改。所以在终端练习，练习完之后建议使用navicat，用图形化界面来操作mysql

使用navicat之间，先新建查询，然后输入

set @@global.sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

然后选中这句话，右键执行选中的语句，然后断开连接再重新连接。完成后就可以正常写sql语句了。

新建查询，即可在这里输入代码，比如这里输入了一个建表语句

CREATE TABLE stu2 (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `sex` enum('0','1') DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8

这里与上面不同的是，使用了enum枚举作为数据类型，即输入数据时只能从0和1中选择，0和1分别表示男生和女生的意思

这里还设置了AUTO_INCREMENT=1001，表示自增序号从1001开始，之后每一条数据都会加1

创建表格成功

修改表结构

• 修改表名：alter table 旧表名 to 新表名;
• 添加字段：alter table 表名 add 字段 字段数据类型 属性;
• 修改字段：alter table 表名 change 旧字段 新字段 数据类型 属性;
• 修改字段：alter table 表名 modify 字段 数据类型 属性;
• 删除字段：alter table 表名 drop 字段名;

注意：

1. 可以将int类型改为varchar类型，但不能将varchar类型改为int类型
2. 字符串需要用单引号包裹，数字不用

-- 修改表名
rename table stu2 to stu;
-- 添加字段
alter table stu add age int not null;
desc stu;
-- 修改字段的全部内容
alter table stu change name StudentName varchar(255) not null;
desc stu;
-- 修改字段的部分内容
alter table stu modify sex int not null;
desc stu;
-- 删除字段
alter table stu drop sex;
desc stu;

change 和 modify 的区别是change一次性修改所有内容包括字段名，而modify则是只修改数据类型和属性。

每次修改完，都可以用 desc 表名; 语句快速查询表格结构

最后修改完的表格结构就是上面这张图这样

在navicat里面运行语句时，可以将多个不相关的，结尾有；结束符的语句都选中，然后一起运行

插入数据

insert into 表名(字段1，字段2，……) values(值1，值2，……)，(值1，值2，……)……；

当字段列表为全部字段时，可以简写为insert into 表名 values(字段值1，字段值2，……);

字段列表中可以给出表结构的全部字段，也可以只给出部分字段。后面的值就是对应位置的字段的值。比如字段列表是name,age，那么值就是’zhang’，12按位置顺序对应name和age。而只给出部分字段时，后面的值也要跳过此字段，然后结果默认填充为 null

注意：如果只给出部分属性，必须给出所有设置为not null的属性

-- 插入数据
-- 为了方便观察，这里新增一个属性sex，sex属性的值只能是0或1。
-- 然后将age设置为not null，插入值的时候才能跳过这个字段
alter table stu add sex enum('0','1') not null;
alter table stu modify age int default null;

-- 插入一条数据，新增一个属性sex，sex属性的值只能是0或1
insert into stu(id,StudentName,age,sex) values(10001,'zhang',18,'0');

-- 插入一条有空值的数据（age值为null）
insert into stu(id,StudentName,sex) values(10002,'chen','1');

-- 插入多条数据
insert into stu(id,StudentName,age,sex) values(10003,'qian',15,'0'),(10004,'cao',22,'1');

-- 快速插入一条数据
insert into stu values(10005,'sun',19,'0');

-- 最后用select查询表内数据
select * from stu;

删除数据

delete from 表名 where 字段=字段值；

-- 删除一条数据
delete from stu where id=10002;
select * from stu;

直接delete from 表名; 可以清空表里面的内容

修改数据

update 表名 set 字段名=字段值 where 字段号；

-- 修改一个字段
update stu set StudentName='liu' where id=10004;
-- 修改多个字段
update stu set StudentName='wang',age=80 where id=10005;

查询语句

就是之前用了很多次的 select 字段名 from 表名;

-- 查询一个表中的部分字段(查询全部数据用*）
select StudentName,age from stu;

-- 查询指定行的数据
select * from stu where StudentName='zhang';

执行select语句会生成一张虚拟表，这张表格并不是真实存在，查询语句结束输出结果后，就会消失。但是针对select语句生成的虚拟表，同样可以进行查询

-- 对虚拟表进行查询
select id,StudentName from(select * from stu as s2) as s1;

但是要注意的是，如果对虚拟表进行查询，那么必须给表取一个别名。被查询的虚拟表和显示结果的虚拟表都需要一个别名。

另外，显示的虚拟表中的列的顺序，是按照select语句选中的字段顺序来定的
比如 select StudentName,id,age from stu;

可以发现列的顺序改变了，StudentName的顺序在id的前面

where与having的区别

1. where子句针对整个元数据表格，在聚合前筛选数据，执行顺序在group by和having子句前。而 having子句是对聚合后的一组数据进行筛选
2. where子句中不能是有聚合函数，having子句中可以使用聚合函数

例子，按照性别分组，选择平均年龄大于20的那一组

-- 使用 where子句，会报错，错误信息Invalid use of group function
select * from stu1 where avg(age)>18 group by id;
-- 使用having子句，可以正常使用avg(age)进行筛选选择
select * from stu1 group by id having avg(age)>18;

这里的having用法其实就相当于求每个年龄大于18的学生，这是因为按id分组，每个组里面只有一条数据。

语句的执行顺序

查询时会主要用到的关键字大概有六个，书写时的顺序是
select —— from —— where —— group by —— having —— order by

但是实际执行的时候，是按照下面这个顺序执行的
from —— where —— group by —— having —— select —— order by

下面解释一下各个关键字：
from：选择具体查询哪个表的数据
where：针对表格数据进行筛选过滤的条件
group by：按照某个字段进行分组（比如性别只有0和1，group by sex后，结果就只有两行，一行sex为0，一行sex为1）
having：对已经分组的数据进行筛选
select：查看查询结果
order by：对select的查询结果进行排序

更加详细的执行顺序是：
from —— join on —— where —— group by（从这里开始，包括这里都可以使用select中的别名）
—— 聚合函数 —— having —— select —— distinct —— order by —— limit

别名

select id , StudentName as n from stu;

select id ,StudentName from stu as student;

关于表别名与字段别名的区别：
表别名只在执行查询时使用，返回结果中并不会显示，而给字段定义别名后，会返回给客户端显示，显示的字段名就是定义的别名。

子句中的运算符

select * from stu where age>20;

select * from stu where age BETWEEN 18 and 22;
select * from stu where age in (18,22);

select * from stu where StudentName like'z%';
select * from stu where StudentName like'l_';

select * from stu where id is null;
select * from stu where id is not null;

注意：

1. between and 和 in的区别，between and是一个区间，只要是在18到22之间的数字都算。而 in 是列表，只有18和22两个数字满足要求，18和22之间的数字不算。
2. like里面的 %是匹配任意多个字符，_ 是只匹配一个字符
3. is null是选中所有该字段为null值的数据，is not null则相反，选中所有该字段不为空的数据

select * from stu; 初始为

逻辑运算符

1. 使用and：select * from stu where StudentName=‘zhang’ and age=18;
   
   and左右的两个条件都满足才选中这条数据

2. 使用or：select * from stu where StudentName=‘zhang’ or age=18;
   
   or 左右的条件只要满足一个就会被选中

3. 使用not，select * from stu where not(age>20);
   
   将条件反转

limit 分页

格式： limit 开始下标,长度;

select * from stu limit 0,3;

只显示前三行的数据