基础知识-SQL数据库部分

（240222，未更新完）

资料来源

零基础33分钟学会4种数据的SQL语言 | bilibli | SQL优化
 SQL内容 | cnblogs | 我没有bug
SQL 教程

基础知识

SQL是用于存储和管理关系数据库中的数据的标准计算机语言
不区分大小写，几乎所有的关系数据库都通用

数据类型	SQL Server	Oracle	MySQL	PostgreSQL
布尔值	Bit	Byte	N/A	Boolean
整形	int	Number	int	int
浮点	float	Number	float	Numeric
定长字符串	char	char	char	char
可变长度字符串	varchar	varchar	varchar	varchar
二进制对象	binary	long	blob	binary

numeric(a,b)

SQL语句分类

DQL查询总结 | cnblogs | 我没有bug
DDL、DML、DCL总结 | cnblogs | 我没有bug

DQL:数据查询语言(带有select)
DML:数据操作语言(对数据增、删、改)
DDL:数据定义语言(对表的结构改变)
TCL:事务控制语言
DCL:数据控制语言

-- 本手册数据库环境
drop table if exists dept;
create table dept(
  deptno int primary key,
  dname varchar(12) not null,
  IOC varchar(10) );
insert into dept values(1,'Development','NEWYORK');
insert into dept(deptno,dname,loc) vaIues(2,'Testing','Chicago');
insert into dept(deptno,dname) values(3,'Marketing');
drop table if exists emp；
create table emp(
  empno int primary key,
  ename char(10) not null,
  deptno int,
  hiredate date,
  sal numeric(8,2)，
  comm numeric(8,2) )；
insert into emp values(1,'Grace',1,'2000-12-02',12000.00,0)；
insert into emp values(2,'Joe',1,'2013-01-03',9100.00,0)；
insert into emp values(3,'JOhn',2,'2021·02·10',8900.00,null)；
insert into emp values(4,'Lisa',2,'2022·12·1',11000.00,null)；
insert into emp values(5,'Ben',3,'2004-09-11',8000.00,9000.00);
commit;

DQL

运算符	说明
=	等于
!=	不等于
>	大于
<	小于
>=	大于等于
<=	小于等于

书写顺序：
select
from
where
group by
having
order by
limit

执行顺序：
from
where
group by
having
select
order by
limit

简单查询

select 字段名 from 表名; --一个字段
select 字段1,字段2 from 表名; --多个字段
select 字段1,字段2...... from 表名; --所有字段
select 字段1,字段2 as abc from 表名; --用as将字段2起名abc
select 字段1,字段2 as "字 符 串" from 表名; --含有空格或中文需要用双引号
select sal*12 from 表名; --可以用数学表达式

条件查询

WHERE子句触发
AND和OR就是&&和||，and是两者都触发，or是前面不触发才触发后面条件
NOT就是不触发

select empno,ename,deptno,hiredate,sal,comm for emp where (deptno<=2 or deptno>3) and sal>15000;
select empno,ename,deptno,hiredate,sal,comm for emp where deptno!=3 and sal>15000;

模糊查询

LIKE,模糊搜索，%(百分号)匹配0个、1个、多个字符，_(下划线)匹配单个字符

select ename from emp where enme like '%a%';
select ename from emp where ename like '__a%';

排序

ORDER BY排序，asc升序，desc降序

select ename,deptno,sal from emp order by deptno desc,sal asc;

UNION和INTERSECT 合并查询和交集查询

INTERSECT查询输出结果的相同数据（交集）
UNION合并查询（合集），输入all不会去掉重复值

SELECT DISTINCT deptno
FROM emp
INTERSECT
SELECT deptno
FROM dept;

SELECT DISTINCT deptno
FROM emp
UNION
SELECT deptno
FROM dept;

SELECT DISTINCT deptno
FROM emp
UNION all
SELECT deptno
FROM dept;

分组查询

GROUP BY用于结合聚合函数，根据一个或多个列对结果集进行分组。

-- GROUP BY演示
select deptno 部门,
	max(sal) as 最高工资，
	min(sal) as 最低工资，
	avg(sal) as 平均工资，
	sum(sal) as 汇总工资，
	count(*) as 员工数，
 from emp
where deptno=1
union
select deptno 部门,
	max(sal) as 最高工资，
	min(sal) as 最低工资，
	avg(sal) as 平均工资，
	sum(sal) as 汇总工资，
	count(*) as 员工数，
 from emp
where deptno=2
union
select deptno 部门,
	max(sal) as 最高工资，
	min(sal) as 最低工资，
	avg(sal) as 平均工资，
	sum(sal) as 汇总工资，
	count(*) as 员工数，
 from emp
where deptno=3;

select deptno 部门,
	max(sal) as 最高工资，
	min(sal) as 最低工资，
	avg(sal) as 平均工资，
	sum(sal) as 汇总工资，
	count(*) as 员工数，
 from emp
group by deptno;

HAVING过滤分组，用于GROUP BY分组之后过滤输出结果，GROUP BY是在WHERE之后执行的。

select deptno,avg(sal) from emp
group by deptno
having avg(sal)>10000;

DISTINCT 查询去重

select distinct job from 表名;
select distinct dname,job from 表名; --name和job联合去重
select count(distinct job) from 表名; --统计工作岗位数量

连接查询

内连接

INNER JOIN内连接，内连接为默认方式，可以省略INNER

select ename,loc from emp,dept where emp.deptno=dept.deptno; -- 按照deptno使用WHERE子句查询
select ename,loc from emp INNER JOIN dept on emp.deptno=dept.deptno; -- 使用inner join子句
select ename,loc,e.deptno from emp e join dept d on e.deptno=d.deptno;

自连接

SELF JOIN自连接，在同一个表里，输出多个不同的结果

select e1.ename,e1.deptno from emp e1 join emp 32
  on e1.deptno=e2.deptno and e1.empno<>e2.empno; -- 同一个部门里共事的员工

select e1.ename, e2.ename, e3.ename
from emp e1, emp e2, emp e3
where e1.deptno = e2.deptno and e2.deptno = e3.deptno
and e1.empno < e2.empno and e2.empno < e3.empno; 
/*
同一个部门共事的三个员工
e1.empno < e2.empno < e3.empno确保每个员工只出现一次，避免重复。
*/

外连接

OUTER JOIN外连接，有LRFT JOIN、RIGHT JOIN、FULL OUTER JOIN，加上WHERE a.key IS NULL、WHERE a.key IS NULL OR b.key IS NULL可以将相同数据排除

insert into emp(empno,ename,deptno,sal) values(10,'Frank',4,12000.00);
insert into dept(deptno,dname) values(5,'Operation');
select e.name,d.name,e.deptno,d.deptno from emp e join dept d on e.deptno=d.deptno;
select e.name,d.name,e.deptno,d.deptno from emp e left join dept d on e.deptno=d.deptno;
select e.name,d.name,e.deptno,d.deptno from emp e right join dept d on e.deptno=d.deptno;
/*
分别在两个表插入新的数据，用内连接无法输出新插入的数据。
用左连接(left join)和右连接(right join)分别输出新数据
*/

子查询

select empno,ename from emp where empno in (3,4,5);
select empno,ename from emp where empno not in (3,4,5);

select ename,hiredate from emp where hiredate between '2013-01-01' and '2013-12-31';

IN（字段1,字段2），匹配括号里的值
BETWEEN，指定范围匹配

NOT IN注意事项

NOT IN表示不在集合中

-- 加入一个新员工后会无法输出结果
insert into dept(deptno,dname) values(5,'Operation');
select deptno,dname from dept d
  where d.deptno not in (select e.deptno from emp e);
insert into emp(empno,ename,deptno) values(10,'Frank',NULL);
select deptno,dname from dept d
  where d.deptno not in (select e.deptno from emp e);
-- 用or运算符能将结果输出
select deptno, dname from dept d
  where d.deptno not in (select e.deptno from emp) or dname = 'Operation';
-- 用LEFT JOIN子句
select d.deptno, d.dname
from dept d
left join emp e on d.deptno = e.deptno
where e.deptno is null or d.dname = 'Operation';

LIMIT

limit 5;--取前5
limit 0,5;-- 从0开始取5条记录

limit 9,3 \[9,10,11];--第四页

DML

INSERT 插入

insert into 表名(字段名1,字段名2,字段名3,字段名4) values(值1，值2，值3，值4);
insert into 表名(字段名1,字段名2,字段名3) values(值1，值2，值3),(值1，值2，值3),(值1，值2，值3);

每次insert都会增加一条记录，没有指定的字段为null
如果前面字段名都省略，相当于所有字段名都写了，那么后面values里的值要写全(不建议采用)

UPDATE 更新

select empno from emp where empno=3;
update emp set deptno=1 where empno=3;
update emp set sal=sal+1000 where empno=3;
update emp set deptno=1,sal=sal+1000 where empno=3;
update emp set sal=sal+1000 where deptno=(select deptno from deptno where dname='Development';)

DELETE 删除

delete from emp where deptno=5; --删除一行数据
truncate table emp; --删除整个表

DDL

CREATE 创建

create table 表名(
    字段名1 数据类型,
    字段名2 数据类型,
    字段名3 数据类型
);

DROP 删除

drop table 表名;
drop table 表名 if exists;

如果表不存在上面语句会报错，下面不报错

DCL

创建用户

create user '用户名'@'主机名' identified by '密码';

删除用户

drop user '用户名'@'主机名';

修改用户密码

update user set password=新密码 where user='用户名';


set password for '用户名'@'主机名'=password('新密码');

忘记密码怎么办

1.管理员权限运行cmd,net stop mysql停止MySQL服务
2.无体验方式启动MySQL,mysql --skip-grant-tables
3.打开新的cmd窗口直接mysql回车，找到存放用户的表进行修改
4.打开任务管理器，手动结束mysql进程
5.管理员权限运行cmd,net start mysql开启MySQL服务

权限管理

查询权限

show grants for '用户名'@'主机名';
授予权限#
grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';

grant select,delete on db1.student to '用户名'@'%';

grant all on *.* to '用户名'@'%';

撤销权限

revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';

数据导入导出

数据库导出

首先退出mysql,直接在windows的dos命令窗口

mysqldump 数据库名>保存地址(.sql文件) -u用户名 -p密码

//导出study数据库

mysqldump study>D:\study.sql -uroot -p123

//导出study数据库下的student表

mysqldump study student>D:\study.sql -uroot -p123

数据库导入

1.create数据库
2.use数据库
3.source .sql文件路径;

函数

AVG() - 返回平均值
COUNT() - 返回行数
MAX() - 返回最大值
MIN() - 返回最小值
SUM() - 返回总和
IFNULL()空处理函数，只要有null参与的数学运算，得到的结果都是null,2+null=null
ALL一个字段对比全部

-- 比2号员工高工资的所有员工
select ename from emp where sal > (select max(sal) from emp where deptno=2)
select ename from emp where sal > all (select sal from emp where deptno=2)

ANY集合中的任意值

-- 比比2号员工高工资的任一员工
select ename from emp where sal > (select mix(sal) from emp where deptno=2)
select ename from emp where sal > any (select sal from emp where deptno=2)

NULL空值

NULL 值代表遗漏的未知数据。
null+null是null,null-null还是null
null=null和null!=null都不会显示结果，因为null不能比较
NULL 用作未知的或不适用的值的占位符。（比如不知道这个员工的佣金，则使用null）

select 1 where null is null;
select 1 where 0 is not null;
select 1 where '' is not null;

-- 如果不用ifnull()| ISNULL() | COALESCE() | NVL() 函数，收入总额会提示NULL。
-- MySQL版本：
SELECT ename, sal, IFNULL(comm, 0) as comm, sal+ IFNULL(comm, 0) as 收入总和 FROM emp;
SELECT ename, sal, COALESCE(comm, 0) as comm, sal+ COALESCE(comm, 0) as 收入总和 FROM emp;

-- Oracle版本：
SELECT ename, sal, NVL(comm, 0) as comm, sal + NVL(comm, 0) as 收入总和 FROM emp;

-- SQL Server版本：
SELECT ename, sal, ISNULL(comm, 0) as comm, sal+ ISNULL(comm, 0) as 收入总和 FROM emp;

-- PostgreSQL版本：
SELECT ename, sal, COALESCE(comm, 0) as comm, sal+ COALESCE(comm, 0) as 收入总和 FROM emp;

约束

非空约束：not null
唯一性约束：unique
主键约束：primary key(简称PK)
外键约束：foreign key(简称FK)
检查约束：check(MySQL不支持，Oracle支持)

主键由表中的一个字段或者多字符组成，主键唯一代表表中的一条记录

例子

查询出比自己部门平均工资高的员工

SELECT * FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees WHERE department = 'YourDepartment')
    AND department = 'YourDepartment';

"employees"员工表，"salary"工资属性，"department"部门属性，"YourDepartment"部门名称

使用between查询工资在一万和一万五千之间的员工。

SELECT * FROM employees
WHERE salary BETWEEN 10000 AND 15000;

因为当地生活成本上涨，公司决定给在CHICAGO工作的员工增加10％的工资

UPDATE employees
SET salary = salary * 1.1
WHERE location = 'CHICAGO';

找出姓名最后一个字面是n或者第二字面是i的员工。

SELECT * FROM employees
WHERE SUBSTRING(name, -1) = 'n' OR SUBSTRING(name, 2, 1) = 'i';

Oracle:

SELECT * FROM employees
WHERE SUBSTR(name, -1) = 'n' OR SUBSTR(name, 2, 1) = 'i';

按照部门升序和入职日期降序员工名。

SELECT * FROM employees
ORDER BY department ASC, hire_date DESC, name;

查询2010年之前入职的姓名、工资和增加10％后的工资(updated salary)和入职时间。

SELECT name, salary, salary * 1.1 AS updated_salary, hire_date
FROM employees
WHERE YEAR(hire_date) < 2010;

Oracle、PostgreSQL:

SELECT name, salary, salary * 1.1 AS updated_salary, hire_date
FROM employees
WHERE EXTRACT(YEAR FROM hire_date) < 2010;

因为公司经营困难，解雇2010年前入职的老员工

DELETE FROM employees
WHERE YEAR(hire_date) < 2010;