MySql详解(二)
一、分组查询
语法:
select col_name1,col_name2... from tb_name group by having ...;
练习:
执行下面的SQL,创建orders表并插入数据 create table orders( id int, product varchar(20), price float ); insert into orders(id,product,price) values(1,'小米手机',900); insert into orders(id,product,price) values(2,'奥妙洗衣液',60); insert into orders(id,product,price) values(3,'乐视TV',90); insert into orders(id,product,price) values(4,'联想键盘',80); insert into orders(id,product,price) values(5,'奥妙洗衣液',60); insert into orders(id,product,price) values(6,'小米手机',900); ~对订单表中商品归类后,显示每一类商品的总价 select * from orders group by product; select count(*) from orders group by product; select sum(price) from orders group by product; ~查询购买了几类商品,并且每类总价大于100的商品 select product,sum(price) 总价 from orders group by product having sum(price)>100; ~查询单价小于100而总价大于100的商品的名称. select * from orders where price<100 group by product having sum(price) > 100;
- where和having的区别
- where和having子句都可以进行过滤 , 但是使用场景有所不同 。
- where对分组之前的数据进行过滤,不能使用聚合函数和别名
- having子句对分组之后的数据进行过滤 , 可以使用聚合函数和别名 。
- 使用where子句的地方一般都可以用having代替 , 但是使用having的地方一般不能使用where代替。
二、数据库备份与恢复
- 备份数据库
- 注意在新开的cmd窗口中 , 不要登录mysql
- 备份命令: mysqldump -u用户名 -p >备份数据存放位置
- 恢复数据
- 方式一: 在cmd中(不登录mysql)
- 恢复命令: mysql -u用户名 -p <数据存放位置
- 方式二: 在mysql客户端中
- 恢复命令: source 数据存放位置
- 方式一: 在cmd中(不登录mysql)
三、外键约束
- 外键:唯一标识其他表中的一条记录,用来通知数据库两张表字段之间的对应关系, 并让数据库帮我们维护这样关系的键就叫做外键
- 外键的作用: 确保数据库数据的完整性和一致性
- 添加外键:foreign key(外键) reference 其他表(关联的键)
案例:
create table dept( id int primary key auto_increment, name varchar(20) ); insert into dept values(null, '财务部'); insert into dept values(null, '人事部'); insert into dept values(null, '科技部'); insert into dept values(null, '销售部'); create table emp( id int primary key auto_increment, name varchar(20), dept_id int, foreign key(dept_id) references dept(id) ); insert into emp values(null, '张三', 1); insert into emp values(null, '李四', 2); insert into emp values(null, '老王', 3); insert into emp values(null, '赵四', 4); insert into emp values(null, '刘能', 4); 要求: (1)执行上面的SQL语句,创建两张表(dept和emp),在创建时先不指定dept_id为外键,尝试删除部门表中的某一个部门。 (2)将dept和emp表删除重建,在创建时指定dept_id为外键,再次尝试删除部门表中的某一个部门。
四、多表设计
- 1 - *(一对多):在多的一方添加列保存一的一方的主键来作为外键, 来保存两张表之间的关系
- 1 - 1(一对一):在任意一方添加列保存另一方的主键作为外键, 来保存两张表之间的关系
-
-
- *(多对多):在一张第三方的表中分别保存两张表的主键作为外键, 来保存两张表之间的关系,可以把多对多的关系拆分成两个一对多的关系来理解
-
五、多表查询
案例:
create table dept( id int primary key auto_increment, name varchar(20) ); insert into dept values(null, '财务部'); insert into dept values(null, '人事部'); insert into dept values(null, '科技部'); insert into dept values(null, '销售部'); create table emp( id int primary key auto_increment, name varchar(20), dept_id int ); insert into emp values(null, '张三', 1); insert into emp values(null, '李四', 2); insert into emp values(null, '老王', 3); insert into emp values(null, '刘能', 5); 需求1:查询出部门表和员工表,同时列出部门信息和员工信息。 select * from dept,emp;(笛卡尔积查询) select * from dept, emp where emp.dept_id=dept.id; 或者 select * from dept inner join emp on emp.dept_id=dept.id; 需求2:查询出部门信息和部门所对应的员工信息,同时列出那些没有员工的部门 select * from dept left join emp on emp.dept_id=dept.id; 需求3:查询出部门信息和部门所对应的员工信息,同时列出那些没有部门的员工 select * from dept right join emp on emp.dept_id=dept.id; 需求4:查询出部门信息和部门所对应的员工信息, 同时列出没有员工的部门和那些没有部门的员工 select * from dept full join emp on emp.dept_id=dept.id;###mysql不支持
笛卡尔积查询
- 两张表相乘得出来的结果。如果左边表有m条记录,右边有n条记录,则查询出来的结果就是m*n条。这些查询结果中包含大量错误的结果,通常不会使用这种查询。
内连接查询:查询出左边表(dept)有且右边表(emp)也有的记录。
select * from emp inner join dept on dept.id=dept_id;
左外连接查询:在内连接查询的基础上,加上左边表有而右边表没有的记录
**查询出部门所对应的员工信息, 同时列出那些没有员工的部门
select * from dept left join emp on dept.id=dept_id;
右外连接查询:在内连接查询的基础上,加上右边表有而左边表没有的记录。
**查询出部门所对应的员工信息, 同时列出那些没有部门的员工
select * from dept right join emp on dept.id=dept_id;
全外连接查询:在内连接查询的基础上,加上左边表有而右边表没有的记录 和 右边表有而左边表没有的记录。
**查询出部门所对应的员工信息,同时列出那些没有员工的部门及些没有部门的员工
select * from dept full join emp on dept.id=dept_id;###mysql不支持全外连接查询
使用union模拟全外连接查询:
select * from dept left join emp on dept.id=dept_id union select * from dept right join emp on dept.id=dept_id;