68 lines
1.6 KiB
Markdown
68 lines
1.6 KiB
Markdown
|
# mysql 导出
|
|||
|
|
mysqldump
|
|||
|
|
https://blog.csdn.net/ichen820/article/details/124022855
|
|||
|
|
|
|||
|
|
|
|||
|
|
# 连接
|
|||
|
|
## 连接mysql3306端口命令
|
|||
|
|
mysql -h58.64.217.120 -ushop -p123456
|
|||
|
|
|
|||
|
|
## 连接非3306端口(指定其他端口) 的命令
|
|||
|
|
mysql -h58.64.217.120 -P3308-ushop -p123456
|
|||
|
|
|
|||
|
|
|
|||
|
|
# 分组
|
|||
|
|
## 分组合并查询字段
|
|||
|
|
```sql
|
|||
|
|
SELECT
|
|||
|
|
GROUP_CONCAT(a.id) as ids,
|
|||
|
|
GROUP_CONCAT(a.name) as names,
|
|||
|
|
CASE
|
|||
|
|
WHEN a.age<18 THEN '少年'
|
|||
|
|
WHEN a.age>=18 AND a.age< 40 THEN '青年'
|
|||
|
|
WHEN a.age>=40 AND a.age< 60 THEN '中年'
|
|||
|
|
END as age,
|
|||
|
|
count(a.id) as count
|
|||
|
|
FROM
|
|||
|
|
(SELECT id, name, birthday, TIMESTAMPDIFF(YEAR,student.birthday,CURDATE()) as age FROM `student`) as a
|
|||
|
|
GROUP BY
|
|||
|
|
CASE
|
|||
|
|
WHEN a.age<18 THEN '少年'
|
|||
|
|
WHEN a.age>=18 AND a.age< 40 THEN '青年'
|
|||
|
|
WHEN a.age>=40 AND a.age< 60 THEN '中年'
|
|||
|
|
END
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
![[Pasted image 20230110121247.png]]
|
|||
|
|
|
|||
|
|
![[SQL原理.html]]
|
|||
|
|
|
|||
|
|
##### 修改表字符集
|
|||
|
|
alter table t2 convert to character set utf8mb4;
|
|||
|
|
|
|||
|
|
|
|||
|
|
##### 查看所有表大小
|
|||
|
|
```sql
|
|||
|
|
select
|
|||
|
|
table_schema as '数据库',
|
|||
|
|
table_name as '表名',
|
|||
|
|
table_rows as '记录数',
|
|||
|
|
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
|
|||
|
|
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
|
|||
|
|
from information_schema.tables
|
|||
|
|
order by data_length desc, index_length desc;
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
##### 查看所有库大小
|
|||
|
|
```sql
|
|||
|
|
select
|
|||
|
|
table_schema as '数据库',
|
|||
|
|
sum(table_rows) as '记录数',
|
|||
|
|
sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
|
|||
|
|
sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
|
|||
|
|
from information_schema.tables
|
|||
|
|
group by table_schema
|
|||
|
|
order by sum(data_length) desc, sum(index_length) desc;
|
|||
|
|
```
|
|||
|
|
|