SQL语法

m0_67665862

已于 2022-07-27 15:47:45 修改

阅读量926

点赞数

文章标签： sql 数据库 mybatis

于 2022-07-27 15:29:17 首次发布

本文链接：https://blog.csdn.net/m0_67665862/article/details/126015472

版权

一:DQL语言

1.基础查询

#进阶1:基础查询
/*
select 查询列表
from 表名;
查询列表可以使:
表中的字段
常量值
表达式
函数
查询的结果是一个虚拟的表格

#启用指定的库
USE myemployees;

#1.查询表中的单个字段
SELECT last_name FROM employees;

#2.查询表中的多个字段
SELECT last_name,salary,email FROM employees;

#3.查询表中的所有字段
#第一种写法
SELECT
`employee_id`,
`last_name`,
`first_name`,
`email`,
`phone_number`,
`job_id`,
`salary`,
`commission_pct`,
`manager_id`,
`department_id`,
`hiredate`
FROM
employees ;

#第二种写法
SELECT * FROM employees;

#4.查询常量值
SELECT 100;
SELECT 'john';

#5.查询表达式
SELECT 100%98;

#6.查询函数
SELECT VERSION();

#7.起别名别名中带有特殊符号加双引号
#方式一
SELECT 100%98 AS 结果;
SELECT last_name AS 姓,first_name 名 FROM employees;
#方式二
SELECT last_name 姓,first_name 名 FROM employees;

#8.去重 select+distinct

#案例:查询员工表中涉及到的所有的部门编号
SELECT DISTINCT department_id 部门编号 FROM employees;

#9.'+'号的作用:只能作为运算符使用
SELECT 'john123'+90;#90
SELECT '123'+90;#213
SELECT NULL+10;#null
#案例:查询员工的名和姓连接成一个字段,并显示为姓名
SELECT CONCAT(`last_name`,`first_name`) AS 姓名
FROM
employees;

#10.显示表departments的结构,并查询其中的全部数据
DESC departments;
SELECT * FROM `departments`;
#11.显示表employees中的全部job_id(不能重复)
SELECT DISTINCT `job_id` FROM`employees`;
#12.显示表employees中的全部列,各个列之间用逗号链接,列头显示成out_put
SELECT
   IFNULL(`commission_pct`,0) AS 奖金率,
   `commission_pct`
FROM
   employees;
#--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT
   CONCAT(`employee_id`,`first_name`,`last_name`,`email`,`phone_number`,`job_id`,`salary`,`manager_id`,`department_id`,`hiredate`,IFNULL(`commission_pct`,0)) AS out_put
FROM
   employees;
2.条件查询

进阶2:条件查询
/*
select
   查询列表
from
   表名
where
   筛选条件;

分类:
1. 按条件表达式筛选
条件运算符:>; <; =; <>; >=; <=;
2. 按逻辑表达式筛选
逻辑运算符:and or not
3. 模糊查询
like; between and; in; is null;
*/
按条件表达式筛选
#案例1:查询工资>12000的员工信息
SELECT
   *
FROM
   employees
WHERE
   salary>12000;

#案例2:查询部门编号不等于90号的员工名和部门编号
SELECT
   last_name,
   department_id
FROM
   employees
WHERE
   department_id<>90;

按逻辑表达式筛选
#案例1:工资在10000到20000之间的员工名,工资及奖金
SELECT
   last_name,
   salary,
   commission_pct
FROM
   employees
WHERE
   salary>10000 AND salary<20000;

#案例2:查询部门编号不是在90到110之间,或者工资高于15000的员工信息
SELECT
   *
FROM
   employees
WHERE
   department_id<90 OR department_id>110 OR salary>15000;

模糊查询
#like :像一般和通配符在一起使用 '%' :任意多个字符,包含0个字符
# '_' :任意单个字符
#案例1:查询员工名中包含字符a的员工信息
SELECT
*
FROM
employees
WHERE
last_name LIKE'%a%';
#案例2:查询员工中第三个字符为e,第五个字符为a的员工名和工资
SELECT
`last_name`,
`salary`
FROM
`employees`
WHERE
`last_name` LIKE'__n_l%';
#案例3:查询员工名中第二个字符为_的员工名
SELECT
`last_name`
FROM
`employees`
WHERE
`last_name` LIKE '_\_%';

#between and

#案例1:查询员工编号在100到120之间的所有的员工信息
SELECT
   *
FROM
   employees
WHERE
   employee_id BETWEEN 100 AND 120;

#in
#案例:查询员工的工种编号是 IT_PROG. AD_VP. AD_PRES中的一个员工名和工种编号
SELECT
   `last_name`,
   `job_id`
FROM
   `employees`
WHERE
   job_id IN ('IT_PROG','AD_VP','AD_PRES');

#is null is not null
#案例:查询没有奖金的员工名和奖金率
SELECT
   `last_name`,
   `commission_pct`
FROM
   `employees`
WHERE
   `commission_pct` IS NOT NULL;

#安全等与 <=>
SELECT
   `last_name`,
   `commission_pct`
FROM
   `employees`
WHERE
   `commission_pct`<=>NULL;

#案例:查询工资为12000的员工信息
SELECT
   *
FROM
   `employees`
WHERE
   `salary` <=> 12000;

3.分组函数

功能:用作统计使用,又称为聚合函数或统计函数或组函数
分类:
和分组函数一同查询的字段要求是GROUP BY后的字段
SUM 求和;AVG 平均值;MAX 最大值;MIN 最小值;COUNT 计算个数
#sum 求和处理数值型忽略null值
SELECT SUM(salary) FROM `employees`;
#avg 平均值处理数值型忽略null值
SELECT AVG(salary) FROM `employees`;
#max 最大值支持日期,人名忽略null值
SELECT MAX(salary) FROM `employees`;
#count 计算不为null的个数忽略null值
SELECT COUNT(salary) FROM `employees`;
#放一起
SELECT SUM(`salary`) 和,AVG(`salary`) 平均,MAX(`salary`) 最高 FROM `employees`;
0
SELECT ROUND(AVG(`salary`),2) FROM `employees`;

#和distinct搭配
SELECT SUM(DISTINCT salary),SUM(salary) FROM `employees`;
SELECT COUNT(DISTINCT `salary`),COUNT(`salary`) FROM `employees`;

#count函数的单独介绍
SELECT COUNT(`salary`) FROM `employees`;
SELECT COUNT(*) FROM `employees`;#效率最高
SELECT COUNT(1) FROM `employees`;
SELECT COUNT('我牛逼') FROM `employees`;

#查询员工表中的最大入职时间和最小入职时间的相差天数
SELECT DATEDIFF(MAX(`hiredate`),MIN(`hiredate`)) AS difference FROM `employees`;
#查询部门编号为90的员工个数
SELECT COUNT(*) FROM `employees` WHERE `department_id`=90;
4.分组查询

语法
/*
select 分组函数,列(要求出现在group by的后面)
from 表
where 筛选条件
group by 分组的列表
order by 子句

*/0

#案例:查询每个工种的最高工资
SELECT MAX(`salary`),`job_id`
FROM `employees`
GROUP BY `job_id`;

#案例:查询每个位置上的部门个数
SELECT COUNT(*),`location_id`
FROM `departments`
GROUP BY `location_id`;

#案例:查询邮箱中包含a字符的,每个部门的平均工资
SELECT AVG(`salary`),`department_id`
FROM `employees`
WHERE `email` LIKE('%a%')
GROUP BY `department_id`;

#案例:查询有奖金的每个领导手下员工的最高工资
SELECT MAX(`salary`),`manager_id`
FROM `employees`
WHERE `commission_pct` IS NOT NULL
GROUP BY `manager_id`;

#having
#案例:查询那个部门的员工个数大于2
SELECT COUNT(*) AS 员工个数,`department_id`
FROM `employees`
GROUP BY `department_id`
HAVING 员工个数>2;

#查询每个工种有奖金的员工的最高工资大于12000的工种编号和其最高工资
SELECT `employee_id`,MAX(`salary`) 最高工资,`job_id`
FROM `employees`
WHERE `commission_pct` IS NOT NULL
GROUP BY `job_id`
HAVING 最高工资>12000;

#查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个,以及其最低工资
SELECT `manager_id`,MIN(`salary`) 最低工资
FROM `employees`
WHERE `manager_id`>102
GROUP BY `manager_id`
HAVING 最低工资>5000;

#按员工姓名的长度分组,查询每一组的员工个数,筛选员工个数大于5的有哪些
SELECT COUNT(*) AS 员工个数 ,LENGTH(`last_name`) 姓名长度
FROM `employees`
GROUP BY LENGTH(`last_name`)
HAVING COUNT(*)>5;

#查询每个部门每个工种的员工的平均工资
SELECT AVG(`salary`),`department_id`,`job_id`
FROM `employees
group by `department_id`,`job_id`
order by AVG(`salary`) desc;

5.排序查询

进阶3:排序查询
语法:
SELECT
       查询列表
FROM
       表
   WHERE
       筛选条件
0   ORDER BY
       排序列表
   ASC/DESC;

#案例1:查询员工信息,要求工资从低到高排序
SELECT
   *
FROM
   `employees`
ORDER BY
   `salary`
DESC;

#案例2:查询部门编号大于等于90的员工信息,按入职时间的先后进行排序
SELECT
*
FROM
`employees`
WHERE
`department_id`>=90
ORDER BY
`hiredate`
ASC;

#案例3:按年薪的高低显示员工的信息和年薪
SELECT
*,
`salary`*12*(1+IFNULL(`commission_pct`,0)) AS 年薪
FROM
`employees`
ORDER BY
年薪
DESC;

#案例4:按姓名的长度来显示员工的姓名和工资
SELECT
LENGTH(`last_name`) 字节长度,
`last_name`,
`salary`
FROM
`employees`
ORDER BY
字节长度
DESC;

#案例6:查询员工信息,先按工资排序,再按员工编号排序
SELECT
*
FROM
`employees`
ORDER BY
`salary` ASC,
`employee_id` DESC;

#查询员工的姓名和部门号和年薪,按年薪降序按姓名升序
SELECT
`last_name`,
`department_id`,
`salary`*12*(1+IFNULL(`commission_pct`,0)) AS 年薪
FROM
`employees`
ORDER BY
年薪 DESC,
`last_name` ASC;

#选择工资不在8000到17000的员工的姓名和工资,按工资降序
SELECT
`last_name`,
`salary`
FROM
`employees`
WHERE
`salary` NOT BETWEEN 8000 AND 17000
ORDER BY
`salary` DESC;

#查询邮箱中包括a的员工信息,并先按邮箱的字节数降序,在按部门号升序
SELECT
LENGTH(`email`) ,
*
FROM
`employees`
WHERE
`email` LIKE '%a%'
ORDER BY
LENGTH(`email`) DESC,
`department_id` ASC;

6.分页查询

#查询前五条员工信息
SELECT *
FROM `employees`
LIMIT 0,5;

#查询11-25
SELECT *
FROM `employees`
LIMIT 10,15;
0
#查询有奖金的员工信息,将工资较高的前10名显示出来
SELECT *
FROM `employees`
WHERE `commission_pct` IS NOT NULL
ORDER BY `commission_pct` DESC
LIMIT 0,10;

7.分组函数

#和distinct搭配
SELECT SUM(DISTINCT salary),SUM(salary) FROM `employees`;
SELECT COUNT(DISTINCT `salary`),COUNT(`salary`) FROM `employees`;

#count函数的单独介绍
SELECT COUNT(`salary`) FROM `employees`;
SELECT COUNT(*) FROM `employees`;#效率最高
SELECT COUNT(1) FROM `employees`;
SELECT COUNT('我牛逼') FROM `employees`;

8.常见函数
调用:
/*
select 函数名(实参列表) from 表;
分类: 单行函数:做处理字符函数数学函数日期函数其他函数流程控制函数
分组函数: 做统计使用

*/0

#一:字符函数
#length :获取字节长度
SELECT LENGTH('join');
SELECT LENGTH('张三丰hahaha');#一个汉字占三个字节一个字母占两个字节

#concat 拼接字符串
SELECT CONCAT(`last_name`,'_',`first_name`) AS 姓名 FROM `employees`;

#upper lower
SELECT UPPER('join');
SELECT LOWER('joHN');
#案例:将姓变大写,将名变小写,然后拼接
SELECT
CONCAT(UPPER(`last_name`),'-' ,LOWER(`first_name`) ) AS 姓名
FROM
`employees`

#substr subtring :截取
SELECT SUBSTR('李莫愁爱上了陆展元',7) AS out_put;
截取从指定索引处指定字符长度的字符
SELECT SUBSTR('李莫愁爱上了陆展元',1,3) AS out_put;
#案例:姓名中首字符大写,其他字符小写然后用_拼接,显示出来
SELECT CONCAT(UPPER(SUBSTR(`last_name`,1,1)),'_',LOWER(SUBSTR(`last_name`,2))) AS out_put
FROM `employees`;

#intstr :返回子串第一次出现的索引,找不到返回0
SELECT INSTR('杨不悔爱上了殷六侠','殷六侠') AS out_put
FROM `employees`;

#trim :去前后空格字符
SELECT LENGTH(TRIM(' 张翠山 ')) AS out_put;
SELECT TRIM('a'FROM'aaaaaaaaa张aaaaaaaaa翠山aaaaaaaaa') ;

#lpad :用指定的字符实现左填充指定长度
SELECT LPAD('殷素素',10,'*') AS out_put;
#rpad :用指定的字符实现右填充指定长度
SELECT RPAD('殷素素',10,'*') AS out_put;

#replace 替换
SELECT REPLACE('张无忌爱上了周芷若','周芷若','赵敏') AS out_put;

9.流程控制函数
# if
SELECT IF(10>5,'大','小');
SELECT `last_name`, `commission_pct`,IF(`commission_pct` IS NULL,'没奖金,呵呵','有奖金,哈哈')
FROM `employees`;
0
#case
/*
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;
when 常量2 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
`employees`;

#case的使用2
/*
case
when条件1 then 要显示的值1或语句1;
when条件2 then 要显示的值2或语句2;
.......
else 要显示的值n或语句n;
end

*/
#查询员工的工资情况
如果工资大于20000,显示A级别
如果工资大于15000,显示B级别
如果工资大于10000,显示C级别
否则,D级别
SELECT
`last_name`,
`salary`,
CASE
WHEN `salary`>=15000 THEN 'A级:你真牛逼'
WHEN `salary`>=10000 THEN 'B级:还差不多'
WHEN `salary`>=5000 THEN 'C级:小伙子你还差的远那'
ELSE 'D级:赶紧几把回家种地吧'
END AS 牛逼程度
FROM
`employees`;
10.其他函数
SELECT VERSION();
SELECT DATABASE();
SELECT USER();

11.

日期函数
#now 返回当前系统日期+时间
SELECT NOW();

#curdate 返回当前系统日期,不包含时间
SELECT CURDATE();

#curtime 返回当前时间,不包含日期
SELECT CURTIME();
0
#获取指定的部分:年,月,日,小时,分钟,秒
SELECT YEAR(NOW()) AS 年;
SELECT YEAR('1998-1-1') AS 年;
SELECT YEAR(`hiredate`) AS 年 FROM `employees`;
SELECT MONTH(NOW()) AS 月;
SELECT MONTHNAME(NOW())AS 月;

#str_to_date:将日期格式的字符转换成指定格式的日期
/*
%Y 四位的年份
%y 两位的年份
%m 月份(01.02.03.....)
%c 月份(1.2.3.4.....)
%d 日(01.02.....)
%H 小时(24小时制)
%h 小时(12小时制)
%i 分钟
%s 秒
*/
SELECT STR_TO_DATE('9-13-1999','%m-%d-%Y');
#查询入职日期为1992--4-3的员工信息
SELECT * FROM `employees` WHERE `hiredate` = '1992-4-3';
SELECT * FROM `employees`
WHERE `hiredate` = STR_TO_DATE('4-3 1992','%c-%d %Y');

#date_format:将日期转换成字符
SELECT DATE_FORMAT('2018/6/6','%Y年%m月%d日');
#查询有奖金的员工名和入职日期(xx月/xx日/xx年)
SELECT
`last_name`,
DATE_FORMAT(`hiredate`,'%m月/%d日/%Y年')
FROM
`employees`
WHERE
`commission_pct` IS NOT NULL;

12.

数学函数
#round 四舍五入
SELECT ROUND(1.65);
SELECT ROUND(-1.45);
SELECT ROUND(-1.55);
SELECT ROUND(1.5678,3);

#ceil 向上取整,返回>=该参数的最小整数
SELECT CEIL(1.52);
SELECT CEIL(1.002);
SELECT CEIL(-1.52);

#floor 向下取整,返回<=改参数的最大整数
SELECT FLOOR(-9.99);

#truncate 截断
SELECT TRUNCATE(1.6599,1);
0
#mod 取余
SELECT MOD(10,-3);
SELECT MOD(-10,3);

13.

#连接查询 (多表查询)
笛卡尔乘积现象:表1有m行,表2有n行,结果=m*n行
发生原因:没有有效的连接条件

分类:
按年代分类:
sq192标准仅仅支持内连接
sq199标准(推荐) 不支持全外连接
按功能分类
内连接:
               等值连接
               非等值连接
               自连接
       外连接:
               左外连接
               右外连接
               全外连接
       交叉连接

#查询女神名对应的男神名
SELECT `name`,`boyName` FROM `boys`,`beauty`
WHERE `beauty`.`boyfriend_id`=`boys`.`id`;

#查询员工名和对应的部门名
SELECT `last_name`,`department_name`
FROM `employees`,`departments`
WHERE `employees`.`department_id`=`departments`.`department_id`;

#查询员工名,工种号,工种名
SELECT `last_name`,`employees`.`job_id`,`job_title`
FROM `employees`,`jobs`
WHERE `jobs`.`job_id`=`employees`.`job_id`;

#查询有奖金的员工名和部门名
SELECT `last_name`,`department_name`,`commission_pct`
FROM `employees`,`departments`
WHERE `employees`.`department_id`=`departments`.`department_id`
AND `employees`.`commission_pct` IS NOT NULL ;

#查询城市名中第二个字符为o的部门名和城市名
SELECT `department_name`,`city`
FROM `departments`,`locations`
WHERE `locations`.`location_id`=`departments`.`location_id`
AND`city` LIKE '_o%';

#查询每个城市的部门个数
SELECT COUNT(*) 个数,`department_name`,`city`
FROM `departments`,`locations`
WHERE `departments`.`location_id`=`locations`.`location_id`
GROUP BY `city`;

#查询有奖金的每个部门的部门名和部门领导编号和该部门的最低工资
SELECT `department_name`,`departments`.`manager_id`,`commission_pct`,MIN(`salary`)
FROM `departments`,`employees`
WHERE`departments`.`department_id`=`employees`.`department_id`
AND `commission_pct` IS NOT NULL
GROUP BY `department_name`;

#查询每个工种的工种名和员工的个数,并且按员工个数降序
SELECT `jobs`.`job_id`,`job_title`,`employee_id`,COUNT(*)
FROM `jobs`,`employees`
WHERE `jobs`.`job_id`=`employees`.`job_id`
GROUP BY `jobs`.`job_id`
ORDER BY COUNT(*) DESC;

#查询员工名,部门名,和所在的城市
SELECT `last_name`,`department_name`,`city`
FROM `departments`,`employees`,`locations`
WHERE `departments`.`department_id`=`employees`.`department_id`
AND `departments`.`location_id`=`locations`.`location_id`;

14.

非等值连接
#查询员工的工资和工资级别

SELECT `salary`,`grade_level`
FROM `employees`,`job_grades`
WHERE `salary` BETWEEN `job_grades`.`lowest_sal` AND `job_grades`.`highest_sal`;
15.自连接
#查询员工名和上级的名称
SELECT e.`employee_id`,e.`last_name`,m.`employee_id`,m.`last_name`
FROM `employees` e,`employees` m
WHERE e.`manager_id`=m.`employee_id`;

16.

sq199语法
/*
select 查询列表
from 表1 别名 {连接类型}
jion 表2 别名
on 连接条件
{where 筛选条件}
{group by 分组}
{having 筛选条件}
{order by 排序列表}

连接类型:
内连接:inner
左外连接:left
右外连接:right
全外连接:full
交叉连接:cross
0

内连接
#查询员工名,部门名
SELECT `last_name`,`department_name`
FROM `employees`
INNER JOIN `departments`
ON `employees`.`department_id`=`departments`.`department_id`;

#查询名字中包含e的员工名和工种名
SELECT `last_name`,`job_title`
FROM `employees`
INNER JOIN `jobs`
ON `employees`.`job_id`=`jobs`.`job_id`
WHERE `last_name` LIKE '%e%';

#查询哪个部门的员工个数>3的部门名和员工个数,并按个数进行降序
SELECT `department_name`,COUNT(*) 员工个数
FROM `departments`
INNER JOIN `employees`
ON `departments`.`department_id`=`employees`.`department_id`
GROUP BY`department_name`
HAVING 员工个数 >3
ORDER BY 员工个数 DESC;

#查询员工名,部门名,工种名
SELECT `last_name`,`department_name`,`job_title`
FROM `employees`
INNER JOIN `departments` ON `employees`.`department_id`=`departments`.`department_id`
INNER JOIN `jobs` ON `employees`.`job_id`=`jobs`.`job_id`
ORDER BY `department_name` DESC;

非等值连接
#查询员工的工资级别
SELECT `salary`,`grade_level`
FROM `employees`
JOIN `job_grades`
ON `employees`.`salary` BETWEEN `job_grades`.`lowest_sal` AND `job_grades`.`highest_sal`;

自连接
#查询员工的名字和上级的名字
SELECT e.`last_name` ,m.`last_name`
FROM `employees` e
JOIN `employees` m
ON e.`manager_id`=m.`employee_id
17.

外连接:用来查询一个表中有,一个表中没有
外连接的查询结果为主表中的所有记录
           如果从表中有和他匹配的,则显示匹配的值
           如果从表中没有,则显示NULL
           结果=内连接结果+主表中有而从表中没有的记录

   左外连接:LEFT JOIN左边的是主表
   0右外连接:RIGHT JOIN右边的是主表

#查询男朋友不在男神表的女神名
SELECT `beauty`.`name`,`boys`.*
FROM `beauty`
LEFT JOIN `boys`
ON `boys`.`id`=`beauty`.`id`
WHERE `boys`.`id` IS NULL

#查询哪个部门没有员工
SELECT `department_name`,`last_name`
FROM `departments`
LEFT JOIN `employees`
ON `departments`.`department_id`=`employees`.`department_id`
WHERE `employees`.`employee_id` IS NULL

#交叉连接笛卡尔乘积
SELECT `beauty`.*,`boys`.*
FROM `beauty`
CROSS JOIN `boys`

18.

EXISTS后面(相关子查询)
EXISTS(完整的查询语句)

SELECT EXISTS(SELECT `employee_id` FROM `employees`)

#查询有员工名的部门名
SELECT `department_name`
FROM `departments`
WHERE EXISTS(
SELECT *
FROM `employees`
WHERE `departments`.`department_id`=`employees`.`department_id`
)

SELECT `department_name`
FROM `departments`
WHERE `departments`.`department_id` IN(
SELECT `department_id`
FROM `employees`
)

19.

FROM后面

#查询每个部门的平均工资的工资等级
SELECT 平均工资.*,`job_grades`.`grade_level`
FROM (
   SELECT AVG(`salary`),`department_id`
   FROM `employees`
   GROUP BY `department_id`
) 平均工资
INNER JOIN `job_grades`
ON 平均工资. AVG(`salary`) BETWEEN `lowest_sal` AND `highest_sal`

20.

SELECT 后面

#查询每个部门的员工个数
SELECT `departments`.`department_id`,`department_name`,(
   SELECT COUNT(*)
   FROM `employees`
   WHERE `employees`.`department_id`=`departments`.`department_id`
) 员工个数
FROM `departments`

#查询员工号=102的部门名
SELECT (
   SELECT `department_name`
   FROM `departments`
   INNER JOIN `employees`
   0ON `departments`.`department_id`=`employees`.`department_id`
   WHERE `employees`.`employee_id`=102
) 部门名
21.

子查询:出现在其他语句中的SELECT语句,称为子查询或内查询
外部的查询语句,称为主查询或外查询
分类: SELECT后面: 仅仅支持标量子查询
   FROM后面: 支持表子查询
   WHERE或者HAVING后面: 标量子查询,列子查询,行子查询
   EXISTS后面: 表子查询

#where 或者 having后面
#子查询放在小括号内
子查询一般放在条件的右侧
标量子查询,一般搭配着单行操作符使用:> < >= <= <>
列子查询,一般搭配多行操作符使用: IN ANY/SOME
1.标量子查询(单行子查询)
#谁的工资比Abel高?
SELECT *
FROM `employees`
WHERE `salary`>(
       SELECT `salary`
       FROM `employees`
       WHERE `last_name` ='Abel'
);
#返回`job_id`与141号员工相同,salary比143号员工多的员工姓名 `job_id` 和工资
SELECT `last_name`,`job_id`,`salary`
FROM `employees`
WHERE `job_id` =(
   SELECT `job_id`
   FROM `employees`
   WHERE `employee_id` = 141
) AND salary>(
   SELECT `salary`
   FROM `employees`
   WHERE `employee_id`=143
);

#返回公司工资最少的员工的`last_name`,`salary`
SELECT `last_name`,`salary`
FROM `employees`
WHERE `salary` =(
SELECT MIN(`salary`)
FROM `employees`
);

#查询最低工资大于50号部门最低工资的部门id和其最低工资
SELECT `department_id`,MIN(`salary`) 最低工资
FROM `employees`
GROUP BY `department_id`
HAVING 最低工资>(
SELECT MIN(`salary`)
FROM `employees`
WHERE `department_id`=50
);

2.列子查询
#返回`location_id`是1400或1700的部门中的所有的员工姓名
SELECT `last_name`
FROM `employees`
WHERE `department_id` IN(
SELECT `department_id`
FROM `departments`
WHERE `location_id` IN(1400,1700)
);

#返回其他部门中比`job_id`为'IT_PROG'部门任意工资低的员工的员工工号,姓名,`job_id`,`salary`
SELECT `employee_id`,`last_name`,`job_id`,`salary`
FROM `employees`
WHERE `salary` < ANY (
SELECT `salary`
FROM `employees`
WHERE `job_id` ='IT_PROG'
)

3.行子查询选择语句需要一样的操作符
#查询员工编号最小并且工资最高的员工信息
SELECT *
FROM `employees`
WHERE (`employee_id`,`salary`)=(
SELECT MIN(`employee_id`),MAX(`salary`)
FROM `employees`
);

二:DML语言

/*
数据操作语言
插入:insert
修改:update
删除:delete
.
*/
1.插入语句:
方式一:
语法:
INSERT INTO 表名(列名,.....)
VALUES(值);

#1.插入的值的类型要与列的类型一直或兼容
INSERT INTO `beauty`(id,NAME,sex,borndate,`phone`,`photo`,`boyfriend_id`)
VALUES(13,'唐艺兮','女','1990-4-23','1898888888',NULL,2);

SELECT * FROM `beauty`

#2.不可以为null的列必须插入值,可以为null的列如何插入值

INSERT INTO `beauty`(id,NAME,sex,borndate,`phone`,`photo`,`boyfriend_id`)
VALUES(13,'唐艺兮','女','1990-4-23','1898888888',NULL,2);
#可以为null的列可以省略
INSERT INTO `beauty`(id,NAME,sex,borndate,`phone`,`boyfriend_id`)
VALUES(14,'金星','女','1990-4-23','1398888888',9);

#3.列的顺序可以调换
#4.列数和值的个数必须一致
#5.可以省略列名,默认所有列,而且列的顺序和表中的列的顺序一致

#6.一次插入多行
INSERT INTO `beauty`
VALUES (16,'唐艺兮1','女','1990-4-23','1898888888',NULL,2),
(17,'唐艺兮2','女','1990-4-23','1898888888',NULL,2),
(18,'唐艺兮3','女','1990-4-23','1898888888',NULL,2);

#7.支持子查询
INSERT INTO `beauty`(`id`,`name`,`phone`)
SELECT `id`,`boyName`,'123546'
FROM `boys`
WHERE `id`<3;

方式二:
语法:
INSERT INTO 表名
SET 列名=值,列名=值..........

INSERT INTO `beauty`
SET id=15,NAME='刘涛',phone='4566464';

2.删除语言

/*
数据操作语言
插入:insert
修改:update
删除:delete
.
*/
插入语句:
方式一:
语法:
INSERT INTO 表名(列名,.....)
VALUES(值);

#1.插入的值的类型要与列的类型一直或兼容
INSERT INTO `beauty`(id,NAME,sex,borndate,`phone`,`photo`,`boyfriend_id`)
VALUES(13,'唐艺兮','女','1990-4-23','1898888888',NULL,2);

SELECT * FROM `beauty`

#2.不可以为null的列必须插入值,可以为null的列如何插入值

#3.列的顺序可以调换
#4.列数和值的个数必须一致
#5.可以省略列名,默认所有列,而且列的顺序和表中的列的顺序一致

#7.支持子查询
INSERT INTO `beauty`(`id`,`name`,`phone`)
SELECT `id`,`boyName`,'123546'
FROM `boys`
WHERE `id`<3;

方式二:
语法:
INSERT INTO 表名
SET 列名=值,列名=值..........

INSERT INTO `beauty`
SET id=15,NAME='刘涛',phone='4566464';

修改语句
语法:
1:修改单表的记录
UPDATE 表名
SET 列=新值,列=新值.....
WHERE 筛选条件;

#修改beauty表中姓唐的女神的电话为13899888899
UPDATE `beauty`
SET `phone`=13899888899
WHERE `name` LIKE '唐%';

#把boys表中的id号为2的名称为张飞,魅力值为10
`boyName`

修改多表的记录
192语法:
UPDATE 表1 别名 ,表2 别名
SET 列=值,.....
WHERE 连接条件
AND 筛选条件

199语法:
UPDATE 表1 别名
INNER/LEFT/RIGHT JOIN 表2 别名
ON 连接条件
SET 列=值,.......
WHERE 筛选条件

#修改张无忌的女朋友的手机号为114
UPDATE `beauty`
INNER JOIN `boys`
ON `beauty`.`boyfriend_id`=`boys`.`id`
SET `beauty`.`phone`=114
WHERE `boyfriend_id`=1;

#修改没有男朋友的女神的男朋友编号都为2号
UPDATE `beauty`
LEFT JOIN `boys`
ON `beauty`.`boyfriend_id`=`boys`.`id`
SET `beauty`.`boyfriend_id`=2
WHERE `boyfriend_id` IS NULL

三:DDL语言

表的创建:
/*create table 表名(
           列名列的类型 [长度],
           列名列的类型 [长度],
           列名列的类型 [长度],
           列名列的类型 [长度],.....
)
*/
#案例:创建book表
CREATE TABLE book(
   id INT,#编号
   bName VARCHAR(20),#书名
   price DOUBLE,#价格
   authorId INT,#作者编号
   publishDate DATETIME#出版日期
);
DESC book

#创建author表
CREATE TABLE author(
       id INT,
       au_name VARCHAR(20),
       nation VARCHAR(10)
);

表的修改:
#修改列名
ALTER TABLE book CHANGE COLUMN publishDate pubDate DATETIME
#修改列的类型
ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMP
#添加新列
ALTER TABLE author ADD COLUMN annual DOUBLE;
#删除列
ALTER TABLE book DROP COLUMN annual;
#修改表名
ALTER TABLE author RENAME TO book_author;

DROP TABLE IF EXISTS stuinfo

CREATE TABLE stuinfo(
       id INT,
       stuname 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 EXISTS stuinfo(
       id INT PRIMARY KEY,
       stuname VARCHAR(20) NOT NULL,
       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 stuinfo MODIFY COLUMN stuinfo VARCHAR(20) NOT NULL;
2.添加默认约束
   ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;
3.添加主键
   ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;

数据定义语言

库和表的管理

#库的管理: 创建,修改,删除
#表的管理: 创建,修改,删除
创建 CREATE
修改 ALTER
删除 DROP

库的创建
# create database [if not exists]库名:如果库不存在就创建,存在就不创建

#创建库BOOKS
CREATE DATABASE books;

库的修改
RENAME DATABASE books TO 新库名

更改库的字符集
ALTER DATABASE books CHARACTER SET gbk;

库的删除
DROP DATABASE IF EXISTS books

六大约束:
NOT NULL:非空
DEFAULT:默认
PRIMARY KEY:主键,不可以为空,保证该字段的值具有唯一性,并且非空
UNIQUE:用于保证该字段的值唯一,可以为空
CHECK:检查(mysql中不支持)
FOREIGN KEY:外键,用于限制两个表的关系,用于保证该字段的值必须来自于主表的关联

#添加列级约束
CREATE DATABASE students;
USE students
CREATE TABLE stuinfo(
       id INT PRIMARY KEY,#主键
       stuName VARCHAR(20) NOT NULL,
       gender CHAR(1) CHECK (gender='男' OR gender='女'),
       seat INT UNIQUE,#唯一
       age INT DEFAULT 18,#默认18
       majorid INT REFERENCES major(id)#外键
);

CREATE TABLE majorid(
id INT PRIMARY KEY,
majorname VARCHAR(20)
);
SHOW INDEX FROM stuinfo

四:事务

TCL:事务控制语言
事务:
一个或一组SQL语句组成一个执行单元,这个执行单元要么全部执行,要么全部不执行

1.在mysql中的数据用各种不同的技术存储在文件或内存中
2.通过SHOW ENGINES;来查看mysql中支持的存储引擎
3.在mysql中用的最多的存储引擎有:INNODB,MYISAM,MEMORY等
4.其中INNODB支持事务,二muisam,MEMORY等不支持事务
5.事务的acid属性
       原子性:原子性是指事务是一个不可分割的工作单位,事务中的操作要么都发生,要么都不发生
       一致性:事务必须使数据库从一个一致性状态变换到另外一个一致性状态
       隔离性:事务的隔离性是指一个事务的执行不能被其他事务干扰,
           即一个事务内部的操作及使用的数据对并发的其他事务是隔离的,
           并发执行的各个事务之间不能相互干扰
       持久性:持久性是指一个事务一旦被提交,他对数据库中数据的改变是永久性的,
           接下来的其他操作和数据故障不应该对其有任何影响

事务的创建
隐式事务:事务没有明显的开启和结束的标记
比如INSERT,UPDATE,DELETE语句

显示事务:

CREATE TABLE account(
       id INT PRIMARY KEY AUTO_INCREMENT,
       username VARCHAR(20),
       balance DOUBLE
);
INSERT INTO account(username,balance)
VALUES('张无忌',1000),('赵敏',1000);
#开启事务:
SET autocommit =0;
START TRANSACTION;
#编写一组事务的语句
UPDATE account SET balance = 500 WHERE username='张无忌';
UPDATE account SET balance = 1500 WHERE username='赵敏';
#结束事务
COMMIT;
#回滚
ROLLBACK;
SELECT * FROM `account`

对于同时运行的多个事务,当这些事务访问数据库中相同的数据时,如果没有采取必要的隔离机制,
就会导致各种并发问题