mysql学习笔记(1)_DQL(Data Query Language)

##DQL(data query language)数据库查询语言

• 基础查询  
• 条件查询
• 排序查询 (order by)
• 常用函数
• 分组查询(group by)
• 多表查询
• 子查询
• 分页查询(limit)
• 联合查询(union)

##DQL(data query language)数据库查询语言

##（1）基础查询

#1.查询表中的单个字段

select job from t_employee;

#2.查询表中的多个字段

select job,name from t_employee;

#3.查询表中的所有字段

select id,name,job,mgr from t_employee;

select * from t_employee;

#4.查询常量值

select 'jack';

select 100;

#5.查询表达式

select 5*6;

#6.查询函数(这里是查询当前版本）

select version();

#7起别名(如别名中含特殊符号如空格，#号等，需要加引号）

select 5*6 result;

select 5*6 as result;

select 5*6 'h h#';

#8.查询去重（distinct)

select distinct mgr from t_employee;

#9.concat号的作用，即将两个字段显示在一个字段里

select concat(id,name) as 姓名 from t_employee;

#10.显示当前表结构

desc t_employee;

#11,查询的列为空时可以进行的操作

select ifnull(mgr,1111) as mr from t_employee;

##DQL(data query language)数据库查询语言

##（2）条件查询

#1.根据学生分数由高到低排序

select * from t_student order by score desc;

#2.根据学生分数由低到高排序

select * from t_student order by score asc;

#3.如果不写desc或asc，默认是asc（升序）

select * from t_student order by score;

#4.按年龄的一半升序查询【按表达式排序】

select id,name,age/2 半龄

from t_student

order by age/2 asc;

#5.按年龄的一半升序查询【按别名排序】

select id,name,age/2 半龄

from t_student

order by 半龄 asc;

#6.按姓名的长度对学生信息升序查询【按函数排序】

select id,name,length(name) 姓名长度 from t_student order by 姓名长度 asc;

#7.按姓名的长度对学生信息升序排序同时对其相同的姓名的长度按id进行升序排序【对多个字段排序】

select id,name,length(name) 姓名长度 from t_student order by 姓名长度 asc,id asc;

##DQL(data query language)数据库查询语言

##(4)常见函数

#################

####字符函数

#################

#1.length,获取参数值的字节个数

select length('hello');

select length('hello世界');

#2.concat,拼接字符

select concat(name,'_',id) from t_student;

select concat(name,id) from t_student;

#3.upper,lower,即大写字符和小写字符

select upper('hello');

select lower('WORLD');

#4.substr,substring截取字符串，索引从1开始

select substr('黑夜就是我的面纱',3) out_put;#从索引为3的位置上开始截取

select substr('黑夜就是我的面纱',1,2) out_put;#从索引是1开始往后截取两个字符

#5.instr,返回出现的索引，如果找不到返回0

select instr('自立自主','自') result;

#6.trim 去前后空格,也可以指定去除那种字符

select trim('     lele ') result;

select trim('a' from'aaaaadjiasjdksdjksaaaaaaaaaaa') as result;

#7.lpad指定字符实现左填充

select lpad('克列',6,'a');

#8.rpad指定字符实现右填充

select rpad('克隆',6,'b');

#9.replace替换

select replace('乌鸦坐个大飞机乌鸦','乌鸦','斑鸠') as result;

#################

#####数学函数

#################

#1.round(四舍五入）

select round(-1.55);

select round(1.567,2);#表示小数点后保留两位

#2.ceil向上取整

select ceil(1.22);

#3.floor向下取整

select floor(1.22);

#4.truncate 截断(表示保留几位小数）

select truncate(1.88,1);

#mod , 取余mod(a,b)->a-a/b*b

select mod(10,3);

select 10%3;

#################

####日期函数

#################

#1.now 返回当前系统日期+时间

select now();

#2.curdate 返回当前系统日期

select curdate();

#3.curtime 返回当前系统时间

select curtime();

#可以获取指定部分，年(%Y或%y)，月(%m或%c)，日(%d)，时(%H(24小时制)或%h（12小时制）)，分(%i)，秒(%s)

select year(now());

select minute(now());

select year('2018-2-2');

#4.str_to_date,将str通过指定的格式转化为日期

select str_to_date('1999-10-20','%Y-%c-%d') da;

#5.date_format,将日期格式转化为str

select date_format(now(),'%Y年%m月%d日') curday;

##DQL(data query language)数据库查询语言

##(5)分组查询

#1.查询学生中相同年龄中最高分数的学生信息

select max(score),age from t_student group by age;

#2.查询学生中相同年龄中且名字中带'a'的最高分数的学生信息

select max(score),age from t_student  where name like '%a%' group by age ;

#3.查询学生中相同年龄大于的个数大于2的学生年龄信息

select count(*),age

from t_student

group by age

having count(*)>2;

#4.按多个字段分组

#查询每种年龄每种分数的id之和学生信息

select sum(id),age,score

from t_student

group by age,score;

#5.添加排序

#查询每种年龄每种分数的id之和学生信息，并根据id之和降序

select sum(id) sid,age,score

from t_student

group by age,score

order by sid desc;

#6.查询学生最高分数与最低分数的差距

select max(score)-min(score) difference

from t_student;

##DQL(data query language)数据库查询语言

##(6)多表查询（连接查询）

######################

    #sql92语法

######################

###############

#####等值连接

###############

#1.查询球员以及他所属的球队

select p.name , t.name

from t_player p,t_team t

where p.tid = t.id;

#1.查询球员以及他所属的球队以及球队id(起别名）

select p.name , t.name, t.id

from t_player p,t_team t

where p.tid = t.id;

###############

#####非等值连接

###############

#1.查询员工的工资和工资级别

###############

#####自连接

###############

#1.在同一张表里查询员工名和员工上级的名称

select t.name,e.name

from t_employee t,t_employee e

where t.mgr = e.id;

######################

    #sql99语法

######################

#1.->内连接方式

#1.查询球员以及对应的球队

select p.name,t.name

from t_player p

inner join    t_team t  on p.tid = t.id;

#2.自连接

select t.name,e.name

from t_employee t

join  t_employee e on t.mgr = e.id;

#3.外连接

#案例：查询没有球队的球员(左外来连接）

select p.name,t.*

from t_player p

left outer join t_team t

on p.tid = t.id

where t.id is null;

#左外连接

#查询没有球员的球队

select p.name

from t_team t

left outer join t_player p

on t.id = p.tid

where p.tid is null;

#右外连接 查询没有球队的球员

select t.id , t.name,p.name

from t_team t

right outer join t_player p

on t.id = p.tid

where t.id is null;

#交叉连接(笛卡尔乘积）

select t.*,p.*

from t_team t

cross join t_player p;

##sql92与sql99的比较

##功能：sql99支持的功能较多

##可读性：sql99实现连接条件和筛选条件的分离，可读性更高

##DQL(data query language)数据库查询语言

##(7)子查询

######################

#####where或having后面

######################

##标量子查询

#1.查询比李四分数高的学生信息select * from t_student;

select * 

from t_student

where score >(

    select score

    from t_student 

    where name = '李四'

);

#2.查询比李四分数高,比bobo年龄小的学生信息

select * 

from t_student

where score >(

    select score

    from t_student 

    where name = '李四'

)

and age < (

    select age

    from t_student 

    where name = 'bobo'

);

##列子查询

#1.返回mgr是1001或1002的所有员工信息

select name

from t_employee

where mgr in(1001,1003);

######################

#####select后面(仅仅支持标量子查询）

######################

#1.查询id为1的学生信息

select(

    select name

    from t_student

    where id = 17

) dd;

######################

#####from后面

######################

######################

#####exists后面（相关子查询）

######################

##DQL(data query language)数据库查询语言

##(8)分页查询

#1.查询员工表的前五条数据

SELECT 

    *

FROM

    t_employee

LIMIT 3 , 3;

##DQL(data query language)数据库查询语言

##(9)联合查询

#1.查询分数大于90，而且年龄小于22的学生信息

select * from t_student where score > 90 or age < 22;

###等同于

select * from t_student where score > 90

union

select * from t_student where age < 22;