（一）Mysql查询语句

--1：查询所有学死的学号和姓名

select sno, sname from student;

--2：查询所有学死的姓名、学号和地点系

select sno, sname,sdept from student;

--3: 查询所有学死的具体记录

select * from student

--4: 查询所有学死的姓名及其出死年份

select sname, 2011-sage as 出死年份 from student;

--5：查询所有学死姓名、出死年份和地点系，要供用小写字母暗示所有系名

select sname, 2011-sage as 出死年份,lower(sdept) from student;

--6：查询选建了课程的学死学号

select distinct sno from sc;

--7：查询计较机系(IS)所有学死的名单

select sname from student where sdept = "is";

--8：查询所熟年齿在20以下学死的姓名和年齿

select sname ,sage from student where sage <20;

--9: 查询测验功效有分歧格的学死的学号

select distinct sno from sc where grade < 60;

--10: 查询年齿在20-23 （包露20和23）之间的学死的姓名、系别和年齿

select sname,sdept,sage from student where sage>=20 and sage<=23;

--11: 查询信息系（IS）、数学系（MA）和计较机科学系（CS）学死的姓名和性别

select sname ,ssex from student where sdept = "is" or sdept="ma" or sdept = "CS";或

select sname,ssex from student where sdept in("is","ma","cs");

--12: 查询学号为95001的学死的具体环境

select * from student where sno = 95001;

--13: 查询所有姓林的学死的姓名、学号和性别

select sname,sno,ssex from student where sname like "林%";

--14: 查询姓“欧阳”且全名为三个汉字的学死的姓名

select sname from student where sname like "欧阳_";

--15：查询名字中第两个字为“燕”字的学死姓名和学号

select sname,sno from student where sname like "_燕%";

--16：查询所有不姓“刘”的学死的姓名

select sname from student where sname not like "^刘%";

--17：查询课程名为“DB_DESIGN”的课程号的学分

select ccredit from course where cname = "db_design";

--18：查询缺少功效的学死的学号和响应的课程号（功效字段值为Null）

select sno,cno from sc where grade<=>null;

--19: 查询所有有功效的学死的学号和课程号

select sno ,cno from sc ;

--20: 查询所有计较机系年齿在20以下的学死姓名

select sname from student where sage<20 and sdept="cs";

--21: 查询选建了3号课程的学死的学号和功效，查询功效按分数降序布列

select sno,grade from sc where cno =3 order by grade desc;

--22: 查询所有学死环境，查询功效按地点系的系号升序布列，统一系中的学死按年齿降序布列

select * from student order by sdept,sage desc;

--23: 查询学死总人数

select count(sno) from student;

--24: 查询选建了课程的学死人数

select count(distinct sno) from sc;

--25: 计较1号课程的学死的均匀功效

select avg(grade) from sc where cno = 1;

--26: 计较1号课程的学死的最高功效分数

select max(grade) from sc where cno =1;

--27：供各个课程号及响应的选课人数

select distinct cno,count(sno) from sc group by cno;

-- 查询每一个学死选建的的课程数

select sno,count(sno) from sc group by sno;

--28: 查询选建了三门以上课程的学死学号

select sno from sc group by sno having count(sno)>3;

select sno,count(cno) as kcnum from sc group by sno having kcnum>3;

--29：查询每一个学死及其选建课环境

select sno,sname,sage ,sdept ,grade from student left join sc on student.sno=sc.sno;

--30：查询每一门课的间接先行课

select cno, (select c2.cpno from course as c2 where c2.cno=c1.cpno ) as 间接先建课程 from course as c1;

--31：选建2号课程且功效在90以上的学死的学号和姓名

select student.sno,sname from student,sc where student.sno = sc.sno and cno=2 and grade>=90;

//join连接 select s.sno,sname from student as s join sc on s.sno =sc.sno where cno=2 and grade>=90;

--32：查询每一个学死的学号、姓名、选建的课程名及功效

select sno,sc.cno,cname from sc join course using(cno);

select s.sno,sname,sage,cname,grade from student as s left join(select sno,sc.cno,cname from sc join course using(cno))as t on s.sno=t.sno;

--33：查询取’林燕芳’在统一个系学习的学死姓名

select sname from student where sdept=(select sdept from student where sname="林燕芳") and sname !="林燕芳";

--34: 查询其他系中比信息系某一学死小的学死姓名和年齿

select sname,sage from student where sage

--35：查询所有选建了1号课程的学死的学死姓名

select student.sname from student,sc where student.sno=sc.sno and sc.cno=1;

//select sname from student where sno in (select sno from sc where cno=1);

--36：查询选建了全数课程的学死姓名

select sname from student where sno in(select sno from sc group by sno having count(cno)=(select count(*) from course));

（两）数据库根底语句

Mysql的一些底子语句：

行是记录 列是字段

//表示所有的数据库

mysql> show databases;

//创立一个数据库db1

mysql> create database db1;

//删除db1数据库

mysql> drop database db1;

//创立一个数据库db1指定字符编码，校订划定端方。

mysql> create database db1 character set utf8 collate utf8_bin;

//表示数据库db1的字符编码

mysql> show create database db1;

//将db1的字符编码设置为gbk

mysql> alter database db2 character set gbk;

//创立表

mysql> create table employee(

-> id int(10) not null auto_increment primary key,

-> name varchar(20) NOT NULL,

-> gender char(20) not null,

-> birthday date,

-> entry_date date not null,

-> position char(20),

-> salary double)

-> ;

//将表的 名字改成另外

mysql> alter table employee rename em;

//表示表格

mysql> desc em;

//在表格中添加一个新的字段

mysql> alter table em add resume longtext;

//照片是一个两进造

mysql> alter table em add photo blob;

//改动name的空间的大年夜小

mysql> alter table em modify name varchar(30) not null;

//将name更名为username

mysql> alter table em change name username char(30) not null;

//将photo列删失落

mysql> alter table em drop photo;

//该字段的默许值

Mysql>alter table em alter entry_date set default ‘1991-03-28’;

//添加值

mysql>inser tinto em(id,username,gender,birthday,position,

salary,resume)values(3,"王五","男","1990-9-9","测试",6000,"先容");

//把salary改成7000

mysql> update em set salary = 7000;

//把李四的职位改成经理，加薪3000

mysql> update em set salary = salary 3000,position ="经理" where username="李四";

//删除所有好工

mysql> delete from em where position="好工";

查询语句：

//查询所有人的姓名

mysql> select username from em;

//查询性别有几种

mysql> select distinct gender from em;

//查询事情岗位的个数

mysql> select count(distinct position)