• mysql基础


    左外连,右外连

    CREATE DATABASE test_mysql;
    use test_mysql;
    CREATE TABLE left_tb( id INT(11) PRIMARY KEY AUTO_INCREMENT,name VARCHAR(32) );
    CREATE TABLE right_tb( id INT(11) PRIMARY KEY AUTO_INCREMENT,name VARCHAR(32),age INT(11) );
    
    INSERT INTO left_tb (name) VALUES ('zhangsan');
    INSERT INTO left_tb (name) VALUES ('lisi');
    INSERT INTO left_tb (name) VALUES ('wangwu');
    
    SELECT * from left_tb;
    1	zhangsan
    2	lisi
    3	wangwu
    
    
    INSERT INTO right_tb (name,age) VALUES ('aaa',11);
    INSERT INTO right_tb (name,age) VALUES ('bbb',12);
    INSERT INTO right_tb (name,age) VALUES ('ccc',13);
    INSERT INTO right_tb (name,age) VALUES ('ddd',14);
    
    SELECT * from right_tb;
    1	aaa	11
    2	bbb	12
    3	ccc	13
    4	ddd	14
    
    SELECT * from left_tb l LEFT JOIN right_tb r ON l.id=r.id;
    +----+----------+------+------+------+
    | id | name     | id   | name | age  |
    +----+----------+------+------+------+
    |  1 | zhangsan |    1 | aaa  |   11 |
    |  2 | lisi     |    2 | bbb  |   12 |
    |  3 | wangwu   |    3 | ccc  |   13 |
    +----+----------+------+------+------+
    
    
    SELECT * from left_tb l RIGHT JOIN right_tb r ON l.id=r.id;
    +------+----------+----+------+------+
    | id   | name     | id | name | age  |
    +------+----------+----+------+------+
    |    1 | zhangsan |  1 | aaa  |   11 |
    |    2 | lisi     |  2 | bbb  |   12 |
    |    3 | wangwu   |  3 | ccc  |   13 |
    | NULL | NULL     |  4 | ddd  |   14 |
    +------+----------+----+------+------+
    
    
    SELECT * from left_tb l LEFT JOIN right_tb r ON l.id=r.age;
    +----+----------+------+------+------+
    | id | name     | id   | name | age  |
    +----+----------+------+------+------+
    |  1 | zhangsan | NULL | NULL | NULL |
    |  2 | lisi     | NULL | NULL | NULL |
    |  3 | wangwu   | NULL | NULL | NULL |
    +----+----------+------+------+------+
    
    SELECT * from left_tb l LEFT JOIN right_tb r ON 1=1;
    +----+----------+------+------+------+
    | id | name     | id   | name | age  |
    +----+----------+------+------+------+
    |  1 | zhangsan |    1 | aaa  |   11 |
    |  2 | lisi     |    1 | aaa  |   11 |
    |  3 | wangwu   |    1 | aaa  |   11 |
    |  1 | zhangsan |    2 | bbb  |   12 |
    |  2 | lisi     |    2 | bbb  |   12 |
    |  3 | wangwu   |    2 | bbb  |   12 |
    |  1 | zhangsan |    3 | ccc  |   13 |
    |  2 | lisi     |    3 | ccc  |   13 |
    |  3 | wangwu   |    3 | ccc  |   13 |
    |  1 | zhangsan |    4 | ddd  |   14 |
    |  2 | lisi     |    4 | ddd  |   14 |
    |  3 | wangwu   |    4 | ddd  |   14 |
    +----+----------+------+------+------+
    

     关于去重:

    SELECT * from right_tb;
    +----+------+------+
    | id | name | age  |
    +----+------+------+
    |  1 | aaa  |   11 |
    |  2 | bbb  |   12 |
    |  3 | ccc  |   13 |
    |  4 | ddd  |   14 |
    |  5 | ccc  |   13 |
    |  6 | ddd  |   14 |
    |  7 | ccc  |   13 |
    |  8 | ddd  |   14 |
    |  9 | ddd  |   14 |
    | 10 | ddd  |   14 |
    | 11 | ccc  |   13 |
    | 12 | ddd  |   15 |
    +----+------+------+
    

     要求把name和age相同的记录当成重复记录

    SELECT min(r.id),r.name,r.age from right_tb r GROUP BY r.age,r.name;
    +-----------+------+------+
    | min(r.id) | name | age  |
    +-----------+------+------+
    |         1 | aaa  |   11 |
    |         2 | bbb  |   12 |
    |         3 | ccc  |   13 |
    |         4 | ddd  |   14 |
    |        12 | ddd  |   15 |
    +-----------+------+------+
    

    删除:

    delete  o from tb_order o join member m on  o.user_id = m.id and  m.phone_number='1xx27237628';
    delete  f from tb_order_refund f join tb_order o on f.order_id =o.id join member m on  o.user_id = m.id and  m.phone_number='1xx27237628';
    delete  r from tb_order_return r join tb_order o on r.order_id =o.id join member m on  o.user_id = m.id and  m.phone_number='1xx27237628';
    
  • 相关阅读:
    【codevs1690】开关灯 (线段树 区间修改+区间求和 (标记))
    【codevs1191】数轴染色 (线段树 区间修改+固定区间查询)
    【机器学习】李航 统计学习方法 知识点总结
    【机器学习】生成模型 判别模型
    【机器学习】贝叶斯定理、精准推断、最大似然估计、连续特征参数估计、EM算法
    python queue 讲解
    【tensorflow】 CNN卷积神经网络原理讲解+图片识别应用(附源码)
    URL解析过程
    Python 可迭代对象迭代器生成器的区别
    【Linux】 修改主机名
  • 原文地址:https://www.cnblogs.com/lanqie/p/7636949.html
Copyright © 2020-2023  润新知