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字段。
书写规范:
所有单词小写,多个单词用下划线_分割。最后可以用代码美化变成大写。
1.3 卸载naviact注意
卸载后还要检查一下注册表
Win +R 输入regedit,注册表:
计算机\HKEY_CURRENT_USER\SOFTWARE\PremiumSoft下把navicat相关的全部删掉。
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基础查询
- 查询所有列
select * from student;
- 查询指定列
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
子查询作为表
子查询形式为多行多列