处理数据库遇到的问题
数据库导出问题
因为之前是用MySQL 5.5 Command Line Client创建了数据库,现在要导出来放到
在“开始”输入cmd,打开DOS命令界面,其次找到安装好的MySQL Server 5.5 根目录,然后输入cd+目录,按enter键进入
(如上图------cd C:\Program Files\MySQL\MySQL Server 5.5\bin)
其次,输入 mysqldump -u root -p student > stu.sql(记住不用加“;”)
mysqldump -u root -p 数据库名 > 表名.sql
然后输入自己MySQL的密码就OK,在MySQL Server的bin目录下找到导出的文件,双击打开,就跳到microsoft sql server management studio软件里面了。(我的是stu文件)
这是我第一次写的代码,执行后有错误。然后可以把它们复制到文档里用快捷键(Ctrl+h)进行文字替换
----------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE `tb_stu` (
`number` int(11) NOT NULL,
`faculty` varchar(50) DEFAULT NULL,
`class` varchar(50) DEFAULT NULL,
`ID` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT NULL,
`password` varchar(50) DEFAULT '123456',
`gender` varchar(5) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=latin1;
INSERT INTO `tb_stu` VALUES (1,'shuji','15shu1',11,'Mary','111111','f'),
(2,'shuji','15shu1',12,'Tom','222222','m'),
(3,'shuji','15shu1',13,'Tim','333333','m'),
(4,'shuji','15shu1',14,'Tina','444444','f'),
(5,'shuji','15shu1',15,'Jane','555555','f'),
(6,'shuji','15shu1',16,'Linda','666666','f'),
(7,'shuji','15shu1',17,'May','7777777','f'),
(8,'shuji','15shu1',18,'xp','888888','m');
----------------------------------------------------------------------------------------------------------------------------------
下图是我修改后的代码(1.把‘’删除,还有int类型不需要加()定义大小;2.自动增加数字的代码可以这样写-----number int identity(1,1);3.因为name是关键字--系统自己的默认值,所以要使用它的时候,用f_name这种格式进行区分;以上是需要注意的地方)
----------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE tb_stu (
number int identity(1,1),
faculty varchar(50) DEFAULT NULL,
class varchar(50),
ID int primary key,
f_name varchar(50) DEFAULT NULL,
password varchar(50) DEFAULT 123456,
gender varchar(5) DEFAULT NULL,
--PRIMARY KEY (ID)
)
INSERT INTO tb_stu (number,faculty,class,f_name,password,gender) VALUES(1,'shuji','15shu1','Mary','111111','f');
INSERT INTO tb_stu (number,faculty,class,f_name,password,gender) VALUES(2,'shuji','15shu1','Tom','222222','m');
INSERT INTO tb_stu (number,faculty,class,f_name,password,gender) VALUES(3,'shuji','15shu1','Tim','333333','m');
INSERT INTO tb_stu (number,faculty,class,f_name,password,gender) VALUES(4,'shuji','15shu1','Tina','444444','f');
INSERT INTO tb_stu (number,faculty,class,f_name,password,gender) VALUES(5,'shuji','15shu1','Jane','555555','m');
INSERT INTO tb_stu (number,faculty,class,f_name,password,gender) VALUES(6,'shuji','15shu1','Linda','666666','f');
INSERT INTO tb_stu (number,faculty,class,f_name,password,gender) VALUES(8,'shuji','15shu1','xp','888888','m');
INSERT INTO tb_stu (number,faculty,class,f_name,password,gender) VALUES(7,'shuji','15shu1','May','7777777','f');
----------------------------------------------------------------------------------------------------------------------------------
(如果出现说f_name无效的错误的话,可以重新创建一个.sql文件,然后把上面黑色代码放进去,再执行一下)
代码准确无误后,点击“执行”这个按钮,生成了tb_stu表(如下图)
PS:
我自己在生成tb_stu表的时候,遇到了name和password两列的值互换的情况,也上网百度了一下。最后自己捯饬发现不是name和password的语句写错了,而是往表里插入数值的时候没有一一对齐。出现上述现象的原因是因为ID是自动增加数值的,所以不需要再赋值给它。然后我就没放上它的值,和原来的列相比“少了ID”这一列的值,所以导致那两列的数值互换了。
解决方法是
insert into tb_stu( (number,faculty,class,f_name,password,gender))把这个括号里的内容加上,但是要去掉ID,然后再依次赋值。
eg:
INSERT INTO tb_stu (number,faculty,class,f_name,password,gender) VALUES(7,'shuji','15shu1','May','7777777','f');
以上就是我自己在处理数据库遇到的问题,如果有不对的地方望大家指出来,相互学习,相互进步,谢谢大家!