###数学相关
- 向下取整 floor(num)
select floor(3.85); - 四舍五入 round(num)
select round(3.8); - 四舍五入 round(num,m) m代表小数位数
select round(23.879,2); - 非四舍五入 truncate(num,m) m代表小数位数
select truncate(23.879,2); - 随机数 rand() 0-3 0 1 2 3
select floor(rand()*4);
-获取3-5 的随机整数
###分组查询 group by
- 格式: group by 字段名;
- select … from 表名 where … group by … order by …limit…;
- 查询每个部门的平均工资
select deptno,avg(sal) from emp group by deptno; - 查询每种职业的最高工资
select job,max(sal) from emp group by job;
####having
- where 后面写普通字段的条件
- having后面写聚合函数的条件,having需要结合group by使用
- 各个关键字的顺序:
select … from 表名 where … group by … having … order by … limit …;
###子查询(嵌套查询)
- 查询工资高于平均工资的员工信息
select * from emp where sal>(select avg(sal) from emp); - 查询员工表中工资最高的员工信息
select * from emp where sal=(select max(sal) from emp); - 查询工资高于20号部门最高工资的员工信息
select * from emp where sal>(select max(sal) from emp where deptno=20);
- 子查询可以写的位置- 写在where或having后面 当做查询条件的值
- 写在创建表的时候
create table emp_10 as (select * from emp where deptno=10); - 写在from后面 当成一张虚拟表 必须有别名
select ename from (select * from emp where deptno=10) newtable;
###关联查询
- 同时查询多张表的查询方式称为关联查询
- 关联查询必须写关联关系,如果不写会得到两张表的乘积,这个乘积称为笛卡尔积,工作中千万不要出现这种情况,这是一个错误的查询结果集
- 查询每一个员工的姓名和对应的部门名
select e.ename,d.dname
from emp e,dept d
where e.deptno=d.deptno;
- 等值连接和内连接 - 等值连接:select * from A,B where A.x=B.x and A.age>20;
- 内连接: select * from A join B on A.x=B.x where A.age>20;
-查询每一个员工的姓名和对应的部门名
select e.ename,d.dname
from emp e join dept d
on e.deptno=d.deptno;
- 内连接练习:
- 查询在new york工作的员工姓名
select e.ename from emp e join dept d on e.deptno=d.deptno
where d.loc=‘new york’; - 查询James的部门名和地点
select d.dname,d.loc from emp e join dept d on e.deptno=d.deptno
where e.ename=‘james’;
- 外连接:查询一张表的全部数据和另外一张表的交集数据
- 格式: select * from A left/right join B on A.x=B.x where A.age=20;
- 查询所有的部门名和对应的员工名
select d.dname,e.ename
from emp e right join dept d
on e.deptno=d.deptno;
- 查询所有的部门名和对应的员工名
- 总结:如果查询的数据是两张表的交集数据使用内连接,如果查询的数据是一张表的全部和另外一张表的交集使用外连接。
##表设计:关联关系
- 外键:用于建立关系的字段称为外键
####一对一 - 什么是一对一: 有AB两张表,A表中一条数据对应B表中的一条数据,同时B表中一条也对应A表中的一条
- 应用场景: 为了提高查询效率 把原有一张表的数据拆成两张表如:商品表和商品详情表 、 用户表和用户信息扩展表
- 如何建立关系: 在从表中添加外键指向主表的主键
- 练习:
创建表保存以下数据:
1,libai,admin,诗仙,38
2,liubei,123456,大耳贼,50
- 创建user(id,username,password)和userinfo(user_id,nick,age)表
create table user(id int primary key auto_increment,username varchar(10),password varchar(10));
create table userinfo(user_id int,nick varchar(10),age int);
insert into user values(null,‘libai’,‘admin’),(null,‘liubei’,‘123456’);
insert into userinfo values(1,‘诗仙’,38),(2,‘大耳贼’,50); - 查询每个用户的用户名和对应的昵称
select u.username,ui.nick
from user u join userinfo ui
on u.id=ui.user_id; - 查询诗仙的用户名和密码是什么
select u.username,u.password
from user u join userinfo ui
on u.id=ui.user_id where ui.nick=‘诗仙’; - 查询liubei的年龄
select ui.age
from user u join userinfo ui
on u.id=ui.user_id where u.username=‘liubei’;
####一对多
- 什么是一对多:有AB两张表,A表中一条数据对应B表多条数据,同时B表一条对应A表一条,称为一对多。
- 应用场景: 员工表和部门表 商品表和商品分类表 用户表和地址表
- 如何建立关系: 在多的表中添加外键指向另外一张表的主键
- 练习:创建表保存以下数据
部门表: 神仙 ,妖怪
员工表: 猪八戒,孙悟空,白骨精,蜘蛛精
- 创建表并插入数据 t_dept(id,name) t_emp(id,name,dept_id)
create table t_dept(id int primary key auto_increment,name varchar(10));
create table t_emp(id int primary key auto_increment,name varchar(10),dept_id int);
insert into t_dept values(null,‘神仙’),(null,‘妖怪’);
insert into t_emp values(null,‘八戒’,1),(null,‘悟空’,1),(null,‘白骨精’,2),(null,‘蜘蛛精’,2); - 查询每个员工的姓名和对应的部门名
select e.name,d.name
from t_dept d join t_emp e
on d.id=e.dept_id; - 查询神仙部的员工姓名
select e.name
from t_dept d join t_emp e
on d.id=e.dept_id where d.name=‘神仙’; - 查询蜘蛛精的部门名
select d.name
from t_dept d join t_emp e
on d.id=e.dept_id where e.name=‘蜘蛛精’;
####多对多
- 什么是多对多: 有AB两张表,A表中一条数据对应B表中的多条数据,同时B表中一体数据对应A表中的多条。
- 应用场景: 用户表和权限表 老师表和学生表
- 如何建立关系: 创建关系表,在关系表中添加两个外键指向另外两个表的主键
###表设计案例:权限管理
- 总共需要5张表: 三张主表:用户表 角色表 权限表 两张关系表:用户-角色关系表,角色-权限关系表