MySQL数据库基础知识点总结

王芃博

一、概念

• 数据库：DataBase，简称DB。按照一定格式存储数据的一些文件的组合顾名思义: 存储数据的仓库，实际上就是一堆文件。这些文件中存储了具有特定格式的数据。
  
• 数据库管理系统：DataBaseManagement，简称DBMS。数据库管理系统是专门用来管理数据库中数据的，数据库管理系统可以对数据库当中的数据进行增删改查。
  
  
  
  • 常见的数据库管理系统:
    MySQL、 OracleMS SqlServer、 DB2、 sybase等
    
  
  
• SQL: 结构化查询语言程序员需要学习SQL语句，程序员通过编写SQL语句，然后DBMS负责执行SQL语句，最终来完成数据库中数据的增删改查操作。
  
• 以上三者关系：
  DBMS --(执行)--> SQL --(操作)--> DB
  

二、基础

• 在Windows操作系统中，使用命令来启动和停止MySQL服务：
  1. net stop MySQL;
  2. net start MySQL;
  3. #其他服务的启停也可以使用以上命令，修改服务名称即可

  复制代码
• 使用客户端登录MySQL数据库：（前提：MySQL安装了，服务启动了）
  使用bin目录下的mysql.exe命令来连接mysql数据库服务器
  
  
  
  • 本地登录（显示编写密码）：
    1. mysql -uroot -p123  #root是用户名，123是密码

    复制代码
  • 本地登录（隐藏密码）：
    1. 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数据：
  1. source D:\document\mysql\node.sql  #路径中不要有中文

  复制代码
• MySQL常用命令
  

*以下命令不区分大小写，且命令要有";"才能执行
退出mysql：

1. exit

复制代码

查看mysql中有哪些数据库：

1. show databases; #以英文分号结尾
2. #mysql默认自带了4个数据库

复制代码

选择使用某个数据库：

1. use 数据库名;

复制代码

创建数据库：

1. create database 数据库名;

复制代码

查看某个数据库下的表：

1. show tables;

复制代码

查看表中的数据：

1. select * from 表名;

复制代码

查看表的结构，不看表的数据：

1. desc 表名;  # “describe 表名;” 的缩写

复制代码

查看MySQL数据库的版本号：

1. select version();

复制代码

查看当前使用的数据库：

1. select database();

复制代码

• 增删改查又叫做CRUD
  

  Create
  Retrive
  Update
  Delete
  

  
  

三、查询

（一）单表查询

1、简单查询

• 查询一个字段：
  

1. select 字段名 from 表名;

复制代码

• 查询多个字段：
  

1. select 字段1,字段2,... from 表名;

复制代码

• 查询所有字段：
  

1. select * from 表名;  #这种方式效率低、可读性差，在实际开发中不建议使用
2. select 所有字段名 from 表名;

复制代码

• 给查询的列起别名：
  

1. select 字段名 (as) 别名 from 表名;
2. #as可以省略，原表列名不变，只是将查询的字段显示为别名
3. #如果起的别名有空格，可以用单引号or双引号将别名括起来

复制代码

• 对查询的字段进行运算操作：
  

1. select 字段表达式 from 表名;
2. #比如 “select sal*12 as '年薪' from emp;”  

复制代码

注意：在所有的数据库中，字符串统一使用单引号括起来。（单引号是标准，双引号在Oracle数据库中用不了，在MySQL中可以使用）

select后面可以跟某个表的字段名(可以等同看做变量名)，也可以跟字面量/字面值(数据)。

2、条件查询

条件查询需要用到where语句，where必须放到from语句表的后面

1. select 字段1，字段2，... from 表名 where 条件;

复制代码

运算符说明=等于或!=不等于</tdtd 小于/td/trtrtd=/tdtd 小于等于/td/trtrtd/tdtd 大于/td/trtrtd>=大于等于between...and...两个值之间，等同于>=and or。如果想让or先执行，需要加()。</p></blockquote>
3、排序

• 单字段排序：
  1. select 字段 from 表名 order by 字段;  #默认升序
  2. select 字段 from 表名 order by 字段 desc;  #指定降序
  3. select 字段 from 表名 order by 字段 asc;  #指定升序

  复制代码
• 多字段排序：
  1. select 字段 from 表名 order by 字段a (desc/asc), 字段b (desc/asc);  #先按照字段a进行排序，序号相同的再按照字段b进行排序

  复制代码
• 根据字段位置进行排序：
  1. select 字段 from 表名 order by 2;  #2表示第2列，按照第2列进行排序
  2. #不建议使用这种方式，列的顺序会改变，不健壮

  复制代码
• 条件查询+排序：
  1. select ... from ... where ... order by ...;  #关键字顺序不能变
  2. #执行顺序：from、where、select、order by

  复制代码

4、数据处理函数/单行处理函数

• 单行处理函数：一个输入对应一个输出
  

[table]函数含义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当怎样就怎么做，当怎样就怎么做，其余情况怎么做

实例：

1. select lower(name) as name from student;
2. select substr(name,1,1) as name from student; #将会输出所有name的第1个字符
3. select name from student where substr(name,1,1)='A';  #相当于：select name from student where name like 'A%';
4. select concat(upper(substr(name, 1, 1)), substr(name, 2, length(name)-1)) as result from student;  #将name字段的数据首字母大写
5. select round(1236.567, 2) as result form student;  #round(数据, 保留小数位数)，生成的结果行数为student表的行数
6. 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最小值

实例：

1. select max(sal) from emp;

复制代码

• 分组函数在使用的时候必须先进行分组，然后才能使用。如果没有对数据进行分组，默认整张表为一组。
  
• 注意事项：
  
  
  
  • 分组函数自动忽略NULL，你不需要提前对null进行处理。
    
  • count(*)和count(具体字段)的区别：
    ​        count(*)：统计表中的总行数。（只要一行数据中有一列不为NULL，则这行数据有效）
    ​        count(具体字段)：表示统计该字段下所有不为NULL的元素的总数。
    
  • 分组函数不能直接使用在where子句中。
    
  
  

6、分组查询（重点！！！）

• 分组查询语法
  1. select ... from ... group by ...

  复制代码

实例：

1. #找出每个工作岗位的工资和
2. select job,sum(sal) from emp group by job;

复制代码

1. #找出每个部门，不同工作岗位的最高薪资
2. #技巧：两个字段联合成一个字段看
3. select deptno,job,max(sal) from emp group by deptno,job;

复制代码

• 重要结论：
  在一条select语句中，如果有group by语句的话，select后面只能跟：参加分组的字段、分组函数。其他的不能跟。
  
• having
  
  
  
  • having可以对分完组后的数据再次进行过滤。
    
  • having不能单独使用，不能代替where，只能和group by搭配使用。
    1. #找出每个部门最高薪资，并显示最高薪资大于3000的
    2. select deptno,max(sal) from emp group by deptno having max(sal)>3000;
    3. #或者先使用where过滤
    4. select deptno,max(sal) from emp where sal>3000 group by deptno;

    复制代码
  • 优化策略：where和having，优先选择where。
    
  
  
• 执行顺序
  

1. select ... from ... where ... group by ... having ... order by ...
2. #以上关键字只能按照这个顺序来，不能颠倒

复制代码

以上语句的执行顺序：from、where、group by、having、select、order by

实例：

1. #找出每个岗位的平均薪资，要求显示平均薪资大于1500的，除MANAGER岗位之外，要求按照平均薪资降序排
2. select job,avg(sal) as avgsal
3. from emp
4. where job <> 'MANAGER'
5. group by job
6. having avg(sal)>1500
7. order by avgsal desc;

复制代码

7、查询结果去重：distinct

1. select distinct 字段 from 表名;

复制代码

• distinct只能出现在所有字段的前面
  
• distinct出现在两个字段之前，则表示两个字段联合起来去除
  

实例：

1. #统计工作岗位数量
2. select count(distinct job) from emp;

复制代码

（二）连接查询

*** 重点！！！**
1、连接查询

• 连接查询分类：
  

  根据语法的年代分类：SQL92、SQL99（重点学习SQL99）
  根据表连接的方式分类：内连接（等值连接+非等值连接+自连接）、外连接（左外连接/左连接+右外连接/右连接、全连接
  

  
  
• 笛卡尔积现象：当两张表进行连接查询，没有任何条件限制时，最终查询结果的条数是两张表条数的乘积。（数学现象）
  

  如何避免？
  连接时加条件，满足这个条件的记录将会被筛选出来。

  1. #各个员工对应的部门
  2. select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;  #SQL92语法
  3. #SQL99语法在‘内连接之等值连接’中

  复制代码

  
  
  • 此时最终查询的结果条数变少了，匹配次数没有减少，还是两张表的条数的乘积。
    
  • 通过笛卡尔积现象得出，表的连接次数越多，效率越低，尽量避免表的连接次数。
    

  
  

2、内连接

等值连接

• SQL99语法（内连接）：
  1. select ...
  2. from a (inner) join b
  3. on a和b的连接条件
  4. where 筛选条件
  5. #inner可以省略，加上可读性更强，表示内连接

  复制代码

  实例：

  1. #各个员工对应的部门
  2. 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后面的条件不是等值关系。
  

实例：

1. #找出每个员工的薪资等级，并显示员工名、薪资、等级
2. select e.ename, e.sal, s.grade
3. from emp e (inner) join salgrade s
4. on e.sal between s.losal and s.hisal;

复制代码

自连接

• 自连接技巧：一张表看作两张表。
  

实例：

1. #查询员工的上级领导，要求显示员工名和对应的领导名
2. select a.ename as '员工名', b.ename as '领导名'
3. from emp a join emp b
4. on a.mgr=b.empno;
5. #将一张表看成两张表

复制代码

3、外连接

• 外连接与内连接的区别：内连接中，连接的两张表没有主次关系，平等的；在外连接中，两张表连接，产生主次关系。（主要看join前面有无right/left来区分）
  
• 带有right的是右外连接，又叫做右连接。
  带有left的是左外连接，又叫做左连接。
  任何一个右连接都有左连接的写法。
  任何一个左连接都有右连接的写法。
  
• 外连接的查询结果条数一定是>=内连接的查询结果条数
  

实例：

1. #各个员工对应的部门，同时将没有员工对应的部门也显示出来
2. select e.ename,d.dname
3. from emp e right (outer) join dept d
4. on e.deptno=d.deptno;
5. #右外连接

复制代码

• 这里的right：表示将join关键字右边的这张表看成主表，主要是为了将这张主表的数据全部查询出来，捎带着关联查询左边的表emp。
  
• outer可以省略，加上可读性就强一些，表示外连接。
  

4、多表连接

• 语法：
  1. select ...
  2. from
  3.         a
  4. join
  5.         b
  6. on
  7.         a和b的连接条件
  8. join
  9.         c
  10. on
  11.         a和c的连接条件
  12. join
  13.         d
  14. on
  15.         a和d的连接条件
  16. ...

  复制代码
  
  
  • 一条SQL中，内连接和外连接可以混合，都可以出现。
    

  实例：

  1. #找出每个员工的部门名称以及工资等级，还有上级领导，要求显示出员工名、领导名、部门名、薪资、薪资等级
  2. select e.ename, l.ename, e.sal, d.dname, s.grade
  3. from  emp e
  4. join dept d on e.deptno=d.deptno
  5. join salgrade s on e.sal between s.losal and s.hisal
  6. left join emp l on e.mgr=l.empno;

  复制代码

  
  

（三）子查询

• 子查询：select语句中嵌套select语句，配嵌套的select语句称为子查询。
  

在where语句中使用子查询

实例：

1. # 找出比最低工资高的员工名字和工资
2. select ename，sal
3. from emp
4. where sal > (select min(sal) from emp);

复制代码

在from语句中使用子查询

• from后面的子查询，可以将子查询的查询结果当作一张临时表。
  

<blockquote>实例：
[code]#找出每个岗位的平均工资的薪资等级select t.*, s.grade from (select job, avg(sal) as avgsal from emp group by job) ton t.avgsal between s.losal and s.hisal; #
来源:https://www.cnblogs.com/Enid/p/17747591.html
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！