CRUD

1.Insert

// 插入一条新的数据，必须全插入
insert into 表名 values (值)
// 选择列名插入数据，必须一一对应
insert into 表名 (字段...) values (值...)

2.Update

// 可以更条件
update 表名 set 列名=新值 ..... [where ....]

3.Delete

// 不加条件会删除整个表的数据
delete from 表名 [where ...]

4.Select

单表查询

// 基本语法 [distinct 去重]
select [DISTINCT] *|{列名...} from 表名
// 使用表达式对查询的列进行运算
select *|{列名...|表达式...} from 表名
// 使用as语句
select 列名 as 别名 from 表名
// 使用order by 进行排序，位于select末尾
select * from 表名 order by 列名或别名 asc | desc .....

• 使用where进行过滤操作

• mysql表查询-加强

# 增强查询
// 使用where子句
-- q：如何查找时间在1992.1.1后入职的员工
select * from emp where hiredate > date('1992-01-01');

// 使用like操作符
// %:表示0到多个字符  _:表示单个字符
-- q：如何显示首字符为s的员工姓名和工资
select ename,sal from emp where ename like 'S%';
-- q：如何显示第三个字符为大写o的所有员工姓名和工资
select ename,sal from emp where ename like '__O%';

// 如何显示没有上级的雇员的情况
select * from emp where mgr is null;

// 查询表结构
desc 表名;

// 使用order by子句
-- q：如何按照工资的从低到高的顺序，显示雇员的信息
select * from emp order by sal; -- (默认升序，desc为降序)
-- q：按照部门号升序而雇员的工资降序排列，显示雇员信息
select * from emp order by deptno, sal desc;

#分页查询
// 基本语法
// 表示start+1行开始取，取出row行，start从0开始计算
select ... limit start,rows
-- q：按雇员的id号升序取出，每页显示3条记录，分别显示第一页，第二页，第三页
select * from emp limit 0,3; -- 第一页
select * from emp limit 3,3; -- 第二页
select * from emp limit 6,3; -- 第三页
// 推出公式
select * from emp limit 每页显示条数 * (页数-1),每页显示条数

#分组加强
// 使用分组函数和分组子句group by
-- q：显示每种岗位的雇员总数、平均工资
select job,count(*),avg(sal) from emp group by job;
-- q：显示雇员总数、以及获得补助的雇员数
select count(*) as total_people, count(comm) as subsidy from emp;
-- 扩展：统计没有获得补助的雇员数
select count(if(comm is null,1,null)) from emp; -- count()中可以写表达式
-- q：显示管理者总人数
select count(distinct mgr) from emp;
-- q：显示雇员工资的最大差额
select Max(sal) - Min(sal) from emp;

#多子句查询
// 如果select语句同时包含group by，having，order by，limit那么顺序就是此顺序
-- q：统计各个部门的平均工资，并且是大于1000的，并且按照平均工资高到低排序，取出前两行记录
select deptno,avg(sal) as avg_sal from emp
						group by deptno having avg_sal > 1000 order by avg_sal desc limit 0,2;

函数

统计函数

#合计统计函数-count
// 返回行的总数
select count(*) | count(列名) from 表名 [where ....]
// count(*)和 count(列名)的区别
// count(*)返回满足条件的记录总行数
// count(列)返回满足条件的列记录的总行数，但是不包含null

#合计函数-sum
// 返回满足where条件的行总和
select sum(列名) from 表名 [where...]
// 单行统计不包含null，多行统计只要有一条数据中有一条null都不包含在结果中

#合计函数-avg
// 返回满足where条件的平均值
select avg(列名) from 表名 [where...]
// 单行统计不包含null，多行统计只要有一条数据中有一条null都不包含在结果中参与平均值的运算

#合计函数-Max/Min
// 返回某一列最大最小值
select Max(列名)|Min(列名) from 表名
// 单行统计不包含null，多行统计只要有一条数据中有一条null都不包含在结果中

分组统计

#分组
// 使用group by对子句进行分组
select 列名... from 表名 group by 列名
// 使用having子句对分组后的结果进行过滤
select 列名... from 表名 group by 列名 having ...

• 练习

dept表

emp表

salgrade表

#练习
// 1.如何显示每个部门的平均工资和最高工资
select deptno,avg(sal),Max(sal) from emp group by deptno [order by...]
// 2.显示每个部门的每种岗位的平均工资和最低工资
-- 分析1.显示每个部门的平均工资和最低工资
select deptno,avg(sal),min(sal) from emp group by deptno
-- 分析2.显示每个部门的每种岗位的平均工资和最低工资
-- 想根据部门分组，然后根据岗位分组
select deptno,job,avg(sal),min(sal) from emp group by deptno,job [order by deptno]
// 3.显示工资低于2000的部门号和它的平均工资
-- 分析1.显示每个部门和它的平均工资
select deptno,avg(sal) from emp group by deptno
-- 分析2.显示工资低于2000的部门号和它的平均工资
select deptno,avg(sal) as pj from emp group by deptno having pj < 2000

字符串相关函数

• 练习

// 以首字母小写的方式显示所有员工emp表的姓名
select concat(lcase(substring(ename,1,1)),substring(ename,2)) from emp;

数学函数

日期函数

加密函数

流程控制函数

多表查询

#多表查询
-- q：显示雇员名，雇员工资以及所在部门的名字[笛卡尔集]
select ename,sal,dname from emp,dept where emp.deptno = dept.deptno;
-- q：如何显示部门号为10的部门名、员工名和工资
select dname,ename,sal from emp,dept where emp.deptno = dept.deptno and emp.deptno = 10;
-- q：显示各个员工的姓名，工资，及其工资的级别
select ename,sal,grade from emp,salgrade where emp.sal >= salgrade.losal and emp.sal <= salgrade.hisal;

#自连接
-- q：显示公司员工的名字和他的上级名字
// 给表取别名，as 可以省略
select a.ename,b.ename from emp a,emp b where a.mgr = b.empno;

#多行子查询
// 子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询
// 单行子查询
-- q：如何显示与SMITH同一部门的所有员工
select * from emp where deptno = (select deptno from emp where ename = 'SMITH');

// 多行子查询
// 多行子查询指返回多行数据的子查询 使用关键字in
-- q：如何查询和部门10的工作相同的雇员的名字、岗位、工资、部门号，但是不含10自己的。
select ename,job,sal,deptno from emp 
			where job in (select distinct job from emp where deptno = 10) and deptno != 10;
-- 可能会去重复，用distinct

#子查询临时表
// 查询ecshop中各个类别中，价格最高的商品
-- 先查询一张临时表
select cat_id,Max(shop_price) from ecs_goods GROUP BY cat_id
-- 再使用临时表和总表关联查询出最后结果
select goods_id,temp.cat_id,goods_name,ecs_goods.shop_price  from 
				ecs_goods,(select cat_id,Max(shop_price) max_sal from 
                   ecs_goods GROUP BY cat_id) temp where 
														temp.max_sal = ecs_goods.shop_price;


// all和any
-- 	q：显示工资比部门30的所有员工的工资高的员工的姓名，工资和部门号
select ename,sal,deptno from emp where sal > (select Max(sal) from emp where deptno = 30);
select ename,sal,deptno from emp where sal > all(select sal from emp where deptno = 30);

-- q：如何显示工资比部门30的其中一个员工的工资高的员工的姓名、工资和部门号
select ename,sal,deptno from emp where sal > (select Min(sal) from emp where deptno = 30);
select ename,sal,deptno from emp where sal > any(select sal from emp where deptno = 30);

#多列子查询
// 多列子查询则是返回多个列数据的子查询
-- 	q：查询和宋江数学，英语，语文完全相同的学生（一一对应）
 select * from students where 
				(math,english,chinese) = (select math,english,chinese from students where name = '宋江');

-- q：查询与smith的部门和岗位完全相同的所有雇员(并且不含smith本人)
select * from emp where 
				(job,deptno) = (select job,deptno from emp where ename = 'SMITH') and ename <> 'SMITH';

练习

#练习
-- 查找每个部门工资高于本部门平均工资的人的资料
select emp.* from 
			emp, (select deptno,avg(sal) avg_sal from emp group by deptno) t where
							emp.deptno = t.deptno and emp.sal > avg_sal;

-- 查找每个部门工资最高的人的详细资料
select emp.* from emp,(select deptno,Max(sal) max_sal from
      emp group by deptno) t where 
						emp.deptno = t.deptno and emp.sal = t.max_sal;

--显示每个部门的信息（包括部门名字，编号，地址）和人员数量
select dept.deptno,dname,loc,total_num from 
			dept,(select deptno,count(*) total_num  from emp group by deptno) temp where 
						temp.deptno = dept.deptno;

合并查询

#合并查询
// union	和	union all
-- union all 不会去重
select ename,sal,job from emp where sal > 2500  
select ename,sal,job from emp where job = 'MANAGER'
select ename,sal,job from emp where sal > 2500 union all select ename,sal,job from emp where job = 'MANAGER'
-- union 会去重
select ename,sal,job from emp where sal > 2500 union select ename,sal,job from emp where job = 'MANAGER'

表复制和去重

#蠕虫复制
// 创建新表，把查到的数据复制到新表对应的字段
insert into my_tab01 (id, `name`, sal, job,deptno) 
      	select empno,ename,sal,job,deptno from emp;
// 自我辅助
insert into my_tab01 select * from my_tab01;

// 如何删除掉一张表的重复记录
-- 1.先创建一张临时表 my_tmp,该表的结构和my_tab02一样
create table my_tmp like my_tab02;
-- 2.把my_tab02的记录通过distinct关键字处理后把记录复制到my_tmp
insert into my_tmp select distinct * from my_tab02;
-- 3.清除掉my_tab02记录
delete from my_tab02;
-- 4.把my_tmp表的记录复制到my_tab02
insert into my_tab02 select * from my_tmp;
-- 5.drop掉临时表my_tmp
drop table my_tmp

// 复制一张表的结构
create table my_tab02 like emp;