select下拉框带模糊查询_SQL简单查询操作(NO.2)

一：基本的查询语句

1.从学生表查询姓名和性别列

select 姓名,性别
from student;

从学生表查询姓名和性别查询结果

2.查询全部列

select *
from student;

全部列查询结果

3.为列设定别名as

select 姓名 as s_name,性别 as '人类性别'
from student;

设定列名：人类性别（性别）

4.删除重复数据distinct

select distinct 姓名
from student;

删除重复数据查询结果

二：指定查询条件

1.选取‘姓名’列里值为‘猴子’的行

select 姓名,学号
from student
where 姓名='猴子';

查询条件：姓名是猴子的姓名和学号查询结果

三：单行注释及多行注释

单行注释符与注释之间要留一个空格

四：运算符（算术运算符/比较运算符/逻辑运算符/null值）

1.算术运算符

select 学号,成绩,
成绩/100 as '百分比成绩'
from score;

通过算术运算符重新定义列（百分比成绩）查询结果

2.比较运算符

select 姓名,学号
from student
where 姓名='猴子';

选取姓名和学号，姓名为猴子的查询结果

select 学号,成绩
from score
where 成绩<60;

在成绩表查找成绩低于60的学号和成绩查询结果

select 姓名,出生日期
from student
where 出生日期<'1990-01-01';

在学生表查找出生日期在1989-01-01之前的姓名和出生日期查询结果

select 学号,成绩
from score
where not 成绩>=60;
select 学号,成绩
from score
where 成绩<60;

not逻辑运算符在成绩表查询成绩非大于等于60的学号和成绩/在成绩表查询成绩小于60的学号和成绩

select 学号,成绩
from score
where 成绩>=60
and 成绩<=90;

在成绩表查询成绩在60和90之间的学号和成绩查询结果

3.逻辑运算符

select 姓名,性别
from student
where 性别='男'
and (姓名='猴子' or 姓名='马云');

查询条件：性别是‘男’并且姓名是猴子或者马云

-- 查询范围：between
select 学号,成绩
from score 
where 成绩 between 60 and 90;

在成绩表查询成绩在60和90之间的学号和成绩查询结果

-- OR
select 学号,成绩
from score
where 成绩<60
or 成绩>90;

在成绩表查询成绩小于60或者成绩大于90的学号和成绩

-- in是or的简单写法
select 姓名,性别
from student 
where 姓名 in('猴子','马云');

in里面满足猴子或者马云其中之一即可

select 姓名,性别
from student 
where 姓名 not in('猴子','马云');

查询条件：姓名不在猴子或者马云里

4.查询null值

查询null值及非null值的sql语句

查询null值结果

五：字符串模糊查询

1.查询姓“猴”的学生名单

-- 查询姓“猴”的学生名单
select *
from student
where 姓名 like "猴%";

2.查询姓名最后一个字是“猴”的学生名单

-- 查询姓名最后一个字是“猴”的学生名单
select *
from student
where 姓名 like "%猴";

查询结果

3.查询姓名带“猴”的学生名单

-- 查询姓名带“猴”的学生名单
select *
from student
where 姓名 like "%猴%";

查询结果

4.查询姓’王‘的学生名单， 并且姓名是3个字符的

/*
查询姓’王‘的学生名单，
并且姓名是3个字符的
*/
select *
from student
where 姓名 like'王__';

查询结果

六：以sqlzoo官网案例为例,使用简单查询sql语句

1.The example uses a WHERE clause to show the population of 'France'. Note that strings (pieces of text that are data) should be in 'single quotes';

Modify it to show the population of Germany

select population
from world
where name='Germany';

2.Checking a list The word IN allows us to check if an item is in a list. The example shows the name and population for the countries 'Brazil', 'Russia', 'India' and 'China'.

Show the name and the population for 'Sweden', 'Norway' and 'Denmark'.

select name,population
from world
where name in ('Sweden','Norway','Denmark');

3.Which countries are not too small and not too big?BETWEENallows range checking (range specified is inclusive of boundary values). The example below shows countries with an area of 250,000-300,000 sq. km. Modify it to show the country and the area for countries with an area between 200,000 and 250,000.

select name,area
from world
where area between 200000 and 250000;

4.Change the query shown so that it displays Nobel prizes for 1950.

SELECT yr, subject, winner
  FROM nobel
 WHERE yr = 1950;

5.Show who won the 1962 prize for Literature.

SELECT winner
  FROM nobel
 WHERE yr = 1962
   AND subject = 'Literature';

6.Show the year and subject that won 'Albert Einstein' his prize.

select yr,subject
from nobel
where winner='Albert Einstein';

7.Give the name of the 'Peace' winners since the year 2000, including 2000.

select winner
from nobel
where yr>=2000
and subject='Peace';

8.Show all details (yr,subject,winner) of the Literature prize winners for 1980 to 1989 inclusive.

select *
from nobel
where subject='Literature'
and yr between 1980 and 1989;

9.Show all details of the presidential winners:

• Theodore Roosevelt
• Woodrow Wilson
• Jimmy Carter
• Barack Obama

SELECT * FROM nobel
 WHERE winner IN ('Theodore Roosevelt','Woodrow Wilson','Jimmy Carter','Barack Obama');

10.Show the winners with first name John

select winner
from nobel
where winner like 'John%';