MySQL 数据库基础

数据类型

整型

类型	范围	备注
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;