MYSQL 基础
参考
https://mp.weixin.qq.com/s/JWU3yOxZnZRuMUpDP46dCQ
数据类型
https://www.runoob.com/mysql/mysql-data-types.html 数据类型选择： https://www.cnblogs.com/liqiangchn/p/8922185.html
数值类型
关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。
create table demo2(
c1 tinyint unsigned -- 无符号整型范围更大
);
| 类型 | 大小 | 范围（有符号） | 范围（无符号） | 用途 |
|---|---|---|---|---|
| TINYINT | 1 Bytes | (-128，127) | (0，255) | 小整数值 |
| SMALLINT | 2 Bytes | (-32 768，32 767) | (0，65 535) | 大整数值 |
| MEDIUMINT | 3 Bytes | (-8 388 608，8 388 607) | (0，16 777 215) | 大整数值 |
| INT或INTEGER | 4 Bytes | (-2 147 483 648，2 147 483 647) | (0，4 294 967 295) | 大整数值 |
| BIGINT | 8 Bytes | (-9,223,372,036,854,775,808，9 223 372 036 854 775 807) | (0，18 446 744 073 709 551 615) | 极大整数值 |
| FLOAT | 4 Bytes | (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) | 0，(1.175 494 351 E-38，3.402 823 466 E+38) | 单精度 浮点数值 |
| DOUBLE | 8 Bytes | (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 双精度 浮点数值 |
| DECIMAL | 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 | 依赖于M和D的值 | 依赖于M和D的值 | 小数值 |
| BIT | — | — | — | 布尔类型只能 1/0 |
注意
1：浮点和定点数值都可以使用(M,D)形式
2：int(N) 当插入数据会在数值补充0，N表示的是显示宽度，不足的用0补足，超过的无视长度而直接显示整个数字，但这要整型设置了unsigned zerofill才有效
int(n)中的n省略的时候，宽度为对应类型无符号最大值的十进制的长度10，如bigint无符号最大值为2^64-1 = 18,446,744,073,709,551,615；长度是20位
`d` int(5) unsigned zerofill DEFAULT NULL
// 当存储1，显示00001
3：选择无符号可以存储从0开始更大的整数，如果确定没有负数可以选择无符号整型
4：存储开关0/1 使用tinyint(1)更好，只有1字节
5：浮点数float在储存空间及运行效率上要优于精度数值类型decimal，但float与double会有舍入错误而decimal则可以提供更加准确的小数级精确运算不会有错误产生计算更精确，适用于金融类型数据的存储。
6：decimal MySQL内部是以 字符串 的形式进行存储，这就决定了它一定是精准
7：float和double在不指定精度时，默认会按照实际的精度来显示，而DECIMAL在不指定精度时，默认整数为10，小数为0。
decimal采用的是四舍五入，float和double采用的是四舍六入五成双
create table test5(a float(5,2),b double(5,2),c decimal(5,2));
-- insert into test5 values (1,1,1),(2.1,2.1,2.1)
| 1.00 | 1.00 | 1.00 |
| 2.10 | 2.10 | 2.10 |
-- insert into test5 values (3.123,3.123,3.123),(4.125,4.125,4.125)
| 3.12 | 3.12 | 3.12 |
| 4.12 | 4.12 | 4.13 | // 浮点数类型遇到5会先看前面一位是偶数就不进位
-- insert into test5 values (5.115,5.115,5.115) //浮点数遇到5看前面是奇数则进位
| 5.12 | 5.12 | 5.12 |
float、double会存在精度问题，decimal精度正常
create table test6(a float,b double,c decimal);
insert into test6 values (1,1,1),(1.234,1.234,1.4),(1.234,0.01,1.5);
select sum(a),sum(b),sum(c) from test6;
| a | b | c |
+-------+-------+------+
| 1 | 1 | 1 |
| 1.234 | 1.234 | 1 |
| 1.234 | 0.01 | 2 |
+-------+-------+------+
字符串类型
| 类型 | 大小 | 用途 |
|---|---|---|
| CHAR | 0-255 bytes | 定长字符串 |
| VARCHAR | 0-65535 bytes | 变长字符串 |
| TINYBLOB | 0-255 bytes | 不超过 255 个字符的二进制字符串 |
| TINYTEXT | 0-255 bytes | 短文本字符串 |
| BLOB | 0-65 535 bytes | 二进制形式的长文本数据 |
| TEXT | 0-65 535 bytes | 长文本数据 |
| MEDIUMBLOB | 0-16 777 215 bytes | 二进制形式的中等长度文本数据 |
| MEDIUMTEXT | 0-16 777 215 bytes | 中等长度文本数据 |
| LONGBLOB | 0-4 294 967 295 bytes | 二进制形式的极大文本数据 |
| LONGTEXT | 0-4 294 967 295 bytes | 极大文本数据 |
注意
1：CHAR 列删除了尾部的空格，而 VARCHAR 则保留这些空格
2：CHAR是定长，GUID这些使用CHAR(36)比较好
3：TEXT是字符存储，BLOB是二进制存储
4：选择使用 BLOB 类型，需要转换为字符串：
SELECT CONVERT(*** USING utf8) FROM table;
5：char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。
6：CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。
7：BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。
8：BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。
9：有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。
enum和set
enum：每次只能插入一个值如下面M/F
SET 类型可以从允许值集合中选择任意 1 个或多个元素进行组合
日期和时间类型
| 类型 | 大小 (bytes) | 范围 | 格式 | 用途 |
|---|---|---|---|---|
| DATE | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |
| TIME | 3 | '-838:59:59'/'838:59:59' | HH:MM:SS | 时间值或持续时间 |
| YEAR | 1 | 1901/2155 | YYYY | 年份值 |
| DATETIME | 8 | 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
| TIMESTAMP | 4 | 1970-01-01 00:00:00/2038结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07 | YYYYMMDD HHMMSS | 混合日期和时间值，时间戳 |
时间戳
Unix 时间戳是从1970年1月1日（UTC/GMT的午夜）开始所经过的秒数，不考虑闰秒
UNIX_TIMESTAMP()
SELECT UNIX_TIMESTAMP('2022-05-12 10:36:09') AS date
SELECT UNIX_TIMESTAMP(); -- 1652322969 默认当前时间
时间戳与时间转换
FROM_UNIXTIME(unix_timestamp,format)。
SELECT FROM_UNIXTIME(1652322969, '%Y-%m-%d %h:%i:%s' ) AS date
注意：
1：TIMESTAMP支持的时间范围较小，而DATETIME范围更大。应该使用datetime。
2：一般注册时间，发布时间这些可以使用时间戳存储，方便计算SELECT UNIX_TIMESTAMP();
3：默认插入时间DEFAULT CURRENT_TIMESTAMP
`create_date` datetime DEFAULT CURRENT_TIMESTAMP COMMENT 'excel导入日期时间',
与java类型对应关系
| MySQL数据类型 | Return value ofGetColumnClassName | Returned as Java Class |
|---|---|---|
| BIT(1) (new in MySQL-5.0) | BIT | java.lang.Boolean |
| BIT( > 1) (new in MySQL-5.0) | BIT | byte[] |
| TINYINT | TINYINT | java.lang.Boolean if the configuration property tinyInt1isBit is set to true(the default) and the storage size is 1, orjava.lang.Integer if not. |
| BOOL, BOOLEAN | TINYINT | See TINYINT, above as these are aliases forTINYINT(1), currently. |
| SMALLINT[(M)] [UNSIGNED] | SMALLINT [UNSIGNED] | java.lang.Integer (regardless if UNSIGNED or not) |
| MEDIUMINT[(M)] [UNSIGNED] | MEDIUMINT [UNSIGNED] | java.lang.Integer, if UNSIGNEDjava.lang.Long |
| INT,INTEGER[(M)] [UNSIGNED] | INTEGER [UNSIGNED] | java.lang.Integer, if UNSIGNEDjava.lang.Long |
| BIGINT[(M)] [UNSIGNED] | BIGINT [UNSIGNED] | java.lang.Long, if UNSIGNEDjava.math.BigInteger |
| FLOAT[(M,D)] | FLOAT | java.lang.Float |
| DOUBLE[(M,B)] | DOUBLE | java.lang.Double |
| DECIMAL[(M[,D])] | DECIMAL | java.math.BigDecimal |
| DATE | DATE | java.sql.Date |
| DATETIME | DATETIME | java.sql.Timestamp |
| TIMESTAMP[(M)] | TIMESTAMP | java.sql.Timestamp |
| TIME | TIME | java.sql.Time |
| YEAR[(2\4)] | YEAR | If yearIsDateType configuration property is set to false, then the returned object type isjava.sql.Short. If set to true (the default) then an object of type java.sql.Date (with the date set to January 1st, at midnight). |
| CHAR(M) | CHAR | java.lang.String (unless the character set for the column is BINARY, then byte[]is returned. |
| VARCHAR(M) [BINARY] | VARCHAR | java.lang.String (unless the character set for the column is BINARY, then byte[]is returned. |
| BINARY(M) | BINARY | byte[] |
| VARBINARY(M) | VARBINARY | byte[] |
| TINYBLOB | TINYBLOB | byte[] |
| TINYTEXT | VARCHAR | java.lang.String |
| BLOB | BLOB | byte[] |
| TEXT | VARCHAR | java.lang.String |
| MEDIUMBLOB | MEDIUMBLOB | byte[] |
| MEDIUMTEXT | VARCHAR | java.lang.String |
| LONGBLOB | LONGBLOB | byte[] |
| LONGTEXT | VARCHAR | java.lang.String |
| ENUM(‘value1’,’value2’,…) | CHAR | java.lang.String |
| SET(‘value1’,’value2’,…) | CHAR | java.lang.String |
数据类型选择的一些建议
选小不选大：一般情况下选择可以正确存储数据的最小数据类型，越小的数据类型通常更快，占用磁盘，内存和CPU缓存更小。
简单就好：简单的数据类型的操作通常需要更少的CPU周期，例如：整型比字符操作代价要小得多，因为字符集和校对规则(排序规则)使字符比整型比较更加复杂。
尽量避免NULL：尽量制定列为NOT NULL，除非真的需要NULL类型的值，有NULL的列值会使得索引、索引统计和值比较更加复杂。
浮点类型的建议统一选择decimal
记录时间的建议使用int或者bigint类型，将时间转换为时间戳格式，如将时间转换为秒、毫秒，进行存储，方便走索引
常见的数据类型选择
姓名：char(20)
年龄：tinyint
各种状态：tinyint （不推荐使用枚举，枚举不好扩展选项）
价格：DECIMAL(7, 3)
文章内容: LONGTEXT
BASE64: LONGTEXT
手机号：bigint(11)
自增主键：unsigned bigint(20)
md5: char(32)
uuid:char(36)
ip: 借用inet_aton/inet_ntoa函数，使用 unsigned int
time: int(10)
email:char(32)
create_date：datetime 默认（CURRENT_TIMESTAMP）
小细节
使用tinyint(4)，不使用tinyint(1)的原因是tinyint(1)会被orm框架认为是布尔类型，在生成实体类时。直接被翻译成布尔类型而不是Integer类型
SQL分类
大小写规范
数据库名称不能改
CREATE DATABASE 数据库名;
CREATE DATABASE 数据库名 CHARACTER SET 字符集;
CREATE DATABASE IF NOT EXISTS 数据库名;
SHOW DATABASES; #有一个S，代表多个数据库
SHOW TABLES FROM 数据库名; #显示数据库下的table
SHOW CREATE DATABASE 数据库名;
ALTER DATABASE 数据库名 CHARACTER SET 字符集; #比如：gbk、utf8等
DROP DATABASE IF EXISTS 数据库名;
创建表
DDL创建
CREATE TABLE [IF NOT EXISTS] 表名(
字段1, 数据类型 [约束条件] [默认值],
字段2, 数据类型 [约束条件] [默认值],
字段3, 数据类型 [约束条件] [默认值],
……
[表约束条件]
);
int 创建的时候默认 INT int(11) DEFAULT NULL。在 MySQL 8.x 版本中，不再推荐为 INT 类型指定显示长度，并在未来的版本中可能去掉这样的语法。这里的 11 实际上是 INT 类型指定的显示宽度，默认的显示宽度为 11。也可以在创建数据表的时候指定数据的显示宽度。
从select中创建表
--复制表结构和数据到新表
CREATE TABLE 新表 SELECT * FROM 旧表
CREATE TABLE emp1 AS SELECT * FROM employees; #创建emp1表，并复制employees的数据
--复制表结构
CREATE TABLE emp2 AS SELECT * FROM employees WHERE 1=2; -- 创建的emp2是空表
create table 表名 like 被复制的表名;
创建临时表
##
##创建临时表-使用TEMPORARY关键字
##
CREATE TEMPORARY TABLE fruit
(
id INT NOT NULL
);
--复制表结构
CREATE TEMPORARY TABLE 新表 SELECT * FROM 旧表 WHERE 1=2
--删除临时表
DROP TEMPORARY TABLE tmp_table;
-- 创建临时表并且复制表结构和数据
create temporary table t2
select * from tab_user;
创建表的规范
自增id
CREATE TABLE animals (
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (id)
);
Alter
追加表的列
ALTER TABLE 表名 ADD 【COLUMN】 字段名 字段类型 【FIRST|AFTER 字段名】;
ALTER TABLE tb_3 ADD age NVARCHAR(100);
ALTER TABLE tb_3 ADD testfiled1 NVARCHAR(50) FIRST;
ALTER TABLE tb_3 ADD testfiled NVARCHAR(50) AFTER salary;
修改table
ALTER TABLE oldtablename RENAME TO newtablename;
ALTER TABLE tb_3 MODIFY name NVARCHAR(100);
修改表的列
ALTER TABLE 表名 MODIFY 【COLUMN】 字段名1 字段类型 【DEFAULT 默认值】【FIRST|AFTER 字段名2】;
ALTER TABLE 表名 CHANGE 【column】 列名 新列名 新数据类型;能改列名
ALTER TABLE dept80 MODIFY salary double(9,2) default 1000;
ALTER TABLE dept80 CHANGE department_name dept_name varchar(15);
删除列
ALTER TABLE 表名 DROP 【COLUMN】字段名
ALTER TABLE tb_1 DROP COLUMN testcolumn;
删除表
DROP TABLE [IF EXISTS] 数据表1 [, 数据表2, …, 数据表n];
TRUNCATE TABLE detail_dept; 不能回滚数据。
TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少，但 TRUNCATE 无事务且不触发 TRIGGER，有可能造成事故，故不建议在开发代码中使用此语句。
TRUNCATE TABLE detail_dept;
DML
SELECT
多行子查询
查询与141号或174号员工的manager_id和department_id相同的其他员工的employee_id，
manager_id，department_id
SELECT employee_id, manager_id, department_id
FROM employees
WHERE (manager_id, department_id) IN
(SELECT manager_id, department_id
FROM employees
WHERE employee_id IN (141,174))
AND employee_id NOT IN (141,174);
相关子查询
子查询的执行依赖于外部查询，通常情况下都是因为子查询中的表用到了外部的表，并进行了条件
关联，因此每执行一次外部查询，子查询都要重新计算一次，这样的子查询就称之为 关联子查询
EXISTS 与 NOT EXISTS
SELECT department_id, department_name
FROM departments d
WHERE NOT EXISTS (SELECT 'X'
FROM employees
WHERE department_id = d.department_id);
返回别名
使用as或者空格
SELECT UserName AS name,UserName name2 FROM datatype
分页
LIMIT [位置偏移量,] 行数
分页公式：
SELECT * FROM table LIMIT (PageNo - 1) * PageSize, PageSize;
显示表结构
DESC datatype ;
DESCRIBE datatype ;
连接查询
CROSS JOIN
笛卡尔积也称为 交叉连接 ，英文是 CROSS JOIN 。在 SQL99 中也是使用 CROSS JOIN表示交
叉连接。它的作用就是可以把任意表进行连接，即使这两张表不相关
FULL JOIN
满外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。
SQL99是支持满外连接的。使用FULL JOIN 或 FULL OUTER JOIN来实现。
需要注意的是，MySQL不支持FULL JOIN，但是可以用 LEFT JOIN UNION RIGHT join代替。
右下图
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL
总结
- 空值查询需要使用IS NULL或者IS NOT NULL，其他查询运算符对NULL值无效
- 当排序过程中存在相同的值时，没有其他排序规则时，可能会导致分页结果乱序，可以在后面追加一个主键排序
- select语法顺序：select、from、where、group by、having、order by、limit，顺序不能搞错了，否则报错
INSERT
INSERT INTO table_name
VALUES
(value1 [,value2, …, valuen]),
(value1 [,value2, …, valuen]),
……
(value1 [,value2, …, valuen]);
INSERT INTO table_name(column1 [, column2, …, columnn])
VALUES
(value1 [,value2, …, valuen]),
(value1 [,value2, …, valuen]),
……
(value1 [,value2, …, valuen]);
DELETE
UPDATE
运算符
除法
一个数除以另一个数，除不尽时，结果为一个浮点数，并保留到小数点后4位；
SELECT 10/3; #3.3333
空运算符
（IS NULL或者ISNULL）判断一个值是否为NULL
SELECT NULL IS NULL, ISNULL(NULL), ISNULL('a'), 1 IS NULL;
最大最小运算符
SELECT least(1,2,3) ,greatest(1,2,3) #1 3
REGEXP运算符
SELECT 'ABCD' REGEXP '^A' #1
逻辑运算符
逻辑非NOT
逻辑或OR
逻辑异或
a、b两个值不相同，则异或结果为1。如果a、b两个值相同，异或结果为0
异或XOR,a xor b相当于 a and(not b);
select 1 xor 1 ,0 xor 0,1 xor 0,0 xor 1,null xor 1;
0 0 1 1
位运算符
SELECT 4>>1; # 4/2=2
SELECT 4<<2; # 4 *2^2 = 16
运算符优先级
浙公网安备 33010602011771号