mysql数据库练手

数据库操作----练手

基于MySQL数据库的一些操作，涉及到事务，jdbc等操作，以下是练手SQL代码：

`student`SHOW DATABASES;
USE school;
CREATE TABLE IF NOT  EXISTS `student`(
`id` INT(100) NOT NULL AUTO_INCREMENT COMMENT '学员id',
`name` VARCHAR(10) NOT NULL DEFAULT '匿名' COMMENT '学员姓名',
`sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '学员性别',
`age` INT(3) NOT NULL DEFAULT '1' COMMENT '学员年龄',
`gradeid` INT(5) NOT NULL COMMENT '学员年级id',
`address` VARCHAR(100) DEFAULT NULL COMMENT '学员地址',
PRIMARY KEY (`id`)
)ENGINE INNODB DEFAULT CHARSET utf8

ALTER TABLE student RENAME AS student1;

ALTER TABLE student1 CHANGE age age1 INT(2);

ALTER TABLE student1 ADD hobby VARCHAR(10);

ALTER TABLE student1 DROP hobby;

ALTER TABLE student1 MODIFY `age1` VARCHAR(10);

CREATE TABLE `grade`(
`gradeid` INT(5) NOT NULL AUTO_INCREMENT COMMENT '年级id',
PRIMARY KEY (`gradeid`)
)ENGINE INNODB DEFAULT CHARSET utf8




ALTER TABLE student 
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`);

ALTER TABLE student MODIFY `gradeid` INT(5)  DEFAULT '1';

`student`


INSERT INTO `student` (`name`,`sex`) VALUE ('李四','女'),('王二','男');

UPDATE `student` SET `name`='城楼' WHERE `id`='1';
UPDATE `student` SET `name`='城楼1' WHERE `id`>='1' AND `sex`='男';
UPDATE `student` SET `name`='城楼3',`age`='18' WHERE `id`=2;

-- 不会清空自动递增，还是会从删除时的递增开始递增
DELETE FROM `student`;
-- 会清空自动递增，从一开始
TRUNCATE TABLE `student`


CREATE DATABASE IF NOT EXISTS `school`;
-- 创建一个school数据库
USE `school`;-- 创建学生表
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`(
`studentno` INT(4) NOT NULL COMMENT '学号',
`loginpwd` VARCHAR(20) DEFAULT NULL,
`studentname` VARCHAR(20) DEFAULT NULL COMMENT '学生姓名',
`sex` TINYINT(1) DEFAULT NULL COMMENT '性别，0或1',
`gradeid` INT(11) DEFAULT NULL COMMENT '年级编号',
`phone` VARCHAR(50) NOT NULL COMMENT '联系电话，允许为空',
`address` VARCHAR(255) NOT NULL COMMENT '地址，允许为空',
`borndate` DATETIME DEFAULT NULL COMMENT '出生时间',
`email` VARCHAR (50) NOT NULL COMMENT '邮箱账号允许为空',
`identitycard` VARCHAR(18) DEFAULT NULL COMMENT '身份证号',
PRIMARY KEY (`studentno`),
UNIQUE KEY `identitycard`(`identitycard`),
KEY `email` (`email`)
)ENGINE=MYISAM DEFAULT CHARSET=utf8;




-- 创建年级表
DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade`(
	`gradeid` INT(11) NOT NULL AUTO_INCREMENT COMMENT '年级编号',
  `gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
    PRIMARY KEY (`gradeid`)
) ENGINE=INNODB AUTO_INCREMENT = 6 DEFAULT CHARSET = utf8;

-- 创建科目表
DROP TABLE IF EXISTS `subject`;
CREATE TABLE `subject`(
	`subjectno`INT(11) NOT NULL AUTO_INCREMENT COMMENT '课程编号',
    `subjectname` VARCHAR(50) DEFAULT NULL COMMENT '课程名称',
    `classhour` INT(4) DEFAULT NULL COMMENT '学时',
    `gradeid` INT(4) DEFAULT NULL COMMENT '年级编号',
    PRIMARY KEY (`subjectno`)
)ENGINE = INNODB AUTO_INCREMENT = 19 DEFAULT CHARSET = utf8;

-- 创建成绩表
DROP TABLE IF EXISTS `result`;
CREATE TABLE `result`(
	`studentno` INT(4) NOT NULL COMMENT '学号',
    `subjectno` INT(4) NOT NULL COMMENT '课程编号',
    `examdate` DATETIME NOT NULL COMMENT '考试日期',
    `studentresult` INT (4) NOT NULL COMMENT '考试成绩',
    KEY `subjectno` (`subjectno`)
)ENGINE = INNODB DEFAULT CHARSET = utf8;


-- 插入科目数据
INSERT INTO `subject`(`subjectno`,`subjectname`,`classhour`,`gradeid`)VALUES
(1,'高等数学-1',110,1),
(2,'高等数学-2',110,2),
(3,'高等数学-3',100,3),
(4,'高等