美文网首页
MySQL基本语句

MySQL基本语句

作者: 殷俊杰 | 来源:发表于2018-05-06 16:50 被阅读0次

一、连接查询

image.png image.png

1.1内连接

内连接(INNER JOIN)使用比较运算符进行表间列数据的比较操作，并列出这些表中与连接条件相匹配的数据行。

select * from t_person p,t_address a where p.PersonId=a.personid;

等价于

select * from t_person p INNER JOIN t_address a on p.PersonId=a.personid;
image.png 
select * from t_person p INNER JOIN t_address a on p.PersonId>a.addressid order by p.PersonId;
image.png 
select * from t_person p INNER JOIN t_address a on p.PersonId<a.addressid order by p.PersonId;
image.png

1.2外连接

1.2.1左连接

LEFT JOIN（左连接），返回左表的全部记录，即使右表中没有对应匹配记录

select * from t_person p LEFT JOIN t_address a on p.PersonId=a.personid;  
等价于
select * from t_address a right join t_person p on a.personid=p.personid;
image.png

在内连接中只返回了124，因为124在右表address中能匹配上，即124是有地址的，但是左外连接是返回左表全部记录，匹配不上会返回null。

select * from t_person p LEFT JOIN t_address a on p.PersonId>a.personid;

可以看到，虽然personid为1的人没有匹配上，但是最后也显示了出来


image.png

1.2.2右连接

RIGHT JOIN（右连接），返回右表的全部记录，即使左表中没有对应匹配记录

select * from t_person p RIGHT JOIN t_address a on p.PersonId=a.personid;
等价于
select * from t_address a left join on t_person p on p.personid=a.personid;

返回右表中全部记录，不管能不能与左表匹配上，此例子中右表全都能与左表匹配，所以没有null值。


image.png

1.2.3全连接

FULL JOIN(全连接)，返回左表，右表的全部记录，即使没有对应匹配的记录，注意：MySQL不支持 FULL JOIN，不过可以通过UNION关键字来合并LEFT JOIN 和RIGTH JOIN来模拟，注意区分UNION 和UNION ALL的区别。为更好的显示效果，向address表插入一条personid为9的地址。

select * from t_person p LEFT JOIN t_address a on p.PersonId=a.personid
union
select * from t_person p RIGHT JOIN t_address a on p.PersonId=a.personid;
image.png 
select * from t_person p LEFT JOIN t_address a on p.PersonId=a.personid
union all
select * from t_person p RIGHT JOIN t_address a on p.PersonId=a.personid;
image.png

union会自动去重，union all不会去重。
留个课堂作业，研究一下minus

二、子查询

子查询是指一个查询语句嵌套在另一个查询语句内部的查询。在 SELECT 子句中先计算子查询，子查询结果作为外层另一个查询的过滤条件。
子查询中常用的操作符有ANY、SOME、ALL、EXISTS、IN，也可以使用比较运算符。子查询可以添加到 SELECT、UPDATE 和 DELETE 语句中，而且可以进行多层嵌套。

2.1 在条件表达式中产生标量的子查询

SELECT * FROM score WHERE
    id = (
        SELECT
            event_id
        FROM
            EVENT
        WHERE
            date = '2015-07-01'
        AND type = 'Q'
    );

所谓标量，就是单个属性的一个原子值。当子查询出现在 WHERE 子句中的比较运算符（= ,>, >= ,< , <= ,<>）的右边，其输出结果应该只有一个才对。很容易理解，如果返回多条结果，就无法进行比较，系统就会报错。
又如：

SELECT * FROM teacher WHERE birth = MIN(birth);  /*错误*/

这个查询是错的！因为MySQL不允许在子句里面使用统计函数，所以改用子查询：

SELECT * FROM teacher WHERE birth = (SELECT MIN(birth)  FROM teacher);

2.2 在条件表达式中产生集合的子查询

如果子查询的输出是一个结果集合，可以通过 ANY、ALL、IN 进行比较。

2.2.1 ANY与SOME

ANY和SOME关键字是同义词，表示满足其中任一条件。它们允许创建一个表达式对子查询的返回结果集进行比较：

SELECT num1
FROM t1
WHERE num1 > ANY(SELECT num2 
                 FROM t2);

上面的子查询返回 t2 的 num2 列，然后将 t1 中的 num1 值与之进行比较，只要大于 num2 的任何一个值，即为符合查询条件的结果。

等价于：

SELECT num1
FROM t1
WHERE num1 > SOME(SELECT num2 
                  FROM t2);

2.2.2 ALL

与ANY/SOME不同，使用ALL时需要同时满足所有内层查询的条件。

SELECT num1
FROM t1
WHERE num1 > ALL(SELECT num2 
                 FROM t2);

上面的子查询还是返回 t2 的 num2 列，然后将 t1 中的 num1 值与之进行比较。但是只有大于所有 num2 值的 num1 才是符合查询条件的结果。

2.2.3 IN

IN关键字后接一个子查询，若在子查询结果集中，返回true，否则返回false。与之相对的是NOT IN。

SELECT num1
FROM t1
WHERE num1 IN (SELECT num2 
               FROM t2);

2.3 在条件表达式中测试空/非空的子查询

EXISTS关键字后接一个任意的子查询，系统对子查询进行运算以判断它是否返回行。

若至少返回一行，那么 EXISTS 的结果为 true，此时外层查询语句将进行查询；
若没有返回任何行，那么 EXISTS 的结果为 false，此时外层语句将不进行查询。

SELECT sName
FROM Student
WHERE EXISTS (SELECT * 
              FROM Grade 
              WHERE gScore < 60);

EXISTS和NOT EXISTS的结果只取决于是否会返回行，而不取决于这些行的内容。

2.4 关联子查询

一般的子查询只计算一次，其结果用于外层查询。但关联子查询需要计算多次。

子查询中使用了主查询中的某些字段，主查询每扫描一行都要执行一次子查询，这种子查询称为关联子查询（Correlated Subquery）。

select p.personid,p.firstname,p.lastname from t_person p where p.PersonId not in (select a.personid from t_address a where a.personid=p.personid);

上面的子查询中使用了 t_person表的 personid 字段。对于 person 表中每一个 personid都会执行一次子查询。
2.5 FROM子句中的子查询
子查询可以用括号括起来作为一个关系，从而出现在 FROM 列表中。由于子查询的结果关系没有正式的名字，故必须给它取一个别名。from后的子查询必须有别名

SELECT *
FROM Grade,
    (SELECT * FROM Student WHERE sDept='CS')x
WHERE x.sID=Grade.gID;

x 就是子查询的结果关系的别名。

三、连表更新

是的，没有错，MySQL是支持多表连表更新的，就是这么骚，Oracle是不会允许有这么骚的操作的，只能通过子查询来连表更新。还是上面的两张表

BEGIN;
update t_person p,t_address a set p.lastname=a.state  where p.PersonId=a.personid;
ROLLBACK;

之前


image.png
image.png

之后


image.png
好，我们回滚
我们再看另一种方法，这种方法Oracle和MySQL都支持，不过一定要加Exists否则将全表更新，不匹配的项将置为null。
之前，好，还是刚才那样，执行下面语句 
update t_person p set p.lastname=(SELECT a.state from t_address a where a.personid=p.PersonId) where exists (SELECT 1 from t_address b where b.personid=p.personid);

查看更新后结果


image.png

该更新的都更新了，不该更新的都没更新，是我们要的结果，好，我们回滚。我们来看看不加exists会怎么样

update t_person p set p.lastname=(SELECT a.state from t_address a where a.personid=p.PersonId)

[SQL]update t_person p set p.lastname=(SELECT a.state from t_address a where a.personid=p.PersonId) 

受影响的行: 5
时间: 0.000s

一看这个就知道凉了，对吧，我们还是来看下更新后结果


image.png

看，果然全表更新了，凉凉，如果你在公司写了这么个sql，那可真是溜的一p，还好我开了事务，嘻嘻，回滚继续。

四、连表删除

还是上面的表

delete p,a from t_person p , t_address a where a.personid=p.personid ;

执行这一波操作之后，等于是把select * from t_person p,t_address a where p.PersonId=a.personid;所查询出来的结果全部删除掉了，只要能匹配上的，p表，a表都会删除，执行后结果如下，剩下的都是匹配不上的

image.png
image.png
回滚。艹。。。刚才忘开事务了。。。。。。。。
额，不是我没开事务，是因为delete from支持事务，delete是不能回滚的，让我想起了truncate的绝望。

limit删除

delete from t_person where personid>2 ORDER BY personid LIMIT 1;

正常情况下delete from t_person where personid>2 ORDER BY personid是会删除所有>2的记录的，但是在此处我限制了limit 1也就是只删除符合条件排序后的第一条记录 也就是personid为3的那一条，执行后结果如下

image.png
回滚
好，今天的课程到这里就要结束了，感谢收看，后期再补其他的吧，例如group by having什么的，感觉也没啥好些的，就酱拜拜
对了。再插一句，mysql是支持select * from table group by column 的select后可以查不是group by的字段，骚操作，在Oracle中是不允许这样的，因为查询出来的结果可能是错的，分组是为了统计。

四、组内排序

例如一个评论表有多个用户评论，需要获取每个用户最后评论的内容。

创建测试数据表及数据

CREATE TABLE `comment` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `content` varchar(200) NOT NULL,
  `addtime` datetime NOT NULL,
  `lastmodify` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `addtime` (`addtime`),
  KEY `uid_addtime` (`user_id`,`addtime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `comment` (`id`, `user_id`, `content`, `addtime`, `lastmodify`) VALUES
(1, 1, '评论1', '2017-05-17 00:00:00', '2017-05-17 00:00:00'),
(2, 1, '评论2', '2017-05-17 00:00:01', '2017-05-17 00:00:01'),
(3, 2, '评论1', '2017-05-17 00:00:02', '2017-05-17 00:00:02'),
(4, 2, '评论2', '2017-05-17 00:00:03', '2017-05-17 00:00:03'),
(5, 3, '评论1', '2017-05-17 00:00:04', '2017-05-17 00:00:04'),
(6, 1, '评论3', '2017-05-17 00:00:05', '2017-05-17 00:00:05'),
(7, 4, '评论1', '2017-05-17 00:00:06', '2017-05-17 00:00:06'),
(8, 4, '评论2', '2017-05-17 00:00:07', '2017-05-17 00:00:07'),
(9, 4, '评论3', '2017-05-17 00:00:08', '2017-05-17 00:00:08'),
(10, 4, '评论4', '2017-05-17 00:00:09', '2017-05-17 00:00:09'),
(11, 3, '评论2', '2017-05-17 00:00:10', '2017-05-17 00:00:10');

select * from comment;
+----+---------+---------+---------------------+---------------------+
| id | user_id | content | addtime             | lastmodify          |
+----+---------+---------+---------------------+---------------------+
|  1 |       1 | 评论1   | 2017-05-17 00:00:00 | 2017-05-17 00:00:00 |
|  2 |       1 | 评论2   | 2017-05-17 00:00:01 | 2017-05-17 00:00:01 |
|  3 |       2 | 评论1   | 2017-05-17 00:00:02 | 2017-05-17 00:00:02 |
|  4 |       2 | 评论2   | 2017-05-17 00:00:03 | 2017-05-17 00:00:03 |
|  5 |       3 | 评论1   | 2017-05-17 00:00:04 | 2017-05-17 00:00:04 |
|  6 |       1 | 评论3   | 2017-05-17 00:00:05 | 2017-05-17 00:00:05 |
|  7 |       4 | 评论1   | 2017-05-17 00:00:06 | 2017-05-17 00:00:06 |
|  8 |       4 | 评论2   | 2017-05-17 00:00:07 | 2017-05-17 00:00:07 |
|  9 |       4 | 评论3   | 2017-05-17 00:00:08 | 2017-05-17 00:00:08 |
| 10 |       4 | 评论4   | 2017-05-17 00:00:09 | 2017-05-17 00:00:09 |
| 11 |       3 | 评论2   | 2017-05-17 00:00:10 | 2017-05-17 00:00:10 |
+----+---------+---------+---------------------+---------------------+

在comment表中，每个用户最后评论的内容就是id为6,4,11,10的记录。

使用group by查询

select * from comment group by user_id;
+----+---------+---------+---------------------+---------------------+
| id | user_id | content | addtime             | lastmodify          |
+----+---------+---------+---------------------+---------------------+
|  1 |       1 | 评论1   | 2017-05-17 00:00:00 | 2017-05-17 00:00:00 |
|  3 |       2 | 评论1   | 2017-05-17 00:00:02 | 2017-05-17 00:00:02 |
|  5 |       3 | 评论1   | 2017-05-17 00:00:04 | 2017-05-17 00:00:04 |
|  7 |       4 | 评论1   | 2017-05-17 00:00:06 | 2017-05-17 00:00:06 |
+----+---------+---------+---------------------+---------------------+

可以看到结果，分组后只会返回分组内的第一条数据。因为group by语法没有进行组内排序的功能，只会按mysql默认的排序显示。
如何才能对group by分组内的数据进行排序了，这个需要根据不同的需求处理.
1.id最大的，评论时间肯定最新
这种情况我们可以使用id代替时间去搜寻并组内排序，使用max(id)就可以获取到每个分组中最大的评论id(即最新的评论)

select * from comment where id in(select max(id) from comment group by user_id) order by user_id;
+----+---------+---------+---------------------+---------------------+
| id | user_id | content | addtime             | lastmodify          |
+----+---------+---------+---------------------+---------------------+
|  6 |       1 | 评论3   | 2017-05-17 00:00:05 | 2017-05-17 00:00:05 |
|  4 |       2 | 评论2   | 2017-05-17 00:00:03 | 2017-05-17 00:00:03 |
| 11 |       3 | 评论2   | 2017-05-17 00:00:10 | 2017-05-17 00:00:10 |
| 10 |       4 | 评论4   | 2017-05-17 00:00:09 | 2017-05-17 00:00:09 |
+----+---------+---------+---------------------+---------------------+

2.id与评论时间没有关系，id大的评论时间可能不是最新

这种情况我们就需要使用max(addtime)来获取最新的评论，但因为不同用户的评论时间有可能相同，因此还需要加多user_id这个条件去查询。

重新创建测试数据

truncate table comment;

INSERT INTO `comment` (`id`, `user_id`, `content`, `addtime`, `lastmodify`) VALUES
(1, 1, '评论1', '2017-05-17 00:00:00', '2017-05-17 00:00:00'),
(2, 1, '评论2', '2017-05-17 00:10:01', '2017-05-17 00:10:01'),
(3, 2, '评论1', '2017-05-17 00:10:02', '2017-05-17 00:10:02'),
(4, 2, '评论2', '2017-05-17 00:00:03', '2017-05-17 00:00:03'),
(5, 3, '评论1', '2017-05-17 00:10:04', '2017-05-17 00:10:04'),
(6, 1, '评论3', '2017-05-17 00:00:05', '2017-05-17 00:00:05'),
(7, 4, '评论1', '2017-05-17 00:00:06', '2017-05-17 00:00:06'),
(8, 4, '评论2', '2017-05-17 00:10:07', '2017-05-17 00:10:07'),
(9, 4, '评论3', '2017-05-17 00:00:08', '2017-05-17 00:00:08'),
(10, 4, '评论4', '2017-05-17 00:00:09', '2017-05-17 00:00:09'),
(11, 3, '评论2', '2017-05-17 00:00:10', '2017-05-17 00:00:10');

select * from comment;
+----+---------+---------+---------------------+---------------------+
| id | user_id | content | addtime             | lastmodify          |
+----+---------+---------+---------------------+---------------------+
|  1 |       1 | 评论1   | 2017-05-17 00:00:00 | 2017-05-17 00:00:00 |
|  2 |       1 | 评论2   | 2017-05-17 00:10:01 | 2017-05-17 00:10:01 |
|  3 |       2 | 评论1   | 2017-05-17 00:10:02 | 2017-05-17 00:10:02 |
|  4 |       2 | 评论2   | 2017-05-17 00:00:03 | 2017-05-17 00:00:03 |
|  5 |       3 | 评论1   | 2017-05-17 00:10:04 | 2017-05-17 00:10:04 |
|  6 |       1 | 评论3   | 2017-05-17 00:00:05 | 2017-05-17 00:00:05 |
|  7 |       4 | 评论1   | 2017-05-17 00:00:06 | 2017-05-17 00:00:06 |
|  8 |       4 | 评论2   | 2017-05-17 00:10:07 | 2017-05-17 00:10:07 |
|  9 |       4 | 评论3   | 2017-05-17 00:00:08 | 2017-05-17 00:00:08 |
| 10 |       4 | 评论4   | 2017-05-17 00:00:09 | 2017-05-17 00:00:09 |
| 11 |       3 | 评论2   | 2017-05-17 00:00:10 | 2017-05-17 00:00:10 |
+----+---------+---------+---------------------+---------------------+

符合条件的应该是id为2,3,5,8的记录
select a.* from comment as a right join
(select user_id, max(addtime) as maxtime from comment where user_id is not null group by user_id) as b
on a.user_id=b.user_id and a.addtime=b.maxtime order by a.user_id asc;

+------+---------+---------+---------------------+---------------------+
| id | user_id | content | addtime | lastmodify |
+------+---------+---------+---------------------+---------------------+
| 2 | 1 | 评论2 | 2017-05-17 00:10:01 | 2017-05-17 00:10:01 |
| 3 | 2 | 评论1 | 2017-05-17 00:10:02 | 2017-05-17 00:10:02 |
| 5 | 3 | 评论1 | 2017-05-17 00:10:04 | 2017-05-17 00:10:04 |
| 8 | 4 | 评论2 | 2017-05-17 00:10:07 | 2017-05-17 00:10:07 |
+------+---------+---------+---------------------+---------------------+
使用right join可以减少外层的数据集。
where user_id is not null 可以使group by user_id时使用索引。

相关文章

  • BigData-MySQL总结大全（一）苏暖人

    BigData之MySQL总结大全 MYSQL常用的基本语句 MYSQL常用的基本语句 例：SELECT TOP ...

  • MySql基本语句

    -建表、删除表 -更改表结构 -基本查询 -更新操作 -删除操作 -插入数据 -聚合函数 -连接语句 -Havin...

  • MySQL基本语句

    库操作 [查] - 查看所有数据库 [增] - 创建新数据库 [查] - 查看创建数据库的SQL的语句 [用] -...

  • MySQL基本语句

    一、连接查询 1.1内连接 内连接(INNER JOIN)使用比较运算符进行表间列数据的比较操作，并列出这些表中与...

  • MySQL基本语句

    一、数据库基础： 二、数据库语句： 三、数据表的操作语句：

  • MySQL——基本语句

    Mysql-基础语法 导语 本博文主要是简述选择数据库和对表内容的增、删、改和查的一些基本语法 USE 语法： U...

  • mysql基本语句

    1.show databases; （展示数据库,注意分号和s） 2.use mysql;(使用某个数据库) 3....

  • MySQL基本语句

    迁移到 https://github.com/lianginet/notes 连接 database管理 tabl...

  • MySQL基本语句

    一、DDL 语句（Data Definition Languages，数据定义语言） 这些语句定义了不同的数据段、...

  • mysql基本语句

    1.增 2.查 3.改（更新） 如果遇到update users set realname...报错的话，先执行 ...

网友评论

      本文标题：MySQL基本语句

      本文链接：https://www.haomeiwen.com/subject/pqoyrftx.html

      延伸阅读

      深度阅读

      • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

      栏目导航

      热点阅读

      关于我们|服务条款|联系我们|MySQL基本语句|投稿指南|网站地图|RSS订阅|排版工具|手机版

      提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

      Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

      备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

      本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

      所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！