MySQL笔记汇总(1)基础篇

通用语法及分类

  • DDL: 数据定义语言,用来定义数据库对象(数据库、表、字段)
  • DML: 数据操作语言,用来对数据库表中的数据进行增删改
  • DQL: 数据查询语言,用来查询数据库中表的记录
  • DCL: 数据控制语言,用来创建数据库用户、控制数据库的控制权限

DDL(数据定义语言)

数据库操作

查询所有数据库:
SHOW DATABASES;
查询当前数据库:
SELECT DATABASE();
创建数据库:
CREATE DATABASE [ IF NOT EXISTS ] 数据库名 [ DEFAULT CHARSET 字符集] [COLLATE 排序规则 ];
删除数据库:
DROP DATABASE [ IF EXISTS ] 数据库名;
使用数据库:
USE 数据库名;

注意事项
  • UTF8字符集长度为3字节,有些符号占4字节,所以推荐用 utf8mb4字符集

表操作

查询当前数据库所有表:
SHOW TABLES;

mysql> show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| Course         |
| Score          |
| Student        |
| Teacher        |
+----------------+

查询表结构:
DESC 表名;

mysql> desc Student;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| s_id    | varchar(20) | NO   | PRI | NULL    |       |
| s_name  | varchar(20) | NO   |     |         |       |
| s_birth | varchar(20) | NO   |     |         |       |
| s_sex   | varchar(10) | NO   |     |         |       |
+---------+-------------+------+-----+---------+-------+

查询指定表的建表语句:
SHOW CREATE TABLE 表名;

创建表:

CREATE TABLE 表名(
    字段1 字段1类型 [COMMENT 字段1注释],
    字段2 字段2类型 [COMMENT 字段2注释],
    字段3 字段3类型 [COMMENT 字段3注释],
    ...
    字段n 字段n类型 [COMMENT 字段n注释]
)[ COMMENT 表注释 ];

最后一个字段后面没有逗号

添加字段:
ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释] [约束];
例: ALTER TABLE emp ADD nickname varchar(20) COMMENT '昵称';

修改数据类型:
ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);
修改字段名和字段类型:
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT 注释] [约束];
例:将emp表的nickname字段修改为username,类型为varchar(30)
ALTER TABLE emp CHANGE nickname username varchar(30) COMMENT '昵称';

删除字段:
ALTER TABLE 表名 DROP 字段名;

修改表名:
ALTER TABLE 表名 RENAME TO 新表名

删除表:
DROP TABLE [IF EXISTS] 表名;
删除表,并重新创建该表:
TRUNCATE TABLE 表名;

DML(数据操作语言)

添加数据

指定字段:
INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...);
全部字段:
INSERT INTO 表名 VALUES (值1, 值2, ...);

批量添加数据:

INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...);

INSERT INTO 表名 VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...);
注意事项
  • 字符串和日期类型数据应该包含在引号中
  • 插入的数据大小应该在字段的规定范围内

更新和删除数据

修改数据:
UPDATE 表名 SET 字段名1 = 值1, 字段名2 = 值2, ... [ WHERE 条件 ];
例:
UPDATE emp SET name = 'Jack' WHERE id = 1;

删除数据:
DELETE FROM 表名 [ WHERE 条件 ];

DQL(数据查询语言)

语法:

SELECT
    字段列表
FROM
    表名字段
WHERE
    条件列表
GROUP BY
    分组字段列表
HAVING
    分组后的条件列表
ORDER BY
    排序字段列表
LIMIT
    分页参数

基础查询

查询多个字段:
SELECT 字段1, 字段2, 字段3, ... FROM 表名;
SELECT * FROM 表名;

设置别名:
SELECT 字段1 [ AS 别名1 ], 字段2 [ AS 别名2 ], 字段3 [ AS 别名3 ], ... FROM 表名;
SELECT 字段1 [ 别名1 ], 字段2 [ 别名2 ], 字段3 [ 别名3 ], ... FROM 表名;

去除重复记录:
SELECT DISTINCT 字段列表 FROM 表名;

例如:

mysql> SELECT * FROM Score;
+------+------+---------+
| s_id | c_id | s_score |
+------+------+---------+
| 01   | 01   |      80 |
| 01   | 02   |      90 |
| 01   | 03   |      99 |
| 02   | 01   |      70 |
| 02   | 02   |      60 |
| 02   | 03   |      80 |
| 03   | 01   |      80 |
| 03   | 02   |      80 |
| 03   | 03   |      80 |
| 04   | 01   |      50 |
| 04   | 02   |      30 |
| 04   | 03   |      20 |
| 05   | 01   |      76 |
| 05   | 02   |      87 |
| 06   | 01   |      31 |
| 06   | 03   |      34 |
| 07   | 02   |      89 |
| 07   | 03   |      98 |
| 07   | 04   |      87 |
+------+------+---------+
-- 去除重复学号
mysql> SELECT DISTINCT s_id FROM Score;
+------+
| s_id |
+------+
| 01   |
| 02   |
| 03   |
| 04   |
| 05   |
| 06   |
| 07   |
+------+

转义:
SELECT * FROM 表名 WHERE name LIKE '/_张三' ESCAPE '/'
/ 之后的_不作为通配符

条件查询

语法:
SELECT 字段列表 FROM 表名 WHERE 条件列表;

条件:

比较运算符 功能 > 大于 >= 大于等于 < 小于

例子:

-- 年龄等于30
select * from employee where age = 30;
-- 年龄小于30
select * from employee where age < 30;
-- 小于等于
select * from employee where age = 20 and age

聚合查询(聚合函数)

搭配分组查询使用。

将一列数据作为一个整体,进行纵向计算。

常见聚合函数:

函数 功能 count 统计数量 max 最大值 min 最小值 avg 平均值 sum 求和

语法:
SELECT &#x805A;&#x5408;&#x51FD;&#x6570;(&#x5B57;&#x6BB5;&#x5217;&#x8868;) FROM &#x8868;&#x540D;;
例:

-- 01学生的课程号和分数都是不同的,所以聚合时不能筛选,而共有的重复数据则可以选择
mysql> SELECT * from Score WHERE s_id = "01";
+------+------+---------+
| s_id | c_id | s_score |
+------+------+---------+
| 01   | 01   |      80 |
| 01   | 02   |      90 |
| 01   | 03   |      99 |
+------+------+---------+
-- 计算学号01学生的平均成绩
mysql> SELECT s_id, avg(s_score) as "平均成绩" from Score WHERE s_id = "01";
+------+--------------+
| s_id |    平均成绩   |
+------+--------------+
| 01   |      89.6667 |
+------+--------------+

分组查询

一般求分组字段和聚合函数的字段。

语法:
SELECT &#x5B57;&#x6BB5;&#x5217;&#x8868; FROM &#x8868;&#x540D; [ WHERE &#x6761;&#x4EF6; ] GROUP BY &#x5206;&#x7EC4;&#x5B57;&#x6BB5;&#x540D; [ HAVING &#x5206;&#x7EC4;&#x540E;&#x7684;&#x8FC7;&#x6EE4;&#x6761;&#x4EF6; ];

where 和 having 的区别:

  • 执行时机不同:where是分组之前进行过滤, &#x4E0D;&#x6EE1;&#x8DB3;where&#x6761;&#x4EF6;&#x4E0D;&#x53C2;&#x4E0E;&#x5206;&#x7EC4;;having是分组后对结果进行过滤。
  • 判断条件不同:where不能对聚合函数进行判断,而having可以。

例子:

mysql> SELECT * FROM Student;
+------+--------+------------+-------+
| s_id | s_name | s_birth    | s_sex |
+------+--------+------------+-------+
| 01   | 赵雷   | 1990-01-01 | 男    |
| 02   | 钱电   | 1990-12-21 | 男    |
| 03   | 孙风   | 1990-05-20 | 男    |
| 04   | 李云   | 1990-08-06 | 男    |
| 05   | 周梅   | 1991-12-01 | 女    |
| 06   | 吴兰   | 1992-03-01 | 女    |
| 07   | 郑竹   | 1989-07-01 | 女    |
| 08   | 王菊   | 1990-01-20 | 女    |
+------+--------+------------+-------+

-- 根据性别分组,统计男性和女性数量(只显示分组数量,不显示哪个是男哪个是女)
mysql> SELECT count(*) FROM Student GROUP BY s_sex;
+----------+
| count(*) |
+----------+
|        4 |
|        4 |
+----------+

-- 根据性别分组,统计男性和女性数量
mysql> SELECT s_sex, count(*) as "人数" FROM Student GROUP BY s_sex;
+-------+--------+
| s_sex | 人数   |
+-------+--------+
| 男    |      4 |
| 女    |      4 |
+-------+--------+

-- 年龄小于45,并根据工作地址分组
select workaddress, count(*) from employee where age < 45 group by workaddress;
-- 年龄小于45,并根据工作地址分组,获取员工数量大于等于3的工作地址
select workaddress, count(*) as address_count from employee where age < 45 group by workaddress having address_count >= 3; -- having 后可以使用聚合函数的结果而where不可以
注意事项
  • 执行顺序:where > 聚合函数 > having
  • 分组之后,查询的字段一般为聚合函数和分组字段,查询其他字段无任何意义

排序查询

语法:
SELECT &#x5B57;&#x6BB5;&#x5217;&#x8868; FROM &#x8868;&#x540D; ORDER BY &#x5B57;&#x6BB5;1 &#x6392;&#x5E8F;&#x65B9;&#x5F0F;1 [, &#x5B57;&#x6BB5;2 &#x6392;&#x5E8F;&#x65B9;&#x5F0F;2];

排序方式:

  • ASC: 升序( 默认
  • DESC: 降序

例子:

-- 根据年龄升序排序
SELECT * FROM employee ORDER BY age ASC;
SELECT * FROM employee ORDER BY age;
-- 两字段排序,根据年龄升序排序,入职时间降序排序
SELECT * FROM employee ORDER BY age ASC, entrydate DESC;
注意事项

如果是多字段排序,当第一个字段值相同时,才会根据第二个字段进行排序

分页查询

语法:
SELECT &#x5B57;&#x6BB5;&#x5217;&#x8868; FROM &#x8868;&#x540D; LIMIT &#x8D77;&#x59CB;&#x7D22;&#x5F15;, &#x67E5;&#x8BE2;&#x8BB0;&#x5F55;&#x6570;;

例子:

-- 查询第一页数据,展示10条
SELECT * FROM employee LIMIT 0, 10;
-- 查询第二页
SELECT * FROM employee LIMIT 10, 10;
注意事项
  • 起始索引从0开始,起始索引 = (查询页码 – 1) * 每页显示记录数
  • 分页查询是数据库的方言,不同数据库有不同实现,MySQL是LIMIT
  • 如果查询的是第一页数据,起始索引可以省略,直接简写 LIMIT 10

DQL执行顺序

from先找到表,where确定数据范围,group by对所选数据进行分组,select选择要返回的字段,order by指定排序,最后根据limit的索引和范围返回数据。

FROM -> WHERE -> GROUP BY -> SELECT -> ORDER BY -> LIMIT

DCL

管理用户

查询用户:

USE mysql;
SELECT * FROM user;

创建用户:
CREATE USER '&#x7528;&#x6237;&#x540D;'@'&#x4E3B;&#x673A;&#x540D;' IDENTIFIED BY '&#x5BC6;&#x7801;';

修改用户密码:
ALTER USER '&#x7528;&#x6237;&#x540D;'@'&#x4E3B;&#x673A;&#x540D;' IDENTIFIED WITH mysql_native_password BY '&#x65B0;&#x5BC6;&#x7801;';

删除用户:
DROP USER '&#x7528;&#x6237;&#x540D;'@'&#x4E3B;&#x673A;&#x540D;';

例子:

-- 创建用户test,只能在当前主机localhost访问
create user 'test'@'localhost' identified by '123456';
-- 创建用户test,能在任意主机访问
create user 'test'@'%' identified by '123456';
create user 'test' identified by '123456';
-- 修改密码                                     加密方式:mysql_native_password
alter user 'test'@'localhost' identified with mysql_native_password by '1234';
-- 删除用户
drop user 'test'@'localhost';
注意事项
  • 主机名可以使用 % 通配

权限控制

常用权限:

权限 说明 ALL, ALL PRIVILEGES 所有权限 SELECT 查询数据 INSERT 插入数据 UPDATE 修改数据 DELETE 删除数据 ALTER 修改表 DROP 删除数据库/表/视图 CREATE 创建数据库/表

查询权限:
SHOW GRANTS FOR '&#x7528;&#x6237;&#x540D;'@'&#x4E3B;&#x673A;&#x540D;';

授予权限:
GRANT &#x6743;&#x9650;&#x5217;&#x8868; ON &#x6570;&#x636E;&#x5E93;&#x540D;.&#x8868;&#x540D; TO '&#x7528;&#x6237;&#x540D;'@'&#x4E3B;&#x673A;&#x540D;';

撤销权限:
REVOKE &#x6743;&#x9650;&#x5217;&#x8868; ON &#x6570;&#x636E;&#x5E93;&#x540D;.&#x8868;&#x540D; FROM '&#x7528;&#x6237;&#x540D;'@'&#x4E3B;&#x673A;&#x540D;';

mysql> SHOW GRANTS FOR 'ravi'@'localhost';
+------------------------------------------+
| Grants for ravi@localhost                |
+------------------------------------------+
| GRANT USAGE ON *.* TO ravi@localhost |
+------------------------------------------+
mysql> GRANT ALL ON myDb.* to 'ravi'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL ON checkup.* to 'ravi'@'localhost';
Query OK, 0 rows affected (0.01 sec)

-- 登陆
mysql -u ravi -p
Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 18
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| checkup            |
| information_schema |
| myDb               |
+--------------------+

-- 撤销权限
mysql> REVOKE ALL ON checkup.* FROM 'ravi'@'localhost';
Query OK, 0 rows affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| myDb               |
+--------------------+
2 rows in set (0.00 sec)
注意事项
  • 多个权限用逗号分隔
  • 授权时,数据库名和表名可以用 * 进行通配,代表所有

函数

  • 字符串函数
  • 数值函数
  • 日期函数
  • 流程函数

字符串函数

常用函数:

函数 功能 CONCAT(s1, s2, …, sn) 字符串拼接,将s1, s2, …, sn拼接成一个字符串 LOWER(str) 将字符串全部转为小写 UPPER(str) 将字符串全部转为大写 LPAD(str, n, pad) 左填充,用字符串pad对str的左边进行填充,达到n个字符串长度 RPAD(str, n, pad) 右填充,用字符串pad对str的右边进行填充,达到n个字符串长度 TRIM(str) 去掉字符串头部和尾部的空格 SUBSTRING(str, start, len) 返回从字符串str从start位置起的len个长度的字符串

使用示例:

-- 拼接
SELECT CONCAT('Hello', 'World');
-- 小写
SELECT LOWER('Hello');
-- 大写
SELECT UPPER('Hello');
-- 左填充
mysql> SELECT LPAD('01', 5, '-');
+--------------------+
| LPAD('01', 5, '-') |
+--------------------+
| ---01              |
+--------------------+
-- 右填充
mysql> SELECT RPAD('01', 5, '-');
+--------------------+
| RPAD('01', 5, '-') |
+--------------------+
| 01---              |
+--------------------+
-- 去除空格
SELECT TRIM(' Hello World ');
-- 切片(起始索引为1)
SELECT SUBSTRING('Hello World', 1, 5); -- Hello

数值函数

常见函数:

函数 功能 CEIL(x) 向上取整 FLOOR(x) 向下取整 MOD(x, y) 返回x/y的模 RAND() 返回0~1内的随机数 ROUND(x, y) 求参数x的四舍五入值,保留y位小数

mysql> SELECT LPAD(ROUND(RAND() * 1000000, 0), 6, '0') as "验证码";
+-----------+
| 验证码    |
+-----------+
| 022516    |
+-----------+

日期函数

常用函数:

函数 功能 CURDATE() 返回当前日期 CURTIME() 返回当前时间 NOW() 返回当前日期和时间 YEAR(date) 获取指定date的年份 MONTH(date) 获取指定date的月份 DAY(date) 获取指定date的日期 DATE_ADD(date, INTERVAL expr type) 返回一个日期/时间值加上一个时间间隔expr后的时间值 DATEDIFF(date1, date2) 返回起始时间date1和结束时间date2之间的天数 FROM_DAYS(number) 将日子总数转换为年月日

例子:

-- CURDATE
mysql> SELECT CURDATE() as "today";
+------------+
| today      |
+------------+
| 2022-09-17 |
+------------+

-- CURTIME
mysql> SELECT CURTIME() as "time";
+----------+
| time     |
+----------+
| 20:26:02 |
+----------+

-- NOW
mysql> SELECT NOW() as "now";
+---------------------+
| now                 |
+---------------------+
| 2022-09-17 20:26:33 |
+---------------------+

-- year
mysql> SELECT YEAR(NOW()) as "year";
+------+
| year |
+------+
| 2022 |
+------+

-- DATE_ADD
mysql> SELECT DATE_ADD(NOW(), INTERVAL 70 YEAR) as "70years later";
+---------------------+
| 70years later       |
+---------------------+
| 2092-09-17 20:28:56 |
+---------------------+

-- DATEDIFF
mysql> SELECT DATEDIFF('2022-9-17', '2021-12-10') as "diff";
+------+
| diff |
+------+
|  281 |
+------+

-- 查询所有学生的年龄,并且根据年龄倒叙排序
mysql> SELECT s_id, s_name, YEAR(FROM_DAYS(DATEDIFF(CURDATE(), s_birth))) as s_age FROM Student;
mysql> SELECT s_id, s_name, TIMESTAMPDIFF(YEAR,s_birth,CURDATE()) as s_age FROM
Student;
+------+--------+-------+
| s_id | s_name | s_age |
+------+--------+-------+
| 01   | 赵雷   |    32 |
| 02   | 钱电   |    31 |
| 03   | 孙风   |    32 |
| 04   | 李云   |    32 |
| 05   | 周梅   |    30 |
| 06   | 吴兰   |    30 |
| 07   | 郑竹   |    33 |
| 08   | 王菊   |    32 |
+------+--------+-------+

流程函数

常用函数:

函数 功能 IF(value, t, f) 如果value为true,则返回t,否则返回f IFNULL(value1, value2) 如果value1不为空,返回value1,否则返回value2 CASE WHEN [ val1 ] THEN [ res1 ] … ELSE [ default ] END 如果val1为true,返回res1,… 否则返回default默认值 CASE [ expr ] WHEN [ val1 ] THEN [ res1 ] … ELSE [ default ] END 如果expr的值等于val1,返回res1,… 否则返回default默认值

例子:

-- IFNULL
mysql> SELECT IFNULL('ok','default'); -- 不为空
+------------------------+
| IFNULL('ok','default') |
+------------------------+
| ok                     |
+------------------------+
mysql> SELECT IFNULL('','default');  -- 空串,返回空串
+----------------------+
| IFNULL('','default') |
+----------------------+
|                      |
+----------------------+
mysql> SELECT IFNULL(null,'default');  -- null
+------------------------+
| IFNULL(null,'default') |
+------------------------+
| default                |
+------------------------+

select
    name,
    (case when age > 30 then '中年' else '青年' end)
from employee;
select
    name,
    (case workaddress when '北京市' then '一线城市' when '上海市' then '一线城市' else '二线城市' end) as '工作地址'
from employee;

约束

分类:

约束 描述 关键字 非空约束 限制该字段的数据不能为null NOT NULL 唯一约束 保证该字段的所有数据都是唯一、不重复的 UNIQUE 主键约束 主键是一行数据的唯一标识,要求非空且唯一 PRIMARY KEY 默认约束 保存数据时,如果未指定该字段的值,则采用默认值 DEFAULT 检查约束(8.0.1版本后) 保证字段值满足某一个条件 CHECK 外键约束 用来让两张图的数据之间建立连接,保证数据的一致性和完整性 FOREIGN KEY

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

常用约束

约束条件 关键字 主键 PRIMARY KEY 自动增长 AUTO_INCREMENT 不为空 NOT NULL 唯一 UNIQUE 逻辑条件 CHECK 默认值 DEFAULT

例子:

create table user(
    id int primary key auto_increment,
    name varchar(10) not null unique,
    age int check(age > 0 and age < 120),
    status char(1) default '1',
    gender char(1)
);

外键约束

添加外键:

CREATE TABLE 表名(
    字段名 字段类型,
    ...
    [CONSTRAINT] [外键名称] FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名)
);
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名);

-- 例子
alter table emp add constraint fk_emp_dept_id foreign key(dept_id) references dept(id);

删除外键:
ALTER TABLE &#x8868;&#x540D; DROP FOREIGN KEY &#x5916;&#x952E;&#x540D;;

删除/更新行为

行为 说明 NO ACTION 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则不允许删除/更新(与RESTRICT一致) RESTRICT 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则不允许删除/更新(与NO ACTION一致) CASCADE 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则也删除/更新外键在子表中的记录 SET NULL 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则设置子表中该外键值为null(要求该外键允许为null) SET DEFAULT 父表有变更时,子表将外键设为一个默认值(Innodb不支持)

更改删除/更新行为:
ALTER TABLE &#x8868;&#x540D; ADD CONSTRAINT &#x5916;&#x952E;&#x540D;&#x79F0; FOREIGN KEY (&#x5916;&#x952E;&#x5B57;&#x6BB5;) REFERENCES &#x4E3B;&#x8868;&#x540D;(&#x4E3B;&#x8868;&#x5B57;&#x6BB5;&#x540D;) ON UPDATE &#x884C;&#x4E3A; ON DELETE &#x884C;&#x4E3A;;

多表查询

多表关系

  • 一对多(多对一)
  • 多对多
  • 一对一

一对多

案例:部门与员工
关系:一个部门对应多个员工,一个员工对应一个部门
实现:在多的一方建立外键,关联一的一方的主键

多对多

案例:学生与课程
关系:一个学生可以选多门课程,一门课程也可以供多个学生选修
实现:建立第三张中间表student_course,中间表至少包含两个外键,分别关联两方主键

MySQL笔记汇总(1)基础篇

一对一

案例:用户与用户详情
关系:一对一关系,多用于 &#x5355;&#x8868;&#x62C6;&#x5206;,将一张表的基础字段放在一张表中,其他详情字段放在另一张表中,以提升操作效率
实现:在任意一方加入外键,关联另外一方的主键,并且设置外键为唯一的(UNIQUE)

MySQL笔记汇总(1)基础篇

查询

合并查询(笛卡尔积,会展示所有组合结果):
select * from employee, dept;

笛卡尔积:两个集合A集合和B集合的所有组合情况(在多表查询时,需要消除无效的笛卡尔积)

消除无效笛卡尔积:
select * from employee, dept where employee.dept = dept.id;

内连接查询

内连接查询的是两张表交集的部分

如果有数据不满足相交条件,则不会被查询到。

隐式内连接:
SELECT &#x5B57;&#x6BB5;&#x5217;&#x8868; FROM &#x8868;1, &#x8868;2 WHERE &#x6761;&#x4EF6; ...;

显式内连接:
SELECT &#x5B57;&#x6BB5;&#x5217;&#x8868; FROM &#x8868;1 [ INNER ] JOIN &#x8868;2 ON &#x8FDE;&#x63A5;&#x6761;&#x4EF6; ...;

显式性能比隐式高

例子:

-- 查询员工姓名,及关联的部门的名称
-- 隐式
select e.name, d.name from employee as e, dept as d where e.dept = d.id;
-- 显式
select e.name, d.name from employee as e inner join dept as d on e.dept = d.id;

外连接查询

左外连接:
查询左表所有数据,以及两张表交集部分数据
SELECT &#x5B57;&#x6BB5;&#x5217;&#x8868; FROM &#x8868;1 LEFT [ OUTER ] JOIN &#x8868;2 ON &#x6761;&#x4EF6; ...;
相当于查询表1的所有数据,包含表1和表2交集部分数据

右外连接:
查询右表所有数据,以及两张表交集部分数据
SELECT &#x5B57;&#x6BB5;&#x5217;&#x8868; FROM &#x8868;1 RIGHT [ OUTER ] JOIN &#x8868;2 ON &#x6761;&#x4EF6; ...;

例子:

-- 左
select e.*, d.name from employee as e left outer join dept as d on e.dept = d.id;
select d.name, e.* from dept d left outer join emp e on e.dept = d.id;  -- 这条语句与下面的语句效果一样
-- 右
select d.name, e.* from employee as e right outer join dept as d on e.dept = d.id;

左连接可以查询到没有dept的employee,右连接可以查询到没有employee的dept

自连接查询

当前表与自身的连接查询,自连接必须使用表别名

语法:
SELECT &#x5B57;&#x6BB5;&#x5217;&#x8868; FROM &#x8868;A &#x522B;&#x540D;A JOIN &#x8868;A &#x522B;&#x540D;B ON &#x6761;&#x4EF6; ...;

自连接查询,可以是内连接查询,也可以是外连接查询

例子:

-- 查询员工及其所属领导的名字
select a.name, b.name from employee a, employee b where a.manager = b.id;
-- 没有领导的也查询出来
select a.name, b.name from employee a left join employee b on a.manager = b.id;

联合查询 union, union all

把多次查询的结果合并,形成一个新的查询集

语法:

SELECT 字段列表 FROM 表A ...

UNION [ALL]
SELECT 字段列表 FROM 表B ...

薪资低于5k的员工和年龄大于50的员工。

注意事项

  • UNION ALL 会有重复结果,UNION 不会
  • 多张表的列数和类型保持一致
  • 联合查询比使用or效率高,不会使索引失效

子查询

SQL语句中嵌套SELECT语句,称谓嵌套查询,又称子查询。
SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2);
子查询 &#x5916;&#x90E8; 的语句可以是 INSERT / UPDATE / DELETE / SELECT 的任何一个

根据子查询结果可以分为:

  • 标量子查询(子查询结果为单个值)
  • 列子查询(子查询结果为一列)
  • 行子查询(子查询结果为一行)
  • 表子查询(子查询结果为多行多列)

根据子查询位置可分为:

  • WHERE 之后
  • FROM 之后
  • SELECT 之后

标量子查询

子查询返回的结果是单个值(数字、字符串、日期等)。
常用操作符:- < > > >= <

Original: https://www.cnblogs.com/ceeSomething8/p/16719632.html
Author: cee_nil
Title: MySQL笔记汇总(1)基础篇

原创文章受到原创版权保护。转载请注明出处:https://www.johngo689.com/712230/

转载文章受原作者版权保护。转载请注明原作者出处!

(0)

大家都在看

亲爱的 Coder【最近整理,可免费获取】👉 最新必读书单  | 👏 面试题下载  | 🌎 免费的AI知识星球