改SQL编码格式 报错 1366 - Incorrect string value: '\xE8\xAF\xAD' for column 'subject' at row 1

:3306/aaa?useUnicode=true&characterEncoding=UTF-8

 

改数据库编码为 utf8：

 Alter database aaa character set utf8 collate utf8_general_ci;

改表编码为 utf8：

 Alter table t_score convert to character set utf8;

查看 数据库 编码格式 :

show create database aaa;

查看 表 编码格式 :

SHOW CREATE TABLE t_score

查看 字段 编码：

SHOW FULL COLUMNS FROM t_score;

新建 指定 编码 数据库：

create database aaa charset=utf8;

 案例：

use aaa;
show create database aaa;
show create table t_score;

CREATE DATABASE `aaa` /*!40100 DEFAULT CHARACTER SET latin1 */
CREATE TABLE `t_score` (
  `name` varchar(255) DEFAULT NULL,
  `subject` varchar(255) DEFAULT NULL,
  `score` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

DROP table if EXISTS t_score ;
create table t_score (
`name` varchar(255),
`subject` varchar(255),
score int
);

alter table t_score convert to character set utf8;
Alter database aaa character set utf8 collate utf8_general_ci;

insert into t_score values ("a","语",7),("a","数",8),("a","外",9);
insert into t_score values ("b","语",8),("b","数",5),("b","外",4);
insert into t_score values ("c","语",7),("c","数",6),("c","外",7);
select * from t_score;

/*每科第一名*/
select t.`subject`,t.`name`,t.`score` from t_score t
 join 
(select max(score) max,`subject` from t_score  group by `subject`)a
on t.score = a.max and t.`subject`=a.`subject` 
order by `subject`;

/*全科及格人数*/
select COUNT(0)人数 from
(select count(0) ct from 
(select * FROM t_score where `score` >6)a
group by `name`
having ct=3)b;

/*平均分及格的学生*/
select name,avg(score) av from t_score group by name having av>6;

/*sql语句实现行转列的3种方法*/
select `name` 姓名,
sum(case `subject` when '语' then `score` else 0 end) 语文,
sum(case `subject` when '数' then `score` else 0 end) 数学,
sum(case `subject` when '外' then `score` else 0 end) 英语 
from t_score group by `name`

 

 

————————————————
原文链接：https://blog.csdn.net/testManger/article/details/105348193