MySQL数据库笔记

1.数据库

此处数据库student
MySQL8.0.18 navicat15
大致方向是建一个学生管理系统，先搞数据库+基础知识

1.1 测试数据库是否可以使用

前提是首先启动MySQL服务。（电脑右键服务里面启动）

mysql -u root -p

输入password：

如果有两个MySQL，则 用mysql -P 3307 -u root -p（注意端口号变化了，默认的应该是3306端口）
退出指令：quit或者exit

1.2 数据库一些小知识点

cloumn 也叫做field字段。
书写规范：
所有单词小写，多个单词用下划线_分割。最后可以用代码美化变成大写。

１.３ 卸载ｎａｖｉａｃｔ注意

卸载后还要检查一下注册表
Win +R 输入regedit，注册表：
计算机\HKEY_CURRENT_USER\SOFTWARE\PremiumSoft下把navicat相关的全部删掉。

ｎａｖｉｃａｔ安装教程

2. 基本操作（一）

2.1 创建用户

create user 'guest' @'%' identified by '123456';

#若修改%为IP地址，则改为仅IP地址可以访问

#赋权,使连接者可以访问自己的student库。

grant all privileges on student.* to "guest" @"%";

#刷新权限

flush privileges;

2.2 显示所有的数据库

#打开数据库
show databases;
#显示所有表格
use student;#student数据库
show tables;

#创建数据库
create database mydb1 default character set 'utf8mb4' collate 'utf8mmb4_general_ci'

#查看MySQL支持哪些编码集
show character set;

#查看数据库的创建结构
show create database mydb1;

#删除数据库
drop database mydb1;

#MySQL常用的字符集和排序规则
字符集：utf8mb4
排序规则：utf8mmb4_general_ci

部分系统使用的字符集是 utf8，但 utf8 最多只能存 3 字节长度的字符，不能存放 4 字节的生僻字或者表情符号，因此使用 utf8mb4

2.3 出现报错注意事项：

先看报错提示：
是否为语法错误：
单词是否拼写错误，写对的关键字会变色
标点符号是否用了中文的，尤其是单引号、逗号、分号。标点符号可以直接定义成英文的。
大多数语句：动词开头+名字。如：

drop table if exits student;

看日志：tomcat中查看

也可右键，将表格转存为SQL结构和数据，在编辑器中打开，不仅可以对照查看自己的错误还可以查看MySQL语句是怎么写的。

2.4 创建表格

给表改名：两种方式

rename to student to student_test;
alter table student rename to student_test;

#创建表，id，login_name,real_name,age,gender,create_date
#主键（primary key）的not null可不写，因为主键不能为空。
#comment ‘’是添加注释
#bit是整数，gender最好用Boolean值。

create table student(
	id int primary key auto_increment,
	login_name varchar(50)not null,
	real_name varchar(50)not null,
	age smallint null,
	gende bit default 0 comment '性别--0:女。1：男'，
	create_date timestamp not null default CURRENT_TIMESTAMP
);

#防止表格已经存在可以添加

drop table if exits student;

#描述student表格

describe student;
desc student;

2.5 字段的修改、添加、删除

#修改表添加字段

alter table student add update_date_datetime comment '数据的更新日期';

#更换字段名字，本质是覆盖。若不写comment，更新后就会没有了。

alter table student change update_date update_datee：

#修改表的字段
#格式：字段 类型–update_date datetime
#修改字段的顺序，实现上移或者下移的功能。

alter table student modify column update_date datetime comment '数据更新的日期'
after id;

#删除update_date这个字段。

alter table student drop update_date;

#student表添加salary字段，并且放在gender后面

alter table student add salary float default 0 comment'工资' 

after gender；


#一步完成示例：
alter table student modify cloumn add salary float default 0 comment '工资' after gender;

#展示表的创建语句，可以查看comment

show create table student;

2.6 查询student表里面的内容

#语法：select */字段名 from 表名 where（字句1）（字句2）。括号表示可以省略。
#select */field1,field2……from student where

select * from student;

2.7 唯一约束、索引

#添加唯一约束：约束 起的约束的名字（至约束哪个字段）

alter table student add unique uq_student_login_name(login_name)

#显示某张表的索引。index是索引，约束的本质就是索引，索引方法默认BTREE

show index from sudent;
show indenx from 'student';#单引号可以不加防止写错

2.8 创建表格，添加主键、约束两种方式

第一种：建表的时候就写好

create table student1(
	id int auto_increment,
	login_name varchar(50)not null,
	real_name varchar(50) not null,
	age smallint null,
	gender bit default 0 comment '性别--0:女。1：男',
	create_date timestamp not null default CURRENT_TIMESTAMP，
	primary key(id,real_name),
	unique uq_student1_login_name(login_name)
);

第二种：建表之后再用语句修改

#见表之后尝试删除主键，
alter table student1 drop primary key;#因为存在自增属性，不让删除主键
#查看表结构

desc student1;

#使用覆盖方式，语句中不再写自增。

alter table student1 modify id int;

#删除主键

alter table student1 drop primary key;

#添加主键

alter table student1 add primary key(id);

#为主键id设置自增，modify用覆盖的方式。

alter table student1 modify column id auto_increment;

设置两个主键，叫做组合主键。只有当两个主键对应的字段内容都相同时，才会提示报错。
主键唯一且不能为空
删除主键用drop,修改自增用modify-》覆盖的方式。

2.9 添加check约束

#添加check约束，constraint约束。MySQL8.0.16版本之后出现的check约束。
#格式：add constraint 起的约束的名字 check（要被约束的对象+约束条件）

alter table student add constraint chk_student_salary check(salary>=1000 and salary<=10000);

#删除check约束

alter table student drop CHECK chk_student_salary;
#alter table student drop costraint chk_student_salary;也有用这一句成功的

#查看student的建表语句，查看check约束语句

show create table student;

3. 基本操作（二）

添加一个学校的表格。

create table school(
	id int primary key auto_increment,
	name varchar(50) not null,
	address varchar(50),
	create_date timestamp not null default CURRENT_TIMESTAMP,
	unique uq_school_name(name)
);
select * from school,

3.1 添加数据

#插入数据，添加数据，insert into是一个词组

insert into school(name,address,create_date) values ('希望学院','海滨路100号','2021-03-17 16:04:00')
insert into school(name,address,create_date) values ('阳光学院','海滨路101号','2021-03-17 16:05:00')

#插入同名张三的数据，又因为login_name不能为空，且唯一unique
#像插入的数据为数字的，就可以不添加单引号。注意小细节。

insert into student(login_name,real_name,age,gender,salary,school_id)values ('san1','张三',20,1,5000,1);

#删除login_name为san1的张三这一条数据。未能实现此句。

delete student where login_name='san1';

3.2 外键foreign key

#因想要student表里的school_id不能随便填，就要添加外键约束，foreign key。references是引用

alter table student add constraint fk_student_scool_id foreign key(school_id) references school(id);

#删除外键

alter table student drop foreign key fk_student_school_id

#区别子表和父表
因student表中有school表的外键约束，所以student表是被约束的，所以是子表，school表是父表（类似父管教子）

#外键
on delete cascade
主表某数据删除时，与主表数据相关的从表的数据也一起删除
on update cascade
主表某数据修改时，与主表数据相关的从表的数据也一起修改
on delete set null
主表某数据删除时，与主表数据相关的从表的数据的外键列为NULL
no action 无任何改变
restrict 是不允许

3.3索引
给表添加索引，提高查询速度，通常给每一个或者几列添加。
#语法：create index 起个名字 on 表名（字段）

create index idx_student_real_name on student(real_name)

#查询带字句，就是带条件

select * from student where real_name='张三';

#描述一下查询到的。key显示的是索引

desc select * from student where real_name='张三';

#解释一下，同desc

explain select* from student where real_name='张三';

#删除索引

drop index idx_student_real_name on student;

#删除了索引之后，key的内容变成null.rows变换了，行数靠后了
#添加索引，提高查询速度原理：相当于添加了一个目录。

• 索引的优点：
  提升查询的速度

• 缺点：写入速度变慢。相对的，之前只需记录数据。现在要记录数据+写入索引（目录）

• 以下情况可以添加索引：
  a) 列中数据值分布很广。1-1千万
  b) 经常在where子句中出现
  c) 表经常被访问而且数据量很大

• 以下情况不要创建索引：
  a) 表很小
  b) 列不经常作为连接条件或出现在WHERE子句中
  c) 表经常更新

• 索引分类：
  主键索引、普通索引、聚合索引（添加多个列）、唯一和全文索引用的比较少。

3.4 insert into插入

插入数据 应与字段的数据类型相同
数据的大小应该在列的数据范围之内

三种写法

insert into student(login_name,real_name,age,gender,salary,school_id)values('san1','张三',20,1,3500,1);#1

insert into student values ('san1','张三',20,1,3500,1);#2

#新建source表，login_name,real_name,salary.一同运行两句话，实现将source中两条数据插入到student表中。

insert into student (login_name,real_name,salary)

select * from source where salary>300;#3
select * from student;#查看已实现#3

#4

insert into student (login_name,real_name,salary)
select 'xiaoqi','小齐',5000；#4‘1未能实现！！！
select 'xiaoqi','小齐'，5000from dual;##4’2#oracle中的写法。

3.5 update

#语法：update 表名 set 字段1=值，字段2=值…… where condition

update student set school_id=3,salary=90000 where id=11;

#平常修改的时候条件写id。因为 id是主键，非空且唯一
#练习：给所有女生的工资涨1000

update student set salary=salary+1000 where gender=0;

3.6 delete、truncate

#语法：delete from 表名 where condition

delete from student where id =6;

#delete 是删除一整行数据，update可以修改一个字段

update student set school_id=null where id=1;

#delete 写完之后一定要加上where再执行。用delete需小心！

#delete from source

#虽然删除了source表，但任留下种子信息（自增信息id），新建数据id是延续之前的id
#重置表，id将第一个开始

truncate table source;

写delete的时候一定要注意啊，一定要先写上where
而且要用where id=值，因为不要用中文，有重名的，但是id是主键，唯一且不可重复。
不使用where子句，将删除表中所有数据。

删除的时候可以先把delete改为select看看是否是要删除的内容，再改回delete就行删除。

3.7 select 、distinct、as

#语法：select */想要显示出来的字段 from表名 where condition1 and condition2 and……

select distinct real_name from student;
select * from student;

#distinct 去重。需用尽量少的字段，类似聚合主键

• 主键和唯一索引的字段不可以去重

• 普通索引（是为了提高查询速度）是可以去重的。

#练习：

select distinct age from student;

as

select login_name,real_name,salary*2 as salary from student as stu;

#salary列值计算起别名。不起的话就会叫做salary*2

select * from student 
where salary>=2000 and salary<=5000 and school_id=1 and gender=1;

select * from student#*表示的是想要显示出来的列
where 1=1;#true #将得到所有的数据

select * from student 
where salsry>=2000 and salary<=5000 and school_id=1 or gender=1;

select * from student 
where gender=0 or school_id=3;#select + where 条件：非常常用

3.8 between and

#找到salary：3000-7000的同学

select * from student where salary between 3000 and 7000;

between and 包含3000和7000两个端点值

3.9 in（set）

#查找学校id=1、2的同学

select * from student where school_id in(1,2);
select * from student where scool_id=1 or school_id=2;#效果同上

select * from student where school_id not in(2,3);
select * from student where school_id !=2 and school_id !=3;#同上

#当工作量大的时候尽量少用in。第二个主要原因是用了in之后索引就不在起作用了。基本上！=用的比较多。

#练习:子句嵌套查询

select  * from student 
where school_id in(select id from school where name ='阳光学院' or name='希望学院');

3.10 like

#%表示0个或者多个任意字符，%X%，%____X%
#查询姓名中包含‘明’的

select * from student where real_name like '%明%';

#查询姓‘明‘的人

select * from student where real_name like '明%';

#查询名字最后一个字是明的人

select * from student where real_name like '%明';

#找到姓名中不姓‘明’的人

selecr * from student 
where real_name not like '_明%';

#找到姓名中不姓‘明’的人同时这个字不在最后一个字

select * from student
where real_name like '_明_'or real_name like '__明_';

3.11 null 与not null

#查询salary字段为空的学生

select * from student;

#将一个学生的salary字段先设置update为空null

update student set salary=null where real_name='小青';

select * from student where salary is null;

select * from student where salsry is not null;

3.12 not与！

#整体取反

select * from student where not (salary>10000);

select * from student where !(salary>10000);

3.13 <>或者！=

select * from student where school_id<>3;

select * from student where schhol_id!=3;

3.14 order by 排序

#找到school_id不是3 的学生并且salary从高到低排序（降序）

select * form student where school_id !=3 
order by salary desc;

#asc是升序（不写的话asc 是默认）

select * from student where school_id!=3
order by school_id asc,salary desc;

#按照创建时间排序，最后创建的最新表示

select * from student 
where school_id!=3
order by create_date desc;

#小综合

select *,salary*2 as salary_sum
from student
where school_id!=3
order by salary_sum desc;

#小综合:salary 女生+100，男生+500，显示性别

select *,
case gender
when 0 then salary+100
when 1 then salsry+500
end as salary_calc,
case gender
when 0 then '女'
when 1 then '男'
end as gender_cale
from student
where school_id !=3
order by salary_calc desc;
#limit放在最后

3.15 exists

#exists判断的是有没有数据

select exists(select 1)

true is 1;false is 0
select exists(select * from student where id=32)
不存在id=32 的学生，所以输出结果为0

select * from student where exists(select * from student where id=32)

=where false输出的表的字段，没有数据

#练习：查找在学校里的学生
子查询

select * from student as stu
where exists(
	select * from school as scl
	where scl.id=stu.school_id
);
select * from student;

数据条数一致

4 基本操作（三）

聚合函数：函数（传参）
聚合函数是用来做纵向运算的函数

4.1 substr裁剪字符串函数

select substr('abcdef',1,4) as title;
select substr('abcdef',1,4)as title from dual;#oracle写法

4.2 count(列名)返回某一列、行的总数

select count(id) from student;

id（主键）有多少行，就表示有几行数据
count（id）比count * 要快一点

练习：
#工资高于3000且school_id是1或者2的学生的人数有多少？

#此句错误。注意and的用法

select count(id) from student 
where salary>3000 
and school_id=1 or school_id=2;

#1.先查询

select * from student
where salary>3000
and( school_id=1 or school_id=2);

#2.再计数

select count(id) from student
where salary>3000
and( school_id=1 or school_id=2);

分两步写是为了防止出错

#这样写是错误的。real_name给的数据只是第一行
具体原因不详

 select count(id), real_name from student 
 where salary>3000 
 and (school_id=1 or school_id=2);

4.3 sum求和

#查询school_id=1和2的，工资高于3000的学生的工资总和

select sum(salary),sum(school_id) from student 
where school_id in(1,2) and salary>3000;

#注意：

• sum仅对数值起作用

• 对于多列求和，逗号不能少

4.4 avg平均值

select avg(salary) from student 
where school_id in(1,2) and salary >3000;

4.5 max、min

#求出最高工资的人

select max(salary) from student
where school_id in(1,2) and salary >3000;

得出结果max salary=9600

#找到最大工资的人

select * from student 
where salary=9600;#1

#子句查询，此句还是考虑不周

select * from student
where salary=(
select max(salary) from student 
where (school_id=1 or school_id=2) and salary>3000
);#2

#子句查询,最终正确的语句

select * from student
where salary =(
select max(salary) from student 
where (school_id=1 or school_id=2 )and salary>3000)
and school_id in(1,2);

#查询school_id=1或者2的，工资高于3000里的最高工资的学生
#因为现在数据有一个3学校的9600max salary的人，所以括号外边要加上and school_id in(1,2)限定范围。

#最小工资

select * from student 
where salary = (
select min(salary) from student 
where (school_id=1 or school_id=2) and salary>3000
)
and school_id in(1,2);

4.6 group by 分组语句

#作用：按照哪些字段分组。
通常，弱国按照哪些字符分组，查询哪个字段
分组通常还与聚合函数配合着使用。
若多个字段分组，会先按照第一个分组，再按照第二个分组。

#查找student表有哪些个学校

select distinct school_id from student;#去重

select school_id from student 
group by school_id;

#查找student表有哪些个学校、每个学校有多少学生

select school_id,count(id) as stu_count from student
group by school_id;

#查找student表有哪些学校，以及每个学校有多少学生、每个学校学生的平均工资

select school_id,count(id) as stu_count,
avg(salary) as salary_avg
from student
group by school_id;

#查找student表有哪些学校，以及每个学校有多少学生、每个学校学生的平均工资，还有工资总和。

select school_id,count(id) as stu_count,
avg(salary) as salary_avg,
sum(salary) as salary_sum#salary 是null就相当于0，不算进去
from student 
group by school_id;

#按照school和gender分组，得出结果并不是按照school_id分组，结果不准确，不要查别的字段（gender）。

select school_id,gender,count(id) as stu_count,
avg(salary) as salary_avg,
sum(salary) as salary_sum
from student 
group by school_id,gender;

4.7having

#找到含5人以上的学校
思路：要将学校分组

select school_id,count(id)>5
from student
where count(id)>5
group by school_id;

#找到学校中工资>2000的人数大于4的学校

select school_id,count(id) as stu_count
from student
where salary>2000
group by school_id
having count(id)>4;

#找到学生平均工资高于3000的学校

select school_id,
avg(salary)
from student as avg_salary
group by school_id
having avg(salary)>3000;

having 作用于组，多用于聚合函数作用后且按分组找到多少人。
having配合着group by使用

4.8limit

实现分页

#select * from tablename [where 条件] limit n,m;
#第一个参数n：起始位置（默认从0开始)，当i=0时可省略i ）；
#第二个参数m：记录数；

#取了前三条数据

select * from student 
where salary>3000 and school_id in (1,2)
limit 3#limit 0,3

#分页要排序

select * from student
where salary>3000 and school_id in(1,2)
order by id#默认asc
limit 0,2#从第一条数据开始查，一页显示两条
#limit 2,2 #从第三条开始查，一页显示两条

#第1页：第0条数据
2：2
3：4
4：6
#pageSize*(pageNume-1)
#7/2 + 7%2总共的页数。

4.9DQL

DQL就是数据查询语言，数据库执行DQL语句不会对数据进行改变，而是让数据库发送结果集给客户端

SELECT selection_list /*要查询的列名称*/
  FROM table_list /*要查询的表名称*/
  WHERE condition /*行条件*/
  GROUP BY grouping_columns /*对结果分组*/
  HAVING condition /*分组后的行条件*/
  ORDER BY sorting_columns /*对结果排序*/
  LIMIT offset_start, row_count /*结果限定*/

执行顺序：
from->where->group by ->聚合函数->having->select->order by->limit

5 连表查询

5.1 子表查询

#查询student表里所有学生的信息及学校名称
1.

select * from student;

select * 
from school 
where school_id=(
select school_id from student
)

5.2连接查询

连接查询会产生笛卡尔积，假设集合A={a,b},集合B={0，1，2}，则两个集合的笛卡尔积为{（a,0）,(a,1),(a,2),(b,0),(b,1),(b,2)}。可以扩展到多个集合的情况。

通常要查询的多个表之间都存在关联关系，通过关联关系（主键、外键）去除笛卡尔积

5.3内连接

#查询student表里所有学生的信息及学校名称

select stu.*,scl.name as name_scl1
from student as stu
inner join school as scl
on scl.id=stu.school_id;

select *
from student as stu
inner join school as scl
on scl.id=stu.school_id;

select stu.school_id,scl.name#看分组的时候都有啥
from (student as stu
inner join school as scl
on scl.id=stu.school_id)
where stu.salary>3000
group by stu.school_id,scl.name#分组
order by stu.school_id#排序

#相当于内连接 inner join。通常不这么写

select * from student as stu,school as scl
where scl.id=stu.school_id

5.4 左外连接、右外链接

#准备工作：
将id=14、16的同学school_id置空

select * from student;
update student set school_id=null where id in(14,16);
#创建course表格
create table course(
	id int primary key auto_increment,
	name varchar(50) not null,
	create_date timestamp default CURRENT_TIMESTAMP
);

select * from course;
insert into course(name) values('大数据的开发与应用');

#course_id (1,3,5)行不通的
#专门建表记录course与student的关系
create table student_course(
	id int primary key auto_increment,
	student_id int not null,
	course_id int not null,
	create_date timestamp default CURRENT_TIMESTAMP
);
select * from student_course;

alter table student_course
add unique uq_student_student_id_course_id(student_id,course_id);

alter table student_course
add score float default 0 after course_id;

#笛卡尔积
#内连接：只有两张表相对应的情况下，才能选上数据
1.

select stu.*,scl_id as id_scl,scl.name as name_scl
from student as stu
inner join school as scl
on scl.id=stu.school_id;

select * from student;

1和2 相比较，少了两条数据。就是14、16的同学school_id空。

下面引用左外连接解决这个问题：
#左外连接
#左student 右school ，左连接是先查询出来左表，然后再查询右表。右边school.id 可以为空，空也会显示出来，为null

select stu.*,scl.id as id_scl,scl.name as name_scl
from student as stu
left outer join school as scl
on scl.id=stu.school_id

#右外连接
#左student 右school ，左边school_id 可以为空，空也会显示出来

select stu.*,scl_id as id_scl,scl.name as name_scl
from student as stu
right join school as scl
on scl.id=stu.school.id;

#全连接MySQL不支持，Oracle支持。类似笛卡尔积

#练习

1.查询所有学生的信息以及他们所选的课程和得分

select stu.real_name,cre.id as course_id,cre.name as name_cre,sc.score
from student as stu
left join student_course as sc
on sc.student_id=stu.id
left join course as cre
on cre.id=stu.course_id;
#select是后面执行的是三张表连接起来后查询的,只查询想要看到的

2.找到没在学校的学生

select  stu.*,scl.id as id_scl,scl.name as name_scl
from student as stu
left join school as scl#左外连接
on scl.id=stu.school_id
where scl.id is null;

3.找到没有学生的学校

方法一：右外连接

select scl.id as id_scl,scl.name as name_scl,
from stuednt as stu
right join school as scl
on scl.id=stu.school_id
where stu.school_id is null;

方法二：子查询
注意使用了distinct（去重）

select * from school
where id not in (select dintinct school_id from student);

4.找到高于本学校平均工资的所有学生。（有三个学校）

（1）.先找到每个学校的平均工资

select school_id,avg(salary) as avg_sal
from student
group by school_id;

方法一：左连接表，将查询的平均工资表作为新的表，school_id作为关联的条件

select * 
from student as stu
left jion (
select school_id,avg(salary) as avg_sal
from student
group by school_id
) as scl_avg_sal
on scl_avg_sal.school_id=stu.school_id
where stu.salary > scl_avg_sal.avg_sal

方法二：子表查询
#主的条件放在子的里面用
#执行顺序需注意

select * from student as main#主查询
where salary>(
	select acg(salary) from student as sub#子查询
	where sub.school_id=main.school_id
)

6.基本操作（四）

6.1事务：

原子性、一致性、隔离性、持久性

#将某些数据竖着接起来，长度和数据必须是一样的

select id,real_name from student
union all
select id,name from school

select datediff('2021-3-23',now());#时间

select CONCAT(now(),'你好，','倩倩')#字符串拼接

select replace('我爱中国|中国加油','中国|','你，')

6.2函数/存储过程

6.3锁lock

大大降低并发的事件，当然也会慢很多。

6.4游标cursor

6.5触发器

7.练习题(集中)

7.1基础查询

1. 查询所有列

select * from student;

2. 查询指定列

select id,name ,age from student;

7.2条件查询

1.查询性别为女，并且年龄小于50的记录

select * from student where gender=0 and age<50;

2.查询学号为S_1001,或者姓名为lisi的记录

select *from student where sid='S_1001' or sname='lisi';

3.查询学号为S_1001，S_1002，S_1003的记录

select * from student where sid in('S_1001','S_1002','S_1003',);

4.查询学号不是S_1001，S_1002，S_1003的记录

select * from student where sid not in('S_1001','S_1002','S_1003',);

5.查询年龄为null的记录

select * from student where ageid null;

6.查询年龄在20到40之间的学生记录

select * from student where age>=20 and age <=40;
select * from student where age between 20 and 40;

7.查询性别非男的学生记录

select * from student where gender !='male';
select * from student where gender<>'male';
select *from student where not gender='male';

8.查询姓名不为null的学生记录

select * from student where not sname is null;
select * from student where sname is not null;

7.3 模糊查询

1.查询姓名由5个字母构成的学生记录

select * from student where sanme like '_____';

2.查询姓名由5个字母构成，并且第5个字母为“i”的学生记录

select * from student where sname like '____i';

3.查询姓名以“z”开头的学生记录

select * from student where sanme like 'z%';

4.查询姓名中第2个字母为“i”的学生记录

select * from student where sname like '_i%';

5.查询姓名中包含“a”字母的学生记录

select * from student where sname like '%a%';

7.4 字段控制查询

1.去除重复记录

select distinct salary from emp;

2.查看雇员的月薪和佣金之和

select *,salary+commission from emp;

注意点：
commission列有很多记录的值为NULL，因为任何东西与NULL相加结果还是NULL，所以结算结果可能会出现NULL。下面使用了把NULL转换成数值0的函数IFNULL：

select *,salary+IFNULL(commission,0) from emp;

3.给列添加别名

select * ,salary+IFNULL(commission,0) as total from emp;

另
给列起别名时，是可以省略AS关键字的：

select *,sal+IFNULL(commission,0) total from emp;

7.5排序

1.查询所有学生记录，按年龄升序排序

select  * from student order by age asc;
select * from student order by age;#(默认)

2.查询所有学生记录，按年龄降序排序

select * from student order by age desc;

3.查询所有雇员，按月薪降序排序，如果月薪相同时，按编号升序排序

select * from emp order by salary desc,empno asc;

7.6聚合函数

7.6.1 COUNT()：

统计指定列不为NULL的记录行数；
说明：当需要纵向统计时可以使用COUNT()。
1.查询emp表中记录数

select count(*) as cnt from emp;

2.查询emp表中有佣金的人数

select count(commission) cnt fromemp;

注意点
因为count()函数中给出的是commission列，那么只统计comm列非NULL的行数
3.查询emp表中月薪大于2500的人数

select count(*) from emp where salary>2500;

4.统计月薪与佣金之和大于2500元的人数

select count(*) as cnt from emp where sal+IFNULL(commission,0)>2500;

5.查询有佣金的人数，以及有领导的人数

select count(commission),count(mgr) from emp;

7.6.2 SUM():

需要纵向求和时使用sum()函数
1.查询所有雇员月薪和

select sum(salary) form emp;

2.查询所有雇员月薪和，以及所有雇员佣金和

select sum(salary),sum(sommission) from emp;

3.查询所有雇员月薪+佣金和

select sum(sum+IFNULL(commission,0) )from emp;

7.6.3AVG():

平均数:
统计所有员工平均工资

select sum(salary)/count(salary) from emp;
select avg(salary) from emp;

7.6.4 MAX()最大值、MIN()最小值

查询最高工资和最低工资

select max(salary),min(salary) from emp;

7.7 分组查询

1.查询每个部门的部门编号和每个部门的工资和

select deptno,sum(salary) from emp group by deptno;

2.查询每个部门的部门编号以及每个部门的人数

select deptno,count(*) from emp group by deptno;

3.查询每个部门的部门编号以及每个部门工资大于1500的人数

select deptno,count(*) from emp where salary>1500 group by deptno;

4.HAVING子句
案例:
查询工资总和大于9000的部门编号以及工资和

select deptno,sum(salary) from emp group by deptno having sum(sal)>9000;

说明
WHERE是对分组前记录的条件，如果某行记录没有满足WHERE子句的条件，那么这行记录不会参加分组；而HAVING是对分组后数据的约束

7.8 limit

定义:LIMIT用来限定查询结果的起始行，以及总行数，通俗点说就是分页
1.查询5行记录，起始行从0开始

select * from emp limit 0,5;

起始行从0开始，即第一行开始！
2.查询10行记录，起始行从3开始

select * from emp limit 3,10;

补充说明
如果一页记录为10条，希望查看第3页记录应该怎么查呢？
第一页记录起始行为0，一共查询10行；
第二页记录起始行为10，一共查询10行；
第三页记录起始行为20，一共查询10行；

7.9 多表查询

7.9.1合并结果集：union、union all

作用：就是把连个select 语句的查询结果合并到一起
两种方式：
1.union：去除重复记录：

select * from t1 union select * from t2;

2.union all:不去除重复记录

select * from t1 union all select * from t2;

注意：
要求：被合并的两个结果、列数、列数类型必须相同

7.9.2连接查询

使用主外键关系做为条件来去除无用信息

select * from emp,dept 
where emp.deptno=dept.deptno;

在多表查询中，在使用列时必须指定列所从属的表，例如emp.deptno表示emp表的deptno列。

上面查询结果会把两张表的所有列都查询出来，也许你不需要那么多列，这时就可以指定要查询的列了。

select emp.ename,emp.salary,emp.commission,dept.dname 
from emp,dept where emp.deptno=dept.deptno;

还可以为表指定别名，然后在引用列时使用别名即可

selecr e.ename,e.sal,e.comm,d.name 
from emp as e,dept as d
where e.deptno=d.deptno;#[其中AS是可以省略的]

1.内连接

select * from emp e 
inner join dept d #inner可以省略，MySQL默认的连接方式就是内连接
on e.deptno=d.deptno;

2.外连接：因为查询出来的结果存在不满足条件的可能

2.1左外连接

select * from emp e 
left outer join dept a
on e.deptno=d.deptno;

说明：
左连接是先查询出左表（即以左表为主），然后查询右表，右表中满足条件的显示出来，不满足条件的显示NULL。

2.2 右外连接

说明：
右连接就是先把右表中所有记录都查询出来，然后左表满足条件的显示，不满足显示NULL。例如在dept表中的40部门并不存在员工，但在右连接中，如果dept表为右表，那么还是会查出40部门，但相应的员工信息为NULL。

select * from emp e 
right outer join dept d
on e.deptno=d.deptno;

2.3连接查询心得

连接不限与两张表，连接查询也可以是三张、四张，甚至N张表的连接查询。通常连接查询不可能需要整个笛卡尔积，而只是需要其中一部分，那么这时就需要使用条件来去除不需要的记录。这个条件大多数情况下都是使用主外键关系去除。
两张表的连接查询一定有一个主外键关系，三张表的连接查询就一定有两个主外键关系，

7.9.3自然查询

说明：
两张连接的表中名称和类型完成一致的列作为条件，例如emp和dept表都存在deptno列，并且类型一致，所以会被自然连接找到！
当然自然连接还有其他的查找条件的方式，但其他方式都可能存在问题！

实现

SELECT * FROM emp NATURAL JOIN dept;[内连接]
		SELECT * FROM emp NATURAL LEFT JOIN dept;[左外连接]
		SELECT * FROM emp NATURAL RIGHT JOIN dept;[右外连接]

7.9.4子查询

1.子查询就是嵌套查询，即SELECT中包含SELECT，如果一条语句中存在两个，或两个以上SELECT，那么就是子查询语句了
2.子查询出现的位置：

• where后，作为条件的一部分
  此时还可以使用如下关键字：all,any
• from后，作为被查询的一条表

3.子查询的结果集形式：

单行单列（用于条件）
单行多列（用于条件）
多行单列（用于条件）
多行多列（用于表）

4.案例：

• 工资高于甘宁的员工：

 select sal from emp where ename='甘宁';#1
 select * from emp where sal>(#1)
 sselet *frim emp where sal>(select sal from emp where ename='甘宁';)
 子查询作为条件，子查询形式为单行单列

• 工资高于30部门所有人的员工信息

select sal from emp where deptno=30:#1
select * from emp where sal>all (#1)
select * from emp where sal > all(select sal from emp where deptno=30)
子查询作为条件，子查询形式为多行单列（可以使用all或者an关键字） 

• 查询工作和工资与殷天正完全相同的员工信息

 select job,sal from emp where ename='殷天正';#1
 select * from emp where (job,sal) in (#1)
 select * from  emp where (job,sal) in(select job,sal from emp where ename='殷天正');
 子查询作为条件，子查询形式为单行多列

• 查询员工编号为1006的员工名称、员工工资、部门名称、部门地址
  不需要外连接（外连接的特性：某一行或某些行记录上会出现一半有值，一半为NULL值）

去除多表，去除部门表，只查员工表
select ename,sal form emp e where empno=1006
与dept 做内连接查询，添加主外键条件去除无用笛卡尔积
select e.ename,e.sal,d.name,d.loc from emp e,dept d 
where e.deptno=d.depttno and empno=1006
查询dept表中dname和loc两列，因为deptno会被作为条件，用来去除无用笛卡尔积，所以需要查询它。
select  dname,loc,deptno from dept;
替换第二步中的dept
select e.ename,e.sal,d.dname,d.loc
from emp e,(select dname,loc,deptno from dept) d 
where e.deptno=d.deptno and e.empno=1006
子查询作为表
子查询形式为多行多列

7.9.5 MySQL数据库的备份与还原