MySQL基础知识汇总

最新推荐文章于 2024-11-11 08:44:04 发布

only-1-bug

最新推荐文章于 2024-11-11 08:44:04 发布

阅读量269

点赞数

分类专栏： Java复习文章标签：数据库 mysql sql

本文链接：https://blog.csdn.net/Toddr/article/details/115281588

版权

Java复习专栏收录该内容

10 篇文章 1 订阅

订阅专栏

1. 查询

sql语句不区分大小写，只要拼写正确怎么都能查

1.1 简单查询

/*基本查询语句*/
select 字段名1,字段名2,字段名3,... from 表名;
select name,age,form student;
/*查询时字段可以参与数学运算*/
select age*12,form student;
/*给查询结果列重命名*/
select 字段名1,字段名2,字段名3 as 重命名 from 表名;
select name,age as Ages form student; /*as关键字可以省略*/

1.2 条件查询

select 字段,字段… form 表名 where 条件;

BETWEEN 1100 AND 3000 包含边界值,且必须左小右大

/*查询工资在1000-3000的员工*/
SELECT ENAME,SAL FROM emp WHERE SAL BETWEEN 1100 AND 3000;

null和not null

/*查询津贴为null的员工*/
SELECT ENAME,COMM FROM emp WHERE COMM IS NULL;
/*查询津贴不为null的员工*/
SELECT ENAME,COMM FROM emp WHERE COMM IS NOT NULL OR COMM=0;

and 和 or

/*查询岗位是manager和salesman的员工*/
SELECT ENAME,JOB FROM emp WHERE JOB='manager' or JOB='salesman';
/*查询薪资大于1000且部门编号是20或30的员工，当运算符优先级不确定时，加小括号*/
SELECT ENAME,SAL,DEPTNO FROM emp WHERE SAL>1000 AND (DEPTNO=20 OR DEPTNO=30)

in

/*查询岗位是manager和salesman的员工*/
SELECT ENAME,JOB FROM emp WHERE JOB IN('salesman','manager')；

like模糊查询

/*找出名字含有O的员工*/
SELECT ENAME FROM emp WHERE ENAME LIKE  '%o%'
/*找出名字第二个字母是A的员工*/
SELECT ENAME FROM emp WHERE ENAME LIKE  '_A%'
/*找出名字最后一个字母是T的员工*/
SELECT ENAME FROM emp WHERE ENAME LIKE  '%T'

去重 distinct

select distinct job from emp

1.3 排序

ORDER BY

默认升序，acs表升序，desc表降序
多个字段排序，条件越靠前主导作用越大

/*按照工资升序，找出员工名和薪资*/
SELECT ENAME,SAL FROM emp ORDER BY SAL
/*按照工资降序，当工资相同时按照首字母升序，找出员工名和薪资*/
SELECT ENAME,SAL FROM emp ORDER BY SAL DESC,ENAME ASC
/*查询岗位是salesman的员工，并按照薪资降序排列*/
SELECT ENAME,JOB,SAL FROM emp WHERE JOB='SALESMAN' ORDER BY SAL DESC

2. 分组

2.1 分组函数

分组函数自动忽略NULL,分组函数不能直接用在where子句中

count(*)和count(字段)区别：count(*) 统计总记录条数， count(字段) 统计字段中不为NULL的数据条数

sum

/*查询员工工资总和*/
SELECT  SUM(SAL)  FROM emp

max

/*查询最高工资*/
SELECT  MAX(SAL)  FROM emp

min

/*查询最低工资*/
SELECT min(SAL)  FROM emp

avg

/*查询平均工资*/
SELECT avg(SAL)  FROM emp

count

/*查询员工总人数*/
SELECT COUNT(ENAME) FROM emp 
/*查询工资高于平均工资员工总人数*/

2.2 group by 和 having

group by ：
- 按照某个或某些字段进行分组,一般和分组函数一起使用
- 当一条SQL语句没有group by，整张表的数据自成一组
- 当一条语句中有group by, select后面只能跟分组函数和参与分组的字段
单个字段分组

/*找出每个工作岗位的最高薪资*/
SELECT MAX(SAL),JOB FROM emp GROUP BY JOB
/*找出每个工作岗位的平均薪资*/
SELECT AVG(SAL),JOB FROM emp GROUP BY JOB

多个字段联合分组

/*找出每个部门不同工作岗位的最高薪资*/
SELECT MAX(SAL),DEPTNO,JOB FROM emp GROUP BY DEPTNO,JOB

having：对分组知后的数据进行再次过滤

/*找出每个部门的最高薪资，要求大于2900*/
SELECT MAX(SAL),DEPTNO FROM emp GROUP BY DEPTNO HAVING MAX(SAL)>2900
SELECT MAX(SAL),DEPTNO FROM emp WHERE SAL>2900 GROUP BY DEPTNO //建议采用这种方式

/*找出每个部门的平均薪资，要求大于2000*/
SELECT AVG(SAL),DEPTNO FROM emp GROUP BY DEPTNO HAVING AVG(SAL)>2000

完整的DQL语句

select xxx from xxx where xxx group by xxx having xxx order by xxx

3. 多表

3.1 两张表

连接查询分类

内连接
- 等值连接
- 非等值连接
- 自链接
外连接
- 左外连接
- 右外连接

两张表连接查询若不加限制，得出的记录条数为两张表记录条数的乘积。此现象称为笛卡尔积现象。

/*找出每个员工的部门名称*/
SELECT	e.ename,d.dname FROM emp e, dept d WHERE e.DEPTNO = d.DEPTNO

3.1.1 内连接

3.1.1.1 等值连接 join…on

特点是：条件是等量关系

/*查询每个员工的部门名称，要求显示员工名和部门名*/
SELECT e.ENAME, d.DNAME FROM emp e,dept d WHERE e.DEPTNO = d.DEPTNO //SQL92语法
SELECT e.ENAME, d.DNAME FROM emp e JOIN dept d ON e.DEPTNO = d.DEPTNO //SQL99语法
/*查询每个员工的部门名称和部门所在地*/
SELECT e.ENAME,d.DNAME,d.LOC FROM emp e JOIN dept d ON e.DEPTNO=d.DEPTNO

语法： ... 表A (inner) join 表B on 连接条件 where ... inner可以省略，带着目的是可读性高，一眼看出链接属性为内连接
SQL99语法语法结构更清晰，表的链接条件和后续where过滤条件分离

3.1.1.2 非等值连接 join…on

特点是：连接条件中的关系是非等量关系

/*找个每个员工的工资等级，要求显示员工名、工资、工资等级*/
SELECT e.ENAME, e.SAL, s.GRADE FROM emp e JOIN salgrade s ON e.SAL BETWEEN s.LOSAL AND s.HISAL ORDER BY SAL ASC

3.1.1.3 自链接

特点：一张表看作两张表

/*找出每个员工的上级领导，要求显示员工名和对应的领导名*/
SELECT a.ENAME,b.ENAME FROM emp a JOIN emp b ON a.MGR=b.EMPNO

3.1.2 外连接

内、外链接的区别：

内连接：

假设A和B表进行链接，使用的是内连接。凡是A表和B表能够匹配上的数据均能查询出来。
AB两张表没有主副之分，两张表是平等的

外连接

假设A和B表进行链接，使用的是外连接。AB两张表中有一张是主表，一张是副表，主要查询主表中的数据，捎带查询副表。
当副表中的数据没有和主表的数据匹配上，副表自动模拟出NULL与之匹配

3.1.3 左连接

表示左边的表是主表

/*找出每个员工的上级领导，要求显示员工名和对应的领导名*/
SELECT a.ENAME,b.ENAME FROM emp a LEFT JOIN emp b on a.MGR=b.EMPNO //左连接
SELECT a.ENAME,b.ENAME FROM emp b RIGHT JOIN emp a ON a.MGR=b.EMPNO //右链接
/*找出那个部门没有员工*/
SELECT d.DNAME,e.ENAME FROM dept d LEFT JOIN emp e ON d.DEPTNO=e.DEPTNO WHERE e.EMPNO IS NULL

3.2 三张表

/*找出每个员工的部门名称以及工资等级*/
SELECT
	e.ENAME,
	d.DNAME,
	s.GRADE 
FROM
	emp e
	JOIN dept d ON e.DEPTNO = d.DEPTNO
	JOIN salgrade s ON e.SAL BETWEEN s.LOSAL 
	AND s.HISAL
/*找出每个员工的部门名称、工资等级以及上级领导*/
SELECT
	a.ENAME '员工',
	d.DNAME,
	s.GRADE,
	b.ENAME '领导' 
FROM
	emp a
	JOIN dept d ON a.DEPTNO = d.DEPTNO
	JOIN salgrade s ON a.SAL BETWEEN s.LOSAL 
	AND HISAL
	LEFT JOIN emp b ON a.MGR = b.EMPNO

4. 子查询

select语句当中嵌套select语句，被嵌套的select语句就是子查询。

select 
	..(select).
from
	..(select).
where
	..(select).

4.1 where语句中使用子查询

/*找出高于平均薪资的员工信息*/
SELECT	* FROM	emp WHERE	SAL >(	SELECT	AVG( SAL ) FROM	emp)

4.2 from语句中使用子查询

/*找出每个部门平均薪资的薪水等级*/
SELECT
	t.*,
	s.GRADE 
FROM
	( SELECT AVG(SAL) AS avg, DEPTNO FROM emp GROUP BY DEPTNO ) t
	JOIN salgrade s ON t.AVG BETWEEN s.LOSAL 
	AND s.HISAL
/*找出每个部门平均的薪水等级*/
SELECT t.DEPTNO,AVG(t.GRADE) FROM (SELECT
	e.ENAME,
	e.SAL,
	e.DEPTNO,
	s.GRADE 
FROM
	emp e
	JOIN dept d ON e.DEPTNO = d.DEPTNO
	JOIN salgrade s ON e.SAL BETWEEN s.LOSAL 
	AND s.HISAL ) t GROUP BY DEPTNO

4.3 select语句中使用子查询

/*找出每个员工所在的部门名称，要求显示员工名和部门名*/
/*内连接方式*/
SELECT
	e.ENAME,
	d.DNAME 
FROM
	emp e
	JOIN dept d ON e.DEPTNO = d.DEPTNO 
/*select嵌套子查询方式*/
SELECT e.ENAME,( SELECT d.DNAME FROM dept d WHERE d.DEPTNO = e.DEPTNO ) AS dname 
FROM
	emp e

5. union(可以将查询结果集相加)

/*找出工作岗位是salesman和manager的员工*/
/*or方式*/
SELECT
	e.ENAME,
	e.JOB 
FROM
	emp e 
WHERE
	JOB = 'salesman' 
	OR JOB = 'manager'
/*union方式*/
SELECT
	ename,
	JOB 
FROM
	emp 
WHERE
	JOB = 'salesman' UNION
SELECT
	ENAME,
	job 
FROM
	emp e 
WHERE
	job = 'manager'

6. limit

/*取出工资前5名的员工*/
SELECT e.ENAME,e.SAL FROM emp e ORDER BY SAL DESC LIMIT 0,5
/*取出工资排名第4到第9的员工*/
SELECT e.ENAME,e.SAL FROM emp e ORDER BY SAL DESC LIMIT 3,6

通用的标准分页原理

limit (pageNo - 1) * pageSize

譬如:pageNo=2,pageSize = 10，表示第二页显示十条记录，每页的记录数也为10

7. 表操作

7.1 插入数据

/*inset插入字段可以省略*/
inset into 表名(字段1,字段2,字段3...字段n) values(值1,值2,值3...值n)
/*表的复制,将查询结果当作表创建出来*/
create table 想要创建的表名 as select * from 想要复制的表名
/*表的批量插入,将查询结果插入到一张表中*/
insert into 想要插入的表(目标表) select * from 结果表

7.2 修改数据

/*update修改数据*/
update 表名 set 字段名1=值1,字段名2=值2 ... 字段名n=值n where 条件
//如果没有where条件整张表全部更新

7.3 删除数据

/*delete删除*/
delete from 表名 where 条件 //如果没有where条件整张表全部更新
/*delete删除大表,即数据截断。不可回滚，数据永久丢失*/
truncate table 表名

8. 约束

8.1 定义以及分类

定义：

在创建表的时候，给表的字段添加相应的约束，添加约束的目的是为了保证表中数据的合法性、有效性、完整性

分类：

非空约束(not null) ：约束的字段不能为NULL
唯一约束(unique)：约束的字段不能重复，但可以为NULL
主键约束(primary key)：约束的字段既不能为NULL，也不能重复
外键约束(foreign key)：…
检查约束(check) Oracle数据库有check约束，目前MySQL不支持该约束

8.2 作用

8.2.1 非空约束

非空约束(not null) ：约束的字段不能为NULL

8.2.2 唯一约束

唯一约束(unique)：约束的字段不能重复，但可以为NULL。

有表级约束和列级约束。

列级约束: 字段名字段类型 unique

表级约束: unique(字段名,字段名)

8.2.3 主键约束

约束的字段既不能为NULL，也不能重复

作用：

表的设计三范式中要求：第一范式任何一张表都应该有主键
主键值是这行记录在这张表中的唯一标识

分类

根据主键字段的字段数量划分
- 单一主键(常用，推荐)
- 复合主键(多个字段联合添加一个主键约束，违背三范式不推荐)
根据主键性质划分
- 自然主键：和业务无关的自然数，强烈推荐
- 业务主键：主键值和系统业务挂钩。例如个人信息表用身份证做主键(实际开发中不建议此方式)

8.2.3 外键约束

概念及特点：

假设t_student表中的classno字段引用t_class表中的cno字段，那么t_student表叫做子表。t_class表叫做父表。
外键值可以为NULL
外键引用其他表的字段不一定需要是主键，但是一定要具有唯一性

顺序要求：

删除数据的时候，先删除子表，再删除父表
添加数据的时候，先添加父表，再添加子表
创建表的时候，先创建父表，再创建子表
删除表的时候，先删除子表，在删除父表

外键约束语法：

foreign key(classno子表字段) references t_class父表名(cno父表字段)

9. 事务(that’s very fucking important)

9.1 事务简介

事务是一个完整的业务逻辑单元，不可再分
譬如转账，A向B转账1000。需要执行两条update语句，第一条A账户余额-1000，第二条B账户余额+1000。这两条语句必须同时成功，或者同时失败。不允许存在单条成长或失败的情况。而要保证同时成功或同时失败就需要使用数据库的事务机制
事务是为了保证数据的完整性和安全性

9.2 事务的四大特性：ACID

A：原子性：事务是最小的工作单元，不可再分。
C：一致性：事务必须保证多条DML语句同时成功或者同时失败。
I：隔离性：事务A与事务B之间具有隔离。
D：持久性：最终数据必须持久化到硬盘文件中，事务才算结束。

9.3 事务的隔离性

事务隔离性存在隔离级别，理论上隔离级别包括4个：

第一级别：读未提交(read uncommitted) 对方事务未提交，我方当前事务可以读取到对方未提交的数据。存在脏读现象(Dirty Read)
第二级别：读已提交(read committed) 对方事务提交后的数据我方可以读取到。解决了脏读现象但存在不可重复读的问题。
第三级别：可重复读(repeatable read) 解决了不可重复读问题，但是读取到的数据是幻象
第四级别：序列化读。解决了上述所有问题。但效率低下，事务无法并发操作，需要事务排队等待。

Oracle默认隔离级别是第二级别：读已提交(read committed)

MySQL默认隔离级别是第三级别：可重复读(repeatable read)

9.4 MySQL事务

MySQL事务默认自动提交。
可以使用start transaction关闭自动提交
set global tronsaction isolation level (read committed)设置全局隔离级别
select @@global.表名 isolation 查看事务的全局隔离级别

10. 视图

10.1 创建/删除视图

create view myview as select empno,ename from emp; //创建
drop view myview //删除

10.2 视图对数据的影响

对视图进行增删改查，会通过视图影响原表的数据

10.3 视图的作用

视图可以隐藏表的实现细节。保密级别较高的系统，数据库只对外提供相关视图，Java程序员支队视图进行CRUD。

11. 数据库设计三范式

11.1 什么是设计范式？

设计表的依据。按照三范式设计的表不会出现数据冗余

11.2 三范式

第一范式：任何一张表都应该有主键，并且每一个字段原子性不可再分。

第二范式：建立在第一范式之上，所有非主键字段，完全依赖主键不能产生部分依赖。(多对多关系，三张表，两张角色表，一张关系表，关系表两个外键)

第三范式：建立在第二范式基础上。所有非主键字段直接依赖主键，不能产生传递依赖（一对多，两张表，多的表加外键）

实际开发过程中，以满足客户的需求为主，有时候会拿冗余换执行速度

关系表设计

一对一：两种方式。第一种主键共享。第二种外键添加唯一约束

一对多：两张表，多的表加外键

多对多：三张表，两张角色表，一张关系表，关系表两个外键

12. 索引

12.1 为什么要使用索引？

通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
可以大大加快数据的检索速度（大大减少的检索的数据量）, 这也是创建索引的最主要的原因。
帮助服务器避免排序和临时表。
将随机IO变为顺序IO
可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

12.2 怎么创建索引？

1.添加PRIMARY KEY（主键索引）

ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

2.添加UNIQUE(唯一索引)

ALTER TABLE `table_name` ADD UNIQUE ( `column` )

3.添加、删除INDEX(普通索引)

ALTER TABLE `table_name` ADD INDEX index_name ( `column` ) //写法一
create index 索引名称 on 表名(字段名)
drop index 索引名称 on 表名

4.添加FULLTEXT(全文索引)

ALTER TABLE `table_name` ADD FULLTEXT ( `column`)

5.添加多列索引

ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

另外：

主键和具有unique约束的字段会自动添加索引。
因此根据主键查询效率较高。尽量根据主键检索

模糊查询的时候，第一个通配符使用的是%，这个时候索引是失效的

only-1-bug

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录