MySQL

一些示例

主键用bigint
boolean用tinyint
日期用datetime

drop table if exists base_company;
create table base_company
(
    company_id                bigint            not null comment '公司ID主键'
        primary key,
    app_id                    varchar(255)      not null comment 'appId',
    app_secret                varchar(255)      not null comment 'app密钥',
    token                     varchar(255)      not null comment '通过app_id和app_secret生成的有时效性的token',
    delete_flag               tinyint default 0 not null comment '删除标志',
    default_config_id         bigint            not null comment '默认配置id',

    sync_frequency            int(11)           not null comment '同步频率',
    sync_method               int(11)           not null comment '同步方式',
    be_create_global_calendar tinyint           not null comment '是否已经创建全局日历',
    be_open_global_calendar   tinyint           not null comment '是否开启全局日历',
    lark_global_calendar_id   varchar(255)      null comment '飞书全局日历id',

    version                   bigint     default 1 comment '行版本号，用来处理锁',
    created_by                bigint(20) default 0 comment '创建人',
    creation_date             datetime   default CURRENT_TIMESTAMP comment '创建时间',
    last_updated_by           bigint(20) default 0 comment '最后更新人',
    last_update_date          datetime   default CURRENT_TIMESTAMP comment '最后更新时间',
    attribute1                varchar(255) comment '备用字段1',
    attribute2                varchar(255) comment '备用字段2',
    attribute3                varchar(255) comment '备用字段3',
    attribute4                varchar(255) comment '备用字段4',
    attribute5                varchar(255) comment '备用字段5'
)
comment '公司表';

概论

数据（元组、行）
字段（属性、列）应该有字段名、数据类型、约束

SQL分为：
DQL数据查询语言：查询语句
DML数据操作语言：insert delete updat，对表中数据的增删改
DDL数据定义语言：create drop alter，对表结构的增删改，例如删除字段。create创建表，drop删除表，alter修改表
TCL事务控制语言：commit提交事务，rollback回滚事务
DCL数据控制语言：grant授权、revoke撤销授权

导入数据

第一步：登录mysql数据库管理系统
dos窗口
mysql -uroot -p
密码
第二步：查看有哪些数据库
show databases；(这个不是SQL语句，属于MySQL的命令，其他数据库不通用)

±-------------------+
| Database |
±-------------------+
| information_schema |
| mysql |
| performance_schema |
| sakila |
| sys |
| world |

第三步：创建我们自己的数据库
create database daihan;(这个不是SQL语句，属于MySQL的命令，其他数据库不通用)

第四步：使用daihan数据
use daihan；(这个不是SQL语句，属于MySQL的命令，其他数据库不通用)

第五步：查看表
show tables；(这个不是SQL语句，属于MySQL的命令，其他数据库不通用)

第六步：初始化数据
source 绝对路径;
//使用source命令可以执行sql脚本

第七步：查看表结构
use daihan;
show tables;
desc 表名;//查看表结构
第八步：查看数据
select* from 表名；

删除数据库：

drop database daihan;

注：
使用use xxx；切换至xxx数据库
使用show tables 查看当前数据库有哪些表
.sql结尾的文件被称为“sql脚本”。sql脚本：以.sql结尾，并且在文件中编写了大量的sql语句

常用命令：
select database();查看当前使用的哪个数据库
select version();查看mysql版本号
\c;结束一条语句
exit ；退出
show create table emp；查看创建表的语句

DQL

简单查询

语句语法：
select 字段名1，字段名2，字段名3… from 表名；

给查询结果的列重命名
select 字段名1 as 新名字1 from 表名；
//as可以省略
//有中文则‘xxx’
//查询所有字段，字段名处只需写一个*

SQL语句不区分大小写
字段名可以写表达式，例如数学运算 字段名1 *12

条件查询语法：
select 字段，字段
from 表
where 条件and条件or条件；

条件符号：< ,>,<>,<=,>=,between…and…闭区间,is null,is not null,

between…and…也可以用在字符上，左闭右开

and 和 or 多加括号

in作用等同于or 字段 in（值1，值2）例如 where job in （‘aasd’，‘asd’）
not in

like 模糊查询
%代表多个字符 _ 代表一个字符
select ename from emp where ename like ‘%o%’；//含有o的
select ename from emp where ename like ‘_o%’；//第二个字母是o的

order by 字段名 //排序，默认升序
order by 字段名 asc； //指定升序
order by 字段名 desc；//指定降序

当字段名1相同的时候，按照字段名2排序：
select name，sal from emp order by 字段名1 desc，字段名2 asc；

分组函数5个

分组函数是对”某一组“（某一列）数据进行操作

count 计数
sum 求和
avg 平均值
max 最大值
min 最小值

select sum(sal) from emp; //对emp表中sal求和
select max(sal) from emp; //找出最高ssal

注：**分组函数自动忽视NULL

count()和count(具体的字段)区别：
count()统计总的数据数；count(具体的字段)统计的是这个字段不为NULL的数据数

分组函数也能组合：
select count(),sum(),avg(sal) from emp;

单行处理函数

通过月薪sal计算年薪
select ename,sal*12 as yearsal from emp

注：计算式中有NULL，计算结果一定是NULL

ifnull(可能为null的数据，将其当作什么处理) //属于单行处理函数
ifnull(sal，0)
select ename,ifnull(sal,0)*12 as yearsal from emp

group by 和 having

group by：按照某个字段或某些字段进行分组
having：对分组之后的数据再次进行过滤

例：找出每个岗位的最高薪资
select max(sal),job from emp group by job;

如果再添加ename：select ename,max(sal),job from emp group by job;
会出现随机5个无意义的ename强行匹配
总结：当一条语句有group by的话 select只能跟参加分组的字段和分组函数

注：
分组函数常和group by联合使用
分组函数只会在group by执行之后执行
group by是在where之后执行

having例:
找出每个部门的最高薪资，要求显示薪资大于2500的数据
select max(sal),deptno from emp group by deptno;//先写上半句，再改进
select max(sal),deptno from emp group by deptno having max(sal)>2500;//效率低
再改进：select max(sal),deptno from emp where sal>2500 group by deptno;//where会先过滤，再分组

总结：where能解决就用where
where不能解决例：
找出部门平均薪资大于2000的
select avg(sal),deptno from emp group by deptno having avg(sal)>2000;
//这里不能用where，因为where后面不能使用分组函数

多字段联合分组
例：找出每个部门deptno不同岗位job的最高薪资sal
select
max(sal),job,deptno
from
emp
group by
job,deptno;

查询结果去重：

select后加上distinct
注：distinct只能出现在所有字段最前方，表示后面的字段联合去重

count(distinct job)去重之后统计数量
语法规则：

顺序
5 select 查出来
1 from 从哪里查
2 where 过滤
3 group by分组
4 having 再过滤
6 order by排序

分组函数不可直接出现在where语句中
错误：select ename,sala from emp where sal>avg(asl);//ERROR 1111：Invalid use of group function
原因：分组函数在group by之后，group by在where之后，冲突了
改正：select ename,sal from emp where sal>(select avg(sal) from emp);//先计算出平均工资，嵌套子查询

找出每个岗位的最高薪资
select max(sal),job from emp group by job;
如果再添加ename：select ename,max(sal),job from emp group by job;
错误：会出现随机5个无意义的ename强行匹配，有结果无意义
总结：**当一条语句有group by的话 select只能跟参加分组的字段和分组函数

UNION

将查询结果集相加

案例：找出工作是SALESMAN和MANAGER的员工
select ename,job from emp where job=‘SALESMAN’ or jor =‘MANAGER’;
select ename,job from emp where job in (‘SALESMAN’,‘MANAGER’);
select ename,job from emp where job=‘SALESMAN’ union select ename,job from emp where job=‘MANAGER’;

LIMIT

是mysql特有的
取结果集中的部分数据
语法：
limit startIndex,length
//startIndex表示其实位置，length表示取几个

案例：取出工资前5的
select ename,sal from emp order by sal desc limit 0,5;

//limit 最后执行

连接查询

连接查询
在多数开发中，都不是在单表中查询。需要多张表联合查询取出最终结果

查询连接的分类：

内连接：
等值连接：
非等值连接：
自连接：
外连接：
左外连接：
右外连接：
全连接：

笛卡尔乘积现象：
在表的连接查询的时候，如果没有条件限制，那么查询结果条数等于两张表条数乘积
select ename,dname from emp,dept;
改进：select e.ename,d.dname from emp e,dept d;//取别名，省略as。可以提升效率

避免笛卡儿积现象：添加条件
注：添加条件并不会减少记录比较次数。只是减少了显示的有效记录

外连接和内连接区别：

内连接：假设A和B表进行连接，凡是A和B能匹配上的数据查询出来。A、B没有主副之分。返回交集
外连接：A和B表进行外连接的时候，有一个是主表另一个是附表。主要查询主表，捎带查询附表
如果附表没有匹配上，会自动模拟null与之匹配

内连接

select e.name,d.name
from emp e,dept d
where e.deptno=d.deptno
//SQL 92 以后不适用

select
e.name,d.name
from
emp e
jion
dept d
on
e.deptno=d.deptno
//SQL99 常用
//join / inner join 省略了inner 内连接
//底层实际还是匹配了笛卡尔乘积那样那么多次

内连接中的等值连接

同上
语法：
…
A
join
B
on
连接条件
where
…

内连接中的非等值连接

查找
select
e.name,s.sal,s.grade
from
emp e
jion
salgrade s
on
e.sal between s.losal and s.hisal;

自连接

特点是一张表看作两张
例：表emp找出每个员工的上级领导，要求显示员工名和对应领导名（员工号empno,员工名ename,对应领导号mgr）
select
a.ename,b.ename
from
emp a
jion
emp b
on
a.mgr=b.ename

注：自连接也能是非等量条件

外连接

最重要的是主表的数据无条件查询出来

和内连接区别：
内连接：假设A和B表进行连接，凡是A和B能匹配上的数据查询出来。A、B没有主副之分
外连接：A和B表进行外连接的时候，有一个是主表另一个是附表。主要查询主表，捎带查询附表
如果附表没有匹配上，会自动模拟null与之匹配

左外连接：左边是主表
右外连接：右边是主表

例：表emp找出每个员工的上级领导，要求显示员工名和对应领导名，必须显示所有员工
员工号empno,员工名ename,对应领导号mgr
所有都显示应该使用外连接
select
a.ename,b.ename
from
emp a
left outer jion
emp b
on
a.mgr=b.ename

//left jion 左连接 左主表。outer可以省略

//区分inner 和 outer 靠是否有left和right

三张表连接

多表连接语法：
A
join
B
join
C
on
…
//A先和B连接，然后再和C连接

内连接：

例：找出每个员工的部门名称和工资等级
emp表empno,ename,deptno,sal empno员工编号 ename员工名 deptno部门编号 sal 工资
dept表deptno,dname deptno部门标号 dname部门名称
salgrade表grade,losal,hisal grade等级 losal最低薪资 hisal最高薪资

select
e.ename,d.dname,s.grade
from
emp e
join
dept d
on
e.deptno=d.deptno
join
salgrade s
on
e.sal between s.losal and s.hisal;

外连接

例：找出员工的部门名称，工资等级，上级领导
//要保留每一个员工，所以内连接工资等级，外连接上级领导

select
e.ename,d.dname,s.grade
from
emp e
join
dept d
on
e.deptno=d.deptno
join
salgrade s
on
e.sal between s.losal and s.hisal
left join
emp e1
on
e.mgr=e1.empno;

//emp表的mgr字段表示领导编号

where后子查询

子查询：在select语句中嵌套select语句，被嵌套的称为子查询。
语法：
select
…(select)…
from
…(select)…
where
…(select)…

案例一：

select * from emp where sal>(select avg(sal) from emp);

案例二：找出每个部门平均薪资的薪资等级

第一步：找出部门平均薪水
select deptno,avg(sal) as avgsal from emp group by deptno;
第二步：把第一步的结果当作一个新表t，让t表和salgrade表连接，条件是t.avgsal between s.losal and s.hisal;
select t.*,s.grade
from t
join s
on t.avgsal between s.losal and s.hisal;

合并
select t.*,s.grade
from (select deptno,avg(sal) as avgsal from emp group by deptno) t
join s
on t.avgsal between s.losal and s.hisal;

from语句中使用，重点

案例三：找出每个员工所在部门名称，要求显示员工名和部门名·

select e.ename,e.deptno(select d.dname from dept d where e.deptno=d.deptno)as dname from emp;
//select子句中使用

DDL & DML

创建表

语法：
create table 表名(
字段名1 数据类型，
字段名2 数据类型，
字段名3 数据类型，
…
);

注：表名最好以t_ 或 tbl_ 开头

字段的常见数据类型
int 整数型
bigint 长整型
float 浮点型
char 定长字符串
varchar 不定长字符串
date 日期类型
BLOB 二进制大对象(图片、视频等)
CLOB 字符大对象(较大文本)

char和varchar怎么选择
char：固定分配长度，不够长补偿，超出报错 效率高
varchar：动态分配长度，超出报错 效率低

default关键字可以设置默认值

例如：创建学生信息表
学生信息：学号、姓名、性别、班级编号、生日
学号 bigint
姓名 varchar
性别 char
班级 int
生日 date

create table t_student(
no bigint,
name varchar(255),
sex char(1),
classno varchar(255),
birth char(10)
);

create table t_student(
no bigint,
name varchar(255),
sex char(1) default 1，
classno varchar(255),
birth char(10)
);
//sex 默认值是1

创建表，加入约束

常见约束：
非空约束 not null 不能为null
唯一约束 unique 不能重复
主键约束 primary key 既不能null也不能重复
外键约束 foreign key

检查约束 check Oracle有MySQL没有

非空约束not null

案例：
drop table if exists t_user;
create table t_user (
id int,
username varchar(255) not null,
password varchar(255)
);
insert into t_user(id,username,password)values(1,‘123’,‘abc’);//成功
insert into t_user(id,password)values(1,‘abc’);//失败，ERROR 1364:Field ‘username’ doesn’t have a default value

唯一性约束

不能重复，但可以为null，或者多个null。单个字段唯一性约束//列级约束
案例：
drop table if exists t_user;
create table t_user (
id int,
username varchar(255)
unique,
password varchar(255)
);
insert into t_user(id,username)values(1,‘abc’);//成功
insert into t_user(id,username)values(1,‘abc’);。//失败，ERROR 1062:Duplicate entry ‘abc’ for key ‘t_user.username’

**两个字段"联合"起来具有唯一性//又称表级约束
create table t_user (
id int,
username varchar(255),
password varchar(255),
unique(username,password)
);

主键约束

案例：
drop table if exists t_user;
create table t_user (
id int primary key,
username varchar(255) unique,
password varchar(255)
);
insert into t_user(id,username,password)values(1,‘123’,‘abc’);

作用：这行记录在表中的唯一标识

主键分类：
根据主键字段数量：单一主键(推荐)、符合主键
根据主键性质：自然主键(推荐)、业务主键

主键数量：只能有一个

注：
MySQL提供主键自增： auto_increment
例：
drop table if exists t_user;
create table t_user (
id int primary key auto_increment,
username varchar(255) unique,
password varchar(255)
);

外键约束

外键约束：foreignkey
外键字段：添加外键约束的字段
外键值： 外键字段中的每个值

业务例子：
设计数据库表维护学生和班级信息
班级表：班级编号（主键），班级名
学生表：学生编号（主键），学生名，班级编号（外键） //填了fk之后，字段里面的值必须来自某个字段

语法：
foreign key(子表字段) references 父表名(父表字段)

外键值可以为null

外键引用别的表的字段，被引用字段不一定是主键，但必须有唯一性unique

案例：
以上条件下，学生表是子表，班级表是父表。先创建父表再创建子表，删除的时候先删除子表，添加的时候先附表。

drop table if exists t_class;
drop table if exists t_student;
create table t_class(
cno int ,
cname varchar(255),
primary key(cno)
);
create table t_student(
sno int,
sname varchar(255),
classno int,
primary key(sno),
foreign key(classno) references t_class(cno)
);
insert into t_class values(101,‘xxxxxxxxxxxx’);
insert into t_class values(102,‘xxxxxxxxxxxx’);
insert into t_student values(1,‘zs1’,101);
insert into t_student values(2,‘zs2’,101);
insert into t_student values(3,‘zs3’,102);
insert into t_student values(4,‘zs4’,102);
insert into t_student values(5,‘zs5’,102);

select *from t_class;
select *from t_student;

insert into t_student values(5,‘zs5’,103);
错误：ERROR 1452 Cannot add or update a child row: a foreign key constraint fails

插入数据

语法格式：
insert into 表名(字段名1，字段名2，…) values (值1，值2，…)
要求：字段的数量和值的数量相同，并且数据类型要对应

insert into t_student(no,name,sex,classno,birth) values (1,‘zhangsan’,‘1’,‘gaosan1ban’ );
//错误：ERROR 1136 (21S01): Column count doesn’t match value count at row 1

insert into t_student(no,name,sex,classno,birth) values (1,‘zhangsan’,‘1’,‘gaosan1ban’, ‘190-10-12’);
//成功：Query OK, 1 row affected (0.00 sec)

改顺序：
insert into t_student(name,sex,classno,birth,no) values (‘lisi’,‘1’,‘gaosan1ban’, ‘190-10-12’,2);
//成功：Query OK, 1 row affected (0.00 sec)

如果字段不齐全，自动在其他字段补null：
insert into t_student(no,name) values (3,‘wangwu’);
//插入： 3 | wangwu | NULL | NULL | NULL

表的复制nb

create table t_stu as select * from t_student;

语法：
create table 表名 as select 语句；

将查询结果插入
insert into 表名 select 语句；

修改数据 update

语法：
update 表名 set 字段名1=值1，字段名2=值2… where 条件；
注意：没有条件表示表全部更新

案例：把学号3改成13
update t_stu set no=13 where no=3;

删除数据

语法：
delete from 表名 where 条件;
注意：没有条件表示表全部删除

删除大表

truncate table t_stu;//表被截断 ，不可回滚，永久丢失

事务，TCL

一个事务是一个完整的业务逻辑单元，不可再分
一个事务就是多条DML捆绑在一起
例：银行账户转账，从A账户转到B账户
update t_tact set balance=balance -10000 where actno=‘act-001’;
update t_tact set balance=balance +10000 where actno=‘act-002’;
以上两条语句必须同时成功或者同时失败。这时就需要使用“事务”机制

和事务相关的只有DNL语句（insert、delete、update）

事务的语句TCL：commit 提交 , rollback 回滚 ，savepoint 保存点

事务的特性：
原子性：事务是最小逻辑结构，不可再分
一致性：事务必须保证所含的DNL语句同时 成功或失败
隔离性：事务A与B之间具有隔离
持久性：最终数据必须持久化到硬盘，事务才算结束

事务之间的隔离性： 4各级别
第一级别：读未提交Read Uncommitted，当前事务可以读到对方事务未提交的数据，存在脏读现象
第二级别：读已提交Read Committed，对方事务提交之后的数据，我方可读取，解决了脏读，但存在不可重复的
第三级别：可重复读Repeatable Read，解决了不可重复读，存在读取的数据是幻像
第四级别：两个事务不能并发Serializable，序列化/串行化，存在效率低

mysql默认第三级别，事务默认情况自动提交–执行一条DML语句就提交
关闭自动提交：start transaction;

rollback；//回滚到上一次的提交点，事务结束，下次需再次开启事务

案例：
create 建表
插入数据； //自动commit
start transaction;//关闭自动提交，开启事务
insert …;
insert …;
select …; //能查出三行数据
rollback；//回滚，事务结束，下次需再次开启事务
select …;// 只有一条数据

start transaction;//再次开启事务
insert …;
insert …;
select …; //查出三行数据
commit; //提交数据，事务结束
select …; //查出三行数据
rollback；//回滚，回滚无效

索引

索引：相当于书的目录，在数据库，查询数据的时候有两种方式，一种是全表扫描；另一种是根据索引检索（效率高）
原理：缩小了扫描的范围

select ename from emp where ename= ‘asd’;
ename 没有索引的时候，会进行全表扫描
ename有索引的时候，会根据索引扫描

创建索引

create index 索引名称 on 表名(字段名)；

删除索引

drop index 索引名称 on 表名；

什么情况下加索引

1、数据量很大
2、字段很少DML操作（字段进行DML操作，索引需要维护）
3、经常出现在where子句中

注：主键和具有UNIQUE约束的字段会自动添加索引。所以根据主键查找效率高

索引的原理：B+树

索引分类：
单一索引：给单个字段添加索引
复合索引：几个字段联合起来添加索引
主键索引：主键字段自动添加索引
唯一索引：unique字段自动添加索引

索引失效
1、select ename from emp where ename like ‘%A%’; //模糊查询，第一个通配符使用%，会失效

视图

对于同一张表，通过不同角度去看

创建视图
create view myview as select empno,ename,from emp;

删除视图
drop view myview;

注：可以对视图进行增删改查，但是会影响原表数据。

作用：
可以隐藏表的实现细节

三范式

第一范式
任何表都有主键，属性字段不可再分

第二范式
所有非主键字段完全依赖于主键，不能部分依赖
例：多对对，三张表，两个外键
t_student 学生表
sno(pk)	sname
 --------------------------
1	张三
2	李四
3	王五
4	赵六

t_teacher教师表
tno(pk)	tname
 -------------------
1	王老师
2	张老师
3	李老师

t_student-teacher教师学生表
id(pk)	sno(fk)	tno(fk)		
 --------------------------
1	1	3
2	1	1
3	2	2
4	2	3
5	3	1
6	3	3

第三范式
所有非主键字段，直接依赖主键，不能传递依赖
一对多，两张表，”多“的表加外键