创建表 查询表以及添加数据
创建表
1. drop table if exists login;//如果表login存在,就会删掉重新创建
create table login()
2. create table if not exists login(); 如果存在就不创建了
3. userid int not null primary key auto_increment comment'这是账号' // 字段类型设为Int 在注释前面加上auto_increment 表示自增
给表添加数据
多条数据用逗号分开,最后一条写分号
insert into teacher values
('804','李诚','男','1958-12-02','副教授','计算机系'),
('831','刘冰','女','1977-08-14','助教','电子工程系 ');
查询数据
1. select * from aa(表名); //查询表aa的所有数据
2. select * from teacher where tname like '王%'; //查询 teacher 表里 条件是 王 开头的名字
3. select * from teacher where tname like '_冰%'; //查询第二个为 冰 的名字
4. select distinot depart from teacher //查询教师不重复的Depart 行列 ( distinot 去重 )
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
查询 #姓王的 select * from teacher where tname like '王%'; #第二个字是冰的 select * from teacher where tname like '_冰%'; #开始查询: #1、查询Student表中的所有记录的Sname、Ssex和Class列。 select sname,ssex,clsaa from student; #2、查询教师所有的单位即不重复的Depart列。 select distinct depart from teacher ; select count(distinct depart) from teacher; #3、查询Student表的所有记录。 select * from student; #4、查询Score表中成绩在60到80之间的所有记录。 select * from score where degree >= 60 and degree <= 80; select * from score where degree between 60 and 80; #5、查询Score表中成绩为85,86或88的记录。 select * from score where degree = 85 or degree = 86 or degree = 88; select * from score where degree in(85,86,88); #6、查询Student表中“95031”班或性别为“女”的同学记录。 select * from student where class = '95031' or ssex = '女'; #7、以Class降序查询Student表的所有记录 select * from stydent order by clsaa desc; #8、以Cno升序、Degree降序查询Score表的所有记录 select * from score cno order by degree desc; #9、查询“95031”班的学生人数。 select count(*) from student where clsaa = '95031'; #10、查询Score表中的最高分的学生学号和课程号。 select sno,cno from score where degree in (select max(degree) from score); #11、查询每门课的平均成绩。 select avg(degree) from score; #12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数 select cno,avg(degree) from score group by cno having cno like '3%' and count(sno)>5; #13、查询分数大于70,小于90的Sno列。 select sno from score where degree>70 and degree<90; #14、查询所有学生的Sname、Cno和Degree列 select sname,cno,degree from student join score where student.sno=score.sno; #15、查询所有学生的Sno、Cname和Degree列。(注意要加上参数 不然会报错 提示 sno是模糊的) select student.sno,cname,degree from student join course join score where student.sno=score.sno and course.cno=score.cno; #16、查询所有学生的Sname、Cname和Degree列。 select sname,cname,degree from student join course join score where student.sno=score.sno and course.cno=score.cno; #17、查询“95033”班学生的平均分 select clsaa,avg(degree) from student join score on student.sno = score.sno where clsaa = '95033'; #18、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。 select * from score where cno = '3-105' and degree>(select degree from score where sno = '109'and cno='3-105');