01-MySQL的登录

服务的启动与停止

# 启动 MySQL 服务命令：
net start MySQL服务名

# 停止 MySQL 服务命令：
net stop MySQL服务名

自带客户端的登录与登出

登录方式1：MySQL自带客户端 开始菜单 → 所有程序 → MySQL → MySQL 8.0 Command Line Client

mysql -h 主机名 -P 端口号 -u 用户名 -p密码
  1. -p与密码之间不能有空格，其他参数名与参数值之间可以有空格也可以没有空格
  2. 密码建议在下一行输入，保证安全

查看当前版本信息

mysql> select version()
c:\> mysql -V
c:\> mysql --version

02-MySQL基本操作

  1. 查看所有数据库：show databases;
  2. 创建自己的数据库：create database 数据库名;
  3. 使用数据库：use 数据库名;
  4. 查看某个库的所有表格：show tables from 数据库名;
  5. 创建新的表格：
  6.  create table 表名称(
     字段名    数据类型,
     字段名    数据类型
 );
  7. 查看一个表的数据：select * from 表名称;
  8. 添加一条记录：insert into 表名称 values(值列表）；
  9. 查看表的创建信息：show create table 表名称;
  10. 查看数据库的创建信息：show create database 数据库名;
  11. 删除表格：drop table 表名称;
  12. 删除数据库：drop database 数据库名；

  13. 数据库导入指令：source d:\mysqldb.sql

    03-MySQL目录结构

    主要目录结构

    MySQL的目录结构说明
    bin目录所有MySQL的可执行文件。如：mysql.exe
    MySQLLinstanceConfig.exe数据库的配置向导，在安装时出现的内容
    data目录系统数据库所在的目录
    my.ini文件MySQL的主要配置文件
    c:\ProgramData\MySQL\MySQL Server 8.0\data用户创建的数据库所在的目录

    04-SQL分类

    SQL语言在功能上主要分为如下3大类：

  14. DDL（Data Definition Languages、数据定义语言）

    • 这些语句定义了不同的数据库、表、视图、索 引等数据库对象，还可以用来创建、删除、修改数据库和数据表的结构。 主要的语句关键字包括 CREATE 、 DROP 、 ALTER 等。

  15. DML（Data Manipulation Language、数据操作语言）

    • 用于添加、删除、更新和查询数据库记 录，并检查数据完整性。 主要的语句关键字包括 INSERT 、 DELETE 、 UPDATE 、 SELECT 等。 SELECT是SQL语言的基础，最为重要。

  16. DCL（Data Control Language、数据控制语言）

    • 用于定义数据库、表、字段、用户的访问权限和 安全级别。 主要的语句关键字包括 GRANT 、 REVOKE 、 COMMIT 、 ROLLBACK 、 SAVEPOINT 等。

    因为查询语句使用的非常的频繁，所以很多人把查询语句单拎出来一类：DQL（数据查询语言）。

    还有单独将 COMMIT 、 ROLLBACK 取出来称为TCL （Transaction Control Language，事务控制语言）。

    05-SQL语言的规则与规范

  17. SQL 可以写在一行或者多行。为了提高可读性，各子句分行写，必要时使用缩进
  18. 每条命令以 ; 或 \g 或 \G 结束
  19. 关键字不能被缩写也不能分行

  20. 关于标点符号

    • 必须保证所有的()、单引号、双引号是成对结束的
    • 必须使用英文状态下的半角输入方式
    • 字符串型和日期时间类型的数据可以使用单引号（' '）表示
    • 列的别名，尽量使用双引号（" "），而且不建议省略as

  21. SQL大小写规范

    • MySQL 在 Windows 环境下是大小写不敏感
    • MySQL 在 Linux 环境下是大小写敏感的
    • 数据库名、表名、表的别名、变量名是严格区分大小写的

    • 关键字、函数名、列名(或字段名)、列的别名(字段的别名) 是忽略大小写的。

      • 推荐采用统一的书写规范
    • 数据库名、表名、表别名、字段名、字段别名等都小写
    • SQL 关键字、函数名、绑定变量等都大写

  22. 注释

    • 单行注释：#注释文字(MySQL特有的方式)
    • 单行注释：-- 注释文字(--后面必须包含一个空格。)
    • 多行注释：/* 注释文字 */

  23. 命名规则

    • 数据库、表名不得超过30个字符，变量名限制为29个
    • 必须只能包含 A–Z, a–z, 0–9, _共63个字符
    • 数据库名、表名、字段名等对象名中间不要包含空格
    • 同一个MySQL软件中，数据库不能同名；同一个库中，表不能重名；同一个表中，字段不能重名
    • 必须保证你的字段没有和保留字、数据库系统或常用方法冲突。如果坚持使用，请在SQL语句中使 用`（着重号）引起来
    • 保持字段名和类型的一致性，在命名字段并为其指定数据类型的时候一定要保证一致性。假如数据 类型在一个表里是整数，那在另一个表里可就别变成字符型了

    06-基本的SELECT语句

  24. SELECT ...;
  25. SELECT 标识选择哪些列 FROM 标识从哪个表中选择;
  26. 列的别名：SELECT last_name AS name, commission_pct comm FROM employees; 也可以在列名和别名之间加入关键字AS，别名使用双引号，以便在别名中包含空格或特 殊的字符并区分大小写：`SELECT last_name "Name", salary*12 "Annual Salary"
    FROM employees;`
  27. 去除重复行：SELECT DISINCT department_id FROM employees;
  28. 所有运算符或列值遇到null值，运算的结果都为null，在 MySQL 里面， 空值不等于空字符串。一个空字符串的长度是 0，而一个空值的长度是空。而且，在 MySQL 里面，空值是占用空间的

  29. 显示表结构：DESC employees;或DESCRIBE employees;

    1. Field：表示字段名称。
    2. Type：表示字段类型，这里 barcode、goodsname 是文本型的，price 是整数类型的。
    3. Null：表示该列是否可以存储NULL值。
    4. Key：表示该列是否已编制索引。PRI表示该列是表主键的一部分；UNI表示该列是UNIQUE索引的一 部分；MUL表示在列中某个给定值允许出现多次。
    5. Default：表示该列是否有默认值，如果有，那么值是多少。
    6. Extra：表示可以获取的与给定列有关的附加信息，例如AUTO_INCREMENT等。
  30. WHERE：SELECT 字段1,字段2 FROM 表名 WHERE 过滤条件

  31. CASE：

    CASE case_expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ...
    [ELSE else_result]
END

    07-运算符

  32. +：SELECT A + B

  33. -：SELECT A - B

    • 一个整数类型的值对整数进行加法和减法操作，结果还是一个整数；
    • 一个整数类型的值对浮点数进行加法和减法操作，结果是一个浮点数；
    • 加法和减法的优先级相同，进行先加后减操作与进行先减后加操作的结果是一样的；
    • 在Java中，+的左右两边如果有字符串，那么表示字符串的拼接。但是在MySQL中+只表示数 值相加。如果遇到非数值类型，先尝试转成数值，如果转失败，就按0计算。（补充：MySQL 中字符串拼接要使用字符串函数CONCAT()实现）
  34. *：SELECT A * B

  35. / 或 DIV：SELECT A / B或SELECT A DIV B

    • 一个数乘以整数1和除以整数1后仍得原数；
    • 一个数乘以浮点数1和除以浮点数1后变成浮点数，数值与原数相等；
    • 一个数除以整数后，不管是否能除尽，结果都为一个浮点数；
    • 一个数除以另一个数，除不尽时，结果为一个浮点数，并保留到小数点后4位；
    • 乘法和除法的优先级相同，进行先乘后除操作与先除后乘操作，得出的结果相同。
    • 在数学运算中，0不能用作除数，在MySQL中，一个数除以0为NULL。
  36. % 或 MOD：SELECT A % B 或 SELECT A MOD B

  37. =：SELECT C FROM TABLE WHERE A = B

    • 如果等号两边的值、字符串或表达式都为字符串，则MySQL会按照字符串进行比较，其比较的 是每个字符串中字符的ANSI编码是否相等。
    • 如果等号两边的值都是整数，则MySQL会按照整数来比较两个值的大小。
    • 如果等号两边的值一个是整数，另一个是字符串，则MySQL会将字符串转化为数字进行比较。
    • 如果等号两边的值、字符串或表达式中有一个为NULL，则比较结果为NULL。
  38. <=> 安全等于：SELECT C FROM TABLE WHERE A <=> B
  39. <> 或 !=：SELECT C FROM TABLE WHERE A ！= B 或 SELECT C FROM TABLE WHERE A <> B
  40. <：SELECT C FROM TABLE WHERE A < B
  41. <=：SELECT C FROM TABLE WHERE A <= B
  42. IS NOT NULL
  43. LEAST在多个值中返回最小值 SELECT C FROM TABLE WHERE LEAST(A,B)
  44. GREATEST 在多个值中返回最大值
  45. BETWEEN AND
  46. IS NULL
  47. IN 属于运算符
  48. NOT IN

  49. LIKE 判断一个值是否符合模糊匹配规则

    1. %”：匹配0个或多个字符。
    2. “_”：只能匹配一个字符。

  50. ESCAPE 指定在模式匹配中使用的转义字符，以便在搜索或匹配特定字符时避免歧义

    在一个字符串中查找匹配字符"%"，但又不希望它被当作通配符使用，可以在查询中使用 ESCAPE 关键字指定转义字符，比如：

    通过使用 ESCAPE ‘’, 可以将"%“字符前的”\"作为转义字符，确保 “%” 被当作普通字符匹配，而不是通配符。这样就避免了%符号被误解为模式中的通配符

    SELECT * FROM table_name WHERE column_name LIKE '%\%%' ESCAPE '\';

  51. REGEXP或RLIKE 判断一个值是否符合正则式的规则 SELECT C FROM TABLE WHERE A REGEXP B

    1. ‘^’匹配以该字符后面的字符开头的字符串。
    2. ‘$’匹配以该字符前面的字符结尾的字符串。
    3. ‘.’匹配任何一个单字符。
    4. “[...]”匹配在方括号内的任何字符。例如，“[abc]”匹配“a”或“b”或“c”。为了命名字符的范围，使用一 个‘-’。“[a-z]”匹配任何字母，而“[0-9]”匹配任何数字。
    5. ​*​ 匹配零个或多个在它前面的字符。例如，“x*”匹配任何数量的‘x’字符，“[0-9]*”匹配任何数量的数字， 而“*”匹配任何数量的任何字符。
  52. NOT 或 ！ 逻辑非
  53. AND 或 &&

  54. OR 或 ||

    1. OR可以和AND一起使用，但是在使用时要注意两者的优先级，由于AND的优先级高于OR，因此先 对AND两边的操作数进行操作，再与OR中的操作数结合。
    2. 当给定的值都不为NULL，并且任何一个值为非0值时，则返 回1，否则返回0；当一个值为NULL，并且另一个值为非0值时，返回1，否则返回NULL；当两个值都为 NULL时，返回NULL

  55. XOR 逻辑异或

    1. 给定的值中任意一个值为NULL时，则返回NULL；如果 两个非NULL的值都是0或者都不等于0时，则返回0；如果一个值为0，另一个值不为0时，则返回1。

  56. & 按位与

    1. 符将给定值对应的二进制数逐位进行逻辑与运算。当给定值对应的二 进制位的数值都为1时，则该位返回1，否则返回0

  57. | 按位或

    1. 将给定的值对应的二进制数逐位进行逻辑或运算。当给定值对应的 二进制位的数值有一个或两个为1时，则该位返回1，否则返回0。
  58. ^ 按位异或
  59. ~ 按位取反

  60. >>按位右移

    1. 将给定的值的二进制数的所有位右移指定的位数。右移指定的 位数后，右边低位的数值被移出并丢弃，左边高位空出的位置用0补齐

  61. << 按位左移

    1. 符将给定的值的二进制数的所有位左移指定的位数。左移指定的 位数后，左边高位的数值被移出并丢弃，右边低位空出的位置用0补齐

    运算符优先级

    优先级运算符
    1:=，=（赋值）
    2\\，OR，XOR
    3&&，AND
    4NOT
    5BETWEEN，CASE，WHEN，THEN，ELSE
    6=（等号），<=>，>=，<=，<，<>，！=，IS，LIKE，REGEXP，IN
    7\
    8&
    9<<，>>
    10-，+
    11*，/，DIV，%，MOD
    12^
    13-，~
    14！
    15()

    08-正则表达式

    MySQL中使用REGEXP关键字指定正则表达式的字符匹配模式。下表列出了REGEXP操作符中常用字符匹配 列表。

  62. ^：匹配文本的开始字符
  63. $：匹配文本的结束字符
  64. .：匹配任何单个字符
  65. *：匹配零个或多个在它前面的字符
  66. +：匹配前面的字符一次或多次
  67. <字符串>：匹配包含指定的字符的文本
  68. [ 字符串集合 ]：匹配字符集合中的人一个字符
  69. [^]：匹配不在括号中的任何字符
  70. 字符串{n}：匹配前面的字符串至少n次

  71. 字符串{n,m}：陪陪前面的字符串至少n次，至多m次

    09-排序与分页

  72. ORDER BY子句排序

    1. ASC：升序
    2. DESC：降序
    3. 可以使用不在SELECT列表中的列排序
    4. 在对多列进行排序的时候，首先排序的第一列必须有相同的列值，才会对第二列进行排序。如果第 一列数据中所有值都是唯一的，将不再对第二列进行排序

  73. LIMIT分页

    1. LIMIT [位置偏移量] 行数
    2. SELECT * FROM table LIMIT(PageNo - 1)*PageSize, PageSize
    3. 在不同的 DBMS 中使用的关键字可能不同。在 MySQL、PostgreSQL、MariaDB 和 SQLite 中使用 LIMIT 关 键字，而且需要放到 SELECT 语句的最后面。
    4. 如果是 SQL Server 和 Access，需要使用 TOP 关键字，比如：SELECT TOP 5 name, hp_max FROM heros ORDER BY hp_max DESC
    5. 如果是 DB2，使用 FETCH FIRST 5 ROWS ONLY 这样的关键字：SELECT name, hp_max FROM heros ORDER BY hp_max DESC FETCH FIRST 5 ROWS ONLY
    6. 如果是 Oracle，你需要基于 ROWNUM 来统计行数：SELECT rownum,last_name,salary FROM employees WHERE rownum < 5 ORDER BY salary DESC;

    10-多表查询

    多表查询，也称为关联查询，指两个或更多个表一起完成查询操作

  74. 等值连接与非等值连接

    • 【 强制 】对于数据库中表记录的查询和变更，只要涉及多个表，都需要在列名前加表的别名（或 表名）进行限定。
    • 说明 ：对多表进行查询记录、更新记录、删除记录时，如果对操作列没有限定表的别名（或表 名），并且操作列在多个表中存在时，就会抛异常。
    • 连接 n个表,至少需要n-1个连接条件。比如，连接三个表，至少需要两个连接条件
  75. 自连接与非自连接

  76. 内连接: 合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行

    • SELECT 字段列表 FROM A表 INNER JOIN B表 ON 关联条件 WHERE 等其他子句

  77. 外连接: 两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的 行 ，这种连接称为左（或右） 外连接。没有匹配的行时, 结果表中相应的列为空(NULL)。

    • 如果是左外连接，则连接条件中左边的表也称为 主表 ，右边的表称为 从表 。

    • SELECT 字段列表 FROM A表 LEFT JOIN B表 ON 关联条件 WHERE 等其他子句;

      • 如果是右外连接，则连接条件中右边的表也称为 主表 ，左边的表称为 从表 。
    • FROM A表 RIGHT JOIN B表 ON 关联条件 WHERE 等其他子句;

  78. 满外连接

    • 满外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。
    • MySQL不支持FULL JOIN，但是可以用 LEFT JOIN UNION RIGHT join代替。

    11-UNION的使用

    合并查询结果 利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集。合并 时，两个表对应的列数和数据类型必须相同，并且相互对应。各个SELECT语句之间使用UNION或UNION ALL关键字分隔。

    SELECT column,... FROM table1 UNION [ALL] SELECT column,... FROM table2

    UNION 操作符返回两个查询的结果集的并集，去除重复记录。

    UNION ALL操作符返回两个查询的结果集的并集。对于两个结果集的重复部分，不去重。

    注意：执行UNION ALL语句时所需要的资源比UNION语句少。如果明确知道合并数据后的结果数据 不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高数据查询的效 率。

    12-内置函数

  79. 数值函数

    • ABS(x)：返回x的绝对值
    • SIGN(x)：返回x的符号。正数返回1，负数返回-1，0返回0
    • PI()：返回圆周率的值
    • CEIL(x)，CEILING(x)：返回大于或等于某个值的最小整数
    • FLOOR(x)：返回小于或等于某个值的最大整数
    • LEAST(e1,e2,e3…)：返回列表中的最小值
    • GREATEST(e1,e2,e3…)：返回列表中的最大值
    • MOD(x,y)：返回X除以Y后的余数
    • RAND()：返回0~1的随机值
    • RAND(x)：返回0~1的随机值，其中x的值用作种子值，相同的X值会产生相同的随机数
    • ROUND(x)：返回一个对x的值进行四舍五入后，最接近于X的整数
    • ROUND(x,y)：返回一个对x的值进行四舍五入后最接近X的值，并保留到小数点后面Y位
    • TRUNCATE(x,y)：返回数字x截断为y位小数的结果
    • SQRT(x)：返回x的平方根。当X的值为负数时，返回NULL
    • POW(x,y)，POWER(X,Y)：返回x的y次方
    • EXP(X)：返回e的X次方，其中e是一个常数，2.718281828459045
    • LN(X)，LOG(X)：返回以e为底的X的对数，当X <= 0 时，返回的结果为NULL
    • LOG10(X)：返回以10为底的X的对数，当X <= 0 时，返回的结果为NULL
    • LOG2(X)：返回以2为底的X的对数，当X <= 0 时，返回NULL

  80. 三角函数

    • SIN(x)：返回x的正弦值，其中，参数x为弧度值
    • ASIN(x)：返回x的反正弦值，即获取正弦为x的值。如果x的值不在-1到1之间，则返回NULL
    • COS(x)：返回x的余弦值，其中，参数x为弧度值
    • ACOS(x)：返回x的反余弦值，即获取余弦为x的值。如果x的值不在-1到1之间，则返回NULL
    • TAN(x)：返回x的正切值，其中，参数x为弧度值
    • ATAN(x)：返回x的反正切值，即返回正切值为x的值
    • ATAN2(m,n)：返回两个参数的反正切值
    • COT(x)：返回x的余切值，其中，X为弧度值
    • RADIANS(x)：将角度转化为弧度，其中，参数x为角度值
    • DEGREES(x)：将弧度转化为角度，其中，参数x为弧度值

  81. 进制间转换

    • BIN(x)：返回x的二进制编码
    • HEX(x)：返回x的十六进制编码
    • OCT(x)：返回x的八进制编码
    • CONV(x,f1,f2)：返回f1进制数变成f2进制数

  82. 字符串函数

    • ASCII(S)：返回字符串S中的第一个字符的ASCII码值
    • CHAR_LENGTH(s)：返回字符串s的字符数。作用与CHARACTER_LENGTH(s)相同
    • LENGTH(s)：返回字符串s的字节数，和字符集有关
    • CONCAT(s1,s2,......,sn)：连接s1,s2,......,sn为一个字符串
    • CONCAT_WS(x, s1,s2,......,sn)：同CONCAT(s1,s2,...)函数，但是每个字符串之间要加上x
    • INSERT(str, idx, len, replacestr)：将字符串str从第idx位置开始，len个字符长的子串替换为字符串replacestr
    • REPLACE(str, a, b)：用字符串b替换字符串str中所有出现的字符串a
    • UPPER(s) 或 UCASE(s)：将字符串s的所有字母转成大写字母
    • LOWER(s) 或LCASE(s)：将字符串s的所有字母转成小写字母
    • LEFT(str,n)：返回字符串str最左边的n个字符
    • RIGHT(str,n)：返回字符串str最右边的n个字符
    • LPAD(str, len, pad)：用字符串pad对str最左边进行填充，直到str的长度为len个字符
    • RPAD(str ,len, pad)：用字符串pad对str最右边进行填充，直到str的长度为len个字符
    • LTRIM(s)：去掉字符串s左侧的空格
    • RTRIM(s)：去掉字符串s右侧的空格
    • TRIM(s)：去掉字符串s开始与结尾的空格
    • TRIM(s1 FROM s)：去掉字符串s开始与结尾的s1
    • TRIM(LEADING s1 FROM s)：去掉字符串s开始处的s1
    • TRIM(TRAILING s1 FROM s)：去掉字符串s结尾处的s1
    • REPEAT(str, n)：返回str重复n次的结果
    • SPACE(n)：返回n个空格
    • STRCMP(s1,s2)：比较字符串s1,s2的ASCII码值的大小
    • SUBSTR(s,index,len)：返回从字符串s的index位置其len个字符，作用与SUBSTRING(s,n,len)、 MID(s,n,len)相同
    • LOCATE(substr,str)：返回字符串substr在字符串str中首次出现的位置，作用于POSITION(substr IN str)、INSTR(str,substr)相同。未找到，返回0
    • ELT(m,s1,s2,…,sn)：返回指定位置的字符串，如果m=1，则返回s1，如果m=2，则返回s2，如 果m=n，则返回sn
    • FIELD(s,s1,s2,…,sn)：返回字符串s在字符串列表中第一次出现的位置
    • FIND_IN_SET(s1,s2)：返回字符串s1在字符串s2中出现的位置。其中，字符串s2是一个以逗号分 隔的字符串
    • REVERSE(s)：返回s反转后的字符串
    • NULLIF(value1,value2)：比较两个字符串，如果value1与value2相等，则返回NULL，否则返回 value
    • 注意：MySQL中，字符串的位置是从1开始的。

  83. 日期和时间函数

    • CURDATE() ，CURRENT_DATE()：返回当前日期，只包含年、 月、日
    • CURTIME() ， CURRENT_TIME()：返回当前时间，只包含时、 分、秒
    • NOW() / SYSDATE() / CURRENT_TIMESTAMP() / LOCALTIME() / LOCALTIMESTAMP()：返回当前系统日期和时间
    • UTC_DATE()：返回UTC（世界标准时间） 日期
    • UTC_TIME()：返回UTC（世界标准时间） 时间
    • UNIX_TIMESTAMP()：以UNIX时间戳的形式返回当前时间。SELECT UNIX_TIMESTAMP() - >1634348884
    • UNIX_TIMESTAMP(date)：将时间date以UNIX时间戳的形式返回
    • FROM_UNIXTIME(timestamp)：将UNIX时间戳的时间转换为普通格式的时间
    • YEAR(date) / MONTH(date) / DAY(date)：返回具体的日期值
    • HOUR(time) / MINUTE(time) / SECOND(time)：返回具体的时间值
    • MONTHNAME(date)：返回月份：January，..
    • DAYNAME(date)：返回星期几：MONDAY，TUESDAY.....SUNDAY
    • WEEKDAY(date)：返回周几，注意，周1是0，周2是1，。。。周日是6
    • QUARTER(date)：返回日期对应的季度，范围为1～4
    • WEEK(date) ， WEEKOFYEAR(date)：返回一年中的第几周
    • DAYOFYEAR(date)：返回日期是一年中的第几天
    • DAYOFMONTH(date)：返回日期位于所在月份的第几天
    • DAYOFWEEK(date)：返回周几，注意：周日是1，周一是2，。。。周六是 7
    • EXTRACT(type FROM date)：返回指定日期中特定的部分，type指定返回的值
    • TIME_TO_SEC(time) 将 time 转化为秒并返回结果值。转化的公式为： 小时3600+分钟 60+秒
    • SEC_TO_TIME(seconds) 将 seconds 描述转化为包含小时、分钟和秒的时间
    • DATE_ADD(datetime, INTERVAL expr type)， ADDDATE(date,INTERVAL expr type)：返回与给定日期时间相差INTERVAL时 间段的日期时间
    • DATE_SUB(date,INTERVAL expr type)， SUBDATE(date,INTERVAL expr type)：返回与date相差INTERVAL时间间隔的日期
    • ADDTIME(time1,time2)：返回time1加上time2的时间。当time2为一个数字时，代表的是 秒 ，可以为负数
    • SUBTIME(time1,time2)：返回time1减去time2后的时间。当time2为一个数字时，代表的 是 秒 ，可以为负数
    • DATEDIFF(date1,date2)：返回date1 - date2的日期间隔天数
    • TIMEDIFF(time1, time2)：返回time1 - time2的时间间隔
    • FROM_DAYS(N)：返回从0000年1月1日起，N天以后的日期
    • TO_DAYS(date)：返回日期date距离0000年1月1日的天数
    • LAST_DAY(date)：返回date所在月份的最后一天的日期
    • MAKEDATE(year,n)：针对给定年份与所在年份中的天数返回一个日期
    • MAKETIME(hour,minute,second)：将给定的小时、分钟和秒组合成时间并返回
    • PERIOD_ADD(time,n)：返回time加上n后的时间

  84. 日期的格式化与解析

    • DATE_FORMAT(date,fmt)：按照字符串fmt格式化日期date值
    • TIME_FORMAT(time,fmt)：按照字符串fmt格式化时间time值
    • GET_FORMAT(date_type,format_type)：返回日期字符串的显示格式
    • STR_TO_DATE(str, fmt)：按照字符串fmt对str进行解析，解析为一个日期
    • %Y 4位数字表示年份 %y 表示两位数字表示年份
    • %M 月名表示月份（January,....） %m 两位数字表示月份 （01,02,03。。。）
    • %b 缩写的月名（Jan.，Feb.，....） %c 数字表示月份（1,2,3,...）
    • %D 英文后缀表示月中的天数 （1st,2nd,3rd,...） %d 两位数字表示月中的天数(01,02...)
    • %e 数字形式表示月中的天数 （1,2,3,4,5.....）
    • %H 两位数字表示小数，24小时制 （01,02..） %h 和%I 两位数字表示小时，12小时制 （01,02..）
    • %k 数字形式的小时，24小时制(1,2,3) %l 数字形式表示小时，12小时制 （1,2,3,4....）
    • %i 两位数字表示分钟（00,01,02） %S 和%s 两位数字表示秒(00,01,02...)
    • %W 一周中的星期名称（Sunday...） %a 一周中的星期缩写（Sun.， Mon.,Tues.，..）
    • %w 以数字表示周中的天数 (0=Sunday,1=Monday....)
    • %j 以3位数字表示年中的天数(001,002...) %U 以数字表示年中的第几周， （1,2,3。。）其中Sunday为周中第一 天
    • %u 以数字表示年中的第几周， （1,2,3。。）其中Monday为周中第一 天
    • %T 24小时制 %r 12小时制
    • %p AM或PM %% 表示%

  85. 流程处理函数

    • IF(value,value1,value2) 如果value的值为TRUE，返回value1， 否则返回value2
    • IFNULL(value1, value2) 如果value1不为NULL，返回value1，否 则返回value2
    • CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 .... [ELSE resultn] END 相当于Java的if...else if...else...
    • CASE expr WHEN 常量值1 THEN 值1 WHEN 常量值1 THEN 值1 .... [ELSE 值n] END 相当于Java的switch...case...

  86. 加密与解密函数

    • PASSWORD(str)：返回字符串str的加密版本，41位长的字符串。加密结果不可逆 ，常用于用户的密码加密
    • MD5(str)：返回字符串str的md5加密后的值，也是一种加密方式。若参数为 NULL，则会返回NULL
    • SHA(str)：从原明文密码str计算并返回加密后的密码字符串，当参数为 NULL时，返回NULL。 SHA加密算法比MD5更加安全
    • ENCODE(value,password_seed)：返回使用password_seed作为加密密码加密value
    • DECODE(value,password_seed)：返回使用password_seed作为加密密码解密value

  87. MySQL信息函数

    • VERSION() 返回当前MySQL的版本号
    • CONNECTION_ID() 返回当前MySQL服务器的连接数
    • DATABASE()，SCHEMA() 返回MySQL命令行当前所在的数据库
    • USER()，CURRENT_USER()、SYSTEM_USER()， SESSION_USER() 返回当前连接MySQL的用户名，返回结果格式为 “主机名@用户名”
    • CHARSET(value) 返回字符串value自变量的字符集
    • COLLATION(value) 返回字符串value的比较规则
    • FORMAT(value,n) 返回对数字value进行格式化后的结果数据。n表示 四舍五入 后保留 到小数点后n位
    • CONV(value,from,to) 将value的值进行不同进制之间的转换
    • INET_ATON(ipvalue) 将以点分隔的IP地址转化为一个数字
    • INET_NTOA(value) 将数字形式的IP地址转化为以点分隔的IP地址
    • BENCHMARK(n,expr) 将表达式expr重复执行n次。用于测试MySQL处理expr表达式所耗费 的时间
    • CONVERT(value USING char_code) 将value所使用的字符编码修改为char_code

    13-聚合函数

    聚合函数作用于一组数据，并对一组数据返回一个值。

  88. AVG()
  89. SUM()
  90. MAX()
  91. MIN()

  92. COUNT()

    • COUNT(*)返回表中记录总数，适用于任意数据类型
    • COUNT(expr) 返回expr不为空的记录总数
    • 用count(*)，count(1)，count(列名)谁好呢？

    • 对于MyISAM引擎的表是没有区别的。这种引擎内部有一计数器在维护着行数。 Innodb引擎的表用count(*),count(1)直接读行数，复杂度是O(n)，因为innodb真的要去数一遍。但好于具体的count(列名)。

      • 能不能使用count(列名)替换count(*)?
    • 不要使用 count(列名)来替代 count( ) ， count( ) 是 SQL92 定义的标准统计行数的语法，跟数 据库无关，跟 NULL 和非 NULL 无关。
    • count(*)会统计值为 NULL 的行，而 count(列名)不会统计此列为 NULL 值的行。
      *

    GROUP BY子句

    可以使用GROUP BY子句将表中的数据分成若干组

    SELECT column, group_function(column)
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column];

    在SELECT列表中所有未包含在组函数中的列都应该包含在 GROUP BY子句

    使用 WITH ROLLUP 关键字之后，在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所 有记录的总和，即统计记录数量。

    HAVING子句

  93. 行已经被分组。
  94. 使用了聚合函数。
  95. 满足HAVING 子句中条件的分组将被显示。

  96. HAVING 不能单独使用，必须要跟 GROUP BY 一起使用。

    SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary)>10000

    非法使用聚合函数 ： 不能在 WHERE 子句中使用聚合函数。

    WHERE和HAVING的对比

    WHERE 可以直接使用表中的字段作为筛选条件，但不能使用分组中的计算函数作为筛选条件； HAVING 必须要与 GROUP BY 配合使用，可以把分组计算的函数和分组字段作为筛选条件。

    如果需要通过连接从关联表中获取需要的数据，WHERE 是先筛选后连接，而 HAVING 是先连接 后筛选。

    14-SELECT的执行过程

    #方式1：
SELECT ...,....,...
FROM ...,...,....
WHERE 多表的连接条件
AND 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,...
#方式2：
SELECT ...,....,...
FROM ... JOIN ...
ON 多表的连接条件
JOIN ...
ON ...
WHERE 不包含组函数的过滤条件
AND/OR 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,...
#其中：
#（1）from：从哪些表中筛选
#（2）on：关联多表查询时，去除笛卡尔积
#（3）where：从表中筛选的条件
#（4）group by：分组依据
#（5）having：在统计结果中再次筛选
#（6）order by：排序
#（7）limit：分页

    关键字的顺序是不能颠倒的：

    SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT...

    SELECT 语句的执行顺序（在 MySQL 和 Oracle 中，SELECT 执行顺序基本相同）：

    FROM -> WHERE -> GROUP BY -> HAVING -> SELECT 的字段 -> DISTINCT -> ORDER BY -> LIMIT

    15-子查询

    子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从MySQL 4.1开始引入。

    SELECT select_list
FROM table
WHERE expr operator    (SELECT select_list FROM table);
  97. 子查询（内查询）在主查询之前一次执行完成
  98. 子查询的结果被主查询（外查询）使用
  99. 子查询要包含在括号内
  100. 将子查询放在比较条件的右侧
  101. 单行操作符对应单行子查询，多行操作符对应多行子查询

  102. 单行子查询

    • =
    • =
    • <
    • <=
    • <>

  103. 多行子查询

    • IN 等于列表中的任意一个
    • ANY 需要和单行比较操作符一起使用，和子查询返回的某一个值比较
    • ALL 需要和单行比较操作符一起使用，和子查询返回的所有值比较
    • SOME 实际上是ANY的别名，作用相同，一般常使用ANY

  104. 关联子查询

    • 如果子查询的执行依赖于外部查询，通常情况下都是因为子查询中的表用到了外部的表，并进行了条件 关联，因此每执行一次外部查询，子查询都要重新计算一次，这样的子查询就称之为 关联子查询 。

    SELECT column1, column2, ...
    FROM table1, other
    WHERE column1 operator

         (SELECT column1, column2 FROM table2 WHERE expr1 = outer.expr2);

16-基础知识

一条数据存储的过程

一个完整的数据存储过程总共有 4 步，分别是创建数据库、确认字段、创建数据表、插入数据

为从系统架构的层次上看，MySQL 数据库系统从大到小依次是 数据库服务器 、 数据库 、 数据表 、数 据表的 行与列

标识符命名规则

  • 数据库名、表名不得超过30个字符，变量名限制为29个
  • 必须只能包含 A–Z, a–z, 0–9, _共63个字符
  • 数据库名、表名、字段名等对象名中间不要包含空格
  • 同一个MySQL软件中，数据库不能同名；同一个库中，表不能重名；同一个表中，字段不能重名
  • 必须保证你的字段没有和保留字、数据库系统或常用方法冲突。如果坚持使用，请在SQL语句中使 用`（着重号）引起来
  • 保持字段名和类型的一致性：在命名字段并为其指定数据类型的时候一定要保证一致性，假如数据 类型在一个表里是整数，那在另一个表里可就别变成字符型了

数据类型

类型数据类型
整数类型TINYINT、SMALLINT、MEDIUMINT、INT(或INTEGER)、BIGINT
浮点类型FLOAT、DOUBLE
定点数类型DECIMAL
位类型BIT
日期时间类型YEAR、TIME、DATE、DATETIME、TIMESTAMP
文本字符串类型CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT
枚举类型ENUM
集合类型SET
二进制字符串类型BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB
JSON类型JSON对象、JSON数组
空间数据类型单值：GEOMETRY、POINT、LINESTRING、POLYGON；
集合：MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、 GEOMETRYCOLLECTION

常用数据类型

数据类型描述
INT从-2^31到2^31-1的整型数据。存储大小为 4个字节
CHAR(size)定长字符数据。若未指定，默认为1个字符，最大长度255
VARCHAR(size)可变长字符数据，根据字符串实际长度保存，必须指定长度
FLOAT(M,D)单精度，占用4个字节，M=整数位+小数位，D=小数位。 D<=M<=255,0<=D<=30， 默认M+D<=6
DOUBLE(M,D)双精度，占用8个字节，D<=M<=255,0<=D<=30，默认M+D<=15
DECIMAL(M,D)高精度小数，占用M+2个字节，D<=M<=65，0<=D<=30，最大取值范围与DOUBLE 相同。
DATE日期型数据，格式'YYYY-MM-DD'
BLOB二进制形式的长文本数据，最大可达4G
TEXT长文本数据，最大可达4G

数据类型属性

MySQL关键字含义
NULL数据列可包含NULL值
NOT NULL数据列不允许包含NULL值
DEFAULT默认值
PRIMARY KEY主键
AUTO_INCREMENT自动递增，适用于整数类型
UNSIGNED无符号
CHARACTER SET name指定一个字符集

阿里巴巴《Java开发手册》之MySQL字段命名

  • 【 强制 】表名、字段名必须使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间只出 现数字。数据库字段名的修改代价很大，因为无法进行预发布，所以字段名称需要慎重考虑。

    • 正例：aliyun_admin，rdc_config，level3_name
    • 反例：AliyunAdmin，rdcConfig，level_3_name
  • 【 强制 】禁用保留字，如 desc、range、match、delayed 等，请参考 MySQL 官方保留字。

  • 【 强制 】表必备三字段：id, gmt_create, gmt_modified。

    • 说明：其中 id 必为主键，类型为BIGINT UNSIGNED、单表时自增、步长为 1。
    • gmt_create, gmt_modified 的类型均为 DATETIME 类型，前者现在时表示主动式创建，后者过去分词表示被 动式更新

  • 【 推荐 】表的命名最好是遵循 “业务名称_表的作用”

    • 正例：alipay_task 、 force_project、 trade_config
  • 【 推荐 】库名与应用名称尽量一致
  • 【参考】合适的字符存储长度，不但节约数据库表空间、节约索引存储，更重要的是提升检索速度。
  • 任何字段如果为非负数，必须是 UNSIGNED

  • 【 强制 】小数类型为 DECIMAL，禁止使用 FLOAT 和 DOUBLE。

    • 说明：在存储的时候，FLOAT 和 DOUBLE 都存在精度损失的问题，很可能在比较值的时候，得到不正确的结果。如果存储的数据范围超过 DECIMAL 的范围，建议将数据拆成整数和小数并 分开存储。
  • 【 强制 】如果存储的字符串长度几乎相等，使用 CHAR 定长字符串类型
  • 【 强制 】VARCHAR 是可变长字符串，不预先分配存储空间，长度不要超过 5000。如果存储长度大 于此值，定义字段类型为 TEXT，独立出来一张表，用主键来对应，避免影响其它字段索引效率。
最后修改：2024 年 05 月 14 日
© 允许规范转载
如果觉得我的文章对你有用，请随意赞赏