数据库的好处
实现数据持久化
使用完整的管理系统统一管理，易于查询
存储大量数据，便于实现共享
安全
DB：数据库(dataBase)，存储数据的仓库
DBMS：数据库管理系统(DataBase Management System)，数据库是通过DBMS创建和操作的容器
SQL：结构化查询语言(Structure Query Language),专门用来与DBMS通信的语言
SQL的优点：
1、不是某个特定数据库供应商专有的语言，几乎所有DBMS都支持SQL
2、简单易学
3、虽然简单，但实际上是一种强有力的语言，灵活使用其语言元素，可以进行非常复杂和高级的数据库操作
SQL语言分类：
1、DML（Data Manipulation Language):数据操纵语句，用于添加、删除、修改、查询数据库记录，并检查数据完整性
INSERT：添加数据到数据库中
UPDATE：修改数据库中的数据
DELETE：删除数据库中的数据
2、DDL（Data Definition Language):数据定义语句，用于库和表的创建、修改、删除。
CREATETABLE：创建数据库表
ALTER TABLE：更改表结构、添加、删除、修改列长度
DROP TABLE：删除表
CREATE INDEX：在表上建立索引
DROP INDEX：删除索引
3、DCL（Data Control Language):数据控制语句，用于定义用户的访问权限和安全级别。
GRANT：授予访问权限
REVOKE：撤销访问权限
COMMIT：提交事务处理
ROLLBACK：事务处理回退
SAVEPOINT：设置保存点
LOCK：对数据库的特定部分进行锁定
4、DQL（Data Query Language):数据查询语言，用来查询记录(数据)
SELECT：选择（查询）数据
DBMS分为两类：
–基于共享文件系统的DBMS （Access）
–基于客户机——服务器的DBMS（MySQL、Oracle、SqlServer）
启动&&停止
第一：右击计算机-管理-服务
第二：通过管理员命令行
net start 服务名
net stop 服务名
登录&&退出
第一：通过自带的客户端，输入账号密码，退出时，exit或者ctrl+c一起按
第二：通过命令行，输入指令：mysql -h localhost(地址) -P+(端口号) -u+账号 -p+密码或者回车隐藏密码
若提示'mysql' 不是内部或外部命令，也不是可运行的程序或批处理文件。则需要重新配置环境变量
mysql常见命令
show databases; 查看数据库中的所有数据库
show tables; 查看数据库中的所有表
create database 数据库名; 创建一个数据库
desc 表名; 查看表的结构
select version; 查看版本
数据库存储数据的特点
1、数据先放在表中，表再放在库中
2、一个库可以有多张表，每张表都有自己的唯一标识名
3、一张表的设计，类似于java中”类"的设计
表中的字段的设计，类似于属性的设计
表中的单条记录，类似于一个对象
表中的所有的记录，类似于对象的集合
orm ：object relation mapping 对象关系映射
SQL语法规范：
不区分大小写
每句话用;或\g结尾
各子句一般分行写
关键字不能缩写也不能分行
用缩进提高语句的可读性
注释
单行注释:#注释文字或者--注释文字
多行注释:/*注释文字*/
DQL
查询
进阶一：
1.查询表中的单个字段
SELECT last_name FROM employees;
2.查询表中的多个字段
SELECT last_name,salary,email FROM employees;
3.查询表中的所有字段
方式一：
SELECT 所有属性名 FROM employees ;
方式二：
SELECT * FROM employees;
4.查询常量值
SELECT 100;
SELECT 'john';
5.查询表达式
SELECT 100%98;
6.查询函数
SELECT VERSION();
7.起别名
/*
①便于理解
②如果要查询的字段有重名的情况，使用别名可以区分开来
*/
方式一：使用as
SELECT 100%98 AS 结果;
SELECT last_name AS 姓,first_name AS 名 FROM employees;
方式二：使用空格
SELECT last_name 姓,first_name 名 FROM employees;
#案例：查询salary，显示结果为 out put
SELECT salary AS "out put" FROM employees;
8.去重
#案例：查询员工表中涉及到的所有的部门编号
SELECT DISTINCT department_id FROM employees;
9.+号的作用
/*
java中的+号：
①运算符，两个操作数都为数值型
②连接符，只要有一个操作数为字符串
mysql中的+号：
仅仅只有一个功能：运算符
select 100+90; 两个操作数都为数值型，则做加法运算
select '123'+90;只要其中一方为字符型，试图将字符型数值转换成数值型
如果转换成功，则继续做加法运算
select 'john'+90; 如果转换失败，则将字符型数值转换成0
select null+10; 只要其中一方为null，则结果肯定为null
*/
#案例：查询员工名和姓连接成一个字段，并显示为 姓名
SELECT CONCAT('a','b','c') AS 结果;
SELECT CONCAT(last_name,first_name) AS 姓名 FROM employees;
10.拼接
CONCAT关键字
SELECT CONCAT(字符1，字符2，...) AS "显示结果" FROM 表名;
其中的属性不能为NULL，
借助IFNULL解决
SELECT CONCAT(属性) AS "显示结果" IFNULL(属性，‘空’) FROM 表名
IFNULL(表达式一，表达式二)
表达式一:可能为NULL的字段或者表达式
表达式二:如果一为NULL，则显示表达式二，否则显示表达式一
进阶查询二：
/*
语法：
select
查询列表
from
表名
where
筛选条件;
执行顺序：
FROM子句
WHERE子句
SELECT子句
分类：
一、按条件表达式筛选
简单条件运算符：> < = <> >= <=,也可以使用!=，但是不建议
二、按逻辑表达式筛选
逻辑运算符：
作用：用于连接条件表达式
and or not(也可以使用&& || !，但是不建议)
&&和and：两个条件都为true，结果为true，反之为false
||或or： 只要有一个条件为true，结果为true，反之为false
!或not： 如果连接的条件本身为false，结果为true，反之为false
三、模糊查询
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 NOT(department_id>=90 AND department_id<=110) OR salary>15000;
三、模糊查询
1.like
/*
not like 与like相反
特点：
①一般和通配符搭配使用
通配符：
% 任意多个字符,包含0个字符
_ 任意单个字符
*/
#案例1：查询员工名中包含字符a的员工信息
select * from employees where last_name like '%a%';#abc
#案例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 '_$_%' ESCAPE '$';(ESCAPE转义字符)
SELECT last_name FROM employees WHERE last_name LIKE '_\_%' ;
2.between and
/*
①使用between and 可以提高语句的简洁度
②包含临界值
③两个临界值不要调换顺序
*/
#案例1：查询员工编号在100到120之间的员工信息
SELECT * FROM employees WHERE employee_id >= 120 AND employee_id<=100;
SELECT * FROM employees WHERE employee_id BETWEEN 120 AND 100;
#案例2：查询年薪在100000-200000之间的员工信息
SELECT * FROM employees WHERE salary*12*(1+IFNULL(commission_pct,0)) NOT BETWEEN 100000 AND 200000;
3.in
/*
含义：判断某字段的值是否属于in列表中的某一项
not in恰好相反
特点：
①使用in提高语句简洁度
②in列表的值类型必须一致或兼容
③in列表中不支持通配符
*/
#案例：查询员工的工种编号是 IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号
SELECT
last_name,
job_id
FROM
employees
WHERE
job_id = 'IT_PROT' OR job_id = 'AD_VP' OR JOB_ID ='AD_PRES';
#------------------
SELECT
last_name,
job_id
FROM
employees
WHERE
job_id IN( 'IT_PROT' ,'AD_VP','AD_PRES');
4、is null
/*
=或<>不能用于判断null值
is null或is not null 可以判断null值
*/
#案例1：查询没有奖金的员工名和奖金率
SELECT
last_name,
commission_pct
FROM
employees
WHERE
commission_pct IS NULL;
#案例2：查询有奖金的员工名和奖金率
SELECT
last_name,
commission_pct
FROM
employees
WHERE
commission_pct IS NOT NULL;
#----------以下为×
SELECT
last_name,
commission_pct
FROM
employees
WHERE
salary IS 12000;
#安全等于 <=>
#案例1：查询没有奖金的员工名和奖金率
SELECT
last_name,
commission_pct
FROM
employees
WHERE
commission_pct <=>NULL;
#案例2：查询工资为12000的员工信息
SELECT
last_name,
salary
FROM
employees
WHERE
salary <=> 12000;
#is null , <=> , =
= :只能判断普通内容
IS NULL:仅仅可以判断NULL值，可读性较高，建议使用
<=> :既可以判断NULL值，又可以判断普通的数值，可读性较低
进阶查询三：
/*
语法：
select
查询列表
from
表名
where
筛选条件;
order by
排序列表
执行顺序:
from 子句
where 子句
select 子句
order by子句
特点
排序列表可以是单个字段，多个字段，表达式，函数，列数，以及以上的组合
降序desc，升序是asc，默认是升序
*/
#案例1:将员工编号>120的员工信息进行工资的升序
SELECT
*
FROM
employees
WHERE
employee_id>120
ORDER BY
salary DESC;
#案例2:队友奖金的员工，按年薪降序(表达式)
SELECT
*，salary*12*(1+IFNULL(commission_pct,0))
FROM
employees
WHERE
commission_pct IS NOT NULL
ORDER BY
salary*12*(1+IFNULL(commission_pct,0)) DESC;
#案例3:队友奖金的员工，按年薪降序(别名)
SELECT
*，salary*12*(1+IFNULL(commission_pct,0))
FROM
employees
WHERE
commission_pct IS NOT NULL
ORDER BY
年薪 DESC;
(因为执行顺序的原因，WHERE不能使用别名)
#案例4:按姓名的字数长度进行升序排列
SELECT
last_name
FROM
employees
ORDER BY
LENGTH(last_name);
#案例5:查询员工的姓名，工资，部门编号，先按工资升序，再按部门编号降序
SELECT
last_name,
salary,
department_id
FROM
employees
ORDER BY
salary ASC,
department_id DESC;
#案例6:按列数排序
SELECT
*
FROM
employees
ORDER BY
2;
#--------------
SELECT
*
FROM
employees
ORDER BY
列名;
常见函数
/*
函数：类似Java中学过的"方法"
为了解决某个问题，将编写的一系列的命令集合封装在一起，对外仅仅暴露方法名，供外部调用
好处:提高重用性和隐藏实现细节
调用函数
叫什么：函数名
干什么：函数功能
分类：
字符函数
数学函数
日期函数
流程控制函数
*/
一、字符函数
CONCAT 拼接字符
SELECT CONCAT(last_name,first_name) AS 姓名 FROM employees;
REPLACE 替换函数
REPLACE(object,search,replace)把object中出现search的全部替换为replace
LENGTH 获取字节长度
SELECT LENGTH('hello,World');
CHAR_LENGTH 获取字符个数
SELECT LENGTH('hello,World');
SUBSTR 截取子串(开始索引是1，不是0,截取长度不写则默认截取后续全部)
SELECT SUBSTR('hello,World',开始索引，截取的长度);
INSTR 获取字符第一次出现的索引
SELECT INSTR('三打白骨精qaaaa白骨精bbbb白骨','白骨精');
TRIM去前后指定的字符，默认是去空格
SELECT TRIM('x' FROM 'xxxxxxxxxxxx啦啦啦xxxxx啦啦啦xxxxxxx') AS a;
显示结果是:啦啦啦xxxxx啦啦啦
LPAD/RPAD 左填充/右填充
SELECT LPAD('木头',10,'a');
显示效果：aaaaaaaa木头
UPPER/LOWER 大写/小写
#案例：查询员工表的姓名，要求格式：姓首字符大写，其他的小写，名所有字符大写，姓与名之间用_分隔，最后起别名OUTPUT
SELECT UPPER(SUBSTR(first_name,1,1)) ,first_name FROM employees;
SELECT LOWER(SUBSTR(first_name,2)) ,first_name FROM employees;
SELECT UPPER(last_name) FROM employees;
SELECT CONCAT(UPPER(SUBSTR(first_name,1,1)),LOWER(SUBSTR(first_name,2)),'_',UPPER(last_name)) "OUTPUT" FROM employees;
STRCMP 比较俩个字符串的大小(如果这两个字符串相等返回0，如果第一个参数是根据当前的排序小于第二个参数顺序返回-1，否则返回1。)
SELECT STRCMP('aec','abc');
LEFT/RIGHT 从左/右截取子串
SELECT LEFT('你好',1);
二、数学函数
ABS 绝对值
SELECT ABS(-2.4);
CEIL 向上取整(返回>=该参数的最小整数)
SELECT CEIL(-1.09);
FLOOR 向下取整(返回<=该参数的最小整数)
SELECT FLOOR(-1.09);
ROUND 四舍五入
SELECT ROUND(1.8712345);
显示结果：2
SELECT ROUND(1.8712345,2);
显示结果：1.87
TRUNCATE 截断
SELECT TRUNCATE(1.8712345,1);
显示结果：1.8
MOD 取余
SELECT MOD(-10,3);
三、日期函数
NOW 获取日期和时间
SELECT NOW();
CURDATE 只获取日期
SELECT CURDATE();
CURTIME 只获取时间
SELECT CURTIME();
DATEDIFF 获取日期差
SELECT DATEDIFF('1998-1-16','2019-7-13');
DATE_FORMAT 按字符串fmt格式化日期datetime值
SELECT DATE_FORMAT('1998-7-16','%Y年%M月%d日 %H小时%i分钟%s秒') 出生日期;
显示效果:1998年July月16日 00小时00分钟00秒
STR_TO_DATE 按指定格式解析字符串为日期类型
SELECT * FROM employees WHERE hiredate < STR_TO_DATE('3/15 1998'.'%m/%d %Y');
四、流程控制函数
IF函数(类似于三目运算符)
SELECT IF(100>9,'好','坏');
#案例1:如果有奖金，则显示最终奖金，如果没有，则显示0
SELECT IF(commission_pct IS NULL,0,salary*12*commission_pct) 奖金,commission_pct FROM employees;
CASE函数
情况一:
CASE 表达式
WHEN 值1 THEN 结果1
WHEN 值2 THEN 结果2
...
ELSE 结果n
END
案例1:部门编号为30，工资为2倍；
部门编号为60，工资为4倍；
部门编号为50，工资为3倍；
显示部门编号，新工资，旧工资
SELECT department_id,salary,
CASE department_id
WHEN 30 THEN salary*2;
WHEN 50 THEN salary*3;
WHEN 60 THEN salary*4;
ELSE salary
END newsalary
FROM employees;
情况二：
类似于多重IF语句，实现区间判断
案例1：如果工资>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 grade
FROM employees;
五、分组函数
用于实现将一组数据进行统计计算，最终得到一个值，又称为聚合函数或统计函数
(均忽略NULL值，都可以和DISTINCT搭配)
sum 求和
avg 求平均数
max 求最大值
min 求最小值
count 计算非空字段值的个数
distinct 去重
#案例1:查询员工信息表中，所有员工的工资和、工资平均值、最低工资、最高工资、有工资的个数。
SELECT SUM(salary),AVG(salary),MIN(salary),MAX(salary),COUNT(salary) FROM employees;
COUNT补充
COUNT(*):用于查询结果集中的总行数
COUNT(常量):在结果集中新加一个常量列
MyISAM存储引擎，count(*)最高
InnoDB存储引擎，count(*)和count(1)效率>count("字段")
六、其他函数
md5(字符):返回MD5加密
PASSWORD(字符):返回加密
分组查询
/*
语法:
SELECT 分组函数,分组后的字段
FROM 表名
WHERE 筛选条件
GROUP BY 分组的字段
HAVING 筛选条件
ORDER BY 排序
执行顺序：
①from子句
②where子句
③group by 子句
④having子句
⑤select子句
⑥order by子句
*/
GROUP BY
#案例1;显示每个部门的工资之和
SELECT SUM(salary), department_id
FROM employees
GROUP BY department_id;
#案例2:查询每个工种的员工平均工资
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id;
#案例3:查询每个领导手下的人数
SELECT COUNT(*),manage_id
FROM employees
WHERE manage_id IS NOT NULL
GROUP BY manage_id;
分组查询中的筛选分为两类
筛选的基表 使用的关键词 位置
分组前筛选 原始表 where group by 的前面
分组后筛选 分组后的结果集 having group by 的后面
#案例4:查询邮箱中包含a字符的每个部门的最高工资
SELECT MAX(salary) 最高工资,department_id
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id;
#案例5:查询那个部门的员工个数>5(WHERE不支持分组函数做条件)
SELECT COUNT(*) 员工个数,department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*)>5
#案例6;每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
SELECT job_id,MAX(salary)
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary)>12000;
#案例7:查询有奖金的员工的最高工资>6000的工种编号和最高工资，按最高工资升序
SELECT MAX(salary) 最高工资,job_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary)>6000
ORDER BY MAX(salary) ASC;
#案例8:查询每个工种每个部门的最低工资，并按最低工资降序
SELECT MIN(salary) 最低工资,department_id,job_id
FROM employees
GROUP BY job_id,department_id
ORDER BY MIN(salary) desc;
补充:查询条件能在WHERE中的就不要放在HAVING后面
连接查询(笛卡尔乘积)
/*
含义：多表查询，当查询的时候涉及多个表的时候，就会用到连接查询
笛卡尔乘积现象:表一 有M行，表二有N行，结果是M*N行
发生的原因;没有有效的连接条件
如何避免;添加有效的连接条件
分类:
按年代分类:
SQL 92标准
仅支持内连接
SQL 99标准【推荐】
支持内连接+外连接(左外和右外)+交叉连接
按功能分类
内连接
等值连接
非等值连接
自连接
外连接
左外连接
右外连接
全外连接
交叉连接
*/
一、SQL92标准
等值连接
/*
① 多表等值连接的结果为多表的交集部分
②n表连接，至少需要n-1个连接条件
③ 多表的顺序没有要求
④一般需要为表起别名
⑤可以搭配前面介绍的所有子句使用，比如排序、分组、筛选
*/
#案例1:查询女生名对应的男朋友的名字
SELECT
NAME,boyName
FROM
boys,beauty
WHERE
beauty.`boyfriend_id`=boys.`id`;
起别名可以提高语句的简洁度，区分多个重名的字段
但是查询的字段就不能用原来的表名去限定了
#案例2;查询员工名、工种号、工种名
SELECT
employees.last_name,employees.job_id,job_title
FROM
employees e,jobs j
WHERE
e.job_id=j.job_id;
#案例3:查询有奖金的员工名、部门名
SELECT
last_name,department_name
FROM
employees e,departments d
WHERE
e.department_id=d.department_id
AND
e.commission_pct IS NOT NULL;
加分组
案例4:查询每个城市的部门个数
SELECT
COUNT(*) 个数,city
FROM
departments d,locations l
WHERE
d.`location_id`=l.`location_id`
GROUP BY
city;
#案例5:查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
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`;
加排序
#案例6:查询每个工种的工种名和员工的个数，并且按照员工的个数降序
SELECT
job_title,COUNT(*)
FROM
employees e,jobs j
WHERE
e.`job_id`=j.`job_id`
GROUP BY
job_title
ORDER BY
COUNT(*) DESC;
三表连接
#案例7:查询员工名、部门名和所在的城市
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`;
非等值连接
#案例1:查询员工的工资和工资级别
SELECT
salary,job_grades
FROM
employees e,job_grade jg
WHERE
salary BETWEEN jg.`lowest_sal` AND jg.`highest_sal`;
自连接
#案例2:查询员工名和上级的名称
SELECT
last_name,employee_id
FROM
employees e,employees m
WHERE
e.`manager_id`=m.`employee_id`;
二、SQL99语法
/*
语法:
SELECT
查询列表
FROM
表1 别名【连接类型】
JOIN
表2 别名
ON
连接条件
【WHERE 筛选条件】
【GROUP BY 分组】
【HAVING 筛选条件】
【ORDER BY 排序列表】
分类:
内连接:inner
外连接;
左外 left【outer】
右外 right【outer】
全外 full【outer】
交叉连接: cross
*/
内连接
/*
语法：
select 查询列表
from 表1 别名
【inner】 join 表2 别名 on 连接条件
where 筛选条件
group by 分组列表
having 分组后的筛选
order by 排序列表
limit 子句;
特点：
①表的顺序可以调换
②内连接的结果=多表的交集
③n表连接至少需要n-1个连接条件
*/
等值连接:
/*
特点：
①添加排序、分组、筛选
②inner可以省略
③ 筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读
④inner join连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集
*/
#案例1:查询员工名和部门名
SELECT
last_name,department_name
FROM
departments d
INNER JOIN
employees e
ON
e.department_id=d.department_id;
#案例2:查询名字中包含e的员工名和工种名(添加筛选)
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%';
#案例3:查询部门个数>3的城市名称和部门个数(添加筛选和分组)
SELECT
city,COUNT(*) 部门个数
FROM
departments d
INNER JOIN
locations l
ON
d.`location_id`=l.`location_id`
GROUP BY
city
HAVING COUNT(*)>3;
#案例4:查询员工名、部门名、工种名，并按部门名降序（添加三表连接）
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;
非等值连接
#案例1:查询员工的工资级别
SELECT
salary,grade_level
FROM
employees e
JOIN
job_grades g
ON
e.`salary`
BETWEEN
g.`lowest_sal` AND g.`highest_sal`;
自连接
#案例1:查询员工的名字、上级的名字
SELECT
e.last_name,m.last_name
FROM
employees e
JOIN
employees m
ON
e.`manager_id`=m.`employee_id`;
外连接
/*
语法:
select 查询列表
from 表1 别名
left|right|full【outer】 join 表2 别名 on 连接条件
where 筛选条件
group by 分组列表
having 分组后的筛选
order by 排序列表
limit 子句;
特点:
1、外连接的查询结果为主表中的所有记录
如果从表中有和它匹配的，则显示匹配的值
如果从表中没有和它匹配的，则显示null
外连接查询结果=内连接结果+主表中有而从表没有的记录
2、左外连接，left join左边的是主表
右外连接，right join右边的是主表
3、左外和右外交换两个表的顺序，可以实现同样的效果
4、全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的(MYSQL不支持)
*/
#案例1:查询男朋友不在boy表中的女神名(左外连接)
SELECT
b.name,bs.*
FROM
beauty b
LEFT OUTER JOIN
boys bs
ON
b.boyfriend_id =bs.id
WHERE
bs.`id` IS NULL ;
#案例2:查询没有员工的部门
SELECT
d.*
FROM
departments d
LEFT OUTER JOIN
employees e
ON
d.`department_id`=e.`department_id`
WHERE
e.`employee_id` IS NULL;
#案例3:查找有男朋友的女神(全外连接)
SELECT
b.*,bo.*
FROM
beauty b
FULL OUTER JOIN
boys bo
ON
b.`boyfriend_id` = bo.id
WHERE
bo.id IS NOT NULL;
交叉连接
案例1:笛卡尔乘积
SELECT
b.*,bo.*
FROM
beauty b
CROSS JOIN
boys bo;
三、sql92 PK sql99pk
/*
功能：sql99支持的较多
可读性：sql99实现连接条件和筛选条件的分离，可读性较高
*/
子查询
概念:出现在其他语句内部的SELECT语句，称为子查询或内查询
内部嵌套其他SELECT语句的查询，称为外查询或主查询
外面的语句可以是insert、update、delete、select等，一般select作为外面语句较多
分类:
按位置
SELECT后面
仅标量子查询
FROM后面
支持表子查询
WHERE或HAVING后面
#标量子查询(单行子查询)
#列子查询(多行子查询)
行子查询
EXISTS后面(相关子查询)
表子查询
按结果集的行列数不同
标量子查询:结果集只有一行一列
列子查询:结果集只有一列多行
行子查询:结果集只有一行多列
表子查询:结果集一般为多行多列
WHERE或HAVING后面
#标量子查询(单行子查询)
#列子查询(多行子查询)
行子查询
/*
特点:
子查询放在小括号内
子查询一般放在条件的右侧
标量子查询，一般搭配着单行操作符使用
> < >= <= = <>
列子查询，一般搭配着多行操作符使用
in、any/some、all
子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果
*/
#1.标量子查询
#案例1:查询Abel的工资
SELECT
salary
FROM
employees
WHERE
last_name = 'Abel'
#案例2:查询员工的信息，满足 salary>案例1的结果
SELECT
*
FROM
employees
WHERE
salary>(
SELECT
salary
FROM
employees
WHERE
last_name = 'Abel'
);
#案例3:返回job_id与141号员工相同，salary比143号员工多的员工 姓名，job_id 和工资
SELECT
e.job_id,e.last_name,e.salary
FROM
employees e
WHERE
e.`job_id`=(
SELECT
job_id
FROM
employees
WHERE
employee_id=141)
AND
e.`salary`>(
SELECT
salary
FROM
employees
WHERE
employee_id = 143);
#案例4:返回公司工资最少的员工的last_name,job_id和salary
SELECT
last_name,job_id,salary
FROM
employees
WHERE
salary=(
SELECT
MIN(salary)
FROM
employees);
#案例5：查询最低工资大于50号部门最低工资的部门id和其最低工资
SELECT
MIN(salary),department_id
FROM
employees
GROUP BY
department_id
HAVING
MIN(salary)>(
SELECT
MIN(salary)
FROM
employees
WHERE
department_id = 50);
2、#列子查询
#案例1：返回location_id是1400或1700的部门中的所有员工姓名
SELECT
last_name
FROM
employees
WHERE
department_id =ANY(
SELECT DISTINCT
department_id
FROM
departments
WHERE
location_id IN(1400,1700)
);
3、#行子查询
#案例1:查询员工编号最小且工资最高的员工信息
SELECT
*
FROM
employees
WHERE
(employee_id,salary)=(
SELECT
MIN(employee_id),MAX(salary)
FROM
employees
);
SELECT后面(只支持标量子查询，即一行一列)
#案例1:查询每个部门的员工个数
SELECT
d.*,(SELECT
COUNT(*)
FROM
employees e
WHERE
e.department_id=d.`department_id`
FROM departments d;
#案例2:查询员工号=102的部门名
SELECT
(
SELECT
department_name
FROM
departments d
JOIN
employees e
ON
e.department_id=d.`department_id`
WHERE
e.employee_id=102
)
部门名;
FROM后面(将子查询结果充当一张表，要求必须起别名)
#案例1:查询每个部门的平均工资的工资登记
SELECT
DISTINCT
ag_dep.*,g.`grade_level`
FROM(
SELECT
AVG(salary) ag,department_id
FROM
employees
GROUP BY
department_id
) ag_dep
JOIN
job_grades g
ON
ag_dep.ag BETWEEN lowest_sal AND highest_sal;
EXISTS后面(相关子查询)
/*
语法：
exists(完整的查询语句)
结果：
1或0
*/
#案例1:查询有员工的部门名
SELECT
department_name
FROM
departments d
WHERE
EXISTS(
SELECT *
FROM employees e
WHERE e.department_id=d.department_id);
#案例2:查询没有女朋友的男生的信息
SELECT
bs.*
FROM
boys bs
WHERE
NOT EXISTS(
SELECT boyfriend_id
FROM beauty b
WHERE bs.`id`=b.`boyfriend_id`
)
分页查询
/*
应用场景:数据量大，服务器分页提交请求
语法：
select 查询列表
from 表
【join type join 表2
on 连接条件
where 筛选条件
group by 分组字段
having 分组后的筛选
order by 排序的字段】
limit 【offset,】size;
offset要显示条目的起始索引（起始索引从0开始）
size 要显示的条目个数
特点：
①limit语句放在查询语句的最后
②公式
要显示的页数 page，每页的条目数size
select 查询列表
from 表
limit (page-1)*size,size;
size=10
page
1 0
2 10
3 20
*/
#案例1：有奖金的员工信息，并且工资较高的前10名显示出来
SELECT
*
FROM
employees
WHERE
commission_pct IS NOT NULL
ORDER BY
salary DESC
LIMIT
10 ;
联合查询
/*
说明：当查询结果来自于多张表，但多张表之间没有关联，这个时候往往使用联合查询，也称为union查询
语法：
select 查询列表 from 表1 where 筛选条件
union
select 查询列表 from 表2 where 筛选条件
特点：
1、多条待联合的查询语句的查询列数必须一致，查询类型、字段意义最好一致
2、union实现去重查询
union all 实现全部查询，包含重复项
*/
#案例1：查询所有国家的年龄>20岁的用户信息
SELECT * FROM usa WHERE uage >20 UNION
SELECT * FROM chinese WHERE age >20 ;
#案例2：查询所有国家的用户姓名和年龄(错误的)
SELECT uname,uage FROM usa
UNION
SELECT age,`name` FROM chinese;
#案例3：union自动去重/union all 可以支持重复项
SELECT 1,'范冰冰'
UNION ALL
SELECT 1,'范冰冰'
UNION ALL
SELECT 1,'范冰冰'
UNION ALL
SELECT 1,'范冰冰' ;
DDL
库的管理
#一、创建数据库
CREATE DATABASE stuDB;
CREATE DATABASE IF NOT EXISTS stuDB;
#二、删除数据库
DROP DATABASE stuDB;
DROP DATABASE IF EXISTS stuDB;
表的管理
#一、创建表
数据类型：
1、整型
TINYINT SMALLINT INT BIGINT
2、浮点型
FLOAT(m,n)
DOUBLE(m,n)
DECIMAL(m,n)
m和n可选
3、字符型
CHAR(n):n可选
VARCHAR(n)：n必选
TEXT
n表示最多字符个数
4、日期型
DATE TIME DATETIME TIMESTAMP
5、二进制型
BLOB 存储图片数据
常见约束
说明：用于限制表中字段的数据的，从而进一步保证数据表的数据是一致的、准确的、可靠的！
NOT NULL 非空：用于限制该字段为必填项
DEFAULT 默认：用于限制该字段没有显式插入值，则直接显式默认值
PRIMARY KEY 主键：用于限制该字段值不能重复，设置为主键列的字段默认不能为空
一个表只能有一个主键，当然可以是组合主键
UNIQUE 唯一：用于限制该字段值不能重复
字段是否可以为空 一个表可以有几个
主键 × 1个
唯一 √ n个
CHECK检查：用于限制该字段值必须满足指定条件
CHECK(age BETWEEN 1 AND 100)
FOREIGN KEY 外键:用于限制两个表的关系,要求外键列的值必须来自于主表的关联列
要求：
①主表的关联列和从表的关联列的类型必须一致，意思一样，名称无要求
②主表的关联列要求必须是主键
语法：
CREATE TABLE [IF NOT EXISTS] 表名(
字段名 字段类型 【字段约束】,
字段名 字段类型 【字段约束】,
字段名 字段类型 【字段约束】,
字段名 字段类型 【字段约束】,
字段名 字段类型 【字段约束】);
#案例1:没有添加约束
CREATE TABLE IF NOT EXISTS stuinfo(
stuid INT ,
stuname VARCHAR(20),
stugender CHAR(1),
email VARCHAR(20),
borndate DATETIME
);
#案例2:添加约束
DROP TABLE IF EXISTS stuinfo;
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),#添加了检查约束，mysql不支持
majorid INT,
CONSTRAINT fk_stuinfo_major FOREIGN KEY (majorid) REFERENCES major(id)#添加了外键约束);
#二、修改表[了解]
语法：ALTER TABLE 表名 ADD|MODIFY|CHANGE|DROP COLUMN 字段名 字段类型 【字段约束】;
#案例1.修改表名
ALTER TABLE stuinfo RENAME TO students;
#案例2.添加字段
ALTER TABLE students ADD COLUMN borndate TIMESTAMP NOT NULL;
#案例3.修改字段名
ALTER TABLE students CHANGE COLUMN borndate birthday DATETIME NULL;
#案例4.修改字段类型
ALTER TABLE students MODIFY COLUMN birthday TIMESTAMP ;
#案例5.删除字段
ALTER TABLE students DROP COLUMN birthday;
#三、删除表
DROP TABLE IF EXISTS students;
#四、复制表
#仅仅复制表的结构
CREATE TABLE newTable2 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;
DML
一、数据 的插入
/*
语法：
插入单行：
insert into 表名(字段名1,字段名2 ,...) values (值1，值2,...);
插入多行：
insert into 表名(字段名1,字段名2 ,...) values
(值1，值2,...),(值1，值2,...),(值1，值2,...);
特点：
①字段和值列表一一对应
包含类型、约束等必须匹配
②数值型的值，不用单引号
非数值型的值，必须使用单引号(比如:日期，字符串)
③字段顺序无要求
自增长:
语法:
列名 数据类型 约束 identity(m,n)
m表示的是初始值，n表示的是每次自动增加的值
MYSQL中则是
列名 数据类型 约束 AUTO_INCREMENT
*/
二、数据 的修改
/*
语法：
update 表名 set 字段名 = 新值,字段名=新值,...
where 筛选条件;
*/
三、数据 的删除
/*
方式1：delete语句
语法：delete from 表名 where 筛选条件;
方式2：truncate语句(删除表中所有数据)
语法：truncate table 表名;
*/
#【面试题】delete和truncate的区别
1.delete可以添加WHERE条件
TRUNCATE不能添加WHERE条件，一次性清除所有数据
2.truncate的效率较高
3.如果删除带自增长列的表，
使用DELETE删除后，重新插入数据，记录从断点处开始
使用TRUNCATE删除后，重新插入数据，记录从1开始
4.delete 删除数据，会返回受影响的行数
TRUNCATE删除数据，不返回受影响的行数
5.delete删除数据，可以支持事务回滚
TRUNCATE删除数据，不支持事务回滚
事务
一、什么是事务？为什么要用事务？
一个事务是由一条或者多条sql语句构成，这一条或者多条sql语句要么全部执行成功，要么全部执行失败！
二、事务的四大特性（ACID）
原子性（Atomicity）：事务中所有操作是不可再分割的原子单位。事务中所有操作要么全部执行成功，要么全部执行失败。
一致性（Consistency）：事务执行后，数据库状态与其它业务规则保持一致。如转账业务，无论事务执行成功与否，参与转账的两个账号余额之和应该是不变的。
隔离性（Isolation）：隔离性是指在并发操作中，不同事务之间应该隔离开来，使每个并发中的事务不会相互干扰。
持久性（Durability）：一旦事务提交成功，事务中所有的数据操作都必须被持久化到数据库中，即使提交事务后，数据库马上崩溃，在数据库重启时，也必须能保证通过某种机制恢复数据。
三、分类
隐式事务：没有明显的开启和结束标记
比如dml语句的insert、update、delete语句本身就是一条事务
insert into stuinfo values(1,'john','男','ert@dd.com',12);
显式事务：具有明显的开启和结束标记
一般由多条sql语句组成，必须具有明显的开启和结束标记
四、步骤：
1、取消隐式事务自动开启的功能
2、开启事务
3、编写事务需要的sql语句（1条或多条）
insert into stuinfo values(1,'john','男','ert@dd.com',12);
insert into stuinfo values(1,'john','男','ert@dd.com',12);
4、结束事务
#演示事务的使用步骤
#1、取消事务自动开启
SET autocommit = 0;
#2、开启事务
START TRANSACTION;
#3、编写事务的sql语句
#将张三丰的钱-5000
UPDATE stuinfo SET balance=balance-5000 WHERE stuid = 1;
#将灭绝的钱+5000
UPDATE stuinfo SET balance=balance+5000 WHERE stuid = 2;
此时存放在缓存中，尚未保存
#4、结束事务
#提交
#commit;
#回滚
ROLLBACK;
#5、查看结果
SELECT * FROM stuinfo;
845
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
