MySQL数据库 学习笔记

项目【一】 认识数据库

<一>四个基本概念：

1，数据：data 基本对象，文字，图片，音，视频；

2，数据库：DB 存放数据的仓库；

3，数据管理系统：DBMS 管理软件；

4，数据库系统：DBS 系统 硬件，软件，人；

<二>理解数据模型：

一，概念模型：

1，信息世界相关术语：

（1）实体：具体事物（学生，课本）

（2）属性：实体的特征

（3）码：学号是学生实体的码

（4）实体型：实体（属性1 ，属性2，，）

（5）实体集：同类实体的集合，所有学生，所有手机

（6）联系：一对一（学生与校园卡）

​ 一对多（班级与学生）

​ 多对多（学生与课程）

2，E-R 图：（三个要素：实体型，联系，属性）

现实世界（具体事物）------> 信息世界（概念模型）------> 机器世界（数据模型）

​ E-R图 关系模型（二维表）

（1）实体型（矩形）：实体名写在框内

（2）联系（菱形）：联系名写在框内，

（3）属性（椭圆）：标注联系的类型

3.术语：

（1）关系：一个关系就是一张二维表

（2）元组：记录 表中的一行 ----->一个元组/一条记录

（3）属性：字段 表中的一列 ----->一个属性或/一个字段

（4）域：顺序的取值范围

（5）候选码（候选键）：能唯一标识一个元组的属性或属性组合

（6）主码（主键）：从关系中选定一个候选码作为主键

（7）全码：候选码包含关系的所有属性时，候选码称为全码

（8）主属性：候选码的属性

非主属性：不包含在任何候选码中的属性

（9）外码（外键）：在一个表中的一列或多列，其值必须对应于另一个表中的主键（Primary Key）或唯一键（Unique Key）。

<三>SQL语句的分类

1、数据定义（DDL）：定义数据库，表，视图，存储过程等数据库对象。

​ CREATE----创建，ALTER----修改，DROP----删除。

2、数据操纵（DML）：对数据库中的数据进行增删改。

​ INSERT----插入，DELETE----删除，UPDATE----修改。

3、数据查询：查询表里的数据SELECT（SQL语句中使用频率最高）

4、数据控制：控制用户访问权限。

GRANT----给用户授权，REVOKE----收回用户权限，COMMIT----提交事务，ROLLBACK----回滚事务

项目【二】数据库设计

1、概念结构设计------生成E-R图

2、逻辑结构设计------生成关系模型（多张二维表）

项目【三】MySQL环境部署

<一>安装方式

1、以.msi为后缀的二进制分发文件

2、以.zip为后缀的压缩文件

<二>安装

1、用户名：root

2、启动MySQL服务：net start MySQL80

修改服务端字符编码：character-set-server

修改客户端字符编码：set character_set_clicent

<三>使用MySQL

1、启动与停止MySQL：

（1）启动：

​ 通过命令提示符（cmd）：net start mysql80

（2）通过Windows服务管理器：控制面板--管理工具--服务--右击MySQL80--选择“启动”/“停止”

（3）MySQL的三种服务启动类型：自动（适合经常使用的）

​ 手动

​ 禁用

（4）登陆服务器：

​ 在“命令提示符”（cmd）窗口输入本地登录命令：“mysql -u root -p”，然后输入密码即可

（5）查看MySQL状态信息：

​ 在“mysql>”提示符后输入“status”，或者”/s“

（6）退出MySQL：

2、MySQL客户端程序：

（1）部分常用的MySQL客户端程序及其功能：

（2）使用MySQL连接服务器的语法：mysql -h hostname -u username -p;

各参数含义如下：

hostname：服务器的主机地址如果客户端和服务器在同一台机器上，可以输入 hostname/IP地址127.0.0.1，也可以省略该参数

username：是登录服务器的用户名

-p后面的参数为 输入密码，-p和密码之间不能有空格，可以在-p后面输入密码，也可以按Enter后以密文形式输入密码

项目【四】数据库的创建与管理

<一>创建与查看数据库

1、创建数据库：

（1）create database [if not exists] 数据库名;

（2）create database 数据库名;

2、查看数据库：

（1）查看所有数据库列表：show databases;

（2）查看某个数据库的信息：show create database 数据库名;

3、修改数据库：

（1）alter database 数据库名;

（2）[character set 字符集名称 | collate 校验规则名称];

        eg：将studb数据库的字符编码修改为utf8：alter database studb character utf8;

4、删除数据库：

drop database [if exists] 数据库名;

EG1----任务实施：

1.查看所有数据库列表：

show databases;

2.创建mydb1数据库：

create database mydb1;
​
create database if not exists mydb1;

3.查看数据库mydb1的创建信息：

show create database mydb1;

4.创建“学生成绩管理”数据库：

create database studb character set gbk collate gbk_bin;
​
show create database studb;

<二>管理数据库：

1、修改数据库：

语法格式：

alter database 数据库名;

character set 字符集名称 | collate 校验规则名称

[character set 字符集名称 | collate 校验规则名称];

说明：

“|”表示此处为选择项，在所列出的各项中仅需选择一项。可以同时修改数据库的字符集和校验规则，也可以只修改其中一项。

2、删除数据库：

语法格式：

drop database [if exists] 数据库名;

EG2----任务实施：

[1]、修改 studb 数据库的字符编码：

将siudb 数据库的字符编码修改为 ut8，使用该字符集默认的校验规则；

alter database studb character set utf8;

[2]、删除studb数据库：

drop database if exists studb;

项目【五】数据表的创建与管理

<一>数据表基础知识：

一：表的命名规范：

1.不能将MySQL保留字作为表名；

2.表名最大长度为64个字符；

3.表名首字母应为字母，

4.见名知义；

二：数据类型：

1、整数类型：

tinynit     smallint     mediumint     int     bigint

2、浮点数类型和定点数类型：

浮点数：单精度（float）和双精度（double）

定点数类型： DECIMAL(m,d) 通过后面的参数分别设置 精度 和 小数位数

3、日期与时间类型：

YEAR    DATE    TIME    DATETIME    TIMESTAMP

4.字符串类型：

MySQL支持两类字符串数据：文本字符串 和 二进制字符串；

（1）CHAR(n)和VARCHAR(n):

​ CHAR(n) ：是固定长度字符串，n的范围是0~255（如果实际插入长度<n,则用空格补齐到指定长度n）----（身份证号码）；

​ VARCHAR(n) ：是可变长度的字符串，n表示最大长度（如果实际插入的字符串长度不够，按实际插入的 长度储存）----------（家庭住址）；

（2）BLOB和TEXT：

​ BLOB：二进制字符串数据（声音，视频，图像等）

​ TEXT：文本字符串数据（个人简历，文章内容，评论等）

（3）ENUM和SET:

​ ENUM：枚举类型；其值为定义字段时枚举列表中的某个值：

​ eg：ENUM('男','女')

​         SET：可以有0或多个值（最多64个）；

<二>创建与查看数据表：

一，创建数据表：

1.创建表之前（用USE 语句切换到表所属的数据库）：USE 数据库名

2.创建表：create table [if not exists] 表名(字段名1 数据类型1,字段名2 数据类型2, ............... , ......);

二，查看数据表：

1.查看当前数据库中的所有数据表：show tables；

2.查看某张数据表的创建信息：show create table 表名；

3.查看某张数据表的结构：DESC 表名；

EG2----任务实施:

1.使用数据表：

use studb;

2.创建stuinfo表：

create table stuinfo(stuno char(4),stuname char(5),stusex enum('男','女'),stubirthday date,stuaddress  varchar(60));

3.创建stucourse表：

create table stucourse (cno char(4),cname varchar(20),credit decimal(2,1),cteacher char(5));

4.创建stumarks表：

create table stumarks (stuno char(4),cno char(4),stucourse decimal(4,1));

5.查看数据库中的所有数据表：

show tables；

6.查看某张表的创建信息：

desc stuinfo;

<三>管理数据表：

一，修改数据表：

1.修改表名：

（修改表名并不修改表结构）

alter table 旧表名 rename [to] 新表名；

2.修改表结构：

2.1.添加字段：

alter table 表名 add 新字段名 数据类型 [first|after 已存在字段名]；

2.2.删除字段：

alter table 表名 drop 字段名；

2.3.修改字段名:

alter table 表名 change 旧字段名 新字段名 数据类型；

​ eg：在修改字段名的同时可以修改字段的数据类型；

​         如果只修改字段名，则必须写原来的数据类型；

2.4.修改字段的数据类型：

（可以修改数据类型或者长度）

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

​ eg：若表中已有数据，修改字段的数据类型或者长度，可能损坏数据；

        ​ 或者原有数据与新的数据类型不匹配，导致修改不成功。

2.5.改变字段的排列位置：

alter table 表名 modify 字段名1 数据类型 first|after 字段名2；

​ eg：数据类型 ------> 字段1的数据类型，不能省略；

​         first用于将字段1设置为表的第一个字段；

​         after用于将字段1移至指定的字段2的后面；

二，删除数据表：

语法格式：

drop table [if exists] 表1[,表2,....];

EG3--任务实施：

1.切换到 studb 数据库：

use studb;

2.将 stumarks 表改名为 stu_marks：然后show tables

alter table stumarks rename stu marks;

3.给 stuinfo 表增加字段身份证号(stuid CHAR(18))：然后desc

alter table stuinfo add stuid char(18);

4.删除 stuinfo 表的 stuid 字段：然后desc

alter table stuinfo drop stuid;

5.为 stubirthday 字段改名：

alter table stuinfo change stubirthday stubirth date;

6.修改 stuname 字段的数据类型：

将 stuint 表中的 stuname 字段的数据类型修改为 VARCHAR(12)

alter table stuinfo modify stuname varchar(12);

7.调整 stusex 字段的位置：

将stuinfo 表中的 stusex字段移至 stuno 字段之后

alter table stuinfo modify stusex enum('男','女') after stuno;

<四>实施数据完整性：

一，MySQL提供的约束：

1、主键约束（primary key）：取值在表中不能重复，构成主键的字段不能为NULL；

2、外键约束（foreign key）：其值必须参考被他参照的表的主键的取值，当外键不是构成主键的字段时，可以为NULL；

3、唯一约束（unique）：在表中的取值不能重复；

4、非空约束（not null）：在表中的取值不能为NULL；

5、默认约束（default）：在没有给它输入数据的情况下取默认值；

6、检查约束（check）：其值必须使check(表达式)中的表达式的返回值为True；

二，实施数据完整性：

1.在创建表的同时实施数据完整性：

create table [if not exists] 表名 （字段名1 数据类型1 [列级完整性约束1],

​                                                         字段名2 数据类型2 [列级完整性约束2],

​                                                                ............）;

eg: 1.1.列级约束和表级约束的区别在于定义的位置不同；

​       1.2.非空约束和默认约束只能设置为列级约束；

                ​ not null | default 默认值；

       ​ 1.3.若主键是多个字段的组合，则只能定义为表级约束：

                ​ primary key (主键)

        ​ 1.4.外键约束要定义为表级约束：

                ​ foreign key (外键) references 父表名(被参照的字段名)

        ​ 1.5.没有合适的列作为表的主键时，可以增加一列整数并将其值设置为自动增加，自增列用关键字：

​                 auto_increment 标识，自增列的数据必须为整型。

​                 （从 1 开始自增）；

EG1.任务实施：

（在创建表的同时实施数据完整性）

（1）创建stuinfo表：

create table stuinfo(
​
                stuno char(4) primary key,
​
                stuname char(5) not null,
​
                stusex enum('男','女'),
​
                stubirthday date,
​
                stuaddress varchar(60) default '地址不详'
​
                );

（2）创建stucourse表：

create table stucourse(
​
                    con char(4) primary key,
​
                    cname varchar (20) unique,
​
                    credit decimal(2,1) not null,
​
                    cteacher char(5)
​
                    );

（3）创建stumarks表：

​ （重建选修表加约束，学号和课程号为主键，学号是外键，课程号也是外键，分数在0~100之间）

create table stumarks(
​
                    stuno char(4),
​
                    cno char (4),
​
                    stuscore decimal(4,1) check(stuscore>0 and stuscore<=100),
​
                    primary key(stuno,cno),
​
                    foreign key(stuno) references stuinfo(stuno) ,
​
                    foreign key(con) references stucourse(stuno)
​
                    );

（4）创建一张带自增列的作为主键的表：

create table test (
​
                    userid int auto_increment primary key,
​
                    username varchar(10)
​
                    );

2、创建表后实施数据完整性：

（删除的时候：先删除子表，再删除父表）

2.1---添加 主键，外键，唯一，检查 约束：

​         alter table 表名 add [constraint 约束名]

        ​ primary key(字段名) | foreign key(字段名) references 父表名(字段名)

2.2---删除 主键，外键，唯一，检查 约束：

        ​ 删除主键约束：alter table 表名 drop primary key;

​         删除外键约束：alter table 表名 drop foreign key 约束名;

​         删除唯一约束：alter table 表名 drop [index|key] 约束名;

​         删除检查约束：alter table 表名 drop check 约束名;

（2）添加和删除 非空 ，默认 约束：（用alter table...modify语句可以添加或删除非空约束和默认约束）

        ​ alter table 表名 modify 字段名 数据类型 [not null | default 默认值];

EG2---任务实施：

（创建表后实施数据完整性）

（1）重新创建studb表：省略

（2）添加约束：

​ 1.把 stuinfo 表的 stuno 字段设为主键：

alter table stuinfo add primary key(stuno);

​ 2.给 stucourse 表的 cname 字段添加唯一约束：

alter table stucourse add unique(cname);

​ 3.给 stumarks 表的 stuno 列添加外键约束，参照 stuinfo 表的 stuno 列：

alter table stumarks add foreign key(stuno) references stuinfo(stuno);

​ 4.给 stumarks 表的 stuscore 列添加检查约束，要求成绩介于 0~100 分之间：

alter table stumarks add check(stuscore between 0 and 100);

​ 5.给 stuinfo 表的 stuaddress 列设置默认值:'地址不详’：

alter table stuinfo modify stuname char(5) default '地址不详';

​ 6.给 stuinfo 表的 stuname 列增加非空约束：

alter table stuinfo modify stuname char(5) not null;

（3）删除约束：

​ 1.删除 stumarks 表的 stuno 列的外键约束(约束名为stumarks_ibfk 1)，代码如下：

alter table stumarks drop foreign key stumarks_ibfk_1;

​ 注:删除外键时，需要先知道外键名，可通过SHOW CREATE TABLE语句查看。

​ 2.删除 stuinfo 表的主键约束，代码如下:

alter tabe stuinfo drop primary key;

​ 3.删除 stucourse 表的 cname 字段的唯一约束，代码如下:

alter table stucourse drop key cname;

​ 4.删除 stuinfo 表的 stuaddress 列的默认值，代码如下:

alter table stuinfo modify stuaddress varchar(60);

​ 5.删除 stuinfo 表的 stuname 列的非空约束，代码如下:

alter table stuinfo modify stuname char(5);

同步实训：

实施数据完整性。为empdb数据的两张表实施数据完整性，具体约束内容参见表5-8和表5-9。要求用两种 方法:

方法一：

（建表的 同时 实施数据完整性;）

create table dept(deptno char(2) primary key,

			   	   dname varchar(14) unique,

			  	   loc varchar(13));


create table emp(empno char(4) primary key,

			 	  ename varchar(10) not null,

			   	  job varchar(9) default 'clerk',

			  	  mgr char(4),

			          hiredate date,

			          sal decimal(7,2) check(sal>0),

			          comm decimal(7,2),

			          deptno char(2),
			          foreign key (deptno) references dept(deptno)
			          );

方法二：

（建表 后 实施数据完整性(先删除) ）

（1）删除dept表中的约束：

alter table emp drop foreign key emp_ibfk_1;

alter table dept drop primary key;

alter table dept drop key dname;

（2）删除emp表中的约束：

alter table emp drop primary key;

alter table emp modify ename varchar(10);

alter table emp modify job varchar(9);

           *删除job的检查约束：先show create table emp;*

alter table emp drop check emp_chk_1;

（3）添加dept表中的约束：

alter table dept add primary key(deptno);

alter table dept add unique(dname);

（4）添加emp表中的约束：

alter table emp add primary key(empno);

alter table emp modify ename varchar(10) not null;

alter table emp modify job varchar(9) default 'clerk';

alter table emp add check(sal>0);

alter table emp add foreign key(deptno) references dept(deptno);

项目【六】数据更新

<一>插入记录：（insert）

1.单行插入：

（1）insert into 表名 [(字段列表)] values(值列表);

（2）insert into 表名 set 字段名1=值1,字段名2=值2...........;

说明：

        ​ (1)values子句提供的值列表要与字段列表一一对应；

        ​ (2)（字段列表）是可选项，如果省略，则 VALUES 子句要按顺序给每个字段提供值；

        ​ (3)数值列表中字符、日期型的数据要加单引号或双引号。

        ​ (4)自动增长列写成NULL或DEFAULT 均可。

        ​ (5)默认列可以写成 DEFAUIT。

        ​ (6)记录要整条插入，没有提供值的字段不是默认值就是NULL。

        ​ (7)插入的数据必须满足表中定义的完整性约束条件!

2.多行插入：

insert into 表名 [ (字段列表) ] values(值列表1),(值列表2),.........,(值列表n);

EG1----任务实施：

[1.1]、插入一条记录：

insert into stuinfo(stuno,stuname,stusex,stubirthday,stuaddress) values('04','豆','女','2004-10-30','沁园四号');

[1.2]、插入时省略字段列表：

insert into stuinfo values('01','兵','男','2002-11-11','沁园二号');

[1.3]、插入时在字段列表中不输入家庭地中，输出默认值：

insert into stuinfo values('14','丸子','女','2004-1-11',default);


insert into stuinfo(stuno,stuname,stusex,stubirthday) values('21','面包','女','2004-12-11');

[2]、给stucourse表插入多条记录：

insert into stucourse values('001','C语言',4,'张老师'),('002','python',3,'颜老师'),('003','MySQL',3,'周老师'),('004','算法与数据结构',5,'钱老师'),('005','高数',4,'徐老师');

[3]、给 stumarks 表插入一条记录:

insert into stumarks values('04','002','99');


insert into stumarks(stuno,cno,stuscore) values('04','001','99'),('01','001','96'),('14','003','98');

<二>修改记录：(update)

update 表名 set 字段名1=表达式1 字段名2=表达式2 [where 条件]；

EG2----任务实施：

[1]、修改stuinfo表：（将学号为01的学生的性别修改为'女'）

update stuinfo set stusex='女' where stuno='01';

[2]、修改stucourse表：（使每科课程的学分+1）

update stucourse set credit=credit+1;

[3]、修改stumarks表：（把001这门课程的所有成绩都-5分）

update stumarks set stuscore=stuscore-5 where cno='001';

<三>删除记录：(delete/truncate)

1.delete语句：

delete from 表名 [where 条件]；

说明：

WHERE 子句用于 选择 要删除的记录，若 没有WHERE子句，则 删除 所有记录。

先删除子表中的相关记录，再删除父表中的记录。

2.truncate语句：

truncate [table] 表名;

3.两个语句的区别：

(1)delete语句后面可以跟where子句，通过指定where子句中的条件表达式，只删除满足条件的部分记录，而 truncate语句只能用于删除表中的所有记录。

(2)truncate语句的执行效率比delet语句高，然而，用truncate语句删除的数据无法恢复。

(3)使用truncate语句删除表中的所有记录，并且再向数据表插入记录时，自动增加字段的值重新开始计算， 默认的初始值为1;

​ 使用delete语句删除表中的所有记录，并且再向数据表插入记录时，自动增加字段的值为删除记录时该字段的最大值加1。

EG3----任务实施：

[1]、删除 stumarks 表中的记录:

delete from stumarks where stuno='01';

​[2]、删除 stucourse 表中的记录:

delete from stucourse where cno='001';

[3]、删除 stumarks 表中的所有记录:

delete from stumarks;

[4]、创建test表：

insert into test values('1','aaa'),('2','bbb'),('3','ccc');

同步实训：

1.插入记录：

插入父表:

insert into emp values('7369','smith','clerk','7902','1980-12-17','800',0,'20'),('7499','allen','salesman','7698','1981-02-20','1600','300','30'),('7521','ward','salesman','7698','1981-02-22','1250','500','30'),('7566','jones','manager','7839','1981-04-02','2975',0,'20'),('7654','martin','salesman','7698','1981-09-28','1250','1400','30');

insert into emp values('7698','lake','manager','7839','1981-05-01','2850',0,'30'),('7782','clark','manager','7839','1981-06-09','2450',0,'10'),('7788','scott','analyst','7566','1987-04-19','3000',0,'20'),('7839','king','president',0,'1981-11-17','5000',0,'10'),('7844','turner','salesman','7698','1981-09--08','1500','0','30');

insert into emp values('7876','adams','clerk','7788','1987-05-23','1100',0,'20'),('7900','james','clerk','7698','1981-12-03','950',0,'30'),('7902','ford','analyst','7566','1981-12-03','3000',0,'20'),('7934','miller','clerk','7782','1982-01-23','950',0,'10');

插入子表：

insert into dept values('10','accounting','new york'),('20','research','dallas'),
('30','sales','chicao'),('40','operations','boston');

2.修改记录：

[1]、给所有员工的“工资”加500(单位是“元”，此处省略)。

update em set sal=sal+500;

[2]、把工号为“7566”的员工其“工资”加 200(单位是“元”，此处省略),“奖金”改为2000(单位是“元”，此处省略)。

update emp set sal=sal+200 where empno='7566';

3.删除记录：

[3.1]、删除所属部门编号为“20”的部门的信息及该部门的所有员工信息：

delete from dept where deptno='20';

delete from emp where deptno='20';

[3.2]、删除 emp 表及 dept 表的所有记录:

 truncate emp;

 truncate dept;

项目【七】简单数据查询

<一>单表无条件查询：

1.语法格式：

select [all|ditinct] 表达式列表

from 表名

[order by 表达式列表 [asc|desc]]

[limit [起始记录,]返回的行数];

1.1-select子句：

select [all|ditinct] 表达式列表

（表达式可以是常量，字段，函数， 或者 常量，字段，函数与运算符共同构成的式子）

（查询所有字段，用 * ）

（表达式可以用别名，定义别名的方式有：

（1）表达式 别名

（2）表达式 as 别名）

（别名可以使用引号定界，也可以不定界。当别名中含有空格等特殊字符时，必须定界；

all是默认选项，表示输出查询结果中的所有行，包括重复行。

distinct 表示要去掉查询结果中的重复行。）

1.2--from子句：

FROM 子句用于选择查询的数据表，语法格式如下:

from 表名

1.3--order by 子句：

order by 子句用于对查询结果排序，语法格式如下:

order by 表达式列表 [asc|desc]

（ASC 表示升序，DESC 表示降序，ASC是默认选项；

升序排序时，空值排在前面; 降序排序时，空值排在后面。）

1.4--limit子句：

limit 子句用于限制返回行的数量，语法格式如下:

limit [起始记录,]返回的行数

（该子句后面可以跟两个参数，第一个参数表示起始记录，如果省略此参数，则表示从第一行开始返回

（行号从0开始计数），第二个参数表示返回的行数。）

（每个子句的顺序均不能随意交换!）

EG1----任务实施：

[1]、查询所有学生的基本信息：

select * from stuinfo;

[2]、查询所有学生的学号和姓名：

select stuno,stuname from stuinfo;

select stuno as 学号,stuname as 姓名 from stuinfo;

[3]、查询至少选修了一门课程的学生的学号:

select stuno from stumarks;（出现的问题：：有重复行）

​ （执行上述代码，结果可以看到有很多重复的学号(重复行)，其原因是一个学生可以选修多门课程，因 此一个学号可能在该表中出现N次，即表示该学生选修了N门课程。

​ 很明显，此任务要查询的是所有唯一的学号。）

select distinct stuno from stumarks;   

​ （在 SELECT 关键字后面使用 DISTINCT 关键字，可以去掉重复行）

[4]、查询选课成绩表中所有的 学号 及 成绩加5分后的结果，列名用 中文别名：

select stuscore+5 as '成绩' from stumarks;

[5]、查询所有学生的选课信息，先按课程号升序排序，课程号相同的按成绩降序排序：

（分析:排序用 ORDER BY子句，多个排序表达式之间用逗号分隔，降序关键字为DESC；

select * from stumarks order by cno,stuscore desc;

按学生出生日期排序：

select * from stuinfo order by stubirthday; （结果显示：年龄从大到小）

select * from stuinfo order by stubirthday desc;（降序排序）（结果显示：年龄从小到大）

[6]、查询年龄最小 的两名学生的学号、姓名及出生日期:

（分析：把查询结果按照出生日期降序排序----->把年龄最小的两个学生排在前边，再用limit子句限制返回前两行；）

select stuno,stuname,stubirthday from stuinfo order by stubirthday desc limit 2;

<二>单表有条件查询:

1.语法格式：

select [all|ditinct] 表达式列表

from 表名

[where <查询条件>]

[order by<排序列名表> [asc|desc]]

[limit [起始记录,]返回的行数];

2.where子句常用运算符：

<> 和 != 表示 不等于

2.1-关系运算符：

where 表达式1 关系运算符 表达式2;

2.2-范围运算符：

在where子句中可以使用 between...and 查询在某个范围内的数据 ，

还可以在前边加 not 关键字，查询在不在某个范围内的数据

where 表达式 [not] between 初始值 and 终止值

等价于：

where [not] (表达式>=初始值 and 表达式<=终止值)

2.3-列表运算符：

用in关键字指定一个值表，在值表中列出所有可能的值；

where 表达式 [not] in (值1，值2，...  值n)

2.4-模糊匹配运算符：

在where子句中，使用运算符like或者not like 可以对字符串进行模糊查找：

where 字段名 [not] like '字符串' [escape '转义字符']

escape'转义字符' 的作用是当用户要查询的字符串本身含有通配符时，可以使用其对通配符进行转义；

2.5-空值判断：

where 字段名 is [not] null

2.6-逻辑运算符：

（运算对象是逻辑表达式）

where not 逻辑表达式 | 逻辑表达式1 {and|or} 逻辑表达式2

EG2:任务实施：

1.查询成绩在 80~90 分之间的所有选课记录：（结果是包含80和90的）

select * from stumarks where stuscore between 80 and 90;

<=>

 select * from stumarks where stuscore>=80 AND stuscore<=90

1.1查询成绩小于80分，大于90 分的所有选课记录：

select * from stumarks where( stuscore<80 or stuscore>90);

2.查询学号为“01”“04”“14”的学生的基本信息:（可以使用用逻辑运算符OR，或者列表运算符IN（最简单）

select * from stuinfo where stuno in('01','04','14');

3.查询所有姓“张”的学生的基本信息:

select * from stuinfo where stuname like '张%';

4.查询姓名中包括“东”字的所有学生的学号及姓名：

​select stuno,stuname from stuinfo where stuname like '%东%';

5.查询成绩为空值的选课记录，将结果按学号升序排序：

​ 分析:此任务要用IS NULL进行判断，并用ORDER BY子句对查询结果进行排序

select * from stumarks where stuscore is null order by stuno;

6.查询学号为“15”和“11”的学生选修课程号为“002”的课程的选课记录:

此任务筛选记录的条件有两个:一个是学号(“15”或“11”)，一个是课程号(“0002”)，这两个条件要同时满足，因此要用逻辑运算符AND（最好把每个条件用括号括起来）；

select * from stumarks where(stuno in('11','15')) and (cno='002');

<三>单表统计查询：

1.语法格式：

select [all|ditinct] 表达式列表

[聚合函数表达式]

from 表名

[where <行筛选条件>]

[group by 分组名列表

[having 组筛选条件]]

[order by<排序列名表> [asc|desc]]

[limit [起始记录,]返回的行数];

1.1-group by子句：

group by 子句的作用相当于excel的分类汇总。

根据 某列或多列的值 对数据表的 行 进行 分组统计。

在这些列中，对应值都相同的行被分在同一组中。

说明:group by子句用于分组统计数据。

在 select 语句的输出列中，只能包含两种目标列表达式，要么是聚合函数，要么是出现在GROUPBY子句中的分组字段。

如果分组字段的值有 null，则null不会被忽略，而会进行单独分组。

1.2-having子句：

having 子句用于筛选分组。

查询时，只有用group by子进行分组，才有可能用having 子句把满足条件的组筛选出来。

（要使用having子句，必须有group by 子句）

2.常用聚合函数：

count: 统计元组个数 或 一列中值的个数

sum: 统计一列中值的总和

avg: 计算一列值的平均值

max: 求一列值中的最大值

min: 求一列值中的最小值

聚合函数语法格式：

函数名 ([all|distinct]列名表达式|*)

eg: all 是默认选项，表示取列名表达式所有的值进行统计;

​         distinct 表示统计时去掉列名表达式的重复值;

        ​ * 表示记录，比如 count() 表示 统计有多少行。

        *数据项为 NULL时，该数据项是 不纳入统计 的。

EG3：任务实施：

1.查询学生的总人数：

select count(*) as 学生人数 from stuinfo;

2.查询选修了课程的人数：

（一个学生可能选修了多门课程，只能按一人统计）

分析:

查询 选修了课程的人数，即 对stumarks表中的学号进行计数(相同学号不重复计数)，计数应使用count函数。

因为一个学生可能选了多门课程，学号会重复，所以在计数前要先用distinct关键字去掉重复的学号。

select count(distinct stuno) as 选课人数 from stumarks;

3.查询学号为‘01’的学生的总分和平均分：

select sum(stuscore) as 总分,avg(stuscore) as 平均分 
from stumarks 
where stuno='01';

4.查询选修了课程的每个学生的最高分和最低分：

分析:

此任务需要用group by子句将记录按学号进行分组(不同的学生通过学号区分，把学号相同的记录分在一组)，这样，select子句中的聚合函数就会 按小组统计 最高分最低分。

select stuno,max(stuscore) as 最高分,min(stuscore) as 最低分 
from stumarks 
group by stuno;

5.查询 至少选修了两门课程的每个学生的选课数量及平均分，查询结果 按平均分 降序排序：

分析:

先用group by子句按学号分组，再用having子句筛选出满足条件“至少选修了两门课程”的小组，最后，查询结果用order by子句排序。order by子句后面的表达式可以用别名。

（上述group by子句后面的“平均分”还可以用“3”代替，表示 SELECT 子句后面的第3个表达式）

select stuno,count(cno) as 选课门数,avg(stuscore) as 平均分 
from stumarks 
group by stuno 
having count(cno)>=2       （筛选条件）
order by 平均分 desc;

3.【知识拓展】查询语句的执行顺序：

各子句的书写顺序不代表执行顺序，当一条查询语句包含所有子句时，它们的执行顺序如下(按数字序号执行):

（5）select [all|ditinct] 表达式列表 [聚合函数表达式]

（1）from 表名

（2）[where <行筛选条件>]

（3）[group by 分组名列表

（4）[having 组筛选条件]]

（6）[order by<排序列名表> [asc|desc]]

（7）[limit [起始记录,]返回的行数];

上述执行顺序的含义:

选择数据表→在表中筛选行→对筛选出来的行进行分组→筛选组→指定结果集中的数据项(分组统计)→对结果集排序→限制查询返回的行。

（因为select子句在 where、group by和having子句后执行，所以多数 DBMS 不允许where、group by和having子句使用 select子句中的别名。

然而，MySQL 是个例外，只有where子句不能使用select子句中的别名。这里建议读者尽量遵守各数据库之间的通用规则，以便代码在各平台之间进行迁移。）

课堂练习：

1、单表无条件查询：

(1)查询所有课程的基本信息：

select *from stucourse;

(2)查询所有课程的课程号和课程名：

select cno,cname from stucourse;

(3)查询至少有一个学生选修的课程号(要求去掉重复行)：

select distinct cno from stucourse;

(4)查询选课表中所有的课程号及成绩加10分后的结果，要求列名使用中文别名(分别为课程号与成绩)：

select cno as '课程号',stuscore+10 as '成绩' from stumarks;

(5)查询所有的选课记录，要求先按学号升序排序，学号相同的按成绩降序排序：

select * from stumarks order by cno,stuscore  desc;

(6)查询学分最高的两门课程的课程号及学分：

select cno,credit from stucourse order by credit limit 2;

(7)将学生按年龄从大到小进行排序，查询年龄排第三位的学生的基本信息。

select * from stuinfo order by stubirthday limit 2,1;

2、单表有条件查询：

(1)查询成绩小于60分或大于90分的所有选课信息。

select * from stumarks where( stuscore<60 or stuscore>90);

(2)查询课程号为“002”和“004”的课程的基本信息：

select * from stucourse where cno in('002','004');

(3)查询除课程号为“004”和“002”的课程外的所有课程的课程号和课程名：

select cno cname from stucourse where cno not in('002','004');

(4)查询所有任课老师姓“钱”的课程的基本信息：

select * from stucourse where cteacher like '钱%';

(5)查询课程名包括“算法”这个词的课程的课程号及课程名：

select cno,cname from stucourse where cname like '%算法%';

(6)查询成绩不为空值的所有选课记录，要求先按课程号升序排序，课程号相同的按成绩降序排序：

select * from stumarks where stuscore is NOT null order by cno,stuscore desc;

(7)查询年龄为22岁的男生的基本信息。

（注:计算学生年龄的表达式为“year(curdate())-year(stubirhday)”，

其中，curdate()函数返回系统当前日期，year(curdate())返回系统当前年份。）

select * from stuinfo where (year(curdate())-year(stubirthday)=19)and(stusex='女');

3、单表统计查询：

(1)查询所有课程的数量：

select count(*) as 课程数量 from stucourse;

(2)查询所有学生选修的课程的数量 (注:1门课可以被多个学生选修，只能按1统计)：

select count(distinct cno) as 有学生选修的课程数量 from stumarks;

(3)查询所有学生中出生日期的最大值和最小值：

select max(stubirthday) as 出生日期最小值,min(stubirthday) as 出生日期最大值 from stuinfo;


select max(stubirthday) as MAX,min(stubirthday) as MIN from stuinfo;

（3.3）查询所有学生中年龄的最大值和最小值：

select year(curdate())-year(min(stubirthday)) as 年龄最大,year(curdate())-year(max(stubirthday)) as 年龄最小 from stuinfo;

（select 后不能写stuno，因为在没有group by的聚合查询中，select列表的表达式 #1 包含非聚合列 原因是：当mysql的sql_mode存在only_full_group_by的时候，在不使用group by 并且select后面出现聚集函数 的话，那么所有被select的都应该是聚集函数，否则就会报错。）

(4)查询课程号为“002”的课程的平均分：

select avg(stuscore) as 平均分 from stumarks where cno='002';

(5)查询每门课程的最高分及最低分：

select cno,max(stuscore) as 最高分,min(stuscore) as 最低分 from stumarks group by cno;

(6)查询 平均分最高 的两门课程的 课程号 及平均分：

select cno as 课程号,avg(stuscore) as 最高平均分  from stumarks group by cno limit 0,1;

(7)查询 平均分达到 75 分以上 的每门课程的 选课人数及平均分，要求按 平均分升序 排序：

select cno,count(distinct stuno) as 选课人数,avg(stuscore) as 平均分 from stumarks group by cno having avg(stuscore)>=75 order by 平均分;

同步实训：

1、单表无条件查询：

① 查询所有部门的基本信息：

select * from dept;

②查询所有员工的姓名及工资：

select ename,sal from emp;

③ 查询所有员工的工号和姓名，并输出员工的1.2倍工资(工资*1.2)，要求使用中文别名(分别为工号、姓名、工资)：

select empno as 工号,ename as 员工姓名,sal*1.2 as 工资 from emp;

④查询至少有一个员工的部门的部门编号(要求去掉重复行)：

select distinct deptno from emp;

⑤ 查询所有的职位(要求去掉重复行)：

select distinct job from emp;

⑥ 查询 工资最高 的员工的工号、姓名及工资：

select empno,ename,sal from emp order by sal desc  limit 1;

⑦ 查询 工作时间最长 的两名员工的工号、姓名及入职日期(为了便于操作，此处的“工作时间最长”可简单理解为“入职时间最早”：

select empno,ename,hiredate from emp order by hiredate limit 2;

2、单表有条件查询：

①查询部门编号为“30”的部门其所有员工的基本信息：

select * from emp where deptno='30';

②查询职位为“manager”或“president”的员工的 工号、姓名及职位：

select empno as 工号,ename as 姓名,job as 职位 from emp where job='manager' or job='president';

③ 查询 每个员工的工号 及其奖金和工资总和(若奖金为空值，则按0计算)：

说明:此题会用到 IFNULL函数。语法格式如下:ifnull(expr1,expr2)功能:假如 expr1不为null，则函数的返回值为 exprl; 否则其返回值为 expr2:

select empno as 工号,sal+(ifnull(comm,0)) as 工资和奖金之和 from emp;

④查询工资在 1500~2500 元之间的所有员工的 工号、姓名及工资：

select empno as 工号,ename as 姓名,sal as 工资 from emp where sal between 1500 and 2500;

⑤ 查询部门编号为“10”的部门的经理(MANAGER)，以及部门编号为“20”的部门的普通员工(CLERK)：

select * from emp where (deptno='10' and job='manager') or (deptno='20' and job='clerk');

⑥ 查询在部门编号为“10”的部门中，既 不是经理 也不是普通员工，并且 工资大于等于2000 元的员工 的 基本信息：

select * from emp where (deptno='10') and not(job='manager') and not(job='clerk') and (sal>=2000);

⑦查询奖金为空值的员工的工号、职位、工资及奖金，结果按工资升序排序：

select empno as 工号,job as 职位,sal as 工资,comm as 奖金 from emp where comm=0 order by sal;

⑧查询 没有奖金 或 奖金低于500元 的员工的基本信息

(“没有奖金”指奖金为空值或等于可用 ifnull 函数简化表达式)：

select * from emp where(comm=0 or comm<=500);

3、单表统计查询：

① 查询员工的数量：

select count(*) from emp;

②查询 至少有一个员工的部门 的部门数量：

select count(distinct deptno) as 至少有一个员工的部门 from emp;

③查询所有员工的最高工资和最低工资：

select max(sal) as 最高工资,min(sal) as 最低工资 from emp;

④查询在部门编号为“20”的部门中，员工的最高工资和最低工资：

select max(sal) as 部门20的最高工资,min(sal) as 部门20的最低工资 from emp where deptno='20';

⑤查询 各部门的 员工的 工资总和 及 平均工资：

select sum(sal),avg(sal) from emp group by deptno;

⑥查询员工的 平均工资 大于 2000元的部门 其员工的 平均工资，结果按 平均工资 降序排序：

select avg(sal) from emp  group by deptno having avg(sal)>2000 order by avg(sal) desc;

⑦查询 员工的平均工资最高 的 部门其部门编号，以及 该部门的 员工的平均工资：

select deptno as 员工平均工资最高的部门编号,avg(sal) as 员工平均工资 from emp group by deptno limit 1;

⑧查询 各职位 的 最低工资 及 最高工资：

select job,max(sal) as 该职位最高工资,min(sal) as 该职位最低工资 from emp group by job;

项目【八】高级数据查询：

如果想查询的数据来自多张表，或者查询的数据和筛选条件不在同一张表中，就要用到高级查询。

此外，高级查询还可以解决一些复杂的单表查询问题。

高级查询 涉及 连接查询、子查询、集合查询。

连接查询 分为

1、交叉连接

2、内连接（等值连接、非等值连接、自连接）||||||||||||（隐式连接、显示连接）

3、外连接（左外连接，右外连接、全连接）

（连接查询 指 ------> 把多张表连接成 一张表 进行查询）

子查询可以嵌套在查询语句中使用，也可以在更新语句中使用，以实现更强大的数据更新功能。

各种连接图解：

<一>交叉连接：

交叉连接又被称为 笛卡儿连接

对表1(m行)和表2(n行)进行交叉连接

就是把 表1的每行 分别 与 表2的每行 进行连接，结果集是两张表所有记录的任意组合（本质），

共有m*n行。

语法格式：

​ 语法格式1：select ... from 表1,表2;

​ 语法格式2：select ... from 表1 cross join 表2;

EG1----任务实施：

[1]、把 stuinfo 表和 stumarks 表进行交叉连接：

语法一：  select * from stuinfo,stumarks;

语法二：  select * from stuinfo cross join stumarks;

（对这两张表进行交叉连接毫无意义，交叉连接在实际应用中也不常见，这里主要为了让读者更好地理解交 叉连接的结果集。）

<二>内连接：

内连接相当于取两张表的交集；

内连接用于把两张表中 满足条件的 记录组合在一起。

内连接相当于交叉连接的子集。

2.1等值连接：

即在两张表有相同字段的前提下，把两张表中该字段值相等的行进行连接。

语法格式：

语法格式1：select ... from 表1,表2 where 表1.列名=表2.列名;

语法格式2：select ... from 表1 [inner] join 表2 on 表1.列名=表2.列名;

2.2非等值连接：

2.3自连接：

自连接是一种特殊的内连接，即连接的两张表是完全相同的。

我们也可以将自连接理解为一张表的两个副本的连接，

为了区分这两个副本，需要给它们分别起别名。

内连接有两种语法格式，同样，自连接也有两种语法格式。

语法格式：

语法格式1：SELECT ... FROM 表名 别名1,表名 别名2 WHERE 别名1.列名=别名2.列名;

语法格式2：SELECT ... FROM 表名 别名1 JOIN 表名 别名2 ON 别名1.列名=别名2.列名;

说明：

1、将n张表连接成一张表，需要进行n+1次的两两连接操作。

对于语法格式1，在WHERE子句中给出连接条件，n张表有n-1个连接条件，所有连接条件要用AND运算符连接起来;

对于语法格式 2，在 FROM 子句后面指定连接条件，在JOIN 后面指定表名，在ON后面写一个连接条件。

2、如果引用的字段被多张表所共有，则引用该字段时必须指定其属于哪张表，引用的语法格式为“表名.字段名”。为了简化连接条件，可以给表起别名。使用别名后，在该查询语句中要统一使用别名代替表名。

3、如果进行连接的两张表没有共同字段，则需要找和这两张表均有共同字段的第三张表，从而间接地完成连接操作。

EG2----任务实施：

[1]、查询所有学生的学号、姓名、课程号及成绩：（等值连接）

分析:要查询的数据分别在stuinfo表和sumarks表中，先把两张表进行内连接，转变为一张表，把学生的基本信息与其选课记录连接起来，连接条件是学号相同。

因为学号在两张表中都存在，故引用该字段时必须指定其属于哪张表，否则进行查询时会报错。

语法1：

select stuinfo.stuno,stuname,cno,stuscore
FROM stuinfo,stumarks
WHERE stuinfo.stuno=stumarks.stuno;

语法2：

select stuinfo.stuno,stuname,cno,stuscore 
FROM stuinfo JOIN stumarks 
ON stuinfo.stuno=stumarks.stuno;

[2]、查询所有学生的学号、姓名、课程名及成绩:（等值连接）

分析：要查询的数据分别在stuinfo,stucourse,stumarks三张表中，要把三张表连接成一张；

注意：三张表连接时，有两个连接条件，要用AND连接起来；

2.1----直接连接：

语法1：

select stuinfo.stuno,stuname,cname,stuscore 
FROM stuinfo,stumarks,stucourse 
WHERE stuinfo.stuno=stumarks.stuno AND stumarks.cno=stucourse.cno;

语法2：

select stuinfo.stuno,stuname,cname,stuscore 
FROM stuinfo JOIN stumarks ON stuinfo.stuno=stumarks.stuno
JOIN stucourse ON stumarks.cno=stucourse.cno;

2.2----简化代码+连接：

语法1：

select a.stuno,stuname,cname,stuscore 
FROM stuinfo a,stumarks b,stucourse c 
WHERE a.stuno=b.stuno AND b.cno=c.cno;

语法2：

select a.stuno,stuname,cname,stuscore 
FROM stuinfo a JOIN stumarks b ON a.stuno=b.stuno
JOIN stucourse c ON b.cno=c.cno;

[3]、查询选修了“周老师”老师讲授的课程的学生的 学号及姓名:（等值连接）

分析:老师姓名在stucourse表中，学生的学号和姓名在stuinfo 表中，这两张表没有共同字段，无法直接内连接，需要找和这两张表均有共同字段的第三张表stumarks，从而间接地完成连接操作。

语法1：

select a.stuno,stuname 
FROM stuinfo a,stumarks b,stucourse c 
WHERE (a.stuno=b.stuno AND b.cno=c.cno) AND (cteacher='周老师');

语法2：

select a.stuno,stuname 
FROM stuinfo a JOIN stumarks b ON a.stuno=b.stuno JOIN stucourse c ON b.cno=c.cno 
WHERE cteacher='周老师';

[4]、查询同一门课程成绩相同的选课记录:（自连接）

分析:此任务虽然看似单表查询问题，但是，使用简单查询是无法解决的。

其原因是比较的数据不在同一行中。我们可以通过 自连接 把 学号不同'+'课程号相同、成绩相同' 的记录连接成一条记录，这样就能得到需要的查询结果了。

select a.stuno,b.stuno,a.cno,a.stuscore FROM stumarks a,stumarks b 
WHERE a.stuscore=b.stuscore AND a.stuno<>b.stuno AND a.cno=b.cno;

<三>外连接：

外连接分为 左外连接、右外连接和全外连接，MySQL目前支持左外连接和右外连接。

我们先介绍左表和右表的概念:对两张表进行连接，关键字JOIN 左边的表叫左表，JOIN 右边的表叫右表。

3.1左外连接：

左外连接的结果集包括 两张表内连接的结果集 和 在左表中没有参加内连接的记录；

如果左表中的某个记录在右表中没有匹配的记录，则 右表中对应的列值在结果集中为空值。

左边的是主表，左表数据全部显示，右表显示符合ON后的条件的数据，不符合的用NULL代替。

与内连接相似，两张表进行外连接时，通常对两张表的相应字段进行比较。

语法格式：SELECT ... FROM 表1 LEFT [OUTER] JOIN 表2 ON 表1.列名=表2.列名;

3.2右外连接：

右外连接的结果集包括 两张表内连接的结果集 和 在右表中没有参加内连接的记录；

如果右表中的某个记录在左表中没有匹配的记录，则左表中对应的列值在结果集中为空值。

右边的是主表，右边表数据全部显示，左边表显示符合ON后的条件的数据，不符合的用NULL代替。

语法格式：SELECT ... FROM 表1 RIGHT [OUTER] JOIN 表2 ON 表1.列名=表2.列名;

左外连接完全可以和右外连接相互替代：只要把表1和表2交换位置，并把LEFT替换为 RIGHT(或者把 RIGHT 替换为 LEFT)即可。

3.3全连接：

全外连接：full join 或 full [outer] join，但Mysql不支持，可以使用union组合并去重实现。

全外接查询：就是 左表独有的数据 加上 右表独有的数据

语法格式：

select*from aaa left join bbb on aaa.C=bbb.C and aaa.D=bbb.D and aaa.E=bbb.E
union 
select*from aaa right join bbb on aaa.C=bbb.C and aaa.D=bbb.D and aaa.E=bbb.E;

EG3----任务实施：

[1]、查询 没有选修课程 的学生的 基本信息：

分析:此任务用内连接显然无解，其原因是内连接后没有选修课程的学生的基本信息就被剔除了，

可以使用外连接把没有选课的学生基本信息保留下来。

(1)先查看 stuinfo表与stumarks 表进行 左外连接 的结果集。

select * FROM stuinfo LEFT JOIN stumarks ON stuinfo.stuno=stumarks.stuno;

（右外连接：

select * FROM stumarks right JOIN stuinfo ON stuinfo.stuno=stumarks.stuno;

）

(2)在结果集中，没有选修课程的学生所在的行对应stumarks表中的相关字段的值全为NULL。

该特征可以用于判断哪些学生没有选修课程，根据实体完整性规则，stumarks表中的参与内连接的那些行，其主属性(构成主键的字段)不可能为NULL，此处涉及两个主属性(stuno 和 cno)，通过判断它们其中的任意一个是否为空值就可以筛选出没有选修课程的学生。

select stuinfo.* 
FROM stuinfo LEFT JOIN stumarks 
ON stuinfo.stuno=stumarks.stuno 
WHERE stumarks.stuno IS NULL;

<四>子查询：

子查询指将一个查询块嵌套在SELECT、INSERT、UPDATE、DELETE等语句内的WHERE子句或其他子句中进行查询。SQL允许多层嵌套查询，即在一个子查询中还可以嵌套其他子查询。

！！！！子查询要用括号括起来！！！！

根据子查询的执行 是否依赖外部查询， 可将子查询分为两类，即 相关子查询 与 不相关子查询；

不相关子查询指不依赖外部查询的子查询，相关子查询指依赖外部查询的子查询。

不相关子查询：先于外部查询执行，子查询得到的结果集不会显示，而是传给外部查询使用，不相关子查询总共执行一次。

相关子查询：它的的执行依赖外部查询，即需要外部查询为其传递值，与外部查询正在判断的记录有关，外部查询执行一次，相关子查询就执行一次。

子查询返回的值要被外部查询的[NOT]IN、[NOT]EXISTS、比较运算符、ANY(SOME)、ALL 等操作符使用，根据操作符的不同，子查询可以分为以下几种：

1.IN子查询：

在嵌套查询中，子查询的结果往往是一个集合，用关键字 IN 判断某列值是否在集合中。

NOT IN 表示査询不在集合中的某列值。

IN 子查询通常是不相关子查询，也是一种最常用的子查询。

2.比较子查询：

带有比较运算符的子查询指 在外部查询与子查询之间用比较运算符进行连接。

当用户确切地知道子查询返回单个值时，可以使用>、<、=、>=、<=、!=或<>等比较运算符。

比较子查询 可能是不相关子查询，也可能是相关子查询，要根据实际情况进行分析。

例如，查询成绩比该门课程的平均成绩高的选课记录，这属于 相关子查询，其原因是子查询要查的是该课程的平均成绩，它与外部查询正在判断的选课记录的课程号相关。

3.EXISTS子查询：

使用 EXISTS 判断子查询是否返回任何记录;

当子查询的结果不为空集(即存在匹配的行)时，返回逻辑真值。

NOT EXISTS用来判断是否不存在匹配的行。EXISTS子查询是相关子查询。

4.相关子查询和不相关子查询：

4.1---不相关子查询：

非相关子查询的执行不依赖与外部的查询。执行过程：

（1）执行子查询，其结果不被显示，而是传递给外部查询，作为外部查询的条件使用。

（2）执行外部查询，并显示整个结果。　　

非相关子查询一般可以分为：返回单值的子查询和返回一个列表的子查询

4.2---相关子查询：

相关子查询的执行依赖于外部查询。多数情况下是子查询的WHERE子句中引用了外部查询的表。执行过程：

（1）从外层查询中取出一个元组，将元组相关列的值传给内层查询。

（2）执行内层查询，得到子查询操作的值。

（3）外查询根据子查询返回的结果或结果集得到满足条件的行。

（4）然后外层查询取出下一个元组重复做步骤1-3，直到外层的元组全部处理完毕。

（与前面介绍过的子查询不同，相关子查询无法独立于外部查询而得到解决。该子查询需要一个“类编号”的值。而这个值是个变量，随SQLSever检索表中的不同行而改变。）

EG4----任务实施：

[1]、查询选修了课程的学生的基本信息：

（1）使用 IN 子查询：

先查询所有选修了课程的学生的学号：

select DISTINCT stuno FROM stumarks;

然后根据第一步得到的学号集合查询这些学生的基本信息：

select * FROM stuinfo WHERE stuno IN (select DISTINCT stuno FROM stumarks);

（2）使用EXISTS子查询：

select * FROM stuinfo WHERE EXISTS(select * FROM stumarks WHERE stuno=stuinfo.stuno);

[2]、查询没有选修课程的学生的基本信息:

（1）使用IN 子查询：

select * FROM stuinfo WHERE stuno NOT IN (select DISTINCT stuno FROM stumarks);

（2）使用EXISTS子查询：

select * FROM stuinfo WHERE NOT EXISTS(select * FROM stumarks WHERE stuno=stuinfo.stuno);

[3]、查询选修了“高数”课程的学生的基本信息:

分析:先从课程表（stucourse）中找到“高数”的课程号，再从选课成绩表（stumarks）中找出选修了该课程的学生的学号，最后从stuinfo表中找出学生的基本信息。（三层子查询）

第一步，查询“高数”课程的课程号：

select cno from stucourse where cname='高数';

第二步，根据“高数”课程的课程号查询选修了该课程的学生的学号：

select stuno from stumarks where cno=(select cno from stucourse where cname='高数');

第三步，根据学号查询学生基本信息：

select * from stuinfo where stuno in (select stuno from stumarks where cno=(select cno from stucourse where cname='高数'));

[4]、查询所有课程中最高成绩对应的选课记录:

分析:虽然本任务涉及单表查询，但是因为最高成绩并不能事先得知，所以这里无法使用简单查询。

​ 可以使用子查询先得到最高成绩，再查询符合条件的选课记录。

第一步，查询选课表中的最高成绩：

select * from stuinfo where stuno in (select stuno from stumarks where cno=(select cno from stucourse where cname='高数'));

第二步，查询成绩等于最高成绩的选课记录：

select * from stumarks where stuscore=(select max(stuscore) from stumarks);

<五>子查询在更新语句中的应用：

1、从一张表向另一张表导入数据：

利用子查询，可以把查询结果（一行或多行数据）插入表中，实现从一张表向另一张表导入数据的功能。

语法格式：

insert into 表名[(字段列表)] select 语句;

说明：字段列表中字段的个数、数据类型必须和SELECT语句中查询的数据项的个数及数据类型 一一对应。

2、嵌套修改：

利用子查询返回的单个值，修改表中某个字段的值；

语法格式：

update 表名 set 字段名=(返回单个值的子查询) [where 条件];

EG5----任务实施:

[1]、创建 stuinfo_2 表，并从 stuinfo 表中导入数据:

创建一张空表 stuinfo_2 (stuno,stuname,avg_stuscore)，

（1）创建空表 stuinfo_2:

create table stuinfo_2
(stuno char(4) primary key,stuname char(5),avg_stuscore decimal(4,1));

（2）将 stuinfo 表中的 stuno 字段和 stumame 字段的数据导入 stuinfo_2 表 的相应字段中：

分析:使用子查询得到 stuinfo 表中的 stumo字段和 stuname 字段的数据，再将这些数据插入stuinfo_2表中;

insert into stuinfo_2(stuno,stuname) select stuno,stuname from stuinfo;

[2]、修改 stuinfo 2表中学号为“04”的学生的平均成绩:

分析:通过查询 stumarks 表可以得到学号为“04”的学生的平均成绩，并且返回的结果是单个数值，我们可以使用这个返回值修改stuinfo_2表中该学生的平均成绩。

UPDATE stuinfo_2 
SET avg_stuscore=(select avg(stuscore) from stumarks where stuno='04')
where stuno='04';

---

思考:

如果想一次修改所有学生的平均成绩，则应该怎么改进上面的代码?

分析:可以使用相关子查询，把正在更新记录的学号传给子查询的WHERE 子句:

UPDATE stuinfo_2 
SET avg_stuscore=(select avg(stuscore) from stumarks where stuno=stuinfo_2.stuno);

[3]、把“高等数学”课程的所有成绩加5分:

分析:stumarks表没有课程名，要先用子查询得到“高等数学”课程的课程号，再将其传给外部的修改语句作为筛选条件；

UPDATE stumarks
SET stuscore=stuscore+5
WHERE cno=(select cno from stucourse where cname='高数');

将“全圆佑”同学的每门课程都加六分；

update stumarks

set stuscore=stuscore+5

where stuno=(select stuno from stuinfo where stuname='全圆佑');

[4]、删除学生“张元英”的所有选课记录:

分析:stumarks表没有学生姓名，要先用子查询从stuinfo表中得到学生“刘卫平”的学号，再将其传给外部的删除语句作为筛选条件。

DELETE FROM stumarks
WHERE stuno=(select stuno from stuinfo where stuname='张元英');

<六>集合查询：

面向集合的操作方式是 SQL 的特点之一，关系型数据库的每张表就是一个集合，一条记录可以看作集合的一个元素。SQL 提供了 并、交、差 运算，利用 并、交、差 运算可以把一些复杂的查询问题简单化。

MySQL目前只支持并运算，并运算也就是合并查询，即将多个查询结果合并到一起:

语法格式：

查询1

union | union all

查询2;

说明：

查询1和查询2的结果集的字段个数和数据类型要一一对应；

UNION ALL 是简单合并，重复行保留；UNION也是简单合并，但会去掉重复行；

UNION和JOIN 的区别:它们都是合并操作，但方向不同，前者是行，后者是列；

EG6----任务实施：

[1]、查询选修了“0001”或“0003”号课程的学生学号，结果 保留 重复行：

分析：先查询选修了课程号为“001”的课程的学生的学号，以及选修了课程号为“005”课程的学生的学号。再把两个结果集进行合并；

select stuno from stumarks where cno='001'
UNION ALL
select stuno from stumarks where cno='005';

[2]、查询选修了“001”或“005”号课程的学生学号，结果 去掉 重复行：

select stuno from stumarks where cno='001'
UNION
select stuno from stumarks where cno='005';

!!!!!!!!!!!!!!!!!!!!!(如果表中的数据量很大，则用集合查询能够提高查询效率。)!!!!!!!!!!!!!!!!!!!!!!!!!!

课堂练习：

写出以下基于 stuinfo 表、stucourse 表和 stumarks 表的查询语句：

要求:

第(1)题用连接查询，第(2)~(7)题请用 连接查询、子查询 两种方法，第(8)~(10)题用子查询

---

(1) 查询 男生的平均成绩 和 女生的平均成绩：

连接查询：（连接stumarks表和stuinfo表）

select stusex as 性别,avg(stuscore) 

from stuinfo as a,stumarks as b 

where a.stuno=b.stuno group by stusex;

select stusex as 性别,avg(stuscore) 

from stuinfo as a join stumarks as b 

on a.stuno=b.stuno group by stusex;

---

用子查询查询男生平均成绩验证：

select avg(stuscore) 

from stumarks 

where stuno in (select stuno from stuinfo where stusex='男');

(2) 查询选修了“高数”课程且成绩在80~90分之间的学生的学号及成绩：

连接查询：（连接三张表）

select a.stuno as 学号,b.stuscore as 成绩 

from stuinfo as a ,stumarks as b,stucourse as c 

where a.stuno=b.stuno and b.cno=c.cno AND c.cname='高数' AND b.stuscore between 80 and 90;

select a.stuno as 学号,b.stuscore as 成绩 

from stuinfo as a join stumarks as b 

on a.stuno=b.stuno 

join stucourse as c 

on b.cno=c.cno

where (c.cname='高数') and (b.stuscore between 80 and 90);

---

子查询：

select stuno as 学号,stuscore as 成绩 

from stumarks 

where cno=(select cno from stucourse where cname='高数') and (stuscore between 80 and 90);

(3) 查询选修了“算法与数据结构”课程的学生的学号、姓名和性别：

连接查询：（连接三张表）

select a.stuno as 学号,a.stuname as 姓名,a.stusex as 性别 

from stuinfo as a ,stumarks as b,stucourse as c 

where a.stuno=b.stuno AND b.cno=c.cno AND c.cname='算法与数据结构';

select a.stuno as 学号,a.stuname as 姓名,a.stusex as 性别 

from stuinfo as a 

join stumarks as b

on a.stuno=b.stuno 

join stucourse as c 

on b.cno=c.cno

where c.cname='算法与数据结构';

---

子查询：

select stuno as 学号,stuname as 姓名,stusex as 性别 

from stuinfo where stuno in (select stuno from stumarks 

where cno=(select cno from stucourse where cname='算法与数据结构'));

(4) 查询至少选修一门课程的女学生的学号及姓名：

连接查询：

select distinct a.stuno as 学号,a.stuname as 姓名 
​
from stuinfo as a ,stumarks as b 
​
where a.stuno=b.stuno and a.stusex='女';

select distinct a.stuno as 学号,a.stuname as 姓名 

from stuinfo as a  join stumarks as b 

on a.stuno=b.stuno 

where a.stusex='女';

---

子查询：

select stuno as 学号,stuname as 姓名 

from stuinfo 

where stuno in (select distinct stuno from stumarks) and stusex='女';

(5) 查询 没有 选修课程号为“003”的课程 的学生的 学号 及 姓名：

连接查询：（外连接）

select stuinfo.stuno,stuinfo.stuname 

from stuinfo left join stumarks 

on stuinfo.stuno = stumarks.stuno and cno='003' 

where stumarks.stuno is null;

---

子查询：

select stuno as 学号,stuname as 姓名 

from stuinfo 

where stuno not in (select distinct stuno from stumarks where cno='003');

(6) 查询 没有学生选修的课程 的 课程号 及 课程名称：

连接查询：

select stucourse.cno as 课程号,cname as 课程名 

from stucourse left join stumarks on stucourse.cno=stumarks.cno

where stumarks.stuno is null;

---

子查询：

select cno as 课程号,cname as 课程名称 

from stucourse 

where cno not in (select cno from stumarks);

(7)查询选修了课程号为“004”的课程，且成绩为不及格的学生 的基本信息：

连接查询：

select a.*

from stuinfo as a ,stumarks as b

where a.stuno=b.stuno and b.cno='004' and b.stuscore<60;

---

子查询：

select * 

from stuinfo 

where stuno in (select stuno from stumarks where cno='004' and stuscore<60);

(8)查询 年龄 小于所有女生 的男生 的 学号、姓名及出生日期： 

select stuno as学号,stuname as 姓名,stubirthday as 出生日期 

from stuinfo 

where stubirthday > (select max(stubirthday) from stuinfo where stusex='女') and 

stusex='男';

(9)查询 成绩 比 每门课程的平均成绩 高 的选课记录：

select * 

from stumarks 

where stuscore > (select avg(stuscore) from stumarks group by stuno order by avg(stuscore) desc limit 1);

(10)查询成比该课程平均成绩高 （‘成绩’ 和‘平均成绩’ 属于同一门课程）的选课记录：

select * 

from stumarks 

where stuscore > (select avg(stuscore) from stumarks group by cno limit 1);

(11)查询选修了“颜老师”讲授的课程的学生的学号和姓名：

select stuno as 学号,stuname as 姓名

from stuinfo where stuno in (select stuno from stumarks 

where cno=(select cno from stucourse where cteacher='颜老师'));

同步实训：

(1)使用内连接（等值连接）：

① 查询 至少有一个员工的 部门的信息：

select distinct dept.* 

from emp,dept 

where dept.deptno=emp.deptno;

② 查询所有员工的 姓名、工资 和 所在部门的名称：

select emp.ename as 姓名, emp.sal as 工资,dept.dname as 部门 
​
from emp,dept 
​
where dept.deptno=emp.deptno;

③ 查询 所有部门 的 详细信息 和 部门人数：

select dept.*,count(*) 

from emp,dept 

where dept.deptno=emp.deptno group by deptno;

④查询 所有“CLERK”(办事员)的 姓名 和 所在部门的名称：

select emp.ename as 姓名,dept.dname as 部门 

from dept,emp 

where dept.deptno=emp.deptno and job='clerk';

⑤ 查询 在部门“SALES”(销售部)工作的 员工的姓名：

select emp.ename as 姓名 

from emp,dept 

where dept.deptno=emp.deptno and dname='sales';

⑥ 查询所有在“CHICAGO”工作的“MANAGER”(经理)和“SALESMAN”(销售员)的工号、姓名及工资：

select emp.empno as 工号,ename as 姓名,sal as 工资 

from emp,dept 

where dept.deptno=emp.deptno and loc='chicaco' and job='salesman';

(2)使用外连接或自连接：

① 查询没有员工的部门的信息(使用外连接)：

select dept.*

from dept left join emp on dept.deptno=emp.deptno

where emp.empno is null;

② 查询 入职日期 早于 其上级领导入职日期 的员工 的信息(使用自连接)：

select a.* 

from emp as a,emp as b

where a.mgr=b.empno and a.hiredate<b.hiredate;

③查询 所有员工的姓名 及其 上级领导的 姓名(使用自连接)：

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

from emp as a,emp as b

where a.mgr=b.empno;

(3)使用子查询： 

① 查询至少有一个员工的 部门的信息：

select * from dept 

where deptno in (select distinct deptno from emp);

②查询 工资比“smith”多 的员工的信息(假设“SMITH”没有同名)：

select * from emp 

where sal > (select sal from emp where ename='smith');

③ 查询在部门“sales”(销售部)工作的 员工的 姓名：

select ename as 姓名 from emp 

where deptno=(select deptno from dept where dname='sales');

④查询与“scott”从事相同工作的 员工的 信息(假设“scott”没有同名)：

select * from emp 

where job = (select job from emp where ename='scott');

⑤查询入职日期 早于 其 上级领导入职日期 的 员工的信息（自连接+子查询）：

select a.* 

from emp as a 

where a.hiredate<(select hiredate from emp as b where b.empno=a.mgr);

⑥ 查询 工资 高于 公司平均工资 的 员工的信息：

select * from emp 

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

⑦查询 工资高于 员工所在部门 平均工资 的 员工 的 信息 (相关子查询)：

select * from emp a 

where  sal > (select avg(sal) from emp as b  where deptno=a.deptno group by deptno);

(4)使用集合查询：

①查询 部门“10”和部门“30”的 所有职位，并 去掉 结果中的重复行：

select job from emp where deptno='10'

UNION

select job from emp where deptno='30';

②查询 “president”和“manager”所在部门的 编号，保留 结果中的重复行：

select deptno from emp where job='president'

UNION ALL

select deptno from emp where job='manager';

项目【九】查询优化：

<一>创建与使用视图：

1.视图的概念：

基本表：真正存放数据的表；

视图的数据来自对一个或多个基本表(或视图)进行查询的结果。

定义视图的主体部分就是一条查询语句，打开视图看到的实际上就是执行这条查询语句所得到的结果集。

(1)方便用户：

在日常应用中，可以将经常使用的查询语句定义为视图，特别是一些复杂的查询语句，从而避免重复书写语句。

(2)安全性：

通过视图，可以把 用户 和 基本表 隔离开，从而使特定用户只能 查询或修改 允许其可见的数据，其他数据则无法看到、无法获取。

(3)逻辑数据独立性：

视图可以屏蔽真实的数据表的结构变化所带来的影响。

例如，当其他应用程序查询数据时，若直接查询数据表，一旦表结构发生改变，则查询用的SQL语句 就要相应地改变，应用程序也必须随之更改。

然而，如果为应用程序提供视图，则修改表结构后只需修改视图对应的 SELECT语句即可，而无须更改应用程序。

2.创建视图（create view）：

语法格式：

create [or replace] view 视图名[(列1，列2，...)]
as
select 语句
[with check option];

说明：

1、or replace 是可选项，可以替代已有的同名视图；

2、(列1，列2，...) 用于声明在视图中使用的列名，相当于给 select 语句的各个数据项起别名；

3、with check option 子句用于 限制通过该视图修改的记录要符合 select语句中指定的选择条件。

注意：

成功创建视图只代表语法没有错误，并不代表其中的 select 语句的逻辑是对的，因此初学者在创建视图前最好先把 select 语句单独调试一下。

3.查看视图：

3.1-查看视图的结构：

describe/desc[ribe] 视图名;

3.2-查看视图的基本信息：

show table status [like'视图名'];

3.3-查看视图的创建信息：

show create view 视图名;

4.使用视图：

4.1-查询数据：

select语句

4.2-更新数据：

1、视图是虚拟表，本身没有数据，通过视图更新的是基本表的数据；

不是所有的视图都可以更新数据，一般只能对“行列子集视图”更新数据，即视图是从单个基本表导出的某些

行与列，并且保留了主键；

2、如果创建视图时使用了WITH CHECKOPTION子句，那么通过视图更新的数据必须满足定义视图时的 select

语句中 where 子句后面的筛选条件，否则会报错。

进一步阐述：如果插入记录，则可以通过刷新该视图看到插入的记录；

​                      如果修改记录，则修改完的结果也能通过该视图看到；

​                      如果删除记录，则只能删除视图里有显示的记录。

5.修改视图（alter view）：

语法格式：
alter view 视图名[(列1,列2,...)]
as
select 语句
[with check option];

==========“在前面所讲的create view语句后增加 or replace 也可以修改已有的视图========

6.删除视图（drop view）：

一次操作可以删除多个视图；

drop view [if exists] 视图名1[,视图名2]...;

EG1---任务实施：

[1]、创建视图v1:

用于查看stuinfo表中所有女生的基本信息，并且强制以后通过该视图插入的记录必须是女生的记录；

create or replace view v1 

as  

select * 

from stuinfo 

where stusex='女' 

with check option;

[2]、创建视图 v2:

创建视图v2，用于查询所有学生的 学号及平均成绩。

创建2后，查看其结构及创建信息。

分析：

在此任务要求查询的数据项中，有一个数据项用到了聚合函数，可以考虑在视图名之后给出新的列名。

create or replace view v2(stuno,avg_stuscore) 

as 

select stuno,avg(stuscore) 

from stumarks 

group by stuno;

[3]、创建视图v3：

用于查询所有学生的学号、姓名、课程名及成绩 （连接三张表）：

create or replace view v3

as 

select a.stuno,stuname,cname,stuscore

from stuinfo as a ,stumarks as b,stucourse as c 

where a.stuno=b.stuno AND b.cno=c.cno;

[4]、使用视图 v2 查询数据:

查询v2视图中学号为“04”的学生的平均成绩；

select avg_stuscore from v2 where stuno='04';

[5]、使用视图 v1 更新 stuinfo 表的数据：

分析:

前面创建的v1、v2、v3三个视图，只有 v1满足“行列子集视图”的条件，可用它更新对应的基本表的数据；

创建 v1 视图时增加了 with check option 子句，强制以后通过该视图插入的必须是女生的记录。

① 插入记录（只能插入性别为“女”的记录，如果性别为"男"则无法插入）：

insert into v1 (stuno,stuname,stusex) values('07','田七七','女');

② 修改记录（只能修改视图中可见的记录，视图中不可见的记录无法被修改）：

update v1 set stubirthday='2023-01-01' where stuno='33';

③ 删除记录（只能删除视图中可见的记录，视图中不可见的记录无法被删除）：

delete from v1 where stuno='02';

[6]、修改视图v3：

修改视图 v3，把列名 stuno、stuname、cname、stuscore 分别改为学号、姓名、课程名、成绩。

alter view v3(学号,姓名,课程名,成绩)

as

select a.stuno,stuname,cname,stuscore

from stuinfo as a join stumarks as b on a.stuno=b.stuno join stucourse as c on b.cno=c.cno;

[7]、删除视图v1和v2：

drop view v1,v2;

<二>创建与使用索引：

1.索引的概念 ：

索引是一种单独的、物理的、对数据表中一列或多列的值进行排序的存储结构，它是某张表中一列或若干列值的集合，以及相应地标识这些值所在数据页的逻辑指针清单。

如果把数据库看成一本书，数据库的索引就像书的目录，其作用就是提高 表中数据的 查询速度。

因为数据存放在数据表中，所以索引是创建在数据表上的。

表的存储由两部分组成：一部分是表的数据页面，另一部分是索引页面，索引就存储在索引页面上。

索引创建后，更新表中数据时由系统自动维护索引页的内容。

索引需要时间与空间的开销，因此要有选择地给某些列创建索引，过多的索引会降低表的更新速度，影响数据库的性能。

---

适合创建索引的列包括:

用于数据表之间相互连接的外键，经常出现在where 、group by 、order by 子句中的字段等。

在查询中很少被使用的字段及重复值很多的字段则不适合创建索引。

常见的几种索引：

(1)普通索引：最基本的索引类型，允许在创建索引的列中插入重复值或空值，只要不与约束冲突即可。

(2)唯一索引：要求索引列的值必须唯一，可以是空值，使用 unique关键字 可以把索引设为唯一索引(项目5的唯一约束其实就是通过唯一索引实现的)。

(3)主键索引：建立主键时自动创建该索引，索引列的值不能重复也不能为空值。

(4)单列索引：创建索引的列是单列。

(5)多列索引：创建索引的列是多列的组合，又被称为组合索引。

​ 只有在查询条件中使用了这些列中的第一列时，该索引才会被使用。

【据创建索引的列的值是否允许重复，可将索引分为普通索引和唯一索引；

根据索引创建在单列还是多列组合上，可将索引分为单列索引和多列索引，单列索引和多列索引可以是普 通索引或唯一索引。

一张表只能有一个主键索引，其他索引可以有多个。】

2.创建索引：

使用语句创建索引有三种方法：

2.1-创建表的时候创建索引：

语法格式：

create table 表名(

字段名1 数据类型1 [列级完整性约束1]

[,字段名2 数据类型2 [列级完整性约束2]].[,...]

[,表级完整性约束1].[,...]

,[unique] index|key[索引名].(字段名[(长度)].[asc|desc])

);

创建表时指定索引的子句为：

[unique] index|key[索引名]（字段名[(长度)] .[asc|desc]）;

说明：

[1]、unique是可选项，如果有该选项，则表示创建的是唯一索引。

[2]、在MySQL中，key和index的意思是一样的。

[3]、索引名如果没有指定，则默认使用字段名。

[4]、长度 ----> 列的 “前几个” 字符创建索引。

[5]、[asc|desc] 是可选项，asc表示升序，desc表示降序，默认是升序。

2.2-使用 create index 语句在已经存在的表上创建索引：

create [unique] index 索引名 on 表名 (字段名[(长度)].[asc|desc]);

注意：索引名不能省略；

2.3-使用 alter table 语句在已经存在的表上创建索引：

alter table 表名 add [unique] index 索引名(字段名[(长度)].[asc|desc]);

3.使用索引：

创建索引的目的是提高查询速度，若想查看索引是否被使用，则可以在查询语句前增加关键字explain;

语法格式：

explain select语句

执行上述代码后，会出现一张表格，可以通过possible_keys 和 key 的值判断是否使用了索引；

说明:

possible_keys：可能使用的索引，可以有一个或多个，如果没有，则值为NULL。

key：显示实际使用的索引，如果没有使用索引，则值为 NULL。

4.删除索引：

4.1-使用 alter table 语句：

alter table 表名 drop index 索引名;

4.2-使用 drop index 语句：

drop index 索引名 on 表名;

EG2---任务实施：

[1]、创建 tl (id,name,score)表，同时创建索引：

create table t1(id int,name varchar(20),score float,index(id));

说明:

使用desc语查看表结构时，key列可能出现的值有如下几种：PRI(主键)、MUL(普通索引)、UNI(唯一索引)。

[2]、使用 CREATE INDEX 语句创建索引：

使用 CREATE INDEX语句为 stuinfo 表的 stuname 列创建唯一索引，索引名为 uqidx；

create unique index uqidx on stuinfo(stuname);

显示（UNI）唯一索引；

[3]、使用 ALTER TABLE 语句创建索引：

使用 alter table 语句为 stuinfo表 的 stuno列 与 stubirhday列创建多列索引，索引名multidx；

stuno列按 升序 排序，stubirthday列按 降序 排序：

alter table stuinfo add index multidx(stuno,stubirthday desc);

注意：用desc语句查看多列索引，则该索引在key列不显示；

[4]、观察查询时是否使用了索引：观察在 査询 stumarks 表中 成绩小于 60分的记录时 是否使用了索引。

explain select * from stumarks where stuscore<60;

查询后可得：possible keys 和 key 的值都为 NULL：

表示在执行“explain select * from stumarks where stuscore<60;”这条查询语句时没有可用的索引，实际上，也未使用索引。

---

为 stumarks表的stuscore列创建一个普通索引，重新执行 EXPLAIN 语句査看索引的使用情况；

create index idx_score on stumarks(stuscore);


explain select * from stumarks where stuscore<60;

[5]、使用 ALTER TABLE 语句删除索引：

alter table stuinfo drop index uqidx;

[6]、使用 DROP INDEX语句删除索引：

drop index multidx on stuinfo;

【拓展知识】MySQL千万级大数据查询优化经验

当数据表有数百万条、数千万条记录后，为了提高查询效率，查询优化就显得非常重要。

(1)对查询进行优化时，应尽量避免全表扫描，先考虑在 WHERE 子句及 ORDER BY 子句涉及的列上创建索引。

(2)当使用索引字段作为查询条件时，如果该索引是复合索引，那么使用该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能地让字段顺序与索引顺序一致。

(3)索引并不是越多越好，索引虽然可以提高查询效率，但同时也降低了数据更新的效率，其原因是数据更新时系统需要维护索引，一张表的索引数量最好不超过6个。

(4)应尽量避免在WHERE子句中对字段进行空值(NULL)判断，否则将导致引擎放弃使用索引而进行全表扫描，可以设置默认值为0，从而确保表中的该列没有空值(NULL)。

(5)应尽量避免在WHERE子句中使用!=或一操作符，否则引擎将放弃使用索引而进行全表扫描。

(6)应尽量避免在WHERE子句中使用 OR 关键字连接筛选条件，否则将导致引擎放弃使用索引而进行全表扫描。

例如，“SELECT idFROM test WHERE num=10 ORnum=20”可以写为“SELECT id FROM test WHERE num=10 UNION ALL SELECT id FROM test WHERE num=20”。

(7)IN和NOT IN要慎用，否则将导致引擎放弃使用索引而进行全表扫描。

例如,“SELECT idFROMtWHERE num IN(1,2,3)”语句中有连续的教值，能用BETWEEN…AND就不要用IN，即可以写为“SELECT id FROM t WHERE num BETWEEN 1 AND 3”。

(8)应尽量避免在WHERE子句中对字段进行表达式操作或函数操作，否则将导致引擎放弃使用索引而进行全表扫描。

(9)不要在 WHERE 子句中的“_”左边进行函数运算、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。例如，“SELECT id FROM t WHERE num/2=100” 可以写为"SELECT id FROM t WHERE num=100*2"。

(10)很多时候，用 EXISTS 代替 IN 是一个好的选择。

(11)尽量使用数值型字段，若字段只包含数值信息，则尽量不要将其设置为字符型字段，这样会降低查询和连接的性能，并增加存储开销。其原因是引擎在处理查询和连接时会逐个比较.

同步实训：

(1)关于视图的操作：

① 创建视图 v1，用于査看 emp 表中所有在部门编号为“20”的部门工作的员工的信息,并且强制以后通过该视图插入的记录必须是在部门编号为“20”的部门工作的员工：

create or replace view v1 

as

select * from emp

where deptno='20'

with check option;	

② 创建视图 v2，用于査看每个部门的平均工资、最高工资和最低工资：

create or replace view v2(deptno,avg_sal,max_sal,min_sal)

as 

select deptno,avg(sal),max(sal),min(sal)

from emp 

group by deptno;

③ 创建视图 v3，用于査看所有经理(manager)的 工号、姓名及所在部门的部门名称：

create or replace view v3

as

select empno,ename,dname

from emp as a join dept as b on a.deptno=b.deptno

where job='manager';

④ 修改视图 v3，把列名 empno、ename、dname 分别改为工号、姓名、部门名称：

alter view v3(工号,姓名,部门名称)

as

select empno,ename,dname

from emp as a join dept as b on a.deptno=b.deptno

where job='manager';

⑤ 查询视图 v1 的所有数据：

select * from v1;

⑥ 查询视图 v2 中平均工资低于 2000 元的部门编号及平均工资：

select deptno,avg_sal

from v2

where avg_sal<2000;

⑦ 通过视图 v1 更新基本表 emp 的数据(试着插入一条记录，再对它进行修改，最后将其删除)：

insert into v1 values('1111','lalali','clerk',null,'1999-09-09','1890','700','20');

⑧ 删除视图 v1 和 v2：

drop view v1,v2;

(2)上机完成以下关于索引的操作。

① 创建一张数据表 test (id,name)，同时给 id 字段 指定 普通索引：

create table test (id int ,name varchar(20),index(id));

② 使用 create index 语句为 emp表 的 ename 列创建唯一索引，索引名为 uqidx：

create unique index uqidx on emp(ename);

③ 使用 alter table 语为 emp表 的 empno列 与 sal 列 建立 多列索引，索引名为 multidx，empno列按升序排序，sal列按降序排序：

alter table emp add index multidx (empno,sal desc);

④ 查看 査询emp表中工资(sal)达到 2000 元的员工 记录时 是否使用了索引：

explain select * from emp where sal>=2000;

⑤ 査看 査询 emp 表中工号为“7788”的员工记录时是否使用了索引：

explain select * from emp where empno='7788';

⑥ 使用 alter table 语句删除 emp表 上的唯一索引 uqidx：

alter table emp drop index uqidx;

⑦ 使用 drop index 语句删除 emp表 的 多列索引 multidx：

drop index multidx on emp;