Mysql 学习笔记(五)：单表与多表查询

表与表之间的关系

#!/usr/bin/env python
# -*- coding:utf-8 -*-

# book ：id name price author_id
# author:aid name birthday gender

# 作者与书 一对多
# create table author(
# aid primary key auto_increment,
# name char(12) not null,
# birthday date,
# gender enum('male','female') default 'male'
# )

# create table book(
# id  int  primary key,
# name char(12) not null,
# price float(5,2)
# author_id int,
# foreign key(author_id) references author(aid)
# )

# 作者与书一对一
# create table author(
# aid primary key auto_increment,
# name char(12) not null,
# birthday date,
# gender enum('male','female') default 'male'
# )

# create table book(
# id  int  primary key,
# name char(12) not null,
# price float(5,2)
# author_id int unique,
# foreign key(author_id) references author(aid)
# )

# 作者与书多对多
# create table author(
# aid primary key auto_increment,
# name char(12) not null,
# birthday date,
# gender enum('male','female') default 'male'
# )

# create table book(
# id  int  primary key,
# name char(12) not null,
# price float(5,2)
# )

# create table book_author(
# id int primary key auto_increment,
# book_id int not null,
# author_id int not null,
# foreign key(book_id) references book(id),
# foreign key(author_id) references author(aid),
# );

where 语句

#!/usr/bin/env python
# -*- coding:utf-8 -*-

# 比较运算 > < = >= <= != <>
# 范围筛选
    # 多选一 字段名 in (值1,值2，值3)
        # 20000,30000,3000,19000,18000,17000
            # select * from employee where salary in (20000,30000,3000,19000,18000,17000)
    # 在一个模糊的范围里  between 10000 and 20000
        # 在一个数值区间  1w-2w之间的所有人的名字
            # select emp_name from employee where salary between 1000