软件设计之MySQL(4)
此篇应在JavaSE之后进行学习:
路线图推荐:
【Java学习路线-极速版】【Java架构师技术图谱】
Navicat可以在软件管家下载
``
使用navicat连接mysql数据库创建数据库、表、转储sql文件,导入sql数据
MySQL数据库入门到大牛,mysql安装到优化,百科全书级,全网天花板
资料可以去尚硅谷官网免费领取
学习内容:
- 子查询
- 数据库创建及管理
- 表的创建
- 表的其他操作
- COMMIT和ROLLBACK
- 阿里MySQL命名规范
- MySQL8新特性—DDL的原子化
1、子查询
子查询
指一个查询语句嵌套在另一个查询语句内部的查询
问题1:谁比Abel的工资高?[获取Abel工资并再次判断大小]
称谓的规范:外查询(主查询)、内查询(子查询)
注意事项:
1、子查询要包含在括号内
2、将子查询放在比较条件的右侧(salary>{}😉
3、单行操作符对应单行子查询
,多行操作符对应多行子查询
#方式1:自连接
SELECT e2.last_name,e2.salary
FROM employees e1,employees e2
WHERE e1.last_name='Abel' AND e2.salary>e1.salary
#方式2:子查询
SELECT last_name,salary
FROM employees
WHERE salary>(
SELECT salary
FROM employees
WHERE last_name = 'Abel'
);
子查询结构
除了GROUP BY 和LIMIT外,其他位置都可以申明子查询
子查询的分类
角度1:
单行子查询vs多行子查询
从内查询返回结果的条目数,数量为1则是单行子查询;
角度2:
相关子查询vs不相关子查询
内查询是否被执行多次
相关子查询举例:查询工资大于本部门平均工资的员工信息,由于员工部门不同则子查询返回的(返回平均部门工资)信息也不同,内与外查询
之间存在着相关性
单行子查询
单行操作符 = 、> 、>= 、< 、<= 、<>
子查询技巧:从里往外写
#返回job_id与141员工相同,salary比143号员工多的员工姓名,job_id和工资
SELECT last_name,salary,job_id
FROM employees
WHERE salary>(
SELECT salary
FROM employees
WHERE employee_id = '143'
)
AND job_id = (
SELECT job_id
FROM employees
WHERE employee_id = '141'
);
多行子查询
多行操作符IN、ANY、ALL、SOME
#IN
#根据部门返回最低工资(多个部门),查询哪些员工处于部门最低工资
SELECT last_name,employee_id
FROM employees
WHERE salary IN
(SELECT MIN(salary)
FROM employees
GROUP BY department_id);
#ANY
#返回其他job_id中比job_id为'IT_PROG'部门任意工资低的员工的信息
#假设IT_PROG部门工资为:4000、2000、8000,则筛选的是8000以下的
SELECT last_name,job_id,salary
FROM employees
WHERE job_id<>'IT_PROG'
AND salary< ANY
(SELECT salary
FROM employees
WHERE job_id = 'IT_PROG');
#ALL
#返回其他job_id中比job_id为'IT_PROG'部门所有工资低的员工的信息
#假设IT_PROG部门工资为:4000、2000、8000,则筛选的是2000以下的
SELECT last_name,job_id,salary
FROM employees
WHERE job_id<>'IT_PROG'
AND salary< ALL
(SELECT salary
FROM employees
WHERE job_id = 'IT_PROG');
MySQL中聚合函数无法嵌套使用
SELECT MIN(AVG(salary)) 是不行的
SELECT MIN(avg_sal)
FROM (
SELECT AVG(salary) avg_sal
FROM employees
GROUP BY department_id
) dept_avg_sal
相关子查询
如果子查询的执行依赖于外部查询,通常情况下都是因为子查询中的表用到了外部的表,并进行了条件关联,因此每执行一次外部查询,子查询都要重新计算一次,这样的子查询就称之为
关联子查询
#查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id
#方式1:相关子查询
SELECT last_name,salary,department_id
FROM employees e1
WHERE salary > (
SELECT AVG(salary)
FROM employees
WHERE department_id = e1.department_id
);
#方式2:在FROM中声明子查询
SELECT e.last_name,e.department_id,e.salary
FROM employees e, (
SELECT department_id ,AVG(salary) avg_sal
FROM employees
GROUP BY department_id
)t_dept_avg_sal
WHERE e.department_id = t_dept_avg_sal.department_id
AND e.salary > t_dept_avg_sal.avg_sal
EXISTS 与 NOT EXISTS关键字
用来检查在子查询中是否存在
满足条件
的行
如果在子查询中不存在满足条件的行:条件返回 FALSE并继续在子查询中查找
如果在子查询中存在满足条件的行:不在子查询中继续查找并条件返回 TRUE
#查询公司管理者的employee_id,last_name,job_id,department_id信息
SELECT employee_id,last_name,job_id,department_id
FROM employees e1
WHERE EXISTS(
SELECT *
FROM employees e2
WHERE e2.manager_id =
e1.employee_id);
2、数据库创建及管理
Navicat中的新建查询和命令行有什么区别?
1、Navicat 新建查询:
这是一个图形化界面,用户可以在一个文本编辑器中编写SQL语句,并通过点击运行按钮来执行这些语句。这个环境会帮助用户更容易地管理查询结果,查看表结构,进行数据筛选等操作。
2、命令行:
命令行是一个基于文本的界面,用户通过键入命令来执行SQL语句。结果通常以纯文本的形式返回,操作需要更熟练的命令行技巧。
数据库创建
数据库管理
修改数据库
删除数据库
3、表的创建
方式1
其中表名、列名(或字段名),数据类型,长度
必须指定
约束条件、默认值可选指定
如果创建表时没有指明使用的字符集,默认使用表所在的数据库的字符集
方式2
基于现有的表创建,同时导入数据
查询语句中字段的别名,可以作为新创建的表的字段的名称
当不想导入表中数据时,可以加一条语句WHERE 1 = 2
CREATE TABLE myemp1
AS
SELECT employee_id emp_id,last_name lname,salary
FROM employees
4、表的其他操作
修改表
1、向已有的表中添加列
2、修改现有表中的列
3、重命名现有表中的列
4、删除现有表中的列
注意:要想在Navicat中图形化设计表,右键表名称
,选择设计表
#追加一列(字段)
#ALTER TABLE 表名 ADD 【COLUMN】 字段名 字段类型 【FIRST|AFTER 字段名】;
#FIRST,AFTER可以指定追加到的位置
ALTER TABLE dept80
ADD job_id varchar(15);
#修改一列
#ALTER TABLE 表名 MODIFY 【COLUMN】 字段名1 字段类型 【DEFAULT 默认值】【FIRST|AFTER 字段名2】;
ALTER TABLE dept80
MODIFY salary double(9,2) default 1000;
#重命名一列
#ALTER TABLE 表名 CHANGE 【column】 列名 新列名 新数据类型;
ALTER TABLE dept80
CHANGE department_name dept_name varchar(15);
#删除一列
#ALTER TABLE 表名 DROP 【COLUMN】字段名
ALTER TABLE dept80
DROP COLUMN job_id;
1、重命名表
2、删除表
3、清空表
#重命名表方式1:
RENAME TABLE emp
TO myemp;
#重命名表方式2:
ALTER TABLE dept
RENAME TO detail_dept;
#删除表
#DROP TABLE [IF EXISTS] 数据表1 [, 数据表2, …, 数据表n];
DROP TABLE dept80;
#清空表,清空表数据,但表结构保留
#TRUNCATE语句不能回滚,而使用 DELETE 语句删除数据,可以回滚
#方式1:
TRUNCATE TABLE detail_dept;
#方式2:
DELETE FROM detail_dept;
5、COMMIT和ROLLBACK
COMMIT:
提交数据,一旦执行,则数据就被永久保存在数据库中,意味着数据不可以回滚
ROLLBACK:
回滚数据,一旦执行,则可以实现数据的回滚,回滚到最近的一次COMMIT之后
DDL和DML说明
1、DDL操作一旦执行,就不可回滚;
2、DML操作默认情况下,一旦执行,也是不可回滚的。但是,如果在执行DML前,执行了语句SET autocommit = FALSE
,则执行的DML操作可以实现回滚
6、阿里MySQL命名规范
7、 MySQL8新特性—DDL的原子化
DDL的原子化(Atomicity)指的是数据库中事务处理的一个基本特性。简单来说,就是
事务中的所有操作要么全部执行成功,要么全部不执行。
不会出现只执行了一部分的情况。
举个例子,如果你在银行账户中转账,数据库需要执行两个操作:从你的账户中扣钱,并把钱转到另一个账户。DDL的原子化确保这两个操作要么都成功,要么都失败。如果其中一个操作失败了,整个事务会回滚,所有已经执行的操作都会撤销,保持数据的一致性。这就像在现实生活中做一件事情,如果不能完全做成,那就干脆不做,避免产生问题。