MySQL数据库基础知识点总结，按照一定格式存储数据
一、概念
-
数据库：DataBase，简称DB。按照一定格式存储数据的一些文件的组合顾名思义: 存储数据的仓库，实际上就是一堆文件。这些文件中存储了具有特定格式的数据。
-
数据库管理系统：DataBaseManagement，简称DBMS。数据库管理系统是专门用来管理数据库中数据的，数据库管理系统可以对数据库当中的数据进行增删改查。
- 常见的数据库管理系统:
MySQL、 OracleMS SqlServer、 DB2、 sybase等
- 常见的数据库管理系统:
-
SQL: 结构化查询语言程序员需要学习SQL语句，程序员通过编写SQL语句，然后DBMS负责执行SQL语句，最终来完成数据库中数据的增删改查操作。
-
以上三者关系：
DBMS --(执行)--> SQL --(操作)--> DB
二、基础
-
在Windows操作系统中，使用命令来启动和停止MySQL服务：
net stop MySQL; net start MySQL; #其他服务的启停也可以使用以上命令，修改服务名称即可 -
使用客户端登录MySQL数据库：（前提：MySQL安装了，服务启动了）
使用bin目录下的mysql.exe命令来连接mysql数据库服务器
-
本地登录（显示编写密码）：
mysql -uroot -p123 #root是用户名，123是密码 -
本地登录（隐藏密码）：
mysql -uroot -p #p后面不加密码直接回车
-
-
表（table）
-
数据库中是以表格的形式表示数据的
-
任何一张表都有行和列：
行（row）：被称为数据/记录。
列（column）：被称为字段。[每一个字段都有：字段名、数据类型、约束等属性]
-
SQL语句的分类
-
DQL：数据查询语言（凡是带有select关键字的都是查询语句）
select…
-
DML：数据操作语言（凡是对表当中的数据进行增删改的都是DML）
insert：增
delete：删
update：改
！注意：DML主要是操作表中的数据data。
-
DDL：数据定义语言（凡是带有create、drop、alter的都是DDL）
create：新建，等同于增
drop：删除
alter：修改
！注意：DDL主要是操作表结构。
-
TCL：事务控制语言
commit：事务提交
rollback：事务回滚
-
DCL：数据控制语言
grant：授权
revoke：撤销权限
……
-
-
导入.sql数据：
source D:\document\mysql\node.sql #路径中不要有中文 -
MySQL常用命令
*以下命令不区分大小写，且命令要有";"才能执行
退出mysql：
exit查看mysql中有哪些数据库：
show databases; #以英文分号结尾 #mysql默认自带了4个数据库选择使用某个数据库：
use 数据库名;创建数据库：
create database 数据库名;查看某个数据库下的表：
show tables;查看表中的数据：
select * from 表名;查看表的结构，不看表的数据：
desc 表名; # “describe 表名;” 的缩写查看MySQL数据库的版本号：
select version();查看当前使用的数据库：
select database();
-
增删改查又叫做CRUD
Create
Retrive
Update
Delete
三、查询
（一）单表查询
1、简单查询
- 查询一个字段：
select 字段名 from 表名;
- 查询多个字段：
select 字段1,字段2,... from 表名;
- 查询所有字段：
select * from 表名; #这种方式效率低、可读性差，在实际开发中不建议使用
select 所有字段名 from 表名;
- 给查询的列起别名：
select 字段名 (as) 别名 from 表名;
#as可以省略，原表列名不变，只是将查询的字段显示为别名
#如果起的别名有空格，可以用单引号or双引号将别名括起来
- 对查询的字段进行运算操作：
select 字段表达式 from 表名;
#比如 “select sal*12 as '年薪' from emp;”
注意：在所有的数据库中，字符串统一使用单引号括起来。（单引号是标准，双引号在Oracle数据库中用不了，在MySQL中可以使用）
select后面可以跟某个表的字段名(可以等同看做变量名)，也可以跟字面量/字面值(数据)。
2、条件查询
条件查询需要用到where语句，where必须放到from语句表的后面
select 字段1，字段2，... from 表名 where 条件;
| 运算符 | 说明 |
|---|---|
| = | 等于 |
| <>或!= | 不等于 |
| < | 小于 |
| <= | 小于等于 |
| > | 大于 |
| >= | 大于等于 |
| between...and... | 两个值之间，等同于>=and<=（要遵循左小右大，闭区间） |
| is null | 为null（is not null表示不为空）（null不能用=进行衡量） |
| and | 并且 |
| or | 或者 |
| in | 包含，相当于多个or（not in表示不在这个范围内）（in后面加的是具体的值，不是区间） |
| not | not可以取非，主要用在is或in中 |
| like | like称为模糊查询，支持%或下划线匹配 %：匹配任意个字符 下划线：一个下划线只匹配一个字符 \为转义字符 |
and和or的优先级：and > or。如果想让or先执行，需要加()。
3、排序
-
单字段排序：
select 字段 from 表名 order by 字段; #默认升序 select 字段 from 表名 order by 字段 desc; #指定降序 select 字段 from 表名 order by 字段 asc; #指定升序 -
多字段排序：
select 字段 from 表名 order by 字段a (desc/asc), 字段b (desc/asc); #先按照字段a进行排序，序号相同的再按照字段b进行排序 -
根据字段位置进行排序：
select 字段 from 表名 order by 2; #2表示第2列，按照第2列进行排序 #不建议使用这种方式，列的顺序会改变，不健壮 -
条件查询+排序：
select ... from ... where ... order by ...; #关键字顺序不能变 #执行顺序：from、where、select、order by
4、数据处理函数/单行处理函数
- 单行处理函数：一个输入对应一个输出
| 函数 | 含义 |
|---|---|
| lower | 转换小写 |
| upper | 转换大写 |
| substr | 取子串 [ substr(被截取的字符串, 起始下标, 截取的长度) ] |
| length | 取长度 |
| trim | 去空格 |
| str_to_date | 将字符串varchar类型转换成date类型 |
| date_format | 格式化日期。将date类型转换成具有一定格式的varchar字符串类型。 格式：date_format(日期类型数据, '日期格式') 这个函数通常使用在查询日期方面。设置展示的日期格式。 |
| format | 设置千分位。 format(数字, '格式') |
| round | 四舍五入 |
| rand | 生成0~1的随机数 |
| ifnull | 可以将null转换成一个具体值[ ifnull(字段, 如果为null被当作哪个值) ] |
| concat | 进行字符串的拼接 |
| case...when...then...when...then...else...end | 当怎样就怎么做，当怎样就怎么做，其余情况怎么做 |
实例：
select lower(name) as name from student; select substr(name,1,1) as name from student; #将会输出所有name的第1个字符 select name from student where substr(name,1,1)='A'; #相当于：select name from student where name like 'A%'; select concat(upper(substr(name, 1, 1)), substr(name, 2, length(name)-1)) as result from student; #将name字段的数据首字母大写 select round(1236.567, 2) as result form student; #round(数据, 保留小数位数)，生成的结果行数为student表的行数 select name,job,sal as oldsal,(case job when 'MANAGER' then sal*1.1 when 'SALEMAN' then sal*1.5 else sal end) as newsal from emp;*在所有数据库中，只要有null参与的数学运算，结果就为null
-
MySQL的日期格式
符号 含义 %Y 年 %m 月 %d 日 %h 时 %i 分 %s 秒
5、分组函数/多行处理函数
- 多行处理函数：输入多行，输出一行。
| 函数 | 含义 |
|---|---|
| count | 计数 |
| sum | 求和 |
| avg | 平均值 |
| max | 最大值 |
| min | 最小值 |
实例：
select max(sal) from emp;
-
分组函数在使用的时候必须先进行分组，然后才能使用。如果没有对数据进行分组，默认整张表为一组。
-
注意事项：
-
分组函数自动忽略NULL，你不需要提前对null进行处理。
-
count(*)和count(具体字段)的区别：
count(*)：统计表中的总行数。（只要一行数据中有一列不为NULL，则这行数据有效）
count(具体字段)：表示统计该字段下所有不为NULL的元素的总数。
-
分组函数不能直接使用在where子句中。
-
6、分组查询（重点！！！）
-
分组查询语法
select ... from ... group by ...
实例：
#找出每个工作岗位的工资和 select job,sum(sal) from emp group by job;#找出每个部门，不同工作岗位的最高薪资 #技巧：两个字段联合成一个字段看 select deptno,job,max(sal) from emp group by deptno,job;
-
重要结论：
在一条select语句中，如果有group by语句的话，select后面只能跟：参加分组的字段、分组函数。其他的不能跟。
-
having
-
having可以对分完组后的数据再次进行过滤。
-
having不能单独使用，不能代替where，只能和group by搭配使用。
#找出每个部门最高薪资，并显示最高薪资大于3000的 select deptno,max(sal) from emp group by deptno having max(sal)>3000; #或者先使用where过滤 select deptno,max(sal) from emp where sal>3000 group by deptno; -
优化策略：where和having，优先选择where。
-
-
执行顺序
select ... from ... where ... group by ... having ... order by ...
#以上关键字只能按照这个顺序来，不能颠倒
以上语句的执行顺序：from、where、group by、having、select、order by
实例：
#找出每个岗位的平均薪资，要求显示平均薪资大于1500的，除MANAGER岗位之外，要求按照平均薪资降序排 select job,avg(sal) as avgsal from emp where job <> 'MANAGER' group by job having avg(sal)>1500 order by avgsal desc;
7、查询结果去重：distinct
select distinct 字段 from 表名;
-
distinct只能出现在所有字段的前面
-
distinct出现在两个字段之前，则表示两个字段联合起来去除
实例：
#统计工作岗位数量 select count(distinct job) from emp;
（二）连接查询
*** 重点！！！**
1、连接查询
-
连接查询分类：
根据语法的年代分类：SQL92、SQL99（重点学习SQL99）
根据表连接的方式分类：内连接（等值连接+非等值连接+自连接）、外连接（左外连接/左连接+右外连接/右连接、全连接
-
笛卡尔积现象：当两张表进行连接查询，没有任何条件限制时，最终查询结果的条数是两张表条数的乘积。（数学现象）
如何避免？
连接时加条件，满足这个条件的记录将会被筛选出来。
#各个员工对应的部门 select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno; #SQL92语法 #SQL99语法在‘内连接之等值连接’中- 此时最终查询的结果条数变少了，匹配次数没有减少，还是两张表的条数的乘积。
- 通过笛卡尔积现象得出，表的连接次数越多，效率越低，尽量避免表的连接次数。
2、内连接
等值连接
-
SQL99语法（内连接）：
select ... from a (inner) join b on a和b的连接条件 where 筛选条件 #inner可以省略，加上可读性更强，表示内连接实例：
#各个员工对应的部门 select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno; #SQL99语法（将sql92中from后面的,换成join，where换成了on） -
SQL92和SQL99对比
SQL92缺点：结构不清晰，表的连接条件和后期进一步筛选的条件，都放到了where后面，用and连接。
SQL99优点：表连接的条件时独立的，连接之后，如果还需要进一步筛选，则再往后添加where
-
在on后面是一个等值条件，所以称为等值连接
非等值连接
- 非等值连接：on后面的条件不是等值关系。
实例：
#找出每个员工的薪资等级，并显示员工名、薪资、等级 select e.ename, e.sal, s.grade from emp e (inner) join salgrade s on e.sal between s.losal and s.hisal;
自连接
- 自连接技巧：一张表看作两张表。
实例：
#查询员工的上级领导，要求显示员工名和对应的领导名 select a.ename as '员工名', b.ename as '领导名' from emp a join emp b on a.mgr=b.empno; #将一张表看成两张表
3、外连接
-
外连接与内连接的区别：内连接中，连接的两张表没有主次关系，平等的；在外连接中，两张表连接，产生主次关系。（主要看join前面有无right/left来区分）
-
带有right的是右外连接，又叫做右连接。
带有left的是左外连接，又叫做左连接。
任何一个右连接都有左连接的写法。
任何一个左连接都有右连接的写法。
-
外连接的查询结果条数一定是>=内连接的查询结果条数
实例：
#各个员工对应的部门，同时将没有员工对应的部门也显示出来 select e.ename,d.dname from emp e right (outer) join dept d on e.deptno=d.deptno; #右外连接
- 这里的right：表示将join关键字右边的这张表看成主表，主要是为了将这张主表的数据全部查询出来，捎带着关联查询左边的表emp。
- outer可以省略，加上可读性就强一些，表示外连接。
4、多表连接
-
语法：
select ... from a join b on a和b的连接条件 join c on a和c的连接条件 join d on a和d的连接条件 ...- 一条SQL中，内连接和外连接可以混合，都可以出现。
实例：
#找出每个员工的部门名称以及工资等级，还有上级领导，要求显示出员工名、领导名、部门名、薪资、薪资等级 select e.ename, l.ename, e.sal, 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 left join emp l on e.mgr=l.empno;
（三）子查询
- 子查询：select语句中嵌套select语句，配嵌套的select语句称为子查询。
在where语句中使用子查询
实例：
# 找出比最低工资高的员工名字和工资 select ename，sal from emp where sal > (select min(sal) from emp);
在from语句中使用子查询
- from后面的子查询，可以将子查询的查询结果当作一张临时表。
实例：
#找出每个岗位的平均工资的薪资等级 select t.*, s.grade from (select job, avg(sal) as avgsal from emp group by job) t on t.avgsal between s.losal and s.hisal; #本站文章为和通数据库网友分享或者投稿，欢迎任何形式的转载，但请务必注明出处. 同时文章内容如有侵犯了您的权益，请联系QQ：970679559，我们会在尽快处理。