Mysql的知识点虽然很多，但是我总结了一些经常出现的考点供大家学习记忆

谈到Mysql，首先我们就需要知道SQL到底是什么，我们为什么要用SQL。

SQL概念：

Structure Query Language(结构化查询语言)简称SQL，它被美国国家标准局(ANSI)确定为关系型数据库语言的美国标准，后被国际化标准组织(ISO)采纳为关系数据库语言的国际标准。

后面的介绍太长了，你只需要知道它是一门查询语言就可以了。

通过SQL我们可以去定义和操作数据，维护数据的完整性和安全性。

SQL有几种呢？

1）DDL(Data Definition Language)数据定义语言，用来操作数据库、表、列等，常用语句：CREATE、 ALTER、DROP；

2）DML(Data Manipulation Language)数据操作语言，用来操作数据库中表里的数据，常用语句：INSERT、 UPDATE、 DELETE，俗称增删改；

3）DQL(Data Query Language)数据查询语言，用来查询数据，常用语句：SELECT，目前SQL里面用的最多的；

4）DCL(Data Control Language)数据控制语言，用来操作访问权限和安全级别； 常用语句：GRANT、DENY。

讲完了SQL，我们就要来谈谈数据库了，以及数据库设计的三大范式了

数据库：长期储存在计算机内、有组织的、可共享的大量数据的集合。

三范式：

1）第一范式(1NF)：字段具有原子性，不可再分。所有关系型数据库系统都满足第一范式；

2）第二范式(2NF)：确保表中的每列都和主键相关。

如果表是单主键，那么主键以外的列必须完全依赖于主键，

如果表是复合主键，那么主键以外的列必须完全依赖于主键，不能仅依赖主键的一部分。

满足第二范式(2NF)必须先满足第一范式(1NF)；

3）第三范式(3NF)：确保非主键列都和主键直接相关，而不是间接相关，非主键列之间不能相关依赖。

满足第三范式必须要先满足第二范式。

数据库了解之后，我们要知道如何去操作数据库

1）DDL操作：

创建一个数据库

-- create database 数据库名称; -- 创建一个叫db1的数据库 create database db1;

查看创建的数据库基本信息

-- 查看创建的数据库信息 -- show create database 数据库名称; show create database db1;

查出所有的数据库

show databases;

删除数据库

drop database db1;

修改数据库的字符集为utf-8

-- alter database 数据库名称 character set 字符集; alter database db1 character set utf8;

切换数据库

use db1;

查看当前使用的数据库

select database();

2）DML操作

创建表

-- create table 表名( 字段1 字段类型, 字段2 字段类型, … 字段n 字段类型 ); create table student( id int, name varchar(20), gender varchar(10), );

查看当前数据库中的所有表

show tables;

查看表信息

-- show create table 表名; show create table student;

查看字段信息

-- desc 表名; desc student;

修改表名

-- alter table 原来表名 rename to 新表名; alter table student rename to stu;

修改字段名

-- alter table 表名 change 原来字段名 新字段名 varchar(10); alter table stu change name newname varchar(10);

修改字段数据类型

-- alter table 表名 modify 字段名 字段类型; alter table stu modify newname int;

增加字段

-- alter table 表名 add 新加字段名 类型; alter table stu add address varchar(100);

删除字段

-- alter table 表名 drop 字段名; alter table stu drop address;

删除表

-- drop table 表名; drop table stu;

插入数据

-- insert into 表名（字段名1,字段名2,...) VALUES (值 1,值 2,...); insert into student (id,name,age,gender) values (1,a,20,male);

同时插入多条数据

-- insert into 表名 [(字段名1,字段名2,...)] VALUES (值 1,值 2,…),(值 1,值 2,…),...; insert into student (id,name,age,gender) values (2,b,17,female),(3,c,19,male),(4,d,18,male);

更改数据

-- update 表名 set 字段名1=值1[,字段名2 =值2,…] [where 条件表达式]; update student set age=20,gender=female where name=d;

更改所有数据

-- update 表名 set 字段名=值; update student set age=18;

删除数据

-- delete from 表名 [where 条件表达式]; delete from student where age=14;

删除所有数据

-- delete from 表名; delete from student;

这里说一个删除有关的细节知识

truncate和delete都能实现删除表中的所有数据的功能，但两者也是有区别的：

truncate和delete的区别：

(1）delete语句后可以跟where子句，可通过指定where子句中的条件表达式只删除满足条件的部分记录。

truncate语句只能用于删除表中的所有记录；

(2）使用truncate语句删除表中的数据后，再次向表中添加记录时自动增加字段的默认初始值重新由1开始。

使用delete语句删除表中所有记录后，再次向表中添加记录时自动增加字段的值为删除时该字段的最大值加1；

(3）delete语句是DML语句，truncate语句通常被认为是DDL语句。

3）DQL操作

查询所有字段

-- select * from 表名; select * from student;

查询指定字段

-- select 字段1,字段2 from 表名; select id,name from student;

查询时过滤重复数据

-- select distinct 字段 from 表名; select distinct gender from student; -- 注意！！！在查询语句中distinct关键字只能用在第一个所查列名之前

因为可能真正开发时还有一些其他的需求，所以在DQL中聚合函数也是很重要的

常用的聚合函数：

count()：统计表中的数据或者指定列的值不为Null的数据个数。

查询该表中有多少人

select count(*) from student;

max()：计算指定列的最大值，如果指定列是字符串类型则使用字符串排序运算。

查询该表中年龄的最大值

select max(age) from student;

min()：计算指定列的最小值，如果指定列是字符串类型则使用字符串排序运算。

查询该表中年龄的最大值

select min(age) from student;

sum()：计算指定列的数值和，如果指定列类型不是数值类型则计算结果为0；

查询该学生表中年纪的总和

select sum(age) from student;

avg()：计算指定列的平均值，如果指定列类型不是数值类型则计算结果为0；

查询该学生表中年纪的平均数

select avg(age) from student;

当然，聚合函数使用也是有规则的，只有SELECT子句和HAVING子句、ORDER BY子句中能够使用聚合函数。

除了聚合函数，DQL中还有一些其他的查询，比如条件查询，这个时候我们就需要用到WHERE关键字去过滤了

关系运算符，比如>=、=、<=等等

查询年龄等于或大于18的学生的信息

select * from student where age>=18;

IN关键字查询

IN关键字用于判断某个字段的值是否在指定集合中，如果字段的值在指定的集合中，则将字段所在的记录将査询出来。

查询ID在(1，10)的字段信息

select * from student where id in (1,10); -- 如果查询的不在这里面的话就是 select * from student where id not in (1,10);

BETWEEN AND关键字查询

BETWEEN AND用于判断某个字段的值是否在指定的范围之内,如果字段的值在指定范围内，则将所在的记录将查询出来。

查询年龄在12-18之间的范围

select * from student where age between 12 and 18; -- 如果查询的不在这里面的话就是 select * from student where age not between 12 and 18;

空值查询

使用 IS NULL关键字判断字段的值是否为空值，

注意！！！空值NULL不同于0，也不同于空字符串。

查询ID不为控制的信息

select * from student where id is not null;

AND关键字查询

AND关键字可以连接两个或者多个查询条件，全部满足才可以查询出来。

查询ID大于5并且年龄等于18的信息

select * from student where id> 5 and age= 18;

OR关键字查询

OR关键字连接多个査询条件，只要满足其中任意一个条件就会被查询出来。

查询ID大于5或者年龄等于18的信息

select * from student where id> 5 or age= 18;

LIKE关键字查询

LIKE关键字可以判断两个字符串是否相匹配，如果匹配则会查询出匹配的结果。

查询name中与zhangsan匹配的学生信息

select * from student where name like zhangsan;

匹配中也有一种情况，就是含有%通配的字符串，%用于匹配任意长度的字符串。

查询name以li开始的信息

select * from student where name like li%; -- lisi liwu liliu都可以查询出来

还有一种则是还有_通配的字符串，下划线通配符只匹配单个字符，如果要匹配多个字符，需要连续使用多个下划线通配符。

查询姓名为zhao开头且长度为7的

select * from student where name like zhao___; -- zhaoliu 后面通配符是三个

LIMIT限制查询结果的数量

当我们执行查询数据时，有时想查一条可能会返回很多条，这个时候就需要对查询结果进行限制了

查询分页条数

-- 分页 公式：开始的索引 = （当前的页码-1 ）* 每页显示的条数 select * from student limit 0,3; -- 前一个表示从0条开始 ，后面一个表示一页显示几个 select * from student limit 3,3; select * from student limit 6,3;

GROUP BY进行分组查询

一般情况下，GROUP BY和聚合函数是一起使用的。

查询各个班级的学生数量，按照班级分组

select count(*), classNumber from student group by classNumber;-- 从不同的班级统计学生数量

统计学生数量大于50的班级

select sum(*),classNumber from student group by classNumber having sum(*)>50;

ORDER BY对查询结果排序

按年龄升序查询

-- select 字段名1,字段名2,… from 表名 order by 字段名1 [ASC 丨 DESC],字段名2 [ASC | DESC]; select * from student order by age asc;

基本的操作讲完之后，我们就要了解一下Mysql事务的四大特性了(高频考点)

事务的四大特性：ACID

1）原子性(Atomicity)

原子性意味着数据库中的事务执行是作为原子，即不可再分，整个语句要么同时执行，要么同时不执行；

2.一致性(Consistency)。

一致性即在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。比如说，我有50我给你转账10块钱，不管成功了还是失败了，我和你的金额总数保持不变(50)；

3.隔离性(Isolation)

事务的执行是互不干扰的，相互隔离的，即一个事务不影响其它事务运行效果。你走你的阳关道，我过我的独木桥，互不打扰；

4.持久性(Durability)

意味着在事务完成以后，该事务对数据库所作的更改便持久的保存在数据库中，不会被回滚。即使出现了任何事故比如断电等，事务一旦提交，则持久化保存在数据库中。

Char和Varchar的区别(高频考点)

区别：

1）长度不同

char类型的长度是固定的，

varchar类型的长度是可变的；

2）效率不同

char类型每次修改的数据长度相同，效率更高，

varchar类型每次修改的数据长度不同，效率更低；

3）存储不同

char类型存储的时候是初始预计字符串再加上一个记录字符串长度的字节，占用空间较大，char最多可以存放255个字符。

varchar类型存储的时候是实际字符串再加上一个记录字符串长度的字节，占用空间较小，

varchar的最大长度为65535个字节，可存放的字符数跟编码有关。

数据表也是有约束的，为了防止错误的数据被插入到数据表中，MySQL中定义了一些维护数据库完整性的规则，这些规则常称为表的约束。

主键约束

主键约束(primary key)用于唯一的标识表中的每一行，被标识为主键的数据在表中是唯一的且其值不能为空，比如我们每个人的身份证就是唯一标识。

-- 字段名 数据类型 primary key; create table student( id int primary key, name varchar(20) );

非空约束

非空约束(NOT NULL)指的是字段的值不能为空。

-- 字段名 数据类型 NOT NULL; create table student( id int name varchar(20) not null );

唯一约束

唯一约束(UNIQUE)用于保证数据表中字段的唯一性，即表中字段的值不能重复出现。

-- 字段名 数据类型 UNIQUE; create table student( id int, name varchar(20) unique );

默认约束

默认约束(DEFAULT)用于给数据表中的字段指定默认值，当在表中插入一条新记录时若未给该字段赋值，那么数据库系统会自动为这个字段插人默认值。

-- 字段名 数据类型 DEFAULT 默认值； create table student( id int, name varchar(20), gender varchar(10) default male );

外键约束

外键约束(FOREIGN KEY)常用于多张表之间的约束。

-- CONSTRAINT 外键名 FOREIGN KEY (从表外键字段) REFERENCES 主表 (主键字段) -- ALTER TABLE 从表名 ADD CONSTRAINT 外键名 FOREIGN KEY (从表外键字段) REFERENCES 主表 (主键字段); -- 学生表 主表 create table student( id int primary key, name varchar(20) ); -- 班级表 从表 create table class( classid int primary key, studentid int ); constraint fk_class_studentid foreign key(studentid) references student(id); alter table class add constraint fk_class_studentid foreign key(studentid) references student(id);

删除外键

-- alter table 从表名 drop foreign key 外键名； alter table class drop foreign key fk_class_studentid;

注意！！！

1）从表里的外键通常为主表的主键；

2）从表里外键的数据类型必须与主表中主键的数据类型一致。

既然谈到了多张表，那么就要讲一下表之间的关联关系了

在这里我们只说到了一对一的表关系，但是在实际开发中，表的关系大致有三种。

一对一

多对一：

多对一(一对多)是数据表中最常见的一种关系，比如像学生和班级，一个班级可以有很多学生，但是一个学生只对应一个班级。在多对一的表关系中，应将外键建在多的一方，否则会造成数据的冗余。

多对多：

多对多是数据表中常见的一种关系，比如像学生和老师，一个老师可以教多个学生，一个学生也可以被多个老师教。在多对多的表关系中，需要定义一张中间表(连接表)，该表会存在两个外键分别参照老师表和学生表。

涉及到多张表时，就需要我们进行关联查询了。

关联查询

查询所有JAVA的学生

-- 创建班级表 CREATE TABLE class( cid int(4) NOT NULL PRIMARY KEY, cname varchar(30) ); -- 创建学生表 CREATE TABLE student( sid int(8) NOT NULL PRIMARY KEY, sname varchar(30), classid int(8) NOT NULL ); -- 为学生表添加外键约束 ALTER TABLE student ADD CONSTRAINT fk_student_classid FOREIGN KEY(classid) REFERENCES class(cid); -- 向班级表插入数据 INSERT INTO class(cid,cname)VALUES(1,Java); INSERT INTO class(cid,cname)VALUES(2,Python); -- 向学生表插入数据 INSERT INTO student(sid,sname,classid)VALUES(1,zhangsan,1); INSERT INTO student(sid,sname,classid)VALUES(2,lisi,1); INSERT INTO student(sid,sname,classid)VALUES(3,wangwu,2); INSERT INTO student(sid,sname,classid)VALUES(4,zhaoliu,2); -- 关联查询 select * from student where classid=(select cid from class where cname=Java);

当然除了关联查询，多表还有连接查询

连接查询

连接查询中又包含三种查询：内连接查询、左外连接查询、右外连接查询

通过代码会更清楚

-- 创建班级表 CREATE TABLE class( cid int (4) NOT NULL PRIMARY KEY, cname varchar(20) ); -- 创建学生表 CREATE TABLE student ( sid int (4) NOT NULL PRIMARY KEY, sname varchar (20), sage int (2), classid int (4) NOT NULL ); -- 向班级表插入数据 INSERT INTO class VALUES(1001,Java); INSERT INTO class VALUES(1002,C++); INSERT INTO class VALUES(1003,Python); INSERT INTO class VALUES(1004,PHP); -- 向学生表插入数据 INSERT INTO student VALUES(1,zhangsan,10,1001); INSERT INTO student VALUES(2,lisi,11,1002); INSERT INTO student VALUES(3,wangwu,14,1002); INSERT INTO student VALUES(4,zhaoliu,13,1003);

内连接查询

内连接(Inner Join)称简单连接或自然连接，是一种非常常见的连接查询。

内连接使用比较运算符对两个表中的数据进行比较并列出与连接条件匹配的数据行，组合成新的记录，也就是说在内连接查询中只有满足条件的记录才能出现在查询结果中。

查询学生姓名及其所属班级名称

-- SELECT 查询字段1,查询字段2, ... FROM 表1 [INNER] JOIN 表2 ON 表1.关系字段=表2.关系字段 select student.sname,class.cname from class inner join student on class.cid= student.sid;

外连接查询

在内连接查询时，返回的结果只包含符合查询条件和连接条件的数据。

但是，我们有时还需要在查询结果中不仅包含符合条件的数据，而且还包括左表、右表或两个表中的所有数据，此时我们就需要使用外连接查询，外连接分为左外连接和右外连接。

-- SELECT 查询字段1,查询字段2, ... FROM 表1 LEFT | RIGHT [OUTER] JOIN 表2 ON 表1.关系字段=表2.关系字段 WHERE 条件;

注意！！！在使用左外连接和右外连接查询时，查询结果是不一样的。

1）LEFT [OUTER] JOIN 左外连接，返回包括左表中的所有记录和右表中符合连接条件的记录，俗话说就是从左到右；

2）RIGHT [OUTER] JOIN 右外连接，返回包括右表中的所有记录和左表中符合连接条件的记录，俗话说就是从右到左。

左外连接查询

左外连接的结果包括LEFT JOIN子句中指定的左表的所有记录，还有所有满足连接条件的记录。如果左表的某条记录在右表中不存在则在右表中显示为空。

查询每个班的班级ID、班级名称还有班级的所有学生的名字

select class.cid,class.cname,student.sname from class left outer join student on class.cid=student.classid;

右外连接查询

右外连接的结果包括RIGHT JOIN子句中指定的右表的所有记录，以及所有满足连接条件的记录。如果右表的某条记录在左表中没有匹配，则左表将返回空值。

select class.cid,class.cname,student.sname from class right outer join student on class.cid=student.classid;

除了连接查询之外，常用的还有子查询，

子查询：

一个查询语句嵌套在另一个查询语句内部的查询，该查询语句可以嵌套在一个SELECT、SELECT…INTO、INSERT…INTO等语句中。

在执行查询时，首先会执行子查询中的语句，再将返回的结果作为外层查询的过滤条件。

在子査询中通常可以使用比较运算符、IN、EXISTS、ANY、ALL等关键字。

先建表演示

-- 创建班级表 CREATE TABLE class( cid int (4) NOT NULL PRIMARY KEY, cname varchar(20) ); -- 创建学生表 CREATE TABLE student ( sid int (4) NOT NULL PRIMARY KEY, sname varchar (20), sage int (2), classid int (4) NOT NULL ); -- 向班级表插入数据 INSERT INTO class VALUES(1001,Java); INSERT INTO class VALUES(1002,C++); INSERT INTO class VALUES(1003,Python); INSERT INTO class VALUES(1004,PHP); -- 向学生表插入数据 INSERT INTO student VALUES(1,zhangsan,10,1001); INSERT INTO student VALUES(2,lisi,11,1002); INSERT INTO student VALUES(3,wangwu,14,1002); INSERT INTO student VALUES(4,zhaoliu,13,1003);

比较运算符的子查询

查询张三同学所在班级的信息

select * from class where cid=(select classid from student where sname=zhangsan);

EXISTS关键字的子查询

EXISTS关键字后面的参数可以带任意一个子查询， 不会产生任何数据，它只返回TRUE或FALSE，当返回值为TRUE时外层查询才会执行。

张三同学若在学生表中，则从班级表中查询所有班级信息

select * from class where exists (select * from student where sname=zhangsan);

ANY关键字的子查询

ANY关键字表示满足其中任意一个条件就返回一个结果作为外层查询条件。

查询比任一学生所属班级号码还大的班级号码

select * from class where cid > any (select classid from student);

带ALL关键字的子查询

ALL关键字的子査询返回的结果需同时满足所有内层査询条件。

查询比所有学生所属班级号码还大的班级号码

select * from class where cid > all (select classid from student);

未完待续，日常更新！