MySQL学习笔记

MySQL学习笔记

目录

第一部分，数据库介绍

第二部分，SQL语言

第三部分，数据完整性

第四部分，多表查询

第五部分，子查询

第六部分，事务

第七部分，视图

第八部分，自定义函数和存储过程

第九部分，索引

第一部分，数据库介绍

1. DBMS：数据库管理系统，一个管理数据库的软件，通常所说的数据库（Oracle、DB2、MySQL、SQL Server）都是指的数据库管理系统。

2. 常见数据库：

a. Oracle：运行稳定，可移植性高，功能齐全，性能超群，适用于大型企业领域，甲骨文公司的产品，收费！

b. DB2：速度快，可靠性好，适用于海量数据，恢复性极强，适用于大中型企业领域，IBM公司的产品，收费！

c. MySQL，开源，体积小，速度快，适用于中小型企业领域，甲骨文公司产品，免费！

d. SQL Server：全面，效率高，界面好，操作容易，但是不跨平台，适用于中小型企业领域，微软公司产品，收费！

3. 数据库类型：关系型数据库（Oracle、DB2、MySQL、SQL Server），非关系型数据库NOsql（MongoDB、Redis、HBase）。

4. RDBMS（关系型数据库）专业术语：数据库，表，列，行，主键，外键，索引。

5. 修改MySQL数据库的密码:

方法一:命令提示符中，在数据库”MySQL”下执行:alter user

‘root’@’localhost’ identidied by ‘新密码’;

方法二：在命令提示符下执行：mysqladmin -u root –p ‘新密码’; 回车后 输入’旧密码’。

6. 语句结束要用分号;。

7. MySQL数据库：

A. 系统数据库（安装完成之后默认自带4个）：

a. Information_schema：存储数据库对象信息，如用户表信息，列信息，里面的内容不能动。

b. Performance_schema：存储数据库服务器性能参数信息，里面的内容不能动。

c. Mysql：存储数据库用户权限信息。

d. Sys：通过这个库可以快速的了解系统的元数据信息，以视图的形式把Information_schema和Performance_schema结合起来，让用户更容易理解相关的数据。

B. 用户数据库，用户自己创建的数据库。

8. 常用命令：

a. 创建数据库：Create database 数据库名;

b. 删除数据库：drop database 数据库名;

c. 查看数据库：show databases;

d. 使用数据库：use 数据库名;

e. 查看数据库表：show tables;

9. 常用的图形化管理工具：Navicat，要先安装好数据库才能安装Navicat，否则会报错。

10. MySQL 的端口号：3306

11. 目前通用的字符集编码是：utf-8

12. 数据库对象：存储、管理和使用数据的不用结构形式，如：表，视图，存储过程，函数，触发器，事件等。

13. 数据库：存储数据库对象的容器。

14. 存储引擎：MySQL中的数据都使用各种不同的技术存储在文件中，每一种技术都使用不同的存储机制，索引技巧，锁定水平，并且最终提供不同的功能和能力，这种存储技术就叫做存储引擎，存储引擎分为以下几种：

a. MYISAM：不支持事务，也不支持外键，访问速度快，对事务完整性没有要求或者以select、insert为主的应用基本都可以使用这个引擎。MySQL5.5及以前的版本默认的

存储引擎就是MYISAM。

b. INNODB：支持事务，提供了具有提交、回滚、崩溃恢复能力的事务安全。但是处理效率没有MYISAM高。MySQL5.5之后的版本默认的存储引擎为INNODB。

c. MEMORY：使用内存来存储数据，访问非常快，但是不安全，一旦服务器关闭数据就会丢失。

15. 文件.frm存储表定义，.myd（mydata）存储数据，.MYI（myindex）存储索引。（在data文件夹下）

第二部分，SQL语言

16. Sql分类：

a. DDL：数据定义语言（跟表结构相关的操作，与表里面的数据无关），用来定义数据库对象，常用的语句有：

Create database 创建数据库，命令：Create database 数据库名 character set utf8；

create table创建数据库表，命令：create table 表名(列名1 数据类型,列名2 数据类型)；

rename table原表名 to 新表名; 修改表的名字

Desc 查看表的字段信息，命令：dese 表名;

Show create table 查看创建表的信息，命令：show create table 表名;

create index创建数据库表的索引，

drop table删除数据库表，

drop index删除数据库表的索引，

truncate删除表中的所有行，

alter table更改表结构（如增加、修改、删除列） ，命令：

alter table 表名 add 列名 数据类型;添加列

Alter table 表名 drop 列名;删除列

Alter table 表名 modify 列名 数据类型; 修改列的数据类型

Alter table 表名 change 原列名 新列名 数据类型;修改列名

alter table add constraint在已有的表上增加约束，

b. DML：数据操作语言（针对表里面的数据），用来操作数据库表中的记录，常用的语句有：

Insert添加数据到数据库中，命令：insert into 表名(列1,列2,列3,列4) values (值1,值2,值3,值4);

Update修改数据表中的数据，命令：update 表名 set 列名=新数据 where 列名=值;

Delete删除数据库中的数据，命令：delete from 表名 where 条件;

c. DQL：数据查询语言，用来查询数据，常用的语句有：

Select选择（查询）数据，

Select语句的书写顺序：

Selectfromwheregroup byhavingorder bylimit;

Select语句的执行顺序：

Fromwheregroup byhavingselectorder bylimit;

 模糊查询：使用like关键字，下划线‘_’代表一个字符，百分号%代表任意字符。

 字段控制查询：

--去除重复记录，命令：select distinct 字段名 from 表名;

--把查询字段的结果进行运算生成新的字段，必须都是数据型，例如：SELECT * , IFNULL(age ,0) + IFNULL(score,0) FROM student;

--对查询结果取别名，用as关键字。

 查询结果排序：使用关键字order by，ASC升序（默认），DESC降序。

 聚合函数：

Count()：统计制定列不为NULL的记录行数；与NULL值的字段进行计算时,结果都为NULL，可以使用IFNULL(字段名,0)，把NULL字段设置为0再参加计算。

Max()：计算制定列的最大值；

Min()：计算制定列的最小值；

Sum()：计算制定列的和；

AVG()：计算制定列的平均值；

 分组查询，在使用分组查询时，select后面直接跟的字段一般都要在group by后面出现。

--Group by 只显示每个组的第一条记录，但与distinct去重复不一样。

--Group by + group_concat()，可以显示每个分组里面包含的记录的集合。

--Group by + 聚合函数，对分组之后的数据做计算。

--Group by + having ，用来分组查询后指定一些条件来输出查询结果。Having的作用于where一样，但having只能用在group by 后面。

 Having 与where的区别：

--Having是指分组后对数据进行过滤；

--Where是在分组前对数据进行过滤；

--Having后面可以使用聚合函数；

--Where后面不能使用聚合函数；

 Limit，指定从哪一行开始，总共查询几行（行数从0开始计数）。

格式：select * from 表名 limit 参数1,参数2；，

例如，limit 0,3，表示从第1行开始查询，一共查3条数据。

 条件查询运算符及关键字有：

运算符：=（等于）、!=（不等于）、<>（不等于）、<（小于）、<=（小于等于）、>（大于）、>=（大于等于）

关键字有：

Between….and;值在什么范围内。

In(集合);值包含在集合中。

Is null;值为空，is not null;值不为空。

And;与

Or; 或

Not;非

d. DCL：数据控制语言，用来定义访问权限和安全级别，常用的语句有：

Grant将权限或角色授予用户，

Revoke从用户或数据库角色中回收权限，

Lock对数据库的特定部分进行锁定，

e. TCL：事务控制语言，常用语句有：

Commit提交事务处理，

Rollback事务处理回滚，

Savepoint设置保存点，

17. 结果集指的是查询结果显示的表格。

18. 常用数据类型：

Double:浮点型，例如double(5,2)表示最多5位，其中必须有2位小数；

Char：固定长度字符串类型；

Varchar：可变长度字符串类型；

Text：字符串类型，用于存储大文本；

Blob：二进制类型，可用于存储二进制文件，如声音、视频、图片；

Date：日期类型，格式为：yyyy-mm-dd

Time：时间类型，格式为：hh:mm:ss

Datetime：日期时间型，格式为：yyyy-mm-dd hh:mm:ss

19. 在MySQL中，字符串类型和日期类型都要用单引号括起来，比如：’mysql’，’2020-01-01’

20. 在命令提示行窗口里面用select语句，可以在语句结尾处加上 \\G ;，来改变显示的效果（按每条记录来显示）。

21. Ctrl键加/，可以对一行进行注释。

第三部分，数据完整性

22. 数据完整性,是指保证用户输入的数据是正确的,分为:

a.实体完整性:表中的一行就是一个实体,标识每一行数据不重复,属于行级约束,类型有:

 主键约束(primary key)，每个表中要有一个主键，数据唯一，并且不能为NULL。添加方式：

--Create table 表名(字段1 数据类型primary key，字段2 数据类型);

--create table 表名(字段1 数据类型，字段2 数据类型，primary key(要设置主键的字段名));

--create table 表名(字段1 数据类型，字段2 数据类型，primary key(主键1，主键2));---联合主键

--Alter table 表名 add constraint primary key(主键);

 唯一约束(unique)，指定列的数据不能重复，但可以为空值。添加方式：

Create table 表名 (字段1 数据类型，字段2 数据类型 Unique)；

 自动增长列(auto_increment)，制定列的数据自动增长，即使数据删除了，还是会从删除的序号继续往下增长。添加方式：

Create table 表名(字段1 数据类型 primary key auto_increment,字段2 数据类型 unique)；

b.域完整性，单元格的数据正确，域代表单元格。分为：

数据类型约束，数值型，字符型，日期型

非空约束（not null）

默认值约束（default）

d. 引用完整性，表与表之间的对应关系，通过外键来实现，创建外键的条件：

--两个表的主键和外键的数据类型要一致；

--两个表必须都是innodb类型。

第四部分，多表查询

23. 表之间的关系：一对一，一对多（添加外键实现，不需要创建中间表），多对多（需要创建中间表，通过中间与其他表之间创建外键来实现）。

24. 拆分表可以避免大量冗余数据的出现。

25. 多表查询

 合并结果集,指的是把两个select语句的查询结果合并到一起,被合并的两个结果列数和列的数据类型必须相同，合并方式有:

--Union,合并时去除重复记录（一行）;

格式:select * from 表1 UNION select * from 表2;

--Union all,合并时不会移除重复记录（一行）;

格式：select * from 表1 UNION ALL select * from 表2;

 连接查询，通过主外键一致可以解决笛卡尔积的问题。

--99查询法，例子：select * from A，B where A.id=B.id;

--内连接,例子：select * from A INNER JOIN B ON A.id=B.id;（inner可以省略）。 内连接与99查询法效果一样。

--外连接

左外连接，左边表中的所有数据全部查出，右边表中只查出满足条件的数据，例子：select * from A LEFT OUTER JOIN B ON A.id=B.id;（outer可以省略）。

右外连接，右边表中的数据全部查出，左边表中只查出满足条件的数据，例子select * from A RIGHT OUTER JOIN B ON A.id=B.id;（outer可以省略）。

--自然连接，连接查询会产生笛卡尔积，通常使用主外键关系等式来去除，但是自然连接无需手动给出主外键等式，它会自动找到这一等式，将两个表中列名和类型完全一致列作为条件。例子，SELECT * FROM A NATURAL JOIN B;

26. 多表查询时不一定非要创建参照完整性（主外键关系），参照完整性的目的主要是保证数据的正确性。

第五部分，子查询

27. 子查询，一个select语句中包含另一个select语句或者两个以上的select语句。

28. 子查询出现的位置：

 Where后，把select查询出的结果当做另一个select的条件，要放在括号里面，例如：SELECT ename,depno FROM employee WHERE depno=(SELECT depno FROM employee WHERE ename ='项羽');。

 From后，把查询出的结果当做一个新表，必须为新表取别名。例子：SELECT ename,salary,depno FROM (SELECT ename,salary,depno FROM employee WHERE depno=30) ss WHERE ss.salary>1200;

29. 自连接,自己跟自己连接,一个表用两次,取不同的别名,例子:

SELECT e1.empno,e1.ename,e2.empno,e2.ename FROM employee

e1,employee e2 WHERE e1.mgr=e2.empno AND e1.empno=7369;

30. 事务：一件事分为多个步骤，只有每个步骤都完成了，这件事才算完成。完成这件事的过程就叫事务。

31. 每条SQL语句就是一个事务，事务只对DML语句有效，对DQL语句无效。

第六部分，事务

32. 事务的特性：

 原子性：指事务包含的所有操作要么全部成功，要么全部失败回滚。

 一致性：一个商品出库时，仓库商品数减1，对应用户的购物车中商品数加1。

 隔离性：一个事务完成了，另一个事务才会有反应，比如，只有张三转账的事务完成了，李四查询账户余额的事务才会生效。

 持久性：一旦事务提交了就写入了数据库，就不可更改了。

33. Mysql 中默认是已经开启事务的，一条语句就是一个事务，一旦回车就相当于提交了事务。

34. 事务的使用：

开启事务：start transaction;

回滚事务：rollback;

提交事务：commit;

35. 事务的并发问题：

 脏读，一个事务正在进行时，另一个事务访问了里面的数据。解决办法提升事务隔离级别，read committed。

 不可重复读，一个事务范围内两次相同的查询却返回了不同的数据，解决办法提升事务隔离级别，repeatable read。

 重复读，解决办法提升事务隔离级别，repeatable read

 幻读，解决办法提升事务隔离级别，Serializable

 对应关系，

36. 事务的隔离级别：由低到高。

 Read-uncommitted（读未提交），一个事务可以读取另一个事务未提交的数据。

 Read-committed（不可重复读），一个事务要等另一个事务提交后才能读取数据。

 Repeatable-read（可重复读），默认的隔离级别。

 Serializable（串行化），这种级别效率低下，比较耗数据库性能，一般不用。

37. 查看事务隔离级别：

select @@global.tx_isolation,@@tx_isolation;

38. 设置隔离级别：

Set globle transaction isolation level read committed;

第七部分，视图

39. 视图，是指由select结果组成的表。

40. 视图特性：

 视图是对若干张基本表的引用，一张虚表，查询语句执行的结果；

 不存储具体的数据（基本表数据发生变化，视图也会随之改变）；

 可以跟基本一样，进行增删改查操作（增删改有条件）；

41. 视图的作用：

 提高安全性：创建一个视图，定义好该视图的所有操作数据，然后将该视图的权限分配给用户，可以避免用户直接访问到基本表的数据。

 提高查询性能：可以从视图中直接查询到指定的数据，而不必每次都从基本表去查询。

 提高数据性：可以把指定的数据单独保存成视图。

42. 创建视图：格式：create [algorithm=merge|temptable|undefined] view 视图名 AS (select语句) [with[local|cascaded]check option];

例子：CREATE VIEW emp_salary_view AS (SELECT * FROM employee WHERE salary>2000);

43. 创建视图的参数：

 algorithm

--Merge（替换式）：默认方式可以省略，这种方式创建的视图可以更新真实表中的数据。

--Temptable（具化式）：数据存在临时表中， 不可以进行更新操作。

--Undefined：不定义，则使用默认。

 With check option：更新数据时，如果数据不符合条件，则无法更新。

 Local和cascaded：可选参数，决定检查测试的范围，默认为cascaded。

44. 修改视图：例子：CREATE OR REPLACE VIEW emp_salary_view AS (SELECT * FROM employee WHERE salary>1000);

45. 删除视图：drop view 视图名；

46. 视图不可更新部分，只要视图当中的数据不是直接来自于基表，就不能直接修改。比如使用聚合函数，distinct关键字，group by子句，having子句，union运算符，from子句中包含多个表，select语句中引用了不可更新的视图等。

第八部分，自定义函数和存储过程

47. Delimiter $，作用把SQL语句的结束标志由’;’分号该为美元符号’$’，避免在写自定义函数和存储过程的时候遇到分号就结束,写完自定义函数和存储过程之后需要再修改回来，每当有用到begin。。and语句的时候都需修改。

48. 自定义函数，语法格式：

elimiter $

Create function 函数名(参数)

Returns 数据类型;

BEGIN

函数体

Return 返回值;

END $

elimiter ;

49. 删除自定义函数，drop function [if exists]函数名;

50. 存储过程，指一组可编程的函数，是为了完成特定功能的SQL语句的集合。也就是具有名字的一段代码，用来完成一个特定的功能。

51. 存储过程的作用：

 将重复性很高的一些操作，封装到一个存储过程中，简化了对这些SQL语句的调用。

 批量处理

 统一接口，确保数据的安全

 相对于Oracle数据库来说，MySQL数据库的存储过程相对功能较弱，使用较少。

52. 存储过程的创建

 创建存储过程：

Delimiter $

Create procedure 名字(参数)

Begin

语句；

End$

Delimiter ;

 调用存储过程：call 名称();

53. 存储过程的参数可以为,in,out,inout,默认为in。

. 删除存储过程，drop procedure [if exists]存储过程名;

55. 创建和调用带有输入参数的存储过程，举例：

delimiter $ -- 更改语句结束符

CREATE PROCEDURE testin(in eno int)

BEGIN

SELECT * FROM employee WHERE empno=eno;

END $

delimiter ; -- 恢复语句结束符

call testin(7902); -- 调用带有输入参数的存储过程

56. 创建和调用带有输出参数的存储过程，举例：

delimiter $ -- 更改语句结束符

CREATE PROCEDURE testout(out sa DOUBLE)

BEGIN

SELECT AVG(salary) into sa FROM employee; --将结果赋值给输出变量

END $

delimiter ; -- 恢复语句结束符

CALL testout(@sa); 调用存储过程，要用@符号加变量名。

SELECT @sa; -- 查看变量的值

57. 对输出变量的使用都需要加@符号。

58. 存储过程中申明变量，用declare，

例如：declare a int default 0;

59. 存储过程中给变量赋值

 Set a=值；

 Select x into a from xxx;

60. 存储过程与函数的区别

 功能上：

--存储过程实现的功能要复杂一点，强大一点，可以执行包括修改表等一系列数据库操作。

--函数的功能正对性比较强。

 返回值上的不同：

--存储过程：可以返回多个值，也可以不返回值，只是实现某种效果或动作。

--函数：必须有返回值，而且只能有一个返回值。

 参数的不同：

--存储过程：参数有三种，in，out，inout。

--函数：参数只有一种，类似于in参数，调用函数时需要按照参数的类型指定值。

 语法结构上的不同：

--存储过程：声明时不需要指定返回类型。

--函数：声明时需要指定返回类型，而且在函数体中必须包含一个有效的return语句。

 调用方式上的不同：

--存储过程：一般是作为一个的部分来执行，用call语句进行调用。

--函数：一般是嵌入在SQL中使用，可以在select中调用。

61. MySQL中流程控制的使用，常用的流程控制语句有：

 IF语句，语法格式：

IF condition THEN

。。。。。。

ELSEIF condition THEN

。。。。。。

ELSE

。。。。。。

END IF;

 CASE语句，

--格式1：

CASE 表达式

WHEN 表达式为值1 THEN 语句

WHEN 表达式为值2 THEN 语句

。。。。。。

ELSE 语句

END CASE;

--格式2：

CASE

WHEN 表达式1 THEN 语句

WHEN 表达式2 THEN 语句

。。。。。。

ELSE 语句

END CASE;

 WHILE循环语句，判断循环条件，满足条件就执行循环体，否则退出，格式为：

WHILE 条件 DO

循环体;

END WHILE;

 LOOP循环语句，没有内置循环条件，但可以通过leave语句退出循环，格式：

循环名:LOOP

循环体;

IF 条件 THEN -- 手工设置结束条件

Leave 循环名;

END IF;

END LOOP 循环名;

 REPEAT循环语句，执行一次循环体，然后判断条件是否为真，为真则退出循环，否则继续执行循环体，格式：

REPEAT

循环体;

UNTIL 条件

END REPEAT；

第九部分，索引

62. 索引，属于数据库优化的内容。用于快速找出在某个列中有一特定值的行。不使用索引的化，MySQL必须从第一条记录开始读完整个表，直到找出相关的行，表越大，查询数据所花的时间越多。

63. 索引的优势与劣势：

 优势：提高检索效率，降低数据排序成本，降低数据库的IO成本，降低CPU的消耗，

 劣势：索引需要占用空间，同时会降低更新表的速度。

. 索引的分类：

 单值索引，即一个索引只包含单个列，一个表可以有多个单值索引。

 唯一索引，索引列的值必须是唯一的，但允许为空值。

 复合索引，一个索引包含多个列。

 全文索引，只有在MYISAM存储引擎上才能使用，并且只能在char，varchar，text类型的字段上使用。

 空间索引，只有在MYISAM存储引擎上才能使用，并且只能在空间数据类型的字段上使用。

65. 索引操作

 自动创建索引，在表上定义了主键时，会自动创建对应的唯一索引。在表上定义了外键时，会自动创建一个普通索引（单值索引）。

 手工创建索引，create index 索引名 on 表名(列名);

 查看索引，show index from 表名;

 删除索引，drop index 索引名 on 表名；

66. Explain ，用来查看索引是否被正确使用，并输出其使用的索引信息。例子：explain select * from employee where ID=1001 \\G; 其中\\G用来分行显示。

67. 索引结构，先对数据进行排序，分为：

 Btree索引，即B+树索引，生成一颗倒立的平衡多叉树。

 Hash索引，只有在memery存储引擎中使用，不常用。

68. 哪些情况需要创建索引：

 主键自动建立唯一索引

 频繁作为查询条件的字段应该创建索引

 查询中与其他表关联的字段，外键关系建立索引

 查询中排序的字段

 查询中统计或者分组的字段

69. 哪些情况不需要创建索引：

 表记录太少

 频繁更新（增删改）的字段

 Where条件里用不到的字段

 包含许多重复值的字段