精华内容
下载资源
问答
  • 文章目录1、多表联合查询1.1、链表1.1.1、交叉链接1.1.2、内链接1.1.3、左链接1.1.4、右链接1.1.5、全外链接2、符合条件连接查询3、子查询3.1、带IN关键字的子查询3.2、带EXISTS关键字的子查询4、pymysql模块4.1、...

    1、多表联合查询

    1.1、链表

    把多张物理表合并成一张虚拟表,再进行后续查询
    
    # dep表
    create table dep(
        id int,
        name varchar(20) 
    );
    
    # 插入数据
    insert into dep values
    (200,'技术'),
    (201,'人力资源'),
    (202,'销售'),
    (203,'运营');
    
    # emp表
    create table emp(
        id int primary key auto_increment,
        name varchar(20),
        sex enum('male','female') not null default 'male',
        age int,
        dep_id int
    );
    
    # 插入数据
    insert into emp(name,sex,age,dep_id) values
    ('allen','male',18,200),
    ('lucy','female',48,201),
    ('lily','male',38,201),
    ('jack','female',28,202),
    ('tom','male',18,200),
    ('andy','female',18,204);
    
    # 查看表结构
    mysql> desc emp;
    +--------+-----------------------+------+-----+---------+----------------+
    | Field  | Type                  | Null | Key | Default | Extra          |
    +--------+-----------------------+------+-----+---------+----------------+
    | id     | int                   | NO   | PRI | NULL    | auto_increment |
    | name   | varchar(20)           | YES  |     | NULL    |                |
    | sex    | enum('male','female') | NO   |     | male    |                |
    | age    | int                   | YES  |     | NULL    |                |
    | dep_id | int                   | YES  |     | NULL    |                |
    +--------+-----------------------+------+-----+---------+----------------+
    5 rows in set (0.00 sec)
    
    mysql> desc dep;
    +-------+-------------+------+-----+---------+-------+
    | Field | Type        | Null | Key | Default | Extra |
    +-------+-------------+------+-----+---------+-------+
    | id    | int         | YES  |     | NULL    |       |
    | name  | varchar(20) | YES  |     | NULL    |       |
    +-------+-------------+------+-----+---------+-------+
    2 rows in set (0.00 sec)
    
    # 查看数据
    mysql> select * from emp;
    +----+-------+--------+------+--------+
    | id | name  | sex    | age  | dep_id |
    +----+-------+--------+------+--------+
    |  1 | allen | male   |   18 |    200 |
    |  2 | lucy  | female |   48 |    201 |
    |  3 | lily  | male   |   38 |    201 |
    |  4 | jack  | female |   28 |    202 |
    |  5 | tom   | male   |   18 |    200 |
    |  6 | andy  | female |   18 |    204 |
    +----+-------+--------+------+--------+
    6 rows in set (0.00 sec)
    
    mysql> select * from dep;
    +------+----------+
    | id   | name     |
    +------+----------+
    |  200 | 技术     |
    |  201 | 人力资源 |
    |  202 | 销售     |
    |  203 | 运营     |
    +------+----------+
    4 rows in set (0.00 sec)
    
    1.1.1、交叉链接
    不适用任何匹配条件, 生成笛卡尔积
    
    mysql> select * from emp,dep;
    +----+-------+--------+------+--------+------+----------+
    | id | name  | sex    | age  | dep_id | id   | name     |
    +----+-------+--------+------+--------+------+----------+
    |  1 | allen | male   |   18 |    200 |  200 | 技术     |
    |  1 | allen | male   |   18 |    200 |  201 | 人力资源 |
    |  1 | allen | male   |   18 |    200 |  202 | 销售     |
    |  1 | allen | male   |   18 |    200 |  203 | 运营     |
    |  2 | lucy  | female |   48 |    201 |  200 | 技术     |
    |  2 | lucy  | female |   48 |    201 |  201 | 人力资源 |
    |  2 | lucy  | female |   48 |    201 |  202 | 销售     |
    |  2 | lucy  | female |   48 |    201 |  203 | 运营     |
    |  3 | lily  | male   |   38 |    201 |  200 | 技术     |
    |  3 | lily  | male   |   38 |    201 |  201 | 人力资源 |
    |  3 | lily  | male   |   38 |    201 |  202 | 销售     |
    |  3 | lily  | male   |   38 |    201 |  203 | 运营     |
    |  4 | jack  | female |   28 |    202 |  200 | 技术     |
    |  4 | jack  | female |   28 |    202 |  201 | 人力资源 |
    |  4 | jack  | female |   28 |    202 |  202 | 销售     |
    |  4 | jack  | female |   28 |    202 |  203 | 运营     |
    |  5 | tom   | male   |   18 |    200 |  200 | 技术     |
    |  5 | tom   | male   |   18 |    200 |  201 | 人力资源 |
    |  5 | tom   | male   |   18 |    200 |  202 | 销售     |
    |  5 | tom   | male   |   18 |    200 |  203 | 运营     |
    |  6 | andy  | female |   18 |    204 |  200 | 技术     |
    |  6 | andy  | female |   18 |    204 |  201 | 人力资源 |
    |  6 | andy  | female |   18 |    204 |  202 | 销售     |
    |  6 | andy  | female |   18 |    204 |  203 | 运营     |
    +----+-------+--------+------+--------+------+----------+
    24 rows in set (0.00 sec)
    
    1.1.2、内链接
    找两张表共有的部分,相当于利用条件从笛卡尔积结果中筛选出了正确的结果,保留两张表有对应关系的记录
    
    mysql> select * from emp,dep where emp.dep_id=dep.id;
    +----+-------+--------+------+--------+------+----------+
    | id | name  | sex    | age  | dep_id | id   | name     |
    +----+-------+--------+------+--------+------+----------+
    |  1 | allen | male   |   18 |    200 |  200 | 技术     |
    |  2 | lucy  | female |   48 |    201 |  201 | 人力资源 |
    |  3 | lily  | male   |   38 |    201 |  201 | 人力资源 |
    |  4 | jack  | female |   28 |    202 |  202 | 销售     |
    |  5 | tom   | male   |   18 |    200 |  200 | 技术     |
    +----+-------+--------+------+--------+------+----------+
    5 rows in set (0.00 sec)
    
    # 等同于如下操作
    mysql> select * from emp inner join dep on emp.dep_id=dep.id;
    +----+-------+--------+------+--------+------+----------+
    | id | name  | sex    | age  | dep_id | id   | name     |
    +----+-------+--------+------+--------+------+----------+
    |  1 | allen | male   |   18 |    200 |  200 | 技术     |
    |  2 | lucy  | female |   48 |    201 |  201 | 人力资源 |
    |  3 | lily  | male   |   38 |    201 |  201 | 人力资源 |
    |  4 | jack  | female |   28 |    202 |  202 | 销售     |
    |  5 | tom   | male   |   18 |    200 |  200 | 技术     |
    +----+-------+--------+------+--------+------+----------+
    5 rows in set (0.00 sec)
    
    1.1.3、左链接
    在内连接的基础上增加左边有右边没有的结果
    
    mysql> select * from emp left join dep on emp.dep_id=dep.id;
    +----+-------+--------+------+--------+------+----------+
    | id | name  | sex    | age  | dep_id | id   | name     |
    +----+-------+--------+------+--------+------+----------+
    |  1 | allen | male   |   18 |    200 |  200 | 技术     |
    |  5 | tom   | male   |   18 |    200 |  200 | 技术     |
    |  2 | lucy  | female |   48 |    201 |  201 | 人力资源 |
    |  3 | lily  | male   |   38 |    201 |  201 | 人力资源 |
    |  4 | jack  | female |   28 |    202 |  202 | 销售     |
    |  6 | andy  | female |   18 |    204 | NULL | NULL     |
    +----+-------+--------+------+--------+------+----------+
    6 rows in set (0.00 sec)
    
    1.1.4、右链接
    在内连接的基础上增加右边有左边没有的结果
    
    mysql> select * from emp right join dep on emp.dep_id=dep.id;
    +------+-------+--------+------+--------+------+----------+
    | id   | name  | sex    | age  | dep_id | id   | name     |
    +------+-------+--------+------+--------+------+----------+
    |    1 | allen | male   |   18 |    200 |  200 | 技术     |
    |    2 | lucy  | female |   48 |    201 |  201 | 人力资源 |
    |    3 | lily  | male   |   38 |    201 |  201 | 人力资源 |
    |    4 | jack  | female |   28 |    202 |  202 | 销售     |
    |    5 | tom   | male   |   18 |    200 |  200 | 技术     |
    | NULL | NULL  | NULL   | NULL |   NULL |  203 | 运营     |
    +------+-------+--------+------+--------+------+----------+
    6 rows in set (0.00 sec)
    
    1.1.5、全外链接
    在内连接的基础上增加左边有右边没有的和右边有左边没有的结果
    注意: mysql不支持全外连接 full JOIN
    强调: mysql可以使用此种方式间接实现全外连接
    
    mysql> select * from emp left join dep on emp.dep_id=dep.id
        -> union
        -> select * from emp right join dep on emp.dep_id=dep.id;
    +------+-------+--------+------+--------+------+----------+
    | id   | name  | sex    | age  | dep_id | id   | name     |
    +------+-------+--------+------+--------+------+----------+
    |    1 | allen | male   |   18 |    200 |  200 | 技术     |
    |    5 | tom   | male   |   18 |    200 |  200 | 技术     |
    |    2 | lucy  | female |   48 |    201 |  201 | 人力资源 |
    |    3 | lily  | male   |   38 |    201 |  201 | 人力资源 |
    |    4 | jack  | female |   28 |    202 |  202 | 销售     |
    |    6 | andy  | female |   18 |    204 | NULL | NULL     |
    | NULL | NULL  | NULL   | NULL |   NULL |  203 | 运营     |
    +------+-------+--------+------+--------+------+----------+
    7 rows in set (0.00 sec)
    
    union与union all的区别: union会去掉相同的纪录
    mysql> select * from emp left join dep on emp.dep_id=dep.id
        -> union all
        -> select * from emp right join dep on emp.dep_id=dep.id;
    +------+-------+--------+------+--------+------+----------+
    | id   | name  | sex    | age  | dep_id | id   | name     |
    +------+-------+--------+------+--------+------+----------+
    |    1 | allen | male   |   18 |    200 |  200 | 技术     |
    |    5 | tom   | male   |   18 |    200 |  200 | 技术     |
    |    2 | lucy  | female |   48 |    201 |  201 | 人力资源 |
    |    3 | lily  | male   |   38 |    201 |  201 | 人力资源 |
    |    4 | jack  | female |   28 |    202 |  202 | 销售     |
    |    6 | andy  | female |   18 |    204 | NULL | NULL     |
    |    1 | allen | male   |   18 |    200 |  200 | 技术     |
    |    2 | lucy  | female |   48 |    201 |  201 | 人力资源 |
    |    3 | lily  | male   |   38 |    201 |  201 | 人力资源 |
    |    4 | jack  | female |   28 |    202 |  202 | 销售     |
    |    5 | tom   | male   |   18 |    200 |  200 | 技术     |
    | NULL | NULL  | NULL   | NULL |   NULL |  203 | 运营     |
    +------+-------+--------+------+--------+------+----------+
    12 rows in set (0.00 sec)
    

    2、符合条件连接查询

    以内连接的方式查询emp和dep表,并且emp表中的age字段值必须大于25,即找出年龄大于25岁的员工以及员工所在的部门
    mysql> select emp.name,dep.name from emp inner join dep on emp.dep_id = dep.id where age > 25;
    +------+----------+
    | name | name     |
    +------+----------+
    | lucy | 人力资源 |
    | lily | 人力资源 |
    | jack | 销售     |
    +------+----------+
    3 rows in set (0.00 sec)
    
    以内连接的方式查询emp和dep表,并且以age字段的升序方式显示
    mysql> select * from emp inner join dep on emp.dep_id=dep.id order by age asc;
    +----+-------+--------+------+--------+------+----------+
    | id | name  | sex    | age  | dep_id | id   | name     |
    +----+-------+--------+------+--------+------+----------+
    |  1 | allen | male   |   18 |    200 |  200 | 技术     |
    |  5 | tom   | male   |   18 |    200 |  200 | 技术     |
    |  4 | jack  | female |   28 |    202 |  202 | 销售     |
    |  3 | lily  | male   |   38 |    201 |  201 | 人力资源 |
    |  2 | lucy  | female |   48 |    201 |  201 | 人力资源 |
    +----+-------+--------+------+--------+------+----------+
    5 rows in set (0.00 sec)
    

    3、子查询

    1: 子查询是将一个查询语句嵌套在另一个查询语句中。
    2: 内层查询语句的查询结果,可以为外层查询语句提供查询条件
    3: 子查询中可以包含: IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
    4: 还可以包含比较运算符: =!=><

    3.1、带in关键字的子查询

    查询平均年龄在25岁以上的部门名
    mysql> select * from dep where id in(select dep_id from emp group by dep_id having avg(age) > 25);
    +------+----------+
    | id   | name     |
    +------+----------+
    |  201 | 人力资源 |
    |  202 | 销售     |
    +------+----------+
    2 rows in set (0.00 sec)
    
    查看技术部员工姓名
    mysql> select * from emp where dep_id in(select id from dep where name="技术");
    +----+-------+------+------+--------+
    | id | name  | sex  | age  | dep_id |
    +----+-------+------+------+--------+
    |  1 | allen | male |   18 |    200 |
    |  5 | tom   | male |   18 |    200 |
    +----+-------+------+------+--------+
    2 rows in set (0.05 sec)
    
    查看不足1人的部门名(子查询得到的是有人的部门id)
    mysql> select * from dep where id not in (select distinct dep_id from emp);
    +------+------+
    | id   | name |
    +------+------+
    |  203 | 运营 |
    +------+------+
    1 row in set (0.00 sec)
    

    3.2、not in不支持null

    mysql> insert into emp values(8,'zhangsan','female',19,null);
    Query OK, 1 row affected (0.23 sec)
    
    mysql> select * from emp;
    +----+----------+--------+------+--------+
    | id | name     | sex    | age  | dep_id |
    +----+----------+--------+------+--------+
    |  1 | allen    | male   |   18 |    200 |
    |  2 | lucy     | female |   48 |    201 |
    |  3 | lily     | male   |   38 |    201 |
    |  4 | jack     | female |   28 |    202 |
    |  5 | tom      | male   |   18 |    200 |
    |  6 | andy     | female |   18 |    204 |
    |  7 | zhangsan | female |   19 |   NULL |
    +----+----------+--------+------+--------+
    7 rows in set (0.06 sec)
    
    查询出没有员工的部门
    mysql> select * from dep where id not in (select distinct dep_id from emp);  # 该方法查不到结果,因为not in 不支持null
    Empty set (0.00 sec)
    
    解决:
    mysql> select * from dep where id not in (select distinct dep_id from emp where dep_id is not null);
    +------+------+
    | id   | name |
    +------+------+
    |  203 | 运营 |
    +------+------+
    1 row in set (0.00 sec)
    

    3.3、带any关键字的子查询

    # 在sql中any和some是同义词,some的用法和功能和any一模一样
    
    # any和in运算符不同之处1
    any必须和其他的比较运算符共同使用,而且any必须将比较运算符放在any关键字之前,所比较的值需要匹配子查询中的任意一个值,这也就是any在英文中所表示的意义
    
    # 查询部门是技术和人力资源的人员信息
    mysql> select * from emp where dep_id in (select id from dep where name in ("技术","人力资源"));
    +----+-------+--------+------+--------+
    | id | name  | sex    | age  | dep_id |
    +----+-------+--------+------+--------+
    |  1 | allen | male   |   18 |    200 |
    |  2 | lucy  | female |   48 |    201 |
    |  3 | lily  | male   |   38 |    201 |
    |  5 | tom   | male   |   18 |    200 |
    +----+-------+--------+------+--------+
    4 rows in set (0.02 sec)
    
    mysql> select * from emp where dep_id = any (select id from dep where name in ("技术","人力资源"));
    +----+-------+--------+------+--------+
    | id | name  | sex    | age  | dep_id |
    +----+-------+--------+------+--------+
    |  1 | allen | male   |   18 |    200 |
    |  2 | lucy  | female |   48 |    201 |
    |  3 | lily  | male   |   38 |    201 |
    |  5 | tom   | male   |   18 |    200 |
    +----+-------+--------+------+--------+
    4 rows in set (0.00 sec)
    
    # 查询部门不是技术和人力资源的人员信息
    mysql> select * from emp where dep_id not in (select id from dep where name in ("技术","人力资源"));
    +----+------+--------+------+--------+
    | id | name | sex    | age  | dep_id |
    +----+------+--------+------+--------+
    |  4 | jack | female |   28 |    202 |
    |  6 | andy | female |   18 |    204 |
    +----+------+--------+------+--------+
    2 rows in set (0.00 sec)
    
    mysql> select * from emp where ! (dep_id = any(select id from dep where name in ("技术","人力资源")));
    +----+------+--------+------+--------+
    | id | name | sex    | age  | dep_id |
    +----+------+--------+------+--------+
    |  4 | jack | female |   28 |    202 |
    |  6 | andy | female |   18 |    204 |
    +----+------+--------+------+--------+
    2 rows in set, 1 warning (0.06 sec)
    
    结论:
        "=any"等价于in运算符,而"<>any"则等价于not in运算符
    
    # any和in运算符不同之处2
    any运算符不能与固定的集合相匹配
    mysql> select * from emp where age<any(18,28,38);  # 该用法是错误的
    
    总结:
        any后也跟子查询语句,与in不一样的地方在哪里
    	in (子查询语句)
    	in (1,2,3)any只能跟子查询语句
    	any必须跟比较运算符配合使用
    

    3.4、带all关键字的子查询

    # all同any类似,只不过all表示的是所有,any表示任意
    
    查询出那些年龄比所有部门的平均年龄都高的员工
    mysql> select * from emp where age > all (select avg(age) from emp where dep_id is not null group by dep_id);
    +----+------+--------+------+--------+
    | id | name | sex    | age  | dep_id |
    +----+------+--------+------+--------+
    |  2 | lucy | female |   48 |    201 |
    +----+------+--------+------+--------+
    1 row in set (0.00 sec)
    
    查询出那些年龄比所有部门的平均年龄都低的员工
    mysql> select * from emp where age < all (select avg(age) from emp where dep_id is not null group by dep_id);
    Empty set (0.05 sec)
    
    查询出那些年龄比任意一个部门的平均年龄低的员工
    mysql> select * from emp where age < any (select avg(age) from emp group by dep_id);
    +----+-------+--------+------+--------+
    | id | name  | sex    | age  | dep_id |
    +----+-------+--------+------+--------+
    |  1 | allen | male   |   18 |    200 |
    |  3 | lily  | male   |   38 |    201 |
    |  4 | jack  | female |   28 |    202 |
    |  5 | tom   | male   |   18 |    200 |
    |  6 | andy  | female |   18 |    204 |
    +----+-------+--------+------+--------+
    5 rows in set (0.00 sec)
    
    查询出那些年龄比任意一个部门的平均年龄高的员工
    mysql> select * from emp where age > any (select avg(age) from emp group by dep_id);
    +----+------+--------+------+--------+
    | id | name | sex    | age  | dep_id |
    +----+------+--------+------+--------+
    |  2 | lucy | female |   48 |    201 |
    |  3 | lily | male   |   38 |    201 |
    |  4 | jack | female |   28 |    202 |
    +----+------+--------+------+--------+
    3 rows in set (0.00 sec)
    

    3.5、带exists关键字的子查询

    exists关字键字表示存在,在使用exists关键字时,内层查询语句不返回查询的记录
    而是返回一个真假值,TrueFalse
    当返回True时,外层查询语句将进行查询;当返回值为False,外层查询语句不进行查询
    
    mysql> select * from dep where exists (select * from emp where id>3);
    +------+----------+
    | id   | name     |
    +------+----------+
    |  200 | 技术     |
    |  201 | 人力资源 |
    |  202 | 销售     |
    |  203 | 运营     |
    +------+----------+
    4 rows in set (0.00 sec): exists后面执行的结果为True就返回前面的sql语句,not exists意思取反
        
    mysql> select * from dep where not exists (select * from emp where id>3);
    Empty set (0.00 sec)
    

    3.6、in与exists

    in和exists在查询效率上比较时,in查询的效率快于exists的查询效率
    
    # exists
    exists后面一般都是子查询,后面的子查询被称做相关子查询(即与主语句相关),当子查询返回行数时,exists条件返回true,否则返回false,exists是不返回列表的值的,exists只在乎括号里的数据能不能查找出来,是否存在这样的记录
    
    查询出那些班级里有学生的班级
    select * from class where exists (select * from stu where stu.cid=class.id)
    
    # exists的执行原理为:
    1、依次执行外部查询: 即select * from class 
    2、然后为外部查询返回的每一行分别执行一次子查询:(select * from stu where stu.cid=class.cid)
    3、子查询如果返回行,则exists条件成立,条件成立则输出外部查询取出的那条记录
    
    # in
    in后跟的都是子查询,in()后面的子查询 是返回结果集的
    
    查询和所有女生年龄相同的男生
    select * from stu where sex='男' and age in(select age from stu where sex='女')
    
    # in的执行原理为:
    in()的执行次序和exists()不一样,in()的子查询会先产生结果集,然后主查询再去结果集里去找符合要求的字段列表去.符合要求的输出,反之则不输出
    

    3.7、not in与not exists

    not exists查询的效率远远高与not in查询的效率
    
    not in()子查询的执行顺序是:
    为了证明not in成立,即找不到,需要一条一条地查询表,符合要求才返回子查询的结果集,不符合的就继续查询下一条记录,直到把表中的记录查询完,只能查询全部记录才能证明,并没有用到索引
    
    not exists:
    如果主查询表中记录少,子查询表中记录多,并有索引。
    例如:查询那些班级中没有学生的班级
    select * from class where not exists(select * from student where student.cid = class.cid)
    
    not exists的执行顺序是:
    在表中查询,是根据索引查询的,如果存在就返回true,如果不存在就返回false,不会每条记录都去查询
    

    3.8、应用

    3.8.1、准备数据
    # 准备数据表
    create database db13;
    
    use db13
    
    create table student(
        id int primary key auto_increment,
        name varchar(16)
    );
    
    create table course(
        id int primary key auto_increment,
        name varchar(16),
        comment varchar(20)
    );
    
    create table student2course(
        id int primary key auto_increment,
        sid int,
        cid int,
        foreign key(sid) references student(id),
        foreign key(cid) references course(id)
    );
    
    # 准备数据
    insert into student(name) values("lily"),("jack"),("tank"),("tom");
    
    insert into course(name,comment) values
    ("数据库","数据仓库"),
    ("数学","高等数学"),
    ("英语","专业八级");
    
    insert into student2course(sid,cid) values(1,1),(1,2),(1,3),(2,1),(2,2),(3,2);
    
    3.8.2、应用练习
    1、查询选修了所有课程的学生id、name
    mysql> select * from student s where not exists
        ->     (select * from course c where not exists
        ->         (select * from student2course sc where sc.sid=s.id and sc.cid=c.id));
    +----+------+
    | id | name |
    +----+------+
    |  1 | lily |
    +----+------+
    1 row in set (0.00 sec)
    
    mysql> select s.id,s.name from student as s
        -> inner join student2course as sc
        -> on s.id=sc.sid
        -> group by s.name
        -> having count(sc.id) = (select count(id) from course);
    +----+------+
    | id | name |
    +----+------+
    |  1 | lily |
    +----+------+
    1 row in set (0.00 sec)
    
    2、查询没有选择所有课程的学生,即没有全选的学生
    mysql> select * from student s where exists
        ->     (select * from course c where not exists
        ->         (select * from student2course sc where sc.sid=s.id and sc.cid=c.id));
    +----+------+
    | id | name |
    +----+------+
    |  2 | jack |
    |  3 | tank |
    |  4 | tom  |
    +----+------+
    3 rows in set (0.00 sec)
    
    3、查询一门课也没有选的学生
    mysql> select * from student s where not exists
        ->     (select * from course c where exists
        ->         (select * from student2course sc where sc.sid=s.id and sc.cid=c.id));
    +----+------+
    | id | name |
    +----+------+
    |  4 | tom  |
    +----+------+
    1 row in set (0.00 sec)
    
    4、查询至少选修了一门课程的学生
    mysql> select * from student s where exists
        ->     (select * from course c where exists
        ->         (select * from student2course sc where sc.sid=s.id and sc.cid=c.id));
    +----+------+
    | id | name |
    +----+------+
    |  1 | lily |
    |  2 | jack |
    |  3 | tank |
    +----+------+
    3 rows in set (0.00 sec)
    

    4、pymysql模块

    4.1、安装的两种方法

    4.1.1、第一种
    # 安装
    pip3 install pymysql
    
    4.1.2、第二种

    在这里插入图片描述
    在这里插入图片描述

    4.2、链接,执行sql,关闭(游标)

    在这里插入图片描述

    import pymysql
    
    user = input('请输入用户名: ').strip()
    pwd = input('请输入密码: ').strip()
    
    # 先链接,拿到游标
    conn = pymysql.connect(
        host='127.0.0.1',
        user='root',
        password='root',
        port=3307,
        db='db01',
        charset='utf8'
    )
    
    cursor = conn.cursor()  # 拿到游标,即mysql >
    
    # 执行sql
    sql = "select * from user where name='%s' and pwd='%s';" % (user, pwd)
    
    rows = cursor.execute(sql)  # 拿到受影响的行数
    cursor.close()
    conn.close()
    
    if rows:
        print('登录成功')
    else:
        print('登录失败')
    

    4.3、execute()之sql注入

    注意: 符号--会注释掉它之后的sql,正确的语法: --后至少有一个任意字符
    
    根本原理: 就根据程序的字符串拼接name='%s',我们输入一个xxx' -- abc,用我们输入的xxx加'在程序中拼接成一个判断条件name='xxx' -- abc'
    
    最后那一个空格,在一条sql语句中如果遇到select *from t1 where id > 1 -- and name='zhangsan';--之后的条件被注释掉了
    
    1、sql注入之: 用户存在,绕过密码
    zhangsan' -- 任意字符
    
    2、sql注入之: 用户不存在,绕过用户与密码
    xxx' or 1=1 -- 任意字符
    

    在这里插入图片描述
    在这里插入图片描述
    解决sql注入

    # 原来是我们对sql进行字符串拼接
    # sql = "select * from user where name='%s' and pwd='%s';" % (user, pwd)
    # rows = cursor.execute(sql)
    
    # 改写为(execute帮我们做字符串拼接,我们无需且一定不能再为%s加引号了)
    sql = "select * from user where name=%s and pwd=%s"  # 注意%s需要去掉引号,因为pymysql会自动为我们加上
    rows = cursor.execute(sql, (user, pwd))
    # pymysql模块自动帮我们解决sql注入的问题,只要我们按照pymysql的规矩来。
    cursor.close()
    conn.close()
    

    4.4、增、删、改操作:conn.commit()

    4.4.1、增
    import pymysql
    # 先链接,拿到游标
    conn = pymysql.connect(user='root', password='root', host='127.0.0.1',
                           port=3307, db='db01', charset='utf8')
    cursor = conn.cursor()  # 拿到游标,即mysql >
    # 执行sql   增:
    rows = cursor.execute("insert into user values(%s,%s,%s)", args=(3, 'wangwu', '123'))
    print(rows)  # 返回受影响的记录条数
    
    # 批量插入
    rows = cursor.executemany("insert into user values(%s,%s,%s)",
                       args=((4, 'jack', '123'), (5, 'tom', '123'), (6, 'lily', '123')))
    print(rows)  # 返回受影响的记录条数
    
    conn.commit()  # 提交到数据库
    cursor.close()
    conn.close()
    
    4.4.2、删
    import pymysql
    
    conn = pymysql.connect(user='root', password='root', host='127.0.0.1',
                           port=3307, db='db01', charset='utf8')
    cursor = conn.cursor()
    # 执行sql   删:
    rows = cursor.execute("delete from user where id>%s", args=(4,))  # 删除数据
    print(rows)  # 返回受影响的记录条数
    
    conn.commit()  # 提交到数据库
    cursor.close()
    conn.close()
    
    4.4.3、改
    import pymysql
    
    conn = pymysql.connect(user='root', password='root', host='127.0.0.1',
                           port=3307, db='db01', charset='utf8')
    cursor = conn.cursor()
    # 执行sql   改:
    rows = cursor.execute("update user set name=%s where id=%s", args=('tom',1))  # 修改数据
    print(rows)  # 返回受影响的记录条数
    
    conn.commit()  # 提交到数据库
    cursor.close()
    conn.close()
    

    4.5、查询操作

    fetchone: 查询一条
    fetchmany: 查询多条
    fetchall: 查询所有
        
    # 查询
    import pymysql
    
    conn = pymysql.connect(user='root', password='root', host='127.0.0.1',
                           port=3307, db='db01', charset='utf8')
    cursor = conn.cursor()
    # 如果想让结果变成字典形式,配置游标:
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    
    cursor.execute("select * from user")
    
    # 查单条 fetchone
    print(cursor.fetchone())  # (1, 'tom', '123')
    print(cursor.fetchone())  # (2, 'lisi', '123')
    print(cursor.fetchone())  # ((3, 'wangwu', '123'))
    
    # 查多条 fetchmany
    print(cursor.fetchmany(3))  # ((1, 'tom', '123'), (2, 'lisi', '123'), (3, 'wangwu', '123'))
    print(cursor.fetchone())  # (4, 'jack', '123')
    
    # 查所有 fetchall
    print(cursor.fetchall())  # ((1, 'tom', '123'), (2, 'lisi', '123'), (3, 'wangwu', '123'), (4, 'jack', '123'))
    # 等同于如下:
    for line in cursor:
        print(line)
    
    print(cursor.fetchall())  # ()   结果取完再查就是空
    
    # 光标的移动
    # 1、绝对路径: 从文件的开头位置算起
    print(cursor.fetchall())  # ((1, 'tom', '123'), (2, 'lisi', '123'), (3, 'wangwu', '123'), (4, 'jack', '123'))
    cursor.scroll(1, mode='absolute')
    print(cursor.fetchone())  # (2, 'lisi', '123')
    cursor.scroll(3, mode='absolute')
    print(cursor.fetchone())  # (4, 'jack', '123')
    
    # #2、相对路径: 根据当前位置算起
    print(cursor.fetchone())  # (1, 'tom', '123')
    print(cursor.fetchone())  # (2, 'lisi', '123')
    cursor.scroll(1, mode='relative')  # 相对于上面的两条向后移一条
    print(cursor.fetchone())  # (4, 'jack', '123')
    
    cursor.close()
    conn.close()
    

    4.6、获取插入的最后一条数据的自增id

    import pymysql
    
    conn = pymysql.connect(user='root', password='root', host='127.0.0.1',
                           port=3307, db='db13', charset='utf8')
    cursor = conn.cursor()
    
    cursor.execute("insert into user (name,password) values(%s,%s)", args=('hhh', '123'))
    
    conn.commit()
    
    print(cursor.lastrowid)  # 查看表中最后一行的iD
    
    cursor.close()
    conn.close()
    
    展开全文
  • mysql两张表联合查询

    2020-04-10 20:24:05
    首先建立数据库mydemo,并切换到新建数据库 ...接着创建name和info两张表 create table name ( id int, name varchar(20), age varchar(10) ); create table info ( name varchar(20), a...

    首先建立数据库mydemo,并切换到新建数据库

    create database mydemo;
    use mydemo

    接着创建name和info两张表

    create table name
    (
    	id int,
        name varchar(20),
        age varchar(10)
    );
    
    create table info
    (
    	name varchar(20),
        address varchar(20)
    );
    

    向两张表中分别插入信息

    insert into name (id,name,age) values (1,"张三","12");
    
    insert into info (name,address) values ("张三","秦皇岛市");

    此时两张表内容如下

         

    此时通过id查询张三的地址

    select address from info,name where id=1;

    输出结果如下

    展开全文
  • Android Sqlite数据库表联合查询

    千次阅读 2018-06-11 00:41:36
    Android Sqlite数据库表联合查询 工作上用数据库存储文件还是很便利的,所以有时候发现一张表存储数据感觉数据结构不是很清晰的时候,就需要新加第二张表或者多张表来进行联合查询对象信,一般是用键将彼此联系...

    Android Sqlite数据库多表联合查询示例

    工作上用数据库存储文件还是很便利的,所以有时候发现一张表存储数据感觉数据结构不是很清晰的时候,就需要新加第二张表或者多张表来进行联合查询对象信,一般是用键将彼此联系起来,在表中,每个主键的值都是唯一的。这样做的目的是在不重复每个表中的所有数据的情况下,把表间的数据交叉捆绑在一起,下面,写一点简单的例子:
    先说一下两张表各自要展现的内容有哪些
    第一张表:

    CREATE TABLE [custoly](
        [CustNo] INTEGER PRIMARY KEY, 
        [Last_Name] CHAR(30), 
        [First_Name] CHAR(30), 
        [VIP_Status] CHAR(10), 
        [Address1] CHAR(40), 
        [Address2] CHAR(40), 
        [City] CHAR(20), 
        [State_Prov] CHAR(10), 
        [Post_Code] CHAR(10), 
        [Country] CHAR(10), 
        [Phone] CHAR(18), 
        [Fax] CHAR(18), 
        [EMail] CHAR(40), 
        [Remarks] CHAR);
    

    这里写图片描述

    第二张表:

    CREATE TABLE [customer](
        [CustNo] INTEGER PRIMARY KEY, 
        [Company] CHAR(30), 
        [Addr1] CHAR(30), 
        [Addr2] CHAR(30), 
        [City] CHAR(15), 
        [State] CHAR(20), 
        [Zip] CHAR(10), 
        [Country] CHAR(20), 
        [Phone] CHAR(15), 
        [FAX] CHAR(15), 
        [TaxRate] FLOAT, 
        [Contact] CHAR(20), 
        [LastInvoiceDate] DATETIME);
    

    这里写图片描述
    两张表的联系是有相同的CustNo,请留意,”CustNo” 列把上面的两个表联系了起来

    下面进行关联查询:

    SELECT customer.LastInvoiceDate 
    FROM custoly
    INNER JOIN customer
    ON custoly.CustNo = 86433 AND customer.CustNo = 86433 
    ORDER BY customer.TaxRate
    

    查询结果意图如下:
    这里写图片描述

    这里写图片描述

    展开全文
  • 先准备两张表 Student表 字段 解释 ID 学号 name 姓名 collegeID 校园ID college表 字段 解释 collegeId 学院ID(主键) collegeName 学院名 内连接 内连接就是表间的主键与外键相连,只...

    表连接的方式
    内连接
    外连接
    自连接
    我们接下来将对这三种连接进行详细的介绍。
    先准备两张表
    Student表

    字段 解释
    ID 学号
    name 姓名
    collegeID 校园ID

    在这里插入图片描述

    college表

    字段 解释
    collegeId 学院ID(主键)
    collegeName 学院名

    在这里插入图片描述
    内连接
    内连接就是表间的主键与外键相连,只取得键值一致的,可以获取双方表中的数据连接方式。语法如下:

    SELECT 列名1,列名2… FROM 表1 INNER JOIN 表2 ON 表1.外键=表2.主键 WhERE 条件语句;
    运行结果:

    mysql> SELECT student.name,college.collegeName FROM student INNER JOIN college ON student.collegeId = college.collegeId;
    +——+————-+
    | name | collegeName |
    +——+————-+
    | 张三 | 清华 |
    | 李四 | 北大 |
    | 王五 | 浙大 |
    +——+————-+
    3 rows in set (0.03 sec)
    

    这样,我们就成功将【student】表中的【name】和【college】表中的【collegeName】进行了重新结合,并检索出来。
    外连接
    与取得双方表中数据的内连接相比,外连接只能取得其中一方存在的数据,外连接又分为左连接和右连接两种情况。接下来,我们将介绍这两种连接方式。

    左外连接
    左连接是以左表为标准,只查询在左边表中存在的数据,当然需要两个表中的键值一致。语法如下:

    SELECT 列名1 FROM 表1 LEFT OUTER JOIN 表2 ON 表1.外键=表2.主键 WHERE条件语句;
    1
    运行结果:

    mysql> SELECT student.name,college.collegeName FROM student LEFT OUTER JOIN college ON student.collegeId = college.collegeId;
    +——+————-+
    | name | collegeName |
    +——+————-+
    | 张三 | 清华 |
    | 李四 | 北大 |
    | 王五 | 浙大 |
    |赵六  | NULL |
    +——+————-+
    4 rows in set (0.00 sec)
    

    我们可以看出,与内连接查询结果不同的是:【赵六】这个学生虽然没有学校ID但是也被查出来了,这就是我们所说的,他会以左连接中的左表的全部数据作为基准进行查询。

    右外连接
    同理,右连接将会以右边作为基准,进行检索。语法如下:

    SELECT 列名1 FROM 表1 RIGHT OUTER JOIN 表2 ON 表1.外键=表2.主键 WhERE 条件语句;
    运行结果:

    mysql> SELECT student.name,college.collegeName FROM student RIGHT OUTER JOIN college ON student.collegeId = college.collegeId;
    +——+————-+
    | name | collegeName |
    +——+————-+
    | 张三 | 清华 |
    | 李四 | 北大 |
    | 王五 | 浙大 |
    | NULL | 厦大 |
    +——+————-+
    4 rows in set (0.00 sec)
    

    我们可以看出,这里就是以右边的表【college】为基准进行了检索,因为【student】中并没有【厦大】的学生,所以检索出来的为【NULL】
    自连接
    自连接顾名思义就是自己跟自己连接,有人或许会问,这样的连接有意义吗?答案是肯定的。
    例如,我们将【student】的数据改为下图:
    在这里插入图片描述
    运行结果如图:

    mysql> SELECT * FROM student s ,student a where a.collegeId=s.collegeId AND a.name <> s.name ORDER BY a.collegeId;
    +———–+——+——-+———–+———–+——+——-+———–+
    | studentId | name | phone | collegeId | studentId | name | phone | collegeId |
    +———–+——+——-+———–+———–+——+——-+———–+
    | 4 | 赵六 | 136 | 11 | 1 | 张三 | 139 | 11 |
    | 1 | 张三 | 139 | 11 | 4 | 赵六 | 136 | 11 |
    +———–+——+——-+———–+———–+——+——-+———–+
    2 rows in set (0.00 sec)
    

    可以看出,我们就将【student】表中在同一个学校的学生查出来了。
    语句释义:

    【student s】和【student a】的含义就是分别给我们的【student】表取了两个不同的别名;
    【a.collegeId = s.collegeId AND a.name <> s.name 】的含义是找出【collegeId】相同,但是【name】不同的人.
    【ORDER BY a.collegeId;】将结果顺序输出;

    自连接的使用情况还是很多的,比如当我们找某个站点所经过的所有公交等,都可以采用自连接的方式进行检索;

    展开全文
  • 这段时间由于高强度的学习,致使到了走火入魔的阶段,当然这正好是一个果断阶段,...2、那么数据两张表的数据纵向怎么合并在一起呢? 记得使用union关键字 3、最头疼的左联,右联查询原理是什么? 记住一点就好:...
  • 我想用ReportViewer实现显示基于多张表查询的结果,但是同时在数据集中拖入两张表进行实验时候失败了。 后来我用的方法是在数据库中先建立相应的视图, 然后将视图拖入数据集中, 然后再新建rpt文件, 选择该...
  • Linux shell实现MySQL数据库简单查询&&MySQL多表联合查询 两张表: appdb.t_position: id, appId, appName, eventId, isdel appdb.t_record: id, uid, name 根据t_record中的name查找uid,uid对应t_position中的...
  • mysql中常用到的几种连接查询 你可以在 SELECT, UPDATE 和 DELETE 语句中使用 Mysql 的 JOIN 来联合多表查询。 JOIN 按照功能大致分为如下三类: INNER JOIN(内连接,或等值...测试表结构如下(两张表): SET .
  • 联合查询 基本概念 联合查询是可合并多个相似的选择查询的结果集。等同于将一个追加到另一个,从而实现将的查询组合在一起,使用为此为UNINO或UNION ALL 联合查询:将多个查询的结果合并到一起(纵向...
  • 数据库联合查询和连接查询

    千次阅读 2018-05-21 09:10:09
    数据库联合查询和连接查询1.JOIN和UNION区别join 是两张表做交连后里面条件相同的部分记录产生一个记录集,union是产生的两个记录集(字段要一样的)并在一起,成为一个新的记录集。JOIN用于按照ON条件联接两个表,...
  • union的作用是将【查询结果字段数相同的】进行强制合并 # 查询名字带东的省和名字带东的市,将结果合并为一张 select ProName as name from t_province where ProName like '%东%' union select CityName ...
  • MySQL数据库(十五)联合查询与连接查询 联合查询 基本概念 联合查询是可合并多个相似的选择查询的结果集。等同于将一个追加到另一个,从而实现将的查询组合在一起,使用为此为UNINO或...
  • 外键:通过外键关联两一个表的主键,两张表产生关系 非空约束:not null,字段值不能为空 自增长约束:increment,自增字段的数据类型一般是整数型的,通常配合主键来使用,自动增加。 唯一约束:nuique ,字段值不能...
  • 查询主要方式为连接查询和联合查询。连接查询:连接就是指个或2个以上的(数据源)“连接起来成为一个数据源”。表达式语句:from 1 [连接方式] join 2 [on 连接条件];连接的结果可以当作一个“”来...
  • 以上配置使用SQL语句去数据库查询结果是一对多的,但MyBatis查询结果却只有一条数据,这里就是字段名重名导致的问题,角色和权限都有一个id字段,只需要将查询语句稍微修改一下就OK了,采用别名的方式去查询,即...
  • mybatis多表联合查询

    2021-06-05 11:17:49
    当我们需要从数据库获取的数据不光是一张表的数据,而是两张或两张表以上整合或分别提取的字段时,例如拿员工和部门的关系来说,查一个部门内所有员工的信息,因为部门表和员工表是分开定义的,这时候就需要用到多...
  • 多表查询,又称表联合查询,即一条sql语句涉及到的表有多,数据通过特定的连接进行联合显示。 笛卡尔积 在数学中,个集合X和Y的笛卡尓积(Cartesian product),又称直积,表示为X × Y. 假设集合A={a, b},集合...
  • 前言 关系型数据库的使用有一套数学理论支撑,数据库表结构优化其实质就是优化范式。通常在关系型数据库中...因此第二范式允许将一次查询的所有属性都存在同一张中。 第三范式需要满足点:一,满足第二范...
  • 学习java的第十五天交叉连接内连接左外连接右外连接多对多关系关联查询示例:外键约束的作用和建立统计(聚合)函数子查询联合查询 交叉连接 直接将两张表中的数据进行笛卡尔集,即相乘,得到数据一般是有问题的,结果...
  • Mybats 多表联合查询之一对一操作

    千次阅读 2019-06-13 09:59:11
    首先呢,我们创建两张表,一个是users表,一个是account表,两张表组成了用户和账户之间的关系,一个用户可以有多个账户,一个账户只能属于一个用户,这就是我们的背景。首先实现一对一表的联合查询,一个用户对应...
  • 同事索要查询的数据共存在于两张表中,一张是街道表,一张是区划表。映射关系是一个区划对应N个街道。搜索效率要达到1秒千次。 数据库操作 SELECT area_id, area_name, parent_id, parent_name, area_leve
  • Mybats 多表联合查询之一对多操作

    千次阅读 2019-06-13 17:43:06
    首先呢,我们创建两张表,一个是users表,一个是account表,两张表组成了用户和账户之间的关系,一个用户可以有多个账户,一个账户只能属于一个用户,这就是我们的背景。首先实现一对一表的联合查询,一个用户对应多...
  • hibernate很多实现都是靠喜欢配关系,但是如果两张表,数据量都非常大的时候,并不合适配关系。  例如:student表和score表需要做联合查询。  1)sql: select s.id,s.name,sc.score from student as s,score as ...
  • 连接查询 在实际开发中,大部分的情况下都不是从单表中查询数据,一般都是多张表联合查询取出最终的结果。 信息存储到一张表中,数据会存在大量的重复,导致数据的冗余。...如果两张表进行连接查询,如
  • Iamten@TOC ...数据库情况(蓝色相连的两张表) mapping.xml &amp;lt;resultMap id=&quot;ClassResultMap&quot; type=&quot;com.five.police.model.Policeinfo&quot; &amp
  • ​ 多表查询,又称表联合查询,即一条sql语句涉及到的表有多,数据通过特定的连接进行联合显示。 笛卡尔积 ​ 在数学中,个集合X和Y的笛卡尓积(Cartesian product),又称直积,表示为X × Y。假设集合A={a, b...
  • 表联合查询 一个根据机场查询航线的例子。有两张表,机场表包含机场信息:机场id、机场名字、机场城市。 航班包含航线信息:航班id、飞机编号、飞行时间、票价、起飞机场id、降落机场id. 需要查询的结果表包含...

空空如也

空空如也

1 2 3 4 5 ... 10
收藏数 186
精华内容 74
关键字:

数据库两张表联合查询