MySQL笔记【基础篇】

     阅读:24

MySQL笔记

相关术语:

  • DB :数据库:在硬盘上以文件的方式存在
  • DBMS:数据库管理系统:例如mysql,oracle,sqlserver…
  • SQL:一门适用于所有DBMS,对DB进行操作的标准规划语言,
  • DQL:数据库查询语句 select,
  • DML:数据库管理语句,修改表中数据update,insert,delete,
  • DDL:数据库管理语句,修改表中的结构drop,create,alter,
  • TCL:commit提交事务,rollback回滚事务,
  • DCL:grant授权,revoke撤销权限

一.DOS窗口命令

  1. DOS窗口进入数据库 mysql -uroot -p密码
  2. 查看数据库:show databases;
  3. 创建数据库:create database 数据库名字;
  4. 使用某个数据库:use 数据库名字;
  5. 查看使用数据库中的表:show tables;
  6. 数据库初始化执行sql文件:source +sql文件路径(直接拖就行)
  7. 删除数据库:drop database 数据库名字;
  8. 查看表结构:desc+表名;
  9. 查看表中数据:select * from 表名;(当在DOS界面中显示MYSQL数据库中的表的信息时,可能会出现中文乱码问题,出现是原因是因为DOS窗口默认字符集为GBK格式,如果当前MYSQL设置的默认编码格式非GBK格式,则可能会出现乱码。
    解决方法:在输出信息前,执行以下语句:
    set names gbk;)
  10. 查询当前使用的哪个数据库:select database();也可直接再use一下需要的数据库;
  11. 查询mysql的版本号:select version();
  12. 结束一条语句:\c 命令
  13. 退出mysql:exit 命令
  14. 查看创建表的时候使用的sql语句:show create table 表名;

注意:以下操作都是基于如下sql脚本进行的,建议先导入sql文件,一边操作一边看!

脚本链接:https://pan.baidu.com/s/1IaeODXfw6oPVjQgyI1BA5A 
提取码:1234

二.DQL

单表简单查询:

1.简单的查询语句(DQL)

语法格式:select 字段名1,字段名2 *12,字段名3 as ’表名‘ ,… from 表名;

  • 字段二表示字段可以进行数学运算;

  • 字段三表示可以重新为该字段命名,字符串用单引号 (as可以省略,用空格替换!);

2.查询所有字段:

select * from 表名 注意:实际开发中不建议*因为效率较低

3.条件查询格式:

select 字段,字段…from 表名 where 条件;条件(> ,>=,<,<=,<>或者!=,between 小数字 and 大数字 ,)

运算符说明
=等于
<> 或 !=不等于
<小于
<=小于等于
>大于
>=大于等于
between … and …两个值之间
is null为null(is not null不为空)
and并且
or或者
in包含,相当于多个or(not in 不在这个范围中)
notnot可以取非,主要用在is或in中
likelike成为模糊查询,支持%或_匹配

in后面不是区间而是具体的值in(800,1000)意思是要么800要么1000

4.模糊查询like

(必须掌握两个符号,一个是%,一个是_)

%表示任意多个字符,__代表一个字符。如果_或%需要转化为普通字符就在前面加‘ \ ’ 起到转义的作用。

5.排序(升序和降序):

默认指的是升序

​ 格式:select 字段1,字段2,from表名 order by 字段名 asc(升序) ;

​ select 字段1,字段2,from表名 order by 字段名 desc(降序) ;

如果按照某个字段排序,而该字段又相等则再在后面的字段加上,第二个字段名 asc/desc;

例如:select 字段1,字段2,from表名 order by 字段名1 desc,字段名2 asc;(越靠前的字段越能起主导作用,order by后面是最后执行的)

6.分组函数:

别名:多行处理函数

所有的分组函数都是对某一组函数进行操作的 注意分组函数自动忽略null,统计不上并且分组函数不能写在where语句后面,分

组函数即使没有group by也自己成为一组,where的优先级高于group by,所以where不执行完,分组函数执行不了。

  • count计数:select count(*) from 表名; //统计的是总记录条数。

​ select count(字段名)from 表名; //统计的是当前字段不为null的数据条数。

  • sum求和:select sum(字段名) from 表名;

  • avg平均值:select avg(字段名) from 表名;

  • max最大值:select max(字段名) from 表名;

  • min最小值:select min(字段名) from 表名;

7.单行处理函数:

ifnull(可能为null的数据,被当作什么处理);

例如:ifnull(sid,0);如果sid为null,那么将其当作0来处理;

8.group by 和 having

group by:按照某个字段或者某些分段进行分组。

例子:select 分组函数 from 表名 group by 字段名; 执行顺序:from-> group by -> select。

注意:

  • 分组函数一般都是和group by联合使用,这也是为什么称之为分组函数的原因,并且任何一个分组函数(count,max,avg,

    min,sum)都是在group by执行完成后才执行。

  • 如果一个sql语句后面有group by则查询的字段字段只能有分组字段,和分组函数。

having:having是对分组之后的数据进行再次过滤。可以理解作为优先级低的where;

9.distinct去重记录:

select distinct 字段1,字段2 from 表;使得字段1和字段2联合起来去重

注意:distinct必须出现在所有字段的最前面。不可:select 字段1,distinct 字段2 from 表;

count(diatinct 字段),先为字段去重,再统计数据条数;

10.总结完整DQL语句

select …from…where…group by…having…order by;

以及查询的顺序;

提示:

1.任何一条sql语句都以’’ ; ''结尾;

2.sql语句不区分大小写;

3.字符串用单引号括起来;

4.任何值与null进行运算结果都是null;

多表连接查询:

1.什么是连接查询:

在实际开发中大部分都不是从一张表中查询数据,一般都是多张表联合查询取得结果。

实际开发中,一般一个业务对应多张表。比如:学生和班级,起码两张。(因为一张表可能会出现冗余的情况,即数据大量重复!)

2连接查询的分类:

1.根据出现年代来划分,包括:

  • sql92(一些老的DBA还在使用的语法,已经被淘汰)

  • sql99(比较新的语法)

2.根据表的连接方式划分,包括:

  • 内连接: 等值连接

    ​ 非等值连接

    ​ 自链接

  • 外连接: 左外连接(左连接)

    ​ 右外连接(右连接)

  • *全连接(很少用,了解)

3.笛卡尔积现象:

在表的连接查询方面的一种现象:当两张表进行查询的的时候,没有任何条件限制,最终的查询结果条数是两张表的乘积。

SELECT e.ename,d.dname FROM emp e , dept d  ;   #会出现笛卡儿积现象(56条数据)(已经被淘汰的方式sql92写法)

关于表别名,使用表别名有什么好处吗?

  • 执行效率高
  • 可读性好

4.避免笛卡尔积现象

答:当然是加条件了。

SELECT e.ename,d.dname FROM emp e , dept d WHERE e.deptno = d.deptno ;

思考:避免了笛卡尔积会减少记录的匹配次数吗?

不会,还是匹配一样的次数,但是只显示有效条数。(并不能提高效率)

5.内连接

1、等值连接

语法:(inner)join …on…

inner可省略,带着只为可读性好一些。两张表内连接的意思 (常用)

SELECT e.ename,d.dname FROM emp e 	INNER(可以省略) JOIN  dept d  ON e.deptno = d.deptno WHERE 过滤条件; 

sql99语法:使得表的连接条件和后来的过滤条件分离了!

2、非等值连接
SELECT e.ename,e.sal,s.grade FROM emp e JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal

sql99语法:表连接条件并不是一个确切的值!

3、自连接

最大特点:一张表看作两张表,自己连自己

SELECT a.ename,b.mgr admin FROM emp a JOIN emp b WHERE a.mgr = b.empno ; 

6.外连接

语法格式:left|right(outer)join … on …

outer可省略,带着只为可读性好一些。两张表外连接的意思

  • 左外连接(左连接):表示左边是主表。

  • 右外连接(右连接):表示右边是主表。

左连接有右连接的写法,同样右连接也有左连接的写法 :

右外连接,a是主表


左外连接,a是主表

案例:查询没有员工的部门

SELECT d.* FROM dept d LEFT JOIN emp e ON e.`DEPTNO` = d.`DEPTNO` WHERE  e.`EMPNO` IS NULL 

7内连接和外连接的区别:

  • 内连接:假设有AB两张表,使用内连接,凡是A表B表能够匹配上的记录查询出来,这就是内连接,AB两张表并没有主次之分,两张表是平等的。
  • 外连接:假设AB两张表,使用外连接,AB两张表中的一张是主表,一张是副表,主要查询主表中的数据,捎带查询副表,当副表中的数据并没和主表中的数据匹配上,副表自动模拟出NULL与之相匹配(主表数据无条件查询)

8.三个表连接查询

案例一:查询每个员工的部门名称和薪资等级

SELECT e.ename,d.`DNAME`,s.grade FROM emp e 
JOIN dept d ON e.`DEPTNO` = d.`DEPTNO` 
JOIN salgrade s ON e.`SAL` BETWEEN s.losal AND s.hisal

案例二:查询每个员工的部门名称、薪资等级、上级领导

SELECT e.`ENAME` '员工' ,d.`DNAME` '部门' , s.grade '薪资等级' , e2.ename '领导' 
FROM emp e 
JOIN dept d ON e.`DEPTNO` = d.`DEPTNO` 
JOIN salgrade s ON e.`SAL` BETWEEN s.losal AND hisal 
LEFT JOIN emp e2 ON e.`MGR` = e2.empno

子查询:

定义:select语句中嵌套select语句,被嵌套的select语句是子查询。

子查询可以出现在:

select 
	....(select)..
from 
	....(select)..
where 
	....(select)..

1.where后面嵌套子查询

select * from emp where sal > (select avg(sal) from emp) ; 

2.from后面嵌套子查询*

SELECT d.dname,AVG(s.grade) '部门平均薪资' 
FROM emp e
JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal 
JOIN dept d ON e.deptno = d.deptno
GROUP BY d.dname

3.在select 后面嵌套子查询

找出每个员工所在的部门名称

写法一:看作临时表
SELECT t.ename,d.dname
FROM dept d JOIN (SELECT e.ename,e.deptno FROM emp e) t ON t.deptno = d.deptno   

写法二:连接查询
SELECT e.ename,d.dname FROM emp e JOIN dept d ON e.deptno = d.deptno

写法三:select后嵌套子查询
SELECT e.ename,(SELECT d.dname FROM dept d WHERE d.deptno = e.deptno) AS dept FROM emp e ;

4.union

可将查询结果集相加

SELECT ename,job FROM emp e WHERE job = 'CLERK'
UNION
SELECT ename,job FROM emp e WHERE job = 'SALESMAN'

而且两张不相干的表中数据也能够因此拼接在一起。(但是列数保持一致)

SELECT ename FROM emp 
UNION
SELECT dname FROM dept

5.*limit

重点中的重点,分页查询全靠它

1.limit是mysql中特有的,其他数据库中没有,但是Oracle中有一个相同的机制叫rownum。

2.limit取结果集中的部分数据

3.语法机制:limit startIndex,length

​ startindex表示起始位置,从0开始,0表示第一条数据。

​ length表示取几个。

4.limit是sql语句中最后一个执行的环节。

`

、

(当前页数-1)* 页的大小 = 当前页起始下标

三.DDL和DML

1.创建表:

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-edWCqC9C-1639059716025)(MySQL笔记.assets/image-20200626103432980.png)]

char 和 varchar 如何选择?

  • char 适用于数长度不会发生改变的时候,是定长的,例如:性别,生日
  • varchar 当一个字段数据长度不确定,例如:简介、姓名、等都是采用varchar

char是直接开辟一定长度的空间,varchar是根据存储数据的长度动态的开辟空间!

测试案例

#创建表
CREATE TABLE user(
   id  INT(10),
   username VARCHAR(30),
   PASSWORD VARCHAR(30)        #最后一行不需要,
);

2.删除表:

drop table 表名;(通用)

drop table 表名 if exists 表名;	//Oracle不支持这种写法。

使用drop删除表,就像是我们把表中的数据擦干净,但是表的结构还在,可以通过rollback回滚过来数据!

我们如果需要删除大表的话,就需要truncate 来删除,会清空所有数据且执行速度很快,并且永久删除,数据不可恢复!

truncate table name 

删除表常用:

drop table if exist 表名(如果存在某个表删除该表)。

3.修改表结构:

用工具:哈哈!设计表选项

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Jgk2FJyv-1639059716027)(MySQL笔记.assets/image-20211207194615486.png)]

(因为在实际开发中表设计好以后,对表结构进行修改是很少的。并且修改表中结构的语句并不会写在java语句当中。)

4.insert:表中插入数据

语法:insert into 表名(字段1,字段2,字段3…)values(value1 , value2 , value3) ;

INSERT INTO dept(deptno,dname,loc)VALUES(50,'Java开发','ZHENGZHOU');

需要注意的地方:当一条insert语句执行成功之后,表格必然会多一行记录,及时多的一行记录当中某些字段为null,后期也没办法再通过insert插入数据了,只能通过update更新 !

**一次插入多行数据:**insert into 表名(字段1,字段2,字段3)values(value1,value2,value3),(value1,value2,value3)

INSERT INTO dept(deptno,dname,loc)VALUES(70,'大数据开发','ZHENGZHOU'),(60,'运维部','KAIFENG');

5.表的复制

了解一下即可!

CREATE TABLE mytable AS SELECT * FROM dept      #将将查询结果当作表创建出来。

将查询出来的结果插入道一张表中

INSERT INTO mytable SELECT * FROM dept

6.update:修改表中数据

语法格式:update 表名 set 字段名1 = 值1 ,字段名2 = 值2 …where 条件 ;

注意:没有条件整张表的数据全部更新 ;

UPDATE dept SET dname = '测试开发' ,loc = 'HUEL' WHERE DEPTNO = 10 ;   #就1个set!

7.delete删除表中数据;

语法格式:delete from 表名 where 条件 ;

注意:没有条件表中数据全部清空!

DELETE FROM dept WHERE deptno = 70   #删除部门号为70的数据

DELETE FROM dept  #清空表中数据

8.增删改查有一个术语:CRUD操作

  • Create(增加)

  • Retrieve(查询)

  • Update(修改)

  • Delete(删除)

四.约束

创建表的时候可以给字段添加相应的约束,约束的目的:保证表中数据的合法性,唯一性,有效性。

  1. 非空约束(not null):约束字段不能为NULL

  2. 唯一约束(unique):约束字段不能重复

  3. 主键约束(primary key):约束字段既不能为NULL也不能重复

  4. 外键约束(foreign key):阿里巴巴手册,已经禁止使用外键了

  5. 检查f约束(check):Oracle数据库有check约束,但是Mysql没有。

注意:我们主要还是记住英文,平时使用的都是英文!

1.非空约束 not null

CREATE TABLE mytable(
   id  INT(10),
   username VARCHAR(30) NOT NULL,    #添加非空约束
   PASSWORD VARCHAR(30)
);

测试

insert into mytable(id,password) values(1,'123') ;  #由于我们的的用户字段要求非空,所以会执行失败
ERROR 1364(HY000) : Filed 'username' doesn't have a default value

2.唯一约束(unique)


列级约束


表级约束

测试

-

3.主键约束(primary key) *

必须记住:一张表的主键约束只有一个 ;最好与别的字段不相关,仅仅代表唯一标识 !

CREATE TABLE mytable(
   id  INT(10) PRIMARY KEY,       #添加主键约束【属于列级约束】
   username VARCHAR(30), 
   email VARCHAR(30)
);

测试

在这里插入图片描述

【表级约束】

主键值自增:在primary key后面添加auto_increment(从1开始,递增);

了解:Oracle也提供一个自增序列叫做序列(sequence);

4.外键约束(foreign key)

关于外键约束的相关术语

  • 外键约束:foreign key
  • 外键字段:添加有外键约束的字段
  • 外键值:外键字段中的每一个值。

t_student中的classno字段引用t_class表中的cno字段,此时t_student表叫做子表。t_class表叫做父表。

顺序要求:

  • 删除数据的时候,先删除子表,再删除父表。
  • 添加数据的时候,先添加父表,在添加子表。
  • 创建表的时候,先创建父表,再创建子表。
  • 删除表的时候,先删除子表,在删除父表。

使用外键【Alibaba规范不推荐使用】

create table t_class(
	cno int ,
    cname varchar(255) ,    #被引用:父表
    primary key(cno)
);

create table t_student(
	sno int ,
    sname varchar(255) ,   #子表
    classno int ,  
    foreign key(classno) reference t_class(cno)
);
#t_student中的classno字段引用t_class表中的cno字段,此时t_student表叫做子表。t_class表叫做父表
  • reference是引用;

  • 外键可以为NULL;

  • 此时classno中的数据来自cno;

  • 外键引用的字段不一定是主键,但是起码是被unique约束;

五.存储引擎

表在数据库中的存储方式。

存储引擎只存在mysql中,(Oracle中有对应机制,但是不叫存储引擎)。

完整的建表语句:

CREATE TABLE mytable(
   id  INT(10) PRIMARY KEY,
   username VARCHAR(30) NOT NULL, 
   PASSWORD VARCHAR(30)
)ENGINE = InnoDB Default CAHRSET=UTF8;

注意:在MYSQL当中,凡是标识符可以使用飘号括起来,最好别用,不通用!

建表的时候可以指定存储引擎,也可以指定字符集。

mysql默认的存储引擎是InnoDB的方式!默认采用的字符集是UFT-8

mysql> show engines \G   #查看当前,mysql版本支持的存储引擎命令! 当前MYSQL版本是5.7.33,支持9种存储引擎!

1.常见的存储引擎:

  • MyISAM 存储索引

  • InnoDB 存储索引

  • MEMORY 存储索引

2.MyISAM 存储引擎:

MyISAM:这种存储引擎不支持事务。是最常用的但不是默认的

 Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO     #不支持事务!
          XA: NO
  Savepoints: NO

它管理的表具有以下特征:

、

使用三个文件表示每个表 :

  • 格式文件 — 存储表的结构(mytable.frm)
  • 数据文件 — 存储表的数据(mytable.MYD)
  • 索引文件 — 存储表中索引的文件(mytable.MYI)

优点:可被转换为压缩,来节省空间,并且可以转换为只读表,提高检索效率!

缺点:不支持事务!

3.InnoDB 存储引擎:

支持事务,行级锁,外键等 ;这种存储引擎安全。

      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
  • 表的结构存储在xxx.frm文件当中
  • 数据存储在tablespace这样的表空间当中(逻辑概念),无法被压缩,无法被转换为只读!
  • 这种InnoDB存储引擎在MYSQL数据库崩溃之后提供自动恢复机制!【事务、安全、重量级】
  • 支持级联删除和级联更新。

4.MEMORY 存储引擎:

所有数据保存在内存中,断电即失,但是查询速度超级快!

    Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
  • 在数据库目录当中,每个表均已.frm格式的文件表示
  • 表数据以及索引被存储在内存当中
  • 表级锁机制
  • 不能包含TEXT(CLOB)或BLOB字段
  • 查询速度快

六.事务(Transaction)*

1、什么是事务

一个事务是一个完整的业务逻辑单元,不可再分。

比如:银行转账,从A账户向B账务转账10000,需要执行两条update语句

update t_act set balance = balance - 10000 where actno = 'act-001' ; 
update t_act set balance = balance + 10000 where actno = 'act-002' ; 

以上两条DML语句必须同时成功,或同时失败,不允许一条成功,一条失败!

想要保证以上的两条DML语句(update、insert、delete)同时成功或者同时失败,那么就需要使用数据库的“事务机制” !

、

2、事务原理

假设一个事儿,需要先执行一条insert,再执行一条update,最后执行一条delete,这个事儿才算完成。

开启事务机制(开始)

  • 执行insert语句–>insert.(这个执行成功之后,把这个执行记录到数据库的操作历史当中,并不会向文件中保存-条数据,不会真正的修改硬盘上的数据。)
  • 执行update语句—> update…(这个执行也是记录一下历史操作,不会真正的修改硬盘上的数据)
  • 执行delete语句---->delete…(这个执行也是记录一下历史操作【记录到缓存】,不会真正的修改硬盘上的数据)

提交事务或者回滚事务(结束)

怎么提交事务,怎么回滚事务?

提交事务:commit;语句

回滚事务:rollback;语句==(回滚永远都是只能回到上次事务的提交点!)==

MySQL默认的事务行为是怎么样的?

MySQL默认情况下是支持自动提交事务的。(自动提交):就是一条DML提交一次!

怎么样才能将MySQL的自动提交事务关闭呢?

先执行如下命令:start transaction ,开启事务,也是将自动提交事务关闭!

在次命令后的指定的DML语句并不会持久化到数据库,等待commit | rollback 操作!

3、事务的特性:ACID

A:原子性(atomicity):事务是最小的工作单元,不可再分。

C:一致性(consistency):事务必须保证多条DML语句同时成功或同时失败。

I :隔离性(isolation):事务A与事务B之间具有隔离。

D:持久性(durability):持久性说的时最终的数据必须持久化道硬盘文件中,事务才算成功的结束。

4、事务的隔离性

将事务A与事务B分别比作教室A,B,然而它们之间的墙就是隔离性,墙越厚隔离级别越高!(这种墙分为四个级别)

  • 第一级别:读未提交(read uncommitted):我们当前事务可以读取对方未提交的事务,

    存在问题:存在脏读(Dirty read)现象,表示读到了脏的数据。

  • 第二级别:读已提交(read committed):我们当前事务可读取对方已提交的事务,解决了脏读现象,存在问题:不可重复读。

  • 第三级别:可重复读(repeatable read):解决不可重复读,永远读取到的都是开启事务时的数据,==存在问题:读取到的数据是幻像(幻读)==例如:执行一条查询的sql需要从1点3点,那么这条sql读取的一直是1点的数据,即使13点之间存在其他操作,该sql的结果并不会受到影响,类似快照,一直读取的是快照中的数据。

  • 第四级别:序列化读/串行化读(serializable):解决了所有问题。 效率低,需要事务排队(类似于synchronized)。

Oracle数据库默认级别:第二级别;读已提交

Mysql数据库默认级别:第三级别,可重复读

5、验证各种隔离级别

对以上各种隔离级别做一个验证

通过命令 select @@tx_isolation;查看当前事物的隔离级别 !
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
读未提交(read uncommitted):
#执行如下命令,设置全局的事务隔离级别为读未提交
set global transaction isolation level read uncommitted;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kXdvA9x6-1639059716028)(MySQL笔记.assets/image-20211208205412655.png)]

读已提交(read committed):

测试 读已提交(read committed)

#执行如下命令,设置全局的事务隔离级别为读已提交
set global transaction isolation level read committed;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-grvPo7Zn-1639059716028)(MySQL笔记.assets/image-20211208210048090.png)]

可重复读(repeatable read):

测试:可重复读(repeatable read)

#执行如下命令,设置全局的事务隔离级别为可重复读
set global transaction isolation level read committed;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-zMBUdTLi-1639059716028)(MySQL笔记.assets/image-20211208210758859.png)]

序列化读/串行化读(serializable):

测试 序列化读/串行化读(serializable)

#执行如下命令,设置全局的事务隔离级别为可重复读
set global transaction isolation level serializable;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-iLBNAP8s-1639059716029)(MySQL笔记.assets/image-20211208211317004.png)]

七.索引 *

1、索引的概述

索引相当于一本书的目录,通过目录可以快速找到对应的资源。

作用:提高检索效率,缩小扫面范围。

注意:索引虽然提高效率但是依旧不能随意添加,因为索引也是数据库中的对象,也是需要数据库不断的维护的,有维护成本,表中数据经常修改就不适合,因为一旦修改索引需要重新排序,进行维护。

2、怎么创建索引对象

语法格式:create index 索引名称 on 表名 (字段名)。

eg:create index emp_sal_index on emp(sal);

3、怎么删除索引对象

语法格式:drop index 索引名称 on 表名;

4、什么时候给字段添加索引

  • 数据量庞大。(根据客户需求,根据线上的环境。)
  • 该字段很少的DML操作。
  • 该字段经常出现在where子句中。

5、sql语句的执行计划

主键和具有unique约束的字段自动添加索引,根据主键查询效率较高,尽量根据主键检索。

==explain+sql语句可以解释该语句。(执行计划)==可以通过此命令判断sql是否走了索引!

6、索引底层采用的数据结构是:b+tree

索引的实现原理

、

案例分析

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-BbXL0p4s-1639059716030)(MySQL笔记.assets/image-20200704154131816.png)]

7、索引分类

  • 单一索引:给单个字段添加索引
  • 复合索引:给多个字段联合起来添加一个索引
  • 主键索引:主键上会自动添加索引
  • 唯一索引:有unique约束的字段上会自动添加索引

8、索引什么时候失效 ?

1、模糊查询,这个时候索引失效,因为以%开始,无法使用索引!

select ename from emp where ename ='%a%' ;

2、使用or的时候会索引失效,因为如果or两端的字段存在一个字段不具备索引,具备索引的字段也会失效!

select ename from emp where ename ='zhangsan' or job = 'Java' ;

3、使用复合索引,没有使用左侧的列去查找,索引失效

creat index emp_job_sal_index on emp(job,sal) # 为job和sal两个字段添加复合索引
select * from emp where job = 'java' ; #会走索引,因为使用job列去查了
select * from emp where sal = 1800 ; #不会走索引,因为没有使用左侧的job列!

4、where当中的索引列参加了运算,索引失效。

select * from emp where sal + 1 = 1800 ;

5、在where当中索引列使用了函数

select * from emp where lower(ename) = 'smith'  ;

八.视图(view)

1.视图的概念

站在不同的角度看到数据。(同一张表的数据,通过不同的角度去看待。)

2.创建/删除视图的sql

#创建视图
create view myview as select * from emp;

#删除视图
drop viem myview;

注意:只有DQL语句才能以视图对象的方式创建出来;

3.对视图进行CRUD操作

对视图进行增删改查,会影响到原表数据。

create table emp_bak as select * from emp;
create view myview as select empno,ename,sal from emp_bak ;
update myview set name = 'sqx' ,sql = 1 where empno = '7369' ; #通过视图修改原表数据
delete from myview where empno = 7369 ;  #通过视图删除原表数据

注意:视图对象时保存在硬盘上的,而非保存在内存当中,所以不会消失!

4.视图的作用

思考:假设有一条复杂的SQL语句,而且这条SQL语句需要在不同的地方反复使用,每一次使用都写一遍,很长很麻烦,怎么办?

:可以把复杂的SQL以视图的形式展示出来,我们需要执行复杂的SQL时,只需查询该SQL所映射的视图即可!并且这种方式很容易后 期的 维护,我们只需改动原SQL,重新创建视图即可!

视图可以隐藏表的实现细节,保密级别较高的系统,数据库只对你提供相关的视图java程序员只能对视图进行CRUD。(并不会提高视图的检索效率)

九.DBA命令(了解)

掌握以下即可,其他需要再查!

1.将数据库导出

#在windows的DOS窗口命令中输入(不需要进入mysql)

mysqldump 数据库名 > 导出路径  -u:mysql账号 -p:mysql密码

2.向数据库导入数据

create database 数据库名;

use数据库名;

source+sql脚本文件路径;

十.数据库设计三范式(重点)

设计表的依据,按照这个三范式设计的表不会出现数据冗余。

1、三范式都是那些

  • 第一范式:要求任何一张表必须有主键,每一个字段原子性不可再分。【核心】
  • 第二范式:建立在第一范式的基础之上,要求所有非主键字段完全依赖主键,不要产生部分依赖。
  • 第三范式:建立在第二范式的基础之上,要求所有非主键字段直接依赖主键,不要产生传递依赖。

声明:三范式是面试官经常问的,所以一定要熟记在心!

设计数据库表的时候,按照以上的范式进行,可以避免表中数据的冗余,空间的浪费。

2、必备口诀

多对多,三张表,关系表两个外键。

一对多,两张表,多的表加外键。

一对一两种方案:主键共享,外键唯一。

提醒:实际的开发中,以满足客户需求为主,有时候会拿冗余换执行速度。

三十四道练习题

1、取得每个部门最高薪水的人员名称;

SELECT e.ename,t.maxsal FROM emp e 
JOIN (SELECT deptno,MAX(sal) maxsal FROM emp GROUP BY deptno) t 
ON t.maxsal = e.sal AND t.deptno = e.deptno ;     #需要满足两个条件!

2、哪些人的薪水在部门的平均薪水之上;

SELECT e.ename,e.sal,t.* FROM emp e 
JOIN (SELECT deptno,AVG(sal)avgsal FROM emp GROUP BY deptno ) t 
ON e.deptno = t.deptno AND e.sal > t.avgsal ;  # 与第一题类似满足两个条件

3、取得部门中(所有人的)平均的薪水等级;

SELECT t.deptno,AVG(grade) 
FROM (
	SELECT e.sal,e.deptno,s.grade
	FROM emp e JOIN salgrade s 
	ON e.`SAL` BETWEEN s.losal AND s.hisal ) t 
GROUP BY deptno

4、不准用组函数(Max)取得最高薪水

SELECT sal maxsal FROM emp ORDER BY sal DESC LIMIT 1 ;

5、取得平均薪水最高的部门的部门编号

SELECT deptno,AVG(sal) FROM emp e GROUP BY deptno ORDER BY AVG(sal) DESC LIMIT 1

6、取得平均薪水最高的部门的部门名称

SELECT deptno,dname FROM dept
WHERE DEPTNO = (SELECT deptno FROM emp e GROUP BY deptno ORDER BY AVG(sal) DESC LIMIT 1) ;

7、求平均薪水等级最低的部门的部门名称

SELECT deptno,dname FROM dept
WHERE DEPTNO = (SELECT deptno FROM emp e GROUP BY deptno ORDER BY AVG(sal) LIMIT 1) ;

8、取得比普通员工(员工代码没在mgr字段出现的)的最高薪水还要高的领导人姓名;

注意(比“普通员工最高薪水”还要高的一定是领导)

SELECT e.ename FROM emp e
WHERE e.sal > (SELECT MAX(sal) FROM emp WHERE empno NOT IN
              (SELECT DISTINCT mgr FROM emp WHERE mgr IS NOT NULL))            
#not in 使用的时候,后面的小括号记得排除null              

9、取得薪水最高的前五名

SELECT ename,sal FROM emp ORDER BY sal DESC LIMIT 5 ;

10、取得薪水最高的第6名刀到第10名员工

SELECT ename,sal FROM emp ORDER BY sal DESC LIMIT 5,5 ;  

11、最后入职的五名员工

SELECT ename,HIREDATE FROM emp ORDER BY HIREDATE DESC LIMIT 5

12、取得每个薪水等级有多少个员工

SELECT COUNT(*),grade 
FROM (SELECT e.ename,s.grade FROM emp e JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal ) t
GROUP BY grade

13.列出所有员工以及领导的名字

SELECT a.ename '员工' ,b.ename '领导' FROM emp a LEFT JOIN emp b ON a.mgr = b.empno

14、列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称

SELECT a.ename '员工' ,a.hiredate,b.ename '领导',b.hiredate,d.dname '部门名称'
FROM emp a 
JOIN emp b ON a.mgr = b.empno    
JOIN dept d ON d.deptno = a.deptno
WHERE a.hiredate < b.hiredate     

15、列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门。

SELECT d.dname '部门名称',e.*
FROM dept d LEFT JOIN emp e ON d.deptno = e.deptno ;

16、列出至少有五个员工的所有部门

SELECT deptno FROM emp GROUP BY deptno HAVING COUNT(*) >= 5 ;   

17、列出薪金比 SMITH多的所有员工信息

SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename = 'smith')

18、列出所有CLERK(办事员)的姓名,部门名称,部门的人数。

注意:对两张临时表,进行连接查询!(目前最复杂)

SELECT t1.ename,t1.job,t1.dname,t2.deptcount
FROM 
(SELECT e.ename,e.job,d.dname,d.deptno FROM emp e JOIN dept d ON d.deptno = e.deptno WHERE job = 'clerk') t1
JOIN 
(SELECT deptno,COUNT(*)deptcount FROM emp GROUP BY deptno) t2
ON t1.deptno = t2.deptno ;

19、列出最低薪金大于1500的各种工作以及从事此工作的全部雇员人数

SELECT job,COUNT(*) FROM emp GROUP BY job HAVING MIN(sal) > 1500

20、列出在部门sales(销售部)工作的员工的姓名,假定不知道销售部的部门编号

SELECT e.ename,d.dname 
FROM emp e 
JOIN (SELECT deptno,dname FROM dept WHERE dname = 'sales' ) d 
ON e.deptno = d.deptno 

21、列出薪金高于公司平均薪金的所有员工,所在部门,上级领导,雇员的工资等级

SELECT e.ename,d.dname,a.ename,s.grade FROM emp e 
JOIN dept d ON d.deptno = e.deptno 
JOIN emp a ON e.mgr = a.empno
JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal
WHERE e.sal > (SELECT AVG(sal) FROM emp) 

22、列出与SCOTT从事相同工作的所有员工以及部门名称

SELECT e.ename,d.dname FROM emp e 
JOIN dept d ON d.deptno = e.deptno 
WHERE job = (SELECT job FROM emp WHERE ename='scott')

23、列出薪金等于30部门中员工的薪金的其他员工的姓名和工资

SELECT ename,sal FROM emp WHERE sal IN(SELECT distinct sal FROM emp WHERE deptno = 30 ) and depno <> 30

24、列出薪金高于部门30工作的所有员工的薪金的员工姓名,薪金,部门名称

SELECT e.ename,e.sal,d.dname FROM emp e
JOIN dept d ON d.deptno = e.deptno
WHERE e.sal > (SELECT MAX(sal) FROM emp WHERE deptno = 30) AND e.deptno <> 30

25、列出在每个部门工作的员工数量,平均工资和平均服务期限

SELECT d.*,COUNT(e.ename) '员工数量',IFNULL(AVG(e.sal) ,0) '平均工资',IFNULL(AVG(TIMESTAMPDIFF(YEAR,e.`HIREDATE`,NOW())),0) '平均服务期限'
FROM emp e 
RIGHT JOIN dept d ON d.deptno = e.deptno 
GROUP BY d.deptno,d.dname,d.loc

27、列出所有员工的姓名,部门名称和工资

SELECT e.ename,d.dname,e.sal FROM emp e JOIN dept d ON e.`DEPTNO` = d.`DEPTNO` 

28、列出所有部门的详细信息和人数

 SELECT d.*,COUNT(*) FROM dept d 
 LEFT JOIN emp e ON e.`DEPTNO` = d.`DEPTNO`
 GROUP BY d.deptno,d.dname,d.loc     #这里使用三个字段联合分组,因此select后可以使用d.*

29、列出各种工作的最低工资以及从事此工作的雇员姓名

SELECT e.ename,t.job,t.minsal FROM emp e 
JOIN (SELECT job,MIN(sal) minsal FROM emp GROUP BY job) t
ON e.job = t.job AND e.sal = t.minsal

30、列出各个部门的MANGER(领导)的最低薪资

#先找所有的领导,然后按部门分组
SELECT deptno,MIN(sal) FROM emp WHERE job = 'MANAGER' GROUP BY deptno 

31、列出所有员工的年工资、按照年薪从低到高排序

SELECT ename,IFNULL(sal,0)*12 FROM emp ORDER BY sal 

32、求出员工领导的薪水超过3000的员工名称与领导名称

SELECT a.ename '员工',b.ename '领导' FROM emp a JOIN emp b ON a.mgr = b.empno WHERE b.sal > 3000

33、求出部门名称当中,带’S’字符的部门员工的工资合计、部门人数

SELECT d.*,SUM(e.sal),COUNT(e.`ENAME`) FROM emp e
JOIN dept d ON e.deptno = d.deptno 
WHERE d.dname LIKE '%S%' 
GROUP BY d.`DEPTNO`,d.`DNAME`,d.`LOC`	

34、给任职超过30年的员工加薪10%

UPDATE emp SET sal = sal * 1.1  WHERE TIMESTAMPDIFF(YEAR,`HIREDATE`,NOW()) > 30 

关于存储过程、游标、索引原理、存储引擎、事务的文章接着就来!