MySQL笔记(持续更新)

一、MySQL

1、简介

​ 1、介绍数据库，数据库有什么用。

​ 2、MySQL软件的安装与卸载

​ 3、MySQL软件的基本操作

2、数据库的概念

（1）数据库系统的发展史:

​ 数据库管理发展至今已经历了三个阶段：人工管理阶段、文件系统阶段、数据库系统阶段。

（2）数据库的概念:

​ 数据库（DB: DataBase）是长期存储在一起的互相有联系的数据集合。

​ 数据库中的数据是集成的、可共享的、最小冗余的、能为多种应用服务的。

（3）数据库管理系统:

​ 数据库管理系统（DBMS: DataBase Management System ）,是一种系统软件，负责数据库中的数据组织、数据操作、数据维护、控制以及保护和数据服务等，为提供数据服务，提供相应的数据语言

（4）常用的数据库管理系统：MySQL 、小海豚

（5）数据库系统(DBS:Database System):

​ 数据库系统由如下几部分组成：数据库（数据）、数据库管理系统（软件）、数据库管理员（人员）、系统平台之一（硬件平台）、系统平台之二（软件平台）。这五个部分构成一个以数据库为核心的完整的运行实体，称之为数据库系统。

（6）数据库管理员（DBA: Database Administrator）:

​ 由于数据库的共享性，因此对数据库的规划、设计、维护、监视等需要有专人管理，称他们为数据库管理员。

（7）为什么要使用MySQL数据库？

​ 1.开源

​ 2.性能强劲

​ 3.便于安装

----------------------------------------------------------------------------------------------

​ 3、 DOS指令：

开启MySQL的服务，net start mysql

DOS窗口链接MySQL，如果连接不上，则安装时没有选择自动配置环境变量

指令1:DOS链接数据库 mysql -uroot -p密码

-u:user（用户） -p:password（密码)

win+r+cmd

指令2：输入cd desktop进入桌面

指令3：退出mysql:quit

指令4: MYSQL改密：mysqladmin -u用户名 -p原密码 password 新密码

打开注册表：命令行 输入： regedit

卸载 mysql

1打开控制面板

2删除c盘下：program Files里面的mysql文件

删除c盘下：program DATE里面的MySQL文件 前提查看隐藏文件夹

3删除注册表里面的mysql win+r 输入regedit

找到HKEY-LOCAL-MACHINE\SYSTEM\conrolset001\Services\Eventlog\Application\MySql 目录删除

​ \currentControlSet\Services\Evebtlog\Application\mysql 目录删除

也有002

mysql基本卸载完成

----------------------------------------------------------------------------------------------

​ 数据库的基本操作与知识

4、数据库的基本操作

4.1、查看数据库：

Show dabases;

4.2、删除数据库：

drop database 库名

4.3、选择数据库：

 use 库名 

4.4、创建数据库

->create database （库名）
   	
->default character set gb2312

>default collate gb2312_chinese_ci;（红色可省略）

4.5、查看数据库的字符集

 Show create database 库名;

4.6、查看数据库的字符编码

   Show create database 库名 \G;

​ 4.7、系统数据库

（mysql text performance_schema information_schema）----不要随意的更改和删除，后果不堪设想

​ 4.8、数据库对象

包含表、视图、存储、过程、函数、触发器、事件（create database 库名）

​ 9、创建数据库之前要查看数据库有没有该数据库，假如有了则创建不成功。

5、数据库命名规则（标识符）

​ A、组成部分：字母、数字、下划线和$符号

​ B、不能是纯数字组成，数字命名尽量不要使用，因为数字不具有可读性。

​ C、不能是MySQL的保留字（关键字）和创建数据库不能有空格，以及一些特殊符号。

​ D、名字长度不可以超过128个字节。

​ E、多个单词组成的要用下划线隔开。

6、中文也可以创建，但是中文数据库不常用。

前提是你选中的数据库要在系统软件中存在。

7、无论在执行什么操作之前都要查看数据库是否存在。

----------------------------------------------------------------------------------------------

6、存储引擎：决定了表的性能

​ MyISAM：提高了高速存储、检索，还具有全文搜索功能

​ inoodb：MySQL默认，提供了具有提交、回滚和崩溃恢复能力的事务安全，提供外键。

7、数据类型：数据型，时间与日期，字符型

​ 7.1、数据类型：（整型（int），浮点型（float），定点型）

​ ​ 7.2、时间与日期：

​ 【1】date (yyyy-mm-dd）

​ 【2】time （hh:mm:ss）

​ 【3】year (yyyy)

​ 【4】datetime (yyyy-mm-dd hh:mm:ss)

7.3、字符类型（char和varchar）

​ – char（定长字符串）例如: 123 ；会输出7个字符空格也会算

​ （默认一个字节大小）

​ – varchar（变长字符串）不会记两边的空格，但是会计算中间的空格

​ （必须指明大小）

--------------------------------------------------------------------------------------------------

​ 表的基本操作与知识

8、表的基本操作

8.1、创建表（必须带字段）

​    cerate table 表名 (id int,sname char(20),age int,sex char(20));

8.2、查看表

​    show tables;

8.3、数据类型

​ 1.整型数据类型（int/bigint/smalint）

​ 2.近视数值类型(float/double/real)保留的位数依次增多。（float只保留1—2位）

3.日期数据类型（date）

8.4、字符类数据类型（char/varchar(变长)/binary/varbinary）

8.5、查看数据库表的结构（定义）

​    	Desc/descried 数据表名

8.6、数据库存储引擎

​ 1.engine：能量/引擎

 a、MYISAM表 存储引擎管理非事务表，提供高速存储和检索，以及全文搜索能力。

​ b、innodb MySQL默认，提供了具有提交、回滚和崩溃恢复能力的事务安全，提供外键。

8.7、查看表的数据结构

   Show create table 表名 \G;

8.8、查看表的数量

   Show tables;

8.9、删除表数据结构

​    Drop table 表名 

8.10、修改表名

 alter table 表名1 rename to(可省) 表名2；

8.11、增加字段

【1】alter table 表名 add 字段（列名） 数据类型 first;→→【加字段在表头

【2】alter table 表名 add 字段1（列名） 数据类型 after 字段2→→→→→→

→→→→→→→→→→→→→→→→→→→→→→→【在字段2的后面加一个字段1）

【3】alter table 表名 add 字段（列名） 数据类型→→→→→→【默认加在末尾

8.12、删除字段：

  alter table 表名 drop 列名(字段)；

8.13、修改字段中的数据类型

  alter table 表名 modify 列表（字段）新的数据类型；

8.14、修改字段以及数据类型

​     	alter table 表名 change 原表名 新表名 新的数据类型；

8.15、修改字段的顺序

​     	alter table 表名 modify 字段名 数据类型 first 或 after 列名（字段）

--------------------------------------------------------------------------------------------------------------------

二、约束

1、常用的约束条件：

​ （1）主键约束

​ （2）外键约束

​ （3）唯一性约束[候选键约束]

​ （4）默认值约束

​ （5）检查约束

​ （6）非空约束

2、数据完整性约束

​ （1）定义完整性

​ （2）定义参照完整性：

2.1、主键（非空性，唯一性）

​ 1、主键是表中某一列或某一些列所构成的一个组合

​ 2、主键列必须遵循的规则

​ 【1、每一给表只可以定义一个主键

​ 【2、唯一性原则：表中两条不同记录在主键上不能具有相同的值

​ 3、主键的定义语句：

​ 【1、创表时增加主键：（1）在表级创建主键，在创好所有的字段的后面加上 primary key（字段名）。

​ （2）在列级创建主键，在你要加主键的字段的后面加上 primary key。

​ 【2、增加主键（前提已建好表）

		alter table 表名 add primary key(字段1)；

​ 【3、删除主键：

​        alter table 表名 drop primary key；

2.2、复合主键：

​ 1、不能包含不必要的多余列，即当从一个复合主键中删除一列后，

​ 如剩下的列依然满足唯一性原则，则此复合主键是不正确的。

​ 2、一个列名在复合主键的列表中只能出现一次。

​ 3、定义符合主键的语句：

​ 【1、创表时增加主键：（1）在表级创建主键，在创好所有的字段的后面加上 primary key（字段名1，字段名2）。

​

​ 【2、增加主键（前提已建好表）

​          alter table 表名 add primary key(字段1，字段2，。。。。。。)；

2.3、候选键：

​ 2.3–1、候选键的概念

​ 【1、一张表可以有多个候选键（且候选键可以为空）

​ 【2、候选键的值必须都是唯一的（数据唯一）

​ 【3、定义了单个候选键的时候：表的详细信息为uni

​ 多个候选键的时候：表的详细信息为mul

​ 23–2、候选键的语句：

​ 【1、在创表时增加候选键：
（1）在表级创建候选键，在创好所有的字段的后面加上 unique（字段名）。

​ （2）在列级创建候选键，在你要加主键的字段的后面加上 unique key。

​ 【2、增加主键（前提已建好表）

​           alter table 表名 add unique (字段1，字段2，。。。。。。)；

​           或者 alter table 表名 add constraint 约束名 unique （字段1）

​ 【3、删除候选键：

​          alter table 表名 drop index 约束名unique （字段1）

2.4、非空约束（not null）

​ 2.4–1、设置非空约束

​ （1）、在创表时在字段后加入not null

​ （2）、在加入字段时一起加入

​            alter table 表名 add 新字段 新数据类型 not null；

​ （3）、创完表时加入not null

​            alter table 表名 modify 字段 数据类型 not null；

​ 2.4–2、删除非空约束

​          alter table 表名 modify 字段 数据类型 null；

2.5、默认值约束（default）

​ 【1、设置默认约束

​ （1）、在创表时加入

​ 在要加默认值的后面加入default

​ （2）、创完表时加入default

​        alter table 表名 alter 字段 数据类型 default'wokao'；

​ （3）、删除默认值

​         alter table 表名 alter drop 字段 数据类型 default；

-------------------------------------------------------------------------------------------

2.6、外键约束（可以为空）

一、外键的概念：外键是指引用另外一个表中的一列或多列数据，被引用的列应该具有主键约束或者唯一性约束，

​ 外键用来建立和加强两个表数据之间的连接。

二、参照表和被参照表的概念:（参照表的外键就是被参照表的主键）

​ 【1、参照表：参照对象---------（a中的字段→b中获取）----a为参照表，b为被参照表

​ 【2、被参照表：被参照对象 从表 主 表

​ 【3、主表和从表：从表的外键关联必须是主表的主键

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

三、定义参照完整性约束定义外键与主键之间的引用规则，即外键的取值或为空，或者等于被参照关系中的某个主键的值，需要遵守以下规则：

​ 【1、被参照表已经使用create table语句创建，或者必须是当前正在创建的表，

​ 后者称为自参照表，即参照表与被参考表时同一个表(只能用列级方法)；

​ 【2、当被参照表的主键为参照表的外键时，被参照表的主键可以为空

​ 【3、主表的主键和从表的外键的数据类型必须一样的；例如：两者必须是int或char类型

​ 如果不满足是会在创建从表是报1055的错误

​ 【4、必须为被参照表定义主键或候选键；

​ 【5、必须在被参照表的表名后面指定列或列名的组合，这个列或列组合必须是被参照表的主键和候选键；

​ 【6、外键对应列的数目必须和被参照表的主键对应列的数目相同；

​ 【7、外键只可以用在使用存储引擎innodb创建的表中，其他引擎不支持外键。

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
三、外键的作用

​ 【1、用于表与表之间的联系

​ 【2、用来保持数据库的参照完整性

​ 【3、保持数据一致性，完整性，主要的目的是控制储存在外键表中的数据，

​ 使两张表形成关联，外键只能引用外表中的列的值

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

四、参照完整性定义法

​ 【1、增加外键—在创表是添加

​ 【2、用alter 加入外键----------alter table 从表名 add constraint 约束名 foreign key(从表的外键字段) references 主表（主表的主键）

​ 【3、删除外键--------alter table 从表名 drop foreign key 约束名

​ 【4、定义外键你自己不命名是系统会自己给你一个约束名

三、 索引

​ 一、索引的分类：普通索引，唯一索引，主键索引，全文索引

3.1、普通索引（index/key）：

​ 普通索引没有唯一性，可以取空值和重复值，可以创建在任何数据的字段上

​ 1、创建普通索引：create table 表名（ 字段名 数据类型。。。。。 index/key(字段名 asc/desc)）【创表的时候用】

​ a、index或key—参数用来指定字段为索引的关键字

​ b、“索引名”参数用来指定所创建索引的名称

​ c、asc 升序

​ d、desc 降序

​ e、 索引名，asc desc都是可选项

​ 【1、使用create在已经创建好的表中添加索引

​       create index 索引名 on 表名（字段名）；

​ 【2、使用alter table 创建索引

​       alter table 表名 add index/key 索引名(字段名 desc/asc)
	  【假如你没有给索引名系统会自动指定字段名为索引名】

​ 【3、查看索引名

​        show index from 表名

3.2、唯一索引：

1、创建唯一索引：create table 表名（ 字段名 数据类型 。。。。。	unique index/key(字段名 asc/desc)）

a、index或key—参数用来指定字段为索引的关键字

b、“索引名”参数用来指定所创建索引的名称

c、asc 升序

d、desc 降序

e、 索引名，asc desc都是可选项

【1、使用create在已经创建好的表中添加索引

​      create unique index 索引名 on 表名（字段名）；

​ 【2、使用alter table 创建索引

​         alter table 表名 add  unique  index/key 索引名(字段名 desc/asc)						
		【假如你没有给索引名系统会自动指定字段名为索引名】

​ 【3、查看索引名

​       show index from 表名

​ 【3】、主键索引 ：主键索引的创建和修改、删除，直接参考上一次课程中的约束中主键约束，主键的索引均是在创建主键、添加主键时，自动创建的索引。

3.3、多列索引：

【1、概念：在创建表的时候用表级完整性约束法定义多列索引

【2、create table 表名（字段名 数据类型，字段名 数据类型，..........字段名 数据类型，
	 index | key 【索引名】 （字段名 【asc|desc】，...，字段	名n【asc|desc】）；

​ 【3、在已经存在的表上使用create创建多列索引，语法格式如下：

​    	reate index 索引名 on 表名（属性名1【asc│desc】，...,属性名nasc│desc】);

​ 【4、通过alter table创建多列索引，其语法格式如下：

alter table table_nameadd index│key [索引名]（属性名1【asc│desc】，...,属性名n【（长度）】【asc│desc】);

3.4、全文索引：

【1】、全文索引主要是关联在数据类型为char、varchar、和text的字段上，以便能够更加快速地查询数据量较大的字符串类型的字段。

​ 【2】、目前只有存储引擎为MyISAM的数据表才能创建全文索引。

​ 【3】、 创建全文索引：create table 表名（数据类型 字段名。。。。。fulltext index/key(字段名 asc/desc)）

​ a、index或key—参数用来指定字段为索引的关键字

​ b、“索引名”参数用来指定所创建索引的名称

​ c、asc 升序

​ d、desc 降序

​ e、 索引名，asc desc都是可选项

​ 【4】、使用create在已经创建好的表中添加全文索引

​       create  fulltext index 索引名 on 表名（字段名）；

​ 【5】、使用alter table 创建全文索引

​       alter table 表名 add  fulltext  index/key 索引名(字段名 desc/asc)
      【假如你没有给索引名系统会自动指定字段名为索引名】

​ 【6】、查看索引名

​       show index from 表名

【7】删除索引：

​       drop index 索引名 on 表名；

​           或

​       drop table 表名 drop index 索引；

【8】很多情况下不推荐使用索引，索引会增加系统的运行机制和内存，在很多情况下

​ 要提高检索数据的速度，一般采用优化查询语句。

​ 数据的基本操作

关键词（insert into（插入数据），select （查询数据），update（更新数据），delele（删除数据））

四、 数据的插入：（insert into）

​    mysql->insert into 表名(字段名1，字段名2，。。。) values（数据1，数据2，。。。。。数据n）

4.2、插入部分数据

​    MySQL->insert into表名(字段1，字段2，字段3)values(数据1，数据2，数据3)

​ 【注意】：若不给字段名的话，系统自动默认全部字段都要插入数据

4.3、复制表中的数据：

​  【1、insert into 表名1 （字段1,字段2。。。。字段n） select 字段a,字段b。。。字段n from 表名;

​    【2、insert into 表名1 select*from 表名;

4.4、更新数据

​   update 表名 set 字段名='新数据' where 字段名=已有的数据值(独一无二的数据值)；

4.5、删除数据

​   delete from 表名 where 字段名=已有的数据值(独一无二的数据值)；

4.6、删除所有数据

​    delete from 表名；

五、查询（select）

5.1、数据的查询

​   select*from 表名；

5.2、避免重复数据查询（distinct）

​    select distinct 字段from 表名；

5.3、数学四则运算数据查询（* / + -）

​    select 字段名*（+，-，/）数值 from 表名；

5.4、设置显示格式数据查询（concat）

Concat（）：MySQL连接函数

select concat(参数1，。。。。，参数n) as 新字段名 from 表名；

5.5、单条件查询

​   select 字段名 from 表名 where 条件字段>数值

5.6、多条件查询

（and）&&：与 ||：或 ！：非

​    select 字段名1，字段名2  from 表名 where 条件1>800 && 条件2<5000;

5.7、范围关键词查询（between and）

（a到b之间的查询）

 select 字段名1，字段名2  from 表名 where 条件 between 数值 and 数值；

（a到b之外的查询）

 select 字段名1，字段名2  from 表名 where 条件 not between 数值 and 数值；

5.8、条件查询

​ 1、关键字用where

​	select 字段名 from tb_name where conditions;

​ 2、多条件查询语句中：

​ 3、与（&& 或 and）

​ 4、或（|| 或 or）

​ 5、非（！ 或 not）

​ 6、between A and B:A到B 之间的范围查询

​ 7、not between A and B:不在 A到B 之间的范围查询

5.9、is null:空值查询

select * from tb_student where nation is null;

5.10、is not null：非空值查询

select * from tb_student where nation is not null;

5.11、in(A,B,C…)

​ 1、in表示的集合查询，不是区间查询

5.12、模糊查询

​ 1、带like关键字的模糊查询：

​ 2、%通配符： “A%” ：%剩下的所有，以A开头的

​ select 字段名1，字段名2… from 表名 where 字段名1 like 条件;

​ 3、"%A%":查询含有A的所有数据

​ 4、"__" 通配符：_代表一个字符

​ 5、“_江西%”：查询的是第二个、第三个字符分别是江西

5.13、查询结果排序

​ 1、ASC：升序（从低到高）

​ 2、DESC：降序（从高到底）

​	select 字段名 from tb_name order by 字段名 asc/desc

5.14、查询语句

​	1、slect 目标字段名 from tb_name  where 过滤条件

5.15、limit分页查询

 offset X   是跳过X个数据

 limit Y      是选取Y个数据

 limit  X,Y  中X表示跳过X个数据，读取Y个数据

#limit的角标是从 0 开始的

#查询一个表中的前三条数据
select * from tb_student limit 0,3;  #(limit 行数，数据条数)

#limit的查询算法
int now_page = 1     #当前页
int pagesize = 3     #一页有多少数据

select * from tb_Student limit (now_page-1)*pagesize,pagesize;

六、函数(可以嵌套使用)

6.1、format (A，B) 函数 ：

​ 1、设置结果显示的格式，A是表达式或者字段名，B是保留的小数点位数，参与了四舍五入运算。

2、select format (3.1415926,3); -------3.142

6.2、truecate（A，B）函数：

​ 1、设置结果显示的格式，A是表达式或者字段名，B是保留的小数点位数。不会四舍五入。

	2、select truecate (3.1415926,3); -------3.141

6.3、函数的分类：

​ 1、聚合函数，数学函数，字符串函数，时间和日期函数

6.3–1、聚合函数

1、count (字段)函数：

​ 用于查询指定记录的记录数，函数会自动忽略字段为null的值。

2、sum ()函数：

​ 可以求出表中指定数据的和（且只能为int类型）。

3、avg ()函数：

​ 可以求出平均值（且只能为int类型）。

4、max ()函数：

​ 可以求最大值（且只能为int类型）。

5、min ()函数：

​ 可以求最小值（且只能为int类型）。

6.3–2、时间和日期函数

select curdate();

1、curdate ()

​ ==current_date()----获取当前的年月日；

2、curtime()

​ == current_time()—获取时分秒；

3、now ()

​ ----获取 年月日 时分秒；

6.3–3、 数学函数

1、ABS()：

​ 可以求出表中某中字段取值的绝对值。

2、floor()：

​ 向下取整-----floor(3.9999999)===3;

3、ceil()：

​ 向上取整------cell(3.1111111)==4;

4、rand()：

​ 取随机数（用于返回0-1的随机数）;

5、ceil(rand()*1000)：

​ 取1-1000的随机数；

6、sqrt()：

​ 用于运算平方根

6.3–4、字符串函数

1、concat(字符串连接函数)

select concat('aaa','bbb');---->aaabbbselect concat('aaaa',null);---->null

2、insert(字符串替换函数)

select insert('mysql',2,2,'lk');---》mlkql#从第 2 个字符开始替换 2 个字符

3、lower(转小写)

select lower('QWER');-----》qwer

4、upper(转大写)

select upper('qwer');----》QWER 

5、left()：返回字符串s的前n个字符

select left('hello',3);---》hel

6、right()：返回字符串s的后n个字符

select right('hello',3);---》llo

7、substring(str,n,len)：截取字符串

​ 将字符串str在第n个位置，截取len个长度的字符串

select substring('hello',2,3);----》ell

8、lpad(‘str’,len,‘str1’)：加在字符串左边

select lPAD('hello',9,'world');--->worlhello#len--->最大长度==9

9、rpad(‘str’,len,‘str1’)：加在字符串右边

select lPAD('hello',9,'world');----》helloworl#len--->最大长度==9

10、repeat(循环打印)

select repeat('mysql',5);---->mysqlmysqlmysqlmysqlmysql

11、replace(替换指定的字符)

select REPLACE('mysql','m','z');----》zysql

12、if (exp,v1,v2)：

​ 如果表达式exp成立，则执行v1,否则执行v2

13、IFNULL(V1，V2)函数:

​ 也是一种条件判断函数，其表示的是如果表达式V1不为空，则显示v1 的值，否则显示v2的值。

七、连接查询，联合查询

7.1、交叉查询

​ 1、查询的是多个表的数据相乘的数据量，并且产生拼接，生成一个笛卡尔积（比如一个表中有20条，另 一条表有10条，所以一共有200条数据）

	2、select */目标列表  from tb_student,tb_course;

7.2、内连接

​ 1、目的：为了消除交叉连接中某些没有意义的数据行。也就是说在内连接查询中只有满足条件的记录才能出现在 结果集中。

​	2、select  */目标列表 from tb_student,tb_score where 
	 tb_student.studentNo=tb_score.studentNo;(查询字段相同的数据)。

7.3、左右外连接

​ 1、左外连接：left outer join

​	select 目标列表 from tb_student left outer join tb_class on 		
    tb_student.classno=tb_class.classno;

​ 2、右外连接：right outer join

​	select 目标列表 from tb_class   right outer join   tb_student  on 	
	tb_student.classno=tb_class.classno;

八、分组聚合查询（group by）

​
​ 1、常和聚合函数在一起

1、select  字段1  
from tb_name  group by  字段2   
having  过滤条件；

​ 【1、判断学生选课数是否大于等于2

 select studentno,count(courseno)  
 from 
 tb_student  grop by studentno 
 having  count(courseno)>=2;

​ 【注意】

​ 1、字段列表1用了那几列的字段名，那么字段列表也要给出相同的字段名

​ 2、字段2 都必须是检索列或者有效的表达式，但不可以有聚合函数 。

​ 3、分组列中存在null值，则null将作为一个单独的分组返回，如果该列中存在多个null值，则将这些null 值所在的行分为一组

九、子查询

（in）9.1、带关键字的查询（集合查询）

​ 1、in后面跟随的是子查询的结果集

​ 【1、例如：查询选修了课程名为计算机基础的学生信息

​select * from tb_student where studentNo in(select studentNo from tb_score where courseNo 
in(select courseNo from tb_course where courseName='计算机基础'));

（运算符）9.2、带运算符的子查询

​ 1、内带比较 运算符的子查询是指父查询与子查询之间用比较运算符进行连接。当用户能确切知道内层返回 的是单值时，可以用<,<=,>,>=,=,<>,!=等比较运算符构造子查询。

9.3、带any关键字的子查询：

（涉及到比区间 某个 大或小时用）

​ 例子：查询男生中比某个女生出生早的学生姓名和出生年份。

select studentName，year(birthday)

->from tb_student

->where sex=’男’and year(birthday) <any (select year(birthday) from tb_student where sex =’女’);

​ 1、 =ANY:其功能与关键字in一样

​ 2、>ANY(>=ANY):比子查询中返回数据记录中最小的还要大于（大于等于）的数据记录；

​ 3、<ANY(<=ANY):比子查询中返回数据记录中最大的还要小于（小于等于）的数据记录；

9.4、带all关键字的子查询：

（涉及到比区间 所有 大或小时用）

例子：查询男生中比所有女生出生晚的学生姓名和出生年份。

>select studentName，year(birthday)

->from tb_student

->where sex=’男’and year(birthday) >all(select year(birthday) from tb_student where sex =’女’);

1、>all(>=all)：比子查询中返回数据记录中最大的还要大于（大于等于）的数据记录；

2、<all(<=all)：比子查询中返回数据集中最小的还要小于（小于等于）的数据记录；

9.5、带exists关键字的子查询：

​ 1、使用关键字exists构造子查询时，系统对子查询进行运算以判断它是否返回结果集。如果子查询的结果集不为 空，则exists返回的结果为true,此时外层查询语句将进行查询，如果子查询的结果集为空，

exists

​	select studentno,studentname from tb_student where exists(select * from tb_class where 	
	b_student.classno=tb_class.classno and classname='计算机14-1班');

not exists （与exists相反）

	​select studentno,studentname from tb_student where not exists(select * from tb_class where 	
	tb_student.classno=tb_class.classno and classname='计算机14-1班');

9.6、限制查询数量

---->limit关键字查询（查询个数）[起始位置从0开始]（放在结尾）

​	1、select * from tb_score order by studentno asc limit   1（起始位置） ,5（个数）; 
  		//查询学号排名前5的数据

十、视图（view）的操作

10.1、视图的基本操作

​ 1、什么是视图：站在不同角度去观察数据

​ 2、复制整张表：

  create tb_table1(新表名)  as  select  * from tb_table（旧表名）   ;

​ 3、创建视图：

create view 视图名 as  select 目标字段 from tb_student

​ 4、删除视图：

drop view  视图名

​ 5、修改视图数据：

update 视图名  set  字段名2=新数据 where 字段名1=数据;

​ 6、创建视图

【1】、create  or  raplace  view  视图名  as  查询语句 ;

【2】、alter  view 视图名 as  查询语句、

​ 7、查看视图的基本信息

desc view  视图名；

​ 8、查看视图的详细信息

show  create  view  视图名  \g;

10.2、视图的作用

​ 1、保护原表的数据

​ 2、当原数据表的字段太多或数据量太大，减少数据量的请求

​ 3、在视图里增删改查会影响到基本表

10.3、视图

​ 1、当视图来着与一张表的时候------视图可以（增删改查）;

​ 2、当视图来着与多表的时候-----视图不能进行（增加，删除）。、

十一、触发器（trigger）

11.1、触发器的基本概念

​ 1、当预定义事件（如用户修改指定表或视图中的数据时）发生时，触发器会自动执行

​ 2、概念：触发器可以用来对表实施复杂的完整性约束，触发器的运行往往伴随着事件的执行

​ 3、触发器是数据库对象之一，都需要声明与执行（执行要伴随着事件触发来激活）

11.2、触发器的优点

​ 1、触发器运行的前提是：表的数据被做了修改（增删改等等~~）3打错

11.3、创建触发器

​ 1、create trigger 触发器名 before/after trigger_event on 表名 for each row trigger_body

​ 执行的时间 触发器的事件 每一行都执行 触发器语句

​ 2、drop trigger 名字;

11.4触发器的类型

​ 1、insert 触发器

​ 2、update 触发器

​ 3、delete 触发器

​ 4、多条语句的触发器：

​ delimiter关键字：用于修改系统结束的标志

​ begin

​ 执行触发器激活后的语句

​ end

十二、事件（event）

12.1、事件的概念

even：事件

schedule：调度器

even schedule：事件调度器（属于系统，不属于数据库的）

事件：事件用完就会销毁

12.2、查看事件调度器的状态

​ windows下修改my.ini文件中加上 event_scheduler=1或 event_scheduler=on来开启。

​ linux 下修改my.cnf文件中加上 event_scheduler=1或 event_scheduler=on来开启。

​		方法一：show variables like 'event_scheduler';

​		方法二：select @@event_scheduler;

​		方法三：select   processlist 

12.3、打开与关闭事件调度器

​		方式一:set global event_scheduler=1;

​		方式二：set global event_scheduler=true;

12.4、三种事件：

at:

​ 在某个时间后执行命令

every(配合starts使用)：

​ 在某个时间范围内，间隔多久循环执行

​ every 数值 单位

starts：

​ 例如：starts timestamp+[interval 数值 单位]—开始时间

​ ends timestamp+[interval 数值 单位]—结束时间

12.5、创建事件

例如：1分钟后创建一个test表

​				create event e_create on schedule at now()+interval 1 minute      

​						事件	事件名		调度器	    时间点 

​       			 do create table test (name char(20),btrthday date);

​						执行的业务逻辑代码  

例如：每5秒像表test中插入一条数据并且在两分钟后结束

​		create event e_insert on schedule every 5 second starts now() ends  now()+interval 2 
		minute do insert into test values('zzj','2002-08-06');

12.6、修改事件

12.6–1、开启事件：

​			alter  event  事件名   enable   on  slave;

12.6–2、暂停事件：

​			alter  event  事件名  disable  on  slave;

12.6–3、修改事件名：

​			alter  event  原事件   rename  新事件名

十三、存储过程与存储函数

13.1、概念：

​ 【1】、一个存储过程是一个功能模块，一个函数是多个功能模块的集合

13.2、存储过程与存储函数的功能：

​ 【1】、灵活性：能够提高数据库的处理速度，同时也可以提高数据库编程的灵活性

13.3、存储过程的两种模式

​ 【1】、声明式语句（create,update,seleect），过程式语句

13.4、存储过程的优缺点:

13.5、例子：

编写一个程序在数据库db_school中创建一个存储过程，用于实现给定表tb_student中一个学生的学号即可修改表tb_student中该学生的性别为一个指定的性别(使用该存储过程修改学生表中的学号、性别)。

​	create procedure sp_update(in student_no char(10),student_sex char(2)) begin update 
​			变量名字              形参 1       形参2          
	tb_student1 set sex=student_sex where studentNo=student_no; end$$

13.6、存储函数：

13.6–1、概念：

​ 在mysql中，存在一种与存储过程十分相似的过程式数据库对象——存储函数。

13.6–2、注意

​ 存储函数必须包含return 语句，此语句不允许出现存储过程中

13.7、使用存储过程变量：

​		call  变量名（形参  数据类型） ；

13.8、查看储存过程

​ 【1、查看所有库的存储过程：

​		show procedure status \G;

​ 【2、查看当前数据库的指定存储过程

​		show   create  procedure 名字 \G; 

13.9、存储函数与存储过程的区别：

​ 【1、存储函数不能拥有输出参数。存储过程拥有输出参数。

​ 【2、可以直接对存储函数调用，不需要使用call语句，存储过程调用需要。

​ 【3、存储函数中必须包含return语句，此语句不允许出现在存储过程中。

十四、变量

14.1、局部变量

14.1–1、作用范围：

​ 在begin。。。and之间，声明也是一定在begin…and之间

14.1–2、declare：

​	 声明局部变量:  declare 变量名

14.1–3、select:

​		select 变量名;

14.2、 全局变量

​ 系统当中包含的变量，用@@来表示,不能随便定义

14.2–1例如：事件调度器

		select @@event_scheduler;

14.3、用户变量

​ 用户根据自己需求而创建的变量，作用范围是整个会话当中定义用户变量，用一个@符合表示-----set 变量名=10;

14.3–1、set语句(给变量赋值)

		set  @var_name=value

14.3–2、declare(定义数据类型)

		declare  变量名  数据类型

14.3–3、select…into:

14.3–3--1、概念：

​ 将选定列的值直接储存局部变量，可以运用在储存过程。

14.3–3--2、语法：

​		select 字段 into 局部变量 from 表名 where 条件；

十五、流程控制

1、条件判断语句

1–1、条件表达式语法1

if 条件表达式 then 执行语句1;
else 执行语句2;
end if;

1–2、条件表达式语法2

if 条件表达式1 then 执行语句1;
else if 条件表达式2 then 执行语句2；
...
else 执行语句n;
end if;

1–3、例题：比较指定的两个数的大小，并输出最大值

#方法一
DELIMITER $$
CREATE PROCEDURE max1(a INT,b INT)
BEGIN
IF a>=b THEN SELECT a;
ELSE SELECT b;
END IF;
END$$
DELIMITER ;
CALL max1(10,20);
#方法二：
DELIMITER $$
CREATE PROCEDURE max2(a INT,b INT)
BEGIN
DECLARE c INT;
IF a>=b THEN SET c=a;
ELSE SET c=b;
END IF;
SELECT c;
END$$
DELIMITER ;
CALL max2(10,20);

1–4、例题3：通过学号，判断民族，如果是汉族，不变，如果不是汉族，就改为少数民族

DELIMITER $$
CREATE PROCEDURE change_nation(sno INT)
BEGIN
DECLARE c VARCHAR(20);
SELECT nation INTO c FROM tb_student1 WHERE studentNo=sno;
IF c!='汉族' THEN UPDATE tb_student1 SET nation='少数民族' WHERE studentNo=sno;
END IF; 
END$$
DELIMITER ;
CALL change_nation(2023212121);

2、case条件判断表达式

2–1、case条件表达式语法

#a、写法一
case 表达式
     when 值1 then 执行语句1;
     when 值2 then 执行语句2;
     ...
     else 执行语句n;
end case;

2–2、case条件表达式语法

#b、写法二
case 
    when 条件表达式1 then 执行语句1;
    when 条件表达式2 then 执行语句2;
    ...
    else 执行语句n
end case;

2–3、例题：输入姓名与成绩判断等级，并向student_level 表中插入数据–姓名，等级

​

#创建
create table score_level (name varchar(30),lev varchar(30));
#改结束符
delimter $$
#创建储存过程
CREATE PROCEDURE if_level(name varchar(30),lev int)
BEGIN
case 
when lev>=0 && lev<60 then insert into score_level values(name,'不及格');
when lev>=60 && lev<80 then insert into score_level values(name,'良'); 
when lev>=80 && lev<=100 then insert into score_level values(name,'优秀');
else insert into score_level values(name,'你输入的学生成绩有误');
end case;
END$$
#验证存储过程
call if_level('张三',80)

​ 结果：

select * from score_level;
+------+------+
| name | lev  |
+------+------+
| 张三 | 优秀 |
+------+------+
1 row in set (0.06 sec)

3、循环语句：

3–1、while循环：

while
SQL语句---
end while;

3–1-1、计算1–100的和且不包含5的倍数：

#换结束符
delimiter $$
#创建存储过程
CREATE DEFINER=`root`@`localhost` PROCEDURE `for_if_sum`()
BEGIN
DECLARE i int DEFAULT 0;
DECLARE sum1 int DEFAULT 0;
while i<=100 do
if i%5 !=0 then set sum1=sum1+i;
end if;
set i=i+1;
end while;
select sum1;
END $$
#验证存储过程
call for_if_sum()$$

3–2、repeat循环

repeat
sql语句---
until 循环条件

3–2-1、例题：求100以内的奇数和

#换结束符
delimiter $$
#创建存储过程
CREATE DEFINER=`root`@`localhost` PROCEDURE `for_if_sum`()
BEGIN
DECLARE i int DEFAULT 0;
DECLARE sum1 int DEFAULT 0;
repeat
if i%2 !=0 then set sum1=sum1+i;
end if;
set i=i+1;
until i>100
end repeat;
select sum1;
END $$
#验证存储过程
call for_if_sum()$$

3–3、loop循环

sum_loop:loop
sql语句----
if 循环条件 leave loop;
end loop;

#sum_loop---是loop循环的标签

3–3-1、例题：求100以内的偶数和

#换结束符
delimiter $$
#创建存储过程
CREATE DEFINER=`root`@`localhost` PROCEDURE `for_if_sum`()
BEGIN
DECLARE i int DEFAULT 0;
DECLARE sum1 int DEFAULT 0;
sum_loop:loop
if i%2 =0 then set sum1=sum1+i;
end if;
set i=i+1;
if i>100 then leave sum_loop;
end if;
end loop;
select sum1;
END $$
#验证存储过程
call for_if_sum()$$

3–3-1、loop的迭代：

​ 例题：求100以内的奇数和：

#换结束符
delimiter $$
#创建存储过程
CREATE DEFINER=`root`@`localhost` PROCEDURE `for_if_sum`()
BEGIN
DECLARE i int DEFAULT 0;
DECLARE sum1 int DEFAULT 0;
sum_loop:loop
if i%2 =0 then
set i=i+1;    #因为初始值为0当if判断是一直迭代所以要先i=i+1      
iterate sum_loop;
end if;
set sum1=sum1+i;
set i=i+1;
if i>99 then leave sum_loop;
end if;
end loop;
select sum1;
END$$
#验证存储过程
call for_if_sum()$$

十六、游标（光标–cursor）

1、概念：

​ 【1、要查看一张表的内容使用select语句，select语句就会一次性的到一条或者多条的记录

​ 【2、场所：存储过程和触发器（主要为存储过程）

​ 【3、游标相当于-----容器(内存块)

2、使用方法：

2–1、使用游标

​ 【1、要使用游标要先—open cur（游标名）----打开游标

2–2、关闭游标

​ 【1、关闭游标----close cur（游标名）----关闭游标

2–3、查看游标里面的内存

fetch  cur into  变量名

2–4、遍历游标

declare continue handler for not found set b=false;

​ 【1、当循环的过程中，如果游标里面的数据被遍历完了，或者是select语句查询结果为空的时候将设置为false

2、例子：

#寻找 tb_student1 中学号与2014310102相同的人有多少
#当我想要对5行数据中的每一行数据进行操作----用到循环和游标

delimiter $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `NewProc`( )
BEGIN
declare a int default null;      #定义一个变量来存放人数的总数
declare b int default 1;  #作为判断语句
declare cur cursor for select count(studentno) from tb_student1 where studentno=2014310102;            #建立一个游标
declare continue handler for not found set b=0;   #系统用来结束游标的遍历
open cur;          #打开游标
fetch cur into a;  #将游标里的数据存放在
repeat             #repeat循环
select a as 总数;          #查询变量a（因为前面已经赋值了）
fetch cur into a;  #将游标里的数据存放在 
until b=0      #循环判定条件
end repeat;        #结束循环
close cur;         #关闭游标
END $$
#验证存储过程
call NewProc()$$

​