MYSQL的数据类型和常用语句

数据类型

• char
• varchar
• tinytext
• text
• int
• tinyint
• bigint
• date
• time
• year
• datatime
• timestamp
• json

约束类型

• primary key 主键约束
• not null
• check
• default
• unique 唯一约束
• foreign key 外键约束

索引类型

• primary index 主键索引
• unique index 唯一索引
• index 普通索引
• fulltext index 全文索引

其他

• auto_increment 自动增长
• engine 引擎类型
• default charset 默认字符集
• comment 备注信息
• constraint 约束,后面可以跟约束名和约束类型

数据库维护相关语句

// 查询某个数据库是否存在
SELECT * FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = 'databasename';
// 查看全局变量信息
SHOW GLOBAL VARIABLES;
// 查看当前会话使用的字符集
SHOW VARIABLES LIKE 'character%';
// 显示数据库的时区
SHOW VARIABLES LIKE 'time_zone';
// 展示保留字
SHOW VARIABLES LIKE 'ft_stopword_file';
SHOW DATABASES;
SHOW CREATE DATABASE database_name;
// 查看mysql支持的数据引擎
SHOW ENGINES;
SHOW TABLES;
// 显示表结构
SHOW CREATE TABLE table_name;
// 显示表详细信息
DESCRIBE table_name | DESC table_name;
USE database_name;
// 查看会话变量信息
SHOW session variables;
// 锁定所有的表
FLUSH TABLES WITH READ LOCK;
// 解锁
UNLOCK TABLES;
// 展示索引
SHOW INDEX FROM table_name ;
SHOW GRANTS FOR cmfac@%;
DROP DATABASE database_name;

数据表结构管理相关语句

ALTER TABLE `table_name` CHANGE created_at created_at timestamp NULL;
CREATE TABLE `table_name` ( `id` BIGINT NOT NULL AUTO_INCREMENT, `field_name` VARCHAR(255) DEFAULT NULL,  PRIMARY KEY(`id`));
// 删除表字段
ALTER TABLE table_name DROP field_name;
// 删除主键
ALTER TABLE table_name DROP INDEX index_name;
// 新增字段
ALTER TABLE table_name ADD field_name CHAR(20) NOT NULL AFTER field_name_last;
// 修改字段类型
ALTER TABLE table_name CHANGE old_field_name new_field_name VARCHAR(20);
ALTER TABLE table_name ADD CONSTRAINT field_name UNIQUE (field_name);
// 修改字段类型
ALTER TABLE table_name MODIFY VARCHAR(20);
ALTER TABLE table_name ADD constraint filed_name_unique UNIQUE (filed_name);
ALTER TABLE table_name ENGINE=InnoDB;
ALTER TABLE table_name DEFAULT charset=utf-8;
ALTER TABLE table_name auto_increment=8;
ALTER TABLE table_name pack_keys= 1;
RENAME TABLE old_table_name TO new_table_name;
// 添加索引
CREATE INDEX field_name_index ON course (field_name(20));
// 添加索引
ALTER TABLE table_name ADD index field_name_index (field_name(20))
// 删除索引
DROP INDEX field_name_index ON table_name;
// 快速创建一个和原表结构相同的表
CREATE TABLE new_table_name LIKE old_table_name;

表数据操作相关语句

INSERT INTO table_name (filed_name) VALUES (value) ;
// filed_name  和 filed_name_2 的列数一定要相同.
INSERT INTO table_name (filed_name) SELECT filed_name_2 FROM table_name_2 ;
// replace 和 insert 类似 ,区别在于新纪录如果主键或者唯一字段和一条旧相同, 那么旧纪录会被删除, 然后在执行新纪录的插入操作.
REPLACE INTO table_name (filed_name) VALUES (value) ;
UPDATE table_name SET field_name = field_value WHERE 1 = 1;
DELETE from table_name WHERE 1=1;
// 清空一个表, 父表总是不能被清空, 清空表会重置自增字段
TRUNCATE TABLE table_name;

表数据查询相关语句

// 不含重复结果的 sql 语句
SELECT DISTINCT filed_name FROM table_name WHERE filed_name = value GROUP BY filed_name_2 HAVING filed_name_2 > 2 ORDER BY filed_name_2 > 1 LIMIT start length;
// 简单的 left join
SELECT a.filed_name,b.filed_name FROM table_name_1 AS a LEFT JOIN table_name_2 AS b ON a.id = b.id WHERE a.id > 1;
// null 不能用等于来进行判断.
SELECT a.filed_name,b.filed_name FROM table_name_1 AS a INNER JOIN table_name_2 AS b ON a.id = b.id WHERE a.name is null;
// with rollup 会在最后加一个合计
SELECT sum(number) FROM table_name GROUP BY filed_name HAVING filed_name > 0 with rollup;
// group_concat 会把指定的列的字段用逗号连接起来.
SELECT group_concat(filed_name) FROM table_name GROUP BY filed_name_2;
// field_name_1 和 field_name_2 的字段类型和字段个数必须相同
SELECT field_name_1 FROM table_name_1 UNION SELECT field_name_2 FROM table_name_2 ;
// 联合查询 union all 包含重复的值 , union 不包含.
SELECT field_name_1 FROM table_name_1 UNION ALL SELECT field_name_2 FROM table_name_2;
// 子查询 括号中的查询和括号外没有关系, 称为不相关子查询. 不查询子查询, 括号中的代码只执行一次.
SELECT field_name_1 FROM table_name_1 WHERE field_name_1 IN (SELECT field_name_2 FROM table_name_2);
//  如果括号中存在结果. 就执行括号外的SQL. 括号内的查询使用了括号外的字段, 相关子查询.
SELECT field_name_1 FROM table_name_1 WHERE field_name_1 EXISTS (SELECT field_name_2 FROM table_name_2 WHERE field_name_1 = field_name_2);
// 相当于大于 field_name_2 的最小值
SELECT field_name_2 FROM table_name_1 WHERE field_name_1 > ANY (SELECT field_name_2 FROM table_name_2);
// 相当于大于 field_name_2 的最大值
SELECT field_name_2 FROM table_name_2 WHERE field_name_2 > ALL (SELECT field_name_2 FROM table_name_2);
// 全文检索 查询结果按关联度进行排序, 如果查询的结果中,权重超过 50% 的值会被忽略, 权重越低的值越容易被查出来
SELECT field_name_1 FROM table_name WHERE MATCH (filed_name_1 ,filed_name_2) against ('whosyourdaddy');
// 在事务中，该查询会阻塞别的事务中的该查询。举个例子，比如这个事务开始时，跑了一下这个查询，这条语句就会被锁起来， 这个事务结束之前， 这条语句无法在被执行。
SELECT field_name FROM table_name WHERE field_name IS NULL FOR update;

常用函数

• now() 当前时间
• sum() 求和
• max() 最大值
• min() 最小值
• avg() 平均数
• group_concat() 可以组合字符串
• to_days() 到今天的时间

本作品采用《CC 协议》，转载必须注明作者和本文链接