基于mysql-5.7.27版本和Navicat Premium 15版本进行！

常用命令：

-- 启动
   net start mysql
   --使用初始密码进入MySQL命令行中
   mysql -uroot -p
   -- 修改密码
   alter user 'root'@'localhost' identified with mysql_native_password by '666';
   -- 刷新数据库
   flush privileges;
   -- 显示所有数据库
   show databases;
   --显示数据库中所有的表
   show tables;

基本概念

  • DB（database）：存储数据的仓库，其中的数据是有组织有关联的
  • DBMS（database management system）数据库管理系统，管理DB的
  • SQL (structure query language) 结构化查询语言，专门与DB通信的语言，所有DBMS（MySQL，Oracle， SQLserver等）都支持；

mysql是一个数据库管理系统(DBMS)而不是数据库

Navicat Premium 15截图：

mysql 主节点刷新binlog日志文件 mysql数据库刷新_sql


图中的student 等就是数据库(DB)。

mysql 主节点刷新binlog日志文件 mysql数据库刷新_mysql_02


上图红色部分就是数据库的表。

数据库的分类

存储位置的不同进行分类：

1.基于磁盘的存储，MySQL，Oracle，SQLServer
2.基于内存的存储，redis非常适合做缓存，

从数据间是否存在关系进行分类：

  1. 关系型数据库：MySQL，Oracle，SQLServer
  2. 非关系型数据库：redis，mongodb nosql(not only sql)

数据表

数据库中有数据表，数据表由行和列组成，表中的每一列称为字段；每一列类似Java中的属性，每一行类似Java中的对象；

SQL语句分为：

mysql 主节点刷新binlog日志文件 mysql数据库刷新_sql_03

关于数据表中NULL值：

理解为没有值或者未知的值

不要用NULL进行算术运算，运算的结果仍然为NULL

SQL常用的数据类型

1、数值型

1.1、整型

类型

所占字节数

范围 (有符号/无符号)

TINYINT

1

-128-127/0-255

SMALLINT

2

-32768-32767/0-65535

MEDIUMINT

3

-8388608-8388607/0-16777215

INT(INTEGER)

4

-2147683648-2147683647/0-4294967295

BIGINT

8

-263–263-1/0–2^64-1

  • 默认有符号，需要无符号的话，用UNSIGNED INT
  • 插入超过范围的数，最终为临界值
  • 整型的长度代表显示的宽度，如果要使用，需要搭配zerofill使用，对于int(M)，如长度小于M，用0左填充至宽度为M，如果大于M则无影响。没实际意义。

1.2、小数

类型

所占字节数

DECIMAL(M,D)

M+2

定点数

FLOAT(M,D)

4

浮点数

DOUBLE(M,D)

8

浮点数

  • D保留小数位数
  • M整数+小数位数和
  • MD可以省略，DECIMAL默认为(10,0)，FLOAT和DOUBLE会根据实际插入的值来确定
  • 定点型精确度高一点，一些高精度要求的可以用定点型，如货币汇率等

2、字符型

类型

备注

CHAR(M)

存放长度固定或有限的字符

VARCHAR(M)

存放长度不固定的字符

TEXT

存放长文本

BINARY(M)

存放长度固定或有限的二进制

VARBINARY(M)

存放长度不固定的二进制

BLOB

存放较大的二进制，如图片等

ENUM()

枚举

SET()

集合

  • M为最多的字符数，“abc”是3个字符，“你好”是两个字符
  • CHAR是固定长度的字符，可省略M，默认为1，费空间，效率高
  • VARCHAR是可变长度字符，不可以省略M，M为最大长度，省空间，效率低
  • BINARY和VARBINARY与CHAR和VARCHAR类似用法
  • ENUM(“a”,“b”,“c”) ，多选一，只能保存"a",“b”,"c"其中之一，不区分大小写，在mysql5.7中如果插入非列表中的内容，则为null
  • SET(“a”,“b”,“c”)，多选多，能保存一个或多个abc中的值，如保存"a,b"，不区分大小写，在mysql5.7如果插入非列表中的内容，则报错

3、日期型

类型

所占字节数

DATE

4

年-月-日

DATETIME

8

年-月-日 时-分-秒

TIMESTAMP

4

时间戳，时区影响小，占空小，用的多

TIME

3

时-分-秒

YEAR

1

年

SQL字段约束

六大约束

约束类型

含义

备注

NOT NULL

非空

不为空…

PRIMARY KEY

主键

保证值得唯一性，且非空

UNIQUE

唯一

保证值的唯一性，可以为空

DEFAULT

默认

指定字段的默认值

CHECK

检查

保证必须是满足条件的值，mysql不支持

FOREIGN KEY

外键

用来限制两个表之间的关系

外键

  • 保证从表的值必须来自于主表的某一列的值，需在从表中添加外键。
  • 主表从表对应的字段类型要一致或兼容
  • 主表的字段必须是一个key（一般是主键或唯一）
  • 插入数据时，必须先插入主表再插入从表

主键与唯一的区别

  • 主键具有唯一性，不允许为null，一张表最多一个，可以组合使用（即多个字段为组合为一个主键）但不推荐。
  • 唯一具有唯一性，允许为null且在mysql5.7中默认可以有多个null，一张表可以有多个，可以组合使用但不推荐
标识列(自增长列)
  • 用AUTO_INCREMENT设置
  • 标识列只能是数值类型，一个表最多一个
  • 标识列必须是主键或者唯一

DQL数据查询语言(Select)

基本语法

select selection_list /*要查询的字段，多个字段用逗号隔开*/
from table_list /*要查询的表名称*/
[where condition /*筛选记录的条件*/
 group by grouping_clounms /*对结果进行分组*/
 having condition /*对分组后的记录进行条件筛选*/
order by cloumns /*对结果进行排序*/
 limit  /*对记录总数进行限定*/]
  • 书写顺序：SELECT、DISTINCT、FROM、JOIN ON、WHERE、GROUP BY、HAVING、ORDER BY、LIMIT
  • 执行顺序：FROM、 JOIN ON、WHERE、GROUP BY、HAVING、SELECT、DISTINCT、ORDER BY、LIMIT
基本查询

select后面可以是表中的字段，常量值，表达式，函数；查询的结果是一个虚拟的表格；

使用SELECT查询的基本语句SELECT * FROM <表名>可以查询一个表的所有行和所有列的数据。

SELECT查询的结果是一个二维表。

模糊查询 like

%：表示0或任意多个字符

_:任意一个字符

例子：

-- 名字由3个字母组成
SELECT * FROM t_stu WHERE sname LIKE '___';
-- 名字由j 开头
SELECT * FROM t_stu WHERE sname LIKE 'j%';
-- 第二个字母为a 的学生记录
SELECT * FROM t_stu WHERE sname LIKE '_a%';
-- 查询姓名中包含字母a的记录
SELECT * FROM t_stu WHERE sname LIKE '%a%'

起别名时AS可以省略不写

排序 order by子句可以跟单个字段，多个字段，表达式，函数，别名

  • 缺省是ASC升序
  • DESC 降序
聚合函数

用作统计使用，又称为聚合函数或者统计函数或者组函数

  • 聚合函数是用来做纵向运算的函数：
  • COUNT(字段)：统计指定列不为NULL的记录行数；一般使用count（*）统计行数
  • MAX(字段)：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
  • MIN(字段)：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
  • SUM(字段)：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；
  • AVG(字段)：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；

sum,avg一般处理数值型,

max，min,count可以处理任意数据类型

分组函数都忽略了null值，可以和distinct搭配使用

注意点：组函数可以出现多个，但是不能嵌套；如果没有group by 子句，结果集中所有行数作为一组

-- 查询emp表中记录数:
SELECT COUNT(empno) AS 总人数 FROM emp;
SELECT COUNT(*) AS 总人数 FROM emp;
SELECT COUNT(1) AS 总人数 FROM emp;
-- 查询emp表中有佣金的人数
SELECT COUNT(comm) FROM emp;
-- 	查询emp表中月薪大于2500的人数
SELECT COUNT(1) FROM emp WHERE sal >2500;
-- 统计月薪与佣金之和大于2500元的人数
SELECT * FROM emp WHERE sal+IFNULL(comm,0)>2500;
-- 	查询有佣金的人数，以及有领导的人数
SELECT COUNT(comm) FROM emp WHERE mgr is not null;
-- 	查询所有雇员月薪和
SELECT SUM(sal) 月薪和 FROM emp;
-- 	查询所有雇员月薪和，以及所有雇员佣金和
SELECT SUM(sal) 月薪和, SUM(comm) 佣金和 FROM emp;
-- 	查询所有雇员月薪+佣金和
SELECT SUM(sal+IFNULL(comm,0)) 总佣金 FROM emp;
SELECT SUM(sal)+SUM(IFNULL(comm,0)) 总佣金 FROM emp;
-- 	统计所有员工平均工资
SELECT AVG(sal) FROM emp;
-- 	查询最高工资和最低工资
SELECT MAX(sal) , MIN(sal) FROM emp;-- 查询emp表中有佣金的人数
SELECT COUNT(comm) FROM emp;
-- 	查询emp表中月薪大于2500的人数
SELECT COUNT(1) FROM emp WHERE sal >2500;
-- 统计月薪与佣金之和大于2500元的人数
SELECT * FROM emp WHERE sal+IFNULL(comm,0)>2500;
-- 	查询有佣金的人数，以及有领导的人数
SELECT COUNT(comm) FROM emp WHERE mgr is not null;
-- 	查询所有雇员月薪和
SELECT SUM(sal) 月薪和 FROM emp;
-- 	查询所有雇员月薪和，以及所有雇员佣金和
SELECT SUM(sal) 月薪和, SUM(comm) 佣金和 FROM emp;
-- 	查询所有雇员月薪+佣金和
SELECT SUM(sal+IFNULL(comm,0)) 总佣金 FROM emp;
SELECT SUM(sal)+SUM(IFNULL(comm,0)) 总佣金 FROM emp;
-- 	统计所有员工平均工资
SELECT AVG(sal) FROM emp;
-- 	查询最高工资和最低工资
SELECT MAX(sal) , MIN(sal) FROM emp;

group by分组查询

查询出来的字段要求是group by后的字段，查询字段中可以出现组函数

group by后面可以跟聚合函数 可以起别名

-- 	查询每个部门的部门编号和每个部门的工资和
SELECT deptno,SUM(sal) FROM emp GROUP BY deptno;

-- 	查询每个部门的部门编号以及每个部门的人数
SELECT deptno,count(1) FROM emp GROUP BY deptno;

-- 查询每个部门的部门编号以及每个部门员工工资大于1500的人数
SELECT deptno,count(1) FROM emp WHERE sal>1500 GROUP BY deptno

having子句

where是对分组前进行过滤；having是对分组后进行过滤

where中不能出现分组/聚合函数，having中可以出现

where是比分组先执行的，having是在分组之后执行的；

having后面可以跟别名

-- 	查询工资总和大于9000的部门编号以及工资和
SELECT deptno,SUM(sal) FROM emp GROUP BY deptno HAVING SUM(sal)>9000;

SELECT deptno,SUM(sal) 总薪资 FROM emp GROUP BY deptno HAVING 总薪资>9000;
limit
SELECT * FROM emp LIMIT 0,5;

第一位表示起始索引位置，第二位表示总的长度；在分页中会使用

第三页为： (3-1)*5, 5;

第n页为：(n-1)*size , size;

连表查询

内连接

  • 多表等值连接的结果是多表的交集部分，N表连接，至少需要N-1个连接条件，没有顺序要求，一般起别名
  • 非等值连接，只要不是等号连接的都是非等值连接

外连接

有主表有从表，主表肯定会显示完整的内容

  • 左外连接，以左表为主，左边的表内容全部显示，右边的表没有的以null进行填充
  • 右外连接，以右表为主，右表内容全部显示，左表没有的以null进行填充

自连接

通过别名，将同一张表视为多张表；同一张表中某个字段要去关联另外一个字段

子查询

例子：

-- 查询员工信息，要求显示员工号，姓名，月薪，部门名称
SELECT empno,ename,sal,em.deptno,dname FROM emp em,dept de WHERE em.deptno=de.deptno; 

-- 查询员工信息，要求显示：员工号，姓名，月薪，薪水的级别
SELECT empno,ename,sal,grade FROM emp em,salgrade sa WHERE em.sal BETWEEN sa.LowSAL AND sa.HISAL;

-- 查询员工信息，要求显示员工号，姓名，月薪，部门名称 使用内链接
SELECT empno,ename,sal,emp.deptno,dname FROM emp INNER JOIN dept ON emp.deptno=dept.deptno;

-- 左外链接
SELECT empno,ename,sal,emp.deptno,dname FROM emp LEFT JOIN dept ON emp.deptno=dept.deptno;

-- 右外连接
SELECT empno,ename,sal,emp.deptno,dname FROM emp RIGHT JOIN dept ON emp.deptno= dept.deptno;

-- 查询员工姓名和员工的老板的名称(自连接)
SELECT a.ename,a.empno,b.ename,b.empno FROM emp a,emp b WHERE a.mgr = b.empno;

-- 查询工资大于20号部门平均工资的员工信息.(子查询)
SELECT * FROM emp WHERE sal > (SELECT AVG(sal) FROM emp GROUP BY deptno HAVING deptno = 20);