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

前言

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

全栈开发自学笔记 已开源

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

MySQL操作笔记

• MySQL操作笔记

• 程序业务流程

• 数据模式

• 1NF、2NF、3NF、BCNF、4NF

• 联结概念图

• 数据库规范

• 数据库视图VIEW

• 数据库事务

• 数据库概念设计

• 用户信息表

• 留言信息表

• 回复信息

• 管理员信息

• 查询语句基本格式

• 内连接查询

• 左外连接

• 右外连接

• 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.

• 外联结截图

• 自引用

• 复合条件

• 子查询

• 合并查询 UNION

• 别名查询

• 正则表达式查询

• SELECT col_name FROM table_name1 RIGHT JOIN table_name2 ON table_name1.col_name =table_name2.col_name AND condition

• 子查询剖析图

• SELECT books,row FROM table_name WHERE row>=ALL(SELECT row FROM table_name).

• SELECT books,row FROM table_name WHERE row<ANY(SELECT row FROM tb_name);

• 达到ANY条件输出

• SELECT * FROM table_name WHERE [NOT] EXISTS (SELECT * FROM table_book WHERE id=27);

• 存在执行,不存在不执行.

• NOT EXISTS反之.

• SELECT * FROM table_name WHERE row>=(SELECT col_name FROM table_name_2 WHERE id =1);

• 子查询可以使用比较运算符

• SELECT * FROM table_name_1 WHERE col_name in(SELECT col_name FROM table_name_2)

• IN运算符可以检测结果集中是否存在某个特定的值.

• IN 子查询

• 比较运算符查询

• [NOT] EXISTS 子查询

• ANY子查询

• ALL 子查询

• 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只是单纯将结果集合并

• col_name AS col_alias;

• SELECT section AS login_section ,name AS login_name FROM table_login;

• SELECT * FROM table_name table_alias WHERE table_alias.col_name =‘PHP’;

• SELECT * FROM tb_user p WHERE p.name =“张三”;

• 为表起别名

• 为字段取别名

• SELECT * FROM info WHERE name REGEXP ‘正则表达式’.

• 外键 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

• 插入数据 INSERT

• 查询数据 SELECT

• 修改数据 UPDATE

• 删除数据 DELETE

• 聚合函数

• INSERT INTO table_name(col_name1,col_name2) VALUES(value1,value2)

• 必选参数 INSERT

• 必选参数 INTO

• 必选参数 数据表名(要插入的列) table_name(column_name1,)

• 必选多可选 VALUES(value1,value2),(value3,value4)

• 示例

• 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 =“”;

• 示例

• 多表组合操作

• 排序规则

• 限制返回数量 LIMIT 结果数;

• 分页返回 LIMIT 起始位置,每页个数.

• LIMIT 3;

• LIMT 1,4;从1开始往后查4个

• SELECT * FROM table_name ORDER BY 字段1 [ASC|DESC] , 字段2 [ASC|DESC]

• 原理：如果第1段有相同的值，相同值的部分用第2个字段来排序。

• SELECT 字段列表,聚合函数 FROM table_name  GROUP BY 字段名 [ASC | DESC]

• 通俗说,GROUP BY是将 字段名一样的进行聚合函数的计算

• 示例

• 等于 =

• 大于 >

• 小于 <

• 大于等于 =>

• 小于等于 <=

• 不等于 != 或 <>

• 多条件查询 AND

• 多条件查询 OR

• 是否为空 IS NULL

• 不为空 IS NOT NULL

• 范围 ..介于 BETWEEN  AND

• 范围 ..不在 NOT BETWEEN  AND

• 在范围之内 IN

• 不在什么范围之内 NOT IN

• 模糊查询 包含 LIKE

• 模糊查询 不包含 NOT LIKE

• 正则表达式 REGEXP

• 语法：SELECT * FROM table_name WHERE 条件判断1 逻辑运算符  条件判断2

• 查询id=27的记录, 如果结果相等 返回1 不相等返回0

• 条件必须全部满足,才能被查询出来

• SELECT * FROM table_name WHERE condition_1 AND condition_2 …可以无限AND叠加

• SELECT * FROM table_name  WHERE user=‘mr’ AND section=‘php’.

• 满足其中一个条件就会被查出来

• SELECT * FROM table_name WHERE condition_1 OR condition_2…OR无限叠加

• SELECT * FROM table_name WHERE section=‘php’ OR section=‘程序开发’;

• SELECT books,row FROM table_name WHERE row IS NULL;

• SELECT books,row FROM table_name WHERE row IS NOT NULL;

• SELECT * FROM table_name WHERE condition BETWEEN 取值1 AND 取值2;

• SELECT * FROM table_name WHERE id BETWEEN 5 AND  7;

• SELECT * FROM table_name WHERE condition NOT BETWEEN 取值1 AND 取值2;

• SELECT * FROM table_name WHERE id NOT BETWEEN 5 AND  7;

• SELECT * FROM table_name WHERE condition IN (元素1,元素2)

• SELECT * FROM table_name WHERE user IN (‘mx’,’xiaomi’)

• SELECT * FROM table_name WHERE condition NOT IN (元素1,元素2)

• SELECT * FROM table_name WHERE user NOT IN (‘mx’,’xiaomi’)

• SELECT * FROM table_name WHERE user like ‘%mr%’

• LIKE (‘李%’) 匹配 包含 李

• LIKE (‘李_’) 匹配 李 + “ ”

• 可选组合参数 第二条件 HAVING

• 筛选GROUP BY之后的参数

• SELECT sex,COUNT(sex) FROM table_name GROUP BY sex HAVING COUNT(sex)>=3;

• 可选子参数 DISTINCT 去掉重复行.

• 可选子函数 CONCAT(col_name1,连接字符串,col_name2);

• 必选参数 查询 SELECT

• 必选参数 指定表 FROM

• 可选组合参数 条件 WHERE

• 可选组合参数 分组 GROUP BY

• 可选组合参数 排序 ORDER BY

• 可选组合参数 限定 LIMIT

• 如果col_name不存在,则创建一个新列

• UPDATE table_name
  SET col_name=
  CASE
  WHEN condition THEN ‘value’
  WHEN condition THEN ‘value’
  WHEN condition THEN ‘value’
  ELSE ‘value’
  END;

• 必选参数 UPDATE

• 必选参数 table_name

• 必选参数 SET

• 可选复合参数CASE WHEN

• 可选复合参数 col_name =new_value1,col_name 2=new_value2

• 可选参数 WHERE

• 可选参数 condition

• 必选参数 DELETE

• 必选参数 FROM

• 必选参数 table_name

• 可选参数 WHERE

• 可选参数 condition

• SELECT sum(row) FROM table_name;

• SELECT MAX(row) FROM table_name;

• SELECT MIN(row) FROM table_name;

• SELECT AVG(row) FROM table_name;

• SELECT COUNT(DISTINCT results) FROM student;

• COUNT(*)全部统计

• SELECT sex,count(sex) AS 人数 FROM student GROUP BY sex DESC.

• COUNT(字段名) 计算非空记录数

• AVG(字段名) 平均数

• MIN (字段名) 最小值

• MAX (字段名) 最大值

• STD(字段名)指定字段标准背离值

• STDTEV(字段名)指定字段标准背离值

• SUM(字段名)指定字段所有记录的总合

• 添加数据表 CREATE

• 查看表结构 SHOW COLUMNS/DESCRIBE

• 查看表详细结构语句 SHOW CREATE TABLE

• 修改表结构 ALTER TABLE

• 重命名 RENAME TABLE

• 删除表 DROP TABLE

• CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table_name(
  col_name type NOT NULL DEFAULT default_value AUTO_INCREMENT PRIMARY KEY,
  );

• 表至少包含一列,否则无法创建

• 列属性创建

• 必选参数 字段名字 col_name

• 必选参数 字段类型 type

• 可选参数 是否可以为空  NOT NULL / NULL

• 可选参数 默认值 DEFAULT default_value

• 可选参数 自增 AUTO_INCREMENT

• 可选参数 主键 PRIMARY KEY

• 可选参数 注释 reference_definition

• 判断是否存在,避免错误

• 如果使用该关键字,表示创建一个临时表

• 可选参数 TEMPORARY

• 必选参数 TABLE

• 是否存在 IF NOT EXISTS

• 定义列属性 create_definition() =>col_name

• 表参数 table_options

• 快速建表 select_statement

• 可选参数 COMMENT ‘备注’

• 设置主键 PRIMARY KEY (col_name)

• 自增列 AUTO_INCREMENT

• 示例:

• 必选参数 查询 SHOW

• 可选参数 FULL

• 必选参数 列数 COLUMNS

• 必选参数 来自 FROM table_name

• 可选参数 来自 FROM database_name

• ——

• 必选参数 列出 DESCRIBE

• 必选参数 数据表名 table_name

• 可选参数 列名 col_name

• 必选参数 SHOW

• 必选参数 CREATE

• 必选参数 TABLE

• 必选参数 table_name

• 可选参数 \G

• 修改表名 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

• 可选参数 IGNORE

• 必选参数 表名 TABLE table_name

• 必选一参数 ADD | MODIFY | CHANGE | DROP

• 必选多可选 alter_spec,[alter_spec]

• 示例 ALTER TABLE table_name ADD col_name type col_definition,MODIFY col_name type;

• RENAME TABLE old_name TO new_name;

• 必选参数 RENAME

• 必选参数 TABLE

• 必选参数 表名 table_name

• 必选参数 TO

• 必选参数 new_table_name;

• 示例

• 必选参数 DROP

• 必选参数 TABLE

• 可选参数 是否存在 IF EXISTS

• 必选参数 table_name;

• 创建数据库 CREATE DATABASE name

• 查看数据库 SHOW

• 使用数据库 USE DATABASE name

• 删除数据库 DROP DATABASE name

• 查看所有数据库 SHOW DATABASES

• 数学函数

• 字符串函数

• 日期和时间函数

• 条件判断函数

• 系统信息函数

• 加密函数

• 其他函数

• 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)

• 数字类型

• 字符串类型

• 日期类型

• 数据类型抉择

• TINYINT 最小整数 (127~255)

• BIT 最小整数 (127~255)

• BOOL 最小整数 (127~255)

• SMALLINT 小型整数 32767

• MEDIUMINT 中型整数 8388607

• INT 标准整数 2147683647

• BIGINT 大整数 9223372036854775807

• FLOAT 单精度浮点数

• DOUBLE 双精度浮点数

• DECIMAL 一般整数 自定义长度

• ENUM 65535

• SET 64

• TINYBLOB 225字节 小BLOB字段

• TINYTEXT 225字节 小TEXT字段

• BLOB 65535字节 常规BLOB字段

• TEXT 65535字节 常规TEXT字段

• MEDIUMBLOB 16777215字节 中型BLOB字段

• MEDIUMTEXT 16777215字节 中型TEXT字段

• LONGBLOB 4294967295字节 长BLOB字段

• LONGTEXT 4294967295 长TEXT字段

• CHAR 0-255

• VARCHAR 0-255

• 普通字符串类型

• TEXT 和 BLOB类型

• 特殊类型SET 和ENUM

• DATE

• TIME

• DATETIME

• TIMESTAMP

• YEAR

• 选择最小的可用类型,如果值永远不超过127,则使用TINYINT比INT强

• 对于完全是数字的,可用整数类型

• 浮点类型用于可能具有小数部分的数.

• 从速度方面考虑,要选择固定的列,可以使用CHAR 类型

• 要节省空间,使用动态的列,可以使用VARCHER类型

• 要将列中的内容限制在一种选择,可以使用ENUM类型.

• 允许在一个列中有多于一个的条目,可以使用SET类型

• 如果要搜索的内容不区分大小写,可使用TEXT类型

• 如果要搜索的内容区分大小写,可以使用BLOB类型

• 数据类型

• 内置函数

• 数据库操作

• 数据表操作

• 数据操作

• 约束操作

• 连接查询

• 数据库思想 & 概念