SQL查询语句详解（一）

一、基本语法

Select  select_list
From  table_name
Where  condition_expression
Group by  group_columns   
having condition_expression
Order by sort_columns

二、查询实例

• 查询所有字段
• 查询指定字段
• 用DISTINCT去除结果中的重复行
• 查询指定数据
• 带有in关键字的查询
• 带between and的范围查询
• 带like的字符匹配查询
  表1：学生
• 结构
  
• 数据
  
  表2：商品表1
• 结构：
  
• 数据：
  
  表3：商品表2
• 结构：
  
• 数据
  
  表4：教师
• 结构：
  
• 数据
  
  
• 数据分析需求

1）查询所有学生的详细信息

select * from 学生;

2）查询学生的学生号，姓名与专业

select 学生号,姓名,专业 from 学生;

3）从商品表1中查询出每一种商品的价值

select 商品代号,单价,数量,单价*数量 as 总价 from 商品表1;

4）从商品表1中查询每种商品的分类名 去除重复项

select distinct 分类名 from 商品表1;

5）从商品表1中查询出分类名为"电视机"的所有商品

select * from 商品表1 where 分类名 ='电视机';

6）从商品表1中查询出单价低于2000元的每一种商品代号，分类名和单价

select 商品代号,分类名,单价 from `商品表1`  where 单价<2000;

7）从商品表2中查询出产地为北京、山西、无锡的所有商品

select * from 商品表2 where 产地='北京' or 产地='山西' or 产地='无锡';
select * from 商品表2 where 产地 in('北京','山西','无锡');

8）从商品表1中查询出商品代号以字符串"dsj"开头的所有商品 （考察模糊查询like以及通配符）

select * from 商品表1 where 商品代号 like 'DSJ%';

9）从商品表1中查询单价在1000到2000之间的所有商品

select * from 商品表1 where 单价 between 1000 and 2000;

10）查询所有姓张的学生的详细信息

select * from 学生 where 姓名 like '张%';

11）从商品表1中查询出单价大于1500,同时数量大于等于10的商品

select * from 商品表1 where 单价>1500 and 数量>=10;

12）从教师表中查询联系电话为空的教师信息

select * from 教师 where 联系电话 is null;

13）从选课表中查询按课程号的升序，同一课程按成绩降序排列

select*from 选课 order by 课程号 asc, 课程号 desc;

14）从学生表中查询各个专业的人数

select 专业,count(*) as 人数 from 学生 group by 专业

15）从学生表中查询出专业的学生数多于1人的专业名及人数

select 专业,count(*) as 人数 from 学生 group by 专业 having count(*)>1
select 专业,count(*) as 人数 from 学生 group by 专业 having 人数>1

16）从选课表中查询成绩最高前三位

select * from 选课 order by 成绩 desc limit 3

17）查询各个专业男女人数各是多少

select 专业,性别,count(*) as 人数 from 学生 group by 专业,性别

18）从商品表1中查询出所有商品的最大数量、最小数量、平均数量及数量总和

select sum(数量) from 商品表1;
select max(数量) from 商品表1;
select min(数量) from 商品表1;
select avg(数量) from 商品表1;

19）从商品表1中查询出分类名为“电视机”的商品的种数、最高价、最低价及平均价

Select sum(数量) from 商品表1 where 分类名='电视机';
Select avg(数量) from 商品表1 where 分类名='电视机';
Select min(数量) from 商品表1 where 分类名='电视机';
Select max(数量) from 商品表1 where 分类名='电视机';

20）查询出产地为南京或无锡的所有商品的商品代号、分类名、产地和品牌

20）查询出产地为南京或无锡的所有商品的商品代号、分类名、产地和品牌

select * from 商品表1 inner join 商品表2 where 产地 in('南京','无锡');

或

select * from 商品表1 inner join 商品表2 where 产地='南京' or 产地='无锡';

员工表中含有佳字的

select * from 员工 where 姓名 like '%佳%';

员工表中含有佳字的两个子长度的员工信息

select * from 员工 where 姓名 like '_佳' or 姓名 like '佳_';

查询本公司有多少个部门

select distinct 部门 from 员工;

请列出家住北京市的生产制造部的家庭名单住址；

select 姓名,家庭住址 from 员工 where 部门='生产制造部' and 家庭住址 like '%北京市%';

请查询出生成制造部女性未婚员工的基本信息

select * from 员工 where 部门='生产制造部' and 性别=-1 and 婚姻状况=-1;

显示生产制造部员工姓名年龄 部门和目前薪资

select 姓名,year(now())-year(出生日期) as 年龄,部门,目前薪资 from 员工 where 部门='生产制造部';

查询出生产制造部中员工年龄大于40岁的员工

select 姓名,year(now())-year(出生日期) as 年龄,部门,目前薪资 from 员工 where 部门='生产制造部' and

year(now())-year(出生日期)>40;

查询入职日期超过三十年的老家伙

select 姓名,year(now())-year(雇佣日期) as 工龄,部门,目前薪资 from 员工 where 
year(now())-year(雇佣日期)>=30;
显示出订货明细表中每个订单的订单号码 产品编号和时间销售额

显示出订货明细表中每个订单的订单号码 产品编号和时间销售额

select 订单号码,产品编号,(单价*数量*(1-折扣)) as 实际销售额 from 订货明细;

查询已婚员工信息 显示 员工编号姓名性别显示出男或女 以及员工婚姻状况

SELECT
	员工编号,姓名,
CASE
		性别 
		WHEN - 1 THEN
		'女' 
		WHEN 0 THEN
		'男' ELSE '不详' 
	END 性别,
CASE 婚姻状况 
	WHEN - 1 THEN
	'未婚' 
	WHEN 0 THEN
	'已婚' ELSE '不详' 
END 婚姻状况
FROM
	员工;
	

SELECT
	员工编号,姓名,
CASE
		性别 
		WHEN - 1 THEN
		'女' 
		WHEN 0 THEN
		'男' ELSE '不详' 
	END 性别,
CASE 婚姻状况 
	WHEN - 1 THEN
	'未婚' 
	WHEN 0 THEN
	'已婚' ELSE '不详' 
END 婚姻状况
FROM
	员工 where CASE 婚姻状况 
	WHEN - 1 THEN
	'未婚' 
	WHEN 0 THEN
	'已婚' ELSE '不详' 
END='已婚';

目录

1、插入数据

1.1 为表的所有字段插入数据

1、insert语句中不指定具体的字段名

2、insert语句中列出所有字段

1.2 为表的指定字段插入数据

1.3 同时插入多条记录

1.4 将查询结果插入到表中

2、更新数据

3、删除数据

数据库通过插入、更新和删除等方式来改变表中的记录。插入数据是向表中插入新的记录，通过 INSERT 语句来实现。更新数据是改变表中已经存在的数据，使用UPDATE 语句来实现。删除数据是删除表中不再使用的数据，通过DELETE 语句来实现。

1、插入数据

插入数据是向表中插入新的记录。通过这种方式可以为表中增加新的数据。在 MySQL中，通过INSERT语句来插入新的数据。使用INSERT 语句可以同时为表的所有字段插入数据，也可以为表的指定字段插入数据。INSERT语句可以同时插入多条记录，还可以将一个表中查询出来的数据插入到另一个表中。

1.1 为表的所有字段插入数据

通常情况下，插入的新记录要包含表的所有字段。INSERT 语句有两种方式可以同时为表的所有字段插入数据。第一种方式是不指定具体的字段名;第二种方式是列出表的所有字段。

1、insert语句中不指定具体的字段名

在 MySQL中，可以通过不指定字段名的方式为表插入记录。其基本语句形式如下:

insert into 表名 values(值1，值2，...，值n);

其中，“表名”参数指定记录插入到哪个表中;“值n”参数表示要插入的数据。“值1”到“值n”分别对应着表中的每个字段。表中定义了几个字段，INSERT语句中就应该对应有几个值。插入的顺序与表中字段的顺序相同。而且，取值的数据类型要与表中对应字段的数据类型一致。

下面向S表中插入记录。插入记录之前，可以通过DESC语句来查看S表的基本结构。查询结果如下:

从查询结果可以看出，S表包含4个字段。那么INSERT 语句中的值也应该是4个。INSERT语句的代码如下:

 ！！！字符串类型的数据，取值必须加上引号。

2、insert语句中列出所有字段

INSERT语句中可以列出表的所有字段，为这些字段来插入数据。其基本语句形式如下:

insert into 表名（属性1，属性2，...，属性n）
values (值1，值2，...，值n);

其中，“属性n”参数表示表中的字段名称，此处必须列出表的所有字段的名称;“值n”参数表示每个字段的值，每个值与相应的字段对应。

下面向S表中插入一条新记录。INSERT 语句的代码如下:

查询结果显示，记录已经插入成功。如果表的字段比较多，用第二种方法就比较麻烦。但是，第二种方法比较灵活。可以随意的设置字段的顺序，而不需要按照表定义时的顺序。值的顺序也必须跟着字段顺序的改变而改变。

下面向S表中插入一条新记录。INSERT语句中字段的顺序与表定义时的顺序不同。INSERT语句的代码如下:

1.2 为表的指定字段插入数据

其基本语句形式如下:

insert into 表名(属性1，属性2,…,属性m)
values(值1,值2,.…,值m);

其中，“属性m”参数表示表中的字段名称，此处指定表的部分字段的名称;“值m”参数表示指定字段的值,每个值与相应的字段对应。

下面向S表的sno和sname这2个字段插入数据。INSERT语句的代码如下:

这种方式也可以随意的设置字段的顺序，而不需要按照表定义时的顺序。

！！！没有赋值的字段，数据库系统会为其插入默认值。这个默认值是在创建表的时候定义的。如上面function字段和address字段的默认值为NULL。如果某个字段没有设置默认值，而且是非空，这就必须为其赋值。不然数据库系统会提示“Field'name' doesn't have a default value”这样的错误。

1.3 同时插入多条记录

同时插入多条记录，是指一个 INSERT语句插入多条记录。当用户需要插入好几条记录，用户可以使用上面两个小节中的方法逐条插入记录。但是，每次都要写一个新的INSERT 语句。这样比较麻烦。MySQL 中，一个 INSERT语句可以同时插入多条记录。其基本语法形式如下:

insert into 表名[(属性列表)】
values (取值列表1),(取值列表2),
...,
(取值列表n);

其中，“表名”参数指明向哪个表中插入数据;“属性列表”参数是可选参数，指定哪些字段插入数据，没有指定字段时向所有字段插入数据;“取值列表n”参数表示要插入的记录，每条记录之间用逗号隔开。

向MySQL的某个表中插入多条记录时，可以使用多个INSERT语句逐条插入记录，也可以使用一个 INSERT语句插入多条记录。选择哪种方式通常根据个人喜好来决定。如果插入的记录很多时，一个INSERT 语句插入多条记录的方式的速度会比较快。

下面向S表中插入3条新记录。INSERT 语句的代码如下:

不指定字段时，必须为每个字段都插入数据。如果指定字段，就只需要为指定的字段插入数据。

1.4 将查询结果插入到表中

INSERT语句可以将一个表中查询出来的数据插入到另一表中。这样，可以方便不同表之间进行数据交换。其基本语法形式如下:

insert into 表名1(属性列表1)
select 属性列表2 from 表名2 where 条件表达式;

其中，“表名1”参数说明记录插入到哪个表中;“表名2”参数表示记录是从哪个表中查询出来的;“属性列表1”参数表示为哪些字段赋值;“属性列表2”表示从表中查询出哪些字段的数据;“条件表达式”参数设置了SELECT 语句的查询条件。

使用这种方法时，必须保证“字段列表1”和“字段列表2”中的字段个数是一样的。而且，每个对应的字段的数据类型是一样的。如果数据类型不一样，数据库系统会报错。然后，阻止INSERT语句向下执行。

下面将s_temp表中所有数据查询出来，然后插入到S表中。插入之前，先执行SELECT语句来查看s_temp表中的记录。SELECT语句执行结果如下:

2、更新数据

更新数据是更新表中已经存在的记录。通过这种方式可以改变表中已经存在的数据。例如，学生表中某个学生的家庭住址改变了，这就需要在学生表中修改该同学的家庭地址。在MySQL中，通过UPDATE语句来更新数据。

在 MySQL中，UPDATE语句的基本语法形式如下:

update 表名
set 属性名1=取值1,属性名2=取值2,
...,
属性名n=取值n
where 条件表达式;

其中，“属性名n”参数表示需要更新的字段的名称;“取值 n”参数表示为字段更新的新数据;“条件表达式”参数指定更新满足条件的记录。

下面更新s表中sno值为s1的记录。将sname字段的值变为‘精益求精‘，city的值变为’北京‘。

表中满足条件表达式的记录可能不止一条。使用UPDATE语句会更新所有满足条件的记录。

下面更新S表中 sno值为s100-s102的记录。将status字段的值变为30。将city字段的值变为“天津”。

！！！使用UPDATE语句更新数据时，可能会有多条记录满足WHERE条件。这个时候一定要特别小心,最好先执行SELECT语句判断满足WHERE条件的记录是否确实是需要更新的。如果其中有些记录不需要更新，应该重新设置WHERE条件.

3、删除数据

删除数据是删除表中已经存在的记录。通过这种方式可以删除表中不再使用的记录。例如，学生表中某个学生退学了，这就需要从学生表中删除该同学的信息。MySQL中，通过DELETE 语句来删除数据。

MySQL中，DELETE语句的基本语法形式如下:

delete from 表名[where条件表达式】;

其中，“表名”参数指明从哪个表中删除数据;“WHERE条件表达式”指定删除表中的哪些数据。如果没有该条件表达式，数据库系统就会删除表中的所有数据。

下面删除s表中sno值为s1的记录。

DELETE语句可以同时删除多条记录。

下面删除s表中 city值为‘北京’的记录。

DELETE语句中如果不加上“WHERE条件表达式”，数据库系统会删除指定表中的所有数据。

下面删除s表中的所有记录。

因为执行DELETE语句后，会将记录从表中删除。删除过程中不会有任何提示，因此必须要特别小心。在条件允许的情况下，最好先用SELECT语句查询准备删除的记录。这样可以确认这些记录确实是需要删除的。

数据库sql语句及字段名不区分大小写，值必须一一对应

1.创建三张表，学生表、选课表、成绩表

CREATE TABLE student
 (Sno varchar(20) NOT NULL,
 Sname varchar(20) NOT NULL,
  sex varchar(20) NOT NULL, 
  age INT NOT NULL, 
  dept varchar(20) NOT NULL, PRIMARY KEY (Sno) 
  );
CREATE TABLE course
(Cno    varchar(20)    NOT NULL,
 Cname  varchar(20)    NOT NULL,
 hours VARCHAR(20)    NOT NULL,
 PRIMARY KEY (Cno)
);
CREATE TABLE SC
(Sno    varchar(20)    NOT NULL,
 Cno    varchar(20)    NOT NULL,
 grade  INT    ,
 PRIMARY KEY (Sno,Cno)
);

2.在三张表中分别添加数据

INSERT INTO student VALUES ('9512101','李勇','男',19,'计算系');
INSERT INTO student VALUES ('9512102','刘晨','男',20,'计算系');
INSERT INTO student VALUES ('9512103','王敏','女',20,'计算系');
INSERT INTO student VALUES ('9521101','张立','男',22,'信息系');
INSERT INTO student VALUES ('9521102','吴宾','女',21,'信息系');
INSERT INTO student VALUES ('9521103','张海','男',20,'信息系');
INSERT INTO student VALUES ('9531101','钱小力','女',18,'数学系');
INSERT INTO student VALUES ('9531102','王大力','男',19,'数学系');

INSERT INTO course VALUES ('C01','计算机文化学','70');
INSERT INTO course VALUES ('C02','VB','90');
INSERT INTO course VALUES ('C03','计算机网络','80');
INSERT INTO course VALUES ('C04','数据库基础','108');
INSERT INTO course VALUES ('C05','高等数学','180');
INSERT INTO course VALUES ('C06','数据结构','72');

INSERT INTO SC VALUES ('9512101','C01',90);
INSERT INTO SC VALUES ('9512101','C02',86);
INSERT INTO SC VALUES ('9512101','C06',NULL);
INSERT INTO SC VALUES ('9512102','C02',78);
INSERT INTO SC VALUES ('9512102','C04',66);
INSERT INTO SC VALUES ('9521102','C01',82);
INSERT INTO SC VALUES ('9521102','C02',75);
INSERT INTO SC VALUES ('9521102','C04',92);
INSERT INTO SC VALUES ('9521102','C05',50);
INSERT INTO SC VALUES ('9521103','C02',68);
INSERT INTO SC VALUES ('9521103','C06',NULL);
INSERT INTO SC VALUES ('9531101','C01',80);
INSERT INTO SC VALUES ('9531101','C05',95);
INSERT INTO SC VALUES ('9531102','C05',85);
commit;

3.通过SQL语句对表中的数据进行查询

（1）分别查询学生表，学生修课表和选课表中的全部数据

select * from course
select * from student
SELECT * from sc

（2）查询成绩在70-80分之间的学生的学号、课程号和成绩

select * from sc where grade >=70 and grade <=80;

（3）查询C01号课程成绩最高的分数（max()求最大值）、（group by 通过与聚合函数共同使用，实现数据分组

select max(grade) from sc where cno='C01' group by cno;

扩展：在某一个班的成绩表(Id,name,course,grade)中查找出班级前十名学生的id、name和总成绩

select sno, sum(grade) total 
from sc 
group by sno order by total desc limit 5

select sc.sno, s.sname, sum(sc.grade) total 
from student s, sc 
where s.sno = sc.sno 
group by sc.sno  
order by total desc 
limit 5

（4）查询学生都选修了哪些课程，要求列出课程号。（group by实现对查询出来的数据进行分组）

select cno from sc group by cno

(5)查询C02号课程的所有学生的平均成绩、最高成绩和最低成绩。

select max(grade),min(grade),avg(grade) 
from sc 
where cno='C02' 
group by cno;

(6)统计每个系的学生人数

select dept, count(*) as '人数' 
from student 
group by dept;

(7)统计每门课程的修课人数和考试最高分

select count(Sno)  '人数',max(grade) 
from sc 
group by cno;

(8)统计每个学生的选课门数，并按选课门数的递增顺序显示结果

SELECT COUNT(Cno) score 
from sc 
GROUP BY Sno 
ORDER BY  score  desc 
LIMIT 3

(9)统计选修课的学生总数和考试的平均成绩

select count(sno),avg(grade) 
from sc 
group by cno 
ORDER BY avg(grade) DESC;

(10)查询选课门数超过2门的学生的平均成绩和选课门数(having只用于group by语句；having可以用于聚合函数，但是where不能；where只用于初始表中帅选查询，having用于在where和group by结果中查询）。

SELECT AVG(grade) avg, COUNT(cno) cnt 
from sc 
group by sno 
having cnt > 2

(11)列出总成绩超过200分的学生，要求列出学号、总成绩

select sno , sum(grade) cnt 
from sc 
GROUP BY sno 
having cnt >= 200

(12)查询选修了C02号课程的学生的姓名和所在系

SELECT s.sname, s.dept 
from student s,  sc 
where sc.cno='C02' 
  and s.sno = sc.sno

(13)查询成绩80分以上的学生的姓名、课程号和成绩，并按成绩的降序排列结果。（排序使用order by，DESC代表逆序，不使用为默认顺序排序）

select s.sname, sc.cno, sc.grade 
from student s, sc 
where sc.grade > 80 
   and s.sno = sc.sno 
ORDER BY sc.grade desc

(14)查询计算机系男生修了‘数据库基础’的学生姓名、性别、成绩

SELECT s.sname, s.sex, sc.grade 
from student s, sc, course c 
WHERE s.dept = '计算系' and s.sex = '男' 
 and c.cname = '数据库基础' 
  and sc.Sno = s.sno 
  and sc.Cno = c.cno

• （15）查询哪些学生的年龄相同，要求列出年龄相同的学生的姓名和年

select sname, age 
from student 
where age in  -- 子查询 in
(select age from student group by age having count(age) > 1)

• （16）查询哪些课程没有人选，要求列出课程号和课程名

select cno, cname 
from course 
where cno not in (select cno from sc group by cno)  -- not in

• （17）查询有考试成绩的所有学生的姓名、修课名称及考试成绩。
  要求将查询结果放在一张新的永久表(假设新表名为new-sc)中

create table new_sc as    -- 将查询结果放在新表中的方法。as可省略
select s.sname, c.cname, sc.grade 
from student s, course c, sc
where s.sno = sc.sno
  and c.cno = sc.cno
  and sc.grade is not null   -- is not null 是不空的

• （18）分别查询信息系和计算机系的学生姓名、性别、修课名称、修课成绩（联合查询使用union）

select s.sname, s.sex, c.cname, sc.grade 
from student s, course c, sc
where s.dept = '信息系'
  and s.sno = sc.sno 
  and c.cno = sc.cno 
UNION  -- 联合查询
select s.sname, s.sex, c.cname, sc.grade 
from student s, course c, sc
where s.dept = '计算系'
  and s.sno = sc.sno
  and c.cno = sc.cno

select s.sname, s.sex, c.cname, sc.grade 
from student s, course c, sc
where s.dept in ('信息系', '计算系')  -- 并列
  and s.sno = sc.sno 
  and c.cno = sc.cno 

参考：
数据库查询语句（从简单到复杂）