超详细mysql知识点总结

为什么用Mysql？

在互联网公司用的相对多一些，mysql数据库免费 小巧轻量级，安装也简单方便 互联网公司像京东、阿里并发量很大，搭建多个服务器，用免费的数据库代价低。

什么是数据库？什么是数据库管理系统？什么是SQL？他们之间的关系是什么？

数据库:DataBase,简称DB。按照一定格式存储的一些文件的组合。

顾名思义:存储数据的仓库，实际上就是一堆文件。这些文件中存储了具有特定格式的数据。

数据库管理系统：

DataBaseManagement，简称DBMS。数据库管理系统是专门用来管理数据库中的数据的，数据库管理系统可以对数据库当中的数据进行增删改查。

常见的数据库管理系统：

Mysql、Oracle、MS sqlserver、DB2、sybase等…

SQL：结构化查询语言

程序员需要学习SQL语句，通过编写SQL语句，然后DBMS负责执行SQL语句，最终来完成数据库中数据的增删改查操作。

SQL是一套标准，这个SQL语句在任何数据库管理系统中都可以使用。

三者之间的关系？

DBMS–执行–>SQL–操作–>DB

查看mysql中有哪些数据库？

mysql>show databases;

mysql默认自带了4个数据库。

怎么选择使用某个数据库呢？

mysql>use test;

创建数据库

mysql>create school;

数据库中最基本的单元是表：table

查看数据库中的表

mysql>show tables;

什么是表？为什么用表存储数据？

excel表格数据就是表，数据库中存储数据是以表格形式表示数据的，因为表比较直观，任何一张表都有行和列。

行（row）：被称为数据/记录。

列（column）：被称为字段。

sql语句的分类

DQL:数据查询语句（凡是带有select关键字的都是查询语句）

DML:数据操作语言（凡是对表中的数据进行增删改查的都是）

insert 增

delete 删

update 改

DDL:数据定义语言（凡是带有create、drop、alter的都是）

DDL主要操作的是表的结构，不是表中的数据

create：增

drop：删除

alter：修改

TCL:是事务控制语言

包括：事务提交：commit；

​ 事务回滚：rollback；

DCL:是数据控制语言。

例如：授权grant、撤销权限revoke

查看表中的数据

mysql>select * from school;

只查看表的结构

mysql>desc school;

注意：mysql不见分号不执行！

简单查询

1、查询一个字段？

select 字段名 from 表名；

其中要注意：select和from都是关键字

​ 字段名和表名都是标识符

2、查询两个字段或多个字段？

使用逗号隔开

查询所有 第一种方式：把每个字段都写上

​ 第二种方式：select * from 表名

3、给查询的列起别名

mysql>select deptno,dname as deptname from dept;

使用as关键字起别名

注意：只是将显示查询结果列名显示为deptname，原列名还是叫：dname

记住：select语句是永远都不会进行修改操作的。（因为只负责查询）

条件查询

select 字段1，字段2，字段3 from 表名 where 条件；

between…and… 两个值之间，等同于>= and <=

select empno,ename,sal from emp where sal between 2450 and 3000;

注意：使用between and的时候必须遵循左小右大原则

为null is null

不为null is not null

and 和or同时出现，有优先级问题？

例如：查询工资大于2500，并且部门编号为10或20部门的员工？

select * from emp where sal>2500 and deptno=10 or deptno=20;

分析以上语句的问题？

and优先级比or高。

以上语句会先执行and，然后执行or

select * from emp where sal>2500 and （deptno=10 or deptno=20）；

and和or同时出现，and优先级较高，如果想让or先执行，需要加“小括号”

in 包含，相当于多个or

查询工作岗位是manager和salesman的员工？

select empno，ename，job from emp where job =‘manager’ or job=‘salesman’;

select empno，ename，job from emp where job in(‘manager’,‘salesman’);

注意：in不是一个区间。in后面跟的是具体的数值

like 称为模糊查询

支持%或者下划线匹配

%匹配任意多个字符

下划线：任意一个字符

找出名字中含有o的？

select ename from emp where ename like ‘%o%’;

找出名字以T结尾的？

select ename from emp where ename like ‘%T’;

找出名字以K开始的？

select ename from emp where ename like ‘K%’;

找出第二个字母是A的？

select ename from emp where ename like ‘_A%’;

排序

查询所有员工薪资，排序

select ename,sal from emp order by sal;//默认是升序

select ename,sal from emp order by sal desc; //指定降序

select ename,sal from emp order by sal asc;//指定升序

先按照薪资排序，薪资一样，按照名字排序

select ename,sal from emp order by sal asc，ename asc;

关键字顺序不能变：

select…

from…

where…

order by…

以上语句的执行顺序：

from

where

select

order by

数据处理函数

数据处理函数又称单行处理函数

单行处理函数的特点：一个输入对应一个输出

和单行处理函数相对的是多行处理函数。（多行处理函数特点：多个输入一个输出）

单行处理函数常见的有哪些？

lower 转换小写

select lower（ename）as ename from emp;

upper 转换大写

substr 取子串（substr（被截取的字符串，起始下标，截取的长度））

select substr (ename，1，1) as ename from emp;

length 取长度

trim 去空格

分组函数（多行处理函数）

多行处理函数的特点：输入多行，最终输出一行。

5个：

count 计数

sum 求和

avg 平均值

max 最大值

min 最小值

注意：分组函数在使用的时候必须先进行分组，然后才能使用。

​ 如果没分组，默认整张表是一组数据。

找出最高工资

select max(sal) from emp;

分组函数

第一点：分组函数自动忽略null，不需要提前对null进行处理。

第二点：分组函数中count(*)和count(具体字段)有什么区别？

count（具体字段）：表示统计该字段所有不为null的元素的总数

count（*）：统计表当中的总行数。（只要有一行数据count则++）

​ 因为每一行记录不可能都为null，一行数据中有一列不为null，则该行数据就是有效的

第三点：分组函数不能够直接使用在where子句中

第四点：所有的分组函数可以组合起来一起用

分组查询

select…from…group by…

将之前的关键字全部组合在一起

select…

from…

where…

group by…

order by…

执行顺序：

1、from…

2、where…

3、group by…

4、select…

5、order by…

为什么分组函数不能直接使用在where后面？

因为分组函数在使用的时候必须分组之后才能使用。

where执行的时候，还没有分组，所以where后面不能出现分组函数。

select sum（sal） from emp；

这个没有分组，为啥sum（）函数可以用呢？

因为select在group by之后执行。

重点结论：

​ 在一条select语句当中，如果有group by语句的话，select后面只能跟参加分组的字段，以及分组函数，其它的一律不能跟。

使用having可以对分完组之后的数据进一步过滤。having不能单独使用，having不能代替where，having必须和group by联合使用。

单表查询总结

select…

from…

where…

group by…

having…

order by…

执行顺序：

1、from

2、where

3、group by

4、having

5 、select

6、order by

从某张表中查询数据，先经过where条件筛选出有价值的数据，对这些有价值 的数据进行分组，分组之后可以使用having继续筛选。select查询出来，最后排序输出。

找出每个岗位的平均薪资，要求显示平均薪资大于1500的，除manager岗位之外，要求按照平均薪资降序排。

select job，avg(sal) as avgsal

from emp

where job<>‘manager’

group by job

having avg(sal)>1500

order by avgsal desc;

把查询结果去除重复记录

注意：原表数据不会被修改，只是去除查询结果的重复记录。

去除重复需一个关键字：distinct

distinct只能出现在所有字段的最前方。

连接查询

1、什么是连接查询？

从一张表中单独查询，称为单表查询。

例如：emp表和dept表联合起来查询数据，从emp表中取员工姓名，从dept表中取部门名字。这种跨表查询，多张表联合起来查询数据，被称为连接查询。

2、连接查询的分类？

根据表连接的方式分类：

内连接：

​ 等值连接

​ 非等值连接

​ 自连接

外连接：

​ 左外连接（左连接）

​ 右外连接（右连接）

3、当两张表进行连接查询时，没有任何条件的限制，最终查询结果条数是两张表条数的乘积，这种现象被称为：笛卡尔积现象。(笛卡尔发现的，这是一个数学现象。)

内连接之等值连接

案例：查询每个员工所在部门名称，显示员工名和部门名？

sql92语法：

select e.ename,d.dname

from emp e,dept d

where e.deptno=d.deptno;

sql92的缺点：结构不清晰，表的连接条件和后期进一步筛选的条件，都放到了where后面。

sql99语法：

select e.ename,d.dname

from emp e

(inner)join dept d //inner可以省略

on e.deptno=d.deptno;

sql99优点：表连接的条件是独立的，连接之后，如果还需要进一步筛选，再往后继续添加where

内连接之非等值连接

案例：找出每个员工的薪资等级，要求显示员工名、薪资、薪资等级？

select e.ename,e.sal,s.grade

from emp e

join salgrade s

on e.sal between s.losal and s.hisal;//条件不是一个等量关系，称为非等值连接。

内连接之自连接

案例：查询员工的上级领导，要求显示员工名和对应的领导名？

技巧:一张表看成两张表

select a.ename as ‘员工名’,b.ename as ‘领导名’

from emp a

join emp b

on a.mgr=b.empno;

外连接

左连接：表示将join关键字左边的这张表看成主表，主要是为了将这张表的数据全部显示出来，捎带着关联查询右边的表。

右连接：表示将join关键字右边的这张表看成主表，主要是将右表的数据全部显示出来，捎带着关联查询左边的表。

三张表或者四张表怎么连接？

select …

from a

join b

on a和b的连接条件

join c

on a和c的连接条件

join d

on a和d的连接条件

子查询

什么是子查询？

select语句中嵌套select语句，被嵌套的select语句称为子查询。

where子句中的子查询

例：找出比最低工资高的员工姓名和工资？

select ename，sal

from emp

where sal>min(sal);

这种写法是错误的，where子句中不能直接使用分组函数。

正确做法：

第一步：查询最低工资是多少

​ select min(sal) from emp;

第二步:找出>800的

​ select ename,sal from emp where sal>800;

第三步：合并

select ename,sal from emp where sal>(select min(sal) from emp) ;

from子句中的子查询

注意：from后面的子查询，可以将子查询的查询结果当做一张临时表。

案例：找出每个岗位的平均工资的薪资等级。

第一步：找出每个岗位的平均工资（按照岗位分组求平均值）

select job,avg(sal) from emp group by job;

第二步：把以上的查询结果就当作一张真实存在的表。

select

​ t.*, s.grade

from

​ (select job,avg(sal) as avgsal from emp group by job) t

join

​ salgrade s

on

​ t.avgsal between s.losal and s.hisal;

union合并查询结果集

union的效率要高一些。对于表连接来说，每连接一次新表，则匹配的次数满足笛卡尔积，成倍的翻。。。

但是union可以减少匹配的次数，在减少匹配次数的情况下，还可以完成两个结果集的拼接。

a 连接 b 连接 c

a 10条记录

b 10条记录

c 10条记录

匹配次数：1000

a 连接b一个结果： 10*10–>100次

a 连接c一个结果： 10*10–>100次

使用union：100次+100次=200次（union把乘法变成了加法运算）

limit

limit是将查询结果集的一部分取出，通常使用在分页查询当中。

分页的作用：提高用户的体验。

完整用法：limit startIndex，length

startIndex 起始下标

length 所取长度

案例：按照薪资降序，取出排名在前5名的员工？

select

​ ename,sal

from

​ emp

order by

​ sal desc

limit 5;

注意：mysql中limit在order by 之后执行

分页

每页显示3条记录

第一页：limit 0，3 [0,1,2]

第二页：limit 3，3 [3,4,5]

第三页：limit 5, 3 [6,7,8]

每页显示pagesize条记录

第pageno页：limit（pageNo-1）*pagesize，pagesize

表的创建

建表的语法格式

（建表属于DDL语句，DDL包括：create drop

alter）

create table 表名（字段名1 数据类型，字段名2 数据类型，字段名3 数据类型）;

mysql中的数据类型

varchar 最长255(可变长度的字符串，会根据实际的数据长度动态分配空间)

​ 优点：节省空间

​ 缺点：需要动态分配空间，速度慢。

char 最长255(定长字符串，不管实际的数据长度是多少，分配固定长度的空间去存储数据，使用不恰当的时候，可能会导致空间的浪费)

​ 优点：不需要动态分配空间，速度快

​ 缺点：使用不当可能会导致空间的浪费。

varchar和char应该怎么选择？

​ 性别字段 char

​ 姓名字段 varchar

int （最长11） 数字中的整数型，等同于java中的int

bigint 数字中的长整型，等同于Java中的long

float 单精度浮点型数据

double 双精度浮点型数据

date 短日期类型

datetime 长日期类型

clob 字符大对象（最多可以存储4G的字符串）比如存储一篇文章，存储一个说明

blob 二进制大对象 （专门用来存储图片、声音、视频等流媒体数据）使用IO流插入

创建一个学生表

学号、姓名、年龄、邮箱地址

create table t_student(

no int,name varchar(32),sex char(1),age int(3),email varchar(255));

删除表

drop table if exists t_student ;//如果这张表存在时删除

插入数据insert（DML）

语法格式： insert into 表名（字段名1，字段名2，字段名3…）values(值1，值2，值3)；

注意：字段名要和值一一对应，数量要对应，数据类型要对应。

insert into t_student(no,name,age,sex,email) values(01,'zhangsan',20,'f','zhangsan@163.com');

注意：insert语句但凡是执行成功了，必然会多一条记录。

没有给其它字段指定值的话，默认值是null

指定默认值 default

使用str_to_date函数进行类型转换

str_to_date函数可以将字符串转换成日期类型date 。常使用在insert

语法格式：

​ str_to_date(‘字符串日期’，‘日期格式’)

mysql的日期格式：

​ %￥年

​ %m月

​ %d 日

​ %h 时

​ %i 分

​ %s 秒

例如：insert into t_user(id,name,birth) values(1,‘zhangsan’,str_to_date(‘01-10-1990’));

如果提供的日期字符串是这个格式 %￥—%m—%d，mysql会自动转换

date和datetime两个类型区别？

date是短日期，只包含年月日信息

datetime是长日期，包括年月日时分秒信息

mysql短日期默认格式：%￥—%m—%d

mysql长日期默认格式：%￥—%m—%d %h：%i：%s

在mysql中用now()获取系统当前时间

修改update（DML）

语法格式：update 表名 set 字段名1=值1，字段名2=值2，字段名3=值3… where 条件；

update t_user set name=‘jack’ where id=2;

删除数据delete（DML）

语法格式：delete from 表名 where 条件；

注意：没有条件，整张表的数据会全部被删除

例：delete from t_user where id=2;

快速删除表中的数据

delete语句删除数据的原理：

表中的数据被删除了，但是这个数据在硬盘上的真实存储空间不会被释放

这种删除缺点是删除效率比较低

这种删除优点是：支持回滚，后悔了可以恢复数据！

truncate语句删除数据的原理：

这种删除效率比较高，表被一次截断，物理删除

这种删除缺点：不支持回滚

优点：快速

删除表操作

drop table 表名；//这不是删除表中的数据，这是把表删除

truncate 是删除表中的数据，表还在。

约束

约束：constraint

在创建表的时候，我们可以在表的字段加上一些约束，来保证这个表中的数据的完整性、有效性

约束的作用就是为了限制表中的数据，保证添加到数据表中的数据准确和可靠

约束包括哪些？

非空约束：not null

唯一性约束：unique

主键约束：primary key

外键约束：foreign key

检查约束：check（mysql不支持，oracle支持）

非空约束

not null

非空约束not null的约束字段不能为null

drop table if exists t_vip;

create table t_vip(id int,`name varchar(255) not null);

insert into t_vip(id,name) values(1,‘zhangsan’);

insert into t_vip(id,name) values(2,‘lisi’);

唯一性约束

唯一性约束unique约束的字段不能重复，但是可以为null

drop table if exists t_vip;

create table t_vip(id int,`name varchar(255) unique，email varchar（255）);//约束直接添加在列后面，这种约束称为列级约束

insert into t_vip(id,name,email)

​ values(1,‘zhangsan’,‘zhangsan@123.com’);

insert into t_vip(id,name,email)

​ values(2,‘lisi’,‘lisi@123.com’);

两个字段联合起来具有唯一性

idrop table if exists t_vip;

create table t_vip(id int,`name varchar(255) ，email varchar(255),unique(name,email));//约束直接添加在列后面，这种约束称为表级约束

insert into t_vip(id,name,email) 

​		values(1,'zhangsan','zhangsan@123.com');

insert into t_vip(id,name,email)

​		 values(2,'zhangsan','zhangsan@sina.com');

在mysql中，如果一个字段同时被not null和unique约束的话，该字段自动变为主键字段。

主键约束(primary key)

主键约束相关术语？

主键约束：就是一种约束

主键字段：该字段上添加了主键约束

主键值：主键字段中的每一个值，主键值

什么是主键？作用？

主键值是每一行记录的唯一标识。

主键的特征：not null +unique

一个表中主键约束只能添加一个。

主键值建议使用：int bigint char等类型

主键值是定长的，一般都是数字。

主键值可以采用自增的方式auto_increment表示自增，从1开始，以1递增。

外键约束

外键约束：一种约束

外键字段：该字段上添加了外键约束

外键值：外键字段当中的每一个值。

注意： 若 t_class是父表

​ t_student是子表

删除表的顺序？

​ 先删子，在删父。

创建表的顺序？

​ 先创建父，在创建子。

删除数据的顺序？

​ 先删除子，在删除父。

插入数据的顺序？

​ 先插父，在插子

create table t_class(

​				classno int primary key,

​				classname varchar(255));

create table t_student(

​				no int primary key auto_increment,

​				name varchar(255),

​				cno int,

​				foreign key(cno) references t_class(classno)

);

子表中的外键引用的父表中的某个字段，被引用的这个字段必须是主键吗？

不一定是主键，但至少具有unique约束

外键值可以为null