mysql实战-常用查询

2017-01-14 16:47
  • 创建数据库
Create DATABASE database-name 
  • 删除数据库
drop database dbname 
  • 创建新表
CREATE TABLE Persons
(
Id_P INT NOT NULL AUTO_INCREMENT,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
  • 删除表
drop table tabname 
  • 新增一列
Alter table tabname add column col type 
  • 插入数据
insert into table1(field1,field2) values(value1,value2) 
  • 删除数据
delete from table1 where id=1
  • 更新
update table1 set field1=value1,field2=value2 where 范围 
  • 模糊查找
select * from table1 where field1 like '%value1%' or field2 like '%value2%'

下面说下多种查询

表结构如下:

  • 学生资料表

Paste_Image.png

  • 学生的成绩表,sid就是学生资料表的外键

Paste_Image.png

分数最高的学生信息,科目

方法一:左连接
select s.course as 科目,max(s.score1) as 最高分,c.name as 姓名 from score s left join student c on c.id = s.sid group by s.course;

方法二:子查询
select course as 科目,max(score1) as 最高分,(select name from student c where c.id=s.sid) as 姓名 from score s group by course;

Paste_Image.png

科目评价为良好,性别为女的学生信息,科目

select s.score1,s.course,c.name,c.sex  from score s join student c on  s.sid=c.id where s.comment='良好' and c.sex='女';

Paste_Image.png

最低分数性别为男和女的信息

select course,min(score1) as 最低分,c.sex,c.name from score s join student c on s.id=c.id and c.sex='男' or sex='女' group by course; 

Paste_Image.png

每科的前两名学生信息

select course,score1, c.name from score s1 left join student c on c.id=s1.sid where 2>(select count(*) from score s where s.course = s1.course and s.score1>s1.score1)  order by s1.course,s1.score1  

Paste_Image.png

还有其他的两种方法可以取前两名学生数据:

方法二:select a.id,a.SName,a.ClsNo,a.Scorefrom Table1 a left join Table1 b on a.ClsNo=b.ClsNo and a.Score<b.Scoregroup by a.id,a.SName,a.ClsNo,a.Scorehaving count(b.id)<2order by a.ClsNo,a.Score desc

方法三:select *from Table1 awhere id in (select id from Table1 where ClsNo=a.ClsNo order by Score desc limit 2)order by a.ClsNo,a.Score desc
  • 查询出2门及2门以上不及格者的平均成绩
select name,sum(score < 60) ,avg(score) from result group by name having sum(score<60) >=2; 

distinct的用法

  • 单列
select distinct name from A
  • 多列
select distinct name, id from A
  • COUNT结合
select count(distinct name) from A;   --表中name去重后的数目, SQL Server支持,而Access不支持

count是不能统计多个字段的,若想使用,请使用嵌套查询,如下

select count(*) from (select distinct xing, name from B) AS M;
  • distinct必须放在开头
select id, distinct name from A;   --会提示错误,因为distinct必须放在开头