数据库学习笔记（持续更新中）

MySQL

• MySQL由瑞典MySQL AB公司开发，目前属于Oracle公司。它是一个开源的关系型数据库管理系统，分为社区版和企业版。
• 启动与关闭MySQL服务，执行如下命令：
  net start mysql
  net stop mysql
• 数据类型
  整型：tinyint、smallint、mediumint、int、bigint
  浮点型：float、double
  日期时间类型：year、time、date、datetime、timestamp
  字符型：char、varchar、tinytext、mediumtext、longtext、enum、set
• 打开数据库
  use {数据库名称};
  显示打开的数据库：select database()；

• 创建数据表
  create table [if not exists] table_name (column_name data_type,....);

• 查看数据表列表
  show tables [from db_name] [like 'pattern' | where 'expr'];
• 查看数据表结构
  show columns from tbl_name;
• 记录的插入与查找
  insert [into] tbl_name [(col_name,...)] values(val,...);
  注意：如果省略col_name，默认对所有列字段进行插入记录。
  select expr,... from tbl_name;
• 空值与非空值
  null 字段可以为空，
  not null 字段值禁止为空

• 自动编号 auto_increment
  自动编号，且必须与主键组合使用
  默认情况下，起始值为1，每次的增量为1

• 主键约束 primary key
  每张数据表中只能存在一个主键
  主键保证记录的唯一性
  主键自动为not null
  可以独立使用，不依赖其他属性，比如 auto_increment
• 唯一约束 unique key
  唯一约束可以保证记录的唯一性
  唯一约束的字段可以为空值(null)
  每张数据表可以存在多个唯一约束
• 默认约束 default
  当前插入记录时，如果没有明确为字段赋值，则自动赋予默认值
• 约束
  约束保证数据的完整性和一致性
  约束分为表级约束和列级约束
  约束类型包括：not null（非空约束）、primary key （主键约束）、unique key（唯一约束）、default （默认约束）foreign key （外键约束）
• foreign key
  父表和子表必须使用相同的存储引擎，而且禁止使用临时表。
  数据表的存储引擎只能为InnoDB。
  外键列和参照列必须具有相似的数据类型。其中数字的长度或是否有符号位必须相同；而字符的长度可以不同。
  外键列和参照列必须创建索引。如果外键列不存在索引的话，MySQL将自动创建索引。
  default-storage-engine=InnoDB
  • 外键约束的参照操作
    cascade：从父表删除或更新且自动删除或更新子表中匹配的行
    set null：从父表删除或更新行，并设置子表中外键列为null。如果使用该选项，必须保证子表列没有指定 not null
    restrict：拒绝对父表的删除或更新操作
    no action ：标准sql关键字，在MySQL中与restrict相同
  • 表级约束与列级约束
    对一个数据列建立的约束，称为列级约束
    对多个数据列建立的约束，称为表级约束
    列级约束既可以在列定义时声明，也可以在列定义后声明
    表级约束只能在列定义后声明
  • 修改数据表
    添加单列
    alter table tbl_name add [column] col_name column_definition [first|after col_name]
    添加多列
    alter table tbl_name add [column](col_name column_definition,...)
    删除列
    alter table tbl_name drop [column] col_name;
    添加主键约束
    alter table tbl_name add [constraint [symbol]] primary key index_type
    添加唯一约束
    alter table tbl_name add [constraint [symbol]] unique [index|key][index_name]index_type
    添加外键约束
    alter table tbl_name add [constraint [symbol]] foreign key [index_name] (index_col_name,...) reference_definition
    添加/删除默认约束
    alter table tbl_name alter [column] col_name {set default literal|drop default}
    删除主键约束
    alter table tbl_name drop primary key
    删除唯一约束
    alter table tbl_name drop {index|key} index_name
    删除外键约束
    alter table tbl_name drop foreign key fk_symbol
    修改列定义
    alter table tbl_name modify [column] col_name column_definition [first | after col_name]
    修改列名称
    alter table tbl_name change [column] old_col_name new_col_name column_definition [first|after col_name]
    数据表更名
    a、alter table tbl_name rename [to|as] new_tbl_name
    b、rename table tbl_name to new_tbl_name [,tbl_name2 to new_tbl_name2]
  • 插入记录
    a、insert [into] tbl_name [(col_name,...)] {values|value} ({expr|default},...),(...),...
    b、insert [into] tbl_name set col_name={expr|default},...
    c、insert [into ] tbl_name [(col_name,...)] select ...
  • 更新记录
    update [low_priority] [ignore] table_reference set col_name={expr1|default} [, col_name2={expr2|default}]... [where
    where_condition]
  • 删除记录
    delete from tbl_name [where where_condition]
  • 查询表达式
    select select_expr [,select_expr ...]
    [ from table_references
    [where where_condition]
    [group by {col_name|position} [ASC|DESC],...]
    [having where_condition]
    [order by {col_name|expr|position} [ASC|DESC],...]
    [limit {[offset,] row_count|row_count offset offset}]
    ]
    每一个表达式表示想要的一列，必须有至少一个。
    多个列之间以英文逗号分隔。
    星号()表示所有列。
    tbl_name. 可以表示命名表的所有列
    查询表达式可以使用[AS] alias_name为其赋予别名
    别名可用于group by order by或having字句
  • 条件表达式
    对记录进行过滤，如果没有指定where字句，则显示所有记录。
    在where表达式中，可以使用MySQL支持的函数或运算符。
  • 查询结果分组 group by
    [group by {col_name|position} [ASC|DESC],...]
  • 分组条件
    [having where_condition]
  • 对查询结果排序
    [order by {col_name|expr|position} [ASC|DESC],...]
  • 限制查询结果返回的数量 limit
    [limit {[offset,] row_count|row_count offset offset}]
  • 子查询
    子查询是指出现在其他SQL语句内的select字句
    子查询指嵌套在查询内部，且必须始终出现在圆括号内。
    子查询可以包含多个关键字或条件，如distinct、group by 、order by、limit、函数等。
    子查询的外层查询可以是 select、insert、update、set或do
    子查询可以返回标量、一行、一列或子查询
    使用比较运算符的子查询
    =、>、<、>=、<=、<>、!=、<=>
    语法结构
    operand comparision_operator subquery
    用any、some或all 修饰的比较运算符
    operand comparison_operator any(subquery)
    operand comparison_operator some(subquery)
    operand comparison_operator all(subquery)
    使用[not] in 的子查询
    语法结构
    operand comparison_operator [not] in (subquery)
    = any 运算符与 in 等效
    !=all 或 <> all 运算符与not in 等效
    使用[not] exists 的子查询
    如果子查询返回任何行，exists将返回true；否则返回false
    将查询结果写入数据表
    insert [into] tbl_name [(col_name,...)] select ...
    多表更新
    update table_references set col_name1={expr1|default}
    [,col_name2={expr2|default}]...
    [where where_condition]
    创建数据表时将查询结果写入到数据表
    create table [if not exists] tbl_name [(create_definition,...)]
    select _statement
    连接
    MySQL在select语句、多表更新、多表删除语句中支持join操作
    语法结构
    table_reference
    {[inner|cross] join|{left|right} [outer] join}
    table)reference
    on conditional_expr
    数据表参照
    table_reference
    tbl_name [[as] alias] | table_subquery [as] alias
    数据表可以使用tbl_name AS alias_name或者tbl_name alias_name 赋予别名。table_subquery可以作为子查询使用在from子句中，这样的子查询必须为其赋予别名
    连接类型
    inner join 内连接
    在MySQL中，join、cross join和inner join是等价的。
    left [outer] join 左外连接
    right [outer] join 右外连接
    连接条件
    使用ON关键字来设定连接条件，也可以使用WHERE来代替
    通常使用ON关键字来设定连接条件
    使用WHERE关键字进行结果记录的过滤
    内连接显示左表及右表符合条件的记录
    左连接显示左表的全部记录及右表符合条件的记录
    右连接显示右表的全部记录及左表符合条件的记录
    多表连接是对外键的逆向操作
    A left join b join_condition
    数据表B的结果集依赖数据表A
    数据表A结果集根据左连接条件依赖所有数据表（B表除外）
    左连接条件决定如何检索数据表B（在没有指定where条件的情况下）
    如果数据表A的某条记录复合where条件，但是在数据表B不存在，符合连接条件的记录，将生成一个所有列为空的额外的B行。
    无限级分类表设计
    自身连接
    同一个数据表对其自身进行连接
    多表删除
    delete tbl_name[.] [,tbl_name[.]]...
    from table_references
    [where where_condition]
    -字符函数
    concat() ——字符连接
    concat_ws() ——使用自定的分隔符进行字符连接
    format() ——数字格式化
    lower() ——转换成小写字母
    upper() ——转换成大写字母
    left() ——获取左侧字符
    right() ——获取右侧字符
    length() ——获取字符串长度
    ltrim() ——删除前导空格
    rtrim() ——删除后续空格
    trim() ——删除前导和后续空格
    substring() ——字符串截取
    [not] like ——模式匹配
    replace() ——字符串替换
    -数值运算符与函数
    ceil() ——进一取整
    div ——整数除法
    floor() ——舍一取整
    mod ——取余数（取模）
    power() ——幂运算
    round() ——四舍五入
    truncate ——数字截取
    [not] between...and... ——[不]在范围内
    [not] in() ——[不]在列出值范围内
    is [not] null —— [不]为空
    -日期时间函数
    now() ——当前日期和时间
    curdate() ——当前日期
    curtime() ——当前时间
    date_add() ——日期变化
    datediff() ——日期差值
    date_format ——日期格式化
    -信息函数
    connection_id() ——连接ID
    database() ——当前数据库
    last_insert_id() ——最后插入记录的ID号
    user() ——当前用户
    version() ——版本信息
    -聚合函数
    avg() ——平均值
    count() ——计数
    max() ——最大值
    min() ——最小值
    sum() ——求和
    -加密函数
    md5() ——信息摘要算法
    password() ——密码算法
    -自定义函数
    用户自定义函数(UDF)是一种对MySQL扩展的途径，期用法与内置函数相同
    两个必要条件：(1) 参数 (2) 返回值
    函数可以返回任意类型的值，同样可以接受这些类型的参数，参数和返回值
    之间没有必然的联系
    创建自定义函数
    create function function_name
    returns
    {string|integer|real|decimal}
    routine_body
    函数体
    (1) 函数体由合法的SQL语句构成
    (2) 函数体可以是简单的select或insert语句
    (3) 函数体如果为复合结构则使用begin...end语句
    (4) 复合结构可以包含声明，循环，控制结构
    -存储过程
    存储过程是SQL语句和控制语句的预编译集合，以一个名称存储并作为一个单元处理。优点如下：
    增强SQL语句的功能和灵活性
    实现较快的执行速度
    减少网络流量
    创建存储过程
    create [definer = {user|current_user}]
    procedure sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body
    proc_parameter: [in|out|inout] param_name type
    in 表示该参数的值必须在调用存储过程时指定
    out 表示该参数的值可以被存储过程改变，并且可以返回
    inout 表示该参数的调用时指定，并且可以被改变和返回
    过程体由合法SQL语句构成，可是任意的SQL语句，如果为复合结构则使用 begin...end语句，复合结构可以包含声明，循环，控制结构
    调用存储过程
    call sp_name([parameter[,...]])
    call sp_name[()]
    修改存储过程
    alter procedure sp_name [characteristic ...]
    comment 'string'
    |{contains_sql| no sql|reads sql data | modifiles sql data}
    |sql security {definer|invoker}
    删除存储过程
    drop procedure [if exists] sp_name
    -存储引擎
    MySQL可以将数据以不同的技术存储在文件(内存)中，这种技术就称为存储引擎。
    每一种存储引擎使用不同的存储机制，索引技巧、锁定水平，最终提供广泛且不同的功能。
    MyISAM、InnoDB、Memory、CSV、Archive
    并发控制
    当多个连接对记录进行修改时保证数据的一致性和完整性
    锁：共享锁、排他锁
    事务
    保证数据库的完整性，原子性、一致性、隔离性、持久性