数据库学习笔记
(尚硅谷2017版Mysql的数据库学习笔记)
一、数据库的概念
DB: database
DBMS: database management System(mysql\oracle\db2\sqlserver)
SQL: Structure Query Language 结构化查询语句(几乎适用于所有DBMS)
二、DBMS的安装和使用
基于客户机:MySQL等。
基于共享文件系统:Access
三、MySQL的启动和停止
1、通过服务启动和停止
2、通过管理员模式下的cmd窗口启动和停止
:net stop MySQL80 停止服务
:net start MySQL80 启动服务
3、进入和退出
a、mysqlserver下的command client
b、系统cmd:mysql -h localhost -P 3306(端口号) -u root -p(粗斜体可选可不选,链接远端时必须)
4、环境变量
a、MYSQL_HOME下C:\Program Files\MySQL\MySQL Server 8.0
b、PATH下%MYSQL_HOME%\bin
四、基础的语法
1、show databases;
2、use test;
3、show tables;查看当前库中的表 show tables from mysql;查看其他库中的表
4、select database();
5、create table onetable(
id int,
name varchar(20));
6、desc onetable;
7、selcet *from onetable;
8、insert into onetable (id,name) values(1,‘lilei’);
9、select version(); / msyql version;
五、SQL语法规范
1、不区分大小写,但建议关键字大写,表名列名小写。
2、每条命令用;结尾。
3、每条命令根据需要可以缩进或者换行。
4、注释 单行#注释文字或者–注释文字
多行/*注释文字
*/
六、DQL语言 (均以Mysql为载体的标准)
1、基础查询(使用练习表myemployees)
a、查询基本结构
select 查询列表 from 表名;(查询列表可以是表中的字段、常量值、表达式、函数)
b、单一字段查询
select last_name from employees;
c、多个字段查询
select last_name,salary,email from employees;(手动输入)
SELECT
`hiredate``department_id``manager_id``salary``job_id``phone_number``email``last_name``first_name`
from
employees;/*````是1旁边的符号,不是单引号,系统自动添加的。点击栏位名自动添加该字段*/
d、所有字段查询
select * from employees;/* *号表示查询所有,显示时不改变表结构*/
e、查询常量池
select 100;
select 'john';
f、查询表达式
select 100%98;
g、查询函数
select version();
h、起别名(注意若别名若是sql中的关键字,要用引号标出,负责系统无法识别该别名)
/*方式一*/
select 100%98 AS 结果;
select last_name AS 姓,first_name AS 名 FROM employees;
/*方式二*/
select last_name 姓,first_name 名 FROM employees;
i、去重 distinct
select distinct department_id From employees;
j、+的作用(仅仅作为运算符)
select 100+99;
select '123'+99;/*系统会试图转换字符型数值为数值型*/
select 'son'+99:/*转换失败,该字符型数值等价于0*/
select null+0;/*只要有一方是null,输出null*/
select last_name+first_name AS 姓名 from employees;/*无结果*/
k、concat拼接函数
select concat('a','b','c') AS 结果;
2、条件查询
a、基本查询结构
select
查询列表
from
表名
where
筛选条件;
b、条件运算符
>大于 <小于 =等于 !=不等于<> >= <=
SELECT
*
FROM
employees
WHERE
salary>12000;
SELECT
last_name,
department_id
FROM
employees
department_id<>90;
c、逻辑运算符(与或非)
&& || !
and or not
d、模糊查询
like, between and, in, is null/is not null
e、逻辑表达式筛选
select
last_name,salary,commission_pct
from
employees
where
salary>=10000 and salary<=20000;
SELECT
*
FROM
employees
WHERE
NOT(department_id>=90 AND department_id<=110) OR salary>15000;
f、模糊查询
SELECT
*
FROM
employees
WHERE
last_name LIKE '%a%';
/*%是通配符,代表任意多个字符,包含0个字符。*/
SELECT
*
FROM
employees
WHERE
last_name LIKE '_a_';
/*_代表任意单个字符*/
SELECT
last_name
FROM
employees
WHERE
last_name LIKE '_\_%';
/*\代表转译,第二个_不在表示单个字符*。后加ESCAPE可以自定义转译字符,最后一句也可以写作如下 */
last_name LIKE '_$_%' ESCAPE '$';
SELECT
*
FROM
employees
WHERE
employee_id BETWEEN 100 AND 120;/*前后数字分大小*/
SELECT
last_name,
job_id
FROM
employees
WHERE
job_id IN ('IT_DROP','AD_VP','AD_PRES');/*in关键字的使用*/
SELECT
last_name,
job_id
FROM
employees
WHERE
commission_pct IS NULL;/*is null用法,不可用‘=’来判断null值*/
g、安全等于
SELECT
last_name,
job_id
FROM
employees
WHERE
commission_pct <=> NULL;/*安全等于才是等于,但是不推荐使用*/
3、排序查询
a、基本格式(order by 一般放在最后除了 limit字句)
select
查询列表
from
表
where
筛选条件
order by
排序列表
/*基本格式*/
select * from employees order by salsary DESC;
/*asc/desc代表升序/降序,不写的话默认的是升序*/
select
*
from
employees
where
department_id>=90
order by
hiredate ASC;
/*编号大于90的员工信息,并按入职时间升序排序*/
select
*,salsary*12*(1+IFNULL(commission_pct,0)) AS 年薪
from
employees
order by 年薪 desc;
/*order支持别名*/
select
length(last_name) AS 字节长度,last_name,salsary
from
employees
order by lengeth(last_name) DESC;
/*按姓名的长度显示员工信息,即按函数排序*/
select
*
from
employees
order by
salsary ASC,employee_id DESC;
/*按工资升序(第一条件)和员工ID降序(第二条件)排序*/
b、查询练习
SELECT
last_name,salary
FROM
employees
WHERE
salary NOT BETWEEN 8000 AND 17000
ORDER BY
salary ;
SELECT
*,LENGTH(email)
from
employees
where
email like '%e%'
order by
length(email) DESC,department_id ASC;
4、常见函数
包含:字符函数、数学函数、日期函数、其他函数、流程控制函数
好处:隐藏了实现细节、提高代码的重用性
特点:函数名和函数功能
分类:单行、分组
单行函数:
a、字符函数
length(长度)
select length('john');
concat(拼接)
select cancat(last_name,'_',first_name) 姓名 from employees;
upper、lower(大小写)
select upper('john');
select concat(upper(last_name),'_',lower(first_name)) 姓名 from employees;
/*姓变大写,名变小写,然后拼接*/
substr、substring(截取)
select substr('我的学习记录',5) AS out_put;/*输出记录*/
select substr('我的学习记录',4,5) AS out_put;/*输出习记*/
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2))) AS out_put
FROM
employees;
instr(返回第一次出现的索引位置)
select instr('我的学习记录','学习') AS out_put;/*输出3*/
select instr('我的学习记录','他的') AS out_put;/*输出0*/
trim(去先后的空格,若要指定去除的字符,其字符视为一个整体来去除)
select length(trim(' 学习 ')) AS out_put;/*输出6*/
select trim('x' from 'xxxxxxxx学习xxxxxxxx') AS out_put;/*输出学习*/
select trim('xx' from 'xxxxxxx学习xxxxxxxx') AS out_put;/*输出x学习*/
lpad(用指定的字符实现左填充指定长度)
select LPAD('我的学习记录',10,'*') AS OUT_PUT;/* 输出****我的学习记录 */
select LPAD('我的学习记录',5,'*') AS OUT_PUT;/* 输出我的学习记,字符数量不够5,从右侧删除字符直至5 */
rpad(用指定的字符实现右填充指定长度)
select RPAD('我的学习记录',10,'*') AS OUT_PUT;/* 输出我的学习记录**** */
select RPAD('我的学习记录',5,'*') AS OUT_PUT;/* 输出我的学习记,字符数量不够5,依然从右侧删除字符直至5 */
replace(替换)
select replace('我的学习记录','记录','笔记') AS out_put;/*输出我的学习笔记*/
b、数学函数
round(四舍五入)
select round(1.45);/*输出1*/
select round(1.458,2);/*小数点后保留两位,输出1.46*/
ceil(向上取整)
select ceil(1.01);/*输出2*/
select ceil(-1.01);/*输出-1*/
floor(向下取整)
selce floor(9.99);/*输出9*/
selce floor(-9.99);/*输出-10*/
truncate(截断)
select TRUNCATE(1.699999,1);/*输出1.6*/
mod(取余)
select mod(10,3);/*输出1*/
select mod(10,-3);/*输出-1*/
select mod(-10,-3);/*输出-1*/
c、日期函数
now(返回当前系统日期)
select now();
curdate(返回系统当前日期,不包含时间)
select curdate();
curtime(返回系统当前时间,不包含日期)
select curtime();
year
select year(now()) 年;
select year('1994-7-7') 年;
select YEAR(hiredate) 入职年 FROM employees;
month
select MONTH(NOW()) 月;
select MONTHNAME(NOW()) 月;
select MONTH(hiredate) 入职月 FROM employees;
select MONTHNAME(hiredate) 入职月 FROM employees;
str_to_date(字符转日期)
select str_to_date('1998-3-3','%Y-%c-%d')AS out_put;
select
*
from
employees
where
hiredate = STR_TO_DATE('4-3 1992','%c-%d %Y');
/*用于解析一些日期格式,用以匹配数据库里的信息*,此时输入的4-3 1992日期格式便能和库中的1992-4-3相匹配*/
date_foramt(日期转字符)
select date_format(now(),'%y年%m月%d日') AS out_put;
select
last_name,DATE_FORMAT(hiredate,'%m月/%d日 %y年')入职日期
from
employees
where
commission_pct is not null;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lk7p0c7q-1591347438105)(C:\Users\Administrator\Desktop\截图文件\mysql\日期函数.png)]
d、其他函数————>基础语法
e、流程控制函数
if(效果等同if...else)
select IF(10>5,'大','小');
select
LAST_NAME,commission_pct,IF(commission_pct is null,'没奖金','有奖金') AS 备注
from
employees;
case函数
(基本格式1)
case 要判断的变量或表达式
when 常量1 then 要显示的值1或这语句1;
when 常量2 then 要显示的值2或这语句2;
when 常量3 then 要显示的值3或这语句3;
...
else 要显示的值n或语句n;
end
eg1:
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 employees;
(基本格式2)
case
when 条件1 then 要显示的值1(或语句1;)
when 条件2 then 要显示的值2(或语句2;)
...
else 要显示的值n(或语句n;)
end
eg2:
SELECT salary,
CASE
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'
END AS 工资级别
FROM employees;
分组函数:
sum:求和, avg:平均值, max最大值, min最小值, count计算个数,都忽略NULL值。
SELECT SUM(salary) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT MIN(salary) FROM employees;
SELECT MAX(salary) FROM employees;
SELECT COUNT(salary) FROM employees;
SELECT
SUM(salary) 和,
ROUND(AVG(salary),2) 平均,/*以上一般处理数值型*/
MIN(salary) 最低,sql
MAX(salary) 最高,
COUNT(salary) 个数/*以上可以处理任何类型*/
FROM employees;
和DISTINCT组合使用
SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;
SELECT COUNT(DISTINCT salary),COUNT(salary) FROM employees;
DATEDIFF(求日期的差值)
SELECT DATEDIFF (MAX(hiredate),MIN(hiredate)) AS DIFFRENCE
FROM employees;
5、分组查询 group by
a、基本格式
select 分组函数
from 表
【where 筛选条件】
group by
【order by】
b、相关练习
select max(salary),job_id
from employees
group by job_id;
/*查询每个工种的最高工资*/
select count(*),location_id
from departments
group by location_id;
/*查询每个位置的部门个数*/
select avg(salary),department_id
from employees
where email like '%a%'
group by department_id;
/*查询邮箱中包含a字符的,每个不萌的平均工资*/
select max(salary),manager_id
from employees
where commission_pct is not null
group by manager_id;
/*查询有奖金的每个领导手下员工的最高工资*/
select count(*),department_id
from employees
group by department_id
having count(*)>2;
/*查询哪个部门的员工个数大于2,添加分组后的筛选,最后用having关键字添加条件*/
/*①查询每个工种有奖金的员工夫人最高工资*/
select max(salary),job_id
from employees
where commission_pct is not null
group by job_id
/*②根据①的结果继续筛选>12000*/
select max(salary),job_id
from employees
where commission_pct is not null
group by job_id
having max(salary)>12000;
/*查询那个工种有奖金的员工的最高工资>12000工种编号和最高工资
select min(salary),manager_id
from employees
where manager_id>102
group by manager_id
having min(salary)<5000;
/*查询领导id>102手下员工的最低工资且小于5000的最低工资和领导id*/
/*按照sql进行一步步翻译是查询员工最低工资和所属领导id,且ID>102,最低工资<5000*/
select count(*) AS ABC,length(last_name) len_name
from employees
group by length(last_name)
having ABC>5;
/*按员工姓名的长度分组,查询每一组员工个数,筛选员工个数>5的有哪些*/
/*此外mysql的having后还支持别名的使用*/
select avg(salary),department_id,job_id
from employees
group by job_id,department_id;
/*查询每个部门每个工种的员工的平均工资*/
select avg(salary),department_id,job_id
from employees
group by job_id,department_id
order by avg(salary) desc;
/*查询每个部门每个工种的员工的平均工资,并按平均工资的高低显示*/
select avg(salary),department_id,job_id
from employees
where department_id is not null
group by job_id,department_id
order by avg(salary) desc;
/*查询每个部门每个工种的员工的平均工资,按平均工资的高低显示,且部门ID不为null*/
select avg(salary),department_id,job_id
from employees
where department_id is not null
group by job_id,department_id
having avg(salary)>10000
order by avg(salary) desc;
/*查询每个部门每个工种的员工的平均工资,按平均工资的高低显示,且部门ID不为null,其平均工资大于10000/
6、连接查询(多表查询——————使用练习表girls)
分类:按时间 sql92、sql99(推荐,唯一不支持全外链接)
:按功能 内连接(等值连接,非等值连接,自连接)
外连接(左外连接,右外连接,全外连接)
交叉连接
a、sql92标准 内连接
①、等值连接(多表等值练级结果为多表的交际部分,n表连接至少n-1个连接条件,多表顺序没有要求,可以搭配前边所学的所有查询子句)
SELECT last_name,department_name
FROM employees,departments
WHERE employees.department_id = departments.department_id;
select last_name,E.job_id,J.job_title
from employees AS E,jobs AS J
where e.job_id = j.job_id;
/*在from后可以为表起别名,强制适用于全句*/
SELECT last_name,E.job_id,J.job_title
FROM employees AS E,jobs AS J
WHERE e.job_id = j.job_id
AND e.commission_pct IS NOT NULL;
select department_name,city
from departments d,locations l
where d.location_id = l.location_id
and city like '_o%';
/*在where后可以接AND来添加筛选*/
SELECT COUNT(*) 个数,city
FROM departments d,locations l
WHERE d.location_id = l.location_id
GROUP BY city
ORDER BY 个数 DESC;
/*添加分组和排序的练习*/
select department_name,d.manager_Id,min(salary)
from departments d,employees e
where d.department_id = e.department_id
and commission_pct is not null
group by department_name,d.manager_id;
/*综合练习:查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资*/
select job_title,count(*)
from employees e,jobs j
where e.job_id = j.job_id
group by job_title
order by count(*) DESC;
/*综合练习:每个工种的工种名和员工个数,并且按员工个数降序*/
select last_name,department_name,city
from employees e,departments d,locations l
where e.department_id = d.department_id
and d.location_id = l.location_id
and city like 's%';
/*三表连接练习:查询员工名,部门名和所在城市*/
②、非等值连接(不是=的运算符)
SELECT salary,grade_level
FROM employees e,job_grades g
WHERE salary BETWEEN g.lowest_sal AND g.highest_sal
AND g.grade_level = 'A';
order by salary ASC;
/*查询员工的工资和工资级别,并且只显示A级别的之后按工资升序排序*/
③、自连接
select e.employee_id,e.last_name,m.employee_id,m.lastname
from employees e,employees m
where e.manager_id = m.employee_id;
/*查询员工信息及其及其领导信息,此时的employee被两次调用,使用别名来时selec的信息更加明确*/
④、练习
SELECT country_id,COUNT(*) 部门个数
FROM departments d,locations l
WHERE d.location_id = l.location_id
GROUP BY country_id
HAVING COUNT(*)>2;
/*查询每个国家下部门个数大于2的国家编号*/
SELECT department_name,job_title,MIN(salary) AS 最低工资
FROM employees e,departments d,jobs j
WHERE e.department_id = d.department_id
AND e.job_id = j.job_id
GROUP BY department_name,job_title;
/*查询每个工种、每个部门的部门名、工种名和最低工资*/
SELECT last_name,job_id,d.department_id,department_name
FROM employees e,departments d,locations l
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id
AND city = 'Toronto';
/*查询在city为'Toronto'工作的员工的last_name,job_id,department_id,department_name*/
SELECT e.last_name,e.employee_id 'Emp#',m.last_name,m.employee_id 'Mgr#'
FROM employees e,employees m
WHERE e.manager_id = m.employee_id
AND e.last_name = 'kochhar';
/*选择指定员工的姓名,员工号,以及他的管理者的姓名和员工号,结果输出带别名格式*/
b、sql99标准 内/左外/右外/交叉连接
select 查询列表
from 表1 别名 【连接类型】
join 表2 别名
on 连接条件
【where 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序列表】
①内连接 inner join(inner 可以省略)
select last_name,department_name
from employees e
inner join department d
on e.department_id = d.department_id;
/*查询员工名和工种名*/
select last_name,job_title
from employees e
inner join jobs j
on e.job_id = j.job_id
where e.last_name like '%e%';
/*查询名字中包含e的员工名和工种名*/
select city,count(*) 个数
from departments d
inner join locations l
on d.location_id = l.location_id
group by city
having count(*)>3;
/*查询部门个数大于3 的城市名和部门个数*/
SELECT department_name,count(*)
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id
GROUP BY department_name
HAVING COUNT(*)>3
ORDER BY COUNT(*) DESC;
/*查询那个部门的员工个数>3的部门名和员工个数,并按个数降序*/
SELECT last_name,department_name,job_title
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id
INNER JOIN jobs j
ON e.job_id = j.job_id
ORDER BY department_name DESC;
/*查询员工名、部门名、工种名,并按部门名降序,三表连接需要注意顺序*/
select salary,grade_level
from employees e
innner join job_grades g
on e.salary between g.lowest_sal and g.highest_sal;
/*查询员工的工资级别,非等值连接*/
select grade_level,count(*)
from employees e
inner join job_grades g
on e.salary between g.lowest_sal and g.highest_sal
group by grade_level
having count(*)>20
order by grade_level desc;
/*查询工资界别个数>20的个数,并且按工资级别降序*/
select e.last_name,m.last_name
from employees e
inner join employees m
on m.employee_id = e.manager_id
where e.last_name like 'k%';
/*查询员工的名字,上级管理者的名字*/
②外连接 (用于查询一个表有,另一个表没有的场景)
select b.name
from beauty b
left outer join boys bo
on b.boyfriend_id = b.id
where bo.id is null;
/*左外链接*/
select b.name
from boys bo
right outer join beauty b
on b.boyfriend_id = b.id
where bo.id is null;
/*右外链接*/
SELECT d.*,e.employee_id
FROM departments d
LEFT OUTER JOIN employees e
ON d.department_id = e.department_id
where e.employee_id is null;
/*哪个部门没有员工*/
③交叉连接
select b.*,bo.*
from beauty b
cross join boys bo;
7.子查询(出现在其他语句中的select语句,称为子查询或者内查询
内部嵌套其他的select语句,称为主查询或者外查询)
分类:按位置:select后面(仅支持标量子查询)
from后面(支持表子查询)
where或having后面⭐(标量子查询 列子查询 行子查询)
exists后面(相关子查询)
按结果集的行列数:标量子查询/单行子查询(结果集只有一行一列)
列子查询(结果有一列多行)
行子查询(结果有一行多列)
表子查询(结果集一般多多行多列)
a、where和having后面
子查询都放在小括号内,一般放在条件的右侧,标量子查询一般搭配单行操作符使用(> < >= <= = <>),列子查询一般搭配多行操作符使用(in/not in、any/some all)
eg:标量子查询
查询工资比Abel高的员工信息
第一步 查询abel工资
select salary
from employees
where last_name = 'Abel';
第二步 查询比结果工资高的员工信息
select *
from employees
where salsary>(第一步);
第三步:结合
select *
from employees
where salsary>(
select salary
from employees
where last_name = 'Abel'
);
select last_name,job_id,salary
from employees
where job_id = (/*job_id与141号员工相同*/
select job_id
from employees
where employee_id =141)
AND salary > (/*salary比143号员工多*/
select salary
from employees
where employee_id =143);
/*返回job_id与141号员工相同,salary比143号员工多的员工姓名,job_id和工资*/
select last_name,job_id,salary
from employees
where salary =(
select min(salary)
from employees);
/*查询最低工资的员工姓名,工种和工资*/
select min(salary),department_id
from employees
group by department_id
having min(salary) > (
select min(salary)
from employees
where department_id = 50);
/*查询最低工资大于50号部门最低工资的部门ID和其最低工资*/
select min(salary) AS 最低工资,department_name AS 部门名,e.department_id 部门编号
from employees e
inner join departments d
on e.department_id = d.department_id
group by e.department_id【或者用别名,此时需要注意和select的是否一致】
having min(salary) > (
select min(salary)
from employees
where department_id = 50);
/*查询最低工资大于50号部门最低工资的部门ID、部门name和其最低工资*/
❌❌❌回看❌❌❌
eg:列子查询(多行单列子查询)
第一步 1400或1700部门的location_id
select department_id
from departments
where location_id IN(1400,1700)
第二步 查询部门中所有员工姓名
select last_name
from employees
where department_id in
第三步 组合
select last_name
from employees
where department_id in (
select department_id
from departments
where location_id IN(1400,1700));
/*返回location_id是1400或1700的部门中所有员工姓名*/
select last_name,employee_id,job_id,salary
from employees
where salary < any(
select DISTINCT salary
from employees
where job_id = 'IT_PROG')
and job_id <>'IT_PROG';
等同于
select last_name,employee_id,job_id,salary
from employees
where salary < (
select max(salary)
from employees
where job_id = 'IT_PROG')
and job_id <>'IT_PROG';
/*返回其他工种中比job_id为‘IT_PROG’工种任一工资低的员工的员工号、姓名、job_id以及salary*/
select last_name,employee_id,job_id,salary
from employees
where salary < all(
select DISTINCT salary
from employees
where job_id = 'IT_PROG')
and job_id <>'IT_PROG';
等同于
select last_name,employee_id,job_id,salary
from employees
where salary < (
select min(salary)
from employees
where job_id = 'IT_PROG')
and job_id <>'IT_PROG';
/*返回其他工种中比job_id为‘IT_PROG’工种所有工资低的员工的员工号、姓名、job_id以及salary*/
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Bp5iYvUQ-1591347438111)(C:\Users\Administrator\Desktop\截图文件\mysql\多行子查询.png)]
eg:行子查询(结果集一行多列或者多行多列)
select *
from employees
where(employee_id,salary)=(
select min(employee_id),max(salary)
from employees);
b、select后面(仅仅支持标量子查询)
SELECT d.*,(
SELECT COUNT(*)
FROM employees e
WHERE e.department_id = d.department_id
) AS 个数
FROM departments d;
/*查询每个部门的员工表个数*/
①用内连接查询
select d.department_name as 部门名
from departments d
inner join employees e
on d.department_id = e.department_id
where e.employee_id = 102;
②用子查询
select(
select depatment_name
from departments d
join employees e
on d.department_id = e.department_id
where e.employee_id = 102;
)部门名;
③表量子查询
select department_name as 部门名
from departments d
where department_id = (
select department_id
from employees e
where e.employee_id = 102
);
/*查询员工号102的部门名*/
c、from后面
SELECT ag_dep.*,g.grade_level
FROM(
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id
)ag_dep【这里必须起别名】
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;
/*查询每个部门的平均工资的工资等级*/❌❌❌回看❌❌❌
d、exists后面(相关子查询,只返回是否有值,1为有 0为没有)
select exists(select employee_id from employees)存在否;
select exists(select employee_id from employees where salary=30000) AS 存在否;
SELECT department_name
FROM departments d
WHERE EXISTS(
SELECT *
FROM employees e
WHERE d.department_id = e.department_id);
等同于
SELECT department_name
FROM departments d
WHERE d.department_id IN (
SELECT department_id
FROM employees e
);
/*查询有员工的部门名*/
8、分页查询(需要分页显示查询数据时)
select 查询列表
from 表
【join type
on 连接条件
where by
having
order by】
limit offset(要显示条目的起始索引,从0开始),size(要显示的条目个数);
select * from employees limit 【0,】5;
select * from emplyees limit 10,15;
/*查询第11条到第25条的信息*/
SELECT *
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY salary
LIMIT 10;
/*查询有奖金的员工信息,并显示前十工资较高的*/
七、DML语言
八、DDL语言
九、TCL语言
练习时使用的sql文件:
链接:https://pan.baidu.com/s/1tcimu7CWyG76V1jYq7cqbw
提取码:hvq0