数据库的优点
1、实现数据持久化到本地
2、结构化查询:使用完整的管理系统统一管理,易于查询
数据库相关概念
DB:数据库(Database):存储数据的“仓库”,保存了一系列有组织的数据;
DBMS:数据库管理系统(Database Management System):数据库是通过DBMS创建和操作的容器;
常见数据库管理系统:MySQL、Oracle、DB2、SqlServer
SQL:结构化查询语言(Structure Query Language):专门用来与数据库通信的语言。
SQL的优点:
1、不是某个特定数据库供应商专有的语言,几乎所有DBMS都支持SQL;
2、简单易学;
3、灵活使用其他语言元素,可进行复杂和高级的数据库操作;
数据库的特点:
1、将数据放到表中,表再放到库中;
2、一个数据库中可以有多个表,每个表都有一个的名字,用来标识自己。表名具有唯一性;
3、表具有一些特性,这些特性定义了数据在表中如何存储,类似Java中“类”的设计;
4、表由列组成,称之为字段,所有表都是由一个或多个列组成的,每一列类似Java中的“属性”;
5、表中的数据是按行存储的,每一行类似Java中的“对象”。
MySQL产品的介绍
MySQL服务的启动与停止:
方式一:计算机——>右击管理——>服务
方式二:通过管理员身份运行
启动:net start 服务名称
停止:net stop 服务名称
MySQL服务端的登录与退出
方式一:使用自带的客户端,只限于roast用户;
方式二:通过Windows自带的客户端
登录:mysql (-h主机名 -P端口号) -u用户名 -p密码
退出:exit 或者 CTRL+C
MySQL的常见命令
1、查看当前所有的数据库
show databases;
2、打开指定的库
use 库名;
3、查看当前库的所有表
show tables;
4、查看其他库的所有表
show tables from 库名;
5、创建表
create table 表名(
列名 列类型,
列名 列类型,
……
)
6、查看表结构
desc 表名;
7、查看服务器的版本
方式一:登录到mysql服务器
select version();
方式二:没有登录到mysql服务器
mysql --version
mysql --V
MySQL的语法规范
1、不区分大小写,但建议关键字大写,表名、列名小写;
2、每条命令用分号“;”结尾;
3、每条命令根据需要,可以进行缩进或换行;
4、注释
单行注释:# 注释文字
单行注释:-- 注释文字
多行注释:/* 注释文字 */
DQL语言的学习
进阶一:基础查询
一、语法
select 查询列表 from 表名;
二、特点
1、查询列表可以是:表中的字段、常量值、表达式、函数
2、查询的结果是一个虚拟的表格
三、示例
1、查询表中的单个字段
select 字段名 from 表名;
select a from b;
2、查询表中的多个字段
select 字段名, 字段名 from 表名;
select a,c,d from b;
3、查询表中的所有字段
select * from 表名;
4、查询常量值
select 常量值;
注:字符型和日期型的常量值必须用单引号引起来,数值型不需要
select 100;
select ‘John’;
5、查询表达式
select 100*98;
6、查询函数
select 函数名(实参列表);
select version();
7、起别名
(1)便于理解
(2)如果要查询的字段有重名的情况,使用别名可以区分开来
方式一:使用as
select 100*98 as 结果;
select last_name as 姓,first_name as 名 from abc;
方式二:使用空格
select last_name 姓,first_name 名 from abc;
例:查询nalary,显示结果为out put;
select nalary as out put from abc;
8、去重
例:查询员工表中涉及到的所有部门编号
select distinct 字段名 from 表名;
9、+号的作用
只有一个功能——运算符,参加运算
select 100+90;两个操作数都为数值型;
select ‘123’+90;其中一方为字符型,试图将字符型数值转换为数值型,如果转换成功,则继续做加法运算;如果转换失败,则将字符型数值转换成0.
select null+10;只要其中一方为null,则结果肯定为null;
补充:concat函数
功能:拼接字符
例:查询员工名和姓连接成一个字段,并显示为姓名
select concat(‘a’,‘b’,‘c’) as 结果;
select concat(last_name, first_name ) as 姓名 from abc;
补充:ifnull函数
功能:判断某字段或表达式是否为null,如果为null,返回指定的值,否则返回原本的值
select ifnull(comission_pct,0)from employee;
补充:isnull函数
功能:判断某字段或表达式是否为null,如果是,返回1,否则返回0
进阶二:条件查询
一、语法
select 查询列表 from 表名 where 筛选条件;
(顺序:from,where,select)
二、分类
(一)、按条件表达式筛选
简单条件运算符:> < = != <> >= <= <=>
例1:查询工资大于12000的员工信息
select * from employee where salary > 12000;
例2:查询部门编号不等于90号的员工名和部门编号
select last_name,department_id
from employee
where department_id !=90;
(二)、按逻辑表达式筛选
逻辑运算符: and or not
作用:用于连接条件表达式
and(&&):两个条件都为true,结果为true,反之为false;
or(||):只要有一个条件为true,结果为true,反之为false;
not(|):如果连接的条件本身为false,结果为true,反之为false。
例1:查询工资在10000到20000之间的员工名、工资及奖金
select last_name,salary,commission_pct
from employee
where salary>=1000 and salary<=20000;
例2:查询部门编号不是在90到110之间,或者工资高于15000的员工信息
方式一:
select * from employee
where department_id<90 or department_id>110 or salary>15000;
方式二:
select * from employee
where not ( department_id>=90 and department_id <= 110) or salary>15000;
(三)、模糊查询
复杂条件运算符:like between and in is null / is not null
1、like:
(1)一般和通配符搭配使用,可以判断字符型或数值型
通配符:
%:任意多个字符,包含0个字符
_:任意单个字符
例1:查询员工名中包含字符a的员工信息
select * from employee where last_name like '% a %';
例2:查询员工名中第三个字符为e,第五个字符为a的员工名和工资
select last_name, salary from employee where last_name like '__e_a%';
例3:查询员工名中第二个字符为_的员工名
方式一:
select last_name from employee where last_name like '_\_%';
方式二:
select last_name from employee
where last_name like '_$_%' escape '$';
(#escape 转义)
2、between and
(1)使用 between and 可以提高语句的简洁度
(2)包含临界值
(3)两个临界值不要调换顺序
例1:查询员工编号在100到120之间的员工信息
方式一:
select * from employee
where employee_id >= 100 and employee_id <= 120;
方式二:
select * from employee where employee_id between 100 and 120;
3、in
含义:判断某字段的值是否属于in列表中的某一项
特点:
(1)使用in提高语句简洁度
(2)in列表中的值类型必须一致或兼容
(3)不支持通配符的使用
例:查询员工的工种编号是IT_PROG,AD_VP,AD_PRES中的一个员工名和工种编号
方式一:
select last_name, job_id from employee
where job_id = 'IT_PROG' or job_id = 'AD_VP' or job_id = 'AD_PRES';
方式二:
select last_name, job_id from employee
where job_id in ('IT_PROG' , 'AD_VP', 'AD_PRES');
4、is null
=或<>不能用于判断null值
is null或is not null可以判断null值
例1:查询没有奖金的员工名和奖金率
select last_name, comission_pct
from employee
where comission is null;
例2:查询有奖金的员工名和奖金率
select last_name, comission_pct
from employee
where comission is not null;
安全等于:<=>
例1:查询没有奖金的员工名和奖金率
select last_name, comission_pct
from employee
where comission <=> null;
例2:查询工资为12000的员工信息
select last_name, salary
from employee
where salary <=> 12000;
is null PK <=>
is null:仅仅可以判断null值,可读性高
<=>:既可以判断null值,又可以判断普通数值,可读性较低
进阶三:排序查询
一、语法
select 查询列表
from 表
【where 筛选条件】
order by 排序列表 【asc/desc】;
(执行顺序:from—>where—>select—>order by)
1、asc:代表升序;desc:代表降序。如果不写,默认升序
2、order by子句中可以支持单个字段、多个字段、表达式、函数、别名
3、order by子句一般是放在查询语句的最后面,limit子句除外
例1:查询员工信息,要求工资从高到低排序
select * from employee order by salary desc;
例2:查询员工信息,要求工资从低到高排序
select * from employee order by salary asc;
或
select * from employee order by salary;
例3:查询部门编号大于等于90的员工信息,按入职时间的先后进行排序
select * from employee
where department_id >= 90
order by hiredate asc;
例4:按年薪的高低显示员工的信息(按表达式排序)
select *,salary * 12 * (1+ifnull(commission_pct,)) 年薪
from employee
order by salary * 12 * (1+ifnull(commission_pct,)) desc;
例5:按年薪的高低显示员工的信息(按别名排序)
select *,salary * 12 * (1+ifnull(commission_pct,)) 年薪
from employee
order by 年薪 desc;
例6:按姓名的长度显示员工的姓名和工资(按函数排序)
select length(last_name) 字节长度,last_name, salary
from employee
order by ength(last_name) desc;
例7:查询员工信息,要求按工资排序,再按员工编号(按多个字段排序)
select * from employee
order by salary asc, employee_id desc;
进阶四:常见函数
一、概念
类似于Java的方法,将一组逻辑语句封装在方法体中,对外暴漏方法名
二、优点:
1、隐藏了实现细节
2、提高代码的复用性
调用:select 函数名(实参列表)【from 表】;
三、特点
1、叫什么(函数名)
2、干什么(函数功能)
三、分类
1、单行函数
2、分组函数
功能:做统计使用,又称为统计函数、聚合函数、组函数
3、常见函数
一、单行函数
(一)字符函数:
length:获取字节长度
concat:连接
substr:截取子串
instr:获取子串第一次出现的索引
trim:去重
upper:变大写
lower:变小写
lpad:左填充
rpad:右填充
replace:替换
(二)数学函数:
round:四舍五入
ceil:向上取整
floor:向下取整
truncate:截断
mod:取模
rand:获取随机数,返回0-12间的小数
(三)日期函数:
now:返回当前日期+时间
curdate:返回当前日期
curtime:返回当前时间
year:返回年
month:返回月
monthname:以英文形式返回月
day:天
hour:小时
minute:分钟
second:秒
str_to_date:将字符转换成日期
date_format:将日期转换成字符
datediff:返回两个日期相差的天数
(四)其他函数
version:当前数据库服务器的版本
database:当前打开的数据库
user:当前用户
password(‘字符’):返回该字符的密码形式
md5(‘字符’):返回该字符的md5加密形式
(五)流程控制函数
1、
if(条件表达式,表达式1,表达式2):如果条件表达式成立,返回表达式1,否则返回表达式2
2、case情况一:
case 变量或表达式或字段
when 常量1 then 常量1
when 常量2 then 常量2
……
else 值n
end
case情况二:
case
when 条件1 then 常量1
when 条件2 then 常量2
……
else 值n
end
(一)字符函数
1、length:获取参数值的字节个数
2、concat:拼接字符
3、upper、lower:upper变大写,lower变小写
例:将姓变大写,名变小写,然后拼接
select concat(upper(last_name), lower(first_name))姓名
from employee;
4、substr、substring
注:索引从1开始
截取从指定索引处后面所有字符
select substr('我和我的祖国都很棒',7) out_put;
显示都很棒
截取从指定索引处指定字符长度的字符
select substr('我和我的祖国都很棒',1,3)out_put;
显示我和我
5、instr:返回子串第一次出现的索引,如果找不到,返回0
select instr('我和我的祖国都很棒','都超棒')as out_put;
6、trim:去空格
select length(trim(' 我 '))as out_put;
7、lpad:用指定的字符实现左填充指定长度
select lpad('请求权',2,'*') as out_put;
8、rpad:用指定的字符实现右填充指定长度
select rpad('请求权',12,'ab') as out_put;
9、replace:替换
select replace('我和我的祖国都很棒','祖国','地球') as out_put;
(二)数学函数
1、round:四舍五入
select round(-1,55);
select round(1,567,2);
2、ceil:向上取整,返回>=该参数的最小整数
select ceil(1,00);
3、floor:向下取整,返回<=该参数的最大整数
select floor(-9,99);
4、truncate:截断
select truncate(1,65,1);
5、mod:取余
select mod(10,3);
(三)日期函数
1、now:返回当前系统日期+时间
select now();
2、curdate:返回当前系统日期,不包含时间
select curdate();
3、curtime:返回当前时间,不包含日期
select curtime();
获取指定的部分,年、月、日、小时、分钟、秒
select year( now() ) 年;
str_to_date:将日期格式的字符转换成指定格式的日期
str_to_date ( '9-13-1999', '%m-%d-%Y' );
date_format:将日期转换成字符
date_format ('2018/6/6', '%Y年%m月%d日');
(四)其他函数
select version();
select database();
select user();
(五)流程控制函数
1、if函数:if else 的效果
2、case函数的使用一:switch case的效果
语法:
case 要判断的字段或表达式
when 常量:then 要显示的值1或语句1;
when 常量:then 要显示的值2或语句2;
………
else 要显示的值n或语句n;
end
例:查询员工的工资,要求:
部门号=30,显示的工资为1.1倍
部门号=40,显示的工资为1.2倍
部门号=50,显示的工资为1.3倍
其他部门,显示的工资为原工资
select salary,department_id
case department_id
when 30 then salary * 1.1
when 40 then salary * 1.2
when 50 then salary * 1.3
else salary
end as 新工资
from employee;
3、case函数的使用二:类似于多重if
语法:
case
when 条件1 then 要显示的值1或语句1;
when 条件2 then 要显示的值1或语句2;
…………
else 要显示的值n或语句n;
end
例:查询员工的工资情况
如果工资大于20000,显示A级别
如果工资大于15000,显示B级别
如果工资大于10000,显示C级别
否则,显示D级别
select salary,
case
when salary > 20000 then 'A'
when salary > 15000 then 'B'
when salary > 10000 then 'C'
else 'D'
end as 工资级别
from employee;
例1:显示系统时间(注:日期+时间)
select now();
例2:查询员工号、姓名、工资、工资提高百分之二十后的结果
select employee_id,last_name, salary, salary*1.2 "new salary"
from employee;
例3、将员工的姓名按首字母排序,并写出姓名的长度
select length(last_name) 长度,substr(last_name,1,1) 首字符,last_name
from employee order by 首字符;
例4、做一个查询,产生下面的结果
select concat (last_name, 'earns',salary, 'monthly but wants', salary*3) as 'dream salary'
from employee
where salary=24000;
5、使用case-when,按照下面的条件
select job_id as job
case job_id
when 'AD_PRES' then 'A'
when 'ST_MAN' then 'B'
when 'IT_PRCC' then 'C'
end as grade
from employee
where job_id = 'AD_PRES';
二、分组函数
(一)功能:用作统计使用,又称为聚合函数或统计函数或组函数
(二)分类:
sum求和,avg平均值,max最大值,min最小值,count计算个数
(三)特点:
1、语法:
select max(字段) from 表名;
2、支持的类型:
sum、avg一般用于处理数值型
max、min、count可以处理任何类型
3、以上分组函数都忽略null值
4、都可以和distinct搭配使用,实现去重的设计
select max(distinct 字段) from 表名;
5、count函数的单独介绍
一般使用count()用作统计函数
count(字段):统计该字段非空值的个数
count():统计结果集的行数
count(1):统计结果集的行数
效率上:
MyISAM存储引擎:count()效率最高
InnoDB存储引擎:count()和count(1)效率>count(字段)
6、和分组函数一同查询的字段要求是group by后出现的字段
1、简单的使用
select sum(salary) from employee;
select avg(salary) from employee;
select max(salary) from employee;
select min(salary) from employee;
select count(salary) from employee;
select sum(salary) 和, avg(salary) 平均, max(salary) 最高,min(salary)最低,count(salary) 个数 from employee;
2、参数支持哪些类型
select sum(last_name), avg(last_name) from employee;
select sum(hiredate), avg(hiredate) from employee;
select max(last_name), min(last_name) from employee;
select max(hiredate), min(hiredate) from employee;
select count(commission_pct) from employee;
select count(last_name) from employee;
3、是否忽略null
select sum(commission_pct), avg(commission_pct),sum(commission_pct)/35, sum(commission_pct)/107
from employee;
4、和distinct搭配
select sum(distinct salary)
from employee;
5、count函数详细介绍
select count(salary) from employee;
select count(*) from employee;
select count(1) from employee;
效率:
myisan存储引擎下,count()的效率高
innodb存储引擎下,count()和count(1)的效率差不多,比count(字段)效率要高一些
6、和分组函数一同查询的字段有限制
select avg(salary),employee_id
from employee;
例1、查询公司员工工资的最大值、最小值、平均值、总和
select max(salary) mx_sal, min(salary) min_sal, avg(salary) avg_sal, sum(salary) sm_sal
from employee;
例2、查询员工表中的最大入职时间和最小入职时间的相差天数(difference)
select datediff(max(hiredate) min(hiredate)) difference
from employee;
例3、查询部门编号为90的员工个数
select count(*)
from employee
where department_id=90;
进阶五:分组查询
一、语法
select 分组函数,列(要求出现在group by的后面)
from 表
【where 筛选条件】
group by 分组的列表
【having 分组后的筛选】
【order by 排序列表】
注意:
查询列表必须特殊,要求是分组函数和group by后出现的字段
二、特点
1、分组查询中的筛选条件分为两类
数据源 | 位置 | 关键字 | |
---|---|---|---|
分组前筛选 | 原始表 | group by子句的前面 | where |
分组后筛选 | 分组后的结果表 | group by子句的后面 | having |
(1)分组函数做条件,肯定是放在having子句中
(2)能用分组前筛选的,就优先考虑使用分组前筛选
2、group by子句支撑单个字段分组,多个字段分组(多个字段之间使用逗号隔开并没有顺序要求),表达式或函数(用的较少)
3、也可以添加排序(排序放在整个分组查询的最后)
引例:查询每个部门的平均工资
select avg(salary) from(employee);
简单的分组查询
例1:查询每个工种的最高工资
select max(salary),job_id
from employee
group by job_id;
例2:查询每个位置上的部门个数
select count(*),location_id
from departments
group by location_id;
添加筛选条件
例1、查询邮箱中含a字符的,每个部门的平均工资
select avg(salary),department_id
from employee
where email like '%a%'
group by department_id;
例2、查询有奖金的每个领导手下员工的最高工资
select max(salary), manager_id
from employee
where commission_pct is not null
group by manager_id;
添加复杂的筛选条件
例1、查询每个部门的员工个数>2
(1)查询每个部门的员工个数
select count(*), department_id
from employee
group by department_id;
(2)根据(1)的结果进行筛选,查询哪个部门的员工个数>2
select count(*), department_id
from employee
group by department_id
having count(*)>2;
例2、查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
(1)查询每个工种有奖金的员工的最高工资
select max(salary), job_id
from employee
group by job_id;
(2)根据(1)结果继续筛选,最高工资>12000
select max(salary), job_id
from employee
group by job_id
having max(salary>12000);
例3、查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个,及其最低工资
(1)查询每个领导手下的员工固定最低工资
select min(salary), manager_id
from employee
group by manager_id;
(2)添加筛选条件,编号>102
select min(salary), manager_id
from employee
where manager_id>102
group by manager_id;
(3)添加筛选条件,最低工资大于5000
select min(salary), manager_id
from employee
where manager_id>102
group by manager_id
having min(salary)>5000;
按表达式或函数分组
例:按员工姓名的长度分组,查询每一组的员工个数,筛选员工个数>5的有哪些
(1)查询每个长度的员工个数
select count(*),length(last_name) len_name
from employee
group by length(last_name);
(2)添加筛选条件
select count(*),length(last_name) len_name
from employee
group by length(last_name)
having count(*)>5;
按多个字段分组
例:查询每个部门每个工种的员工的平均工资
select avg(salary),department_id, job_id
from employee
group by department_id, job_id;
添加排序
例:查询每个部门每个工种的员工的平均工资,并且按平均工资的高低显示
select avg(salary),department_id, job_id
from employee
where department_id is not null
group by department_id, job_id
having avg(salary)>10000
order by avg(salary) desc;
进阶六:连接查询
一、含义
连接查询又称多表查询,当查询的字段来自于多个表时,就会用到连接查询
二、笛卡尔乘积现象
当查询多个表时,没有添加有效的连接条件,导致多个表所有行实现完全连接
表1有m行,表2有n行,结果m*n行
发生原因:没有有效的连接条件
如何避免:添加有效的连接条件
三、分类
(一) 按年代分类:
sql92标准(仅支持内连接)
sql99标准【推荐】
(二) 按功能分类:
内连接:
等值连接
非等值连接
自连接
外连接:
左外连接
右外连接
全外连接(MySQL不支持)
交叉连接
一、sql92标准
1、等值连接
语法:
select 查询列表
from 表1 别名,表2,别名
where 表1.key = 表2.key
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】
特点:等值标准:
(1)多表等值连接的结果为多表的交集部分
(2)n表连接,至少需要n-1个连接条件
(3)多表的顺序没有要求
(4)一般需要为表起别名
(5)可以搭配前面介绍的所有子句使用,比如排序、分组、筛选
例1:查询女神名和对应的男神名
select name,boyname
from boys, beauty
where beauty.boyfriend_id = boys.id;
例2:查询员工名和对应的部门名
select last_name, department_name
from employee, department
where employee. 'department_id'=department.'department_id';
为表起别名
(1)提高语句简洁度
(2)区分多个重名的字段
注:如果为表起了别名,则查询的字段就不能使用原来的表名去限定
例:查询员工号,工种号,工种名
select e.last_name, e.job_id, j.job_title
from employee e, job j
where e.'job_id' = j.'job_id';
两个表的顺序可以调换
可以添加筛选
例1、查询有奖金的员工名,部门名
select last_name,department_name
from employee e, department d
where e.'department_id' = d.'department_id'
and e.'commssion_pct' is not null;
例2、查询城市名中第二个字段为o的部门名和城市名
select department_id, city
from department d, location l
where d.'location_id' = l.'location_id'
and city like '_o%';
可以加分组
例1、查询每个城市的部门数
select count(*) 个数,city
from department d, location l
where d.'location_id' = l.'location_id'
group by city;
可以加函数
例:查询每个工种的工种名和员工的个数,并且按员工个数降序
select job_id, count(*)
from employee e, job.j
where e.'job_id' = j.'job_id'
group by job_title
order by count(*) desc;
可以实现三表连接
例:查询员工名,部门名和所在的城市
select last_name,department_name,city
from employee e, department d, location l
where e.'department_id' = d.'department_id'
and d.'department_id' = l.'department_id'
and city like'a%';
2、非等值连接
语法:
select 查询列表
from 表1 别名,表2,别名
where 非等值的连接条件
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】
例1:查询员工的工资和工资级别
select salary,grade_level
from employee e, grade g
where salary between g.'loweat_sal' and g.'higheat_sal'
and g.'grade_level' = 'A';
3、自连接
语法:
select 查询列表
from 表 别名1,表,别名2
where 等值的连接条件
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】
例:查询员工名和上级的名称
select e. employee_id, e.last_name, m.memployee_id, m.last_name
from employee e, employee m
where e.'manager_id' = m.'manger_id';
二、sql99语法
语法:
select 查询列表
from 表1 别名 【连接类型】
join 表2 别名
on 连接条件
【where 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序列表】;
内连接:inner
外连接(左外:left【outer】、右外:right【outer】、全外:full【outer】)
交叉连接:cross
(一)内连接
1、语法:
select 查询列表
from 表1 别名
【inner】 join 表2 别名 on 连接条件
where 筛选条件
group by 分组列表
having 分组后的筛选
order by 排序列表
limit 子句;
2、分类:
等值连接
非等值连接
自连接连接
3、特点:
(1)添加排序、分组、筛选
(2)inner可以省略
(3)筛选条件放在where后面,连接条件放在on后面,提高分离性,便于阅读
(4)inner join连接和sql92语法中的等值连接效果是一样的,都是查询多表的交集
(表的顺序可以调换
内连接的结果=多表的交集
n表连接至少需要n-1个连接条件)
1、等值连接
例1:查询员工名、部门名
select last_name, department_name
from employee e
inner join department d
on e.'department_id' = d.'department_id';
例2:查询名字中包含a的员工名和工种名(添加筛选)
select last_name, job_title
from employee e
inner join job j
on e.'job_id' = j.'job_id'
where e.'last_name' like '%a%';
例3、查询部门个数大于3的城市名和部门个数(添加分组+筛选)
(1)查询每个城市的部门个数
(2)在(1)的基础上筛选满足条件
select city,count(*) 部门个数
from department d
inner join location l
on d.'location_id' = l.'location_id'
group by city
having count(*)> 3;
例4、查询哪个部门的员工个数>3的部门名和员工个数,并按个数降序(添加排序)
(1)查询每个部门的员工个数
select count(*),department_name
from employee e
inner join department d
on e.'department_id' = d.'department_id'
group by department;
(2)在(1)的结果上筛选员工个数>3的记录,并排序
select count(*)个数,department_id
from employee e
inner join department d
on e.'department_id' = d.'department_id'
group by department_name
having count(*)>3
order by count(*) desc;
例5、查询员工名、部门名、工种名,并按部门名排序
select last_name, department_name, job_title
from employee e
inner join department d on e.'department_id' = d.'department_id'
inner join job j on e.'job_id' = j.'job_id'
order by department_name desc;
2、非等值连接
例:查询员工的工资级别
select salary,grade_level
from employee e
join job_graded g
on e.'salary' between g.'lowest_sal' and g.'hightest_sal';
3、自连接
例:查询员工的名字、上级的名字
select e.last_name, m.last_name
from employee e
join employee m
on e.'manager_id' = m.'employee_id';
(二)外连接
1、应用场景:用于查询一个表中有,另一个表没有的记录
2、语法:
select 查询列表
from 表1 别名
left/right/full【outer】 join 表2 别名 on 连接条件
where 筛选条件
group by 分组列表
having 分组后的筛选
order by 排序列表
limit 子句;
3、特点:
(1)外连接的查询结果为主表中的所有记录,如果从表中有和它匹配的,则显示匹配的值;如果从表中没有和它匹配的,则显示null
外连接查询结果=内连接结果+主表中有而从表中没有的记录
(2)左外连接:left join 左边的是主表
右外连接:right join 右边的是主表
全外连接:full join 两边都是主表
(3)左外和右外交换两个表的顺序,可以实现同样的效果
(4)全外连接=内连接的结果+表1中有但表2中没有的+表2中有但表1中没有的
(5)一般用于查询除了交集部分的剩余的不匹配的行
例1:查询男友不在男生表的女生名
左外连接:
select b.name, bo.*
from beauty b
left outer join boy bo
on b.'boyfrind_id' = bo.'id';
右外连接:
select b.name, bo.*
from boy bo
left outer join beauty b
on bo.'id' = b.'boyfrind_id';
例2:查询哪个部门没有员工
左外:
select d.*, e.employee_id
from department d
left outer join employee e
on d.'department_id' = e.'department_id'
where e.'employee_id' is null;
右外:
select d.*, e.employee_id
from employee e
left outer join department d
on d.'department_id' = e.'department_id'
where e.'employee_id' is null;
全外连接:
use girl
select b.*, bo.*
from beauty b
full outer join boy bo
on b.'boyfrind_id' = bo.id;
交叉连接:
语法:
select 查询列表
from 表1 别名
cross join 表2 别名;
特点:类似于笛卡尔乘积
select b.*, bo.*
from beauty b
cross join boy bo;
sql92 PK sql99
功能:sql99支持的较多
可读性:sql99实现连接条件和筛选条件的分离,可读性较高
进阶七:子查询
一、含义
出现在其他语句内部的select语句,称为子查询或内查询
外面的语句可以是inner,update,delete,select等,一般select作为外面语句较多
外部的查询语句为select语句,称为主查询或外查询
二、分类
1、按子查询出现的位置:
select后面:仅支持标量子查询
from后面:支持表子查询
where后面或having后面:支持标量子查询(单行)/列子查询(多行)/行子查询
exists后面(相关子查询):支持表子查询
2、按结果集的行列数不同:
标量子查询(结果集只有一行一列)
列子查询(结果集只有一列多行)
行子查询(结果集只有一行多列)
表子查询(结果集一般为多行多列)
一、where后面或having后面
1、标量子查询(单行子查询)
2、列子查询(多行子查询)
3、行子查询(多列多行)
特点:
(1)子查询放在小括号内
(2)子查询一般放在条件的右侧
(3)标量子查询,一般搭配着单行操作符使用:> < >= <= = <>
列子查询,一般搭配着多行操作符使用:in any/some all
(4)子查询的执行优先于主查询执行,主查询的条件用到了子查询的结果
1、标量子查询
例1:谁的工资比Abel高
(1)查询Abel的工资
select salary
from employee
where last_name = ‘Abel’;
(2)查询员工的信息,满足salary>(1)结果
select * from employee
where salary>{
select salary from employee
where last_name = 'Abel'
};
例2:返回job_id与141号员工相同,salary比143号员工多的员工姓名,job_id和工资
(1)查询141号员工的job_id
select job_id from employee where employee_id = 141
(2)查询141号员工的salary
select salary from employee where employee_id = 143
(3)查询员工的姓名,job_id和工资,要求job_id=(1)且salary>(2)
select last_name, job_id, salary from employee
where job_id = {
select job_id from employee where employee_id = 141
} and salary > {
select salary from employee where employee_id = 143
};
非法使用标量子查询
2、列子查询(多行子查询)
例1:返回location_id是1400或1700的部门中的所有员工姓名
(1)查询location_id是1400或1700的部门编号
select distinct department_id from department
where location_id in(1400,1700)
(2)查询员工姓名,要求部门号是(1)列表中的某一个
select last_name from employee
where department_id in(
select distinct department_id from department
where location_id in(1400,1700)
);
3、行子查询(结果一行多列或多行多列)
例:查询员工编号最小且工资最高的员工信息
select * from employee
where (employee_id, salary) = (
select min(employee_id), max(salary) from employee
);
(1)查询最小的员工编号
select min(employee_id) from employee
(2)查询最高工资
select max(salary) from employee
(3)查询员工信息
select * from employee
where employee_id = (
select min(employee_id) from employee
) and salary = (
select max(salary) from employee
);
二、select后面
注:仅支持标量子查询
例:查询每个部门的员工个数
select d.*, (
select count(*) from employee e
where e.department_id = d.'department_id'
)
from department d;
三、from后面
注:将子查询结果充当一张表,要求必须起别名
例:查询每个部门的平均工资的工资等级
(1) 查询每个部门的平均工资
select avg(salary), department_id
from employee
group by department_id
(2) 连接(1)的结果集和job_grade表,筛选条件平均工资 between lowest_sal and highest_sal
select ag_dep.*, g.'grade_level'
from (
select avg(salary), department_id from employee
group by department_id
) ag_dep
inner join job_grade g
on ag_dep.ag between lowest_sal and highest_sal;
四、exists后面(相关子查询)
语法:
exists(完整的查询语句)
结果;
结果为1或0
例:查询有员工的部门名
select department_name
from department d
where exists(
select * from employee e
where d,'department_id' = e.'department_id'
);
进阶八:分页查询
一、应用场景
当要显示的数据,一页显示不全,需要分页提交sqli请求
二、语法
select 查询列表
from 表
【join type】 join 表2
on 连接条件
where 筛选条件
group by 分组字段
having 分组后的筛选
order by 排序的字段
limit offset, size;
offset :要显示条目的起始索引(起始索引从0开始)
size:要显示大的条目个数
三、特点
1、limit 语句放在查询语句的最后
2、公式:要显示的页数为page,每页的条目数size
select 查询列表
from 表
limit (page-1)*size,size;
例1:查询前五条员工信息
select * from employee limit 0,5;
select * from employee limit 5;
例2:查询第11条-第25条
select * from employee limit 10,15;
例3:有奖金的员工信息,并且工资较高的前十名显示出来
select * from employee
where commission_pct is not null
order by salary desc
limit 10;
进阶九:联合查询
union:联合、合并,将多条查询语句的结果合并成一个结果
引例:查询部门编号>90的邮箱包含a的员工信息
1、select *
from employee
where email like'%a%' or department_id>10;
2、select *
from employee
where email 立刻 '%a%';
select * from employee where department_id > 90;
union
1、语法:
查询语句1
union 【all】
查询语句2
union 【all】
……;
2、意义:
(1)将一条比较复杂的查询语句拆分成多条语句
(2)适用于查询多个表的时候,查询的列基本一致
3、应用场景:要查询的结果来自于多个表,且多个表中没有直接的连接关系,但查询的信息一致时
4、特点:
(1)要求多条查询语句的查询列数是一致的
(2)要求多条查询语句的查询的每一列的类型和顺序最好一致
(3)union关键字默认去重,用union all可以包含重复项
例:查询中国用户男性的信息和外国用户中男性的信息
select id,name,csex from t_ca where csex = '男'
union
select t_id,t_name,t_gender from t_ua where t_gender = 'male';
查询总结:
语法:
select 查询列表 7
from 表1 别名 1
连接类型 join 表2 2
on 连接条件 3
where 筛选 4
group by 分组列表 5
having 筛选 6
order by 排序列表 8
limit 起始条目索引,条目数; 9
DML语言
数据操作语言
插入:insert
修改:update
删除:delete
一、插入语句
方式一:经典的方式
语法:
insert into 表名(字段名,……) values(值1,……);
注:
1、插入的值的类型要与字段的类型一致或兼容
insert into beauty(id, name, sex,photo,2id)
value(13, '小王', '女',null,2);
2、假如表中有可以为null的字段,可通过以下两种方式插入null值
(1)字段和值都省略
(2)字段写上,值使用null
不可以为null的列必须插入值,可以为null的列如何插入值?
方式一:
insert into beauty(id, name, sex,photo,2id)
value(13, '小王', '女',null,2);
方式二:
insert into beauty(id, name, sex,2id)
value(13, '小王', '女',2);
3、字段的个数和顺序不一定与原始表中的字段个数和顺序一致,但必须保证值和字段一一对应
4、字段和值的个数必须一致
5、可以省略字段名,默认所以列,而且列的顺序和表中列的顺序一致
方式二:
语法:
insert into 表名
set 字段=值, 字段=值,……
例:insert into beauty
set id=19,name=‘小王’,phone=‘123’;
两种方式PK
1、方式一支持一次插入多行,方式二不支持
insert into beauty
values(13, '小王', '女',null,2),
(14, '打王', '女',null,2),
(15, '去王', '女',null,2);
2、方式一支持子查询,方式二不支持
二、修改语句
1、修改单表的记录
语法:
update 表名
set 列=新值,列=新值,……
where 筛选条件;
例1:修改beauty表中姓王女神的电话为123
update beauty set phone=‘123’
where name like ‘王%’;
2、修改多表的记录
语法:
sql92语法:
update 表1 别名,表2 别名
set 列=值,……
where 连接条件
and 筛选条件;
sql99语法:
update 表1 别名
inner|left|right join 表2 别名
on 连接条件
set 列=值,……
where 筛选条件;
例:修改小张女朋友的手机号为789
update boys bo
inner join beauty b on bo.'id' = b.'boyfriend_id'
set b.'phone' = '789'
where bo.'boyname'='小张';
三、删除语句
方式一:delete
语法:
1、单表的删除
delete from 表名 where 筛选条件;
例1:删除手机号以9结尾的女生信息
delete from beauty where phone like‘&9’;
2、多表的删除
sql92语法:
delete 表1的别名,表2的别名
from 表1 别名, 表2 别名
where 连接条件
and 筛选条件;
sql99语法:
delete 表1的别名,表2的别名
from 表1 别名
inner|left|right join on 表2 别名
where 连接条件
and 筛选条件;
例:删除小张女朋友的信息
delete b
from beauty b
inner join boys bo on b.'boyfriend_id' = bo.'id'
where bo.'boyname' = '小张';
方式二:truncate(一删全删)
语法:
truncate table 表名;
例:将魅力值大于100的男生删除
truncate table boys;
delete PK truncate
1、delete可以加where条件,truncate不能加
2、truncate删除,效率高
3、假如要删除的表中的表中有自擅长列,
如果用delete删除后,再插入数据,自增长列的值从断点开始,
如果用truncate删除后,再插入数据,自增长列的值从1开始
4、truncate删除没有返回值,delete删除有返回值
5、truncate删除不能回滚,delete删除可以回滚
DDL语言
数据定义语言
库和表的管理
一、库的管理
创建、修改、删除
二、表的管理
创建、修改、删除
创建:create
修改:alter
删除:drop
一、库的管理
1、库的创建
语法:
create database 【if not exists】库名 【character set 字符集名】;
例:创建库books
create database books;
2、库的修改
rename database books to 新库名;(基本不用)
更改库的字符集、
alter database 库名 character set 字符集名;
alter database books character set gbk;
3、库的删除
drop database 【if exists】 库名;
二、表的管理
1、表的创建
语法:
create table 【if not exists】表名(
列名 列的类型【(长度)约束】,
列名 列的类型【(长度)约束】,
……
列名 列的类型【(长度)约束】
);
例:创建表book
create table if exists book(
id int,
bname varchar(20),
price double,
author varchar(20)
publishdate datetime
);
2、表的修改
alter table 表名 add|drop|modify|change column 列名 【列类型 约束】;
(1)修改列名
alter table book change column publishdate pubdate datetime;
(2)修改列的类型或约束
alter table book modify column pubdate timestamp;
(3)添加新列
alter table author add column annual double;
(4)删除列
alter table author drop column annual;
(5)修改表名
alter table author rename to book_abc;
3、表的删除
drop table 【if exists】 表名;
通用的写法:
drop database if exists 旧库名;
create database 新库名;
drop table if exists 旧表名;
create table 表名();
4、表的复制
insert into author values
(1,‘小王’,‘西安’),
(2,‘小李’,‘延安’),
(3,‘小田’,‘渭南’),
(4,‘小张’,‘安康’);
1、仅复制表的结构
create table copy表名 like author旧表;
2、复制表的结构+数据
create table copy2 select * from author;
只复制部分数据
create table copy3 select id, au_name
from author where nation='西安';
仅复制某些字段
create table copy4
select id, au_name
from author where 0;
常见的数据类型
数值型:
整型
小数:定点数、浮点数
字符型:
较短的文本:char、varchar
较长的文本:text、blob(较长的二进制数据)
日期型
一、整型
分类:
tinyint(1)、smallint(2)、mediumint(3)、int/integer(4)、bigint(8)
特点:
(1)如果不设置无符号还是有符号,默认是有符号,如果想设置有符号,需要添加unsigned关键字
(2)如果插入的数值超出了整型的范围,会报out of range异常,并且插入临界值
(3)如果不设置长度,会有默认的长度
长度代表了显示的最大宽度,如果不够会用0在左边填充,但必须搭配zerofill使用
1、如何设置无符号和有符号
create table tab_int(
t1 int,
t2 int unsigned
);
二、小数
分类:
1、浮点型
float(M,D)
double (M,D)
2、定点型
dec(M,D)
decimal(M,D)
特点:
(1)M和D
M:整数部位+小数部位
D:小数部位
如果超过范围,则报out or range异常,并插入临界值
(2)M和D都可以省略,
如果是decimal,则M默认为10,D默认为0
如果是float和double,则会根据插入的数值的精度来决定精度
(3)定点型的精确度较高,如果要求插入数值的精确度较高如货币运算等则考虑使用
原则:
所选择的类型越简单越好,能保存数值的类型越小越好
三、字符型
较短的文本:char、varchar
特点:
写法 | M的意思 | 特点 | 空间的耗费 | 效率 | |
---|---|---|---|---|---|
char | char(M) | 最大的字符数,可以省略,默认为1 | 固定长度的字符 | 比较耗费 | 高 |
varchar | varchar(M) | 最大的字符数,不可以省略 | 可变长度的字符 | 比较节省 | 低 |
较长的文本:text、blob(较长的二进制数据)
四、日期型
分类:
date只保存日期
time只保存时间
year只保存年
datetime保存日期+时间
timestamp保存日期+时间
常见约束
含义:一种限制,用于限制表中的数据,为了保证表中数据的准确和可靠性
分类:六大约束
not null:非空,用于保证该字段的值不能为空,比如姓名、学号等
default:默认,用于保证该字段有默认值,比如性别
primary key:主键,用于保证该字段的值具有唯一性,并且非空,比如字号、员工编号等
unique:唯一,用于保证该字段的值具有唯一性,所以为空,比如路径号
check:检查约束【MySQL中不支持】
foreign key:用于限制两个表的关系,用于保证该字段的值必须来自于主表的关联列的值,在从表添加外键约束,用于引用主表中某列的值,比如主表的专业编号,员工表的部门编号,员工表的工种编号
添加约束的时机:
1、创建表时
2、修改表时
约束的添加分类:
列级约束:六大约束语法上都支持,但外键约束没有效果
表级约束:除了非空、默认,其他的都支持
主键和唯一对比:(取前两行)
外键:
1、要求在从表设置外键关系
2、从表的外键列的类型和主表的关联列的类型要求一致或兼容,名称无要求
3、主表的关联列必须是一个key(一般是主键或唯一键)
4、插入数据时,先插入主表,再插入从表
删除数据时,先删除从表,再删除主表
可以通过以下两种方式删除主表的记录
方式一:级联删除
alter table stuinfo
add constraint fk_stu_major foreion key(majorid) peferences major(id) on delete cascade;
方式二:级联置空
alter table stuinfo
add constraint fk_stu_major foreion key(majorid) peferences major(id) on delete set null;
一、创建表时添加约束
1、添加列级约束
语法:
直接在字段名和类型的后面追加约束类型即可
只支持:默认、非空、主键、唯一
create table 类名(
字段名 字段类型 not null,#非空
字段名 字段类型 primary key,#主键
字段名 字段类型 unique,#唯一
字段名 字段类型 default 值,#默认
constraint 约束名 foreign(字段名) references 主表(被引用例)
);
注:
支持类型 | 可以起约束名 | |
---|---|---|
列级约束 | 除了外键 | 不可以 |
表级约束 | 除了非空和默认 | 可以,但对主键无效 |
列级约束可以在一个字段上追加多个,中间用空格隔开,没有顺序要求
例:
create table stuinfo(
id int primary key ,#主键
stuinfo varchar(20) not ull,#非空
gender char(1) check(gender = '男' or gender = '女'),#检查
seat int unique,#唯一
age int default 18,#默认约束
majorid int foreign references major(id) #外键
);
create table major(
id int primary key,
majorname varchar(20)
);
2、添加表级约束
语法:在各个字段的最下面
【constraint 约束名】 约束类型(字段名)
create table stuinfo(
id int ,
stuinfo varchar(20) ,
gender char(1) ,
seat int,
age int ,
majorid int ,
constraint pk primary key(id),#主键
constraint uq unique(seat),#唯一键
constraint ck check(gender ='男' or gender = '女'),#检查
constraint fk_stuinfo_major foreign key(majorid) references major(id) #外键
);
通用的写法:
create table if not exists stuinfo(
id int primary key,
stuname varchar(20) not full,
sex char(1),
age int default 18,
seat int unique,
majorid int,
constraint fk_stuinfo_major foreign key(majorid) references major(id)
);
二、修改表时添加约束
1、添加列级约束
alter table 表名 nodify column 字段名 字段类型 新约束;
2、添加表级约束
alter table 表名 add 【constraint 约束名】约束类型(字段名) 【外键的引用】;
1、添加非空约束
alter table stuinfo nodify column stuname varchar(20) not full;
2、添加默认约束
alter table stuinfo nodify column age int default 10;
3、添加主键
(1)列级约束
alter table stuinfo nodify column id int primary key;
(2)表级约束
alter table stuinfo nodify add primary key(id);
4、添加唯一
(1)列级约束
alter table stuinfo nodify column seat int unique;
(2)表级约束
alter table stuinfo nodify add unique(seat);
5、添加外键
alter table stuinfo nodify add foreign key(majorid) references major(id);
三、修改表时删除约束
1、删除非空约束
alter table stuinfo nodify column stuname varchar(20) null;
2、删除默认约束
alter table stuinfo nodify age int;
3、删除主键
alter table stuinfo drop primary key;
4、删除唯一
alter table stuinfo drop index seat;
5、删除外键
alter table stuinfo drop foreign key majorid;
标识列
又称为自增长列,
含义:可以不用手动的插入值,系统提供默认的序列值
特点:
1、标识列必须和主键搭配吗?不一定,但要求是一个key
2、一个表中可以有多少个标识列?至多一个!
3、标识列的类型只能是数值型
4、标识列可以通过set auto_increment_increment=3;设置步长,可以通过手动插入值,设置起始值
一、创建表时设置标识列
create table tab_identity(
id int primary key auto_increment,
name varchar(20)
);
二、修改表时设置标识列
alter table tab_identity modify column id int primary key auto_increment;
三、修改表时删除标识列
alter table tab_identity modify column id int ;
TCL语言的学习
transaction control language 事务控制语言
事务
一个或一组SQL语句组成一个执行单元,这个执行单元要么全部执行,要么全部不执行
存储引擎:
事务的ACID属性:
1、原子性:一个事务不可再分割,要么执行,要么都不执行
2、一致性:一个事务执行会使数据从一个一致状态切换到另一个一致状态
3、隔离性:一个事务的执行不受其他事务的干扰
4、持久性:一个事务一旦提交,则会永久的改变数据库的数据
事务的创建
隐式事务:事务没有明显的开启和结束的标记。如insert、update、delete语句
显式事务:事务有明显的开启和结束的标记。前提:必须先设置自动提交功能为禁用
使用显示事务:
步骤1:开启事务
set autocommit=0;
start transaction;可选的
步骤2:编写事务中的SQL语句
select insert / update / delete
语句1;
语句2;
……
设置回滚点:savepoint 回滚点名;
步骤3:结束事务
commit;提交事务
rollback;回滚事务
回滚到指定的地方:rollback to 回滚点名;
并发事务
1、事务的并发问题是如何发生的?
多个事务 同时 操作 同一个数据库的相同数据时
2、并发问题都有哪些?
脏读:一个事务读取了其他事务还没有提交的数据,只是读到其他事务“更新”的数据
不可重复读:一个事务多次读取,结果不一样
幻读:一个事务读取了其他事务还没有提交的数据,只是读到其他事务“插入”的数据
3、如何解决并发问题?
通过设置隔离级别来解决并发问题
4、事务的隔离级别:
脏读 | 不可重复读 | 幻读 | |
---|---|---|---|
read uncommitted | √ | √ | √ |
read committed | × | √ | √ |
repeatable read | × | × | √ |
serializable | × | × | × |
MySQL中默认第三个隔离级别repeatable read
Oracle中默认第二个隔离级别read committed
查看隔离级别
select @@tx_isolation;
设置隔离级别
set session|global transaction isolation level 隔离级别;
视图
含义:
虚拟表,和普通表一样使用
MySQL51版本出现的新特性,是通过表动态生成的数据
比如:舞蹈班和普通版的对比
视图的优点:
1、简化SQL语句
2、提高了SQL的重用性
3、保护基表的数据,提高了安全性
一、创建视图
语法:
create view 视图名
as
查询语句;
二、视图的修改
方式一:
语法:
create or replace view 视图名
as
查询语句;
方式二:
语法:
alter view 视图名
as
查询语句;
三、删除视图
语法:
drop view 视图名,视图名,……;
四、查看视图
desc 视图名;
show create view 视图名;
五、视图的更新
具备以下特点的视图不允许更新:
1、包含以下关键字的SQL语句:分组函数、distinct、group by、having、union或union all
2、常量视图
3、select中包含子查询
4、join
5、from一个不能更新的视图
6、where子句的子查询引用了from子句中的表
视图PK表:
创建语法的关键字 | 是否实际占用物理空间 | ||
---|---|---|---|
视图 | create view | 只是保存了SQL逻辑 | 增删改查,一般不能增删改 |
表 | create table | 保存了数据 | 增删改查 |