sql语句示例

# 如果这个数据库已经存在就删去
DROP DATABASE if EXISTS jiaowu;
# 创建一个数据库，字符编码是utf8编码 排序规则是 utf8_general_ci  如果没有这句话 则不能插入中文数据
CREATE DATABASE jiaowu DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
# 指定操作哪个数据库，若没有这句话，那么下面的创建表 以及插入数据就不知道在哪个数据库下进行操作
# 会报没有选择数据库的错错误
USE jiaowu;
DROP TABLE if EXISTS student;
CREATE TABLE student
(
student_account char(50) not null,
student_name char(50) not null,
student_sex char(4) not null,
student_password char(50) not null,
PRIMARY KEY(student_account)
);
INSERT INTO student VALUES('141210104','邓超','男','123456');
INSERT INTO student VALUES('141210105','李华','女','123456');
INSERT INTO student VALUES('141210106','张三','男','123456');
INSERT INTO student VALUES('141210107','李四','男','123456');
 
DROP TABLE if EXISTS teacher;
CREATE TABLE teacher
(
teacher_account char(50) not null,
teacher_name char(50) not null,
teacher_password char(50) not null,
PRIMARY KEY(teacher_account)
);
INSERT INTO teacher VALUES('987','黄药师','123456');
INSERT INTO teacher VALUES('989','洪七公','123456');
INSERT INTO teacher VALUES('986','黄蓉','123456');
INSERT INTO teacher VALUES('985','欧阳锋','123456');
INSERT INTO teacher VALUES('984','郭靖','123456');
INSERT INTO teacher VALUES('983','裘千仞','123456');
 
DROP TABLE if EXISTS course;
CREATE TABLE course
(
course_id int not null auto_increment,#设置递增
course_name char(50) not null,
course_score float not null,
teacher_account char(50) not null,
classroom char(50) not null,
primary key(course_id) 
)auto_increment=1;# 每次增加1  其实默认也是1
# 由于id设置是递增的，就不用每次都插入id了
insert into course(course_name,course_score,teacher_account,classroom) values('java语言程序设计',5.0,'987','4032');
insert into course (course_name,course_score,teacher_account,classroom) values('c++语言程序设计',5.0,'986','3012');
insert into course (course_name,course_score,teacher_account,classroom) values('c#语言程序设计',5.0,'985','4312');
insert into course (course_name,course_score,teacher_account,classroom) values('javaWeb语言程序设计',5.0,'984','2012');
insert into course (course_name,course_score,teacher_account,classroom) values('c语言程序设计',5.0,'987','4015');
insert into course (course_name,course_score,teacher_account,classroom) values('python语言程序设计',5.0,'987','1012');
 
DROP table if EXISTS score;
CREATE table score
(
score_id int not null auto_increment,
student_account char(50) not null ,
course_id int not null,
chengji float not null,
primary key (score_id)
)auto_increment=1;
insert into score (student_account,course_id,chengji)VALUES('141210104',1,100);
insert into score (student_account,course_id,chengji)VALUES('141210105',2,100);
insert into score (student_account,course_id,chengji)VALUES('141210106',3,100);
insert into score (student_account,course_id,chengji)VALUES('141210107',4,100);
insert into score (student_account,course_id,chengji)VALUES('141210108',1,100);
insert into score (student_account,course_id,chengji)VALUES('141210109',1,100);