2021-04-04

学习数据库原理的自学练习以及出现’\xE7\xB2\xBE\xE7\x9B\x8A’ for column ‘sname’ at row 1 解决思路

借鉴于-jinxlzc博主的自学练习

创建数据库miku1

创建表S；

mysql> create table S (
    -> sno char(6), 
    -> sname char(20),
    -> status int(4),
    -> city char(8));

创建表P；

mysql> create table P (pno char(6),pname char(20),color char(4),weight int(6));

mysql> create table P (pno char(6),pname char(20),color char(4),weight int(6));
Query OK, 0 rows affected (0.02 秒)

创建表J；

mysql> create table J (jno char(6),jname char(20),city char(8));

mysql> create table J (jno char(6),jname char(20),city char(8));
Query OK, 0 rows affected (0.02 秒)

创建SPJ表：

mysql> create table SPJ (sno char(6),pho char(6),jno char(6),qty int(4)); //这里pho打错了应该是pno

mysql> create table SPJ (sno char(6),pho char(6),jno char(6),qty int(4));
Query OK, 0 rows affected (0.02 秒)

//上面语句中pho是错误的，因为是自我练习，所以就不在这里修改，下面给出了修改方法。锻炼应用能力！

向表s中插入数据；

insert into S(sno,sname,status,city) values ('S1','精益','20','天津');
Incorrect string value: '\xE7\xB2\xBE\xE7\x9B\x8A' for column 'sname' at row 1

insert into S(sno,sname,status,city) values (‘S1’,‘精益’,‘20’,‘天津’);

代码报错：分析是char值太大！

mysql> insert into S(sno,sname,status,city) values (‘S1’,‘精益’,‘20’,‘天津’);
Incorrect string value: ‘\xE7\xB2\xBE\xE7\x9B\x8A’ for column ‘sname’ at row 1

修改sname的char值大小为4

Alter table S alter column sname char(4); //这中修改方法是书中的，sql

在mysql中正确的语法为：

 alter table S modify column sname char(4);

mysql> alter table S modify column sname char(4);
Query OK, 0 rows affected (0.03 秒)

alter table P modify column sname char(4);

mysql> alter table P modify column sname char(4);
Query OK, 0 rows affected (0.03 秒)

alter table J modify column sname char(4);

mysql> alter table J modify column sname char(4);
Query OK, 0 rows affected (0.03 秒)

解决之后仍然发现报错：Incorrect string value: ‘\xE7\xB2\xBE\xE7\x9B\x8A’ for column ‘sname’ at row 1 //这种是出现了utf-8，数据库没有设置好！所以这里修改数据库编码，同时修改列的编码让他识别中文！//在创建表时需要注意：对列中文的时候加

alter table 表名 change 列名 列名 varchar(50) character utf8;

alter table S change sname sname varchar(50) character set utf8;

alter table S change sname sname varchar(50) character set utf8;
Query OK, 0 rows affected (0.02 秒)

nsert into S(sno,sname,status,city) values ('S1','精益','20','天津');

insert into S(sno,sname,status,city) values (‘S1’,‘精益’,‘20’,‘天津’);
Query OK, 1 rows affected (0.01 秒)

//其他表修改同上！

继续插入S表中数据；

insert into S(sno,sname,status,city) values ('S2','盛细','10','北京');

insert into S(sno,sname,status,city) values (‘S2’,‘盛细’,‘10’,‘北京’);

insert into S(sno,sname,status,city) values ('S3','东方红','30','北京');

insert into S(sno,sname,status,city) values (‘S3’,‘东方红’,‘30’,‘北京’);

insert into S(sno,sname,status,city) values ('S4','丰泰盛','20','天津');

insert into S(sno,sname,status,city) values (‘S4’,‘丰泰盛’,‘20’,‘天津’);

insert into S(sno,sname,status,city) values ('S5','为民','30','上海');

insert into S(sno,sname,status,city) values (‘S5’,‘为民’,‘30’,‘上海’);

select * from S;

mysql> select * from S;
±-----±----------±-------±-------+
| sno | sname | status | city |
±-----±----------±-------±-------+
| S1 | 精益 | 20 | 天津 |
| S2 | 盛细 | 10 | 北京 |
| S3 | 东方红 | 30 | 北京 |
| S4 | 丰泰盛 | 20 | 天津 |
| S5 | 为民 | 30 | 上海 |
±-----±----------±-------±-------+

插入P表中数据；

insert into P(pno,pname,color,weight) values ('P1','螺母','红','12');

insert into P(pno,pname,color,weight) values (‘P1’,‘螺母’,‘红’,‘12’);

insert into P(pno,pname,color,weight) values ('P2','螺栓','绿','17');

insert into P(pno,pname,color,weight) values (‘P2’,‘螺栓’,‘绿’,‘17’);

insert into P(pno,pname,color,weight) values ('P3','螺丝刀','蓝','14');

insert into P(pno,pname,color,weight) values (‘P3’,‘螺丝刀’,‘蓝’,‘14’);

insert into P(pno,pname,color,weight) values ('P4','螺丝刀','红','14');

insert into P(pno,pname,color,weight) values (‘P4’,‘螺丝刀’,‘红’,‘14’);

insert into P(pno,pname,color,weight) values ('P5','凸轮','蓝','40');

insert into P(pno,pname,color,weight) values (‘P5’,‘凸轮’,‘蓝’,‘40’);

insert into P(pno,pname,color,weight) values ('P6','齿轮','红','30');

insert into P(pno,pname,color,weight) values (‘P6’,‘齿轮’,‘红’,‘30’);

select * from P;

mysql> select * from P;
±-----±----------±------±-------+
| pno | pname | color | weight |
±-----±----------±------±-------+
| P1 | 螺母 | 红 | 12 |
| P2 | 螺栓 | 绿 | 17 |
| P3 | 螺丝刀 | 蓝 | 14 |
| P4 | 螺丝刀 | 红 | 14 |
| P5 | 凸轮 | 蓝 | 40 |
| P6 | 齿轮 | 红 | 30 |
±-----±----------±------±-------+
6 行于数据集 (0.01 秒)

插入j表中数据；

insert into J(jno,jname,city) values ('J1','三建','北京');

insert into J(jno,jname,city) values (‘J1’,‘三建’,‘北京’);

insert into J(jno,jname,city) values ('J2','一汽','长春');

insert into J(jno,jname,city) values (‘J2’,‘一汽’,‘长春’);

insert into J(jno,jname,city) values ('J3','弹簧厂','天津');

insert into J(jno,jname,city) values (‘J3’,‘弹簧厂’,‘天津’);

insert into J(jno,jname,city) values ('J4','造船厂','天津');

insert into J(jno,jname,city) values (‘J4’,‘造船厂’,‘天津’);

insert into J(jno,jname,city) values ('J5','机车厂','唐山');

insert into J(jno,jname,city) values (‘J5’,‘机车厂’,‘唐山’);

insert into J(jno,jname,city) values ('J6','无线电厂','常州');

insert into J(jno,jname,city) values (‘J6’,‘无线电厂’,‘常州’);

insert into J(jno,jname,city) values ('J7','半导体厂','南京');

insert into J(jno,jname,city) values (‘J7’,‘半导体厂’,‘南京’);

mysql> select * from J;
±-----±-------------±-------+
| jno | jname | city |
±-----±-------------±-------+
| J1 | 三建 | 北京 |
| J2 | 一汽 | 长春 |
| J3 | 弹簧厂 | 天津 |
| J4 | 造船厂 | 天津 |
| J5 | 机车厂 | 唐山 |
| J6 | 无线电厂 | 常州 |
| J7 | 半导体厂 | 南京 |
±-----±-------------±-------+
7 行于数据集 (0.01 秒)

插入spj表数据：

insert into SPJ(sno,pno,jno,qty) values ('S1','P1','J1','200');

insert into SPJ(sno,pno,jno,qty) values ('S1','P1','J3','100');

insert into SPJ(sno,pno,jno,qty) values ('S1','P1','J4','700');

insert into SPJ(sno,pno,jno,qty) values ('S1','P2','J2','100');

insert into SPJ(sno,pno,jno,qty) values ('S2','P3','J1','400');

insert into SPJ(sno,pno,jno,qty) values ('S2','P3','J2','200');

insert into SPJ(sno,pno,jno,qty) values ('S2','P3','J4','500');

insert into SPJ(sno,pno,jno,qty) values ('S2','P3','J5','400');

insert into SPJ(sno,pno,jno,qty) values ('S2','P5','J1','400');

insert into SPJ(sno,pno,jno,qty) values ('S2','P5','J2','100');

insert into SPJ(sno,pno,jno,qty) values ('S3','P1','J1','200');

insert into SPJ(sno,pno,jno,qty) values ('S3','P3','J1','200');

insert into SPJ(sno,pno,jno,qty) values ('S4','P5','J1','100');

insert into SPJ(sno,pno,jno,qty) values ('S4','P6','J3','300');

insert into SPJ(sno,pno,jno,qty) values ('S4','P6','J4','300');

insert into SPJ(sno,pno,jno,qty) values ('S4','P6','J4','200');

insert into SPJ(sno,pno,jno,qty) values ('S5','P6','J4','200');

insert into SPJ(sno,pno,jno,qty) values ('S5','P2','J4','100');

insert into SPJ(sno,pno,jno,qty) values ('S5','P3','J1','200');

insert into SPJ(sno,pno,jno,qty) values ('S5','P6','J2','200');

insert into SPJ(sno,pno,jno,qty) values ('S5','P6','J4','500');

插入错误：列名搞错了修改列明：

alter table SPJ change  pho pno char(4);

再次插入：

mysql> select * from SPJ
-> ;
±-----±-----±-----±-----+
| sno | pno | jno | qty |
±-----±-----±-----±-----+
| S1 | P1 | J1 | 200 |
| S1 | P1 | J3 | 100 |
| S1 | P1 | J4 | 700 |
| S1 | P2 | J2 | 100 |
| S2 | P3 | J1 | 400 |
| S2 | P3 | J2 | 200 |
| S2 | P3 | J4 | 500 |
| S2 | P3 | J5 | 400 |
| S2 | P5 | J1 | 400 |
| S2 | P5 | J2 | 100 |
| S3 | P1 | J1 | 200 |
| S3 | P3 | J1 | 200 |
| S4 | P5 | J1 | 100 |
| S4 | P6 | J3 | 300 |
| S4 | P6 | J4 | 300 |
| S4 | P6 | J4 | 200 |
| S5 | P6 | J4 | 200 |
| S5 | P2 | J4 | 100 |
| S5 | P3 | J1 | 200 |
| S5 | P6 | J2 | 200 |
| S5 | P6 | J4 | 500 |
±-----±-----±-----±-----+
21 行于数据集 (0.01 秒)

使用SQL语言完成以下查询：

（1）求供应工程J1零件的供应商号码SNO；

mysql> select sno from SPJ where jno='j1';
±-----+
| sno |
±-----+
| S1 |
| S2 |
| S2 |
| S3 |
| S3 |
| S4 |
| S5 |
±-----+
7 行于数据集 (0.01 秒)

（2）求供应工程J1零件P1的供应商号码SNO；

mysql> select sno from SPJ where jno='j1' and pno='p1';
±-----+
| sno |
±-----+
| S1 |
| S3 |
±-----+
2 行于数据集 (0.01 秒)

（3）求供应工程J1零件为红色的供应商号码SNO；

mysql> select sno from p,spj where p.color='红' and spj.jno='j1' and spj.pno=p.pno;
±-----+
| sno |
±-----+
| S1 |
| S3 |
±-----+
2 行于数据集 (0.02 秒)

（4）求没有使用天津供应商生产的红色零件的工程好JNO；

子句子：找出 使用红色的 jon号：select jno from spj,p,s where p.color=‘红’ and s.city=‘天津’ and spj.pno=p.pno and spj.sno=s.sno;

用not in 排除：

select jno from j where jno not in (select jno from spj,p,s where p.color=‘红’ and s.city=‘天津’ and spj.pno=p.pno and spj.sno=s.sno) ;

mysql> select jno from j where jno not in (select jno from spj,p,s where p.color=‘红’ and s.city=‘天津’ and spj.pno=p.pno and spj.sno=s.sno);
±-----+
| jno |
±-----+
| J2 |
| J5 |
| J6 |
| J7 |
±-----+
4 行于数据集 (0.02 秒)

（5）求至少用了供应商S1所供应的全部零件的工程和JNO；

这里用exists

找出工程和使用的零件 select * from p where spj.pno=p.pno and spj.jno=j.jno)

再找出采用s1 且（使用工程和零件）

Select * from spj where spj.sno=‘s1’ and exsts ( select * from p where spj.pno=p.pno and spj.jno=j.jno)

再找到jno：

select jno from j where exists (select * from spj where spj.sno='s1' and exists(select * from p where spj.pno=p.pno and spj.jno=j.jno));

select jno from j where exists (select * from spj where spj.sno=‘s1’ and exists(select * from p where spj.pno=p.pno and spj.jno=j.jno));

结果如下：
mysql> select jno from j where exists (select * from spj where spj.sno=‘s1’ and exists(select * from p where spj.pno=p.pno and spj.jno=j.jno));
±-----+
| jno |
±-----+
| J1 |
| J2 |
| J3 |
| J4 |
±-----+
4 行于数据集 (0.02 秒)