深入浅出后端开发(SQL指令笔记)

前言

这里筑梦师,是一名正在努力学习的iOS开发工程师,目前致力于全栈方向的学习,希望可以和大家一起交流技术,共同进步,用简书记录下自己的学习历程.

全栈开发自学笔记 已开源

本文阅读建议
1.一定要辩证的看待本文.
2.本文仅代表个人片面观点,如有不同观点,还往及时指出.
3.本文是个人对SQL指令的自我记忆笔记,仅供参考
4.本文所表达观点并不是最终观点,还会更新,因为本人还在学习过程中,有什么遗漏或错误还望各位指出.
5.觉得哪里不妥请在评论留下建议~
6.觉得还行的话就点个小心心鼓励下我吧~

MySQL操作笔记

• MySQL操作笔记
  • 数据类型
    • 数字类型
      • TINYINT 最小整数 (127~255)
      • BIT 最小整数 (127~255)
      • BOOL 最小整数 (127~255)
      • SMALLINT 小型整数 32767
      • MEDIUMINT 中型整数 8388607
      • INT 标准整数 2147683647
      • BIGINT 大整数 9223372036854775807
      • FLOAT 单精度浮点数
      • DOUBLE 双精度浮点数
      • DECIMAL 一般整数 自定义长度
    • 字符串类型
      • 普通字符串类型
        • CHAR 0-255
        • VARCHAR 0-255
      • TEXT 和 BLOB类型
        • TINYBLOB 225字节 小BLOB字段
        • TINYTEXT 225字节 小TEXT字段
        • BLOB 65535字节 常规BLOB字段
        • TEXT 65535字节 常规TEXT字段
        • MEDIUMBLOB 16777215字节 中型BLOB字段
        • MEDIUMTEXT 16777215字节 中型TEXT字段
        • LONGBLOB 4294967295字节 长BLOB字段
        • LONGTEXT 4294967295 长TEXT字段
      • 特殊类型SET 和ENUM
        • ENUM 65535
        • SET 64
    • 日期类型
      • DATE
      • TIME
      • DATETIME
      • TIMESTAMP
      • YEAR
    • 数据类型抉择
      • 选择最小的可用类型,如果值永远不超过127,则使用TINYINT比INT强
      • 对于完全是数字的,可用整数类型
      • 浮点类型用于可能具有小数部分的数.
      • 从速度方面考虑,要选择固定的列,可以使用CHAR 类型
      • 要节省空间,使用动态的列,可以使用VARCHER类型
      • 要将列中的内容限制在一种选择,可以使用ENUM类型.
      • 允许在一个列中有多于一个的条目,可以使用SET类型
      • 如果要搜索的内容不区分大小写,可使用TEXT类型
      • 如果要搜索的内容区分大小写,可以使用BLOB类型
  • 内置函数
    • 数学函数
      • ABS(x) 返回x的绝对值
      • CEIL(x),CEILIN(x),返回不小于x的最小整数值
      • FLOOR(x)返回不大于x的最大整数值
      • RAND() 返回0~1的随机数
      • RAND(x) 返回0~1的随机数,x值相同时返回的随机数相同?
      • SIGN(x) 返回参数 -1 0 或1的符号.取决于x的正负
      • PI() 返回圆周率 默认7位
      • TRUNCATE (x,y) 返回数值x保留到小数点后y位的值
      • ROUND(x) 返回离x最近的整数
      • ROUND(x,y)保留x小数点后y位的值,截断时进行四舍五入
    • 字符串函数
      • SUBSTRING_INDEX(text , ’,’ ,1)
    • 日期和时间函数
    • 条件判断函数
    • 系统信息函数
    • 加密函数
    • 其他函数
  • 数据库操作
    • 创建数据库 CREATE DATABASE name
    • 查看数据库 SHOW
      • 查看所有数据库 SHOW DATABASES
    • 使用数据库 USE DATABASE name
    • 删除数据库 DROP DATABASE name
  • 数据表操作
    • 添加数据表 CREATE
      • 可选参数 TEMPORARY
        • 如果使用该关键字,表示创建一个临时表
      • 必选参数 TABLE
      • 是否存在 IF NOT EXISTS
        • 判断是否存在,避免错误
      • 定义列属性 create_definition() =>col_name
        • 表至少包含一列,否则无法创建
        • 列属性创建
          • 必选参数 字段名字 col_name
          • 必选参数 字段类型 type
          • 可选参数 是否可以为空 NOT NULL / NULL
          • 可选参数 默认值 DEFAULT default_value
          • 可选参数 自增 AUTO_INCREMENT
          • 可选参数 主键 PRIMARY KEY
          • 可选参数 注释 reference_definition
      • 表参数 table_options
      • 快速建表 select_statement
      • 可选参数 COMMENT ‘备注’
      • 设置主键 PRIMARY KEY (col_name)
      • 自增列 AUTO_INCREMENT
      • 示例:
        • CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table_name(
          col_name type NOT NULL DEFAULT default_value AUTO_INCREMENT PRIMARY KEY,
          );
    • 查看表结构 SHOW COLUMNS/DESCRIBE
      • 必选参数 查询 SHOW
      • 可选参数 FULL
      • 必选参数 列数 COLUMNS
      • 必选参数 来自 FROM table_name
      • 可选参数 来自 FROM database_name
      • ——
      • 必选参数 列出 DESCRIBE
      • 必选参数 数据表名 table_name
      • 可选参数 列名 col_name
    • 查看表详细结构语句 SHOW CREATE TABLE
      • 必选参数 SHOW
      • 必选参数 CREATE
      • 必选参数 TABLE
      • 必选参数 table_name
      • 可选参数 \G
    • 修改表结构 ALTER TABLE
      • 必选参数 修改 ALTER
      • 可选参数 IGNORE
      • 必选参数 表名 TABLE table_name
      • 必选一参数 ADD | MODIFY | CHANGE | DROP
        • 修改表名 ALTER TABLE table_name RENAME TO new_table_name
        • 改变列名 CHANGE COLUMN old_col_name new_col_name type ….;
          • 如果不改变列名 则写两遍
        • 改变某列属性 ALTER TABLE table_name MODIFY COLUMN col_name type;
        • ALTER TABLE table_name DROP COLUMN col_name;
      • 必选多可选 alter_spec,[alter_spec]
      • 示例 ALTER TABLE table_name ADD col_name type col_definition,MODIFY col_name type;
    • 重命名 RENAME TABLE
      • 必选参数 RENAME
      • 必选参数 TABLE
      • 必选参数 表名 table_name
      • 必选参数 TO
      • 必选参数 new_table_name;
      • 示例
        • RENAME TABLE old_name TO new_name;
    • 删除表 DROP TABLE
      • 必选参数 DROP
      • 必选参数 TABLE
      • 可选参数 是否存在 IF EXISTS
      • 必选参数 table_name;
  • 数据操作
    • 插入数据 INSERT
      • 必选参数 INSERT
      • 必选参数 INTO
      • 必选参数 数据表名(要插入的列) table_name(column_name1,)
      • 必选多可选 VALUES(value1,value2),(value3,value4)
      • 示例
        • INSERT INTO table_name(col_name1,col_name2) VALUES(value1,value2)
    • 查询数据 SELECT
      • 必选参数 查询 SELECT
        • 可选子参数 DISTINCT 去掉重复行.
        • 可选子函数 CONCAT(col_name1,连接字符串,col_name2);
      • 必选参数 指定表 FROM
        • 可选组合参数 第二条件 HAVING
          • 筛选GROUP BY之后的参数
          • SELECT sex,COUNT(sex) FROM table_name GROUP BY sex HAVING COUNT(sex)>=3;
      • 可选组合参数 条件 WHERE
        • 等于 =
          • 查询id=27的记录, 如果结果相等 返回1 不相等返回0
        • 大于 >
        • 小于 <
        • 大于等于 =>
        • 小于等于 <=
        • 不等于 != 或 <>
        • 多条件查询 AND
          • 条件必须全部满足,才能被查询出来
          • SELECT * FROM table_name WHERE condition_1 AND condition_2 …可以无限AND叠加
          • SELECT * FROM table_name WHERE user=‘mr’ AND section=‘php’.
        • 多条件查询 OR
          • 满足其中一个条件就会被查出来
          • SELECT * FROM table_name WHERE condition_1 OR condition_2…OR无限叠加
          • SELECT * FROM table_name WHERE section=‘php’ OR section=‘程序开发’;
        • 是否为空 IS NULL
          • SELECT books,row FROM table_name WHERE row IS NULL;
        • 不为空 IS NOT NULL
          • SELECT books,row FROM table_name WHERE row IS NOT NULL;
        • 范围 ..介于 BETWEEN AND
          • SELECT * FROM table_name WHERE condition BETWEEN 取值1 AND 取值2;
          • SELECT * FROM table_name WHERE id BETWEEN 5 AND 7;
        • 范围 ..不在 NOT BETWEEN AND
          • SELECT * FROM table_name WHERE condition NOT BETWEEN 取值1 AND 取值2;
          • SELECT * FROM table_name WHERE id NOT BETWEEN 5 AND 7;
        • 在范围之内 IN
          • SELECT * FROM table_name WHERE condition IN (元素1,元素2)
          • SELECT * FROM table_name WHERE user IN (‘mx’,’xiaomi’)
        • 不在什么范围之内 NOT IN
          • SELECT * FROM table_name WHERE condition NOT IN (元素1,元素2)
          • SELECT * FROM table_name WHERE user NOT IN (‘mx’,’xiaomi’)
        • 模糊查询 包含 LIKE
          • SELECT * FROM table_name WHERE user like ‘%mr%’
          • LIKE (‘李%’) 匹配 包含 李
          • LIKE (‘李_’) 匹配 李 + “ ”
        • 模糊查询 不包含 NOT LIKE
        • 正则表达式 REGEXP
        • 语法：SELECT * FROM table_name WHERE 条件判断1 逻辑运算符 条件判断2
      • 可选组合参数 分组 GROUP BY
        • SELECT 字段列表,聚合函数 FROM table_name GROUP BY 字段名 [ASC | DESC]
        • 通俗说,GROUP BY是将 字段名一样的进行聚合函数的计算
        • 示例
      • 可选组合参数 排序 ORDER BY
        • SELECT * FROM table_name ORDER BY 字段1 [ASC|DESC] , 字段2 [ASC|DESC]
        • 原理：如果第1段有相同的值，相同值的部分用第2个字段来排序。
      • 可选组合参数 限定 LIMIT
        • 限制返回数量 LIMIT 结果数;
          • LIMIT 3;
        • 分页返回 LIMIT 起始位置,每页个数.
          • LIMT 1,4;从1开始往后查4个
      • 多表组合操作
      • 排序规则
      • 示例
        • SELECT * FROM table_name;
        • SELECT col_name1,col_name2 FROM table_name;
        • SELECT table1.id ,table1.name FROM table1,table2 WHERE table1.name =table2.name AND table2.name =“”;
    • 修改数据 UPDATE
      • 必选参数 UPDATE
      • 必选参数 table_name
      • 必选参数 SET
      • 可选复合参数CASE WHEN
        • UPDATE table_name
          SET col_name=
          CASE
          WHEN condition THEN ‘value’
          WHEN condition THEN ‘value’
          WHEN condition THEN ‘value’
          ELSE ‘value’
          END;
      • 可选复合参数 col_name =new_value1,col_name 2=new_value2
        • 如果col_name不存在,则创建一个新列
      • 可选参数 WHERE
      • 可选参数 condition
    • 删除数据 DELETE
      • 必选参数 DELETE
      • 必选参数 FROM
      • 必选参数 table_name
      • 可选参数 WHERE
      • 可选参数 condition
    • 聚合函数
      • COUNT(字段名) 计算非空记录数
        • SELECT COUNT(DISTINCT results) FROM student;
        • COUNT(*)全部统计
        • SELECT sex,count(sex) AS 人数 FROM student GROUP BY sex DESC.
      • AVG(字段名) 平均数
        • SELECT AVG(row) FROM table_name;
      • MIN (字段名) 最小值
        • SELECT MIN(row) FROM table_name;
      • MAX (字段名) 最大值
        • SELECT MAX(row) FROM table_name;
      • STD(字段名)指定字段标准背离值
      • STDTEV(字段名)指定字段标准背离值
      • SUM(字段名)指定字段所有记录的总合
        • SELECT sum(row) FROM table_name;
  • 约束操作
    • 外键 FOREIGN KEY
      • CREATE TABLE table_name(
        int_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        contact_id INT NOT NULL ,
        CONSTRAINT my_contacts_contact_id_fk
        FOREIGN KEY (contact_id)
        REFERENCES my_contacts (contact_id)
        );
      • 截图
    • 检查约束 CHECK
  • 连接查询
    • 查询语句基本格式
      • SELECT selection_list //要查询哪些列
        FROM table_name //指定数据表
        WHERE primary_constraint 查询时要满足的条件,行必须满足的条件
        GROUO BY grouping_columns //如何对结果进行分组
        ORDER BY sorting_columns //如何对结果进行排序
        HAVING secondary_constraint //查询时满足的第二条件
        LIMIT count //限定输出的查询结果
      • SELECT * | id,user,col_name… FROM table_name 查询全部列或多列
    • 内连接查询
      • SELECT name,books FROM table_name_1,table_name_2 WHERE table_name1.user =table_name2.user
      • 内连接,结果集只包含参加连接的表中与指定字段相符的行.
      • 如果没有WHERE限定,则为交叉连接或者全连接
      • 相等联结
        SELECT col_name FROM table_name INNER JOIN table_name WHERE conditions = conditions
      • 不等联结
        SELECT col_name FROM table_name INNER JOIN table_name WHERE conditions <> conditions
      • 自然联结
        SELECT col_name FROM table_name NATURAL JOIN table_name ;
    • 左外连接
      • SELECT col_name FROM table_name1 LEFT JOIN table_name2 ON table_name1.col_name =table_name2.col_name;
      • 左外链接,是指将左表中的所有数据分别与右表的每条数据进行结合,返回的结果除内连接的数据外,还包括左表中不符合条件的数据,并在右表的相应列中添加NULL值.
      • 截图
    • 右外连接
      • SELECT col_name FROM table_name1 RIGHT JOIN table_name2 ON table_name1.col_name =table_name2.col_name;
      • 右外连接,是指将右表中的所有数据分别与左表中的每条数据进行连接组合,返回的结果除内连接数据外,还包括表中不符合条件的数据,并在左表的相应列中添加NULL.
      • 截图
    • 外联结截图
    • 自引用
    • 复合条件
      • SELECT col_name FROM table_name1 RIGHT JOIN table_name2 ON table_name1.col_name =table_name2.col_name AND condition
    • 子查询
      • IN 子查询
        • SELECT * FROM table_name_1 WHERE col_name in(SELECT col_name FROM table_name_2)
        • IN运算符可以检测结果集中是否存在某个特定的值.
      • 比较运算符查询
        • SELECT * FROM table_name WHERE row>=(SELECT col_name FROM table_name_2 WHERE id =1);
        • 子查询可以使用比较运算符
      • [NOT] EXISTS 子查询
        • SELECT * FROM table_name WHERE [NOT] EXISTS (SELECT * FROM table_book WHERE id=27);
        • 存在执行,不存在不执行.
        • NOT EXISTS反之.
      • ANY子查询
        • SELECT books,row FROM table_name WHERE row<ANY(SELECT row FROM tb_name);
        • 达到ANY条件输出
      • ALL 子查询
        • SELECT books,row FROM table_name WHERE row>=ALL(SELECT row FROM table_name).
      • 子查询剖析图
    • 合并查询 UNION
      • SELECT col_name FROM table_name_1 UNION
        SELECT col_name FROM table_name_2
      • SELECT col_name FROM table_name_1 UNION ALL
        SELECT col_name FROM table_name_2
      • 合并查询结果 使用UNION 和UNION ALL关键词
      • UNION可以去除相同记录.
      • UNION ALL只是单纯将结果集合并
    • 别名查询
      • 为表起别名
        • SELECT * FROM table_name table_alias WHERE table_alias.col_name =‘PHP’;
        • SELECT * FROM tb_user p WHERE p.name =“张三”;
      • 为字段取别名
        • col_name AS col_alias;
        • SELECT section AS login_section ,name AS login_name FROM table_login;
    • 正则表达式查询
      • SELECT * FROM info WHERE name REGEXP ‘正则表达式’.
  • 数据库思想 & 概念
    • 数据模式
    • 1NF、2NF、3NF、BCNF、4NF
    • 联结概念图
    • 数据库规范
    • 数据库视图VIEW
    • 数据库事务
    • 数据库概念设计
    • 用户信息表
    • 留言信息表
    • 回复信息
    • 管理员信息
  • 程序业务流程

结束语

如果您对这篇文章有什么意见或者建议,请评论与我讨论.
如果您觉得还不错的话~可以点个喜欢鼓励我哦.
如果您想和我一起学习,请毫不吝啬的私信我吧~
介个是我的个人博客,欢迎参观哦~