数据库介绍
DB:数据(Database) 即存储数据的“仓库”。它保存了一系列有组织的数据
DBMS:数据库管理系统(Database Management System)是一种操纵和管理数据库的大型软件,例如建立、使用和维护数据库。
DBS:(数据库系统)包括DB(数据库)和DBMS(数据库管理系统)
MySQL的数据类型
MySQL 是⼀种关系型数据库,在Java企业级开发中⾮常常⽤,因为 MySQL 是开源免费的,并且⽅便扩展。阿⾥巴巴数据库系统也⼤量⽤到了 MySQL,因此它的稳定性是有保障的。MySQL是开放源代码的,因此任何⼈都可以在 GPL(General Public License) 的许可下下载并根据个性化的需要对其进⾏修改。MySQL的默认端⼝号是3306。
常用的数据类型有:
-
整型(xxxint)
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-KKmYy4L5-1631171287351)(E:\3 编程学习资料\日记\mysql\image-20210904200928839.png)]
-
位类型(bit)
-
浮点型(float和double、real):对于浮点列类型,在MySQL中单精度值使用4个字节,双精度值使用8个字节
-
定点数(decimal,numeric)
-
日期时间类型(date,time,datetime,year)
-
字符串(char,varchar,xxxtext)
-
二进制数据(xxxBlob、xxbinary)
-
枚举(enum)
-
集合(set)
MySQL的逻辑架构与存储引擎
- MyISAM不支持事务、也不支持外键,其优势是访问的速度快,对事务完整性没有要求或者以SELECT、INSERT
为主的应用。每个MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始,扩展名指出文件类
型。.frm文件存储表定义。数据文件的扩展名为.MYD (MYData)。索引文件的扩展名是.MYI (MYIndex)。 - InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比MyISAM的存储引擎,InnoDB写
的处理效率差一些,并且会占用更多的磁盘空间以保存数据和索引。InnoDB:所有的表都保存在同一个数据文
件中,InnoDB表的大小只受限于操作系统文件的大小限制。MyISAM只缓存索引,不缓存真实数据;InnoDB不
仅缓存索引还要缓存真实数据,对内存要求较高,而且内存大小对性能有决定性的影响。
SQL操作
SQL的语言规范
- MySQL对于SQL语句不区分大小写,SQL语句关键字尽量大写
- SQL 可以写在一行或者多行。为了提高可读性,各子句分行写,必要时使用缩进
- 关键字不能被缩写也不能分行
- 值,除了数值型,字符串型和日期时间类型使用单引号(’ ')
- 别名,尽量使用双引号(" "),而且不建议省略as
- 所有标点符号使用英文状态下的半角输入方式
- 必须保证所有(),单引号,双引号是成对结束的
- 可以使用(1)#单行注释 (2)–空格单行注释 (3)/* 多行注释 */
命名规则
- 数据库、表名不得超过30个字符,变量名限制为29个
- 必须只能包含 A–Z, a–z, 0–9, _共63个字符
- 不能在对象名的字符间留空格
- 必须不能和用户定义的其他对象重名
- 必须保证你的字段没有和保留字、数据库系统或常用方法冲突
- 保持字段名和类型的一致性,在命名字段并为其指定数据类型的时候一定要保证一致性。假如数据类型在一个表
里是整数,那在另一个表里可就别变成字符型了
SQL分类
- DDL(Data Definition Languages):数据定义语言,这些语句定义了不同的数据段、数据库、表、列、索引
等数据库对象。
主要的语句关键字包括 CREATE 、 DROP 、 ALTER 等。 - DML(Data Manipulation Language):数据操作语言,用于添加、删除、更新和查询数据库记录,并检查
数据完整性。
主要的语句关键字包括 INSERT 、 DELETE 、 UPDATE 、 SELECT 等。
SELECT是SQL语言的基础,最为重要。 - DCL(Data Control Language):数据控制语言,用于控制不同数据段直接的许可和访问级别的语句。这些
语句定义了数据库、表、字段、用户的访问权限和安全级别。
主要的语句关键字包括 GRANT 、 REVOKE 、 COMMIT 、 ROLLBACK 、 SAVEPOINT 等。
下面是所用表格介绍
DML
SELECT
基本语句使用
#02-基本的SELECT语句
USE temp; #使用指定的数据库
#1. 基本使用
SELECT employee_id,last_name,EMAIL
FROM employees;
SELECT department_id,manager_id,location_id
FROM departments;
SELECT * # * : 代表所有的字段
FROM employees;
SELECT *
FROM countries;
#2. 列的别名
#as : alias
#可以使用一对"",给列起别名
SELECT employee_id emp_id,last_name AS lname,salary "monthly salary"
FROM employees;
#3. 去除重复行
SELECT DISTINCT department_id
FROM employees;
#如下操作是错误的
SELECT employee_id, DISTINCT department_id
FROM employees;
#4. 空值问题
#空值,表示没有赋值,理解为null。
#空值参与运算的问题:结果也为空。
#空值,不等同于0,'','null'
SELECT employee_id,commission_pct,salary,salary * (1 + commission_pct),
salary * (1 + IFNULL(commission_pct,0)) "real_salary"
FROM employees;
#5. 显示表结构
DESC employees;
DESCRIBE employees;
过滤数据
#03-过滤数据
#查询90号部门员工的信息
SELECT employee_id,last_name,department_id,salary
FROM employees
# 使用where实现数据的过滤
# where 必须紧跟在from子句的后面
WHERE department_id = 90;
SELECT employee_id,last_name,department_id,salary
FROM employees
#where salary > 5000;
WHERE department_id <> 90;
# 2. between 下边界 and 上边界 (包含边界)
#查询工资大于等于6000 且小于等于 8000之间的员工信息
SELECT employee_id,salary
FROM employees
WHERE salary >= 6000 AND salary <= 8000;
#where salary between 6000 and 8000;
#WHERE salary BETWEEN 8000 AND 6000;
#3. in(set):
SELECT employee_id,department_id,salary
FROM employees
#where department_id = 30 or department_id = 40 or department_id = 50;
#where department_id in (30,40,50);
WHERE salary IN (6000,7000,8000);
#4. like : 模糊查询
#精确查询
SELECT employee_id,LAST_NAME
FROM employees
WHERE last_name = 'King'; #字符串、日期需要使用一对''表示。注意,不要使用""
#where hire_date = '1993-01-13';
#查询姓名中包含字符'a'的员工
# % : 表示0个,1个或多个字符
SELECT employee_id,LAST_NAME
FROM employees
WHERE last_name LIKE '%a%';
# 查询姓名中包含字符'a'且包含字符'e'的员工
SELECT employee_id,LAST_NAME
FROM employees
#WHERE last_name LIKE '%a%e%' or last_name LIKE '%e%a%';
WHERE last_name LIKE '%a%' AND last_name LIKE '%e%';
# 查询姓名中第3个字符是a的员工
# _ : 表示一个不确定的字符
SELECT employee_id,LAST_NAME
FROM employees
WHERE last_name LIKE '__a%';
# 查询姓名中第2个字符是_且第3个字符是a的员工
SELECT employee_id,LAST_NAME
FROM employees
#WHERE last_name LIKE '_\_a%'; # sout("林辉很\"帅\"");
WHERE last_name LIKE '_#_a%' ESCAPE '#';
# 5. is null:空值
SELECT last_name,commission_pct
FROM employees
WHERE commission_pct IS NULL;
SELECT last_name,commission_pct
FROM employees
#WHERE !(commission_pct IS NULL);
WHERE commission_pct IS NOT NULL;
# 6. 算术运算符
SELECT employee_id,salary,department_id
FROM employees
WHERE department_id MOD 20 = 0;
#选择工资不在5000到12000的员工的姓名和工资
SELECT last_name,salary
FROM employees
#where salary < 5000 or salary > 12000;
WHERE salary NOT BETWEEN 5000 AND 12000;
#where !(salary >= 5000 and salary <= 12000);
#选择公司中没有管理者的员工姓名及job_id
SELECT last_name,job_id
FROM employees
WHERE manager_id IS NULL;
排序和分页
#04-排序和分页
#1. 排序
# 1.1
#按照员工的工资从高到低排序
# desc: descend,降序
# asc : ascend,升序
SELECT employee_id,salary
FROM employees
#order by salary desc; #降序
#order by salary asc; #升序
ORDER BY salary ;#如果没有显式指名asc或desc,则默认升序排列
# 1.2 使用列的别名进行排序
#别名可以在order by中使用
SELECT employee_id,last_name,salary sal
FROM employees
ORDER BY sal;
#不能在过滤条件中使用列的别名。
#如下操作报错:
SELECT employee_id,last_name,salary sal
FROM employees
WHERE sal >= 6000;
#order by要声明在where的后面
SELECT employee_id,last_name,salary sal
FROM employees
WHERE salary > 5000
ORDER BY last_name ASC;
#1.3 二级排序
SELECT employee_id,last_name,department_id,salary
FROM employees
ORDER BY department_id,salary DESC;
#2. 分页
#每页显示20条记录,显示第1页数据:
SELECT employee_id,last_name,salary
FROM employees
LIMIT 0,20;
#每页显示20条记录,显示第2页数据:
SELECT employee_id,last_name,salary
FROM employees
LIMIT 20,20;
#每页显示20条记录,显示第3页数据:
SELECT employee_id,last_name,salary
FROM employees
LIMIT 40,20;
#每页显示pageSize条记录,显示第pageNo页数据:
#limit (pageNo - 1) * pageSize,pageSize
#查询工资最高的20个员工信息:top-N
SELECT employee_id,last_name,salary
FROM employees
ORDER BY salary DESC
LIMIT 0,20; #limit 要声明在order by的后面
多表查询
#05-多表查询
#出现了笛卡尔积的错误
SELECT employee_id,last_name,department_name
FROM employees,departments; #查询出2889行数据
SELECT 2889/107
FROM DUAL;
SELECT *
FROM departments;
#正确的写法:多表的查询,一定要有连接条件
SELECT employee_id,last_name,department_name
FROM employees,departments
#多表的连接条件
WHERE employees.`department_id` = departments.`department_id`
#进一步:如果查询的字段在多个表中都出现,则一定需要指明来自于哪个表。比如:department_id
SELECT employee_id,last_name,department_name,departments.department_id
FROM employees,departments
WHERE employees.`department_id` = departments.`department_id`
#建议开发中多表查询中,查询的字段都指明来自于哪个表。--->sql优化
SELECT employees.employee_id,employees.last_name,departments.department_name,departments.department_id
FROM employees,departments
WHERE employees.`department_id` = departments.`department_id`
#表的别名的使用。一旦给表起了别名,就可以在select中或where中使用。
SELECT e.employee_id,e.last_name,d.department_name,d.department_id
FROM employees e,departments d
WHERE e.`department_id` = d.`department_id`
#需求:
SELECT e.employee_id,e.last_name,d.department_name,l.city
FROM employees e,departments d,locations l
#连接条件
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id;
#结论:如果实现n个表的多表查询,则至少需要n - 1个连接条件。
##############################################
/*
多表查询的分类:
1. 等值连接 vs 不等值连接
2. 自连接 vs 非自连接
3. 内连接 vs 外连接
*/
#不等值连接
SELECT employee_id,salary,grade_level
FROM employees e,job_grades j
#where e.`salary` >= j.`lowest_sal` and e.`salary` <= j.`highest_sal`;
WHERE e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`;
#自连接
#查询员工的employee_id,last_name及其管理者的employee_id,last_name
SELECT emp.employee_id,emp.last_name,mgr.employee_id,mgr.last_name
FROM employees emp,employees mgr
WHERE emp.`manager_id` = mgr.`employee_id`;
########################################################
/*
内连接:合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行
外连接:分为左外连接 和 右外连接
左外连接:两个表在连接过程中除了返回满足连接条件的行以外,还返回左表中不满足条件的行
这种连接称为左外连接。
右外连接:两个表在连接过程中除了返回满足连接条件的行以外,还返回右表中不满足条件的行
这种连接称为右外连接。
*/
#内连接的例子:
SELECT employee_id,last_name,department_name
FROM employees e,departments d
WHERE e.`department_id` = d.`department_id`
# 要想实现外连接,需要使用sql-99语法中的相关结构。
####################################################
#sql-99语法实现内连接:举例1
SELECT employee_id,last_name,department_name
FROM employees e INNER JOIN departments d
ON e.`department_id` = d.`department_id`;
#举例2:
SELECT employee_id,last_name,department_name,city
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
JOIN locations l
ON d.`location_id` = l.`location_id`;
#左外连接:
#需求:查询所有员工的employee_id,last_name,department_name
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`;
#右外连接:
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;
#与上一个select是相同的需求。
SELECT employee_id,last_name,department_name
FROM departments d LEFT JOIN employees e
ON e.`department_id` = d.`department_id`;
# 满外连接:两个表在连接过程中除了返回满足连接条件的行以外,还返回左表和右表中不满足条件的行
#这种连接称为满外连接。
# mysql 不支持full join
SELECT employee_id,last_name,department_name
FROM employees e FULL JOIN departments d
ON e.`department_id` = d.`department_id`;
7中JOIN
#06-7种JOIN
#中图:内连接 106条
SELECT employee_id,last_name,department_name
FROM employees e INNER JOIN departments d
ON e.`department_id` = d.`department_id`;
#左上图:左外连接 107条
SELECT employee_id,last_name,department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`;
#右上图:右外连接 122条
SELECT employee_id,last_name,department_name
FROM employees e RIGHT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`;
#左中图: 1条
SELECT employee_id,last_name,department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL;
#右中图:
SELECT employee_id,last_name,department_name
FROM employees e RIGHT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;
#左下图:满外连接 123条
#方式一:左中图 + 右上图
SELECT employee_id,last_name,department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL
SELECT employee_id,last_name,department_name
FROM employees e RIGHT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`
#方式二:左上图 + 右中图 :略
#右下图:左中图 + 右中图: 17条记录
SELECT employee_id,last_name,department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL
SELECT employee_id,last_name,department_name
FROM employees e RIGHT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;
#结论:能使用union all的场景,就不推荐使用union。因为避免去重,效率低。
单行函数
#07-单行函数
# 1. 字符串类型
SELECT CONCAT('hello','world','hello','beijing') "details"
FROM DUAL;
#需求: xxx worked for yyy
SELECT CONCAT(emp.last_name,' worked for ', mgr.last_name) "details"
FROM employees emp JOIN employees mgr
ON emp.`manager_id` = mgr.`employee_id`;
SELECT CONCAT_WS('-','hello','world','beijing')
FROM DUAL;
SELECT CHAR_LENGTH('hello'),LENGTH('hello'),CHAR_LENGTH('中国'),LENGTH('中国')
FROM DUAL;
#sql中索引从1开始!
SELECT INSERT('helloworld',2,3,'aaaaa')
FROM DUAL;
#下面的查询中,salary会自动的转换为字符串类型,此自动转换的行为称为:隐式转换
SELECT employee_id,LPAD(salary,10,' ') "details", RPAD(last_name,10,' ')
FROM employees;
DESC employees;
# 针对于数值类型、字符串类型、日期类型存在隐式转换。
SELECT 1 + '1'
FROM DUAL;
SELECT TRIM('aa' FROM 'aaaahelaaloa')
FROM DUAL;
SELECT REPEAT('hello',5)
FROM DUAL;
SELECT STRCMP('abc','abe')
FROM DUAL;
#索引从1 开始
SELECT SUBSTRING('hello',2,2)
FROM DUAL;
# 2. 数值类型
SELECT CEIL(123.342),FLOOR(23.999),
MOD(12,5),MOD(12,-5),MOD(-12,5),MOD(-12,-5),RAND() * 100
FROM DUAL;
SELECT ROUND(123.567),ROUND(123.567,0),ROUND(123.567,1),ROUND(123.567,-2)
FROM DUAL;
SELECT TRUNCATE(123.967,0),TRUNCATE(123.567,1),TRUNCATE(123.567,-1)
FROM DUAL;
# 3. 日期类型
SELECT employee_id,hire_date
FROM employees
WHERE hire_date = '1993-01-13';
#如何获取当前的年月日、时分秒、年月日时分秒
SELECT CURDATE(),CURRENT_DATE(),CURTIME(),CURRENT_TIME(),NOW(),SYSDATE()
FROM DUAL;
/*
insert into employees(employee_id,last_name,hire_date)
values(304,'Tom',curdate())
*/
SELECT YEAR(CURDATE()),MONTH(CURDATE()),DAY(CURDATE()),
HOUR(CURTIME()),MINUTE(CURTIME()),SECOND(CURTIME())
FROM DUAL;
SELECT DAYOFWEEK(CURDATE()),WEEKDAY(NOW()),DAYNAME(CURDATE())
FROM DUAL;
SELECT DATE_ADD(NOW(), INTERVAL 1 YEAR)
FROM DUAL;
SELECT DATE_ADD(NOW(), INTERVAL -1 YEAR); #可以是负数
SELECT DATE_ADD(NOW(), INTERVAL '1_1' YEAR_MONTH); #需要单引号
#显式操作
#格式化:日期 ---> 字符串
#DATE_FORMAT(datetime ,fmt)
SELECT DATE_FORMAT(NOW(),'%Y/%M/%d %h:%i:%s')
FROM DUAL;
#解析: 字符串 ---> 日期
#STR_TO_DATE(str, fmt)
SELECT STR_TO_DATE('2021/May/26 09:54:17','%Y/%M/%d %h:%i:%s')
FROM DUAL;
#隐式操作:日期类型、字符串类型、数值类型之间存在隐式的转换。
# 4. 流程控制
#结构1 : if
SELECT employee_id,last_name,salary,IF(salary > 10000,'高工资','低工资') "details",
IF(commission_pct IS NOT NULL,commission_pct,0) "details1"
FROM employees;
#结构2: ifnull
SELECT employee_id,last_name,salary,
IF(commission_pct IS NOT NULL,commission_pct,0) "details",
IFNULL(commission_pct,0) "details1",
salary * (1 + commission_pct),salary * (1 + IFNULL(commission_pct,0))
FROM employees;
#结构3:case when ... then ... when ... then ... else ... end 类似于if-else if - .. -else 的多选一
SELECT employee_id,last_name,salary,CASE WHEN salary > 15000 THEN '高富帅'
WHEN salary > 10000 THEN '潜力股'
WHEN salary > 5000 THEN '打工人'
ELSE '小屌丝' END "details"
FROM employees;
SELECT employee_id,last_name,department_id,CASE WHEN department_id = 10 THEN '10号部门'
WHEN department_id = 20 THEN '20号部门'
WHEN department_id = 30 THEN '30号部门'
END "details"
FROM employees;
#结构4:case ... when ... then ... when ... then ... else ... end 类似于switch-case
SELECT employee_id,last_name,department_id,CASE department_id WHEN 10 THEN '10号部门'
WHEN 20 THEN '20号部门'
WHEN 30 THEN '30号部门'
ELSE '其他部门' END "details"
FROM employees;
/*
**练习:查询部门号为 10,20, 30 的员工信息,
若部门号为 10, 则打印其工资的 1.1 倍,
20 号部门, 则打印其工资的 1.2 倍,
30 号部门打印其工资的 1.3 倍数。
*/
SELECT employee_id,last_name,salary,department_id,CASE department_id WHEN 10 THEN salary * 1.1
WHEN 20 THEN salary * 1.2
WHEN 30 THEN salary * 1.3
END "details"
FROM employees
WHERE department_id IN (10,20,30);
# 5. 其它函数
SELECT DATABASE(),VERSION(),USER()
FROM DUAL;
SELECT PASSWORD('abcd'),MD5(MD5('abcd'))
FROM DUAL;
分组函数
#09-子查询
#谁的工资比Abel的高?
#方式1:
SELECT salary
FROM employees
WHERE last_name = 'Abel';
SELECT last_name,salary
FROM employees
WHERE salary > 11000;
#方式2:自连接
SELECT e1.last_name,e1.salary
FROM employees e1 JOIN employees e2
ON e1.`salary` > e2.`salary`
WHERE e2.`last_name` = 'Abel';
#方式3:子查询
SELECT last_name,salary
FROM employees
WHERE salary > (
SELECT salary
FROM employees
WHERE last_name = 'Abel'
)
# 子查询的概念: 外层:外查询、主查询 ; 内层:内查询、子查询
# 子查询的分类: 单行子查询、 多行子查询
#1. 单行子查询
# 可以使用的比较运算符有: = > >= < <= <> !=
#题目:返回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,job_id和salary
SELECT last_name,job_id,salary
FROM employees
WHERE salary = (
SELECT MIN(salary)
FROM employees
);
#技巧:书写子查询的方式:①从外往里写 ②从里往外写
#题目:查询最低工资大于60号部门最低工资的部门id和其最低工资
SELECT department_id,MIN(salary)
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING MIN(salary ) > (
SELECT MIN(salary)
FROM employees
WHERE department_id = 60
)
#空值的情况:
SELECT last_name, job_id
FROM employees
WHERE job_id =
(SELECT job_id
FROM employees
WHERE last_name = 'Haas');
#非法使用子查询
SELECT employee_id, last_name
FROM employees
WHERE salary =
(SELECT MIN(salary)
FROM employees
GROUP BY department_id);
#2. 多行子查询
#多行子查询可以使用的比较操作符有:in all any
#in:
SELECT employee_id, last_name
FROM employees
WHERE salary IN
(SELECT MIN(salary)
FROM employees
GROUP BY department_id);
#题目:返回其它job_id中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id 以及salary
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE job_id <> 'IT_PROG'
AND salary < ANY(
SELECT salary
FROM employees
WHERE job_id = 'IT_PROG'
)
# 题目:返回其它job_id中比job_id为‘IT_PROG’部门所有工资都低的员工的员工号、姓名、job_id以及salary
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE job_id <> 'IT_PROG'
AND salary < ALL(
SELECT salary
FROM employees
WHERE job_id = 'IT_PROG'
)
###################################################
#题目:查询员工中工资大于公司平均工资的员工的last_name,salary和其department_id
SELECT last_name,salary,department_id
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
);
#题目:查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id
#方式一:相关子查询
SELECT last_name,salary,department_id
FROM employees e1
WHERE salary > (
#本部门的平均工资
SELECT AVG(salary)
FROM employees e2
WHERE e2.department_id = e1.`department_id`
);
#方式二:除了在group by 和limit 之外的位置都可以编写子查询
SELECT e.last_name,e.salary,e.department_id
FROM employees e,(
SELECT department_id,AVG(salary) avg_sal
FROM employees
GROUP BY department_id) dept_avg_sal
WHERE e.department_id = dept_avg_sal.department_id
AND e.`salary` > dept_avg_sal.avg_sal;
#另例:查询员工的employee_id,last_name,要求按照department_name从小到大排序
SELECT employee_id,last_name
FROM employees e
ORDER BY (
SELECT department_name
FROM departments d
WHERE e.`department_id` = d.`department_id`
) DESC;
#EXISTS的使用
#题目:查询公司管理者的employee_id,last_name,job_id,department_id信息
#方式一:
SELECT employee_id,last_name,job_id,department_id
FROM employees
WHERE employee_id IN (
SELECT DISTINCT manager_id
FROM employees
);
#方式二:
SELECT employee_id,last_name,job_id,department_id
FROM employees e1
WHERE EXISTS (
SELECT 'x'
FROM employees e2
WHERE e1.`employee_id` = e2.manager_id
);
增、删、改
#12-数据处理之增、删、改
# 1. 增/添加:INSERT
#方式1:一条一条的添加
DESC emp1;
SELECT *
FROM emp1;
INSERT INTO emp1
VALUES(1,'Tom','tom@126.com',3000,CURDATE());
INSERT INTO emp1
VALUES(2,'Tom1','tom@126.com',NULL,CURDATE());
#进化一步:
INSERT INTO emp1(id,last_name,salary,hire_date,email)
VALUES(3,'Tom2',5000,'2000-10-12','Tom2@126.com');
#没有声明的字段,在添加操作执行完后,值为null
INSERT INTO emp1(id,last_name,email)
VALUES(4,'Tom3','Tom3@126.com');
#方式2:基于现有的表
INSERT INTO emp1(id,last_name)
SELECT employee_id,last_name
FROM employees
WHERE department_id IN (10,20,30)
SELECT *
FROM emp1;
DESC emp1;
DESC employees;
#2. 删除数据:delete from .... where ...
DELETE FROM emp1
WHERE id = 1;
#3. 修改数据: update ... set ..,.. where ...
UPDATE emp1
SET salary = 7000
WHERE id = 4;
UPDATE emp1
SET salary = 8000,hire_date = CURDATE()
WHERE id = 4;
DDL
创建和管理表
#11-创建和管理表 --DDL
#数据库对象:表、视图、存储过程、函数、触发器、同义词、索引等
# 1.创建库
CREATE DATABASE database0419;
SHOW DATABASES; #查看所有的数据库
# 2. 使用指定的数据库
USE database0419;
SHOW TABLES; #查看指定的库下包含哪些表
USE temp;
SHOW TABLES;
SELECT *
FROM employees;
# 3. 删除库
DROP DATABASE database0419;
# 4. 创建表
# 方式一:"白手起家"的方式
CREATE TABLE emp1(
id INT,
last_name VARCHAR(15),
email VARCHAR(25),
salary DOUBLE(10,2),
hire_date DATE
);
DESC emp1;
SELECT *
FROM emp1;
#方式二:基于现有的表,创建新的表
CREATE TABLE emp2
AS
SELECT employee_id,last_name,salary
FROM employees ;
DESC emp2;
DESC employees;
#说明:使用此种方式创建表时,还可以将原有表中的数据复制到新表中。
SELECT *
FROM emp2;
#如下结构中select中列的别名,就作为新创建的表的字段名
CREATE TABLE emp3
AS
SELECT employee_id emp_id,last_name,salary AS sal
FROM employees ;
#查询失败
SELECT employee_id
FROM emp3;
#练习1:复制employees表,包含所有数据
CREATE TABLE employees_copy
AS
SELECT *
FROM employees;
SELECT *
FROM employees_copy;
SELECT *
FROM employees;
#练习2: 复制employees表,不包含任何数据
CREATE TABLE employees_copy_blank
AS
SELECT *
FROM employees
WHERE 1 = 2;
SELECT *
FROM employees_copy_blank;
# 5. 修改表
DESC emp3;
# 5.1 增加一个列
ALTER TABLE emp3
ADD email VARCHAR(25);
SELECT *
FROM emp3;
# 5.2 删除一个列
ALTER TABLE emp3
DROP email;
ALTER TABLE emp3
DROP COLUMN sal;
# 5.3 修改字段(类型、储值范围)
DESC emp3;
ALTER TABLE emp3
MODIFY last_name VARCHAR(30);
#通常不会修改字段的类型!!
#报错
ALTER TABLE emp3
MODIFY last_name INT;
# 5.4 重命名字段
DESC emp3;
ALTER TABLE emp3
CHANGE last_name lname VARCHAR(21);
# 6. 重命名表
RENAME TABLE emp3 TO myemp3;
SELECT *
FROM myemp3;
# 7. 删除表
DROP TABLE employees_copy;
# 8. 清空表: 清空表中的数据,但是表结构保留
SELECT *
FROM myemp3;
TRUNCATE TABLE myemp3;
/*
对比TRUNCATE TABLE 和 DELETE FROM ...
结论:TRUNCATE TABLE 一旦操作,就不可以回滚数据
DETELE FROM 支持删除表中的所有数据,可以回滚数据
测试:COMMIT 与 ROLLBACK的使用
1. COMMIT:表示提交数据。数据一旦被提交,就不可回滚。
ROLLBACK:表示回滚数据。 回滚操作,也只能回滚到最近的一次COMMIT之后。
2. 默认情况下,对数据表的操作(DDL、DML),都是在执行之后,默认提交数据的。
要想测试TRUNCATE TABLE 和 DETELE FROM的区别,需要关闭默认提交的行为:
SET autocommit = FALSE;
*/
CREATE TABLE myemp
AS
SELECT *
FROM employees;
SELECT *
FROM myemp;
COMMIT;
#首先测试DELETE FROM
SET autocommit = FALSE;
DELETE FROM myemp; #删除数据
SELECT *
FROM myemp;
ROLLBACK; #回滚数据
#接着测试TRUNCATE TABLE
COMMIT;
SET autocommit = FALSE;
TRUNCATE TABLE myemp; #清空表
SELECT *
FROM myemp;
#并没有将数据回滚成功
ROLLBACK; #回滚数据
#结论:以TRUNCATE TABLE为代表的DDL操作,都会在执行完以后,自动的COMMIT提交数据。
# 而且此提交行为不受SET autocommit = FALSE;的影响。所以,ROLLBACK行为对DDL操作都失效。
# 关于COMMIT、ROLLBACK涉及到数据库事务的操作
约束
#13-约束(constraint)
DESC employees;
/*
1. 约束,针对表中的数据,在添加、删除、修改的过程中,进行的限制。
2. 约束的分类:
角度一(从声明的位置上):列级约束 vs 表级约束
角度二(从作用的列的数量上):单列约束 vs 多列约束
角度三(从功能上区分):
not null :非空约束
unique: 唯一性约束
primary key:主键约束
foreign key:外键约束
check : 检查约束
default : 默认值约束
3. 设置约束的时机:情况1:在CREATE TABLE 的同时,给表的字段添加上约束。
情况2:通过 ALTER TABLE 的方式添加、删除约束
*/
# 1. not null :非空约束
# 创建表的同时,添加约束
CREATE TABLE emp3(
id INT NOT NULL,
last_name VARCHAR(15) NOT NULL,
email VARCHAR(25),
hire_date DATE
);
DESC emp3;
#添加成功
INSERT INTO emp3(id,last_name,email,hire_date)
VALUES(1,'Tom','tom@126.com',CURDATE());
SELECT * FROM emp3;
#添加失败
INSERT INTO emp3(last_name,email,hire_date)
VALUES('Tom','tom@126.com',CURDATE());
#添加失败
INSERT INTO emp3(id,last_name,email,hire_date)
VALUES(2,NULL,'tom@126.com',CURDATE());
#在ALTER TABLE时,删除非空约束
ALTER TABLE emp3
MODIFY last_name VARCHAR(15) NULL;
DESC emp3;
#添加成功
INSERT INTO emp3(id,last_name,email,hire_date)
VALUES(2,NULL,'tom@126.com',CURDATE());
SELECT *
FROM emp3;
#在ALTER TABLE时,添加非空约束
ALTER TABLE emp3
MODIFY hire_date DATE NOT NULL;
#2. unique: 唯一性约束
# 创建表的同时,添加约束
CREATE TABLE emp4(
id INT UNIQUE, #列级约束
last_name VARCHAR(15),
email VARCHAR(25),
hire_date DATE,
#表级约束
CONSTRAINT emp4_email_uk UNIQUE(email)
);
DESC emp4;
#添加成功
INSERT INTO emp4(id,last_name,email,hire_date)
VALUES(1,'Tom','tom@126.com',CURDATE());
SELECT * FROM emp4;
#添加失败
INSERT INTO emp4(id,last_name,email,hire_date)
VALUES(1,'Tom','tom@126.com',CURDATE());
#添加失败
INSERT INTO emp4(id,last_name,email,hire_date)
VALUES(2,'Tom','tom@126.com',CURDATE());
#添加成功
INSERT INTO emp4(id,last_name,email,hire_date)
VALUES(2,'Tom1',NULL,CURDATE());
#添加成功
INSERT INTO emp4(id,last_name,email,hire_date)
VALUES(3,'Tom1',NULL,CURDATE());
#结论:声明为unique约束的字段,在添加或修改数据时,允许多次设置为null.
/*
在修改表的时候,如何删除约束?
1. 在创建唯一约束的时候,如果不给唯一约束名称,就默认和列名相同
2. MySQL会给唯一约束的列上默认创建一个唯一索引
3. 删除唯一约束只能通过删除唯一索引的方式删除
4. 删除时需要指定唯一索引名,唯一索引名就和唯一约束名一样。
5. 如果创建唯一约束时未指定名称,如果是单列,就默认和列名相同,如果是组合列,那么默认和()中排在第一个的列名相同。也可以自定义唯一性约束名。
*/
DESC emp4;
#删除索引,进而删除了唯一性约束
ALTER TABLE emp4
DROP INDEX emp4_email_uk;
ALTER TABLE emp4
DROP INDEX id;
#添加唯一性约束
ALTER TABLE emp4
ADD CONSTRAINT emp4_id_uk UNIQUE(id);
#3. primary key:主键约束
# 一个表中只能声明一个主键约束
# 主键约束,既满足唯一性,也满足非空性。
# 通过声明有主键约束的字段,可以确定表中的唯一的一条记录。
# 通常,在创建表的同时,都需要指名一个主键约束。
CREATE TABLE emp5(
id INT PRIMARY KEY, #列级约束
last_name VARCHAR(15),
email VARCHAR(25),
hire_date DATE,
salary DOUBLE(10,2)
);
DESC emp5;
#添加成功
INSERT INTO emp5(id,last_name,email,salary)
VALUES(1,'Tom','tom@126.com',2000);
SELECT *
FROM emp5;
#添加失败
INSERT INTO emp5(id,last_name,email,salary)
VALUES(1,'Tom','tom@126.com',2000);
#添加失败
INSERT INTO emp5(id,last_name,email,salary)
VALUES(NULL,'Tom','tom@126.com',2000);
#开发中常见的声明方式:
CREATE TABLE emp6(
id INT AUTO_INCREMENT,
last_name VARCHAR(15),
email VARCHAR(25),
hire_date DATE,
salary DOUBLE(10,2),
#表级约束
CONSTRAINT emp6_id_pk PRIMARY KEY(id)
);
INSERT INTO emp6(last_name,email,salary)
VALUES('Tom','tom@126.com',2000);
SELECT *
FROM emp6;
#如何删除主键
ALTER TABLE emp5
DROP PRIMARY KEY;
DESC emp5;
#添加主键约束
ALTER TABLE emp5
ADD CONSTRAINT emp5_id_pk PRIMARY KEY(id);
#4. foreign key:外键约束
#作用:在表A的字段a上声明有一个外键约束,与表B中的字段b相关联。则字段a在insert等操作时,
# 其赋的值一定是字段b中出现过的数据。
#要求:要想能关联成功,必须要求字段b声音有主键约束或唯一性约束
CREATE TABLE dept7(
dept_id INT,
dept_name VARCHAR(10)
);
#添加外键约束失败
CREATE TABLE emp7(
id INT,
last_name VARCHAR(15),
dept_id INT,
#声明外键:表级约束
CONSTRAINT emp7_dept_id_fk FOREIGN KEY(dept_id) REFERENCES dept7(dept_id)
);
#补救措施:
ALTER TABLE dept7
ADD CONSTRAINT dept7_dept_id_pk PRIMARY KEY(dept_id);
DESC dept7;
#添加外键约束成功
CREATE TABLE emp7(
id INT,
last_name VARCHAR(15),
dept_id INT,
#声明外键:表级约束
CONSTRAINT emp7_dept_id_fk FOREIGN KEY(dept_id) REFERENCES dept7(dept_id)
);
#添加失败
INSERT INTO emp7(id,last_name,dept_id)
VALUES(1,'Tom',10);
#
INSERT INTO dept7(dept_id,dept_name)
VALUES(10,'IT');
#添加成功
INSERT INTO emp7(id,last_name,dept_id)
VALUES(1,'Tom',10);
SELECT *
FROM emp7;
#结论:在实际开发中,不建议在创建表时使用外键约束。
#5. 检查约束(check)
# 对mysql失效
CREATE TABLE emp8(
id INT,
last_name VARCHAR(15),
salary DOUBLE(10,2) CHECK(salary > 3000)
);
DESC emp8;
INSERT INTO emp8
VALUES(1,'Tom',4000);
#添加成功
INSERT INTO emp8
VALUES(2,'Tom1',2000);
SELECT *
FROM emp8;
#6.默认值约束(default)
CREATE TABLE emp9(
id INT,
last_name VARCHAR(15),
salary DOUBLE(10,2) DEFAULT 2500
);
DESC emp9;
INSERT INTO emp9
VALUES(1,'Tom',5000);
INSERT INTO emp9(id,last_name)
VALUES(1,'Tom');
SELECT *
FROM emp9;
数据库事务
什么是事务
- 事务:一组逻辑操作单元,使数据从一种状态变换到另一种状态。
- 数据库事务由以下的部分组成:
一个或多个DML 语句
一个 DDL(Data Definition Language – 数据定义语言) 语句
一个 DCL(Data Control Language – 数据控制语言) 语句
如何处理事务
-
事务处理(事务操作):保证所有事务都作为一个工作单元来执行,即使出现了故障,都不能改变这种执行方
式。当在一个事务中执行多个操作时,要么所有的事务都被提交(commit),那么这些修改就永久地保存下来;
要么数据库管理系统将放弃所作的所有修改,整个事务回滚(rollback)到最初状态。 -
为确保数据库中数据的一致性,数据的操纵应当是离散的成组的逻辑单元:当它全部完成时,数据的一致性可以保
持,而当这个单元中的一部分操作失败,整个事务应全部视为错误,所有从起始点以后的操作应全部回退到开始状
态。 -
当一个连接对象被创建时,默认情况下是自动提交事务:每次执行一个SQL 语句时,如果执行成功,就会向数
据库自动提交,而不能回滚。 -
具体过程:
设置提交状态:SET AUTOCOMMIT = FALSE;
以第一个 DML 语句的执行作为开始
以下面的其中之一作为结束:
COMMIT或ROLLBACK语句
DDL 语句(自动提交)
用户会话正常结束系统异常终止
事务的ACID属性
-
原子性(Atomicity) 原子性是指事务是一个不可分割的工作单位,事务中的操作要么都发生,要么都不发
生。 -
一致性(Consistency) 事务必须使数据库从一个一致性状态变换到另外一个一致性状态。
-
隔离性(Isolation) 事务的隔离性是指一个事务的执行不能被其他事务干扰,即一个事务内部的操作及使用的
数据对并发的其他事务是隔离的,并发执行的各个事务之间不能互相干扰。 -
持久性(Durability) 持久性是指一个事务一旦被提交,它对数据库中数据的改变就是永久性的,接下来的其
他操作和数据库故障不应该对其有任何影响
数据库的隔离级别
- 对于同时运行的多个事务, 当这些事务访问数据库中相同的数据时, 如果没有采取必要的隔离机制, 就会导致各种
并发问题:
脏读: 对于两个事务 T1, T2, T1 读取了已经被 T2 更新但还没有被提交的字段。 之后, 若 T2 回滚, T1读取的
内容就是临时且无效的。
不可重复读: 对于两个事务T1, T2, T1 读取了一个字段, 然后 T2 更新了该字段。 之后, T1再次读取同一个字
段, 值就不同了。
幻读: 对于两个事务T1, T2, T1 从一个表中读取了一个字段, 然后 T2 在该表中插入了一些新的行。 之后, 如 果 T1 再次读取同一个表, 就会多出几行。 - 数据库事务的隔离性:数据库系统必须具有隔离并发运行各个事务的能力, 使它们不会相互影响, 避免各种并发
问题。 - 一个事务与其他事务隔离的程度称为隔离级别。数据库规定了多种事务隔离级别, 不同隔离级别对应不同的干扰
程度, 隔离级别越高, 数据一致性就越好, 但并发性越弱。 - 数据库提供的 4 种事务隔离级别
- Oracle 支持的 2 种事务隔离级别:READ COMMITED, SERIALIZABLE。 Oracle 默认的事务隔离级别为: READ
COMMITED 。 - Mysql 支持 4 种事务隔离级别。 Mysql 默认的事务隔离级别为: REPEATABLE READ