初入数据库

SQL：操作关系型数据库的编程语言，定义了一套操作关系型数据库的统一标准。

DDL（Data Definition Language）数据定义语言

数据库

• show databases;
• create database db01;
• use db01;
• select database(); 显示当前使用的数据库
• drop database db01; 删除数据库

表

• 创建
  create table 表名（
  字段1 字段类型 [约束] [comment 字段注释]
  …
  字段n 字段类型 [约束] [comment 字段注释]
  ）[comment 表注释];
• 约束：作用在表中字段上的规则，用于限制存储在表中的数据，目的：保证数据库中数据的正确性、有效性和完整性。
  
• 查询
  • show tables 查询当前数据库所有表
  • desc 表名 查询表结构
  • show create table 表名 查询建表语句
• 修改
  
• 删除
  drop table [if exists] 表名;
  删除表时，表中数据都会被删除

DML(Data Manipulation Language) 数据操作语言

添加数据INSERT

修改数据UPDATE

如果没有添加where条件则代表更新所有行的对应字段。

删除数据DELETE

如果没有添加where条件则代表删除所有行的。
如果目的是要删除某个字段的值，无法使用delete可以使用UPDATE，将字段值更新为NULL。

DQL(Data Query Language) 数据查询语言

普通查询

条件查询

聚合函数

将一列数据作为一个整体，进行纵向计算。
select 聚合函数(字段列表) from 表名

count

count(字段)：统计该字段非null值的个数
count(*)：求行数

max

max(字段)：找最大值

min

min(字段)：找最小值

avg

avg(字段)：求该字段所有值的平均值

sum

sum(字段)：求该字段所有值的和

分组查询

group by ：分组字段
having ：条件筛选过滤
分组查询中select可以查询的字段为分组字段和聚合函数

select gender from tb_emp group by gender;
1

select gender,count(*) from tb_emp group by gender;
1

若是分组过后的过滤要将条件放在having中

select job,count(*) from tb_emp where entrydate<='2015-01-01' group by job having count(*) >=2;
1

执行顺序： where->聚合函数->having
where和having区别：

• 执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。
• 判断条件不同：where不能对聚合函数进行判断；而having可以对聚合函数进行判断。

排序查询

升序：ASC （默认） ， 降序：DESC

嵌套排序

若一级排序值相同，根据二级排序继续比较

select * from tb_emp order by entrydate, update_time desc ;
1

分页查询

起始索引计算公式：( 页码 - 1 ) * 每页展示记录数

样例

-- 查询用户表：条件：姓名：某张某，性别：男，入职时间：2002-01-01  2015-01-01

select *
from tb_emp
where name like '%张%'
  and gender = 1
  and entrydate between '2002-01-01' and '2015-01-01'
order by update_time desc
limit 0,10;
1
2
3
4
5
6
7
8
9

if判断式：if(条件判断式，true取值，false取值)，可以用于查询结果重命名

select if(gender = 1, '男性员工', '女性员工') 性别, count(*)
from tb_emp
group by gender;
1
2
3

'性别’是给字段进行重命名，省略了as。

case表达式：case 表达式 when 值1 then 结果1 when 值2 then 结果2 .... else 结果n end

select (case job when 1 then '班主任' when 2 then '讲师' when 3 then '学工主管' when 4 then '教研主管' else '未分配' end) 职位,
       count(*)
from tb_emp
group by job;
1
2
3
4

多表设计

外键

当一个字段中的取值被外部表作为外键关联时，这个字段的这个项无法删除

物理外键

使用foreign key 定义外键关联的另外一张表称为物理外键：影响增删改的效率(要检查外键关系)；仅用于单节点数据库，不适用于分布式、集群场景；容易引发数据库的死锁问题，消耗性能。

逻辑外键

在业务层逻辑中，解决外键关联。通过逻辑外键，可以很方便解决上述问题。

表对应关系

一对多

eg：部门和员工
一的一方成为父表，多的一方称为子表
实现方式：在数据库中多的一方，添加字段，来关联一的一方的主键(外键)
如果一个表的一个字段是关联至另一个表的，则我们要进行外键处理，否则其实只是字段名相同而已，并无任何关系

一对一

eg：用户和身份证
多用于单表拆分，将一张表的基础字段放在一张表中，其他字段放在另一张表中，以提升效率。
实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)

多对多

eg：学生和课程
实现方法：建立第三张表作为中间表，中间表至少包含两个外键，分别关联两方主键。

多表查询

查询时如果直接使用select * from 表1，表2，得到的结果为两个表的笛卡尔积。
所以我们要进行筛选，如加上where tb_emp.dept_id = tb_dept.id;

连接查询

内连接

相当于查询A，B交集部分的数据

隐式内连接

select 表1.name，表2.name from 表1 表2 where 表1.two_id = 表2.id;
1

显式内连接

select 表1.name，表2.name from 表1 inner join 表2 on 表1.two_id = 表2.id;
1

Tips:还可以进行表的重命名

select a.name b.name from 表1 a 表2 b where a.two_id = b.id;
1

外连接

左外连接

查询左表所有数据(包括两张表交集部分数据)
完全包含表1的数据

右外连接

查询右表所有数据(包括两张表交集部分数据)
完全包含表2的数据

子查询