sql查询代码
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
一、创建数据库和表
1 创建数据库
create database Student
2 创建表student，course和student_course
use Student;
go
create table students(
Student_id varchar(32)primary key,
Student_name varchar(32),
Student_sex char(2),
Student_nation char(5),
Student_birthday datetime,
Student_time datetime,
Student_classid varchar(32),
Student_home varchar(32),
Student_else varchar(32)
);
go
create table course(
Course_id varchar(32)primary key,
Course_name varchar(32),
Course_period int,
Course_credit int,
Course_kind int,
Course_describe varchar(32)
);
go
create table student_course(
ID bigint primary key,
Student_id varchar(32),
Course_id varchar(32),
Student_grade int,
Course_year tinyint
);
3 在表student中插入数据
insert into students values
('011001','叶海平','男','汉','1989-01-23','2010-09-01','011','山西',null), ('011002','景风','男','汉','1989-06-25','2010-09-01','011','重庆',null), ('012001','华丽佳','女','汉','1988-05-20','2010-09-01','012','大连',null), ('012002','范治华','男','汉','1989-05-20','2010-09-01','012','山东',null), ('013001','李佳佳','女','汉','1990-03-01','2010-09-01','013','湖南',null),
('013002','史慧敏','女','汉','1988-10-11','2010-09-01','013','湖北',null), ('014001','安静','女','汉','1991-03-23','2010-09-01','014','山西',null), ('014002','尹强','男','汉','1987-06-02','2010-09-01','014','重庆',null), ('015001','曹洪','男','汉','1989-05-16','2010-09-01','015','大连',null), ('015002','杨世英','女','汉','1989-12-03','2010-09-01','015','天津',null)
4 在表course中插入数据
insert into course values
('1001','电子商务基础',72,2,1,null),
('2001','英语',72,3,0,null),
('2002','英语',72,3,0,null),
('3001','网页设计与制作',72,2,1,null),
('4001','网络数据库',72,3,1,null),
('5001','电子商务安全与管理',72,2,1,null)
5 在表student_course中插入数据
insert into student_course values
(1,'011001','1001',82,1),
(2,'011002','1001',86,1),
(3,'011001','2001',78,1),
(4,'011002','2001',80,1),
(5,'011001','2002',77,2),
(6,'011002','2002',88,2),
(7,'012001','1001',90,1),
(8,'012002','1001',75,1),
(9,'012001','2001',68,1),
(10,'012002','2001',73,1),
(11,'012001','2002',80,2),
(12,'012002','2002',78,2),
(13,'012001','4001',85,2),
(14,'012002','4001',66,2),
(15,'013001','1001',76,1),
(16,'013002','1001',81,1),
(17,'013001','2001',69,1),
(18,'013002','2001',90,1),
(19,'013001','2002',70,2),
(20,'013002','2002',89,2),
(21,'013001','4001',88,2),
(22,'013002','4001',86,2),
(23,'014001','4001',76,2),
(24,'014002','4001',80,2),
(25,'015001','4001',88,2),
(26,'015002','4001',66,2)