使用Navicat代码新建数据库以及解决出现的Incorrect string value: ‘xE6x9Dx8ExE5x8Bx87’ for column ‘Sname’ at row

上方点击查询

新建查询

在编辑器中写入代码进行数据库和表的创建

在此把创建的代码模板写出来

create database educ;
use educ;
CREATE TABLE Student
(
Sno CHAr(9) NOT NULL PRIMARY KEY,
Sname CHAr(20),
Ssex CHAr(2),
Sage SMALLINT,
Sdept CHAr(20)
);

CREATE TABLE Course
(
Cno CHAr(4) NOT NULL PRIMARY KEY,
Cname CHAr(40) NOT NULL,
Cpno CHAr(4),
Ccredit SMALLINT,
FOREIGN KEY (Cpno) REFERENCES Course(Cno)
);

CREATE TABLE SC
(
Sno CHAr(9) NOT NULL,
Cno CHAr(4) NOT NULL,
Grade SMALLINT,
PRIMARY KEY(Sno,Cno),
FOREIGN KEY (Sno) REFERENCES Student(Sno),
FOREIGN KEY (Cno) REFERENCES Course(Cno)
);

创建完成之后，进行插入操作

INSERT INTO Student VALUES('200215121','李勇','男',20,'CS');
INSERT INTO Student VALUES('200215122','刘晨','女',19,'CS');
INSERT INTO Student VALUES('200215123','王敏','女',18,'MA');
INSERT INTO Student VALUES('200215125','张立','男',19,'IS');
INSERT INTO Student VALUES('200215124','张立','男',19,'IS');

INSERT INTO Course VALUES('2','数学',null,2);
INSERT INTO Course VALUES('6','数据处理',null,2);
INSERT INTO Course VALUES('7','pascal语言','6',4);
INSERT INTO Course VALUES('5','数据结构','7',4);
INSERT INTO Course VALUES('4','操作系统','6',3);
INSERT INTO Course VALUES('1','数据库','5',4);
INSERT INTO Course VALUES('3','信息系统','1',4);

INSERT INTO SC VALUES('200215121','1',92);
INSERT INTO SC VALUES('200215121','2',85);
INSERT INTO SC VALUES('200215121','3',88);
INSERT INTO SC VALUES('200215122','2',90);
INSERT INTO SC VALUES('200215122','3',80);

这里在运行时可能会出现一个问题： Incorrect string value: ‘xE6x9Dx8ExE5x8Bx87’ for column ‘Sname’ at row 1

在上网查过之后有了解决方案：

把原本出错的数据库删除掉

drop database educ：

在新建表的代码后面加上DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci即可

在此把图挂出来

这样，一个完整的数据库就创建好啦

我们来查询一下其中一个表的数据