深入浅出MySQL数据库

1.什么是数据库？
1. 数据库(Database)是按照数据结构来组织、存储和管理数据的仓库
2. 常见数据库
  1. Oracle
  2. DB2
  3. SQL Server
  4. Postgre SQL
  5. MySQL
3. 相关术语
  1. 数据库系统(Database System):DBS
    1. 数据库(Database)
    2. 数据库管理系统(Database Management System):DBMS
    3. 应用开发工具
    4. 管理员及用户
  2. SQL语言
    1. Structured Query Language：结构化查询与颜
      1. DDL
      2. 数据定义语言
      3. DML
      4. 数据操作语言
      5. DQL
      6. 数据查询语言
      7. DCL
      8. 数据控制语言
2.MySQL相关操作
1. 配置文件
  1. my.cnf是MySQL的配置文件
2. 登陆/退出MySQL
  1. 登陆
    1. mysql -uroot -p
    2. mysql -uroot -proot
    3. 得到版本号
      1. mysql -V
      2. mysql --version
    4. 登陆的同时打开指定数据库
      1. mysql -uroot -p -D db_name
  2. 退出
    1. exit
    2. quit
  3. 登陆信息中需要掌握的
    1. 命令行结束符默认使用;或者\g来结束
    2. 可以通过help或者\h或者?加上相关关键字来查看手册
    3. \c可以取消当前命令的执行
3. SQL语句语法规范
  1. 常用MySQL的关键字我们需要大写，库名、表名、字段名称等使用小写
  2. SQL语句支持折行操作，拆分的时候不能把完整单词拆开
  3. 数据库名称、表名称、字段名称不要使用MySQL的保留字，如果必须要使用，需要用反引号``将其括起来
4. 常用SQL语句
  1. SELECT USER()
    1. 得到登陆的用户
  2. SELECT VERSION()
    1. 得到MySQL的版本信息
  3. SELECT NOW()
    1. 得到当前的日期时间
  4. SELECT DATABASE()
    1. 得到当前打开的数据库
3.数据库相关操作
1. 创建数据库
  1. CREATE {DATABASE|SCHEMA} db_name;
  2. 检测数据库名称是否存在，不存在则创建
    1. CREATE DATABASE [IF NOT EXISTS] db_name;
  3. 在创建数据库的同时指定编码方式
    1. CREATE DATABASE [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] charset;
  4. 注意
    1. 数据库名称最好有意义
    2. 名称不要包含特殊字符或者是MySQL关键字
2. 查看当前服务器下全部数据库
  1. SHOW DATABASES|SCHEMAS;
3. 查看指定数据库的详细信息
  1. SHOW CREATE DATABASE db_name;
4. 修改指定数据库的编码方式
  1. ALTER DATABASE db_name [DEFAULT] CHARACTER SET [=] charset;
5. 打开指定数据库
  1. USE db_name;
6. 得到当前打开的数据库
  1. SELECT DATABASE()|SCHEMA();
7. 删除指定的数据库
  1. DROP DATABASE db_name;
  2. 如果数据库存在则删除
    1. DROP DATABASE [IF EXISTS] db_name;
4.数据表相关操作
1. 数据表
  1. 是数据库最重要的组成部分之一，数据是保存在数据表中
  2. 数据表由行(row)和列(column)来组成
  3. 每个数据表中至少有一列，行可以有零行一行或者多行组成
  4. 表名要求唯一，不要包含特殊字符，最好含义明确
2. 创建表
  1. CREATE TABLE [IF NOT EXISTS] tbl_name( 字段名称字段类型 [完整性约束条件], 字段名称字段类型 [完整性约束条件], ... )ENGINE=存储引擎 CHARSET=编码方式;
    1. UNSIGNED
      1. 无符号，没有负数，从0开始
    2. ZEROFILL
      1. 零填充，当数据的显示长度不够的时候可以使用前补0的效果填充至指定长度,字段会自动添加UNSIGNED
    3. NOT NULL
      1. 非空约束，也就是插入值的时候这个字段必须要给值,值不能为空
    4. DEFAULT
      1. 默认值，如果插入记录的时候没有给字段赋值，则使用默认值
    5. PRIMARY KEY
      1. 主键，标识记录的唯一性，值不能重复，一个表只能有一个主键，自动禁止为空
    6. AUTO_INCREMENT
      1. 自动增长，只能用于数值列，而且配合索引使用,默认起始值从1开始，每次增长1
    7. UNIQUE KEY
      1. 唯一性，一个表中可以有多个字段是唯一索引，同样的值不能重复，但是NULL值除外
    8. FOREIGN KEY
      1. 外键约束
3. 查看当前数据库下已有数据表
  1. SHOW TABLES;
  2. SHOW [FULL] TABLES [{FROM | IN} db_name] [LIKE 'pattern' | WHERE expr]
4. 查看指定数据表的详细信息
  1. SHOW CREATE TABLE tbl_name;
5. 查看表结构
  1. DESC tbl_name;
  2. DESCRIBE tbl_name;
  3. SHOW COLUMNS FROM tbl_name;
6. 删除指定的数据表
  1. DROP TABLE [IF EXISTS] tbl_name;
7. 表结构相关操作
  1. 添加字段
    1. ALTER TABLE tbl_name ADD 字段名称字段属性 [完整性约束条件] [FIRST|AFTER 字段名称]
  2. 删除字段
    1. ALTER TABLE tbl_name DROP 字段名称
  3. 添加默认值
    1. ALTER TABLE tbl_name ALTER 字段名称 SET DEFAULT 默认值;
  4. 删除默认值
    1. ALTER TABLE tbl_name ALTER 字段名称 DROP DEFAULT
  5. 修改字段类型、字段属性
    1. ALTER TABLE tbl_name MODIFY 字段名称字段类型 [字段属性] [FIRST | AFTER 字段名称]
  6. 修改字段名称、字段类型、字段属性
    1. ALTER TABLE tbl_name CHANGE 原字段名称新字段名称字段类型字段属性 [FIRST | AFTER 字段名称]
  7. 添加主键
    1. ALTER TABLE tbl_name ADD PRIMARY KEY(字段名称)
  8. 删除主键
    1. ALTER TABLE tbl_name DROP PRIMARY KEY;
  9. 添加唯一
    1. ALTER TABLE tbl_name ADD UNIQUE KEY|INDEX [index_name] (字段名称)
  10. 删除唯一
    1. ALTER TABLE tbl_name DROP index_name;
  11. 修改数据表名称
    1. ALTER TABLE tbl_name RENAME [TO|AS] new_tbl_name
    2. RENAME TABLE tbl_name TO new_tbl_name;
  12. 修改AUTO_INCREMENT的值
    1. ALTER TABLE tbl_name AUTO_INCREMENT=值
5.MySQL中的数据类型
1. 数值型
  1. 整数型
  2. 浮点型
2. 字符串类型
  1. CHAR效率高于VARCHAR,CHAR相当于拿空间换时间，VARCHAR拿时间换空间
  2. CHAR默认存储数据的时候，后面会用空格填充到指定长度；而在检索的时候会去掉后面空格；VARCHAR在保存的时候不进行填充，尾部的空格会留下
  3. TEXT列不能有默认值,检索的时候不存在大小写转换
3. 日期时间类型
4. 二进制类型
6.MySQL存储引擎
1. MyISAM存储引擎
  1. 默认MyISAM的表会在磁盘中产生3个文件
    1. .frm
      1. 表结构文件
    2. .MYD
      1. 数据文件
    3. .MYI
      1. 索引文件
  2. 可以在创建的时候指定数据文件和索引文件的存储位置，只有MyISAM表支持
    1. DATA DIRECORY [=] 数据保存的绝对路径
    2. INDEX DIRECTORY [=] 索引文件保存的绝对路径
  3. MyISAM单表最大支持的数据量2的64次方条记录
  4. 每个表最多可以建立64个索引
  5. 如果是复合索引，每个复合索引最多包含16个列，索引值最大长度是1000B
  6. MyISAM引擎的存储格式
    1. 定长（FIXED 静态）
      1. 是指字段中不包含VARCHAR/TEXT/BLOB
    2. 动态（DYNAMIC）
      1. 只要字段中包含了VARCHAR/TEXT/BLOB
    3. 压缩（COMPRESSED）
      1. myisampack创建
2. InnoDB存储引擎
  1. 设计遵循ACID模型，支持事务，具有从服务崩溃中恢复的能力，能够最大限度保护用户的数据
  2. 支持行级锁，可以提升多用户并发时的读写性能
  3. 支持外键，保证数据的一致性和完整性
  4. InnoDB拥有自己独立的缓冲池，常用的数据和索引都在缓存中
  5. 对于INSERT、UPDATE、DELETE操作，InnoDB会使用一种change buffering的机制来自动优化，还可以提供一致性的读，并且还能够缓存变更的数据，减少磁盘I/O，提高性能
  6. 创建InnoDB表之后会产生两个文件
    1. .frm表结构文件
    2. .ibd,数据和索引存储表空间中
  7. 所有的表都需要创建主键，最好是配合上AUTO_INCREMENT,也可以放到经常查询的列作为主键
7.MySQL数据操作
1. 添加记录
  1. INSERT [INTO] tbl_name[(col_name,...)] {VALUE|VALUES}(VALUES...);
  2. 不指定字段名称
    1. INSERT tbl_name VALUE(value...)
    2. 需要按照建表时的字段顺序给每一个字段赋值
  3. 列出指定字段
    1. INSERT tbl_name(字段名称,...) VALUES(值,...)
  4. INSERT ... SET的形式
    1. INSERT tbl_name SET 字段名称=值,...;
  5. INSERT ... SELECT
    1. INSERT tbl_name[(字段名称...)] SELECT 字段名称,... FROM tbl_name [WHERE 条件]
  6. 一次添加多条记录
    1. INSERT tbl_name[(字段名称,...)] VALUES(值,...), (值,....), (值,...)
2. 修改记录
  1. UPDATE tbl_name SET 字段名称=值,字段名称=值 [WHERE 条件]
  2. 如果不添加条件，整个表中的记录都会被更新
3. 删除记录
  1. DELETE FROM tbl_name [WHERE 条件]
  2. 如果不添加条件，表中所有记录都会被删除
  3. DELETE 清空数据表的时候不会重置AUTO_INCREMENT的值，可以通过ALTER 语句将其重置为1
  4. 彻底清空数据表
    1. TRUNCATE [TABLE] tbl_name;
      1. 清除表中所有记录
      2. 会重置AUTO_INCREMENT的值
4. 查询记录
  1. SELECT select_expr,... FROM tbl_name [WHERE 条件] [GROUP BY {col_name|position} HAVING 二次筛选] [ORDER BY {col_name|position|expr} [ASC|DESC]] [LIMIT 限制结果集的显示条数]
  2. 查询表中所有记录
    1. SELECT * FROM tbl_name;
      1. *所有字段
  3. 指定字段的信息
    1. SELECT 字段名称,... FROM tbl_name
  4. 库名.表名
    1. SELECT 字段名称,... FROM db_name.tbl_name;
  5. 给字段起别名
    1. SELECT 字段名称 [AS] 别名名称,... FROM db_name.tbl_name;
  6. 给数据表起别名
    1. SELECT 字段名称 ,... FROM tbl_name [AS] 别名;
  7. 表名.字段名的
    1. SELECT tbl_name.col_name,... FROM tbl_name;
  8. WHERE 条件
    1. 会筛选出符合条件的记录
      1. 比较运算符
      2. > >= < <= != <> <=>
      3. <=> 和 =的区别
      4. <=>可以检测NULL值
      5. IS [NOT] NULL
      6. 检测值是否为NULL或者NOT NULL
      7. 指定范围
      8. [NOT] BETWEEN ... AND
      9. 指定集合
      10. [NOT] IN(值,...)
      11. 逻辑运算符
      12. AND
      13. 逻辑与
      14. OR
      15. 逻辑或
      16. 匹配字符
      17. [NOT] LIKE
      18. %
      19. 任意长度的字符串
      20. _
      21. 任意一个字符
  9. GROUP BY 分组
    1. 把值相同放到一个组中，最终查询出的结果只会显示组中一条记录
    2. 分组配合GROUP_CONCAT()查看组中某个字段的详细信息
    3. 配合聚合函数使用
      1. COUNT()
      2. 统计记录总数
      3. 如果写的是COUNT(字段名称)，字段中的值为NULL，不统计进来
      4. 写COUNT(*)会统计NULL值
      5. SUM()
      6. 求和
      7. MAX()
      8. 求最大值
      9. MIN()
      10. 求最小值
      11. AVG()
      12. 求平均值
    4. 配合WITH ROLLUP关键使用
      1. 会在记录末尾添加一条记录，是上面所有记录的总和
    5. HAVING子句对分组结果进行二次筛选
  10. ORDER BY 排序
    1. ORDER BY 字段名称 ASC|DESC
  11. LIMIT 限制结果集显示条数
    1. LIMIT 值
      1. 显示结果集的前几条记录
    2. LIMIT offset,row_count
      1. 从offset开始，显示几条记录,offset从0开始
5. 多表查询
  1. 笛卡尔积的形式
  2. 内连接的形式
    1. 查询两个表中符合连接条件的记录
    2. SELECT 字段名称,... FROM tbl_name1 INNER JOIN tbl_name2 ON 连接条件
  3. 外连接的形式
    1. 左外连接
      1. SELECT 字段名称,... FROM tbl_name1 LEFT [OUTER] JOIN tbl_name2 ON 条件;
      2. 先显示左表中的全部记录，再去右表中查询复合条件的记录，不符合的以NULL代替
    2. 右外连接
      1. SELECT 字段名称,... FROM tbl_name1 RIGHT [OUTER] JOIN tbl_name2 ON 条件;
      2. 先显示右表中的全部记录，再去左表中查询复合条件的记录，不符合的以NULL代替
6. 外键约束
  1. 只有InnoDB存储引擎支持外键
  2. 创建外键
    1. 建表时指定外键
      1. [CONSTRAINT 外键名称 ]FOREIGN KEY(字段名称) REFERENCES 主表(字段名称)
      2. 子表的外键字段和主表的主键字段类型要相似；如果是数值型要求一致，并且无符号也要一致；如果是字符型，要求类型一致，长度可以不同
      3. 如果外键字段没有创建索引，MySQL会自动帮我们添加索引
      4. 子表的外键关联的必须是父表的主键
      5. 外键约束的参照操作
      6. CASCADE
      7. 从附表删除或更新，子表也跟着删除或者更新，级联的操作
      8. SET NULL
      9. 从附表删除或者更新记录，并设置子表的外键列为NULL。
      10. NO ACTION | RESTRICT
      11. 拒绝对父表做更新或者删除操作
    2. 动态添加外键
      1. 动态添加外键
      2. ALTER TABLE tbl_name [CONSTRAINT 外键名称] ADD FOREIGN KEY(外键字段) REFERENCES 主表(主键字段);
      3. 动态添加外键之前表中的记录一定合法的记录，没有脏值，否则外键添加不成功
      4. 动态删除外键
      5. ALTER TABLE tbl_name DROP FOREIGN KEY fk_name;
7. 特殊形式的查询
  1. 子查询
    1. SELECT 字段名称 FROM tbl_name WHERE col_name=(SELECT col_name FROM tbl_name)
    2. 内层语句查询的结果可以做为外层语句查询的条件
    3. 由IN引发的子查询
    4. 由比较运算符引出子查询
    5. 由EXISTS引发的子查询
    6. ANY SOME ALL
    7. INSERT ... SELECT
    8. CREATE ... SELECT
    9. CREATE TABLE tbl_name LIKE tbl_name;
  2. 联合查询
    1. UNION
      1. SELECT 字段名称,... FROM tbl_name1 UNION SELECT 字段名称... FROM tbl_name2;
    2. UNION ALL
      1. SELECT 字段名称,... FROM tbl_name1 UNION ALL SELECT 字段名称... FROM tbl_name2;
    3. UNION ALL 是简单的合并，UNION会去掉表中重复记录
  3. 自身连接查询
    1. 无限级分类的实现形式
8.MYSQL常用函数
1. 数学函数
  1. CEIL()
    1. 进一取整
  2. FLOOR()
    1. 舍掉小数部分
  3. ROUND()
    1. 四舍五入
  4. TRUNCATE()
    1. 截取小数点后几位
  5. MOD()
    1. 取余数
  6. ABS()
    1. 取绝对值
  7. POWER()
    1. 幂运算
  8. PI()
    1. 圆周率
  9. RAND()或者RAND(X)
    1. 0~1之间的随机数
  10. SIGN(X)
    1. 得到数字符号
  11. EXP(X)
    1. 计算e的x次方
2. 字符串函数
3. 日期时间函数
4. 其它常用函数
9.图形化工具管理数据库
1. MySQL Workbench
常用SQL语句
1. 查看上一步操作产生的警告信息
  1. SHOW WARNINGS;
2. mysql中的注释
  1. # 注释内容
  2. -- 注释内容