MySQL数据库(2)——DML、视图、函数

-- 向表中添加字段信息
INSERT INTO student(id,name,age) VALUES(1,'张三',20);
-- 按照默认顺序添加字段信息可以不用指明要添加的字段名
INSERT INTO student  VALUES(2,'张三',20);
-- 一条语句向表中批量添加数据
INSERT INTO student (id,name,age) VALUES
	(3,'李四',18),
	(4,'王五',18),
	(5,'赵六',20);

2.2 文件加载

/**
	以文件的形式将数据加载到表中，
  文件中的字段必须与表中的字段相匹配
	默认的字段分隔符是\t 如果不是\t ,则需要进行明确制定
*/
LOAD DATA INFILE 'F:\StudentInfo.txt' INTO TABLE student
FIELDS TERMINATED by ','
LINES TERMINATED by '\r\n';

3、修改数据

-- 更新数据
UPDATE student set age =20;
UPDATE student set age=30 WHERE id=1;
UPDATE student set age=40 WHERE id>3;
-- 同时更新表中多个字段的数据内容
UPDATE student set age=50,name='八嘎' WHERE	id>4;

4、删除数据

-- 删除表中指定条件的数据
DELETE FROM student WHERE id<2;
-- 删除全表数据
DELETE FROM student;
-- 清空表中数据
TRUNCATE student;

5、查询数据

5.1 DQL

基本关键字及其说明

/*
	select 		        查询字段
	from			    查询表/视图
	join			    多表连接
	where			    按条件查询、过滤
	group by	        分组查询
	having		        用于分组后进行过滤条件的筛选
	order by [asc/desc] 按照升序/降序进行排序，默认升序
	limit			    分页：从某条数据开始读取多少条数据
*/

着重号

字段名或表名尽量不要使用数据库中的关键字
如果一定要使用，需要使用着重号（也叫反引号``）来进行着重标明

 select `name` from emps;

SQL简单查询

查询员工表中所有列所有行

# 在实际开发中，不建议在SQL中使用 *号
select * from emps;

查询部分字段

select ename,job,sal from emps;

查询所有管理人员

select ename,job,sal from emps where job='MANAGER';

5.2 别名查询

在查询某个字段或某张表时，我们可以给这个字段或这张表取一个别名，这种查询就叫别名查询，一旦字段/表有了别名,原字段名/表名直接失效。
使用别名查询时，需要用到一个关键字叫 as ，但这个关键字在书写时可以省略(但不建议)

#对员工表起别名
select empno,ename,job,mgr,hiredate,sal,comm,deptnp from emps as e;
#为了验证表的别名已经起作用了，我们可以把别名加到字段的前面，作为字段的前缀
select emps.empno,ename,job,mgr,hiredate,sal,comm,deptnp from emps as e;
#上面的语句会报错，因为 emps表已经被改为了 e 了，所以要修改为如下语句
select e.empno,ename,job,mgr,hiredate,sal,comm,deptnp from emps as e;

5.3 去重查询

某些场景下，我们希望查询出来的数据是不重复的，可以使用 distinct 关键字来实现

/**distinct会将全表进行扫描，再把去重后的数据展示出来，
在处理大量信息时效率不高，可使用 子查询 实现去重*/
# 查询员工表中的职位数据(不去重)
select job from emps;
# 查询员工表中的职位数据(去重)
select distinct job from emps;

5.4 单表查询

算数运算符

-- 加(+)，减(-)，乘(*)，除(/ 或 div)，模(% 或 mod)
#筛选出 部门编号 是奇数的员工数据
select ename,job,sal,deptnp from emps where deptno%2!=0;
#筛选出 eid 除以2后等于1的数据
select ename,job,sal,deptnp from emps where deptno div 2=1;

比较运算符

-- 等于(=)，大于(>)，大于等于(>=)，小于(<)，小于等于(<=)，不等于(!= 或 <>)
# 查询基本工资不等于 10000 的数据
select * from emps where sal > 10000;
# 查询基本工资等于 null 的数据
select * from emps where salary = null;
select * from emps where salary is null;

逻辑运算符

-- 与(&&,and)，或(||,or)，非(not)
# 查询职位为普通职员(CLERK)，并且在 1985 年1月1日 以后出生的员工
select * from emps where job='CLERK' and birthday > '1985-01-01
00:00:00';
# 查询职位为普通职员(CLERK)，并且在 1985 年1月1日 以后出生的员工
select * from emps where job='CLERK' && birthday > '1985-01-01 00:00:00';
# 查询职位是SALESMAN或CLERK的员工
select * from emps where job='CLERK' or job='SALESMAN';
# 查询职位是SALESMAN或CLERK的员工
select * from emps where job='CLERK' || job='SALESMAN';

范围和集合

-- between...and...
# 查询薪资在1400-2000之间的员工
select * from emps where sal between 1400 and 3000;
-- in / not in
# 查询10,20部门的员工
select * from emps where deptno [not] in(10,20);

模糊查询

-- % :0-n个任意字符
# 查询名字中 有字符A 的员工
select * from emps where ename like '%A%';
# 查询名字中 以A开头 的员工
select * from emps where ename like 'A%';
-- _ ：一个任意字符
# 查询名字中有一个字符不确定的员工
select * from emps where ename like 'ALL_N';

统计查询

-- 和(sum)，平均数(avg)，记录数(count)，最大值(max)，最小值(min)
# 查询10部门所有员工的平均薪资
select avg(sal) as avgsal from emps where deptno=10;
# 查询20部门一共有多少人
select count(*) as empall from emps where deptno=20;
# 查询30部门的最高薪资
select max(sal) from emps where deptno=30;

分页查询

/**
语法: limit offset,pagecount;
        offset：分页的起始偏移量
        pagecount：每页显示的记录数
*/
# 从第1条数据开始查询，查2条
select * from emps limit 0,2;
# 结果每页3条 查找第n页的数据
# offset=(当前页码-1)*每页显示记录数
select * from emps limit (page-1)*pagecount,pagecount;

分组查询

-- 分组不聚合
# 按deptno分组查询信息，不使用聚合函数
select ename,job,sal,deptno from emps GROUP BY deptno;
-- 分组聚合
# 按deptno分组，查询每个部门总薪资
select ename,job,sum(sal),deptno from emps GROUP BY deptno;
-- 多字段分组
# 按deptno和job分组，查询每个部门每个职位的总薪资
select ename,job,sum(sal),deptno from emps GROUP BY deptno,job;
-- 按聚合后条件查询（having）
# 按deptno和job分组，查询每个部门每个职位的总薪资大于5000的信息
select sum(sal) as saldept,deptno from emps GROUP BY deptno,job having saldept>5000;

排序查询

-- 单字段升序
# 按薪资升序排序
select * from emps order by sal [asc];
-- 单字段降序
# 按薪资降序排序
select * from emps order by sal desc;
-- 多字段排序
# 按薪资升序排序，如果薪资相同按奖金(comm)降序排序
select * from emps order by sal,comm desc;

5.5 多表查询

笛卡尔积

-- 多张表同时查询时，总记录条数 就是这几张表中 记录数的乘积
-- 查询过程中避免出现 笛卡尔积，否则数据库性能会受到影响
# 同时查询emps表和dept表数据
select * from emps,dept;

关联查询

-- where
# 查询员工表的部门编号和部门表的部门编号一致的信息
select * from emps,dept where emps.deptno=dept.deptno;
select * from emps as e,dept as d where e.deptno=d.deptno;
-- on:字段名可以不一样
# 查询员工表的部门编号和部门表的部门编号一致的信息，如果on中的连接字段名不一致可以不加表
名
select * from emps e
    join dept d
    on e.deptno=d.deptno;

-- using：字段名一致
## 查询员工表的部门编号和部门表的部门编号一致的信息,两个表的连接字段如果一致可以使用 using
select * from emps e
join dept
using (deptno);

内连接

查询符合条件的所有数据

外连接

左外连接

以左表为基准表，如果右表中没有数据和左表匹配，则以空值（ null 值）为填充

-- 以员工表为主表查询数据
select * from emps e
left [outer] join dept d
on e.deptno=d.deptno;

右外连接

以右表为基准表，如果左表中没有数据和右表匹配，则以空值（ null 值）为填充

-- 以部门表为主表查询数据
select * from emps e
right [outer] join dept d
on e.deptno=d.deptno;

全外连接

在 MySQL 中不支持全外连接（ full join ），但是我们可以使用 union 关键字来代替，它代表联合查询的意思。把左外连接和右外连接联合起来就形成了全外连接了

-- 全外连接
select column_list
from 表1 left join 表2
[on 条件]
union
select column_list
from 表1 right join 表2
[on 条件];

自链接

-- 当表 1 和表 2 是同一张表时，只是用了别名的方式来虚拟两张表进行关联查询时，就形成了自连接
-- 在自连接中也可以使用内连接和外连接
# 以员工表做自连接，查询每个员工及他们的领导信息
select e1.ename,e1.job,e1.mgr,e1.sal,e2.ename,e2.job,e2.sal from emps as e1
left join emps as e2
on e1.mgr=e2.empno;

5.6 子查询

where型子查询

-- where型：把内层SQL的查询结果作为外层SQL的查询 条件
# 查询薪资大于2900的部门名和部门所在城市
select dname,loc from dept where deptno in
(select distinct deptno from emps where sal>2900);

from型子查询

-- from型：内层SQL语句查询的结果作为外层SQL查询的 数据
# 查询每个部门员工高于自己部门平均薪资的人数和平均薪资
select count(*) as highsalcount,a.salavg,e.deptno from emps as e
left join
(select deptno,avg(sal) as salavg from emps group by deptno) as a
on e.deptno=a.deptno
where sal>a.salavg
group by deptno;

复制表子查询

-- 1、按照已有表结构新建表
 create table emps_new like emps;
/**
   2、按照已有表的表结构和表中数据新建表，
      按照子句中查询的字段创建新表，
      按照子句中的查询条件复制数据
*/
create table emps_data as (select ename,sal,deptno from emps where deptno='20');
-- 3、从已有表中复制数据到另一个表
insert into emps_new select * from emps where deptno='30';
-- 4、复制表结构和数据的同时也可以直接在新表定义新字段
create table emps_new2 (id int(11) primary key auto_increment)
as (select empno,ename,sal,deptno from emps where deptno='20');

6、函数

数学函数

主要用于处理数字

-- ln(2) x的自然对数
SELECT LN(2);
-- PI() 圆周率
SELECT PI();
-- abs(x) 返回x的绝对值
SELECT ABS(-1);
-- round(x) 四舍五入
SELECT ROUND(1.23456);
-- runcate(x,y) 数值 x 保留到小数点后 y 位的值（不会进行四舍五入）
SELECT TRUNCATE(1.23456,3);
-- ceil(x) / ceiling(x) 向上取整
SELECT CEIL(1.3);
SELECT CEILING(1.3);
-- floor(x) 向下取整
SELECT FLOOR(1.5);
-- exp(x) e的x次方
SELECT EXP(3);
-- log(x) 以e为底的对数
SELECT LOG(20.085536923188);
-- pow(x,y) / power(x,y) x的y次方
SELECT POW(2,3);
SELECT POWER(2,3);
-- sqrt x的平方根
SELECT SQRT(25);
-- mod(x,y) 取模
SELECT mod(5,2);
-- rand() 0-1之间的随机数
SELECT RAND();
-- sign(x) x 的符号，x 是负数、0、正数分别返回 -1、0 和 1
SELECT SIGN(-10);
-- greatest(expr1,expr2,...) 列表中的最大值
SELECT GREATEST(3,12,34,8,25);
-- least(expr1,expr2,...) 列表中的最小值
SELECT LEAST(3,12,34,8,25);

聚合函数

-- AVG(col) 返回指定列的平均值
-- COUNT(col) 返回指定列中非NULL值的个数
-- MIN(col) 返回指定列的最小值
-- MAX(col) 返回指定列的最大值
-- SUM(col) 返回指定列的所有值之和
-- GROUP_CONCAT(col) 返回由属于一组的列值连接组合而成的结果

字符串函数（部分）

-- ASCII(char) 返回字符的ASCII码值
SELECT ASCII('a');
-- LENGTH(s)返回字符串str中的字符数
SELECT LENGTH('hello');
-- CONCAT(s1,s2...,sn) 将s1,s2...,sn连接成字符串
SELECT CONCAT('hello ','world ','!');
-- CONCAT_WS(sep,s1,s2...,sn)将s1,s2...,sn连接成字符串，并用sep字符间隔
SELECT CONCAT_WS('#','hello ','world ','!');

日期和时间函数

-- CURDATE()或CURRENT_DATE() 返回当前的日期
SELECT CURDATE();
SELECT CURRENT_DATE();
-- CURTIME()或CURRENT_TIME() 返回当前的时间
SELECT CURTIME();
SELECT CURRENT_TIME();
-- NOW() 返回当前的日期和时间
SELECT NOW();
-- YEAR(date) 返回日期date的年份(1000~9999)
SELECT YEAR((NOW());

格式化函数

-- DATE_FORMAT(date,fmt) 依照字符串fmt格式化日期date值
/*
%Y:4位年份 %y:2位年份
%M:月份英文标识 %m:月份数字
%D：
*/
SELECT DATE_FORMAT(NOW(),'%W,%D %M %Y %r');
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d');
SELECT DATE_FORMAT(19990330,'%Y-%m-%d');
SELECT DATE_FORMAT(NOW(),'%h:%i %p');

类型转换函数

-- 它可以把一个值转化为指定的数据类型。类型有：
-- BINARY,CHAR,DATE,TIME,DATETIME,SIGNED,UNSIGNED
SELECT CAST(NOW() AS CHAR);

条件判断函数

-- 其中包括IF语句、IFNULL语句、CASE...WHEN语句等
-- CASE WHEN 相当于 if...else...
select empname,salary,
case when salary>100000 then "high"
when salary>10000 and salary<=100000 then "middle"
when salary>1000 and salary<=10000 then "low"
else "low爆了"
end as sallevel
from employee;

-- CASE WHEN 相当于 switch...case...
select empname,
case empname when "xx" then "very beautiful"
when "xxx" then "beautiful"
when "xxx" then "kawayi"
else "pretty"
end as namedesc
from employee;

-- IF(test,t,f) 如果test是真，返回t；否则返回f
SELECT IF(100>10,'right','wrong')
SELECT IF(100<10,'right','wrong')
-- IFNULL(arg1,arg2) 如果arg1不是空，返回arg1，否则返回arg2
SELECT IFNULL('happy','hello');
SELECT IFNULL(NULL,'hello');
-- NULLIF(arg1,arg2) 如果arg1=arg2返回NULL；否则返回arg1
SELECT NULLIF('hello','hello1')
SELECT NULLIF('hello','hello')

系统信息函数

-- VERSION() 返回MySQL服务器的版本
SELECT VERSION();
-- USER()或SYSTEM_USER() 返回当前登陆用户名
SELECT USER();
-- CONNECTION_ID() 返回当前客户的连接ID
SELECT CONNECTION_ID();
-- DATABASE() 返回当前数据库名
SELECT DATABASE();
-- FOUND_ROWS() 返回最后一个SELECT查询进行检索的总行数
SELECT FOUND_ROWS();
-- BENCHMARK(count,expr) 将表达式expr重复运行count次结果永远是0 主要看运行时间
SELECT BENCHMARK();

加密函数

-- AES_ENCRYPT(str,key)
-- 返回用密钥key对字符串str利用高级加密标准算法加密后的结果，调用AES_ENCRYPT的结果
是一个二进制字符串，以BLOB类型存储
SELECT AES_ENCRYPT('root','key');
-- AES_DECRYPT(str,key) 返回用密钥key对字符串str利用高级加密标准算法解密后的结
果
SELECT AES_DECRYPT(AES_ENCRYPT('root','key'),'key');
-- ENCODE(str,key) 使用key作为密钥加密字符串str，调用ENCODE()的结果是一个二进
制字符串，它以BLOB类型存储
SELECT ENCODE('xufeng','key')
-- DECODE(str,key) 使用key作为密钥解密加密字符串str
SELECT DECODE(ENCODE('xufeng','key'),'key');#加解密放在一起
-- ENCRYPT(str,salt) 使用UNIXcrypt()函数，用关键词salt(一个可以惟一确定口令的
字符串，就像钥匙一样)加密字符串str
SELECT ENCRYPT('root','salt');
-- MD5() 计算字符串str的MD5校验和
SELECT MD5('123456');
-- PASSWORD(str) 返回字符串str的加密版本，这个加密过程是不可逆转的，和UNIX密码
加密过程使用不同的算法
SELECT PASSWORD('123456');
-- SHA() 计算字符串str的安全散列算法(SHA)校验和
SELECT SHA('123456');

7、视图

在数据库中，view是根据执行的 SQL 语句的结果所形成的一张虚拟表

view和table的在使用方式上完全相同，但是视图是不占用物理空间，它是一张逻辑表

视图只限于数据的查询

优点

让用户或程序员只能看到他们所需要的数据，而不需要把表中所有的信息都暴露出来。

增强数据的安全性。
我们可以把经常需要做多表查询的这些数据定义到视图中，这样做就简化了SQL查询语句的编写。

缺点

操作视图会比操作物理表慢，所以尽量避免对大型数据表创建视图
尽量不要创建嵌套视图，因为它比较耗性能
尽量在视图中只返回所需要的信息，不要在视图中使用不需要的访问表

对视图的操作同于对表的操作

标签：--,DML,SELECT,视图,查询,emps,MySQL,deptno,select
From： https://blog.csdn.net/qq_64751004/article/details/141600714