查看MySQL数据存放位置
SHOW GLOBAL VARIABLES LIKE '%DATADIR%';
查看每个数据库的大小,以G为单位返回,此命令与数据实际文件大小有差异
SELECT table_schema,SUM(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024/1024/1024 AS total_mb FROM information_schema.TABLES group by table_schema;
查看指定数据库下某张表的所有列名称
SELECT column_name FROM information_schema.columns WHERE table_schema = 库名 AND table_name = 表名'
对某一列数据去重查询1 DISTINCT 效率比GROUP BY高
SELECT DISTINCT col_name FROM table_1;
对某一列数据去重查询2 GROUP BY 效率比DISTINCT低,但功能强大,一般配合聚合函数使用
SELECT col_name FROM table_1 GROUP BY col_name;
关联查询-1
SELECT t1.id, t1.name FROM table_1 t1, table_2 t2 WHERE t1.id = t2.id
关联查询-2.1(内连接关联查询) JOIN 表名 ON 条件 查询比 关联查询-1 的效率高
SELECT t1.id, t1.name FROM table_1 t1 INNER JOIN table_2 t2 ON t1.id = t2.id
关联查询-2.2(左连接关联查询)
SELECT t1.id, t1.name FROM table_1 t1 LEFT JOIN table_2 t2 ON t1.id = t2.id
关联查询-2.3(右连接关联查询)
SELECT t1.id, t1.name FROM table_1 t1 RIGHT JOIN table_2 t2 ON t1.id = t2.id
关联更新-1
UPDATE table_1 t1, table_2 t2 SET t1.name = t2.name WHERE t1.id = t2.id
关联更新-2 同理,效率比关联更新-1 高
UPDATE table_1 t1 LEFT JOIN table_2 t2 ON t1.id = t2.id SET t1.name = t2.name
清空表数据-1 可根据条件删除,使用DELETE据说误删还可以找回
DELETE FROM '表名' WHERE '条件'
清空表数据-2 清空表的速度比delete快,TRANCE是先删表再重建
TRANCE TABLE '表名'
–20210526
INSERT INTO TB_NAME (COLUMN_NAME) VALUES ("value")
新的SQL标准里,双引号表示列名!~
所以该语句在一些版本中会报 unknown column 'value' in field list。
把"value"识别为了列名称,所以找不到该列,把双引改为单引就可以了~~~