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进行过滤操作

比较运算符	作用
> < <= >= = <> !=	大于、小于…
between … and …	在某一区间内
in (set)	显示在in列表中的值
%like% ‘…’ not like ‘…’	模糊查询 %模糊匹配
is null	判断是否为空
逻辑运算符	作用
and	与
or	或者
not	不成立

• 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

字符串相关函数

函数	作用
charset(str)	返回字符串的字符集
concat(string2 […])	连接字串
instr(string,substring)	返回substring在string中出现的位置，没有返回0
ucase(string)	转成大写
lcase(string)	转成小写
left(string,length)	从string中的左边取length个字符
length(string)	string的长度
replace(str,search_str,replace_str)	在str中用replace_str替换search_str
strcmp(string1,string2)	逐字符比较两字串的大小
substring(str,position [length])	从str的position开始，取length个字符
ltrim(string2) rtrim(string) trim	去除前端空格或后端空格

• 练习

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

数学函数

函数	作用
abs(num)	绝对值
bin(decimal_number)	十进制转二进制
ceiling(number)	向上取整
conv(number,from_base,to_base)	进制转换
floor(number)	向下取整
format(number,decimal_places)	保留小数位数
hex(decimalNumber)	转十六进制
least(number,number…)	求最小值
mod(numberator,denominator)	求余
rand([seed])	随机返回一个浮点值（0 =< v <= 1.0）

日期函数

函数	作用
current_date()	当前日期
current_time()	当前时间
current_timestamp()	当前时间戳
date(datetime)	返回datetime的日期部分
date_add(date2,interval d_value d_type)	在date2中加上日期或时间
date_sub(date2,interval d_value d_type)	在date2上减去一个时间
datediff(date1,date2)	两个日期差
timediff(date1,date2)	两个时间差
now()	当前时间
year | month | date(datetime)from_unixtime()	年月日

加密函数

函数	作用
user()	查询用户
database()	查询当前使用的数据库名称
md5(str)	为字符串算出一个md5 32的字符串，（用户密码）加密
password(str) (MySQL8废除)	从原文密码str计算并返回密码字符串，通常用于对mysql数据库的用户密码加密

流程控制函数

函数	作用
if(expr1,expr2,expr3)类似Java三元运算	如果expr1为True，则返回expr2否则返回expr3
ifnull(expr1,expr2)	如果expr1不为null，则返回expr1，否则返回expr2
select case when expr1 then expr2 when expr3 then expr4 else expr5 end;	如果expr1为TRUE，则返回expr2，如果expr2为t，返回expr4，否则返回expr5

多表查询

#多表查询

-- 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;
/ 如何删除掉一张表的重复记录
-- 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;