CRUD

1.Insert

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

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

// 如果没有条件，整个表的数据deletetete将被删除 from 表名 [where ...]

// 基本语法 [distinct 去重]select [DISTINCT] *|{列名...} from 表名// 使用表达式计算查询列的selectt *|{列名...|表达式...} from 表名// 使用as语句selecttctt使用as语句 列名 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.192年找到时间？.1后入职员工selectttelectt * from emp where hiredate > date('1992-01-01');// 使用like操作符//// %:表示0到多个字符  _:表示单个字符-- q：如何显示首字符为s的员工姓名和工资selectt ename,sal from emp where ename like 'S%';-- q：如何显示第三个字符是大写o的所有员工姓名和工资？ ename,sal from emp where ename like '__O%';// 如何展示没有上级员工的selectt？ * from emp where mgr is null;// desc查询表结构 表名;// 使用order by子句-- q：如何按照工资从低到高的顺序，显示员工的信息selectt * from emp order by sal; -- (默认升序，desc为降序)-- q：根据部门号升序和员工工资降序排列，显示员工信息selectt * from emp order by deptno, sal desc;

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

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

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

#总统计函数-count// 回归行总数selectt count(*) | count(列名) from 表名 [where ...]// count(*)和 count(列名)/// count(*)返回符合条件的记录总数/// count(列)返回符合条件的列记录总数，但不包括null

#合计函数-sum// 返回符合where条件的行总和selectt sum(列名) from 表名 [where...]// 单行统计不包括null，只要一个数据中有一个null，就不包括在结果中

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

#合计函数-Max/Min// 回到一列最大最小值的selecttecttect Max(列)|Min(列) from 表名// 单行统计不包括null，只要一个数据中有一个null，就不包括在结果中

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

• 练习

dept表

emp表

salgrade表

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

函数

作用

charset(str)

返回字符串的字符集

concat(string2 [...])

连接字串

instr(string,substring)

在string中返回substring的位置，未返回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表的名称selectt以首字母小写的形式显示 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个部门名称、员工名称和工资？ dname,ename,sal from emp,dept where emp.deptno = dept.deptno and emp.deptno = 10;-- q：显示每个员工的姓名、工资和工资水平 ename,sal,grade from emp,salgrade where emp.sal >= salgrade.losal and emp.sal <= salgrade.hisal;

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

#多行子查询//// 子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询//// 单行子查询-- q：如何显示与SMITH相同部门的所有员工selectt？ * 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 !select ename,job,sal,deptno from emp where job in (select distinct job from emp where deptno = 10) and deptno != 10;-- 使用distinctt可能会重复

#临时表//// 查询ecshop中各类价格最高的商品-- 首先查询临时表selectt cat_id,Max(shop_price) from ecs_goods GROUP BY cat_id-- 然后用临时表和总表关联查询最终结果 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名员工的员工姓名，工资和部门号selectt 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的员工的姓名、工资和部门号？ 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';

#练习-- 搜索各部门工资高于本部门平均工资的人的信息 emp.* from emp, (select deptno,avg(sal) avg_sal from emp group by deptno) t whereemp.deptno = t.deptno and emp.sal > avg_sal;-- 搜索每个部门工资最高的人的详细信息。 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;--显示各部门的信息(包括部门名称、编号、selectt)和人员数量 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 不会去重selecttctttel 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相同的createe结构 table my_tmp like my_tab02;-- 2.把my通过distinct关键字处理tab02记录后，将记录复制到my_tmpinsert into my_tmp select distinct * from my_tab02;-- 3.清除my_tab02记录deletetete from my_tab02;-- 4.把my_tmp表的记录复制到my_tab02insertt into my_tab02 select * from my_tmp;-- 5.drop落临时表my__tmpdrop table my_tmp// 复制一张表的结构createte table my_tab02 like emp;