MySQL打卡4-表联结@Datawhale

1. 设置mysql别名

表别名

查询数据时,如果表名很长,使用起来不方便,此时,就可以为表取一个别名,用这个别名来代替表的名称
SELECT * FROM 表名 [AS] 别名;
注意,为表指定别名,AS关键字可以省略不写

mysql> select * from salary;
+----+------+-----+--------+
| id | name | sex | salary |
+----+------+-----+--------+
|  1 | A    | f   |   2500 |
|  2 | B    | m   |   1500 |
|  3 | C    | f   |   5500 |
|  4 | D    | m   |    500 |
+----+------+-----+--------+
4 rows in set (0.00 sec)

mysql> select * from salary as ss where ss.salary > 2000;
+----+------+-----+--------+
| id | name | sex | salary |
+----+------+-----+--------+
|  1 | A    | f   |   2500 |
|  3 | C    | f   |   5500 |
+----+------+-----+--------+
2 rows in set (0.00 sec)

列别名

在查询数据时,为了使显示的查询结果更加直观,可以为字段取一个别名
SELECT 字段名 [AS] 别名 [,字段名 [AS] 别名,……] FROM 表名;
注意,为字段指定别名,AS关键字可以省略不写。

mysql> select id as user_id, name as user_name, sex as user_sex, salary as user_salary from salary;
+---------+-----------+----------+-------------+
| user_id | user_name | user_sex | user_salary |
+---------+-----------+----------+-------------+
|       1 | A         | f        |        2500 |
|       2 | B         | m        |        1500 |
|       3 | C         | f        |        5500 |
|       4 | D         | m        |         500 |
+---------+-----------+----------+-------------+
4 rows in set (0.00 sec)

2. INNER JOIN、LEFT JOIN、CROSS JOIN、自连接、UNION几种方式区别与联系

inner join:

左右两张表连接字段完全一致
SELECT XXX FROM XXX INNER JOIN XXX ON XXX; 这里 INNER 可以省略

left join:

以左表为全部,去连接右表

cross join:

交叉连接,得到的结果是两个表的乘积,即笛卡尔积.
笛卡尔(Descartes)乘积又叫直积。假设集合A={a,b},集合B={0,1,2},则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1), (b,2)}。可以扩展到多个集合的情况。类似的例子有,如果A表示某学校学生的集合,B表示该学校所有课程的集合,则A与B的笛卡尔积表示所有可能的选课情况。
笛卡尔积算法针对的是表之间数据行的匹配次数,跟内连接还是外连接无关,至于查询结果与你的查询条件有关系。

自连接:

自连接是连接的一种用法,但并不是连接的一种类型,因为他的本质是把一张表当成两张表来使用。

mysql有时在信息查询时需要进行对自身连接(自连接),所以我们需要为表定义别名。
mysql自连接

union:

UNION 语句:用于将不同表中相同列中查询的数据展示出来;(不包括重复数据)

{A, B} UNION {B, C} = {A, B, C}

SELECT 列名称 FROM 表名称 UNION SELECT 列名称 FROM 表名称 ORDER BY 列名称;

union all:

UNION ALL 语句:用于将不同表中相同列中查询的数据展示出来;(包括重复数据)

{A, B} UNION ALL {B, C} = {A, B, B, C}

SELECT 列名称 FROM 表名称 UNION ALL SELECT 列名称 FROM 表名称 ORDER BY 列名称;

作业:

项目五:组合两张表 (难度:简单)
在数据库中创建表1和表2,并各插入三行数据(自己造)
表1: Person
+-------------+---------+
| 列名         | 类型     |
+-------------+---------+
| PersonId    | int     |
| FirstName   | varchar |
| LastName    | varchar |
+-------------+---------+
PersonId 是上表主键

表2: Address
+-------------+---------+
| 列名         | 类型    |
+-------------+---------+
| AddressId   | int     |
| PersonId    | int     |
| City        | varchar |
| State       | varchar |
+-------------+---------+
AddressId 是上表主键

编写一个 SQL 查询,满足条件:无论 person 是否有地址信息,都需要基于上述两表提供 person 的以下
信息:FirstName, LastName, City, State
--创建表
create table person (
personid int not null primary key,
firstname varchar(20) not null,
lastname varchar(20) not null
);
--插入数据
insert into person values (101, 'ming', 'Yao');
insert into person values (102, 'lei', 'Wu');
insert into person values (103, 'zhi', 'Zheng');
--查询结果
mysql> select * from person;
+----------+-----------+----------+
| personid | firstname | lastname |
+----------+-----------+----------+
|      101 | ming      | Yao      |
|      102 | lei       | Wu       |
|      103 | zhi       | Zheng    |
+----------+-----------+----------+
3 rows in set (0.00 sec)
--创建表
create table address (
addressid int not null primary key,
personid int not null,
city varchar(20) not null,
state varchar(20) not null
);
--插入数据
insert into address values (8000, 9999, 'xian', 'UA');
insert into address values (8008, 102, 'beijing', 'AB');
insert into address values (9009, 103, 'hangzhou', 'AC');
--查询结果
mysql> select * from address;
+-----------+----------+----------+-------+
| addressid | personid | city     | state |
+-----------+----------+----------+-------+
|      8000 |     9999 | xian     | UA    |
|      8008 |      102 | beijing  | AB    |
|      9009 |      103 | hangzhou | AC    |
+-----------+----------+----------+-------+
3 rows in set (0.00 sec)

mysql> select firstname, lastname, a.city, a.state
    -> from person
    -> left join address as a on person.personid = a.personid;
+-----------+----------+----------+-------+
| firstname | lastname | city     | state |
+-----------+----------+----------+-------+
| ming      | Yao      | NULL     | NULL  |
| lei       | Wu       | beijing  | AB    |
| zhi       | Zheng    | hangzhou | AC    |
+-----------+----------+----------+-------+
3 rows in set (0.00 sec)
项目六:删除重复的邮箱(难度:简单)
编写一个 SQL 查询,来删除 email 表中所有重复的电子邮箱,重复的邮箱里只保留 Id 最小 的那个。
+----+---------+
| Id | Email   |
+----+---------+
| 1  | a@b.com |
| 2  | c@d.com |
| 3  | a@b.com |
+----+---------+
Id 是这个表的主键。
例如,在运行你的查询语句之后,上面的 email表应返回以下几行:
+----+------------------+
| Id | Email            |
+----+------------------+
| 1  | a@b.com |
| 2  | c@d.com  |
+----+------------------+

#问题分析
a. 删除重复的电子邮箱
b. 需要保留ID最小的那个(删除的ID比留下的那一条ID要大)
c. 考察自连接知识,把一张表当两张表来用。
--code--
mysql> select * from email;
+----+---------+
| ID | Email   |
+----+---------+
|  1 | a@b.com |
|  2 | c@d.com |
|  3 | a@b.com |
+----+---------+
3 rows in set (0.00 sec)

mysql> delete e1
    -> from email as e1, email as e2
    -> where e1.Email = e2.Email
    -> and e1.ID > e2.ID;
Query OK, 1 row affected (0.01 sec)

mysql> select * from email;
+----+---------+
| ID | Email   |
+----+---------+
|  1 | a@b.com |
|  2 | c@d.com |
+----+---------+
2 rows in set (0.00 sec)

--此处补充delete删除单表及多表的用法,之前不知道这个知识点
# 单表删除内容
mysql> select * from students2;
+-----+-------+--------+---------+
| sid | sname | gender | dept_id |
+-----+-------+--------+---------+
|   1 | 555   | 1      |       2 |
|   2 | 555   | 2      |       2 |
|   3 | 555   | 3      |       2 |
|   4 | 555   | 3      |       1 |
|   5 | 555   | 1      |       1 |
+-----+-------+--------+---------+
5 rows in set (0.00 sec)

mysql> delete from students2 where sid=4;
Query OK, 1 row affected (0.05 sec)

mysql> select * from students2;
+-----+-------+--------+---------+
| sid | sname | gender | dept_id |
+-----+-------+--------+---------+
|   1 | 555   | 1      |       2 |
|   2 | 555   | 2      |       2 |
|   3 | 555   | 3      |       2 |
|   5 | 555   | 1      |       1 |
+-----+-------+--------+---------+
4 rows in set (0.00 sec)

# 多表删除内容
mysql> select * from person;
+----------+-----------+----------+
| personid | firstname | lastname |
+----------+-----------+----------+
|      101 | ming      | Yao      |
|      102 | lei       | Wu       |
|      103 | zhi       | Zheng    |
+----------+-----------+----------+
3 rows in set (0.00 sec)

mysql> select * from address;
+-----------+----------+----------+-------+
| addressid | personid | city     | state |
+-----------+----------+----------+-------+
|      8000 |     9999 | xian     | UA    |
|      8008 |      102 | beijing  | AB    |
|      9009 |      103 | hangzhou | AC    |
+-----------+----------+----------+-------+
3 rows in set (0.00 sec)

mysql> delete p1, a1
    -> from person p1 inner join address a1
    -> where p1.personid = a1.personid;
Query OK, 4 rows affected (0.01 sec)

mysql> select * from person;
+----------+-----------+----------+
| personid | firstname | lastname |
+----------+-----------+----------+
|      101 | ming      | Yao      |
+----------+-----------+----------+
1 row in set (0.00 sec)

mysql> select * from address;
+-----------+----------+------+-------+
| addressid | personid | city | state |
+-----------+----------+------+-------+
|      8000 |     9999 | xian | UA    |
+-----------+----------+------+-------+
1 row in set (0.00 sec)

总结

  • 熟悉inner join / left join / cross join / 自连接 、union / union all 几种不同的连接方式
  • 主要学习自连接用法,一张表当两张表用
  • 深入学习delete语句用法

参考文章:
mysql自连接的一些用法

mysql union 操作符 | 菜鸟教程

mysql(3)-- 内连接、外连接的区别

MySQL-5.7 DELETE语句详解

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值