一、数据库表常用查询语句
(一)查询全部
select * from date.student; # select *(*表示全部) from 库名.表名(库名可以不写,默认正在使用的库)
(二)条件查询
条件查询(where)可以使用的一些逻辑语句说明如下表:
运算符 | 意义 | 类别 |
= | 等于 | 比较运算符 |
> | 大于 | 比较运算符 |
< | 小于 | 比较运算符 |
>= | 大于等于 | 比较运算符 |
<= | 小于等于 | 比较运算符 |
!= | 不等于 | 比较运算符 |
<> | 不等于 | 比较运算符 |
and | 和 | 逻辑运算符 |
or | 或者 | 逻辑运算符 |
not | 不为 | 逻辑运算符 |
in | 非连续范围 | 范围查询 |
between ... and ... | 连续范围 | 范围查询 |
is null | 判断为空 | 判断空值 |
is not null | 判断非空 | 判断空值 |
代码部分:
create database if not exists db; # 创建数据库db
use db; # 使用数据库db
create table if not exists db_student( # 创建数据表db_student
# 列名 数据类型(长度限制) 其余条件, 详见前面博客
uid int not null auto_increment primary key,
age int,
sid char(20)
sex bit,
name char(20),
isDelete bit defult 0 # 这里就是防止误删使用的逻辑删除,加上一条判断,bit表示二进制(即0或1)储存
) engine=innodb default charset=utf8;
# 数据可以自己插入,详见前面博客
insert into db_student(age, sid, sex, name) values (18, '0001', 1, 'Ross'),
(20, '0002', 0, 'Fred'),
(23, '0003', 0, 'Gary'),
(15, '0004', 0, 'John'),
(30, '0005', 1, 'Malcolm'); # 插入多条缺省数据
# 缺省插入,会产生部分空值,用于下面的查询,可以自己输入
insert into student(age,sex,name) values (18, 1, 'Joke');
insert into student(age,sid,sex,name) values (18, '', 1, 'li');
insert into student(age,sex,name) values (36, 1, '高磊'),(20, 1, '高鹏鹏'),(30, 1, '高子龙'),(30, 1, '高兴');
insert into student(age,sex,name) values (34, 1, '高%');
insert into student(age,sex,name) values (34, 1, '高%1'),(34, 1, '高%2'),(34, 1, '高%3');
# 按条件查询
select name from db_student; # 显示数据库db中的db_student全部name(这个name可以是uid、telephone 等一切在表格中的信息)信息,其余不显示
select name,uid from db_student; # 显示name和uid两列
select name as 姓名,uid as ID from db_student; # 显示name和uid两列,并将name一列显示为姓名,将uid显示为ID
select name from db_student where name = 'John'; # 显示name一列name为Joke的人
select uid as ID from db_student where name = 'John'; # 显示name为John的uid并将列名显示为ID
select uid as ID from db_student where name='John' and age=18; # 显示name一列为John,并且age是18的uid,并且将uid显示为ID,and也可以使用or,表示或者。
select * from db_student where age between 15 and 20; # 查询age在15到20之间的人(包含),between介于两者之间,类似于age>=20 and age <=30;
select * from db_student where uid in (1,3,5); # 查询uid是1,3,5的信息,类似于id=1 or id=2 or id=3
多条件的查询支持各种组合,太多就没写了
关于查找空值:空字符串('')和null是有区别的
select * from student where uid is not null; -- 只是不识别null的值,不识别空字符
select * from student where uid != ''; -- 把null也看成一种特殊空字符串,等效于is not null and != ''; !=不等于
# 找空值错误写法,以下运行查询为空
select * from student where uid != null; # 可以理解为这个是一个空对象
# 正确写法
select * from student where uid is null; -- (--和#一样,表示注释) 查询值为null的数据
模糊查询(like),主要是两个参数,见下表
运算符 | 含义 |
% | 任意字符 |
_ | 任意单个字符 |
select * from student where name like '高%'; # 查询姓高的全部学生信息,%任意字符
select * from student where name like '高_'; # 查询姓高的,并且两个汉字组成得学生信息,_单个字符
select * from student where name='高%'; # 查询学生姓名为高%的学生信息
# 其他组合
# 查询姓高的学生信息,并且只显示name,sid,age三列,同时将name列显示为姓名,age列显示为年龄
select name as 姓名, sid, age as 年龄 from student where name like '高%';
# 查询姓高的,并且年龄等于36岁的学生信息
select * from student where name like '高%' and age=36;
# 查询name一列带有高或者兴字,同时年龄大于20岁的学生信息
select * from student where (name like '%高%' or name like '%兴%') and age > 20;
注:where 后面不能接聚合函数 (如avg(平均值)、length(字符长度,否则会出错)) 以下代码为错误语法,注意不能使用下面方法查询。
# 错误语法
select * from student where length(name) = 2; # 错误语法,想查询名字为两个字的学生信息
select * from student where age < avg(age); # 错误语法,想查询年龄小于平均值的学生信息
分页查询,如百度中第几页几页
limit (start) count 从start开始获取count条数据
select * from student limit 5; # 查询前5条学生信息
select * from student limit 2, 3; # 从第二条(不包括)开始,获取3条数据
排序查询,使查询出的数据进行排序
升序(asc):order by 列名 asc, [列2 asc]...
降序(desc):order by 列名 desc, [列2 desc]...
select * from student where name != '' and sid != '' order by age desc;
# 查询名字不为空,sid不为空的所有学生信息, 按照年龄降序输出
select * from student order by age desc limit 1; # 查询年龄最大的学生信息,这种最大的问题就在于要是有2个及以上相同的也只显示第一个
# 子查询,注:子查询必须使用括号括起来,且必须一一对应(如where跟了一列数据,那么max里面也只能跟一列数据),不然会报错
select * from student where age = (select max(age) from student); # 利用子查询可以做到找到全部的信息,只要是最大值都可以显示
# 利用子查询查找年龄倒数第三的学生信息
select * from student where age = (select distinct age from student order by age desc limit 2, 1);
# distinct 用于去重,后面子查询意义为将数据库student中的age一列去重,并且排序(降序),从第三个开始取,取出一位
二、聚合与组合
含义:即对一组数进行计算,同时返回一个单一的值
目的:快速统计数据,方便浏览
(一)、聚合查询
函数 | 含义 |
count(列名) | 计算总行数,括号中也可以写*,写*即表示计算总行数,两者计算结果相同 |
max(列名) | 表示求当前列的最大值 |
min(列名) | 表示求此列的最小值 |
sum(列名) | 表示求出此列的和 |
avg(列名) | 求此列的平均值 |
group_concat(列名) | 按组进行来接数据 |
代码部分:
# 先创建数据库和数据表
create database if not exists date; # 数据库不存在则创建数据库,存在不运行
use date; # 使用数据库date
create table if not exists student(
id int(5) zerofill,
name varchar(5),
age int(3),
gender enum('男', '女')
); # 数据表不存在就创一个
insert into student VALUES (1, 'A', 16, '男'), # 在mysql中VALUES和values是一样的
(2, 'B', 17, '女'),
(3, 'C', 21, '女'),
(4, 'D', 16, '女'); # 随便加些数据,可以自己设定自己写
# count的用法
use date;
select count(1) from student; # 第一列
select count(*) from student; # 全列
select count(id) from student; # 指定列,id叫索引,即列名
# 三者结果相同,但是效率有不同的地方,其中运行效率(即*最慢) 全部(*) < 列号(1) < 列名(id)
# 其他
select max(age) from student; # 显示最大的数,即21
select min(age) from student; # 显示最小的数,即16
select sum(age) from student; # 显示age列的和,即70,注意,字符串相加会显示为0
select avg(age) from student; # 计算平均值,即17.5000
# 扩展
# 函数打印出来会以max(age)显示列名,加上as可以根据你的喜好命名,即:
select max(age) as 最大年龄 from student; # 计算后显示结果列名打印为最大年龄
# 同时,聚合函数也可以加入条件,即
select min(age) from student where age > 20; # 取age一列大于20的最小值,但是where后面不支持聚合函数,如错误写法:
select avg(age) from student where age > avg(age); # 错误写法,where后面不能有聚合函数
分组查询:
# 分组查询
# 基础写法
select * from student group by gender; # 输出全部列,并按照gender分组,但是这样写有个弊端,就是只能显示每一组第一个信息
select count(*) from student group by gender; # 分组统计人数,这样写会只打印一列
# 正常写法
select gender,count(*) from student group by gender; # 统计各组人数,共两列,第一列为组内的各数据,后面则是人数
# 扩展
select gender as 性别,count(*) as 人数 from student group by gender; # 可以搭配as使用
主键和外键:
主键(PK):一个表可以有或者没有,但是最多只能有一个
外键(FK):一个表可以有或者没有,可以一个,也可以有多个
一对多,主键和外键的关系是一对多或者多对多,如,张三在北京有一套房,那么在其他地方也可以有另外一套,这就是一对多;在这里张三即是主键。
多对多,比如说每一个课程表示一个ID(Java、Python、C),学生也可以有很多,比如(张三、李四、王五),而一个学生可以选择多个课程,一个课程也可以有多个学生,这就是多对多。
实例(创建FK(外键)表):代码如下:
外键建表语句:constraint 外键的名字 foreign key (哪列使用外键,这个括号内填写的是列名,当前表的列名) references 要引用的表名(引用表的列名);
下方代码的含义,即建立一个fk_student_Courses的表,并且将Courses_id定义为外键,外键引用的表是Courses,使用的键,即值是id一列
create database if not exists School;
use School;
create table if not exists Courses(
id int not null auto_increment primary key,
name char(20)
);
insert into Courses(name) values ('Python开发'),('Java开发'),('Web前端'),('C开发');
create table if not exists students(
id int not null auto_increment primary key,
name char(20),
Courses_id int,
constraint fk_students_Courses foreign key(Courses_id) references Courses(id)
# constraint 定义 fk_students_Courses 外键名字,随便取 foreign key(Courses_id) 声明外键列名 references引用哪个表的哪一列
);
insert into students(name, Courses_id) values ('Wilson', 1),
('Lily', 2),
('Barry', 3),
('Paul', 4),
('Anne', 4);
PS:选中库,然后键盘按ctrl + shift + alt + u 可以查看表与表之间的关系,当数据库过多的时候是个不错的方法
例:找出上面的表中学生人数大于1的班级
# 首先得知道班级人数大于1的班级ID,那么子查询即:
select Courses_id FROM students group by Courses_id having count(Courses_id) > 1;
# having支持聚合操作,所以一般情况下使用having比where多在这种查询中
# 第二步,显示哪些数据
select * from Courses where id = (select Courses_id FROM students group by Courses_id having count(Courses_id) > 1);
# 这里是显示出满足条件的班级,即显示子查询(这里结果是4,即id为4的数据)中满足条件的数据
其他关于constraint foreign key references的介绍:
# 一般来说这里记不住代码可以使用工具创建,代码如下:
create table if not exists students(
id int not null auto_increment primary key, # 列名id,不为空,主键自增
name char(20), # 名字限定20字内
Courses_id int, # 课程id,整数类型
constraint fk_students_Courses foreign key(Courses_id) references Courses(id)
on update cascade on delete cascade # 主键删则外键删,主键更新外键更新,比如学生一栏学生姓名发生了更改,那么同样的,在课程中也会进行更改,删除后也会删除
);
工具创建外键表:
右键库→new→table
查看工具的建表语句(选中表后ctrl+b):
其他的一些补充:★
where和having的区别:where是对from后面指定的表进行的数据筛选,对于原始数据的筛选,但是having是对group by 的结果进行的筛选,所以对于聚合函数结果进行二次筛选必须使用having,注:where后面不能接聚合函数,having可以使用聚合函数。
三、多表连接查询
连接是在多个表之间通过一定的链接条件,使表之间发生关联,进而能从多个表之间获取数据。
多表查询分类:
1、内连接(join on):
隐匿内连接
显示内连接
2、外连接(left|right):
左外连接
右外连接
1、笛卡尔积
# 建表
drop database if exists db_0916;
create database db_0916;
use db_0916;
# 左表
drop table if exists Left_t;
create table Left_t
(
id int not null,
name varchar(20)
);
insert into Left_t values (1, 'Left_a'),
(2, 'Left_b'),
(3, 'Left_c'),
(4, 'Left_d'),
(5, 'Left_e');
# 右表
drop table if exists Right_t;
create table Right_t
(
id int not null,
name varchar(20)
);
insert into Right_t VALUE (2, 'Right_a'),
(3, 'Right_b'),
(4, 'Right_c'),
(5, 'Right_d'),
(6, 'Right_e');
# 笛卡尔积现象
select * from left_t,right_t; # 这里出来的数据会有点多,因为一一匹配,如1,2为左边表格,a,b为右边表格,那么匹配就是1a,1b,2a,2b
select * from left_t join right_t; -- 这个是等价于上方的语句
输出:
内连接:
格式:select 内容 第一个表 join 第二个表 ... on 连接条件
select * from left_t join right_t on left_t.id = right_t.id;
# 展示左表和右表全部信息,信息来着左表加入右表,链接的目标(条件)是左表id等于右表id,即只有左表id等于右表id才会进行连接,其他信息舍弃,如往下面看下面第一张图
# 其他扩展
select left_t.id, left_t.name, right_t.name from left_t join right_t on left_t.id = right_t.id; # 输出结果如往下面看第二张图
# 这里将*内容改为你要显示的内容,即会显示你要的列,同时也可以as给查出的列进行命名,如下
# select left_t.id as ID, left_t.name as 左表名字, right_t.name as 右表名字 from left_t join right_t on left_t.id = right_t.id;
# 输出结果见第三张图
输出:
外连接:
左外连接:左表全部保留,右表关联不上的用null表示
select * from left_t left join right_t on left_t.id = right_t.id;
输出:
右外连接:右连接-右表全部保留,左表关联不上的用null表示
select *
from left_t
right join right_t on left_t.id = right_t.id;
输出:
左表独有数据(即右表没有的数据,就左表有)
select * from left_t left join right_t on left_t.id = right_t.id
where right_t.id is null;
# 左表独有数据
# 如果is null 变为 is not null 则会取公共数据
select * from left_t left join right_t on left_t.id = right_t.id
where right_t.id is not null;
输出:
右表独有数据(即左表没有的数据,就右表有):
select *
from left_t
right join right_t on left_t.id = right_t.id
where left_t.id is null;
输出:
并集(union):两表连接,取共同数据,union会自动去除重复数据,如果加上all 即union all 那么就不会去除,下图一为去重结果,图二不去重结果
select * from left_t left join right_t on left_t.id = right_t.id
union
select * from left_t right join right_t on left_t.id = right_t.id;
输出:
注:union all 效率比union的效率要高很多
四、子查询
子查询是SQL嵌套查询的程序模块。当一个查询是另一个查询的条件时,称之为子查询。
特点:
1、一个查询的结果做为另一个查询的条件
2、有查询的嵌套,内部的查询称为子查询
3、子查询要使用括号