mysql项目实战，常用sql语句的实战笔记

1.使用sql语句对数据库进行创建

//创建实验用的数据库
CREATE DATABASE jsschool;

//使用当前数据库
USE jsschool;

//创建学生表
CREATE TABLE student (
    sno VARCHAR(20) PRIMARY KEY COMMENT '学生编号',
    sname VARCHAR(20) NOT NULL COMMENT '学生姓名',
    ssex VARCHAR(10) NOT NULL COMMENT '学生性别',
    sbirthday DATETIME COMMENT '学生生日', 
    class VARCHAR(20) COMMENT '学生班级'
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

//创建教师表
CREATE TABLE teacher (
    tno VARCHAR(20) PRIMARY KEY COMMENT '教师编号',
    tname VARCHAR(20) NOT NULL COMMENT '教师姓名',
    tsex VARCHAR(10) NOT NULL COMMENT '教师性别',
    tbirthday DATETIME COMMENT '教师生日',
    prof VARCHAR(20) NOT NULL COMMENT '教师职称',
    depart VARCHAR(20) NOT NULL  COMMENT '教师院系'
)ENGINE=InnoDB DEFAULT CHARSET=utf8;


//创建课程表
CREATE TABLE course (
    cno VARCHAR(20) PRIMARY KEY COMMENT '课程编号',
    cname VARCHAR(20) NOT NULL COMMENT '课程名称',
    tno VARCHAR(20) NOT NULL COMMENT '外键教师编号',
    FOREIGN KEY(tno) REFERENCES teacher(tno) 
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

//创建分数表
CREATE TABLE score (
    sno VARCHAR(20) NOT NULL COMMENT '学生编号', 
    cno VARCHAR(20) NOT NULL COMMENT '课程编号', 
    degree DECIMAL COMMENT '成绩',
    FOREIGN KEY(sno) REFERENCES student(sno),	
    FOREIGN KEY(cno) REFERENCES course(cno)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

//列出所有创建的表
SHOW TABLES;

2.给我们创建好的数据表中插入一些数据

INSERT INTO student VALUES('101', '张琴', '男', '1999-02-01', '51');
INSERT INTO student VALUES('102', '李福', '男', '1995-01-02', '52');
INSERT INTO student VALUES('103', '刘军', '男', '1998-01-24', '51');
INSERT INTO student VALUES('104', '张文', '女', '1994-02-20', '53');
INSERT INTO student VALUES('105', '陈锦文', '女', '1999-02-10', '56');
INSERT INTO student VALUES('106', '周建', '男', '1993-05-21', '55');
INSERT INTO student VALUES('107', '钱富', '男', '1995-09-20', '55');
INSERT INTO student VALUES('108', '刘建国', '男', '1996-06-01', '56');
INSERT INTO student VALUES('109', '李四', '男', '1998-08-03', '57');


INSERT INTO teacher VALUES('1', '张宝宝', '女', '1975-02-02', '助教', '电子工程');
INSERT INTO teacher VALUES('2', '李雯雯', '女', '1979-09-08', '讲师', '计算机科学');
INSERT INTO teacher VALUES('3', '王富贵', '男', '1972-05-05', '助教', '土木工程');
INSERT INTO teacher VALUES('4', '金钱多', '男', '1976-05-24', '助教', '外语系');




INSERT INTO course VALUES('65', '集成电路', '1');
INSERT INTO course VALUES('64', '数据结构', '2');
INSERT INTO course VALUES('67', '工程结构', '3');
INSERT INTO course VALUES('66', '英语', '4');


INSERT INTO score VALUES('101', '65', '86');
INSERT INTO score VALUES('102', '64', '75');
INSERT INTO score VALUES('103', '65', '68');
INSERT INTO score VALUES('103', '64', '92');
INSERT INTO score VALUES('104', '67', '88');
INSERT INTO score VALUES('104', '67', '76');
INSERT INTO score VALUES('105', '66', '64');
INSERT INTO score VALUES('106', '66', '91');
INSERT INTO score VALUES('107', '65', '78');
INSERT INTO score VALUES('108', '64', '85');
INSERT INTO score VALUES('109', '66', '79');
INSERT INTO score VALUES('109', '67', '81');

3.基本的sql查询语句

//查询所有数据从student表中
select* from student;
//查询sname,ssex,class 三个字段
select sname,ssex,class from student;
//查询字段depart 取出重复的数据，只针对distinct字段
select distinct depart from teacher;
//查询分数在60 到80 之间的学生
select *from score where degree between 60 and 80;
//查询成绩是85 86 88的学生
select *from score where degree in(85,86,88);
//查询51班同学或者性别是女的同学
select *from student where class='51' or ssex='女';
//查询成绩， 排列顺序是让编号顺序排列，成绩倒叙排列
select *from score order by cno asc,degree desc;
//统计51班学生的数量
select count(*) from student where class='51';
//子查询，查询成绩最好的学生
select sno,cno,degree from score where degree=(select max(degree) from score);
//排序查询，选出成绩最好的学生
select sno,cno,degree from score order by degree desc limit 1;

4.计算平均值

//计算各科成绩的平均值

select avg(degree) from score where cno='64';

select avg(degree) from score where cno='65';


//按照课程分组，计算各科的平均值
select avg(degree) from score group by cno;





//计算各科的平均分数，统计各科的总人数，选出人数大于2的，并且课程以6开头的
select cno,avg(degree),count(*) from score 
group by cno having count(cno)>=2 and cno like '6%';;

5.联表查询

select sname,cno,degree from student,score 
where student.sno=score.sno;

select sname,cname,degree from student,course,score 
where student.sno=score.sno 
and course.cno=score.cno;