MySql
文章目录
一、为什么要学习数据库
- 持久化数据
- 方便检索
- 存储大量数据
- 共享
- 通过组合分析,获取新的数据
二、数据库的概念及特点
1)、数据库相关概念
简介:数据库相关概念,是一个关系型数据库,现属于oracle公司
DBMS、DB、SQL
DB: database数据库,存储一系列有组织数据的容器
DBMS : Database Management System数据库管理系统,使用DBMS管理和维护DB
SQL : StructureQueryLanguage结构化查询语言,程序员用于和DBMS通信的语言
2)、数据库存储数据的特点
数据库是如何存储数据的
- 将数据放到表中,表再放到库中
- 一个数据库中可以有多个表,每个表都有一个的名字,用来标识自己。表名具有唯一性。
- 表具有一些特性,这些特性定义了数据在表中如何存储,类“似java中类”的设计。
- 表由列组成,我们也称为字段。所有表都是由一个或多个列组成的,每一列类似java中的”属性
- 表中的数据是按行存储的,每一行类似于java中的一个对象
- DDL (Data Definition Language): 数据定义语言,用来定义数据库对象:库、表、列等; create/drop/
- DML (Data Manipulation Language): 数据操作语言,用来定义数据库记录(数据);
- DCL (Data Control Language): 数据控制语言,用来定义访间权限和安全级别;
- DQL (Data Query Language): 数据查询语言,用来查询记录(数据)。80%
三、DQL语言★
- DQL (Data Query Language): 数据查询语言,用来查询记录(数据)。80%
1)、基础查询★
语法:
select 查询列表 from 表名;
特点:
- 查询列表可以是: 表中的字段、常量值、表达式、函数
- 查询的结果是一个虚拟的表格 select后面跟的查询列表,可以有多个部分组成,中间用逗号隔开
例如: select字段1,字段2,表达式from表; - 执行顺序
- from 子句
- select 子句
#先进库,不进库查表
show tables from test;
#查看当前库 使用的是一个函数
select database();
#1.查询表中的单个字段
SELECT last_ name FROM employees;
#2.查询表中的多个字段
SELECT last_ name, salary, email FROM employees;
#3.查询表中的所有字段
SELECT * FROM EMPLOYEES;
#4.查询常量值
SELECT 100;
SELECT 'john' ;
#5.查询表达式
SELECT 100998;
#6.查询函数
SELECT VERSION();
#七、起别名
#方式一:使用as关键字
SELECT USER() AS用户名;
SELECT USER() AS "用户名";
SELECT USER() AS '用户名' ;
SELECT last_ name AS "姓名”FROM employees;
#方式二:使用空格
SELECT USER () 用户名;
SELECT USER () "用户名";
SELECT USER () '用户名';
#当别名中间有空格的时候,用清号扩起来。
SELECT last_ name "姓 名” FROM employees;
#八、+的作用
#需求:查询first_ name和last_ name 拼接成的全名,最终起别名为:姓名
#方案1: 使用+ passx 不成功
SELECT first_ name+last_ name AS "姓名"
FROM employees;
#方案2:使用concat拼接函数
SELECT CONCAT (first_ name,last_ name) AS "姓名"
FROM employees;
#九、distinct的使用
#需求:查询员工涉及到的部门编号有哪些
SELECT DISTINCT department_ id FROM employees;
#十、查看表的结构
DESC employees;
SHOW COLUMNS FROM employees;
mysql中+的作用:
1、加法运算
①、两个操作数都是数值型
100+1.5
②、其中- -个操作数为字符型
将字符型数据强制转换成数值型,如果无法转换,则直接当做0处理
‘张无忌’+100== =>100
③、其中一个操作数为null
null+null== = =》null
null+100== ==》null
#不重复查询
select distinct d_id from employee;
#ifnull(表达式一,表达式二),如果为空就替换成二,不会空照常
#concat拼接到一列
select concat(id,',',IFNULL(department_name,'空')) as out_put
from department;
2)、条件查询★
语法:
select 查询列表
from 表名
where 筛选条件;
执行顺序:
①from子句
②where子句,
③select子句
select last name, first name from emplovees where salary> 2 0000;
特点:
1、按关系表达式筛选
关系运算符: > < >= <= = <>(不等于)
补充:也可以使用!=,但不建议
2、按逻辑表达式筛选
逻辑运算符: and or not
补充:也可以使用&&11 ! , 但不建议
3、模糊查询
like in between and is null
#例1:
select *
from department
where department_name <> 'AA';
#案例2:查询工资<15000的姓名、工资
SELECT last_ name, salary
FROM employees
WHERE salary<15000;
#二、按逻辑表达式筛选
#案例1:查询部门编号不是50-100之 间员工姓名、部门编号、邮箱
SELECT last_ name, department_ id, email
FROM employees
WHERE NOT (department_id>=50 AND department_id<=100);
#案例2:查询奖金率>0.03或者员工编号在60-110之间的员工信息
SELECT *
FROM employees
WHERE commission pdt>0.03 OR (employee_ id >=60 AND employee_ id<=110) ;
1)、模糊查询★
模糊查询语句:
**like **
1、like/not like
功能: 一般和通配符搭配使用,对字符型数据进行部分匹配查询
常见的通配符:
_任意单个字符
%任意多个字符
#案例1:查询姓名中包含字符a的员工信息
SELECT *
FROM employees
WHERE last_ name LIKE '%a%';
#案例2:查询姓名中包含最后一个字符为e的员工信息
SELECT *
FROM employees
WHERE last_ name LIKE '%e' ;
#案例3:查询姓名中包含第一个字符为e的员工信息
SELECT *
FROM employees
WHERE last_ name LIKE 'e%' ;
#案例4:查询姓名中包含第三个字符为x的员工信息
SELECT *
FROM employees
WHERE last_ name LIKE '__x%' :
#案例5:查询姓名中包含第二个字符为_的员工信息
SELECT *
FROM employees
WHERE last_ name LIKE '_$_ %' ESCAPE '$' ;#设置转义字符
#WHERE last_ name LIKE '_\_ %';这个也可以
2)、字段控制查询★
in between and is null
1、in
功能:查询某字段的值是否属于指定的列表之内
a in(常量值1,常量值2,常量值3, …)
a not in(常量值1,常量值2,常量值3…)
in/not in
非数值的用单引号,数值型的不用单引号
#案例1:查询部门编号是30/50/90的员工名、部门编号.
SELECT last_ name, department_ id
FROM employees
WHERE department_ id IN(30,50, 90) ;
2、between and
功能: 判断某个字段的值是否介于xx之间
between and/not between and
#案例1:查询部门编号是30-90之间的部门编号、员工姓名
SELECT department_id, last_name
FROM employees
WHERE department_id BETWEEN 30 AND 90;
#案例2:查询年薪不是100000- 200000之间的员工姓名、工资、年薪.
SELECT last_ name, salary, salary*12* (1+IFNULL (comnission_ _pct,0)) 年薪
FROM employees
WHERE salary*12* (1+IFNULL (commission_ pct,0) ) NOT BETWEEN 100000 AND 200000;
3、is null
== 只能判断普通的内容
IS 只能判断NULL值
<=> 安全等于,既能判断普通内容,又能判断NULL值(语义性差)
#4、is null/is not null
#案例1:查询没有奖金的员工信息
SELECT *
FROM employees
WHERE commission_ _pct IS NULL; .
#案例2:查询有奖金的员工信息
SELECT *
FROM employees
WHERE commission_ pct IS NOT NULL;
练习题:
-
查询工资大于12000 的员工姓名和工资
-
查询员工号为176的员工的姓名和部门号和年薪
select last_name,department_id,salary*12*(1+ifnull(commission_pct,0)) from employees where employee_id = 176;
-
选择工资不在5000到12000 的员工的姓名和工资
-
选择在20或50号部门工作的员工姓名和部门号
-
选择公司中没有管理者的员工姓名及job_ id
select last_name,job_id from employees where manager_id is null;
-
选择公司中有奖金的员工姓名,工资和奖金(率)级别
select last_name,salary,commission_pct from employees where commission_pct is not null;
-
选择员工姓名的第三个字母是a的员工姓名
-
选择姓名中有字母a和e的员工姓名
-
显示出表employees 表中first_ name 以’e’结尾的员工信息
-
显示出表employees 部门编号在80-100 之间的姓名、职位
select last_name,job_id,department_id from employees where department_id between 80 and 100;
-
显示出表employees 的manager_ id是100,101,110 的员工姓名、职位
select last_name,job_id,department_id from employees where manager_id in(100,101,110);
3)、排序 order by★
order by
特点:
1、排序列表可以是单个字段、多个字段、表达式、函数、列数、以及以上的组合
2、升序,通过 asc,默认行为。降序,通过 desc
语法:
select查询列表
from表名
[where筛选条件]
order by 排序列表
执行顺序:
①from子句
②where子句
③select子句
④order by子句
举例:
select last_ name, salary
from employees
where salary> 10000
order by salary ;
#一、按单个字段排序
#案例1:将员工编号>120的员工信息进行工资的升序/降序
SELECT *
FROM employees
WHERE employee_ id> 120
ORDER BY salary ASC;
#ORDER BY salary DESC;
#三、按别名排序
#案例1:对有奖金的员工,按年薪降序
SELECT *, salary*12* (1+IFNULL (commission_ _pct,0)) 年薪
FROM employees
WHERE commission _pEt IS NOT NULL
ORDER BY 年薪 DESC;
#四、按函数的结果排序
#案例1:按姓名的字数长度进行升序
SELECT last_ name
FROM employees
ORDER BY LENGTH(last_ name) ;
#五、按多个字段排序
#案例1:查询员工的姓名、工资、部门编号,先按工资升序,再按部门编号降序
SELECT last_name, salary, department_id
FROM employees
ORDER BY salary ASC, department_id DESC;
#六、按列数排序,字符按abcdefg来排序
SELECT * FROM employees
ORDER BY 2;#第二列
SELECT * FROM employees
ORDER BY first_ name ;
4)、常见函数★
函数: 类似于java 中学过的“方法”,
为了解决某个问题,将编写的一系列的命令集合封装在一一起, 对外仅仅暴露方法名,供外部调用
1、自定义方法(函数)
2、调用方法(函数)★
叫什么: 函数名
干什么: 函数功能
常见函数: .
字符函数
数学函数
日期函数
流程控制函数
1. 字符函数
索引不是从零开始
1、CONCAT 拼接字符
2、LENGTH 获取字节长度
3、CHAR_ LENGTH 获取字符个数
4、SUBSTRING 截取子串.
5、INSTR 获取字符第一次出现的索引
6、TRIM去前后指定的字符,默认是去空格
7、LPAD/RPAD 左填充/右填充
#1
SELECT CONCAT 'hello,',first_ name,last_ name)备注 FROM employees;
#2
SELECT LENGTH('hello, 郭襄') ;
#3
SELECT CHAR_ LENGTH('hello, 郭襄') ;
#4
#substr(str,起始索引,截取的字符长度)
#substr (str,起始索引)
SELECT SUBSTR('张三丰爱上了郭襄',1,3) ;
SELECT SUBSTR('张三丰爱上了郭襄',7) ;
#5
select instr('三打白骨精aaa白骨精bb白骨精', '白骨精');
#6
select trim(' 虚 竹 ') a;#去一前一后
#去一前一后的x
SELECT TRIM('x' FROM 'xxxxxx虚xxx竹xxxxxxxxxxxxxxxxxx') AS a;
#7
select lpad('木婉清',10,'a');
#是根据索引来判断的,如果索引小于字符长度则只输出到索引的位置
select rpad('木婉清',1,'a');
#案例:查询员工表的姓名,要求格式:姓首字符大写,其他字符小写,名所有字符大写,且姓和名之间用_分割,最后起别名"OUTPUT"
8、UPPER()/LOWER
select concat(upper(substr(first_name,1,1)),lower(substr(first_name,2)),'_',upper(last_name)) OUTPUT
from employees;
9、STRCMP 比较两个字符大小
SELECT STRCMP('aec', 'abc') ;
10、LEFT/RIGHT 截取子 串 从左边截取/从右边截取
SELECT LEFT('鸠摩智',1);
SELECT RIGHT('鸠摩智',1);
2. 数学函数
- ABS(); 绝对值
- CEIL(); 向上取整
- floor(); 向下取整
- round(); 四舍五入
- TRUNCATE(); 截断
- mod(); 取余
select abs(-89.2);
select ceil(121.9);
select floor(-0.9);
select round(1.8436468);
在第二位阶段结果是1.84
select truncate(1.843445453434,2);
#第一个数是正结果就是正
select mod(-10,3);
3.日期函数
- now(); 当前时间
- curtime(); 年月日
- curdate(); 时分秒
- datediff(); 截断时间(计算天数)
- date_format();
- str_to_date();
- 提取year(hiredate) 年,month(hiredate)月,day(日),hour(小时),minute(分钟),second(秒)
select now();
select curdate();
select curtime();
select datediff('2020-4-3','1999-3-7');
select date_format('1999-3-7','%Y年%m月%d日 %h小时%m分钟%s秒') 出生日期;
#对应格式转化日期 可以冲当where条件
select STR_TO_DATE('3/15 1998','%m/%d %Y') 日期;
+------------+
| 日期 |
+------------+
| 1998-03-15 |
+------------+
4. 流程控制函数
-
if
if(条件,表达式1 ,表达式2) :如果条件成立,返回表达式1 , 否则返回表达式2
-
case 相当于switch 在数据库中相当于一列 只不过是长了一点
等值判断
case表达式
when值1 then结果1
when值2 then结果2
else结果n
end区间判断
case
when条件1 then结果1
when条件2 then结果2
else结果n
end#需求:如果有奖金,则显示最终奖金,如果没有,则显示0
SELECT IF (100>9, '好', '坏');
select if(commission_pct is not null,salary*12*commission_pct,0) 奖金,salary
from employees;
select department_id,salary,
case department_id
when 30 then salary*2
when 40 then salary*3
when 50 then salary*4
else salary #else可以省略
#别名
end newSalary
from employees;
5)、分组(聚合)函数★
分组函数清单:
sum(字段名): 求和
avg(字段名): 求平均数
max(字段名): 求最大值
min(字段名): 求最小值
count (字段名): 计算非空字段值的个数,直接排除了空选项
1》实参的字段的类型, sum和avg只支持数值型,其他三个可以支持任意类型
2》这五个函数都忽略null值
3》count可以支持以下参数
count(字段) :查询该字段非空值的个数
count(*) :查询结果集的行数
count(1) :查询结果集的行数
4》分组函数可以和distinct搭配使用,实现去重的统计
select count(distinct字段) from表;
- 统计结果集推荐使用count(*)
count(*) and count(1) 统计记录数。
- 搭配distinct去掉重复统计
- 搭配group by 统计
#一求和。平均数,最大值。最小值。非空数量。
select sum(salary) Sum,avg(salary),max(salary) Max,min(salary) Min,count(salary) Count
from employees;
select count(salary) salaryS
from employees;
select count(salary) salaryS
from employees
where salary>2500;
#可以直接进行筛选
select count(*) S,count(1) s
from employees
where department_id = 60;
#去除重复的
select count(distinct department_id)
from employees;
select department_id,sum(salary) 和
from employees
group by department_id;
6)、分组查询
语法:
select 查询列表
from表名
where 筛选条件 不支持分组函数的条件
group by分组列表;
having 分组后筛选
order by 排序列表
执行顺序:
① from
②where
③group by
④having
⑤select
⑥order by
特点:
①查询列表往往是分组 函数和被分组的字段★
②分组查询中的筛选分为两类
where –> group by —> having
分组函数做条件只可能敦在having后面!!!
一般是每个后面分组
筛选的基表 | 使用的关键词 | 位置 | |
---|---|---|---|
分组前筛选 | 原始表 | where | group by的前面 |
分组后筛选 | 分组后的结果集 | having | group by的后面 |
1)、简单的分组
案例1:查询每个工种的员工平均工资
select Job_id,avg(salary) Avg
-> from employees
-> group by Job_id;
2)、可以实现分组前的筛选
案例1:查询邮箱中包含a字符的每个部门的最高工资
案例2:查询有奖金的每个领导手下员工的平均工资
select department_id,count(*),max(salary) Max
from employees
where email like '%a%'
group by department_id;
#
select manager_id,count(*) Count,avg(salary) Avg
from employees
where manager_id is not null
group by manager_id;
3)、可以实现分组后的筛选
案例1:查询哪个部门的员工个数>5
案例2:每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
案例3:领导编号>102的每个领导手下的最低工资大于5000的领导编号和最低工资
select department_id,count(*)
from employees
#分组前的筛选
group by department_id
#分组后的筛选 have 筛选在临时表里面的数据
having count(*)>5;
#2
select job_id,max(salary) Max
from employees
where commission_pct is not null
group by job_id
having Max>12000;
#3
select manager_id,min(salary) Min
from employees
where manager_id>102
group by manager_id
having Min>5000;
4)、可以实现排序
案例:每个工种有奖金的员工的最高工资>6000的工种编号和最高工资,按最高工资升序
select job_id,max(salary) Max
from employees
where commission_pct is not null
group by job_id
having Max >6000
order by Max asc;
5)、按多个字段分组
案例:查询每个工种,每个部门的最低工资,并按最低工资降序
工种和部门一样才算一组
select department_id,job_id,min(salary)
from employees
group by job_id,department_id
order by min(salary) desc;
7)、连接查询
含义:又称多表查询,当查询的字段来自于多个表时,就会用到连接查询
- 笛卡尔乘积现象:表1有m行,表2有n行,结果=m*n行
发生原因: 没有有效的连接条件
如何避免: 添加有效的连接条件
select last_name,department_name
from employees e,departments d
#连接条件
where e.department_id = d.department_id;
分为SQL 1992 语法------------------------》》 SQL 1999语法
1. 内连接
①等值连接
—————SQL92 语法
语法:
select查询列表
from表1.别名,表2别名
where 连接条件
and 筛选条件
group by分组列表
having分组后筛选
order by排序列表
执行顺序:
1、from子句
2、where子句
3、and子句
4、group by子句
5、having子句
6、select子句,
7、order by子句
特点:
1、为了解决多表中的字段名重名问题,往往为表起别名,提高语义性
2、表的顺序无要求
-
多表等值连接的结果为多表的交集部分
-
n表连接,至少需要n-1个连接条件
-
多表的顺序没有要求
-
一般需要为表起别名
-
可以搭配前面介绍的所有子句使用,比如排序分组、 筛选
简单的两表连接
#案例:查询员工名和部门名
select last_name,department_name
from employees e,departments d
#连接条件
where e.department_id = d.department_id;
添加筛选条件
#案例1:查询部门编号>100的部门名和所在的城市名
select d.location_id,l.city
from departments d,locations l
where d.location_id = l.location_id
and d.department_id>100;
案例2:查询有奖金的员工名、部门名
select e.last_name,d.department_name
from employees e,departments d
where e.commission_pct is not null
and e.department_id = d.department_id;
案例3:查询城市名中第二个字符为o的部门名和城市名.
select d.department_name dept_name,l.city 城市
from departments d,locations l
where d.location_id = l.location_id
and l.city like '_o%';
- 添加分组+筛选
案例1:查询每个城市的部门个数
select city,count(department_id) id
from departments d,locations l
where d.location_id = l.location_id
group by city;
案例2:查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资 sal –6100–145
select d.department_name deptName,d.manager_id Man_id,min(salary)
from employees,departments d
where employees.department_id = d.department_id and commission_pct is not null
group by employees.department_id ;
案例3:查询部门中员工个数>10的部门名
select department_name,count(*)
from employees e,departments d
where e.department_id = d.department_id
group by e.department_id
having count(*)>10;
4.添加分组+筛选+排序
案例1:查询部门中员工个数>10的部门名,并按部门名降序
select department_name,count(*)
from employees e,departments d
where e.department_id = d.department_id
group by e.department_id
having count(*)>10
order by count(*) asc;
案例2:查询每个工种的员工个数和工种名,并按个数降序
select count(*) 员工个数,j.job_title
from employees e,jobs j
where e.job_id = j.job_id
group by e.job_id
order by j.job_title desc;
三表连接
案例:查询员工名、部门名、城市名
SELECT e.last_name,d.department_name dept_name,l.city
from employees e,departments d,locations l
where e.department_id = d.department_id and d.location_id = l.location_id
ORDER BY e.last_name desc;
—————SQL99 语法
#一、内连接
语法:
SELECT查询列表
EROM表名1别名
【INNER】 JOIN #【】的意思是可不选
表名2别名
ON连接条件
WHERE筛选条件
GROUP BY分组列表
HAVING分组后筛选I
ORDER BY排序列表;
SQL92和SQL99的区别:
SQL99,|使用JOIN关键字代替了之前的逗号,并且将连接条件和筛选条件进行了分离,提高阅读性! ! !
习题:
一、简单连接.
案例:查询员工名和部门名
二、添加筛选条件
案例1:查询部门编号>100的部门名和所在的城市名
三、添加分组+筛选
案例1:查询每个城市的部门个数
四、添加分组+筛选+排序
案例1:查询部门中员工个数>10的部门名,并按部门名降序
五、三表连接
#一、
select d.department_name,e.last_name
from employees e join departments d
on d.department_id = e.department_id;
#二、
select d.department_id Did,l.city
from departments d join locations l
on d.location_id = l.location_id
where d.department_id > 100;
#三、
select l.city,count(*)
from locations l join departments d
on d.location_id = l.location_id
group by city;
#四、
select d.department_name,count(*)
from employees e join departments d
on e.department_id = d.department_id
group by e.department_id
having count(*)>10
order by d.department_name DESC;
#五、
select stuname, ma jornane, score
from stuinfo s
#两表相互的条件写join 表名后面,然后在写join 第三个表
join major m on s. majorid = m.id
join result r on r.stuid = s.stuid
where s.gender =’男’;
②非等值连接
—————SQL92 语法
#案例1:查询员工的工资和工资级别
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';
—————SQL99 语法
#案例1:查询部门编号在10-90之间的员工的工资级别,并按级别进行分组
select g.grade_level,count(*) 该级别个数
from employees e inner join job_grades g
on e.salary between g.lowest_sal and g.highest_sal
where e.department_id between 10 and 90
group by g.grade_level
order by g.grade_level;
③自连接
———————SQL92语法
#3、自连接
#案例:查询员工名和上级的名称
select e.employee_id Eid,e.last_name LName,m.employee_id Mid,m.last_name MName
from employees e,employees m
where e.manager_id = m.employee_id;
—————SQL99 语法
select e.last_name EName,m.last_name MName
from employees e inner join employees m
where e.manager_id = m.employee_id;
2. 外连接(左连接、…
只支持99语法
说明:查询结果为主表中所有的记录,如果从表有匹配项,则显示匹配项:如果从表没有匹配项,则显示null
应用场景:一般用于查询主表中有但从表没有的记录
特点:
1、外连接分主从表,两表的顺序不能任意调换
2、左连接的话,左边为主表
右连接的话,右边为主表
语法:
select查询列表
from表1别名
left /right/full [ outer] join 表2别名
on连接条件
where筛选条件;
有必要先判断一下主表和从表
主表指有的数据从表没有匹配的
习题
一、查询编号>3的女神的男朋友信息,如果有则列出详细,如果没有,用null填充
二、查询哪个城市没有部门
三、查询部门名为SAL或IT的员工信息
①左外连接
#左连接
#案例1:查询所有女神记录,以及对应的男神名,如果没有对应的男神,则显示为null
select b.*,b1.*
from beauty b
left join boys b1
on b.boyfriend_id = b1.id;
#案例2: 查哪个女神没有男朋友
select b.name
from beauty b
left join boys bo
on b.boyfriend_id = bo.id
where bo.id is null;
#案例3:查询哪个部门没有员工,并显示其部门编号和部门名
select d.department_id,d.department_name
from departments d
left join employees e
on e.department_id = d.department_id
where e.employee_id is null;
②右外连接
#案例1:查询所有女神记录,以及对应的男神名,如果没有对应的男神,则显示为null
select b.*,b1.*
from beauty b
right join boys b1
on b.boyfriend_id = b1.id;
#案例3:查询哪个部门没有员工,并显示其部门编号和部门名
select d.department_id,d.department_name
from employees e
right join departments d
on e.department_id = d.department_id
where e.employee_id is null;
③全外连接
full 表示把主表从表的交集和交集以外的都显示出来
mysql 中不支持,但可以勉强实现
select b.*,bo.*
from beauty b
left join boys bo
on b.boyfriend_id = bo.id
union
select b.*,bo.*
from beauty b
right join boys bo
on b.boyfriend_id = bo.id;
3. 自然连接(不常用)
不常用
8)、子查询√
说明:当一个查询语句中又嵌套了另一个完整的select语句, 则被嵌套的select语句称为子查询或内查询
外面的select语句称为主查询或外查询。
分类:
按子查询出现的位置进行分类:
1、select后面
要求: 子查询的结果为单行单列(标量子查询)
2、from后面
要求:子查询的结果可以为多行多列
3、 where或having后面 ★
要求:子查询的结果必须为单列
单行子查询
多行子查询
4、exists后面
要求:子查询结果必须为单列(相关子查询)
特点:
1、子查询放在条件中,要求必须放在条件的右侧
2、子查询一-般放在小括号中
3、子查询的执行优先于主查询
4、单行子查询对应了单行操作符: > < >= <= = <>
多行子查询对应了多行操作符: any/some all in
#二)多行子查询
3
in:判断某字段是否在指定列表内
x in(10,30,50)
any/ some:判断某字段的值是否满足其中任意一个
x>any(10,30, 50)
x>min () x大于其中最小的一个就成立
x=any(10,30, 50) .
x in(10,30, 50)
all:判断某字段的值是否满足里面所有的
x >a11 (10,30,50)
x >max() x大于其中最大的一个成立
1、select后面
意义 不大,一般可以用别的代替
2、from后面
查询每个部门平均工资的级别。 要个from集中的起个别名 ,把from集当成一张表来查
select s.Asalary,g.grade_level,s.department_id
from
(select avg(salary) Asalary,department_id
from employees
where department_id is not null
group by department_id) s
join job_grades g
where s.Asalary between g.lowest_sal and g.highest_sal;
3、 where或having后面 ★
1.练习
1.1查询和 zlotkey_ 相同部门的员工姓名和工资
1.2查询工资比公司平均工资高的员工的员工号,姓名和工资
2.#案例:
2.1返回 location id是1400或1700的 部门中的所有员工姓名
2.2题目:返回其它部门中比job_ id为’IT_ PROG’ 部门任一工资都低的员工的员工号、姓名、job_ id以及salary
2.3题目:返回其它部门中比job_ id为 'IT PROG’ 部门所有工资都低的员工的员工号、姓名、job_id 以及salary
2.4题目:使用子查询实现城市为Toronto的,且工资>10000的员工姓名
#3.查询各部门中工资比本部门平均工资高的员工的员工号,姓名和工资
3.1 查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
3.2 查询管理者是King 的员工姓名和工资
3.3查询平均工资最低的部门信息和该部门的平均工资
#1.1
select last_name,salary,department_id
from employees
where
department_id = (select department_id from employees where last_name = 'zlotkey');
#1.2
select employee_id,last_name,salary
from employees
where salary > (select avg(salary) from employees);
#2.1
select last_name
from employees e
where department_id
in(select department_id
from departments d
join locations l
on d.location_id = l.location_id
where l.location_id in(1400,1700));
#2.2
select employee_id,last_name,job_id,salary
from employees #小于最大的值就行了
#加上distinct 去掉重复的。
where salary < any(select distinct salary from employees where job_id = 'IT_PROG');
#2.3
select employee_id,last_name,job_id,salary
from employees #小于最大的值就行了
where salary < all(select salary from employees where job_id = 'IT_PROG');
#2.4
select last_name,salary
from employees e
join departments d
on d.department_id = e.department_id
where
(select l.location_id l_id from locations l where city = 'Toronto') = d.location_id
and e.salary > 10000;
#3 先查平均工资(不用两张表就可以查),在查结果
select e.employee_id,e.department_id,e.last_name,e.salary
from employees e
join (select e.department_id did,avg(e.salary) avgSalary
from employees e
group by e.department_id) avgsalary
on e.department_id = avgsalary.did
where e.salary> avgsalary.avgSalary
order by e.department_id asc;
3.1
select department_id,employee_id,last_name
from employees
where department_id in(select distinct department_id
from employees
where last_name like '%u%')
order by department_id;
3.2
select last_name,salary
-> from employees
-> where manager_id in(select employee_id from employees where last_name = 'K_ing');
3.3
select d.*,a.salary
from departments d
join
(select e.department_id did, avg(e.salary) salary from employees e group by e.department_id) a
on d.department_id = a.did
order by a.salary
limit 1;
3.4
select min(e.salary),e.department_id
from (select department_id dept_id
from employees
group by department_id
order by max(salary)
limit 1) s
join employees e
on e.department_id = s.dept_id;
4、exists后面
意义不大exists(select…) 存在返回1 不存在返回0
9)、分页查询★
语法:
select查询列表
from表1别名
join表2_ 别名
on连接条件
where 筛选条件
group by, 分组
having 分组后筛选
order by排序列表
limit (起始条目索引,显示的条目数)
执行顺序:
1》from子句
2》join子句
3》on子句
4》where子句
5》group by子句
6》having子句
7》select子句,
8》order by子句
9》limit子句
特点:
①起始条目索引如果不写 默认从0开始,
②limit后面支持两个参数
参数1:显示的起始条目索引
参数2:条目数
#案例1:查询员工信息表的前5条
#案例2:查询有奖金的,且工资较高的第11名到第20名
#1
select *
from employees
#索引从零开始
limit 0,5;
#2
select last_name,salary,commission_pct
from employees
where commission_pct is not null
order by commission_pct desc
limit 10,10;
#公式:
#假如要显示的页数是page,每页显示的条目数为size
select *
from employees
limit (page-1) *size,size;
page size=10
1 limit 0,10
2 limit 10, 10
3 limit 20, 10
4 limit 30, 10
10)、union联合查询√
说明:当查询结果来自于多张表,但多张表之间没有关联,这个时候往往使用联合查询,也称为union查询
#案例2:查询所有国家的用户姓名和年龄
#案例3: union 自动去除重复的 用union all
select * from employees
union #用关键字连接,两个完全不同的查询,列名相同的会挤到同一列,(如果不起别名的话)先查询的一列的上面
select * from departments
SELECT uname, uage FROM usa
UNION #出来的结果是 age对应的值在uname下面
SELECT age,'name' form chinese ;
#union 自动去除重复的
SELECT 1,'范冰冰'
UNION all
SELECT 1,'范冰冰'
UNION all
SELECT 1,'范冰冰';
四、DML语的学习★
DML (Data Manipulation Language)数据操纵语言:
insert update delete 对表中的数据的增删改
1)、插入语句
单行语法:
insert into 表名(字段名1,字段名2…) values (值1, 值2, …);
插入多行:
insert into 表名(字段名1,字段名2 , …) values
(值1,值2,…), (值1,值2…), (值1,值2,…) ;
特点:
①字段和值列表一一对应
包含类型、约束等必须匹配
②数值型的值,不用单引号
非数值型的值,必须使用单引号
③字段顺序无要求
#案例1:要求字段和值列表一一对 应,且遵循类型和约束的限制
#案例2:可以为空字段如何插入
#案例3:默认字段如何插入
#案例4:可以省略字段列表,默认所有字段
#案例1:要求字段和值列表一一对 应,且遵循类型和约束的限制
INSERT INTO stuinfo (stuid,stuname,stugender,email,age,majorid)
VALUES(1, '吴倩','男','wuqian@qq.com',12,1);
INSERT INTO stuinfo (stuid,stuname,stugender,email,age,majorid)
VALUES(3,'李宗盛','男','wuqian@qq.com',45,2);
#案例2:可以为空字段如何插入
INSERT INTO stuinfo (stuid,stuname,email,age,majorid)
VALUES(4,'齐秦','qiqinqq.com',null,2);
#案例3:默认字段如何插入
INSERT INTO stuinfo (stuid,stuname,email,stugender,majorid)
VALUES(7,'齐小鱼','qiqin@qq. com',DEFAULT,2);
#案例4:可以省略字段列表,默认所有字段
INSERT INTO stuinfo VALUES(8,'林忆莲','女','lin@126.com',12,3);
补充:设置自增长列
auto_increment 一般用于主键后面
1、自增长列要求必须设置在一个键上,比如主键或唯一键
2、自增长列要求数据类型为数值型
3、一个表至多有一个自增长列
2)、修改语句
语法:
update 表名set 字段名=新值,字段名=新值, … where 筛选条件;
#案例1:修改年龄<20的专业编号为3号,且邮箱更改为xx@qq. com
UPDATE stuinfo SET majorid = 3,email='xx@qq. com'
WHERE age<20;
3)、删除语句√
方式1: delete语句
语法: delete from表名where 筛选条件;
方式2: truncate语句 不能添加where条件,一次性清除所有数据
语法: truncate table表名;
[面试题] delete和truncate的区别
- delete可以添加WHERE条件
TRUNCATE不能添加WHERE条件,一次性清 除所有数据 - truncate的效率较高
- 如果删除带自增长列的表,
使用DELETE删除后,重新插入数据,记录从断点处开始
使用TRUNCATE删除后,重新插入数据,记录从1开始 - delete删除数据,会返回受影响的行数
TRUNCATE删除数据,不返回受影响的行数 - delete删除数据,可以支持事务回滚
TRUNCATE删除数据,不支持事务回滚
#案例1:删除姓李所有信息
DELETE FROM stuinfo WHERE stuname LIKE '李号' ;
#案例2:删除表中所有数据
TRUNCATE TABLE stuinfo ;
五、DDL语言的学习
说明: Data Define Language数据定 义语言,用于对数据库和表的管理和操作
1》定义顺序(书写顺序)
select distinct 查询列表
from表名别名
join表名别名
on连接条件
where 筛选条件.
group by,分组列表
having 分组后筛选
order by.排序列表
limit 条目数;
2》执行顺序
from子句
join子句
on子句
where子句
group by子句
having子句
select子句
order by子句
limit子句
1)、库和表的管理
1. 库的管理
#一、创建数据库
#二、删除数据库
CREATE DATABASE stuDB;
# 如果不存在 则创建
CREATE DATABASE IF NOT EXISTS stuDB;
DROP DATABASE stuDB;
DROP DATABASE IF EXISTS stuDB;
2. 表的管理
一、创建表√
语法:
CREATE TABLE [IF NOT EXISTS] 表名(
字段名 字段类型 [字段约束],
字段名 字段类型 [字段约束]
);
案例:
CREATE TABLE IF NOT EXISTS stuinfo(
stuid int,
stuname varchar(20),
borndate datetime
);
二、修改表
语法: ALTER TABLE表名ADD IMODIFY ICHANGE IDROP COLUMN字段名字段类型[字段约束] ;
#1.修改表名
#2.添加字段
#3.修改字段名.
#4.修改字段类型
#5.删除字段
ALTER TABLE stuinfo RENAME TO students;
ALTER TABLE students ADD COLUMN borndate TIMESTAMP NOT NULL;
ALTER TABLE students CHANGE COLUMN borndate bi rthday DATETIME NULL;
ALTER TABLE students MODIFY COLUMN bi rthday TIMESTAMP;
ALTER TABLE students DROP COLUMN bi rthday;
三、删除表√
DROP TABLE IF EXISTS stuinfo;
四、复制表√
仅仅复制表的结构: create table表名like旧表;
可以复制表的结构+数据: create table表名子查询;
只复制表的结构 表名 被复制的表名
create table [newTable] like major;
#复制表的结构+数据 库.表
CREATE TABLE newTable3 SELECT * FROM girls.' beauty' ;
#案例:复制employees表 中的last_ name, department id, salary字段到新表emp表,但不复制数据
create table emp
select last_name,department_id,salary
from myemployees.employees
where 1=2;
2)、常见数据类型介绍
字段类型
1、整型
TINYINT SMALLINT INT BIGINT
2、浮点型
FLOAT (m, n)
DOUBLE (m, n)
DECIMAL (m, n)
m和n可选
3、字符型
char(n):n可选,默认是1.固定长度的字符,效率较高,适合存储长度固定的字段
varchar(n): n必选,不能省略。可变长度的字符,效率较低,适合存储长度变化较大的字段
text:保存较长文本
字段类型 | 写法 | 描述 |
---|---|---|
整型 | TINYINT SMALLINT INT BIGINT | 见名知意 |
浮点型 | FLOAT (m, n),DOUBLE (m, n),DECIMAL (m, n) | 小数点前m位,小数点后n位,m和n可选 |
字符型 | CHAR(n) :n可选, VARCHAR(n): n必选 ,TEXT | char适合变化幅度不大的,n最多字符个数 |
日期型 | Datetime,Timestamp | |
二进制 | blob | 存图片(sql语句不能存) |
4、日期型:
date 只能保存日期
time 只能保存时间
datetime 保存日期+时间所占字节空间: 8,能表示的日期范围较大
timestamp 保存日期+时间所占字节空间: 4,能表示的日期范围较小
保存范围 | 所占字节 | |
---|---|---|
Datetime | 1900-1-1~xxx年 | 8 |
Timestamp | 1970-1-1~2038-12-31 | 4 |
3)、常见六大约束
常见六大约束
说明:用于限制表中字段的数据的,从而进一步保证数据表的数据是一致的、准确的、可靠的!
-
NOT NULL非空: 用于限制该字段为必填项
-
DEFAULT默认: 用于限制该字段没有显式插入值,则直接显式默认值
-
PRIMARY KEY 主键:用于限制该字段值不能重复,设置为主键列的字段默认不能为空
一个表只能有一个主键,当然可以是组合主键 -
UNIQUE唯-:用于限制该字段值不能重复
字段是否可以为空 | 一个表可以有几个 | |
---|---|---|
主键 | × | 1个 |
唯一 | √ | n个 |
- CHECK检查 : mysql 不支持,用于限制该字段值必须满足指定条件
CHECK(age BETWEEN 1 AND 100)
-
FOREIGN KEY外键:用于限制两个表的关系,要求外键列的值必须来自于主表的关联列
要求:
①主表的关联列和从表的关联列的类型必须一致,意思一样,名称无要求
②主表的关联列要求必须是主键 外键需要写在最后面,也能支持写在列后面,但是不支持
create table if not exists stuinfo(
stuid int primary key,
stuname varchar(20) unique not null,
stugender char(1) default '男',
email varchar(20) not null,
age int check(age between 0 and 100),
majorid int,
constraint fk_stuinfo_major foreign key (majorid) references major(id)
);
select d.department_id,avg(e.salary) avgSalary
from departments d
join employees e
on d.department_id = e.department_id
group by e.department_id;
六、TCL语言的学习
1)、事务和事务处理
什么是事务?为什么要用事务?
一个事务是由一-条或者多条sgl语句构成,这一条或者多 条sgl语句要么全部执行成功,要么全部执行失败!
默认情况下,每条单独的sgL.语句就是一个单独的事务!
- 原子性 (Atomicity):事务中所有操作是不可再分割的原子单位。事务中所有操作要么
全部执行成功,要么全部执行失败。 - 致性(Consistency): 事务执行后,数据库状态与基它业务规则保持-致。如转账业
务,无论事务执行成功与否,参与转账的两个账号佘额之和应该是不变的。 - 隔离性(Isolation): 隔离性是指在并发操作中,不同事务之间应该隔离开来,使每个并
发中的事务不会相互干扰。 - 持久性(Durability):-旦事务提交成功, 事务中所有的数据操作都必须被持久化到数
据库中,即使提交事务后,数据库马上崩溃,在数据库重启时,也必须能保证通过某种
机制恢复数据。
分类:
隐式事务 : 没有明显的开启和结束标记
比如dm1语句的insert、update、 delete语句本身就是一条事务
insert into stuinfo values(1, 'john', '男', 'ert@dd.com' ,12) ;
显式事务: 具有明显的开启和结束标记
一般般由多条sql语句组成,必须具有明显的开启和结束标记
步骤:
取消隐式事务自动开启的功能
1、开启事务
2、编写事务需要的sq1语句(1条或多条)
插入语句是自己提交的。
insert into stuinfo values(1, 'john', '男', 'ert@dd. com' , 12) ;
insert into stuinfo values(1, 'john',男', 'ert@dd. com',12) ;
3、结束事务
SHOW VARIABLES LIKE '%auto%';
#演示事务的使用步骤
#1、取消事务自动开启
SET autocommit = 0;
#2、开启事务
START TRANSACTION;
#3、编写事务的sq1语句
#将张三丰的钱-5000
UPDATE account SET money=money-1000 WHERE id = 1;
#将灭绝的钱+5000
UPDATE account SET money=money+1000 WHERE id = 2;
#4、结束事务 sql不能演示异常,所以下面的选项只能选一个来写
#提交
COMMIT ;
#回滚
ROLLBACK;