• 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';
    
  • 相关阅读:
    使用 DataAdapter 执行批量更新 [摘自MSDN]
    深入浅出net泛型编程
    模版页中引用文件路径的问题
    查询SQLSERVER某个表所占用空间大小的SQL语句
    如何获取SQL Server数据库里表的占用容量大小的存储过程
    确定计算机是否可以运行 Windows Vista? 操作系统
    SQL语句 [转]
    SQLServer中如何将一个字段的多个记录值合在一行显示
    ASP.net在页面所有内容生成后、输出内容前对页面内容进行操作
    oracle 删除用于及其表空间
  • 原文地址:https://www.cnblogs.com/lanqie/p/7636949.html
Copyright © 2020-2023  润新知