MySql211012

MySql211012

CREATE DATABASE stuexmp IF NOT EXISTS;
USE stuexmp;
ALTER DATABASE stuexmp DEFAULT CHARACTER SET utf8;
CREATE TABLE TeacherInfo(
	tid VARCHAR(6) NOT NULL PRIMARY KEY,
	tname VARCHAR(8) NOT NULL,
	tsex VARCHAR(2) NOT NULL DEFAULT '男',
	tbirthday DATE NOT NULL,
	school VARCHAR(12),
	Address VARCHAR(20)
);

CREATE TABLE TeacherInfo1(
	tid VARCHAR(6) NOT NULL PRIMARY KEY,
	tname VARCHAR(8) NOT NULL,
	tsex VARCHAR(2) NOT NULL DEFAULT '男',
	tbirthday DATE NOT NULL,
	school VARCHAR(12),
	Address VARCHAR(20)
);

CREATE TABLE TeacherInfo2(
	tid VARCHAR(6) NOT NULL PRIMARY KEY,
	tname VARCHAR(8) NOT NULL,
	tsex VARCHAR(2) NOT NULL DEFAULT '男',
	tbirthday DATE NOT NULL,
	school VARCHAR(12),
	Address VARCHAR(20)
);

CREATE TABLE CourseInfo(
	cid VARCHAR(4) NOT NULL,
	cname VARCHAR(16) NOT NULL,
	credit TINYINT
);

INSERT INTO TeacherInfo
	VALUES('100005','李慧强','男','19680925','计算机学院','北京市海淀区'),
	('100024','刘松','男','19760217','计算机学院','北京市海淀区'),
	('400021','陈霞飞','女','19751207','通信学院','上海市黄浦区'),
	('800004','刘全明','男','19780816','数学学院','广州市越秀区'),
	('120007','张莉','女','19820321','外国语学院','成都市锦江区');
	
INSERT INTO CourseInfo
	VALUES('1004','数据库系统',4),
	('1025','物联网技术',4),
	('4002','数字电路',4),
	('8001','高等数学',4),
	('1201','英语',4);
	
#使用insert into ... select...语句，将TeacherInfo表的记录快速插入TeacherInfo1中	
INSERT INTO TeacherInfo1
	SELECT * FROM TeacherInfo;
	
#省略列名表插入记录	
INSERT INTO TeacherInfo2
	VALUES('100005','李慧强','男','19680925','计算机学院','北京市海淀区');	
#不省略列名表插入记录
INSERT INTO TeacherInfo2(tid,tname,Address,school,tbirthday,tsex)
	VALUES('400021','陈霞飞','上海市黄浦区','通信学院','19751207','女');
#插入教师编号800004，姓名为“”，性别男 取默认值，出生日期为19780816，学院和地址为空的记录
INSERT INTO TeacherInfo2(tid,tname,tbirthday)
	VALUES('800004','刘全明','19780816');

#info1中更改编号为120007的出生日期	
UPDATE TeacherInfo1
	SET tbirthday = '1983-09-19'
	WHERE tid = '120007';
	
#将info1中性别为男的家庭地址都改为浦东新区	
UPDATE TeacherInfo1
	SET Address = '上海市浦东新区'
	WHERE tsex = '男';
	
#删除info1中编号为400021的记录	
DELETE FROM TeacherInfo1
	WHERE tid = '400021';
	
#delete语句删除全部记录	
DELETE FROM TeacherInfo1;
#truncate语句删除全部记录	
TRUNCATE TeacherInfo2;