MySQL一周从入门到精通Day2

数据查询语言DQL --简单查询

1. 了解select的完整语法

SQL中，select语句来查询数据。不同关系型数据库，会有细微差别，在MySQL官网文档中可以查询到,32行查询语法。https://dev.mysql.com/doc/refman/8.0/en/select.html
本节简单查询用到以下两句：

select column_name1,column_name2 from table_name [where where_condition]

2. 掌握使用select进行简单查询

#登录
mysql -h localhost -u root -p
#查看数据库
show databases;
#选择数据库
use mydb;
#查看数据表
show tables;
//已有person表
#表查询
select * from person;	//这里*是选择表的全部字段
//已有部分字段
#表查询（限定范围）
select name,id_number from person;
select name,id_number from person where name='张三'；
#查询小工具
select 8*9;
//返回72

---

数据查询语言DQL --条件查询（单条件/多条件）

1. 熟练掌握where子句各类运算符的使用

where子句（单条件查询）

• SQL中，insert，update，delete，select后边都可以带where子句，用于增删改查
• 语法：

 select column_name from table_name where column_name 运算符 value

• 运算符：= ,<>或!=,>,<,>=,<=,between and

where子句（多条件查询）

• 使用and or 把多个过滤条件结合起来
• 语法：

select column_name from table_name where condition1 and condition2 or condition3

2.熟练掌握多条件查询and,or的使用

#登录
mysql -h localhost -u root -p
#查看数据库
show databases;
#选择数据库
use mydb;
#创建数据表
create table employee(
id int not null auto_increment primary key,
name varchar(30) comment '姓名',
sex varchar(1) comment '性别',
salary int comment '薪资（元）'
);
#插入字段
insert into employee(name,sex,salary) values('张三','男',5000);
insert into employee(name,sex,salary) values('李四','男',5500);
insert into employee(name,sex,salary) values('王五','女',6000);
insert into employee(name,sex,salary) values('赵六','男',6500);
#查询表
select * from employee;
#单条件查询
select * from employee where sex='男'；	//男员工工资
select * from employee where salary between 5000 and 6000;
#多条件查询
select * from employee where sex='女' and salary > 5500;
select * from employee where sex='男' or salary > 5000;
select * from employee where sex='男' and (salary > 6000 or salary < 5500);

---

数据查询语言DQL – in和like的使用

1. 掌握运算符in的使用

运算符in允许我们在where中过滤某个字段的多个值
语法：select column_name from table_name where column_name in(value1,value2,..)

2. 掌握运算符like的使用

where中，查询包含 xxx 字符串的所有记录，就用到了like，类似模糊查询
语法：select column_name from table_name where column_name like '%value%'
说明：

• like中的%类似正则表达式中的*，匹配任意0/多个字符
• like中的_匹配任意单个字符，效果类似%
• like中如果没有% _ ,就相当于=的效果

---

实战:

#登录
mysql -h localhost -u root -p
#查看数据库
show databases;
#选择数据库
use mydb;
#查看数据表
show tables;
#查询表
select * from employee;
#运算符in
select * from employee where id in(1,2,3);
#运算符like
insert into employee(name,sex,salary) values('张七','男',5000);
insert into employee(name,sex,salary) values('张七八','男',5000);

select * from employee where name like '张三'；	//相当于=
select * from employee where name like '张%'；	//张X 张xx
select * from employee where name like '张_';	//张x

---

MySQL常用函数讲解

1. 函数now()

• 返回当前日期和时间，select now();
• 应用场景：多数业务表会带一个创建时间create_time的字段，记录每一个数据产生的时间。所以在insert数据时候就可以用now()函数
• 例如：insert into user(id,name,crete_time) values(1,'张三'，now());

2. 函数date_format()

• 以指定的格式显示日期/时间，date_format();
• 应用场景：在查询使用数据，会有不同格式要求，用这个函数进行格式转换
• 例如：select name,data_format(birthday,'%Y/&m/%d') from user;

3.聚合函数

• 对一组值进行计算，返回单个值
• 聚合函数5个： count（统计）, sum（求和）, avg（均值）, min ,max。后四个忽略空值null

4.函数ifnull()

• 处理null值
• select ifnull(v1,v2) , v1不为null，则返回v1，否则返回v2

5.函数case when

• 流程控制语句，类似if else / switch
• 语法：
• case [col_name] when [value1] then [result1]... else [default] end;
• case when [expr] then [result1]... else[default] end;
• 例如：

//字段含 id name sex(以 0 1 2表示)
select id,name,case sex when 1 then '男' when 2 then '女' else '未知' end as sex from person;	//as别名  0 1 2 整体替换为了男女未知

---

实战

#登录
mysql -h localhost -u root -p
#查看数据库
show databases;
#选择数据库
use mydb;
#查看数据表
show tables;
#查询表
select * from employee;

#count
select count(*) from employee;
select count(*) from employee where sex='男'；
#sum
select sum(salary) from employee;
#avg
select avg(salary) from employee;
#max
select max(salary) from employee;

#null
insert into employee(name,sex,salary) values('hu','男',null);
//统计中null记录会被跳过
select avg(salary) from employee;

#case when
select
	id,
	name,
	case sex
		when '男' then 'M'
		when '女' then 'F'
		else ''
	end as sex from employee;

#查看用户
select user();
#加密
select md5("hu");		

---

数据查询语言DQL–查询结果的排序与分页

1. 排序的应用场景及order by的使用

SQL中，用order by对查询结果进行排序，按照一列或者多列

select column_name1,column_name2
from table_name1,table_name2
order by column_name,column_name [ASC|DESC]

默认按照升序排列，ASC

2.分页的应用场景及limit的使用

在select语句中limit来约束返回的记录数，通常用limit实现分页

select column_name1,column_name2
from table_name1,table_name2
limit [offset,] row_count

1 offset 第一行偏移量，也就是从哪一个记录开始，第一行偏移量是0
2 row_count指定返回的最大行数
分页公式： limit (page-1) * row_count, row_count

---

实战

#登录
mysql -h localhost -u root -p
#查看数据库
show databases;
#选择数据库
use mydb;
#查看数据表
show tables;
#查询表
select * from employee;

//默认是按照id排序的
#按照salary默认升序
select * from employee order by salary;
#升序
select * from employee order by salary asc;
#降序
select * from employee order by salary desc;
#按照两个字段排序
select * from employee order by sex,salary desc;
//优先按照第一个字段排序，在此基础再按照第二个字段排序

#limit
select * from employee limit 2;		//获取前2条数据
select * from employee limit 0，2；	//每页2条数据 第一页
select * from employee limit 2，2；	//第二页
select * from employee limit 4，2；	//第三页

---

数据查询语言DQL–group by和having

1. group by的应用场景和使用

对数据进行分组，必须配合聚合函数（aggregate_function）使用

select column_name,aggregate_function(column_name)
from table_name
group by column_name

2.having的应用场景及使用

SQL语句中,where关键字无法和聚合函数一起使用，因此使用having函数对分组后的数据进行筛选。功能和where类似

select column_name,aggregate_function(column_name)
from table_name
group by column_name
having aggregate_function(column_name) operator value

---

实战

#登录
mysql -h localhost -u root -p
#查看数据库
show databases;
#选择数据库
use mydb;
#查看数据表
show tables;
#查询表
select * from employee;
#创建数据表
drop table if exists employee;
create table employee(
id int not null auto_increment primary key,
name varchar(30) comment '姓名',
sex varchar(1) comment '性别',
salary int comment '薪资（元）',
dept varchar(30) comment '部门'
);
#插入字段
insert into employee(name,sex,salary,dept) values('张三','男',5000,'部门A');
insert into employee(name,sex,salary,dept) values('李四','男',5500,'部门B');
insert into employee(name,sex,salary,dept) values('王五','女',6000,'部门C');
insert into employee(name,sex,salary,dept) values('赵六','男',6500,'部门A');
#查询表
select * from employee;

#性别分组
select sex,count(*) from employee group by sex;
#各部门最高薪资
select dept,max(salary) from employee group by dept;

#having
select dept,count(*) as count from employee group by dept having count(*)<2;	//部门小于2人

---

数据查询语言DQL-- group_concat函数

group_concat的应用场景和使用

使用group by可以分组统计每个部门有多少员工。假如，还想统计每个部门员工姓名（员工列表），如何实现？
group_concat配合group by使用， 将某一列的值按指定的分隔符进行拼接，MySQL默认分隔符为逗号

group_concat ([distinct] column_name [order by column_name asc/desc] [separator '分隔符'])

例如：select dept, group_concat(name) from employee group by dept;

---

实战

#登录
mysql -h localhost -u root -p
#查看数据库
show databases;
#选择数据库
use mydb;
#查看数据表
show tables;
#查询表
select * from employee;

#group by分组
select dept,count(*) from employee group by dept;
#员工列表拼接
select dept,count(*),group_concat(name)as name from employee group by dept;
#员工列表按照名字降序
select dept,count(*),group_concat(name order by name desc)as name from employee group by dept;
#员工列表按照名字降序,分隔符为；
select dept,count(*),group_concat(name order by name desc separetor ';')as name from employee group by dept;

---

数据查询语言DQL-- distinct函数

distinct的应用场景和使用

使用distinct来在查询中返回列的唯一不同值（去重复）。
查询数据时，希望得到某列的所有不同值，用distinct

select distinct column_name,column_name from table_name

例如：select distinct dpt from employee;

---

实战

#登录
mysql -h localhost -u root -p
#查看数据库
show databases;
#选择数据库
use mydb;
#查看数据表
show tables;
#查询表
select * from employee;

#性别distinct
select distinct sex from employee;
select distinct dpt from employee;
//group by可以达到同样效果
select dpt from employee group by dpt;

---

数据查询语言DQL-- 表连接（内/外/自连接）

• 在多张表中获取自己想要的数据
• 表连接join是在多个表之间通过一定连接条件，使表之间发生关联，从而从多个表之间获取数据
• 语法：

 select table1.column, table2.column 
 from table1,table2
 where table1.column1 = table2.column2;

1.表连接的几种方式

• 内连接 join/inner join
• 外连接 左连接 left join ;全连接 full join
• 自连接 同一张表内的连接

2.几种表连接的区别

• 内连接 A∩B ：只连接匹配的行
• 左连接 A
• 右连接 B
• 全连接 A∪B
• 交叉连接 cross join （笛卡尔积 AB排列组合）：尽量避免笛卡尔积

---

3.多表连接查询

#登录
mysql -h localhost -u root -p
#查看数据库
show databases;
#选择数据库
use mydb;
#查看数据表
show tables;
#创建数据表
drop table if exists score;
drop table if exists student;
create table student(
stu_no varchar(20) not null  primary key comment '学号',
name varchar(30) comment '姓名',
address varchar(30) comment '地址'
);
create table score(
id int not null auto_increment primary key,
course varchar(50) comment '科目',
stu_no varchar(20) comment '学号',
score int comment '分数',
foreign key(stu_no) references student(stu_no)
);
#插入字段
insert into student(stu_no,name,address) values('2020001','lily','江苏');
insert into student(stu_no,name,address) values('2020002','herry','浙江');
insert into student(stu_no,name,address) values('2020003','jack','山东');
insert into score(course,stu_no,score) values('计算机','2020001',99);
insert into score(course,stu_no,score) values('english','2020001',89);
insert into score(course,stu_no,score) values('music','2020003',79);

#查询表
select * from student;
select * from score;

#内连接
select A.stu_no,A.name,B.course,B.score 	//选取需要的两个表中的字段
from student A								//给表取别名 A B，区分开
join score B on(A.stu_no = B.stu_no);		//和B表的连接条件
//另一种写法
select A.stu_no,A.name,B.course,B.score
from student A, score B
where A.stu_no = B.stu_no;

#左连接
//内连接基础上改成 left join 就可以
select A.stu_no,A.name,B.course,B.score 	//选取需要的两个表中的字段
from student A								//给表取别名 A B，区分开
left join score B on(A.stu_no = B.stu_no);		//和B表的连接条件

#交叉连接
select A.stu_no,A.name,B.course,B.score
from student A, score B;
//产生了笛卡尔积，所有字段排列组合一遍

---

自连接

• 一种特殊的表连接，指相互连接的表在物理上为同一张表，逻辑上是多张表。
• 用以表中的数据有层次结构，如区域表，菜单表，商品分类表。
• select A.column,B.column from tableA,tableB where A.column = B.column;

#登录
mysql -h localhost -u root -p
#查看数据库
show databases;
#选择数据库
use mydb;
#查看数据表
show tables;
#创建表
create table area(
id int not null auto_increment primary key comment '区域id',
pid int not null comment '父id（0-省份）',
name varchar(30) comment '区域名字'
);
#插入字段
insert into area(id,pid,name) values(1,0,'贵州');
insert into area(id,pid,name) values(2,1,'贵阳');
insert into area(id,pid,name) values(3,1,'遵义');
insert into area(id,pid,name) values(4,0,'江苏');
insert into area(id,pid,name) values(5,4,'南京');
insert into area(id,pid,name) values(6,4,'苏州');

#查看表
select * from area;
#区域
select * from area where pid!=0;

#自连接
select A.id,A.name,B.name as provinceName
from area A,area B 
where A.pid = B.id and A.pid!=0;

---

数据查询语言DQL-- 子查询exists和in的使用

1.in的使用

子查询: 如果in后面的值来源于某个查询结果，并非是指定的几个值，就用到子查询。又称嵌套查询。

select column_name from table_name
where column_name in (
			select column_name from table_name [where]
);

例如：在之前的student和score表中，查询学计算机课程的学生信息

select 学号，姓名，地址
from 学生表
where 学号 in (select 学号 from 成绩表 where 科目=计算机);

2.exists的使用

exists是子查询中用于测试内部查询是否返回任何行的布尔运算符。把主查询的数据放到子查询中做条件验证，根据结果（true/false）决定著查询数据是否保留

select column_name1 
from table_name1
where exists (select * from table_name2 where condition);

例如：select A.* from student A where exists(select * from score B where A.stu_no = B.stu_no);

---

实战

#登录
mysql -h localhost -u root -p
#查看数据库
show databases;
#选择数据库
use mydb;
#查看数据表
show tables;
#查询表
select * from student;
select * from score;

#查询所有选修课程的学生	//in
select A.*
from student A
where A.stu_no in(select B.stu_no from score B);
 
 #查询选修music的学生	//in
select A.*
from student A
where A.stu_no in(select B.stu_no from score B where B.sourse = 'music');
 
 #查询所有选修课程的学生	//exists
select A.*
from student A
where exists(select * from score B where A.stu_no = B.stu_no);
 
 #查询未选修课程的学生	//not exists
select A.*
from student A
where not exists(select * from score B where A.stu_no = B.stu_no);

---