Mysql(linux版)SQL基础(第三节)

关于group by的sql_mode
only_full_group_by(看下面那张图报的错)
说明：

在5.7版本中MySQL sql_mode参数中自带，5.6和8.0都没有
在带有group by 字句的select中，select 后的条件列（非主键列），
要么是group by后的列，要么需要在函数中包裹

group_concat
列转行聚合函数
mysql> select user,group_concat(host) from mysql.user group by user;
Mysql(linux版)SQL基础(第三节)
如上图所示，不允许select后面的列名出现group by后面没有的列名，但是用聚合函数包一下就可以出现！

concat
做列值拼接
mysql> select concat(user,"@",host) from mysql.user;

关于多表连接语法规则
1.首先找涉及到的所有表
2.找到表和表之间的关联列
3.关联条件写在on后面
A join B on 关联列
4. 所有需要查询的信息放在select后
5. 其他的过滤条件where group by having order by limit 网最后放
6.注意：对多表连接中，驱动表选择数据行少的表。后续所有表的关联列尽量是主键或唯一键（表设计），至少建立一个索引。

别名
表别名
列别名
count
oldguo linux a,b,c,d
oldguo python x，y,z
oldboy linux 10
hsw python 11

distinct
mysql> select count(distinct countrycode) from city;

select 执行顺序
select user ,count(name) from 表 where 列 group by user having 列 order by 列 ;

**. 扩展类内容-元数据获取 *****

元数据介绍及获取介绍
元数据是存储在"基表"中。
通过专用的DDL语句，DCL语句进行修改
通过专用视图和命令进行元数据的查询
information_schema中保存了大量元数据查询的试图
show 命令是封装好功能，提供元数据查询基础功能

information_schema的基本应用 ***
tables 视图的应用
mysql> use information_schema;
mysql> desc tables;

TABLE_SCHEMA 表所在的库名
TABLE_NAME 表名
ENGINE 存储引擎
TABLE_ROWS 数据行
AVG_ROW_LENGTH 平均行长度
INDEX_LENGTH 索引长度

例子：
USE information_schema;
DESC TABLES;
– 1. 显示所有的库和表的信息
SELECT table_schema,table_name FROM information_schema.tables;

– 2. 以以下模式显示所有的库和表的信息
– world city,country,countrylanguage

SELECT table_schema,GROUP_CONCAT(table_name)
FROM information_schema.tables
GROUP BY table_schema;

– 3. 查询所有innodb引擎的表
SELECT table_schema,table_name ,ENGINE
FROM information_schema.tables
WHERE ENGINE=‘innodb’;

– 4. 统计world下的city表占用空间大小
– 表的数据量=平均行长度行数+索引长度
– AVG_ROW_LENGTHTABLE_ROWS+INDEX_LENGTH
SELECT table_name,(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024
FROM information_schema.TABLES
WHERE table_schema=‘world’ AND table_name=‘city’;

– 5. 统计world库数据量总大小
SELECT table_schema,SUM((AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH))/1024
FROM information_schema.TABLES
WHERE table_schema=‘world’;

– 6. 统计每个库的数据量大小，并按数据量从大到小排序
SELECT table_schema,SUM((AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH))/1024 AS total_KB
FROM information_schema.TABLES
GROUP BY table_schema
ORDER BY total_KB DESC ;

2.2 配合concat()函数拼接语句或命令
例子：
– 1. 模仿以下语句,进行数据库的分库分表备份。
mysqldump -uroot -p123 world city >/bak/world_city.sql

SELECT
CONCAT(“mysqldump -uroot -p123 “,table_schema,” “,table_name
,” >/bak/”,table_schema,"_",table_name,".sql")
FROM information_schema.tables;

– 2. 模仿以下语句,进行批量生成对world库下所有表进行操作
ALTER TABLE world.city DISCARD TABLESPACE;

SELECT
CONCAT(“ALTER TABLE “,table_schema,”.”,table_name," DISCARD TABLESPACE;")
FROM information_schema.tables
WHERE table_schema=‘world’;

2.2 show介绍*****
show databases; 查看数据库名
show tables; 查看表名
show create database xx; 查看建库语句
show create table xx; 查看建表语句
show processlist; 查看所有用户连接情况
show charset; 查看支持的字符集
show collation; 查看所有支持的校对规则
show grants for xx; 查看用户的权限信息
show variables like ‘%xx%’ 查看参数信息
show engines; 查看所有支持的存储引擎类型
show index from xxx 查看表的索引信息
show engine innodb status\G 查看innoDB引擎详细状态信息
show binary logs 查看二进制日志的列表信息
show binlog events in ‘’ 查看二进制日志的事件信息
show master status ; 查看mysql当前使用二进制日志信息
show slave status\G 查看从库状态信息
show relaylog events in ‘’ 查看中继日志的事件信息
show status like ‘’ 查看数据库整体状态信息

Mysql(linux版)SQL基础(第三节)

相关推荐