Mysql最全笔记，快速入门，干货满满，爆肝

比眉伴天荒 2021-09-09 04:14 308阅读 0赞

## 目录 ##

一、 MySQL的重要性  
二、MySQL介绍  
三、软件的服务架构  
四、 MySQL的安装  
五、 SQL语句  
六、 数据库相关（DDL）  
七、 表相关  
八、 DML相关（表中数据）  
九、 DQL（重点）  
十、 数据完整性  
十一、 表与表之间的关系

## **一、MySQL的重要性** ##

1.1定位  
（1）重点在SQL语句 ，需要能够在没有任何提示下，用纸和笔手写SQL。  
（2）在面试中95%的概率 手写SQL语句。  
（3）通过SQL语句去操作数据库  
需要了解SQL、Oracle、SQLserver等数据库的区别：方言

## 二、MySQL介绍 ##

2.1什么是数据库？  
作用：存储数据的，能够长期（断电、关机在开机数据还有）保存数据。  
数据存储在哪里：硬盘和内存  
我们平时说的数据库：数据管理系统（软件）（Databases Manage System： DBMS）  
数据库软件（电脑的Excel软件）中可以创建多个文件夹（数据库（逻辑的））  
一个数据库：（一个Excel文件）存储XX电商项目数据  
一个数据库：（一个Excel文件）存储XX图书馆管理项目数据  
每个数据库可以继续划分（表）  
数据库软件–>多个数据库–>多个表–>多条数据（一条一行，一行多列）  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1hhaFk2Ng_size_16_color_FFFFFF_t_70]

**2.2数据库的分类**  
2.2.1关系型数据库（RDBMS：relative database man）  
特点：  
（1）表与表之间有关系  
（2）是通过SQL语句去操作数据库。  
（3）有行有列（和Excel类似）  
比较有名：  
MySQL:免费、开源。  
Oracle：甲骨文，收费，大型公司，一年费用9位数。  
SQL Server：微软公司，可以安装Windows  
DB2  
**2.2.2非关系型数据库（No-SQL）**  
特点：  
（1）表与表之间没有关系  
（2）通过API（java、PHP、Python代码）去操作  
（3）充分使用内存，同步到磁盘  
比较有名的代表：  
Redis：  
HBase：  
mangoDb：  
neo4j：  
**2.2.3什么是SQL**  
结构化查询语言(Structured Query Language)简称SQL，用于存取数据以及查询、更新（数据的操作）和管理（数据库、表的创建、修改、删除）关系数据库系统；  
通过SQL语句去操作关系型数据库  
不同的数据库对SQL语句的支持不完全一样  
85%的SQL语句，关系型数据库都支持。  
各个数据库在SQL语句上都有自己的扩展（方言）。  
结构化：有行有列的数据、Excel数据  
非结构化：视频、音乐

## 三、软件的服务架构 ##

**3.1一些场景**  
刷抖音  
抖音APP；短视频通过网络获取，在网络之后有人给你服务。（服务器）  
和别人微信聊天  
打开APP，通过网络和别人聊天，在网络之外有人提供服务（服务器）  
上淘宝购物  
打开浏览器，输入淘宝的网址

**3.2架构模式**

C：Client，客户端  
S：server，服务器  
B：Browser：浏览器  
C/S：客户端/服务器端  
抖音APP/微信/手淘  
B/S：浏览器/服务器端  
淘宝网站  
注意：B/S是特殊的C/S架构。  
总结：一个项目，肯定不单单只有一个APP那么简单。  
**3.3MySQL的架构**  
MySQL其实就一个B/S架构。  
要使用MySQL，  
（1）先启动MySQL的服务  
（2）通过客户端连接MySQL的服务（客户端有很多）  
（3）客户端有很多：图形化界面、命令行、代码

## 四、MySQL的安装 ##

**4.1企业的用法**  
（1）购买云服务（阿里云、腾讯云）然后自行安装MySQL服务。  
阿里云：  
64核 128GB–>64台（1核 2GB）  
（2）直接购买MySQL服务器。（帮你做了很多：定期备份、宕机重启、宕机报警）

**4.2命令**  
启动命令终端： Win + R–>输入cmd–>回车  
启动MySQL服务：net start mysql  
关闭MySQL服务：net stop mysql  
4.3客户端  
方式一：命令行 mysql -u root -p  
\-u：user：用户名 root（超级管理员）  
\-p：password：密码  
\-h：hostname主机名（ip）  
\-P：Port端口  
你没有指定-h，则相当于“-h localhost”，此时是以socket方式连接的，而不是以tcp/ip方式，-P参数不起作用。进去以后你可以用show status看一下connection连接方式这一行验证一下。  
方式二：图形化界面  
（1）Navicat for MySQL 复制到不含中文的目录下  
（2）双击“navicat.exe”运行  
（3）输入激活码  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1hhaFk2Ng_size_16_color_FFFFFF_t_70 1]

## 五、SQL语句 ##

**5.1数据库的概念**  
MySQL：数据软件  
数据库：在软件里面创建多个数据库（逻辑、文件夹）  
在数据库中可以创建多个表。  
数据：数据必须存储在表中。  
**5.2SQL语句的分类**

*  数据查询语言DQL（Data Query Language） 对表中 数据 的查询
 *  数据操纵语言DML（Data Manipulation Language） 对 表中 数据 的操作
 *  添加数据、删除数据、更新数据、查询数据（DQL） 对Excel中的数据进行处理。
 *  数据定义语言DDL（Data Definition Language） 对数据库（database）、表（table）结构的定义。
 *  创建数据库、删除、修改 创建表、删除、修改 创建一个Excel文件 数据控制功能DCL（Data Control Language）
    
    对用户的权限的管理。  
    设置用户只能查看数据，不能修改数据。  
    设置用户只能对其中的 一个数据库进行操作。  
    DBA：数据库管理员  
    **5.3SQL语法**  
    （1）代码，用“;”表示结束。  
    SQL写完之后，以英文的“;”表示结束。  
    （2）SQL语句可能会很长  
    在记事本中完成SQL语句的编写，然后复制  
    **5.4SQL关键字**  
    Crtate：创建  
    Date：日期  
    Database：数据库  
    Show：展示；查看  
    Drop：删除  
    Where：在条件；条件  
    Alter：修改  
    Table：表格  
    Modify：修改  
    Remove：移除，删除  
    Key：钥匙；键  
    Row：行；多少行  
    Field：字段；列  
    Type：类型  
    Query：查询  
    Update：更新  
    Data：数据  
    **5.5记事本推荐**  
    在写sql语句时必不可少的是记事本，这对于写语句，校验，存储sql，都有很大有用处，直接在命令行中打一连串的命令有些不现实。  
    这里推荐用 **Nodepad++**  
    使用：  
    （1）在桌面 创建一个文本文件。  
    （2）把文件的后缀名改成sql。  
    （3）这个文件用nodepad++打开这个文件  
    （4）SQL语句的关键字会有提示。

## 六、数据库相关（DDL） ##

**6.1创建数据库**

#（1）创建数据库
    create database + 数据库名字;

**6.2查看所有数据库**

#（2）查看所有数据库
    show databases;

**6.3删除数据库**

#（3）删除数据库
    drop  database 数据库名字;

**6.4字符集**  
字符集：字符的集合。  
什么是字符：a,b,c,d,A,B,0,1,2,3,\_,し，て，い，る。  
GB2312：中文字符集  
ASCLL字符集：  
Utf-8：统一的字符编码

## 七、表相关 ##

**7.1表的概念**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1hhaFk2Ng_size_16_color_FFFFFF_t_70 2]  
**7.2 MySQL的数据类型**  
7.2.1数值  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1hhaFk2Ng_size_16_color_FFFFFF_t_70 3]  
7.2.2字符串  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1hhaFk2Ng_size_16_color_FFFFFF_t_70 4]  
7.2.3日期类型  
![在这里插入图片描述][ca22e613712940029a801b98ddade17c.png]  
7.3创建表

create table 表名(
           字段1名字  字段1类型,
    	   字段2名字  字段2类型,
           字段3名字  字段3类型,
    )
    create table t_user(
         id  int, 
    	 name varchar(4),
    	 age tinyint,
    	 sex varchar(2),
    	 phone varchar(11),
    	 score double(5,2)
    
    );

7.4查看表

show  tables;（查看所有表）

7.4.1查看表所有的数据

select * from  +表名;

**7.6表的修改**

7.6.1添加列：

##（1） 添加一列（添加一个字段）
    ##给用户表 t_user添加一列 字段名为 balance ，类型是 double;
    alter table t_user add balance double(5,2);

7.6.2修改列：

##（2） 将性别的数据类型改成 int （0：男，1：女 ， 2：未知）
    alter table t_user modify sex int(1);

7.6.3删除列：

##（3） 把 t_user表的 balance字段删除
    alter table t_user drop balance;

7.6.4修改列名：

##（4） 把 t_user 表，手机字段改成tel
    alter table t_user change phone tel varchar(12);

7.6.5修改表(重命名)的名字

##（5）修改表(重命名)的名字 t_user-->tb_user;
    rename table t_user to tb_user;

## 八、DML相关（表中数据） ##

**8.1DML是什么**  
通过DML实现表中数据的操作  
插入数据： insert  
查询数据： select  
更新数据： update  
删除数据： delete  
**8.2插入数据**  
语法：  
insert into 表名(字段1,字段2,字段3…) values(字段1的值,字段2的值,字段3的值,…)  
注意：  
（1）前后字段 个数 类型 必须一样。  
（2）数字（整数，小数）、布尔类型 值正常写，其他类型需要加上单引号‘zsf’;  
（3）如果插入的 值 是空的 null  
**8.2.1正常的写法**

##（1）插入数据 1、zsf 18岁 、男、1772222、90
    insert into  tb_user(id,name,age,sex,tel,score)values(1,'zsf',18,'1',1772222,90);

![在这里插入图片描述][e4742c8a428647e982a5be54ce0ca61f.png]  
8.2.2字段名省略  
![在这里插入图片描述][82e798f03c5746ac9c7f06c13ca73929.png]  
8.2.3一次性插入多条数据  
![在这里插入图片描述][17a70ae2962e474eba26d4ec00af40a6.png]  
8.2.4字段缺失

##（4）字段缺失
    insert into  tb_user(id,tel) values(5,'1506544');
    insert into tb_user(id,name,tel)values(6,null,'1888823');

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1hhaFk2Ng_size_16_color_FFFFFF_t_70 5]  
**8.3更新数据**  
8.3.1语法

语法：
    update 表名 set 字段1名字 = 字段1的值, 字段2名字 = 字段2的值 where 条件

8.3.2更新所有数据  
![在这里插入图片描述][e8ec7cd1e41a4846bb18c0245ccee09d.png]  
8.3.3根据条件更新

##(2)将学号为6的学生成绩改为59;
    update tb_user set score=59 where id=6;

![在这里插入图片描述][07eefb92794b4c4b97f6837e691f7130.png]

##(3)将名字为ww的学生，成绩设置为60;
    update tb_user set score=60 where name='ww';

![在这里插入图片描述][3b7589a6c0e94d718edc93e14c532534.png]

8.3.4更新多个字段

##(4)将学号是5的学生，成绩设为99，名字设置为“xl”;
    update tb_user set  score=99,name='xl' where id=5;

![在这里插入图片描述][484899f24a9d4efd944a2a3bb9b7c90f.png]

##(5)将所有人的成绩减去10分；
    update tb_user set score=score-10;

![在这里插入图片描述][5559e4e19b0940b78d8e01035554cae3.png]

**8.4删除数据**  
8.4.1语法

语法：
    delete  from  表名 where 条件

8.4.2删除所有数据

##（1）删除所有数据
    delete from tb_user ;

8.4.3根据条件删除

##(2) 删除学号为1的学生数据
    delete from tb_user  where id =1;

![在这里插入图片描述][bc6eb5f234e647daa00786c6a87ea241.png]

##(3)删除姓名为“ww”的学生
    delete from tb_user where name = 'ww';

![在这里插入图片描述][bf365c30f1ec4b59bffb957a7d609ac3.png]  
8.4.4truncate删除

##(4)删除所有数据
    truncate table tb_user;

8.4.5delete 和 truncate区别

##(5)delete 和 truncate区别
    delete :一行一行的把数据删除;
    truncate: 先删除表（drop），重新创建（create）表;

## 九、DQL（重点） ##

**DQL介绍**  
数据库在执行DQL的时候，不会对数据进行改动，仅仅是查看，只要把服务器中的数据获取返回给客户端。  
查询的到的结果也是一张表

**9.1数据准备**  
![在这里插入图片描述][3e47162660da412eae550ec31e0a31d5.png]

mysql> create  table stu(
        -> sid char(6),
        -> sname varchar(50),
        -> age int ,
        -> gender varchar(50)
    -> );

INSERT INTO stu VALUES('S_1001', 'liuYi', 35, 'male');
    INSERT INTO stu VALUES('S_1002', 'chenEr', 15, 'female');
    INSERT INTO stu VALUES('S_1003', 'zhangSan', 95, 'male');
    INSERT INTO stu VALUES('S_1004', 'liSi', 65, 'female');
    INSERT INTO stu VALUES('S_1005', 'wangWu', 55, 'male');
    INSERT INTO stu VALUES('S_1006', 'zhaoLiu', 75, 'female');
    INSERT INTO stu VALUES('S_1007', 'sunQi', 25, 'male');
    INSERT INTO stu VALUES('S_1008', 'zhouBa', 45, 'female');
    INSERT INTO stu VALUES('S_1009', 'wuJiu', 85, 'male');
    INSERT INTO stu VALUES('S_1010', 'zhengShi', 5, 'female');
    INSERT INTO stu VALUES('S_1011', 'xxx', NULL, NULL);

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1hhaFk2Ng_size_16_color_FFFFFF_t_70 6]

create table emp  (empno int,
    ename varchar(50), 
    job varchar(50), 
    mgr int, hiredate date,
    sal decimal(7,2),
    comm decimal(7,2),deptno int);

![在这里插入图片描述][358aea4b777242c99c99bd0f12d721d6.png]

INSERT INTO emp values(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
    INSERT INTO emp values(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
    INSERT INTO emp values(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
    INSERT INTO emp values(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
    INSERT INTO emp values(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
    INSERT INTO emp values(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
    INSERT INTO emp values(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
    INSERT INTO emp values(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);
    INSERT INTO emp values(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
    INSERT INTO emp values(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
    INSERT INTO emp values(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);
    INSERT INTO emp values(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
    INSERT INTO emp values(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
    INSERT INTO emp values(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);

![在这里插入图片描述][2efc2f0207df483d946cab465884dac3.png]  
create table dept(  
\-> deptno int,  
\-> dname varchar(50),  
\-> loc varchar(50));  
INSERT INTO dept values(10, ‘ACCOUNTING’, ‘NEW YORK’);  
INSERT INTO dept values(20, ‘RESEARCH’, ‘DALLAS’);  
INSERT INTO dept values(30, ‘SALES’, ‘CHICAGO’);  
INSERT INTO dept values(40, ‘OPERATIONS’, ‘BOSTON’);  
**9.2基础查询**  
9.2.1查询所有的列

##（1.1）查询所有列：*表示所以列
    ###eg：查询所有的员工信息
    select * from emp;       /select * from + 表名;

![在这里插入图片描述][79f05311223642d5ac7c6e67a70598c1.png]  
9.2.2查询指定列

##(1.2)查询指定列
    select empno,ename,deptno from emp;   /select+要查询的列 from + 表名;

![在这里插入图片描述][a41e06a99b3d4f168564244cdb16fd39.png]  
**9.3条件查询where**  
9.3.1常见的运算符  
关系运算符：=、！=、<、>、>=、<=  
区间：between A and B --> \[A,B\]  
And:并且、和 Not null: 非空  
In：在…里面 Is not null:非空  
Or：或者 Not：否、非

9.3.2或者Or、并且and

##（2.1）查询性别女，并且 年龄65的记录
    select *from stu where  gender = 'female'  and  age =65;

##(2.2)查询学号是S_1001或者名字为lisi的记录
     select * from stu where sid ='S_1001' or  age = 65;

![在这里插入图片描述][b417ae7b6a794f9fadc5c5f33b24342e.png]  
9.3.3在什么里面In

##(2.3)查询学号是S_1001,S_1002,S_1003的记录
     写法一：select * from stu  where sid = 'S_1001' or  sid='S_1002'  or sid='S_1003';
     写法二：select * from stu  where sid in ('S_1001','S_1002','S_1003');
    ##(2.4)查询学号不是S_1001,S_1002,S_1003的记录
     select * from stu  where sid not in ('S_1001','S_1002','S_1003');

![在这里插入图片描述][90924c7fd6b04cccaf118dfae26125f0.png]  
9.3.4是否为空is（not）null

##（2.5）查询年龄为null 的记录
    select * from stu where age is null;

![在这里插入图片描述][b01271cb3e174c4caa279e49c78a0ff6.png]  
9.3.5区间between

##（2.6）查询年龄在20~40岁之间的
    方式一：select * from stu  where age >=20 and  age <=40;
    方式二：select * from stu  where  age between  20 and 40;

![在这里插入图片描述][805d1833a2a54cf6b7f151451a172f4b.png]  
9.3.6非not

##（2.7）查询性别是非男性的学生记录
    select *from stu where gender != 'male';
    select *from stu where gender <>'male';
    select *from stu where not  gender = 'male';

![在这里插入图片描述][5c9df2a846424f28a150650b77f325e2.png]

## 9.4模糊查询 ##

9.4.1介绍  
（1）当想查询学生姓名中饱含字符a，就需要使用模糊查询，模糊查询的话使用关键字是like（像）  
（2）通配符  
*:任意一个字符  
张*: 张三，张三丰  
张\_\_:张三，张三丰  
%：表示0或者多个字符  
张%：张三，张三丰

9.4.2通配符：\_、%

##(3)通配符
    ##查询名字由五个字母构成的 学生记录
    select * from stu where sname like'_____'; （五个‘_’）
    
    ##查询名字由五个字母构成的 并且第五个字符是i的学生记录
    select * from stu where sname like '_____i';
    ##查询名字以z开头 的学生记录
    select * from stu where sname like 'z%';
    
    ##查询名字第二个字母是i的学生
    select * from stu where sname like '_i%';

注意：  
（1）SQL的性能优化  
避免使用\*，如果表中有100个字段，全部列出。  
模糊查询时候，避免使用通配符开头（避免全表扫描）

##查询名字以z开头 的学生记录
    select * from stu where sname like 'z%';
    
    ##查询名字第二个字母是i的学生
    select * from stu where sname like '_i%';

**9.5字段控制查询**

9.5.1去除重复查询

##（1）去除重复数据
    select disctinct deptno from emp;

9.5.2 ifnull

##(2)查询员工的薪资加奖金
    ifnull (A,B);如果A是空 使用B的值 如果A不是空就使用A
    ##问题： 任何数据 + null--->null;
     ##1800 + null -->1800;
    select  sal , comm ,sal+ifnull (comm,0) from emp ;

9.5.3别名as  
别名：给这个列 换 一个新名字。

##9.7别名
    select  sal , comm ,sal+ifnull (comm,0)as money from emp ;
    
    ##as 关键字可以省略
    select  ename name ,sal , comm ,sal+ifnull (comm,0)as money from emp ;

9.5.4排序 order by asc/desc  
升序：asc 降序：desc

##排序
    ##升序order by
    ##（1）查询所有的学生信息 根据年龄升序排列
    select * from stu order by  age asc;

![在这里插入图片描述][65673e76ffe74493b4ee5f126bcab869.png]

##（2）查询所有的学生信息 根据年龄降序排列
    select * from stu order by  age desc;

![在这里插入图片描述][c5ed6b202c0c4ae5817e01e06e1931bb.png]

##（3）查询所有员工信息 根据工资进行排序 如果工资相同按照员工工号升序
    select * from emp order by sal desc ,empno asc;

![在这里插入图片描述][febe602ae38547228915d984880689df.png]  
**9.6聚合函数**  
MySQL系统提供了很多函数：  
Count：统计个数，次数，null不统计 Round：四舍五入  
Msx：最大值 Sum：求和  
Avg：平均值  
9.6.1 count函数

##count 函数
    ##（1）查询emp表中记录数
    count(*):不会忽略null的值
    select  count(*) from emp;
    
    ##（2）统计emp表中 有提成的人
    ##count（具体字段），null的话会被忽略
    select count(comm) from emp;
    
    ##（3）工资大于2500的员工人数
    select count(*) from emp where sal>2500;
    ##（4）薪资 （工资+奖金）大于2500的员工人数
    select count(*) from emp where (sal+ifnull(comm,0) )>2500;
    
    ##（5）薪资有提成的人数、有上一级领导的人数；
    select count(comm),count(mgr) from emp;

9.6.2 sum求和，avg平均数，round四舍五入

##sum、avg、round
    ##（1）查询所有员工工资总和
    select sum(sal) from emp;
    
    ##（2）查询所有员工工资、提成 总和
    select sum(sal) ,sum(comm ) from emp;
    
    ##（2）查询所有员工工资、提成 总和、薪资
    select sum(sal) ,sum(comm) ,sum(sal+ifnull(comm,0)) money from emp;
    
    ##(3)计算员工平均工资
    select avg(sal) , avg(comm) from emp;
    
    ##(4)平均工资保留两位小数
    select round (avg(sal),0),avg(comm ) from emp;

9.6.3 Max 和 Min

##Max最大值Min最小值
    ##(1)查询员工表中，工资最高的、最低的
    select  max(sal) max_sal,min(sal)  min_sal from emp;

9.6.4 分段函数 case  
\#\#员工表中，根据薪资不一样，给不一样的评论  
>4000,NB666  
>3000,NB666  
>2000,NB6  
<2000,ZZ

select 
           sal,
    	   case
    	           when sal>4000   then'NB666'
    			   when sal>3000   then'NB66'
    			   when sal>2000   then'NB6'
    			   else 'ZZ'
    	   end as pj
    from
            emp;

9.6.5字符串截取  
Left(A,4)从左边开始截取4个字符  
Right(A,4) 从右边开始截取4个字符  
我 爱 你

1987-04-19-->1987
    ###（1）获取员工的入职年份
    select ename,hiredate,left(hiredate,4)from emp;

![在这里插入图片描述][302dbc1c802445fb94cca1cccdf7e1da.png]

###（2）获取员工的入职工龄
    select ename,hiredate,2021-left(hiredate,4)from emp;

![在这里插入图片描述][4742e540d2c0417e8b50d0650350779e.png]

输出中间的数04    select right (left('1987-04-19',7),2);

![在这里插入图片描述][36ca67c65c7d4d5190c05466b9a34a57.png]  
**9.7分组查询 group by**

9.7.1场景  
统计各个部门的人数情况  
按照性别统计人数。  
按照班级统计参加比赛的人数。  
\#\#(1)统计各个部门的人数  
部门ID 人数  
10 6  
20 7  
30 3

部门ID 人数 平均工资  
10 6 100  
20 3 200  
30 5 150  
（1）分组之后的结果：和具体一条数据没有关系  
（2）分组会和聚合函数一起使用  
（3）需求中包含：“每个”，“各个”，这种，group by  
\#\#(1)统计各个部门的人数

部门ID 人数 平均工资  
10 6 100  
20 3 200  
30 5 150

select
              deptno,count(*),avg(sal)
    from 
            emp
    group by
            deptno;

\#\#（2）统计各个部门各个岗位 人数。  
部门id 平均工资  
10 600  
20 300  
30 400

部门ID 岗位 人数  
1 java开发 10  
2 前端开发 6  
3 出纳 2  
2 会计 4

select 
          job, deptno,count(*)
    from 
           emp 
    group by 
           deptno,job;

9.7.3having  
需求：统计平均工资超过2000的部门。  
算出各个部门的平均工资。  
过滤，只留下超过2000的。

select
         job,avg(sal) as avg_sal
       from
       emp
       Group by
       Deptno
    Having 
        Sal_avg>2000;

9.7.4 where 与 having的区别  
Where ：过滤：分组之前过滤：对数据源进行过滤。  
Having过滤：分组之后的过滤  
**9.8 Limit**  
Limit限制查询结果的条目数。

##语法： limit start len;从start开始,查询len条
    ##(1)查询前五条数据
    select * from emp limit  5;
    ##（2）查询第二条到第五条数据
    select * from emp limit 2,5;

使用场景：分页查询  
如果一个页面显示10条数据，获取数据  
第一次 limit 0,10  
第一次 limit 10,10  
第一次 limit 20,10  
**9.9执行顺序**

select 
              deptno,avg(sal)as sal_avg
    from 
              emp
    where 
              mgr is not null
    group by
              deptno
    order by  
              sal_avg desc
    limit 2;

关键字执行顺序：  
from -->where -->group by–>select–>having–>order by–> limit

## 十、数据完整性 ##

**10.1什么是数据完整性**  
作用：保证 用户输入的数据 保存到数据库 是正确的。  
比如：  
年龄：应该是数字  
学号：应该是不能重复的。  
成绩：不能为空  
怎么去保证数据的完整性：添加约束。  
完整性约束分类  
实体（记录） 完整性  
域完整性  
引用完整性  
**10.2实体完整性（行）**  
实体（entity）：就是表中一行的（记录）数据。  
实体完整性：标识 每一条数据不能重复。  
约束类型（通过什么方式解决这个问题）：  
主键约束（primary key）  
不能为空，不重复  
唯一约束（unique）  
不能重复，可以为空  
自增长列（auto\_increment）  
序号自己会改变  
10.2.1主键约束（primary key）  
特点：不能为空，不重复

##没有约束
       create  table stu0(
              id  int,
    		  name varchar(50)
    )
    insert into stu0(name)varchar('张三丰');

##方式一：创建表，并且添加主键约束
    create table stu1(
                id int primary key,
    			name varchar(50)
    		)
    	##方式二：
    	   create table stu2(
    	           id  int,
    			   name varchar(50),
    			   primary key(id,name)
    			   )
    成功： insert into stu1(id,name)value(2,'张三丰');##成功
     测试一：insert into stu1(id,name)value(null,'张三丰');##失败 ，提示不能为空
            ERROR 1048(23000):Column'id'cannot be null
     测试二：
                插入重复值：报错
    			     Duplicate entry'2' fro key 'PRIMARY'
    select * from stu1;
       自增长列（auto_increment）

10.2.2唯一约束（unique）  
不重复，可以为空  
\#\#给名字 添加 唯一的 约束

create table stu3(
        id int primary key,
    	name varchar(50)unique
    	)
    	insert into stu3(id,name)value(1,'张三丰');
    	insert into stu3(id,name)value(2,'张三丰');
    	
    	ERROR 1062(23000):Duplicate entry'张三丰'for key 'name'
    	
    	insert into stu3(id,name )value(2,'张三');

10.2.3自增长列（auto\_increment）

##实现，用户的学号，自动增长，
    	create table stu4(
    	  id int primary key auto_increment,
    	  name varchar(50)
    	  )
    	  
    	  ##插入数据
    	  insert into stu4(name)value('张三丰');
    	  insert into stu4(name)value('ls');

10.3.1数据类型  
![在这里插入图片描述][3eddb0c4ef8b4fc195092dce1a534241.png]  
10.3.2非空约束（not null）

##创建表，名字name 字段为非空
    	create table stu5(
    	   id int primary key ,
    	   name varchar(50) not null,
    	   sex varchar(10)
    	   );
    	   
    	   ##成功：
    	   insert  into stu5(id,name,sex) values(1,'zsf','n');
    	   insert  into stu5(id,name,sex) values(2,'null','n');
    	   ERRO 1048(23000):COALESCE'name'cannot be null;
    	    insert  into stu5(id,name,sex) values(2,'zz','null');
    		ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'

10.3.3默认值约束（default）  
值没给，就使用默认值。

create table stu6(
       id int primary key ,
       name varchar(50)not null,
       sex varchar(10)Default'N'
       );
    	
    	查看表结构
    	desc stu6;
    	insert into stu6(id,name,sex)values(1,'zsf','nv');
    	insert into stu6(id,name)values(2,'zsf');
        查看结构
    	select * from stu6;

**10.4引用完整性**  
主外键的关系，2个表之间建立管理关系之后，他们删除，更新操作都是受影响的。

##引用完整性
    学生表 
            id
           名字
            性别
    成绩表
             id
           学生ID
    	   成绩
    create table stu7(
         id int primary key,
    	 name varchar(50)
    	);
    create table score(
       id int primary key,
       sid int ,
       score double,
       constraint aa foreign key(sid) references stu7(id)
       );
       
       insert into stu7(id,name)values(1,'zsf');
       insert into score(id,sid,score)values(1,1,90);
       ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db_0402
       `.`score`, CONSTRAINT `aa` FOREIGN KEY (`sid`) REFERENCES `stu7` (`id`))

## 十一、表与表之间的关系 ##

**11.1为什么要拆表？**  
拆分表的目的：  
为了解决数据的冗余的问题，可以减少重复数据的储存，表的结构也更加清晰了。  
拆分之后：  
在某些业务下，需要查询多个表，速度降低了。  
空间 和 时间  
**11.2表与表之间的关系**  
11.2.1一对一  
人与身份证的关系：  
注意：  
（1）一对一不存在数据冗余问题 可以不拆分表吗？ 可以！  
（2）什么场景需要拆分  
用户信息 + 身份证+……>表中信息太多了。  
11.2.2一对多  
最最常见的关系：  
学生和成绩的关系：1个学生有多个成绩  
部门和员工的关系：1个部门有多个员工  
这种关系怎么去建立，怎么去表达？  
在多的一方存储“一”的主键ID。  
在成绩表中存储用户ID  
在员工表中存储部门ID  
建表：使用主外键实现。  
11.2.3多对多  
老师与学生的关系：一个老师对应多个学生，一个学生对应多位老师。  
这种关系怎么去建立，怎么去表达？  
用户和商品：订单（谁（用户ID）什么时候买了什么商品（商品ID））  
ID 用户ID 商品ID  
1001 1 6  
1002 1 5  
1003 2 6

用户和权限的关系：谁拥有什么权限  
ID 用户ID 权限ID  
1001 1 1001  
1002 1 1003  
1003 2 1001  
**11.3多表查询**  
多表查询查询有如下：  
（1）合并结果集：union，union all  
（2）连接查询：（列的合并）  
内连接：  
外连接：  
左外链接：  
右外连接：  
全连接：  
自然连接  
（3）子查询  
**11.4合并结果集（行连接）**  
2个结果集（表）合并到一起。  
要求：  
11.4.1union all（全部）  
将两个表的结果直接放在一起，不做任何处理。  
11.4.2union  
将两个表的结果直接放在一起，相同的数据只留下一个（去重）  
**11.5连接查询（列连接）**  
问题：学生表有10条数据，成绩有10条数据  
连接后有多少条数据（笛卡尔积）：10\*10  
连接查询会产生笛卡尔积；假设集合A=\{a，b\}，集合B=\{c，d\}，  
2个集合的笛卡尔积\{（a，c），（a，d），（b，c），（b，d）\}  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1hhaFk2Ng_size_16_color_FFFFFF_t_70 7]  
但是：  
（1）从数据来讲，有很多无用的数据。  
（2）通过 where条件过滤无用数据：通过主外键来清除无用数据

使用主外键清除无用数据
    select * from emp,dept where emp.deptno=dept.deptno;

![在这里插入图片描述][9c90e92437aa499c8ea76e7fbd1d2e0b.png]  
上面的写法就是内连接，但是它不在SQL的标准中  
`标准的写法：select * from emp e inner join dept d on e.deptno = d.deptno;` inner可以省略  
（1） Inner 可以省略不写  
（2） 内连接特点：查询的条件必须满足条件 不满足条件的数据将会丢失。  
部门 是40的数据就没有了，员工emp中没有40这个部门的人。  
11.5.2外连接  
A left join B ：A左连接 B表；A表是主表 A right join B：A右连接B表；B表是主表  
B left join A：B左连接A表； B表是主表 B right join A：B右连接A表；A表是主表  
特点：主表的数据全部都在，  
然后去匹配 非主表的 数据 匹配补上NULL

# 员工表 emp是主表，去匹配dept
    Select * from emp e left join dept d on e.depton= d.depton;
    ##部门表是主表，然后去匹配员工表
    Select * from emp e right join dept  d on e.depton=d.depton;

11.5.3自然连接  
自然连接：是一种特殊的等值连接，它要求两个关系进行比较的分量必须是相同的属性组，并且在结果集中将重复属性列去掉。

基本这些入门，勤加练习，需要勤能补拙

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1hhaFk2Ng_size_16_color_FFFFFF_t_70]: /images/20210813/489e93eb7bbd4b749289e7d41ec1bead.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1hhaFk2Ng_size_16_color_FFFFFF_t_70 1]: /images/20210813/7552a7eab21e468fb4d170a311e78c1e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1hhaFk2Ng_size_16_color_FFFFFF_t_70 2]: /images/20210813/5b4fc01079e34b698503be68eb79c007.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1hhaFk2Ng_size_16_color_FFFFFF_t_70 3]: /images/20210813/2888367c93d94994a83d7e6085aaa463.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1hhaFk2Ng_size_16_color_FFFFFF_t_70 4]: /images/20210813/4772e7c3f5d34ebf9f3e473f4a5498a5.png
[ca22e613712940029a801b98ddade17c.png]: /images/20210813/2c16fd7c04574ff68569a700c5c8813d.png
[e4742c8a428647e982a5be54ce0ca61f.png]: /images/20210813/feb0fc5b814940a4923994bc2a56b665.png
[82e798f03c5746ac9c7f06c13ca73929.png]: /images/20210813/9274450b1e6c46369e1e2ee5a54272a5.png
[17a70ae2962e474eba26d4ec00af40a6.png]: /images/20210813/3ccfb3e801cf449493f381349b24618a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1hhaFk2Ng_size_16_color_FFFFFF_t_70 5]: /images/20210813/2abed436e9564e809b764cf5523b9c25.png
[e8ec7cd1e41a4846bb18c0245ccee09d.png]: /images/20210813/abe07d6f965c4f3cbe8b9027c20b0e09.png
[07eefb92794b4c4b97f6837e691f7130.png]: /images/20210813/6af4a86a422a44a2904e5847deb43f04.png
[3b7589a6c0e94d718edc93e14c532534.png]: /images/20210813/535c875fdd284136abca3e20dac64cec.png
[484899f24a9d4efd944a2a3bb9b7c90f.png]: /images/20210813/6e6443ecbc8d41abb7dfe6050cfb901b.png
[5559e4e19b0940b78d8e01035554cae3.png]: /images/20210813/1ed18d97b5ad4dfebcdd0f671cdde8ad.png
[bc6eb5f234e647daa00786c6a87ea241.png]: /images/20210813/1e42f446b1974190b2d9ca3d9ee09bf2.png
[bf365c30f1ec4b59bffb957a7d609ac3.png]: /images/20210813/9bd0f8217a0e4b729fb074eeea69aead.png
[3e47162660da412eae550ec31e0a31d5.png]: /images/20210813/ee8c059ff37744f987b5e3cedbb7ea5f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1hhaFk2Ng_size_16_color_FFFFFF_t_70 6]: /images/20210813/713048192fd94e45858e8478392d29c3.png
[358aea4b777242c99c99bd0f12d721d6.png]: /images/20210813/127ee3adb1304fe9b83a78e53e3bf0e3.png
[2efc2f0207df483d946cab465884dac3.png]: /images/20210813/2770f0c2aece4b0fb42262c95ee823fc.png
[79f05311223642d5ac7c6e67a70598c1.png]: /images/20210813/9b134497bf934e0e91f9013b924f8ac8.png
[a41e06a99b3d4f168564244cdb16fd39.png]: /images/20210813/ad8d1fd45d52488fba6adb9c76b6d297.png
[b417ae7b6a794f9fadc5c5f33b24342e.png]: /images/20210813/7885fbfe2afd4c72bbe56b20a51020a0.png
[90924c7fd6b04cccaf118dfae26125f0.png]: /images/20210813/677514ce0d9542c8983503506f98d17d.png
[b01271cb3e174c4caa279e49c78a0ff6.png]: /images/20210813/f7e3c8fab6734cb7965e6babe06267de.png
[805d1833a2a54cf6b7f151451a172f4b.png]: /images/20210813/93f3a77ecbcf480e92f2029101e290a6.png
[5c9df2a846424f28a150650b77f325e2.png]: /images/20210813/a72baa30d4df4a89a2357b82cafc6ef0.png
[65673e76ffe74493b4ee5f126bcab869.png]: /images/20210813/5aed1d037d4a445a839972d3d2839e28.png
[c5ed6b202c0c4ae5817e01e06e1931bb.png]: /images/20210813/277af506118f4a979a112a193b86046d.png
[febe602ae38547228915d984880689df.png]: /images/20210813/8473af9ac29a43e2b157ab2f7e7f20e8.png
[302dbc1c802445fb94cca1cccdf7e1da.png]: /images/20210813/0964e26f60154fc2ab01e6eb2eece95e.png
[4742e540d2c0417e8b50d0650350779e.png]: /images/20210813/e0f1645ce68c44f3b904e88e33253763.png
[36ca67c65c7d4d5190c05466b9a34a57.png]: /images/20210813/4e72dc3eaaa84b1c967d77cafd789ff8.png
[3eddb0c4ef8b4fc195092dce1a534241.png]: /images/20210813/25a87d7660984623958cc4445480dddb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1hhaFk2Ng_size_16_color_FFFFFF_t_70 7]: /images/20210813/3aed9182fe7e49d8a4062f7f9b71ca3c.png
[9c90e92437aa499c8ea76e7fbd1d2e0b.png]: /images/20210813/b34966e57aea47bbb531ae84dbebe3ac.png