MySQL数据库学习笔记

最新推荐文章于 2023-08-15 21:00:22 发布

鲑鱼683

最新推荐文章于 2023-08-15 21:00:22 发布

阅读量377

点赞数

分类专栏： MySQL 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/weixin_42506939/article/details/120201837

版权

MySQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

数据库介绍

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）

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Du1Mvmxn-1631171287352)(E:\3 编程学习资料\日记\mysql\image-20210904201158681.png)]$

字符串（char,varchar,xxxtext）

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-jOxE4Mmq-1631171287353)(E:\3 编程学习资料\日记\mysql\image-20210904201340613.png)]$
二进制数据（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

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-hkG0KP1j-1631171287353)(E:\3 编程学习资料\日记\mysql\image-20210906153257507.png)]$

#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

鲑鱼683

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
MySQL数据库学习笔记

数据库介绍DB：数据（Database）即存储数据的“仓库”。它保存了一系列有组织的数据DBMS：数据库管理系统（Database Management System）是一种操纵和管理数据库的大型软件，例如建立、使用和维护数据库。DBS：(数据库系统)包括DB(数据库)和DBMS(数据库管理系统)MySQL的数据类型MySQL 是⼀种关系型数据库，在Java企业级开发中⾮常常⽤，因为 MySQL 是开源免费的，并且⽅便扩展。阿⾥巴巴数据库系统也⼤量⽤到了 MySQL，因此它的稳定性是有保障的。M
复制链接

扫一扫