Mysql安装教程以及基本用法

MYSQL

安装mysql

1. 解压mysql-8.0.30-winx64.zip到 d:/kaifamiao/envirments文件夹下

2. 输入cmd使用管理员方式打开命令提示窗口

3. 使用命令将目录调整到D:\kaifamiao\enviroments\mysql-8.0.30-winx64目录下

4. 依次执行脚本

   1. 1initialization.bat 初始化。完成后会有一个data目录产生

   2. 2install.bat 安装 mysql 服务

      看到Service successfully installed.表示服务安装成功

   3. 3startup.bat 启动mysql 服务

      1. MySQL 服务正在启动 .
         MySQL 服务已经启动成功。

      4.4reboot.bat为重启服务命令

      5.5stop.bat为停止服务命令

      6.6uninstall.bat 为卸载服务命令

验证mysql

启动mysql服务后, 进入到 {mysql根目录}/bin

1. cmd窗口中输入以下命令：

   mysql -u root -p
   

2. 能够进入到mysql即为成功安装。

3. 修改环境变量path的值，添加D:\kaifamiao\enviroments\mysql-8.0.30-winx64\bin

将mysql添加到环境变量

将mysql的bin目录地址添加到系统环境变量–>PATH中

将mysql添加到服务

以管理员的方式启动cmd(命令提示窗口)，使用命令进入到[mysql]\bin，执行如下命令。

mysqld --install(服务名)

如:

mysqld --install Mysql 5.7

删除服务命令是:

mysqld --remove服务名

mysql端口被占用解决

在cmd窗口下执行如下命令：

netstat -ano|findstr 3306

查找正在执行的3306端口程序

如果出现如图所示列表表示以上程序使用了3306端口，找到程序的PID(最后一列)

去任务管理栏找到对应程序结束任务就行了。

忘记mysql密码（忘记密码后使用）

以下操作均以管理员方式进行。

1. 打开 cmd 关闭 mysql 服务, net stop mysql

2. 以管理员身份在命令提示窗口( cmd )中进入到mysql根目录->bin文件夹，输入：

跳过权限认证

3. 重新打开一个 cmd 窗口，输入 mysql

mysql -uroot -p

不输入密码即可进去 mysql

4. 连接权限数据库：

use mysql

5. 修改数据库连接密码,置为空：

update user set authentication_string = '' where user = "root";

mysqld --console --skip-grant-tables --shared-memory

16. 退出

exit

quit

7. 关闭第一个窗口

重启 mysql 服务即可。

再重新进入mysql ,修改密码

连接MySQL

mysql -u 用户名 -p 

• mysql --help查询所有参数

  SQL 分类

  • DDL: 数据定义语句。 如： CREATE / ALTER / DROP
  • DML: 数据操纵语句。如：INSERT / UPDATE / DELETE
  • DQL: 数据查询语句。如：SELECT

  所有的SQL都应该以英文状态下的分号结束;

建库

• 创建数据库语法结构：

  • CREATE DATABASE [ IF NOT EXISTS ] db_name

  • CREATE DATABASE 表示创建数据库

  • db_name是要创建的数据库名称

    CREATE DATABASE company_info;
    

• 使用数据库：

  • USE db_name

    USE company_info;
    

• 删除数据库语法结构

  • drop database db_name

    DROP DATABASE company_info;
    

• 显示所有的数据库

  SHOW DATABASES;
  

  建表

  • 创建数据表的语法结构

    CREATE TABLE tab_name(
    col_name datatype default null/number comment '注释',
    col_name datatype
    ) [CHARACTER set 编码格式];
    /*
    * 使用CREATE TABLE table关键词创建数据表
    * tab_name是数据表的名称
    * col_name是列名称
    * datatype是列的数据类型
    * DEFAULT 是默认值
    * COMMENT 是注释
    */
    CREATE TABLE dept(
         deptno INT DEFAULT 1 COMMENT '部门编号',
         deptname VARCHAR(20) DEFAULT NULL COMMENT '部门名称'
    )CHARACTER SET utf8;
    

    – 显示所有表

      SHOW tables;
    
    

创建用户

​ CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

例如：

‐‐ user1用户只能在localhost这个IP登录mysql服务器
CREATE USER 'user1'@'localhost' IDENTIFIED BY '1234';
‐‐ user2用户可以在任何电脑上登录mysql服务器
CREATE USER 'user2'@'%' IDENTIFIED BY '1234';

授权

用户创建之后，没什么权限,需要给用户授权

GRANT 权限1, 权限2… ON 数据库名.表名 TO ‘用户名’@‘主机名’;

• GRANT 授权关键字
• 授予用户的权限，如SELECT，INSERT，UPDATE等。如果要授予所有的权限则使用ALL
• 数据库名.表名：该用户可以操作哪个数据库的哪些表。如果要授予该用户对所有数据库和表的相应操作权限则可用*表示，如*.*
• '用户名'@'主机名': 给哪个用户授权

例子：

1. 给user1用户分配对test这个数据库操作的权限

GRANT CREATE,ALTER,DROP,INSERT,UPDATE,DELETE,SELECT ON test.* TO 'user1'@'localhost';

2. 给user2用户分配对所有数据库操作的权限

GRANT ALL ON *.* TO 'user2'@'%';

撤销授权

REVOKE 权限1, 权限2… ON 数据库.表名 FROM ‘用户名’@‘主机名’;

例子：

撤销user1用户对test操作的权限

REVOKE ALL ON test.* FROM 'user1'@'localhost';

查看权限

SHOW GRANTS FOR ‘用户名’@‘主机名’;

例子：

查看user1用户的权限

SHOW GRANTS FOR 'user1'@'localhost';

删除用户

DROP USER ‘用户名’@‘主机名’;

例子：

DROP USER 'user2'@'%';

修改密码

修改管理员密码

mysqladmin -uroot -p password 新密码

– 新密码不需要加上引号

– 注意：需要在未登陆MySQL的情况下操作。

例子：

mysqladmin ‐uroot ‐p password 123456
输入老密码

修改普通用户密码

set password for ‘用户名’@‘主机名’ = ‘新密码’;

– 注意：需要在登陆MySQL的情况下操作。

set password for 'user1'@'localhost' = '666666';

常见的数据类型

int、double、date、datetime、varchar、time、timestamp

查看表结构

-- 查看表结构
DESCRIBE dept;
DESC dept;

练习：

创建一张user表, 可以存储以下内容：姓名，性别，年龄，生日，电话，家庭住址，邮箱

create table `user`(
		`name` VARCHAR(20) COMMENT '姓名',
		`gender` CHAR(1) COMMENT '性别',
		`age` INT COMMENT '年龄',
		`birthday` DATE COMMENT '生日',
		`phone` VARCHAR(20) COMMENT '电话',
		`address` VARCHAR(50) COMMENT '住址',
		`email` VARCHAR(20) COMMENT '邮箱'
);

创建和某表结构一样的表

-- 创建和dept结构一样的表
CREATE TABLE d LIKE dept;

-- 创建表
CREATE TABLE t AS select * from dept;

删除表

DROP TABLE table_name

添加列

ALTER TABLE d ADD id INT;

修改列属性

ALTER TABLE d MODIFY id VARCHAR(20);

修改列名

ALTER TABLE d CHANGE id ss VARCHAR(20);	

删除列

ALTER TABLE d DROP ss;

重命名表

RENAME TABLE d TO dd;

CRUD操作

• 对数据表中的数据操作通常有添加(Create)、查询(Retrieve)、修改(Update)、删除(Delete)、简称为CRUD。

添加数据

INSERT INTO table_name VALUES(值列表)

INSERT INTO table_name (列列表) VALUES(值列表)

-- 不推荐使用
INSERT INTO dept VALUE(1,'研发部');
-- 2
INSERT INTO dept VALUES(2,'销售部');
INSERT INTO dept VALUES(3,'行政部'),(4,'技术部');
-- 3
INSERT INTO dept(deptno,deptname)VALUES(5,'安保部');

区别：

• value和values的区别，values可以同时插入多条数据用逗号隔开
• dept和dept(列名，列名。。。)区别，如果不写列表必须按照列表创建时的顺序每一列都要添加
• 有列名的按照列名排列顺序添加

查询数据

-- 查询所有数据
SELECT *FROM dept;
-- 查询某列的数据
SELECT deptname FROM dept;
-- 根据条件查询*
SELECT deptno FROM dept WHERE deptname='销售部';

修改数据

--全部修改为6
UPDATE dept SET deptno=6;
--根据条件修改
UPDATE dept SET deptno=1 WHERE deptname='研发部';
UPDATE dept SET deptno=2 WHERE deptname='销售部';
UPDATE dept SET deptno=3 WHERE deptname='行政部';
UPDATE dept SET deptno=4 WHERE deptname='技术部';
UPDATE dept SET deptno=5 WHERE deptname='安保部';

删除数据

-- 删除数据 ,一定要加 where 条件
DELETE FROM dept WHERE deptno=5;
-- 全部删除
DELETE FROM dept;
-- 清空/截断 所有数据(慎用)
TRUNCATE TABLE dept;

区别:

• delete from dd;
• truncate table dd;
• delete 是清空表中的数据,DML
• truncate 是清空表数据（删除表后重新创建一个一样表），DDL

where条件连接

当sql语句中的条件有多条时，可以将多个条件连接起来。他们之间的关系有一下几种：

and

a and b: 表示 需要同时满足 a 条件 和 b 条件

or

​ a or b: 表示 满足 a 条件 或 b 条件都可以

in

​ in(a, ... ,b)： 表示在 a 及 b 这些值中都可以

like

​ 模糊查询, % 表示任意个字符 _ 表示一个字符

数据备份和还原

命令行备份

备份结构

1.备份表结构

mysqldump -u root -p -d dbname table1 table2 ... > a.sql

2.备份数据库的所有表结构

mysqldump -u root -p -d dbname > b.sql

3.备份多个数据库的所有表结构

mysqldump -u root -p -d --databases db1 db2... > c.sql

4.备份所有数据库的表结构

mysqldump -u root -p -d --all-databases > d.sql

备份数据和结构

(相当于在备份结构的语法上去掉-d选项)

1.备份表结构和数据

mysqldump -u root -p dbname table1 table2 ... > a.sql

2.备份数据库的所有表结构和数据

mysqldump -u root -p dbname  > b.sql

3.备份多个数据库的表结构和数据

mysqldump -u root -p --databases db1 db2  > c.sql

4.备份所有数据库的表结构和数据

mysqldump -u root -p --all-databases > d.sql

• mysqldump -h 127.0.0.1 -u root -p root db_name>path;
  • 使用mysqldump 命令备份数据库
  • -h指定数据库所在的服务器的ip地址
  • -u指定登录数据库的密码
  • db_name是要备份的数据库的名称
  • 使用输出目标操作符>,指定输出的文件具体路径c:/back.sql

备份表数据

mysql -u root -p -e "selec 语句" dbname > 目标文件名

select * from xxx into outfile ‘/tmp/stud.txt' ; 

命令行还原

还原表结构和数据

mysql -u root -p [dbname] < 目标文件
mysql -h127.0.0.1 -uroot -proot db_name<back.sql

load data infile '/tmp/stud.txt' into table students;
source /backup/all_db_2013-09-08.sql

高级查询

distinct

在select语句中，可以使用distinct关键字对查询的结果集进行去重。

select distinct 列1, ... , 列n  from table_name [其他子句];

去重必须结果集中每个列的值都相同。

order by

order by用于对结果进行排序显示，可以使用ASC / DESC两种方式进行排序，可以有多个排序条件

• ASC：表示升序排序，如果不写即为此排序方式
• DESC：表示降序排序

select [distinct] 列1, ... , 列n from table_name [其他子句] order by 排序列1 [DESC], 排序列2 [DESC];

分页查询limit子句

select * from emp limit 0,2;

• 第一个参数0是表示从第几条开始查询 (这里的 0 是可以省略不写的)；
• 第二个参数 表示查询出几条数据
• 后面不够的，有多少写多少；

select * from emp order by empNo limit 5;
select * from emp limit 5,5;

select * from table_name  limit (页码 - 1) * 每页数量, 每页数量;

聚合函数

Mysql中内置了 5 种聚合函数，分别是：SUM 、max、min、avg、count。

• sum： 求和

  select sum(列) from table_name [其他子句];
  

• max: 求最大值

  select max(列) from table_name [其他子句];
  

• min: 求最小值

  select min(列) from table_name [其他子句];
  

• avg： 求平均值

  select avg(列) from table_name [其他子句];
  

• count: 求数量

  select count(列) from table_name [其他子句];
  

group by

group by 是对数据进行分组，分组时，表中有相同值的分为一组。分组后可以进行聚合查询。

group by分组后的查询中，select的列不能出现除了group by 分组条件以及聚合函数外的其他列。

select 列1, 列2, (聚合函数) from table_name group by 列1, 列2;

having

having是对group by分组后的结果集进行筛选。

select 列1, 列2, (聚合函数) from table_name group by 列1, 列2 having 分组后条件;

综合查询

SELECT DISTINCT emp.deptno FROM emp JOIN dept ON emp.deptno = dept.deptno WHERE bridate >= '2000-01-01' GROUP BY emp.deptno HAVING count(*) >= 2 ORDER BY count(*) DESC  LIMIT 0, 5;

书写顺序是以上。

SQL语句的执行顺序

from --> on --> join --> where --> group by --> having --> select --> distinct-- > order by–> limit

问题：

1. 在使用了GROUP BY的SQL中， ORDER BY 可以使用 聚合函数 吗? 可以使用未在 GROUP BY 中出现的列吗？

   SELECT deptno, COUNT(*) FROM emp GROUP BY deptno ORDER BY count(*) DESC, empno
   ASC; -- 可以执行吗？
   

MODIFY 和 CHANGE 关键字用于修改表的列名、数据类型以及列的约束。区别在于：

MODIFY 不会修改列名， CHANGE 关键字允许同时修改列名。使用 CHANGE 时，必须指定原始列名、新列名以及新的数据类型和列约束(如NOT NULL、DEFAULT等)ALTER TABLE tb name CHANGE old column name new column name new data type new constraints;

约束

基本概念

约束是作用于表中字段上的规则，用于限制存储在表中的数据。

目的：保证数据库中数据的正确、有效性和完整性。约束是为了保证进入数据库的数据都是有效的、可靠的，会对列的值进行一些约束，确保存进去的数据都是有效的。

 查看约束 SHOW CREATE TABLE table_name;

分类：

 约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束。

主键约束（PK）

主键约束最显著的特征是主键列中的值是不允许重复的，通过主键约束可强制表的实体完整性。当创建或更改表时可通过定义 PRIMARY KEY 约束来创建主键。一个表只能有一个PRIMARY KEY约束，且 PRIMARY KEY 约束中的列不能接受NULL值。

 ALTER TABLE tab_name ADD CONSTRAINT pk_name PRIMARY KEY(字段名);
# 设置该字段为主键，主键约束名称为pk_name

设置主键约束的几种方式：

-- 1.创建表的时候指定主键约束
CREATE TABLE `table_name` (
`id` int PRIMARY KEY,-- 设置主键
`name` varchar(20) ,
);
CREATE TABLE `table_name` (
`id` int,
`name` varchar(20) ,
PRIMARY KEY (`id`) -- 设置主键
);
CREATE TABLE `table_name` (
`id` int,
`name` varchar(20) ,
constraint pk primary key(id) -- 设置主键
);

-- 2.修改某一列为主键
ALTER TABLE 表名称 ADD [CONSTRAINT] PRIMARY KEY(id);
ALTER TABLE 表名称 MODIFY [COLUMN] 字段名 属性 PRIMARY KEY; -- 修改列类型
ALTER TABLE 表名称 CHANGE [COLUMN] 字段名 字段名 属性 PRIMARY KEY; --修改列名称和类型

删除主键约束

ALTER TABLE 表名称 DROP PRIMARY KEY; [mysql 8.0.22 之后的版本]
ALTER TABLE 表名称 DROP INDEX PRIMARY KEY; [mysql 8.0.22 之前的版本]

自增长列（标识列）

并不是所有表在设计完成后都能找到适合作为主键的列，为此数据库提供了自增长列，自增长列是int类型的，其值是由数据库自动维护的，是永远都不会重复的，因此自增长是最适合作为主键列的。在创建表时，通过 AUTO_INCREMENT 关键字来标识自增长列，在MySQL数据库中自增长列必须是主键列。

特点：

• 标识列必须和一个Key搭配（Key指主键、唯一、外键…）

• 一个表最多有一个标识列

• 标识列的类型只能是数值型

• 标识列可以通过SET auto_increment_increment = 3;设置步长（全局，退出数据库重新进入会恢复默认值），可以通过插入行时手动插入标识列值设置起始值。

CREATE TABLE goods(
no INT PRIMARY KEY AUTO_INCREMENT, -- 直接设置自增长
name VARCHAR(10)
);
ALTER TABLE 表名 MODIFY [COLUMN] 列名 列类型 AUTO_INCREMENT; -- 修改
为自增长列
ALTER TABLE 表名 CHANGE 列名 列名 列类型 AUTO_INCREMENT;
-- 删除自增长列
ALTER TABLE 表名 MODIFY [COLUMN] id int;

联合主键

联合主键（Composite Primary Key）是指在数据库表中，由多个列共同组成的主键，用来唯一标识表中的每一行数据。它的作用类似于单一列的主键，但不是由单个列组成，而是由多个列组合而成。联合主键可以确保表中的每一行都具有唯一性，并且每个列组合的值都不会重复。

在创建表时，可以在列定义中指定多个列作为联合主键。

CREATE TABLE 表名 (
列名1 数据类型,
列名2 数据类型,
列名3 数据类型,
PRIMARY KEY (列名1, 列名2, 列名3)
);
-- 修改列的时候创建
ALTER TABLE 表名 ADD [CONSTRAINT] PRIMARY KEY (列名1, 列名2, 列名3);
-- 删除
ALTER TABLE 表名称 DROP PRIMARY KEY;

联合主键在以下情况非常有用：

1.当单个列无法唯一标识表中的每一行，但多个列组合在一起可以唯一标识每一行数据时。

2.提高查询性能：联合主键可以更有效地支持涉及多个列的查询，避免创建额外的索引。

3.在具有多个外键的关联表中，可以使用联合主键来确保外键引用的准确性。

 联合主键要求每个列组合的值都是唯一的

唯一约束

对于非主键列中的值也要求唯一性时，就需要唯一约束。

-- 创建表时
CREATE TABLE `table_name` (
`id` int,
`name` varchar(20) UNIQUE # 唯一约束
);
CREATE TABLE `table_name` (
`id` int,
`name` varchar(20),
CONSTRAINT uq UNIQUE(name) #唯一约束
);
-- 修改表
ALTER TABLE 表名 ADD UNIQUE(列名称);
ALTER TABLE 表名 ADD CONSTRAINT [constraint_name] UNIQUE(列名称);
ALTER TABLE 表名 CHANGE [COLUMN] 列名 列名 类型 UNIQUE;
ALTER TABLE 表名 MODIFY [COLUMN] 列名 列类型 UNIQUE;
-- 删除唯一约束
ALTER TABLE 表名称 DROP INDEX 设置唯一时的名称;

# 如果没有设置约束名称，名称默认是字段名

唯一约束允许有多个 NULL 值

默认约束

为列中的值设置默认值， DEFAULT value

-- 创建表时
CREATE TABLE `table_name` (
`id` int DEFAULT value,
`name` varchar(20) unique,
);
-- 修改表
ALTER TABLE 表名 MODIFY [COLUMN] 列名 列类型 DEFAULT 默认值;
ALTER TABLE 表名 CHANGE 列名 列名 列类型 DEFAULT 默认值;

-- 删除
ALTER TABLE 表名 MODIFY [COLUMN] 列名 列类型;
ALTER TABLE 表名 ALTER COLUMN 列名 DROP DEFAULT; 【8.0.23以上的版本】
ALTER TABLE 表名 ALTER COLUMN 列名 SET DEFAULT NULL;【8.0.23以前的版本】

如果已经设置了值，默认值就无效了

非空约束

NOT NULL ：非空，用于保证该字段的值不能为空。

-- 创建表时
CREATE TABLE `table_name` (
`id` int NOT NULL, # 非空约束
`name` varchar(20),
);
ALTER TABLE 表名 MODIFY [COLUMN] 列名 列类型 NOT NULL;
ALTER TABLE 表名 CHANGE 列名 列名 列类型 NOT NULL;
-- 删除
ALTER TABLE 表名 MODIFY [COLUMN] 列名 列类型 [null];

修改列的约束确保现有数据满足非空约束条件，否则可能导致操作失败。

检查约束

从MySQL 8.0.19版本开始，MySQL支持了检查约束。检查约束允许你在表定义中声明条件，并确保符合该条件的数据才能插入或更新到相应的列中。

CREATE TABLE employees (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50),
age INT,
email VARCHAR(100),
CHECK (age >= 18) -- 添加检查约束：年龄必须大于等于18
);
-- 修改表的时候
ALTER TABLE 表名 CHANGE 列名 列名 列类型 CHECK(condition);
ALTER TABLE 表名 MODIFY 列名 列类型 CHECK(condition);
ALTER TABLE 表名 ADD CONSTRAINT 列名 CHECK(condition);
-- 删除检查约束
ALTER TABLE 表名 DROP CONSTRAINT 约束名;

外键约束

用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。表中列的值来自于另外一张表的主键或唯一键的列称为外键 FK ，将被引用值的表称为主表或父表，将引用值的表称为从表或子表。

-- 创建表时
CREATE TABLE `table_name` (
`id` int NOT NULL,
`name` varchar(20),
`rid` int,
CONSTRAINT fk_a_b FOREIGN KEY(rid) REFERENCES b(id)
);

ALTER TABLE 从表表名 ADD [CONSTRAINT] 约束名称 FOREIGN KEY (从表字段)REFERENCES 主表表名(主表字段);

ALTER TABLE goods ADD [CONSTRAINT] fk_category_no FOREIGN KEY (category_no) REFERENCES category(no);

-- dept是主表，userinfo 是从表
--在 userinfo 表中添加或修改时， dept_no列的值必须是 dept 表中 deptno 字段中的存在值
alter table userinfo add constraint foreign key fk_dept_no (dept_no) REFERENCES dept(deptno);


-- 删除
ALTER TABLE 表名称 DROP FOREIGN KEY 索引名 ;# 设置外键时的名称

• 外键列类型需要与引用列类型一致
• 外键列的值必须是主表中引用列的值或者 NULL
• 一个表可以有多个外键列
• 从表列可以随便删除
• 删除主表数据时，会先检查从表中有没有对此数据的关联，如果有就不能直接删除

在 设 置 外 键 的 时 候 后 面 添 加 on delete cascade / on update cascade 在删除/更新主表时，级联删除/更新外键列的数据

在设置外键的时候后面添加 on delete set null / on update set null 在删除/更新主表时，外键列的值会变成null

多表查询

笛卡尔乘积现象

表查询中的笛卡尔乘积现象：多行表在查询时，如果定义了无效连接或者漏写了连接条件，就会产生笛卡尔乘积现象，所谓的笛卡尔乘积即是每个表的每一行都和其他表的每一行组合。

笛卡尔乘积现象

SELECT * FROM emp,dept;

等值连接查询

通常是在存在主键外键关联关系的表之间的连接进行，使用"="连接相关的表

n个表进行等值连接查询，最少需要n-1个等值条件来约束

自连接查询

表表查询不仅可以在多个表之间进行查询，也可以在一个表之中进行多表查询

--查询当前公司员工和所属上级员工的信息
select e1.empno as 员工编号,e1.ename as 员工姓名,e2.empno as 领导编号,e2.ename as 领导姓
名 from emp as e1,emp as e2 where e1.mgr = e2.empNo;

内连接查询

内连接查询使用 inner join 关键字实现，inner可以省略。内连接查询时，条件用 on连接，多个条件使用 () 将其括起来.

--查询每个部门的所有员工
select dept.name,emp.name from emp inner join dept on emp.deptno = dept.deptno;

和等值查询差不多

外连接

外连接分为左外连接（ left outer join ） 和右外连接（ right outer join ）其值 outer 可以省略。外连接查询时，条件用on连接，多个条件使用 () 将其括起来.左外连接表示以左表为主表，右外连接表示以右表为主表。查询时将主表信息在从表中进行

--查询每个部门的所有员工
select dept.name,emp.name from emp right join dept on emp.deptno = dept.deptno;
select dept.name,emp.name from emp left join dept on emp.deptno = dept.deptno;

子查询

存在于另外一个SQL语句中、被小括号包起来的查询语句就是子查询。相对于子查询来说，在外部直接执行的查询语句被称作主查询

子查询分为：

• 单列子查询: 返回单行单列数据的子查询
• 单行子查询: 返回单行多列数据的子查询
• 多行子查询: 返回数据是多行单列的数据
• 关联子查询: 子查询中如果使用了外部主SQL中的表或列，就说这个子查询跟外部SQL是相关的

单行子查询

单行单列

--查询软件部门下的所有员工
select * from emp e where e.deptno = (select d.deptno from dept d where d.dname = '软件部' );

多行子查询

如果子查询返回了多行记录，则称这样的嵌套查询为多行子查询，多行子查询就需要用到

多行记录的操作符

如： in , all , any

in 子查询中所有的记录

--统计所有的员工分布在哪些部门的信息
select * from dept d where d.deptno in (select e.deptno from emp e);
--查询公司中比任意一个员工的工资高的所有员工
select * from emp e1 where e1.sal > any (select e1.sal from emp e2);
--查询公司中比所有的助理工资高但不是助理的员工
select * from emp e1 where e1.sal > all(select e2.sal from e2.emp where w2.joblike '%助理');

any 表示大于子查询中的任意一个值，即大于最小值

all 表示大于子查询中的所有值，即大于最大的值

多列子查询

单行多列

--查询公司中和员工***相同薪水和奖金的员工
select * from emp e1 where (e1.sal,e1.comm) = (select e2.sal,e2.comm from emp e2
where e2.ename = '张青');

了解 EXISTS / NOT EXISTS 用法

问题：

1. 在 DELETE / UPDATE 中可以使用子查询吗？有限制吗？限制是什么？

DELETE FROM emp WHERE deptno = (SELECT deptno FROM emp WHERE ename = 'SCOTT'); --是否可以

子查询**[应用]**

存在于另外一个SQL语句中、被小括号包起来的查询语句就是子查询。相对于子查询来说，在外部直接执行的查询语句被称作主查询

子查询分为：

• 单列子查询: 返回单行单列数据的子查询
• 单行子查询: 返回单行多列数据的子查询
• 多行子查询: 返回数据是多行单列的数据
• 关联子查询: 子查询中如果使用了外部主SQL中的表或列，就说这个子查询跟外部SQL是相关的

单列子查询

导入 scott用户表sql.sql

查看 emp表中与SMITH岗位相同的员工信息

1. 先尝试查看 SMITH 所从事的岗位

2. 查询从事 clerk 工作的员工

--统计所有的员工分布在哪些部门的信息
select * from dept d where d.deptno in (select e.deptno from emp e);

--查询公司中比任意一个员工的工资高的所有员工
select * from emp e1 where e1.sal > any (select e1.sal from emp e2);

--查询公司中比所有的助理工资高但不是助理的员工
select * from emp e1 where e1.sal > all(select e2.sal from e2.emp where w2.joblike '%助理');

--查询公司中和员工***相同薪水和奖金的员工
select * from emp e1 where (e1.sal,e1.comm) = (select e2.sal,e2.comm from emp e2 where e2.ename = '张青');

单行子查询

查询 与 SMITH 在同一个部门且岗位相同的员工的信息

1. 查询 SMITH 所在的部门和从事的岗位

2. 查询在 20 部门从事 clerk 岗位的员工信息

3. 可以成对比较，也可以把子查询当成一张虚拟表使用

多行子查询

查询emp表中与20部门员工岗位相同的员工信息

1. 查询20部门的所有岗位

2. 剔除重复行

3. 根据20部门的岗位来查询emp表中的员工

关联子查询

查询哪些员工与SMITH不在同一个部门

SELECT * FROM emp e
WHERE EXISTS ( SELECT * FROM emp p WHERE p.ename = 'SMITH' AND p.deptno != e.deptno);

单行函数

MySQL 函数 | 菜鸟教程 (runoob.com)

单列子查询

导入 scott用户表sql.sql

查看 emp表中与SMITH岗位相同的员工信息

1. 先尝试查看 SMITH 所从事的岗位

2. 查询从事 clerk 工作的员工

--统计所有的员工分布在哪些部门的信息
select * from dept d where d.deptno in (select e.deptno from emp e);

--查询公司中比任意一个员工的工资高的所有员工
select * from emp e1 where e1.sal > any (select e1.sal from emp e2);

--查询公司中比所有的助理工资高但不是助理的员工
select * from emp e1 where e1.sal > all(select e2.sal from e2.emp where w2.joblike '%助理');

--查询公司中和员工***相同薪水和奖金的员工
select * from emp e1 where (e1.sal,e1.comm) = (select e2.sal,e2.comm from emp e2 where e2.ename = '张青');

单行子查询

查询 与 SMITH 在同一个部门且岗位相同的员工的信息

1. 查询 SMITH 所在的部门和从事的岗位

2. 查询在 20 部门从事 clerk 岗位的员工信息

3. 可以成对比较，也可以把子查询当成一张虚拟表使用

多行子查询

查询emp表中与20部门员工岗位相同的员工信息

1. 查询20部门的所有岗位

2. 剔除重复行

3. 根据20部门的岗位来查询emp表中的员工

关联子查询

查询哪些员工与SMITH不在同一个部门

SELECT * FROM emp e
WHERE EXISTS ( SELECT * FROM emp p WHERE p.ename = 'SMITH' AND p.deptno != e.deptno);

单行函数

MySQL 函数 | 菜鸟教程 (runoob.com)