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

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

标签：

MySQL

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
  并发控制
  当多个连接对记录进行修改时保证数据的一致性和完整性
  锁：共享锁、排他锁
  事务
  保证数据库的完整性，原子性、一致性、隔离性、持久性