测试人员工作在工作中会用到SQL来辅助测试,求职时也常常会在笔试环节遇到各种各样的sql设计题目,张老师整理了一些工作中常用的sql知识点,希望对大家有所帮助。
sql语句学习全攻略
创建表
1、创建学生表,分数表和课程表
create or replace table student(
sid int(11) primary key not null,
sname char(25) not null,
age int(11) not null,
sex char(2) not null,
department char(40) ,
address char(200) ,
birthplace varchar(256)
);
create or replace table sc(
sid int(11) not null,
cid int(11) not null,
grade int(11)
);
create or replace table course(
cid int(11) not null primary key default 4,
cname char(40),
teacher char(40)
);
#以下是插入课程表的数据
delete from course ;
insert into course values('8108001','math','sandy');
insert into course values('8108002','english','sherry');
insert into course values('8108003','computer','sandy');
insert into course values('8108004','web','sandy');
insert into course values('8108005','java','sandy');
insert into course values('8108006','C languge','sherry');
insert into course values('8108007','python','xiaozhu');
insert into course values('8108008','testing','xiaozhu');
insert into course values('8108009','linux','sherry');
insert into course values('8108010','shell','sherry');
#以下是插入成绩级表的数据
delete from sc;
insert into sc values('3108001','8108010','90');
insert into sc values('3108001','8108003','67');
insert into sc values('3108002','8108003','54');
insert into sc values('3108002','8108010','84');
insert into sc values('3108003','8108003','78');
insert into sc values('3108004','8108004','89');
insert into sc values('3108005','8108006','56');
insert into sc values('3108006','8108005','60');
insert into sc values('3108007','8108004','79');
insert into sc values('3108008','8108008','89');
insert into sc values('3108009','8108002','46');
insert into sc values('3108010','8108003','87');
insert into sc values('3108011','8108001','85');
insert into sc values('3108011','8108002','81');
insert into sc values('3108012','8108001','97');
insert into sc values('3108012','8108002','55');
insert into sc values('3108013','8108002','86');
insert into sc values('3108013','8108001','71');
insert into sc values('3108014','8108002','69');
insert into sc values('3108014','8108001','78');
insert into sc values('3108015','8108002','67');
insert into sc values('3108016','8108001','85');
insert into sc values('3108016','8108003','85');
insert into sc values('3108016','8108002','85');
insert into sc values('3108016','8108004','85');
insert into sc values('3108016','8108005','85');
insert into sc values('3108016','8108006','80');
insert into sc values('3108016','8108007','79');
insert into sc values('3108016','8108009','36');
insert into sc values('3108016','8108010','78');
insert into sc values('3108016','8108008','88');
insert into sc values('3108016','8108021','83');
insert into sc values('3108015','8108001','85');
insert into sc values('3108015','8108003','85');
insert into sc values('3108015','8108004','85');
insert into sc values('3108015','8108005','85');
insert into sc values('3108015','8108006','80');
insert into sc values('3108015','8108007','79');
insert into sc values('3108015','8108009','36');
insert into sc values('3108015','8108010','78');
insert into sc values('3108015','8108008','88');
insert into sc values('3108015','8108021','83');
#以下是插入学生信息数据
delete from student;
insert into student values('3108001','wang min',21,'f','computer-tec','zhongshan road','jiangsu');
insert into student values('3108002','jidu',20,'m','english','zhongshan road','fujian');
insert into student values('3108003','wangqing',19,'f','computer-tec','zhongshan road','jiangsu');
insert into student values('3108004','liuxin',23,'f','chinese','zhongshan road','shanghai');
insert into student values('3108005','ligu',22,'f','computer-tec','zhongshan road','jiangsu');
insert into student values('3108006','songjia',19,'m','english','zhongshan road','jiangsu');
insert into student values('3108007','huamao',20,'f','chinese','zhongshan road','shanghai');
insert into student values('3108008','zhujiao',21,'f','english','zhongshan road','jiangsu');
insert into student values('3108009','wuyi',23,'m','computer-tec','zhongshan road','jiangsu');
insert into student values('3108010','jilian',18,'f','chinese','zhongshan road','hunan');
insert into student values('3108011','linbiao',22,'m','computer-tec','zhongshan road','jiangsu');
insert into student values('3108012','maoguai',21,'m','english','zhongshan road','fujian');
insert into student values('3108013','rongqi',23,'m','computer-tec','zhongshan road','jiangsu');
insert into student values('3108014','sangzi',20,'f','chinese','zhongshan road','hunan');
insert into student values('3108015','surui',16,'f','computer-tec','zhongshan road','fujian');
insert into student values('3108016','liushaoqi',24,'m','english','zhongshan road','hunan');
问题列表
需要参考答案的下方留言