Linux

以SQL语言为底层的关系型数据库

MySQL

  1. 关系型数据库
    • 保证数据的完整性和一致性
    1. 完整性
      • 实体记录（每一个实体记录都是独一无二的，没有冗余，主键/唯一索引）
      • 参照（外键）
      • 域存储的数据都是有效的数据（数据类型/数据长度/非空约束/默认值约束/检查约束）
    2. 一致性（事务）
      • 要么全部成功，要么全部失败
      1. 原子性
        • 操作不可分割
      2. 一致性
        • 事务前后数据状态要一致
      3. 隔离性
        • 并发的多事务不能知道彼此的中间状态
      4. 持久性
        • 事务完成后的结果要反映在物理存储上
  2. SQL
    • DDL（数据定义语言）
    • DML（数据操作语言）
    • DCL（数据控制语言）
  3. DDL
    • create（创建）
    • drop（删除）
    • alter（修改）
  4. DML
    • insert（插入）
    • delete（删除）
    • update（更新）
    • select（查询）
  5. DCL
    • grant（授权）
    • revoke（移除授权）

规范

  1. 给数据库和表命名时尽量选择全小写
  2. 作为筛选条件的字符串是否区分大小写看设置的校对规则
  3. 数据库中的对象通常会用前缀加以区分
  4. 建立外键时双方的列的数据类型必须保持一致
  5. 通常不推荐使用in或者not in集合运算和distinct去重操作（可以考虑用exists或者not exists代替集合运算的去重操作）
  6. 写查询语句时，尽量用正向条件，而不是负向条件
  7. *某些情况会导致底层查询两次
  8. 中文或特殊字符的数据会比字母数字下划线组合的数据查询速度要慢且储存空间占用多

命令

控制MySQL行为的指令

命令注释
select version()显示当前数据库的版本
show databases显示全部数据库
create database 数据库名 default charset 字符编码创建一个指定数据库并指定字符编码
use 数据库名切换指定数据库
show tables显示当前数据库的二维表
drop database 数据库名删除指定数据库
create table 表名 (表的属性)在当前数据库定义一张表
comment '注释内容'注释
drop table 表名删除指定表
alter table 表名 add column 列名 列的数据类型指定表中追加指定列并指定列的数据类型
alter table 表名 drop column 列名指定表中删除指定列
alter table 表名 change column 列名01 列名02 列的属性修改指定列
insert into 表名 values (值01,值02)向指定表中追加一行数据（一行中从左到右的各列数据，主键列的数据不可重复）
insert into 表名 (列名01,列名02) values (值01,值02)向指定表中追加一行指定列的数据（假设仅追加2列，那么剩余列的属性要内容可为空）
insert into 表名 values (值01,值02),(值03,值04)向指定表中追加多行数据（default：默认值，需要配置默认属性）
delete from 表名 where 列名=值删除指定表中的符合条件的行，where：过滤条件（<>：不等号，is null/is not null：判断是否为空值）
truncate table 表名彻底删除指定表（截断表）
update 表名 set 列名=值 where 条件set：赋值/更新/修改指定表中符合条件的列值
update 表名 set 列名=值 where 集合条件更新修改指定表中符合集合条件的列值
set 列名01=值,列名02=值多列赋值
between 条件指定一个条件范围
alter table 表名 add constraint 约束名 foreign key (外键列名) references 参照表名 参照列名给指定表的外键列添加外键约束（参照完整性，constraint：约束，foreign key：外键，references：参照）
alter table 表名 add constraint 约束名 unique (列名)指定表中添加唯一性约束列（指定列或组合列的值只能使用一次）
select * from 表名查询指定表的所有列/行的值
select 列名 from 表名查询指定表的指定列的值
select 列名 as 别名 from 表名查询指定表的指定列的值且把显示的结果的列名替换为别名（别名可作为列名来使用）
select case 列名 when 条件 then '时雨' else '琼崎' end as 别名 from 表名查询指定表并替换符合条件的列的值为分支语句的值
now()拿到当前操作系统的日期时间
select if(列名,真值,假值) as 别名 from 表名查询指定表并替换符合条件的列的值为真/假值
select 列名 from 表名 where 条件查询指定表并显示符合条件的列
select 列名 from 表名 where 列名 like '字符/通配符/正则表达式'模糊查询符合条件的列（通配符，%：>=0，_：一个）
distinct 列名指定列去重
order by 列名指定列的值进行升序排序
order by 列名 desc指定列的值进行降序排序
year(now())拿到当前操作系统日期时间的年份
select max(列名) from 表名查询指定表的指定列的最大值
select min(列名) from 表名查询指定表的指定列的最小值
select count(列名) from 表名查询指定表的指定列有多少条记录
select 列名,count(主键列) from 表名 group by 列名查询指定表并统计指定主键列且根据指定列分组
select 列名 from 表名 where 条件名=(select min(条件名) from 表名)子查询（把结果作为父查询的条件）
select 列名01 from 表名01 where 列名02 in (select 列名02 from 表名02 group by 列名02 having count(列名02)>3)子查询/分组/集合运算
select 列名01,别名01 from 表名01 表的别名01,(select 列名02,avg(列名03) as 别名01 from 表名02 group by 列名02) 表的别名02 where 表的别名01.列名02=表的别名02.列名02子查询/连接查询方法01（表的别名02代表着子查询结果存放的临时表，临时表由子查询的结果产生的，让表与表之间联接）
select 列名01,别名01 from 表名01 表的别名01 inner join(select 列名02,avg(列名03) as 别名01 from 表名02 group by 列名02) 表的别名02 on 表的别名01.列名02=表的别名02.列名02子查询/连接查询方法02（inner join：表与表之间的联接，on：联接条件）
select 列名01,列名02,列名03 from 表名01 别名01,表名02 别名02,表名03 别名03 where 别名01.列名=别名03.列名 and 别名02.列名=别名03.列名连接查询（防止笛卡尔积，内联接，列值相等才可以直接连接，建立条件的前提是之间有外键关联，A中有B且A参照了B）
ifnull(列名,显示的值)如果指定列的值中有null则替换为指定值
limit 行数量仅显示一页中的指定行数量
limit 行数量 offset 跳过的行数量跳过指定的行数量，显示跳过后的指定行数量
limit 跳过的行数量,行数量简化分页
first指定列追加到第一列
after指定列追加到指定追加列的后面
dual伪表
select 列名01,列名02 from 表名01 表的别名01 where exists(select '时雨' from 表名02 表的别名02 where 表的别名01.列名=表的别名02.列名)存在性判断(真：输出筛选条件的结果，假：不输出筛选条件的结果)
explain生成执行计划
create index 索引名 on 表名 (列名)给指定表的指定列建立索引（索引：可以加速查询，会使用额外的存储空间而且会让CRU变得更慢，因为要更新索引，不能滥用索引）
drop index 索引名 on 表名删除指定表的指定索引
create view 视图名 as 执行的查询代码创建一个指定视图（查询的快照，会随着数据的更新而更新，视图：可以用来限制用户的查询权限，查询操作的快照）
select 列名 from 视图名执行指定视图并查询指定列
drop view 视图名删除指定的视图
delimiter 定界符定义指定的定界符作为一般语句的结束
create procedure 过程名(形参) begin 过程体; end创建存储过程
call 过程名(实参)调用存储过程
select @实参名查询接收的参数（@实参名：接收输出的参数）
drop procedure 过程名删除指定的存储过程
create user '用户名'@'登录方式' indentified by '用户密码'创建一个指定用户（无任何权限，登录方式，localhost：仅限本机，%：任意方式，ip地址：指定的ip地址）
drop user '用户名'@'登录方式'删除指定用户
grant all privileges on 数据库名.表名 to '用户名'@'登录方式'把指定数据库的指定表的全部权限给i指定用户（all privileges：全部权限，表名的*：全部表）
revoke 权限名 on 数据库名.表名 from '用户名'@'登录方式'收回指定用户的指定数据库的指定表的指定权限
start transaction开启事务（把多个CRU操作做成不可分割的原子性操作，要么全部成功，要么全部失败）
begin开启事务环境
commit事务环境内的操作全部生效并关闭事务环境
rollback回档（关闭事务环境前执行）

代码示例：

/*
1. int
    · 此列的数据类型为整型
2. not null
    · 内容不可为空
3. auto increment
    · 每增加一行，指定列的值自动从'1'开始自增
4. collate
    · 指定排序方式为区分大小写
5. 外联接
    · 左外联接
    · 右外联接
    · 全外联接
6. from 表名01 left outer join 表名02 on
    · 表名01（左），表名02（右）
7. left outer join on
    · 左外联接（如果左边的表不满足联接条件，也要在结果中显示）
8. right outer join on
    · 右外联接（如果右边的表不满足联接条件，也要在结果中显示）
9. full outer join on
    · 全外联接（如果两边的表都不满足联接条件，也要在结果中显示）
10. 存储过程
    · 把一系列的SQL语句封装到一个过程中
    · 创建过程时也提前编译了SQL语句
11. 形参
    · out（定义输出参数）
    · in（定义输入函数）
    · inout（定义输出/入函数）
12. 触发器
    · 在执行CRU操作时可以触发其它的级联操作（有可能会导致锁表）
    1. on delete cascade on update cascade
        · 删除/更新参照列的同时也同步删除/更新外键列
    2. on delete restrict on update restrict
        · 在删除/更新参照列的同时不同步删除/更新外键列
    3. on delete set null on update set null
        · 在删除/更新参照列的同时也同步吧外键列对应的值设置为null
*/
(
foo01 int not null auto increment collate collate_utf8_bin,
foo02 varchar(3) not null,  -- 此列的数据类型为字符型且限制输入的字符数量为3个
foo03 bit default 1,  -- 此列的数据类型为比特（二进制）型且默认值为1
foo04 date,  -- 此列的数据类型为日期型
foo05 decimal(10,2)  -- 限制此列的浮点数据输入（保留2位小数，整数位不得超过8位）
primary key (foo01),  -- 设置foo01列为主键（唯一标识符）
foreign key (外键列名) references 参照表名 参照列名  -- 配置外键列
);

create procedure foo(args int,out ret float)
begin
	-- SQL语句
end