MySQL 常用命令行

命令行

链接 MySQL 监视器

mysql -u 账号 -p 密码

库相关命令

// 创建数据库
CREATE DATABASE 数据库名
// 删除数据库
DROP DATABASE 数据库名
// 查看所有数据库
show DATABASES
// 选中某个数据库
USE 数据库名

表相关命令

// 创建表
CREATE TABLE 表名 (字段名1 数据类型 [其它可选项],字段名2 数据类型 [其他可选项],...)
// 删除表
DROP TABLE 表名
// 显示表的数据结构
DESC 表名
// 查询数据库中所有表
SHOW TABLES;

字段数据类型

• TINYINT：一个非常小的整数，可以带符号。如果是有符号，它允许的范围是从-128到127。如果是无符号，允许的范围是从0到255，占1个字节
• INT：正常大小的整数，可以带符号。如果是有符号的，它允许的范围是从-2147483648到2147483647。如果是无符号，允许的范围是从0到4294967295，占4个字节
• BIGINT：一个大的整数，可以带符号。如果有符号，允许范围为-9223372036854775808到9223372036854775807。如果无符号，允许的范围是从0到18446744073709551615. 占8个字节
• FLOAT(M,D)：只能使用带符号的浮点数。可以定义显示长度(M)和小数位数(D)，默认为10,2。其中2是小数的位数，10是数字(包括小数)的位数。占4个字节
• DOUBLE(M,D)：和 FLOAT 相似，默认 16,4，占8个字节
• DATE：以YYYY-MM-DD格式的日期
• DATETIME：以YYYY-MM-DD HH:MM:SS格式存储日期
• VARCHAR(M)：可变长度的字符串是以长度为1到255之间字符数
• BLOB(Text)：字段的最大长度是65535个字符

字段可配置项

// 非空
NOT NULL
// 自增
AUTO_INCREMENT
// 注释
COMMENT '具体注释'

外键

CONSTRAINT `外键名称` FOREIGN KEY (`字段名称`) REFERENCES `关联表` (`关联字段`) ON DELETE NO ACTION ON UPDATE NO ACTION

使用外键必须具备以下条件：

• 使用 Innodb 引擎
• 关联字段必须建立索引
• 相关联的两个字段数据类型必须相似，可以互相转换

外键具体行为（不论任何行为，操作从表对主表无影响）：

• NO ACTION：默认行为、从表数据不存在时，主表才能删除（修改）
• CASCADE：级联，删除（修改）主表数据，从表数据自动更新
• SET NULL：置为空、删除（修改）主表数据，从表数据置为空

表数据相关命令

// 插入
INSERT INTO 表名(列名1,列名2,...)VALUES(数据1,数据2,...)
// 修改
UPDATE 表名 SET 列名1 = 数据1,列名2=数据2,...WHERE 条件表达式
// 删除
DELETE FROM 表名 WHERE 条件表达式
// 查询
SELETE 列名1,列名2,... FROM 表名 WHERE 条件表达式

条件表达式

// 运算符类
where id > 10
// NULL 操作符类
where 字段名 is NULL 
where 字段名 NOT NULL
// LIKE 运算符，_只能表示一个字符，%可以表示任意个字符
where 字段名 like '_X'(%X)
// BETWEEN 操作符
where id between 1 and 15
// IN 操作符
where id in (1,2,3)
// 组合 AND OR
where id = 1 or id = 2 or id = 3 

结果特殊处理

// 对查询结果排序，默认从小到大排序，可以加 DESC 从大往小排
order by 字段名 
// 获取指定条数，下面0代表从第一个数据开始算，3代表总共要查询的条数
limit 0,3
// 对查询结果分组，根据字段名具体值分组
group by 字段名
// 分组加判断条件，和 where 类似，区别在于 where 在分组前进行数据过滤，having 在分组后进行数据过滤
group by 字段名 having id != 0
// 查询结果链接 
select Conncat(字段名,字段名,字段名,字段名) from xxx
// 对查询结果重命名
selete Conncat(字段名,字段名,字段名,字段名) as '查询字段名' from xxx
// 对查询结果进行运行
selete id * 10 as new_id from xxx

函数

函数	描述
Concat(str1,str2,…)	链接字符串
Lower(str)	str转为小写
Upper(str)	str转为大写
Trim(str)	去除两边空格
SubString(str,pos,len)	获取字符串从 pos 开始，长度 len 的字符串
Abs(num)	返回绝对值
Sqrt(num)	返回平凡根
Floor(num)	返回整数值
Ceiling(num)	返回大于 num 的最小整数
now()	返回现在时间
date_format(d1,format)	格式化日子
AVG(列名)	平均值
COUNT(列名)	总量值
MAX(类名)	最大值
MIN(类名)	最小值
SUM(类名)	平均值

级联查询

// 内连接
SELECT 列名1 ... FROM 表1 
INNER JOIN 表2
ON 表1.外键 = 表2.外键
[WHERE / ORDER BY 语句等]

// 左外连接
SELECT 列名1 ... FROM 表1 
LEFT JOIN 表2
ON 表1.外键 = 表2.外键
[WHERE / ORDER BY 语句等]

// 右外连接
SELECT 列名1 ... FROM 表1 
RIGHT JOIN 表2
ON 表1.外键 = 表2.外键
[WHERE / ORDER BY 语句等]

这里内连接没有先后顺序，需要两张表都有值。左、右连接分别以指向的那张表作为主表，不在乎关联表是否真的有值，如果没有值就显示 NULL

子查询

SELECT 列名1... FROM 表名
WHERE 列名 比较运算符 (SELECT 命令)

组合查询

// 去重
select x from A where xxx  UNION select xx from A where xxx 
// 不去重
select x from A where xxx  UNION ALL select xx from A where xxx 

使用组合查询需要具备以下条件：

1. UNION 必须由两条或者两条以上的select语句组成
2. UNION 中的每个查询必须包含相同的列、表达式或者聚合函数，但出现顺序不必一样
3. 列数据类型必须兼容，但不必完全相同

表维护和改造

// 修改列的数据格式
ALTER TABLE 表名 MODIFY 列名 数据类型
// 新增列
ALTER TABLE 表名 ADD 列名 数据类型
// 把列添加到表的首字段
ALTER TABLE 表名 ADD 列名 数据类型 FIRST
// 把列添加到表的某个列后面
ALTER TABLE 表名 ADD 列名 数据类型 AFTER 列名
// 修改列的位置
ALTER TABLE 表名 MODIFY 列名 数据类型 AFTER 列名
// 修改列名和类型
ALTER TABLE 表名 CHANGE 修改前列名 修改后列名 修改后类型
// 删除某一列
ALTER TABLE 表名 DROP 列名
// 复制一张新表（包含结构和数据）
CREATE TABLE 新表名 SELECT * FROM 旧表名
// 只复制一张表结构
CREATE TABLE 新表名 LIKE 旧表名
// 复制一张表的数据到另一个同类型表
INSERT INTO 表名 SELECT * FROM 带数据的表