mysql相关基础知识

windows进入mysql的cmd 命令

mysql -u root -p12345

mysql有两个路径：安装路径和数据存储路径

（1）查看mysql的安装路径命令(要进入mysql中后执行)

show variables like '%basedir%';

（2）查看mysql数据存储路径

show variables like 'datadir';

查询某个表的列数

SELECT COUNT(*) 
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA='数据库名' AND table_name='表名'; 

eg： 查询数据库wdc-erpt中的表名以fitment开头的表字段列总和

SELECT COUNT(*) 
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA='wdc-erpt' AND table_name LIKE 'fitment%'; 

eg： 查询数据库wdc-erpt中所有表的总数

SELECT COUNT(*) TABLES, table_schema FROM information_schema.TABLES 

WHERE table_schema = 'wdc-erpt' GROUP BY table_schema;

查看数据库的事务隔离级别

select @@global.tx_isolation

 

修改数据库字符集：

ALTER DATABASE db_name DEFAULT CHARACTER SET character_name [COLLATE ...];

把表默认的字符集和所有字符列（CHAR,VARCHAR,TEXT）改为新的字符集：

1. ALTER TABLE tbl_name CONVERT TO CHARACTER SET character_name [COLLATE ...]

2. 如：ALTER TABLE logtest CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

只是修改表的默认字符集：

1. ALTER TABLE tbl_name DEFAULT CHARACTER SET character_name [COLLATE...];

2. 如：ALTER TABLE logtest DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

 

修改字段的字符集：

1. ALTER TABLE tbl_name CHANGE c_name c_name CHARACTER SET character_name [COLLATE ...];

2. 如：ALTER TABLE logtest CHANGE title title VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci;

 

查看数据库编码：

SHOW VARIABLES LIKE 'character_set_database';

 

查看表编码：

SHOW CREATE TABLE tbl_name;

 

查看字段编码：

SHOW FULL COLUMNS FROM tbl_name;