MySQL基础
创建数据库：
create database 数据库名 character set ‘utf8' collate 'utf8_general_ci'
create 创建
database 数据库
character set ‘utf-8’ 设置字符集为utf-8
collate ‘utf8_general_ci’ 字符集规则
创建表：
create table 表名(属性1 数据类型(长度) 约束,……,属性n 数据类型(长度) 约束) engine=innodb default charset=utf8;
engine=innodb 指定存储引擎为innodb
default charset=utf8 默认字符集 为utf8
例：create table salgrade(
grade int(2) primary key,
local float(7,2),
sisal float(7,2)
) engine=innodb default charset=utf8;
一、DDL语句 -create alter dropcreate语句
1.CREATE
1）创建database #数据库
语法：
create database 数据库名 character set 'utf8' collate 'utf8_general_ci';
[character set 'utf8' collate 'utf8_general_ci'] #设置字符集及字符集校对规则
【例】创建database：exercise
create database exercise character set 'utf8' collate 'utf8_general_ci';
【练习】创建company数据库
create database company character set 'utf8' collate 'utf8_general_ci';
2）创建table
语法：
create table 表名(属性1 数据类型(长度) 约束,属性2 数据类型(长度) 约束,...,属性n 数据类型(长度) 约束) engine=innodb default charset=utf8;
engine=innodb default charset=utf8; #指定存储引擎innodb，默认字符集为utf8。
mysql中常见数据类型：数值型、日期型、字符型
数值型：整型、浮点型
类型 范围
tinyint 1字节,-128~127
smallint 2字节,-32 768~32 767
mediumint 3字节,-8 388 608~8 388 607
int 4字节,-2 147 483 648~2 147 483 647
bigint 8字节,-9 233 372 036 854 775 808~9 233 372 036 854 775 807
float(p,s) 4字节,p表示有效位数，s表示精度 600.011 float(6,3)
double(p,s) 8字节,p表示有效位数，s表示精度
decimal(p,s) 存储为字符串的浮点数
日期型：
类型 范围
date 3字节，日期，yyyy-mm-dd 2018-08-06
time 3字节，日期，hh-mm-ss 11:26:20
datetime 8字节，日期，yyyy-mm-dd hh-mm-ss 2018-08-06 11:26:20
字符型：
char(n) 固定长度字符串，最大为255个字符 效率更高
varchar(n) 可变长度字符串，最大为65535个字符 节约空间
sname char(10),sname的值为smith，此时在内存中同样占用10个字符的空间
sname varchar(10),sname的值为smith，此时在内存中占用5个字符的空间
约束：主键约束，外键约束，非空约束，唯一约束，检查约束，默认值
#主键约束：primary key 表中的每一行都应该具有可以唯一标识自己的列而这个承担标识作用的列称为主键
#外键约束：foreign key 外键用于关联两个表
注意：所有表的存储引擎必须为innodb类型
被引用表中，必须为被引用列创建索引
引用表中，必须为引用的列创建索引
#非空约束：not null
#唯一约束：unique
#检查约束：check(条件) （mysql中不支持检查约束，可以使用，但是没有任何效果） check(sex in('男','女')) enum枚举
#默认值：default
注意：同一列上有多个约束时，约束之间使用空格隔开
同一列上有默认值和其他约束时，默认值写在其他约束前面
创建emp,dept,salgrade表
#创建salgrade表
create table salgrade(
grade int(2) primary key, #主键约束
losal float(7,2),
hisal float(7,2)
) engine=innodb default charset=utf8;
#创建dept表
create table dept(
deptno int(2) primary key, #主键约束
dname varchar(20) not null, #非空约束
loc varchar(20) not null,
index dept_index(deptno) #表示在deptno列上创建索引dept_index,索引名称为dept_index可自定义
) engine=innodb default charset=utf8;
#创建emp表
create table emp
(empno int(4) primary key, #主键约束
ename varchar(20) not null, #非空约束
job varchar(10) not null,
mgr int(4),
hiredate date not null,
sal float(7,2) not null,
comm float(7,2),
deptno int(2),
index emp_index(deptno), #在emp表的deptno列创建索引
foreign key(deptno) references dept(deptno) on delete cascade on update cascade #在deptno列上创建外键约束，引用dept表的deptno列，具有级联删除和修改
)engine=innodb default charset=utf8;
[练习]
1.创建数据库:test4
#创建数据库test4
create database test4 character set 'utf8' collate 'utf8_general_ci';
2.创建表学生表(学号,姓名,年龄,性别(gender),出生年月,学院编号,年级)
学生表：student
学号：sno 主键
姓名：sname 非空
年龄：age
性别：gender 取值(M/F)默认值'M'
出生年月:birthday
学院编号：xno 外键，引用院系表中的学院编号
年级：grade 非空
#创建student表
create table student(
sno int(4) primary key,
sname varchar(20) not null,
age int(2),
gender char(2) default 'M' check(gender in('F','M')),
birthday date,
xno int(4),
index student_index(xno),
foreign key(xno) references collage(xno) on delete cascade on update cascade
) engine=innodb default charset=utf8;
3.创建表：院系(学院编号,系名,地址)
院系：collage
学院编号：xno 主键
系名：cname 非空
地址：loc
#创建院系表
create table collage(
xno int(4) primary key,
cname varchar(20) not null,
loc varchar(20),
index collage_index(xno)
) engine=innodb default charset=utf8;
2).alter：用于在已有的表中添加、修改或删除列
添加：alter table 表名 add 列名 数据类型;
【例】在student表中增加phone列，数据类型为bigint,非空约束
alter table student add phone bigint(11) not null;
【练习】在collage表中增加 teacher列，数据类型varchar(20)
修改：alter table 表名 modify 列名 数据类型;
删除：alter table 表名 drop 列名;
3).drop：删除database和table
删除库：drop database 数据库名;
删除表：drop table 表名;
alter 用于在已有的表中添加项
alter table 表名 add 列名 数据类型
例:在sudent表中增加phone列，数据类型为bigint，非空约束
alter table student add phone bigint(11) not null
修改数据类型：modify
alter table student modify phone bigint(11) not null
drop 删除数据库和表单
删除表单中的列：drop
alter table sudent drop phone
修改属性名：change
alter table grade change sname newname varchar(20);
#sname原名 new name新名 varchar(20)参数不能少
修改表名：rename
alter table student rename newstudent
#student 原表名 newstudent 新表名
insert 插入数据
insert into 表名(属性1,….属性n) values(数值1,….数值n);
insert into student(son,sname,age,gender) values(1001,’xxx’,20,’1998-09-01’)
#数值型数值不用引号，字符型需用引号
多行插入
insert into student(son,sname,age,gender) values(1001,’xxx’,20,’1998-09-01’),(1002,’xxx’,21,’1998-09-02’)
update更新数据
update 表名 set 列名=value [where 条件] 不加where就会更新整列 加就限制某行
update collage set loc = ‘成都’ where cname = ‘英语学院’;
delete删除数据
delete from 表名 [where 条件]
delete from collage where xno =1030;
删除所有行
delete from collage; 删除表内容，表格式保留
select语句（查询功能）
基本语法： select 属性1,属性2,.. from 表名1,表名2;
1.查询所有列
语法： select * from 表名;
【例】查询dept 表中的所有数据
select * from dept;
【练习】查询emp表中的所有数据
select * from emp;
2.查询指定列
语法： select 列1,列2,... from 表名;
【例】查询emp表中员工的编号及员工的姓名
select empno,ename from emp;
【练习】查询员工表中员工的编号，姓名，职位，入职日期
select empno,ename,job,hiredate from emp;
3.算术运算 +、-、*、/
对数值型和日期型数据可用算数运算创建表达式
【例】查询员工表中员工的姓名，工资 ，每个员工工资加300
select ename,sal,sal+300 from emp;
【练习】查询员工表中员工的姓名，工资，每个员工的年收入（12个月工资）
select ename,sal,sal*12 from emp;
【练习】查询员工表中员工的姓名，工资 ，日薪（每个月30天计算）
select ename,sal,sal/30 from emp;
【例】查询员工表员员工的姓名，入职日期，及每个员工入职前三天的日期
select ename,hiredate,hiredate-3 from emp;
4.空值
注意：空值是指不可用，不知道，不适用的值
空值不等于零或空格
包括空值的算法表达式等于空
【例】查询员工的姓名，工资，奖金，月收入（工资+奖金）
select ename,sal,comm,sal+comm from emp;
5.列的别名 别名用双引号
【例】查询员工的姓名，工资，奖金，月收入（工资+奖金）别名为月收入
a.使用as
select ename,sal,comm,sal+comm as "月收入" from emp;
b.不使用as,直接跟别名
select ename,sal,comm,sal+comm "月收入" from emp;
【练习】查询员工表中员工的姓名，工资 ，日薪（每个月30天计算）
select ename,sal,sal/30 "日薪" from emp;
【练习】查询员工表中员工的姓名，工资，每个员工的年收入（12个月工资）
select ename,sal,sal*12 as "年收入" from emp;
6.重记录
select deptno from emp; 14行结果
去重记录使用 distinct
注：DISTINCT操作会引起排序，通过排序去掉重复记录！
【例】查询员工表员有哪些部门号，去掉重复数据
select distinct deptno from emp;
【练习】查询员工表员有哪些mgr（查询结果去掉重记录）
select distinct mgr from emp;
【练习】
1.从dept表中选择所有数据
select * from dept;
2.从emp表中查询员工编号，姓名，职位，部门号
select empno,ename,job,deptno from emp;
3.从emp表中查询员工姓名，工资，奖金，以及年收入（工资+奖金的总和*12），给年收入列取别名”年收入”
select ename,sal,comm,(sal+comm)*12 "年收入" from amp;
限定和排序
where子句：限定查询返回的记录
注意：在Where子句中字符串和日期要用单引号括起来
对字符串不区分大小写，可以使用BINARY关键字设定字符串区分大小写
日期值是格式敏感的
在Where子句后面不能使用列的别名限定查询
where 子句写在 from 后面
【例】查询10号部门员工的编号，姓名，职位，工资，入职日期，部门号
select empno,ename,job,sal,hiredate,deptno from emp
where deptno = 10;
【练习】查询职位为SALESMAN的员工的编号，姓名，职位
select empno,ename,job from emp
where job = 'SALESMAN';
【练习】查询员工入职日期为1981年2月22日入职的员工的姓名，入职日期
select ename,hiredate from emp
where hiredate = '1981-02-22';
【例】查询员工表员职位为manager的员工的姓名，职位
select ename,job from emp
where job = 'manager';
使用binary区分大小写
select ename,job from emp
where job = binary 'MANAGER';
1.普通比较运算符 =、>=、<= 、< 、>、 <> /!=
【例】查询员工表中工资大于3000的员工的姓名，工资
select ename,sal from emp
where sal > 3000.00;
【练习】查询员工表中工资小于等于2000的员工的姓名，工资，奖金
select ename,sal,comm from emp
where sal <=2000.00;
【练习】查询员工表中职位不为SALESMAN的员工的姓名，职位，工资，部门号
select ename,job,sal,deptno from emp
where job <> 'SALESMAN';
select ename,job,sal,deptno from emp
where job != 'SALESMAN';
【练习】查询员工表中年收入（12个月工资）大于50000的员工的姓名，职位，工资，年收入
select ename,job,sal,sal*12 "年收入" from emp
where sal*12 > 50000.00;
between…and...在两个值之间查询（包含输入的两个值）
例：查询员工工资在2000.00-3000.00之间的员工姓名、职位、工资
select ename,job,sal from emp
where sal between 2000.00 and 3000.00;
in(list) 匹配列出的值，list为列出的值
例：查询员工表中部门号位10和20部门的员工姓名、职位、工资、部门
select ename,job,sal,deptno from emp
where deptno in(10,20);
like 使用like运算符执行通配查询，查询条件包涵文字字符或数字
(%)可表示零或多个任意字符
(_)可表示一个字符
例：查询员工表中值为的第四个字符为E的员工姓名、职位、工资、部门号
select ename,job,sal,deptno from empwhere job like '___E%';
＃查询字符后面要跟％号，不然只能识别只有四位最后字符为E的
is null 是空值
例：查询员工表中mgr为空值的员工姓名、职位、mgr
select ename,job,mgr from amp
where mgr is null
逻辑运算符
and或者&& 如果组合的条件都为真则返回真值，满足所有条件返回结果
例：查询员工表中职位为salesman且部门号位30的员工姓名、职位、工资、部门号
select ename,job,sal，deptno from emp
where job = 'salesman'
and deptno = 30;
or或者|| 如果组合的条件之一是真值，返回真值 满足条件之一即可
返回结果
例：查询员工表中职位为salesman或者clerk的员工姓名、职位、工资
select ename,job,sal from emp
where job = 'salesman'
or job = 'clerk';
not或者! 如果条件返回为假则返回真值
例：select ename,sal,domo from emp
where not(comm is null);
＃三个命令有顺序优先级，同时使用需比较运算( =、>=、<= 、< 、>、 <> /!=)、not、and、or这个顺序
＃括号能提高优先级
order by 子句
使用order by子句将记录排序
asc:升序缺省 可以不写
desc:降序
注意：order by 子句在select语句最后
例：查询员工表中员工的姓名、工资 查询结果按工资的降序排列
select ename,sal,job from emp
where sal > 2500.00
order by sal desc;
例：查询员工表中员工的姓名、工资、部门号，查询结果按部门号降序，工资按升序排列
select ename,sal,deptno from emp
order by deptno desc,sal asc;
select ename,sal,deptno from emp
where sal > 1500.00
order by deptno desc,sal asc;
单行函数
1.字符函数
1) upper('x') 将x中的字符返回为大写
lower('x') 将x中的字符返回为小写
【例】将'abcdefg'返回大写
select upper('abcdefg') from dual;
【例】将员工表中员工的姓名返回小写
select lower(ename) from emp;
【练习】将员工表中的姓名，职位返回小写
【练习】将部门表中的部门名称，部门地址返回小写
2)length('x') 返回x的字符数，返回x的长度
【例】返回'abcdefg'长度
select length('abcdefg') from dual;
【练习】返回员工表中员工姓名的长度
select ename,length(ename) from emp;
3)ifnull(x,value) 如果x为空，返回value，否则返回x； value为自定义的值
【例】查询员工表中员工的姓名，职位，工资，奖金，奖金为空时返回0
select ename,job,sal,comm,ifnull(comm,0.00) from emp;
【练习】查询员工表员工的姓名，职位，工资，奖金，月收入（工资+奖金）
select ename,job,sal,comm,sal+ifnull(comm,0.00) "月收入" from emp;
select ename,job,sal,comm,ifnull(comm+sal,sal) "月收入" from emp;
【练习】查询员工表员工的姓名，职位，工资，奖金，月收入（工资+奖金），年收入（12个月的月收入）
select ename,job,sal,comm,sal+ifnull(comm,0.00) "月收入",(sal+ifnull(comm,0.00))*12 "年收入" from emp;
select ename,job,sal,comm,sal+ifnull(comm,0.00) "月收入",ifnull((sal+comm),sal)*12 "年收入" from emp;
4)replace(x,char1,char2) 将x中的char1替换为char2
【例】将员工表中员工姓名中的字符A替换为C
select ename,replace(ename,'A','C') from emp;
【练习】将员工表中职位中的字符E替换为D
select job,replace(job,'E','D') from emp;
5)substring(x,start,[length]) 从x的start位置开始返回长度为length的子字符串
【例】从'ABCDEFGHIJK'的第5个字符开始返回长度为3的子字符串
select substring('ABCDEFGHIJK',5,3) from dual;
【练习】查询员工表中员工的姓名，职位，从员工姓名的第二个字符开始返回长度为3的字符串
select ename,substring(ename,2,3),job from emp;
【练习】查询员工表中员工的姓名，职位，从职位的第三个字符开始返回长度为2的字符串
select ename,job,substring(job,3,2) from emp;
【练习】从'ABCDEFGHIJK'中返回'GHI'
select substring('ABCDEFGHIJK',7,3) from dual;
注意：当start为正数时，从X的左边开始数第start字符位置返回长度为length的子字符串，返回的字符串均为从左往右数
当start为负数时，从X的右边开始数第start字符位置返回长度为length的子字符串，返回的字符串均为从左往右数
【练习】从'ABCDEFGHIJK'中返回'GHI'
select substring('ABCDEFGHIJK',7,3) from dual;
select substring('ABCDEFGHIJK',-5,3) from dual;
6)lpad(x,length,char) 从x的左边使用char将x补齐到长度为length的字符串 char 与 length 为自定义
rpad(x,length,char) 从x的右边使用char将x补齐到长度为length的字符串
【例】使用*将'ABCDE'补齐为'*****ABCDE'
select lpad('ABCDE',10,'*') from dual;
【练习】使用*将员工表中的姓名从右边补齐到长度为10的字符串，如'SMITH',补齐为'SMITH*****'
select ename,rpad(ename,10,'*') from emp;
补充：函数嵌套
一个函数中使用另一个函数返回的结果（将一个函数返回的结果给另一个函数使用）
【例】用函数嵌套将字符'ABCDE'返回结果为'*****ABCDE*****'显示
select lpad(rpad('ABCDE',10,'*'),15,'*') from dual;
【思考】返回员工表中的姓名，如'*****SMITH*****'显示，姓名前后各加5个*
select ename,lpad(rpad(ename,length(ename)+5,'*'),length(ename)+10,'*') "返回结果" from emp;
7)concat(str1,str2) 将str1和str2连接
【例】使用'*****'和员工姓名拼接显示为如：'*****SMITH'
select ename,concat('*****',ename) from emp;
select ename,concat(concat('*****',ename),'*****') from emp;
8)instr(str,char) 返回char在str中第一次出现的位置
【例】返回员工表中员工姓名中'A'第一次出现的位置
select ename,instr(ename,'A') from emp;
【练习】返回员工表中职位中字符'E'第一次出现的位置
select job,instr(job,'E') from emp;
9)left(x,length) 从x的左边返回length个字符
right(x,length) 从x的右边返回length个字符
【例】从'ABCDEFGHIJKLMN'中返回'ABCD'
select left('ABCDEFGHIJKLMN',4) from dual;
【例】从'ABCDEFGHIJKLMN'中返回'KLMN'
select right('ABCDEFGHIJKLMN',4) from dual;
数字函数
floor(x) 返回小于等于x的最大整数
select floor(567.89) from dual; #567
celling(x)返回大于等于x的最小整数
select celling(567.89) from dual; #568
mod(x,y)返回x除以y的余数
select mod(10,3) from dual; #1
round(x,y) 返回对x精确到y位的结果
例：返回567.456精确到小数点后2位
select round(567.456,2) from dual; #567.45
truncate(x,y) 返回对x截取到y位的结果
select truncate(78905.131,1) from dual; #78905.1
注意：y位正数时，截取小数点后y位
y位负数时，截取小数点前y位 ＃截取不会四舍五入
select truncate(78905.131,-3) from dual; #78000
日期函数
curdate();
返回当前日期
curtime();
返回当前时间
now();
返回当前日期和时间
＃ ()里面不需要添加内容
last_day(x)
返回x所在月的最后一天的日期
例：查询员工表中员工的姓名，入职日期。以及入职月的最后一天
select ename,hiredate,last_day(hiredate) from emp;
date_add(x,interval,n f)
返回日期加上间隔时间n的结果,f可以为day/minth/year
例：查询员工表中员工的姓名、入职时间及员工入职后三天的日期
select ename,hiredate,date_add(hiredate,interval -3 day) from emp;
date_format(x,fmt)
返回日期x按指定格式fmt格式化后的结果
例：查询员工表员工的姓名、入职日期及员工入职所在月份（两位数显示）
select ename,hiredate,date_format(hiredate,'%m') from emp;
控制流函数
case
case 属性名称 when 值1 then 结果1......when 值n then 结果n end;
例：查询员工表中员工的姓名、工资、部门号为10显示为财务部，20显示为研发部，30显示为销售部
select ename,sal,deptno,case deptno when 10 then '财务部' when 20 then '研发部' when 30 then '销售部' end from emp;
注意：命令结束，必须要以end结尾
if
if(条件,值1,值2)如果条件为真，返回结果值1，否则返回结果值2
例：查询员工表中员工的姓名、职位、如果职位为'MANAGER’或者’PRESIDENT'，显示为管理人员，否则显示为普通员工
select ename,job,if(job in('manager','president'),'管理人员','普通员工') '职级' from emp
order by job desc;
多表查询
语法： select 表1.列1,表2.列2 from 表1,表2
where 连接条件（n张表联合查询至少需要n-1个连接条件）
内连接
等值连接、不等连接、自连接
外连接
左外连接、右外连接
语法：select 表1.列1,表2.列2 from 表1 left/right join 表2 on 表1.列=表2.列;
1.内连接
1)等值连接 表和表之间有相等的值，主要用于有主外键关系的表
【例】查询员工的姓名，工资，职位，部门号，部门名称
select emp.`ename`,emp.`job`,emp.sal,emp.`deptno`,dept.dname from emp,dept
where emp.deptno = dept.`deptno`;
【练习】查询员工的姓名，工资，部门号，部门名称，部门地址
select emp.`ename`,emp.sal,emp.`deptno`,dept.dname,dept.loc from emp,dept
where emp.deptno = dept.`deptno`;
2)不等值连接 表和表之间没有相等的值
【例】查询员工的姓名，工资，以及工资等级
select emp.ename,emp.sal,salgrade.grade from emp,salgrade
where emp.sal between salgrade.`losal` and salgrade.`hisal`
order by emp.sal;
select e.ename,e.sal,s.grade from emp e,salgrade s
where e.sal between s.`losal` and s.`hisal`
order by e.sal;
3)自连接 把一张表当成多个表来使用
【例】查询员工的编号，姓名，职位，MGR,以及员工的直接上级的姓名
select e.empno,e.ename,e.job,e.mgr,m.ename from emp e,emp m #把emp表当成员工表e,把emp表当成直接上级领导表m
where e.mgr = m.empno;
【练习】查询员工的编号，姓名，职位，MGR,以及员工的直接上级的姓名及直接上级的入职日期
select e.empno,e.ename,e.job,e.mgr,m.ename,m.hiredate from emp e,emp m #把emp表当成员工表e,把emp表当成直接上级领导表m
where e.mgr = m.empno;
2.外连接
#from后面的为左边表，join后面的为右边表
1)左外连接 包含左边表的全部数据，以及右边表全部匹配的数据
语法： select 表1.列1,表2.列2 from 表1 left join 表2 on 表1.列=表2.列;
【例】查询员工的编号，姓名，职位，MGR,以及员工的直接上级的姓名，没有上级的员工信息也一起列出
select e.empno,e.ename,e.job,e.mgr,m.ename from emp e left join emp m on e.mgr = m.empno;
2)右外连接 包含右边表的全部数据，以及左边表全部匹配的数据
语法： select 表1.列1,表2.列2 from 表1 right join 表2 on 表1.列=表2.列;
【例】查询员工的编号，姓名，职位，MGR,以及员工的直接上级的姓名，没有上级的员工信息也一起列出
select e.empno,e.ename,e.job,e.mgr,m.ename from emp m right join emp e on e.mgr = m.empno;
分组函数
组函数
count(column) 返回指定列中非空值的个数
例：统计员工表中的奖金的员工的个数
select count(comm) from emp;
去除重复：
select count(distinct job) from emp;
sum(column) 返回指定列的所有值之和
例：查询员工表中所有员工工资总和
select sun(sal) from emp; #可以排除空值信息
avg(column) 返回指定列的平均值
例：查询员工表中员工的平均工资
select round(avg(sal),2) from emp; #保留统计结果2位小数点
max(column) 返回最大值
例：查询最低工资
select max(sal) from emp;
min(column) 返回最小值
例：查询最低工资
select min(sal) from emp;
group by 子句用于分组查询
注意：1、 如果在select 后面有组函数，又有普通列（没有用函数的列），需要使用group by子句，并且所有的 普通列必须都出现在group by子句中；
2、group by 子句中的普通列可以不在select后面
例：查询员工表中每个部门的平均工资
select avg(sal) from emp
group by deptno;
select deptno,avg(sal) from emp
group by deptno;
例：查询员工表中每个部门每个职位的平均工资
select deptno,job,avg(sal) from emp
group by deptno,job;
having 子句 用于限定分组查询结果
注意：1、不能使用where子句限定分组结果
2、有group by子句不一定有having子句，但是有group by子句时要使用限定条件必须用having用where
3、有组函数时限定条件用having
例：查询员工平均工资大于2000的部门及部门平均工资，以及部门的总工资
select deptno,sum(sal),avg(sal) from emp
group by deptno
having avg(sal) > 2000.00;
【练习】查询部门总工资高于11000.00的部门的总工资，部门号，部门平均工资
select deptno,sum(sal),avg(sal) from emp
group by deptno
having sum(sal) > 11000.00;
子查询分类
1)单行子查询
子查询返回一行记录
使用单行比较运算符 = 、>、 <、 >= 、<=、 <>
【例】查询员工表中工资高于20号部门的最高工资的人员的姓名，工资
#20号部门最高工资
select max(sal) from emp where deptno = 20;
select ename,sal from emp
where sal > (select max(sal) from emp where deptno = 20);
【练习】查询员工表中入职日期晚于'MARTIN'的员工的编号，姓名，入职日期
select empno,ename,hiredate from emp
where hiredate > (select hiredate from emp where ename = 'MARTIN');
【练习】查询员工表中职位与'BLAKE'一致的员工的编号，姓名，职位
select empno,ename,job from emp
where job = (select job from emp where ename = 'BLAKE');
【例】查询职位与7369一致并且工资高于7876的员工的姓名，职位
select ename, job from emp
where job = (select job from emp where empno = 7369)
and sal > (select sal from emp where empno = 7876);
【例】查询职位与7369一致或者工资高于7876的员工的姓名，职位
select ename, job from emp
where job = (select job from emp where empno = 7369)
or sal > (select sal from emp where empno = 7876);
使用组函数与having子句
先执行子查询
然后返回结果到主查询的HAVING 子句
【例】查询部门最低工资高于20号部门最低工资的部门号与最低工资
select deptno, min(sal) from emp
group by deptno
having min(sal) >(select min(sal) from emp where deptno = 20);
【练习】查询部门最高工资高于20号部门平均工资的部门号与最高工资，最低工资
select deptno,max(sal),min(sal) from emp
group by deptno
having max(sal) > (select avg(sal) from emp where deptno = 20);
2)多行子查询
子查询返回多行一列
多行比较运算符 in 、 any 、 all
a) in 等于列表中的任何值
【例】查询与SMITH或者MILLER的部门号相同的员工的姓名，部门号
select ename,deptno from emp
where deptno in(select deptno from emp where ename = 'SMITH' or ename = 'MILLER');
select ename,deptno from emp
where deptno in(10,20); #(10，20)= select deptno from emp where ename = 'SMITH' or ename = 'MILLER'
【练习】查询与SMITH或者JONES职位相同的员工的姓名，职位，部门号
select ename,job,deptno from emp
where job in(select job from emp where ename in('SMITH','JONES'))
order by deptno;
b) any 比较子查询返回的每一个值，只要其中一个值满足条件就返回true
<any 指小于最大值
>any 指大于最小值
【例】查询职位不为CLERK且工资大于职位为CLERK的员工最低工资的员工的编号，姓名，职位
#使用多行子查询
select empno, ename, job from emp
where sal > any(select sal from emp where job = 'CLERK')
and job <> 'CLERK';
#单行子查询
select empno,ename,job from emp
where sal > (select min(sal) from emp where job = 'CLERK')
and job <> 'CLERK';
【练习】查询职位不为MANAGER且入职日期早于其中一个职位为MANAGER的员工的入职日期的员工的编号，姓名，职位，入职日期（多行子查询）
select empno,ename,job,hiredate from emp
where job <> 'MANAGER'
and hiredate < any(select hiredate from emp where job = 'MANAGER');
【练习】查询入职日期不早于其中一个部门最晚入职的员工的姓名，入职日期
select ename,hiredate from emp
where hiredate >= any(select max(hiredate) from emp group by deptno);
【练习】查询入职日期晚于其中一个部门最晚入职的员工的姓名，入职日期
select ename,hiredate from emp
where hiredate > any(select max(hiredate) from emp group by deptno);
c)all 比较子查询返回的每一个值，要其中每一个值都要满足条件才返回true
>all 指大于最大值
<all 指小于最小值
【练习】查询入职日期晚于所有部门最早入职的员工的姓名，入职日期
select ename,hiredate from emp
where hiredate >all(select min(hiredate) from emp group by deptno);
【练习】查询入职日期早于所有部门最晚入职的员工的姓名，入职日期
select ename,hiredate from emp
where hiredate < all(select max(hiredate) from emp group by deptno);
3)多列子查询
子查询返回多行多列
多行多列子查询使用in运算符
【例】查询每个部门最高工资员工的姓名，工资以及部门号
#每个部门最低工资
select max(sal) from emp group by deptno;
select ename,sal,deptno from emp
where sal in(select max(sal) from emp group by deptno); #只要有员工工资等于其中一个部门的最高工资就可以被查询出来
select ename,sal,deptno from emp
where (deptno,sal) in(select deptno,max(sal) from emp group by deptno); #查询时加上部门号做限制，只能查询出每个部门工资最高的员工的信息 不同部门有详细属性需加上属性限制
【练习】查询每个部门最晚入职员工的姓名，职位，入职日期,部门号
select ename,job,hiredate,deptno from emp
where (deptno,hiredate) in(select deptno,max(hiredate) from emp group by deptno);
4)分页查询
limit 起始行号,返回行数 #起始行号和返回行数均为数字
语法：sql语句 limit 起始行(第一行的行号为0) 返回行数量
【例】查询员工表中收入排名前5位的员工的姓名，工资，部门号
select ename,sal,deptno from emp
order by sal desc
limit 0,5;
【练习】查询员工表中工资排名第8到12位的员工的姓名，工资，部门号
select ename,sal,deptno from emp
order by sal desc
limit 7,5;
【练习】查询员工表中最早入职的前三个员工的姓名，入职日期
select ename,hiredate from emp
order by hiredate
limit 0,3;
存储过程
一、存储过程
1.存储过程语法
delimiter //
create procedure 过程名(模式 形参1 数据类型,……)
begin
程序体;
end;//
delimiter ;
注意：
(1)这里需要注意的是DELIMITER //和DELIMITER ;两句，DELIMITER是分割符的意思，因为MySQL默
认以";"为分隔符，如果我们没有声明分割符，那么编译器会把存储过程当成SQL语句进行处理，则存储
过程的编译过程会报错，所以要事先用DELIMITER关键字申明当前段分隔符，这样MySQL才会将";"当
做存储过程中的代码，不会执行这些代码，用完了之后要把分隔符还原。
(2)存储过程根据需要可能会有输入、输出、输入输出参数，多个参数用","分割开。
(3)过程体的开始与结束使用BEGIN与END进行标识。
形参模式：
in：该参数用于接收外部的值传入存储过程内部 #in可以省略不写
out：该参数用于将存储过程内部处理结果传出存储过程
数据输出： select 输出的对象;
调用存储过程： call 过程名(实参1,实参2,......);
【举例】
语法：
call precedure_name（[param1,param2]…..）
举例：
call pr_add(10, 20); #pr_add为存储过程名，10，20为in模式的实参
2.用户变量
MySQL存储过程中，定义变量有两种方式：
用户变量：
使用SET或SELECT直接赋值，变量名以@开头.
set @变量名=值1;
select @变量名:=值2;
select 列1，列2，.... into 变量1，变量2,.... from 表名 where 条件;
例如: set @var=1;
select @var:=5;
select empno,ename into @var,@name from EMP where empno =7499 ;
可以在一个会话的任何地方声明，用户变量可以作用于当前整个连接，但是当前连接断开后，其所
定义的用户变量都会消失称为用户变量。
【例】定义一个存储过程，输出3+4的和
delimiter //
create procedure test1()
begin
set @var=3;
set @vb=4;
select @var+@vb;
end; //
delimiter ;
call test1();
【例】定义一个存储过程，该过程接收两个整数，调用该过程，输出这两个整数的和
delimiter //
create procedure pro_sum(in a int,b int)
begin
set @sum=a+b; #声明变量@sum并且为其赋值为(a+b) select @sum:=a+b;
select @sum;
end;//
delimiter ;
call pro_sum(13,14)ect @cha;
end;// #select @ji;
delimiter ;
call test(3,4);
【例】定义一个存储过程，该过程接收一个部门号，输出与该部门最高工资相同的员工的姓名
#查询出与20号部门最高工资相同的员工的姓名
select ename from emp where (deptno,sal) in(select deptno,max(sal) from emp where deptno = 20);
delimiter //
create procedure test2(dno int)
begin
select ename into @ename from emp where (deptno,sal) in(select deptno,max(sal) from emp where deptno = dno);
select @ename;
end; //
delimiter ;
call test2(20);
【练习】定义一个存储过程，该过程接收两个正整数，调用该过程，输出这两个数的差，以及输出这两个数的积
delimiter //
create procedure test(a int,b int)
begin
set @cha=a-b; #定义变量@cha接收a-b的差
select @ji:=a*b; #定义@ji接收a*b的积
select @cha,@ji; #sel
【例】定义一个存储过程，该过程接收一个部门号，输出与该部门最高工资相同的员工的姓名
#查询出与20号部门最高工资相同的员工的姓名
select ename from emp where (deptno,sal) in(select deptno,max(sal) from emp where deptno = 20);
delimiter //
create procedure test2(dno int)
begin
select ename into @ename from emp where (deptno,sal) in(select deptno,max(sal) from emp where deptno = dno);
select @ename;
end; //
delimiter ;
call test2(30);
【练习】定义一个存储过程，该过程接收一个员工编号，输出该员工的姓名，工资，部门号
select ename,sal,deptno from emp where empno = 7789;
delimiter //
create procedure test3(eno int)
begin
select ename,sal,deptno into @ename,@sal,@deptno from emp where empno = eno; #将查询出的姓名，工资，部门号赋值给变量@ename,@sal,@deptno
select @ename,@sal,@deptno; #输出@ename,@sal,@deptno
end; //
delimiter ;
call test3(7788);
3.条件结构
if 语句
case语句
1)if 语句
if 语句根据条件表达式的值确定执行不同的语句块，if语句的语法格式如下。
if 条件表达式1 then
语句块1;
[elseif 条件表达式2 then
语句块2] ...
[else 语句块n]
end if;
说明：end if后必须以“;”结束
a) 单分支if语句
【例】定义 一个存储过程，输入一个整数，如果这个数为偶数，则输出'XXX为偶数'
delimiter //
create procedure test4(a int)
begin
if mod(a,2)=0 then
select concat(a,'为偶数'); #如果Mod(a,2)=0,则输出a为偶数;
end if;
end; //
delimiter ;
call test4(4);
b)双分支if语句
【例】定义 一个存储过程，输入一个整数，如果这个数为偶数，则输出'XXX为偶数'，否则输出'XXX为奇数'
delimiter //
create procedure test5(a int)
begin
if mod(a,2)=0 then
select concat(a,'为偶数'); #如果Mod(a,2)=0,则输出a为偶数;
else
select concat(a,'为奇数');
end if;
end; //
delimiter ;
call test5(7);
【练习】定义一个存储过程，接收两个整数a和b,如果a-b大于0，则输出'a大于b',否则输出'a小于b' (a!=b,输出结果中a.b为具体的数字)
delimiter //
create procedure test6(a int,b int)
begin
if a-b>0 then
select concat(a,'大于',b);
else
select concat(a,'小于',b);
end if;
end; //
delimiter ;
call test6(5,7)
c)多分支if语句
【例】定义一个存储过程，接收两个整数a和b,如果a-b大于0，则输出'a大于b',小于0时输出'a小于b'，否则输出'a等于b' (输出结果中a.b为具体的数字)
delimiter //
create procedure test7(a int,b int)
begin
if a-b>0 then
select concat(a,'大于',b);
elseif a-b<0 then
select concat(a,'小于',b);
else
select concat(a,'等于',b);
end if;
end; //
delimiter ;
call test7(5,7)
【练习】编写存储过程接收一个员工编号，查询出该员工的工资，如果工资>=2500，输出'H'，如果在[1500,2500),输出'M',否则输出'L'
delimiter //
create procedure test8(eno int)
begin
select sal into @sal from emp where empno = eno;
if @sal>=2500 then
select @sal,'H' as "工资等级";
elseif @sal>=1500 then
select @sal,'M' as "工资等级";
else
select @sal,'L' as "工资等级";
end if;
end;//
delimiter ;
call test8(7521);
2)case语句
语法：
case 表达式
when value1 then 语句块1;
when value2 then 语句块2;
…
else 语句块n;
end case;
注意：表达式的结果为一个常量，即一个具体的值
【例】编写存储过程，接收一个员工编号，查询该员工的部门号，当为10号时输出'人事部',当为20号时输出'研发部',否则输出'销售部'
delimiter //
create procedure test9(eno int)
begin
select deptno into @deptno from emp where empno = eno;
case @deptno
when 10 then
select @deptno,'人事部' as "部门名称";
when 20 then
select @deptno,'研发部' as "部门名称";
else
select @deptno,'销售部' as "部门名称";
end case;
end; //
delimiter ;
call test9(7698);
【练习】编写一个存储过程，该过程接收一个员工编号，查询出该员工的职位，当职位为'CLERK'时输出'普通员工'，当职位为'SALESMAN'时输出'销售人员',
当职位为'MANAGER'时输出'经理',当职位为'ANALYST'输出'开发人员',否则输出'CEO'
delimiter //
create procedure test10(eno int)
begin
select job into @job from emp where empno = eno;
case @job
when 'CLERK' then
select @job,'普通员工' as "职位";
when 'SALESMAN' then
select @job,'销售人员' as "职位";
when 'MANAGER' then
select @job,'经理' as "职位";
when 'ANALYST' then
select @job,'开发人员' as "职位";
else
select @job,'CEO' as "职位";
end case;
end; //
delimiter ;
call test10(7788);
4.循环结构
MySQL提供了三种循环语句，分别是while、repeat以及loop。
除此以外，MySQL还提供了iterate语句以及leave语句用于循环的控制。
1)while语句
语法：
[循环标签:]while 条件表达式 do #循环标签: 自定义内容
循环体;
end while [循环标签];
说明：
1）end while后必须以“;”结束
2）当条件表达式的值为true时,反复执行循环体,直到条件表达式的值为false,while语句的语法格式如下
3）循环标签可以省略
【例】编写存储过程，输出数字1-10
delimiter //
create procedure test11()
begin
set @num=0;
w:while @num<10 do
set @num=@num+1; #当@num小于10的时候，执行一次@num=@num+1,
select @num; #输出每一次执行后的@num的值
end while w; #@num循环后的值不小于10的时候，停止循环
end; //
delimiter ;
call test11();
【练习】编写存储过程，输出数字10-19
delimiter //
create procedure test12()
begin
set @num=9;
w:while @num<19 do
set @num=@num+1; #当@num小于19的时候，执行一次@num=@num+1,
select @num; #输出每一次执行后的@num的值
end while w; #@num循环后的值不小于19的时候，停止循环
end; //
delimiter ;
call test12();
2)repeat语句：当条件表达式的值为false时，反复执行循环，直到条件表达式的值为true
[循环标签:]repeat
循环体;
until 条件表达式
end repeat [循环标签];
说明：end repeat后必须以“;”结束
【例】编写存储过程，输出数字1-10
delimiter //
create procedure test13()
begin
set @num=0;
r:repeat
set @num=@num+1; #当@num<=9的时候执行循环，执行@num=@num+1
select @num;
until @num>9 #直到@num>9时停止循环
end repeat r;
end; //
delimiter ;
call test13();
【练习】编写存储过程，输出数字10-19
delimiter //
create procedure test14()
begin
set @num=9;
r:repeat
set @num=@num+1; #当@num<=18的时候执行循环，执行@num=@num+1
select @num;
until @num>18 #直到@num>18时停止循环
end repeat r;
end; //
delimiter ;
call test14();
3)循环控制语句
a)leave 关键字：用于跳出当前的循环语句（例如while语句）
语法格式如下：
leave 循环标签;
说明：leave 循环标签后必须以“;”结束
【例】编写存储过程，输出数字1-10
delimiter //
create procedure test15()
begin
set @num=0;
w:while 0=0 do #where 后面的条件表达式为真时，进行循环
set @num=@num+1;
select @num;
if @num>9 then
leave w; #当@num>9时，跳出循环
end if;
end while w;
end;//
delimiter ;
call test15();
【练习】编写存储过程，输出数字10-19
delimiter //
create procedure test16()
begin
set @num=0;
w:while 0=0 do #where 后面的条件表达式为真时，进行循环
set @num=@num+1;
select @num;
if @num>18 then
leave w; #当@num>18时，跳出循环
end if;
end while w;
end;//
delimiter ;
call test16();
b)iterate 关键字：用于跳出本次循环，继而进行下次循环。
语法格式：
iterate 循环标签;
说明：iterate循环标签后必须以“;”结束
【例】编写存储过程，输出数字1-10之间能被3整除的数
delimiter //
create procedure test17()
begin
set @num=0;
w:while @num<10 do
set @num=@num+1;
if mod(@num,3)=0 then
select @num; #当循环结果能被3整除时。输出@num
else
iterate w; #当循环结果不能被3整除时，跳出当前循环，进行下一次循环
end if;
end while w;
end; //
delimiter ;
call test17();
【练习】编写存储过程，输出1-20之间能被5整除的数
delimiter //
create procedure test18()
begin
set @num=0;
w:while @num<20 do
set @num=@num+1;
if mod(@num,5)=0 then
select @num;
else
iterate w;
end if;
end while w;
end; //
delimiter ;
call test18();
4)Loop循环
语法：
[循环标签:] loop
循环体;
if 条件表达式 then #当条件表达式为真的时候跳出循环
leave [循环标签];
end if;
end loop;
说明：end loop后必须以“;”结束
【例】编写存储过程，输出数字1-10
delimiter //
create procedure test19()
begin
set @num=0;
l:loop
set @num=@num+1;
select @num;
if @num>9 then
leave l;
end if;
end loop;
end; //
delimiter ;
call test19();
out形参
【例】
delimiter //
create procedure test20(out a varchar(20)) #out型 形参a
begin
select dname into a from dept where deptno=10; # 查询dept表10号部门的名称，将这个值(部门名称)赋给变量a
end; //
delimiter ;
call test20(@b); # 调用存储过程，声明一个参数@b接收存储过程传出的数据
select @b; # 查询变量@b的值
【例】向部门表中插入50条数据，部门名称及部门地址不允许重复
delimiter //
create procedure test21()
begin
set @dno=50;
w:while @dno<100 do
insert into dept values(@dno,concat(@dno,'号部门'),concat(@dno,'号部门地址'));
set @dno=@dno+1;
end while w;
end; //
delimiter ;
call test21();
where 、group by、having区别
select 列a,聚合函数 from 表名 where 过滤条件 group by 列a having 过滤条件
group by 字句也和where条件语句结合在一起使用。当结合在一起时，where在前，group by 在后。即先对select xx from xx的记录集合用where进行筛选，然后再使用group by 对筛选后的结果进行分组 使用having字句对分组后的结果进行筛选
需要注意having和where的用法区别：
1.having只能用在group by之后，对分组后的结果进行筛选(即使用having的前提条件是分组)。
2.where肯定在group by 之前
3.where后的条件表达式里不允许使用聚合函数，而having可以。
四、当一个查询语句同时出现了where,group by,having,order by的时候，执行顺序和编写顺序是：
1.执行where xx对全表数据做筛选，返回第1个结果集。
2.针对第1个结果集使用group by分组，返回第2个结果集。
3.针对第2个结果集中的每1组数据执行select xx，有几组就执行几次，返回第3个结果集。
4.针对第3个结集执行having xx进行筛选，返回第4个结果集。
5.针对第4个结果集排序。
例子：
完成一个复杂的查询语句，需求如下：
按由高到低的顺序显示个人平均分在70分以上的学生姓名和平均分，为了尽可能地提高平均分，在计算平均分前不包括分数在60分以下的成绩，并且也不计算贱人（jr）的成绩。
分析：
1．要求显示学生姓名和平均分
因此确定第1步
|
1
|
select s_name,avg(score) from student
|
2．计算平均分前不包括分数在60分以下的成绩，并且也不计算贱人（jr）的成绩
因此确定第2步
|
1
|
where score>=60 and s_name !=’jr’
|
3．显示个人平均分
相同名字的学生（同一个学生）考了多门科目 因此按姓名分组
确定第3步
|
1
|
group by s_name
|
4．显示个人平均分在70分以上
因此确定第4步
|
1
|
having avg(s_score)>=70
|
5．按由高到低的顺序
因此确定第5步
|
1
|
order by avg(s_score) desc
|
浙公网安备 33010602011771号