从概念到实操，一篇搞定MySQL基础内容

一 什么是SQL？

Structured Query Language：结构化查询语言
其实就是定义了操作所有关系型数据库的规则。每一种数据库操作的方式存在不一样的地方，称为“方言”。

二 SQL的分类

1.DDL：操作数据库，表
2.DML：增删改表中的数据
3.DQL：查询表中的记录
4.DML：定义数据库的访问权限和安全级别

1.DDL（操作数据库，表）

（1）操作数据库（CRUD）

• 创建数据库

create database if not exists mydb1；

• 查询数据库

show databases; --查询所有数据库的名称
show create database 数据库名称; --查询某个数据库的创建语句

• 修改数据库

alter database mydb1 character set utf8; --将mydb1的字符集改为utf-8

• 删除数据库

drop database if exists mydb1; --如果存在则删除

• 使用数据库

use mydb1;

（2）操作表（CRUD）

• 创建

语法：
create table 表名(
		列名1 数据类型1,
		列名2 数据类型2,
		....
		列名n 数据类型n
);
* 注意：最后一列，不需要加逗号（,）

常用数据类型：
1. int：整数类型
	* age int,
2. double:小数类型
	* score double(5,2)
3. date:日期，只包含年月日，yyyy-MM-dd
4. datetime:日期，包含年月日时分秒	 yyyy-MM-dd HH:mm:ss
5. timestamp:时间错类型	包含年月日时分秒	 yyyy-MM-dd HH:mm:ss	
	* 如果将来不给这个字段赋值，或赋值为null，则默认使用当前的系统时间，来自动赋值
6. varchar：字符串
	* name varchar(20):姓名最大20个字符
	* zhangsan 8个字符  张三 2个字符

• 复制

create table 表名 like 被复制的表名;	

• 查询

show tables; --查询所有表名
desc 表名; --查询表结构

• 修改

1. 修改表名
	alter table 表名 rename to 新的表名;
2. 修改字符集
	alter table 表名 character set 字符集名称;
3. 添加
	alter table 表名 add 列名 数据类型;
4. 删除
	alter table 表名 drop 列名;

• 删除

drop table [if exists] 表名;

2.DML（增删改表中的数据）

• 添加

语法：
insert into 表名（列名1，列名2.....） values（值1，值2.....）;
若需要修改所有的列，则列名可以省略
insert into 表名 values(值1，值2.....);

• 删除

delete from 表名 [where 条件]; --有多少条记录就会进行多少次删除，性能不好
truncate 表名; --先删除表，然后创建一张一样的表

• 修改

update 表名 set 列名1=值1, 列名2=值2... [where 条件];

3.DQL（查询表中的记录）

语法：
select
	字段列表
from
	表名列表
where
	条件
group by
	分组依据
having
	分组之后的条件
order by
	排序依据字段以及排序方式
limit
	分页（开始，数据条数）

（1）基础查询

1. 多个字段的查询
	select 字段名1，字段名2... from 表名；
	* 注意：
		* 如果查询所有字段，则可以使用*来替代字段列表。
2. 去除重复：
	* distinct
3. 计算列
	* 一般可以使用四则运算计算一些列的值。（一般只会进行数值型的计算）
	* ifnull(表达式1,表达式2)：null参与的运算，计算结果都为null
		* 表达式1：哪个字段需要判断是否为null
		* 如果该字段为null后的替换值。
4. 起别名：
	* as：as也可以省略

（2）条件查询

语法：
select 字段列表 from 表名 where 条件;
1. 多个字段的查询
	select 字段名1，字段名2... from 表名；
	* 注意：
		* 如果查询所有字段，则可以使用*来替代字段列表。
2. 去除重复：
	* distinct
3. 计算列
	* 一般可以使用四则运算计算一些列的值。（一般只会进行数值型的计算）
	* ifnull(表达式1,表达式2)：null参与的运算，计算结果都为null
		* 表达式1：哪个字段需要判断是否为null
		* 如果该字段为null后的替换值。
4. 起别名：
	* as：as也可以省略

例子：

-- 查询年龄大于等于20 小于等于30
select* from student where age >= 20 &&  age <=30;
select * from student where age >= 20 and  age <=30;
select * from student where age between 20 and 30;
-- 查询姓刘的有哪些？ like
select* from student where NAME like '刘%';
-- 查询姓名第二个字是非的人
select * from student where NAME like "_非%";
-- 查询姓名是3个字的人
select * from student where NAME like '___';
-- 查询姓名中包含天的人
select * from student where NAME like '%天%';
-- 查询年龄22岁，18岁，25岁的信息
select * from student where age in (22,18,25);

（3）排序查询

语法：order by 子句
		 order by 排序字段1 排序方式1 ，  排序字段2 排序方式2...
排序方式：
			ASC：升序，默认的。
		    DESC：降序。

注意：
			如果有多个排序条件，则当前边的条件值一样时，才会判断第二条件。

（4）聚合函数

1. count：计算个数
	1. 一般选择非空的列：主键
	2. count(*)
2. max：计算最大值
3. min：计算最小值
4. sum：计算和
5. avg：计算平均值

例子：

select max(math) from student;
select count(name) from student;
select avg(math) from student;

（5）分组查询

1. 语法：group by 分组字段；
		2. 注意：
			1. 分组之后查询的字段只能是：分组字段、聚合函数
			2. where 和 having 的区别？
				1. where 在分组之前进行限定，如果不满足条件，则不参与分组。having在分组之后进行限定，如果不满足结果，则不会被查询出来
				2. where 后不可以跟聚合函数，having可以进行聚合函数的判断。

（6）分页查询

1. 语法：limit 开始的索引,每页查询的条数;
2. 公式：开始的索引 = （当前的页码 - 1） * 每页显示的条数
			-- 每页显示3条记录 
3. limit 是一个MySQL"方言"

select* from student limit 0,3; -- 第1页			
select * from student limit 3,3; -- 第2页		

三 约束

概念

对表中的数据进行限定，保证数据的正确性，有效性和完整性

1 主键约束 primary key

1. 注意：
    1. 含义：非空且唯一
    2. 一张表只能有一个字段为主键
    3. 主键就是表中记录的唯一标识

2. 在创建表时，添加主键约束
    create table stu(
       id int primary key,-- 给id添加主键约束
       name varchar(20)
    );
3. 删除主键
   alter table stu drop primary key;
4. 创建完表后，添加主键
   alter table stu modify id int primary key;
5. 自动增长：
   1.  概念：如果某一列是数值类型的，使用 auto_increment 可以来完成值得自动增长
   2. 在创建表时，添加主键约束，并且完成主键自增长
        create table stu(
            id int primary key auto_increment,-- 给id添加主键约束
            name varchar(20)
        );
   3. 删除自动增长
        alter table stu modify id int;
   4. 添加自动增长
        alter table stu modify id int auto_increment;

2 非空约束 not null

1. 创建表时添加约束
        create table stu(
            id int,
            name varchar(20) not null -- name为非空
        );
2. 创建表完后，添加非空约束
        alter table stu modify name varchar(20) not null;
3. 删除name的非空约束
        alter table stu modify name varchar(20);

3 唯一约束 unique

1. 创建表时，添加唯一约束
    create table stu(
         id int,
          phone_number varchar(20) unique -- 添加了唯一约束
	);
    * 注意mysql中，唯一约束限定的列的值可以有多个null
2. 删除唯一约束
	alter table stu drop index phone_number;
3. 在创建表后，添加唯一约束
    alter table stu modify phone_number varchar(20) unique;

4 外键约束 foreign key

1. 在创建表时，可以添加外键
   * 语法：
      create table 表名(
         ....
         外键列
         constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)
      );
2. 删除外键
      alter table 表名 drop foreign key 外键名称;
3. 创建表之后，添加外键
      alter table 表名 add constraint 外键名称 foreign key (外键字段名称) references 主表名称(主表列名称);
4. 级联操作
   1. 添加级联操作
      语法：alter table 表名 add constraint 外键名称 
           foreign key (外键字段名称) references 主表名称(主表列名称) on update cascade on delete cascade;
   2. 分类：
      1. 级联更新：on update cascade 
      2. 级联删除：on delete cascade 

四 备份

1. 命令行：
    * 语法：
        * 备份： mysqldump -u用户名 -p密码 数据库名称 > 保存的路径
        * 还原：
            1. 登录数据库
            2. 创建数据库
            3. 使用数据库
            4. 执行文件 ：source 文件路径

五 多表查询

1 笛卡尔积

• 有两个集合A,B .取这两个集合的所有组成情况。
• 要完成多表查询，需要消除无用的数据（使用隐式内连接where，显式内连接 join on，左外连接left join on，右外连接 right join on）
• 笛卡尔积是所有的排列组合情况，接下来的筛选条件就是负责在这些所有的情况中选出合适的数据

2 多表查询分类

（1）内连接查询

• 隐式内连接查询：where

例子：
-- 查询员工表的名称，性别。部门表的名称
select
	t1.name,
	t1.gender,
	t2.name
from
	emp t1,
	dept2
where
	t1.dept_id = t2.id;

• 显示内连接查询 :[inner] join on

语法：select 字段列表 from 表名1 [inner] join 表名2 on 条件
例子：
select * from emp inner join dept on emp.id = dept.id;

内连接查询要关注:
	1.从哪些表中查数据
	2.条件是什么
	3.查询哪些字段

（2）外连接查询

• 左外连接查询:left [outer] join``

查询的是左表的所有数据，以及两表的交集部分
select 
	t1.*,t2.name 
from
	emp t1 --左表
left join 
	dept t2 
on 
	t1.dept_id = t2.id;

• 右外连接查询:right [outer] join
  如上

（3）子查询

• 概念：查询中嵌套着查询
• 不同情况：
  （1）子查询结果是单行单列

select * from emp where emp.salary < (select avg(salary) from emp);

（2）子查询结果是多行单列

-- 查询财务部和法务部所有员工信息
select * from emp where dept_id in (select id from dept where name = '财务部' or name = '法务部'); 

（3）子查询结果的多行多列

select 
	* 
from 
	dept t1,
	(select * from emp where emp.join_date > 2022-1-1) t2 
where 
	t1.id = t2.dept_id;