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;
MySql211012CREATE 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