mysq之select基础查询

select

基础查询

1. select 列表
   

select 列名 from 表名

SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[LIMIT N][ OFFSET M]

• 查询语句中你可以使用一个或者多个表，表之间使用逗号(,)分割，并使用WHERE语句来设定查询条件。

• SELECT 命令可以读取一条或者多条记录。

• 你可以使用星号（*）来代替其他字段，SELECT语句会返回表的所有字段数据

• 你可以使用 WHERE 语句来包含任何条件。

• 你可以使用 LIMIT 属性来设定返回的记录数。
  *你可以通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。

• 可以使用like来进行模糊查询,like的搭配使用 %代表多个字符, _ 代表单个字符

• order by 分组查询， order by 搭配desc 降序，默认 asc升序

  2. 特点

1 查询列表可以时字段，常量，表达式，函数，也可以时多个
2 查询结果时虚拟集

3. 示例

   1 :查询单个字段

select 字段名 from 表名

2: 查询多个字段
select 字段名，字段名 from 表名

3: 查询所有字段
select * from 表名

4: 查询常量
select 常量
注意：字符型和日期型的常量必须用单引号引起来，数值型不要
select 80;

5: 查询函数
select 函数名（实例列表）

6:查询表达式
select 100/1234

7 起别名
1 : as
select last_name as name,first_name as 名 from employee;

2: 空格
省略as，字段和别名中间留一个空格

as使用注意点

as 的别名使用可以使用在from后查询的原始表,不可以使用在查询原始表时，因为原始表内是没有as的别名，查询原始表后的语句 是有别名的 那么就可以使用


as使用举例
#查询员工最高工资和最低工资的差距（DIFFERENCE）
select MAX(salary) as gao, MIN(salary) as di, ( ‘gao’ - ‘di’) DIFFERENCE from employees; 这里是不可以的 因为 gao 和di 字段在原始表是不存在的 是无法使用的 如果使用在查询后的原始表是可以的 因为查询原始表后就有了起的别名


正确使用
select MAX(salary) 最高薪资, MIN(salary) 最低薪资, (MAX(salary) - MIN(salary)) DIFFERENCE from employees

8:去重 distinct
select distinct 字段名 from 表名;
只需要在想要去掉重复的字段前加上DICTINCT关键字即可

为表起别名

表起别名和字段起别名时一样的，在表名后加上空格和别名或者as 加别名

表起别名好处

提高语句的简洁度
区分多个重名的字段

• 注意：如果为表起了别名， 则查询的字段就不能用原来的表名去限定了
  *如下
  

• 正确的用法

select last_name, e.job_id,jobs.job_title from employees as e, jobs where e.job_id = jobs.job_id;

• 表起了别名可以理解为原表名已经没有了 所有的使用都要时表的别名

---

dictinct 去重department_id字段

9:+号 的作用
java中+号的作用
1 运算符 ：两个操作都是数值型的
2 :连接符 之哟啊一个操作符为字符串的
mysql中的+
仅仅只有一个功能：运算符
select 100+90;两个操作数都为数值型，则做加法运算
select ‘123’+90; 其中一方为字符型，试图将字符型转化为数值
如果转换成功则继续做加法，如果转换失败则字符型数值转化为0
select null+值 ; 结果都是null

concat函数
功能：拼接字符
select concat(字符1,字符2,字符3);

查询单列	select 列表from 列名	select name from employees; 查询emploees 表中的所有人的名字
查询多个字段	select 字段,字段,字段 from employees;	select id,name,info from employees;
查询所有字段 * 代替所有	select * from 表名;	select * from employees; 查询employees内的所有的信息
查询常量	select 常量值 from 表名;	select 90 from employees;
查询函数	select 函数名(实例列表)
查询表达式	select 100/24
–	–
起别名 as 或者空格	select 字段 as 别名 from 表名	select name as 姓名 from employees; 或者 select name 别名 from employees;
去重 distinct	select distinct 字段 from 表名	select distinct id from employees; 查询employees表并且对id进行去重
+ 加法运算	select 数值+数值	select id + age from employees；
select 字符+数值;先试图将字符转换成数值，如果转换成功，则继续运算；否则转换成0，再做运算	select null+值;结果都为null
concat函数 拼接字符	select concat(字段1，字段2，字段3) from 表名	select concat(employee_id,first_name,last_name) 别名 from employees;
ifnull函数 判断某字段或者表达式是否为null，如果为null，指定返回值，否则返回原本的值	select ifnull(字段,要返回的信息) from 表名	select ifnull(manager_id, ‘这是空’) from employees; select ifnull(department_id, “这是部门id”) 部门信息 from employees;
isnull 函数 判断是否为null，如果是返回1否则返回0	select isnull(字段) from employees;	select isnull(department_id) 部门id from employees; 查询部门id是否为null

---

子查询： where

语法:
select 查询列表
 from
     表名
where
     筛选条件；

筛选条件分类

1. 一按条件表达式筛选

   简单条件运算符 :  >, <, =, !=, <>, >=, <=。 大于，小于，等于，不等于，不等于，大于等于，小于等于 
   

2. 按逻辑表达式筛选

   a . 逻辑运算符
   作用:用于连接条件表达式

   && || !
   and or not
   && 和and ：两个条件都是True 结果都为true 反之为false
   ||和or ： 只要两个有一个条件为true，结果为true，反之为false
   !或not ： 如果连接的条件本身为false,结果为true反之为false
   建议使用 ! not or

• 查询部门编号不等于90号的员工名和部门编号

  c : 模糊查询 :like 一般搭配通配符使用，可以判断字符型或数值型
  

通配符：%任意多个字符，_ 任意单个字符

is  NULL  : 仅仅可以判断null值，可读性较高，建议使用
<=> 安全等于： 既可以用判断null值，又可以判断普通的数值，可读性较低

sql中null的判断用is 或者not is

	不等于null  ： not is null
	等于null :          is null
	eg: num is null; num等于空
		num not is null； num不等于空

查询模式	条件
条件运算符	>, <, =, !=, <>, >=, <=
–	–
逻辑运算筛选	&& ,! and ,or , not
模糊查询	like ,between…and, in, is null, is not null

• 其中between … and是闭区间，null只能和is ，is not 一起使用

	is  NULL  : 仅仅可以判断null值，可读性较高，建议使用
><=> 安全等于： 既可以用判断null值，又可以判断普通的数值，可读性较低

eg: 建立一个表studentOne

create table studentOne (
id int primary key auto_increment Comment “id”,
name varchar(32) COMMENT “姓名”,
chinese float COMMENT ‘语文课’,
english float COMMENt “英语课”,
math float COMMENt “数学课”
) charset = utf8;

• 插入数据

insert into studentOne values(1,‘老王’, 69,70,89);
insert into studentOne values(2,‘李明’,89,78,90);
insert into studentOne values(3,‘乘风’,67,89,56);
insert into studentOne values(4,‘南宫流云’,87,78,77);
insert into studentOne value (5,‘南宫皓月’,88,98,90);
insert into studentOne values(6,‘南宫紫月’,82,84,67);
insert into studentOne values(7,“隔壁小姐姐”,47,58,92);
insert into studentOne values(8,“隔壁王大妈”, 57,75,83);
insert into studentOne values(9,“小可爱”, 79,80,80);

  查询

select id , name 姓名, chinese as 语文成绩, math 数学 from studentOne;


select id , name 姓名, (chinese + english+math) as 成绩 from studentOne; as别名查询 ，查询每个学生的总成绩


查询南宫姓名的人的所有总成绩 :
select id , name ,(chinese+english+math) as 成绩 from studentOne where name like “南宫%”;


查询英语成绩大于60的学生的总成绩
select id , name 姓名, (chinese+english+math) as 成绩 from studentOne where english > 60;


查询总成绩大于200的学生的成绩信息
select id , name as 姓名, (chinese + math+ english) as 总成绩 from studentOne where (chinese + english + math) > 200;


查询姓老并且id大于1的学生信息
select id , name from studentOne where name like “老%” and id >=1;


select id ,name from studentOne where chinese > math;
查询语文大于数学的成绩学生


查询所有总分大于200并且数学成绩小于语文成绩的学生信息
select id , name 姓名 from studentOne where (chinese +math + english) > 200 and math < chinese;


查询所有英语成绩在80到90分的同学 不包含80
select id , name 姓名, english from studentOne where english > 80 and english <= 90;


between …and… 查询所有英语成绩在80到90分的同学
between …and…是闭区间
select id ,name 姓名 from studentOne where english between 80 and 90;

查询数学成绩为89,90,91的同学信息 select id , name 姓名 from studentOne where math in(89,90,91); select id ,name 姓名 from studentOne where math = 89 or math = 90 or math = 91;
对数学成绩进行排序 select id ,name 姓名, math from studentOne order by math; select id , name 姓名, math from studentOne order by math desc; -- 降序
对总分进行从高到低输出 select id ,name 姓名, (chinese+math+english) 总成绩 from studentOne order by (chinese + english + math) desc;
统计当前student表中一共有多少学生 select count(*) as 人数 from studentOne;
统计一个班数学总成绩 sum函数 select sum(math) 数学总成绩 from studentOne ;

avg
求数学的平均值
select avg(math) 数学平均成绩 from studentOne;

显示每个部门的平均工资和最高工资 select avg(job),max(job) from EMP order by depton;

建立一个employees表

– 查询部门编号不等于90号的员工名和部门编号
select concat(first_name,last_name )as 姓名,department_id from employees where department_id <> 90;


– 查询工资z在10000到20000之间的员工名，工资及奖金

select last_name,salary,commission_pct from employees where salary >= 10000 and salary <= 20000;


– 查询没有奖金的员工名和奖金率
– is null = 或者<> 不能用于判断null null需要和is not 使用
select concat(first_name, last_name) as 姓名, commission_pct 奖金率 from employees where commission_pct is not null;


– 使用安全等于可以和null一起使用

select concat(first_name,last_name) 姓名, commission_pct from employees WHERE commission_pct <=> null;


– 查询薪资为12000 的员工信息

select concat(first_name,last_name) 姓名 from employees where salary <=> 12000;
select concat(first_name, last_name) 姓名 from employees where salary = 12000;


->- 1. 查询工资大于 12000 的员工姓名和工资

select employee_id,last_name 名字 from employees where salary > 12000;


– 2. 查询员工号为 176 的员工的姓名和部门号和年薪
select employee_id,last_name 名字,department_id 部门号,salary12>(1+ifnull(commission_pct),0) 年薪 from employees where employee_id = 176;


– 3选择工资不在 5000 到 12000 的员工的姓名和工资
SELECT * from employees where salary not in (5000-12000);
select * from employees where salary not between 5000 and 12000;


– 4: 选择在 20 或 50 号部门工作的员工姓名和部门号
select employee_id,last_name 名字,department_id from employees where department_id = 20 or department_id = 50;
?


– 5: 选择公司中没有管理者的员工姓名及 job_id
SELECT last_name 名字,job_id from employees where manager_id is null;
select last_name as 名字, job_id from employees where manager_id <=> null;


– 6: 选择公司中有奖金的员工姓名，工资和奖金级别
select employee_id, last_name 名字,salary from employees where salary is not null;


– 7. 选择员工姓名的第三个字母是 a 的员工姓名 _ 代表一个字符%代表多个字符
select employee_id,concat(first_name,last_name) 姓名 from employees where first_name like “__a%”;


– 8. 选择姓名中有字母 a 和 e 的员工姓名
select employee_id, concat(first_name,last_name) 姓名 from employees where first_name like “%a%” and last_name like “%e%”;


– 9. 显示出表 employees 表中 first_name 以 'e’结尾的员工信息

select employee_id, first_name 姓 from employees where first_name like “%e”;


– 10. 显示出表 employees 部门编号在 80-100 之间 的姓名、职位

select employee_id,concat(first_name,last_name) 姓名,job_id from employees where department_id between 80 and 100;

select employee_id, concat(first_name,last_name) 姓名,job_id from employees where department_id in (80,90);

-- 11. 显示出表 employees 的 manager_id 是 100,101,110 的员工姓名、职位 select employee_id,last_name 名字, job_id from employees where manager_id = 100 or manager_id = 101 or manager_id = 110;
-- 12 查询没有奖金，且工资小于18000的sqlary，last_name select last_name,salary from employees where commission_pct is null and salary < 18000;
-- 13 查询employee表中的 job_id 不为"IT" 或者工资为12000的员工信息 select * from employees where job_id != "IT" and salary = 12000; select * from employees where job_id <> "IT" and salary = 12000;
-- 14 查询部门表中涉及哪些位置编号 select DISTINCT location_id from departments;

• 面试题 ：

面试题 : select * from employees;
与 select * from employees where commission_pct like “%%” and last_name like “%%”;是否一样
答： 不一样因为判断的字段有null值的话第二个查询是获取的只有null的

如果是select * from employees where commission_pct like “%%” or last_name like “%%”; 的话一样因为last_name是不为空的