---------------------------------单表查询-------------------------------------
一、select完整的语法
select【distinct】（*|字段|四则运算|聚合函数） from 表名
distinct 去除重复
where查询条件
group by 分组
having 分组后的过滤
order by 排序
limit 限制获取后的数据
二、关键字的执行优先级
#重点中的重点：关键字的执行优先级fromwheregroup byhavingselectdistinctorder bylimit
1、找到：from
2、拿着where指定的约束条件，去文件/表中取出一条条的记录
3、将取出来的的一条条记录进行分组group by，如果没有group by，则整体作为一组
4、将分组的结果进行having过滤
5、执行select
6、去重
7、将执行结果按条件排序：order by
8、限制结果的显示条数
简单查询
create table stu(id int primary key auto_increment,name char(10),math float,english float);insert into stu values(null,"赵云",90,30);insert into stu values(null,"小乔",90,60);insert into stu values(null,"小乔",90,60);insert into stu values(null,"大乔",10,70);insert into stu values(null,"李清照",100,100);insert into stu values(null,"铁拐李",20,55);insert into stu values(null,"小李子",20,55);
mysql> select *from stu;+----+-----------+------+---------+| id | name | math | english |+----+-----------+------+---------+| 1 | 赵云 | 90 | 30 || 2 | 小乔 | 90 | 60 || 3 | 小乔 | 90 | 60 || 4 | 大乔 | 10 | 70 || 5 | 李清照 | 100 | 100 || 6 | 铁拐李 | 20 | 55 || 7 | 小李子 | 20 | 55 |+----+-----------+------+---------+7 rows in set (0.10 sec)----------------------------------------------------------直接用字段名查询，mysql> select name from stu;+-----------+| name |+-----------+| 赵云 || 小乔 || 小乔 || 大乔 || 李清照 || 铁拐李 || 小李子 |+-----------+----------------------------------------distinct避免重复mysql> select distinct math from stu;+------+| math |+------+| 90 || 10 || 100 || 20 |+------+4 rows in set (0.38 sec)
mysql> +----+-----------+------+---------+ -> | id | name | math | english | -> +----+-----------+------+---------+ -> | 1 | 赵云 | 90 | 30 | -> | 2 | 小乔 | 90 | 60 | -> | 3 | 小乔 | 90 | 60 | -> | 4 | 大乔 | 10 | 70 | -> | 5 | 李清照 | 100 | 100 | -> | 6 | 铁拐李 | 20 | 55 | -> | 7 | 小李子 | 20 | 55 | -> +----+-----------+------+---------+-----------------------------------------------------------------mysql> select english+10 from stu;+------------+| english+10 |+------------+| 40 || 70 || 70 || 80 || 110 || 65 || 65 |+------------+7 rows in set (1.68 sec)mysql> select name,math+10 from stu;+-----------+---------+| name | math+10 |+-----------+---------+| 赵云 | 100 || 小乔 | 100 || 小乔 | 100 || 大乔 | 20 || 李清照 | 110 || 铁拐李 | 30 || 小李子 | 30 |+-----------+---------+7 rows in set (0.00 sec)mysql> select name,math+english as"总分" from stu; #as别名+-----------+--------+| name | 总分 |+-----------+--------+| 赵云 | 120 || 小乔 | 150 || 小乔 | 150 || 大乔 | 80 || 李清照 | 200 || 铁拐李 | 75 || 小李子 | 75 |+-----------+--------+7 rows in set (0.91 sec)
where约束
1、比较运算符：> , < , >= , <= ,<>,!=
mysql> select *from stu;+----+-----------+------+---------+| id | name | math | english |+----+-----------+------+---------+| 1 | 赵云 | 90 | 30 || 2 | 小乔 | 90 | 60 || 3 | 小乔 | 90 | 60 || 4 | 大乔 | 10 | 70 || 5 | 李清照 | 100 | 100 || 6 | 铁拐李 | 20 | 55 || 7 | 小李子 | 20 | 55 |+----+-----------+------+---------+mysql> select *from stu where english = 100;+----+-----------+------+---------+| id | name | math | english |+----+-----------+------+---------+| 5 | 李清照 | 100 | 100 |+----+-----------+------+---------+1 row in set (0.00 sec)mysql> select *from stu where math >=90;+----+-----------+------+---------+| id | name | math | english |+----+-----------+------+---------+| 1 | 赵云 | 90 | 30 || 2 | 小乔 | 90 | 60 || 3 | 小乔 | 90 | 60 || 5 | 李清照 | 100 | 100 |+----+-----------+------+---------+4 rows in set (0.00 sec)mysql> select *from stu where 90<=math<=100;+----+-----------+------+---------+| id | name | math | english |+----+-----------+------+---------+| 1 | 赵云 | 90 | 30 || 2 | 小乔 | 90 | 60 || 3 | 小乔 | 90 | 60 || 4 | 大乔 | 10 | 70 || 5 | 李清照 | 100 | 100 || 6 | 铁拐李 | 20 | 55 || 7 | 小李子 | 20 | 55 |+----+-----------+------+---------+7 rows in set (1.52 sec)mysql> select *from stu where math>=60 and english >= 60;+----+-----------+------+---------+| id | name | math | english |+----+-----------+------+---------+| 2 | 小乔 | 90 | 60 || 3 | 小乔 | 90 | 60 || 5 | 李清照 | 100 | 100 |+----+-----------+------+---------+
2、between
mysql> select name,math from stu where math between 60 and 90;+--------+------+| name | math |+--------+------+| 赵云 | 90 || 小乔 | 90 || 小乔 | 90 |+--------+------+3 rows in set (0.00 sec)mysql> select name,math from stu where math not between 60 and 90;+-----------+------+| name | math |+-----------+------+| 大乔 | 10 || 李清照 | 100 || 铁拐李 | 20 || 小李子 | 20 |+-----------+------+4 rows in set (0.00 sec)
3、in
mysql> select id,name,english from stu where english=60 or english=70 or english=100;+----+-----------+---------+| id | name | english |+----+-----------+---------+| 2 | 小乔 | 60 || 3 | 小乔 | 60 || 4 | 大乔 | 70 || 5 | 李清照 | 100 |+----+-----------+---------+4 rows in set (0.00 sec)mysql> select id,name,english from stu where english in(60,70,100);+----+-----------+---------+| id | name | english |+----+-----------+---------+| 2 | 小乔 | 60 || 3 | 小乔 | 60 || 4 | 大乔 | 70 || 5 | 李清照 | 100 |+----+-----------+---------+4 rows in set (0.00 sec)
4、like
#like 长得像 模糊匹配#% 任意个任意字符#_ 一个任意字符select *from stu where name like "李%"; 开头带李的select *from stu where name like "%李%"; 名字带有李的select *from stu where name like "%李"; 最后一个字是李的
5、逻辑运算符：在多个条件直接可以使用运算符 and or not
group by 分组查询
一、什么是分组，为什么分组
#1、首先明确一点：分组发生在where之后，即分组是基于where之后得到的记录而进行的#2、分组指的是：把一个整体，按照某个标识分成不同部分#3、为何要分组呢？ 为了统计数据，例如获得一个部门的最高工资，等等 小窍门：‘每’这个字后面的字段，就是我们分组的依据#4、大前提： 可以按照任意字段分组，但是分组完毕后，比如group by math ，只能查看math字段，如果想查看组内信息，需要借助于聚合函数
create table emp (id int,name char(10),sex char,dept char(10),job char(10),salary double);insert into emp values(1,"刘备","男","市场","总监",5800),(2,"张飞","男","市场","员工",3000),(3,"关羽","男","市场","员工",4000),(4,"孙权","男","行政","总监",6000),(5,"周瑜","男","行政","员工",5000),(6,"小乔","女","行政","员工",4000),(7,"曹操","男","财务","总监",10000),(8,"司马懿","男","财务","员工",6000);
注意，5.7以下的版本需要set global sql_mode = "STRICT_TRANS_TABLES,ONLY_FULL_GROUP_BY";
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';设置后需要退出重新登录最后设置在配置文件中，在配置文件中添加sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION注意，此时没有双引号复制代码#设置前mysql> select *from emp group by dept;+------+--------+------+--------+--------+--------+| id | name | sex | dept | job | salary |+------+--------+------+--------+--------+--------+| 1 | 刘备 | 男 | 市场 | 总监 | 5800 || 4 | 孙权 | 男 | 行政 | 总监 | 6000 || 7 | 曹操 | 男 | 财务 | 总监 | 10000 |+------+--------+------+--------+--------+--------+3 rows in set (0.00 sec)#设置后mysql> select *from emp group by dept; #报错,不能用*，只能查找一个ERROR 1055 (42000): 'day44.emp.id' isn't in GROUP BY #from前跟的一定要与by后跟的一致mysql> select dept from emp group by dept;+--------+| dept |+--------+| 市场 || 行政 || 财务 |+--------+---------------------------------------------------------------------------------此时select只能查一个字段，想要获取其他信息，需要借助函数group_concat()group by 与group_concat连用mysql> select dept,group_concat(name) from emp group by dept;+--------+----------------------+| dept | group_concat(name) |+--------+----------------------+| 市场 | 刘备,张飞,关羽 || 行政 | 孙权,周瑜,小乔 || 财务 | 曹操,司马懿 |+--------+----------------------+
#设置前mysql> select *from emp group by dept;+------+--------+------+--------+--------+--------+| id | name | sex | dept | job | salary |+------+--------+------+--------+--------+--------+| 1 | 刘备 | 男 | 市场 | 总监 | 5800 || 4 | 孙权 | 男 | 行政 | 总监 | 6000 || 7 | 曹操 | 男 | 财务 | 总监 | 10000 |+------+--------+------+--------+--------+--------+3 rows in set (0.00 sec)#设置后mysql> select *from emp group by dept; #报错,不能用*，只能查找一个ERROR 1055 (42000): 'day44.emp.id' isn't in GROUP BY #from前跟的一定要与by后跟的一致mysql> select dept from emp group by dept;+--------+| dept |+--------+| 市场 || 行政 || 财务 |+--------+--------------------------------------------------------------------------------- 此时select只能查一个字段，想要获取其他信息，需要借助函数 group_concat() group by 与group_concat连用
mysql> select dept,group_concat(name) from emp group by dept;
+--------+----------------------+| dept | group_concat(name) |+--------+----------------------+| 市场 | 刘备,张飞,关羽 || 行政 | 孙权,周瑜,小乔 || 财务 | 曹操,司马懿 |+--------+----------------------+
聚合函数
sum 求和
avg 平均数
max 最大值
min 最小值
count 计数
mysql> select avg(salary) from emp;+-------------+| avg(salary) |+-------------+| 5475 |+-------------+1 row in set (0.00 sec)mysql> select count(salary) from emp;+---------------+| count(salary) |+---------------+| 8 |+---------------+1 row in set (0.00 sec)
having过滤
#having与where不同之处#！！！执行的优先级从高到底：where > group by > having #1. Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。#2. Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数
查询排序 order by
#order by默认是升序#单字段select *from emp order by salary;#多字段（无论 升降序，先按前边的排序，如果有相同的，再按后边的）select *from emp order by salary id; （先按salary,工资相同的再按id）#可以使用desc 来指定为降序select *from emp order by salary desc；select *from emp order by id desc,salary desc;
mysql> select *from emp order by salary;+------+-----------+------+--------+--------+--------+| id | name | sex | dept | job | salary |+------+-----------+------+--------+--------+--------+| 2 | 张飞 | 男 | 市场 | 员工 | 3000 || 3 | 关羽 | 男 | 市场 | 员工 | 4000 || 6 | 小乔 | 女 | 行政 | 员工 | 4000 || 5 | 周瑜 | 男 | 行政 | 员工 | 5000 || 1 | 刘备 | 男 | 市场 | 总监 | 5800 || 4 | 孙权 | 男 | 行政 | 总监 | 6000 || 8 | 司马懿 | 男 | 财务 | 员工 | 6000 || 7 | 曹操 | 男 | 财务 | 总监 | 10000 |+------+-----------+------+--------+--------+--------+mysql> select *from emp order by salary desc; +------+-----------+------+--------+--------+--------+| id | name | sex | dept | job | salary |+------+-----------+------+--------+--------+--------+| 7 | 曹操 | 男 | 财务 | 总监 | 10000 || 4 | 孙权 | 男 | 行政 | 总监 | 6000 || 8 | 司马懿 | 男 | 财务 | 员工 | 6000 || 1 | 刘备 | 男 | 市场 | 总监 | 5800 || 5 | 周瑜 | 男 | 行政 | 员工 | 5000 || 3 | 关羽 | 男 | 市场 | 员工 | 4000 || 6 | 小乔 | 女 | 行政 | 员工 | 4000 || 2 | 张飞 | 男 | 市场 | 员工 | 3000 |+------+-----------+------+--------+--------+--------+mysql> select *from emp order by salary desc,id desc;+------+-----------+------+--------+--------+--------+| id | name | sex | dept | job | salary |+------+-----------+------+--------+--------+--------+| 7 | 曹操 | 男 | 财务 | 总监 | 10000 || 8 | 司马懿 | 男 | 财务 | 员工 | 6000 || 4 | 孙权 | 男 | 行政 | 总监 | 6000 || 1 | 刘备 | 男 | 市场 | 总监 | 5800 || 5 | 周瑜 | 男 | 行政 | 员工 | 5000 || 6 | 小乔 | 女 | 行政 | 员工 | 4000 || 3 | 关羽 | 男 | 市场 | 员工 | 4000 || 2 | 张飞 | 男 | 市场 | 员工 | 3000 |+------+-----------+------+--------+--------+--------+8 rows in set (0.00 sec)
限制查询的记录数 limit
select *from emp limit a,b;
a表示起始位置
b表示获取的条数
总共为10页 每一页显示 3条 页数 10 / 3 由余数则 + 1 = 4page = 1select *from emp limit 0,3page = 2(page - 1) * 3select *from emp limit 3,3page = 3(page - 1) * 3select *from emp limit 6,3起始位置的计算公式limit (页数-1) * 每页的条数,每页条数
mysql> select *from emp limit 0,3;+------+--------+------+--------+--------+--------+| id | name | sex | dept | job | salary |+------+--------+------+--------+--------+--------+| 1 | 刘备 | 男 | 市场 | 总监 | 5800 || 2 | 张飞 | 男 | 市场 | 员工 | 3000 || 3 | 关羽 | 男 | 市场 | 员工 | 4000 |+------+--------+------+--------+--------+--------+3 rows in set (0.00 sec)mysql> select *from emp limit 3,3;+------+--------+------+--------+--------+--------+| id | name | sex | dept | job | salary |+------+--------+------+--------+--------+--------+| 4 | 孙权 | 男 | 行政 | 总监 | 6000 || 5 | 周瑜 | 男 | 行政 | 员工 | 5000 || 6 | 小乔 | 女 | 行政 | 员工 | 4000 |+------+--------+------+--------+--------+--------+3 rows in set (0.00 sec)
正则表达式匹配（也是模糊匹配）
语法: select *from emp regexp "表达式"; select *from emp where name regexp ".*ba$"; like 只有 % 和 _ 灵活度没有 regexp高
-------------------多表查询----------------
准备表
#建表create table department(id int,name varchar(20) );create table employee(id int primary key auto_increment,name varchar(20),sex enum('male','female') not null default 'male',age int,dep_id int);#插入数据insert into department values(200,'技术'),(201,'人力资源'),(202,'销售'),(203,'运营');insert into employee(name,sex,age,dep_id) values('egon','male',18,200),('alex','female',48,201),('wupeiqi','male',38,201),('yuanhao','female',28,202),('liwenzhou','male',18,200),('jingliyang','female',18,204);#查看表结构和数据mysql> desc department;+-------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id | int(11) | YES | | NULL | || name | varchar(20) | YES | | NULL | |+-------+-------------+------+-----+---------+-------+mysql> desc employee;+--------+-----------------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+--------+-----------------------+------+-----+---------+----------------+| id | int(11) | NO | PRI | NULL | auto_increment || name | varchar(20) | YES | | NULL | || sex | enum('male','female') | NO | | male | || age | int(11) | YES | | NULL | || dep_id | int(11) | YES | | NULL | |+--------+-----------------------+------+-----+---------+----------------+mysql> select * from department;+------+--------------+| id | name |+------+--------------+| 200 | 技术 || 201 | 人力资源 || 202 | 销售 || 203 | 运营 |+------+--------------+mysql> select * from employee;+----+------------+--------+------+--------+| id | name | sex | age | dep_id |+----+------------+--------+------+--------+| 1 | egon | male | 18 | 200 || 2 | alex | female | 48 | 201 || 3 | wupeiqi | male | 38 | 201 || 4 | yuanhao | female | 28 | 202 || 5 | liwenzhou | male | 18 | 200 || 6 | jingliyang | female | 18 | 204 |+----+------------+--------+------+--------+
多表连接查询
#重点：外链接语法select 字段列表 from 表1 inner|left|right join 表2 on 表1.字段 = 表2.字段;
笛卡尔积查询
积 表示乘积的意思 把两个表中的所有数据 全部建立关联关系 a 表 有二条 b表有三条 总数据量为2 * 3 = 6条 可以保证 肯定有一条关联关系是正确的,但是同时会产生大量错误数据, 我们需要加以过滤 来得到正确的数据 select *from 表1，表2.....where 过滤条件 mysql> select *from department,employee where dep_id = department.id and department.name = "技术";
内连接：只连接匹配的行
两边数据完全匹配成功才会显示 inner（可以省略） join on（on只能与join一起使用，但是在join中 可以把on换成where）on == where 只能用于连接查询如果用来筛选匹配关系 建议使用on 连接查询中必须使用on
mysql> select *from department,employee where dep_id = department.id and department.name = "技术";+------+--------+----+-----------+------+------+--------+| id | name | id | name | sex | age | dep_id |+------+--------+----+-----------+------+------+--------+| 200 | 技术 | 1 | egon | male | 18 | 200 || 200 | 技术 | 5 | liwenzhou | male | 18 | 200 |+------+--------+----+-----------+------+------+--------+mysql> select *from department join employee on dep_id = department.id where department.name = "技术";+------+--------+----+-----------+------+------+--------+| id | name | id | name | sex | age | dep_id |+------+--------+----+-----------+------+------+--------+| 200 | 技术 | 1 | egon | male | 18 | 200 || 200 | 技术 | 5 | liwenzhou | male | 18 | 200 |+------+--------+----+-----------+------+------+--------+
多对多： 1.先把三个表全都连在一起 select * from stu join tsr join tea 2.用on来筛选出正确关系 on stu.id = tsr.s_id and tea.id = tsr.t_id （把错误的丢掉） 3.然后通过where 添加额外的条件 where tea.name = "egon";stu tsr join 为三个表
外链接之左连接：优先显示左表全部记录
语法：select *from 表1 left join 表2 on 条件左边无论是否匹配，都全部显示，右边只显示匹配成功的 select *from employee left join department on dep_id = department.id;
外链接之右连接：优先显示右表的全部记录
全外连接：显示左右两个表的全部记录
全外连接：在内连接的基础上增加左边有右边没有的和右边有左边没有的结果#注意：mysql不支持全外连接 full JOIN#强调：mysql可以使用此种方式间接实现全外连接select * from employee left join department on employee.dep_id = department.idunionselect * from employee right join department on employee.dep_id = department.id;#查看结果+------+------------+--------+------+--------+------+--------------+| id | name | sex | age | dep_id | id | name |+------+------------+--------+------+--------+------+--------------+| 1 | egon | male | 18 | 200 | 200 | 技术 || 5 | liwenzhou | male | 18 | 200 | 200 | 技术 || 2 | alex | female | 48 | 201 | 201 | 人力资源 || 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 || 4 | yuanhao | female | 28 | 202 | 202 | 销售 || 6 | jingliyang | female | 18 | 204 | NULL | NULL || NULL | NULL | NULL | NULL | NULL | 203 | 运营 |+------+------------+--------+------+--------+------+--------------+#注意 union与union all的区别：union会去掉相同的纪录
子查询
#1：子查询是将一个查询语句嵌套在另一个查询语句中。#2：内层查询语句的查询结果，可以为外层查询语句提供查询条件。#3：子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字#4：还可以包含比较运算符：= 、 !=、> 、
create table emp (id int,name char(10),sex char,age int,dept_id int,job char(10),salary double);insert into emp values(1,"刘备","男",26,1,"总监",5800),(2,"张飞","男",24,1,"员工",3000),(3,"关羽","男",30,1,"员工",4000),(4,"孙权","男",25,2,"总监",6000),(5,"周瑜","男",22,2,"员工",5000),(6,"小乔","女",31,2,"员工",4000),(7,"曹操","男",19,3,"总监",10000),(8,"司马懿","男",24,3,"员工",6000);create table dept(id int primary key,name char(10));insert into dept values(1,"市场"),(2,"行政"),(3,"财务");
连接查询：mysql> select *from dept join emp -> on dept_id = dept.id -> where dept.name = "财务";子查询：先通过部门名称拿到部门id select name from emp where dept_id = 3;在根据id 找对应的员工select name from emp where dept_id = (select id from dept where name = "财务");连到一起select name from emp where dept_id = (select id from dept where name = "财务");
带in关键字的子查询
#1、查询平均年龄在25岁以上的部门名 先查找到平均年龄大于25的部门idselect dept_id from emp group by dept_id having avg(age)>25;再通过ID找部门名select name from dept where id in (select dept_id from emp group by dept_id having avg(age)>25);#2、查找市场部所有人员的名字select id from dept where name = "市场";select name from emp where dept_id =(select id from dept where name = "市场");#3、查找各个部门的人数mysql> select *from dept join(select dept_id,count(*) from emp group by dept_id)as a-> on id = dept_id; （Every derived table must have its own alias，每个派生的表都必须有自己的别名）
----------------------------------------------------------------------
用户管理
mysql用户指的是和客户端连接服务器时使用的账户
在一些公司中，很多项目的数据 可能会放在同一个服务器
那就必须要为每一个用户明确其所拥有的权限
通常 到公司之后 都会给你个一个账号的名称和密码 并且 为你制定可以访问哪些数据库和表
对用户这个账号的增删改查，以及权限的增删改查
mysql与权限相关的表
user
数据库30分 设计图书管理系统,图书表包含,书名,售价,出版社,页数,作者信息 其中包含作者信息管理,作者表包含,姓名,年龄,性别,国籍信息 1.创建图书数据库 2.创建相关的表 3.添加作者信息 莫言,男,65,中国 尼古拉·奥斯特洛夫斯基,女,65,USA 金庸,男,94,中国 古龙,男,48中国 4.添加 图书信息 提示 先添加图书信息 到图书表 在添加关系到中间表 绝代双骄,68元,500页,(古龙,金庸) 射雕英雄传,198元,800页,(金庸) 钢铁是怎样炼成的,88元,300页, (尼古拉·奥斯特洛夫斯基,古龙) 丰乳肥臀,78元,200页,(莫言)
mysql> create database book_db charset utf8;Query OK, 1 row affected (0.16 sec)mysql> use book_db;Database changedmysql> create table author( -> id int primary key auto_increment, -> name varchar(20), -> sex enum("男","女") not null, -> age int, -> nationnality varchar(20));Query OK, 0 rows affected (2.19 sec)mysql> insert into author value(1,"莫言","男",65,"中国");Query OK, 1 row affected (1.87 sec)mysql> insert into author value(2,"尼古拉斯",65,"USA");ERROR 1136 (21S01): Column count doesn't match value count at row 1mysql> insert into author value(2,"尼古拉斯","女",65,"USA");Query OK, 1 row affected (1.84 sec)mysql> insert into author value(3,"金庸","男",94,"中国");Query OK, 1 row affected (0.13 sec)mysql> insert into author value(4,"古龙","男",48,"中国");Query OK, 1 row affected (0.14 sec)mysql> create table book( -> id int primary key auto_increment, -> book_name varchar(20), -> book_price int, -> num int);Query OK, 0 rows affected (2.04 sec)mysql>mysql> insert into book values( -> 1,"绝代双骄",68,500), -> (2,"射雕英雄传",198,800), -> (3,"钢铁是怎样炼成的",88,300), -> (4,"丰乳肥臀",78,200);mysql> create table book_author( -> id int primary key auto_increment, -> book_id int, -> author_id int -> );Query OK, 0 rows affected (2.05 sec)mysql> insert into book_author values(null,1,3),(null,1,4),(null,2,3),(null,3,2), -> (null,3,4),(null,4,1);Query OK, 6 rows affected (1.79 sec)Records: 6 Duplicates: 0 Warnings: 0mysql> select *from author join book join book_author -> on book_id = book.id and author_id = author.id -> where author.name = "金庸";mysql> select *from author join book join book_author -> on book_id = book.id and author_id = author.id -> where book.book_name = "绝代双骄";mysql> delete from book where book_name = "钢铁是怎样炼成的";Query OK, 1 row affected (1.86 sec)mysql>mysql>mysql> delete from book_author where id = 1;Query OK, 1 row affected (1.87 sec)