数据类型
整型
类型 | 范围 | 备注 |
bit | 1~64B | bit(2)表示2个二进制位，取值范围为0~3 |
tinyint | 1B | 标记 UNSIGNED 时，取值范围为0~255。当标记 ZEROFILL 且同时标记了 UNSIGNED，当位数不足 M 时，添加前导 0。例如 tinyint(5)，当值为 123时，会添加两个前导0，即00123 |
smallint | 2B | 同上 |
mediumint | 3B | 同上 |
int | 4B | 同上 |
integer | 4B | 与 int 完全相同 |
bigint | 8B | 同上 |
浮点数
类型 | 范围 | 备注 |
float | 正负3.4E38 | float(5,2)表示长度为5，其中2位小数。与Java中float精度相同 |
double | 正负1.79E308 | 同上 |
numeric | 小数点前后有效位数之和不大于65位 | 使用与float、double相同，但存储方式不同，把9位十进制压缩成4个字节来存储。相当于把浮点数当成字符来保存，但进行了压缩，比float、double更安全可靠，不会出现精度缺失的问题 |
decimal | 65位 | 在MySQL中，decimal与numeric完全相同，二者在表示钱时，很有用 |
字符串
类型 | 范围 | 备注 | 注意 |
char | 255B | 固定长度字符串类型。char(4)，当数据长度为2时，会在后边添加2个空格补位 | 长度固定，所以存取效率比 varchar 快，但会占据多余空间，是以空间换时间 |
varchar | 65535B | 可变长度字符串。varchar(4)，当数据长度为2时，不会添加空格补位。占3个字节，其中一个字节用来记录长度，当数据长度大于255时，记录长度就需要2个字节了 | 相比 char 存取速度慢，因其长度可变，所以不会浪费多余的空间，是以时间换空间 |
tinytext | 2^8~1B | 可变长度字符串 | |
text | 2^16~1B | 同上 | 用于存储较大字符串，有一个字符集，并且会根据字符集的校对规则对值进行排序和比较 |
mediumtext | 2^24~1B | 同上 | |
longtext | 2^32~1B | 同上 |
布尔类型
类型 | 范围 | 备注 |
bool | 布尔值，但实际上是用tinyint(1)表示的。当值为0时表示假，当值为非0时表示真。-128也是真 | |
boolean | 与bool完全相同 |
日期类型
类型 | 范围 | 备注 |
date | yyyy-mm-dd | |
datetime | yyyy-mm-dd hh:mm:ss | |
timestamp | 时间戳，用在记录插入与更新自动记录时间 | |
year | year(2)，表示两位的年 | |
time | 只表示时间 |
二进制
类型 | 范围 | 备注 | 注意 |
binary | 255B | 固定长度二进制类型。binary(10)，当数据为5B时，会添加5B来补位，即长度为10B | |
varbinary | 255B | 可变长度二进制类型。varbinary(10)，当数据为5B时，不会补位，长度为6B，其中1B用来记录长度 | |
tinyblob | 2^8~1B(256B) | 可变长度二进制类型 | |
blob | 2^16~1B(64K) | 同上 | 存储二进制数据，没有字符集 |
mediumblob | 2^24~1B(16M) | 同上 |
SQL 语句格式
用户
功能 | 格式 | 备注 |
创建 | CREATE USER { 用户名@IP地址 | 用户名@‘%’ } IDENTIFIED BY 密码; | 用户名@IP地址：用户只能在指定的IP地址上登录； 用户名@‘%’：用户可以在任意IP地址上登录。 |
删除 | DROP USER ‘用户名@IP地址’; | |
修改用户 | RENAME USER ‘用户名@IP地址’ TO ‘新用户名@IP地址’; | |
修改密码 | SET PASSWORD [‘用户名@IP地址’] = PASSWORD(新密码); |
权限
功能 | 格式 | 备注 |
查看用户权限 | SHOW GRANTS FOR ‘用户名@IP地址’; | |
授权 | GRANT ALL PRIVILEGES ON 数据库.* TO 用户名@IP地址; GRANT { CREATE | ALTER | DROP | INSERT | UPDATE | DELETE | SELECT } ON 数据库名.* TO 用户名@IP地址; | |
取消授权 | REVOKE 权限 ON 库名.表名 FORM 用户名@IP地址; |
数据库
功能 | 格式 | 备注 |
连接数据库 | mysql -uroot -p | |
断开连接 | quit 或 exit | |
创建 | CREATE DATABASE [IF NOT EXISTS] 数据库名 [CHARSET SET utf8]; | 数据库中数据编码采用的是安装时指定的默认编码 utf8 |
删除 | DROP DATABASE [IF EXISTS] 数据库名; | |
查看全部 | SHOW DATABASES; | |
切换数据库 | USE 数据库名; | |
查看定义信息 | SHOW CREATE DATABASE 库名; | |
查看所有的编码 | SHOW VARIABLES LIKE ‘char%’; | |
修改编码 | ALTER DATABASE 数据库名 CHARACTER SET utf8; | |
查看正在使用的数据库 | SELECT DATABASE(); |
表
功能 | 格式 | 备注 |
创建 | CREATE TABLE [IF NOT EXISTS] 表名( `列名` 数据类型 [列属性] [列索引] [列注释] , …, PRIMARY KEY (`列名`), KEY `外键名` (`作为外键的列`), CONSTRAINT `外键名` FOREIGN KEY (`作为外键的列`) REFERENCES `目标表` (`目标列`) )ENGINE = INNODB DEFAULT CHARSET = utf8; | |
删除 | DROP TABLE [IF EXISTS] 表名; | |
修改表名 | ALTER TABLE 旧表名 RENAME { AS | TO } 新表名; | |
查看所有表 | SHOW TABLES; | |
查看创表代码 | SHOW CREATE TABLE 表名; | |
查看表结构(列名及属性等) | DESC 表名; | |
修改表的字符集 | ALTER TABLE 表名 CHARACTER SET 字符集; |
表字段
功能 | 格式 | 备注 |
添加 | ALTER TABLE 表名 ADD 列名 数据类型 [列属性]; | |
删除 | ALTER TABLE 表名 DROP 列名; | |
修改列的类型及约束 | ALTER TABLE 表名 MODIFY 列名 数据类型 [列属性]; | MODIFY 关键字不能用来列名的重命名，只能修改列的类型和约束 |
修改列名 | ALTER TABLE 表名 CHANGE 旧列名 新列名 数据类型 [列属性]; | CHANGE 关键字用来列名的重命名，不能修改列的类型和约束； |
修改默认值 | ALTER TABLE 表名 ALTER 列名 SET DEFAULT 默认值; | |
删除默认值 | ALTER TABLE 表名 ALTER 列名 DROP DEFAULT; |
表主键
功能 | 格式 | 备注 |
添加 | ALTER TABLE 表名 ADD PRIMARY KEY(列名); | |
删除 | ALTER TABLE 表名 DROP PRIMARY KEY; |
表外键
功能 | 格式 | 备注 |
添加 | ALTER TABLE 表名 ADD CONSTRAINT `外键名` FOREIGN KEY (`作为外键的列`) REFERENCES `目标表` (`目标列`); | |
删除 | ALTER TABLE 表名 DROP FOREIGN KEY 外键名称; |
表数据
功能 | 格式 | 备注 | 注意 |
添加 | INSERT INTO `表名` [(列名,…)] values (值,…)[,(值,…)]; | ||
删除 | DELETE FROM `表名` [WHERE 条件]; | DELETE 删除数据，如果是 Innodb，自增会从0开始(存在内存中，断电即失)；如果是Myisam，则继续从上一个子增量开始(存在文件中，不会丢失)； | 一条条删除，不清空 auto_increment 记录数 |
删除 | TRUNCATE TABLE `表名`; | TRUNCATE 删除数据，不会改变表结构和索引约束，自增清零 | 将表删除，重新建表，auto_increment 将置为0 |
更新 | UPDATE `表名` SET `列名` = 值[,…] [WHERE 条件]; | ||
查看 | SELECT [ALL | DISTINCT] {* | table.* | [table.列名 [as 别名][,…]]} FROM `表名` [as 别名] [left | right | inner join `表名`] [WHERE 条件] [GROUP BY 分组列[,…]] [HAVING 过滤条件] [ORDER BY 排序列[,…]] [LIMIT {[offset,]页码 | 条目数 offset}] |
事务
功能 | 格式 | 备注 |
开启/关闭 | SET autocommit = {0 | 1}; | 0 关闭自动提交；1 开启自动提交(默认) |
开始 | START TRANSACTION; | |
提交 | COMMIT; | |
回滚 | ROLLBACK; |
事务保存点
功能 | 格式 | 备注 |
新增 | SAVEPOINT 保存点名; | |
回滚到某个保存点 | ROLLBACK TO SAVEPOINT 保存名; | |
删除 | RELEASE SAVEPOINT 保存点; |
索引
功能 | 格式 | 备注 |
新增 | CREATE {INDEX | UNIQUE INDEX | FULLTEXT INDEX} 索引名 ON `表名` (`列名`); | 不能用来创建主键索引，必须用 ALTER TABLE 来代替创建 |
新增 | ALTER TABLE `表名` ADD {PRIMARY KEY | INDEX | UNIQUE | FULLTEXT} 索引名 (`列名`); | |
删除 | DROP INDEX 索引名 ON `表名`; | |
删除 | ALTER TABLE `表名` DROP INDEX 索引名; |
运算符
比较运算符
运算符 | 描述 | 注意 |
> < <= >= = <> | 大于、小于、小于等于、大于等于、等于、不等于 | |
BETWEEN … AND … | 在某一区间中 | 含头含尾 |
IN | 在 in 列表中的值 | |
LIKE 通配符 | 模糊查询 | % 用来匹配多个字符 _ 用来匹配一个字符 |
IS NULL | 判断为空 | |
IS NOT NULL | 判断不为空 |
逻辑运算符
运算符 | 描述 |
and | 多个条件同时成立 |
or | 多个条件任意一个成立 |
not | 取反 |
视图（view）
- 视图是一个虚拟表，其内容有查询定义。其与真实表一致，包含列和行数据；
- 它并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成；
- 定义视图的筛选可以来自当前或其他数据库的表或视图。
创建视图
-- 格式：CREATE VIEW 视图名 AS SQL语句
CREATE VIEW getAreaDayInfo AS
SELECT * FROM area_day_info WHERE pollutantCode IN('6','7');删除视图
-- 格式：DROP VIEW 视图名称
DROP VIEW getAreaDayInfo;修改视图
-- 格式：ALTER VIEW 视图名称 AS SQL 语句
ALTER VIEW getAreaDayInfo AS
SELECT * FROM area_day_info WHERE pollutantCode IN('6','7','8');使用试图
-- 格式：SELECT * FROM 视图名称
SELECT * FROM getAreaDayInfo函数（function）
在MySQL中有很多内置函数，比如SUM()、MAX()等，函数可以传参数，也可以接受返回值，但是函数没办法得到执行语句得到的结果。
创建函数
DELIMITER \\
CREATE FUNCTION functionName(
i1 int,
i2 int)
RETURNS int
READS SQL DATA
## 由于开启了二进制文件，就必须指定函数状态
## 函数状态有三种：DETERMINISTIC(不确定的)、NO SQL(没有SQL语句,不会修改数据)、READS SQL DATA(只读,不修改数据)
BEGIN
DECLARE num int;
SET num = i1 + i2;
RETURN(num);
END \\
DELIMITER ;删除函数
-- 格式：DROP FUNCTION functionName;
DROP FUNCTION functionName;执行函数
-- 获取返回值
DECLARE @i VARCHAR(32);
SELECT UPPER('alex') into @i;
SELECT @i;
-- 在查询中使用
SELECT f1(1,2);触发器（trigger）
当你在执行这条语句之前或者之后触发一次增删改查，触发器用于定制用户对表的行进行【增/删/改】前后的操作。
创建触发器
-- 格式：CREATE TRIGGER 触发器名 <before|after> <insert|update|delete> ON <表名> FOR EACH ROW BEGIN ... END;
-- 实例：插入后操作
DELIMITER //
CREATE TRIGGER triggerName AFTER INSERT ON user FOR EACH ROW
BEGIN
IF NEW.NAME == 'xkf' THEN
INSERT INTO user_info(NAME) VALUES('xxx')
END
END //
DELIMITER ;删除触发器
-- 格式：DROP TRIGGER triggerName;
DROP TRIGGER triggerName;使用触发器
-- 格式：增删改的操作可以用来触发触发器
INSERT INTO user(name) VALUES('XXX');存储过程（procedure）
创建存储过程
DELIMITER // -- 自定义sql语句结尾符号
CREATE PROCEDURE procedureName()
BEGIN
SELECT * FROM user;
END //
DELIMITER ; -- 自定义sql语句结尾符号带参的存储过程
- in 仅用于入参
- out 仅用于返回值
- inout 既可以用于入参又可以当作返回值
DELIMITER \\
CREATE PROCEDURE procedureName(
in i1 int, -- 入参
inout i3 int, -- 即入参又能用于出参
out r1 int -- 出参
)
BEGIN
DECLARE temp1 int; -- 声明变量
SET temp1 = 1;
SET i3 = temp1 + 100;
SET r1 = temp1;
END \\
DELIMITER ;
-- 执行存储过程
DECLARE @t1 INT DEFAULT 3; -- 设置变量默认值为3
DECLARE @t2 INT;
CALL procedureName(1,@t1,@t2);
SELECT @t1,@t2;执行存储过程
-- 格式：call 存储过程名();
CALL procedureName();删除存储过程
-- 格式：DROP PROCEDURE 存储过程名;
DROP PROCEDURE procedureName;
